Have completely failed to add a tutorial for making heat maps using Google Fusion Tables. To make this work, you have to first collect up some shapefiles – you can download them from open data sites, often as a KML, which can be directly uploaded or as shapefiles, which can be converted using Shape to FT.
The Middle Super Output Areas used below are available for download from the ONS. Alternatively download this Google Fusion Table and then upload the downloaded file to your own Google account.
To make a Fusion Table heat map, you will need a table containing your shapefiles and one containing your data.
For this example, I’m using the claimant count data, which gives an indication of the number of people claiming jobseeker’s allowance at low levels.
This can be downloaded from the NOMIS site – clicking on advanced query brings up a tool that allows you to select various Department for Work and Pensions Statistics for different geographic areas.
The options are on the left-hand side of the screen, the easiest thing to do is work down the list selecting the variables you want.
For this example, I’ve selected claimants aged 24 and under in the Middle Super Output Areas in London.
Once you are happy with your selections, you can download the data as an Excel spreadsheet.
The spreadsheet needs some cleaning before it can be used. Firstly in order for it to be uploaded to Google Fusion Tables, the headers need to be in the first row.
Also, so the data can be combined with other data sets, the codes need to be in their own separate column.
This can be done by creating a new column to the left and adding the formula: =LEFT(B2,9), copying the first nine characters (the code part) into the new column.
Copy and paste the column, using Paste Special > paste values only, in the same place to get rid of the formula.
You can also tidy up the column with the name of the area in, to remove the part with the code, by adding a new column to the left, using =REPLACE(C2,1,12,“”).
Again copying and pasting values only will allow you to get rid of the formula, so you can delete the column with the codes and areas in.
While this data gives me the number of claimants looking for work, I want the proportion of young people are claiming jobseeker’s allowance for each area.
When making heat maps it is best to compare areas on a proportional scale, such as percentage or the number per 10,000 head of population, as different areas have different populations.
The population numbers for output areas can be downloaded from the ONS.
You can copy the figures for people aged under 24 into your original spreadsheet with the figures for claimants.
In order to make sure the claimants and the populations correspond to the same areas, sort the code columns for both. You can then check that they are lined up by comparing the code columns using =A2=E2
Add the columns for the number of people aged 15 to 19 and 20 to 24 to get a total population to compare claimants.
You can then work out the percentage of young people claiming jobseeker’s allowance in each area.
Save the file as a CSV and then upload to Google Fusion Tables.
Once the file has being uploaded, you can then merge it with the table containing the shapefile data using the codes to match up areas.
The new table will have a column containing the geometric information for each area – it will show up as a column with KML in italics for each matched area.
If you click on map, you will see the areas mapped out in red, which is the default.
In order to change the colours on the map, you need to select Tools > Change map styles.
You then need to select Polygons > Fill Colour and from there you have the option of buckets, which colour different areas different colours based on the value in the column selected, or gradient, which does the same, but colours areas based on a scale rather than distinct values.
It also helps to know how big a range of values you have – so from looking at the spreadsheet and sorting the percentage column from smallest to largest, I know the values go from 0% to 16.7%.
For this one, I have picked buckets and split it into four buckets
I have picked blue and orange for the colour scheme as people who are colour blind can still read it. Colorbrewer is a useful site for picking colour schemes for your heat map.
When you click save, the map should show up in the new colour scheme pretty instantly.
As you can see it is fairly dominated by the dark blue because a lot of the values are under 5%. One way to avoid this is to split the data into quartiles (or quintiles or deciles etc) so that there is a roughly equal number of areas of each colour – or you could pick out the bottom 10% of values and the top 10% and then split the remaining ones equally.
Doing this gives a better indicator of what areas are at the extremes and which are in the middle, rather than by splitting the values equally and possibly ending up with almost all the values grouped together and a few outliers.
This version of the map has the bottom 10% in dark blue, next 40% in light blue, next 40% in light orange and top 10% in dark orange
When you click on an area in the map, an info bubble comes up giving you information about that area.
To change what the info bubble says, click Tools > Change info window layout.
There are two options, a simple one where you can tick which columns you want to include data from.
Or one where you can combine data from the table with custom HTML. With this you can click on the information on the left to add in the data from a column for that particular area.
The benefit to this is you can create info bubbles which display the information about the data in sentences, making it easier to read and understand.
For more tips and ideas on working with data, buy Get Started with Data Journalism