How to Compare Data From Two Authorities
MHCLG Open Data offers a variety of ways to get to the data you need. This task-based guide describes how to compare data from two authorities. Other guides are available for working with data in a dataset once you've found one you're interested in.
This guide refers to the data cart. For more information, please also see: How to create a custom dataset using the data cart.
A common query is selecting all LSOAs for one local authority area and comparing them with all LSOAs in another local authority area. The data cart tool is useful here because it makes it possible to select the LSOAs in a district and download data at LSOA level for individual districts.
As an example, let’s take the idea that we want to compare two local authorities' deprivation data.
Go to the homepage, select the Data Cart option in the top menu.
This takes us to the data cart, which will be empty at this stage (if you've not used it before).
The first thing we're going to do is add the geographic areas we want. In this example, we want deprivation data for Hackney and Lewisham. Deprivation is recorded at LSOA level, so we want all the LSOAs for each area.
To do this, we need to use Atlas to get the lists of LSOAs. You can use the Atlas user guide to do this.
In Atlas search for Hackney and expand the Lower Layer Super Output Areas group.
This will list a sample of LSOAs in Hackney. From here, we can click the 'add to data cart' button, and this will add all 144 LSOAs to our cart.
We can then repeat this for Lewisham, and clicking the data cart tab at the top of the page should show that we've added 313 rows.
We can find data by searching or browsing by theme. In this example, we want to compare deprivation data for two authorities, so we go to the Search option in the toolbar at the top of the page and enter the term ‘deprivation’:
A list of datasets will appear:
Select English Indices of Deprivation 2015 - LSOA level. (This is the most recent deprivation dataset).
This takes us to a dataset, which shows you a summary of the dataset's dimensions. This is a multidimensional dataset, so we need to narrow down what we're interested in before adding them to the cart.
Select the data you want to see by clicking the links to ‘lock’ the values of dimensions to single values. In this example, we'll lock the Indices dimension to ‘Index of Multiple Deprivation (IMD)’ and lock the Measure Type to ‘rank’. Each time you click a dimension value, the page will reload, with a lock icon next to your locked dimension values.
This takes us to a spreadsheet of IMD, with the two "unlocked" dimensions as the rows and columns (here: area vs period).
Click the green button above the column of data that says 'Add 1 column to data cart' to add the data to your cart.
Click the Data Cart tab to view the cart. The cart should look like this:
Now we have all the data and the areas that we want, we can save our cart by clicking the "Save cart" button at the bottom of the cart.
Once you've saved a cart, you can download it or share the link with others (who can do the same). Note that once a cart is saved you can't edit it (so that the contents stay consistent for people you've shared it with), but you can add its contents to a new 'in-progress' cart.
To view the results, download the CSV file. This will give you a custom spreadsheet of the LSOAs you selected, with the columns you chose (2015 deprivation).
This will open in Excel or a text editor. Also, most programming languages have good support for CSV.
- Search for the areas that you are interested in using the Atlas tool
- Add all LSOAs for that area to the cart
- Repeat for the area(s) you want to compare
- Browse / Search for deprivation data
- Select English Indices of Deprivation 2015 - LSOA level
- Save cart. You can edit the cart before saving.
- After saving the cart, you get a permalink below the download button, so the cart can be bookmarked or shared via social media, email etc
- Download the cart as spreadsheet
- Open in excel
To continue exploring our datasets, return to opendatacommunities.org