Codes, sentinels and the 99 that becomes a mean — Python for Programme Data — Data Analysis

A number that means “no answer”

Paper forms and the systems built to mirror them do not have a blank. They have a code. 99 means “not answered”, 88 means “not applicable”, -99 means “not measured”, and 999 means someone needed a third one.

None of them are values. All of them are numbers as far as pandas is concerned.

import pandas as pd

muac = pd.read_csv(RAW / "muac-screening-artibonite-2024.v1.csv")
print(muac["muac_mm"].mean())        # several millimetres below the truth

The result is wrong and, worse, plausible. Nothing about it looks like an error, and a MUAC mean is not a figure most readers can sanity-check by eye. A 99 in an age column is even quieter — it is a possible age.

Declare sentinels at read time

muac = pd.read_csv(
    RAW / "muac-screening-artibonite-2024.v1.csv",
    dtype={"child_id": "string", "commune": "string"},
    na_values={"muac_mm": ["-99"]},
)

print(muac["muac_mm"].mean())
print(muac["muac_mm"].isna().sum())

Scope the sentinel to the column. na_values=["-99"] without a dictionary applies it to every column in the file, which is right until a column legitimately holds -99.

The sentinel list is documentation, not folklore

Sentinels come from the form’s codebook. Write them down where the code can see them:

SENTINELS = {
    "muac_mm": ["-99"],           # not measured
    "age_months": ["99", "-1"],   # not answered / not applicable
}

muac = pd.read_csv(path, na_values=SENTINELS)

A sentinel you did not know about is invisible. This is worth one direct check per numeric column before trusting it:

for column in ["muac_mm", "age_months"]:
    print(column, sorted(muac[column].dropna().unique())[:5],
          sorted(muac[column].dropna().unique())[-5:])

Values clustered at the extremes — 98, 99 at the top of an age column, -1 at the bottom — are sentinels, not observations. A histogram with a spike at exactly 99 is the same signal.

Declaring a sentinel is not deciding what to do about the missing value. That decision — drop, impute, report separately — belongs to the analysis, and it has to be written down. Here you are only stopping a code from pretending to be a measurement.

Categorical vocabularies

outcome in the MUAC register takes four values and sex takes two. Declaring the vocabulary buys you an error when something outside it appears.

OUTCOMES = pd.CategoricalDtype(
    ["no-action", "referred-tsfp", "referred-otp", "referred-sc"], ordered=False
)

muac["outcome"] = muac["outcome"].astype(OUTCOMES)
print(muac["outcome"].isna().sum())

Count the missing immediately after converting. This is the trap: a value outside the declared categories becomes NaN rather than raising. A jump from zero to nine means nine rows carried a value you did not know about, and you want to see that now rather than discover it as a gap in a table three steps later.

To see what they were, compare before and after:

raw_values = set(muac_raw["outcome"].dropna().unique())
declared = set(OUTCOMES.categories)
print("unexpected:", raw_values - declared)

Ordered categories

Some vocabularies have an order, and declaring it makes comparison work:

LADDER = pd.CategoricalDtype(
    ["surface-water", "unimproved", "limited", "basic", "safely-managed"],
    ordered=True,
)

wash["service"] = wash["service"].astype(LADDER)
at_least_basic = wash["service"] >= "basic"

Without ordered=True that comparison raises. With it, the JMP ladder sorts and plots in the right order rather than alphabetically, which is the difference between a readable chart and one that puts “basic” between “unimproved” and “limited”.

Booleans recorded five ways

The oedema column is the standard example. Two communes recorded Y and N in the first quarter; the rest recorded true and false.

print(muac["oedema"].value_counts(dropna=False))

Never cast such a column directly:

# Wrong in a way that is hard to see: every non-empty string is truthy,
# so "false" becomes True.
muac["oedema"] = muac["oedema"].astype(bool)

Map it explicitly, with an allow-list:

BOOLEANS = {
    "true": True, "TRUE": True, "Y": True, "y": True, "yes": True, "1": True,
    "false": False, "FALSE": False, "N": False, "n": False, "no": False, "0": False,
}

