The same values can be stored wide or long, and the shape decides what your tools can do with them. Pivot a messy table both ways, watch a chart break and heal, then pick the variables yourself.
The three tidy rules
Rule 1
Each variable is a column
city, year, and temp_c each get exactly one column. If a variable's values are spread across headers (2021, 2022, ...), it is not a column yet.
Rule 2
Each observation is a row
One measurement event per row: one city in one year. A wide row silently packs several observations into one line.
Rule 3
Each value is a cell
One value per cell, nothing encoded in the header text and no two facts glued into one field.
City temperatures (illustrative annual means)
| city | 2021 | 2022 | 2023 | 2024 |
|---|---|---|---|---|
| Oslo | 6.9 | 7.4 | 7.1 | 7.8 |
| Madrid | 15.4 | 16.1 | 15.8 | 16.5 |
| Cairo | 22.1 | 22.6 | 22.3 | 22.9 |
3 rows, 5 columns. The variable year is not a column, it is hiding in 4 column headers.
Same 12 values either way. Pivot longer melts the 4 year columns into a year column plus a value column; pivot wider spreads them back. Flip the toggle both ways and watch each cell travel to its new home.
What a charting tool draws from this shape
wrong viewThe goal is temperature over time, one line per city. But the wide table has no year column to put on the x axis, so a naive tool does the only mechanical thing it can: one series per column (4 year lines) plotted over row positions (3 cities). The values are all correct; the shape made the right chart impossible.
Pivot longer and pivot wider are inverses
wide: 3 rows x 5 cols year hides in 4 headers 12 value cells long: 12 rows x 3 cols year is a column 12 value rows pivot_longer(wide) -> long pivot_wider(long) -> wide pivot_wider(pivot_longer(wide)) == wide round trip: intact, all cells match
Value cells (wide)
12
3 cities x 4 years
Observation rows (long)
12
one per (city, year) pair
Round trip check
intact
wide to long to wide, all 12 cells compared
Because the two pivots are exact inverses, no information is lost in either direction. That is why tidy is a working shape, not a moral one: analysis code, grouped charts, and joins want long tidy input, while a human scanning for one city's trend often reads the wide layout faster. Store and process tidy, reshape to wide at the last moment for presentation.
Your turn: which columns are variables, which are values?
Pivoting longer needs one decision from you: which columns identify the observation (keep them), and which columns are really values of a single measurement spread wide (melt them into rows). Mark each column below, pivot, and the checker will tell you, from the table you actually produced, whether every row now holds exactly one observation. Getting it wrong is instructive: try melting weight_kg once and read what happens.
| patient | weight_kg | bp_week1 | bp_week2 | bp_week3 |
|---|---|---|---|---|
| P1 | 70 | 128 | 126 | 122 |
| P2 | 84 | 141 | 138 | 135 |
| P3 | 63 | 118 | 117 | 119 |
Illustrative diary: 3 patients, systolic blood pressure (mmHg) recorded for three weeks, plus each patient's weight.
patient
weight_kg
bp_week1
bp_week2
bp_week3