Our Data School project this week was for one of our clients, Surrey County Council. They conduct a Resident’s Survey every quarter and wanted us to improve on their current visualisations, which are split into two categories as there are two different audiences: council members and the residents themselves.
One of the goals of the project was to display particular results from the survey on a map. Seeing results on a map allows for quick and easy comparison and trends can be spotted, for example a cluster of postcodes being unhappy with traffic. Below is an image of the map I created in Tableau as part of the Data School application process using house sales data to March 2015. You can quickly see that from January to March 2015, the most expensive newly built flats sold were in the City of Westminster – as represented by the darkest shade of red (I did include a bar chart below to rank all the districts too).
For the project, SCC were interested in the following geographic maps:
- Local Authority District (LADs)
- Electoral Wards
- Clinical Commissioning Groups (CCGs)
- Layer Super Output Areas (LSOAs)
In order to use any type of map in Tableau, your dataset contain a column for the geography you wish to map. In the survey dataset, there was a column for LSOA therefore each record (which refers to each survey response in this case) contained this data. Tableau has several built-in Geographic Roles with predefined longitudes and latitude coordinates – click here to see the full list, learn basic concepts of map views and to learn how you can create maps in Tableau.
LSOAs, CCGs and Electoral Wards cannot be mapped in Tableau out of the box, however it is possible to create custom maps for Tableau using Alteryx. Fortunately Craig Bloodworth has already created an add-on for Tableau which can be used to create maps for LSOAs, CCGs and many more geographies – click here for the full list of Geographic Roles and to see a video tutorial of how to install this add-on. The add-on doesn’t contain Electoral Wards but Craig told us, though possible, it’s not the easiest thing to do so we gave this a miss given our time constraint.
Since the survey dataset didn’t contain LAD or CCG data we needed to find datasets which map LSOAs to these corresponding geographies. SCC kindly provided us a dataset which maps LSOAs to LADs therefore we just needed to find a dataset which maps LSOAs to CCGs and combine these two datasets with the survey dataset. A simple google search allowed us to find a dataset on an NHS website which maps LSOAs to CCGs. We then used Alteryx to combine all this data together.
When we created a map of the LSOAs in Surrey, we noticed some gaps on the map. We found that Tableau didn’t recognise 3 of the LSOAs. After conducting some research we discovered that the codes for these LSOAs had changed over the years. We therefore went back to the Alteryx workflow and used a formula tool to rename these 3 LSOAs.
Back to Tableau, we were now able to create maps of LSOAs, LADs and CCGs using the survey data to represent the colour of these geographies.
Below is a map of the overall satisfaction score based on all questions in the survey by LSOAs, with dark blue representing ‘very satisfied’ and dark red representing ‘very dissatisfied.’ Using the parameter control located on the top right hand side of the image you can also view the overall satisfaction score by LADs and CCGs. A filter could also be used to show the score for individual or groups of questions in the survey.
