r/datasets • u/attila_govhouse • 1h ago
resource [self-promotion] SDWIS water-system files: keep the quarter with the PWSID
EPA's SDWIS download is a useful source for public-water-system records, but PWSID alone is not the snapshot key. The field documentation identifies SUBMISSIONYEARQUARTER plus PWSID for the public water system table.
Source and dictionary: https://echo.epa.gov/tools/data-downloads/sdwis-download-summary
If you combine quarters, retain the quarter field. Otherwise the same system can look like a duplicate or overwrite a previous snapshot. When joining the geographic-area table, expect more than one geography row for a system. The mailing ZIP field and the served-ZIP field also describe different things.
A simple validation is to count geography rows per system and quarter before joining them to system-level population. Summing a whole-system population once for every ZIP would count the same people repeatedly. Missing served geography should stay unknown, rather than becoming zero service. These are proposed checks, not a claim that I have audited the full national extract.
I publish GOV.HOUSE. Its ZIP-based lookup offers a more accessible way to find reported systems and their source context: https://gov.house/drinking-water/?utm_source=reddit&utm_medium=social . Reported service areas can be incomplete, so confirm the utility on a water bill. A utility record is not a test of an individual home's tap water.