Tag Archives: Fusion Tables

Exploring America: Visualizing Data with Google Fusion Tables

Interactive maps and charts can be a great way to add interest and visual appeal to a primarily textual work of journalism, and in a newsroom there are many times when it’s only after the article has been largely written that we think to create them. But visualizations can also serve as essential reporting tools in and of themselves, allowing journalists to see patterns in otherwise impenetrable data sets – patterns that can provide essential leads to finding interesting stories in the first place.

This habit of pattern-finding and data analysis is already well known in the area of journalism usually known as “computer-assisted reporting” (CAR), and where it exists is usually the purview of perhaps one or two specialist reporters. Typically, the tools associated with CAR have been both reasonably expensive and time-consuming to learn: database technologies like Microsoft Access; mapping technologies like ArcGIS. Few individuals and not all organizations could realistically afford the software, training and personnel required to do this kind of work.

At the infamously (and in this case, happily) exponential pace of technological evolution, however, there are now free, user-friendly tools whose power and versatility is rapidly surpassing the more “traditional” tools of CAR. A prime example of this Google Fusion Tables, which combines the essential functions of a database (large-scale data storage, powerful sorting and filtering, the ability to merge tables) with the robustness of Google’s mapping resources (and their many charting tools).

A quick walk-through of how to use the the merging and mapping functions is provided through the Fusion Tables Help, of which the below is an edited and annotated version. To follow along, you’ll need to be logged in to a Gmail account.

To start, click on each of the links below (these should come up in new browser tabs):

110th US Congressional District Outlines

2008 1-year American Community Survey (ACS) Data

You’ll notice that these pages basically looks like giant spreadsheets, and this is essentially what they are. Looking at the column headers, take a moment to notice that the format of the Outlines table’s “id” column and the ACS table’s “Two-Digit District” column is very similar.

Looking a little further, you’ll see that in the upper-right there is a count of the number of rows in the table (e.g. “1100 of 436″ for the ACS data); clicking on the “Next>>” link will show you the next 100 rows.

Just below the “Next>>” link, in the gray title bar, you’ll see a square button with a small triangle in it – this is the subtle clue you’re given that there are more columns of data than what you currently see. If you begin clicking this in the ACS data set, you’ll quickly discover the many, many columns that this table contains.

Browsing that many columns of data is tedious, though, and impossible to analyze. So let’s get it mapped instead so we can see really see what’s here.

In the Outlines table, click the “Merge” link in the blue bar. In the box on the left, you see radio buttons for each of the columns in that table, with “id” selected. Above the empty box on the right are the instructions: “Merge with” followed by an input box and a GET button. Ignoring the dropdown, paste the url of the ACS table (http://www.google.com/fusiontables/DataSource?dsrcid=237928) in the input box and click “GET”.



The right-hand box now contains radio buttons of every column in the ACS table – but what it wants you to do is tell it how to match up the information in the two tables. Remember How “id” and “Two-Digit District” looked pretty similar? Make sure those are the radio buttons selected and then type a name for your about-to-be-merged table into the input box labeled “Save as a new table named”. Finally, click the “Merge Tables” button.




Very often a red box with the warning “Could not merge tables” will appear for a moment. Ignore this and wait for the page to finish loading.

You’ll now be looking at a new Fusion Table with the name you entered earlier. The columns from the first table have a white background; those from the merged or “joined” table are pale yellow. Because we connect outline information with data, we can now see that data visualized by selecting “Visualize >> Map” in the blue bar. After several seconds, you’ll see a bright red Google Map of the U.S., with gray outlines marking the 110th Congressional District boundaries.


Click somewhere on the map. After a moment, a balloon pops up with a readout of the first 10 columns of table data for that area. That’s a little useful, but if we were interested in data only about one district, it would have been just as easy to read from the table. Instead, click the “Configure styles” link above the map.

In the “Configure map styles” popup, click on “Fill color” under the “Polygons” header at left. To the right, click “Buckets” and then select the “Divide into 2 buckets” radio button. First, click on the “2” dropdown and change your number of buckets to “4”. Open the “Column” dropdown below this and you can quickly scroll through all of the data columns available in ACS table. To start exploring, select one and click the “Save” button at the bottom. After a moment, a yellow “Map style saved” label will appear above the map, and it will be recolored according to your selection. To see how other columns of data map, simply click “Configure styles” again, select a different column of data from the dropdown, and click “Save”.

A few notes:

  • Keep in mind that the default “bucket” ranges (0-25, 25-50, 50-75 and 75-100) may not be ideal for the particular data column you’ve chosen. You may need to adjust these values in order for the map to be meaningful (or even show any color variation at all).
  • Also note that the default “bucket” colors should really be shades of a single color, rather than 4 distinct colors. Any time you are mapping the intensity of a single value, it should be indicated by intensity of a single color, not multiple colors.
  • Why doesn’t your chosen data point show up in the little balloon? You need to adjust its contents by clicking the “Configure info window” link above the map. There you can select the with check boxes the exact column information you want to appear. Select a few on the left, click “Save” and then click on the map again.

Having played around with the data for a little while, you’ve found some interesting data points. I’m always interested in rent burden and housing affordability, so I chose the very last column of data “Percent of Renter-occupied Units Spending 30 Percent or More of Household Income on Rent and Utilities”. After using the table view and (by clicking on the column header) sorting the data and find its minimum and maximum values, I adjusted my ranges to be 0-30, 30-45, 45-60, and 60-100, colored in shades of red. The result is a map that shows a few interesting things – at first glance, we note that Nebraska’s 3rd Congressional district is the only one in the country where more than 70% of people live in affordable housing, and two of the most rent-burdened districts are California’s 45th and Florida’s 25th – not in districts in New York, San Francisco, Los Angeles or other notoriously expensive cities. What’s going on here? This data alone won’t tell us, but it has given us a lead towards what might be an interesting story.

Once you’ve done the rest of your research and discovered some of the “why” behind your visualization, you’ll want to make sure your readers have your data at their disposal. To add the map to your page/site you’ll need to do 2 things:

1. Share it. In the upper-right corner of the map or table view, you’ll see a “Share” button that brings up a popup. In the bottom half of this window, three radio buttons list the “Visibility options”. To add your map to a webpage, you’ll need to make it at least “Unlisted”, if not “Public”.







2. Embed it. Click “Get embeddable link” above the map and a small scrolling window is revealed above the map with code that you can paste into an HTML. To it into WordPress, as above, you’ll need an iframe plugin like Easy iFrame Loader installed. Using the revealed code, follow the directions to add the map to your post or page.

If you want only to email your link to a few people (and not have others be able to view it) you can leave the “Visibility options” on “Private” and share it either through the “Share” popup, or else email them the link made visible when you click the “Get KML network link”.

So that’s a first round on using Google Fusion Tables to generate explore and share data sets through interactive mapping. There are many other features available here though, so there will undoubtedly be more Google Fusion Tables fun to come.