Powered by OKFN
Getting started
Make sure you’ve got copies of the sample dataset and the GRAIN dataset on landgrabbing open in your copy of LibreOffice.
A quick task
Take a look at the sample dataset about the selection of snacks on the author’s kitchen shelves. It has data about their main ingredient, quantity, and the risk they pose to the author’s health:
Start by building a pivot table using the data from the sample sheets:
- Select all the data. You can do this by selecting cell A1 and dragging the mouse to cell D9, or holding down Ctrl-A (Cmd-A on Apple Mac computers).
- With the data now selected choose Data → Pivot Table → Create from the spreadsheet’s top menu. A pop-up window will appear asking if you want to use the ‘current selection’. Choose OK.
- As illustrated below, you’ll see some grey tiles that correspond with the column headings from your raw data.
- Let’s pivot them, that is, turn a column into a row. Select and hold the tile labelled Snack and drag it into the white area called Row Field, as illustrated below:
- Click OK. A new worksheet will be created, which you’ll see in the tabs at the bottom of the spreadsheet. Below is the data it will contain:
So, what has happened to the data?
In the original data, “Biscuit” is mentioned 3 times: the Pivot table shows it only once. “Sandwich” is mentioned 2 times: the Pivot table shows it only once. And so on. The Pivot table has grouped and summarised the data in the Snack column of your raw dataset. It answers the question of what different types of snack are included in the data.
Pivot tables can be created with more than one Row Field. Using the sample dataset, let’s choose another row of data to add:
- In the pivot table you have created, there is a secondary menu. This is activated with a right click of your mouse (or a two-fingered tap on the keypad on Apple Mac computers) anywhere on the pivot table. It will look like this:
- Select Edit Layout. This will open the pivot table editor again. This time, drag Ingredient into the Row Fields area, then click on OK. The data produced by the pivot table will now look different:
What’s happened this time? In the same way as before, the pivot table has also grouped and summarised the data about ‘Ingredients’. The great thing about this is that it has grouped the data about ingredients to show them for each type of snack. We can turn this around to give another view, from the perspective of the ingredient, not the treat.
To do this, edit the pivot table layout again (right click on the pivot table), re-order the tiles that appear as Row Fields (as shown below) to place ‘Ingredient’ on top.
Select OK to re-create the pivot table with the new layout. This is how the data in it will look:
In this pivot table the groups of values are arranged in a different way. Rather than showing the ingredients that go into each snack, this shows the types of snack that contain a particular ingredient.
Got it? Let’s try it out on a larger dataset where we can see the value of a pivot table more dramatically.
A longer task
Let’s try the same technique on the larger GRAIN dataset on commercial landgrabbing, a cleaned version of which you can download from the Datahub.
Spend a bit of time familiarising yourself with this dataset. A good (but more time consuming) way of doing this is to work through the School of Data course called A Gentle Introduction to Cleaning Data, which also uses the GRAIN dataset as the basis of lessons.
If you don’t have time right now, the basics of this dataset are as below:
- the dataset has been made by GRAIN, a research and advocacy organisation which works to support biodiversity and sustainable, community-controlled food systems.
- each row of the dataset contains details about the sale of a huge amount of agricultural land in a country, often in the global south.
- the columns contain data about the names of investors and the countries where they are based, the country where the land deal has been carried out, the size of the land deal, and the amount of money invested to purchase the land, and whether the deal went ahead.
To create a pivot table in the GRAIN dataset the steps are the same:
- Select the complete dataset (from cell A1 to I417). Remember that if you don’t select data, it won’t be included in the pivot table.
- From the top menu, select Data → Pivot Table → Create.
- In the window that appears, choose “Current selection” and then click “OK”.
- Choose the layout of your pivot table by dragging the ‘tiles’ representing the different columns of data into different parts of the pivot table layout.
- When you’re happy, select ‘OK’ to create the pivot table.
- If you want to change the layout of a pivot table, right click on the pivot table to bring up a secondary menu, and select ‘Edit Layout’.
The GRAIN dataset has nine columns of data. In this lesson, we’ll just add different combinations of fields into the Row Fields part of the pivot table to answer specific questions.
We’ll walk through one of the questions to get you started: “In which countries has land been acquired?”
- The data you need to answer this is in column A, labelled ‘Landgrabbed’.
- Select the complete dataset. Go to Data → Pivot Table → Create.
- Choose ‘Current selection’ and the empty layout window will appear, as below:
- Uh oh! As you can see, there are two tiles that are labelled the same. This is because there are two columns that are very similar – ‘LandgrabbeD’ and ‘LandgrabbeR’ – and the pivot table layout unhelpfully trims the label. Hover your mouse over the tile to reveal the full name of the column of data you want to add, as below:
- Now you know which tile contains the right data, drag ‘Landgrabbed’ into the Row Fields area, and click on OK to make the pivot table:
- The data in this pivot table will be as below, a list of countries:
We can now build on this list to increase our understanding of what is in the dataset. For example, by editing the layout and dragging the tile called ‘Landgrabber’ into the Row Fields area, we can answer this question: “Which companies have acquired land in which countries?”
- Here’s how the pivot table layout should look:
- After clicking “OK”, here’s the first few rows of data that you’ll get in the pivot table:
- For extra points, try reversing the order of the tiles and creating a pivot table from that layout. It will show you the same data but arranged around the investor (the ‘Landgrabber’) rather than the country where land has been acquired. Here’s a bit of the data you’ll get from that layout:
Now you’re pretty much an expert, here are a few more questions that you can answer by adding in data to the Row Fields of a pivot table. Have a go at these:
- In which countries are investors based (their base)?
- In which countries are investors based, and where did they acquire land?
- Which investors are working in which sectors?
- Which investors are working in which sectors, and how did they use the land they purchased? Tip: data on how acquired land was used is in the column called ‘Production’.
- Which companies work in which sectors, broken down by base country?
- What are the names of investors that have made similar sized land acquisitions, and in which countries did they make those acquisitions?
- What were similar sized land acquisitions used for, and in which country, and what is the status of the deal?
Bonus features: sort and autofilter
Where you see a downwards-pointing triangle in the top row of a pivot table, click it to activate the sort and autofilter features of the spreadsheet. Click on them to bring up the interface and have a play around with it to see how it affects the data in the pivot table.