Back to the lesson·Lesson 5 of 8·Service is a year, not a day
74.4%, 33.9%, 83.3%
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.
What this lesson covers
- What a repeat-visit register makes possible
- Rate one: the share of visits that found a working point
- Rate two: the share of points that worked every time
- Rate three: the share of people with a working point
- Which one to report
- The two points counted twice
- What comes next
Speaker notes
Three functionality rates from one register, all correct. They differ because they count visits, points and people, and only one of them answers the question a water programme is actually asked.What a repeat-visit register makes possible — In Python
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())Speaker notes
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.What a repeat-visit register makes possible — In R
library(dplyr) points |> summarise(visits = n(), water_points = n_distinct(water_point_id)) points |> count(functional_status)Speaker notes
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 — In Python
WORKING = {"functional", "partially-functional"} working = points["functional_status"].isin(WORKING) print(f"visit-level functionality: {working.mean():.1%} of {len(points):,} visits")Rate one: the share of visits that found a working point — In R
points |> summarise(functionality = mean(functional_status %in% c("functional", "partially-functional")), n = n())Rate one: the share of visits that found a working point
- 74.4% — This is what almost every water point report publishes, and it answers *"if I visit a point at random, will it…
- Partially functional counts as working — A point running at reduced yield is providing water, and classifying it as…
Speaker notes
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 — In Python
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()}")Rate two: the share of points that worked every time — In R
points |> summarise(always = all(functional_status %in% c("functional", "partially-functional")), .by = water_point_id) |> summarise(share = mean(always), n = n())Rate two: the share of points that worked every time
- 33.9% — 82 of 242 points
- 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
Speaker notes
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 — In Python
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%}")Rate three: the share of people with a working point — In R
points |> filter(!is.na(users_estimated)) |> summarise(weighted = sum(users_estimated[functional_status %in% c("functional", "partially-functional")]) / sum(users_estimated))Rate three: the share of people with a working point
- 83.3% — nearly nine points above the visit-level figure
Speaker notes
83.3%, nearly nine points above the visit-level figure. The reason is in the source types.Rate three: the share of people with a working point — In Python
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))Rate three: the share of people with a working point — In R
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)Rate three: the share of people with a working point
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 Rate three: the share of people with a working point
- The points that serve the most people break the least — So weighting by population moves the figure up, and the gap…
Speaker notes
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 — Example
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%.Speaker notes
All three, and this is one of the cases where three numbers is genuinely the right answer.Which one to report
- If you must give one, give the population-weighted rate and say so — because the standard is about people rather than…
Speaker notes
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 — In Python
duplicates = points[points["water_point_id"].str.startswith("WP09")] print(duplicates.groupby("water_point_id")["community"].agg(["nunique", "first"]))The two points counted twice — In R
points |> filter(startsWith(water_point_id, "WP09")) |> count(water_point_id)Speaker notes
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.
Speaker notes
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.