lesson 211 pandas Transformations · advanced

Group and aggregate

groupby splits a table into one group per key value, applies a statistic to each group, and combines the results: one row per channel, per state or per customer.

You'll be able to

Compute one statistic per group with df.groupby(key)[column].sum() (or mean, max, size)
Compute several named statistics at once with agg(name=(column, function)) and as_index=False
Count how often each value appears with value_counts, and sort the result

The idea

In the Collection Algorithms module you counted orders per channel with a dictionary and a loop. pandas does the same in one call: orders.groupby("channel") splits the rows into one group per channel, a statistic is applied to each group, and the results are combined into a Series indexed by channel. size() counts rows per group; value_counts() is the shortcut for counting the values of one column.

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · Revenue per channel

import pandas as pd

orders = pd.DataFrame({
    "channel": ["web", "web", "web", "web", "phone", "marketplace", "app", "app", "app"],
    "status": ["delivered", "returned", "returned", "delivered", "delivered", "delivered", "delivered", "delivered", "cancelled"],
    "total": [35.84, 91.47, 55.72, 246.6, 54.55, 17.75, 64.17, 39.15, 25.24],
})
revenue = orders.groupby("channel")["total"].sum()
print(revenue.sort_values(ascending=False).round(2))
print(orders.groupby("channel").size().to_dict())
print(orders["status"].value_counts().to_dict())

example-2.py · Grouping by a column that isn't there

import pandas as pd

orders = pd.DataFrame({"channel": ["web", "app"], "total": [35.84, 64.17]})
print(orders.groupby("Channel")["total"].sum())

A report usually wants several numbers per group. agg takes named aggregations: name=(column, function), where the function is a name such as "count", "sum", "mean", "max" or "size". as_index=False keeps the key as an ordinary column, so the result is a normal table you can sort, filter or save.

example-3.py · A report per state

import pandas as pd

orders = pd.DataFrame({
    "order_id": ["O100282", "O100376", "O100918", "O101219", "O101723", "O102603", "O103199"],
    "ship_state": ["TX", "CA", "NY", "TX", "NY", "TX", "FL"],
    "total": [35.84, 91.47, 55.72, 246.6, 54.55, 17.75, 64.17],
})
report = orders.groupby("ship_state", as_index=False).agg(
    orders=("order_id", "count"),
    revenue=("total", "sum"),
    largest=("total", "max"),
)
print(report.sort_values("revenue", ascending=False))

When to use it: any question with *per* or *by* in it: revenue per channel, orders per customer, average rating per product. Mistakes to avoid: counting with count on a column that has gaps (it skips them; size counts rows); averaging averages (group the raw rows instead); and forgetting that the keys become the index unless you pass as_index=False or call reset_index().

Review

groupby(key)[col].stat() gives one result per key; size() counts rows; value_counts() counts values.
agg(name=(column, function)) makes several named columns; as_index=False keeps the key as a column.
A share is the mean of a True/False column.
Next: combine two tables that share a key.

Practise this