How to Handle Missing Data in Analytics

Missing values are in almost every real dataset: a form field left blank, a system that did not record a value, a question that did not apply. Most tools offer a one-click fix, delete the rows or fill in the average, and both can quietly change your conclusions. The right approach depends on why the data is missing, which is why the first step is always to look at the pattern before choosing a method.

This guide works through the problem on real data: 48,842 records from the 1994 US Census used in the well-known Adult dataset, which records whether each person earns more than 50,000 dollars a year (UCI: Adult).

Step 1: Count the gaps and look for patterns

import pandas as pd
from sklearn.datasets import fetch_openml

census = fetch_openml(data_id=1590, as_frame=True).frame
census["high_income"] = (census["class"] == ">50K").astype(int)
print(census.shape, "| missing:", census.isna().sum()[lambda s: s > 0].to_dict())
no_workclass = census["workclass"].isna()
print("rows missing workclass:", no_workclass.sum(), "| of those, also missing occupation:",
      (no_workclass & census["occupation"].isna()).sum())

(48842, 16) | missing: {'workclass': 2799, 'occupation': 2809, 'native-country': 857}
rows missing workclass: 2799 | of those, also missing occupation: 2799

Three columns have gaps, and two of them travel together: every one of the 2,799 records without a work class also lacks an occupation. Values that go missing together usually share a cause, such as a question skipped by a particular kind of respondent, or a record from a system that did not capture employment details. The dataset alone cannot say which; the documentation or the data's owner might.

Step 2: Ask whether the missing rows are different

The key question is whether the people with gaps resemble everyone else:

compare = census.groupby(no_workclass.map({True: "workclass missing", False: "workclass present"})).agg(
    rows=("age", "size"), median_age=("age", "median"), median_hours=("hours-per-week", "median"),
    high_income_rate=("high_income", "mean"))
print(compare.round(3).to_string())

                    rows  median_age  median_hours  high_income_rate
workclass                                                           
workclass missing   2799        34.0          36.0             0.095
workclass present  46043        37.0          40.0             0.248

They do not. Only 9.5% of people with a missing work class earn over 50,000 dollars, against 24.8% of everyone else, and they work fewer hours. The data is not missing completely at random. Statisticians distinguish three situations: missing completely at random, where the gaps are unrelated to anything; missing at random, where they depend on other recorded variables; and missing not at random, where they depend on the missing value itself (Wikipedia: missing data). Only in the first case does removing incomplete rows leave a representative sample.

Step 3: See what dropping rows does

complete = census.dropna()
print(f"share earning over 50K, all rows: {census['high_income'].mean():.2%} | complete rows only: {complete['high_income'].mean():.2%} "
      f"({len(census) - len(complete)} rows dropped)")

share earning over 50K, all rows: 23.93% | complete rows only: 24.78% (3620 rows dropped)

Dropping the 3,620 incomplete rows raises the headline rate from 23.93% to 24.78%, because the removed group earned less. A difference under one percentage point may or may not matter for your question, but it is a bias you introduced, and in other datasets the same mistake produces much larger shifts. Dropping rows is safe only when the gaps are few and unrelated to what you are measuring, and it should always be reported.

Step 4: See what filling does

The common alternative is to fill gaps with the most common value, or the mean for numbers:

top = census["occupation"].mode()[0]
share = lambda s: (s == top).mean()
print(f"'{top}' share: with gaps left as missing {share(census['occupation']):.1%} | "
      f"after filling gaps with it {share(census['occupation'].fillna(top)):.1%} | "
      f"gaps as their own 'Unknown' category {census['occupation'].isna().mean():.1%} of rows")

'Prof-specialty' share: with gaps left as missing 12.6% | after filling gaps with it 18.4% | gaps as their own 'Unknown' category 5.8% of rows

Filling every gap with the most common occupation inflates that occupation's share from 12.6% to 18.4%, by almost half, and assigns a professional occupation to a group that, as step 2 showed, earns much less than professionals typically do. Any breakdown by occupation would now be distorted. Keeping the gaps as an explicit "Unknown" category, 5.8% of rows, is more honest: it preserves the totals and shows readers how much is unknown.

Choosing a method

For models, scikit-learn's imputers can add indicator columns that record which values were missing, so that information is not lost when gaps are filled (scikit-learn: imputation). In spreadsheets and pandas, the equivalent is a simple true or false column next to the filled one (pandas: working with missing data).

Things that look like values but are gaps

Missing data is not always blank. Watch for zeros that mean "not recorded", placeholder dates such as 1900-01-01, codes such as -1 or 999, text such as "N/A", "none" or "?", and empty strings that tools do not recognise as missing. Convert them to proper missing values before counting. How to audit a messy dataset shows a scientific dataset where negative numbers stood for "not available", and data cleaning, step by step covers the rest of the cleaning process.

Reporting missing data honestly

• State how many values are missing in each field that matters to the conclusion.
• Say what you did with them and why.
• Where the choice could change the answer, show the result both ways.
• Never let a filled value appear in a report as if it had been observed.

Handling missing values is part of the data cleaning and preparation module of the Data Analytics course.

Sources

• Missing data (Wikipedia) — Wikipedia
• Adult (UCI Machine Learning Repository) — UCI Machine Learning Repository
• Working with missing data (pandas user guide) — pandas documentation
• Imputation of missing values (scikit-learn) — scikit-learn documentation