My affinity for tile grid maps is well documented. I think there a nice alternative to the standard choropleth map and even though they introduce their own geographic distortions (such as South Carolina placed to the east of North Carolina), they are generally easy to create and easy to read.
Given my creation of a US tile grid map, European tile grid map, and the ill-conceived global tile grid map, it was time to bring it a little closer to home. I’ve now made a tile grid map of the 25 zip codes in Washington, DC. (Hat tip to Ann Emery for reminding me that I had created this some time ago and had failed to post it.)
As with all tile grid maps, there are a few issues with this one:
- I haven’t quite nailed down the curvature on the western edge along the Potomac.
- Some zip codes vary greatly in size, so they are hard to align. For example, in this map, zip codes 20005, 20006, and 20036 (“A”, “B”, and “C”) are basically on top of each other. I can’t really do that in the tile version.
- I think the biggest issue is that I didn’t get the three large zip codes in Anacostia (20032, 2002, 20019) to sit on their own along the south-eastern edge of the District.
How to Make It
If you’ve read any of my previous tutorials on the tile grid map, you’ll notice that I create them using Excel’s Conditional Formatting menu. You can’t tilt the cells in an Excel spreadsheet, however, so I took a different approach and instead will use a scatterplot with diamond shapes. (Of course, it is possible to create the map in the spreadsheet, take a screenshot, and then rotate the image.)
Step 1. Data Preparation. I have each zip codes and their populations in columns B and C. I create a little look-up table to place them into four separate bins. I use a VLOOKUP formula in column D to pull out the Group numbers from my look-up table:
The values in that look-up table could be quartiles or quintiles or anything really.
Step 2. Data Series. To make this whole thing a little bit easier, I’m going to plot four separate series (to coincide with the four separate groups as defined in the look-up table). By using separate series, the colors in the chart will update automatically without having to make manual changes to the colors each time. I use a pretty simple formula in columns E-L; in the first cell of column E, for example, I use this: =IF($D3=1,$M3,NA()).
This checks the zip code’s group and, if true, places the “xFull” and “yFull” values in those cells; if false, it inserts an #N/A, which Excel will ultimately ignore when we create the plot. The “xFull” and “yFull” values come from playing around with the scatterplot layout. I simply created a 6×6 grid and started messing around to find where each zip code would go.
Step 3. Create the Chart. Create a scatterplot with the four series, select the points, and change the Marker Type to a diamond. Then, increase the size of the diamond so the edges touch each other (in my version, to 54). Select Marker Colors for the points and change the Marker Border to white. Because I’ve used four different series, I need to set the colors four times, but because this is all built with formulas, when the data or cutoffs change, I won’t need to do anything to the colors.
Step 4. Add Labels. If you’re in Excel 2016 (PC version), you can use the custom label option to label the scatterplots with a custom range. On a Mac or in previous versions, you need to do this by hand by adding data labels and then manually editing them to the zip code number.
A quick aside: I’ve also tried creating a tile grid map for Virginia counties, but some of the smaller counties and districts are really troublesome to line up. I may save that for another day. (A square state like Colorado or Wyoming, or a smaller state, would probably be easier.)
So that’s it, really. A scatterplot with large diamonds. Here’s the Excel file if you want to play around. If you have a better layout, please send it along!