Grouping and aggregation

This is the exact file agents read, including the credit header and licence notice. Save it as grouping-aggregation/SKILL.md.

---
name: grouping-aggregation
description: >-
  Compares a metric across groups honestly: it states the question as a metric
  across a grouping, picks the aggregate that fits (a total, a per-unit rate as
  a ratio of sums, or a typical value by shape), reports every group with its
  size, weights any overall figure by group size instead of averaging the group
  averages, and checks for Simpson's paradox before trusting the comparison. Use
  when someone asks to "break this down by", "group by X", "revenue by region",
  "conversion rate by channel", "average order value per segment", "which
  category performs best", builds a pivot table or cross-tab, or compares a
  number across customers, cohorts, products, stores or time buckets. Do NOT use
  for: a first-pass profile of a whole dataset (use exploratory-data-analysis),
  the shape of one variable (use data-distributions or descriptive-statistics),
  two-variable relationships (use correlation-analysis), exploring a time-indexed
  series (use time-series-exploration), A/B test read-outs, causal claims or
  forecasting.
---

# Grouping and aggregation

Almost every real question about data is a question about groups: revenue by region, conversion by channel, response time by plan, repeat rate by cohort. The move looks trivial, a group-by and a mean, and that is where it goes wrong. A mean of per-row rates answers a different question from a rate computed across the group. A ranking that puts a three-order segment at the top is reading noise. An overall figure rebuilt as the plain average of group figures ignores that the groups are different sizes. And a comparison that looks decisive can reverse the moment you split it by a variable nobody controlled for. This skill does the group comparison properly: it ties the aggregate to the question, keeps the group sizes in view, weights overall figures correctly, and checks for the confounding that makes aggregates lie.

The discipline is to choose the aggregate before computing it, to report the size next to every number, and to break a surprising result down one more level before believing it.

## When to use this, and when not to

Use this skill when:

- Someone wants a metric compared across groups: "break revenue down by region", "conversion rate by channel", "average order value per plan", "which product category sells best".
- A pivot table or cross-tabulation is being built to summarise one measure across one or two categorical dimensions.
- An overall number is being rebuilt from group numbers, or a group ranking is about to drive a decision.
- A reported difference between segments needs checking before anyone acts on it.

Do not use this skill for:

- A first-pass profile of a whole dataset. Use `exploratory-data-analysis`, which decides which columns are worth grouping on in the first place.
- The centre and spread of a single variable (`descriptive-statistics`) or the shape of one variable (`data-distributions`). This skill summarises a metric across groups; those summarise one column on its own.
- The relationship between two variables as variables, not groups. Use `correlation-analysis`.
- Data indexed by time, where trend and seasonality matter. Use `time-series-exploration`.
- An A/B test or experiment read-out, a causal claim, or forecasting. Grouping describes differences; it does not prove one group caused another's result.

## Inputs you must have before starting

Ask for anything missing. Do not assume it.

1. **The metric, in words.** "Conversion rate" and "number of conversions" are different questions and need different aggregates. Pin down whether the metric is a count, a total, a rate or a typical value.
2. **The grouping variable or variables,** and what each row represents. The grain decides whether a group count means customers, orders or events.
3. **For a rate, the numerator and denominator columns.** A rate is conversions over sessions, revenue over orders, defects over units. You cannot aggregate a rate correctly without both parts.
4. **The decision the comparison feeds.** Ranking stores to close, sizing a segment, or watching a number over time are different jobs and change how much group size and confounding matter.
5. **Known confounders.** Ask what differs between the groups besides the thing being measured: case mix, device split, time period, how each group was selected. This is the list you will stratify by in step 6.
6. **Sensitivity.** If the data is personal, health, financial, legal or employment data, follow the PII rules below; small groups can identify individuals.

If the person gives you a column to group on and nothing else, confirm the metric (input 1) and whether it is a rate (input 3) before computing anything, because those answers decide the whole method.

## Workflow

Work on a copy of the data. This skill does not alter the source.

### Step 1: State the question as a metric across a grouping

Write the question in one line: metric M, grouped by G, for a reason R. "Mean order value, by acquisition channel, to decide where to spend." This forces the next three decisions: what the metric is, what the groups are, and how good a difference has to be to matter.

### Step 2: Build the groups deliberately, and decide what happens to the edges

Two defaults will quietly change your answer, so set both on purpose:

