Data Cleaning and Missing-Data Management

Introduction

Data preparation is not a clerical prelude to analysis; it is a form of measurement control. Analytical results are only as credible as the processes used to detect, document, and manage errors in the underlying data. In practice, datasets may contain duplicated records, invalid values, inconsistent categories, impossible dates, extreme observations, and missing fields. These problems arise from human entry, sensor failure, system integration, changing definitions, privacy protection, and intentional nonresponse.

Cleaning therefore requires more than deleting unusual rows. It involves identifying the data-generating process, distinguishing error from legitimate variation, preserving an audit trail, and evaluating how each intervention changes the intended analysis. A technically convenient treatment can introduce bias, reduce variance artificially, or conceal important subgroups.

This chapter develops a principled workflow for diagnosing and treating data-quality problems, with particular emphasis on missing-data mechanisms and imputation. It also considers robust methods, reproducibility, governance, and the analytical consequences of cleaning decisions.

Learning Objectives

By the end of this chapter, you should be able to:

1. Diagnose structural, validity, consistency, duplication, and plausibility errors.
2. Distinguish legitimate extreme observations from errors and influential cases.
3. Explain missing completely at random, missing at random, and missing not at random.
4. Select and justify deletion, correction, robust treatment, or statistical imputation.
5. Implement a defensible cleaning workflow with validation and auditability.
6. Evaluate the effects of cleaning choices on bias, variance, uncertainty, and fairness.

1. Data Quality as a Measurement Problem

A useful distinction is between an observed value and the value that would ideally have been recorded. Let Y_i denote the true quantity for unit i, and X_i the recorded value. A simple measurement-error representation is:

Here e_i is recording or measurement error, expressed in the same units as Y_i. The equation is a conceptual model, not a universal law. It is appropriate for quantitative measurements where additive error is plausible; categorical errors require a misclassification model instead.

Data quality has several dimensions:

A cleaning decision should begin with a data-quality specification: variable definitions, units, valid ranges, permitted categories, key fields, temporal rules, and business or scientific constraints. Without such a specification, automated rules may incorrectly classify rare but valid cases as errors.

2. A Reproducible Cleaning Workflow

A professional workflow is iterative:

1. Profile: inspect dimensions, types, distributions, missingness, unique values, and key relationships.
2. Specify: translate domain knowledge into explicit validation rules.
3. Diagnose: locate violations and classify their likely causes.
4. Preserve: retain the raw source unchanged and create versioned working data.
5. Repair or treat: correct known errors, resolve duplicates, handle outliers, and manage missingness.
6. Validate: rerun rules and compare distributions before and after treatment.
7. Document: record rule, action, rationale, date, operator, and effect on row and value counts.
8. Assess sensitivity: repeat important analyses under plausible alternative treatments.

The workflow should be separated from the analytical model. For example, imputation parameters must be estimated using the training data only when a predictive model is being developed; otherwise information from the test set can leak into evaluation.

A cleaning log commonly includes:

3. Diagnosing Common Data Problems

3.1 Structural and type errors

Structural errors include a column shifted during import, numeric values stored as text, mixed date formats, encoding failures, and wrong units. A value such as 1,250 may be interpreted differently across locales. Dates such as 03/04/2025 are ambiguous unless a convention is known.

Inspect schema, encoding, precision, units, and parsing failures before calculating statistics. Parsing failures should not silently become missing values: they need a separate error flag because “not supplied” and “supplied but unparseable” imply different processes.

3.2 Range, domain, and relational checks

A range check verifies that a value lies in a scientifically or operationally plausible interval. A domain check verifies membership in a permitted set, such as {single, married, divorced}. Relational checks use more than one field: a newborn’s age cannot be negative, a transaction date should fall within the account’s active period, and total cost should normally equal the sum of component costs within an allowed rounding tolerance.

Hard constraints are logically necessary; soft constraints identify cases for review. For example, a body temperature outside a physiological range may be an instrument error, but an unusually high value is not automatically impossible.

3.3 Duplicate records and entity resolution

Exact duplicates can often be removed after confirming that the duplicated rows represent the same event. Near-duplicates require entity resolution: matching records using identifiers, names, addresses, timestamps, or probabilistic linkage. Two purchases by the same customer are not duplicate customer records, so the unit of analysis must be established first.

