📐 Pivot Tables

📚 What You'll Learn

By the end of this lesson, you will be able to:

Reshape long data into a wide summary table with pivot_table()
Choose the right index, columns, and values for a question you want to answer
Summarize with any aggfunc — mean, sum, count, or a list of several functions at once
Add row and column totals with margins=True and fill gaps with fill_value
Build multi-level pivots across several index and column keys
Know when to reach for pd.crosstab() and how pivot_table differs from plain pivot()

⏱️ Estimated Time: 40–55 minutes

🎯 Project: Turn a flat sales log into an executive summary — revenue by product and region, with subtotals, grand totals, and a heatmap you can read at a glance.

Reshape and summarize data the way spreadsheet pivot tables do — but with the full power of pandas behind them.

🌟 From Long Rows to a Readable Summary

A raw transaction log is great for storage but terrible for reading. Thousands of rows like "North, Laptop, 1499" answer nothing on their own. A pivot table rotates that long list into a compact grid — products down the side, regions across the top, revenue in every cell — so the story jumps out immediately.

If you have ever built a pivot table in Excel or Google Sheets, you already know the idea. In pandas, pivot_table() does the same reshape-and-aggregate move in a single, reproducible line of code, and it scales to millions of rows. In this lesson you will map the four questions every pivot answers — what goes down, what goes across, what gets measured, and how is it summarized — and then build one interactively.

🔄 The Anatomy of a Pivot

Every call to pivot_table() answers four questions. Keep them in mind and the API stops feeling mysterious:

graph LR A[Long DataFrame] --> B[index
rows to keep] A --> C[columns
spread across the top] A --> D[values
the number to summarize] A --> E[aggfunc
how to combine] B --> F[Wide Pivot Table] C --> F D --> F E --> F style A fill:#f9f,stroke:#333,stroke-width:2px style F fill:#9f9,stroke:#333,stroke-width:2px

import pandas as pd

# The four building blocks of every pivot table
pivot = pd.pivot_table(
    sales,               # the long DataFrame
    index='product',     # rows   -> what goes DOWN the side
    columns='region',    # columns -> what spreads ACROSS the top
    values='revenue',    # values  -> the number in each cell
    aggfunc='sum',       # aggfunc -> how duplicate combinations combine
    fill_value=0         # replace missing combinations with 0
)
print(pivot)

Because a product can appear in the same region many times, pandas must decide how to combine those matching rows — that is the job of aggfunc. This is the one thing a spreadsheet hides from you, and the one thing that makes pandas pivots so flexible.

🎮 Interactive Pivot Builder

Drag fields into the layout areas to see how index, columns, and values shape the result. Then toggle grand totals on and off.

Available Fields

product

region

quarter

revenue

Index (rows)

Columns

Values

Show row & column totals (margins=True)

Source data (long format)

Product	Region	Revenue

Pivot result (revenue by product & region)

Revenue heatmap

Darker cells mean more revenue — exactly how an analyst scans a pivot for hot spots.

🎯 Basic Pivot Table

One index, one column set, one value, one aggregation.

# Average revenue for each product in each region
pd.pivot_table(
    sales,
    index='product',
    columns='region',
    values='revenue',
    aggfunc='mean'
)

# Shorthand: DataFrame method form
sales.pivot_table(
    index='product',
    values='revenue',
    aggfunc='sum'
)

🧮 Multiple Aggregations

Pass a list — or a dict per column — to summarize several ways at once.

# Several stats for one value column
pd.pivot_table(
    sales, index='product', values='revenue',
    aggfunc=['sum', 'mean', 'count']
)

# Different aggregation per value column
pd.pivot_table(
    sales, index='region',
    values=['revenue', 'quantity'],
    aggfunc={'revenue': 'sum', 'quantity': 'mean'}
)

➕ Margins (Totals)

margins=True adds an "All" row and column of subtotals.

