The 5,247 adults examined in the National Health and Nutrition Examination Survey (NHANES) of 2017 to 2018, built from the raw files in Introduction to Data Wrangling, have a mean systolic blood pressure of 126.36 mm Hg. That one number hides most of what the data say. Men average 127.41 and women 125.37; adults aged 18 to 39 average 114.83 and those 60 and over 136.85. Split once more, by sex within age, the comparison between men and women reverses: men have the higher pressure among younger adults, women among the oldest. Most first questions about a dataset have this form. Which groups differ, and by how much, and does the answer change when the groups are cut more finely?
This article covers the tools for answering them: the split-apply-combine strategy and its implementation in pandas, frequency and contingency tables, pivot tables, and the trap that comes with comparing groups that differ in composition. It is part of exploring, the first step of wrangling, because grouped summaries reveal not only patterns but problems: groups that are too small, codes that should not be there, keys that are missing.
The data are the same adults throughout, one row per person, with sex, age, systolic and diastolic pressure, body mass index (BMI), self-rated health and whether the person has a usual place of health care. They are treated as a group of people to be described, not as a sample from which to estimate figures for the United States; that would need the survey's weights.
Split, apply, combine
Wickham (2011) named a pattern that appears throughout data analysis: "you break up a big problem into manageable pieces, operate on each piece independently and then put all the pieces back together." He called it the split-apply-combine strategy and noted that it appears in preparing data ("group-wise ranking, standardization, or normalization"), in summarizing ("calculating marginal means, or conditioning a table of counts by dividing out group sums") and in modeling ("fitting separate models to each panel of panel data"). It is the idea behind SQL's GROUP BY (Aggregate Functions), spreadsheet pivot tables and the BY statement of SAS procedures, all of which Wickham lists, and he relates it to the map-reduce model of large-scale computing, in which "the map step corresponds to split and apply, and reduce corresponds to combine" (Data Engineering shows that correspondence in code).
The three steps, on nine of the adults, the first three in the file from each age group:
- Split the rows into groups by the value of one or more key variables, here age group.
- Apply a function to each group separately, here the mean of systolic pressure: (111.33 + 113.33 + 118.67)/3 = 114.44 for the youngest group, 132.22 for the middle group and 153.89 for the oldest.
- Combine the results into a new table with one row per group.
Three people per group give unstable means; the oldest group's 153.89 owes much to one adult with a pressure of 202. With all 5,247 adults the same three lines of code give 114.83, 126.32 and 136.85. The strategy's restriction, as Wickham (2011) notes, is that each piece is processed "only once and independently of all other pieces"; a running mean, in which each value depends on its neighbors, does not fit it.
groupby in pandas
In pandas the split is df.groupby(keys), and what follows it chooses the apply and combine steps. The commonest case reduces each group to one row:
adults.groupby("age_group").sbp.agg(["size", "mean", "std", "median"])
| Age group | Size | Mean | Standard deviation | Median |
|---|---|---|---|---|
| 18 to 39 | 1,745 | 114.83 | 12.85 | 113.33 |
| 40 to 59 | 1,579 | 126.32 | 18.56 | 124.00 |
| 60 and over | 1,923 | 136.85 | 21.03 | 134.00 |
The table shows more than the rise of the mean with age. The spread rises too, from a standard deviation of 12.85 mm Hg among young adults to 21.03 among the oldest, and in every group the median is below the mean, the sign of a right-skewed distribution that The Normal Distribution examines for the same data. The age groups here were made from age by pd.cut(age, [17, 39, 59, 80]); how such bins are defined, and how they can silently drop the youngest adults, is covered in Enriching Data.
Size and count
Two counts look alike and are not. size() counts the rows in each group; count() counts the non-missing values of each column in each group. For the adults grouped by sex:
| Group | Rows (size) |
Diastolic present (count) |
BMI present (count) |
|---|---|---|---|
| Men | 2,547 | 2,541 | 2,512 |
| Women | 2,700 | 2,691 | 2,664 |
Every adult has a systolic pressure, but 35 men and 36 women have no BMI. A mean BMI by sex (29.25 for men, 30.07 for women) is computed from the count column, not the size column. Reporting the counts next to every grouped statistic shows readers how much data each one rests on and exposes groups where values are missing unevenly.
Missing keys
A row whose key is missing belongs to no group, and by default groupby drops it. Self-rated general health is recorded on a five-point scale from excellent to poor; five adults refused or did not know, and their answers are missing. groupby("health") returns five groups totaling 5,242 adults, and the five others vanish without a warning. groupby("health", dropna=False) keeps them as a sixth group. Their mean systolic pressure, 105.3, is far below every other group's, a reminder that a missing key may mark a different kind of respondent, not a random one. Whether to keep such a group depends on the question, but it should be a decision rather than a default.
Transform, filter and apply
Not every application reduces a group to one row.
Transform returns a result the same length as the group, aligned with the original rows. Wickham's (2011) example of group-wise standardization is a natural one here: a systolic pressure of 140 mm Hg is 0.68 standard deviations above the mean of all adults, but relative to the person's own age group it is 1.96 standard deviations above the mean for an adult under 40 and only 0.15 for an adult of 60 or over.
adults["z_in_group"] = (adults.groupby("age_group").sbp
.transform(lambda s: (s - s.mean()) / s.std()))
Filter keeps or drops whole groups by a condition on the group. Requiring at least 100 adults in every age group keeps four of the five self-rated health groups (5,033 adults) and drops those who rated their health poor, of whom only 32 are under 40.
Apply runs any function on each group's rows and combines whatever it returns. With it the "apply" step can be a model. Fitting a least-squares line of systolic pressure on age separately for each sex (Simple Linear Regression) gives, for men, $\widehat{\text{sbp}} = 108.58 + 0.374 \times \text{age}$ and, for women, $\widehat{\text{sbp}} = 92.06 + 0.673 \times \text{age}$:
def line(d):
slope, intercept = np.polyfit(d.age, d.sbp, 1)
return pd.Series({"n": len(d), "intercept": intercept, "slope": slope})
cols = ["age", "sbp"]
adults.groupby("sex")[cols].apply(line)
Women's pressure rises with age almost twice as fast. The lines predict 119.8 mm Hg for a man of 30 and 112.3 for a woman of 30, but 134.8 and 139.2 at 70: the two lines cross. The tables below show the same crossing without a model.
Frequency and contingency tables
The simplest grouped summary is a count. A frequency table gives the number of rows with each value of one categorical variable, s.value_counts() in pandas, and is the first thing to look at for any category: it shows the groups that exist, their sizes, and stray codes. A contingency table (or cross-tabulation) counts the rows for every combination of two categorical variables, one on the rows and one on the columns. pd.crosstab builds one, with totals if asked:
pd.crosstab(adults.age_group, adults.bp_category, margins=True)
The blood pressure categories are those of the 2017 American College of Cardiology and American Heart Association guideline, as defined in Types of Numerical Data; they need a diastolic pressure, so this table covers the 5,232 adults who have one.
| Age group | Normal | Elevated | Stage 1 | Stage 2 | All |
|---|---|---|---|---|---|
| 18 to 39 | 1,142 | 234 | 267 | 99 | 1,742 |
| 40 to 59 | 568 | 229 | 409 | 372 | 1,578 |
| 60 and over | 382 | 352 | 419 | 759 | 1,912 |
| All | 2,092 | 815 | 1,095 | 1,230 | 5,232 |
Counts are hard to compare across rows of different sizes, so contingency tables are usually read as percentages, and the choice of denominator decides the question answered. normalize="index" divides each row by its total, giving the distribution of categories within each age group:
| Age group | Normal | Elevated | Stage 1 | Stage 2 |
|---|---|---|---|---|
| 18 to 39 | 65.6% | 13.4% | 15.3% | 5.7% |
| 40 to 59 | 36.0% | 14.5% | 25.9% | 23.6% |
| 60 and over | 20.0% | 18.4% | 21.9% | 39.7% |
Two thirds of young adults have normal pressure; two fifths of the oldest have stage 2 hypertension. normalize="columns" answers the other question, the age distribution within each category: of the adults with stage 2 hypertension, 61.7% are 60 or over and 8.0% are under 40. "What share of older adults have stage 2 hypertension?" (39.7%) and "what share of adults with stage 2 hypertension are older?" (61.7%) are different questions, and confusing them is a common error in reading such tables. The same distinction between conditioning on rows and on columns is the subject of Conditional Probability and Total Probability.
Pivot tables
A pivot table generalizes the contingency table: instead of counting the rows in each cell, it computes any statistic of a numerical variable over them. One key goes on the rows, another on the columns, and aggfunc names the statistic:
adults.pivot_table(values="sbp", index="sex", columns="age_group",
aggfunc="mean", margins=True)
| Mean systolic (mm Hg) | 18 to 39 | 40 to 59 | 60 and over | All |
|---|---|---|---|---|
| Men | 119.31 | 126.49 | 134.93 | 127.41 |
| Women | 110.83 | 126.18 | 138.85 | 125.37 |
| All | 114.83 | 126.32 | 136.85 | 126.36 |
The crossing found by the regression lines is visible directly. Men's pressure is 8.5 mm Hg higher than women's among young adults, practically the same in middle age, and 3.9 mm Hg lower at 60 and over. A table of means by sex alone, or by age alone, could not show this; the pivot table shows how the difference between two groups depends on a third variable, which a statistician would call an interaction.
Three practical points apply to every pivot table.
- Margins are computed from the rows, not from the cells. The "All" entries are means over all the adults in that row or column, so they weight each cell by its size. The mean of the three age-group means in the bottom row is 126.00, not the 126.36 in the corner, because the age groups have different sizes.
margins=Truegives the weighted version, which is usually the one wanted. - Every cell needs its count. A pivot table of means should be read with the contingency table of the same keys, here 824, 743 and 980 men and 921, 836 and 943 women in the three age groups. A mean from five people and a mean from 900 look alike in a pivot table. Combinations with no rows at all produce empty cells (
NaN), which are not errors but facts about the data. - A pivot table is a groupby in another shape.
adults.groupby(["sex", "age_group"]).sbp.mean().unstack()gives the same body as the table above;unstackmoves the second key from the rows to the columns.pivot_tableadds margins and fills in all combinations, but the computation is the same split-apply-combine.
Which variable goes on the rows is partly a matter of layout, since tables with few columns read better on narrow screens, and partly a matter of the comparison wanted: values side by side in a row are easier to compare than values in a column.
When the groups differ in composition
Grouped comparisons have a trap. NHANES asks every participant whether there is a place they usually go when they are sick or need advice about their health; 992 of the adults answered that there is no such place, and 4,254 named one place or more than one (one adult did not know). Adults with no usual place of care have a crude mean systolic pressure of 122.97 mm Hg, against 127.16 for those with one: 4.2 mm Hg lower. Now split by age:
| Mean systolic (mm Hg) | 18 to 39 | 40 to 59 | 60 and over | All ages (crude) |
|---|---|---|---|---|
| No usual place of care | 116.03 | 127.26 | 142.46 | 122.97 |
| Usual place of care | 114.24 | 126.15 | 136.38 | 127.16 |
In every age group the adults with no usual place of care have the higher mean, by 1.8, 1.1 and 6.1 mm Hg. The comparison reverses when the groups are combined. A reversal of this kind is known as Simpson's paradox, though nothing in it is contradictory.
The explanation is in the age composition. A crude mean is a weighted average of the age-specific means, each weighted by the share of the group in that age band. Among the adults with no usual place of care, 58.3% are 18 to 39 and 14.8% are 60 or over; among those with one, 27.4% are 18 to 39 and 41.7% are 60 or over (mean ages 39.0 and 52.4 years). Pressure rises steeply with age, so the older group's crude mean is pulled up by its composition, not by higher pressure at any given age.
The remedy is to compare the groups as if they had the same composition. Direct standardization applies each group's age-specific means to one standard set of weights. With the age distribution of all 5,247 adults as the standard (33.26% aged 18 to 39, 30.09% aged 40 to 59, 36.65% aged 60 and over),
$$\bar{y}^{\,\text{std}} = \sum_{k} w_k\, \bar{y}_k,$$
where $\bar{y}_k$ is the group's mean in age band $k$ and $w_k$ the standard share of that band. For adults with no usual place of care this is $0.3326 \times 116.03 + 0.3009 \times 127.26 + 0.3665 \times 142.46 = 129.10$, and for those with one 125.94. On a common age structure, adults with no usual place of care are 3.2 mm Hg higher, the opposite of the crude comparison. Direct and Indirect Standardization develops the method for death rates, where the same reversal routinely appears between populations of different ages, and gives its variance.
Two cautions belong with the method. The standardized figure depends on the standard chosen, and is a summary for comparison, not a description of either group as it is. And age is only the variable checked here: if the groups also differed in another variable that affects blood pressure, such as body mass, the age-standardized comparison would still be confounded by it. The reversal is a reason to ask, before trusting any grouped comparison, which other variables differ between the groups.
Exploring as a cycle
These tools are used in a loop rather than once. A grouped mean shows a difference; a contingency table checks whether the groups are large enough to trust it; a pivot table by a third variable shows whether the difference holds within subgroups; a surprising cell sends the analyst back to the rows behind it, where a stray code or a missing key may turn out to be the explanation. Fast movement between these views is most of what exploratory work consists of. The visual counterparts, the plots that show the same comparisons, are the subject of Visualizing Data with Multiple Features and Performing Exploratory Data Analysis.
In Python
import pandas as pd
# adults: one row per adult with sex, age, care, sbp, dbp, bmi, health
adults["age_group"] = pd.cut(adults.age, [17, 39, 59, 80], labels=["18-39", "40-59", "60+"])
adults.groupby("age_group", observed=True).sbp.agg(["size", "mean", "std", "median"])
cols = ["dbp", "bmi"]
adults.groupby("sex")[cols].agg(["size", "count"]) # rows against non-missing values
adults.groupby("health", dropna=False).sbp.agg(["size", "mean"]) # keep the missing-key group
pd.crosstab(adults.age_group, adults.bp_category, margins=True)
pd.crosstab(adults.age_group, adults.bp_category, normalize="index") # row percentages
adults.pivot_table(values="sbp", index="sex", columns="age_group",
aggfunc="mean", margins=True, observed=True)
# direct standardization over age groups
spec = adults.pivot_table(values="sbp", index="care", columns="age_group",
aggfunc="mean", observed=True)
w = adults.age_group.value_counts(normalize=True).sort_index()
(spec * w).sum(axis=1)
observed=True restricts grouping by a categorical key to the categories that occur; recent versions of pandas warn when it is not given. The numbers in this article are computed by tools/experiments/data-science/grouping-and-pivot-tables.py in the site's repository, which builds the adults from the public NHANES files.
References
- National Center for Health Statistics (2020). National Health and Nutrition Examination Survey 2017–2018 Data Documentation, Codebook, and Frequencies: Demographic Variables and Sample Weights (DEMO_J) and Blood Pressure (BPX_J). Centers for Disease Control and Prevention. https://wwwn.cdc.gov/nchs/nhanes/continuousnhanes/default.aspx?BeginYear=2017 record · 8 pages
- pandas development team (2024). Group by: split-apply-combine. pandas 2.2 user guide. https://pandas.pydata.org/pandas-docs/version/2.2/user_guide/groupby.html record
- Wickham, H. (2011). The split-apply-combine strategy for data analysis. Journal of Statistical Software, 40(1), 1–29. https://doi.org/10.18637/jss.v040.i01 record · 3 pages