A deterministic rule might retain the record with the latest trusted update. A probabilistic linkage method assigns a match score based on agreement patterns, but uncertain matches should be reviewed or retained with a linkage-status field. Incorrect deduplication can destroy genuine repeated events.

3.4 Inconsistent categories and labels

Categories may differ by capitalization, spelling, abbreviations, language, or historical coding. A mapping table should be versioned rather than replaced by undocumented string manipulation. “Unknown,” “not applicable,” “not asked,” and a genuinely missing response are analytically distinct states and should not automatically be collapsed.

Category harmonization also requires semantic review. “Part-time” in one source may mean fewer than 30 hours per week, whereas another source may use fewer than 20 hours. Identical labels do not guarantee identical meanings.

4. Outliers, Robustness, and Influence

An outlier is an observation unusually distant from the rest of a distribution. It may be an error, a rare but valid case, or evidence of a separate population. Outlier detection is therefore not equivalent to outlier deletion.

For a quantitative variable, the interquartile range is:

where Q₁ and Q₃ are the first and third quartiles. A commonly used screening rule flags observations below Q₁ − 1.5×IQR or above Q₃ + 1.5×IQR. This is a screening convention, not proof of error; it is most useful for distributions where quartiles are meaningful and can be misleading for highly discrete or multimodal data.

The standardized score is:

where x̄ is the sample mean and s is the sample standard deviation. It is sensitive to outliers because both the mean and standard deviation can be distorted. Robust alternatives include the median and the median absolute deviation (MAD):

For approximately normal data, a robust standardized score is sometimes defined as 0.6745(x_i − median(x))/MAD, because 0.6745 approximately aligns MAD with the standard deviation under normality. This scaling should not be used when MAD is zero or when the normal-reference interpretation is inappropriate.

Treatment options include source correction, transformation, winsorisation, robust estimation, stratified analysis, or retention with an influence assessment. Winsorisation replaces values beyond specified quantiles with boundary values; it changes the estimand and must be reported. In regression, leverage and influence diagnostics should complement univariate screening because a moderate-looking value can strongly affect a fitted relationship.

5. Missing Data: Concepts and Mechanisms

Missingness may be represented by a missingness indicator R_j, where R_j = 1 if variable Y_j is observed and R_j = 0 otherwise. The mechanism concerns how R_j relates to observed and unobserved data.

Missing completely at random (MCAR)

Data are MCAR when missingness is unrelated to both observed and unobserved values. Informally, the missing cases are a random subset. Under MCAR, complete-case analysis is generally unbiased for many simple estimands, although it is less precise because the sample is smaller. MCAR is uncommon and should not be assumed merely because missingness percentages are similar across columns.

Missing at random (MAR)

Data are MAR when, conditional on observed variables, missingness does not depend on the unobserved value itself. Symbolically, with observed data Y_obs and missing data Y_mis:

This assumption is not directly testable from observed data alone. It becomes more plausible when relevant predictors of response are included, such as age, site, prior measurements, or treatment group.

Missing not at random (MNAR)

MNAR means missingness still depends on the unobserved value after conditioning on observed information. For example, people with very high debt may be less willing to report debt even after accounting for income and age. MNAR requires sensitivity analysis, external information, a selection model, a pattern-mixture model, or a scientifically justified restriction.

A missingness matrix, indicator summaries, group comparisons, and temporal plots can reveal patterns. However, statistical tests of missingness are not definitive evidence for MCAR or MAR because the unobserved values remain unavailable.

6. Strategies for Missing Values

6.1 Deletion

Listwise deletion removes any record with a missing value in variables used by an analysis. It is transparent and simple but reduces sample size and can bias results unless assumptions support it. Pairwise deletion uses all available pairs for particular calculations, but different statistics may be based on different samples and produce an incoherent covariance matrix.

Deletion is defensible when missingness is minimal, the analysis population is clearly defined, missingness is plausibly MCAR, or the missing record is outside the target population. It is not defensible merely because it is convenient.

6.2 Deterministic correction and domain-based reconstruction

Some missing values can be reconstructed from authoritative fields. For example, age may be calculated from a verified date of birth and assessment date. Such reconstruction is not statistical imputation, but it still requires validation of dates, units, and rounding rules. A derived value should retain its provenance and a flag indicating that it was reconstructed.

6.3 Simple statistical imputation

Mean imputation replaces a missing value with the observed mean. Median imputation is more robust to skewness. Mode imputation can be used for categorical variables, but it increases the apparent frequency of the dominant category. These methods are easy to implement but typically underestimate variability and weaken relationships between variables.