pd.pivot_table(
    sales,
    index='product',
    columns='region',
    values='revenue',
    aggfunc='sum',
    margins=True,          # add grand totals
    margins_name='Total',  # label for the totals
    fill_value=0
)

🏗️ Multi-Level Pivots

Lists of keys create hierarchical (MultiIndex) rows and columns.

pd.pivot_table(
    sales,
    index=['region', 'product'],   # nested rows
    columns=['year', 'quarter'],   # nested columns
    values='revenue',
    aggfunc='sum',
    fill_value=0
)

# Flatten a MultiIndex afterwards
pivot.columns = ['_'.join(map(str, c))
                 for c in pivot.columns]

🔀 pivot() vs pivot_table()

pivot() only reshapes and fails on duplicates; pivot_table() reshapes and aggregates.

# pivot(): pure reshape, no aggregation.
# Raises if (index, columns) pairs repeat.
sales.pivot(index='date',
            columns='product',
            values='revenue')

# pivot_table(): safe with duplicates because
# aggfunc collapses them (default is 'mean').
sales.pivot_table(index='date',
                  columns='product',
                  values='revenue',
                  aggfunc='sum')

📊 crosstab()

A pivot specialized for frequency counts and proportions.

# Count of transactions per product/region
pd.crosstab(sales['product'], sales['region'])

# Normalize to row proportions
pd.crosstab(
    sales['product'], sales['region'],
    normalize='index'
)

# Weighted crosstab acts like a pivot_table
pd.crosstab(
    sales['product'], sales['region'],
    values=sales['revenue'], aggfunc='sum'
)

🌍 Real-World Scenario: Executive Sales Summary

Let's turn a flat sales log into the kind of summary a manager actually wants to read:

import pandas as pd
import numpy as np

# Sample flat sales log
np.random.seed(7)
n = 1000
sales = pd.DataFrame({
    'date': pd.to_datetime('2024-01-01') +
            pd.to_timedelta(np.random.randint(0, 365, n), unit='D'),
    'product': np.random.choice(['Laptop', 'Phone', 'Tablet', 'Watch'], n),
    'region': np.random.choice(['North', 'South', 'East', 'West'], n),
    'quantity': np.random.randint(1, 6, n),
    'unit_price': np.random.uniform(100, 1500, n).round(2),
})
sales['revenue'] = (sales['quantity'] * sales['unit_price']).round(2)
sales['quarter'] = sales['date'].dt.to_period('Q').astype(str)

# 1. Revenue by product and region, with grand totals
summary = pd.pivot_table(
    sales,
    index='product',
    columns='region',
    values='revenue',
    aggfunc='sum',
    margins=True,
    margins_name='All',
    fill_value=0
).round(0)
print("Revenue by Product x Region:")
print(summary)

# 2. Several metrics at once
metrics = pd.pivot_table(
    sales,
    index='product',
    values=['revenue', 'quantity'],
    aggfunc={'revenue': ['sum', 'mean'], 'quantity': 'sum'}
).round(2)
print("\nPer-product metrics:")
print(metrics)

# 3. Multi-level pivot: quarter nested under region
trend = pd.pivot_table(
    sales,
    index='region',
    columns='quarter',
    values='revenue',
    aggfunc='sum',
    fill_value=0
).round(0)
print("\nRegional revenue by quarter:")
print(trend)

# 4. Share of total: divide each cell by the grand total
share = summary.iloc[:-1, :-1]
share = (share.div(share.values.sum()) * 100).round(1)
print("\n% of total revenue by cell:")
print(share)

💡 Pro Tips for Pivot Mastery

Always set fill_value: empty product/region combinations become NaN otherwise, which breaks later math.
Name your margins: margins_name='Total' reads better than the default "All".
Flatten MultiIndex columns before exporting to CSV or plotting.
Reach for crosstab when you only need counts or proportions.
Chain with .style.background_gradient() in notebooks to get an instant heatmap.
Remember the default aggfunc is 'mean' — set it explicitly so readers are never surprised.

