Note how we first need to select the data we want to include in the pivot table. The following GIF shows how to create pivot tables in Google Sheets. So here’s where we finally meet the pivot table: our tool of choice to quickly get from long to wide.Īlmost every spreadsheet software like Excel, LibreOffice or Google Sheets has the option to create pivot tables. value: cigarettes sold to adults each day.variable 2: years, as columns in the data.variable 1: countries, as rows in the data.In our line chart up there, the structure is the following: For a line chart, that’s a reasonable idea: It’s only able to show two variables anyway. We want to bring the data in the wide format with just two variables. Now that we know why long formats are neat, we want to destroy them. From long format to wide format: pivot tables Imagine forcing that into a wide format: □. We have four variables here (country, year, gender, age group). In the 3rd and the 4th row, the age group is different and between the 5th and the 6th row, it’s the country. The difference between the 1st and 2nd row is just in the gender. “That looks pretty wide”, you say? Yes, but this table still has only one value per row. Again, that’s how you recognize a long format: There is only one value per row. And look at this beauty. The long format is our hero for situations like this: But imagine we want to show the number of sold cigarettes for each country in each year for each gender in each age group. But there’s a reason why you will find so much data in suuuuper long tables: Long format tables allow you to add as many variables as you want. What does that mean? Well, the wide format allows exactly two variables (some call it dimension, too): Country (rows) and years (columns). They didn’t make it easier for me to create data visualisations in tools like Adobe Illustrator or Datawrapper, which all require the wide format. In the tables on the right are always multiple values in a row, which makes them pretty wide (especially if you have many years or countries): This table format is called the wide format, or unstacked data. This table format is called the long format, or narrow format, or tall format, or stacked data, or tidy data. All three tables show exactly the same information: What many data sources give us:įun fact: The table on the left is pretty long. Let’s add some colors, to see the difference in these tables better (The two tables on the right are the same, just with switched columns and rows.). Ugh, that’s not ideal, is it? What we actually need to create a line chart in Datawrapper (and many other charting tools) is this table format: Let's begin: The difference between long and wide formatĮvery time we download something from the We will use pivot tables to create the following chart out of data that I found at Our World in Data: And that’s what I’ll explain in this article. For example, if your original table has the salary of each person in each country, you could use a pivot table to calculate the average salary in each country (the country is your group).īesides doing all the summarising, pivot tables are excellent to get your data from the long format into a wide format. You can calculate averages, counts, max/min values or sums for numbers in a group. Pivot tables are extra tables in your Excel/Google Sheets/LibreOffice etc., in which you can summarize data from your original table. This article was first published on the Datawrapper blog. How to get data in the right format with pivot tables
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |