The column that was not a number

The quarterly WASH report says 46.5% of tested water points met the free residual chlorine target of 0.2 to 0.5 mg/L. Your predecessor’s script is one line longer than yours and produces a different answer.

Theirs converted the column to a number first.

The file

water-point-monitoring-2024.v1.csv — 2,629 monitoring visits to 242 water points. 811 of them carry a chlorine reading and 271 of those are recorded as the string <0.1, the field kit’s detection limit. Synthetic.

The task

Part one. Reproduce both numbers. Show the line that produces 46.5% and the line that produces 30.9%, and state in one sentence what each is the compliance rate of.

Part two. Say which is correct and why. Your answer has to address what a <0.1 reading is a measurement of, and why the direction of the error is not a coincidence — every censored value fails the target, so removing them can only move the figure one way.

Part three. Produce the compliance table you would publish: readings taken, readings below detection, readings in the target range, readings above it, and the compliance rate on the full denominator. Break it down by source_type. One type holds 200 of the 251 in-range readings and only 28 of the 271 censored ones; the other four are the reverse, and the reason is not a data problem.

Part four. Write the two sentences that go under the table — one stating the denominator and the treatment of censored values, one stating what a reader must not conclude.

What you will need to handle

Three things, and only the first is obvious.

• as.numeric("<0.1") returns NA with a warning. In pandas, pd.to_numeric(..., errors="coerce") does the same with no warning at all. Either way, 271 measurements become missing values and the denominator shrinks.
• The target is a range, not a minimum. A reading of 0.9 mg/L fails it as surely as one below 0.1, for a different reason, and lumping “above target” in with “compliant” is the mirror image of the error you are correcting.
• Testing is not spread evenly. 323 of 390 piped-tap visits carry a reading against 300 of 1,421 handpump visits, so the tested subsample is not the network. Say what that does to a network-wide compliance figure.

Three reference points

811 readings, 271 below the detection limit, 251 inside the 0.2 to 0.5 mg/L range. If your two headline numbers are not approximately 30.9% and 46.5%, check whether your range test is inclusive at both ends before going further.

The questions to answer in prose

Three sentences each.

1. A colleague proposes substituting half the detection limit — 0.05 mg/L — for every censored reading, so the column can be averaged. Say when that is a reasonable convention and why it is unnecessary for this particular indicator.

2. 243 of the 271 censored readings are on sources that are not chlorinated at all. Explain what that does to a compliance figure computed across every source type, say what the indicator is actually measuring on a handpump, and give the denominator you would report it on instead.

3. Your predecessor’s script was correct arithmetic on the rows it had. Say what kind of review would have caught this, and why a code review of the calculation would not have.

What to hand in

An R script producing the compliance table by source type, both headline figures side by side with their denominators, and the two sentences as a comment at the foot of the file. Tables printed and written to outputs/.

How to know you are done

Your denominator is 811 in every row that reports compliance, and the count of censored readings appears in the table rather than in a comment. Someone reading only the table can reconstruct both your figure and your predecessor’s.