Cleaning Data and Handling Missing Values

Cleaning data and handling missing values turns raw rows into feature values that a model can learn from without silently changing the problem. The practical outcome is a repeatable preprocessing pipeline that records what was cleaned, learns replacement values only from training data, and applies the same rules to validation, test, and future prediction rows.

In machine learning this is not cosmetic housekeeping. A missing age, a negative transaction amount, a duplicated customer row, or a category spelled three ways changes the distribution seen by the algorithm. Tree models can sometimes route missing values directly, while linear models and neural networks usually require numeric arrays with no undefined values. The data preparation goal is to preserve real signal while preventing missingness, outliers, and inconsistent encodings from becoming accidental leakage or noise.

Why Values Go Missing

Before choosing deletion or imputation, identify the missingness mechanism. MCAR, missing completely at random, means absence is unrelated to observed or unobserved values, such as a sensor packet lost during a brief network outage. MAR, missing at random, means absence is explainable by observed fields, such as income being more often blank for younger applicants. MNAR, missing not at random, means absence depends on the value itself or on an unobserved cause, such as high-income customers declining to report income.

This distinction affects model design. Deleting MCAR rows may be acceptable if enough data remains. For MAR data, conditional imputation by group can be better than one global mean. For MNAR data, the fact that the value is missing may itself be predictive, so a missingness indicator or explicit Unknown category can carry legitimate information. The rule is not “fill every blank”; it is “represent what the model will know at prediction time.”

Internal Mechanics of a Cleaning Pipeline

A robust cleaning step has two phases: fit and transform. During fit, the transformer inspects only the training split and stores learned state, such as medians, most-frequent categories, allowed category names, clipping thresholds, or duplicate-key rules. During transform, it applies that stored state to new data. This separation prevents validation or test rows from influencing the imputation values and making evaluation look better than deployment.

Numeric imputers usually replace missing values with a statistic such as mean, median, or a domain constant. The median is resistant to outliers, while the mean preserves the arithmetic center when the distribution is symmetric. Categorical imputers commonly use the mode or a sentinel such as Missing. A missingness indicator is a new binary feature such as age_was_missing; it lets the model distinguish a real median-valued age from an imputed one.

Cleaning also includes consistency rules around missingness. Blank strings, whitespace, None, NaN, impossible dates, and sentinel values like -999 may all mean “not observed,” but only if the data dictionary says so. Duplicate removal should use a business key and timestamp policy, not just full-row equality. Outlier handling should be tied to measurement limits or model sensitivity, because an unusual value may be the signal the model needs.

API Anatomy

In Python, the common shape is a table-like object, a set of columns, and a transformer that is fitted inside a pipeline. The important design choice is that preprocessing belongs inside cross-validation, not before it. If medians are computed once on the whole dataset and then evaluated by folds, every fold has already leaked information from its held-out rows.

import pandas as pd
from sklearn.impute import SimpleImputer

train = pd.DataFrame({"age": [22, None, 45, 36], "fare": [7.25, 8.05, None, 26.0]})
imputer = SimpleImputer(strategy="median")
imputer.fit(train)

print(imputer.statistics_.tolist())
print(imputer.transform(pd.DataFrame({"age": [None, 50], "fare": [9.0, None]})).tolist())

The fitted statistics are [36.0, 8.05]. The transformed rows become [[36.0, 9.0], [50.0, 8.05]]. The input order matters: a transformer stores statistics by column position or name depending on the library path, so production code should keep a stable schema and reject unexpected columns instead of guessing.

Worked Example 1: Inspect Missingness

The first example profiles where values are absent and whether a sentinel needs to be normalized. It also shows why counts alone are incomplete; missingness by target or subgroup can reveal bias or leakage risks.

import pandas as pd

raw = pd.DataFrame({
    "customer_id": [1, 2, 3, 4, 5],
    "age": [29, None, 41, -999, 35],
    "plan": ["basic", "", "pro", "basic", None],
    "churned": [0, 1, 0, 0, 1],
})

