Cleaning Data

Finding and handling dirty data, with a reason for every decision. Special codes for refused and don't know, structural against unknown missing values, a zero that a file reader turns into 5.4e-79, units and self-reported measurements, totals stored beside their parts, suppressed counts, and duplicates; then Rubin's three missing-data mechanisms (MCAR, MAR, MNAR) and a seeded experiment on NHANES blood pressure showing how complete-case analysis, mean imputation, regression imputation and stochastic regression imputation bias the mean, spread and correlation under each.

NHANES asks the adults it interviews how much they weigh. In the 2017 to 2018 weight history file, the 6,161 answers have a mean of 355.3 pounds and a standard deviation of 1,297.6. Nobody in the survey weighs that much on average; the median answer is 170 pounds. The mean is wrong because 113 of the answers are not weights. They are codes, 7777 for "refused" and 9999 for "don't know", stored in the same column as the weights (National Center for Health Statistics, 2020c). Once they are set aside, the mean of the 6,048 real answers is 178.5 pounds and the standard deviation 48.7.

Nothing in the file flags those values as unusual. They are numbers in a numerical column, and any summary will use them. Cleaning is the step of wrangling that finds such values and decides what to do about each: special codes, missing values, impossible or inconsistent values, units, duplicated records. This article works through the kinds of dirty data found in real public files, each with the decision it called for, and then takes up the hardest case, values that are simply absent, with an experiment that measures what the common remedies do.

Codes that look like data

Survey and administrative files encode non-answers as numbers outside the range of real values. NHANES uses codes made of sevens and nines, such as 7 and 9, 77 and 99, or 7777 and 9999, chosen to lie outside each variable's range of real values; each variable's codebook lists the codes it uses with their counts (National Center for Health Statistics, 2020b, 2020c). A few examples from the 2017 to 2018 files:

Variable Real values Refused Don't know
Education level, adults 20 and over (DMDEDUC2) 1 to 5 7 (2 people) 9 (11 people)
Annual household income (INDHHIN2) 1 to 15 77 (170) 99 (220)
Self-reported height, inches (WHD010) 49 to 82 7777 (1) 9999 (128)
Self-reported weight, pounds (WHD020) 72 to 484 7777 (9) 9999 (104)

The damage depends on the gap between the code and the real values. For height, 129 codes among 6,106 answers raise the mean from 66.3 inches to 275.7. For education, two 7s and eleven 9s among 5,569 answers shift a mean of codes only slightly, which makes them more dangerous, not less, because the error is too small to notice. The median, which depends only on order, is barely affected (170 pounds and 66 inches with or without the codes), which is one reason to look at medians alongside means during exploration.

The remedy is to replace each code by a missing value before any computation, using the codebook rather than guessing from the data:

whq["WHD020"] = whq["WHD020"].replace({7777: np.nan, 9999: np.nan})

Setting the codes to missing is a decision too: it says that a refusal carries no information about weight. That is the usual choice, but people who refuse to give their weight may differ from those who answer, and the later sections show why that matters.

Two kinds of missing

A missing value can mean that a true value exists but was not recorded, or that no value exists. Education level is blank for 3,685 people in the demographics file, and every one of them is under 20; the question was asked only of adults 20 and over, and all 5,569 of them have a code. Those blanks are not unknown educations, they are inapplicable. The pregnancy status variable is likewise defined only for women aged 20 to 44. Wickham (2014) draws the same line: a value that "represents an observation that should have been made, but was not" must be kept as missing, while "structural missing values, which represent measurements that cannot be made (e.g., the count of pregnant males) can be safely removed".

The distinction decides the remedy. Inapplicable values should never be imputed, since there is nothing to estimate; they are handled by restricting the analysis to the people to whom the variable applies. Unknown values are candidates for the methods later in this article.

In pandas, a missing number is NaN, a missing date NaT, and a missing object None or NaN; df.isna().sum() counts them by column. Files also use blank strings, sentinel numbers such as $-999$ and, as the next section shows, zeros, so a count of NaN values is the beginning of the inventory, not the end.

A zero that is not zero

