Reshaping Data (pivot, pivot_table, melt)

Long vs wide (why reshaping exists)

• Long format: one row per observation (better for analysis/plotting)
• Wide format: values spread across columns (good for reporting)

From the book

McKinney frames stack/unstack and pivot/melt as the same idea at two levels: stack rotates columns into rows (wide → long), unstack rotates rows back into columns (long → wide). pivot is really just “set an index, then unstack” — and it will raise an error if your (index, columns) pair isn’t unique. Use pivot_table instead when you need to aggregate duplicate combinations.

Example: long format

Long format

import pandas as pd
 
df = pd.DataFrame({
    "date": ["2025-01-01", "2025-01-01", "2025-01-02", "2025-01-02"],
    "city": ["pune", "delhi", "pune", "delhi"],
    "sales": [100, 120, 90, 150],
})
 
print(df)

pivot: reshape when data is unique

pivot

wide = df.pivot(index="date", columns="city", values="sales")
print(wide)

If the combination of (index, columns) isn’t unique, pivot throws an error.

pivot_table: reshape with aggregation

pivot_table

wide = df.pivot_table(index="date", columns="city", values="sales", aggfunc="sum")
print(wide)

melt: wide → long

melt

wide = pd.DataFrame({
    "date": ["2025-01-01", "2025-01-02"],
    "pune": [100, 90],
    "delhi": [120, 150],
})
 
long = wide.melt(id_vars=["date"], var_name="city", value_name="sales")
print(long)

Practical notes

• Many ML/EDA workflows prefer long format.
• Pivoted tables often have a multi-index for columns—use reset_index() or wide.columns renaming if needed.

Visualize it

diagram Wide vs long, and how to move between them mermaid

  flowchart LR
  W["Wide format
(one column per city)"] -->|"melt()"| L["Long format
(one row per observation)"]
  L -->|"pivot() / pivot_table()"| W

melt turns wide columns into long rows; pivot/pivot_table turns long rows back into wide columns.

sketch Wide table <-> long table p5.js Pause Restart

melt stacks columns into rows; pivot spreads rows back out into columns. Same data, two shapes.

🧪 Try It Yourself

Exercise 1 – Melt Wide to Long

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Pivot Long Back to Wide

pch.exerciseTag pch.exerciseTitle

Exercise 3 – Aggregate Duplicates With pivot_table

pch.exerciseTag pch.exerciseTitle

Next

Reshaping assumes your tables are already combined — continue to Merging and Joining Data (merge, join, concat) for how to bring multiple tables together in the first place.