A tool to aid in the transition from the legacy NWIS discrete data format to the WQX format

This blog describes a mapping file available to users to ease the transition to the WQX version 3.0 format. The spreadsheet provides high-level mapping of data fields in the legacy NWIS format to WQX fields.

Overview

Recently, the USGS modernized the format of its discrete water quality and sample data from a USGS-specific output format to the internationally recognized Water Quality Exchange (WQX) standard (for more background, read this blog post). Over many years, users have come to rely on USGS data from the NWIS qw webpage in a particular format for ongoing reporting and data analysis. Making the switch to a new format can be challenging, and we want to support users during this transition period. This blog post describes a resource (spreadsheet ) to help users access and use USGS discrete data in their existing workflows as they transition from legacy to modernized services.

The spreadsheet is available in MS Excel file format and maps NWIS data output fields from the legacy qw format to the WQX v3.0 tabular (such as .csv, .txt or .xlsx file formats) field names. This table is intended to be used as a guide to translate existing data processing or analysis workflows: it is not a one-to-one mapping that users could plug into an automated workflow and translate between legacy and current output formats. This is because, while some of the legacy NWIS qw fields directly translate to a single WQX field, many do not map one-to-one, and some of the mappings are somewhat complex.

Here is a simple example: for the WQX field ‘Location_Identifier’, the field is populated by combining two legacy NWIS fields: agency_cd and site_no.

While this is a fairly straightforward example that could be automated, there are other, more complicated mappings. Take, for example, mapping legacy NWIS qw remark_cd. This field maps to multiple WQX fields including Result_ResultDetectionCondition, Result_MeasureValueType and DetectionLimit_TypeA. These are conditional fields and are populated based on the specific information in remark_cd. For instance, censored data are populated in the Result_ResultDetectionCondition and DetectionLimit_TypeA fields, while non-censored data populate the WQX fields Result_MeasureValueType and Result_StatisticalBase.

Considerations related to data profiles

There are two important things to consider about how the WQX v3.0 fields appear in a data download. Users download the data as a subset of the full WQX v3.0 metadata set called a data profile. There are multiple different profiles that serve a subset of data relevant to a particular use case. The first point is that not all data fields appear in every data profile, and that is by design so as to avoid overwhelming users with 340+ columns! Second, there are differences in a few field names depending on the data profile being downloaded. The diagram below illustrates the differences in field names related to the Detection Limit fields for different profiles.

How users should approach using the mapping spreadsheet

The goal of this mapping file is to simplify the process of transitioning to the new WQX v3.0 format. The file will be maintained for approximately six (6) months after legacy qw format is decommissioned, after which time users’ transitions will be complete and this resource will be retired.

We recognize that this is a significant change in how the data are structured and that users may still have questions even with this reference. We are available to answer questions, so please do not hesitate to reach out.

For general questions about USGS data format and delivery: wdfn@usgs.gov

For questions about the WQX v3.0 translation of the USGS qw format: gs-w_help_nwis@usgs.gov