Google Fusion tables – the beauty of having data visualised on a map!

2011 Census data

Displaying results   using fusion tables

400px-Ireland_trad_counties_named.svg

Dublin – 14 February 2016

Fusion tables run by Google: what are they?

Hi! Firstly I would like to say that I am not going to write another guide on how to use fusion tables but I will share few useful tips down below (this should increase my passing rate was told me! 🙂 that I discovered while using fusion tables. Well firstly what is a fusion table run by Google?

Google Fusion Tables is a cloud Software as a Service (SaaS) application that enables the hosting, management, sharing and publishing of data online.

Google Fusion Tables primarily enables the visualisation of data stored in tables in the form of graphical charts, maps, time lines and plots, with the ability to publish, share and integrate them with individual users and websites. Google Fusion works by importing data values from the tables created online or from a user spreadsheet and converting it into a meaningful graphical data representation.

Google Fusion Tables is entirely hosted on the Google cloud infrastructure, which maintains the most updated version of data across all the shared users.

…so in few words a great FREE tool to use to visualise your data using geolocation, so, visualising on maps! 

fusion table ex 2 fusion tab example

First step: prepare your data

Displaying data properly is very important but in order to achieve it the data quality behind the graph or map has to be tidy up and in order; it means data has to have a format, and each column has to contain the same type of data. In my example here, I started with a table which contained redundant data and had to be deleted, simplified and checked to make sense for the geolocation function, the counties borders and ultimately the final result.

Census 2011 CSO Central Statistics Office

A word has to spend also for the for the geolocation function which reads addresses or simply county names (in my case).

One big caveat, however: the address data needs to be in one field (or two, if you have latitude and longitude data, which I don’t in our example below).

Fusion Tables automatically begins geocoding when you visualise the location on a map but before doing that you might want to consider to add an hint to fusion tables before starting geolocating your entries: what that?

Imagine your work contains the word ‘Springfield’ and considering that there are more than 70 Springfields locations in the US alone… is now understandable why would be good to use the location hint!

Here below where to click to add the hint:

Geocode location hint

For fusion table working properly, as was said, the address data needs to be in one field (or two, if you have latitude and longitude data). Do you know how to do that? If you are not very familiar with Excel spreadsheets there is the CONCATENATE formula which groups data in one cell.

Please also note that file has to be saved in .CSV format, otherwise Google fusion tables do not read it.

So after I clean and correct the data it appeared like  this.Data table census 2011

As you might notice I have added ‘Ireland’ beside counties name – it is another alternative to the geolocation hint described above.

Display your data!

Once I had this data I the created the following heat maps which in all fairness does not tell you much, does it?! 

Irish population 2011 by county Table 1 Google Fusion TablesIt simply displays a dot for each county where if you scroll over with the mouse return the data for that county.

It is not incorrect but it does not fit the purpose either.

So, back into Tools menu, change map, change feature style, is where I changed  the fixed marker icons with Buckets….

I have assigned a scale and a legenda, which is visible on the bottom right of the image below and…. the same data returned a slightly more meaningful result.

Irish population 2011 by county TABLE 1 BUCKETS and pinsIt is now bit easier to identify, with the help of the legenda, that less amount of population is living in the areas stretching from Sligo down to Kilkenny, while the heaviest populated area is Dublin, followed by Cork and Galway.

Despite this representation is better than the one before is still basic and require a certain amount of effort in reading it.

So I went a step ahead and researched for a file (well we had a hint where to find this file – thank you Darren!) containing the boundaries for each county. (see example below).

Screen Shot 2016-02-16 at 23.10.55-55

File was uploaded in Google fusion table and the map returned was similar to my opening image, with the only difference that counties name were not displayed.

From here the map was then merged to the existing one: during this merging process is important to identify first the relationships between the new imported data  with the existing one.

In our case it had to be linked to the county names.

Next step was to repeat the process of the buckets and I porously applied the same numbers of buckets and values.

As you can see below the image is now much clearer and easier to be interpreted.

image10-40

At the first glance it is visible that the most populate county is Dublin, followed by Cork and Galway. A clear yellow path in the middle shows the counties less populated.

The curiosity corner

Considering that our table had also a column for Mens and a column for women I compared the two genders and the visual results are the following.

Including all counties in a bar chart the most visible difference is county Dublin where there is a highest difference between women and man in all Ireland. (Thumbs up folks! If you are reading this blog in DBS, you are in the right county!).

Fusion table - M vs F

In all other counties the difference is not so clear due to the amount of population that lives in Dublin and the scale of the graph minimise the differences among the less populated countries, so I took out the three most populated: Dublin, Cork and Galway and the result is the following:

The 7 least populated – with the exception of Sligo – have more men than women….

…while…

The first 7 most populated counties have more women than men.

The ‘Sunny South East’  counties (Wexford, Waterford and Wicklow) have a greater difference in gender than other Irish counties. (except Dublin, as mentioned above)

Fusion table - M vs F excluding D C G K

15 counties out of 26 have more women, with the lowest difference overall between the two gender, is in Donegal with just 91 more women.

TIPS and freebies

As I was mentioning at the beginning of this post, here a useful tip to know before loosing the plot as ‘someone’ (myself) did it!

Only if you create a new chart the option ‘Changing appearance’ will be available, otherwise if you duplicate a graph it turns grey and graph adjustments can be very limited! An example is my last graph above, where I duplicated it and applying a filter I excluded Dublin, Cork, Galway and Kildare, but I could not change the graph title!

Why? Because I duplicated an existing chart.

Only when you create a chart,’Change appearance’ will be selectable (click on Tools –> change chart –> and on the top right  click Change appearance.

Darren, did you know this one?! 🙂

See you at my next post!

  

https://www.google.com/fusiontables/data?docid=1dfKX5y15-S6q9bSmWNpeuKXhfxCyHRxy5ve3KxsG#map:id=3

https://www.google.com/fusiontables/data?docid=1LH25kiLHoYczokUg9RK1PDtqPLKiVXVP3BRuVB4S#chartnew:id=5