clean = raw.replace({"age": {-999: pd.NA}, "plan": {"": pd.NA}})
print(clean.isna().sum().to_dict())
print(clean.groupby("churned")["plan"].apply(lambda s: s.isna().mean()).to_dict())

The missing counts are {'customer_id': 0, 'age': 2, 'plan': 2, 'churned': 0}. The grouped rates show that plan is missing for all churned rows and for none of the non-churned rows in this tiny sample. That does not prove causation, but it does warn you to check data collection timing: if plan is blank only after account closure, using it may leak post-outcome information.

Worked Example 2: Build a Column-Specific Imputer

The second example uses separate strategies for numeric and categorical features. Numeric age gets a median plus an indicator. Categorical plan gets an explicit missing category because absence can carry behavioral signal and because one-hot encoders need a stable category vocabulary.

import pandas as pd
from sklearn.compose import ColumnTransformer
from sklearn.impute import SimpleImputer
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import OneHotEncoder

X_train = pd.DataFrame({"age": [29, None, 41, None, 35], "plan": ["basic", None, "pro", "basic", None]})

numeric = SimpleImputer(strategy="median", add_indicator=True)
categorical = Pipeline([
    ("impute", SimpleImputer(strategy="constant", fill_value="Missing")),
    ("onehot", OneHotEncoder(handle_unknown="ignore", sparse_output=False)),
])

preprocess = ColumnTransformer([
    ("num", numeric, ["age"]),
    ("cat", categorical, ["plan"]),
])

matrix = preprocess.fit_transform(X_train)
print(matrix.tolist())
print(preprocess.get_feature_names_out().tolist())

The first numeric column is age after median imputation, and the second numeric column is the age-missing indicator. The categorical columns represent Missing, basic, and pro. Rows with missing age contain 1.0 in the indicator column, so the model can learn a different weight for imputed age if the training data supports it.

Worked Example 3: Fit Cleaning With the Model

The third example places preprocessing and the estimator in one pipeline. This is the pattern to use for train-test splits and cross-validation because every fitted object is learned only from the training rows passed into fit.

import pandas as pd
from sklearn.compose import ColumnTransformer
from sklearn.impute import SimpleImputer
from sklearn.linear_model import LogisticRegression
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import OneHotEncoder

X = pd.DataFrame({
    "age": [29, None, 41, None, 35, 52],
    "plan": ["basic", None, "pro", "basic", None, "pro"],
})
y = [0, 1, 0, 0, 1, 0]

features = ColumnTransformer([
    ("age", SimpleImputer(strategy="median", add_indicator=True), ["age"]),
    ("plan", Pipeline([
        ("impute", SimpleImputer(strategy="constant", fill_value="Missing")),
        ("onehot", OneHotEncoder(handle_unknown="ignore")),
    ]), ["plan"]),
])

model = Pipeline([("features", features), ("classifier", LogisticRegression())])
model.fit(X, y)
print(model.predict(pd.DataFrame({"age": [None, 46], "plan": ["basic", "enterprise"]})).tolist())

The code prints a deterministic list of two class predictions for this dataset. The unknown enterprise plan is ignored by the one-hot encoder instead of crashing, while the missing age is filled by the fitted median and flagged by the indicator. In a real project, you would evaluate this pipeline with a held-out split or cross-validation before trusting those predictions.

Design Choices and Trade-offs

Row deletion is simple and keeps observed values unchanged, but it can shrink the dataset and bias it toward easier-to-measure cases. Column deletion is reasonable when a feature is mostly absent, expensive to repair, and weakly related to the target; it is dangerous when absence is itself meaningful. Mean and median imputation keep matrix dimensions stable, but they reduce variance and can distort correlations. Model-based imputation can use other features to estimate missing values, but it adds complexity and can overfit if fitted outside the training fold.

Explicit missing categories are often strong for categorical features, especially when “not provided” is a user action. They can also encode unfair or sensitive process differences, so inspect missingness across protected or operationally important groups where allowed. Missingness indicators help linear models represent the difference between observed and imputed values, but too many indicators can add noise when missingness is rare.

