Python for Data course menu

pandas Pivot Tables and Crosstabs (Like Excel Pivot Tables)

Build Excel-style pivot tables in pandas with pivot_table: rows, columns, values and aggregation functions, totals with margins, filling gaps, counting with crosstab, percentages, and reshaping with pivot and melt.

Intermediate8 min readUpdated October 24, 2026

On this page

A first pivot table
Totals, gaps and several statistics
Counting with crosstab
pivot and melt: wide and long data
Common mistakes
Practice
Key takeaway

A pivot table summarises data in a grid: one variable down the side, another across the top, and a statistic in each cell. If you have used pivot tables in Excel or Google Sheets, pd.pivot_table is the same idea in code, which means it is repeatable and works on millions of rows.

Python

import pandas as pd

sales = pd.DataFrame({
    "month":   ["Jan", "Jan", "Jan", "Feb", "Feb", "Feb", "Mar", "Mar"],
    "region":  ["North", "South", "North", "South", "North", "South", "North", "South"],
    "product": ["Pen", "Pen", "Book", "Book", "Pen", "Pen", "Book", "Book"],
    "revenue": [200, 80, 300, 750, 60, 120, 900, 150],
})
print(sales)

Output

  month region product  revenue
0   Jan  North     Pen      200
1   Jan  South     Pen       80
2   Jan  North    Book      300
3   Feb  South    Book      750
4   Feb  North     Pen       60
5   Feb  South     Pen      120
6   Mar  North    Book      900
7   Mar  South    Book      150

A first pivot table#

index goes down the side, columns across the top, values is what to summarise, and aggfunc how:

Python

import pandas as pd

sales = pd.DataFrame({
    "month":   ["Jan", "Jan", "Jan", "Feb", "Feb", "Feb", "Mar", "Mar"],
    "region":  ["North", "South", "North", "South", "North", "South", "North", "South"],
    "product": ["Pen", "Pen", "Book", "Book", "Pen", "Pen", "Book", "Book"],
    "revenue": [200, 80, 300, 750, 60, 120, 900, 150],
})
table = pd.pivot_table(sales, index="region", columns="product", values="revenue", aggfunc="sum")
print(table)

Output

product  Book  Pen
region
North    1200  260
South     900  200

Each cell is the total revenue for that region and product. The default aggfunc is the mean, a common surprise; set it explicitly.

Totals, gaps and several statistics#

Python

import pandas as pd

sales = pd.DataFrame({
    "month":   ["Jan", "Jan", "Jan", "Feb", "Feb", "Feb", "Mar", "Mar"],
    "region":  ["North", "South", "North", "South", "North", "South", "North", "South"],
    "product": ["Pen", "Pen", "Book", "Book", "Pen", "Pen", "Book", "Book"],
    "revenue": [200, 80, 300, 750, 60, 120, 900, 150],
})
months = ["Jan", "Feb", "Mar"]
table = pd.pivot_table(sales, index="month", columns="region", values="revenue",
                       aggfunc="sum", fill_value=0, margins=True, margins_name="Total")
print(table.reindex(months + ["Total"]))

print(pd.pivot_table(sales, index="product", values="revenue", aggfunc=["sum", "count", "max"]))

Output

region  North  South  Total
month
Jan       500     80    580
Feb        60    870    930
Mar       900    150   1050
Total    1460   1100   2560
            sum   count     max
        revenue revenue revenue
product
Book       2100       4     900
Pen         460       4     200

margins=True adds row and column totals.
fill_value=0 shows 0 where a combination has no data instead of NaN.
reindex puts the months in calendar order rather than alphabetical.

Counting with crosstab#

pd.crosstab counts how often each combination occurs, which is a quick way to see how two categories relate:

Python

import pandas as pd

customers = pd.DataFrame({
    "plan":    ["basic", "pro", "basic", "pro", "basic", "basic", "pro", "basic"],
    "churned": ["no", "no", "yes", "no", "yes", "no", "no", "yes"],
})
print(pd.crosstab(customers["plan"], customers["churned"], margins=True))
print(pd.crosstab(customers["plan"], customers["churned"], normalize="index").round(2))

Output

churned  no  yes  All
plan
basic     2    3    5
pro       3    0    3
All       5    3    8
churned   no  yes
plan
basic    0.4  0.6
pro      1.0  0.0

normalize="index" turns each row into proportions: 60% of basic customers churned versus none of the pro customers.

pivot and melt: wide and long data#

Data comes in two shapes:

long: one row per observation (month, region, revenue), easy to filter and group;
wide: one row per item with a column per category (month, North, South), easy to read.

pivot turns long into wide (with no aggregation, so each combination must be unique); melt turns wide into long:

Python

import pandas as pd

wide = pd.DataFrame({"month": ["Jan", "Feb"], "North": [500, 60], "South": [80, 870]})
long = wide.melt(id_vars="month", var_name="region", value_name="revenue")
print(long)
print(long.pivot(index="month", columns="region", values="revenue"))

Output

  month region  revenue
0   Jan  North      500
1   Feb  North       60
2   Jan  South       80
3   Feb  South      870
region  North  South
month
Feb        60    870
Jan       500     80

Spreadsheets often arrive wide; most pandas operations (and plotting libraries like seaborn) prefer long data, so melt is worth knowing.

Common mistakes#

Practice#

Practice exercise

Using the sales table, build a pivot table with products as rows and months as columns (in calendar order) showing total revenue, with 0 for missing combinations and a Total row and column.

Show hint

pd.pivot_table(..., index="product", columns="month", aggfunc="sum", fill_value=0, margins=True), then reorder the columns with a list.

Show solution

Python

import pandas as pd

sales = pd.DataFrame({
    "month":   ["Jan", "Jan", "Jan", "Feb", "Feb", "Feb", "Mar", "Mar"],
    "region":  ["North", "South", "North", "South", "North", "South", "North", "South"],
    "product": ["Pen", "Pen", "Book", "Book", "Pen", "Pen", "Book", "Book"],
    "revenue": [200, 80, 300, 750, 60, 120, 900, 150],
})
table = pd.pivot_table(sales, index="product", columns="month", values="revenue",
                       aggfunc="sum", fill_value=0, margins=True, margins_name="Total")
print(table[["Jan", "Feb", "Mar", "Total"]])

Output

month    Jan  Feb   Mar  Total
product
Book     300  750  1050   2100
Pen      280  180     0    460
Total    580  930  1050   2560

Key takeaway#

Next: Merging and Joining DataFrames.

Finished this lesson?

Python for Datapandas groupby Explained: Group, Aggregate and TransformLearn pandas groupby: split-apply-combine, aggregating with sum, mean and count, several statistics with agg and named aggregations, grouping by several columns, transform for group-level columns, and filter.Intermediate12 min read Python for Datapandas concat, stack and explode: Combining and Reshaping DataStack DataFrames with pd.concat (rows and columns), combine many CSV files into one table, add a source column, and reshape data with stack, unstack, explode and transpose.Intermediate11 min read Python for DataPython Sales Report Project: Automate a Monthly Report With pandasBuild an automated monthly sales report with pandas: load a year of transactions, compute revenue, growth and top products, build region pivot tables, chart the trend, and export a multi-sheet Excel report you can rerun every month.Advanced13 min read

A first pivot table#

Totals, gaps and several statistics#

Counting with crosstab#

pivot and melt: wide and long data#

Common mistakes#

Practice#

Key takeaway#

Related lessons