Reshaping: pivot, melt, stack

Tools and reports expect different shapes: plots and groupby like long data, humans and models often like wide.

The same data can be long (one row per observation: date, city, temp) or wide (one row per date, one column per city). Reshaping moves between them.

Long to wide:

df.pivot(index="date", columns="city", values="temp") rearranges without aggregating; it raises if any (index, column) pair appears twice.
df.pivot_table(index="date", columns="city", values="temp", aggfunc="mean") aggregates duplicates. Add fill_value=0 for empty cells and margins=True for row and column totals. It's essentially a groupby on two keys followed by unstack.
pd.crosstab(df.a, df.b) is a quick frequency table of two columns (normalize= for shares).

Wide to long:

df.melt(id_vars=["date"], var_name="city", value_name="temp") turns every non-id column into rows of (variable, value). It's the standard way to tidy spreadsheet-style data, such as one column per month.

stack and unstack move a level between the row index and the columns. unstack() pivots the innermost index level into columns (so df.groupby(["date", "city"]).temp.mean().unstack() gives the wide table) and stack() does the reverse.

A useful rule of thumb: keep data long ("tidy": one variable per column, one observation per row) while computing, because groupby, merge and most plotting libraries expect it. Pivot to wide at the end for presentation or when a model needs one column per feature.

After pivoting, the column index has a name (city) and sometimes multiple levels. df.columns.name = None or reset_index() tidy it for export.

Example

import pandas as pd

long = pd.DataFrame({
    "date": ["d1", "d1", "d2", "d2", "d2"],
    "city": ["Oslo", "Lima", "Oslo", "Lima", "Lima"],
    "temp": [3, 19, 5, 21, 23],
})
wide = long.pivot_table(index="date", columns="city", values="temp", aggfunc="mean")
print(wide)
# city  Lima  Oslo
# date
# d1    19.0   3.0
# d2    22.0   5.0

try:
    long.pivot(index="date", columns="city", values="temp")
except ValueError as e:
    print("pivot:", e)            # duplicate (d2, Lima)

back = wide.reset_index().melt(id_vars="date", var_name="city", value_name="temp")
print(back.shape)                 # (4, 3)

print(long.groupby(["date", "city"])["temp"].mean().unstack().equals(wide))   # True
print(pd.crosstab(long["date"], long["city"]))   # counts per pair

Remember

pivot_table aggregates into a wide table; pivot only rearranges unique pairs
melt turns wide columns into long rows
unstack moves an index level to columns; stack reverses it
Compute on long (tidy) data; pivot wide for presentation

Watch out for

Using pivot on data with duplicate index/column pairs; use pivot_table with an explicit aggfunc.

Check yourself

A sheet has columns `store, Jan, Feb, Mar`. Which call gives rows of (store, month, sales)?
Why might `pivot` fail where `pivot_table` succeeds?

Official docs: pandas.pydata.org/docs/user_guide/reshaping.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks