In Liquidations – Map Visualization, Tony Rose of DSA Insights reviewed a set of charts created by Business Week (click on the image below for the original full-size graphic). Tony described some shortcomings of these
The graphic consists of bubbles representing the numbers of store closings within a state, centered on the state. This is like the dots used for representing cities on a map, with different sized dots for city populations within a certain range. Business Week obviously tried this, but a cartographer uses dots which are small compared to the distance between cities. Business Week’s charts have some bubbles which run into each other in regions where states have small geographical areas but high store densities. The northeast region of the Circuit City map (left) is difficult to resolve, and the same region of the KB Toys map (right) has such severe overlap, that it’s impossible to read.
A second issue relates to the sizes of bubbles in the legend and in the maps. Apparently the maps and legend were created separately, then resized independently while building the overall graphic. For example, the Circuit City map has five bubble sizes, the largest (CA) being 40 pixels wide and the second (TX and FL) 27 pixels wide. The legend also has five bubble sizes, the two largest being 26 and 17 pixels. Without counting the different sizes and translating the set of legend bubbles to the set of map bubbles, it is all too easy to misinterpret the values.
While it is a nice touch to see which regions of the country have high and low values, the way the bubbles were used make this less effective than a color-coded heat map of the states would have come out. Of course, the heat map suffers from distortion due to states with smaller geographic areas having higher store densities.
Can someone think of a better approach than the bubble map? What’s wrong with a bar chart? We can use the actual values, not an arbitrary range, and sort them by this value.
After examining this bar chart, a second chart might show a heat map of the states, with white, light, medium, and dark shades of a single color.
Note: The Business Week map uses final values for Circuit City closings, of either 567 stores closed in 2009 or 722 stores closed in 2008-2009. My chart and map use the more readily available list of 155 Circuit City store closings announced in November 2008.
If you wanted to, you could color code the bars in the bar chart to match the area colors in the map.
Tony thought the bubble maps had an advantage of being able to quickly see which states have large numbers of store closings, with a disadvantage of not being able to readily resolve the smaller numbers. The filled-in map that I generated has the advantage of being able to quickly see which states have large values, small values, and zero values. In conjunction with the bar chart, you can see geographically how different regions were affected by the store closings, and determine the actual numbers of closings for each state.
Anonymous says
I love the idea of charting by state/country in Excel – I’ve tried various forms of this all with abysmal results. Would greatly appreciate further info on this if possible!
Jon Peltier says
Excel is not well suited for geographical analytics. You can get third party software packages that work with Excel, or that work alone, to produce these maps. Most of these packages are costly, and your data will require a lot of preparation before it can be plotted.
For the map in this post, I simply took a bitmap image of the state boundaries, used a program similar to MS Paint to fill in selected areas with the appropriate fill colors, and built a legend out of shapes and text. Not too much work for this purpose, but I would not use this approach if I had to do more than a small number of them.
Stefan Sandauer says
You can use the solutions of Tushar Meta or Jorge Camos on this matter. I worked out a third way i would submit, if you want.
Jon Peltier says
Both Tushar’s Dashboard example – conditional colors of shapes and Jorge’s How to create a thematic map in Excel rely on creation of individual map shapes and programmatically changing their colors based on data in a worksheet. The techniques work reasonably well, but they take a lot of prep. You need to draw the shapes, or hunt for them on the internet. You need to set up the data. You need to set up a range that is used to convert values into colors. And you need the code to change the colors of the shapes.
Stefan – I’d like to see your approach too.
Liu 's chart blog says
Jon－
Here is my solution to draw a bubble data map ,just like that in the biz weekly .
Keith says
Nice to see a little retail-centric info design.
Jon, can you recommend 3rd party geographical analytics add-ons for Excel?
Jon Peltier says
Keith –
I’ve never used one. There were a lot of things listed in a Google search. I was only looking for familiar names, and only Tableau caught my eye, so you’re on your own.
For what it’s worth, Tableau looks like a great piece of software for analysis and visualization. It’s not cheap, but it does a lot of things easily that are difficult in Excel. Tableau handles maps, but all of their posted examples show dots instead of filled regions.
You should also look at Many Eyes, a online graphics service that includes maps with bubbles and with filled regions.
DMurphy says
A very interesting blog, particularly in this financial crisis. However, I personally believe that the data itself does not give the full picture. Yes, we see that CA has lost 24 stores, but out of how many – 25? 50? 100? – and that MI has lost 7, but again, out of how many? Is there a geographical/demographic effect?
Although I don’t really like them too much, would a 100% Stacked Bar show a better picture, where the second series is the number of stores remaining “open”. This would provide the reader with added info of whether these are just “cut-backs” or “total cessation”. e.g. here’s a quick’n’nasty example and I have to stress that I have randomly generated the total number of stores (so I apologise if it gives a totally incorrect image of Circuit City Stores see:
Being a non-US reader, my geography of the US States leaves something to be desired, so I do like the idea of plotting on a map – it gives the reader an insight to geographic spread of the stores. However, it again does not provide all the information I seek – including how many stores remain open. Can anyone do a relative “donut” rather than single circles – that would provide me with a chart of the geographic spread AND the total store info (open/closed)?
My tuppence worth (or 2-cents’ worth if you prefer).
Jon Peltier says
David –
Thanks for the note. Yes, the data I had was not complete. I know it represented about 20% of the Circuit City stores then in existance, but I don’t have the list of the stores that didn’t close until 2009, so I don’t know how the percentages were distributed. If I had the totals, I would have considered a stacked column (probably not 100% stacked) and stacked the still-open numbers on top of the closed numbers.
An interesting display might have a column chart with the numbers by state, with a map showing percentage by state.
Of course, the percentage that has closed in 2009 is 100%.
Tony says
Jon – I think you’ve hit on the highlights and two very important points.
1) Column chart shows it much more accurately. I also really like the column chart with the different shades of color that you created.
2) The map shows the proximity of states and values to each other, which is not visible in the column chart.
I prefer both of your charts over the bubble chart on the map.
I’ve used Microsoft MapPoint, which is OK. I am a big fan of Tableau and have also used Many Eyes, which is free. Thanks for the link.
Sjoerd Hoogwater says
Off-topic (couldn’t find a better place to let you know):
Here is another fun cartoon of chart-use:
http://www.ritholtz.com/blog/2009/04/fancy-charts-graphs/
Jorge Camoes says
When I created the tutorial on how to create a map in Excel I though I would be easier to find maps in WMF format or an utility to convert ESRI or Mapinfo files. That’s not exactly the case.
If anyone wants my Excel file with the states map just send me a message. I’ve been planning to update that post and add the file, but something more urgent always get in the way.
Colin Banfield says
Mapland has been around for some time. I haven’t tried it, but the Basic version is inexpensive. There’s also a demo version for download. Looks similar to the add-in that was once shipped with Excel.
http://www.softill.com/home.html