Grouping and Aggregation

What you'll build

A revenue report per country and per customer: total revenue, number of orders and average order value, plus each order's share of its customer's spending, the questions behind most business dashboards.

The idea

• groupby(keys) splits rows into groups; an aggregation combines each group into one row.
• Named aggregation (agg(new_name=("column", "func"))) gives clear column names.
• transform returns a value per original row (for example the group total), so you can compute shares or z-scores.
• as_index=False keeps the group keys as columns, which is handier for further processing.

The code

from io import StringIO
import pandas as pd

csv = StringIO("""order_id,date,customer,country,product,quantity,unit_price
1001,2026-01-03,ana,DE,mug,2,8.50
1002,2026-01-03,ben,US,teapot,1,24.00
1003,2026-01-04,chen,DE,kettle,1,31.90
1004,2026-01-05,ana,DE,teapot,1,24.00
1005,2026-01-07,dara,FR,mug,4,8.50
1006,2026-01-07,ben,US,mug,1,8.50
""")
orders = pd.read_csv(csv, parse_dates=["date"])

orders["total"] = orders["quantity"] * orders["unit_price"]

by_country = (
    orders.groupby("country", as_index=False)
    .agg(revenue=("total", "sum"), orders=("order_id", "count"), avg_order=("total", "mean"))
    .sort_values("revenue", ascending=False)
    .round(2)
)
print(by_country)

orders["customer_total"] = orders.groupby("customer")["total"].transform("sum")
orders["share"] = (orders["total"] / orders["customer_total"]).round(2)
orders["rank_in_customer"] = orders.groupby("customer")["total"].rank(ascending=False).astype(int)
print(orders[["customer", "order_id", "total", "share", "rank_in_customer"]].sort_values(["customer", "rank_in_customer"]))

  country  revenue  orders  avg_order
0      DE     72.9       3      24.30
1      FR     34.0       1      34.00
2      US     32.5       2      16.25
  customer  order_id  total  share  rank_in_customer
3      ana      1004   24.0   0.59                 1
0      ana      1001   17.0   0.41                 2
1      ben      1002   24.0   0.74                 1
5      ben      1006    8.5   0.26                 2
2     chen      1003   31.9   1.00                 1
4     dara      1005   34.0   1.00                 1

Common mistakes

• groupby(...).mean() on all columns, including IDs, producing meaningless averages; aggregate only what you need.
• Merging aggregated results back to rows by hand when transform does it directly.
• Grouping on columns with missing values: by default those rows are dropped; use dropna=False to keep them.

Key points

• groupby + named agg produces clear summary tables.
• transform broadcasts group results back to every row.
• Watch missing keys and aggregate only meaningful columns.

Further reading: Group by: split-apply-combine