Data Analysis / Basic: Questions and Trustworthy Tables / Lesson 6

Grouping, Rates, and Denominators

Build group summaries whose weights, missing values, and eligibility rules answer the same question at every level.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Distinguish row counts, observed counts, and rates.
Combine rates using the correct exposure weights.
Reconcile group results with the overall population.

A grouping key creates a new analytical unit

Grouping partitions records into categories and summarizes each partition. State the key, eligibility rule, aggregation, and resulting row grain. A group total and a group mean answer different questions. Keep unknown group labels explicit when their exclusion would change the reported population, and distinguish absent groups from groups with measured zero.

A useful summary can include eligible rows, observed measurements, event count, exposure, and the numerator/denominator used in a rate. These counts let a reviewer see whether a difference is about performance, group size, or data capture. A lone percentage hides all three possibilities.

Do not average rates without their denominators

The first program compares a mean of two group rates with the combined event rate. GroupA has one success in two opportunities; groupB has nine in ninety. Their unweighted mean is30%, while the overall rate is about10.87%. Both calculations are arithmetic, but only the ratio of summed counts answers the overall opportunity-level question.

An unweighted group-rate mean can be meaningful if the question gives each group equal importance. It must then be labeled as that estimand, not the overall individual opportunity rate. Choose weights from the question, not from whichever formula produces a favorable result.

python Two different rate questions

groups = [(1, 2), (9, 90)]
unweighted = sum(successes / total for successes, total in groups) / len(groups)
combined = sum(successes for successes, _ in groups) / sum(total for _, total in groups)
print(f"unweighted={unweighted:.2f} combined={combined:.2f}")

Expected output

unweighted=0.30 combined=0.11

The displayed0.30 weights groups equally;0.11 is the rounded rate across all92 opportunities.

Count rows and observed values separately

The second program creates only an isolated in-memory SQLite table. COUNT(*) includes every row, while COUNT(value) and AVG(value) use non-null measurements under SQL semantics. GroupA therefore has two rows but one observed value. GroupB has an observed zero, which must not be treated as missing. The explicit ORDER BY makes output order reproducible.

The code closes its connection and touches no configured database. In a real query, declare the intended SQL dialect and validate the schema’s types. A syntactically valid aggregate can still group at the wrong grain or hide missingness if only the average is selected.

python An in-memory SQL denominator audit

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as connection:
    connection.execute("CREATE TABLE measurements (team TEXT, value INTEGER)")
    connection.executemany("INSERT INTO measurements VALUES (?, ?)", [("A", 10), ("A", None), ("B", 0)])
    query = "SELECT team, COUNT(*), COUNT(value), AVG(value) FROM measurements GROUP BY team ORDER BY team"
    for team, rows, observed, average in connection.execute(query):
        print(f"{team} rows={rows} observed={observed} average={average:.1f}")

Expected output

A rows=2 observed=1 average=10.0
B rows=1 observed=1 average=0.0

A has one usable measurement among two rows; B has one genuine zero measurement.

Aggregation can reverse an apparent comparison

An overall difference can reflect a changing mixture of groups rather than a change within groups. Compare subgroup rates and their weights before attributing a movement to improvement. A site receiving more difficult cases can have a lower overall success rate even when its within-case-type results are stronger. This motivates stratification, not an automatic causal conclusion.

Keep the same eligibility and time-window definitions across comparison groups. A numerator from completed cases divided by all arrivals can be a valid completion rate, while a success-among-completed metric uses another denominator. Neither should silently replace the other between periods.

Make grouping defaults and reconciliation explicit

pandas groupby options control missing keys, categorical combinations, and sorting; defaults have changed across releases. Set dropna and observed deliberately for the intended population instead of copying an old snippet without its version. This course’s pandas references are documentation-based rather than executed examples. A SQL NULL group and a dropped missing-key group are not equivalent results.

Reconcile the sum of group eligible counts to the total after the declared exclusions. For additive quantities, group totals should match the overall total at the same grain. Rates need denominator-based recombination instead. Test empty groups, all-missing measurements, zero exposure, and a single large group so formulas cannot hide accidental equal weighting.

Common errors and fixes

The overall rate is a mean of subgroup percentages.

Why it happens: Groups receive equal weights despite unequal exposure.
How to fix it: Sum numerators and denominators for the opportunity-level rate.
Prevent it next time: Preserve counts behind every percentage.

A group average hides missing capture.

Why it happens: Only AVG is reported.
How to fix it: Add eligible and observed counts.
Prevent it next time: Test a group with missing and zero values.

A report drops unknown groups after a library update.

Why it happens: Version-sensitive grouping defaults were implicit.
How to fix it: Pin versions and set grouping options explicitly.
Prevent it next time: Reconcile group counts to the eligible total.

Check your understanding

1. Two groups have rates50% from1/2 and10% from9/90. State an honest label for each combined calculation.

Show worked solution

The unweighted mean30% is the mean of group-level rates with equal group weight. The overall opportunity rate is10/92, approximately10.87%. Report the chosen estimand and counts rather than calling both overall success. If equal group weighting is a policy choice, explain why that question is relevant instead of disguising it as an individual-level rate.

2. A group has five rows but COUNT(value)=0. What should its average and rate report communicate?