- **Missing group keys.** By default pandas excludes rows whose group key is missing (`dropna=True`); a whole "unknown" group vanishes from the result (pandas groupby user guide, verified 2026-10-27). Decide whether unknown is a group worth seeing. If it is, keep it.
- **Unused categories.** With a categorical group key, the tool can either return only the groups that appear or every defined category, including empty ones with a count of zero. Choose explicitly (`observed=True` to drop unused, `observed=False` to keep them); an unexpected row of zeros or a missing expected group both come from this setting (pandas groupby user guide, verified 2026-10-27).

Then check cardinality. If the grouping variable has hundreds of values, a table of hundreds of rows is not a comparison; cap it to the top groups by size plus an "other" bucket, or roll up to a coarser grouping.

### Step 3: Choose the aggregate from the question, with this rule

| The question is | Aggregate | The trap to avoid |
|---|---|---|
| How many? | count or size | `count` ignores missing values in a column; `size` counts rows. Know which you want |
| What is the total? | sum | Summing a rate or an average is meaningless |
| What is the rate / share? | sum(numerator) / sum(denominator) over the group | Averaging the per-row rates (see step 4) |
| What is the typical value? | median for skewed data, mean for symmetric | A mean on a skewed group is pulled by its tail; check the shape with `data-distributions` |
| What is the spread within a group? | IQR or standard deviation, by shape | Reporting a centre with no spread hides how variable the group is |

Decide `size` versus `count` and `mean` versus `median` before you run anything, not by looking at which number reads better.

### Step 4: Aggregate a rate as a ratio of sums, never as a mean of rates

This is the single most common grouping error. To get a group's conversion rate, divide the group's total conversions by its total sessions. Do not average the per-row conversion rates. The mean of ratios weights a user with 2 sessions the same as a user with 2,000; the ratio of sums weights by volume, which is almost always the question. The two numbers can differ enormously and can even rank groups in opposite orders.

Decision rule: if the metric is "per something" (per session, per order, per unit, per user), compute `sum(numerator) / sum(denominator)` within each group. Only use a mean of per-row rates when the explicit question is "the typical individual rate", and say so.

### Step 5: Report the group size next to every aggregate, and set a floor

A group aggregate without its size is unreadable. A 100% conversion rate on 2 sessions is noise; on 20,000 it is a finding. Always carry the group's n (and, for a rate, the denominator total) alongside the metric.

Decision rule: set a minimum group size before you rank. Below it, do not rank the group against the others; suppress it, pool it into "other", or flag it as too small to trust. Where the groups will be published and could identify people, suppress small cells rather than show them (see Guardrails). The floor depends on how noisy the metric is; state the floor you used.

### Step 6: Rebuild overall figures by weighting, and check for Simpson's paradox

Two linked traps:

- **Averaging the averages.** The overall mean is not the plain average of the group means unless the groups are the same size. To get an overall figure from group figures, weight each group by its size (for a rate, add the numerators and denominators back up). Report the weighted overall number, or compute it directly from the raw rows.
- **Simpson's paradox.** A difference that holds overall can reverse inside every subgroup, and the reverse can also happen, when the groups have a different mix of some other variable. Treatment A can beat B in both mild and severe cases yet lose overall because A was given the harder cases (metricgate, verified 2026-10-27). Before you trust a group comparison, break it down by the confounders from input 5 and see whether the direction holds. If splitting by a plausible confounder flips or erases the difference, report the stratified result, not the pooled one, and be careful which variable you condition on: adjusting for something on the causal path can mislead as badly as ignoring a confounder (metricgate, verified 2026-10-27).

### Step 7: For two dimensions, pivot or cross-tabulate, and name the normalisation

When the question has two categorical dimensions (region by channel, say), a pivot table or cross-tab is the right shape. If you show proportions rather than counts, state which way they are normalised: across the row, down the column, or over the whole table. "30% of EMEA revenue came from paid" and "30% of paid revenue came from EMEA" are different claims from the same cell, and a reader cannot tell which you mean unless you say.

### Step 8: Write it up, then verify

Report the metric and the aggregate you used, each group's value with its size, the comparison, the overall weighted figure, and the confounder check. Then run the quality bar below. Recompute one group's figure a second way from the raw rows; if it does not match the aggregated table, the grouping is wrong somewhere and nothing in the table is safe yet.

## Worked example

**Input:** "Which acquisition channel converts best? Here's one row per session with `channel`, `device`, and `converted` (0/1)." 240,000 sessions.

**Step 1.** Metric: conversion rate, by channel, to decide where to spend.

**Step 3 to 4.** Conversion rate is a rate, so it is `sum(converted) / count(sessions)` per channel, not the mean of a per-session 0/1 averaged some other way. Computed as a ratio of sums:

