GroupBy — Split, Apply, Combine · Data Engineering Quest

The single most useful pattern in tabular analytics

The split-apply-combine idiom — split rows into groups, apply a function to each group, combine the per-group results into a final table — covers the vast majority of real-world tabular questions. Pandas exposes it as df.groupby(...), and learning it well is one of the highest-leverage things you can do as a Pandas user.

The three flavors of "apply"

Aggregate — produce one value per group (sum, mean, median, count). Result has one row per group.
Transform — produce one value per row, computed within the group (z-score within each store, rank within each customer). Result has the same shape as the input.
Filter — keep or drop entire groups based on a per-group test ("only stores with >100 orders this month").

Named aggregations are the modern style

Old code uses dictionaries-of-lists (.agg({'amount': ['sum', 'mean']})) and produces ugly multi-index columns. New code uses the named aggregation form: .agg(revenue=('amount_usd', 'sum'), orders=('order_id', 'nunique')). Result columns get clear names, and the code reads as a contract with the consumer.

Code

Aggregate, transform, and filter — one DataFrame, three patterns·python

import pandas as pd
import numpy as np

rng = np.random.default_rng(7)
df = pd.DataFrame({
    'store':      rng.choice(['A', 'B', 'C'], 1000),
    'customer':   rng.integers(1, 200, 1000),
    'order_id':   [f'O{i:05d}' for i in range(1000)],
    'amount_usd': rng.uniform(5, 500, 1000).round(2),
})

# Aggregate — one row per store
summary = df.groupby('store', as_index=False).agg(
    revenue=('amount_usd', 'sum'),
    avg_ticket=('amount_usd', 'mean'),
    orders=('order_id', 'nunique'),
    customers=('customer', 'nunique'),
)

# Transform — z-score the order amounts within each store
df['amount_z_in_store'] = (
    df.groupby('store')['amount_usd']
      .transform(lambda s: (s - s.mean()) / s.std())
)

# Filter — keep only stores whose total revenue > $50,000
big_stores = df.groupby('store').filter(lambda g: g['amount_usd'].sum() > 50_000)

Exercise

From the example DataFrame, compute (1) per-store median ticket size, (2) per-customer total spend across all stores, (3) the row of each store's largest order. Hint: for #3, groupby('store').apply(lambda g: g.loc[g['amount_usd'].idxmax()]) works, but df.sort_values(['store', 'amount_usd']).drop_duplicates('store', keep='last') is faster.

GroupBy — Split, Apply, Combine