6.2 Data Cleansing & Anomaly Remediation

Data Cleansing & Anomaly Remediation

Once data quality profiling has identified structural defects, missing values, extreme outliers, and entity discrepancies, the Certified Health Data Analyst (CHDA) must design and execute robust, scientifically defensible data cleansing and remediation workflows. In healthcare analytics, data remediation is governed by a fundamental ethical and statistical mandate: the analyst must correct technical defects and standardize disparate representations without introducing statistical bias, distorting underlying clinical distributions, or erasing clinically meaningful signals.

---

1. Missing Data Mechanisms in Healthcare (Rubin's Taxonomy)

Missing data is ubiquitous across clinical and operational healthcare databases. Clinicians may omit optional flowsheet entries, patients may skip sensitive survey questions, or diagnostic tests may not be clinically indicated for healthy individuals. Donald Rubin established the foundational statistical taxonomy that classifies missing data mechanisms into three distinct categories based on the relationship between the observed data, the missing values, and the probability of missingness:

+---------------------------------------------------------------------------------------------------+
|                         RUBIN'S MISSING DATA MECHANISMS IN HEALTHCARE                             |
+-----------------------------------+-----------------------------------+---------------------------+
| 1. MISSING COMPLETELY AT RANDOM   | 2. MISSING AT RANDOM (MAR)        | 3. MISSING NOT AT RANDOM  |
|    (MCAR)                         |                                   |    (MNAR)                 |
| - Missingness is entirely random  | - Missingness depends on OBSERVED | - Missingness depends on  |
| - Unrelated to observed or        |   variables (e.g., age, sex, unit)|   the UNOBSERVED value    |
|   unobserved clinical values      | - Conditionally random given      |   itself                  |
| - Example: Dropped lab test tube  |   observed covariates             | - Non-ignorable missingness|
| - Deletion retains unbiased mean  | - Example: Vitals missing in young| - Example: Severe depressed|
|   (but loses statistical power)   |   low-acuity ambulatory visits    |   patients skipping PHQ-9 |
+-----------------------------------+-----------------------------------+---------------------------+

1. Missing Completely at Random (MCAR)

• Statistical Definition: The probability of a data point being missing is completely independent of both observed covariates and the unobserved value itself:

$$P(M = 1 \mid Y_{\text{obs}}, Y_{\text{mis}}, X) = P(M = 1)$$

Where $M$ is the missingness indicator ($1 = \text{missing}, 0 = \text{observed}$), $Y_{\text{obs}}$ represents observed clinical outcomes, $Y_{\text{mis}}$ represents missing values, and $X$ represents observed covariates.

• Healthcare Clinical Example: A blood specimen tube is accidentally dropped and shattered on the laboratory floor; a scheduled nightly database backup process experiences a transient network outage, dropping a random 2-minute stream of telemetry packets.
• Impact: The missing records represent a completely unbiased, random subsample of the total population. While statistical power is reduced due to smaller sample sizes, parameter estimates (e.g., sample mean) remain unbiased.

2. Missing at Random (MAR)

• Statistical Definition: The probability of missingness depends systematically on observed patient characteristics or covariates ($X, Y_{\text{obs}}$), but is conditionally independent of the unobserved missing value itself ($Y_{\text{mis}}$):

$$P(M = 1 \mid Y_{\text{obs}}, Y_{\text{mis}}, X) = P(M = 1 \mid Y_{\text{obs}}, X)$$

• Healthcare Clinical Example: In an outpatient EHR dataset, blood pressure readings are frequently missing for young, healthy patients presenting for routine orthopedic consultations, but are consistently captured for elderly diabetic patients presenting to cardiology. Once patient age, visit type, and primary diagnosis are controlled for in a regression model, the missingness of blood pressure is random.
• Impact: Simple deletion introduces substantial selection bias. However, because the missingness mechanism is fully explained by observed variables, advanced statistical imputation (e.g., Multiple Imputation by Chained Equations [MICE]) yields unbiased parameter estimates.

3. Missing Not at Random (MNAR / Non-Ignorable Missingness)

• Statistical Definition: The probability of missingness depends directly on the unobserved value itself ($Y_{\text{mis}}$), even after controlling for all observed covariates:

$$P(M = 1 \mid Y_{\text{obs}}, Y_{\text{mis}}, X) \neq P(M = 1 \mid Y_{\text{obs}}, X)$$

• Healthcare Clinical Example: In a psychiatric depression study, patients experiencing severe, debilitating depressive episodes are far more likely to skip their scheduled follow-up appointments and omit the Patient Health Questionnaire (PHQ-9) depression severity survey. The missingness is directly caused by the high severity of the unobserved depression score itself. Similarly, patients with active substance use disorders frequently omit self-reported illicit drug history.
• Impact: MNAR introduces severe, systematic bias that cannot be eliminated through standard imputation models. Remediation requires specialized econometric selection models (Heckman selection models), pattern-mixture modeling, or sensitivity analyses.

---

2. Missing Data Mechanisms Comparison Matrix

---

3. Remediation Strategies for Missing Data

When confronting missing data, health data analysts must select an appropriate remediation technique based on the underlying missingness mechanism, sample size, and analytical objective.

+---------------------------------------------------------------------------------------------------+
|                         MISSING DATA REMEDIATION TAXONOMY                                         |
+-----------------------------------+-----------------------------------+---------------------------+
| DELETION APPROACHES               | BASIC IMPUTATION                  | ADVANCED / ML IMPUTATION  |
| - Listwise Deletion (Complete Case| - Mean / Median / Mode Imputation | - Stochastic Regression   |
| - Pairwise Deletion (Available    |   (Deflates variance, Type I error| - MICE (Rubin's Rules)    |
|   Case Analysis)                  | - Last Observation Carried Forward| - k-NN Imputation         |
| - Risk: Severe bias under MAR/MNAR|   (LOCF - Distorts trajectories)  | - MissForest (Random Frst)|
+-----------------------------------+-----------------------------------+---------------------------+

1. Deletion Approaches

• Listwise Deletion (Complete Case Analysis): Discards any patient record that contains a missing value in any of the variables selected for the analysis.
  • Critique: While standard in legacy software, listwise deletion can eliminate 30% to 70% of a clinical study cohort if many variables are analyzed. It produces severely biased parameter estimates unless the data are strictly MCAR.
• Pairwise Deletion (Available Case Analysis): Utilizes all available cases for each specific bivariate calculation (e.g., calculating the correlation between Variable A and Variable B using all cases with both present, while using a different subset for Variable B and Variable C).
  • Critique: Produces mathematically inconsistent correlation matrices (non-positive definite matrices) that can cause multivariate algorithms (such as factor analysis or linear regression) to fail.

2. Basic Single Imputation Techniques and Their Limitations

• Mean / Median / Mode Imputation: Replaces missing values with the arithmetic mean (for normally distributed variables), median (for skewed continuous variables), or mode (for categorical variables) of the observed values.
  • Severe Statistical Pitfalls: Single central-tendency imputation artificially clusters data points at a single value, severely deflating the sample variance, artificially compressing standard errors, narrowing confidence intervals, and inflating Type I error rates (false positives). Furthermore, it attenuates and distorts covariance and correlation structures between variables.
• Last Observation Carried Forward (LOCF) & Baseline Observation Carried Forward (BOCF): Common in longitudinal clinical trial datasets, LOCF imputes a missing follow-up measurement with the patient's most recently observed prior value.
  • Severe Clinical Pitfalls: In chronic degenerative diseases (such as Alzheimer's dementia or progressive renal failure), LOCF falsely assumes that the patient's clinical state remained completely static, leading to dangerous overestimation of treatment efficacy.

3. Advanced Imputation: Multiple Imputation by Chained Equations (MICE)

Multiple Imputation, developed by Donald Rubin, is the gold standard methodology for handling multivariate missingness under the Missing at Random (MAR) assumption.

+---------------------------------------------------------------------------------------------------+
|                         THE THREE STAGES OF MULTIPLE IMPUTATION (MICE)                            |
+---------------------------------------------------------------------------------------------------+
  [INCOMPLETE DATASET]  --> [1. IMPUTATION (m copies)] --> [2. ANALYSIS]        --> [3. POOLING]
  Patient | Age | HbA1c     Dataset 1: Imputed (MICE)       Model 1: Estimates       Rubin's Rules
  101     | 62  | [NULL]    Dataset 2: Imputed (MICE)  -->  Model 2: Estimates  -->  Pooled Point
  102     | 45  | 7.2       Dataset 3: Imputed (MICE)       Model 3: Estimates       Estimates & Pooled
  103     | 71  | [NULL]    Dataset m: Imputed (MICE)       Model m: Estimates       Standard Errors
+---------------------------------------------------------------------------------------------------+

1. Step 1: Imputation ($m$ Datasets Generated): The algorithm generates $m$ complete datasets (typically $m = 5$ to $20$). Each missing value is imputed using a series of chained univariate conditional regression models (predictive mean matching for continuous variables, logistic regression for binary variables, multinomial regression for nominal categories) that draw from the posterior predictive distribution, incorporating random residual error to preserve natural variance.
2. Step 2: Analysis ($m$ Identical Models Fitted): The analyst fits the planned statistical or machine learning model independently across each of the $m$ completed datasets, generating $m$ separate parameter estimates ($Q_1, Q_2, \dots, Q_m$) and variance estimates ($U_1, U_2, \dots, U_m$).
3. Step 3: Pooling (Rubin's Rules): The $m$ separate results are mathematically pooled into a single consolidated estimate and standard error using Rubin's Rules:
   • Pooled Point Estimate ($\bar{Q}$): $$\bar{Q} = \frac{1}{m} \sum_{i=1}^{m} \hat{Q}_i$$
   • Within-Imputation Variance ($\bar{U}$): The average variance across the $m$ models: $$\bar{U} = \frac{1}{m} \sum_{i=1}^{m} U_i$$
   • Between-Imputation Variance ($B$): The variance of the parameter estimates across the $m$ datasets: $$B = \frac{1}{m - 1} \sum_{i=1}^{m} (\hat{Q}_i - \bar{Q})^2$$
   • Total Pooled Variance ($T$): Incorporates both within-imputation uncertainty and the between-imputation uncertainty introduced by missingness: $$T = \bar{U} + \left(1 + \frac{1}{m}\right) B$$

4. Non-Parametric & Machine Learning Imputation

• k-Nearest Neighbors (k-NN) Imputation: Identifies the $k$ most similar complete patient records based on a multidimensional distance metric (e.g., Gower's distance for mixed continuous and categorical healthcare data) and imputes the weighted average or mode of the neighbors. Highly effective for non-linear clinical relationships.
• MissForest (Iterative Random Forest Imputation): An ensemble machine learning algorithm that fits random forests across all variables iteratively, accommodating complex high-order interactions and non-linearities without requiring parametric distributional assumptions.

---

4. Outlier Detection and Handling in Healthcare

Outliers are observations that deviate markedly from the overall distribution of the dataset. In health data analytics, outliers arise from two fundamentally different sources: data generation/entry errors (e.g., typing errors, specimen hemolysis) and true acute clinical extremes (e.g., septic shock, catastrophic trauma).

+---------------------------------------------------------------------------------------------------+
|                         STATISTICAL OUTLIER DETECTION METHODOLOGIES                               |
+-------------------------------------------------+-------------------------------------------------+
| Z-SCORE METHOD (PARAMETRIC)                     | INTERQUARTILE RANGE (IQR) RULE (NON-PARAMETRIC)  |
| - Assumes approximate Normal (Gaussian) dist    | - Robust against skewed, non-normal distributions|
| - Z = (X - Mean) / StdDev                       | - IQR = Q3 - Q1                                  |
| - Mild Outlier: |Z| > 3.0                       | - Lower Fence: Q1 - 1.5 * IQR                    |
| - Extreme Outlier: |Z| > 3.29 (p < 0.001)       | - Upper Fence: Q3 + 1.5 * IQR                    |
| - Sensitive to extreme values distorting Mean   | - Extreme Outliers: Q1 - 3.0*IQR / Q3 + 3.0*IQR  |
+-------------------------------------------------+-------------------------------------------------+

1. Statistical Outlier Detection Methods

• Parametric Z-Score Method: Calculates the number of standard deviations an observation falls from the mean: $$Z = \frac{X - \mu}{\sigma}$$ Rule: Observations with $|Z| > 3.0$ (or $|Z| > 3.29$, representing $p < 0.001$) are flagged as statistical outliers. Limitation: The mean ($\mu$) and standard deviation ($\sigma$) are themselves highly sensitive to extreme outliers, which can cause masking.
• Non-Parametric Interquartile Range (IQR) Rule (Tukey's Fences): Calculates the spread of the middle 50% of the data: $$\text{IQR} = Q_3 - Q_1$$ $$\text{Lower Outer Bound} = Q_1 - 1.5 \times \text{IQR}, \quad \text{Upper Outer Bound} = Q_3 + 1.5 \times \text{IQR}$$ $$\text{Extreme Outer Bound} = Q_3 + 3.0 \times \text{IQR}$$ Advantage: The IQR rule is robust and unaffected by extreme values, making it ideal for right-skewed healthcare metrics such as Length of Stay (LOS) and Total Inpatient Billed Charges.

2. Clinical Plausibility vs. True Extremes

Health data analysts must never blindly delete statistical outliers without clinical contextual review:

• Physiologically Impossible Value (Error): A recorded Serum Potassium of 28.0 mmol/L (lethal above 9.0 mmol/L) or a heart rate of 450 bpm. Root cause: in vitro specimen hemolysis or keypunch error. Action: Set to null and flag for clinical data integrity review.
• Clinically Extreme Value (Valid Critical Condition): A patient presenting to the emergency department with a blood glucose of 1,150 mg/dL in Diabetic Ketoacidosis (DKA), or a troponin level of 45.0 ng/mL in massive ST-elevation myocardial infarction (STEMI). Action: Retain in clinical analysis; apply robust statistical methods.

3. Outlier Remediation Techniques

• Trimming (Truncation): Removing outlier records from the analytical dataset. Only justifiable when records represent verified data collection errors.
• Winsorization: Replaces extreme outlier values beyond specified percentiles with the value at that percentile threshold. For example, in a 99% Winsorization, all values above the 99th percentile are set equal to the 99th percentile value, and all values below the 1st percentile are set to the 1st percentile value. This preserves sample size and reduces variance distortion without discarding clinical records.
• Mathematical Transformation: Applying monotonic mathematical transformations (such as natural log transformation $\ln(X + 1)$, square root $\sqrt{X}$, or Box-Cox power transformations) to stabilize variance and normalize heavy right-skewed distributions (e.g., healthcare financial charges).

---

5. Deduplication & Patient Identity Resolution

Patient identification errors across disparate healthcare IT systems result in fragmented medical histories (duplicate records) or dangerous cross-patient contamination (overlay records). Enterprise Master Person Index (EMPI) engines utilize record linkage algorithms to resolve identities.

+---------------------------------------------------------------------------------------------------+
|                         RECORD LINKAGE & IDENTITY RESOLUTION PIPELINE                             |
+---------------------------------------------------------------------------------------------------+
  [DISPARATE SOURCE RECORDS] --> [1. BLOCKING]          --> [2. STRING COMPARISON]  --> [3. MATCH DECISION]
  - EHR Alpha: Jhn Smyth         Reduce comparison space:    - Levenshtein Distance     - Probabilistic Score
  - LIS Beta:  John Smith        Match on Soundex(LastName)  - Jaro-Winkler (Names)     - Score > T_high: Auto-Merge
  - Billing:   J. Smith          + Birth Year (1968)         - Exact Match (DOB, SSN)   - T_low < Score < T_high: Review
                                                                                        - Score < T_low: Non-Match
+---------------------------------------------------------------------------------------------------+

1. Deterministic vs. Probabilistic Record Linkage

• Deterministic Linkage: Relies on exact character matching across predefined key combinations (e.g., Rule 1: Exact Match on SSN; Rule 2: Exact Match on First_Name + Last_Name + DOB + Zip_Code). While highly specific, deterministic linkage yields high false-negative rates when typographical errors or name changes occur.
• Probabilistic Linkage (Fellegi-Sunter Methodology): Calculates statistical weights representing the likelihood that two records belong to the same individual given their agreement or disagreement across multiple demographic attributes:
  • Agreement Weight ($w_{\text{agree}}$): $\log_2 \left( \frac{m_i}{u_i} \right)$, where $m_i = P(\text{field agrees} \mid \text{true match})$ and $u_i = P(\text{field agrees} \mid \text{true non-match})$.
  • Disagreement Weight ($w_{\text{disagree}}$): $\log_2 \left( \frac{1 - m_i}{1 - u_i} \right)$.
  • Composite match scores are compared against two thresholds:
    1. Upper Threshold ($T_{\text{high}}$): Records with score $\ge T_{\text{high}}$ are automatically merged.
    2. Lower Threshold ($T_{\text{low}}$): Records with score $< T_{\text{low}}$ are designated non-matches.
    3. Manual Review Band ($T_{\text{low}} \le \text{Score} < T_{\text{high}}$): Flagged for manual review by HIM data integrity specialists.

2. String Similarity and Phonetic Algorithms

• Levenshtein Distance: The minimum number of single-character edit operations (insertions, deletions, substitutions) required to transform one string into another (e.g., transforming "Smith" to "Smyth" has a Levenshtein distance of 1).
• Jaro-Winkler Similarity: Measures string similarity based on the number of matching characters and transpositions, adding a prefix bonus for strings that agree from the beginning. Outputs a normalized score between 0.0 (no similarity) and 1.0 (identical). Ideal for comparing human first and last names (e.g., "Katherine" vs "Catherine").
• Soundex & Double Metaphone: Phonetic indexing algorithms that encode words based on their English acoustic pronunciation, converting names into an alphanumeric code (e.g., "Smith" and "Smyth" both encode to S530).

3. Patient Record Merge Protocol

When merging duplicate patient records (e.g., merging duplicate MRN 1001 into surviving master MRN 2002), the master database must:

1. Re-link all historical clinical encounters, diagnostic lab orders, surgical notes, and billing claims from the retired MRN to the surviving master MRN.
2. Maintain an immutable crosswalk mapping table storing retired identifiers to preserve historical auditability.
3. Broadcast an HL7 v2 ADT^A40 (Merge Patient Information) message across all connected downstream ancillary systems (LIS, RIS, PIS, Billing) to synchronize the merge across the entire enterprise.

---

6. Healthcare Standardization and Normalization

To enable interoperability and analytics, disparate operational formats must be cleansed and standardized to authoritative national standards:

1. Address Standardization (USPS CASS)

Addresses must be cleansed and standardized against the United States Postal Service (USPS) Coding Accuracy Support System (CASS) guidelines:

• Converting street suffixes to standard abbreviations (Street to ST, Avenue to AVE, Boulevard to BLVD, Suite to STE).
• Standardizing directional indicators (Northwest to NW).
• Appending 5-digit ZIP codes with accurate 4-digit delivery point routing codes (ZIP+4), essential for socioeconomic geocoding and social determinants of health (SDOH) area deprivation indexing.

2. International Phone Number Standardization (ITU-T E.164)

Standardizing raw user inputs (e.g., "(555) 382-9100", "555.382.9100", "5553829100") into standard international E.164 format:

+15553829100  [+ <Country Code 1> <10-Digit National Number>]

3. USCDI v3/v4 Sex and Gender Representation

Under the Office of the National Coordinator for Health IT (ONC) United States Core Data for Interoperability (USCDI) standards, health data models must decouple biological sex from gender identity:

• Sex Assigned at Birth: Biological sex assigned at birth recorded using standard HL7/OMB code sets (Male, Female, Unknown).
• Legal Sex: Current administrative sex utilized for insurance billing and claim adjudication.
• Gender Identity: Self-identified gender identity mapped to SNOMED CT concepts (Identifies as Male, Identifies as Female, Non-Binary, Transgender Male, Transgender Female, Additional Gender Category).
• Sexual Orientation & Pronouns: Discrete USCDI data elements capturing patient preferences.

4. Unit of Measure Conversion (UCUM)

Clinical laboratories and biomedical devices report measurements in disparate engineering units that must be normalized to standard Unified Code for Units of Measure (UCUM) representations:

• Blood Glucose Conversion: $$\text{Glucose (mg/dL)} = \text{Glucose (mmol/L)} \times 18.0182$$
• Serum Creatinine Conversion: $$\text{Creatinine (mg/dL)} = \frac{\text{Creatinine (}\mu\text{mol/L)}}{88.4}$$
• Body Weight Conversion: $$\text{Weight (kg)} = \frac{\text{Weight (lbs)}}{2.20462}$$
• Body Temperature Conversion: $$\text{Temp (}^{\circ}\text{C)} = (\text{Temp (}^{\circ}\text{F)} - 32) \times \frac{5}{9}$$