| Channel | Sessions | Conversions | Rate |
|---|---|---|---|
| Paid search | 120,000 | 4,800 | 4.0% |
| Email | 90,000 | 4,050 | 4.5% |
| Referral | 29,500 | 1,180 | 4.0% |
| Partner micro-site | 500 | 45 | 9.0% |

**Step 5.** The partner micro-site "wins" at 9.0%, but on 500 sessions that is 45 conversions; the figure is noisy and below the floor set for ranking (here, 5,000 sessions). It is flagged, not ranked first. Among the channels above the floor, email leads.

**Step 6.** Break the email-versus-paid comparison down by device. Email's traffic is 70% desktop; paid's is 70% mobile, and mobile converts worse everywhere. Within desktop, paid converts 5.2% to email's 5.0%; within mobile, paid 2.8% to email 2.6%. Paid is ahead in both devices but behind overall: a Simpson's paradox driven by device mix. The honest read-out is per device, and the pooled "email wins" is an artefact of who each channel reached.

**Write-up (shortened):**

> **Conversion rate by channel, per session (n = 240,000), 2026-10-27.** Rates are conversions over sessions. Pooled: email 4.5%, paid 4.0%, referral 4.0%; the partner micro-site shows 9.0% but on 500 sessions (45 conversions), below the 5,000-session floor, so it is flagged, not ranked. **Watch the mix:** split by device, paid beats email on both desktop (5.2% vs 5.0%) and mobile (2.8% vs 2.6%); email leads overall only because it skews desktop. Report per device. Next: confirm the device split is expected before shifting budget.

## Templates

### Group comparison write-up (use verbatim)

```
# <metric> by <grouping>, <grain>, <date>   (n = <total>)
Aggregate used: <sum / ratio of sums / median / mean>, because <reason>
Floor for ranking: <min group size>

| group | metric | n (and denominator for a rate) |
|-------|--------|--------------------------------|
Overall (weighted): <value>   (not the average of the group values)

Confounder check: split by <variable> -> <direction holds / flips / erases>
What it means: <the honest comparison, and the next step>
```

### Opening questions when inputs are thin

```
Three things so the comparison is honest:
1. What exactly is the metric: a count, a total, a rate (over what), or a typical value?
2. What should each group be weighted by, and what is the smallest group worth ranking?
3. What differs between these groups besides the metric (device, period, case mix)?
```

## Failure modes and anti-patterns

