Regional drinking water quality monitoring program: data publishing workflow

Arizona Statue University (ASU) has been working with regional water providers (Salt River Project (SRP), Central Arizona Project (CAP)) and metropolitan Phoenix cities since 1998 on algae-related issues affecting drinking water supplies, treatment, and distribution. The results have improved the understanding of taste and odor (T&O) occurrence, control, and treatment, improved the understanding of dissolved organic and algae dynamics, and initiated a forum to discuss and address regional water quality issues. The monitoring benefits local Water Treatment Plants (WTPs) by optimizing ongoing operations (i.e., reducing operating costs), improving the quality of municipal water for consumers, facilitating long-term water quality planning, and providing information on potentially future-regulated compounds. ASU has been monitoring water quality in terminal reservoirs (Lake Pleasant, Saguaro Lake, and Bartlett Lake) continuously from 1998 to the present for algae-related constituents (taste and odors, and more recently cyanotoxins), nutrients, and disinfection by-product precursors (i.e., total and dissolved organic carbon and organic nitrogen). Additional monitoring has been conducted in the SRP and CAP canal systems and in water treatment plants in Phoenix, Tempe and Peoria. During this work the Valley has been in a prolonged drought and recently one above average wet year, and this data provides important baseline data for development of new or expanded WTPs and management of existing WTPs in the future. The current work has improved the understanding of T&O sources and treatment, but additional research and monitoring into the future is necessary.

Reservoir monitoring is conducted once per month at Bartlett Lake, Saguaro Lake, and Lake Pleasant, and quarterly at Roosevelt, Apache, and Canyon Lakes. Samples are depth integrated in the epilimnion and hypolimnion. CAP will collect samples from Lake Pleasant. SRP will collect samples from Bartlett, Saguaro, Roosevelt, Apache, and Canyon Lakes (at no cost to ASU or cities). Field measurements for temperature with depth will also be collected. River samples (Salt River below Saguaro Lake @ Blue Point Bridge and Verde River at the Beeline Highway) will be collected once per month. Samples will be analyzed for carbon (TOC/DOC), total nitrogen, total phosphorous, arsenic, conductance and T&O compounds (MIB, Geosmin, Cyclocitrol). The purpose of the lake sampling is to provide early warning information on potentially large changes in water quality – due to algae production, lake destratification, and forest fire or other runoff events. Additional monthly sampling will be coordinated with USGS (Salt River above Roosevelt, Verde River at Tangle) and CAP (Lake Havasu).

Canal monitoring is conducted once per month (January through June) and twice per month as needed during periods of higher T&O production (i.e., July-December). Field measurements for temperature and pH will be made. Sampling will include the CAP, Arizona, and South canals at multiple locations. Monthly samples will be analyzed for carbon (TOC/DOC), total nitrogen, arsenic, conductance, and T&O compounds (MIB, Geosmin, Cyclocitrol). Bi-weekly samples will be analyzed only for T&O compounds. The purpose of the canal sampling is to identify hot-spots of T&O production and to make recommendations to the cities/SRP/CAP to perform some type of treatment (brushing, copper, etc.). Additional canal sampling will be scheduled to further identify canal hot spots or to provide more frequent process control information.

WTP raw and finished water is collected once per month (January through June) and twice per month as needed during periods of higher T&O production (e.g., July-December). WTP sampling will be conducted at two Tempe WTPs, one Peoria WTP, Glendale WTPs and other selected WTPs. Monthly samples will be analyzed for carbon (TOC/DOC), total nitrogen, arsenic, conductance, and T&O compounds (MIB, Geosmin, Cyclocitrol). Bi-weekly samples will be analyzed only for T&O compounds. The purpose of the WTP sampling is to provide continued evaluation of water quality produced at the WTPs.

Source data reside in an Access database. Use mdbtools to extract tables as csv files:

• sudo apt install mdbtools
• export all tables: mdb-tables -d ',' ASU\ Regional\ Water\ Quality\ database.mdb | xargs -L1 -d ',' -I{} bash -c 'mdb-export ASU\ Regional\ Water\ Quality\ database.mdb "$1" > "$1".csv' -- {}
• note that the above command does result in the error Error: Table does not exist in database but all relevant tables are exported.

• data refresh
• workflow converted from Rmd to qmd
• units updated from the EML standard unit library to QUDT units (e.g., milligramsPerLiter to MilliGM-PER-L); coloniesPer100milliliter and literPerMilligramMeter remain custom units owing to the absence of QUDT equivalents
• fix the unit of altitude (ft) in sampling_locations that was listed as meter but is in fact feet
• carbon numeric values rounded to the resolution of the measurement (TOC, DOC: 3 decimals; UVA: 4; SUVA, fluorescence intensities, FI: 2) to remove floating-point artifacts in the source data
• new attribute Turbidity (NTU) in field_measurements
• microbial: site acronym ISTB3 recoded to ISTB4; ISTB3 appears only on the ISTB4 cold-tap records (2024-06-23 through 2025-03-13) whose comments read "ISTB4-Tap-Cold"
• microbial: the source data include a Legionella (colonies per 100mL) column (2024-01-07 through 2025-03-13) that has not been part of this data package; it is not clear why Legionella was excluded and it continues to be omitted pending clarification
• site LPP (cluster cap, sampled since 2023) is described inconsistently in the source site location field; the code is documented consistently across entities as "CAP canal at Lake Pleasant Parkway near Loop 303"
• definitions added for factor levels new to this version (e.g., LPP, 24th Street WTP, ISTB4 hot and cold taps, Roosevelt Lake at 55m depth) and for levels undocumented in v6; the mycobacterium_flag code NR remains undefined (metadata_not_provided)
• custom units documented in custom_units.yaml (the unitList in the workflow is removed)

• data refresh
• expanded .gitignore to ignore files within data directory

• data refresh
• update attributes metadata files to yaml format
• update config.yaml to reflect updates to capeml
• pared repository of old data (and adding data directory to ignore)

• data refresh
• fix units of total phosphorus and total dissolved phosphorus that were listed as nanograms per liter but in fact are micrograms per liter