README.md

In TravisPritchardODEQ/deqalcrit: Implementation of Oregon DEQ's Aluminum Criteria Procedures

Installing deqalcrit

Install deqalcrit from github by running this code:

devtools::install_github('TravisPritchardODEQ/deqalcrit')

Only Oregon DEQ users with properly setup access to use the AWQMSdata backage will be able to use all features. Other users will be able to use the Al_crit_calculator(), Al_default_DOC(), and Al_default_criteria() functions, as those do not need access to AWQMS.

Included functions

| Function | Purpose | |-----------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------| | al_anc_query(al_df) | Queries AWQMS to get ancillary data needed to calculate Al criteria. | | al_combine_ancillary(al_df, ancillary_df) | Combines Al data and output from al_get_ancillary in preparation to calculate criteria. | | al_crit_calculator(df, ph_col = “pH”, hardness_col = “Hardness”, DOC_col = “DOC”, lat_col = “Lat_DD”, long_col = “Long_DD”, verbose = FALSE) | Calculates aluminum criteria based on EPA’s 2018 national recommended freshwater aquatic life criteria for aluminum. | | al_default_DOC(lat, long) | Looks up default DOC values used in calculating Al criteria. | | al_default_criteria(lat, long, type) | Looks up default acute or chronic Al criteria values. |

Using deqalcritpackage

This section illustrates a sample workflow for calculating aluminum criteria.

First, lets load some needed packages:

library(deqalcrit)
library(AWQMSdata)

Next, get some Al data out of AWQMS. In this example, we are going to use data from the Surface Water Ambient Monitoring program at station 10917-ORDEQ, which is on the Pudding River. We will use the AWQMSdata package to query Oregon’s AWQMS database from the backend. To install and setup this package, see https://github.com/TravisPritchardODEQ/AWQMSdata

al_data_AWQMS <- AWQMSdata::AWQMS_Data(char = "Aluminum",
                                       media = 'Water',
                                       project = 'Surface Water Ambient Monitoring',
                                       station = '10917-ORDEQ')

This dataframe is very wide, so for illustration purposes, let’s get rid of some columns we do not need. Columns that need to be present for the calculator to work are:

• MLocID,

• Lat_DD,

• Long_DD,

• SampleStartDate,

• SampleStartTime,

• SampleMedia

• SampleSubmedia

If these columns don’t exist, the ancillary data joins won’t work.

al_data <- al_data_AWQMS %>%
  dplyr::select(MLocID, AU_ID, Lat_DD, Long_DD, SampleStartDate, SampleStartTime,
                SampleMedia, SampleSubmedia, Char_Name, Char_Name, Sample_Fraction,
                Result_Numeric,Result_Operator,  Result_Unit )

That gets us:

MLocID AU\_ID Lat\_DD Long\_DD SampleStartDate SampleStartTime SampleMedia SampleSubmedia Char\_Name Sample\_Fraction Result\_Numeric Result\_Operator Result\_Unit 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-10-14 13:42:00 Water Surface Water Aluminum Dissolved 6.15 = ug/l 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-10-14 13:42:00 Water Surface Water Aluminum Total Recoverable 99.00 = ug/l 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-06-22 13:37:00 Water Surface Water Aluminum Dissolved 20.00 < ug/l 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-06-22 13:37:00 Water Surface Water Aluminum Total Recoverable 53.30 = ug/l 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-06-30 12:31:00 Water Surface Water Aluminum Dissolved 12.80 = ug/l 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-06-30 12:31:00 Water Surface Water Aluminum Total Recoverable 54.10 = ug/l 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-02-05 11:53:00 Water Surface Water Aluminum Dissolved 23.10 = ug/l 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-02-05 11:53:00 Water Surface Water Aluminum Total Recoverable 923.00 = ug/l 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-02-03 13:23:00 Water Surface Water Aluminum Dissolved 30.00 = ug/l 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-02-03 13:23:00 Water Surface Water Aluminum Total Recoverable 885.00 = ug/l

Next, we need to get all the ancillary data. The al_get_ancillary() function will query AWQMS and fetch all ancillary data needed to determine the criteria. It is a wrapper around the AWQMS_Data() function that queries the ancillary parameters from monitoring locations in the aluminum dataset. The date range for the query is the min/max date of the entire dataset. This supplies much more data than needed, however, it was the easiest to implement and the code will discard unneeded data later.