⚠️ Common Pitfalls to Avoid

Using pivot() on duplicate keys: it raises a ValueError; use pivot_table() to aggregate instead.
Forgetting the default aggregation: a "wrong" total is usually a silent mean where you wanted a sum.
Ignoring NaN cells: missing combinations skew averages and totals unless filled.
Losing the keys: pivot results index by the group keys — call .reset_index() for a flat table.
Including margins in downstream math: the "All" row/column will double-count if you aggregate again.

📋 Pivot Table Cheat Sheet

Goal	Code	Notes
Basic summary	`pd.pivot_table(df, index='a', values='v', aggfunc='sum')`	One dimension down the side
Cross-tab layout	`pd.pivot_table(df, index='a', columns='b', values='v')`	Second dimension across the top
Several stats	`aggfunc=['sum', 'mean', 'count']`	List for multiple functions
Per-column stats	`aggfunc={'v1':'sum','v2':'mean'}`	Dict keyed by value column
Add totals	`margins=True, margins_name='Total'`	Adds subtotal row & column
Fill gaps	`fill_value=0`	Replaces missing combinations
Nested keys	`index=['a','b'], columns=['c']`	Creates a MultiIndex
Counts only	`pd.crosstab(df.a, df.b)`	Frequency table

📓 Learning Journal

Keep a learning journal — digital or physical. After this lesson, take a few minutes to write down:

Key concepts you learned
Techniques that clicked for you
Questions or confusion points to revisit
Ideas you want to try
Your progress and feelings about learning this

✍️ This lesson's prompt: Think of a flat list you deal with — expenses, tickets, grades, workouts. Write out the four pivot questions for it: what goes down the side (index), what spreads across the top (columns), what number you would measure (values), and how you would combine it (aggfunc). What summary would suddenly become obvious?

📝 Lesson Summary

🎓 Key Takeaways

A pivot table is a reshape plus an aggregation: index down the side, columns across the top, values in the cells, combined by aggfunc.
The default aggfunc is mean — always state it so totals mean what you intend.
margins=True adds subtotals and a grand total; fill_value replaces missing combinations.
Lists of keys build multi-level pivots; pivot_table() aggregates duplicates while plain pivot() only reshapes, and crosstab() specializes in counts.

🎉 What You've Accomplished

You can now collapse a long transaction log into a boardroom-ready grid — products against regions, quarters against categories — complete with totals and a heatmap. That single skill turns raw data into the summaries decisions are actually made from.

❓ Common Questions at This Stage

When should I use pivot_table() instead of groupby()?

They overlap heavily — a pivot table is essentially a groupby followed by an unstack. Reach for pivot_table() when you want a two-dimensional grid (one field down, another across) and built-in margins. Reach for groupby() when you want a long, tidy result or more complex chained operations.

Why are some cells NaN?

A NaN means that combination of index and column never appeared in the source data — for example, a product that was never sold in a region. Pass fill_value=0 (or another sensible default) so those gaps do not break averages, sums, or plotting.

My totals look wrong — too small?

Almost always the default aggfunc='mean' is the culprit: you are seeing averages where you expected sums. Set aggfunc='sum' explicitly. If you added margins=True, also make sure you are not re-aggregating the "All" row/column downstream.

🔭 Looking Ahead

Pivots summarize a snapshot. Next you'll add the dimension of time — working with datetime indexes, resampling to new frequencies, and smoothing trends — so your summaries can move as well as sit still.

✅ Before the Next Lesson

Build a pivot with two index levels and two aggregations on a dataset of your own.
Add margins=True and confirm the grand total matches df['value'].sum().
Recreate one pivot as a crosstab and compare the outputs.
Write your Learning Journal entry for this lesson.

🌟 Encouragement for the Journey

Pivot tables are where scattered rows finally line up into a picture. The first time your one-line pivot_table() reproduces a report that used to take an afternoon in a spreadsheet, you'll feel the power of pandas click into place. Keep reshaping!