pandas pivot_table, pivot and melt: Reshape Data Between Long and Wide

Data · By the Loop & Branch editorial team · Published 2026-11-13

Close-up of a blue and white woven fabric showing the crossing threads of warp and weft — Warp and weft: every value sits where a row meets a column.

Data arrives in two shapes. Long data has one row per observation: a month, a region, a product, a number. Wide data spreads one of those labels across the columns, the way a spreadsheet report does. pandas moves between the two with a handful of methods: pivot_table() and pivot() go from long to wide, melt() goes back, and crosstab() builds count tables directly. Picking the right one is mostly a question of whether your data has duplicates.

All examples were run with pandas 3.0 and Python 3.13. They behave the same on pandas 2.x except where noted.

The example data

import pandas as pd

sales = pd.DataFrame({
    "month":   ["Jan", "Jan", "Jan", "Feb", "Feb", "Feb", "Feb"],
    "region":  ["North", "South", "North", "North", "South", "South", "North"],
    "product": ["tea", "tea", "coffee", "tea", "coffee", "tea", "tea"],
    "units":   [10, 7, 4, 12, 5, 3, 6],
})

This is long data. Notice that North sold tea twice in February: two rows share the same month, region and product. That detail decides which method works.

pivot_table: aggregate into a cross-table

pivot_table() takes the labels that become rows (index), the labels that become columns (columns), the numbers to put in the cells (values) and a function to combine numbers that land in the same cell (aggfunc):

sales.pivot_table(index="region", columns="product",
                  values="units", aggfunc="sum")

product  coffee  tea
region
North         4   28
South         5   10

Always pass aggfunc explicitly. Its default is "mean", so without it North's tea cell would show 9.333333 (the average of 10, 12 and 6) rather than the total of 28, and the table would look plausible while answering a different question.

Three more arguments cover most reports:

sales.pivot_table(index="region", columns="product", values="units",
                  aggfunc="sum", fill_value=0,
                  margins=True, margins_name="Total")

product  coffee  tea  Total
region
North         4   28     32
South         5   10     15
Total         9   38     47

fill_value=0 replaces the NaN that appears when a row and column combination has no data at all. Use it only when zero is really the right answer: a missing measurement is not the same as a zero.
margins=True adds row and column totals computed with the same aggfunc. With "mean", the margin is the mean of the underlying rows, not the mean of the cells above it.
aggfunc accepts a list, such as ["sum", "count"], or a dictionary mapping each value column to its own function.

Under the hood, pivot_table() is a groupby on the index and column labels followed by unstack(). If you already think in groupby terms, that is a useful way to predict its output.

pivot: reshape without aggregating

pivot() only moves values around. It has no aggfunc, so every combination of index and column must appear once. On our raw data it fails:

sales.pivot(index="region", columns="product", values="units")
# ValueError: Index contains duplicate entries, cannot reshape

This is the most common reshaping error. You have three ways out:

Switch to pivot_table() with an explicit aggfunc, if combining duplicates is what you want.

Aggregate first, then pivot. This is clearer when the aggregation is more than a sum:

monthly = sales.groupby(["month", "region"], as_index=False)["units"].sum()
wide = monthly.pivot(index="month", columns="region", values="units")

Find the duplicates, because they may be a data error: sales[sales.duplicated(["region", "product"], keep=False)] lists every offending row.

The same duplicate-key logic causes rows to multiply in a merge; our guide to merge, join and concat shows how validate= catches it there.

Sort order and categorical labels

The result of the previous example looks like this:

region  North  South
month
Feb        18      8
Jan        14      7

February comes first because labels are sorted alphabetically. To keep calendar order, make the column an ordered categorical before pivoting:

order = ["Jan", "Feb", "Mar"]
s2 = sales.assign(month=pd.Categorical(sales["month"], categories=order, ordered=True))
s2.pivot_table(index="month", columns="region", values="units", aggfunc="sum")

Now Jan comes before Feb. What about March, which is a category with no rows? In pandas 3.0 the observed argument of pivot_table() defaults to True, so unused categories are dropped. Pass observed=False to keep a March row; with aggfunc="sum" it shows 0, with "mean" it shows NaN. In pandas 2.x the default was False and recent 2.x versions warn about the coming change, so set the argument explicitly if your code must run on both.

Flatten the result

A pivoted table has a named column index (region above) and the old labels in its row index. For export or further work, turn it back into ordinary columns:

flat = wide.reset_index().rename_axis(columns=None)

  month  North  South
0   Feb     18      8
1   Jan     14      7

With several aggregation functions, the columns become a two-level MultiIndex such as ("sum", "tea"). Join the levels into plain names:

m = sales.pivot_table(index="region", columns="product",
                      values="units", aggfunc=["sum", "mean"])
m.columns = [f"{func}_{prod}" for func, prod in m.columns]
# sum_coffee  sum_tea  mean_coffee  mean_tea

Means such as 9.333333 often print with long decimals or in scientific notation on larger data; see how to control number display in pandas if the report needs tidy figures.

melt: from wide back to long

melt() is the inverse operation. Columns you list in id_vars stay as they are; every other column is turned into two: one holding the old column name, one holding the value.

long = flat.melt(id_vars="month", var_name="region", value_name="units")

  month region  units
0   Feb  North     18
1   Jan  North     14
2   Feb  South      8
3   Jan  South      7

You need this more often than you might expect. Spreadsheets, statistical offices and finance exports usually publish wide tables, one column per year or per category, while plotting libraries, groupby() and databases all prefer long data. Melting is typically the first step after read_csv(). Add value_vars=[...] to melt only some columns, and ignore_index=False to keep the original index.

When column names combine a prefix and a key, such as sales_2024 and sales_2025, pd.wide_to_long() splits them for you:

wide = pd.DataFrame({"store": ["A", "B"],
                     "sales_2024": [100, 80], "sales_2025": [120, 95]})
pd.wide_to_long(wide, stubnames="sales", i="store", j="year", sep="_").reset_index()

  store  year  sales
0     A  2024    100
1     B  2024     80
2     A  2025    120
3     B  2025     95

crosstab: counts and percentages

When the question is "how many rows fall in each combination", pd.crosstab() is shorter than a pivot table. It takes columns (or any arrays) rather than column names:

pd.crosstab(sales["region"], sales["product"])
pd.crosstab(sales["region"], sales["product"], normalize="index").round(2)

product  coffee  tea
region
North         1    3
South         1    2

product  coffee   tea
region
North      0.25  0.75
South      0.33  0.67

normalize="index" divides each row by its total, "columns" divides each column, and True divides by the grand total. Pass values= and aggfunc= to sum a column instead of counting rows, which gives the same result as the first pivot_table() above.

Which one to use

You want to	Use
Summarise records into a report table	`pivot_table(..., aggfunc=...)`
Reshape data whose combinations are already unique	`pivot()`
Count or compare proportions of two categories	`pd.crosstab()`
Turn year or category columns back into rows	`melt()` or `pd.wide_to_long()`
Move one index level to the columns, or back	`unstack()` / `stack()`

For a worked example on real public data, our property-price tutorial pivots median sale prices by month and city before plotting them.

Summary

Keep raw data long, pivot only at the end for display, and always name the aggregation. If pivot() complains about duplicate entries, decide whether the duplicates should be summed, averaged or fixed. Use melt() to bring wide downloads back into a shape that groupby() and charts understand, and set observed explicitly when categorical labels are involved.

Sources

pandas documentation, Reshaping and pivot tables (user guide: pivot, pivot_table, melt, wide_to_long, crosstab).
pandas documentation, pandas.DataFrame.pivot_table (API reference: aggfunc, margins, observed).