The King's Lynn projectJoining two tide gauges that keep different clocks
A month of Great Ouse readings, three ways of pairing them, and a test of what each one costs.
The Environment Agency publishes live river and tide readings through an open web service. The learner downloads September 2026 for two gauges on the Great Ouse. The King's Lynn gauge reported every five minutes: 8,533 readings between 00:05 UTC on 1 September and 17:15 UTC on 30 September, with eight short breaks, the longest half an hour. The Freebridge gauge, 1.18 km to the south by the Agency's own coordinates, reported every quarter of an hour: 2,850 readings with a single 45-minute break. Over the month the King's Lynn level ran from -1.805 m to 4.483 m above Ordnance Datum.
Inside the window both gauges cover, King's Lynn has 8,528 rows. A plain join on the timestamp, the way a spreadsheet lookup works, finds a Freebridge reading for 2,841 of them and leaves 5,687 empty, two rows in every three. The fix pandas offers is merge_asof, an as-of join: for each King's Lynn row, take the Freebridge reading that was current at that moment. Two settings decide what "current" means. Direction says whether to look back, forward or to whichever reading is closest. Tolerance says how old a reading may be before it no longer counts.
Pairing 8,528 King's Lynn readings with Freebridge, our pandas run| Method | Rows filled | Left empty | What to watch |
|---|
| Exact timestamp match | 2,841 | 5,687 | Most rows lost |
| As-of, look back, 10 minute limit | 8,522 | 6 | Values up to 10 minutes old |
| As-of, nearest, 10 minute limit | 8,524 | 4 | 2,841 rows use a later reading |
Looking back filled all but six rows; those six sit in the Freebridge break, where the limit stopped the code reaching further. The ages split almost exactly into thirds: 2,841 readings were fresh, 2,841 were five minutes old and 2,840 were ten. Nearest was fresher on average, but for 2,841 rows it reached forward and took a reading from five minutes later. In a historical chart that does no harm. In anything that decides in real time, it uses information nobody had yet.
Self-test: King's Lynn thinned to quarter hours, then refilled and checked against the true 5-minute readings| Refill method | Mean error | Largest error | Off by more than 10 cm |
|---|
| Look back | 8.2 cm | 69.4 cm | 26.2% |
| Nearest | 6.4 cm | 47.7 cm | 16.2% |
| Straight line between readings | 1.0 cm | 28.1 cm | 0.8% |
To measure the price of stale values, the learner hides two readings in every three at King's Lynn, refills them each way and compares with what the gauge actually said. The straight line wins easily, but it needs the next reading before it can draw anything, so it cannot run live. Why do stale values cost so much here? The steepest ten minutes of the month came at 17:00 UTC on 12 September, when the level at King's Lynn rose 0.884 m. The sharpest ten-minute drop all month was 0.238 m, so the fastest rise was more than three and a half times the fastest fall. Of the 1,487 look-back refills that missed by more than 10 cm, 831 came while the water was rising and 656 while it was falling.
Ages 8 to 11
Two friends read a clock at different times; match each note to the latest one before it.
Ages 11 to 15
Load both gauges into Python lists and write the look-back match with a loop.
Ages 15 and up
Use pandas merge_asof, try every direction and tolerance, then run the thinning test.
Sources and licence
Readings are from the Environment Agency flood-monitoring API under the Open Government Licence. The station details, including coordinates, are the Agency's. The joins, the thinning test and every figure above were produced in our own Python run.
