Using Google Sheets for basic data analysis, data visualization, and mapping can almost instantly upgrade your ability to produce and publish data-driven journalism. Check out, for example, this interactive map of unemployment rates in Tennessee’s 10 Metropolitan Statistical Areas.
June 2015 Unemployment Across Tennessee’s 10 Metro Areas
Redder shading indicates higher unemployment. Bluer shading indicates lower unemployment. Click any metro area for details.
This tutorial, featuring three YouTube-hosted “how to” videos, will show you everything you need to know to produce a post, like this one, that details the latest monthly unemployment estimates for any set of Metropolitan Statistical Areas. The videos use Tennessee’s 10 MSAs as examples, but you could use the same skills to analyze and write about any set of MSAs in any state or region of the country. Best of all, you’ll see how to do it all using the free, Web-based spreadsheet and mapping apps available from Google.
Part 1 shows how to use Google Sheets to capture, filter and analyze the latest Metropolitan Statistical Area unemployment figures from the U.S. Bureau of Labor Statistics’ Local Area Unemployment Statistics page, which provides updated figures once a month.
MSA Unemployment Data Analysis Part 1
Jump to specific points in the video (These links will open in a new window):
- Opening a Google Sheet
- Adding data (Also, here’s a link to the Local Area Unemployment Statistics page)
- Formatting and filtering
- Writing a formula
- Averaging and copying
- Anchoring addresses
- More formatting
- Sorting
Your finished analysis should look like this.
Optional Pro tip:
There’s another, possibly easier, way to get data from a web-based data table into a Google Sheet. To try it out, open a Google Sheet and paste this code into cell A1 (or any cell you like, really):
=importhtml(“http://www.bls.gov/web/metro/laummtch.htm”,”table”,2)
… then hit “Enter.”
Cool, huh? Once Google Sheets pulls in the data, you might want to copy what you want and paste it into a new tab. Otherwise, if you’re editing happens to eliminate the “=importhtml” line of code, the data will disappear on you, because the code is no longer there to retrieve the data.
Part 2 explains how to make an online, interactive column chart in Google Sheets comparing each MSA’s unemployment rate. It also shows how to embed the chart in a WordPress post using the iframe plugin. The video also briefly covers creating other types of charts from other MSA-level employment data.
MSA Unemployment Data Analysis Part 2
Jump to specific points in the video (These links will open in a new window):
- Preparing the data and creating the chart
- Customizing the chart
- Installing the “iframe” WordPress plugin (You have to do this only once)
- Embedding the chart in a WordPress post
- Adjusting the chart’s size and adding rules
- Making a line chart. See the sample data table.
- Making a pie chart. See the sample data table.
Note: Google Sheets has updated its chart editor. To see the new charter editor in operation, watch the MSA Unemployment Data Analysis Part 2 v. 2 video. To use the chart editor shown above, click the “Use the old chart editor” link in the bottom-right corner of the new chart editor.
Part 3 illustrates how to make an online, interactive map showing the boundaries of each MSA and using shading to indicate each MSA’s unemployment rate. You’ll also learn how to configure “pop-up” windows that give additional information about each MSA’s unemployment picture. The techniques involve pairing Google Sheets with one of its cousins, Google Fusion Tables. You can make a similar map using any .kml file you can find on the Web, including those downloadable from the U.S. Census Bureau’s KML – Cartographic Boundary Files page. More advanced map-making skills are covered in the “Excel and making maps with Google Fusion Tables” section of my free, online “Data Journalism” minicourse.
MSA Unemployment Data Analysis Part 3 (Google Fusion Tables)
Jump to specific points in the video (These links will open in a new window):
- Install the Google Fusion Tables app into your Google Drive. You have to do this only once.
- Prepare the data in your MSA unemployment analysis Google Sheet.
- Import the unemployment data into a Google Fusion Table.
- Download a .kml file of all U.S. MSAs and import it into a second Google Fusion Table.
- Merge the two Google Fusion Tables you’ve created. (Note: When merging the two tables, choose the second “name” field on the list for the .kml file. The video directs you to choose the first “name” field, but the Census Bureau has changed the layout of its .kml files since the video was made.)
- Embed the map in a WordPress post.