Data comes in two shapes. Long format has one row per observation; wide format spreads values across columns. Analysis tools usually prefer long; people reading reports usually prefer wide. Reshaping moves between them.
Long vs wide
Long:
| store | month | sales |
|---|---|---|
| Andheri | Jan | 410 |
| Andheri | Feb | 455 |
| Thane | Jan | 380 |
Wide:
| store | Jan | Feb |
|---|---|---|
| Andheri | 410 | 455 |
| Thane | 380 |
pivot_table: long to wide, with aggregation
import pandas as pd
report = pd.pivot_table(
sales,
index="store",
columns="month",
values="sales",
aggfunc="sum",
fill_value=0,
margins=True, # adds an "All" row and column
)pivot_table handles duplicates by aggregating them with aggfunc (sum, mean, count and more). It is the pandas version of an Excel pivot table.
Several aggregations at once:
pd.pivot_table(sales, index="store", columns="month", values="sales", aggfunc=["sum", "mean"])This creates multi-level columns. Flatten them for export:
report.columns = [f"{agg}_{month}" for agg, month in report.columns]pivot: reshape without aggregating
sales.pivot(index="store", columns="month", values="sales")pivot only rearranges. If a store-month pair appears twice, it raises an error. Use pivot_table when duplicates are possible.
melt: wide to long
Many Excel files arrive wide, with one column per month. Most analysis needs them long:
long = wide.melt(id_vars="store", var_name="month", value_name="sales")id_vars are the columns to keep; every other column becomes a (month, sales) pair. Power BI users know this as unpivot.
crosstab: counts of two categories
pd.crosstab(tickets["city"], tickets["category"], normalize="index")normalize="index" turns counts into row percentages, for example the share of each ticket category within each city.
stack and unstack
After a groupby on two keys, unstack moves the inner key into columns:
sales.groupby(["store", "month"])["sales"].sum().unstack()stack does the reverse.
Which shape when?
- Long for groupby, plotting with Seaborn (
hue="month"), and loading into Power BI. - Wide for final report tables and side-by-side comparisons.
Practise
Our merge, pivot and reshape set includes pivot tables, melt and crosstabs. If you know Excel pivots, read the Excel pivot tables guide and compare.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: