PD-01-017-P · Primer — Reshaping: pivot_table & melt

The idea in one sentence

Your data holds the same facts whether it's wide (one row per subject, many value columns) or long (one row per observation) — pivot_table spreads long into wide, and melt stacks wide back into long.

Why it exists

The exact same numbers can be arranged two ways, and each shape is good at a different job. Long/tidy form — one measurement per row — is what pandas loves for groupby, filtering, and plotting libraries like seaborn. Wide form — a grid with categories across the top — is what humans love to read and what spreadsheets and heatmaps expect. Real data rarely arrives in the shape you need, so you spend a surprising amount of your life converting between the two. Rather than hand-rolling loops, pandas gives you two dependable, inverse tools: pivot_table to widen and melt to lengthen.

How it works

Think of a long table with three columns: a row label (quarter), a category (region), and a value (revenue). pivot_table turns the category's values into new columns, filling the grid with the matching values. Going the other way, melt collapses those value columns back into two columns — one holding the old column names (var_name), one holding the numbers (value_name) — while keeping your identifier columns fixed via id_vars. They are mirror images: pivot fans out, melt gathers in. The one thing to keep straight is that pivot_table always aggregates (its default is mean), so it needs an aggfunc when several rows land in the same cell. Here is the full round trip on tiny inline data.

import pandas as pd

# LONG (tidy) form: one row per observation
long = pd.DataFrame({
    "quarter": ["Q1", "Q1", "Q2", "Q2"],
    "region":  ["East", "West", "East", "West"],
    "revenue": [100, 80, 130, 90],
})

# LONG -> WIDE: spread 'region' into columns
wide = long.pivot_table(index="quarter", columns="region", values="revenue")
print(wide)
# region    East  West
# quarter
# Q1       100.0  80.0
# Q2       130.0  90.0

# WIDE -> LONG: gather region columns back into rows
back = wide.reset_index().melt(
    id_vars="quarter", var_name="region", value_name="revenue"
)
print(back.shape)   # (4, 3)  -> back to one row per observation

The one gotcha

pivot_table silently averages duplicates. If two long rows share the same index + columns pair, they collide in one cell — and because the default aggfunc is "mean", pandas quietly returns their average instead of erroring. Two rows of East, Q1 worth 100 and 20 become 60.0, not 120, and nothing warns you. When you want a total (or a count, or the raw values), say so explicitly: aggfunc="sum". If you truly expect no duplicates and want a crash when they appear, use plain df.pivot(...) instead — it raises ValueError on collisions rather than hiding them.

See it — long rows fan out into a wide grid, and melt gathers them back

flowchart LR
  subgraph LONG ["LONG (one row<br/>per observation)"]
    A["quarter | region | revenue<br/>Q1 | East | 100<br/>Q1 | West | 80<br/>Q2 | East | 130"]
  end
  subgraph WIDE ["WIDE (grid: region<br/>across the top)"]
    B["quarter | East | West<br/>Q1 | 100 | 80<br/>Q2 | 130 | 90"]
  end
  A -->|"pivot_table<br/>(spread out)"| B
  B -->|"melt<br/>(gather in)"| A

The one-line takeaway

pivot_table widens long data into a readable grid, melt narrows it back into tidy rows — and remember pivot's default aggregation is mean, so pass aggfunc="sum" when you mean total.

Where you'll meet it

Whenever a report needs a "categories across the top" table, when a plotting library demands tidy long input, when you unpivot a messy spreadsheet dump into something groupby-friendly, or when you build a summary matrix (region × quarter revenue) for a dashboard — reshaping between wide and long is the quiet step that makes every downstream tool cooperate.

Named concepts: pivot_table, melt, wide vs long, reshaping