cassionData Analysis

Back to the lessonLesson 4 of 8Getting the export in

Codes, sentinels and the 99 that becomes a mean

The same deck as the downloads, rendered as a page. Start the slideshow to present it full screen — arrow keys or a click advance one slide, Escape leaves.

Slides · PDFSlides · PowerPoint

  1. Slide 1 / 26

    What this lesson covers

    • A number that means "no answer"
    • Categorical vocabularies
    • Booleans recorded five ways
    • Text that should be one value
    • Stata and SPSS carry their labels with them
    • The read function, assembled
    • What comes next
    Speaker notes
    Missing-value sentinels, categorical vocabularies, booleans recorded five ways, and Stata value labels — turning what the form recorded into what pandas can compute on.

  2. Slide 2 / 26

    A number that means "no answer" — In Python

    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
    Speaker notes
    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.

  3. Slide 3 / 26

    A number that means "no answer"

    • Declare sentinels at read time
    Speaker notes
    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.

  4. Slide 4 / 26

    A number that means "no answer" — In Python

    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())

  5. Slide 5 / 26

    A number that means "no answer"

    • Scope the sentinel to the column — na_values=["-99"] without a dictionary applies it to every column in the file,…
    • The sentinel list is documentation, not folklore
    Speaker notes
    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. Sentinels come from the form's codebook. Write them down where the code can see them:

  6. Slide 6 / 26

    A number that means "no answer" — In Python

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

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

  7. Slide 7 / 26

    A number that means "no answer" — In Python

    for column in ["muac_mm", "age_months"]:
    print(column, sorted(muac[column].dropna().unique())[:5],
          sorted(muac[column].dropna().unique())[-5:])
    Speaker notes
    A sentinel you did not know about is invisible. This is worth one direct check per numeric column before trusting it:

  8. Slide 8 / 26

    A number that means "no answer"

    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.
    Speaker notes
    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.

  9. Slide 9 / 26

    Categorical vocabularies — In Python

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

muac["outcome"] = muac["outcome"].astype(OUTCOMES)
print(muac["outcome"].isna().sum())
    Speaker notes
    outcome in the MUAC register takes four values and sex takes two. Declaring the vocabulary buys you an error when something outside it appears.

  10. Slide 10 / 26

    Categorical vocabularies

    • Count the missing immediately after converting — This is the trap: a value outside the declared categories becomes…
    Speaker notes
    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:

  11. Slide 11 / 26

    Categorical vocabularies — In Python

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

  12. Slide 12 / 26

    Categorical vocabularies

    • Ordered categories
    Speaker notes
    Some vocabularies have an order, and declaring it makes comparison work:

  13. Slide 13 / 26

    Categorical vocabularies — In Python

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

wash["service"] = wash["service"].astype(LADDER)
at_least_basic = wash["service"] >= "basic"
    Speaker notes
    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".

  14. Slide 14 / 26

    Booleans recorded five ways — In Python

    print(muac["oedema"].value_counts(dropna=False))
    Speaker notes
    The oedema column is the standard example. Two communes recorded Y and N in the first quarter; the rest recorded true and false.

  15. Slide 15 / 26

    Booleans recorded five ways — In Python

    # 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)
    Speaker notes
    Never cast such a column directly:

  16. Slide 16 / 26

    Booleans recorded five ways — In Python

    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
    Speaker notes
    Map it explicitly, with an allow-list:

  17. Slide 17 / 26

    Booleans recorded five ways — In Python

    muac["oedema"] = muac["oedema"].astype("boolean")     # True / False / <NA>
    Speaker notes
    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: 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.

  18. Slide 18 / 26

    Text that should be one value — In Python

    print(wash["district"].value_counts())
# Nord-Ouest    727
# NORD-OUEST     33
# Nord Ouest     22
# nord-ouest     20
    Speaker notes
    Free-text-ish columns arrive with case, spacing and spelling variation. The WASH survey has a district written four ways.

  19. Slide 19 / 26

    Text that should be one value — In Python

    wash["district"] = (
    wash["district"].astype("string").str.strip().str.lower()
    .str.replace(r"\s+", "-", regex=True)
)
print(wash["district"].nunique())     # 3
    Speaker notes
    Ungrouped, that splits the district into four fragments, none of which looks alarming.

  20. Slide 20 / 26

    Text that should be one value — In Python

    CANONICAL = {
    "gonaives": "Gonaïves",
    "st-marc": "Saint-Marc",
    "saint-marc": "Saint-Marc",
}
wash["district"] = wash["district"].map(CANONICAL).fillna(wash["district"])
    Speaker notes
    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: Keep that table in the code, not in your head. It is a documented decision that someone will need to check.

  21. Slide 21 / 26

    Stata and SPSS carry their labels with them — In Python

    survey = pd.read_stata(path)                        # values converted to labels
survey = pd.read_stata(path, convert_categoricals=False)   # raw codes
    Speaker notes
    A .dta from a survey firm holds both the code and its meaning: 1 = Yes, 2 = No, 9 = Don't know.

  22. Slide 22 / 26

    Stata and SPSS carry their labels with them — In Python

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

print(variables["hh_size"])
print(labels.get("consent", {}))
    Speaker notes
    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: 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".

  23. Slide 23 / 26

    The read function, assembled — In Python (cont.)

    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,
    )
    Speaker notes
    Everything above belongs in one function the notebook and the scripts share:

  24. Slide 24 / 26

    The read function, assembled — In Python (cont.)

        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
    Speaker notes
    One function, one place to fix when the next export introduces a fifth way of writing "no".

  25. Slide 25 / 26

    What comes next

    • The table is in memory and every column means what it says.
    Speaker notes
    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.

  26. Slide 26 / 26

    Where this goes next

    Read the full lesson, with runnable code Back to the lesson