Lesson 4 of 8
Unit · What Sphere asks that a ladder does not
Tested on a third, censored on a fifth
E. coli was tested on 33.4% of households, chlorine on 40.0%, and both on 13.3%. In the water point register, 271 chlorine readings are the string "<0.1" — and dropping them raises apparent compliance by sixteen points.
The denominator changes, and the report usually does not
Water quality is expensive to measure. A survey that interviews 2,403 households tests a fraction of them, and every quality indicator therefore runs on a different denominator from every access indicator.
import pandas as pd
households = pd.read_csv("wash-household-survey-2024.v1.csv")
ecoli = households["ecoli_cfu_100ml"].notna()
chlorine = households["free_residual_chlorine_mgl"].notna()
print(f"E. coli tested: {ecoli.sum():>5} = {ecoli.mean():.1%}")
print(f"chlorine tested: {chlorine.sum():>5} = {chlorine.mean():.1%}")
print(f"both: {(ecoli & chlorine).sum():>5} = {(ecoli & chlorine).mean():.1%}")library(dplyr)
households |> summarise(
ecoli = mean(!is.na(ecoli_cfu_100ml)),
chlorine = mean(!is.na(free_residual_chlorine_mgl)),
both = mean(!is.na(ecoli_cfu_100ml) & !is.na(free_residual_chlorine_mgl))
)802 households for E. coli, 961 for chlorine, and 320 for both. The last number is the one that matters: any question relating the two runs on 13.3% of the survey, and an interval on it will be wide.
Print the analysable n for every quality figure, in the table rather than in a footnote. A quality percentage next to an access percentage on the same row reads as the same denominator, and here it never is.
The JMP risk classes
tested = households[ecoli]
classes = pd.cut(
tested["ecoli_cfu_100ml"],
[-1, 0, 10, 100, 10**9],
labels=["safe <1", "low 1-10", "moderate 11-100", "high >100"],
)
print(classes.value_counts(normalize=True).round(3))households |>
filter(!is.na(ecoli_cfu_100ml)) |>
mutate(risk = cut(ecoli_cfu_100ml, c(-1, 0, 10, 100, Inf),
labels = c("safe", "low", "moderate", "high"))) |>
count(risk) |> mutate(share = n / sum(n))| Risk class | Households | Share of tested |
|---|---|---|
| Safe (<1 CFU/100 mL) | 430 | 53.6% |
| Low (1–10) | 173 | 21.6% |
| Moderate (11–100) | 139 | 17.3% |
| High (>100) | 60 | 7.5% |
Report the classes, not a mean. E. coli counts are heavily skewed and a mean of 14 CFU/100 mL describes nothing — the classes are what the guideline is written in and what a decision is taken on.
Improved is not safe, and this is where you can prove it
IMPROVED = {"piped-into-dwelling", "piped-into-yard", "public-tap",
"borehole", "protected-well", "protected-spring"}
tested = tested.assign(improved=tested["water_source"].isin(IMPROVED))
print(tested.groupby("improved")["ecoli_cfu_100ml"].agg(
n="size", safe=lambda s: (s < 1).mean(), high=lambda s: (s > 100).mean()
).round(3))households |>
filter(!is.na(ecoli_cfu_100ml)) |>
mutate(improved = water_source %in% improved) |>
summarise(n = n(), safe = mean(ecoli_cfu_100ml < 1),
high = mean(ecoli_cfu_100ml > 100), .by = improved)| Source | Tested | Safe | High risk |
|---|---|---|---|
| Improved | 625 | 61.1% | 3.8% |
| Unimproved or surface | 177 | 27.1% | 20.3% |
Source type predicts quality strongly, and 38.9% of households on an improved source still have detectable E. coli. Lesson 1 said the top rung needs freedom from contamination as well as an improved source; this is the number that shows why it is a separate condition rather than a formality.
The censored value, and why it is not missing
The water point register measures chlorine in the field, and the kit has a detection limit.
points = pd.read_csv("water-point-monitoring-2024.v1.csv")
readings = points["free_residual_chlorine_mgl"].dropna()
censored = readings.astype(str).str.startswith("<")
print(f"tested {len(readings)}, censored {censored.sum()}")
print(readings[censored].unique())points |>
filter(!is.na(free_residual_chlorine_mgl)) |>
count(censored = startsWith(free_residual_chlorine_mgl, "<"))811 visits carry a reading and 271 of them are the string <0.1. That is a
measurement: the true value lies between zero and the detection limit. It is not
a missing value, and the two things it is usually turned into are both wrong.
numeric = pd.to_numeric(readings, errors="coerce") # <-- the silent one
print(f"after coercion: {numeric.isna().sum()} became NaN")
in_range = readings[~censored].astype(float).between(0.2, 0.5)
print(f"in target range, over all tested: {in_range.sum() / len(readings):.1%}")
print(f"in target range, dropping censored: {in_range.mean():.1%}")points |>
filter(!is.na(free_residual_chlorine_mgl),
!startsWith(free_residual_chlorine_mgl, "<")) |>
summarise(in_range = mean(between(as.numeric(free_residual_chlorine_mgl), 0.2, 0.5)))30.9% against 46.5%. Dropping the censored readings raises apparent compliance with the chlorination target by nearly sixteen points, and it does it in exactly one direction — every censored value is a failure, so removing them removes only failures.
pd.to_numeric(..., errors="coerce") does this silently and looks like a
type fix.
What to do with a censored value
Three options, in order of preference for this indicator.
Classify rather than average. The target is a range, so a censored reading
answers the question perfectly: <0.1 is below 0.2 and therefore
non-compliant. Compliance is computable on all 811 readings with no
substitution at all.
Substitute the limit, or half of it, if you genuinely need a mean. State
which you used — LOD/2 is the common convention — and report how many values
it applied to.
Report the share below the limit as its own number. 33.4% of these readings are below detection, which is a finding about chlorination practice rather than a nuisance in the data.
compliant = pd.to_numeric(readings.where(~censored), errors="coerce").between(0.2, 0.5)
result = pd.DataFrame({
"readings": [len(readings)],
"below detection": [censored.sum()],
"in 0.2-0.5 range": [compliant.sum()],
"compliance": [f"{compliant.sum() / len(readings):.1%}"],
})
print(result)# The denominator is every reading, including the ones below the limit.Never let a detection limit shrink a denominator. It is the single most common way a water quality report becomes optimistic, and it survives review because the arithmetic on the remaining values is correct.
The cross-field inconsistency worth naming
treats = households["water_treated_at_home"]
print(f"treat at home: {treats.sum()}")
print(f" of which no chlorine reading: {(treats & ~chlorine).sum()}")households |> filter(water_treated_at_home) |>
count(no_reading = is.na(free_residual_chlorine_mgl))533 households report treating their water and have no chlorine measurement. The cleaning course established what this is — missingness that correlates with the thing being measured — and the consequence here is specific: dropping those rows removes households that treat their water more often than average, so the tested subsample is not representative of the survey it came from.
Report it as its own block
Water quality
E. coli, point of collection 802 households tested (33.4% of survey)
Safe <1 CFU/100 mL 53.6%
Low 1-10 21.6%
Moderate 11-100 17.3%
High >100 7.5%
Improved sources: 61.1% safe (n=625)
Unimproved and surface: 27.1% safe (n=177)
Free residual chlorine, water points 811 visits tested (30.8% of visits)
Below detection limit (<0.1 mg/L) 33.4% counted as non-compliant
Within 0.2-0.5 mg/L target 30.9%
Quality was tested on a subsample and does not share the denominator of the
access indicators above. 533 households reporting home treatment have no
chlorine reading, so the tested subsample over-represents untreated water.Its own block, its own denominators, its own limitation. A quality figure placed in a table of access figures will be read against their denominator, and nothing in the layout will stop it.
What comes next
Everything so far is a cross-section: what was true on the day someone called. The next unit is the register that visits the same water points twelve times, and the first thing it does is turn one functionality rate into three.