pandas groupby and Aggregation: A Practical Guide

Hi, I'm Mathew. If I had to pick one pandas tool that turns raw rows into real answers, it's groupby. "Revenue per country", "orders per customer", "average per month": they're all the same idea. Let's walk through it with a cleaned slice of a real online retail dataset.

Quick note on size: this lesson uses a small extract (1,394 cleaned rows, all from December 2010), so the focus is on the technique, not on big business conclusions.

Setup

import pandas as pd

df = pd.read_csv("online_retail_sample.csv")
df = df.dropna(subset=["CustomerID"]).drop_duplicates()
df = df[(df["Quantity"] > 0) & (df["UnitPrice"] > 0)]
df["TotalPrice"] = df["Quantity"] * df["UnitPrice"]

We drop rows with no customer, drop duplicates, remove returns and zero prices, then create a TotalPrice column for each line.

1. Split, apply, combine

groupby follows the split-apply-combine pattern. The key thing to know: nothing is computed until you aggregate.

by_country = df.groupby("Country")
print(type(by_country))   # DataFrameGroupBy, no numbers yet

revenue_by_country = (
    df.groupby("Country")["TotalPrice"]
      .sum()
      .sort_values(ascending=False)
)

The top of the result:

Country
United Kingdom    28259.30
Norway             1919.14
France              855.86
EIRE                555.38
Australia           358.25

Read it as a recipe: group by Country, pick TotalPrice, sum it, sort it.

2. Several aggregations at once

Pass a list to agg:

df.groupby("Country")["TotalPrice"].agg(["sum", "mean", "count"])

That works, but the column names are generic. Named aggregation is the version I use daily, because you choose the output column names:

named = df.groupby("Country").agg(
    total_revenue=("TotalPrice", "sum"),
    avg_line_total=("TotalPrice", "mean"),
    num_invoices=("InvoiceNo", "nunique"),
)
named.sort_values("total_revenue", ascending=False).head(5)

Country	total_revenue	avg_line_total	num_invoices
United Kingdom	28259.30	22.63	76
Norway	1919.14	26.29	1
France	855.86	42.79	1
EIRE	555.38	26.45	2
Australia	358.25	25.59	1

Notice the three aggregations use different source columns. Also, be careful with the word "average": mean of TotalPrice is the average line item, not the average invoice. (The notebook calls this column avg_order_value; I renamed it here because each invoice has several lines.) To get a true average order value, you'd sum per invoice first.

3. Grouping by more than one column

Pass a list of columns and you get a multi-level index:

df["InvoiceDate"] = pd.to_datetime(df["InvoiceDate"])
df["Month"] = df["InvoiceDate"].dt.to_period("M")

monthly_country = df.groupby(["Month", "Country"])["TotalPrice"].sum()

That's a long, stacked result. To make it a readable grid, unstack pivots the inner level into columns:

monthly_country.unstack().fillna(0).round(2)

Since this extract only covers one month (2010-12), the grid is a single row. With a full year you'd get one row per month and one column per country, ready for a chart.

4. transform: put the group stat back on every row

agg collapses each group to one row. transform does the same calculation but returns a result the same length as your original data, so you can compare each row with its own group:

df["CountryAvgOrder"] = df.groupby("Country")["TotalPrice"].transform("mean")
df["AboveCountryAvg"] = df["TotalPrice"] > df["CountryAvgOrder"]

df["AboveCountryAvg"].value_counts()
# False    1069
# True      325

So 325 of the 1,394 lines are worth more than their own country's average. The pattern "compare each row with its group" is useful for flagging outliers, calculating share of group total, or filling missing values with a group mean.

5. pivot_table and crosstab

pivot_table is groupby plus unstack in one call, spreadsheet style:

pivot = df.pivot_table(
    values="TotalPrice", index="Month", columns="Country",
    aggfunc="sum", fill_value=0,
)

crosstab is the one to reach for when you want counts of combinations:

top_countries = revenue_by_country.head(4).index
subset = df[df["Country"].isin(top_countries)]
pd.crosstab(subset["Country"], subset["Month"])

In this extract that shows 1,249 United Kingdom lines, 73 Norway, 21 EIRE and 20 France for 2010-12.

Which tool should I use?

• One summary number per group: groupby(...).agg(...)
• Clean column names for several summaries: named aggregation
• Group stat on every original row: transform
• Spreadsheet-style grid of a value: pivot_table
• Counts of category combinations: crosstab

Common mistakes

1. Forgetting to aggregate. A bare groupby object prints nothing useful.
2. Reading a mean without its count. Netherlands shows a mean of 96.30, but it's based on only 2 rows. Always look at count next to mean.
3. Mixing up line items and orders, as above.

Try it yourself

The best way to lock this in is to change one thing at a time. Swap Country for StockCode or CustomerID and rerun the same named aggregation. Add a second metric such as max or std. Then group by two columns and unstack the result. Each small change shows you how the pieces fit together, and that's far more memorable than reading about it. Once groupby feels natural, nearly every later topic, from cohort analysis to feature engineering, becomes a variation on the same split-apply-combine idea.

A final tip: when a result looks surprising, check count and nunique before you believe it. Most "interesting" findings in small groups turn out to be small samples.

Get the full notebook, dataset and quiz

The lesson on the site has the full runnable notebook, the dataset, and a quiz so you can check you've really got it.

• Full lesson: Pandas groupby and Aggregation
• Watch the video walkthrough: https://www.youtube.com/watch?v=Vp3kePFGE-w
• Subscribe for the next lesson in the series: https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1

Everything is free. See you in the next one!