In BWAM/stayCALM: Stay Calm when Applying a Consolidated Assessment Listing Methodology (CALM)

Introduction

This documentation describes how tables from the New York State Department of Environmental Conservation's (NYSDEC's) database are prepared for informing assessments using the stayCALM R-package. Some of this work will not be necessary once the database is finalized. There will still be some amount of data preparation, but it should not be as extensive.

Preprocessing

Prepare the R Environment

Load the necessary R packages into the global environment.

library(tidyverse)
library(readxl)
library(stayCALM)

Upload and Prepare CALM Logic Files.

These files should be finalized and added to the the Assessment database.

Establish the file path to the Excel workbook created by Keleigh Reynolds and Sarah Rickard to tabularize NYSDEC's water quality standards.

calm_logic.path <- file.path(
  "C:",
  "Users",
  "zmsmith.000",
  "New York State Office of Information Technology Services",
  "BWAM - Automation of Assessment",
  "Logic_CALM_Automation_v4.xlsx"
)

This sheet contains some of NYSDEC's water quality standards (WQS). An authoritative table should be included in the Assessment database moving forward.

wqs.df <- read_xlsx(calm_logic.path,
                           sheet = "wqs") %>% 
    separate_rows(water_type, sep = "; ")

This sheet contains a threshold less stringent than the WQS that will indicate if a waterbody is stressed. When available, the 75th percentile of the historical values attaining the WQS were calculcated. This value represents the attaining values nearest the impaired threshold; therefore, the stressed category represents an intermediate zone between attainment and impairment. An authoritative table should be included in the Assessment database moving forward.

wqs_75p.df <- read_xlsx(calm_logic.path,
                           sheet = "wqs_75p")

Define which columns will be used to join the WQS table with the 75th percentile threholds table.

join.cols <- c("parameter", "fraction", "direction",
               "class", "class_type",
               "water_type",
               "type", "trout_class",
               "block", "units")

Check that all parameters in teh 75th percentile thresholds table have a match in the WQs table.

anti.df <- anti_join(wqs_75p.df, wqs.df, 
                        by = join.cols) %>% 
  select(all_of(join.cols))
if (nrow(anti.df) != 0) stop(paste("All parameters in the 75th percentile",
                                   "threshold table should have a match in",
                                   "the WQS table. Please resolve before",
                                   "finalizing this table."))

Join the WQS table with the 75th percentile threshold table.

join.df <- full_join(wqs.df, wqs_75p.df,
                        by = join.cols)

Some of the tables include a list of information in a cell. The code below separates each of these cells into individual rows. Each row should represent a single parameter, class, and spatial extent.

separate.df <- join.df %>% 
  separate_rows(class, sep = "; ") %>% 
  separate_rows(spatial_extent, sep = "; ")

Based on the trout_class column, the appropriate trout variants are appended on to the class.

separate.df$class <- assign_trout_class(separate.df$trout_class, separate.df$class)
unnest.df <- unnest(separate.df, class)
nysdec_wqs <- subset(unnest.df, !class %in% c("d(t)", "d(ts)"))

Export NYSDEC WQS

With the usethis package, the Assessment data is exported as a .rda file making it easily accessible during the development and testing of the stayCALM package.

usethis::use_data(nysdec_wqs, overwrite = TRUE)

BWAM/stayCALM documentation built on May 21, 2020, 3:24 p.m.