Stop 06 · Python for Data AnalysisTopics · 7/12

GroupBy & Aggregation in Pandas

In short

'Revenue by city', 'orders per month', 'average rating per product'. Most business questions are 'something per something'. That's groupby, and it's the Pandas version of a pivot table.

Pandas' equivalent of SQL's GROUP BY — splitting data into groups, applying an aggregate function, and combining results back into a summary table.

The jaadu sorts mangoes, bananas and guavas into three baskets at a fruit stall.

Think of it like this

Sorting the fruit stall

A fruit seller dumps the morning delivery on the cart, then sorts it into baskets: mangoes, bananas, guavas. Then they count or weigh each basket. Split into groups, apply a sum or count, combine the answers into one small table. That's groupby.

Watch it happen

Split, apply, combine

All rows

Split

Apply

Combine

1/4Six orders: Pune 200, Delhi 500, Pune 300, Delhi 100, Goa 400, Pune 100.

The big ideas

What you need to know

groupby("col")

Picks what to split by. You can group by several columns, like city and month together.

Pick an aggregate

sum, mean, median, count, nunique, min, max. count ignores blanks, size counts all rows.

Named aggregation

agg(revenue=("amount", "sum"), orders=("order_id", "count")) gives clean column names in one step.

reset_index()

Turns the group labels back into normal columns, which is easier to sort, merge or chart.

See it in code

Walk through the code

python

1summary = (2    orders3    .groupby(["city", "month"])4    .agg(5        revenue=("amount", "sum"),6        orders=("order_id", "count"),7        avg_order=("amount", "mean"),8    )9    .reset_index()10    .sort_values("revenue", ascending=False)11)12summary.head()

1/3Split rows into groups for every city and month combination.

One readable chain gives revenue, order count and average order value for each city and month.

Save yourself the pain

Common mistakes

Oops

Averaging the per-city averages to get an overall average.

Do this instead

Cities have different order counts. Compute the overall mean from the raw rows, or weight by count.

Oops

Using count when you meant unique customers.

Do this instead

count counts rows. nunique counts distinct values, like how many different customers ordered.

Your mission

Rebuild a pivot table in code

~25 min

Quick check

You want how many different customers ordered in each city. Which aggregate fits?

Go deeper

1Practice a groupby().agg() call summarizing sales by category and month.
2Learn to apply multiple aggregate functions in a single groupby call.
3Compare the output directly to the same query written in SQL.