Handling missing data

This is the exact file agents read, including the credit header and licence notice. Save it as handling-missing-data/SKILL.md.

---
name: handling-missing-data
description: >
  Find, diagnose and handle missing values in a dataset so that gaps are filled or removed on purpose, not by accident, across pandas, scikit-learn, SQL and R. Covers disguised sentinels (-999, "N/A", "", blanks), the MCAR, MAR and MNAR mechanisms, the drop versus impute versus flag decision, mean, median, mode, forward and backward fill, interpolation, KNN, iterative and multiple imputation, the missingness indicator, and fitting the imputer on training data only to avoid leakage. Use it when someone says "handle missing data", "fill the NaNs", "impute", "deal with nulls", "dropna", "fillna", "my column has blanks", "replace missing values", or "why did my row count drop". Do NOT use it for extreme but present values (see outlier-detection), for repeated rows (see duplicate-detection), for turning rows into columns, or for designing a schema's nullability.
license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/data-cleaning-preparation/handling-missing-data/.
---

# Handling missing data

## When to use this, and when not to

Use this skill when a dataset has gaps and you need to decide what to do with them before analysis or modelling. The triggers are a request to fill or remove missing values, columns full of `NaN`, `NA`, `NULL` or blanks, a call to `dropna`, `fillna` or an imputer, a row count that fell without explanation, or placeholder values like `-999`, `9999`, `"N/A"` or empty strings standing in for real numbers. The work is to find every missing value including the disguised ones, decide why it is missing, then drop, impute or flag it on purpose.

Do not use it for values that are present but extreme or impossible (that is `outlier-detection`), for rows that repeat (that is `duplicate-detection`), for reshaping rows into columns, or for deciding which schema columns should allow NULL. Those are separate jobs, and mixing them is how a cleaning pass quietly changes numbers it was never asked to touch.

The core idea: a missing value is a question, not a defect to paper over. Fill it only once you know why it is missing and what the fill will do to the distribution, and keep the fact that it was missing, because that fact is often information in its own right (Rubin, "Inference and missing data", 1976).

## Inputs the agent must have before starting

1. The tool and version. pandas 3.0 changed how in-place writes behave, SQL has no built-in imputation, and scikit-learn's `IterativeImputer` is still experimental. The right code depends on which one you are in (see the appendix).
2. What each column means and its type, so you know whether a gap is numeric, categorical, a date, or free text.
3. The real meaning of "missing" in this data. Ask whether a blank means unknown, not applicable, or a true zero, and whether placeholders like `-999`, `0`, `"N/A"`, `"Unknown"` or an empty string are being used as stand-ins. A legitimate zero must never be imputed.
4. Where the data is going. Imputation for a one-off report is low stakes; imputation that feeds a model needs a train and test split, so the method must be fitted without leaking test information.
5. Whether rows or columns may be removed at all. Dropping data is irreversible, so confirm it is allowed before doing it, and default to working on a copy rather than the raw source.

If any of these is unknown, ask before touching the data. Guessing that a `0` is missing, or that an empty string is a true blank, changes the answer silently.

## The workflow, step by step

### 1. Surface the disguised missing values first

Before counting anything, find the gaps that `isna` cannot see. Teams encode missing data as `-999`, `9999`, `0`, `"N/A"`, `"NA"`, `"null"`, `"none"`, `"unknown"`, a single space or an empty string. A standard null check reports zero missing while the column is full of them. Run a value frequency count on each column, look for suspicious spikes at round sentinels, then convert those to the tool's real missing marker (`pandas` `replace`, SQL `NULLIF`) so every later step sees them. Decision rule: if a value is a placeholder for "no data", it is missing, whatever it looks like, and must be converted before you measure.

### 2. Measure the missingness, per column and overall

Count missing values per column as a percentage, and count how many rows have any gap at all. This single table drives every later decision: a column that is 2% missing, a column that is 60% missing and a column that is always present each need different treatment, and you cannot tell which is which until you have measured.

### 3. Classify the mechanism (MCAR, MAR, MNAR)

The mechanism decides what handling is safe (Rubin, 1976; Little and Rubin, *Statistical Analysis with Missing Data*).

- **MCAR, missing completely at random.** The gap is unrelated to anything, observed or not, for example a sample lost in transit. Listwise deletion is unbiased here, though it still costs sample size.
- **MAR, missing at random.** The gap depends on other observed columns but not on the missing value itself, for example younger users skipping an income field more often when age is recorded. Imputation that uses the other columns is appropriate.
- **MNAR, missing not at random.** The gap depends on the value that is missing, for example high earners refusing to state income. No method fully corrects this from the data alone; it needs a missingness indicator, domain knowledge and a sensitivity analysis.

