Introduction
The same data can be laid out in two ways. In long format each row is one observation: Ada, maths, 90. In wide format each row is one entity and each column one measurement: Ada, with a maths column and an art column. Long data is easiest to filter, group and store; wide data is easiest to read and to compare across. Analysts switch between them constantly, and pandas has a method for each direction.
How it works
pivot_table spreads long rows into a grid: one row per index value, one column per columns value, with values in the cells:
scores = pd.DataFrame({
"name": ["Ada", "Ada", "Rahim", "Rahim", "Mei", "Rahim"],
"subject": ["maths", "art", "maths", "art", "maths", "art"],
"score": [90, 70, 80, 84, 95, 86],
})
grid = scores.pivot_table(index="name", columns="subject", values="score")
grid.loc["Rahim", "art"] # 85.0: the mean of 84 and 86
grid.loc["Mei", "art"] # nan: Mei has no art score
Rahim has two art scores, so they fall in the same cell. pivot_table aggregates them, with aggfunc="mean" by default; aggfunc="sum", "count", "max" and others are available. A cell with no data is NaN, and fill_value=0 fills it. margins=True adds an "All" row and column with the totals of the aggregation. The simpler pivot does no aggregation, and raises an error when two rows fall in the same cell.
melt goes the other way. It keeps the id_vars columns and turns every other column into rows of (variable, value):
wide = pd.DataFrame({"name": ["Ada", "Mei"], "maths": [90, 95], "art": [70, 88]})
long = wide.melt(id_vars=["name"], var_name="subject", value_name="score")
long["subject"].tolist() # ['maths', 'maths', 'art', 'art']
long["score"].tolist() # [90, 95, 70, 88]
The new rows come column by column: all the maths rows, then all the art rows. Without var_name and value_name, the new columns are called variable and value.
pd.crosstab(df["city"], df["product"]) counts how many rows have each combination of the two columns; margins=True adds row and column totals labelled All, and normalize=True turns counts into fractions. It is pivot_table with a count, made convenient.
Example
# Long to wide (pivot_table) and back again (melt).
long_rows = [("Ada", "maths", 90), ("Ada", "art", 70), ("Rahim", "maths", 80), ("Rahim", "art", 84), ("Mei", "maths", 95), ("Rahim", "art", 86)]
# pivot_table(index="name", columns="subject", values="score", aggfunc="mean")
cells = {}
for name, subject, score in long_rows:
cells.setdefault((name, subject), []).append(score)
names = sorted({r[0] for r in long_rows})
subjects = sorted({r[1] for r in long_rows})
print("name", subjects)
wide = {}
for name in names:
row = []
for subject in subjects:
values = cells.get((name, subject))
row.append(None if values is None else sum(values) / len(values)) # no data: NaN
wide[name] = row
print(name, row)
# melt(id_vars=["name"], var_name="subject", value_name="score"): column by column
melted = []
for j, subject in enumerate(subjects):
for name in names:
melted.append((name, subject, wide[name][j]))
print("melted:", melted)Line by line
- The long rows hold six scores; Rahim has two art scores.
- The grid has one row per name and one column per subject, both sorted.
- Each cell is the mean of the scores that fall in it; Mei's art cell has none, so it is None (NaN).
- melt walks the grid column by column, giving back long rows, including the missing cell.
Expected result
name ['art', 'maths']
Ada [70.0, 90.0]
Mei [None, 95.0]
Rahim [85.0, 80.0]
melted: [('Ada', 'art', 70.0), ('Mei', 'art', None), ('Rahim', 'art', 85.0), ('Ada', 'maths', 90.0), ('Mei', 'maths', 95.0), ('Rahim', 'maths', 80.0)]
In the real world
Sales data arrives long, one row per sale, and becomes a wide region-by-month grid for a report. Survey exports arrive wide, one column per question, and are melted to long format to be grouped and charted. Plotting libraries mostly expect long data, while people and spreadsheets prefer wide, so reshaping is part of almost every pipeline.
Common mistakes
- Using pivot on data with duplicate cells, which raises an error; use pivot_table with an aggregation.
- Forgetting that the default aggregation is the mean, when a total was wanted.
- Leaving NaN cells in a grid that will be summed or plotted, instead of choosing a fill value.
- Losing the identifying columns in melt by not listing them in id_vars.
Best practices
- Store and process data in long format, and pivot to wide only for presentation.
- Always state aggfunc in pivot_table, even when the mean is wanted.
- Name melt's output columns with var_name and value_name.
Summary
Long format has one row per observation; wide format one row per entity and one column per measurement. pivot_table builds a grid from long rows, aggregating duplicates (mean by default), with NaN for empty cells unless a fill value is given and totals from margins. melt turns columns back into rows, column by column, and crosstab counts combinations of two columns.
Quick check
Key takeaways
- pivot_table spreads long rows into a grid and aggregates duplicates (mean by default); cells with no data are NaN.
- melt gathers columns into rows, column by column.
- crosstab counts combinations; margins=True adds an All row and column.
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.