Data Cleaning & Handling Missing Values Questions

Understand common data quality issues: missing values (NaN, null), duplicates, outliers, inconsistent formats, and incorrect data types. Know strategies for handling each: removing rows/columns with missing data, imputation (mean, median, forward fill), deduplication, type conversion, and validation checks. Understand the trade-offs of each approach.

MediumTechnical

138 practiced

Discuss the use of missingness indicator features (binary flags that a column was missing) and interactions between missingness and feature values in supervised models. When do these indicators improve predictive performance and when can they introduce bias or overfitting?

Sample Answer

Missingness indicators are binary features (1 = value missing) that encode information about whether a value was observed. They can improve supervised models when missingness is informative — i.e., Not Missing At Random (NMAR) or Missing At Random conditional on observed variables — because the fact a value is missing itself correlates with the target. Example: patients missing a lab test because clinician judged them healthy; the missing flag then carries signal about outcome.

Interactions between missingness and feature values (e.g., value * missing_flag or separate imputation plus flag) let models learn different relationships conditional on "observed" vs "imputed" regimes. This is useful if the predictive slope differs when a measurement was actually taken.

However, missingness indicators can introduce bias or overfitting:- If missingness is purely random (MCAR) the indicator adds noise and can reduce generalization.- If missingness depends on post-treatment variables or the target in a way that won't hold at deployment, using indicators can induce target leakage or collider bias (especially in causal settings), producing biased estimates.- High-cardinality interactions or many indicators can overfit small samples; tree models may exploit spurious patterns.

Best practices:- Diagnose missingness mechanism (MCAR/MAR/NMAR) with exploratory analyses and domain knowledge.- Prefer parsimonious use: add indicators only for features where missingness correlates with target or process.- Combine with robust imputation (e.g., model-based or multiple imputation) and include interactions only when justified.- Validate with temporal or external holdout to detect leakage or unstable patterns.- For causal inference, be cautious: include missing indicators only after considering causal graph to avoid introducing colliders.

In short: indicators are powerful when missingness is informative; use them deliberately, test stability, and avoid blind inclusion that risks bias or overfitting.

MediumTechnical

81 practiced

Write pytest-style unit tests for a function clean_numeric_column(series) that replaces empty strings and sentinel values (-999, 9999) with NaN and converts the Series to float. Include tests for edge cases: all sentinels, mixed types, already-float series, and preservation of the original index.

Sample Answer

Approach: use pytest with pandas to create Series inputs and pandas.testing.assert_series_equal to compare expected Series (with float dtype and NaNs). Cover: mixed sentinels/empty strings, all sentinels -> all NaN floats, mixed types (ints/strs), already-float series, and check index preserved and function doesn't change index values.

python

import pandas as pd
import numpy as np
import pytest
from pandas.testing import assert_series_equal

# Assume clean_numeric_column is imported from the module under test
# from mymodule import clean_numeric_column

@pytest.mark.parametrize("input_vals, expected_vals, index", [
    # mixed values: empty string and sentinels replaced, others converted to float
    (["1", "", "-999", 5, "9999", "3.5"], [1.0, np.nan, np.nan, 5.0, np.nan, 3.5], [0,1,2,3,4,5]),
    # all sentinels -> all NaN, dtype float
    (["-999", 9999, "-999", "9999"], [np.nan, np.nan, np.nan, np.nan], ["a","b","c","d"]),
    # mixed types: ints, floats, sentinel strings
    ([10, "20", -999, 30.5], [10.0, 20.0, np.nan, 30.5], [0,1,2,3]),
])
def test_clean_numeric_column_parametrized(input_vals, expected_vals, index):
    s = pd.Series(input_vals, index=index)
    result = clean_numeric_column(s)
    expected = pd.Series(expected_vals, index=index, dtype=float)
    # ensure dtype is float and values match including NaN positions
    assert result.dtype == float
    assert_series_equal(result, expected)

def test_already_float_series_preserved_and_index():
    idx = ["r","s","t"]
    s = pd.Series([1.0, np.nan, 3.2], index=idx, dtype=float)
    result = clean_numeric_column(s)
    # values and index preserved, dtype still float
    assert_series_equal(result, s)

def test_function_does_not_modify_input_series():
    s = pd.Series(["", "-999", "4"], index=[0,1,2])
    original_copy = s.copy()
    _ = clean_numeric_column(s)
    # original input should remain unchanged (no in-place)
    assert_series_equal(s, original_copy)

Key points:- Use assert_series_equal to compare NaNs and dtype.- Check index preservation explicitly.- Include non-string numeric inputs and sentinel forms as strings/ints.- Test that function does not mutate the input Series in-place.