To probe the mechanism, compare the rows where a column is missing against the rows where it is present, across the other observed columns. If missingness is predicted by observed columns, treat it as MAR. If no observed column predicts it, it is consistent with MCAR. A non-significant Little's MCAR test is consistent with MCAR but cannot rule out MNAR (metricgate, "How to handle missing data"). MNAR cannot be confirmed from the data; it is a judgement from how the data was collected.

### 4. Decide drop, impute or flag, with thresholds

- **Drop rows** only when under about 5% of rows are incomplete and MCAR is plausible; above that, deletion loses power and can bias results (metricgate). Row deletion is irreversible, so confirm it first.
- **Drop a column** only when it is almost entirely missing (a common cut is above 50 to 60%) and carries no usable signal. Even then, a near-empty column can be worth keeping as a presence or absence flag.
- **Flag and impute** is the default for everything in between: add an indicator (step 5), then impute (step 6).
- **MNAR or informative missingness** is never silently imputed. Add the indicator, impute if you must, and run the analysis with and without the imputed values to see how much they move the result.

### 5. Add a missingness indicator before you impute

For any column where the fact of being missing could carry meaning, add a boolean column (`income_missing`) before imputing, so the signal survives the fill (scikit-learn `MissingIndicator`, or `add_indicator=True` on the imputer). This is cheap insurance: if missingness turns out to be informative, the model can still use it; if not, the indicator does no harm.

### 6. Choose the imputation method by type, distribution and purpose

- **Categorical:** fill with the most frequent value, or better, with an explicit `"Unknown"` category that preserves the signal rather than inventing a majority label.
- **Numeric, roughly symmetric:** the mean is acceptable for a quick fill.
- **Numeric, skewed:** use the median, which is not dragged by the tail.
- **Ordered or time series:** forward fill, backward fill or interpolation, but only within a meaningful order and never across unrelated groups. Interpolate by time, not position, when the index is a timestamp with uneven gaps (pandas `interpolate(method="time")`).
- **Values that depend on other columns:** `KNNImputer` uses similar rows, and `IterativeImputer` (MICE) models each column from the others.
- **Statistical inference:** single imputation understates uncertainty. Use multiple imputation (MICE with 20 or more imputations) and pool with Rubin's rules, not one filled dataset (wentorai, "missing-data-handling"; scikit-learn impute guide).

Decision rule: mean and median single imputation shrink variance and weaken correlations, so they are fine for a rough report and wrong for inference. The more the result depends on the spread of the data, the more you move towards KNN, iterative or multiple imputation.

### 7. Fit the imputer on training data only

When the data feeds a model, compute the fill statistics (mean, median, neighbours, model) on the training split alone, then apply those same statistics to both the training and test sets. Fitting the imputer on the full dataset before the split leaks test information into training and makes the model look better than it is (scikit-learn impute guide). For a time series, fit on past data only. This is the single most common correctness bug in cleaning code found in the wild, including in published cleaning skills that call `fit_transform` on the whole frame.

### 8. Write the fill correctly for your tool

In pandas 3.0, copy-on-write means `df[col].fillna(x, inplace=True)` operates on a temporary and silently leaves the original unchanged. Assign the result back instead: `df[col] = df[col].fillna(x)`, or `df.fillna({col: x})`. The same applies to `ffill`, `bfill` and `replace`. Any cleaning code that relies on `inplace=True` on a column should be rewritten (pandas, "Working with missing data"; pandas Copy-on-Write notes).

### 9. Verify

Re-run the missing count and confirm it is zero where you intended, and still non-zero where you chose to leave gaps. Compare the mean and standard deviation of each imputed column before and after: a large variance drop is the signature of over-eager mean imputation. Confirm the indicators are present, confirm the imputer was fitted on training data only, and log the before and after counts so the decision is auditable.

## Worked example, end to end

A customer table has `income` with gaps, and a quick null check reports 4% missing. A value count shows a spike of `-999` and a scatter of `"N/A"` strings, so the true missingness is 18% once those are converted to real nulls. Probing shows that missing `income` is more common for younger customers and one acquisition channel, both recorded, so the mechanism is MAR, not MCAR.

The wrong approach fills the whole column with the mean, in place, on the full dataset before splitting:

