Stop 03 · Data Manipulation & VisualizationTopics · 6/10

GroupBy & Aggregation

In short

Which city orders the most? Which product returns the most? Average spend per user? Almost every useful question about data starts with 'per something', and groupby answers it.

Splitting data into groups and computing aggregate stats per group — the pandas equivalent of SQL's GROUP BY, used constantly in EDA and feature engineering.

The purple blob sorts a heap of clothes into red, blue and white baskets.

Think of it like this

The dhobi sorting laundry

A huge pile of clothes arrives. The dhobi first sorts them into baskets by colour, washes each basket the right way, then stacks the folded results together. Split into groups, apply a calculation, combine the answers. That is exactly how groupby works.

Watch it happen

sales.groupby('city')['amount'].sum()

All rows

Split

Apply sum

Combine

1/4Five orders: Pune 200, Delhi 450, Pune 300, Delhi 150, Goa 500.

The big ideas

What you need to know

Split, apply, combine

Every groupby does these three steps. Picture them and the output shape stops surprising you.

Named aggregations

.agg(total=('amount', 'sum'), orders=('amount', 'count')) gives tidy, readable column names.

Group by several columns

groupby(['city', 'month']) gives one row per city-month pair. Great for trends per segment.

transform keeps the shape

transform('mean') returns a value for every original row, perfect for features like 'user's average spend'.

See it in code

Walk through the code

python

1import pandas as pd2 3sales = pd.DataFrame({4    "city": ["Pune", "Delhi", "Pune", "Delhi", "Goa"],5    "amount": [200, 450, 300, 150, 500],6})7 8print(sales.groupby("city")["amount"].sum())   # Delhi 600, Goa 500, Pune 5009 10summary = sales.groupby("city").agg(11    total=("amount", "sum"), orders=("amount", "count"), avg=("amount", "mean"),12).reset_index()13 14sales["city_avg"] = sales.groupby("city")["amount"].transform("mean")15print(summary)16print(sales)

1/4Five orders across three cities.

agg makes a summary table with one row per city. transform adds the city average back onto every order, a common feature engineering trick.

Save yourself the pain

Common mistakes

Oops

Using agg when you want a value per original row, then struggling to merge it back.

Do this instead

Use transform. It returns a result aligned with the original rows.

Oops

Forgetting that groupby drops rows whose key is NaN by default.

Do this instead

Pass dropna=False if missing keys should form their own group.

Your mission

Find the best tipping day

~15 min

Quick check

You want a new column with each customer's average order value on every one of their order rows. Which method fits?

Go deeper

1Practice `.groupby()` with multiple aggregation functions.
2Learn `.agg()` with custom functions for more complex summaries.
3Use groupby to answer a real analytical question on a dataset.