The round nobody drove

The denominator that was never questioned

Every functionality figure in the last two lessons divided by visits made. Nobody chose that denominator; it is what a groupby produces from a file whose rows are visits.

The denominator you meant is visits due, and the register knows what that is: 242 points, twelve scheduled rounds.

import pandas as pd

points = pd.read_csv("water-point-monitoring-2024.v1.csv", parse_dates=["visit_date"])

due = points["water_point_id"].nunique() * 12
made = len(points)
print(f"due {due:,}, made {made:,}, coverage {made / due:.1%}")

library(dplyr)

points |> summarise(
  due = n_distinct(water_point_id) * 12,
  made = n(),
  coverage = n() / (n_distinct(water_point_id) * 12)
)

2,629 of 2,904 — 90.5% coverage. Two hundred and seventy-five rounds were never driven, and the register does not contain a row saying so. This is the DHIS2 course’s reporting rate arriving in a different file format: absence is not a value, and nothing in the data will remind you it happened.

Coverage is not spread evenly, which is the whole problem

coverage = points.groupby("district").agg(
    points=("water_point_id", "nunique"),
    visits=("water_point_id", "size"),
)
coverage["due"] = coverage["points"] * 12
coverage["coverage"] = coverage["visits"] / coverage["due"]
print(coverage.round(3))

points |>
  summarise(points = n_distinct(water_point_id), visits = n(), .by = district) |>
  mutate(due = points * 12, coverage = visits / due)

District	Points	Visits made	Due	Coverage
Sud-Est	79	913	948	96.3%
Centre	79	882	948	93.0%
Nord-Ouest	84	834	1,008	82.7%

Nord-Ouest’s rounds collapse when the roads close.

monthly = points.pivot_table(
    index=points["visit_date"].dt.month, columns="district",
    values="water_point_id", aggfunc="size",
)
print(monthly)

points |> count(district, month = lubridate::month(visit_date)) |>
  tidyr::pivot_wider(names_from = district, values_from = n)

From about 77 points a month to between 51 and 60 in June, July, August and September. Centre and Sud-Est barely move.

Bound the answer

The honest response to a denominator you cannot fully observe is not a correction factor. It is a range, computed from the two assumptions that bracket the truth.

• Upper bound: every missed visit would have found a working point. That is the observed rate, which already assumes the missed visits look like the seen ones.
• Lower bound: every missed visit would have found a broken point.

WORKING = {"functional", "partially-functional"}
ok = points["functional_status"].isin(WORKING)

bounds = points.assign(ok=ok).groupby("district").agg(
    ok=("ok", "sum"), made=("ok", "size"), points=("water_point_id", "nunique"),
)
bounds["due"] = bounds["points"] * 12
bounds["observed"] = bounds["ok"] / bounds["made"]
bounds["lower"] = bounds["ok"] / bounds["due"]
bounds["band"] = bounds["observed"] - bounds["lower"]
print((bounds[["observed", "lower", "band"]] * 100).round(1))

points |>
  mutate(ok = functional_status %in% c("functional", "partially-functional")) |>
  summarise(ok = sum(ok), made = n(), pts = n_distinct(water_point_id),
            .by = district) |>
  mutate(due = pts * 12, observed = ok / made, lower = ok / due)

District	Observed	Lower bound	Band
Nord-Ouest	76.4%	63.2%	13.2
Centre	75.7%	70.5%	5.3
Sud-Est	71.4%	68.8%	2.6

Nord-Ouest is the best district on the observed figure and the only one whose ranking is not safe. Its band overlaps both other districts completely. Sud-Est looks worst and is the only one you can be confident about, because it visited nearly everything.

A number computed on 96% of its denominator and a number computed on 83% are not comparable, and nothing in the table says so unless you put it there.

Is the missingness random?

The bounds are wide because they assume nothing. Narrowing them means arguing that the missed visits resemble the made ones, and that argument is testable.

observed = points.pivot_table(
    index="water_point_id", columns="round", values="functional_status",
    aggfunc="first",
)
followed_by_gap = []
for point, row in observed.iterrows():
    for r in range(1, 12):
        if pd.isna(row.get(r)):
            continue
        broken = row[r] not in WORKING
        followed_by_gap.append((pd.isna(row.get(r + 1)), broken))

check = pd.DataFrame(followed_by_gap, columns=["gap_next", "broken"])
print(check.groupby("gap_next")["broken"].agg(["mean", "size"]).round(3))

# Same shape: was the point broken at the visit immediately before a missed round?

26.7% of visits that were followed by a missed round found a broken point, against 24.7% of visits that were followed by a made one. The difference points the way you would fear — a point known to be broken is a little likelier to be skipped — and it is small enough that it neither proves the bias nor rules it out.

That is a legitimate finding and it should be reported as one. A test that comes back ambiguous is not a failed test; it tells you the bounds cannot honestly be narrowed, which is what you needed to know.

What not to do

Three repairs that all look reasonable.

Carry the last observation forward. Fill each missing round with the point’s previous status. It gives 74.2% overall, almost identical to the observed rate, and it is the assumption that a broken point stays broken and a working one keeps working — which is exactly the thing in question. It manufactures precision without adding information.

Impute from the district mean. Assumes the missed points are like the visited ones in the same district, which is the assumption the whole lesson doubts.

Drop the district. Removes the worst-covered district from the report, which is nearly always also the hardest to reach and the worst served.

print("Reported: 76.4% (observed), 63.2% to 76.4% (bounds), coverage 82.7%")
print("Not reported: a single imputed number that hides which of these it is")

# The band and the coverage are the finding. Neither is a caveat.

Report coverage beside every rate

Water point functionality by district, 2024

  District      Functionality   Coverage   Range if unvisited were broken
  Sud-Est          71.4%          96.3%    68.8 - 71.4
  Centre           75.7%          93.0%    70.5 - 75.7
  Nord-Ouest       76.4%          82.7%    63.2 - 76.4

  275 of 2,904 scheduled visits were not made, concentrated in Nord-Ouest
  during June to September when roads are impassable. Districts are not
  ranked here: Nord-Ouest's range overlaps both others.

The rate, the coverage, the range, and a sentence declining to rank. Refusing to rank is a result, and it is more useful than a league table that reverses the moment somebody drives the missing rounds.

What comes next

You now have every WASH number this course can produce and a set of caveats attached to each. The last lesson is the report they go into — which figures belong on the same page, which denominators must be stated, and the one table that tells a reader what may be compared with what.