The diastolic blood pressure columns of the NHANES examination file contain zeros, which the documentation allows: "Diastolic BP can be zero" (National Center for Health Statistics, 2020a). The documentation does not give the reason; in a reading by ear, such a value arises when the sounds that mark the diastolic pressure can still be heard as the cuff pressure falls to zero. For averaging, a zero should be treated as missing, which in pandas is replace(0, np.nan). Read the file with pd.read_sas(path, format="xport") in pandas 2.2.3, though, and the line does nothing. Before and after it, the first diastolic reading has 2,402 missing values; the 134 zeros are still there. (bpx.BPXDI1 == 0).sum() returns 0.

The zeros have been read as 5.397605346934028e-79, which is exactly $2^{-260}$. The reason lies in the file format. A SAS transport file stores every number in the IBM mainframe floating-point representation, and a reader must convert each one to the IEEE representation that modern computers use (SAS Institute, 2021). Zero in the IBM format is eight zero bytes. The reference conversion routine published with the format specification handles it as a special case, returning zero "since the ieee number is zero" when both halves of the stored value are zero. The vectorized conversion in pandas 2.2.3 (the function _parse_float_vec in pandas/io/sas/sas_xport.py) applies the general formula to every value without that branch. For an all-zero field the formula's exponent term becomes $(0 - 65) \times 4 + 1023 = 763$, and the result is $2^{763 - 1023} = 2^{-260}$, a tiny positive number. Every true zero in a transport file read this way becomes that number, in any column. The behavior was reported to the pandas project in 2023 (issue 50670), and a change that returns exact zeros was merged into its development version in October 2026 for the 3.2 release; releases up to 3.1.1 behave as described here (pandas development team, 2026).

The consequences are quiet. A printed table shows 0.0 for these values at most display precisions, so they look right. A mean is barely affected. But every test for zero fails, so any rule that treats zero specially, such as "a diastolic reading of 0 is missing", silently stops applying. In the 5,247 adults of Introduction to Data Wrangling, 83 diastolic readings are zeros; averaged in as values, they lower the mean diastolic pressure from 72.49 to 72.12 mm Hg, and they leave 15 adults with a diastolic pressure of 0 instead of no value.

The repair is to map the tiny values back to zero right after reading. No real measurement in these files is anywhere near $10^{-30}$, so the threshold is safe:

d = pd.read_sas(path, format="xport")
num = d.select_dtypes("number").columns
d[num] = d[num].mask(d[num].abs() < 1e-30, 0.0)

The Applied Statistics and Data Science scripts on this site read every NHANES file through a function that does this (statdata.read_xpt). The general lessons are older than this file format. A reader's output should be checked against the documentation's frequency tables (the codebook gives the count of each value), and a rule that tests for an exact value should be tested on data where the answer is known.

Units and self-reports

Self-reported height and weight sit in the same survey as measured ones, which allows a direct check. The weight history file stores weight in pounds and height in inches, converted from whatever unit the respondent used ("standardized to weight in pounds using the conversion factor 2.2046 pounds per kilogram"; National Center for Health Statistics, 2020c). The examination file stores measured weight in kilograms and height in centimeters. Comparing the two needs a common unit; using the exact definitions, 1 pound is 0.45359237 kilograms and 1 inch 2.54 centimeters.

Forgetting the conversion produces numbers that look reasonable on their own. For the 5,637 people with both weights, the self-reported pounds average 178.9 and the measured kilograms 81.9; read as kilograms, the self-reports would suggest people overstate their weight by a factor of 2.2. Converted, self-reported weight averages 81.16 kg against 81.88 kg measured: people report 0.71 kg less than they weigh on average (median difference $-0.17$ kg). The gap is larger for women ($-1.29$ kg) than for men ($-0.10$ kg). Self-reported height is 1.65 cm more than measured height on average (1.89 cm for men, 1.43 for women), across 5,570 people. These differences are small for each person but systematic, and they bias any quantity built from self-reports in a predictable direction: a body mass index from reported height and weight will be too low on average. Where both are available, the measured value is the one to use; where only the self-report is available, the bias is worth stating. Fifty-six people's self-reported and measured weights differ by more than 20 kg, which may be errors of reporting, recording or unit, or real changes between interview and examination, and which deserve a look before anything is decided.

