The National Center for Health Statistics (NCHS) publishes weekly counts of deaths for every state. A count says little on its own: California recorded 268,775 deaths in 2019 and West Virginia 22,817, which mostly says that California has more people. To compare states the counts need a denominator, and the denominator is in a different file from a different agency, the Census Bureau's population estimates. Combining the two gives a death rate per 100,000 people, and with it a different picture: West Virginia's rate is nearly twice California's.
Enriching a dataset means adding information it does not contain, by joining another table, by stacking more records of the same kind, or by deriving new variables from existing ones. It is where wrangling most often goes wrong without any visible error, because a join that matches the wrong rows, or too many, still returns a table. This article covers the pandas tools for joining and stacking with the checks that catch those failures, works the death-rate example from raw files to a result checked against a published figure, and ends with derived features. The relational theory of joins and their SQL syntax are covered in Join Queries.
Joins
A join (in pandas, a merge) combines two tables by matching the values of one or more key columns. Rows of the left table are paired with rows of the right table that have the same key, and the result has the columns of both.
merged = left.merge(right, on="SEQN", how="left")
merged = left.merge(right, left_on="name", right_on="NAME", how="inner") # keys named differently
The how argument decides what happens to rows whose key appears in only one table (pandas development team, 2024):
- inner keeps only keys found in both tables;
- left keeps every row of the left table, with missing values in the right table's columns where there is no match;
- right does the same for the right table;
- outer keeps every key from either table, with missing values on whichever side has no match.
The NHANES files show each case. The demographics file DEMO_J has 9,254 people and the blood pressure examination file BPX_J 8,704. A left join of demographics to blood pressure keeps 9,254 rows, 550 of them without examination data; an inner join keeps the 8,704 who were examined. The weight history questionnaire WHQ_J (6,161 people aged 16 and over) and the body measurement file BMX_J (8,704 examined people of all ages) overlap only partly. Their outer join has 9,037 rows: 5,828 people in both, 333 who answered the questionnaire but were not examined, and 2,876 examined people with no questionnaire, all of them aged 15 or under, below the questionnaire's age range.
Every join type creates missing values except the inner join, which instead drops rows. Neither is a free choice. An inner join of the questionnaire and the examination quietly restricts the analysis to people aged 16 and over who were examined; that may be exactly what is wanted, but it should be stated.
Checking keys: validate and indicator
Two arguments turn a join from an act of faith into a checked operation.
validate declares how many rows each key should match on each side, and raises an error if the data disagree: "one_to_one" checks that the keys are unique in both tables, "one_to_many" in the left, and "many_to_one" in the right (pandas development team, 2024). A person number should be unique in each NHANES file, so every NHANES join on this site uses validate="one_to_one".
indicator=True adds a column _merge whose value is both, left_only or right_only for each row. Counting it with value_counts() gives the size of every part of the join in one line, which is the quickest way to see whether a join did what was expected.
A third check is to count rows before and after. A left join with a key that is unique on the right must return exactly as many rows as the left table had. If it returns more, the right key was not unique.
One behavior of pandas differs from SQL and deserves a warning. The pandas documentation notes that "If both key columns contain rows where the key is a null value, those rows will be matched against each other. This is different from usual SQL join behaviour and can lead to unexpected results." Rows with missing keys should be removed or examined before a join.
When a join multiplies rows
Suppose the 2019 weekly death counts, 2,808 rows (54 jurisdictions for 52 weeks), are joined by state name to the Census Bureau's 2021 population file, sc-est2021-alldata6.csv. That file does not have one row per state: it has 4,644, one for each combination of sex, Hispanic origin, race and single year of age, including rows that are totals of other rows (Structuring Data describes it). The join pairs every weekly row of a state with all 4,644 of that state's population rows. The result has 12,315,888 rows, and the deaths column, summed over it, totals 12,965,606,820, about 2,300 times the 5,721,228 deaths in the weekly file. No error is raised, because a many-to-many join is a legitimate operation; it is simply not the one intended.
validate="many_to_one" catches the mistake at once:
MergeError: Merge keys are not unique in right dataset; not a many-to-one merge
The fix is to reduce the right table to one row per key before joining, here by selecting the total rows or by summing the detailed ones. The general rule is to know the grain of each table, what one row represents, before joining it, and to state it with validate.
Worked example: state death rates
The goal is the crude death rate of each state in 2019,
$$\text{crude rate} = \frac{\text{deaths}}{\text{population}} \times 100{,}000,$$
from two public files: the NCHS weekly death counts by state for 2014 to 2019 (National Center for Health Statistics, 2021) and the Census Bureau's Vintage 2019 estimates of state population on 1 July 2019 (U.S. Census Bureau, 2019). Rates in Public Health discusses what a crude rate measures and why it is not the right comparison between populations of different ages, which Direct and Indirect Standardization addresses.
Step 1: make the grain match. The deaths are weekly; the populations are annual. Summing the weekly all-cause counts for MMWR year 2019 gives one row per jurisdiction, 54 rows. The 52 MMWR weeks of 2019 run from Sunday, 30 December 2018 to Saturday, 28 December 2019, as Structuring Data explains, so this "year" has 364 days.
Step 2: join with an indicator. The Census file has 57 rows. An outer join on the name, with indicator=True and validate="one_to_one":
first = deaths.merge(pop, on="name", how="outer", indicator=True, validate="one_to_one")
first["_merge"].value_counts() # both 53, right_only 4, left_only 1
The unmatched rows explain themselves once listed. The death counts treat New York City as its own jurisdiction, separate from the rest of New York State; the Census file has no row for the city. The Census file has rows for the Northeast, Midwest, South and West regions, which are totals of states. An inner join would have dropped the 54,052 deaths in New York City without a word, and a careless sum over the Census file would have counted every person twice.
Step 3: fix the keys, then join again. New York City's deaths are added to New York State's, the Census rows are restricted to the nation, the states, the District of Columbia and Puerto Rico, and the join is repeated. Now all 53 rows match, validate="one_to_one" passes, and the 51 state and District of Columbia populations sum to 328,239,523, exactly the national figure, which confirms that the right rows were kept.
Step 4: compute and check. For the United States, 2,845,957 deaths over a population of 328,239,523 gives a crude rate of 867.0 per 100,000. The published crude death rate for 2019 is 869.7 (Murphy, Kochanek, Xu and Arias, 2024, Table 6). The difference is 0.3%, and it lies in the deaths, not the population: the published rate rests on 2,854,838 deaths of United States residents (Murphy et al., 2024, Table 1), and that number divided by the same 328,239,523 people gives 869.7. The weekly file has 8,881 fewer deaths, for identifiable reasons. The MMWR year is one day short of a calendar year, which accounts for about 7,800 of them; scaling the rate by 365/364 gives 869.4. The weekly file also counts deaths by place of occurrence, while the published figure counts deaths of residents, and its year runs from 30 December to 28 December rather than over the calendar year. A small, explained difference from an authoritative figure is strong evidence that the join and the arithmetic are right; a large or unexplained one would send the analyst back.
The state rates range widely:
| Lowest crude rates | per 100,000 | Highest crude rates | per 100,000 |
|---|---|---|---|
| Utah | 602.9 | West Virginia | 1,273.2 |
| Alaska | 615.5 | Tennessee | 1,105.7 |
| California | 680.2 | Maine | 1,102.4 |
New York, with New York City included, has 792.2 and Puerto Rico 917.9. Much of the spread reflects age: Utah and Alaska have young populations and Maine and West Virginia old ones. Age-adjusted rates, which need deaths by age, are the subject of Direct and Indirect Standardization, which shows, with 2021 figures, Maine's crude rate 49% above Alaska's while its age-adjusted rate is the lower of the two.
The example shows the usual sequence for enriching with an external source: find a source with a matching key; reduce both tables to the same grain; join with an indicator and inspect every unmatched key; fix the keys at their source rather than dropping the rows; join again with validate; and check the result against a total or a published figure. Public sources for such joins include Data.gov, the Census Bureau, the World Bank's open data, the UCI Machine Learning Repository and Kaggle, and each comes with its own conventions for names, codes and units, which are where joins fail.
Stacking records
Joining adds columns. Concatenation adds rows: records of the same kind from another file, period or place. pd.concat([a, b], ignore_index=True) stacks two tables, aligning columns by name; a column present in only one table is filled with missing values for the other's rows. join="inner" keeps only the shared columns instead. Wickham (2014) describes the common case of "a single observational unit ... stored in multiple tables", split "by another variable, so that each represents a single year, person, or location", and recommends adding a column that records where each piece came from before combining them.
Stacking the NHANES demographics of 2015 to 2016 (DEMO_I, 9,971 people, 47 columns) and 2017 to 2018 (DEMO_J, 9,254 people, 46 columns), with a cycle column added, gives 19,225 rows and 52 columns. The person numbers do not overlap between cycles, so SEQN is still unique, which should be checked. But the column sets differ. Five columns exist only in 2015 to 2016 (DMDHRAGE, DMDHRBR4, DMDHREDU, DMDHRMAR, DMDHSEDU) and four only in 2017 to 2018 (DMDHRAGZ, DMDHREDZ, DMDHRMAZ, DMDHSEDZ); join="inner" keeps 42. The documentation explains the change: "Due to disclosure concerns, the household reference person's age in years variable was recoded to a categorical variable (<20, 20-39, 40-59, or 60+ years)", and the reference person's education and marital status were collapsed to three categories each (National Center for Health Statistics, 2020).
The renamed column is not merely a new name for the old one, so the two cycles cannot be stacked under one name directly. Harmonizing them means converting the more detailed version to the coarser one: cutting the 2015 to 2016 ages, which run from 18 to 80, into the same four groups. The harmonized distributions are then comparable: 0.6%, 40.1%, 37.0% and 22.3% in the four groups for 2015 to 2016, and 1.3%, 36.9%, 36.4% and 25.5% for 2017 to 2018. Variables are renamed, recoded and redefined between releases of most long-running surveys, and stacking files from different releases without reading each one's documentation is a reliable way to compare things that are not the same.
Deriving features
The other form of enrichment computes new variables from existing ones: ratios and rates (deaths per population, as above), differences and changes, transformations such as logarithms, products of two variables, and categories cut from a number.
Arithmetic on columns is vectorized in pandas: df["bmi"] = df.weight / (df.height / 100) ** 2 computes body mass index for every row at once, and is clearer and faster than df.apply with a function of each row, which is best kept for logic that cannot be written as column arithmetic. Recomputing NHANES body mass index from measured weight and height this way for the 8,005 people with all three values reproduces the published BMXBMI exactly after rounding to one decimal (the largest difference before rounding is 0.05). A derived feature that duplicates a published one is a free check on both the derivation and the understanding of the units.
Binning cuts a number into categories with pd.cut. The function's intervals are closed on the right by default, which catches people out at the lowest edge. Cutting the ages of the 5,856 adults with edges 18, 40, 60 and 80 gives the intervals (18, 40], (40, 60] and (60, 80], which do not contain 18; the 144 adults aged exactly 18 receive a missing category without any warning. Either the lowest edge must be lowered (17, as in the other articles of this topic), include_lowest=True given, or the intervals closed on the left with right=False, which gives [18, 40), [40, 60) and [60, 81). The edge values also move people between groups: with right-closed edges, the 40-year-olds are in the younger group, and with left-closed edges in the older. The bins should be chosen from the subject, such as the conventional cut point of 30 for obesity (2,236 of the 5,434 adults with a body mass index, 41.1%), and their edges stated.
Binning gives up information, and a model can usually use the number directly. Binned variables are useful for tables, for groups in Exploring Data with Groups and Pivot Tables, and for communicating; for prediction, transformations and flexible models are often better than coarse categories, as Multiple Linear Regression discusses for polynomial terms and interactions.
Enrichment creates cleaning work
Every enrichment step can create new problems: a left join introduces missing values wherever there is no match; a stacked file brings columns that do not exist in the other; a ratio divides by zero where a denominator is zero, producing infinities that look like numbers; a derived variable inherits the missing values of every input. After each enrichment step the checks of Cleaning Data apply again. The cycle of the wrangling steps described in Introduction to Data Wrangling is a loop because of this.
In Python
import pandas as pd
# join with checks
m = deaths.merge(pop, on="name", how="outer", validate="one_to_one", indicator=True)
print(m["_merge"].value_counts())
print(m.loc[m["_merge"] != "both", "name"].tolist()) # inspect every unmatched key
# fix keys at the source, then join again
deaths["name"] = deaths["name"].replace({"New York City": "New York"})
deaths = deaths.groupby("name", as_index=False)["deaths"].sum()
rates = deaths.merge(pop, on="name", how="inner", validate="one_to_one")
rates["crude"] = rates.deaths / rates["pop"] * 1e5
# stack two releases, recording the source
both = pd.concat([demo_i.assign(cycle="2015-2016"), demo_j.assign(cycle="2017-2018")],
ignore_index=True)
assert both.SEQN.is_unique
# derive and bin
adults["bmi"] = adults.weight / (adults.height / 100) ** 2
adults["age_group"] = pd.cut(adults.age, [18, 40, 60, 81], right=False)
The numbers in this article are computed by tools/experiments/data-science/enriching-data.py in the site's repository from the public NCHS, Census Bureau and NHANES files.
References
- Murphy, S. L., Kochanek, K. D., Xu, J., and Arias, E. (2024). Deaths: Final data for 2021. National Vital Statistics Reports, 73(8). National Center for Health Statistics. Tables 1 and 6. https://www.cdc.gov/nchs/data/nvsr/nvsr73/nvsr73-08.pdf record · 4 pages
- 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). Centers for Disease Control and Prevention. https://wwwn.cdc.gov/Nchs/Data/Nhanes/Public/2017/DataFiles/DEMO_J.htm record · 8 pages
- National Center for Health Statistics (2021). Weekly Counts of Deaths by State and Select Causes, 2014–2019. data.cdc.gov, dataset 3yf8-kanr. https://data.cdc.gov/d/3yf8-kanr record · 5 pages
- pandas development team (2024). pandas.DataFrame.merge. pandas 2.2 API reference. https://pandas.pydata.org/pandas-docs/version/2.2/reference/api/pandas.DataFrame.merge.html record
- U.S. Census Bureau, Population Division (2019). Annual Estimates of the Resident Population for the United States, Regions, States, and Puerto Rico: April 1, 2010 to July 1, 2019 (Vintage 2019), file nst-est2019-alldata.csv. https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/ record
- Wickham, H. (2014). Tidy data. Journal of Statistical Software, 59(10), 1–23. https://doi.org/10.18637/jss.v059.i10 record · 5 pages