```python
df["income"].fillna(df["income"].mean(), inplace=True)   # three bugs at once
```

This leaks across the train and test split, it shrinks the variance of a skewed column, and under pandas 3.0 the `inplace=True` may leave the column unchanged, so the gaps quietly remain. The corrected version converts sentinels, adds an indicator, uses the median because income is skewed, and fits on the training split only:

```python
import numpy as np
train["income"] = train["income"].replace([-999, "N/A"], np.nan)
test["income"]  = test["income"].replace([-999, "N/A"], np.nan)
train["income_missing"] = train["income"].isna().astype(int)
test["income_missing"]  = test["income"].isna().astype(int)
fill = train["income"].median()            # fitted on train only
train["income"] = train["income"].fillna(fill)
test["income"]  = test["income"].fillna(fill)
```

Verifying, the missing count is zero, the median fill moved the mean only slightly, the `income_missing` flag is kept for the model to use, and the test set was filled with the training median, so no test information leaked. The report states that 18% of income was imputed with the training median under a MAR assumption, so no reader mistakes the filled values for observed ones.

## Variations you will meet in practice

- **Legitimate zeros.** A `0` in a purchases column may mean "bought nothing", not "unknown". Confirm before converting zeros to nulls; the opposite error, imputing over a real zero, inflates every total.
- **Whole rows empty.** Rows that are blank except for an identifier are usually structural debris; drop them after confirming they carry no data.
- **Nullable dtypes.** With pandas `Int64`, `boolean` or `string` columns, the marker is `pd.NA`, and `bool(pd.NA)` raises `TypeError`. Use `isna`, or `&`, `|`, `~`, not `and`, `or`, `not` (pandas missing-data guide).
- **Silent reductions.** `pd.Series([np.nan]).sum()` returns `0.0`, and reductions skip `NA` by default (`skipna=True`). Pass `skipna=False` when a missing value should make the whole result missing (pandas missing-data guide).
- **Categorical "Unknown" beats mode.** Filling a categorical gap with the most frequent label can invent a pattern; an explicit `"Unknown"` category keeps the truth and lets the model learn from it.

## Templates you can use verbatim

```python
# 1. Surface disguised missing values and measure (pandas)
import numpy as np, pandas as pd
SENTINELS = [-999, 9999, "N/A", "NA", "null", "none", "unknown", "", " "]
df = df.replace(SENTINELS, np.nan)
missing = (df.isna().mean() * 100).round(1).sort_values(ascending=False)
print(missing)                 # percentage missing per column

# 2. Flag then impute, fitted on train only (no leakage, no inplace)
for col in ["income", "age"]:
    med = train[col].median()
    for part in (train, test):
        part[f"{col}_missing"] = part[col].isna().astype(int)
        part[col] = part[col].fillna(med)

# 3. Multivariate / inference-grade imputation
from sklearn.experimental import enable_iterative_imputer  # required import
from sklearn.impute import IterativeImputer, KNNImputer
imp = KNNImputer(n_neighbors=5).fit(train[num_cols])   # fit on train
train[num_cols] = imp.transform(train[num_cols])
test[num_cols]  = imp.transform(test[num_cols])
```

```sql
-- SQL: treat a sentinel as NULL, then fill with a chosen value
SELECT id,
       COALESCE(NULLIF(income, -999), (SELECT median_income FROM params)) AS income
FROM customers;   -- SQL has no built-in mean/median impute; compute the fill yourself
```

## Failure modes and anti-patterns

1. **Missing values hidden as sentinels.** A null check reports clean while the column is full of `-999` or `"N/A"`. Spot it with a value count and a suspicious spike at a round number or placeholder string. Fix: convert sentinels to real nulls before measuring.
2. **Mean imputation presented as fact.** The standard deviation falls and correlations weaken after filling, then the report quotes confidence intervals as if the values were observed. Spot it by comparing variance before and after. Fix: median or multiple imputation, add an indicator, and label imputed values.
3. **Data leakage.** The imputer is fitted on the whole dataset before the split, so test scores look too good. Spot it when offline metrics beat production by a wide margin. Fix: fit on the training split only, transform both.
4. **`inplace=True` that does nothing.** Under pandas 3.0 copy-on-write, `df[col].fillna(x, inplace=True)` leaves the column unchanged. Spot it when nulls remain after a fill. Fix: assign the result back.
5. **Silent dropping.** `dropna()` removes rows or columns and the sample shrinks or an informative column disappears. Spot it from a fallen row count or a MNAR pattern. Fix: flag and confirm before dropping, and run the analysis with and without the dropped data.