1. **Averaging the rates.** Taking the mean of per-row or per-subgroup rates instead of dividing summed numerators by summed denominators. Spot it when a rate was computed with `.mean()` on a ratio column, or when the overall rate is the plain average of the group rates. Fix it with a ratio of sums, weighted by volume.
2. **Ranking on group size you ignored.** A tiny group tops the table on a handful of events. Spot it when the leading group has a small n or a small denominator. Fix it by reporting n beside every figure and setting a floor below which groups are not ranked.
3. **Trusting the pooled comparison (Simpson's paradox).** A difference reverses or vanishes once you split by a variable the groups do not share evenly. Spot it by checking whether the groups have a different mix of a known confounder. Fix it by stratifying and reporting the within-group result, chosen with the causal role of the variable in mind.
4. **Silent edge handling.** A missing-key group is dropped by default, or unused categories appear as empty rows, and the totals no longer add up. Spot it when the group counts do not sum to the row count, or an expected group is absent. Fix it by setting the missing-key and unused-category options on purpose and reconciling the totals.
5. **One centre on a skewed or mixed group.** Reporting a group mean when the group is skewed or is itself a mixture, so the mean describes nobody. Spot it when mean and median diverge within a group. Fix it with the median for skewed groups, and split a mixed group further before summarising it (hand the shape to `data-distributions`).

## Quality bar

Run this against your own output. Every item must be verifiable.

- [ ] The metric was named as a count, total, rate or typical value before any aggregate was chosen.
- [ ] A rate was computed as a ratio of summed parts, not as a mean of per-row rates, unless the typical-individual question was explicit.
- [ ] Every group aggregate is reported with its size (and denominator for a rate).
- [ ] A minimum group size for ranking was set and stated, and smaller groups were suppressed or flagged.
- [ ] Any overall figure was weighted by group size, not taken as the plain average of the group figures.
- [ ] The comparison was broken down by at least one plausible confounder, and any reversal was reported.
- [ ] Missing-key and unused-category handling was set deliberately, and the group counts reconcile to the row count.
- [ ] For a two-dimensional table, the direction of any proportion (row, column or total) was stated.
- [ ] One group figure was recomputed from the raw rows and matches the aggregated table.
- [ ] If the data is sensitive, small cells were suppressed and no individual is identifiable.

## Guardrails

- **Do not fabricate.** Every figure comes from the data. The aggregation rules, the ratio-of-sums convention, the Simpson's paradox caution and the small-group floor are method, labelled as such; the numbers are the dataset's.
- **Work on a copy; confirm before changing data.** Rolling rare categories into "other", capping outliers, or re-coding groups are changes the person approves first. Report the groups as they are before reshaping them.
- **PII and credentials.** Group cells can be small enough to identify a person, especially when two dimensions are crossed. Never print raw identifiers, suppress or combine cells below a safe size before sharing (a common threshold is a minimum count per cell), and do not paste grouped personal data into a third-party service. Report structure and aggregates, not individuals.
- **Not advice.** When the grouping concerns health, money, legal standing or employment, the output describes differences between groups only. It is not medical, financial, legal or HR advice, and a difference between groups is not proof that the group caused it; a decision needs a qualified human.
- **Cost.** Grouping is cheap and local. A cross-tab of two high-cardinality columns can explode into millions of near-empty cells; cap cardinality first, and sample for an exploratory pass on very large data.
- **Scope and no duplication.** This skill compares a metric across groups. For one variable's centre and spread use `descriptive-statistics`; for its shape use `data-distributions`; for a whole-dataset profile use `exploratory-data-analysis`; for two variables as variables use `correlation-analysis`; for groups defined by time use `time-series-exploration`. It does not run experiments, prove causation or forecast.

## Tool appendix (Python / pandas)

```
This section is tool-specific. The workflow above is tool-agnostic and works
the same in SQL (GROUP BY with SUM()/SUM() for rates), R or a spreadsheet.
```

- Named aggregation keeps output columns explicit: `df.groupby("channel").agg(sessions=("session_id","size"), conversions=("converted","sum"))`, then `rate = conversions / sessions`. This gives the ratio of sums directly (pandas groupby user guide, verified 2026-10-27).
- Do not do `df.groupby("channel")["rate_col"].mean()` for a rate; that is the mean-of-ratios trap. Sum the parts and divide.
- Keep or drop the missing-key group on purpose: `groupby("channel", dropna=False)` includes a row for missing keys; the default `dropna=True` drops it (pandas groupby user guide, verified 2026-10-27).
- For categorical keys, set `observed=True` to return only groups that appear, or `observed=False` to include unused categories as zero rows (pandas groupby user guide, verified 2026-10-27).
- Two dimensions: `pd.pivot_table(df, index="region", columns="channel", values="revenue", aggfunc="sum")`; or `pd.crosstab(df.region, df.channel, normalize="index")` for row proportions (`"columns"` for column, `"all"` for table). State which `normalize` you used.
- Weighted overall value: `numpy.average(group_values, weights=group_sizes)`, or recompute from the raw rows.
- Report size with the metric: include `size` in the `agg` call so every row carries its n.
- Guard against Simpson's paradox by grouping on the confounder too: `df.groupby(["channel","device"]).agg(...)` and compare the within-device rates to the pooled ones.

## Sources

- pandas, "Group by: split-apply-combine" user guide (pandas 3.1.0; `dropna` default `True` excludes NA keys; `observed` for categoricals; named aggregation), pandas.pydata.org (last verified 2026-10-27).
- pandas, `pivot_table` and `crosstab` API reference (pandas 3.1.0; `normalize` options), pandas.pydata.org (last verified 2026-10-27).
- "Simpson's Paradox: When Aggregating Reverses Results", metricgate.com (pooled rate as a size-weighted average; stratify by confounders; causal role of the third variable) (last verified 2026-10-27).
- E. H. Simpson, "The Interpretation of Interaction in Contingency Tables", Journal of the Royal Statistical Society, 1951 (the paradox's original statement) (last verified 2026-10-27).
- "When Numbers Mislead Us", arXiv:2507.03628 (aggregation and averaging pitfalls in practice) (last verified 2026-10-27).
- Anthropic, Agent Skills repository and skill-authoring guidance, github.com/anthropics/skills (reviewed as the reference for skill triggering and structure; no grouping or aggregation skill exists there) (last verified 2026-10-27).
- Representative community "data-analysis" SKILL.md files (for example Mindrally/skills), reviewed as existing implementations: generic pandas best-practice lists without aggregate-choice rules, group-size floors or confounder checks (last verified 2026-10-27).

Use, adapt and share this skill under CC BY 4.0: credit Afeez Adebayo, link to afeezadebayo.com and to this skill's page. LinkedIn: linkedin.com/in/afeez-adebayo.