Pivot tables are powerful.
They are tables in Excel or Google Sheets that you can create 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 be using some example data from Our World In Data that shows 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" (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 columns 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 copy it into Flourish.