Group-specific summaries can be more sensible when the grouping variable is known before imputation. Nevertheless, groups with few observations may produce unstable estimates, and grouping on a variable affected by missingness can introduce bias.

6.4 Regression and machine-learning imputation

Regression imputation predicts missing values from other variables. A deterministic prediction often lies too close to the conditional mean, thereby understating natural variation. Stochastic regression imputation adds a random residual drawn from an estimated error distribution, but its validity depends on the model and missingness assumptions.

Nearest-neighbour, random-forest, and other machine-learning methods can capture nonlinearities, but predictive accuracy is not the same as valid uncertainty representation. These methods must be evaluated with respect to the downstream estimand, not only prediction error.

6.5 Multiple imputation

Multiple imputation creates m completed datasets, analyses each one, and combines estimates. Let θ̂_l be the estimate from imputed dataset l, and let U_l be its estimated variance. Rubin’s combining rules are:

Here θ̄ is the pooled estimate, W is within-imputation variance, B is between-imputation variance, and T is total variance. These rules assume the imputations appropriately represent uncertainty under the chosen imputation model. The imputation model should generally be at least as rich as the analysis model, including the outcome, predictors, design variables, and variables related to missingness.

The number of imputations should reflect the amount of missing information; a small fixed number is not universally adequate. Convergence diagnostics, plausibility checks, and sensitivity analyses remain necessary.

7. Worked Example: Missing Examination Scores

Suppose five observed scores are 62, 68, 70, 75, and 85, with one score missing. The observed mean is:

Mean imputation inserts 72. The completed six-score dataset has mean 72, because the inserted value equals the observed mean. However, the imputed observation contributes no new spread around the mean, so the sample variance is smaller than it would be if the student’s true score had been, for example, 50 or 95. Consequently, mean imputation can make the class appear more homogeneous and can attenuate associations with other variables.

A better approach might use prior assessment, attendance, programme, and examination conditions in a multiple-imputation model. If the missing score occurred because students with poor performance were less likely to submit, MAR may be doubtful; a sensitivity analysis could examine lower plausible values for the missing score. The correct method depends on the research question: estimating the class mean, comparing programmes, or predicting future performance may require different models.

8. Case Study: Illustrative Hospital Readmission Dataset

Background

A hospital uses an integrated dataset to predict 30-day readmission. Variables include patient identifier, admission and discharge dates, age, diagnosis group, length of stay, laboratory values, insurance category, and readmission outcome.

Problem

Initial profiling finds duplicate patient identifiers, inconsistent diagnosis labels, missing laboratory values, negative lengths of stay, and extreme recorded ages. Missing laboratory values are more common in emergency admissions and in patients discharged quickly.

Analysis

The team first defines the unit of analysis as an admission rather than a patient. Exact duplicate admissions are removed only when admission timestamp, facility, and clinical fields agree. Near-duplicate identifiers are sent to a linkage review rather than automatically merged. Negative lengths of stay are traced to a date-parsing error involving day and month order.

Age values above 110 are checked against the source system. Two are transcription errors; one is a verified centenarian and is retained. Diagnosis labels are mapped using a clinical coding dictionary, with “not recorded” kept distinct from “not applicable.” Laboratory missingness is tabulated by admission route, ward, and discharge status, revealing a plausible MAR structure conditional on observed clinical variables.

Approach and solution

The corrected date rule is applied reproducibly. Laboratory values are imputed using multiple imputation with admission route, age, diagnosis, prior laboratory results, length of stay, and outcome included in the imputation model. The model is fitted separately within training folds to prevent leakage. Complete-case, single-imputation, and multiple-imputation analyses are compared. A sensitivity analysis assumes that unobserved laboratory values for early-discharge patients are systematically lower than MAR predictions.

Outcome

The estimated readmission effect is similar under complete-case and multiple-imputation analyses, but confidence intervals are wider after uncertainty is correctly propagated. A simple mean-imputation model produces an apparently more stable prediction model and poorer calibration for emergency admissions. The MNAR sensitivity analysis materially changes the estimated effect, prompting the hospital to treat missingness itself as a process-quality indicator rather than merely a nuisance.

Learning points

