RamayaiAI Academy Apply Now
Lesson 15 · Rebecca, Mumbai

Pivot Tables & Crosstabs

You've been at the payments processor 6 months. Your manager asks: "Summarize revenue by merchant category and payment method. How does it break down by city?" In Excel, you'd drag columns into a pivot table. In pandas, you'll do it in one line.

  • Master pivot_table(): reshape data with index, columns, values, and aggfunc
  • Build multi-level aggregations (revenue AND transaction count)
  • Add row and column totals with margins=True
  • Use crosstab() for frequency counts (payment method × city)
  • Normalize crosstabs to answer: "What % of UPI transactions come from Mumbai?"
Sample data: merchant_settlements.csv (~200 rows, 3 months, 4 countries)
Download CSV →

1. Pivot = Excel's Most-Used Feature, in One Line

Excel's pivot table is the go-to tool for any analyst. You drag fields into rows and columns, and Excel summarizes the data. Pandas does the same with pivot_table()—except it's one function call, not 20 clicks.

Here's the outcome first (picture): a table showing revenue (gross_amount) by merchant category (rows) × payment method (columns).

Pivot table: Revenue by Category × Payment Method
Heatmap showing revenue in rupees by merchant category and payment method
What this shows: Every row is a category (Food, Travel, Retail, Utilities, Telecom). Every column is a payment method (UPI, Card, Wallet, NetBanking, BankTransfer). Each cell is the total gross_amount for that category–method pair. Travel dominates across all methods; Retail is strong on Cards.

Now, here's how you made it:

Create a pivot table
The magic: Three parameters shape the entire table: index (rows), columns (columns), values (what to sum). aggfunc='sum' means "add up all gross_amounts for each category–method pair."

2. pivot_table() Anatomy: Index, Columns, Values, Aggfunc

Every pivot table has four key parts. Understanding them is the entire game.

pd.pivot_table(df, index='merchant_category', columns='payment_method', values='gross_amount', aggfunc='sum')
index: Which column becomes rows? Answer: merchant_category. Each unique category gets a row.
columns: Which column becomes columns? Answer: payment_method. Each unique method gets a column.
values: What do we put in each cell? Answer: gross_amount. (Could also be settlement_id, fee, net_amount, etc.)
aggfunc: How do we combine values when there are duplicates? 'sum' adds them. 'count', 'mean', 'max' also work.

Variation: Revenue by City (rows) × Category (columns)

Same idea, different question: "How much revenue did each city contribute, broken down by category?"

Pivot by city and category
Same structure, different shape: Now rows are cities (Mumbai, Bangalore, Nairobi, Jakarta, Manila), and columns are categories. This pattern is the same whether your merchants are in Mumbai, Nairobi, or Manila—the reshaping logic is identical.

3. Multiple Aggregations: Revenue AND Count

One value at a time is useful, but often you want two numbers per cell: the sum AND the count. "How much revenue, and how many transactions?"

Use aggfunc as a dictionary mapping columns to functions.

Aggregate revenue and count in one pivot

The result is a two-level column table: the top level is the column name (gross_amount, settlement_id), and the second level is the payment_method (UPI, Card, etc.).

Two-level pivot: Revenue (₹) and Transaction Count
Heatmap showing transaction counts by merchant category and payment method
Accessing multi-level columns: Use double-bracket notation: pivot_multi['gross_amount']['UPI'] gives you the revenue column for UPI. pivot_multi['settlement_id']['Card'] gives you the transaction count for Card. This structure is powerful for side-by-side comparisons.

4. margins=True: Row and Column Totals

Adding one parameter gives you subtotals and grand totals. Set margins=True.

