Pooling Water-Quality Data Across Organizations in the Delaware River Basin

U.S. Geological Survey Fact Sheet 2020–3006, March 2020. By Jennifer C. Murphy and Megan E. Shoda. Integrated Water Availability Assessments Program.

Why pool data

The USGS has launched a pilot Integrated Water Availability Assessment (IWAA) in the Delaware River Basin, to develop ways of assessing how much water is available for people and ecosystems from monitoring data. Water-quality monitoring tells citizens, managers and scientists how healthy streams are and whether water is safe and available for drinking, farming, recreation and more.

Many organisations sample water quality, each at its own sites and frequency for its own needs. Each dataset serves its owner — but pooled into one multisource dataset, they can answer far more, and more kinds of, questions. This fact sheet describes "harmonising" data from many organisations, and a new surface-water-quality dataset for the basin that will support the assessment's analysis and modelling. The same process could be used in other regional basins or nationally.

The Water Quality Portal

The Water Quality Portal (WQP, https://www.waterqualitydata.us/) is a public repository of physical, chemical and biological water-quality data from more than 1.5 million sites, collected by over 400 federal, state, tribal and local organisations. It lets them store and use their own data and makes the data easy for others to find and reuse.

Harmonising the data

Pulling everyone's data from the WQP at once is a big step up from gathering it organisation by organisation — but turning it into a harmonised dataset, quality-controlled and consistently named, still takes considerable time and expertise. The steps:

• choose and combine the right parameter names;
• identify comparable and problematic methods;
• clean text fields;
• convert to common units;
• identify the right fraction (filtered or unfiltered);
• check missing, zero or unrealistic values;
• reconcile remark codes and quality comments;
• resolve duplicates.

The original data are kept. Records are flagged — as duplicates, possible errors, metadata problems, assumptions made for missing or ambiguous information, or unwanted parameters and methods — so users can decide what to leave out.

From retrieval to harmonised dataset: 20 organisations, stream sites only, any period. USGS.

Metadata problems

• Of the more than 1.5 million records retrieved for the basin, about 6 percent had a metadata or data problem — mostly ambiguous units — and were excluded.
• About 7 percent were duplicates: same organisation, site, date, time, parameter and fraction.
• 14 percent were ambiguous but could be resolved with an assumption. A common one concerned the word "total": "total nitrogen" was taken to mean an unfiltered sample of all forms of nitrogen if the organisation reported only "total" and "dissolved" fractions. But if it also reported "unfiltered", or reported only "total", it was unclear whether the sample was filtered, and those records were flagged for exclusion.
• In all, about a third of the records needed an assumption or were unwanted, duplicate or flagged for a problem.

The Delaware River Basin dataset

The USGS retrieved surface-water data for nutrients, salinity (total dissolved solids and specific conductance), major ions, sediment (suspended-sediment concentration and total suspended solids), field measurements (temperature, dissolved oxygen, pH and turbidity) and bacteria (total and fecal coliforms, E. coli and Enterococcus), harmonised it, and matched water-quality sites to nearby USGS streamgages. Everything is in the data release (Shoda and others, 2019).

• Twenty organisations contributed, through the WQP.
• The data run from the early 1900s to the present, covering more than 14,000 days when a sample was taken or a measurement made in a river or stream in the basin.
• State and local organisations supplied about a third of the samples, federal agencies the rest.
• More than 90 percent came from five: the Delaware Department of Natural Resources and Environmental Control, the Delaware River Basin Commission, the New Jersey and Pennsylvania Departments of Environmental Protection, and the USGS.

Recent and long-term sites. Only about 3 percent of sites had recent sampling — monthly or quarterly in at least 2 of the 3 years 2016–18. About 50–60 percent of those also had long-term records: quarterly sampling for at least 80 percent of a decade or more (from 2008–18 back to 1978–2018).

Streamflow. Flow is essential to understanding how water quality varies and changes. Of the nearly 5,000 sites, only about a third matched a USGS streamgage whose drainage area differed by no more than 10 percent. About 40 percent of recently sampled sites and about 50 percent of sites with one to two decades of data have a nearby, recently active (2016–18) streamgage, as do most sites with 30- to 40-year records.

Stream sites meeting the recent (2016–18) and long-term sampling screens, with and without a nearby active USGS streamgage, for four kinds of measurement. USGS.

Sources

• Murphy, J.C., and Shoda, M.E., 2020, Pooling resources across organizations—Multisource water-quality data for the Delaware River Basin: U.S. Geological Survey Fact Sheet 2020–3006. https://doi.org/10.3133/fs20203006 · https://pubs.usgs.gov/publication/fs20203006
• Shoda, M.E., Murphy, J.C., Falcone, J.A., and Duris, J.W., 2019, Multisource surface-water-quality data and U.S. Geological Survey streamgage match for the Delaware River Basin: USGS data release, https://doi.org/10.5066/P9PX8LZO
• The figures and their numbers come from the fact sheet's PDF.
• Rewritten in hubnx's own words.