CMCE10002 Chap.8 Tidy Data and Reshaping with Pivots
Tidy Data and Reshaping with Pivots
Tidy is a contract, not tidiness
A dataset is tidy when each column is a variable, each row is an observation and each cell is one value.
The week 5 lecture presents this as a technical contract that the whole toolkit assumes, not as a matter of neatness.
The payoff is that code survives growth
Every tidy dataset has the same structure, so adding more firms or more years adds rows rather than columns and the code does not change.
A table storing each year in its own column has the opposite property: every new year is a new column and every piece of code naming columns has to be rewritten.
Two directions, one decision rule
Lengthen when a variable is trapped in a column heading, so years or quarters become values the code can group and filter on.
Widen when a variable name is stored in a cell, so measures that were stacked each gain a column of their own. Anything you intend to map, group or filter on has to exist as values inside a column.
Why reshaping is usually the first real step
Reporting systems are built to be read by people, and a table carrying one column per period is easy to read.
That same property makes it hard to compute with, so nearly every extract arriving from a finance or operations system needs lengthening before anything else happens. Lengthening also produces two new columns from the ones it consumes, one holding the former headings and one holding the former values, and naming both for what they actually are is what keeps the result readable.
What this chapter covers
- 01
The three tidy rules and what each one rules out
- 02
Why tidy data makes code survive the arrival of more data
- 03
Lengthening a table when a variable sits in the column headings
- 04
Widening a table when a variable name is stored as data
- 05
Predicting the row count of a reshaped table before running it
Deciding which direction to pivot
- 1Decide the reshape needed to plot revenue over time by store.
- 1Decide the reshape needed to compare all three metrics on one chart.
- 1State the rule both answers share.
Key terms
- Tidy data
- A table in which each column is a variable, each row is an observation and each cell holds one value.
- Long format
- A shape with one row per unit and period, where the period is a value in a column.
- Wide format
- A shape with one row per unit and one column per period, where the period is a column name.
- Lengthening
- Moving a variable out of the column headings so it becomes a column of values.
- Widening
- Giving each measure stored in a cell its own column, so one unit occupies one row.
Tidy Data and Reshaping with Pivots FAQ
What makes a dataset tidy?
Three conditions together: each column is a variable, each row is an observation and each cell holds one value. The definition is a technical contract rather than a description of neatness, and the tools in this subject assume it has been honoured.
How many rows does a lengthened table have?
The original row count multiplied by the number of columns being gathered. Three firms held across four quarters in the wide shape produce twelve rows once lengthened, and predicting that number before running the step is the cheapest check available.
Can a table be tidy for one question and awkward for another?
Yes. The test is always whether the thing you need to group by, filter on or map to an axis is available as a column of values. If it exists only as a column name, the table needs reshaping before that particular question can be asked.
Does reshaping change what the data says?
No. The cell values before and after a pivot are identical; what changes is their address, and therefore what the code is able to ask. That is why a reshape is never a substantive analytical decision on its own, and why getting the direction wrong costs nothing except a second pivot.
Exam move
Before any pivot, write down what one row of the result will be and how many rows there will be. A reshaped table with the predicted row count is almost certainly correct, and one without it has usually gathered a column you did not intend.
Working through Tidy Data and Reshaping with Pivots in CMCE10002? Sia is AskSia’s AI Statistics tutor — ask any CMCE10002 Tidy Data and Reshaping with Pivots question and get a clear, step-by-step explanation grounded in how CMCE10002 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
