Introduction
"Total sales per city", "average score per class", "number of orders per customer": most summaries are about groups. In pandas they are all one pattern, groupby, described by its three steps: split the rows into groups by a key, apply a function to each group, and combine the results into a new table. Once that pattern is clear, every grouped question reads the same way.
How it works
df = pd.DataFrame({
"city": ["Dhaka", "Paris", "Dhaka", "Lima", "Paris", "Dhaka"],
"amount": [30, 50, 20, 10, 25, None],
})
df.groupby("city")["amount"].sum().tolist() # [50.0, 10.0, 75.0]: Dhaka, Lima, Paris
df.groupby("city")["amount"].mean().tolist() # [25.0, 10.0, 37.5]
df.groupby("city").size().tolist() # [3, 1, 2]
df.groupby("city")["amount"].count().tolist() # [2, 1, 2]
The result is indexed by the group keys, sorted by default (sort=False keeps the order of first appearance). Missing values inside a group are skipped by sum, mean, min and max, exactly as for a whole column. That is why there are two different counts: size counts the rows in each group, and count counts only the values that are present. Dhaka has three rows, but only two amounts.
agg applies several functions at once: df.groupby("city")["amount"].agg(["sum", "mean", "count"]) gives one column per function. Named aggregation chooses the result names: df.groupby("city").agg(total=("amount", "sum"), orders=("amount", "count")). Grouping by a list of columns, groupby(["city", "year"]), makes one group per combination. Rows whose key itself is missing are left out of every group, unless dropna=False. Add .reset_index(), or pass as_index=False, to get the keys back as an ordinary column.
Sometimes you want a group result on every row, for example each order's share of its city's total. transform does that: it computes per group and gives the result back with the original length and index.
df["city_total"] = df.groupby("city")["amount"].transform("sum")
df["share"] = df["amount"] / df["city_total"]
filter keeps or drops whole groups: df.groupby("city").filter(lambda g: len(g) >= 2) keeps the rows of cities with at least two rows.
Example
# Split, apply, combine: group the rows by city and summarise each group.
cities = ["Dhaka", "Paris", "Dhaka", "Lima", "Paris", "Dhaka"]
amounts = [30, 50, 20, 10, 25, None]
groups = {}
for city, amount in zip(cities, amounts):
groups.setdefault(city, []).append(amount) # split
for city in sorted(groups): # groupby sorts its keys
values = groups[city]
present = [v for v in values if v is not None] # apply
print(city, "size", len(values), "count", len(present), "sum", sum(present), "mean", sum(present) / len(present))
# transform("sum"): every row gets its own group's total, so shares are easy
totals = {c: sum(v for v in vals if v is not None) for c, vals in groups.items()}
print("share:", [None if a is None else round(a / totals[c], 2) for c, a in zip(cities, amounts)])Line by line
- Split: each city's amounts are collected into its own list.
- Apply and combine: each group, in sorted order, is summarised; Dhaka's missing amount counts towards size but not count, sum or mean.
- Transform: each row is divided by its own city's total, giving a share for every row; the missing amount gives a missing share.
Expected result
Dhaka size 3 count 2 sum 50 mean 25.0
Lima size 1 count 1 sum 10 mean 10.0
Paris size 2 count 2 sum 75 mean 37.5
share: [0.6, 0.67, 0.4, 1.0, 0.33, None]
In the real world
groupby is the pandas form of SQL's GROUP BY and of a spreadsheet's pivot table, and it underlies most business reporting: revenue per region, churn per plan, average delivery time per courier. transform is used for features in machine learning, such as each customer's spending compared with their country's average, and filter for removing groups too small to trust.
Common mistakes
- Confusing size and count when a column has missing values.
- Expecting the original row order after groupby, which sorts the keys by default.
- Forgetting that rows with a missing key disappear from the groups.
- Using apply for something agg or transform does, which is slower and harder to read.
Best practices
- Name aggregated columns with named aggregation, so reports are self-explaining.
- Use reset_index() when the result will be joined or saved.
- Check group sizes before trusting a group mean.
Summary
groupby splits rows by key, applies a function to each group and combines the results, sorted by key. sum, mean, min and max skip missing values; size counts rows and count counts present values; agg gives several results at once. transform returns one result per row, and filter keeps or drops whole groups.
Quick check
Key takeaways
- groupby splits rows by key, applies a function to each group and combines the results, with the keys sorted.
- size counts rows; count counts values that are present.
- transform keeps one result per row; filter keeps or drops whole groups.
Practice problems for this lesson
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.