Scraping 107M rows of data to build this

Ben's Bites published an account of scraping 107 million rows of UK council spending data and building a map-style site to show what each council spent and on what, described as an 'Apple Maps' for the data.

The data is publicly available but not easy to find or format, according to the post. Every English council must publish what it pays out over £500.

Codex spun up three subagents in separate threads to find the information, which is spread across council websites, often in unusual formats, and assembled a catalogue of 31 official sources. It returned real data from five councils, logging each download with a checksum fingerprint. Initial work produced 8 councils, 1.8 million rows and £9 billion of spend in one CSV.

Planning covered roughly 382 councils across the UK, with 339 tracked in England and Wales. At one point 78 councils and 34.35 million rows were collected, but six councils including Lancashire and Norfolk were blocking scrapers. Wales was later dropped because it does not share England's spend-reporting mandate. A data-collection run lasted nearly two days; after 552 threads, 319 of 339 councils were collected or parked.

The build used Parquet and DuckDB, which the author had not previously heard of. Prototypes included a receipt, a bank statement, a county grid, a 'Your county's favourite retailer' mini-site, comparison cards, a Council Ledger, charts and maps. The map version ended up closest to Apple Maps in feel.

Reported stats: 8.2 billion tokens, 235 messages from the author and 656 subagents. Cleaning involved four subagents classifying 2,000 unknown vendors, merging duplicate suppliers and reviewing naming and categories. A £5m threshold filter was found in the static site. The author said final tweaks to the site and data are still under way before open-sourcing.