So here’s the scenario, you have a spreadsheet containing some interesting data about each county in the U.S and you would like to be able to make a web map from it in order to visually explore patterns in the data.
This tutorial will show you how this can be achieved quickly and easily using free tools. For this example we’re going to map U.S unemployment by county.
Understanding the Data
You can download the sample data we will use here.
The sample data contains five files. The first is data.csv, this is tabular data of U.S unemployment by county and contains two columns “fips” (county code) and “rate”. This data doesn’t contain any geographic information which can be mapped.
The remaining four files called counties (.shp/.dbf/.prj/.shx) are collectively known as a Shapefile. A Shapefile is the de facto standard for sharing geographic data and can be read by most mapping applications. Shapefiles with administrative boundaries for most countries can be downloaded for free online, to find this Shapefile we could just Google for “US county Shapefile” and we’ll be returned many sources for the data.
Loading the Data into QGIS
Now that we have our unemployment data and a Shapefile of U.S counties we need to join these two files together. For this we are going to be using QGIS which is a very popular free and opensource desktop GIS program that can be downloaded from here.
From within QGIS in the menu bar choose Layer → Add Vector Layer and choose counties.shp from the tutorial sample data. You will now see a map on screen showing the counties. If we right click on “counties” in the left hand layer panel and choose “Open Attribute Table” we will be able to see the information contained in the attribute table for the counties Shapefile. As you can see each county only has one piece of data and that’s the fips code (unique county ID).
Next we need to import our CSV file by pressing Layer → Add Delimited Text Layer and choosing data.csv from the downloaded sample data. You will also need to set the file format to CSV and set the “Geometry Definition” option to “No Geometry” then press OK.
Joining the Shapefile and the Spreadsheet
Now it’s time to “join” the datasets. Right click on “states” in the left hand layers panel and choose “Properties” and go to the Join tab. From here press the green add icon at the bottom. To join datasets we need to use a property which is unique and present in both datasets, in this case it’s the “fips” code column in the Shapefile and the “fips” code column in the CSV. When we press okay QGIS will match the records using the fips code and then add the additional columns in our CSV (rank & rate) to the attribute table of the Shapefile.
Pro Tip: For this example we’re joining the records using the fips code, but you could just as easily join records using any column that has unique values, such as state name or zip code. Trying to join on columns where the values aren’t all unique will cause data errors.
Once complete we can right click on counties in the left hand layer menu and select “View Attribute Table”, you will now see that a new column has been added called “data_pvrty” that contains the poverty rate for each county.
Close the attribute table and right click again on counties and select “Save As” to save the newly joined dataset. We now have a Shapefile that can be used in any mapping application to visualize the unemployment rate of counties.
Creating our heatmap in Mango
Now that you have your Shapefile let’s upload it to Mango and take a look. If you don’t yet have an account your can sign up for free here.
Once you have logged into your map portal just complete the following steps:
- Press the “Create Map” button the admin sidebar
- Press “Add Layer → Upload Data” Select the four files of the Shapefile we made.
- Once the upload finishes choose “Quantity” in the Layer Style panel.
- In the Class Breaks row, select “data_pvrty” from the Column dropdown, and select “5” as the Number.
- Press the green Custom Legend button at the bottom of the panel, and update the values in the Range column in the table to 5, 10, 15, 20 and 25
- Press Done, then hit Save on the Style panel.
You will now be able to see your heat map showing U.S counties by unemployment. Please take some time to experiment with different values to see what happens to the map.