All lessons

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.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

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.

Tidy means one row per observation, one column per variable, one table per kind of thing. Store and analyse tidy; make wide tables only for people to read.

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:

  1. Each variable is a column (store, month, sales).
  2. Each observation is a row (one store in one month).
  3. 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

Pythonorders.csv

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:

Messy: quarters in the header.
storeQ1Q2
S0112090
S0280110
Name the grain. One row per store per quarter.
Tidy. df.melt(id_vars='store', var_name='quarter', value_name='budget')
storequarterbudget
S01Q1120
S02Q180
S01Q290
S02Q2110
Now one filter does it. tidy.query('budget >= 100') → 2 rows.
For the report. pivot_table turns it wide again for people.

4. Your turn

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

  1. A survey sheet has one column per question. Tidy or not? How do you fix it?
  2. Why pivot only at the end?
  3. Name the grain of customers.csv.
Answers
  1. Not tidy (question is a variable hidden in headers); melt to respondent, question, answer.
  2. Wide tables are awkward to filter, group or join; keep the flexible shape until the final report.
  3. 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

1Which table is tidy?
2When is a wide table the right choice?
3What is the grain of a table?

Sources

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.