Four kinds of missing in survey data

The problem

You run df.dropna() and your denominator changes. Nobody notices for a quarter, and by then the figure is in a report.

In a survey, an empty cell can mean at least four different things, and only one of them is "we do not know the answer".

What you will have at the end

The four kinds of missing separated, counted and labelled, and a rule for which ones belong in a denominator.

The four kinds

Skipped is not missing data. It is a correct, informative answer about a household the question did not apply to, and it must leave the denominator. Refused and don't-know are missing, but they are missing for different reasons and you report them separately.

The steps

Look at what is actually in the column

import pandas as pd

df = pd.read_csv("household-survey-messy.csv", dtype=str, keep_default_na=False)

income = df["monthly_income_ugx"].str.strip()
not_a_number = income[~income.str.replace(",", "").str.replace(" ", "").str.isdigit()]
print(not_a_number.value_counts())

          30
n/a        8
-88        6
NA         4
-99        3

Five spellings of "no number here", and they do not all mean the same thing.

Separate the skips from the rest

In this survey, consent drives the skip logic: a household that declined the interview has every later field blank, correctly.

consent = df["consent"].str.strip().str.lower()
refused_interview = consent.isin(["no", "n", "0", "false"])

print("declined the interview:", refused_interview.sum())
print("blank income among them:", (income[refused_interview] == "").sum())
print("blank income among the rest:", (income[~refused_interview] == "").sum())

declined the interview: 28
blank income among them: 28
blank income among the rest: 2

That is the whole lesson in three numbers. Of 30 blank incomes, 28 are skip logic — households that correctly never reached the question — and 2 are genuinely unexplained. A dropna() treats all 30 identically and silently removes 28 rows that were never a problem.

Encode the difference instead of deleting it

import numpy as np

def classify(value, skipped):
    value = value.strip()
    if skipped:
        return "not_applicable"
    if value == "":
        return "missing_unknown"
    if value == "-99":
        return "refused"
    if value == "-88":
        return "dont_know"
    if value.upper() in {"NA", "N/A"}:
        return "missing_unknown"
    return "answered"

df["income_status"] = [
    classify(v, s) for v, s in zip(df["monthly_income_ugx"], refused_interview)
]
print(df["income_status"].value_counts())

Now make the numeric column, with the non-answers as real missing values:

df["income_clean"] = pd.to_numeric(
    df["monthly_income_ugx"].str.replace(",", "").str.replace(" ", ""),
    errors="coerce",
)
df.loc[df["income_clean"] < 0, "income_clean"] = np.nan   # -99 and -88 are codes, not money

errors="coerce" turns anything unparseable into NaN instead of raising. The negative-value line matters: left alone, -99 is a perfectly valid number and will drag your mean down without a word.

The denominator

asked = df[df["income_status"] != "not_applicable"]
answered = asked[asked["income_status"] == "answered"]

print(f"mean income: {answered['income_clean'].mean():,.0f} UGX")
print(f"response rate: {len(answered)} of {len(asked)} asked "
      f"({len(answered) / len(asked):.1%})")

Report both. A mean without a response rate is half a finding.

Where it breaks

-99 counted as money

The single most expensive mistake in this lesson. Sentinel codes are valid numbers and no tool will warn you. Always check df["income_clean"].min() before you trust a mean.

keep_default_na=True erasing the evidence

Read normally, pandas turns NA and n/a into NaN before you see them — so you cannot tell a typed NA from an empty cell. By then the distinction is gone. This is why the previous lesson reads everything as text first.

dropna() on the whole frame

df.dropna()            # drops a row if ANY column is blank
df.dropna(subset=["income_clean"])   # drops only where income is missing

The first form will remove nearly every row of a survey, because somewhere in sixty columns each household has one blank. Always pass subset.

Zero is an answer

0 income is a household that earns nothing — a finding. NaN is a household you did not learn about. Never fill one with the other.

Your turn

Build the same classification for has_latrine, then answer two questions:

1. What share of households that were asked have a latrine?
2. How does that share change if you treat the skipped households as "no"?

The gap between those two numbers is what a careless fillna(0) would have done to your indicator.

Next

The district spelled four ways — normalising categories, and where fuzzy matching stops being safe.