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)
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”
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.
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.
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!