A site about how community groups and charities can make the most of data and open data to do something useful. Focused on Birmingham, relevant everywhere.

Data Visualisation Tools for Community Groups


Data Visualisation

Whether you are collecting your own information or downloading open data sets to use,   often what you end up with is a spreadsheet full of numbers.  Unless you know what you are looking for these can be hard to decipher.

This is where data visualisation tools come in.  You can use them can to turn those numbers in your spreadsheet into maps, graphs and charts It will help you display your work and highlight findings – literally visualise what the numbers mean.

A quick Google shows that there are lots of tools out there to do this but knowing where to start is daunting especially when you first start out. I know, as I’m just starting out myself and some of it looks like a foreign language.

I’m going to try and share some of the available tools as I discover them and look what they work best for on this blog. Starting today with the most obvious for me  –  Google’s data tools.

If you already have a Google email address you already have access to everything I’m going talk about below and for free.  They are included as part of the Google tools, either as a default option in google drive, or via an extension.

Google Speadsheets Charts

Google spreadsheets is very much like excel where you can create charts from columns and rows of data, You can  create bar charts, pie chart, scatter charts, line graphs and more. simply by highlighting the data you want to use and clicking on the “Create Chart” button and then running through the options.

All charts are customisable but can also be downloaded as images so you can use them outside of the spreadsheet too – to embed in a blog post or put in a report etc.

Google Spreadsheet Charts


Google Charts

Google Charts is a developers app for displaying visualations in a website. I’m not going to pretend I understand everything in the website as I’m no coder, but even I can tell it’s a great tool for displaying interactive data visualisation on your website…if you’re that way inclined, as well as creating images like those available in Spreadsheeds.

I’ve been looking around and it appears this a good place to the look at using charts in your website if you understand the code.

Google Fusion Tables

Google Fusion Tables is an experimental app from Google, It’s not included in the drive as standard so you will have to install the app from the chrome web store. I know I have barely scratched the surface of what fusion tables can do but I’ve played and it’s a brilliant tool for mapping data.

It seemed really tricky at first and there is still plenty I need to get my head around, but at it’s simplest  if you have a spreadsheet that contains location data you can use fusion tables to put that data onto a Google Map. This map will then also have all the capabilities of zooming etc. you’d expect from Google and you can embed the maps into your website / blog posts.

In addition to a single map you can also layer maps to compare data – I’ve yet to figure this out how to do this in tables, but this afternoon I came across a tool someone has created to make it a copy and paste job. This tool also makes embedding the layered fusion table maps easy, (and single maps too ) you can just take the generated HTML and copy and paste onto your site/blog.

If using you’re using wordpress you need to do this in HTML view and then NOT return to visual view before publishing as WordPress strips out the code – If I can I’ll write a follow up post on how to do this directly from tables as it also does something funky to the formatting that I’ve got to try and figure out. The map above is from the layers tool mentioned above.

But if you’re still not sure about embedding from fusion tables or using a tool as it seems complicated you could still use the maps for yourself or once you’ve got the map on screen you could use a programme such as Skitch to capture images of your screen to share easier.

Creating maps from spreadsheets using Google Fusion Tables (Part 2)


Gateway Status Map

Gateway Family Services are a community Interest Company in Birmingham that work with people that need help from communities across Birmingham, Communities that fall in areas of deprivation. However as well as providing support to people in need in these areas they also actively employ staff and volunteers from them too.

But how can they demonstrate this?

Gateway wanted to be able to map 2 types of data, Where their employees come from and if they fall in an area of deprivation and then also what the employment status of those people were, whether they were Staff, Volunteer, Interpreter of Apprentice.

We worked with them to create these maps using Google Fusion Tables and this is how we did it:

We blogged previously about mapping the deprivation areas,  Now we’re looking at the employment status.

Because we had already created a table with their data we didn’t need to do that again and we just needed to look at the different feature styles to work out what to do next.

Google fusion tables lets you map your data using different filters. One of which is by column…

We had a status column in the table, but we couldn’t just select it as there was no way for the map to differentiate between them, so we had to tell the table what to do before it would appear on the map.

To do this we had to create a separate table to and merge it with this one we were currently working on.

We created a new spreadsheet that looked like this:



Column one giving the Category, Column 2 title “Icon Name” giving the descriptor of the marker as we wanted it to appear on the map. (You can find the descriptions by looking at the options on the Map Features Style on your existing table).

Fusion Table Marker Style


Using the steps in the previous post we created a new table just for this data.

Then going back to the original data table we had to merge these two tables together.

To do this go File > Merge > and select the table you’ve created with your icon colours in…


Merging Fusion Tables

Now you need to tell it what data it needs to merge… So in this instance the option on the left was the status column as this what I wanted to map by and the right was the category column as this was where I’d put the different status on the second spreadsheet..

Merging data in Fusion Tables
and then click next….

This will actually create an entirely new table to work from, which I hadn’t appreciated at first, so the previous deprivation map I had created wasn’t imported with it.  This was easy enough to recreate as I was using the same data – but if you are planning on creating lots of maps in different tabs from the same table, it is worth noting that it would be better to know what you’re going to do and merge your tables before you start.

Now I’d got my tables merged and recreated the deprivation map I could get on with creating the status map

Click the + symbol and select Add map,  once again select the Post Code for the location option.

Create New Map Fusion Tables

Now we can use the merged data to get the map to show the markers by status.

Click on Change feature styles and select the column tab.

Here you want to check the “Use icon specified in column” option and select Icon Name from the drop down options and Save.

Column Options Fusion Tables


This will create a map showing the points in the marker colours we chose when creating the second spreadsheet – so in this instance:

Red – Employee
Green – Volunteer
Blue – Interpreter
Yellow – Apprentice:

Gateway Status Map


And just like with the deprivation map you can click on markers to expand an information box to give more context to the point you are looking at

Status Cards Fusion Tables


and rename the map tab so it was easy to see what data we were seeing displayed on the maps.

Renamed Map

Both of these maps will now be used with their board to give proof to their claims that not only do they work with people in areas of need – they also actively employ from those areas too. 

Created under the Open Government License

Created under the Open Government License


Marcus Belben giving feedback and the Kings Heath and Moseley Open Data Surgery


How easy is it to use open data – Surprisingly so, as Marcus Belben tells us after the Open Data Surgery in Kings Heath last night.

Created under the Open Government License

Created under the Open Government License