The case demonstrates that cleaning is inseparable from domain knowledge, leakage control, uncertainty estimation, and subgroup evaluation. A value can be unusual but valid; a missing value can carry operational information; and a model with strong overall accuracy can still be unsafe for a subgroup.

9. Evaluating Analytical Consequences

Every substantive cleaning decision should be assessed against the target estimand and the intended use. Important consequences include:

• Bias: systematic deviation from the population quantity of interest.
• Variance: sampling or estimation uncertainty; aggressive deletion may increase it.
• Attenuation: weakened relationships after noisy or mean-imputed variables are used.
• Selection effects: deleting cases can change the population represented.
• Calibration and discrimination: predictive performance may change differently across metrics.
• Fairness: missingness and cleaning may affect demographic groups unequally.

Compare sample size, distributions, correlations, outcome rates, model coefficients, confidence intervals, calibration, and subgroup metrics before and after treatment. Sensitivity analysis should include plausible alternative rules, not only the preferred pipeline. A result that appears only under one arbitrary outlier threshold should be reported as fragile.

10. Governance, Ethics, and Contemporary Practice

Cleaning can encode institutional values. Automatically replacing undocumented income with zero may penalise people whose data were not collected. Removing records from a minority subgroup may improve aggregate completeness while reducing representation. Privacy-preserving transformations can create legitimate irregularities that should not be “corrected” without understanding their purpose.

Good governance requires provenance, access control, retention rules, reproducible code, versioned dictionaries, and explicit responsibility for sign-off. Sensitive variables used to model missingness should be handled lawfully and proportionately, even when they are not included in the final predictive model. Documentation should state what was changed, why, by whom, and with what expected effect.

Modern data-quality systems increasingly use automated schema checks, data contracts, anomaly monitoring, lineage tools, and constraint-based validation. Artificial intelligence can assist with entity matching, code mapping, and anomaly ranking, but it should not silently decide whether a rare observation is wrong. Human review, benchmark datasets, confidence thresholds, and audit logs are essential. Generative systems are especially unsuitable as unverified sources for filling factual records.

Best-Practice Checklist

1. Preserve raw data and work on versioned copies.
2. Define the unit of analysis before deduplicating.
3. Record units, valid domains, and hard versus soft constraints.
4. Distinguish error, unknown, not applicable, and not collected.
5. Investigate outliers before removing them.
6. Diagnose missingness by subgroup and time, not only overall percentage.
7. Fit imputation models without using information from held-out data.
8. Propagate imputation uncertainty when inference is required.
9. Compare alternative cleaning choices through sensitivity analysis.
10. Report cleaning decisions as part of the analytical method.

Chapter Summary

Data cleaning is a measurement and governance activity as well as a technical operation. Reliable workflows profile data, apply explicit domain rules, preserve provenance, diagnose duplicates and inconsistent categories, and distinguish erroneous values from legitimate extremes. Outlier rules such as the IQR fence are screening tools, not automatic deletion policies.

Missingness must be considered through MCAR, MAR, and MNAR mechanisms. Deletion is simple but can reduce precision and introduce selection bias. Deterministic reconstruction is appropriate when authoritative information supports it. Mean, median, and mode imputation are convenient but often understate variability. Regression, machine-learning, and multiple imputation can improve realism, provided their assumptions, uncertainty, leakage risks, and sensitivity to the missingness mechanism are evaluated.

The final quality criterion is not a visually tidy dataset but a defensible analytical result. Cleaning choices should be documented, validated, compared under plausible alternatives, and examined for effects on accuracy, uncertainty, representation, and fairness.

Key Terms

Data cleaning: Systematic detection, correction, transformation, or treatment of data-quality problems.

Data provenance: Information about a datum’s origin, transformations, and responsible processes.

Validity: Conformity to permitted formats, domains, and rules.

Completeness: Degree to which required data are present.

Duplicate: More than one record representing the same intended entity or event.

Entity resolution: Determining whether records refer to the same real-world entity.

Outlier: An observation unusually distant from the distribution of other observations.

Influence: The extent to which an observation affects an estimated result.

Winsorisation: Replacing extreme values with specified boundary values.

Missingness indicator: A variable recording whether a value is observed or missing.

MCAR: Missing completely at random.

MAR: Missing at random conditional on observed information.

MNAR: Missing not at random after conditioning on observed information.

Listwise deletion: Removing records missing any variable required for an analysis.

Imputation: Replacing missing values with estimated or reconstructed values.