ancillary_data<- deqalcrit::al_anc_query(al_data)

The parameters that are queried are:

• Organic carbon
• pH
• Hardness, Ca, Mg
• Hardness, non-carbonate
• Calcium
• Magnesium
• Specific conductance

Now, we have to run some calculations on these values to get the final DOC, pH, and Hardness values. And then join them to the Aluminum dataset. al_combine_ancillary() will perform the calculations identified in the implementation document and then perform the join. The calculations are a bit complex, but here is what is going on:

1. Convert all ug/L units to mg/L
2. If we have dissolved and total fractions at the same date and time, keep the dissolved fraction, and drop the total fraction. If we only have total fraction, keep the total.
3. Convert data format from “Long” to “Wide”
4. If we only have TOC and not DOC, convert TOC to DOC by multiplying by 0.83
5. If we don’t have DOC or TOC, look up the default DOC value by lat/long using the published map service at https://arcgis.deq.state.or.us/arcgis/rest/services/WQ/OR_Ecoregions_Aluminum/MapServer.
6. If we don’t have Hardness, use calcium and magnesium. If we don’t have one of those, use specific conductance.
7. Split DOC, Hardness and pH into separate dataframes.
8. Join each of the three dataframes into the aluminum dataset. Ancillary data must be collected on the same day. If more than 1 daily result for an ancillary parameter, only keep the one that is nearest in time to the aluminum data.

al_data_joined <- deqalcrit::al_combine_ancillary(al_df = al_data,
                                       ancillary_df = ancillary_data)

That gets us:

MLocID AU\_ID Lat\_DD Long\_DD SampleStartDate SampleStartTime SampleMedia SampleSubmedia Char\_Name Sample\_Fraction Result\_Numeric Result\_Operator Result\_Unit DOC Hardness pH al\_ancillary\_cmt 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-10-14 13:42:00 Water Surface Water Aluminum Dissolved 6.15 = ug/l 2.84 75.8 7.7 NA 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-10-14 13:42:00 Water Surface Water Aluminum Total Recoverable 99.00 = ug/l 2.84 75.8 7.7 NA 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-06-22 13:37:00 Water Surface Water Aluminum Dissolved 20.00 < ug/l 2.65 61.5 7.8 NA 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-06-22 13:37:00 Water Surface Water Aluminum Total Recoverable 53.30 = ug/l 2.65 61.5 7.8 NA 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-06-30 12:31:00 Water Surface Water Aluminum Dissolved 12.80 = ug/l 2.13 51.9 7.8 NA 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-06-30 12:31:00 Water Surface Water Aluminum Total Recoverable 54.10 = ug/l 2.13 51.9 7.8 NA 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-02-05 11:53:00 Water Surface Water Aluminum Dissolved 23.10 = ug/l 2.22 47.6 7.4 NA 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-02-05 11:53:00 Water Surface Water Aluminum Total Recoverable 923.00 = ug/l 2.22 47.6 7.4 NA 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-02-03 13:23:00 Water Surface Water Aluminum Dissolved 30.00 = ug/l 1.98 36.1 7.3 NA 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-02-03 13:23:00 Water Surface Water Aluminum Total Recoverable 885.00 = ug/l 1.98 36.1 7.3 NA

At this point, calculating the criteria is as easy as:

al_criteria <- deqalcrit::al_crit_calculator(al_data_joined)

