The district that looked best

A donor report is due and it wants a district ranking on water point functionality. The register has twelve monthly rounds and looks complete.

Two hundred and seventy-five of its scheduled visits were never made, it does not contain a row for any of them, and the district with the fewest visits comes out top.

The file

water-point-monitoring-2024.v1.csv — 2,629 monitoring visits to 242 water points across three districts, monthly through 2024, with status, yield, chlorine and the number of people each point serves. Synthetic.

Set up first

A project directory, the file read-only, an outputs/ directory, and a script that runs top to bottom from a clean kernel.

Part one: three rates, three denominators

For the whole register and for each district, compute:

• visit-level functionality — visits finding a working point over visits made;
• point-level functionality — points working at every visit they received, over points;
• population-weighted functionality — users served by a working point over users served, summed across visits.

Print them in one table with every denominator stated. Decide whether partially-functional counts as working, write the decision as a comment at the head of the script, and use it consistently.

Two things to handle before the arithmetic. Two points were re-registered after a handover and appear twice under different identifiers; find them and say what you did. Four visits have no users_estimated, which affects exactly one of the three rates.

Part two: the denominator nobody chose

The register has 242 points and twelve scheduled rounds, so 2,904 visits were due and 2,629 were made.

Compute coverage — visits made over visits due — for each district and each month. Then bound each district’s functionality:

• the observed rate, which assumes the missed visits look like the made ones;
• the lower bound, which assumes every missed visit would have found a failure.

Produce a table with observed, lower bound and the width of the band. Then look at the ranking under each assumption, and note what happens to it.

Part three: was the missingness random?

You cannot narrow the band without arguing that the missed visits resemble the made ones. Test it.

For every visit that was followed by a missed round, and every visit that was followed by a made one, compute the share that found a broken point. Report both with their n.

The difference is small. Say in three sentences what it does and does not let you conclude, and whether it justifies narrowing the bounds.

Part four: the paragraph

Write the district paragraph for the donor report. Six sentences at most, containing:

• the three rates for the whole programme, each with its denominator;
• coverage, by district;
• the range for the least-covered district;
• one sentence declining to rank the districts, and why;
• one sentence saying what would make a ranking possible.

Check your numbers

	Expected
Visit-level functionality, whole register	about 74%
Point-level functionality	about 34%
Population-weighted functionality	about 83%
Overall coverage	about 90%
Coverage, worst district	about 83%
Widest band	about 13 points

If your population-weighted figure is below the visit-level one, the weights went on the wrong side of the division — the piped schemes serve the most people and break the least, so weighting must move it up.

The questions to answer in prose

Three sentences each.

1. Your three functionality rates differ by nearly fifty points between the highest and the lowest. Explain to a programme manager why all three are correct, and which one you would put in a Sphere-referenced report.

2. Last-observation-carried-forward would fill every missed round and produce a number almost identical to the observed rate. Say why that is not reassuring, and what assumption it smuggles in.

3. The district with the worst coverage is also the hardest to reach in the rains. Explain why that makes dropping it from the report worse than including it with a wide band, and what the coverage figure tells a programme to change.

What to hand in

A Python script or notebook producing:

• the three-rate table with every denominator and n stated
• the coverage table by district and by month
• the bounds table with the band width, and the ranking under each assumption
• the missingness test with both shares and their n
• the six-sentence paragraph as a markdown cell or docstring

How to know you are done

Delete outputs/, restart the kernel, run once, and every table regenerates identically. Changing your partially-functional decision at the top of the script moves all three rates and nothing else — if a coverage figure moves, a status filter has leaked into a denominator that should only count visits.

What this lab is not

It is not an evaluation of the water programme. Whether the points were the right points, whether the communities were consulted, and whether the maintenance model is viable are all questions this register cannot answer. This lab is about the denominator: three defensible rates from one file, and the discipline of saying what the visits nobody made could have done to any of them.