Multiple imputation: Creating and analysing several plausible completed datasets.

Within-imputation variance: Average estimation variance across imputed datasets.

Between-imputation variance: Variation in estimates across imputed datasets.

Sensitivity analysis: Examination of how conclusions change under plausible assumptions.

Data leakage: Use of information during training that would not be available at prediction or evaluation time.

Review Questions

Short Answer Questions

1. Distinguish validity from accuracy in data quality.
2. Why should the unit of analysis be established before deduplication?
3. What does MAR mean in the context of missing data?
4. State one limitation of mean imputation.
5. Why is an outlier not automatically an error?

Long Answer Questions

1. Explain a complete, reproducible workflow for cleaning an integrated analytical dataset.
2. Compare listwise deletion, deterministic reconstruction, simple imputation, and multiple imputation.
3. Explain how MCAR, MAR, and MNAR differ and why the distinction matters.
4. Discuss the statistical and ethical consequences of deleting records with missing values.
5. Explain Rubin’s combining rules and the roles of within- and between-imputation variance.

Analytical / Application Questions

1. A survey has 25% missing income values, concentrated among respondents with high reported expenses. Diagnose the likely missingness concern and propose an analysis and sensitivity strategy.
2. A hospital dataset contains duplicate patient IDs, impossible dates, and laboratory values missing more often among emergency admissions. Design a cleaning and validation plan that avoids leakage and preserves an audit trail.
3. Two models produce identical average accuracy, but one uses mean imputation and performs substantially worse for a minority subgroup. Analyse possible causes and recommend additional evaluations.

Multiple Choice Questions with Answers

Question 1

Which condition defines missing at random (MAR)?

A. Missingness is unrelated to all observed and unobserved values.

B. Missingness depends only on the unobserved value.

C. After conditioning on observed variables, missingness does not depend on the unobserved value.

D. Every variable has the same percentage of missing values.

Correct Answer: C. After conditioning on observed variables, missingness does not depend on the unobserved value.

Question 2

For the values 10, 12, 13, 15, and 50, which measure is generally most robust to the extreme value 50?

A. Mean

B. Median

C. Variance

D. Standard deviation

Correct Answer: B. Median

Question 3

What is the main statistical weakness of mean imputation?

A. It always increases sample size incorrectly.

B. It usually understates variability and can weaken associations.

C. It can only be used for categorical variables.

D. It guarantees unbiased estimates under MNAR.

Correct Answer: B. It usually understates variability and can weaken associations.

Question 4

If Q₁ = 20 and Q₃ = 32, what is the upper 1.5×IQR screening fence?

A. 38

B. 44

C. 50

D. 56

Correct Answer: C. 50

Question 5

Which action is most appropriate for a verified but unusually high age?

A. Delete it automatically using a z-score rule.

B. Replace it with the sample mean.

C. Retain it and document its verification.

D. Convert it to a missing value.

Correct Answer: C. Retain it and document its verification.

Question 6

In Rubin’s combining rules, between-imputation variance measures:

A. Variation among estimates from different imputed datasets.

B. Measurement precision within one completed dataset only.

C. The number of missing records.

D. The percentage of duplicated rows.

Correct Answer: A. Variation among estimates from different imputed datasets.

Question 7

Why can pairwise deletion be problematic?

A. It always removes every incomplete record.

B. Different statistics may use different subsets of records.

C. It cannot calculate any correlation.

D. It converts categorical variables to numeric variables.

Correct Answer: B. Different statistics may use different subsets of records.

Question 8

Which practice best prevents evaluation leakage during imputation for predictive modelling?

A. Fit the imputation model on the entire dataset before splitting.

B. Use the outcome from the future deployment period.

C. Fit imputation procedures within the training data of each fold.

D. Replace all missing values with zero after testing.

Correct Answer: C. Fit imputation procedures within the training data of each fold.

Question 9

Which is a hard relational validation rule?

A. A value is above the sample mean.

B. A category is less frequent than another category.

C. Discharge occurs before admission.

D. A record belongs to a small subgroup.

Correct Answer: C. Discharge occurs before admission.

Question 10

Why is MNAR difficult to establish from observed data alone?

A. It requires no assumptions.

B. The missing values needed to evaluate the dependence are unobserved.

C. It occurs only in categorical variables.

D. It is identical to MCAR.

Correct Answer: B. The missing values needed to evaluate the dependence are unobserved.