groupby and aggregation

01The problem

Kolanut's managing director asks the questions every manager asks:

"Which products bring in the most money? How did each month go? And in HR: which departments are we losing people from, and what do we pay at each level?"

Every one of those is split, apply, combine: split the rows into groups (by product, month or department), apply a calculation to each group (sum, average, count), and combine the results into one small table. In Excel that's a pivot table. In pandas it's groupby, and it's the single most useful tool in this course.

02The concept

The pattern

orders.groupby("product_id")["revenue"].sum()

Read it left to right: take orders, group by product_id, take the revenue column, and sum it within each group. The result is a Series with one row per product.

Common calculations: .sum(), .mean(), .median(), .min(), .max(), .count() (non-empty values), .nunique() (distinct values) and .size() (rows per group, including blanks).

Sorting and the top N

Add .sort_values(ascending=False) to rank the groups, and .head(5) for the top five. .idxmax() gives the label of the largest group directly.

Several measures at once: named aggregation

orders.groupby("product_id").agg(
    revenue=("revenue", "sum"),
    lines=("order_id", "count"),
    avg_quantity=("quantity", "mean"),
)

Each line is new_name=(column, calculation). You get a tidy table with exactly the columns you named.

Grouping by more than one column

orders.groupby(["year", "discount_pct"])["revenue"].sum() gives one row per combination. Add .unstack() to turn the second level into columns, which reads like a pivot table.

Back to an ordinary table: reset_index()

The group labels become the result's index. .reset_index() turns them back into a normal column, which you'll want before merging, charting or saving.

Shares of a total

Divide each group by the total: by_product / by_product.sum(). To put each row's group total back on every row, use transform:

orders["product_total"] = orders.groupby("product_id")["revenue"].transform("sum")

That's useful for "what share of its product's revenue is this line?"

03Example

Set up revenue and dates as in lesson 5, then rank products and look at the months:

import pandas as pd

base = "https://academy.cloudtechanalytics.com/datasets/sales/"
orders = pd.read_csv(base + "orders.csv")
products = pd.read_csv(base + "products.csv")
orders["revenue"] = orders["quantity"] * orders["unit_price"] * (1 - orders["discount_pct"] / 100)
orders["order_date"] = pd.to_datetime(orders["order_date"])
orders["month"] = orders["order_date"].dt.to_period("M")

by_product = orders.groupby("product_id").agg(
    revenue=("revenue", "sum"),
    lines=("order_id", "count"),
    avg_quantity=("quantity", "mean"),
).sort_values("revenue", ascending=False)
by_product.head().round(1)

revenue  lines  avg_quantity
product_id
9           78372810.0    258          14.2
3           74837880.0    282          14.3
4           74594380.0    322          13.7
13          71586900.0    237          13.3
11          70241610.0    272          13.6

Product IDs aren't very readable. A dictionary of names, used with .map() (a lookup, like lesson 2's dictionaries), fixes that:

names = dict(zip(products["product_id"], products["product_name"]))
by_product.index = by_product.index.map(names)
by_product.head(3)

Now the monthly picture:

monthly = orders.groupby("month")["revenue"].sum()
print(f"Best month: {monthly.idxmax()} (₦{monthly.max():,.0f})")
print(f"Worst month: {monthly.idxmin()} (₦{monthly.min():,.0f})")

Best month: 2025-12 (₦66,284,310)
Worst month: 2025-02 (₦36,138,690)

December is Kolanut's peak: festive stock-up by its retail customers.

04Walkthrough

1. Run the Example's set-up cell and the product ranking. Detergent (product 9) leads, though bottled water (product 2) appears on the most lines: frequent isn't the same as valuable.
2. Run the names cell and look at by_product.head(3) again with product names.
3. Shares: share = by_product["revenue"] / by_product["revenue"].sum() and share.head(3). The top three products bring in about 27% of revenue.
4. Group by two columns: orders.groupby([orders["order_date"].dt.year, "discount_pct"])["revenue"].sum().unstack(). Read across a row: one year's revenue split by discount level.
5. Switch to the HR data:

employees = pd.read_csv("https://academy.cloudtechanalytics.com/datasets/hr/employees.csv")
by_dept = employees.groupby("department").agg(
    staff=("employee_id", "size"),
    resigned=("status", lambda s: (s == "Resigned").sum()),
    avg_salary=("monthly_salary", "mean"),
)
by_dept["resign_rate"] = by_dept["resigned"] / by_dept["staff"]
by_dept.sort_values("resign_rate", ascending=False).round(2)

6. The lambda is a small function written in place: for each department's status values, it counts how many are "Resigned". Use it when the calculation you need isn't one of the built-in names.
7. Add .reset_index() to by_dept and see department become an ordinary column again.

05Practice

06Challenge

07More practice

Optional drills on the legal dataset. Load matters.csv and invoices.csv from https://academy.cloudtechanalytics.com/datasets/legal/.

08Check your understanding