Dog shit and data – playing with Google Fusion tables

I’ve heard a lot about Google Fusion Tables over the last few weeks. Simon Rogers, editor of the Guardian Datablog, must have mentioned them 10 times when he came to talk at City last month. Some of our other lecturers, including Paul Bradshaw and Gary Moskowitz have also mentioned them in class or in passing.

As a fledgling interactive journalist, they sound pretty important. Especially when I don’t really know what they are.

Being the industrious student that I am, I thought I would ignore all of the immediate work I should have been doing over the weekend, and instead have a play around.

I thought I would keep things simple and create an interactive map – the type of thing you see on the Guardian all the time.

I knew I would need some data so I decided to head over to whatdotheyknow.com to see if anyone had kindly submitted a single freedom of information request to all of London’s borough councils.

Luckily for me, a man called Hugh Roberts had recently used whatdotheyknow to FOI 165 councils across the country about dog shit.

Success!

Hugh had asked each of the 165 councils (including the London borough ones):

    • how many complaints have you received about dog fouling from 2005 to 2012?
    • how many fixed penalty notices for dog fouling did you issue during the same period?
    • what were the dates of any dog fouling campaigns the council participated in?

I decided to discard the third question for the moment and focus on the first two.

I created a Delicious bundle consisting of every whatdotheyknow response page for each of London’s 33 boroughs.

Unfortunately, Hugh probably didn’t attend Heather Brooke’s excellent talk on the FOI act last month, so he hadn’t known to ask the councils to provide the data in Excel format. This meant laboriously trawling through every response letter, deciphering the myriad of different ways that each council had chosen to display its information.

The irony that I was doing the digital equivalent of sifting through dog shit wasn’t lost on me.

I then totted up the figures and put them into a spreadsheet:

Not every council had successfully responded to Hugh, so I only had data for 26 of the 33 London boroughs. Once I had finished with the spreadsheet, I exported it as a CSV (comma separated values) file.

Next, I went to my Google Drive and clicked on Create –> More –> Fusion Table (experimental). I uploaded my CSV file, gave the table a name and a description, and clicked Finish.

The data was then imported into a Google table:

Then I went to research.google.com/tables and searched for ‘London boroughs’. I clicked on a result that looked like it would contain the borough outlines and copied the URL.

Back on my original table, I clicked on File –> Merge, and pasted the URL into the box that appeared. After clicking Next, I made sure that the ‘Borough’ categories matched up from both tables, clicked Next again, made sure every tick box was ticked, and finally clicked Merge.

Google then fuses (see where the fusion aspect comes into it? Clever stuff!) the two tables together. In the resulting window, there’s a tab called ‘Map of Outline’. Click it, and hey presto, your dog shit data should now be lovingly housed by beautiful, red borough boundaries.

Go to Tools –> Map styles to have a play with the colour and border settings.

If you can’t be bothered to make one yourself, have a look at my end result by clicking on the screenshot below (unfortunately there’s no way to embed the final product on WordPress blogs). I set up a gradient colour system so you can quickly glean a bit more information from it before interrogating the figures further.

As with all data journalism, the data is useless unless you use it to tell a story. My main aim was to learn a bit about the Google Fusion software and how to transform data from a spreadsheet into something more interesting, but I’ll follow this post up with some ideas on where I might head next if I was writing a story.

Advertisements
3 comments

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

%d bloggers like this: