Level 2 · Revenue per category, several ways

Summing into a grid

Counting is one use of pivot_table; summing a money column is the other everyday one. Only the answer to “what do I calculate?” (values) and “which operation?” (aggfunc) change:

spend = marketing_spend.assign(year=marketing_spend["spend_date"].dt.year)
spend.pivot_table(index="year", columns="channel", values="amount", aggfunc="sum").round(2)
# channel      email       paid     social
# year
# 2023      28989.02  253948.40   99253.03
# 2024      32876.47  294047.40  114970.96
# 2025      38322.22  340778.33  131540.77

The year comes from the date with .dt.year. The year labels are integers, so a column of a year-across grid is selected as grid[2025], not grid["2025"].

Making the columns first

A pivot can only use columns that exist. Revenue per category needs things the order lines don’t have yet: the category (in products), the order’s date and status (in orders) and the revenue itself. So the work happens before the pivot: merge, filter, compute the measure, then pivot.

Several functions at once

aggfunc also takes a list. Each function becomes its own block of columns:

p = items.pivot_table(index="category", values="revenue",
                      aggfunc=["sum", "median", "count"])
p.columns.tolist()
# [('sum', 'revenue'), ('median', 'revenue'), ('count', 'revenue')]

The column labels now have two levels: the function on top, the measured column underneath. When this game shows or checks such a table, the levels are joined with an underscore, so you see sum_revenue, median_revenue and count_revenue. In your own code you can do the same with p.columns = ["_".join(c) for c in p.columns], or select a block with p["median"].

On all three years of completed orders, Decor has 7,407 lines with a median of $44.98, and Furniture only 1,450 lines with a median of $670.65. Sum alone would show two big categories; the median and the count show they are big in completely different ways.

Why median next to sum

The median is the middle line: half the lines are below it, half above. Unlike the mean, a handful of $10,000 business orders barely moves it, so it describes a typical purchase. Count tells you how much evidence each figure rests on. A total, a typical value and a count together answer most “how is this category doing?” questions.

Common mistakes

• Pricing lines with products["unit_price"]. That’s the current list price; the order line has the price the customer actually paid.
• Forgetting aggfunc. The default is the mean, so each cell shows an average line.
• Filtering on status or year after the pivot. The pivot no longer has those columns; filter first.
• Expecting plain column names from a list of functions. They have two levels; join or select them.