muac["oedema"] = (
    muac["oedema"].astype("string").str.strip().str.lower()
    .map({k.lower(): v for k, v in BOOLEANS.items()})
)

print(muac["oedema"].isna().sum())    # anything the map did not cover

An allow-list rather than a heuristic, for the reason that makes this lesson worth a session: anything outside it stays missing and gets counted, rather than being guessed at. A value the map does not cover is a question for whoever entered it.

Use pandas’ nullable boolean when a genuine “not recorded” exists:

muac["oedema"] = muac["oedema"].astype("boolean")     # True / False / <NA>

bool cannot hold missing; boolean can. In this register 44 records have no oedema assessment at all, and collapsing those to False would silently assert that 44 children were checked and found clear.

Text that should be one value

Free-text-ish columns arrive with case, spacing and spelling variation. The WASH survey has a district written four ways.

print(wash["district"].value_counts())
# Nord-Ouest    727
# NORD-OUEST     33
# Nord Ouest     22
# nord-ouest     20

Ungrouped, that splits the district into four fragments, none of which looks alarming.

wash["district"] = (
    wash["district"].astype("string").str.strip().str.lower()
    .str.replace(r"\s+", "-", regex=True)
)
print(wash["district"].nunique())     # 3

Normalise before the first groupby, not after you notice the totals do not add up. And normalise into a canonical form you choose, rather than picking whichever spelling was most common — the most common spelling can change with the next export.

Where the variation is not mechanical — Gonaives versus Gonaïves, St-Marc versus Saint-Marc — a mapping table is the honest answer:

CANONICAL = {
    "gonaives": "Gonaïves",
    "st-marc": "Saint-Marc",
    "saint-marc": "Saint-Marc",
}
wash["district"] = wash["district"].map(CANONICAL).fillna(wash["district"])

Keep that table in the code, not in your head. It is a documented decision that someone will need to check.

Stata and SPSS carry their labels with them

A .dta from a survey firm holds both the code and its meaning: 1 = Yes, 2 = No, 9 = Don't know.

survey = pd.read_stata(path)                        # values converted to labels
survey = pd.read_stata(path, convert_categoricals=False)   # raw codes

pandas gives you one or the other. To keep both — which is what you want, because the code is what the codebook documents and the label is what a reader needs — read the metadata separately:

with pd.io.stata.StataReader(path) as reader:
    labels = reader.value_labels()
    variables = reader.variable_labels()

print(variables["hh_size"])
print(labels.get("consent", {}))

R’s haven preserves labels more completely than pandas does. If you are handed a .dta from a survey firm and the labels matter, reading it in R and writing a CSV plus a codebook is a legitimate step — and this is the one place in this course where the answer is “use the other language”.

The read function, assembled

Everything above belongs in one function the notebook and the scripts share:

from pathlib import Path
import pandas as pd

SENTINELS = {"muac_mm": ["-99"]}
OUTCOMES = pd.CategoricalDtype(
    ["no-action", "referred-tsfp", "referred-otp", "referred-sc"]
)
BOOLEANS = {"true": True, "y": True, "yes": True, "1": True,
            "false": False, "n": False, "no": False, "0": False}

def read_register(path: Path) -> pd.DataFrame:
    muac = pd.read_csv(
        path,
        dtype={"child_id": "string", "commune": "string", "sex": "string"},
        na_values=SENTINELS,
    )
    muac["screening_date"] = pd.to_datetime(
        muac["screening_date"], format="%Y-%m-%d", errors="raise"
    )
    muac["outcome"] = muac["outcome"].astype(OUTCOMES)
    muac["oedema"] = (
        muac["oedema"].astype("string").str.strip().str.lower()
        .map(BOOLEANS).astype("boolean")
    )

    assert muac["child_id"].notna().all(), "every row needs an identifier"
    return muac

One function, one place to fix when the next export introduces a fifth way of writing “no”.

What comes next

The table is in memory and every column means what it says. The next unit works it: selecting and filtering without the warning nobody reads, then grouping to the numerator and denominator an indicator actually needs.