Thames Water rainfall stress metric
By Isaac Kirk, in relation to #30 and the website chart work in #32.
Notebook at notebooks/Thames-Water/thames-water-rainfall-metric.ipynb. This follows on from the discharge findings doc (docs/thames-water-discharge-findings.md), which ended with one question the descriptive charts couldn't answer: did Thames Water actually spill less in 2025, or did 2025 just have less rain? This doc is my answer, and it turns out to be more nuanced than a single number can capture.
What the metric is
For each monitor and each day, I sum the rainfall that monitor received over the past N days. I do that for four candidate windows (1, 3, 7 and 14 days) and pick the one that best predicts whether a spill started that day. The reason I'm going wider than a single day is that overflows aren't driven only by today's rain. Saturated ground from the previous week, a string of moderate days that gradually fills the sewer, one final heavy day pushing it over the edge: any combination of those can stress the system, and a one-day figure throws all of that history away.
The dataset for the test is around 1.1 million monitor-days across 2021 to 2026 (572 monitors with daily readings, minus the first 13 days where the 14-day lookback would be incomplete). About 3.33% of those monitor-days had a spill. Building the rolling windows uses SQL self-joins with BETWEEN, which is the same range pattern the week 11 lecture covers.
Are spill days actually wetter
Yes, at every window. Each panel compares the same pair, quiet days on the left and spill days on the right, and the spill day boxes sit noticeably higher across all four windows. That confirms the rainfall-stress framing isn't crazy and the rest of the analysis is worth doing. The next question is which window separates them most cleanly.
The stress curve
Each curve shows, for a given amount of rainfall, the probability that a monitor-day ended in a spill. Every curve starts near zero on dry days and climbs as rainfall increases. The 1-day and 3-day curves rise most steeply and reach the highest probabilities at extreme rainfall, around 50% for the 1-day window above 60mm and over 60% for the 3-day window above 100mm. The 7-day curve climbs more gradually to about 25% before dropping back at very high rainfall (where the data thins out and the few observations happened not to spill). The 14-day curve rises most gently and tops out around 14%.
For an analytical chart I think the 7-day curve is the most readable, because it spans a sensible mm range and rises through it smoothly. At 25mm of rain over a week the probability is around 5.6%, at 40mm it's 8.6%, at 62mm it's 12.4%. The shape of every curve tells the same story though: rainfall predicts spills, the relationship is non-linear, and there's a threshold in each window past which a spill is meaningfully likely.
Picking the best window
To pick the recommended window with a number rather than by eye, I used a robust effect size: the gap between the spill-day median and the quiet-day median, scaled by the IQR of quiet days. The week 9 lecture was firm that for skewed data like rainfall, median and IQR are the right pair, because mean and standard deviation get skewed by outliers but the IQR doesn't. The numbers from section 6:
| Window | Spill day median (mm) | Quiet day median (mm) | Quiet day IQR (mm) | Effect size |
|---|---|---|---|---|
| 1 day | 5.2 | 0.3 | 2.2 | 2.23 |
| 3 days | 13.4 | 3.0 | 8.9 | 1.17 |
| 7 days | 26.3 | 11.1 | 19.4 | 0.78 |
| 14 days | 47.0 | 26.1 | 30.4 | 0.69 |
The 1-day window wins on this test, which surprised me at first because I'd assumed the longer windows would do better by capturing more context. The intuition is that a spill day in the dataset is, on average, a heavy-rain day, and the longer windows pick up a lot of moderate rain that hasn't yet pushed the system past capacity, which is extra noise that drags their effect size down. The 7-day window still has real predictive power and is the better story chart because the threshold is more visible in it, but for raw discrimination the 1-day window is sharper.
Did rainfall explain the 2025 drop, the aggregate test
For each year, the blue bar is the share of monitor-days that had a spill, and the orange bar is the share of monitor-days where rainfall over the chosen window crossed the threshold where spill probability starts to climb sharply (12.5mm in a single day).
Two years on the chart aren't directly comparable to the others. 2021 has a 0% spill rate by construction, because the discharge data doesn't begin until April 2022. 2026 only covers January to April, and from the monthly chart in the discharge findings doc those months are the seasonal peak for spills, so the rate is inflated against a four-month denominator. The comparable window is 2023 to 2025.
Inside that window the aggregate story is mostly consistent with rainfall driving the change. 2024 had both the highest spill rate (6.57%) and the highest stress rate (4.76%) of the three years. 2025 dropped on both, to 3.27% and 2.90%. The stress rate fell by about 40% from 2024 to 2025 and the spill rate fell by about 50%. The two drops aren't identical but they're in the same ballpark.
Read at this level of aggregation, the 2025 drop looks mostly like a weather story with a small operational improvement on top. Reading section 7 alone I would have said the answer was "weather plus a bit of improvement". The next section qualifies that finding hard.
Where the simple aggregate hides the operational picture
This is the harder finding, and it's the one I wasn't expecting. A spill that starts after a dry stretch can't be defended on weather grounds, because there's nothing rainfall-wise to push the system past capacity at the moment the spill starts. So the share of spills that begin after a dry stretch is, in some sense, the cleanest test of "how much is the network spilling for reasons that aren't the weather".
I use two definitions of dry stretch in parallel: cumulative rainfall under 5mm over the previous three days (strict), and under 10mm over the previous seven days (stricter). Both should move in the same direction if the finding is real.
The share rose in 2025 under both definitions:
| Year | Events | Dry 3-day event share | Dry 3-day hour share | Dry 7-day event share | Dry 7-day hour share |
|---|---|---|---|---|---|
| 2023 | 21,024 | 20.1% | 11.1% | 12.2% | 5.0% |
| 2024 | 29,861 | 21.2% | 16.4% | 9.2% | 6.7% |
| 2025 | 13,464 | 27.5% | 18.6% | 18.4% | 13.3% |
Under the 3-day definition the share jumped from 21% in 2024 to 28% in 2025, an increase of about a third. Under the 7-day definition it almost doubled, from 9% in 2024 to 18% in 2025. The duration share rose more gently than the count share, which tells me dry-stretch spills tend to be shorter on average than wet-stretch spills, but the direction is the same across both definitions and both metrics.
The reason the share rose while the absolute count of spills fell is that the operational floor didn't move with the weather. Dry-stretch spilling isn't weather-driven by construction, so a drier year doesn't reduce it. The wet-day spills fell hard in 2025 because there were fewer wet days, but the dry-stretch spills stayed roughly where they were, which mechanically pushed their share up.
So the aggregate "spill rate fell faster than stress rate" finding from the previous section, while true, is misleading on its own. The two findings read together say that total spills are way down, but Thames Water hasn't reduced the kind of spilling that has no rainfall to blame.
Cumulative rainfall vs spill counts and duration
This chart pairs the relationship the dry-stretch analysis hinted at with the absolute numbers. I bucket spills by their cumulative rainfall over the previous 3 days (one line) and the previous 7 days (the other), then total up two things per bucket: number of events on the left panel, total spill duration in hours on the right panel.
Both panels show the same hump shape. Spill counts rise as rainfall increases out of the dry range, peak somewhere in the moderate-rainfall band, then fall away in the heavy and intense bands. The peak isn't at the highest rainfall, because high-rainfall days are rare in the dataset even though the per-day probability of a spill is highest there. The chart in section 5 showed that probability climb, and this chart shows what it adds up to in absolute terms.
Two practical readings come out of this. First, the 7-day window produces a smoother curve and reads better as a public chart. The 3-day window is sharper and peaks earlier, which is more useful for an analytical reading but harder to caption for the website. Second, the duration panel has roughly the same shape as the count panel but the right tail is heavier, which means individual spills tend to last longer when the rainfall context is heavier. The two metrics agree on the basic shape but disagree on where the system's worst total hours sit.
Where geographically does this happen
The geographic map plots every Thames Water monitor at its latitude and longitude. Dot size = total number of days the monitor produced at least one spill across the 2021 to 2026 window. Dot colour = the rainfall residual, which is the gap between actual spill days and what would be expected given the rainfall the monitor received.
Two numbers stand out from this section. The network-wide ratio of spill days to high-rainfall days is 0.977. That means across all of Thames Water, on virtually every day when rainfall crosses the stress threshold the network does spill somewhere. The system is at saturation: when stressed, it fails. Of the 581 monitors with at least a year of coverage, 213 are over-spilling for their rainfall exposure and 368 are under-spilling. The over-spillers aren't evenly distributed either. Distinct clusters of dark-red dots stand out around Bedfordshire (upper part of the map, around latitude 52.1) and west Berkshire (lower west of the map, around latitude 51.4 west of Swindon), which would be a starting shortlist for any site-level follow-up.
What this map can't do is explain why those clusters are there. It's a correlational map, not a causal one. But it surfaces concrete locations the team can talk about, which the aggregate findings in earlier sections can't.
What this notebook can't tell you
The aggregate threshold of 12.5mm hides site-to-site variation, so a per-site refit would catch sensitive monitors that this analysis misses. The rainfall data is on a daily grid, which means a sub-daily intense burst is invisible to me. The geographic map is correlational and says nothing about why the over-spilling clusters exist. The metric work has been done on Thames Water only, and the regression in #22 extends to the broader 155-site sample and may pick a different window as best.
Output for the regression and the website
The notebook writes a rainfall_stress table back to the database, keyed on permitNumber, locationName and date, holding all four window values plus the recommended one duplicated under the stable column name rainfall_stress. That way the regression in #22 can join on the discharge keys and use whichever window suits without me having to rebuild anything. Around 1.1 million rows, schema mirrors rainfall_data.
The notebook also writes three JSON files into website/data/ for the website charts brief in #32:
chart_a_rainfall_vs_spills.jsoncovers Graph A in #32: cumulative rainfall vs spill counts and duration, both 3-day and 7-day windows.chart_e_dry_stretch_trend.jsoncovers Chart E in #32: dry-stretch share of spills over time, both 3-day and 7-day definitions.chart_geographic_residuals.jsoncovers the geographic map, which was beyond the original brief but I kept it in for an editorial close.
Graphs B, C and D in #32 aren't written here because they need the regression in #22.
Website candidates
For the three-visualisation budget, the strongest combination from this notebook plus the discharge analysis depends on what tone the website wants.
The cleanest narrative trio is the discharge annual totals (set the scene), the cumulative rainfall vs spills chart from section 9 (show the rainfall relationship), and the dry-stretch trend from section 8 (show what rainfall doesn't explain). That arc lands as "spills happened, rainfall is the obvious driver, but a meaningful share has no rainfall excuse, and that share is rising".
A more editorial trio is the discharge annual totals, the dry-stretch trend, and the geographic map. That arc lands as "spills dropped a lot, but the dry-day floor didn't fall, and here is where the worst over-spilling clusters are". This loses the rainfall relationship as an explicit chart but gains a concrete location-based piece for the close.
Either way the underlying story is the same: spills fell sharply in 2025 because the weather was drier, but Thames Water didn't actually clean up their act in any way that would survive a different summer.