Data Visualization, Spring 2015
(A bit of a work in progress …)
Our goal is to make a choropleth map much like this one below, based on U.S. Census Bureau data, which you can then embed in a blog post or web page.
First, you need your Census Table data source. The spreadsheet needs to be well formatted with a single header row and no extra cruft at the bottom of the file. For this example, we’ll use some Percent Civilian Unemployed XLS from the 2013 1-Year ACS, table DP03. Download that Excel spreadsheet to your computer.
You will need a Google Drive account that is NOT connected to UT-Austin. They don't allow use of the Fusion Tables app.
Upload your data
In drive, click on New, go down to More and then choose Google Fusion Tables. If you’ve never done this before, you will first have to click the “Connect more apps” link there, search for “Fusion Tables” and connect before trying again.
Next, you will browse to the file you are uploading. You can use an Excel spreadsheet, delimited text file, or even a Google Spreadsheet link. For this example, we’ll click Choose File and find our file ACS_13_1YR_DP03.xlsx, and then click next.
Finding your data again
That will take you to a list of all your tables and allow you to search for public tables. It is not an easy place to find by searching and clicking around.
Find your geography
When you upload data, GFT will sometimes sniff out that one of your columns is a city, country or other geography and highlight it in yellow. It might even create a map on another tab. For this lesson, however, we want to merge our data to a specific shapefile of all the counties in Texas, and it is already available as a public file in Fusion Tables.
There are many public shapefiles saved into Fusion Tables, and you can search for them. The one we want for this exercise is named Texas County Shapefile (utdataviz). Click on that link to open it. (To save a new shapefile to GFT, see “Uploading shapefiles” below.)
The merge overview
Before go further, let’s outline all the steps to merge files, then we’ll go over them in detail:
Have both your data file and your shapefile open in Fusion Tables
Decide if you want all your date, or all your shapes, and then start with that file.
Merge in your new file, creating the new combined file.
Style your features and info windows.
Publish and then share or embed as necessary.
Choosing the correct file to start with
Sometimes you don’t have data for every feature in your shapefile. In our example, we have a table of 53 counties of data, but there are 254 counties in the state. In other words, we don’t have every county. (This is a common occurrence when using ACS data. The other counties apparently didn’t have a sample size large enough to be viable for inclusion.) So we have to decide: Do we want to start with all our data and then combine the just to the shapes that have a match? Or do we want to start with all our counties, and pull in just the data that have matches.
What’s the difference? If we start with our data, the missing counties will be absent. If we start with our counties, we’ll have the shape for every county, but the those missing data will show in the default red color. The answer depends on your goal.
For this lesson, we’ll start with the data of the ACS_13_1YR_DP03 table.
Merging
Go ahead and open both your ACS_13_1YR_DP03 data file (Here is my copy) and your Texas County Shapefile in seperate tabs. Compare the two and find the key that is common to both. The column Id2 in the data file matches with column GEOID10 in the shapefile. Connect these fields, and you have a merge.
From your data file, go under File to Merge and you’ll get this window:
You can search for the table you need, or since you followed the directions above, you already have it open so you can copy and paste the url in the web address field at the bottom.
You’ll notice the numbers themselves don’t actually match across the files in this window … it is just showing you a preview of what some of those cells look like so you can see if they are similar. The will match once you hit Next … which you should do now.
Here have a chance to include or not include particular columns that may not be of interest in this merge. Click Merge to complete it.
This is a good time to note that you will be creating a NEW file based on these other two, and that they are all linked together. Changes made to the original data or shape files will be transferred/reflected in this merged combination. (I need to check on this behavior.)
The new merged table
Now you have a new Google Fusion table, and there has been a tab added called Map of Geometry. It shows only the counties that you have in your data. Now we’ll get to color the shading of the shapes, and choose what is in the pop-up.
Change feature styles
Click on the “Change feature styles” button and you’ll get a window like this:
We are filling the polygons based from a “bucket” of the data, in our case using the percent unemployed in the civilian labor force.
Choose how many buckets you want.
Choose the field to pull from
Change the values and colors to make sense.
Also note in the window Legend > Automatic legend. You can go there and choose to “Show polygon fill legend.” There isn’t any customization beyond the name, unfortunately.
Adjust your info windows
Click on the “Change infor window” and you’ll have the option of choosing which fields to show in the window:
But if you then click on the Custom link, you can use some basic HTML to change this info window around. (I’ve put in images, tables and Google Charts into these windows.) In this case, we’ll just clean up the field names:
Publish your map
I renamed my “Map of geography” field to “Unemployment” by clicking on the menu and choosing rename. Then to publish your map, but under that same dropdown to Publish.
You then get a screen where you can get a link to share your project, or embed code put your blog on a website or blog.
There are many options there to use, but here are some hints:
If you send someone a link to you map, use this link on this Publish page from the map, not the url to the table. The public presentation is nicer than looking at the table view.
If you are going to embed, look at the width and height. iframe should work on most sites.
Some hints about saving data from American Factfinder
Google Fusion Tables can be picky about non-numbers being in number fields, so you might follow these steps when downloading Census data.
Find the table you want click on it so you are at the Table View and can see examples of your data.
Click on the download button. When you get the pop-up, choose to save a CSV file, with “Data and annotations in separate files.”
You’ll use the file that ends in “.csv” that does NOT have “_ann” in the name.
Clean up your data so you have one header row that you can read and understand. Make sure you keep the id numbers so you can match to a shapefile later.
If you end up saving the data as a spreadsheet, and adding more sheets to it, just make sure that the data you want to load is on the first sheet in the Workbook.
Uploading shapefiles
Google Fusion Tables does not take the most common .shp file format that you get from ArcGIS. It instead uses the .kml files (Keyhole Markup Files). More and more sites supply a .kml, too, but you might have to convert many of your .shp file to.kml. There are several ways:
We’ll learn to do this with free QGIS later in the semester.
You can upload to shpescape.com, which is also free, but often backed up.
There are some command-line tools ogr2ogr, but you have to know what your are doing both with shapefiles and the command-line.
Other useful Google Fusion Table shapefiles already uploaded:
This is a quick post to show how you can scrape data from websites using Google Spreadsheets. (There are ways to do this in Excel as well).
