Lesson 5 of 8 · 7 min · Working with tables in pandas
Pivot tables and reshaping: pivot_table, crosstab and melt
What you will learn
- Build pivot tables with
pivot_table - Add row and column totals and fill empty cells
- Count combinations with
crosstab - Go between wide and long layouts with
pivotandmelt - Draw a quick chart from a pivot
In simple words
A pivot table turns a long list into a grid: one thing down the side, another across the top, and the totals where they cross. You already know it from Excel. pandas can build the same grid in code, which means you can repeat it every month on new data without clicking anything.
Words to know
- Pivot table: a grid that summarises values by two categories.
- Wide format: one row per item with a separate column for each measurement, such as one column per month.
- Long format: one row per measurement, with columns saying which item and which measurement.
- Melt: turn a wide table into a long one.
pivot_table
Add a month column to the orders and build a grid of total amount by customer and month:
orders["month"] = orders["order_date"].dt.to_period("M").astype(str)
grid = orders.pivot_table(
index="customer_id", # rows
columns="month", # columns
values="amount", # what to summarise
aggfunc="sum", # how
fill_value=0, # replace empty cells
margins=True, # add totals row and column
margins_name="Total",
)
month 2025-09 2025-10 Total
customer_id
1 2000 500 2500
2 1500 0 1500
3 0 1500 1500
Total 3500 2000 5500
Customer 1: September 1200 + 800 = 2000, October 500. Customer 3 only ordered in October (600 + 900 = 1500). The argument names map to Excel: index is Rows, columns is Columns, values is Values, aggfunc is "Summarize values by", and margins gives Grand Totals.
Several aggregations or values:
orders.pivot_table(index="status", values="amount", aggfunc=["count", "sum", "mean"])
orders.pivot_table(index="customer_id", columns="status", values="amount",
aggfunc="sum", fill_value=0)
A percentage of total grid:
share = grid / grid.loc["Total", "Total"] * 100
or grid.div(grid["Total"], axis=0) for percentage of each row.
pivot_table versus groupby
A pivot table is a groupby with the second key unstacked into columns. These give the same numbers:
orders.groupby(["customer_id", "month"])["amount"].sum().unstack(fill_value=0)
Use groupby when you want a tidy list (one row per combination) for further analysis or a chart library; use pivot_table when you want a grid to read or paste into a report.
crosstab
crosstab counts how often combinations occur:
pd.crosstab(orders["customer_id"], orders["status"])
pd.crosstab(orders["customer_id"], orders["status"], margins=True)
pd.crosstab(orders["customer_id"], orders["status"], normalize="index") # row percentages
For customer 1 it shows 3 paid and 0 for refunded and pending. It is the quickest way to see how two categories relate.
pivot: long to wide
pivot reshapes without aggregating, so each index and column pair must be unique, otherwise it raises an error:
monthly = orders.groupby(["customer_id", "month"], as_index=False)["amount"].sum()
wide = monthly.pivot(index="customer_id", columns="month", values="amount")
Missing combinations become NaN. Use pivot_table when pairs may repeat and you need an aggregate.
melt: wide to long
Spreadsheets are often wide, with a column for each month. Tools such as charts and groupby prefer long data. melt converts:
wide = grid.drop(columns="Total").drop(index="Total").reset_index()
long = wide.melt(id_vars="customer_id", var_name="month", value_name="amount")
customer_id month amount
0 1 2025-09 2000
1 2 2025-09 1500
2 3 2025-09 0
3 1 2025-10 500
...
id_vars are the columns kept as identifiers; every other column becomes a pair of "which column" (var_name) and "its value" (value_name).
stack() and unstack() move index levels between rows and columns and are what pivot is built from.
Quick charts
pandas draws charts using matplotlib. After a pivot:
import matplotlib.pyplot as plt
grid.drop(index="Total", columns="Total").plot(kind="bar", stacked=True)
plt.title("Order amount by customer and month")
plt.ylabel("Amount (INR)")
plt.tight_layout()
plt.savefig("orders_by_customer.png", dpi=150)
plt.show()
kind can be "line" (trends), "bar" or "barh" (comparisons), "hist" (distribution), "box" and "scatter" (relationship, with x= and y=). Always label axes and title the chart with the finding. Save with savefig before show(), or the saved file can be blank.
Formatting for reports
grid.style.format("{:,.0f}") # thousands separators in notebooks
grid.round(1)
grid.to_excel("report.xlsx", sheet_name="By customer")
In pandas 3, to_excel writes plain, unstyled cells; to keep styles, call Styler.to_excel.
Common mistakes
- Pivoting on columns with duplicate pairs using
pivot(error) instead ofpivot_tablewith an aggregation. - Leaving
NaNin the grid when zero is meant; usefill_value=0. - Using
meanon already-averaged data and getting a misleading result. - Charting a table with a totals row, which dwarfs the bars. Drop margins first.
- Forgetting
reset_index()before exporting or merging.
Try it yourself
- Build a pivot of amount by status and customer, with totals.
- Make the same grid with
groupby(...).unstack()and compare. - Use
crosstabto count orders per customer and status, with row percentages. - Melt your pivot back to long format, then group it again to confirm the totals.
- Plot a stacked bar of monthly amount by status and save it as a PNG.
Check yourself
How do the arguments of pivot_table map to an Excel pivot?
index is Rows, columns is Columns, values is Values, aggfunc is the summary function, and margins=True adds grand totals.
What is the difference between pivot and pivot_table?
pivot only reshapes and needs unique index-column pairs. pivot_table aggregates, so it handles repeated pairs.
When do you use melt?
To turn a wide table (one column per month, say) into a long table with one row per measurement, which is easier to group, filter and chart.
What does fill_value=0 do in a pivot?
It replaces empty (missing) cells of the grid with 0.
Interview questions
How do you recreate an Excel pivot table in pandas?
Use df.pivot_table(index=..., columns=..., values=..., aggfunc=..., margins=True); for the same result as a grouped table, groupby(...).sum().unstack().
How do you convert a table with one column per month into one row per month?
Use melt with id_vars for the identifier columns, setting var_name and value_name.
When would you pick groupby over pivot_table?
When you need tidy, one-row-per-combination output for more analysis or plotting libraries; pivot tables are better for human-readable grids.
Summary
pivot_tablegives an Excel-style grid withaggfunc,fill_valueandmargins.crosstabcounts combinations;pivotandmeltswitch between wide and long layouts.- Plot with
.plot(kind=...), label the chart and save it. - A pivot is a
groupbywhose second key becomes columns.
Going deeper
- Reshaping and pivot tables: the official guide.
- pandas.pivot_table reference: parameters.
- Chart visualization:
DataFrame.plotoptions. - Matplotlib quick start: the plotting library underneath.
See how much of this you can explain
A free mock interview asks you new questions on Python for data analysis with pandas for Data Analyst and scores every answer, with what to fix.