Step 4: Fusion Tables – combining our data

The Open Charities project have created a dataset of all UK Charities including, where available, details of their income and expenditure, taking the data from the Charity Commission website.

Using Google Fusion Tables we can combine data on Esme Fairbairn Foundation grants, with details of any charities those grants went to.

First we had to load our Google Spreadsheet into Fusion Tables. This is straighforward as Google provide a direct import from Spreadsheets. Fortunately for us, the Open Charities dataset was already available on Fusion Tables.

As Fusion Tables uses fairly simple matching to merge two tables, and Open Charities has charity names in upper case, we had to create an updated version of our original spreadsheet, using the =UPPER() formula in Google Spreadsheets to create a column with uppercase organisation names.

It’s then a simple process of telling Fusion Tables which values to use to create a merged table:

The resulting table had over 1000 rows in where the name of the organisation given a grant by Esme Fairbairn Foundation matched with the name of a UK Charity.

Matching by name isn’t 100% reliable. Ideally we would match details using a charity number if this was available. We could also explore using other tools like Freebase Gridworks to try and match grant recipients with Wikipedia pages about them in Freebase, as the image below shows:

We cleaned up the merged table by downloading a copy and removing all the non-matched lines and then uploading it again.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>