Pivot Tables & Crosstab

Reviewed & published by Brayan K

A pivot table in pandas is a summary grid built with df.pivot_table() that takes long, row-by-row data and reshapes it so one categorical column becomes the rows, another becomes the columns, and a numeric column is aggregated into each cell — exactly like a spreadsheet PivotTable.

Part of the free Pandas course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn in This Lesson

• Building summaries with df.pivot_table()
• Choosing index, columns, values, and aggfunc
• Adding grand totals with margins=True
• Applying multiple aggregation functions at once
• Why df.pivot errors on duplicate cells
• Counting category combinations with pd.crosstab

1 Your First Pivot Table

Start with long-format sales data — one row per transaction. pivot_table turns it into a grid: index picks the rows, columns picks the columns, values is the number to summarise, and aggfunc says how (sum, mean, count, ...).

import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "North", "South", "South", "North"],
    "product": ["Pen", "Pad", "Pen", "Pad", "Pen"],
    "amount": [10, 20, 15, 25, 30],
})

# Rows = region, Columns = product, Cell = total amount
table = sales.pivot_table(
    index="region",
    columns="product",
    values="amount",
    aggfunc="sum",
)
print(table)
# product   Pad   Pen
# region
# North      20    40   <- North Pens: 10 + 30 = 40
# South      25    15

# ✅ Expected output:
# product  Pad  Pen
# region           
# North     20   40
# South     25   15

2 Totals (margins=True) & Multiple Aggregations

Add margins=True for an "All" row and column of grand totals. Pass a list to aggfunc to compute several summaries at once.

import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "North", "South", "South", "North"],
    "product": ["Pen", "Pad", "Pen", "Pad", "Pen"],
    "amount": [10, 20, 15, 25, 30],
})

# margins=True adds an 'All' row and column of totals
totals = sales.pivot_table(
    index="region", columns="product", values="amount",
    aggfunc="sum", margins=True,
)
print(totals)
# An 'All' row and 'All' column now show the grand totals.

print("---")

# Multiple aggfuncs: get both the sum and the mean per region
multi = sales.pivot_table(
    index="region", values="amount",
    aggfunc=["sum", "mean", "count"],
)
print(multi)
# Three summary columns: sum, mean, and count of amount per region.

# ✅ Expected output:
# product  Pad  Pen  All
# region                
# North     20   40   60
# South     25   15   40
# All       45   55  100
# ---
#           sum   mean  count
#        amount amount amount
# region                     
# North      60   20.0      3
# South      40   20.0      2

3 pivot vs pivot_table, and pd.crosstab

df.pivot() only reshapes and errors if any cell would receive more than one value. pd.crosstab() is a shortcut for frequency tables — counting how often category combinations occur, optionally as proportions with normalize=.

import pandas as pd

df = pd.DataFrame({
    "gender": ["F", "M", "F", "M", "F"],
    "passed": ["Yes", "No", "Yes", "Yes", "No"],
})

# crosstab counts how many times each (gender, passed) pair occurs
freq = pd.crosstab(df["gender"], df["passed"])
print(freq)
# passed   No  Yes
# gender
# F         1    2
# M         1    1

print("---")

# normalize='index' turns each row into proportions that sum to 1
prop = pd.crosstab(df["gender"], df["passed"], normalize="index")
print(prop)
# Each row now shows the fraction passing/failing.

print("---")

# df.pivot needs unique index/column pairs or it raises ValueError.
unique = pd.DataFrame({
    "year": [2023, 2023, 2024, 2024],
    "metric": ["sales", "cost", "sales", "cost"],
    "value": [100, 60, 120, 70],
})
print(unique.pivot(index="year", columns="metric", values="value"))

# ✅ Expected output:
# passed  No  Yes
# gender         
# F        1    2
# M        1    1
# ---
# passed        No       Yes
# gender                    
# F       0.333333  0.666667
# M       0.500000  0.500000
# ---
# metric  cost  sales
# year               
# 2023      60    100
# 2024      70    120

🎯 Your Turn: Average Score per Class

Build a pivot table of the average score for each class. Which aggfunc gives the average?

import pandas as pd

grades = pd.DataFrame({
    "class": ["A", "A", "B", "B", "B"],
    "subject": ["Math", "Sci", "Math", "Sci", "Math"],
    "score": [80, 90, 70, 60, 100],
})

# 🎯 YOUR TURN: average score per class per subject
table = grades.pivot_table(
    index="class", columns="subject",
    values="score", aggfunc="___",
)
print(table)

# EXPECTED OUTPUT:
# subject  Math   Sci
# class
# A        80.0  90.0
# B        85.0  60.0   <- B Math = (70 + 100) / 2 = 85.0

📋 Quick Reference

🏆 Mini Challenge: Store Performance Grid

From transaction-level data, build a store-by-month revenue grid with grand totals, then find the best month overall.

import pandas as pd

tx = pd.DataFrame({
    "store": ["A", "A", "B", "B", "A", "B"],
    "month": ["Jan", "Feb", "Jan", "Feb", "Jan", "Feb"],
    "revenue": [200, 150, 300, 250, 100, 50],
})

# 1) Store x month revenue grid with totals
grid = tx.pivot_table(
    index="store", columns="month", values="revenue",
    aggfunc="sum", margins=True, margins_name="Total",
)
print(grid)

# 2) Best month overall = highest column total in the 'Total' row
month_totals = grid.loc["Total"].drop("Total")
print("Best month:", month_totals.idxmax())
# EXPECTED: Jan total = 200 + 300 + 100 = 600, Feb = 150 + 250 + 50 = 450
# Best month: Jan

# ✅ Expected output:
# month  Feb  Jan  Total
# store                 
# A      150  300    450
# B      300  300    600
# Total  450  600   1050
# Best month: Jan

❓ Frequently Asked Questions

Lesson complete — you can summarise data like a spreadsheet!

You can build pivot tables with custom aggregations and totals, choose between pivot and pivot_table, and count category combinations with crosstab. These are the everyday reporting tools of a data analyst.

Practice quiz

Continue this course

• Previous: Concatenating & Appending
• Next: Reshaping: melt, stack, unstack — Move between wide and long formats with melt, stack, and unstack
• Quick reference: Pandas cheat sheet › Grouping & Aggregation

Frequently asked questions

What is the difference between df.pivot and df.pivot_table?

df.pivot only reshapes — it requires that each index/column combination is unique and raises a ValueError if there are duplicates, because it cannot decide which value to keep. df.pivot_table aggregates: when several rows share the same index/column cell it combines them with an aggfunc (mean by default). Use pivot for pure reshaping of already-unique data, and pivot_table when you need to summarise.

How do I add row and column totals to a pivot table?

Pass margins=True to df.pivot_table. Pandas adds an extra row and column (labelled 'All' by default, customisable with margins_name) containing the grand totals computed with the same aggfunc.

When should I use pd.crosstab instead of pivot_table?

pd.crosstab is a focused shortcut for frequency tables — counting how often combinations of two (or more) categorical variables occur. crosstab(a, b) counts co-occurrences directly. You can also pass values and aggfunc to make it behave like pivot_table, and normalize= to turn counts into proportions.