Handling Missing Values

Why Missing Data Breaks Analysis

Missing values are not an edge case. They are the default state of real-world data. Survey respondents skip questions. Sensors fail. ETL jobs drop rows during joins. If you do not handle missing values deliberately, they will corrupt your aggregations, bias your models, and silently produce wrong answers.

Consider a simple example. You have a sales table with 1,000 rows and a revenue column. Fifty rows have NULL revenue. If you run AVG(revenue), the database ignores the NULLs and returns the average of 950 rows. That number looks correct but is computed on a biased sample. If the 50 missing rows are all from a region where the sensor was down, your average systematically overstates revenue for that region.

Missing data also breaks joins. In SQL, NULL = NULL evaluates to UNKNOWN, not TRUE. An inner join on a column with NULLs silently drops those rows. A left join preserves them but fills the unmatched side with NULLs, which then propagate into downstream calculations.

The Three Mechanisms of Missingness

Statisticians classify missing data by the mechanism that causes it. Understanding the mechanism determines the correct response.

MCAR (Missing Completely At Random): The probability of a value being missing is unrelated to any other variable, observed or unobserved. A server randomly drops 1% of log entries. Deleting MCAR rows introduces no bias, only a small loss of statistical power.

MAR (Missing At Random): The probability of missingness depends on observed variables but not on the missing value itself. High-income respondents are less likely to report salary. If you know income correlates with age and region, you can impute salary using those observed predictors without bias.

MNAR (Missing Not At Random): The probability of missingness depends on the missing value itself. People with very high or very low incomes are less likely to report. Imputation based on observed data will be biased because the missing values are systematically different from the observed ones. MNAR requires domain knowledge, sensitivity analysis, or specialized models.

Auditing for Missing Values

Before choosing a treatment, quantify the problem. A missing-value audit answers four questions: How many values are missing? Where are they concentrated? Is missingness correlated with other variables? What is the mechanism?