Failure Modes and Troubleshooting

A common symptom is an evaluation score that drops sharply after deployment. The cause is often leakage: imputation, scaling, or deduplication was fitted on all rows before splitting. Diagnose by reviewing the pipeline order and checking whether any transformer calls fit before the train-test split. Correct it by moving all learned preprocessing into a pipeline evaluated inside the split or cross-validation loop.

Another symptom is a runtime error such as “input contains NaN.” The cause is usually an uncovered column, a new sentinel value, or a transformer that emits missing values after imputation. Diagnose by printing missing counts after each transformation stage on a small failing batch. Correct it by normalizing all documented missing tokens, adding the missing column to the transformer, or adding validation that rejects unsupported values before prediction.

A third symptom is a sudden rise in the rate of imputed values. The cause may be an upstream schema change, a failed source system, or a changed form field. Diagnose by tracking missingness rates per feature over time and comparing them with the training baseline. Correct the source if it is a data defect; if it is a real process change, retrain and document the new missingness mechanism.

Security, Performance, and Reliability

Cleaning can expose sensitive data because raw fields often contain names, free text, or operational notes. Profile and log aggregate missingness rates, not raw records, unless there is an approved debugging path. Performance costs are usually dominated by wide one-hot encodings, repeated dataframe copies, and expensive iterative imputers. For reliability, persist the fitted preprocessing pipeline with the model artifact so the exact same medians, categories, and indicators are used at prediction time.

Schema validation is part of reliability. A prediction service should require expected columns, types, and units; it should fail clearly when required fields disappear. Silent defaulting is attractive, but a model that fills every broken feed with medians can keep returning plausible-looking predictions while the business process is actually blind.

Hands-on Lab: Compare Missing-Value Strategies

Prerequisites: Python with pandas and scikit-learn installed, plus a small tabular classification dataset or the synthetic data below. The goal is to compare deletion, median imputation, and median imputation with an indicator using the same train-test split.

  1. Create a dataframe with numeric and categorical features, then intentionally set some values to missing.
  2. Split the data into training and test rows before fitting any cleaning step.
  3. Build one pipeline that drops rows with missing values from the training data and evaluates only complete test rows.
  4. Build a second pipeline with median numeric imputation and constant categorical imputation.
  5. Build a third pipeline that also adds numeric missingness indicators.
  6. Compare accuracy and inspect the learned feature names so you can explain what each model received.

Verification: confirm that no transformer is fitted before the split, that transformed matrices contain no missing values, and that the feature names include an indicator column in the third pipeline. Cleanup: remove temporary notebooks, generated CSV files, and serialized model artifacts unless you need them for a later experiment. If results are surprising, rerun with a different random seed and inspect missingness rates by target before drawing conclusions.

Assessment Exercises

  • A hospital dataset has lab results missing mostly for patients who were discharged quickly. Would you delete those rows, impute globally, impute by subgroup, or add indicators? Justify the choice in terms of the prediction time information.
  • You discover that test accuracy improves after imputing medians on the full dataset before splitting. Explain why this improvement is not trustworthy and rewrite the evaluation flow.
  • A categorical feature starts receiving a new value in production. Describe how handle_unknown="ignore" changes the failure behavior and what monitoring you would still add.
  • For a skewed income feature with rare very high values, compare mean imputation, median imputation, and an explicit missingness indicator. Which model types are most sensitive to the choice?
  • Design a diagnostic report that distinguishes true missing values, invalid sentinel values, duplicate records, and out-of-range measurements.

Summary

Cleaning data and handling missing values is the part of data preparation that decides what information the model truly receives. The core mechanics are to normalize missing tokens, understand why values are absent, fit replacement rules only on training data, and transform every future row with the same stored rules. Good choices are specific: delete only when the remaining data stays representative, impute with statistics that match the distribution, add indicators when absence is meaningful, and monitor missingness after release because a changing data feed can invalidate a previously sound model.