Module 6: Reshaping · Lesson 6.5 · about 15 min
Tidy data: which shape for which job
The tidy data rules (one row per observation, one column per variable, one table per kind of thing), why long shapes are best for analysis and wide shapes for reading, and how to choose the right grain for a report.
Module 6 · about 15 minutes · uses all of Module 6. You’ll be able to say what shape a table should be for the job, and get it there.
In everyday terms: a filing cabinet: one card per sale, one box on the card per fact. A summary sheet for the manager is written from the cards, never instead of them.
1. Three rules
A tidy table follows three rules:
- Each variable is a column (store, month, sales).
- Each observation is a row (one store in one month).
- Each kind of thing is its own table (stores, products, orders), linked by keys.
orders.csv, products.csv and stores.csv are tidy. A budget sheet with a column per month is not: “month” is hiding in the column names.
2. Long for code, wide for eyes
Analyse in the long shape (filter, group, join, model), then pivot to wide at the very end for the table people read.
3. Choose the grain on purpose
| Question | Grain you need |
|---|---|
| Which products sell together? | order line |
| Average order value | order (sum lines first) |
| Customer lifetime value | customer |
| Daily sales trend | store × day |
Moving to a coarser grain is a groupby; joining tables of different grains needs care (Lesson 5.3).
- Observation
- One thing measured: a sale line, a survey answer, a daily reading.
- Variable
- One kind of fact: store, date, qty. A month name in a header is a variable hiding as a column name.
- Grain
- What one row means. Say it out loud: "one row per order line".
- Long for code, wide for eyes
- melt to analyse; pivot_table to present.
- Physical meaning
- In a tidy table every number has exactly one place, so sums, filters and joins cannot double-count or miss values. Messy shapes force every query to know the layout.
“Tidy means sorted or clean.” A tidy table can still have typos and gaps. Tidy is about shape: where each fact lives. Cleaning (Module 3) and tidying are separate jobs.
Watch it happen, one step at a time:
| store | Q1 | Q2 |
|---|---|---|
| S01 | 120 | 90 |
| S02 | 80 | 110 |
df.melt(id_vars='store', var_name='quarter', value_name='budget')| store | quarter | budget |
|---|---|---|
| S01 | Q1 | 120 |
| S02 | Q1 | 80 |
| S01 | Q2 | 90 |
| S02 | Q2 | 110 |
tidy.query('budget >= 100') → 2 rows.pivot_table turns it wide again for people.4. Your turn
How it works
Principle: tools (groupby, merge, charts, models) expect variables in columns. Tidy data makes every question a filter or a group on a column. The idea comes from statistician Hadley Wickham’s paper Tidy Data (2014), and it’s the same as database normal form: one fact, one place.
Power BI and Tableau also want long, tidy tables (a fact table plus lookup tables): model the data that way and the visuals do the pivoting.
5. Practice
- A survey sheet has one column per question. Tidy or not? How do you fix it?
- Why pivot only at the end?
- Name the grain of
customers.csv.
Answers
- Not tidy (question is a variable hidden in headers); melt to respondent, question, answer.
- Wide tables are awkward to filter, group or join; keep the flexible shape until the final report.
- One row per customer.
New words: tidy data, observation, variable, grain, fact table, lookup table.
- Tidy: one row per observation, one column per variable.
- Values hiding in headers (months, quarters) are a sign to melt.
- Say the grain of every table out loud.
- Analyse long, present wide.
Check yourself
Sources
- Wickham, H. (2014), Tidy Data, Journal of Statistical Software 59(10) https://doi.org/10.18637/jss.v059.i10
- pandas documentation, User Guide: Reshaping and pivot tables https://pandas.pydata.org/docs/user_guide/reshaping.html
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.