Groupby and Aggregation
The owner's favourite question is "how is each branch doing?" That is not one number but one number per branch. Filtering three times and summing three times would work, but groupby does the whole thing in one line, for three branches or three hundred.
The rule
Split by a key, apply a summary, combine into one row per group.
sales.groupby("branch")["copies"].sum()
# branch
# Chennai 10
# Delhi 9
# Pune 7Read it in three moves:
- Split:
groupby("branch")sorts the rows into piles, one per branch. - Apply:
["copies"].sum()totals the copies inside each pile. - Combine: the answers come back as a Series whose index is the branch names, sorted alphabetically.
Common summaries: sum, mean, count, min, max, median, and size, which counts rows per group:
sales.groupby("branch").size() # Chennai 2, Delhi 3, Pune 3For several summaries at once, use agg:
sales.groupby("branch").agg(
total_copies=("copies", "sum"),
avg_copies=("copies", "mean"),
revenue=("revenue", "sum"),
)| branch | total_copies | avg_copies | revenue |
|---|---|---|---|
| Chennai | 10 | 5.00 | 1720 |
| Delhi | 9 | 3.00 | 3450 |
| Pune | 7 | 2.33 | 1790 |
Each name=("column", "summary") pair makes one output column, so the result has tidy names.
Group by two keys by passing a list: sales.groupby(["branch", "genre"])["copies"].sum() gives one row per branch-genre pair that actually occurs.
The shortcut
Turn the question into the pattern "[summary] of [column] per [key]". The key goes in groupby, the column in the brackets, the summary at the end. Any English question with "per", "each" or "by" is a groupby.
Fill the pattern
Summary: total. Column: revenue. Key: genre.
The trap is confusing count and size. size counts every row in the group; count counts only the non-missing values in a column. They differ as soon as your data has gaps.
Drills
Answer fast — trust the rule.
What does sales.groupby("genre")["copies"].sum() give for fiction?
What is the index of sales.groupby("branch")["price"].mean()?
Which line answers "average copies per sale in each genre"?
How many rows does sales.groupby(["branch", "genre"])["copies"].sum() have?
Remember
- Split by a key, apply a summary, combine into one row per group.
- Pattern: "[summary] of [column] per [key]".
sizecounts rows;countcounts non-missing values.