Reshaping: melt, stack and pivot_table

Reshape pandas DataFrames between wide and long format with melt, pivot, pivot_table, stack, unstack and crosstab, and learn when to use each shape.

• Intermediate
• 17 min read
• 4 objectives

Before this lessonLesson 7: Grouping, Merging and Pivots

The same data can be laid out in different shapes. A spreadsheet that has one column per month is wide: easy for a person to scan, awkward for code. A table with one row per (product, month) pair is long: repetitive to look at, but perfect for groupby, filtering and plotting libraries, which expect one observation per row.

You met pivot_table briefly in the grouping lesson as a way to summarize. This lesson treats reshaping as its own skill: moving freely between wide and long so that each step of your analysis gets the shape it needs.

Wide vs long

Here is the same sales data in both shapes. In the wide version, the month is hidden in the column names. In the long version, month is a real column you can filter on and group by.

import pandas as pd

wide = pd.DataFrame({
    "product": ["mouse", "desk"],
    "jan": [120, 15],
    "feb": [135, 12],
    "mar": [150, 18],
})
print(wide)

long = wide.melt(id_vars="product", var_name="month", value_name="units")
print(long)

  product  jan  feb  mar
0   mouse  120  135  150
1    desk   15   12   18
  product month  units
0   mouse   jan    120
1    desk   jan     15
2   mouse   feb    135
3    desk   feb     12
4   mouse   mar    150
5    desk   mar     18

melt: wide to long

melt keeps the id_vars columns as identifiers and stacks every other column into two new ones: a column holding the old column names (var_name) and one holding the values (value_name). Use value_vars to melt only some columns. Once data is long, questions like "total units per month" become a one-line groupby.

import pandas as pd

scores = pd.DataFrame({
    "student": ["Ada", "Grace", "Linus"],
    "math": [95, 88, 72],
    "physics": [90, 93, 70],
    "art": [60, 75, 85],
})

long = scores.melt(
    id_vars="student",
    value_vars=["math", "physics"],   # ignore art
    var_name="subject",
    value_name="score",
)
print(long)
print(long.groupby("subject")["score"].mean())

  student  subject  score
0     Ada     math     95
1   Grace     math     88
2   Linus     math     72
3     Ada  physics     90
4   Grace  physics     93
5   Linus  physics     70
subject
math       85.000000
physics    84.333333
Name: score, dtype: float64

pivot and pivot_table: long to wide

pivot is the inverse of melt. You say which column becomes the rows (index), which becomes the new column headers (columns) and which fills the cells (values). It only reshapes, it never aggregates, so every (index, column) pair must be unique.

pivot_table does the same but aggregates duplicates with aggfunc (mean by default). Use it whenever there can be more than one row per cell, which in real data is most of the time. margins=True adds row and column totals.

import pandas as pd

sales = pd.DataFrame({
    "region": ["north", "north", "south", "south", "north"],
    "month": ["jan", "feb", "jan", "feb", "jan"],
    "revenue": [100, 120, 80, 95, 40],
})

try:
    sales.pivot(index="region", columns="month", values="revenue")
except ValueError as e:
    print("pivot failed:", e)

table = sales.pivot_table(
    index="region", columns="month", values="revenue",
    aggfunc="sum", fill_value=0, margins=True, margins_name="total",
)
print(table)

pivot failed: Index contains duplicate entries, cannot reshape
month   feb  jan  total
region                 
north   120  140    260
south    95   80    175
total   215  220    435

stack and unstack

pivot_table with several index columns returns a DataFrame with a MultiIndex: row labels made of more than one level. unstack() moves the innermost row level into the columns (long to wide), and stack() moves the innermost column level back into the rows (wide to long). They are the MultiIndex-aware versions of pivot and melt.

import pandas as pd

sales = pd.DataFrame({
    "region": ["north", "north", "south", "south"],
    "month": ["jan", "feb", "jan", "feb"],
    "revenue": [140, 120, 80, 95],
})

s = sales.set_index(["region", "month"])["revenue"]
print(s)

wide = s.unstack()          # month becomes columns
print(wide)

back = wide.stack()         # and back to a long Series
print(back.equals(s.sort_index()))

region  month
north   jan      140
        feb      120
south   jan       80
        feb       95
Name: revenue, dtype: int64
month   feb  jan
region          
north   120  140
south    95   80
True

When you are done with a MultiIndex, reset_index() turns its levels back into ordinary columns. Most people find flat tables easier to work with, so it is common to finish a reshape with it.

crosstab: counting combinations

pd.crosstab is a shortcut for the very common "how many rows have each combination of A and B" question. It is a pivot table that counts by default. normalize turns counts into proportions, per row ("index"), per column or overall.

import pandas as pd

users = pd.DataFrame({
    "plan": ["free", "pro", "free", "team", "pro", "free"],
    "device": ["mobile", "desktop", "mobile", "desktop", "mobile", "desktop"],
})

print(pd.crosstab(users["plan"], users["device"]))
print(pd.crosstab(users["plan"], users["device"], normalize="index").round(2))

device  desktop  mobile
plan                   
free          1       2
pro           1       1
team          1       0
device  desktop  mobile
plan                   
free       0.33    0.67
pro        0.50    0.50
team       1.00    0.00

Which shape when?

• Long for storage, groupby, filtering, joining and most plotting libraries (seaborn, plotly express).
• Wide for reports, quick comparisons across columns, and computing differences between columns such as feb - jan.
• Use melt when data arrives wide from a spreadsheet, and pivot_table when you need a human-friendly summary at the end.

Recap

• Long data has one observation per row; wide data spreads a variable across columns.
• melt goes wide to long; pivot goes long to wide without aggregating.
• pivot_table aggregates duplicates and can add totals with margins=True.
• stack and unstack move MultiIndex levels between rows and columns; reset_index flattens.
• crosstab counts combinations and can normalize them into shares.

# Write your solution here