Unit problems take other forms in other data: temperatures in both scales, currencies, percentages stored as 97.7 in one row and 0.977 in another. The defense is the same: find the unit of every numerical column in the documentation, and check the range of each column against it.

Totals beside their parts, and overlapping records

The NCHS weekly death counts by state, 2014 to 2019, have one row for each of 54 jurisdictions in each week. For MMWR year 2019, summing the all-cause deaths over every row gives 5,721,228. The United States had about half that. The jurisdictions include the United States itself, so its 2,845,957 deaths appear once as a total and once again spread over the states. They also include New York City separately from New York State, and Puerto Rico. Summing the 50 states, the District of Columbia and New York City gives 2,845,957 exactly, which settles two questions at once: the "New York" rows exclude New York City (100,060 and 54,052 deaths), and Puerto Rico (29,314) is not part of the United States total.

Such rows are not duplicates in the usual sense; no two are identical, and the combination of jurisdiction, year and week is unique in every row (df.duplicated(subset=keys).sum() is 0). They are a mixture of levels of aggregation in one table, and the cleaning decision is which level the question needs. Structuring Data meets the same pattern in Census files, and Enriching Data handles the New York City rows during a join.

Duplicates proper are repeated records of the same unit. df.duplicated() marks rows identical in every column after their first appearance, df.duplicated(subset=["id"]) rows that repeat a key, and drop_duplicates removes them. Exact duplicates usually come from appending the same extract twice or from a join that matched too much, and the fix is to keep one. Near-duplicates, the same person under two spellings of a name or the same transaction with two timestamps, need a rule for when two records describe the same unit, which is a problem of record linkage rather than of cleaning code. The most useful habit is the cheapest: assert that keys are unique wherever they are supposed to be, so that duplicates are caught where they first appear.

Counts withheld for privacy

The same weekly file withholds small numbers. In 47,649 cells, in 11,271 of its 16,902 rows, a cause-specific count is blank and the matching flag column says Suppressed (counts 1-9). Public health agencies suppress small counts so that individuals cannot be identified (the rule is stated in the flag itself). The cells are missing for a known reason: the value is between 1 and 9.

That reason has consequences. Summing septicemia deaths over the 52 jurisdictions for 2019 while skipping blanks, as sum() does by default, gives 33,374. The 1,215 suppressed cells hide between 1,215 and 10,935 more deaths, so the true total lies between 34,589 and 44,309; the United States row, which is not suppressed, gives 38,307. Skipping the blanks understates the total by 12.9%. For influenza and pneumonia, the known values sum to 44,641 against a national 49,516. Here the missingness depends on the missing value itself, which is the most difficult mechanism in the next section, and the only reason the bias can be bounded is that the suppression rule is published.

Why data are missing: three mechanisms

Whether a remedy for missing values works depends on why the values are missing. Rubin (1976) classified the possibilities, and van Buuren (2018, §1.2) summarizes the classification in terms of the probability that a value is missing:

  • Missing completely at random (MCAR). "If the probability of being missing is the same for all cases", the missing values are a random subset, and "causes of the missing data are unrelated to the data". Van Buuren's example is a weighing scale whose batteries ran out.
  • Missing at random (MAR). "If the probability of being missing is the same only within groups defined by the observed data". The scale fails more often on a soft surface than a hard one, and the surface was recorded. MAR is broader and more realistic than MCAR, and modern methods generally assume it.
  • Missing not at random (MNAR). Neither holds: the probability of being missing depends on something unobserved, often the missing value itself, as when a scale produces more missing values for heavier objects.

The names are easy to misread. "Missing at random" does not mean random in the everyday sense; it means random once the observed variables are taken into account. And the data alone cannot distinguish MAR from MNAR, since the evidence would be in the values that are missing.

Real missingness can be examined against what is observed. Of the 5,533 adults who attended the NHANES examination, 286 have no systolic reading. The rate is 6.0% at ages 18 to 39, 4.8% at 40 to 59, 3.9% at 60 to 79 and 8.4% at 80 and over (age is recorded as 80 for everyone 80 or older), and it is 5.6% for women against 4.7% for men. Missingness that varies with observed age and sex rules out MCAR. Whether it is MAR, depending on age and sex but not otherwise on blood pressure, cannot be settled from the data; a reading may fail more often for reasons related to the pressure itself.

