Water Quality Data Harmonization Pipeline

Water Quality
R
ETL
environmental science
Multi-lab data arrive with mixed units and naming. I built a reproducible R pipeline that standardizes them. It reflects a pipeline pattern I’ve applied across multiple projects.
Author

Ian Gault

Published

January 1, 2025

View Vignette View on GitHub

About the data

This project uses synthetic data. The workflow reflects practices commonly used across many water quality monitoring programs of this type.

Environmental monitoring programs often aggregate data from multiple laboratories, each with its own submission conventions — analyte names, units, detection limit notation, and quality flags rarely align out of the box. This pipeline addresses that problem end-to-end.

What it does

The pipeline ingests raw multi-lab water quality submissions and produces a clean, standardized dataset ready for analysis or reporting. Key steps include:

  • Analyte name standardization — resolves lab-specific submission codes to a common naming convention
  • Fraction parsing — extracts dissolved/total fractionation from analyte suffixes
  • Classification — groups analytes into analysis categories (Nutrients, Major Ions, Metals, etc.)
  • Water type detection — distinguishes freshwater from seawater samples to apply appropriate thresholds
  • Unit conversion — converts mixed units with correct scaling of associated detection limits
  • Detection limit handling — flags and substitutes values below detection using consistent conventions
  • QAQC checks — automated screening for field blanks, field duplicates, holding time exceedances, and metals ratio verification

Design

The pipeline is organized into modular R functions (harmonize.R, units.R, qaqc.R, database.R, utils.R) and documented via an R Markdown vignette. The vignette runs standalone on synthetic test data, with generic program and site labels.