The MassWateR package includes additional tools for generating output that will facilitate upload of data to the EPA Water Quality Exchange (WQX). The function for generating the WQX output can be used once the required data are successfully imported into R (see the data input and checks vignette for an overview). The results data file, the data quality objectives file for accuracy, the site metadata, and the wqx metadata files are required to use these functions. The example data included with the package are imported here to demonstrate how to use the analysis functions:
library(MassWateR)
# import results data
respth <- system.file("extdata/ExampleResults.xlsx", package = "MassWateR")
resdat <- readMWRresults(respth)
#> Running checks on results data...
#> Checking column names... OK
#> Checking all required columns are present... OK
#> Checking valid Activity Types... OK
#> Checking Activity Start Date formats... OK
#> Checking depth data present... OK
#> Checking for non-numeric values in Activity Depth/Height Measure... OK
#> Checking Activity Depth/Height Unit... OK
#> Checking Activity Relative Depth Name formats... OK
#> Checking values in Activity Depth/Height Measure > 1 m / 3.3 ft... OK
#> Checking Characteristic Name formats... OK
#> Checking Result Values... OK
#> Checking for non-numeric values in Quantitation Limit... OK
#> Checking QC Reference Values... OK
#> Checking for missing entries for Result Unit... OK
#> Checking if more than one unit per Characteristic Name... OK
#> Checking acceptable units for each entry in Characteristic Name... OK
#>
#> All checks passed!
# import accuracy data
accpth <- system.file("extdata/ExampleDQOAccuracy.xlsx", package = "MassWateR")
accdat <- readMWRacc(accpth)
#> Running checks on data quality objectives for accuracy...
#> Checking column names... OK
#> Checking all required columns are present... OK
#> Checking column types... OK
#> Checking no "na" in Value Range... OK
#> Checking for text other than <=, ≤, <, >=, ≥, >, ±, %, AQL, BQL, log, or all... OK
#> Checking overlaps in Value Range... OK
#> Checking gaps in Value Range... OK
#> Checking Parameter formats... OK
#> Checking for missing entries for unit (uom)... OK
#> Checking if more than one unit (uom) per Parameter... OK
#> Checking acceptable units (uom) for each entry in Parameter... OK
#> Checking empty columns... OK
#>
#> All checks passed!
# import site metadata
sitpth <- system.file("extdata/ExampleSites.xlsx", package = "MassWateR")
sitdat <- readMWRsites(sitpth)
#> Running checks on site metadata...
#> Checking column names... OK
#> Checking all required columns are present... OK
#> Checking for missing latitude or longitude values... OK
#> Checking for non-numeric values in latitude... OK
#> Checking for non-numeric values in longitude... OK
#> Checking for positive values in longitude... OK
#> Checking for missing entries for Monitoring Location ID... OK
#>
#> All checks passed!
# import wqx metadata
wqxpth <- system.file("extdata/ExampleWQX.xlsx", package = "MassWateR")
wqxdat <- readMWRwqx(wqxpth)
#> Running checks on WQX metadata...
#> Checking column names... OK
#> Checking all required columns are present... OK
#> Checking unique parameters... OK
#> Checking Parameter formats... OK
#>
#> All checks passed!
Generating output for WQX
The single function for generating output for upload to WQX is
tabMWRwqx()
. This function will export a single Excel
workbook with three sheets, named “Project”, “Locations”, and “Results”.
The output is populated with as much content as possible based on
information in the input files. The remainder of the information not
included in the output will need to be manually entered before uploading
the data to WQX. All required columns are present, but individual rows
will need to be verified for completeness. It is the responsibility of
the user to verify this information is complete and correct before
uploading the data.
The workflow for using this function is to import the required data
(results, data quality objectives file for accuracy, site metadata, and
wqx metadata, as above) and to fix any errors noted on import prior to
creating the output. The function can be used with inputs as paths to
the relevant files or as data frames returned by
readMWRresults()
, readMWRacc()
,
readMWRsites()
, and readMWRwqx()
. For the
former, the full suite of data checks can be evaluated with
runkchk = T
(default) or suppressed with
runchk = F
, as explained in the relevant help files. In the
latter case, downstream analyses may not work if data are formatted
incorrectly.
The workbook can be created as follows by including the required files and specifying an output directory where the Excel file is saved (a temporary directory is used here). Once the function is done running, a message indicating success and where the file is located is returned. The Excel file can be further edited by hand as needed.
tabMWRwqx(res = resdat, acc = accdat, sit = sitdat, wqx = wqxdat, output_dir = tempdir())
#> Excel workbook created successfully! File located at /tmp/RtmpPFBfKc/wqxtab.xlsx
As a convenience, the input files can also be passed to the
tabMWRwqx()
function as a named list using the
fset
argument. This eliminates the need to individually
specify the input arguments.
# names list of inputs
fsetls <- list(
res = resdat,
acc = accdat,
sit = sitdat,
wqx = wqxdat
)
tabMWRwqx(fset = fsetls, output_dir = tempdir())
#> Excel workbook created successfully! File located at /tmp/RtmpPFBfKc/wqxtab.xlsx
The name of the output file can also be changed using the
output_file
argument, the default being
wqxtab.xlsx
. Warnings can also be turned off or on
(default) using the warn
argument. This returns any
warnings when data are imported and only applies if the file inputs are
paths.
If preferred, the data frames that are saved as separate sheets in
the workbook can be returned as a list by setting
listout = T
.
tabMWRwqx(fset = fsetls, listout = T)
#> $Projects
#> # A tibble: 2 × 6
#> `Project ID` `Project Name` `Project Description` QAPP Approved Indicator (Y…¹
#> <chr> <chr> <chr> <chr>
#> 1 My_WQ NA NA NA
#> 2 NA NA NA NA
#> # ℹ abbreviated name: ¹`QAPP Approved Indicator (Yes/No)`
#> # ℹ 2 more variables: `Project Attachment File Name (optional)` <chr>,
#> # `Project Attachment Type (optional)` <chr>
#>
#> $Locations
#> # A tibble: 12 × 10
#> `Monitoring Location ID` `Monitoring Location Name` Monitoring Location …¹
#> <chr> <chr> <chr>
#> 1 ABT-026 Rte 2, Concord NA
#> 2 ABT-062 Rte 62, Acton NA
#> 3 ABT-077 Rte 27/USGS, Maynard NA
#> 4 ABT-144 Rte 62, Stow NA
#> 5 ABT-162 Cox Street bridge NA
#> 6 ABT-237 Robin Hill Rd, Marlboro NA
#> 7 ABT-301 Rte 9, Westboro NA
#> 8 ABT-312 Mill Road, Westboro NA
#> 9 DAN-013 Danforth Br, Hudson NA
#> 10 ELZ-004 Elizabeth Br, Stow NA
#> 11 HOP-011 Hop Br, Northboro NA
#> 12 NSH-002 Nashoba, Commonwealth, W. Co… NA
#> # ℹ abbreviated name: ¹`Monitoring Location Type`
#> # ℹ 7 more variables: `Tribal Land Indicator (Yes/No) (optional)` <chr>,
#> # `Tribal Land Name (optional)` <chr>,
#> # `Monitoring Location Latitude (DD.DDDD)` <dbl>,
#> # `Monitoring Location Longitude (-DDD.DDDD)` <dbl>,
#> # `Monitoring Location Source Map Scale (conditional)` <chr>,
#> # `Monitoring Location Horizontal Collection Method` <chr>, …
#>
#> $Results
#> # A tibble: 813 × 31
#> `Project ID` `Monitoring Location ID` `Activity ID` Record ID User Suppl…¹
#> <chr> <chr> <chr> <chr>
#> 1 My_WQ ABT-026 ABT-026:2022051… A1234-67
#> 2 My_WQ ABT-077 ABT-077:2022051… A1234-68
#> 3 My_WQ ABT-301 ABT-301:2022051… A1234-69
#> 4 My_WQ ABT-312 ABT-312:2022051… A1234-70
#> 5 My_WQ DAN-013 DAN-013:2022051… A1234-71
#> 6 My_WQ ELZ-004 ELZ-004:2022051… A1234-72
#> 7 My_WQ HOP-011 HOP-011:2022051… A1234-73
#> 8 My_WQ NSH-002 NSH-002:2022051… A1234-74
#> 9 My_WQ ABT-026 ABT-026:2022061… A1234-75
#> 10 My_WQ ABT-062 ABT-062:2022061… A1234-76
#> # ℹ 803 more rows
#> # ℹ abbreviated name: ¹`Record ID User Supplied`
#> # ℹ 27 more variables: `Activity Type` <chr>, `Activity Media Name` <chr>,
#> # `Activity Start Date` <chr>, `Activity Start Time` <chr>,
#> # `Activity Start Time Zone` <chr>, `Activity Depth/Height Measure` <chr>,
#> # `Activity Depth/Height Unit` <chr>, `Activity Relative Depth Name` <chr>,
#> # `Sample Collection Method ID` <chr>, …
Submitting data to Mass DEP
If you are submitting your data to Mass DEP, you must follow all of their instructions as described on their External Data Submittal webpage. Data and QC data can be submitted using either the MassDEP data submission template OR by uploading data to WQX. If you are submitting through WQX you should complete the submission by notifying Mass DEP via email (WQData.Submit@mass.gov). The e-mail must include the group name / project name under which the data was submitted to WQX, a copy of the project QAPP (if not already submitted), a statement of data integrity, the MassWateR QC Report (with the raw data print out), and any other supporting data reports.
Uploading data to WQX
If desired, all rows from the WQX output file (created with
tabMWRwqx()
) can be copied and pasted into the WQX
upload template (also available on the Resources
tab), which offers an enhanced spreadsheet layout with more interactive
functionality (e.g., drop-down lists, prompts) and additional tabs with
supporting information (e.g., instructions, field definitions). This
template is especially useful if manual edits to the data are needed
prior to WQX upload.
To upload data to WQX, you will need to create or sign into your WQX account. From there, you can upload your Projects, Locations, and Results spreadsheets individually. Note that Projects and Locations need only be uploaded once for your organization, unless the data change. For each spreadsheet upload, assign the appropriate MassWateR custom import configuration, check that your Organization ID is assigned, browse to your upload file, and select Import Data (image below). The next screen will notify you of upload status and prompt you to correct any errors or warnings that arise. More information on error resolution can be found in the WQX User Guide.