EasyTechnical

68 practiced

Define three quantitative metrics you would track to measure missingness and data quality over time (e.g., percent-missing per feature). For each metric provide sample alert thresholds, suggested aggregation window, and the remediation/workflow when thresholds are exceeded.

Sample Answer

1) Percent missing per feature (feature-level missing rate)- Definition: (count_nulls / total_rows) * 100 per feature.- Alert thresholds: Warning > 5%, Critical > 15% (tune per feature business importance).- Aggregation window: 24-hour rolling window (or per-batch if streaming).- Remediation/workflow: - Warning: auto-create ticket, notify data owner with feature-level trend and recent rows; defer action if expected (seasonal). - Critical: pause downstream models that depend on the feature (soft-fail), run root-cause script to check ingestion/parsing, fallback to default/imputation, and deploy hotfix. Post-mortem and adjust source SLAs.

2) Delta missing rate (sudden change)- Definition: (current_window_missing_rate − baseline_missing_rate) / baseline, expressed as percent change.- Alert thresholds: Warning > 50% relative increase, Critical > 200% relative increase.- Aggregation window: compare last 1-3 windows (e.g., today vs 7-day baseline).- Remediation/workflow: - Automated anomaly detection flags likely upstream schema or pipeline break. - Run schema diff, sample affected rows, notify ETL owner and revert recent deployments if correlated. - If transient spike, mark as anomaly and monitor; if persistent, trigger full pipeline rollback + data replay.

3) Row-level completeness score (per-row fraction of required fields present)- Definition: required_fields_present / total_required_fields, aggregated distribution (median, 5th percentile).- Alert thresholds: Median < 0.95 (warning), 5th percentile < 0.7 (critical).- Aggregation window: daily with hourly sampling for high-volume sources.- Remediation/workflow: - Warning: log and alert product/data engineers, apply conservative imputations for low-risk features. - Critical: block downstream training/serving, run targeted sampling and enrich/re-ingest missing rows, restore from backups if available; update data contract and add stricter validation tests.

Notes: accompany all alerts with context (sample rows, timestamps, upstream job IDs), automatic dashboards, and escalation matrix (owner, on-call, SLA). Continuously tune thresholds per feature criticality and seasonality.

MediumTechnical

69 practiced

A dataset contains dates in mixed formats such as '2020-01-02', '01/02/2020', 'Jan 2 2020', '20200102', and 'NA'. Describe a robust Python approach to parse these into a single datetime column for 10M rows, including handling ambiguous day/month orders, error tokens, performance, and fallback rules.

Sample Answer

Approach summary:- Use vectorized parsing with pandas + dateutil for flexibility, but avoid per-row Python loops. Pre-clean tokens, map known error tokens to NaT, detect majority day-first vs month-first to resolve ambiguities, parse common formats with fast methods (pd.to_datetime with format), fall back to slower parser only when needed, and parallelize if necessary.

python

import pandas as pd
import numpy as np
from dateutil import parser
from concurrent.futures import ProcessPoolExecutor

# example series s
s = pd.Series(...)  # 10M strings

# 1) Normalize obvious error tokens
error_tokens = {'', 'NA', 'N/A', 'null', 'None', 'nan'}
s_clean = s.str.strip().replace(error_tokens, pd.NaT)

# 2) Fast vectorized parsing for common unambiguous formats
# yyyy-mm-dd, yyyymmdd, Mon D YYYY, mm/dd/YYYY (ambiguous)
parsed = pd.to_datetime(s_clean, format='%Y-%m-%d', errors='coerce')
parsed = parsed.fillna(pd.to_datetime(s_clean, format='%Y%m%d', errors='coerce'))

# 3) Heuristic to detect majority dayfirst vs monthfirst using clearly unambiguous subset
sample = s_clean.dropna().sample(n=10000, random_state=1)
try_dt = pd.to_datetime(sample, dayfirst=False, errors='coerce')
count_mf = try_dt.notna().sum()
try_dt2 = pd.to_datetime(sample, dayfirst=True, errors='coerce')
count_df = try_dt2.notna().sum()
dayfirst = count_df > count_mf

# 4) Parse remaining with vectorized to_datetime using the inferred dayfirst
remaining_mask = parsed.isna() & s_clean.notna()
parsed.loc[remaining_mask] = pd.to_datetime(s_clean[remaining_mask], dayfirst=dayfirst, errors='coerce')

# 5) Final fallback: apply dateutil.parser for stubborn rows in parallel
def parse_chunk(arr, dayfirst):
    out = []
    for v in arr:
        try:
            out.append(parser.parse(v, dayfirst=dayfirst))
        except Exception:
            out.append(pd.NaT)
    return out

to_fallback = s_clean[parsed.isna() & s_clean.notna()]
if not to_fallback.empty:
    chunks = np.array_split(to_fallback.values, 8)  # tune workers
    with ProcessPoolExecutor(max_workers=8) as ex:
        results = ex.map(lambda c: parse_chunk(c, dayfirst), chunks)
    parsed_vals = np.concatenate(list(results))
    parsed.loc[parsed.isna() & s_clean.notna()] = pd.to_datetime(parsed_vals)

Key points and reasoning:- Use pd.to_datetime with explicit formats first (fast, C-optimized).- Infer dayfirst by sampling unambiguous parses to handle ambiguous dd/mm vs mm/dd consistently.- Replace known error tokens early to avoid wasted parsing.- Fall back to dateutil only for leftovers; run it in parallel to handle 10M scale.- Store result as datetime64[ns] for efficient downstream ops.

Complexity and performance:- Vectorized parses are O(n) and fast (C-level). Fallback Python parsing is heavier; minimize its use. Parallelizing fallback reduces wall time roughly by CPU cores.

Edge cases:- Ambiguous dates like "01/02/2020": resolved by inferred dayfirst; consider allowing a per-column override.- Timezones or partial dates: decide policy (assume naive UTC or set tz after parse).- Invalid tokens -> NaT.

Alternatives:- Use Apache Spark (pyspark.sql.to_timestamp) for distributed data, or faster C libraries (ciso8601) for ISO-only inputs.

MediumTechnical

67 practiced

Design an experiment to compare three imputation strategies (mean/mode single imputation, KNN imputation, and IterativeImputer) for a regression task. Describe data splits, metrics, how to capture imputation variability, and the statistical comparison you would run to choose the best method.

Sample Answer

Situation: I need a rigorous experiment comparing mean/mode single imputation, KNN, and IterativeImputer for a regression target.

Design:- Data preparation: start with a fully observed dataset or use held-out complete subset to enable ground-truth comparison. Simulate realistic missingness patterns (MCAR, MAR, maybe MNAR if plausible) at multiple missing rates (e.g., 5%, 20%, 40%).- Splits & validation: use repeated stratified (by target quantiles) k-fold CV on the original data. For each repetition: 1. Split into train/validation/test (e.g., outer 5-fold CV; within training do inner CV for model/hyperparameter tuning). 2. Inject missingness only into train and validation folds — keep test fully observed for unbiased evaluation.

Imputation & modeling pipeline:- For each fold and method, fit imputer using only training data, transform train/val/test accordingly (do not leak).- Train a fixed regression model (e.g., RandomForest/GBM) with identical hyperparameter tuning procedure per method (nested CV).- Repeat the whole procedure with multiple RNG seeds to capture stochasticity.

Metrics:- Downstream predictive performance on test: RMSE and MAE (primary), R² (secondary).- If using original complete data, compute imputation error (RMSE per feature) to assess reconstruction quality.- Record per-fold per-seed metrics to form paired samples.

Capturing variability:- Use repeated experiments (e.g., 10 repeats × 5 folds = 50 paired observations per method).- For probabilistic methods (IterativeImputer), consider multiple imputations (repeat imputation several times and average predictions or pool using Rubin’s rules) and include variation across imputations.

Statistical comparison:- Check normality of paired differences. Use Friedman test across methods over folds/repeats (non-parametric, accounts for multiple classifiers).- If Friedman significant, run post-hoc pairwise Wilcoxon signed-rank tests with Holm-Bonferroni correction (or Nemenyi test) to identify which methods differ.- Report effect sizes (e.g., Cohen’s d or rank-biserial) and confidence intervals via bootstrap.- Complement statistical tests with practical significance thresholds (e.g., >5% RMSE reduction).

Reporting:- Present mean ± std of metrics, imputation errors, p-values, corrected pairwise comparisons, and plots: boxplots of RMSE, critical difference diagram (Nemenyi), and per-feature imputation error heatmap.- Discuss runtime and stability trade-offs (KNN scaling, IterativeImputer convergence), and sensitivity to missingness mechanism/rate.

This setup ensures fair comparison, captures variability from data splits and stochastic imputation, and uses appropriate non-parametric tests to choose the best method for the regression task.

Unlock Full Question Bank

Get access to hundreds of Data Cleaning & Handling Missing Values interview questions and detailed answers.

Join thousands of developers preparing for their dream job.