Dry is not broken

One status, several different failures

functional_status says a point is not working. It does not say whether the pump is broken, the water table has dropped, the committee has stopped collecting fees, or the community has moved. Those need four different responses and one of them is not a repair.

The sequence of visits can separate them, because they have different shapes over time.

Shape over the year	What it is	What to do
Down in the same months every year, up in between	Seasonal — the source runs dry	Deepen, or provide a dry-season alternative
Down once, then up after a gap	Breakdown — repaired	Look at the length of the gap
Down and stays down to the end	Abandoned or awaiting parts	Find out which; they are not the same
Up and down repeatedly	Chronic — under-maintained or overloaded	Management problem, not an asset problem

The seasonal signal, and where it is not

import pandas as pd

points = pd.read_csv("water-point-monitoring-2024.v1.csv", parse_dates=["visit_date"])
WORKING = {"functional", "partially-functional"}
DRY_MONTHS = {1, 2, 3, 11, 12}

points["dry_season"] = points["visit_date"].dt.month.isin(DRY_MONTHS)
points["ok"] = points["functional_status"].isin(WORKING)

print(points.groupby("dry_season")["ok"].agg(["mean", "size"]).round(3))

library(dplyr)

points |>
  mutate(dry = lubridate::month(visit_date) %in% c(1, 2, 3, 11, 12),
         ok = functional_status %in% c("functional", "partially-functional")) |>
  summarise(functionality = mean(ok), n = n(), .by = dry)

67.8% in the dry months against 79.4% in the wet ones. An eleven-point swing, which a report comparing a March assessment to a July one would read as a collapse or a recovery depending on which way round it did the subtraction.

Now split it by source type, which is where the finding actually is.

seasonal = (
    points.groupby(["source_type", "dry_season"])["ok"].mean().unstack()
)
seasonal.columns = ["wet", "dry"]
seasonal["swing"] = seasonal["wet"] - seasonal["dry"]
print((seasonal * 100).round(1).sort_values("swing", ascending=False))

points |>
  summarise(functionality = mean(ok), .by = c(source_type, dry)) |>
  tidyr::pivot_wider(names_from = dry, values_from = functionality)

Source type	Wet	Dry	Swing
Protected well	74.7%	36.4%	38.3
Protected spring	84.8%	41.0%	43.8
Handpump borehole	75.0%	76.1%	−1.1
Piped scheme tap	95.9%	98.8%	−2.9

Two source types carry the entire seasonal effect and two do not move at all. Shallow wells and springs draw on a water table that drops; a borehole reaches below it and a piped scheme has a storage tank. The eleven-point average was a mixture of a forty-point effect and no effect.

That is the finding. Boreholes in this network do not have a seasonal problem and wells do, so a dry-season programme that rehabilitates boreholes is rehabilitating the wrong asset.

Classifying each point from its own sequence

def classify(group):
    group = group.sort_values("visit_date")
    failures = group.loc[~group["ok"]]
    if failures.empty:
        return "never failed"
    if not group["ok"].tail(3).any() and len(group) >= 3:
        return "down at year end"
    if failures["dry_season"].all():
        return "seasonal"
    return "intermittent"

shape = points.groupby("water_point_id").apply(classify, include_groups=False)
print(shape.value_counts())

points |>
  arrange(visit_date) |>
  summarise(
    shape = case_when(
      all(ok) ~ "never failed",
      !any(tail(ok, 3)) & n() >= 3 ~ "down at year end",
      all(dry[!ok]) ~ "seasonal",
      TRUE ~ "intermittent"
    ), .by = water_point_id) |>
  count(shape)

Shape	Points
Never failed	82
Intermittent	80
Seasonal	46
Down at year end	34

The order of the tests matters and it is a judgement, not a detail. A point that fails only in dry months and is still down in December satisfies both rules. Testing permanence first calls it abandoned; testing seasonality first calls it seasonal. This code tests permanence first, because a point that has not run for three visits is an operational problem now whatever caused it — and because the December reading is the one a January plan is written against.

Write down which order you used. Two analysts with the same file and different orderings will produce different counts and both will be right.

The status value that looks like the answer and is not

The register has an abandoned status, and it is tempting to use it directly.

ever_abandoned = points.loc[points["functional_status"].eq("abandoned"),
                            "water_point_id"].nunique()
print(f"points ever recorded as abandoned: {ever_abandoned}")
print(f"points down at the end of the year: 34")

points |> filter(functional_status == "abandoned") |>
  summarise(points = n_distinct(water_point_id))

Seventeen points are recorded as abandoned at some visit; thirty-four are still down at the end of the year. The status is an enumerator’s judgement made at one visit, and the sequence is evidence. Half the points that never come back were never labelled abandoned, because the enumerator who saw them in July had no way to know they would still be down in December.

Derive the state from the history where you can, and use the recorded status as a cross-check. A field an enumerator fills in from a single observation cannot carry information that only exists across observations.

Downtime is a management statistic

down = points.loc[~points["ok"] & points["days_since_breakdown"].notna()]
print(down.groupby("management")["days_since_breakdown"].agg(
    median="median", visits="size"
).sort_values("median"))

points |>
  filter(!ok, !is.na(days_since_breakdown)) |>
  summarise(median_days = median(days_since_breakdown), n = n(), .by = management)

Management	Median days down at the visit
Private operator	29
Utility	32
NGO-managed	48
Community committee	68

A broken point under a community committee has been down more than twice as long as one under a private operator. This is the one number in the register that points at an action rather than at an asset — the difference is a spare parts supply chain and a maintenance fund, not the pump.

Beware the thirty-seven rows where a point is recorded as functional and carries a days_since_breakdown anyway: it is last month’s answer carried forward. Filter on the status, not on the presence of the field.

Report the shapes, not just the rate

Water point failure, 2024, 242 points

  Never failed                82    33.9%
  Intermittent                80    33.1%
  Seasonal (dry months only)  46    19.0%
  Down at year end            34    14.0%

  Seasonality is concentrated in protected wells (74.7% wet, 36.4% dry) and
  protected springs (84.8% wet, 41.0% dry). Handpump boreholes and piped
  schemes show no seasonal effect.

  Points classified as down at year end if not working at any of the last
  three visits; seasonal if every recorded failure fell in a dry month.
  Permanence tested before seasonality.

Four counts and the rule that produced them. A single functionality percentage would send a rehabilitation budget to the wrong forty-six points, because they do not need rehabilitating — they need a dry-season alternative, and their pumps are fine.

What comes next

Every number in this lesson is computed over visits that were made. One district missed a third of its rounds in the rainy season, and the points it missed were not a random third — which is the next lesson, and the reason its functionality appears to improve when the roads close.