Pivot tables and crosstabs in pandas

Sixteen authored sales rows total 15500, while a report grid with both margins sums to 62000. A pivot tablepivot table: A grid whose cells summarize source rows sharing specified row and column keys, with a declared aggregation and missing-value policy. rearranges long rowslong format: A representation whose row grain is stated and whose measured variables remain in columns, rather than spreading a reporting dimension into separate columns. into a grid. You choose a column whose values become the rows, a column whose values become the columns, a column of numbers and a way to combine them, and every cell of the grid summarises the rows that share its row and column values. Pivot tables and crosstabscrosstab: A table of combinations of categorical labels, commonly showing occurrence counts or shares under a declared denominator. in pandas reproduce the two summaries that the authored spreadsheet-style examples use. pivot_table(index, columns, values, aggfunc) builds the grid, with margins=True for totals and fill_value=0 for empty cells. pd.crosstab(a, b) counts how often each pair of labels occurs, and its normalize option turns the counts into shares of each row, each column or the grand total. For the shown literal-equality criteria and declared types, SUMIFS and COUNTIFS can be expressed as matching pandas operations, and one rule keeps the analysis correct: pivot at the end, for reading, and keep the data itself as long rows.

Each grid cell reduces its matching source rows

The authored 16-row sales fixture spans four branches and four calendar quarters: one sale per row, with its branch, its date and its revenue.

branch,date,revenue
North,2026-01-12,1200
South,2026-01-20,900
East,2026-02-03,700
North,2026-02-25,800
South,2026-03-15,1100
North,2026-04-08,1500
East,2026-04-22,650
West,2026-05-05,400
South,2026-05-19,1000
North,2026-07-02,1300
West,2026-07-21,550
East,2026-08-14,900
South,2026-09-09,1250
North,2026-10-06,1600
West,2026-11-11,700
South,2026-12-01,950

import pandas as pd

sales = pd.read_csv("sales.csv", parse_dates=["date"])  # 16 sales of four branches over one year
sales["quarter"] = "Q" + sales["date"].dt.quarter.astype(str)

grid = sales.pivot_table(
    index="branch",        # one row per branch
    columns="quarter",     # one column per quarter
    values="revenue",      # the numbers in the cells
    aggfunc="sum",         # how to combine the rows of a cell
    fill_value=0,          # a cell with no sales shows 0
    margins=True,          # add totals
    margins_name="Total",  # instead of the default "All"
)
print(grid)

quarter    Q1    Q2    Q3    Q4  Total
branch                                
East      700   650   900     0   2250
North    2000  1500  1300  1600   6400
South    2000  1000  1250   950   5200
West        0   400   550   700   1650
Total    4700  3550  4000  3250  15500

Each cell adds up the sales that share its branch and quarter: North sold 1,200 and 800 in the first quarter, so its Q1 cell is 2,000. West opened in the second quarter and East sold nothing in the last one, and fill_value=0 writes 0 where the grid would otherwise show a missing value. Zero is justified here by a known empty cell in this authored fixture; it is not a universal replacement for unrecorded revenue. margins=True adds a total row and a total column, computed from the rows themselves, and margins_name renames them from the default “All”. Write aggfunc out every time. Its default is the mean, which is rarely what a grid of revenue should show, and a grid of means looks just as plausible as a grid of sums. Quarters of a financial year that does not end in December come from the date column too, for example with dt.to_period("Q-MAR") for a year that ends in March.

A crosstab frequency and its share need a declared population

import pandas as pd

admissions = pd.read_csv("admissions.csv")  # 20 admissions: the course and the mode of study

counts = pd.crosstab(admissions["course"], admissions["mode"], margins=True, margins_name="Total")
print(counts)
print()
shares = pd.crosstab(admissions["course"], admissions["mode"], normalize="index")  # each row adds up to 1
print(shares.round(3))

mode          full-time  online  part-time  Total
course                                           
Commerce              5       1          2      8
Data science          2       5          1      8
Design                3       0          1      4
Total                10       6          4     20

mode          full-time  online  part-time
course                                    
Commerce          0.625   0.125      0.250
Data science      0.250   0.625      0.125
Design            0.750   0.000      0.250

Crosstabs in pandas count occurrences of label combinations. Crosstabs in pandas can also normalize those counts into shares of a declared row, column or whole-table denominator. Called as pd.crosstab(course, mode), it gives the number of admissions for each pair of course and mode of study, and margins=True adds the totals: 20 admissions, 8 of them in Commerce. With normalize="index" every row is divided by its own total, so each row adds up to 1. That reads as “how do the students of each course study?”: 62.5 % of Commerce students study full-time, while 62.5 % of Data science students study online. normalize="columns" and normalize="all" divide by the column totals and by the grand total instead. Pivot tables can count as well, with aggfunc="size", but a crosstab says what it does in one call. Its grand total is the number of rows in which both labels are present, which is a useful count control for this declared complete-label population. It does not by itself prove exact source-row identity.

Matching predicates translate the shown spreadsheet criteria

The source and matching contracts matter more than a superficial syntax translation. Confirm wildcard, blank, case and lookup-mode behaviour before generalising beyond these literal-equality examples. A spreadsheet formula answers one cell, while a group-by answers every combination at once, so one line of pandas replaces a column of copied formulas, and a new branch appears in the result without anyone extending a range.

A report grid and source rows serve different calculations

The shown pivot is a reporting representation. Keep the data long, one row per sale, while you clean, join, filter and summarise it, and usually pivot at the reporting boundary: a grid has one column per quarter, and every step that follows has to know those columns. When data arrives already pivoted, melt turns the grid back into long rows. The representation also defines what later calculations can recover: a cell has no individual sale identifier or within-cell distribution. Keep the transaction source for later joins or statistics that need those observations. A pivot that goes into a report should say in its title what each cell holds, such as “revenue, sum, by branch and quarter”, because a grid of sums and a grid of means look exactly alike.

Practise the reading core

• In branch_quarter.py, request a mean; compare each populated cell with the sum report.
• In admissions_crosstab.py, switch the denominator to columns; name the changed question.

Pivot tables that match the spreadsheet cell by cell, with the traps reproduced Pro: opens with a Pro, Premium or Ultimate pass

Reports or analysis tables: summing a pivot, going back to long rows, and shares Pro: opens with a Pro, Premium or Ultimate pass

Interview questions

Key takeaways

• pivot_table(index, columns, values, aggfunc="sum") builds the grid; margins=True adds totals and fill_value=0 fills empty cells.
• Always pass aggfunc: the default is the mean.
• pd.crosstab(a, b) counts pairs of labels, and normalize="index" gives shares that add up to 1 per row.
• With the shown literal-equality criteria and declared types, SUMIFS, COUNTIFS and AVERAGEIFS map to grouped summaries of matching rows.
• Pivot at the end, for reading; keep the data as long rows while you analyse it.