MLocID AU\_ID Lat\_DD Long\_DD SampleStartDate SampleStartTime SampleMedia SampleSubmedia Char\_Name Sample\_Fraction Result\_Numeric Result\_Operator Result\_Unit DOC Hardness pH al\_ancillary\_cmt CCC FAV CMC Final\_CMC Final\_CCC Flag 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-10-14 13:42:00 Water Surface Water Aluminum Dissolved 6.15 = ug/l 2.84 75.8 7.7 NA 884.9625 4712.758 2356.379 2400 880 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-10-14 13:42:00 Water Surface Water Aluminum Total Recoverable 99.00 = ug/l 2.84 75.8 7.7 NA 884.9625 4712.758 2356.379 2400 880 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-06-22 13:37:00 Water Surface Water Aluminum Dissolved 20.00 < ug/l 2.65 61.5 7.8 NA 980.3054 4774.276 2387.138 2400 980 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-06-22 13:37:00 Water Surface Water Aluminum Total Recoverable 53.30 = ug/l 2.65 61.5 7.8 NA 980.3054 4774.276 2387.138 2400 980 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-06-30 12:31:00 Water Surface Water Aluminum Dissolved 12.80 = ug/l 2.13 51.9 7.8 NA 975.5843 4321.402 2160.701 2200 980 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-06-30 12:31:00 Water Surface Water Aluminum Total Recoverable 54.10 = ug/l 2.13 51.9 7.8 NA 975.5843 4321.402 2160.701 2200 980 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-02-05 11:53:00 Water Surface Water Aluminum Dissolved 23.10 = ug/l 2.22 47.6 7.4 NA 632.1950 3199.684 1599.842 1600 630 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-02-05 11:53:00 Water Surface Water Aluminum Total Recoverable 923.00 = ug/l 2.22 47.6 7.4 NA 632.1950 3199.684 1599.842 1600 630 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-02-03 13:23:00 Water Surface Water Aluminum Dissolved 30.00 = ug/l 1.98 36.1 7.3 NA 544.8366 2619.108 1309.554 1300 540 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-02-03 13:23:00 Water Surface Water Aluminum Total Recoverable 885.00 = ug/l 1.98 36.1 7.3 NA 544.8366 2619.108 1309.554 1300 540

If the aluminum result is missing a value for DOC, pH, or hardness; it will lookup the default criteria values using the lat/long and querying the GIS web service at https://arcgis.deq.state.or.us/arcgis/rest/services/WQ/OR_Ecoregions_Aluminum/MapServer

And if we wanted the extra information provided in EPA’s criteria calculation:

al_criteria_extra <- deqalcrit::al_crit_calculator(al_data_joined, verbose = TRUE)

