In this how to, we use data that we already collected from Import.io and import it into Google FusionTables in order to create a map of that information. We use an example of mapping hospitals in the UK based on what percentage of staff who work at each hospital actually would recommend getting treated there.
In our previous how to, we explained how to capture live data from the web using Import.io. In this how to we wanted to show you what we can actually do with that data.
We decided to make an interactive map with different coloured pins. Using Care Quality Commission data on hospital performance in England, we create a map of which hospitals the staff who work there actually recommend attending.
Step 1: Export data from Import.io
There are a few ways to link structured data in Import.io to other programmes, especially keeping it a live link. But, to keep things simply, we’re going to download the data instead.
To do so, in the upper right-hand corner, select download, and then click CSV. Alternatively, if you’d prefer to use it in Excel you can download it as an XLS file directly.
Step 2: Import data into Google Drive Spreadsheets
In Google Drive select the upload button from the top left of the screen, find the saved data and upload it.
We don’t want it as a CSV file though, we want to use it as a live Google Spreadsheet. So at the top of the page, select ‘Open’. This will automatically create a new Spreadsheet.
If you haven’t followed along the previous how to and would like to copy the data into your own Google Spreadsheet, you can access the data here.
Step 3: Add a column that indicates the right pin/marker
Google Fusion Tables allow a number of different pins on their maps. We want four different ones in our map — a red one for under-performing hospitals, a yellow or amber one for ok hospitals, a green one for hospitals that are performing well, and grey one for hospitals where we don’t have the information.
That means we need to introduce a column that labels the pins correctly. We can base it off of the image names provided in column x ‘rec_by_staff_image/_source’. You can see that there are three images used: one has an ‘ok’, another has a ‘tick’ and the final one has an ‘exclamation’. We’re going to create a formula that associates the correct pin with each of those. Effectively the correct associations are:
‘OK’ with ‘small_yellow’
‘tick’ with ‘small_green’
‘exclamation’ with ‘small_red’
[blank] with ‘measle_grey’
As it’s empty, I’ve entered the following formula in cell Z2:
=IF(ISNUMBER(FIND("ok", X2)), "small_yellow", IF(ISNUMBER(FIND("tick", X2)), "small_green", IF(ISNUMBER(FIND("exclamation", X2)), "small_red", "measle_grey")))
What this is effectively saying is along the lines of ‘check this cell to see if it contains the term “ok”, if so in this cell write “small_yellow”, if not check to see if it contains the word “tick”, etc’.
To fill down the entire column, simply double click the bottom-right corner of the cell.
Step 4: Create a new Fusion Table
If you haven’t already installed Fusion Tables in your Google Drive, you may need to do that first. Simply go the to Google Fusion Tables homepage while logged into your Google account and select ‘Get the Drive App’.
Back in Google Drive, instead of selecting the upload button, select ‘create’ and then Fusion Table.
Fusion Tables will then ask where to import data from. As we’ve been using Google Drive we’ll select Google Spreadsheet and then select the correct dataset.
Fusion Tables automatically detects that we have column names in our first row, so just select next on the following page.
Finally, before creating the table it will ask you to give the file an appropriate name, check whether to allow download of the data, and to credit the initial data source, in this case the NHS. Select ‘Finish’ and the new Fusion Table will appear.
Step 5: Geocode data based on postcode
Two of the columns are already highlighted in yellow by Fusion Tables: hospital name and hospital post code. That is because these are the columns Google can use to geo-reference the data.
The easiest way to do this is to flip to the map tab that has automatically been generated. It will start geocoding data based on hospital name. But the postcode in England gives an exact spot, so it’s much better to pause this first attempt at geocoding by name (which might get a number of ambiguous places) and then select hospital_postcode in the ‘Location’ drop-down menu on the left.
It may take some time to georeference all 502 hospitals, but when it’s done a map of England should appear with a number of red markers where each of the hospitals is.
Step 6: Change the colour of the pins to indicate whether staff recommend individual hospitals
There are two potential ways to change the colour of the pins. The first is by creating ‘buckets’ based on the percent of staff indicating that they recommend the hospital. Let’s try that first.
Select the ‘Change feature style’ button on the left side of the screen. Then under ‘marker icon’ select buckets.
We’ll create three buckets based on the column ‘rec_by_staff’. If less that 50% of the staff would recommend a hospital, we’ll give it a red marker. If it’s between 50% – 75% of staff that recommend a hospital, we’ll give it a yellow marker, and if it’s above 75% we’ll give it a green marker. Select save and the map with different colours should now appear.
However, the methodology used by the NHS doesn’t quite give us so defined break points (probably because of sample sizes at various hospitals). Also, this erroneously takes hospitals where we have no data and lumps them in with under-performing hospitals.
So it’s better to use the images that they’ve used as a guide on how well a hospital is doing.
Since we’ve already created a column that gives the right icon name based on those images, we’ll change the style again but instead of buckets, we’ll use ‘Column’ and select the radio button ‘Use icon specified in a column’ and select the column ‘rec_by_staff_image/_alt’ from the dropdown menu. It results in a slightly different map, as below.
tep 7: Edit the information box
The map is a nice way to get an overview of how the various hospitals are performing. But if we want more information on how individual hospitals are doing, we can click on the button and an information window appears. Unfortunately, at the moment it’s a bunch of gobbledygook (technical term). So let’s clean it up!
Select the ‘Change info window’ button on the left of the screen. Start by selecting some of the relevant variables to display. In this case we want: Hospital website (which contains its name), the hospital address, the hospital phone number, the percent of staff that recommend the hospital, and the image of how well it’s doing.
That looks alright, but it’s still a bit clunky. Let’s flip over to the custom tab and customise the look a bit.
I’ve ended up with the following code, which you can cut and paste directly.
<div class='googft-info-window' style='height: 15em; overflow-y: auto'> <h3>{hospital_website}</h3> <div style="width:60px; float:left; margin:5px;"><img src='{rec_by_staff_image}' height='60' style='vertical-align: middle'/></div><div style="float:right"><b>Hospital address:</b> {hospital_address}<br> <b>Hospital telephone:</b> {hospital_telephone}<br> <b>Percent of staff who recommend hospital:</b> {rec_by_staff}%<br></div> </div>
Step 8: Publish the map!
One of the great things about working with Google Fusion Tables is that you can publish directly to the web.
First you’ll need to edit the privacy of the entire workbook. Go to File > Share and a new window should pop up. Under the privacy settings, select the ‘Change’ link and select the radio button: ‘Anyone with link’. Save changes.
In the map menu, now select ‘Publish’.
The first thing to do is to specify a correct size. It is then possible to share the link, or to embed the visualisation in an iFrame (as below) or using javascript!