Pivot tables are powerful.
They are tables in Excel or Google Sheets that you can create and use to summarize and rearrange your data.
This tutorial will walk you through creating wide data from long data, the data format that is required in templates like our Bar chart race or our Line chart race.
- 1
First, let's have a look at our data. In this case, we're going to use example data from Our World In Data showing CO2 emissions, in metric tons per capita, for each country. It looks something like this:
Entity Code Year Per capita CO2 emissions (tonnes per capita) Afghanistan AFG 1800 0 Afghanistan AFG 1801 0 Afghanistan AFG 1802 0 Afghanistan AFG 1803 0 Afghanistan AFG 1804 0 Afghanistan AFG 1805 0 Afghanistan AFG 1806 0 Afghanistan AFG 1807 0 Afghanistan AFG 1808 0 Afghanistan AFG 1809 0 Afghanistan AFG 1810 0 You might notice that the original data (above) is in a long format, with each row being just one observation. This is unfortunate, because, to make a bar or line chart race using the data, we need it to be in a wide format, such as the format below.
Country 1800 1801 1802 1803 1804 Armenia 0 0 0 0 0 Australia 0 0 0 0 0 Austria 0 0 0 0 0 Azerbaijan 0 0 0 0 0 Bahamas 0 0 0 0 0 - 2
To transform your long data into wide data, you can use pivot tables! While we're going to use Excel to manipulate our data, the process is similar in Google Sheets and other spreadsheet programs.
-
To create a pivot table in Excel, select all the relevant data and go to Insert > Pivot Table.
- 3
-
Your pivot table will now be created in a new tab. Drag and drop the names of your columns into the fields panel on the right to manipulate, transform and compare different parts of your original dataset.
The pivot table field Year, for example, can be set to be a column by dragging it to that Columns box.
-
It always helps to think back to our dataset and decide what we want our data to look like, and then try to build a new data structure with that in mind.
-
This is what the PivotTable Fields panel looked like after we transformed the example data:
- 4
Once you have brought your data into the right format, you can add it to a Flourish template.