How we built a source-checked dataset of 160 US hard water and water quality statistics

#opendata #python #datacleaning #dataset

Water System Guide (https://watersystemguide.com/) publishes a page of US hard water and drinking water statistics. We also released the numbers as an open dataset: 160 rows, one statistic per row, CC BY 4.0 for the compilation (DOI 10.5281/zenodo.23534609). This post covers how it was built and what it can't tell you.

The problem with water statistics

Many water figures get repeated from blog to blog with no traceable origin. "85% of US homes have hard water" is the best-known one. We could not trace it to a primary source, so it is not in the dataset. The rule was simple: if a figure can't be found at the body that produced it, it stays out.

Sources

Every row cites the organization that produced the figure: federal agencies (EPA, USGS, CDC), state agencies, utilities, universities, WHO/UNICEF, original survey publishers (Gallup, WQA) and the foundation that commissioned a study (WQRF/Battelle). No roundup blogs or vendor pages.

The 160 rows come from 64 source URLs and 39 publishers. By source type:

source_type	rows
us_federal_agency	91
us_local_government_or_utility	25
us_state_agency	15
industry_association_or_foundation	10
intergovernmental_organization	10
survey_publisher	6
university	3

They cover 12 topics, from chloride regulation (22 rows) and public water systems (20) to global access (8).

Verification

On 2026-10-11 each of the 64 source URLs was fetched. HTML was converted to text and PDFs with pdftotext -layout; spreadsheet values were read from the original file. Each statistic's exact quote or table value was then matched against the fetched text, with whitespace normalized. Anything that didn't match was rewritten to what the source says, or dropped. The final pass reported 0 mismatches, and every URL returned HTTP 200 at build time. One CDC page blocks automated requests, so it was read with a browser-style reader, and those rows say so.

Cleaning choices

Dated, not blended. Each row keeps its own source date. Revised series stay as separate rows: EPA's lead service line estimate appears as 9.2M (2023), 9M (2024) and 4M (2025).
No machine-split numbers. Each statistic is a full sentence. Splitting it into value and unit columns automatically wasn't reliable enough to publish, so the sentence is the authoritative value.
License per row. A source_license_note column separates US federal figures (91 rows, public domain), state/local government figures (40 rows, facts cited with credit) and figures from copyrighted sources (29 rows, referenced with credit).
Caveats in the data. 29 rows carry review_flags, for example a source with no publication date, or an industry-affiliated source.

A quick look with the standard library:

import csv, collections
rows = list(csv.DictReader(open("data.csv", encoding="utf-8")))
print(len(rows))  # 160
print(collections.Counter(r["topic"] for r in rows).most_common(3))

Limits

It is a compilation of published figures, not new measurements or a statistical sample.
Rows differ in year, method, unit and geography (102 rows are US-wide, 10 global, the rest state or local). Read source_date and the source before comparing two rows.
Source pages can change after the 2026-10-11 retrieval.

Where to get it

Companion page: https://watersystemguide.com/guides/hard-water-statistics/
Dataset (Zenodo): https://doi.org/10.5281/zenodo.23534609
Kaggle: https://www.kaggle.com/datasets/watersystemguide/us-hard-water-and-water-quality-statistics-2026

If you use a single figure, please cite the original publisher in that row too.

Disclosure: this post is published by Water System Guide. It was drafted with AI assistance and reviewed by the site owner. All figures come from the dataset files and its documentation.

DEV Community