MLocID AU\_ID Lat\_DD Long\_DD SampleStartDate SampleStartTime SampleMedia SampleSubmedia Char\_Name Sample\_Fraction Result\_Numeric Result\_Operator Result\_Unit DOC Hardness pH al\_ancillary\_cmt CCC FAV CMC Final\_CMC Final\_CCC Flag 1\_Chronic\_Genus\_Mean\_Value\_ug\_L 1\_Chronic\_Genus 2\_Chronic\_Genus\_Mean\_Value\_ug\_L 2\_Chronic\_Genus 3\_Chronic\_Genus\_Mean\_Value\_ug\_L 3\_Chronic\_Genus 4\_Chronic\_Genus\_Mean\_Value\_ug\_L 4\_Chronic\_Genus 1\_Acute\_Genus\_Mean\_Value\_ug\_L 1\_Acute\_Genus 2\_Acute\_Genus\_Mean\_Value\_ug\_L 2\_Acute\_Genus 3\_Acute\_Genus\_Mean\_Value\_ug\_L 3\_Acute\_Genus 4\_Acute\_Genus\_Mean\_Value\_ug\_L 4\_Acute\_Genus 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-10-14 13:42:00 Water Surface Water Aluminum Dissolved 6.15 = ug/l 2.84 75.8 7.7 NA 884.9625 4712.758 2356.379 2400 880 1034.72679593329 Salmo 1520.08037753046 Salvelinus 2429.70280579458 Daphnia 2530.44325552532 Lampsilis 5733.17689272201 Daphnia 7116.27880497672 Micropterus 7889.09161952066 Oncorhynchus 19162.4313980968 Ceriodaphnia 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-10-14 13:42:00 Water Surface Water Aluminum Total Recoverable 99.00 = ug/l 2.84 75.8 7.7 NA 884.9625 4712.758 2356.379 2400 880 1034.72679593329 Salmo 1520.08037753046 Salvelinus 2429.70280579458 Daphnia 2530.44325552532 Lampsilis 5733.17689272201 Daphnia 7116.27880497672 Micropterus 7889.09161952066 Oncorhynchus 19162.4313980968 Ceriodaphnia 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-06-22 13:37:00 Water Surface Water Aluminum Dissolved 20.00 < ug/l 2.65 61.5 7.8 NA 980.3054 4774.276 2387.138 2400 980 1091.58888301009 Salmo 1603.61444964553 Salvelinus 2235.26713222098 Daphnia 2327.94588109153 Lampsilis 5274.38246395711 Daphnia 7507.34480100749 Micropterus 8322.62655491534 Oncorhynchus 17628.9680266462 Ceriodaphnia 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-06-22 13:37:00 Water Surface Water Aluminum Total Recoverable 53.30 = ug/l 2.65 61.5 7.8 NA 980.3054 4774.276 2387.138 2400 980 1091.58888301009 Salmo 1603.61444964553 Salvelinus 2235.26713222098 Daphnia 2327.94588109153 Lampsilis 5274.38246395711 Daphnia 7507.34480100749 Micropterus 8322.62655491534 Oncorhynchus 17628.9680266462 Ceriodaphnia 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-06-30 12:31:00 Water Surface Water Aluminum Dissolved 12.80 = ug/l 2.13 51.9 7.8 NA 975.5843 4321.402 2160.701 2200 980 1041.75592574141 Salmo 1530.40662242374 Salvelinus 1866.16689147557 Daphnia 1943.54198915069 Lampsilis 4403.44591719386 Daphnia 7164.62127341142 Micropterus 7942.68397769649 Oncorhynchus 14717.9708357812 Ceriodaphnia 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-06-30 12:31:00 Water Surface Water Aluminum Total Recoverable 54.10 = ug/l 2.13 51.9 7.8 NA 975.5843 4321.402 2160.701 2200 980 1041.75592574141 Salmo 1530.40662242374 Salvelinus 1866.16689147557 Daphnia 1943.54198915069 Lampsilis 4403.44591719386 Daphnia 7164.62127341142 Micropterus 7942.68397769649 Oncorhynchus 14717.9708357812 Ceriodaphnia 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-02-05 11:53:00 Water Surface Water Aluminum Dissolved 23.10 = ug/l 2.22 47.6 7.4 NA 632.1950 3199.684 1599.842 1600 630 718.114941262288 Salmo 1054.95714937938 Salvelinus 1557.28005663353 Daphnia 1621.84807412433 Lampsilis 3674.5901658818 Daphnia 4938.79752232819 Micropterus 5475.14047884978 Oncorhynchus 12281.861049712 Ceriodaphnia 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2015-02-05 11:53:00 Water Surface Water Aluminum Total Recoverable 923.00 = ug/l 2.22 47.6 7.4 NA 632.1950 3199.684 1599.842 1600 630 718.114941262288 Salmo 1054.95714937938 Salvelinus 1557.28005663353 Daphnia 1621.84807412433 Lampsilis 3674.5901658818 Daphnia 4938.79752232819 Micropterus 5475.14047884978 Oncorhynchus 12281.861049712 Ceriodaphnia 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-02-03 13:23:00 Water Surface Water Aluminum Dissolved 30.00 = ug/l 1.98 36.1 7.3 NA 544.8366 2619.108 1309.554 1300 540 602.836826513472 Salmo 885.606166224136 Salvelinus 1211.27262439556 Daphnia 1261.49446578173 Lampsilis 2858.14388673777 Daphnia 4145.97838602203 Micropterus 4596.22286257336 Oncorhynchus 9552.99080777169 Ceriodaphnia 10917-ORDEQ OR\_SR\_1709000905\_02\_104088 45.23366 -122.7502 2016-02-03 13:23:00 Water Surface Water Aluminum Total Recoverable 885.00 = ug/l 1.98 36.1 7.3 NA 544.8366 2619.108 1309.554 1300 540 602.836826513472 Salmo 885.606166224136 Salvelinus 1211.27262439556 Daphnia 1261.49446578173 Lampsilis 2858.14388673777 Daphnia 4145.97838602203 Micropterus 4596.22286257336 Oncorhynchus 9552.99080777169 Ceriodaphnia

Additional tools

These functions use DEQ’s map services to lookup default DOC and criteria values. These functions send lat/long information to the map server and returns default values based on geographic location.

deqalcrit::al_default_DOC(45.23366, -122.7502)
#> [1] 1.16

deqalcrit::al_default_criteria(45.23366, -122.7502, type = "Chronic")
#> [1] 470

deqalcrit::al_default_criteria(45.23366, -122.7502, type = "Acute")
#> [1] 940

deqalcrit::al_default_criteria(45.23366, -122.7502, type = "Ecoregion")
#> [1] "Willamette Valley"

TravisPritchardODEQ/deqalcrit documentation built on Dec. 18, 2021, 5:13 p.m.