-- Count NULLs per column
SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN revenue IS NULL THEN 1 ELSE 0 END) AS revenue_nulls,
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS customer_nulls,
    ROUND(100.0 * SUM(CASE WHEN revenue IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS revenue_null_pct
FROM sales;

-- Check if missingness correlates with another variable
SELECT
    region,
    COUNT(*) AS rows_total,
    SUM(CASE WHEN revenue IS NULL THEN 1 ELSE 0 END) AS rows_missing,
    ROUND(100.0 * SUM(CASE WHEN revenue IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS missing_pct
FROM sales
GROUP BY region
ORDER BY missing_pct DESC;

The second query reveals whether missingness is concentrated. If one region has 40% missing revenue while others have 2%, the mechanism is likely not MCAR. That concentration is evidence of a systematic problem that deletion will not fix.

Sentinel Values and Implicit Missingness

Missing data is not always NULL. Datasets encode missingness in ways that are easy to miss:

• Empty strings '' in CSV exports
• Placeholder values like -1, 999, 0, or 9999
• Text sentinels: 'N/A', 'unknown', '--', 'missing'
• Dates like '1900-01-01' or '9999-12-31'

These sentinels are dangerous because they are typed as valid data. A revenue column with -1 for missing will produce a wrong average that looks plausible. Always check the data dictionary and inspect the distribution of values before assuming a column is clean.

-- Find sentinel values in a numeric column
SELECT revenue, COUNT(*) AS freq
FROM sales
WHERE revenue < 0 OR revenue = 999 OR revenue = 9999
GROUP BY revenue;

-- Find text sentinels in a categorical column
SELECT status, COUNT(*) AS freq
FROM customers
WHERE status IN ('N/A', 'unknown', '--', '', 'missing')
GROUP BY status;

The audit is not complete until you have identified both explicit NULLs and implicit sentinels. Treat them as the same problem: information that is absent but encoded as if it were present.

Deletion: When Dropping Rows or Columns Is Correct

Deletion is the simplest missing-value strategy. It is also the most dangerous when applied carelessly. The decision to delete depends on the mechanism of missingness, the proportion of missing data, and the role of the affected column.

Listwise Deletion

Listwise deletion removes any row that has a missing value in any column of interest. In SQL, this is the default behavior of most aggregations and joins. In pandas, df.dropna() does the same.

-- Listwise deletion via inner join (drops rows with NULLs in join keys)
SELECT s.*, c.customer_name
FROM sales s
INNER JOIN customers c ON s.customer_id = c.customer_id;

-- Explicit listwise deletion
SELECT *
FROM sales
WHERE revenue IS NOT NULL
  AND customer_id IS NOT NULL
  AND region IS NOT NULL;

Listwise deletion is unbiased when data is MCAR. It reduces sample size, which reduces statistical power, but it does not systematically distort estimates. When data is MAR or MNAR, listwise deletion biases results because the deleted rows differ systematically from the retained rows.

When to use listwise deletion:

• Missingness is MCAR or nearly so
• The proportion of missing rows is small (under 5%)
• The analysis does not require the specific rows being dropped
• You need a quick, defensible baseline

When to avoid listwise deletion:

• Missingness exceeds 10-15% of rows
• Missingness is concentrated in a subgroup (MAR/MNAR)
• The dropped rows are the subject of the study
• Sample size is already small

Column Deletion

Column deletion removes an entire column that is mostly missing. If a column is 80% NULL, it carries little information and imputation would be fabrication.

-- Identify columns with high missingness
SELECT
    ROUND(100.0 * SUM(CASE WHEN notes IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS notes_null_pct,
    ROUND(100.0 * SUM(CASE WHEN revenue IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS revenue_null_pct
FROM sales;

If notes is 85% NULL and is not essential to the analysis, drop it. If revenue is 85% NULL but is the target variable, you have a data collection problem, not a cleaning problem. No cleaning strategy can recover information that was never recorded.

The Bias Problem with Deletion

The central risk of deletion is selection bias. Suppose you are analyzing customer churn and the last_purchase_date column is 30% NULL. Customers who never purchased have no purchase date. Deleting those rows removes all non-purchasers from your analysis, making churn appear lower than it actually is.

This is MNAR: the missingness depends on the missing value (no purchase means no date). Deletion is the wrong response. The correct response is to flag these customers as non-purchasers and include them in the analysis.

Documenting Deletion Decisions

Every deletion decision must be documented with:

1. The criterion used (e.g., "rows with NULL revenue")
2. The number of rows removed
3. The rationale (e.g., "MCAR confirmed by Little's test" or "column 80% missing, not analytically relevant")
4. The impact on sample size and any subgroup representation

Without this documentation, a reviewer cannot distinguish deliberate cleaning from accidental data loss. Deletion is irreversible; a documented decision at least makes the reasoning auditable.

Imputation: Filling Gaps with Defensible Estimates

Imputation replaces missing values with plausible estimates. It preserves sample size and, when done correctly, reduces bias compared to deletion. The cost is that imputed values are estimates, not observations. They introduce uncertainty that must be acknowledged.

Mean, Median, and Mode Imputation

The simplest imputation methods use a single statistic computed from observed values.

-- Mean imputation for revenue
UPDATE sales
SET revenue = (SELECT AVG(revenue) FROM sales WHERE revenue IS NOT NULL)
WHERE revenue IS NULL;

-- Median imputation (more robust to outliers)
-- Using a window function approach
WITH median_calc AS (
    SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) AS median_revenue
    FROM sales
    WHERE revenue IS NOT NULL
)
UPDATE sales
SET revenue = (SELECT median_revenue FROM median_calc)
WHERE revenue IS NULL;

-- Mode imputation for categorical columns
UPDATE customers
SET region = (
    SELECT region FROM customers
    WHERE region IS NOT NULL
    GROUP BY region
    ORDER BY COUNT(*) DESC
    LIMIT 1
)
WHERE region IS NULL;

Mean imputation is appropriate for symmetric, roughly normal distributions. It preserves the overall mean but compresses the variance. If you impute 20 missing values with the mean, the standard deviation shrinks because the imputed values have zero deviation from the mean.

Median imputation is robust to outliers. For skewed distributions like income or revenue, the median is a better central estimate. It does not preserve the mean, but the mean of a skewed distribution is itself a poor summary.

Mode imputation works for categorical variables. It assigns the most common category, which is a reasonable default when no other information is available.

The Variance Problem

Single-value imputation has a structural flaw: it treats imputed values as if they were real observations. This understates uncertainty. If you impute 50 missing revenue values with the mean, your analysis behaves as if you have 50 additional real data points at the mean. Confidence intervals shrink. P-values become artificially significant.

For descriptive analysis and reporting, this is often acceptable. For statistical inference and modeling, it is not. In those cases, use multiple imputation or add noise to imputed values.

Conditional Imputation

Instead of using a global statistic, impute based on related variables. If revenue is missing, estimate it from the customer's historical average, the region's median, or a regression on observed predictors.

-- Impute revenue using the customer's own historical average
UPDATE sales s
SET revenue = (
    SELECT AVG(s2.revenue)
    FROM sales s2
    WHERE s2.customer_id = s.customer_id
      AND s2.revenue IS NOT NULL
)
WHERE s.revenue IS NULL
  AND EXISTS (
    SELECT 1 FROM sales s3
    WHERE s3.customer_id = s.customer_id
      AND s3.revenue IS NOT NULL
);

Conditional imputation is more defensible than global imputation because it uses information specific to the missing row. It requires that the conditioning variable is observed and genuinely predictive of the missing value.

When Imputation Goes Wrong

Imputation fabricates data. If you impute 40% of a column, you are 40% fabrication. That is not analysis; it is fiction. Rules of thumb:

• Under 5% missing: imputation is low-risk
• 5-20% missing: imputation is reasonable with documentation
• 20-40% missing: imputation requires strong justification and sensitivity analysis
• Over 40% missing: consider whether the column should exist at all

Always compare results with and without imputation. If conclusions change, the imputation is driving the finding, and you need to report that sensitivity.

Flagging and Indicator Variables

Flagging preserves missingness as information. Instead of deleting or imputing, you add a binary indicator column that records whether the value was missing. This is the safest strategy when missingness itself is predictive.

Why Missingness Can Be Signal

In many domains, the fact that data is missing is more informative than the data itself. A missing income field on a loan application may indicate the applicant chose not to disclose, which correlates with default risk. A missing last_login_date for a user account may indicate the account is abandoned.

If you impute or delete these values, you destroy the signal. If you flag it, the model or analysis can use missingness as a feature.

-- Add a missingness indicator
ALTER TABLE sales ADD COLUMN revenue_missing INT DEFAULT 0;

UPDATE sales
SET revenue_missing = 1
WHERE revenue IS NULL;

-- Now you can analyze missingness as a factor
SELECT
    revenue_missing,
    COUNT(*) AS row_count,
    AVG(revenue) AS avg_revenue,
    AVG(CASE WHEN returned = 1 THEN 1.0 ELSE 0.0 END) AS return_rate
FROM sales
GROUP BY revenue_missing;

If the return rate differs significantly between rows with observed revenue and rows with missing revenue, the missingness indicator is a valuable feature.

Combining Flagging with Imputation

The most robust approach is to do both: impute the missing value for use in calculations, and add a flag so the analysis can account for the imputation.

-- Impute with median and flag
WITH median_val AS (
    SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) AS med
    FROM sales WHERE revenue IS NOT NULL
)
UPDATE sales
SET
    revenue = COALESCE(revenue, (SELECT med FROM median_val)),
    revenue_imputed = CASE WHEN revenue IS NULL THEN 1 ELSE 0 END
WHERE revenue IS NULL;

This pattern gives you a complete column for aggregation while preserving the ability to filter or weight by imputation status. In machine learning, the flag becomes a feature. In reporting, you can disclose what proportion of values were imputed.

Domain-Specific Flags

Sometimes the correct flag is not binary but categorical. A missing_reason column records why the value is absent:

• 'not_collected' — the field was not part of the original form
• 'sensor_failure' — a technical fault
• 'withheld' — the subject chose not to provide
• 'not_applicable' — the question does not apply

These categories are more informative than a simple 0/1 flag because they distinguish mechanisms. 'sensor_failure' is closer to MCAR; 'withheld' is likely MNAR. The appropriate treatment differs by reason.

When to Choose Flagging

Flagging is the default choice when:

• Missingness may be predictive (MNAR suspected)
• You are building a predictive model
• The proportion of missing data is high
• You want to preserve the option to analyze with and without imputed values

Flagging costs almost nothing: one additional column. It is the lowest-risk strategy because it discards no data and fabricates nothing. The only downside is added complexity in downstream analysis, which is a manageable tradeoff.

A Practical Workflow for Choosing a Strategy

When you encounter a column with missing data, work through this decision sequence:

1. Quantify: What percentage of values is missing? Under 5% is low-risk; over 40% is a structural problem.
2. Investigate: Is missingness concentrated in a subgroup? If so, it is not MCAR.
3. Classify the mechanism: MCAR, MAR, or MNAR. Use domain knowledge and the concentration analysis.
4. Choose the strategy:
   • MCAR + low percentage → deletion or simple imputation
   • MAR + moderate percentage → conditional imputation or flagging
   • MNAR or high percentage → flagging with domain-specific investigation
5. Validate: Compare summary statistics before and after treatment. If the mean, variance, or distribution shape changes dramatically, your treatment is altering the data in ways you need to understand and document.
6. Document: Record the mechanism, the strategy, the rationale, and the impact.

This workflow is not a rigid algorithm. It is a structured way to ensure you consider the relevant factors before making an irreversible decision. The most common failure mode in data cleaning is not choosing the wrong strategy; it is choosing a strategy without conscious deliberation.