74.4%, 33.9%, 83.3%

What a repeat-visit register makes possible

Everything in the first unit is a cross-section. A household survey can tell you what service existed on the day the enumerator called, and it structurally cannot tell you how much of the year the water was there.

The monitoring register can, because it visits the same points again.

import pandas as pd

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

print(f"visits: {len(points):,}")
print(f"water points: {points['water_point_id'].nunique()}")
print(points["functional_status"].value_counts())

library(dplyr)

points |> summarise(visits = n(), water_points = n_distinct(water_point_id))
points |> count(functional_status)

2,629 visits to 242 points across twelve monthly rounds. That structure supports three different functionality rates, and the difference between them is not a methodological quibble — it is three different questions.

Rate one: the share of visits that found a working point

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

print(f"visit-level functionality: {working.mean():.1%} of {len(points):,} visits")

points |> summarise(functionality = mean(functional_status %in%
                    c("functional", "partially-functional")), n = n())

74.4%. This is what almost every water point report publishes, and it answers “if I visit a point at random, will it be working?”

It is the easiest to compute and the easiest to bias, because the denominator is visits made rather than visits due. Lesson 7 is entirely about that.

Partially functional counts as working. A point running at reduced yield is providing water, and classifying it as failure would put a queue problem in the same category as a dry borehole. Say which side of the line you put it, because reasonable analysts differ and the two answers are three points apart.

Rate two: the share of points that worked every time

by_point = points.assign(ok=working).groupby("water_point_id")["ok"]
always = by_point.all()

print(f"point-level functionality: {always.mean():.1%} of {len(always)} points")
print(f"points that failed at least once: {(~always).sum()}")

points |>
  summarise(always = all(functional_status %in%
            c("functional", "partially-functional")), .by = water_point_id) |>
  summarise(share = mean(always), n = n())

33.9% — 82 of 242 points. Two thirds of the network failed at least once during the year.

This answers a different question: “how many of these assets are reliable?” And it is the number an asset manager needs, because a point that works three visits in four is not three quarters of a water supply — it is a water supply with a gap in it that a household has to solve some other way.

The two rates are not in tension. 74.4% of visits and 33.9% of points are both true, of the same file, at the same time. A point can be working most of the time and still fail during the year, and a network can be mostly up and mostly unreliable.

Rate three: the share of people with a working point

served = points.dropna(subset=["users_estimated"])
population_weighted = (
    served.loc[served["functional_status"].isin(WORKING), "users_estimated"].sum()
    / served["users_estimated"].sum()
)
print(f"population-weighted functionality: {population_weighted:.1%}")

points |>
  filter(!is.na(users_estimated)) |>
  summarise(weighted = sum(users_estimated[functional_status %in%
            c("functional", "partially-functional")]) / sum(users_estimated))

83.3%, nearly nine points above the visit-level figure. The reason is in the source types.

print(points.groupby("source_type").agg(
    points=("water_point_id", "nunique"),
    median_users=("users_estimated", "median"),
    functionality=("functional_status", lambda s: s.isin(WORKING).mean()),
).round(3))

points |>
  summarise(n = n_distinct(water_point_id),
            median_users = median(users_estimated, na.rm = TRUE),
            functionality = mean(functional_status %in%
              c("functional", "partially-functional")), .by = source_type)

Source type	Functionality	Typical users
Piped scheme tap	97.2%	about 1,290
Handpump borehole	75.5%	about 280
Protected spring	66.6%	about 200
Protected well	58.1%	about 185

The points that serve the most people break the least. So weighting by population moves the figure up, and the gap between 74.4% and 83.3% is a real statement about who bears the failures: the people on protected wells, who are the fewest per point and the worst served.

Which one to report

All three, and this is one of the cases where three numbers is genuinely the right answer.

Water point functionality, 2024

  Visit-level        74.4%   1,957 of 2,629 monitoring visits
  Point-level        33.9%      82 of 242 points working at every visit
  Population-weighted 83.3%   of an estimated 96,700 users

  Partially functional counted as working (134 visits).
  Two points were re-registered after a handover and are counted twice;
  removing them moves the visit-level figure to 74.7%.

If you must give one, give the population-weighted rate and say so, because the standard is about people rather than assets. But publish the point-level rate next to it, because it is the one that says the network is not reliable, and the population-weighted rate hides that behind a handful of well-run schemes.

The two points counted twice

duplicates = points[points["water_point_id"].str.startswith("WP09")]
print(duplicates.groupby("water_point_id")["community"].agg(["nunique", "first"]))

points |> filter(startsWith(water_point_id, "WP09")) |> count(water_point_id)

Two points were handed from one programme to another and re-registered under new identifiers, so the register holds them twice and every denominator is two points too large. The effect is small — 74.4% becomes 74.7% — and the size of the effect is not the reason to fix it. An asset register that double-counts is wrong about what exists, and the next thing anyone does with it is plan a maintenance budget.

What comes next

Two thirds of these points failed at least once. That number treats a borehole that ran dry in February exactly like one that has been abandoned since March, and the next lesson separates them — using the sequence of visits rather than the status field, because the status field cannot tell them apart.