Add row and column margins
Pivot table with margins: subtotals and grand total
Category UPI Card Wallet NetBanking BankTransfer All Food 89.2K 124.5K 67.3K 95.8K 103.2K 480.0K Travel 156.4K 198.7K 112.9K 134.2K 167.8K 770.0K Retail 72.1K 143.2K 89.5K 76.3K 91.4K 472.5K Utilities 98.6K 112.4K 56.2K 78.9K 84.3K 430.4K Telecom 134.8K 156.9K 93.4K 121.6K 145.2K 651.9K All 551.1K 735.7K 429.3K 506.8K 591.9K 2.81M Amber row/column: added by margins=True. Last cell is the grand total (₹2.81M)
What margins show: The rightmost column (All) sums each category across all payment methods. The bottom row (All) sums each method across all categories. The cell in the bottom-right corner is the grand total (₹2.81M). Use this to spot which category or method dominates overall.

5. crosstab(): Frequency Counts (Not Sums)

crosstab() is a specialized pivot table for counting. Instead of summing revenue, you count how many transactions occur at each intersection.

Typical use: "How many settlements happened in each city × payment method combination?"

Create a crosstab (frequency table)
Crosstab: Settlement Count by City × Payment Method
Heatmap showing settlement counts by city and payment method
What this shows: Each cell is the count of settlements. Mumbai–BankTransfer has 31 transactions; Mumbai–UPI has 14. The color gradient (pale → dark purple → amber) shows which cells have more activity. You can spot patterns: BankTransfer dominates in India (Mumbai, Bangalore), while Card and Wallet are stronger in Kenya (Nairobi).

6. Normalizing Crosstabs: Rows vs. Columns vs. All

Raw counts are useful, but percentages often tell a clearer story. "What percentage of BankTransfer transactions come from Mumbai?" (row-wise) vs. "What percentage of Mumbai transactions use BankTransfer?" (column-wise).

Add normalize parameter to crosstab():

Three normalization options

What each tells you:

normalize='index' (Row %)

"What % of Mumbai's transactions use each method?"

CityUPICardWallet
Mumbai12.5%16.1%22.3%
Bangalore14.3%17.6%22.7%
Nairobi17.9%23.2%20.5%

Each row sums to 100%. Read rows to compare method adoption across cities.

normalize='columns' (Column %)

"What % of Card transactions come from each city?"

CityUPICardWallet
Mumbai21.2%19.3%28.6%
Bangalore25.8%22.5%30.9%
Nairobi30.3%27.9%26.1%

Each column sums to 100%. Read columns to compare city contribution to each method.

normalize='all' (Total %)

"What % of ALL transactions are Mumbai–Card?"

CityUPICardWallet
Mumbai5.1%6.5%8.1%
Bangalore6.2%7.6%8.3%
Nairobi7.2%9.4%7.5%

Table sums to 100%. Each cell is its share of all transactions.

When to use which?

  • Row normalization: "How do method preferences differ by city?" Compare across rows to spot geographic variation.
  • Column normalization: "How is each method distributed geographically?" Compare down columns to spot concentration.
  • All normalization: "What fraction of the overall business is this segment?" Answer when each cell is equally important.
Quick tip: If you're preparing a deck for your manager, choose the normalization that answers their question. "Which payment method should we focus on?" → column %. "Which cities are underserving us?" → row %.
0/3
Quiz Progress
Q1: What's the difference between pivot() and pivot_table()?
pivot() is for Excel files; pivot_table() is for pandas DataFrames
pivot() errors on duplicate index–column pairs; pivot_table() aggregates them using aggfunc
They're identical; pivot_table() is just newer
pivot_table() requires margins=True; pivot() doesn't
Q2: What does margins=True add to a pivot table?
A legend showing the color gradient
Row and column subtotals, plus a grand total in the bottom-right
Percentage normalization for all cells
A border around the table
Q3: What's the difference between normalize='index' and normalize='columns' in crosstab()?
They're the same; both normalize the entire table
index makes rows sum to 100%; columns makes columns sum to 100%
columns is faster; index is for small tables
They can't be used together
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Pivot tables solve 80% of reporting questions

Join the Best AI Program: The AI-Era Analyst — 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Analyst →