Google Fusion tables – putting data together

I was lucky enough to spend two weeks at the Guardian Datablog over Christmas, where I got to grips with Google Fusion tables.

The beauty of Fusion tables are that you can add multiple spreadsheets together to make a single table, which you can then download as a spreadsheet. This is particularly useful for compiling data that is released over time such as, say, detail on spending that is released monthly by councils or government departments.

Several councils around the UK have begun to publish datasets of spending on their websites. Let’s take Manchester City Council as an example and see how we can build one table of all their 2012 spending (except December, which hasn’t yet been published).

Step 1: Find your data and download all the relevant files

These are likely to be .csv files for the public sector.

Step 2: Open the latest file to see how it is formatted

It should look like this (the columns may need to be expanded so all the data can fit)

screenshot mcc 1

Step 3: Open all the other .csv files. Check for formatting and dates

Government and local councils are opening up their data, which is a big step in the right direction, but sometimes there are still problems with the formatting. They may have the wrong dates, or the formatting may have changed halfway through the year. Check all the columns are lined up in the same order and that there are none missing.

Step 4: Save the .csv files as .xls files.

That means resave them as Excel spreadsheets, Google spreadsheets or whichever program you’re using. You might want to rename them if the file name is too full of jargon.

Step 5: Open a Google Fusion table

You need Google Drive for this. Click “Create”, “More”, “Fusion table (experimental)”,then “Create empty table”

mcc screenshot 2
A brand new Fusion table

Step 6: Create columns in your table that match your spreadsheets

Add new columns if you need to, and give them the same headings as there are in the spreadsheets, which should be identical after step 3.

Step 7: Upload the spreadsheets to the table

OK, we’re ready to upload now. Click “File”, “Import more rows”, then select your file from your computer. Check quickly that the headings match up (it gives you a chance to do this before uploading) and then click “Finish”. A good habit to get into is to check off each spreadsheet as you go, perhaps by labelling it a different colour, renaming it “…DONE” or something else to show that it’s been uploaded. Uploading 20 or more spreadsheets can take time, and you don’t want to lose track of where you are and risk duplicating any spreadsheets.

mcc screenshot 3

Step 8: Download your Fusion table as a spreadsheet

When all of your spreadsheets have been uploaded, click “File”, “Download”. It downloads it as a .csv file, which of course can be converted into a .xls file as in step 4.

Congratulations!

You have just created a spreadsheet from a fusion table. Using the Manchester example, you now have eleven months of spending in one spreadsheet rather than just one month’s worth or eleven different spreadsheets. The next task is to analyse your new spreadsheet and see what you can find. Pivot tables are a good way to do this, and Siân Boyle (another Interactive MA student) has written another post on the Interhacktives website [I’m involved in its editing] about how to get started with them. Happy hunting!

Advertisements

4 thoughts on “Google Fusion tables – putting data together

    1. Anna, not sure what you mean here? If you change the data in your spreadsheet, I don’t think it changes in the Fusion table. You can, however, edit rows in your Fusion table and you can “import more rows” if you have new data into your Fusion table. Does that help?

      Like

  1. “Google Fusion tables – putting data together « ROB GRANT” ended up
    being a superb post, can’t help but wait to look over much more of ur postings.
    Time to squander a little time on the internet lolz.
    Thanks ,Teresa

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s