cassionData Analysis

Back to the lessonLesson 5 of 8Making the pieces line up

Attaching a population denominator

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 / 21

    What this lesson covers

    • The numerator is the easy half
    • Where a denominator actually comes from
    • Join on the code, never on the name
    • The projection, and the year nobody states
    • Catchment populations do not add up
    • Coverage above 100%
    • Rates per thousand, and the annualisation trap
    • Write the denominator down
    • What comes next
    Speaker notes
    Where a denominator comes from, how to join it without matching on a name, and why catchment populations do not add up to a district. Plus the coverage figure above 100% and what it is really telling you.

  2. Slide 2 / 21

    The numerator is the easy half

    • Counting what your programme did is bookkeeping.
    Speaker notes
    Counting what your programme did is bookkeeping. Deciding what to divide it by is the analysis, and it is where a coverage figure is won or lost. The vaccination extract makes this concrete: it ships target_population in the file, one figure per facility, constant across the twelve months. That is convenient and it is also somebody's estimate, made at some point, by some method, and the whole of this lesson is about not treating it as a fact just because it arrived in a column.

  3. Slide 3 / 21

    Where a denominator actually comes from

    • A census projection. The national statistics office publishes a census and a growth rate; every district population…
    • An administrative population frame. The health ministry's own denominators by facility catchment, which is what…
    • A programme target. How many people the programme planned to reach. A legitimate denominator for "did we do what we…
    • A survey. The most defensible and the least available, because a survey gives you a proportion with a confidence…
    • Name the source in the column — target_population_moh_2024 and target_population_census_projection are different…
    Speaker notes
    Four sources, in roughly descending order of how often they are argued about. Name the source in the column. target_population_moh_2024 and target_population_census_projection are different numbers, and the moment two of them are in the same folder without labels somebody will average them.

  4. Slide 4 / 21

    Join on the code, never on the name — In Python

    population = pd.read_csv("admin-population-2024.csv")   # admin2_pcode, year, pop_total, pop_under1

vax["admin2_pcode"] = vax["admin2_pcode"].astype("string").str.strip().str.upper()

with_denominator = vax.merge(
    population.query("year == 2024"),
    on="admin2_pcode", how="left", validate="many_to_one",
)

missing = with_denominator["pop_under1"].isna().sum()
assert missing == 0, f"{missing} rows have no population figure"
    Speaker notes
    The cleaning course made this argument about place names. It is worth repeating here because a population frame is where a name join does the most damage: the denominator is the one column where a failed match does not look like a failed match, it looks like a facility with no coverage.

  5. Slide 5 / 21

    Join on the code, never on the name — In R

    population <- read_csv("admin-population-2024.csv")

with_denominator <- vax |>
  mutate(admin2_pcode = toupper(stringr::str_squish(admin2_pcode))) |>
  left_join(filter(population, year == 2024),
            by = "admin2_pcode", relationship = "many-to-one")

stopifnot(!any(is.na(with_denominator$pop_under1)))
    Speaker notes
    The assertion is the important line. A left join that fails to match leaves a missing denominator; a missing denominator makes a coverage figure missing; and a missing coverage figure gets excluded from a mean, so the district average silently becomes the average of the places that matched.

  6. Slide 6 / 21

    The projection, and the year nobody states — In Python

    GROWTH = 0.024   # national annual growth rate, published with the census
CENSUS_YEAR = 2015

population["pop_2024"] = population["pop_census"] * (1 + GROWTH) ** (2024 - CENSUS_YEAR)
    Speaker notes
    A census projection is a base population and a growth rate. Compute it in the open:

  7. Slide 7 / 21

    The projection, and the year nobody states — In R

    GROWTH <- 0.024
CENSUS_YEAR <- 2015

population <- population |>
  mutate(pop_2024 = pop_census * (1 + GROWTH)^(2024 - CENSUS_YEAR))

  8. Slide 8 / 21

    The projection, and the year nobody states

    • Write the census year, the growth rate and its source next to the number.
    • Use the same projection for every indicator in a report. Two indicators on two projections cannot be compared, and…
    • Where displacement has happened, say that the projection does not account for it, and give the direction of the error…
    Speaker notes
    Nine years at 2.4% is a factor of 1.24. A quarter of your denominator is an assumption, and it compounds: two districts projected from the same census with the same national rate will preserve their relative sizes exactly, which is precisely what does not happen where there has been displacement. So when a coverage figure is challenged, the projection is usually the honest answer. Three habits that keep it defensible:

  9. Slide 9 / 21

    Catchment populations do not add up — In Python

    by_facility = vax.query("antigen == 'penta3' and period == '2024-01-01'")
