People love looking at maps. It’s just one of those facts of life. So since economic analyses are always tied to a geography of one form or another, there’s really no excuse not to communicate the results of your analyses visually using maps.
Fortunately, if you have your analysis results ready, you don’t need to purchase complicated geographic information system (GIS) tools or software to generate a map like the one below—all you need is a Google account and this totally free Excel spreadsheet we’ve put together for you (jump to the bottom of this post to download the spreadsheet).
How to make a map
1. Prepare your results
Using either Google Sheets or Microsoft Excel, create a spreadsheet with (at the very least) a column for county names, a column for your results numbers, and a column for the “polygon.” Polygon data are coordinates which tell Google Maps where to draw the shape of the county on the map and which other data points belong to that county.
For demonstration purposes, I’m going to look at industry output for wholesale trade (IMPLAN Sector 395) for each county in Oregon using our data from 2015. That makes for a spreadsheet which in Google Sheets looks something like this:
2. Add Polygon Data
Now we need to add a column to our “Oregon Wholesale Trade Output by County 2015” spreadsheet with polygon data for each county.
To grab the polygon data, open up the spreadsheet “US_County_Polygons.xls” that you’ve just downloaded. Copy the polygon data in column D-Polygons which appear in the rows with their corresponding county names. Rather than scrolling through the whole spreadsheet, I searched for “OR-” to find only the data in column B which correspond to counties in Oregon.
Paste the polygon data into the “Oregon Wholesale Trade Output by County 2015” spreadsheet.
Side note: If you want to map the whole country, then the fastest way to go may be to add the data from “US_County_Polygons.xls” as a tab in your results spreadsheet and then use ‘County Name,’ ‘State-County,’ or ‘State Abbr’ as a common key to call for corresponding polygon data into a column in the same tab as your results.
3. Add Fusion Tables to your Google Drive
Now it’s time for the fun bit! From Google Drive, click on “New” in the upper left corner. From the drop-down menu, select “More” and then “+Connect more apps.”
In the search bar in the upper right corner of the modal, type “fusion tables” and hit the return (enter) key on your keyboard.
Click on the “+CONNECT” button next to the top result and then click on “OK.”
Click on “New” again in the upper left corner of the screen and now choose “More” and then “Google Fusion Tables.”
Once you click on “Google Fusion Tables,” a new tab will open with a modal which prompts you to supply a spreadsheet. You’ll have the option to upload an Excel sheet, use a Google Sheets file, or create a spreadsheet from scratch. For the purposes of this demonstration, we’ll use the Google Spreadsheets option.
From here, you can either find the Google Spreadsheet using the search bar or scroll through available spreadsheets in your Google Drive. Once you find the spreadsheet you want to use, click on the file’s icon to highlight it and then click on the “Select” button.
It may take a few moments for the new Fusion Table to read the spreadsheet. Once it’s finished, a new modal will appear. Click “Next.”
In the next modal, you’ll have the opportunity to name the Fusion Table and add some metadata to the file before advancing to the editable project. Once you’re happy with the state of things, click “Finish.”
4. Editing your Fusion Table
The first thing you’ll see once everything is set up is a view of the first of three tabs in the project which contains the original data from your spreadsheet. This data now exists independent of the original spreadsheet so any new changes to the source data will have to be made manually here as well (or you could very easily start over).
To see the state of things geographically, click on the third tab named “Map of County Names.”
What you’re seeing now is a zoomed-out map with pins for the names of every county—the default for all fusion tables in which the project recognizes county names (that’s why we’re seeing a few pins conspicuously dropped outside of Oregon).
Click on the drop down menu next to the word “Location” in the upper left of the “Configure map” section and select “Polygons” from the drop down menu.
Now that the map is referring to our polygon data rather than county names, you’ll see that it’s now drawn the shapes for each county on the map. Zoom in to your study area just as you would normally while using Google maps.
Click on the “Change Feature Styles” button in the left sidebar. A new modal titled “Change map feature styles” will appear. Click on “Fill color” in the left side navigation of the modal. Then choose the “Buckets” tab from the “Polygon background colors” box. From here, you’ll be able to edit the number of colors for distributions of values for each county. There’s a lot to edit in here in no particular order so let’s break it down:
- From the “Divide into _ buckets” drop down, choose “6” (or however many buckets you’d like).
- In the drop down next to the word “Column,” choose “Output ($)” which, in this case, is the data column that we want to visualize on the map.
- Click on “use this range” to evenly distribute the values in the “Output ($)” column into the 6 buckets.
- Click on the color drop down menus on the far right of each bucket row to change the colors.
Once you’re satisfied with your bucket and color scheme, click on the “Save” button.
Now your map will be colored with the specifications you’ve just saved. Neat-o! Click here to see the whole map in a new browser window.
5. Publishing and Sharing
To share your map with the world by embedding it on your website, click on “Tools” from the menu bar and then click on “Publish…”
A new modal will appear with pre-written code for embedding on your website. Copy and paste the text from beneath the “Paste HTML to embed in a website” heading” into your web page code.
You’ll also find options for printing or exporting your map as an image file.
Wrapping it up
This is only one small way which you can use IMPLAN and Google Fusion Tables to visualize data. With the polygon data added to your IMPLAN results, you can make heat maps, add legends, alter what data points are visible to users who click on counties, and a bunch of other cool things. But everything you’ve done so far (if you’ve been able to follow along with this tutorial) is all you need in order to figure out all kinds of impressive ways to use this tool.
Download the U.S. County Polygons Spreadsheet!
Enter your email below to access a downloadable Excel (.xlsx) file prepopulated with county polygon data.