AnalyticsPractice
1 min read
◆ The PatternSplit-apply-combine — the single most-used pattern in data analytics
Nearly every analytics question is "metric X by segment Y" — revenue by region, churn by plan, latency by endpoint. The engine underneath is always split-apply-combine: split rows into groups, apply an aggregation to each, combine the results into a table. pandas groupby, SQL GROUP BY, and spreadsheet pivot tables are the same idea in three dialects.
split(rows, key) → apply(agg) → combine
Split-apply-combine — group rows by key, reduce each group with mean/sum/count/…, stack the results.
// Interactive — raw rows on the left, one aggregated bar per group on the right
Aggregationmean
| Tool | Best for | One-liner |
|---|---|---|
| groupby + agg | Metrics by one or more keys | df.groupby('region')['rev'].sum() |
| pivot_table | Two keys → rows × columns grid | df.pivot_table('rev', 'region', 'month') |
| crosstab | Counts of category × category | pd.crosstab(df.plan, df.churned) |
| resample | Time-based grouping | df.resample('W')['rev'].sum() |
| transform | Group stat broadcast back to rows | g['rev'].transform('mean') |
Mean of means ≠ overall mean. Averaging per-group averages weights every group equally regardless of size. Aggregate from raw rows (or weight by group size) — this is Simpson's paradox waiting to happen in a dashboard.
Pattern bridge: Aggregation is also feature engineering — per-entity groupby stats ("customer's average order", "merchant's txn count") are among the strongest features in tabular ML and fraud models.
When to use this
✓ Use when: Building any report, dashboard metric, or segment comparison — and when engineering aggregate features for models.
✗ Skip when: You need row-level detail (aggregation destroys it) — or the "groups" are time windows with order mattering, where rolling windows beat plain groupby.
Try it on real data
Kaggle: Superstore Sales (10K orders — region, category, segment)
UCI: Online Retail (540K transactions, invoice-level)
Superstore is the canonical groupby playground: every business question is a two-line aggregation away.
Quick start — copy to notebook
pip install pandas seaborn
# ────────────────────────────────────────
import seaborn as sns
tips = sns.load_dataset('tips')
summary = (tips.groupby(['day', 'time'], observed=True)
.agg(orders=('total_bill', 'count'),
revenue=('total_bill', 'sum'),
avg_bill=('total_bill', 'mean'))
.round(2))
print(summary)
print(tips.pivot_table('total_bill', index='day',
columns='time', aggfunc='sum', observed=True))Worked example
900 baskets of 40 in a big store and 100 baskets of 80 in a small one. The per-store means are 40 and 80; their average is 60. The average basket is 44. Both are “the mean”: one counts stores, the other counts baskets. Aggregating aggregates silently changes the question.
Where it misleads
- Averages of ratios and ratios of averages differ; decide which one the question needs.
- Grouped results can reverse when the groups are combined (Simpson’s paradox).
- Small groups produce extreme means by chance; rank them with care.
In code
df = pd.DataFrame({'store': ['big'] * 900 + ['small'] * 100,
'basket': np.r_[np.full(900, 40.0), np.full(100, 80.0)]})
per_store = df.groupby('store').basket.mean()
mean_of_means = per_store.mean() # each store counts once
pooled = df.basket.mean() # each basket counts onceAssumes import numpy as np and import pandas as pd (and SciPy where imported). Each snippet simulates or makes up its own data, as the comments say.
The same idea with a library
# Python — the aggregation toolbox import pandas as pd # Multiple metrics per group, named columns summary = (df.groupby('region') .agg(orders=('order_id', 'count'), revenue=('amount', 'sum'), avg_basket=('amount', 'mean')) .sort_values('revenue', ascending=False)) # Rows × columns grid pivot = df.pivot_table(values='amount', index='region', columns='month', aggfunc='sum', fill_value=0) # Same thing in SQL # SELECT region, COUNT(*), SUM(amount), AVG(amount) # FROM orders GROUP BY region ORDER BY SUM(amount) DESC; # Group stat next to each row (for "% of segment" columns) df['pct_of_region'] = df['amount'] / df.groupby('region')['amount'].transform('sum')
Sources
- H. Wickham, “The Split-Apply-Combine Strategy for Data Analysis”, Journal of Statistical Software 40(1), 2011
- Python for Data Analysis (3rd ed.), W. McKinney, O’Reilly, 2022
- E. H. Simpson, “The Interpretation of Interaction in Contingency Tables”, Journal of the Royal Statistical Society B 13(2), 1951
Last reviewed October 2026 · How this site checks its content · Report a mistake
Linked from 2