{"id":428,"date":"2014-01-06T21:38:36","date_gmt":"2014-01-07T02:38:36","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=428"},"modified":"2014-01-06T21:38:36","modified_gmt":"2014-01-07T02:38:36","slug":"create-excel-choropleth-maps-from-shape-files","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2014\/01\/06\/create-excel-choropleth-maps-from-shape-files\/","title":{"rendered":"Create Excel Choropleth Maps from Shape Files"},"content":{"rendered":"
\n

How to transform Shape Files into Microsoft Excel Choropleth Maps \u2013 including 2 Maps of Germany by Zip Codes<\/h3>\n

\"GermanyOn several occasions I thought (and stated) that I already published everything I have to say about Choropleth Maps with Excel.<\/p>\n

Having said that, it seems as if\u00a0\u201cthe ghosts I called I can\u2019t get rid of now\u201d<\/em>. Recently someone asked me if I could provide an Excel Choropleth Map of Germany by zip codes. Careless and stupid boy I am, I answered \u201csure this is no problem\u201d. What a misjudgment.<\/p>\n

If you followed my previous\u00a0Excel Choropleth Map articles<\/a>, you know that it takes either an Enhanced Metafile of the map you can ungroup in Excel to get the shapes or at least an SVG file to transform it to an EMF file using e.g.\u00a0Inkscape<\/a>.<\/p>\n

And here is the roadblock I encountered: I simply couldn\u2019t find a map of Germany by zip codes in one of the required formats. All I could find were ESRI shape files. After hours of searching for EMF and SVG files, I gave up, simply reversed my thinking and looked for a tool to convert shape files into SVG. 5 minutes later I had the solution.<\/p>\n

Today\u2019s post describes how to use\u00a0Indiemapper<\/a>, a free online tool, to transform shape files into SVG which can then be used for Microsoft Excel Choropleth Maps in the well-known way.<\/p>\n<\/div>\n

\n

The Challenge<\/h4>\n

The Choropleth Map approach I used in all articles here requires ungrouped shapes in Excel. The easiest way of getting there is an EMF file you simply import into Excel and ungroup it until you have one shape for each region you want to visualize. If you don\u2019t have an EMF file,\u00a0this article<\/a>\u00a0shows you how to transform an SVG file into EMF format.<\/p>\n

But what if you can find the map you need neither in EMF nor in SVG format.\u00a0Global Administrative Areas<\/a>, for one, provides the administrative regions of all countries of the world. The problem: they are ESRI shapes files, not EMF or SVG.<\/p>\n

Today\u2019s challenge is to find an easy way of transforming ESRI shape files into EMF which can be directly used in Excel for creating Choropleth Maps.<\/p>\n

The Solution – Indiemapper<\/h4>\n

Indiemapper<\/a>\u00a0is a free service provided by\u00a0Axis Maps<\/a>\u00a0making static, thematic maps from geographic data. It also as an export feature to transfer shape files into SVG, JPG and PNG.<\/p>\n

\"Indiemapper\"<\/a>Step 1: Load your Shape Files<\/strong><\/p>\n

After launching Indiemapper, click on Shapefile and browse for the file on your computer.<\/p>\n

\"Browse<\/a>If available, also upload the .dbf file in the following dialogue.<\/p>\n

Step 2: Skip New Layer Option<\/strong><\/p>\n

Skip the next dialogue window to add a new layer by clicking on Cancel:<\/p>\n

\"Skip<\/a>Step 3: Turn off the Graticule<\/strong><\/p>\n

\"Graticule<\/a><\/p>\n

If necessary (like it was in my case), uncheck the strokes checkbox in the Graticule window.<\/p>\n

Step 4: Export<\/strong><\/p>\n

\"Export<\/a><\/p>\n

Click on export at the top right of the website, choose \u201clayered SVG\u201d, select a filename and click on Generate File:<\/p>\n

\"Generate<\/a><\/p>\n

Finally export the generated SVG file to your computer:<\/p>\n

\"Export<\/a><\/p>\n

That\u2019s it. In 4 simple steps we transformed an ESRI Shape File into SVG, i.e. the file format we can now transform into an EMF file and prepare it for being used as a Choropleth Map in Excel, exactly the way I already described it here:\u00a0Build your own Choropleth Maps with Excel<\/a>.<\/p>\n

The Show Case \u2013 Germany by Zip Codes<\/h4>\n

With the few steps described above combined with the technique to transform SVG files into Excel Choropleth Maps (here<\/a>), I was able to produce the following 2 maps of Germany by zip codes in almost no time.<\/p>\n

For your understanding: German zip codes have 5 digits and there are more than 8,200 of them (only the ones which represent a geographical region). Very often, geographical visualizations use only the first 2 digits of the zip code, dividing Germany into 95 regions. 95? Shouldn\u2019t that be 99? No. the remaining 4 are unused or do not represent a geographical area.<\/p>\n

So, here is a map of Germany by zip code 2 (what we call PLZ 2):<\/p>\n

\"Germany<\/a>And here is the comprehensive one: Germany by zip code 5 (PLZ 5) with more than 8,200 shapes:<\/p>\n

\"Germany<\/a><\/p>\n

The Disadvantages<\/p>\n

Let\u2019s call a spade a spade: using Choropleth Maps in Microsoft Excel is nothing else than the poor man\u2019s geographical visualization tool. It takes some time and know-how to set them up and they come with a couple of disadvantages.<\/p>\n

The zip code 2 map with 95 shapes works pretty well. With the zip code 5 map, however, the technique is definitely reaching its limits:<\/p>\n