print("sum of facility targets:", by_facility["target_population"].sum())
    Speaker notes
    A facility's catchment is the population it is supposed to serve. Sum the catchments of every facility in a district and you will not get the district.

  10. Slide 10 / 21

    Catchment populations do not add up — In R

    vax |>
  filter(antigen == "penta3", period == "2024-01-01") |>
  summarise(total = sum(target_population))

  11. Slide 11 / 21

    Catchment populations do not add up — In Python

    district = (
    vax.query("antigen == 'penta3'")
    .groupby("period")
    .agg(doses=("doses_administered", "sum"),
         target=("target_population", "sum"))
)
district["coverage"] = district["doses"] / district["target"]
    Speaker notes
    Here that sum is 11,774 for the month. It is a usable district denominator only if the catchments partition the district — no overlap, no gaps — and they almost never do. Two facilities on either side of a town both count the town. A population between two catchments is counted twice or not at all. The consequence is specific and common: facility-level coverage is unreliable and district-level coverage is fine. People cross catchment boundaries to be vaccinated, so a facility's numerator includes children from its neighbour's denominator. Aggregate to the level where the boundary crossing happens inside the unit, and the noise cancels.

  12. Slide 12 / 21

    Catchment populations do not add up — In R

    district <- vax |>
  filter(antigen == "penta3") |>
  summarise(doses = sum(doses_administered),
            target = sum(target_population), .by = period) |>
  mutate(coverage = doses / target)

  13. Slide 13 / 21

    Coverage above 100%

    • The denominator is too small. An out-of-date projection, or a catchment coefficient that assumes fewer under-ones…
    • The numerator includes people from outside. Boundary crossing, or a campaign that drew people in from a…
    • The grain is wrong. Doses counted where children were meant — a child receiving three doses of a three-dose antigen…
    • A join multiplied something. Everything in lesson 1.
    Speaker notes
    It happens constantly and it is never a programme exceeding its population. Four causes, in the order worth checking:

  14. Slide 14 / 21

    Coverage above 100% — In Python

    over = district[district["coverage"] > 1]
print(over)

  15. Slide 15 / 21

    Coverage above 100% — In R

    district |> filter(coverage > 1)

  16. Slide 16 / 21

    Coverage above 100%

    • Report it rather than capping it — A coverage figure clipped at 100% has thrown away the evidence that the denominator…
    Speaker notes
    Report it rather than capping it. A coverage figure clipped at 100% has thrown away the evidence that the denominator is wrong, and the denominator being wrong is the finding.

  17. Slide 17 / 21

    Rates per thousand, and the annualisation trap — In Python

    district["per_1000"] = 1000 * district["doses"] / district["target"]

  18. Slide 18 / 21

    Rates per thousand, and the annualisation trap — In R

    district <- district |> mutate(per_1000 = 1000 * doses / target)
    Speaker notes
    Two traps live in the same place. First, a monthly coverage figure is not an annual one, and multiplying by twelve assumes every month reported — which the cleaning course showed is false in August and September here. Second, a rate per thousand needs its period stated in the label: doses_per_1000_under1_per_month is unambiguous, rate is not.

  19. Slide 19 / 21

    Write the denominator down

    FieldValue
    IndicatorPenta3 coverage, district
    NumeratorPenta3 doses administered, facilities reporting
    DenominatorSurviving infants, MoH catchment figures summed to district
    SourceMoH population estimates 2024, projected from 2015 census at 2.4%
    DisaggregationMonth, facility type
    CaveatReporting rate 29% in August; coverage computed on reporting facilities only
    Speaker notes
    Every indicator you publish should carry a reference row: That table takes two minutes and settles the question permanently. It is the same move as the definitions file the foundations course writes beside a results table, and it is what the Indicator Design and the LogFrame course later builds into a full reference sheet.

  20. Slide 20 / 21

    What comes next

    • The denominator answers "out of how many people".
    Speaker notes
    The denominator answers "out of how many people". The next lesson answers "out of how many periods" — building a complete calendar so that a facility which never reported in August appears as a gap rather than not appearing at all.

  21. Slide 21 / 21

    Where this goes next

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