Reshaping: melt, pivot_table, stack & unstack

The same data can be laid out wide (one column per month) or long (one row per branch-month). Spreadsheets and reports like wide tables; groupby, plotting libraries and databases prefer long ones. Reshaping lets you switch freely.

Wide vs long

Wide                          Long
branch  Apr  May  Jun         branch  month  sales
Surat   12   15   11          Surat   Apr    12
Indore   9   10   14          Surat   May    15
                              Surat   Jun    11
                              Indore  Apr     9
                              ...

melt: wide to long

import pandas as pd

wide = pd.DataFrame({
    "branch": ["Surat", "Indore"],
    "Apr": [12, 9], "May": [15, 10], "Jun": [11, 14],
})

long = wide.melt(id_vars="branch", var_name="month", value_name="sales")
print(long.head())

id_vars are the columns to keep as identifiers; every other column becomes a row. Use value_vars=[...] to melt only some columns. This is the first step whenever you receive a "one column per month" Excel file.

pivot: long to wide (no aggregation)

back = long.pivot(index="branch", columns="month", values="sales")

pivot requires each index/column pair to be unique. If the same branch-month appears twice, it raises ValueError: Index contains duplicate entries. In that case you need aggregation.

pivot_table: long to wide with aggregation

report = pd.pivot_table(
    sales,
    index="branch",
    columns="category",
    values="amount",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="Total",
)

Tool	Duplicates allowed?	Aggregates?	Typical use
pivot	No	No	Reshape tidy data back to wide
pivot_table	Yes	Yes (aggfunc)	Excel-style summary reports
pd.crosstab	Yes	Counts by default	Frequency tables
melt	–	No	Wide to long

aggfunc can be a list or dict, for example aggfunc={"amount": "sum", "qty": "mean"}.

stack and unstack: moving index levels

These work with a MultiIndex. unstack moves an index level into the columns; stack moves column labels into the index.

g = sales.groupby(["branch", "category"])["amount"].sum()   # Series with 2-level index
table = g.unstack("category", fill_value=0)                # categories become columns
again = table.stack()                                      # back to a long Series

groupby(...).sum().unstack() is often the quickest route to a report table and is equivalent to many pivot_table calls.

Order and tidiness

After melting months, sort them in calendar order rather than alphabetical order by making the column an ordered categorical:

months = ["Apr", "May", "Jun"]
long["month"] = pd.Categorical(long["month"], categories=months, ordered=True)
long = long.sort_values(["branch", "month"])

Common mistakes

• Using pivot on data with duplicates instead of pivot_table.
• Forgetting fill_value=0, leaving NaN where no sales happened.
• Months sorting alphabetically (Apr, Jun, May) after reshaping.
• Keeping reports wide inside your pipeline; do calculations on long data and pivot at the end.