## Quality bar

- Every disguised sentinel is converted to a real missing marker before anything is measured.
- Missingness is quantified per column, and the assumed mechanism (MCAR, MAR or MNAR) is stated with a reason.
- A missingness indicator is added wherever the gap could be informative.
- The imputation method matches the column type, its distribution and whether the output is a report or an inference.
- Any imputer that feeds a model is fitted on training data only, and pandas writes are assigned back rather than relying on `inplace`.
- No row or column is dropped without explicit confirmation, and imputed values are labelled as estimates, never presented as observed.
- A before-and-after count and a variance check are recorded.

## Data handling and safety

Missing values often sit in records with personal or sensitive fields (income, health, contact details). Do not log raw values, paste them into prompts or send them to a third-party service while imputing; work on the data in place with the tool you already have. Dropping rows or columns, and overwriting the original file, are irreversible, so require explicit human confirmation and default to writing a new cleaned copy rather than editing the raw source. Imputed values are estimates: label them as such, keep the indicator column, and never let a filled number be read as a measured one. When the data affects health, money, legal standing or employment, an imputed value can change a decision about a real person, so add a clear "this is estimated, not observed" note and a human review step, and do not present imputed figures as fact.

## Appendix: tool-specific notes (verify before relying on these)

- **pandas:** `isna`/`notna` detect `NaN`, `NaT` and `pd.NA`; equality does not (`np.nan == np.nan` is `False`, `pd.NA == pd.NA` is `<NA>`). `dropna`, `fillna`, `ffill`, `bfill`, `interpolate`, `replace`. `fillna(None)` does not fill a numeric column. Reductions skip `NA` by default; `sum` of all-`NA` is `0`. Under copy-on-write, assign results back rather than using `inplace=True`.
- **scikit-learn:** `SimpleImputer` (`strategy` of `mean`, `median`, `most_frequent`, `constant`); `KNNImputer` (`n_neighbors`, `nan_euclidean_distances`); `IterativeImputer`, which is experimental and needs `from sklearn.experimental import enable_iterative_imputer`; `MissingIndicator` or `add_indicator=True`. Fit on training data only. `SimpleImputer` drops all-missing columns unless `keep_empty_features=True`.
- **SQL:** missing is `NULL`; test with `IS NULL`, not `=`. `NULLIF(x, -999)` turns a sentinel into `NULL`; `COALESCE` fills with a fallback. There is no native mean or median imputation, so compute the fill value in a subquery or application layer, and never fill with a magic number that later reads as real.
- **R:** missing is `NA`; test with `is.na`. The `mice` package does multiple imputation and pooling; `naniar` and `VIM` visualise and summarise missingness.

## Sources

- pandas documentation, "Working with missing data": the sentinels per dtype (`NaN`, `NaT`, `pd.NA`), that equality does not detect missing values, `dropna`/`fillna`/`ffill`/`bfill`/`interpolate`/`replace` behaviour, that `fillna(None)` does not fill numeric columns, that reductions skip `NA` by default and `sum` of all-`NA` is `0`, and the `pd.NA` boolean-context error. Verified 31 October 2026.
- scikit-learn User Guide, "Imputation of missing values" (section 8.4, v1.9.1): `SimpleImputer` strategies, `KNNImputer` with `nan_euclidean_distances`, `IterativeImputer` as experimental with the required enabling import, `MissingIndicator` and `add_indicator`, and the list of estimators that handle `NaN` natively. Verified 31 October 2026.
- Rubin, "Inference and missing data" (Biometrika, 1976), and Little and Rubin, *Statistical Analysis with Missing Data*: the MCAR, MAR and MNAR mechanisms and the principle that the mechanism determines valid handling. Verified 31 October 2026.
- metricgate, "How to handle missing data: MCAR, MAR, and MNAR": definitions and examples of each mechanism, that Little's MCAR test non-significance is consistent with MCAR but cannot separate MAR from MNAR, that complete-case analysis is often acceptable when under 5% of rows are incomplete and MCAR is plausible, and that multiple imputation is the standard for MAR. Verified 31 October 2026.
- wentorai research-plugins, "missing-data-handling" and "data-cleaning-pipeline" SKILL.md: a worked MICE and Rubin's-rules pooling implementation, the recommendation of 20 or more imputations, and a reproducible cleaning order. Noted as an existing implementation that this skill improves on by adding the leakage rule, the copy-on-write fix and the sentinel-first step. Verified 31 October 2026.