What the remedies do: an experiment

The effect of each remedy can be measured by deleting values from data that are complete and comparing the results with the truth. The 5,247 adults with a systolic reading serve as the complete data. Their mean systolic pressure is 126.36 mm Hg, its standard deviation 20.13, its correlation with age 0.487 and the least-squares slope on age 0.527 mm Hg per year. In each run, 30% of the systolic values are deleted by one of three mechanisms:

  • MCAR: each value is deleted with probability 0.30.
  • MAR: the probability depends on age, $\Pr(\text{missing}) = 1/(1 + e^{-(a + 1.5 z_{\text{age}})})$, where $z_{\text{age}}$ is standardized age and $a$ is set so that the average probability is 0.30. The youngest tenth of adults lose 2.5% of their values and the oldest tenth 74.6%.
  • MNAR: the same form, with standardized systolic pressure in place of age, so that high pressures are the most likely to be lost.

Four remedies are then applied, and each estimate is recorded:

  • Complete-case analysis (listwise deletion) uses only the rows with a value.
  • Mean imputation fills each gap with the mean of the observed values.
  • Regression imputation fits a least-squares line of systolic pressure on age to the complete cases and fills each gap with the line's prediction.
  • Stochastic regression imputation adds to each prediction a random draw from a normal distribution with the residual standard deviation of that fit.

The experiment is repeated 30 times with seeds 0 to 29; the table gives the median of each estimate over the runs (the interquartile ranges are in the figure).

Mechanism Method Mean SD Correlation with age Slope
(complete data) 126.36 20.13 0.487 0.527
MCAR complete cases 126.35 20.07 0.487 0.526
mean imputation 126.35 16.81 0.408 0.368
regression imputation 126.34 17.66 0.554 0.526
stochastic regression 126.28 20.11 0.485 0.526
MAR complete cases 123.04 18.63 0.472 0.517
mean imputation 123.04 15.63 0.360 0.305
regression imputation 126.22 16.81 0.574 0.517
stochastic regression 126.29 19.08 0.507 0.521
MNAR complete cases 119.32 14.43 0.433 0.340
mean imputation 119.32 12.06 0.356 0.230
regression imputation 120.41 12.58 0.505 0.340
stochastic regression 120.37 14.45 0.437 0.341
Estimates of the mean, standard deviation and correlation with age of systolic pressure after deleting 30% of values, by mechanism and remedy: the marker is the median over 30 runs and the bar the interquartile range. The dashed line is the value in the complete data. Only under MCAR do the simple remedies get the mean right; mean and regression imputation distort the spread and the correlation under every mechanism; nothing recovers the truth under MNAR.
Estimates of the mean, standard deviation and correlation with age of systolic pressure after deleting 30% of values, by mechanism and remedy: the marker is the median over 30 runs and the bar the interquartile range. The dashed line is the value in the complete data. Only under MCAR do the simple remedies get the mean right; mean and regression imputation distort the spread and the correlation under every mechanism; nothing recovers the truth under MNAR.

The results match what the literature leads one to expect, and each row has a lesson.

Complete-case analysis is unbiased under MCAR, as van Buuren (2018, §1.3.1) states: "If the data are MCAR, listwise deletion produces unbiased estimates of means, variances and regression weights." Under MAR, the mean falls to 123.04, because older adults, who have higher pressures, are the ones most often dropped. The slope on age stays close to the truth (0.517), since the deletion depended only on age, the regression's own input. Its cost under MCAR is lost precision: the estimates rest on 70% of the data.

Mean imputation gets the mean exactly as complete-case analysis does, and no better, but it damages everything else. Filling 30% of values with one number shrinks the standard deviation from 20.13 to 16.81 even under MCAR, and weakens the correlation with age from 0.487 to 0.408 and the slope from 0.527 to 0.368. Van Buuren (2018, §1.3.3) is blunt: mean imputation "will underestimate the variance, disturb the relations between variables, bias almost any estimate other than the mean and bias the estimate of the mean when data are not MCAR."

Regression imputation uses the observed age to predict each missing pressure. Under MAR it recovers the mean (126.22), the improvement it is designed for. But the imputed values lie exactly on a line, so the spread is too small (16.81) and the correlation too large (0.574 against 0.487). In van Buuren's (2018, §1.3.4) words, "regression imputation artificially strengthens the relations in the data. Correlations are biased upwards. Variability is underestimated."

