Data can be recorded in both a long format, typically preferred for reading by machines, and in a short format, which is more intuitively read by humans. So how can we change our data between these formats? And when is a good idea to do so?
A pivot is a transformation which converts rows to columns, making the dataset wider. This can be particularly useful for creating summary tables of long lists of records, for example if you want to convert individual sales records into totals by category (see below).
We can see in our rubber duck sales records that “Measure” and “Value” have been removed and replaced by “Sales” and “Profit”. Reducing the number of rows in our table from 4 to 2.
Wide tables are often much easier for people to read, making them a good choice for inclusion in presentations, reports and when providing raw data to stakeholders.
An unpivot is a transformation which converts columns to rows, making the dataset taller. This is often necessary when applying data analysis tools (such as Python’s pandas or R’s tidyverse). Let’s do this with a slightly more complex set of duck sales data.
We can see that “Sales”, “Profit” and “Revenue” have been converted into value within “Measure” and the values within these columns are now in the “Value” column. Now our data is more machine readable and ready for further analysis.
I hope this has helped with any pivot problems!