Lesson 5 of 8 · 7 min · Working with tables in pandas

Pivot tables and reshaping: pivot_table, crosstab and melt

Covers: pandas 3.x with matplotlib. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

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 pivot and melt
  • 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 of pivot_table with an aggregation.
  • Leaving NaN in the grid when zero is meant; use fill_value=0.
  • Using mean on 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

  1. Build a pivot of amount by status and customer, with totals.
  2. Make the same grid with groupby(...).unstack() and compare.
  3. Use crosstab to count orders per customer and status, with row percentages.
  4. Melt your pivot back to long format, then group it again to confirm the totals.
  5. 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_table gives an Excel-style grid with aggfunc, fill_value and margins.
  • crosstab counts combinations; pivot and melt switch between wide and long layouts.
  • Plot with .plot(kind=...), label the chart and save it.
  • A pivot is a groupby whose second key becomes columns.

Going deeper

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.