PYTHON / PANDAS
Pivot tables, melt, and reshaping
Move data between long and wide form with pivot, pivot_table, melt and unstack, and decide when duplicate pairs need an aggregation.
What you will learn
- Reshape long to wide with pivot when each index/column pair appears exactly once
- Use pivot_table with an explicit aggfunc when pairs repeat; the default is mean
- Undo a pivot with reset_index() then melt(id_vars=...), because melt drops the index
- Missing pairs become NaN and promote int columns to float unless you pass fill_value
Understanding Pivot tables, melt, and reshaping
The same measurements can sit in a table as one row per observation (city, month, rain_mm) or as one row per city with a column per month. The numbers are identical; what differs is whether "month" lives inside cells as data or on an axis as a label. pivot moves the values of one column up into the column labels and scatters the value column into the resulting grid, and melt moves those labels back down into cells. Because column labels have to identify a single column, pivot only works when each (index, columns) pair occurs once.
pivot computes nothing: it is set_index plus unstack, a pure relabeling. If two rows carry the same index and column pair there is no single number for that cell, so pandas raises ValueError instead of guessing which one wins. pivot_table is the other shape of the same idea: groupby on index plus columns, aggregate, then unstack, which is why it tolerates duplicates. Its default aggfunc is "mean", so quantities that should be totalled come out averaged if you never pass aggfunc yourself.
melt runs the other direction and looks only at columns; it discards the index entirely, so labels that ended up in the index after a pivot must be brought back with reset_index before they can be listed in id_vars. The id_vars columns are repeated once per melted column, and everything else is stacked into one value column, which forces those columns to share one dtype: melt an int column together with a string column and the value column becomes object. Widening can also invent cells that never existed in the source rows; those appear as NaN and push int64 up to float64 unless you supply fill_value.
import pandas as pd
long = pd.DataFrame({
"city": ["Oslo", "Oslo", "Lima", "Lima", "Cairo", "Cairo"],
"month": ["Jan", "Feb", "Jan", "Feb", "Jan", "Feb"],
"rain_mm": [49, 36, 1, 0, 5, 4],
})
wide = long.pivot(index="city", columns="month", values="rain_mm")
print(wide)
print()
back = wide.reset_index().melt(id_vars="city", value_name="rain_mm")
print(back)Reshaping never changes the values, only which axis their labels live on, so the only real decision is what to do when two rows want the same cell.
Worked examples
Duplicate pairs: pivot fails, pivot_table aggregates
Shows why a repeated index/column pair is an error for pivot but a grouping instruction for pivot_table.
import pandas as pd
sales = pd.DataFrame({
"store": ["A", "A", "A", "B", "B"],
"day": ["Mon", "Mon", "Tue", "Mon", "Tue"],
"amount": [10, 5, 8, 20, 3],
})
try:
sales.pivot(index="store", columns="day", values="amount")
except ValueError as e:
print("pivot ->", type(e).__name__)
print(sales.pivot_table(index="store", columns="day",
values="amount", aggfunc="sum"))Example explained
Line 1Two rows have store A on Mon, so cell (A, Mon) would have to hold both 10 and 5.
Line 2pivot only relabels, so it raises ValueError rather than picking or averaging a value.
Line 3pivot_table(aggfunc="sum") groups those rows first, making (A, Mon) equal to 15.
Line 4The result stays int64 because all four store/day combinations exist, so no NaN is created.
melt with several id_vars
Turns two measurement columns into one label column and one value column while keeping two identifier columns.
import pandas as pd
scores = pd.DataFrame({
"student": ["Ana", "Ben"],
"group": ["x", "y"],
"math": [90, 75],
"art": [60, 88],
})
tidy = scores.melt(
id_vars=["student", "group"],
value_vars=["math", "art"],
var_name="subject",
value_name="score",
)
print(tidy)
print(tidy.dtypes["score"])Example explained
Line 1id_vars are copied once per melted column, which is why Ana appears twice.
Line 2value_vars sets both which columns are stacked and the row order: all math rows, then all art rows.
Line 3var_name and value_name name the two new columns; without them you get "variable" and "value".
Line 4score is int64 only because math and art were both int64; mixing in a text column would give object.
groupby plus unstack, and the NaN it creates
Builds a wide table from a MultiIndex Series and shows how fill_value avoids the float promotion.
import pandas as pd
df = pd.DataFrame({
"team": ["red", "red", "blue"],
"half": [1, 2, 1],
"goals": [2, 0, 1],
})
s = df.groupby(["team", "half"])["goals"].sum()
print(s.unstack("half"))
print()
print(s.unstack("half", fill_value=0))Example explained
Line 1groupby(["team", "half"]).sum() puts both keys in a MultiIndex, and unstack("half") lifts the named level onto the columns.
Line 2That two-step is what pivot_table does internally, which is why pivot_table demands an aggfunc.
Line 3blue has no row for half 2, so that cell is NaN and the whole frame becomes float64, printing 1.0 instead of 1.
Line 4fill_value=0 supplies the absent cell while the array is being built, so the result stays int64.
Important notes
pivot and unstack sort the labels they create, so month columns come out alphabetically (Feb before Jan); use an ordered Categorical or reindex the columns to get calendar order.
pivot_table(margins=True) computes the "All" row and column from the underlying rows, not from the cells on screen, so with aggfunc="mean" the margin is not the mean of the displayed means.
Common mistakes
Hitting the ValueError from pivot on duplicated pairs and "fixing" it with drop_duplicates: rows are thrown away instead of combined, so totals silently shrink. pivot_table(aggfunc="sum") is the fix.
Leaving pivot_table's aggfunc at its default: a store with rows 10 and 5 shows 7.5 instead of 15, and nothing in the output signals that a mean was taken.
Calling melt directly on a pivoted frame: melt ignores the index, so id_vars=["city"] raises KeyError and a plain wide.melt() loses the city labels for good.
Try it yourself
Change, predict, then run
Build a long DataFrame of three products and two quarters with a units column, then add one extra row that repeats an existing product/quarter pair. Pivot it with pivot_table(aggfunc="sum"), then reset_index and melt it back so you again have one row per product/quarter, and check that the units total is unchanged.
Open the Python workspaceCheck your understanding
A DataFrame has two rows with store 'A' and day 'Mon'. df.pivot(index='store', columns='day', values='amount') raises ValueError. What is the actual problem?
- The store column repeats values, and pivot needs an index column with unique entries.
- The amount column contains duplicate numbers, so pandas cannot align them.
- Two rows map to the same (store, day) cell and pivot has no rule for combining them.
- The day column must be numeric before its values can become column labels.
Show answer
pivot only moves labels between axes, and one cell can hold one value, so a repeated (index, columns) pair has no defined result; pivot_table with aggfunc='sum' is how you supply the missing rule. Repeated values inside store alone are perfectly normal, since that is exactly what makes several source rows share one output row.