groupby is the pandas operation you will use most in analysis. Once you understand the idea behind it, the many variations become easy.
The idea: split, apply, combine
- Split the rows into groups by one or more keys.
- Apply a calculation to each group.
- Combine the results into a new table.
The examples use a fictional cloud-kitchen brand, TiffinTown, with an orders DataFrame: order_id, kitchen, city, dish, amount, rating, order_date.
One key, one summary
orders.groupby("city")["amount"].sum()The result is a Series indexed by city. Add .reset_index() to get a normal DataFrame, or pass as_index=False to groupby.
Several summaries with named aggregation
summary = orders.groupby("city").agg(
orders=("order_id", "count"),
revenue=("amount", "sum"),
avg_order=("amount", "mean"),
avg_rating=("rating", "mean"),
).sort_values("revenue", ascending=False)This is the cleanest way to build a report table: every output column gets a readable name.
Several keys
orders.groupby(["city", "kitchen"], as_index=False)["amount"].sum()To turn the second key into columns, use unstack() or pivot_table.
size() vs count()
size()counts rows per group, including rows with missing values.count()counts non-missing values per column.
If rating has gaps, groupby("city")["rating"].count() is the number of rated orders, not all orders.
transform: keep every row
agg returns one row per group. transform returns a result aligned to every original row:
orders["city_avg"] = orders.groupby("city")["amount"].transform("mean")
orders["vs_city_avg"] = orders["amount"] - orders["city_avg"]
orders["share_of_kitchen"] = orders["amount"] / orders.groupby("kitchen")["amount"].transform("sum")Use it for "compared with its group" calculations.
filter: keep whole groups
busy = orders.groupby("kitchen").filter(lambda g: len(g) >= 100)Keeps only kitchens with at least 100 orders: the pandas version of SQL's HAVING.
Top N per group
(orders.groupby(["city", "dish"], as_index=False)["amount"].sum()
.sort_values(["city", "amount"], ascending=[True, False])
.groupby("city").head(3))Rank within a group
orders["rank_in_city"] = orders.groupby("city")["amount"].rank(method="dense", ascending=False)Grouping by time
orders.groupby(orders["order_date"].dt.to_period("M"))["amount"].sum()
orders.groupby(pd.Grouper(key="order_date", freq="W"))["order_id"].count()Common mistakes
- Forgetting
reset_index()and then struggling to merge or plot the result. - Using
count()when you meantsize(), or the other way round. - Calculating an "average of averages" by grouping twice when the groups have different sizes.
- Grouping on a column with inconsistent text ("Pune", "pune") and getting split groups. Clean first.
Practise
Our groupby set starts with single summaries and builds up to transform and filter, and practice test 1 mixes groupby with selecting and filtering.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: