Skip to content

Missing Values

Missing data is the most common data quality problem you will encounter. A missing value does not mean "zero" — it means "we don't know." Treating it as zero, or ignoring it, gives you wrong results. This file shows you how to detect, understand, and handle missing values correctly — with the right strategy for each situation.

The business cost of missing data

A sales dashboard with 15% of total_amount rows missing will underreport revenue. A customer segmentation model trained without handling missing region values will silently drop 15% of customers from the analysis. Neither failure is obvious until someone asks "why does the dashboard show £340k but the ERP shows £412k?"

Learning Objectives

  • Detect missing values with .isna(), .isnull(), .info()
  • Understand WHY values are missing (MCAR, MAR, MNAR)
  • Choose the right strategy: drop, fill, or impute
  • Implement missing value handling in Pandas

Detecting Missing Values

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

df = pd.read_csv("sales_data.csv", parse_dates=["order_date"])

# Count missing per column
missing = df.isnull().sum()
missing_pct = df.isnull().mean() * 100

missing_report = pd.DataFrame({
    "missing_count": missing,
    "missing_pct": missing_pct.round(1),
}).sort_values("missing_pct", ascending=False)

print(missing_report[missing_report["missing_count"] > 0])

Sample output:

column missing_count missing_pct
sales_rep 180 18.0
total_amount 45 4.5
region 22 2.2
order_date 8 0.8 
# Which rows have any missing value?
rows_with_missing = df[df.isnull().any(axis=1)]
print(f"{len(rows_with_missing)} rows have at least one missing value")

# Are the missing values concentrated in the same rows?
missing_per_row = df.isnull().sum(axis=1)
print(missing_per_row.value_counts().sort_index())
# Output: 0 → 720 rows, 1 → 230 rows, 2 → 42 rows, 3+ → 8 rows

For mid-level analysts

Check whether missing values cluster in the same rows. If 90% of rows with a missing sales_rep also have a missing region, they may share a root cause — for example, online orders that don't go through a sales rep and aren't geo-tagged. Understanding this pattern shapes your cleaning strategy.

Understanding Why Values Are Missing

The strategy for handling missing values depends on WHY they are missing.

Type Meaning Example Strategy
MCAR — Missing Completely At Random No pattern — random failures Random rows missing order_date due to a logging bug Safe to drop or impute with mean/median
MAR — Missing At Random Missing depends on other observed columns sales_rep missing only for online channel orders Impute based on the related column
MNAR — Missing Not At Random Missing because of the value itself High-revenue orders missing discount_pct because they never receive discounts Flag as a separate category — do not impute

MNAR data cannot be safely imputed

If customers with high debt do not report their income, filling with the mean creates a fake "average" that misrepresents high-risk customers. If orders over £10,000 never have a discount and discount_pct is blank for those rows, filling with 0 is accidentally correct — but for the wrong reason. Always check whether the pattern of missingness correlates with any other column before deciding.

# Test for MCAR vs MAR: does missingness in one column correlate with another?
df["sales_rep_missing"] = df["sales_rep"].isna().astype(int)

# Are online orders more likely to have a missing sales_rep?
if "channel" in df.columns:
    print(df.groupby("channel")["sales_rep_missing"].mean())

# Correlation between missing flags
missing_flags = df.isnull().astype(int)
print(missing_flags.corr().round(2))

Strategy 1 — Drop Rows or Columns

# Drop rows with ANY missing value (use cautiously — loses data)
df_clean = df.dropna()
print(f"Rows remaining: {len(df_clean)} of {len(df)}")

# Drop rows missing a SPECIFIC critical column
df_clean = df.dropna(subset=["order_id", "customer_id"])

# Drop columns with >50% missing (usually not salvageable)
threshold = 0.5
cols_to_drop = [col for col in df.columns if df[col].isnull().mean() > threshold]
df_clean = df.drop(columns=cols_to_drop)
print(f"Dropped columns: {cols_to_drop}")

Rule: only drop rows if missing data is MCAR and the remaining sample is still representative

Dropping rows changes your population. If 30% of rows are missing sales_rep, dropping them might remove all direct sales orders and leave only online orders — completely biasing any analysis of sales performance.

Strategy 2 — Fill with a Fixed Value

# Fill numeric with 0 (only when 0 makes business sense — e.g., no discount = 0%)
df["discount_pct"] = df["discount_pct"].fillna(0)

# Fill categorical with "Unknown" or a meaningful label
df["region"] = df["region"].fillna("Unknown")
df["sales_rep"] = df["sales_rep"].fillna("Online / No Rep")

# Fill with mode (most common value) — reasonable for low-cardinality categoricals
mode_status = df["status"].mode()[0]
df["status"] = df["status"].fillna(mode_status)

Strategy 3 — Fill with Statistical Estimates

# Fill numeric with mean (only when distribution is symmetric)
df["unit_price"] = df["unit_price"].fillna(df["unit_price"].mean())

# Fill with median (more robust to outliers — preferred for revenue/price columns)
df["total_amount"] = df["total_amount"].fillna(df["total_amount"].median())

# Fill within groups — more accurate than global median
# Fill missing unit_price with the median price for that product category
df["unit_price"] = df.groupby("category")["unit_price"].transform(
    lambda x: x.fillna(x.median())
)

For mid-level analysts

Group-level imputation is almost always better than global imputation. A missing unit_price for an Electronics order should be filled with the Electronics median (£149), not the global median across all categories (which might be £49 because Books and Stationery dominate the count).

# Verify the imputation made sense
print(df.groupby("category")["unit_price"].agg(["mean", "median", "count"]))

Strategy 4 — Forward/Backward Fill (Time Series)

For time-ordered data where a missing value can be estimated from adjacent rows:

# Sort by date first — always required before ffill/bfill
df = df.sort_values("order_date")

# Forward fill — carry last known value forward
df["daily_target"] = df["daily_target"].ffill()

# Backward fill — use the next known value
df["daily_target"] = df["daily_target"].bfill()

# Limit how many consecutive NaNs to fill (avoid filling long gaps with stale data)
df["daily_visitors"] = df["daily_visitors"].ffill(limit=3)  # fill max 3 consecutive gaps

print(f"Remaining nulls after ffill(limit=3): {df['daily_visitors'].isna().sum()}")

ffill on non-time-series data is dangerous

Forward fill works for sequential time series data (e.g., daily website traffic) where a day's value is a reasonable estimate of the next day's value. Do NOT use ffill on an orders table where rows are independent transactions — filling sales_rep from the previous row is meaningless.

Strategy 5 — Flag Missing as a Separate Category

When MNAR, do not impute — tell your model or dashboard that the value is unknown:

# Create a binary "is_missing" flag column before imputing
df["sales_rep_missing"] = df["sales_rep"].isna().astype(int)

# Then fill with a placeholder (so you don't lose the row)
df["sales_rep"] = df["sales_rep"].fillna("Unknown")

# For categorical: add an explicit "Unknown" category
df["region"] = df["region"].fillna("Not Assigned")

This way you can: 1. Keep all rows in your analysis 2. Filter or segment on sales_rep_missing == 1 to understand the "no rep" segment separately 3. Give your ML model the missingness signal as a feature

Visualising Missing Data

import seaborn as sns

# Heatmap of missing values (good for smaller datasets — shows which rows are missing what)
plt.figure(figsize=(12, 6))
sns.heatmap(df.isnull(), yticklabels=False, cbar=False, cmap="viridis")
plt.title("Missing Value Map (yellow = missing)")
plt.tight_layout()
plt.show()

# Bar chart of missing percentages — easier to read for reporting
missing_pct = df.isnull().mean() * 100
missing_pct = missing_pct[missing_pct > 0].sort_values(ascending=False)

fig, ax = plt.subplots(figsize=(10, 4))
bars = ax.bar(missing_pct.index, missing_pct.values, color="#EF4444", alpha=0.8)
ax.set_title("Missing Data by Column (%)")
ax.set_ylabel("% Missing")
ax.set_xlabel("")
ax.axhline(5, color="orange", linestyle="--", alpha=0.7, label="5% threshold")
ax.axhline(20, color="red", linestyle="--", alpha=0.7, label="20% threshold")
ax.legend()
plt.xticks(rotation=45, ha="right")
plt.tight_layout()
plt.show()

For senior analysts

A missing value heatmap sorted by row index can reveal patterns invisible in aggregate counts. If rows 400–500 all show missing order_date, that might indicate a specific day when the data pipeline failed — actionable intelligence for the data engineering team, not something you should silently impute away.

Choosing the Right Strategy — Decision Guide

def recommend_strategy(col_name, missing_pct, dtype, is_critical):
    """
    Simple rules-of-thumb for choosing a missing value strategy.
    These are starting points, not automatic decisions.
    """
    if is_critical and missing_pct > 0:
        return "DROP: critical column, cannot impute"
    if missing_pct > 50:
        return "DROP COLUMN: too much missing to be useful"
    if missing_pct > 20:
        return "FLAG + FILL 'Unknown': likely MNAR or structurally missing"
    if dtype in ["int64", "float64"] and missing_pct <= 5:
        return "IMPUTE: group median if categorical grouping available, else global median"
    if dtype == "object" and missing_pct <= 10:
        return "FILL 'Unknown' or mode"
    return "INVESTIGATE: check why values are missing before deciding"

for col in df.columns:
    missing_pct = df[col].isnull().mean() * 100
    if missing_pct > 0:
        dtype = str(df[col].dtype)
        is_critical = col in ["order_id", "customer_id", "order_date"]
        print(f"{col:20} {missing_pct:5.1f}% → {recommend_strategy(col, missing_pct, dtype, is_critical)}")

Practice Exercises

Warm-up

  1. Load the sales dataset and print a missing value report: column name, count missing, percentage missing. Sort by percentage descending.
  2. Drop all rows where order_id is missing.
  3. Fill discount_pct with 0 where it is NULL.

Main

  1. For each numeric column, fill missing values with the column median. For each string column, fill with "Unknown". Verify no nulls remain.
  2. Add a binary flag column sales_rep_missing (1 if sales_rep is NaN, 0 otherwise) before imputing sales_rep.
  3. Fill missing unit_price values with the median price for the same product category using groupby().transform(). Print before/after null counts.

Stretch

  1. Build a function audit_missing(df) that returns a DataFrame with: column name, count, percentage, and a suggested strategy ("DROP", "GROUP_IMPUTE", "FILL_ZERO", "FLAG") based on the rules discussed in this file.
  2. Simulate a time series with random gaps. Use ffill(limit=3) to fill short gaps. Flag any remaining gaps with a "gap_filled" indicator column. Plot the original series, the filled series, and highlight the flagged gaps.

Interview Questions

[Beginner] What is the difference between df.dropna() and df.fillna(0)?

Show answer

df.dropna() removes rows (or columns) that contain missing values. The row is gone from your dataset.

df.fillna(0) replaces missing values with 0 — the row stays, but the NaN is replaced with the specified value.

The choice depends on context: - dropna() is appropriate when the row is unusable without the value (e.g., no order_id) - fillna(0) is appropriate when 0 is genuinely the right value (e.g., no discount applied = 0%) - fillna(0) is wrong when 0 is not meaningful (e.g., filling missing revenue with 0 makes zero-revenue rows indistinguishable from genuinely missing revenue)

Always ask: what does "unknown" mean for this column?

[Beginner] How do you find which columns have missing values and how many?

Show answer 
# Count of missing values per column
df.isnull().sum()

# Percentage missing per column
df.isnull().mean() * 100

# Combined report
missing = pd.DataFrame({
    "count": df.isnull().sum(),
    "pct": (df.isnull().mean() * 100).round(1),
}).sort_values("pct", ascending=False)

print(missing[missing["count"] > 0])

Also useful: df.info() shows non-null counts directly next to each column's dtype.

[Mid-level] What does MCAR, MAR, and MNAR mean? Why does it matter which type of missing data you have?

Show answer

MCAR — Missing Completely At Random The probability of a value being missing has no relationship to any variable in the dataset — it's random chance. Example: A logging bug randomly failed to record the sales_rep name for 3% of orders, regardless of order size, region, or channel. Why it matters: MCAR is the "safest" type. You can drop or impute these rows without creating bias — the remaining sample is still representative of the full population.

MAR — Missing At Random The probability of missing depends on other observed variables, but not on the missing value itself. Example: Online orders (observable) never have a sales_rep name. The missingness is explainable by the channel column. Why it matters: You can impute based on the related variable. Fill sales_rep with "Online" for all rows where channel == "online". You are not guessing blindly.

MNAR — Missing Not At Random The probability of missing depends on the value itself — you cannot observe why. Example: High-revenue orders are missing discount_pct because they are negotiated deals where the discount is confidential. The value is missing because of what the discount is. Why it matters: MNAR is the most dangerous type. If you impute with the mean discount, you are underestimating the discount on your highest-value orders — systematically biasing your margin analysis. Better to flag these rows as "Negotiated / Undisclosed" and treat them as a separate segment.

[Mid-level] When should you fill missing values with the mean vs the median?

Show answer

Use mean when: - The distribution is approximately symmetric (bell-shaped) - There are no significant outliers that would skew the mean

Use median when: - The distribution is right-skewed (revenue, order values, prices almost always are) - There are outliers — the median is robust to them, the mean is not - You are unsure — the median is the safer default for most business data

import matplotlib.pyplot as plt

# Quick check: how far apart are mean and median?
mean = df["total_amount"].mean()
median = df["total_amount"].median()
ratio = mean / median

print(f"Mean: £{mean:,.2f}")
print(f"Median: £{median:,.2f}")
print(f"Ratio: {ratio:.2f}x")

# If ratio > 1.2, the distribution is right-skewed — use median

In a sales dataset, total_amount is almost always right-skewed because a few large corporate orders pull the mean up. Imputing missing revenue with the mean would overestimate the "typical" missing order.

[Senior] You have a customer churn prediction model. The income column is missing for 35% of rows — and you suspect these are predominantly high-income customers who chose not to report. How would you handle this?

Show answer

This is a classic MNAR problem. High-income customers are less likely to report their income, which means the missingness correlates directly with the value itself. Imputing with the mean or median would systematically under-estimate the income of the missing group — and your churn model would learn the wrong signal.

Step 1: Confirm the MNAR hypothesis 

# Is income missingness correlated with other proxies for wealth?
df["income_missing"] = df["income"].isna().astype(int)
# Do customers with higher spend have higher missingness?
print(df.groupby("income_missing")["total_spend"].median())
# Do premium subscribers have higher missingness?
print(df.groupby("income_missing")["subscription_tier"].value_counts(normalize=True))
If the data confirms the hypothesis, imputing with mean/median will create bias.

Step 2: Create a missingness indicator feature 

df["income_missing"] = df["income"].isna().astype(int)
This gives the model the information that "this customer did not report income" — which itself is a predictive signal for being high-income.

Step 3: Use a reasonable imputation for rows that need a numeric value If the model requires a number in the income column, impute with the median of the non-missing group (acknowledging the bias), but keep the income_missing flag so the model can learn to discount the imputed value.

Step 4: Document the limitation In your model card or analysis report, note that income is MNAR for 35% of customers and that model performance on this segment may be lower than overall reported metrics.

Previous: Agenda | Next: Duplicates