As Google says, “Fusion Tables is an experimental data visualization web application to gather, visualize, and share larger data tables.” Although it has several uses, I think the most important one is the option to create an interactive map with the data. And I think is a great tool to do this, as it is very easy and intuitive to use – when you don’t get stuck in something and desperately try to fix it without succeeding, but let’s not get ahead of ourselves-, and you can create very nice maps.
Next 12 of March is the No Smoking Day so I decided to create a map showing some of the smoking habits in the UK. I chose to map the percentage of smokers by local authority in the UK, data released by the British Heart Foundation as part of the campaign for this day.
Starting with Fusion Tables
In order to upload the data to Fusion table, I needed to clean it. In this case it was easy, as the BHF provided a simple table with the percentages. But just saving that table as a CSV document and uploading wasn’t going to work. Because of the way it was build, the data didn’t show in the right columns.
I had to clean the document and just leave the data itself, getting rid of all the extra content. Doing that was as simple as copying the rows that I wanted and pasting them in a new spreadsheet, using the “Past Special” option to do it without the initial format. After doing this, I was ready to save it as CSV and upload it to Fusion Tables. Or at least, that was what I thought, because the data wasn’t showing correctly either.
So I decided to go for another option: create a Google Spreadsheet and paste the data in there. Luckily, this worked and I had my data uploaded to Fusion Tables looking good, because I was starting to run out of options.
The big problem: geocoding
But the problems didn’t end there. They just started. The next step was to geocode the locations to place the different local authorities in the map. The problem was that just with the name, Google didn’t know if some of the places where in the UK or elsewhere, so I found myself with tons of local authorities located in places as US, Africa or Australia. I decided to join the Local Authorities and the Area/Region columns to give it more information about the location.
To do this, I tried the concatenate formula (=CONCATENATE(A1,” “,B1)) but it gave me an analysis error that, even if I look everywhere for how to fix it, I couldn’t find the solution. So I went for the easy option and used the operator & (=A1 & “, ” & B1), resulting in a nice one column location with both the local authority and the region where is located. I just had to open it again in Fusion Tables, choose this new column as location and leave the others as text.
To help it be more accurate when geocoding, I gave it the location hint “United Kingdom”. After the geocoding wait… almost done! This time, just a couple of local authorities where incorrectly placed in the United States, so I decided to give them the right location manually.
Creating the map
It was time to choose the right map. The option with marker points didn’t seem the right one, because you had to look each authority separately to see the percentage of smokers, so it made it hard to compare the differences across the UK. I tried with a heat map but it didn’t convinced me either, so I went for the gradient polygons map.
In order to be able to do this map, I needed the boundaries of the local authorities. To do that, I found very useful Simon Rogers’ post called Borders and boundaries: 16 Google Fusion border files for you to use as I was able to take them from there. So the only thing I had to do was to merge both tables.
I faced another problem when doing this. My initial data featured both the English and the Welsh name in the local Authorities in Wales so those places were left without any data because the names didn’t match. It also found a problem with places with the “and” in it, as one used the word and the other one “&”.
The only way I knew how to fix this, was checking the names that didn’t match and changing them manually. So that’s what I did. After all the problems faced, I had a nice maps showing exactly what I wanted. There was just one last thing to do: creating the legend. Luckily, Fusion Table has automated it in the last few years, so is as easy as going to “Change feature style” > “Automatic legend” and choosing the title and the legend position in the map.