pandas · Chapter 14 of 22
Pivot tables and crosstab
By the end of this chapter you can turn a long table into a grid with pivot_table (rows from one column, columns from another, a statistic in every cell), fill the empty cells, add totals at the edges, count combinations and their shares with crosstab, say when pivot is enough and why it refuses repeated pairs, and get back to a long table. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Grouping and combining
- 4 tasks
- pandas
- In the all labs plan
The first 2 chapters of pandas are free. This one comes with the all labs plan.
Why you need this
Harpreet's monthly sheet is a grid: a row for every month, a column for every category, the rupees in the cells and the totals at the edges. It is how finance reads a year at a glance. Harpreet also thinks cash on delivery comes back more often than the other payments, and wants to see what happens to each payment type's lines. Shabnam wants COD's return rate against UPI's in two lines she can read on her phone. And Gopal wants the pieces of each category he ships to each city, to stock the right courier boxes.
Chapter 12 already computes the numbers: a groupby on two keys. But it gives them long, one row for every pair:
import pandas as pd
lines = pd.read_csv("/workspace/data/orders_site.csv").drop_duplicates()
lines = lines[lines["customer_id"] != "TEST"].dropna(subset=["qty"]).copy()
lines["qty"] = lines["qty"].astype(int)
lines["line_rs"] = lines["price_rs"] * lines["qty"]
lines["code"] = lines["sku"].str.strip().str.upper().str.slice(3, 6)
lines["month"] = lines["placed_at"].str.slice(0, 7)
sold = lines[lines["status"] != "cancelled"]
by_two = sold.groupby(["month", "payment"])["line_rs"].sum()
print(len(by_two))
print(by_two.head(6))
48
month payment
2025-10 COD 1830326.7
Card 2065344.3
NetBanking 301599.6
UPI 5378308.1
2025-11 COD 1384101.0
Card 1979630.0
Name: line_rs, dtype: float64
48 rows for 12 months and 4 payments. To compare March's UPI with April's, you scroll and count. A grid puts the same 48 numbers in 12 rows of 4, where a month is a row and a payment a column.
The month here is text: placed_at is 2025-10-01 11:55:03, and its first 7 characters, sliced with chapter 10's .str.slice(0, 7), are 2025-10. Real dates are chapter 18; text months sort in time order because the year comes first.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
month_category.py: revenue by month and category, with totals
Write month_by_category(lines) in month_category.py. lines is the website's lines as read_csv reads orders_site.csv, with the exact duplicate, test and cancelled lines and the lines with no qty dropped, and two more columns: line_rs (price_rs times qty) and category (saree, dupatta, stole or kurta). placed_at is text such as 2025-10-01 11:55:03, and its first 7 characters are the month, 2025-10.
Return a pivot table (a DataFrame, made with pivot_table) with one row for each month that has lines, labelled by the month as text like 2025-10, in time order, and a last row labelled Total; and one column for each category in the lines, from A to Z, and a last column Total. Each cell is the sum of line_rs for that month and category, and 0 where that month has no lines of that category; the Total row and column are the sums of the rest. The table it was given is not changed.
The Check tries the website's lines and a table of 7 lines in 3 months, where some months have no lines of a category.
- Task 2
status_shares.py: what happens to each payment type's lines
Write status_shares(lines) in status_shares.py. lines is the website's lines as read_csv reads orders_site.csv, with the exact duplicate and test lines dropped and every status kept (cancelled, delivered, returned, shipped). A few lines have no payment.
Return a DataFrame with one row for each payment type, labelled from A to Z, and one column for each status in the lines, from A to Z. Each cell is the share of that payment type's lines that have that status, a decimal between 0 and 1 (0.0 where none), so every row adds up to 1. Lines with no payment are left out. pd.crosstab is the tool for it.
The Check tries the website's lines, a table of 7 lines, and the same 7 lines with one payment blanked.
- Task 3
return_rates.py: COD's return rate against UPI's
The starter keeps three lines: path = "/workspace/data/orders_site.csv", first = "COD" and second = "UPI". Keep them as they are. Read the CSV file at path with read_csv, and count its lines as they are, with nothing dropped. Then print two lines, one for the payment type first and one for second, each exactly like:
COD: 120 of 1500 lines returned (8.0%)
that is: the payment type, a colon and a space, the number of its lines whose status is returned, the word of, the number of its lines, the words lines returned, and the share of returned lines as a percentage with one decimal place in brackets (Python's format :.1% prints 0.08 as 8.0%). The numbers above are made up: yours come from the file. pd.crosstab with its totals gives both numbers of every payment type.
The Check also runs the file with first and second changed to "Card" and "NetBanking", and with the path line changed to a small CSV file of 7 lines, first changed to "Card" and second to "COD"; there Card has no returned lines, so its line says 0 of 2 lines returned (0.0%). Print what the table says, never typed-in numbers.
- Task 4 · interview question
Interview: city_units.py, pivot or pivot_table
Gopal wants the pieces of each category he ships to each city. Harpreet's city_units(lines) stops with ValueError: Index contains duplicate entries, cannot reshape. Fix it so it returns a DataFrame with one row for each city, labelled from A to Z, and one column for each category in the lines, from A to Z; each cell is the sum of qty for that city and category, as whole numbers, and 0 where none were sold.
lines is the same table as in Task 1, with the cities cleaned to the 16 names (chapter 10) and qty as whole numbers. The Check also tries a table of 7 lines in 3 cities, where some cities have no lines of a category.
Devika will ask: why did pivot raise "Index contains duplicate entries" where pivot_table worked?
What you will learn
- pivot_table: index (the rows), columns, values and aggfunc, a groupby on two keys laid out as a grid.
- fill_value: what an empty cell holds, often 0.
- margins: a Total row and column, and margins_name to name them.
- Several statistics: a list for aggfunc.
- pd.crosstab: counts of each pair, and their shares with normalize.
- pivot against pivot_table: pivot only moves values, and refuses repeated pairs.
- Back to long: stack and reset_index.
More in Grouping and combining
Build "Pivot tables and crosstab" yourself
Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of pandas are free. This one comes with the all labs plan.