How to Compare Data From Two Authorities
DCLG’s open linked data site, OpenDataCommunities, offers a variety of ways to get to the data you need. This task-based guide describes how to compare data from two. 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 what 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 OpenDataCommunities homepage, select the Tools option in the toolbar and ‘Your data cart’ from the drop down menu.
This takes us to the data cart, which will 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.
Let's go to ‘Explore’ on the toolbar, then choose ‘by area’:
Here you'll see a diagram representing the areas that data is held for on the site. We want data for Hackney and Lewisham so select ‘London Boroughs’:
This takes us to an expandable list of London boroughs:
Now we can go to the LSOAs. When we select Hackney (one of our boroughs of choice), we are taken to a map of that area plus a list of the different areas within it:
Here we can add all the LSOAs for Hackney to the cart. This will mean that when we search for data, only data for these LSOAs will be added. Click on the 'add 144 to cart' button, next to the "2011 Lower Layer Super Output Areas" title.
Repeat the above to add Lewisham LSOAs to the cart. Explore > By area > London Boroughs > Lewisham. Scroll down to view all areas within Lewisham and add all 2011 Lower Layer Super Output Areas to the cart:
Before we add the data we want, we can check we’ve added the correct areas to the cart by going to the toolbar at the top of the page and selecting ‘Your data cart’.
Or you can alternatively use the link near the footer, which shows you a summary of the current contents of your cart:
When we get to our cart, you should see the geographies you added:
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 what data you want to see by ‘locking’ the values of dimensions to single values, by clicking the links. 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).
Hover over the column header (in this case 2015). A menu will appear:
Select ‘Add column to cart’.
To check on your cart, head to the Tools option on the toolbar at the top of the page and select ‘Your data cart’. (Or the 'your data cart' link in the footer).
Now your 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:
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.
- Browse ‘By area’ to get to a diagram of areas
- Select London Boroughs (or non metropolitan districts / metropolitan districts / English unitary authorities)
- Select the borough, district or authority of interest
- 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