groupby and Named Aggregation

“What’s our average order value?” sounds like one number, but order_items has no order value column: an order with four products has four rows. You first need one row per order, with the order’s revenue summed from its lines. “Revenue per product”, “orders per channel” and “sales per country” are the same shape of question. In a spreadsheet you would build a pivot table; in pandas you use groupby, the method you will call more than any other.

Split, apply, combine

import pandas as pd

items = pd.read_csv("order_items.csv")
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])

by_product = items.groupby("product_id")["revenue"].sum()
print(by_product.sort_values(ascending=False).head(3).round(2))
print(round(by_product.sum(), 2), round(items["revenue"].sum(), 2))

Read the middle line as three steps. Split the rows into groups, one per product_id. Apply a calculation, sum, to the revenue column of each group. Combine the results into a Series indexed by the group key. Product 21, the Standing Desk, earned 60,017.83 dollars, almost twice the next product.

The last line is a habit worth copying: the group totals add up to the overall total. If they do not, rows went missing (for example, rows whose key is NaN, which groupby drops by default) or were duplicated before you grouped.

Several statistics at once: named aggregation

One number per group is rarely enough. agg with named aggregation computes several, and names each output column:

import pandas as pd

items = pd.read_csv("order_items.csv")
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])

per_order = items.groupby("order_id").agg(
    revenue=("revenue", "sum"),
    lines=("order_item_id", "count"),
    units=("quantity", "sum"),
    max_discount=("discount", "max"),
)
print(per_order.head().round(2))
print(per_order.shape, "| average order value:", round(per_order["revenue"].mean(), 2))

Each keyword is an output column, and each value is a pair: (input column, function). Functions are given by name as strings: "sum", "mean", "median", "min", "max", "count", "size", "nunique", "first", "last", "std". The result has one row per order, 2,066 of them, and the average order value is 228.43 dollars, across all statuses, cancellations included, for now.

You will also see the older style agg({"revenue": "sum", "discount": "max"}), or agg(["sum", "mean"]) on one column. They work, but they reuse the input names, so a table with two statistics of the same column ends up with confusing headers. Named aggregation says exactly what each column is.

size versus count

Two ways to count, and they answer different questions:

import pandas as pd

orders = pd.read_csv("orders.csv")
print(orders.groupby("channel").size())                   # rows per group
print(orders.groupby("channel")["coupon_code"].count())   # non-missing coupons per group

size() counts rows: 1,146 web orders. count() counts the non-missing values of a column: 208 of those web orders have a coupon code. Use size (or count on a column that is never missing, such as an id) for “how many”, and count for “how many have a value”.

Several keys and flat results

Group by a list of columns to cross two dimensions. The result has a two-level index (a MultiIndex), and unstack() spreads the inner level into columns, which reads like a pivot table:

import pandas as pd

orders = pd.read_csv("orders.csv")
by_channel_status = orders.groupby(["channel", "status"]).size()
print(by_channel_status.head(4))
print(by_channel_status.unstack())

flat = orders.groupby("channel", as_index=False).agg(
    orders=("order_id", "count"),
    avg_fee=("shipping_fee", "mean"),
)
print(flat.round(2))

as_index=False keeps the group keys as ordinary columns instead of moving them into the index, which is handy when the result feeds a merge, a chart or a CSV export. reset_index() on an indexed result does the same afterwards.

Group results on every row: transform

Sometimes you want the group’s number next to each row rather than one row per group. “What share of its order does each line represent?” needs every line to know its order’s total. transform runs the same group calculation but returns a result aligned to the original rows:

import pandas as pd

items = pd.read_csv("order_items.csv")
items["revenue"] = items["quantity"] * items["unit_price"] * (1 - items["discount"])
items["order_revenue"] = items.groupby("order_id")["revenue"].transform("sum")
items["share_of_order"] = items["revenue"] / items["order_revenue"]
print(items[["order_id", "revenue", "order_revenue", "share_of_order"]].head(5).round(3))

Order 1001 has four lines, and the Burr Coffee Grinder line carries 62% of its value. The rule of thumb: agg when you want a summary table, transform when you want a new column on the detailed table.

The exercise builds the per-order table you will use from here on. Next you combine it with orders and customers, because the questions that matter, such as “revenue by country, excluding cancellations”, need columns from more than one table.