Stochastic regression imputation puts the scatter back by adding noise of the right size, and under MCAR it recovers all four quantities. Under MAR it recovers the mean and slope, and comes closer than any other method on spread and correlation, but its standard deviation is still low (19.08). The reason is in the data: blood pressure varies more among older adults (a standard deviation of 21.03 at 60 and over against 12.85 under 40, as Exploring Data with Groups and Pivot Tables shows), and the imputation model adds the same amount of noise at every age. The older adults, whose values are mostly the ones being imputed, receive too little. Van Buuren notes the same limitation of a model that "assumes equal dispersion around the regression line". A better imputation model would let the spread depend on age.

Under MNAR nothing works. Every method estimates a mean between 119.3 and 120.4, six or seven mm Hg too low, because the high values that were lost leave no trace in the observed data that any method could use. This is the case of the suppressed death counts, and of any survey question that people with a particular answer tend to skip. The remedies in that case are, in van Buuren's words, "to find more data about the causes for the missingness, or to perform what-if analyses to see how sensitive the results are under various scenarios", as was done with the bounds on septicemia deaths above.

A final caution applies to every single imputation, stochastic or not: standard errors computed from the filled-in data treat imputed values as if they had been observed, and are too small. Multiple imputation, which creates several completed datasets with different random draws and combines their results, accounts for that uncertainty, and is the standard method when values are missing at random; van Buuren (2018) is a full treatment.

Outliers

An outlier is a value far from the others. It may be an error (a unit mix-up, a typing slip, a code like 9999) or a real extreme. The cleaning question is which. The heaviest adult with a height, weight and blood pressure on record weighs 219.6 kg, and the highest mean systolic pressure is 238 mm Hg; both are rare but physiologically possible, and deleting them would remove real people from the data. The 9999-pound "weight" is impossible and is a code. The rule that follows is to decide from documentation and plausibility, not from distance alone: a value three standard deviations from the mean is a reason to look, not a reason to delete. Methods for finding outliers are the subject of Detecting Outliers and Detecting Outliers Using DBSCAN.

A cleaning checklist

The examples above suggest an order of work:

  1. Read the codebook. List the special codes, the units, the ranges and the conditions under which each variable was collected.
  2. Replace special codes by missing values, separating inapplicable from unknown.
  3. Check that the reader returned what the file contains: compare value counts with the codebook's frequencies.
  4. Convert units and check each column's range.
  5. Check keys for uniqueness, and find rows that are totals of other rows.
  6. For each variable with missing values, look at who is missing, by the variables that are observed, and choose a remedy whose assumptions fit. Report how many values were missing and what was done.
  7. Record every decision in code, so that it can be read and repeated.

In Python

import numpy as np
import pandas as pd

df.isna().sum()                                   # missing values per column
df["WHD020"] = df["WHD020"].replace({7777: np.nan, 9999: np.nan})
df["WHD020_kg"] = df["WHD020"] * 0.45359237      # pounds to kilograms

df.duplicated(subset=["SEQN"]).sum()              # repeated keys
df = df.drop_duplicates()

# who is missing?
df.groupby("age_group")["sbp"].apply(lambda s: s.isna().mean())

# simple remedies (each with the costs shown above)
cc = df.dropna(subset=["sbp"])                                  # complete cases
df["sbp_mean"] = df.sbp.fillna(df.sbp.mean())                   # mean imputation
b1, b0 = np.polyfit(cc.age, cc.sbp, 1)
pred = b0 + b1 * df.age
df["sbp_reg"] = df.sbp.fillna(pred)                             # regression imputation
sd = np.std(cc.sbp - (b0 + b1 * cc.age), ddof=2)
rng = np.random.default_rng(0)
df["sbp_sto"] = df.sbp.fillna(pred + rng.normal(0, sd, len(df)))  # stochastic regression

scikit-learn's SimpleImputer and IterativeImputer implement mean and model-based imputation inside a modeling pipeline. The numbers in this article are computed by tools/experiments/data-science/cleaning-data.py in the site's repository from the public NHANES and NCHS files.

References