Pandas
Intermediate 12 min read Pandas

Pivots and Melting

Wide vs long: the shape every report needs

pivot_table builds Excel-style cross-tabs from long data; melt (and stack/unstack) reshapes wide data back to tidy rows. Mastering the wide↔long round-trip is table-shaping superpower.

pivot_tablemeltstackunstackwidelongreshape
Prerequisites: Pandas Grouping and Aggregation
Before we start

What you will learn

  • Build pivot tables with rows, columns and values
  • Melt wide frames into tidy long form
  • Understand stack/unstack for MultiIndex reshaping
Introduction

What is this?

pivot_table(df, index='city', columns='plan', values='revenue') cross-tabulates: each city-rows × plan-columns cell carries an aggregation. The inverse, df.melt(id_vars='city', var_name='plan', value_name='revenue'), takes one row per cell back to tidy long form. stack/unstack flip the last index level between rows and columns — the same reshape grammar SQL can't express.

Motivation

Why do you need it?

  • Cross-tabs are the native shape of business reports (rows=segment, cols=month).
  • Modeling and most pandas ops want tidy/long data; reshape gets you there.
  • Wide exports from finance need melting before analysis.
  • pivot_table's fill_value and margins give report-ready output with one call.
Think about it

Mental model

Imagine

Flipping a table's rows into columns

Think of

Pivot / melt

One analyst likes cities as rows, plans as columns; another wants one 'city, plan, value' tuple per line for the model. Pivot flips long→wide; melt flips wide→long. Both describe the same facts in different arrangements for different consumers.

Mechanics

How it works

  • pivot_table(index=..., columns=..., values=..., aggfunc='sum') crosses keys and aggregates.
  • fill_value and margins add zeros and grand totals that save report plumbing.
  • melt(id_vars, value_vars, var_name, value_name) turns columns into labeled rows.
  • wide_to_long and transpose handle other common shapes (e.g., date-suffixed columns).
  • stack() pushes the last index column into rows; unstack() pulls a row level into columns.
  • The round-trip pivot→melt→pivot preserves information when keys are unique.

The reshape round-trip.

Long form
pivot_table
Wide form
melt
Tidy again
Hands-on

Examples

Cross-tab and back

Cross-tab and back
import pandas as pd
df = pd.DataFrame({
    "city": ["austin", "austin", "houston", "austin", "houston"],
    "plan": ["pro", "basic", "pro", "pro", "basic"],
    "revenue": [300, 100, 900, 400, 60],
})
wide = df.pivot_table(index="city", columns="plan", values="revenue", aggfunc="sum", fill_value=0)
print(wide)
long = wide.reset_index().melt(id_vars="city", var_name="plan", value_name="revenue")
print(long)

pivot_table makes the report cross-tab in one call; reset_index + melt turns it back into one row per city-plan cell.

Real world

In production

  • A finance team builds monthly revenue matrices straight from long order data.
  • A research analyst melts a wide experiment table before running statsmodels.
  • A BI dashboard feeds a pivot on the fly and reports margins and segment totals.
Guidelines

Best practices

  • For modeling, keep data long; pivot only at the reporting boundary.
  • One aggfunc per question — mixing sum and mean in one cross-tab confuses readers.
  • Use fill_value=0 only when absence means zero; NaN otherwise is honest.
  • Give Index names (navy) before unstack so stacks come out readable.
Watch out

Common mistakes

  • pivot_table collapsing duplicate index keys into one cell, averaging rows together.
  • Melting with value_vars wrong, turning the id column into junk rows.
  • Assuming a key is unique when it isn't — the pivot then silently aggregates.
  • Transposing a frame and forgetting to reset the index, keeping a MultiIndex surprise.
When things break

Debugging tips

  • A pivot cell that's 'too low' means duplicates were aggregated — check duplicated(index+columns).any().
  • Melted output larger than input → you included a cell that repeats; inspect id_vars.
  • stack/unstack order confusion → name the index and re-run with a 3-row sample.
  • When a reshape explodes, assert the round-trip shape before trusting values.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which converts wide columns into long rows?

2

pivot_table requires unique index/columns pairs to avoid silent aggregation.

Go deeper

Official documentation

pandas: Reshaping and pivoting
docs