Exploring Data with Groups and Pivot Tables

Summarizing data group by group. The split-apply-combine strategy of Wickham (2011) and its pandas form, groupby with aggregate, transform, filter and apply; size against count and what happens to missing group keys; frequency and contingency tables with crosstab and their row and column percentages; pivot tables with margins; and a reversal in which one group has the lower average blood pressure overall but the higher in every age group, explained by age composition and removed by direct standardization. Worked on 5,247 adults from NHANES 2017-2018.

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-apply-combine on nine adults. The rows are split by age group, the mean systolic pressure is computed within each group, and the three results are combined into one table with a row per group. Each color marks a group, and the group name is also printed on every row.
Split-apply-combine on nine adults. The rows are split by age group, the mean systolic pressure is computed within each group, and the three results are combined into one table with a row per group. Each color marks a group, and the group name is also printed on every row.
  • 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=True gives 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; unstack moves the second key from the rows to the columns. pivot_table adds 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.

Left: mean systolic pressure by age group; adults with no usual place of health care have the higher mean in every group. Right: the share aged 60 and over is 14.8% among adults with no usual place of care and 41.7% among those with one, which is why the crude means, 123.0 and 127.2, point the other way.
Left: mean systolic pressure by age group; adults with no usual place of health care have the higher mean in every group. Right: the share aged 60 and over is 14.8% among adults with no usual place of care and 41.7% among those with one, which is why the crude means, 123.0 and 127.2, point the other way.

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