Introduction
The same data can be laid out in many ways. A sales report often has one row per shop and one column per month, which is easy to read. But try to answer "what were total sales per month?" or "which shop grew fastest?" and you end up writing special code for each column. Tidy data is a layout that makes such questions uniform, and most data tools, from pandas to charting libraries, expect it.
How it works
A table is tidy when:
- each variable is a column,
- each observation is a row,
- each value is a single cell.
In the sales report, the month is really a variable, but it is spread across column names: that is the wide format. The tidy, long format has three columns, shop, month and sales, and one row per shop and month. With it, totals per month, per shop or per anything else are the same simple grouping loop.
Turning wide into long is called melting: for each row and each measurement column, emit one new row holding the identifier, the column's name and the value. Turning long into wide is pivoting: collect the rows by identifier and turn one column's values into column names. Pivoting needs a rule for gaps (a shop with no sales recorded in March) and for duplicates (two values for the same shop and month), so state both. People often want the wide form for reports, and analyses want the long form, so both directions are common.
Two other untidy patterns are frequent:
- Two variables in one column, such as "Dhaka-2021" holding a city and a year. Split it into two columns. Choose the split carefully: splitting at the last hyphen keeps a city like "Cox's-Bazar" whole.
- Several values in one cell, such as tags "food;travel". Split them and give each its own row, repeating the other fields. That makes counting tags a simple loop.
In pandas these are df.melt(...), df.pivot(...) or pivot_table, df["col"].str.split(...) and df.explode(...).
Example
# Tidy data: one row per observation, one column per variable.
wide = [
{"shop": "North", "jan": 120, "feb": 135, "mar": 150},
{"shop": "South", "jan": 90, "feb": 80, "mar": 110},
]
months = ["jan", "feb", "mar"]
# Melt: turn the month columns into rows of (shop, month, sales).
long = []
for row in wide:
for month in months:
long.append({"shop": row["shop"], "month": month, "sales": row[month]})
for row in long:
print(row)
# A tidy table makes questions easy: total sales per month.
totals = {}
for row in long:
totals[row["month"]] = totals.get(row["month"], 0) + row["sales"]
print(totals)
# Pivot: back to one row per shop.
back = {}
for row in long:
back.setdefault(row["shop"], {"shop": row["shop"]})[row["month"]] = row["sales"]
print(list(back.values()) == wide)Line by line
widehas one row per shop and one column per month.- The nested loop melts it: one row for every shop and month, six in all.
- With the long table, monthly totals are a single loop that adds sales by month.
- Pivoting groups the rows by shop again and rebuilds the original table exactly.
Expected result
{'shop': 'North', 'month': 'jan', 'sales': 120}
{'shop': 'North', 'month': 'feb', 'sales': 135}
{'shop': 'North', 'month': 'mar', 'sales': 150}
{'shop': 'South', 'month': 'jan', 'sales': 90}
{'shop': 'South', 'month': 'feb', 'sales': 80}
{'shop': 'South', 'month': 'mar', 'sales': 110}
{'jan': 210, 'feb': 215, 'mar': 260}
True
In the real world
Spreadsheets shared between people are usually wide, with years or months across the top; data pipelines melt them on arrival. Survey exports often hold multiple-choice answers as lists in one cell. Charting libraries such as ggplot and Seaborn take long data directly: one column for the x-axis, one for the values and one for the colour.
Common mistakes
- Writing a separate calculation for each month column instead of melting first.
- Pivoting without deciding what happens to missing combinations or duplicates.
- Splitting "city-year" at the first hyphen, which breaks names that contain hyphens.
- Keeping lists in cells, so counting their items needs special code everywhere.
Best practices
- Store and analyse data in tidy long form; pivot to wide only for reports.
- Name the identifier columns explicitly when melting.
- Document the rule for gaps and duplicates in every pivot.
Summary
Tidy data has one variable per column, one observation per row and one value per cell. Melt wide tables into long ones to analyse them, pivot long tables into wide ones for people to read, and split columns that combine variables or hold lists of values.
Quick check
Key takeaways
- Tidy tables have one variable per column, one observation per row and one value per cell.
- Melting and pivoting move between the long form analysis needs and the wide form people read.
- Split columns that hold two variables, or lists of values, into proper columns and rows.
Practice problems for this lesson
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.