The wqg_data from bcgov

What does `Probable Effect Level` mean in the notes?

Media Tissue types all to just Tissue

ie Tissue (dietary) to just Tissue

Add Phototoxic colum

with columns: Phototoxic, Non-Phototoxic and Insensitive

What does `Upper SWQG` mean in the notes?

Also see #30

A median of 1 sample in 1 day seems wrong for pH in Recreation - Swimming

  Variable EMS_Code Use   Media  Days Samples Statistic Notes
  <chr>    <chr>    <chr> <chr> <dbl>   <dbl> <chr>     <chr>
1 pH       EMS_0004 Recr… Water     1       1 median    NA   
2 pH       EMS_0004 Recr… Water     1       1 median    NA   
3 pH       EMS_PH-F Recr… Water     1       1 median    NA   
4 pH       EMS_PH-F Recr… Water     1       1 median    NA

The uses are are all Recreation - Swimming.

Can you check these please @HeatherGranger

Value Tissue (Dietary) to something else?

The following enteries need Days and Samples

# A tibble: 12 x 5
   Variable                                  EMS_Code Use                         Days Samples
   <chr>                                     <chr>    <chr>                      <dbl>   <dbl>
 1 Cyanobacteria Total                       NA       Recreation - Swimming         NA      NA
 2 Cyanobacterial Toxins: Microcystins Total EMS_MCYS Recreation - Swimming         NA      NA
 3 Temperature                               EMS_0013 Drinking Water - Aesthetic    NA      NA
 4 Temperature                               EMS_TEMF Drinking Water - Aesthetic    NA      NA
 5 Temperature                               EMS_0013 Recreation - Swimming         NA      NA
 6 Temperature                               EMS_TEMF Recreation - Swimming         NA      NA
 7 Turbidity                                 EMS_0015 Drinking Water                NA      NA
 8 Turbidity                                 EMS_TURF Drinking Water                NA      NA
 9 Turbidity                                 EMS_0015 Drinking Water                NA      NA
10 Turbidity                                 EMS_TURF Drinking Water                NA      NA
11 Turbidity                                 EMS_0015 Drinking Water                NA      NA
12 Turbidity                                 EMS_TURF Drinking Water                NA      NA

Need to have Types: Interim, Working and Approved

because Working is taken from another jurisdiction where it may be Interim or Approved.

(But aren't Working ones approved or interim in BC? - question for self)

Ensure logical conditions are actual logical R expressions

Commit not merged?

@joethorley - I pushed the wqgs_all.csv file with the added EMS codes but there is something about merging with main branch? Is there something else I have to do to merge in the changes? Thanks!

Move Media before Value (as primary)

fix Limit '20.36 (duration in minutes)^-0.4'

cannot be evaluated as R expression - move to LimitNotes?

Units narrative are often unitless - stick with NA

Need three columns PredictedEffectLevel, Direction and Limit

The values will be

PredictedEffectLevel: No Effect; Probable Effect
Direction: Upper Limit; Lower Limit
Limit: a numeric value

Fix invalid R expressions in Condition

for example:
EMS_0107 | EMS_1107 >= 3.4
should be
EMS_0107 >= 3.4 | EMS_1107 >= 3.4

Get EMS Codes for Taxonomic Codes

Error in limit for Hexachlorobenzene in marine aquatic life?

Consider the 5th row - the coded limit is 0.0231 ug/g but the notes say 0.23 ug/g - can you confirm which is correct @HeatherGranger

           Variable EMS_Code                       Use   Limit Units
1 Hexachlorobenzene EMS_H003   Agriculture - Livestock    0.52  ug/L
2 Hexachlorobenzene EMS_H003 Aquatic Life - Freshwater   0.011  ug/g
3 Hexachlorobenzene EMS_H003 Aquatic Life - Freshwater   0.241  ug/g
4 Hexachlorobenzene EMS_H003     Aquatic Life - Marine 0.00381  ug/g
5 Hexachlorobenzene EMS_H003     Aquatic Life - Marine  0.0231  ug/g
                                                                            Notes
1                                                                            <NA>
2                                      based on equalibrium partitioning approach
3                                                                            <NA>
4                                                                            <NA>
5 0.23 �g/g maximum level for dredge disposal based on apparent effects threshold

What does `Lower SWQG` mean in the notes?

Fix invalid R expressions in Limit

Strip Total and Dissolved into new column

if doesn't say explicitly, assume 'Total'...
what should the new column name be?

Why is temperature not an WQG Type?

It's the only one that is blank

The following variables are currently coded as the mean of 1 sample in 1 day

# A tibble: 46 x 3
   Variable                                EMS_Code Use                     
   <chr>                                   <chr>    <chr>                   
 1 Chloride Dissolved                      EMS_1104 Drinking Water - Aesthe…
 2 Chlorophenol, 2,3,4,6-TTCP              EMS_T037 Drinking Water - Aesthe…
 3 Chlorophenol, 2,4-DCP                   EMS_D050 Drinking Water - Aesthe…
 4 Chlorophenol, 2,4,6-TCP                 EMS_T042 Drinking Water - Aesthe…
 5 Chlorophenol, DCP Total                 EMS_D060 Drinking Water - Aesthe…
 6 Chlorophenol, DCP Total                 EMS_D060 Recreation - Swimming   
 7 Chlorophenol, MCP                       EMS_M050 Drinking Water - Aesthe…
 8 Chlorophenol, MCP                       EMS_M050 Recreation - Swimming   
 9 Chlorophenol, PCP                       EMS_P022 Drinking Water - Aesthe…
10 Chlorophenol, PCP                       EMS_P022 Recreation - Swimming   
11 Chlorophenol, TCP Total                 EMS_T021 Drinking Water - Aesthe…
12 Chlorophenol, TCP Total                 EMS_T021 Recreation - Swimming   
13 Chlorophenol, TTCP Total                EMS_T020 Drinking Water - Aesthe…
14 Chlorophenol, TTCP Total                EMS_T020 Recreation - Swimming   
15 Chlorophyll a                           EMS_0143 Recreation - Swimming   
16 Copper Total                            EMS_CU-T Drinking Water - Aesthe…
17 Cyanobacteria Total                     NA       Recreation - Swimming   
18 Cyanobacterial toxins: Microcystins To… NA       Recreation - Swimming   
19 Ethylbenzene                            EMS_B021 Drinking Water - Aesthe…
20 Iron Total                              EMS_FE-T Drinking Water - Aesthe…
21 Manganese Total                         EMS_MN-T Drinking Water - Aesthe…
22 Methyl tertiary-butyl ether Total       EMS_MTBE Drinking Water - Aesthe…
23 Nitrate Total                           EMS_0110 Recreation - Swimming   
24 Nitrate Total                           EMS_0110 Recreation - Swimming   
25 Nitrate Total                           EMS_1110 Recreation - Swimming   
26 Nitrate Total                           EMS_1110 Recreation - Swimming   
27 Nitrite Total                           EMS_0111 Recreation - Swimming   
28 Nitrite Total                           EMS_0111 Recreation - Swimming   
29 Nitrite Total                           EMS_1111 Recreation - Swimming   
30 Nitrite Total                           EMS_1111 Recreation - Swimming   
31 Phosphorus Total                        EMS_P--T Drinking Water - Aesthe…
32 Phosphorus Total                        EMS_P--T Recreation - Swimming   
33 Sulphate Total                          EMS_0121 Drinking Water - Aesthe…
34 Temperature                             EMS_0013 Drinking Water - Aesthe…
35 Temperature                             EMS_0013 Recreation - Swimming   
36 Temperature                             EMS_TEMF Drinking Water - Aesthe…
37 Temperature                             EMS_TEMF Recreation - Swimming   
38 Toluene Total                           EMS_T001 Drinking Water - Aesthe…
39 Turbidity                               EMS_0015 Drinking Water          
40 Turbidity                               EMS_0015 Drinking Water          
41 Turbidity                               EMS_0015 Drinking Water          
42 Turbidity                               EMS_TURF Drinking Water          
43 Turbidity                               EMS_TURF Drinking Water          
44 Turbidity                               EMS_TURF Drinking Water          
45 Xylenes Total                           EMS_X006 Drinking Water - Aesthe…
46 Zinc Total                              EMS_ZN-T Drinking Water - Aesthe…

@HeatherGranger Can you confirm that the following variables are based on the mean of 1 sample in 1 day (it seems wrong)

Chlorophenol, PCP - Drinking Water duplicate

in current all_wqgs data, all columns are identical for Chlorophenol, PCP - Drinking Water except Limit.

In original spreadsheet, the differ in Type (Aesthetic and Maximum) - somehow both now have type of Aesthetic Objective

Row with Limit = 0.06 should have Type 'Maximum'

Cases with open-ended Conditions

These need to be added to limits spreadsheet

From @atillmanns review of app:

"The second glitch is for substances with logical conditions and it can be traced back to the "all_wqgs.csv" sheet. It seems we are missing maximum values for some of the parameters. For example, for Alkalinity, when calcium dissolved is >8 mg/L then no guideline shows up. However, the user needs to know that for water bodies with dissolved calcium >8 mg/L, the guideline is actually >20 mg/L. This doesn't fit so well into our table structure. I'll pull all the data together and then we can see how best to integrate this with the "all_wqgs.csv" spreadsheet."

Simplify WQG Types

To just Working, Interim and Approved?

Rename Type to Term

Consistent variable naming scheme?

Variables without EMS codes

# A tibble: 19 x 2
   Variable                              EMS_Code
   <chr>                                 <chr>   
 1 Cyanobacteria Total                   NA      
 2 3-hydroxyphenol                       NA      
 3 4-hydroxyphenol                       NA      
 4 Acrolein                              NA      
 5 Alcohol ethoxylated sulphate (AES)    NA      
 6 Alcohol ethoxylated surfactants (AE)  NA      
 7 Aniline Total                         NA      
 8 Diisopropanolamine                    NA      
 9 Lead - Tetra-ethyl lead               NA      
10 Lead - Tetra-methyl lead              NA      
11 Linear alkylbenzene sulphonates (LAS) NA      
12 Polychlorinated Biphenyl 105 Total    NA      
13 Polychlorinated Biphenyl 126 Total    NA      
14 Polychlorinated Biphenyl 169 Total    NA      
15 Polychlorinated Biphenyl 77 Total     NA      
16 Tebuthiuron                           NA      
17 Tin, Tricyclohexyltin                 NA      
18 Tin, Triethyltin                      NA      
19 Tin, Triphenyltin                     NA

EMS codes look up

The EMS codes in the all_wqgs.csv file are congruent with the codes listed in the “EMS Parameter Dictionary” available on DataBC: https://catalogue.data.gov.bc.ca/dataset/bc-environmental-monitoring-system-results/resource/5c982e1a-7c04-466a-a0d7-a775f7968a7a

There are still some missing EMS codes. We will need to work with ENV staff to get these codes entered into the EMS system.

Guideline type needs to be added back in please

I see the column that described the guideline type (short, long, max, etc.) has been removed. This will be necessary for the user interface as it helps people interpret the guidelines and is used in all of our online documents.

Split Recreation into Recreation - Swimming and Recreation - Aesthetic

Group Aquatic Lifes to just Aquatic Life and add Freshwater, Marine, Estarine and Marine & Estuarine sub categories

EMS Units as separate lookup table

Redefine acid sensitivity condition algebraically

Waterbody is highly sensitive to acid inputs (<4 mg/L dissolved calcium).  Sensitivity to acid inputs can be determined by the concentration of dissolved calcium: < 4 mg/L is highly sensitive to acid inputs; 4 to 8 mg/L is moderately sensitive; and > 8 mg/L is low sensitivity.

Can we redefine these algebraically in terms of EMS_CA-D?

ie EMS_CA-D < 4 etc

Why multiple EMS Parameters or Taxonomic Code for a substance?

May need to add two columns (if Taxonomic Code separate) or have lookup table?

Diethyl phthalate duplicate

Two rows of Diethyl phthalate with all columns identical except for Limit
This confirmed in the raw spreadsheet originally sent by @atillmanns so is not a result of subsequent changes.
Which Limit is correct?

Ammonia Total - Long Term Chronic duplicates

There are two sets of duplicates for Ammonia Total - Long Term Chronic

Condition = '0<= EMS_0013 & EMS_0013 < 15 & 6.5 <= EMS_0004 & EMS_0004 < 7.7'
Condition = '0<= EMS_0013 & EMS_0013 < 15 & 7.7 <= EMS_0004 & EMS_0004 < 8'

Everything including Condition is identical but two possible Limits

This is confirmed in the original spreadsheet, so is not a result of subsequent changes

Variables with unrecognized EMS Codes

I think the following have been mangled by Excel - can we fix by just adding leading 0s so they are 4 characters in length (after the EMS_)?

# A tibble: 18 x 2
   Variable                                    EMS_Code
   <chr>                                       <chr>   
 1 Coliforms Fecal                             EMS_450 
 2 Colour Apparent                             EMS_1   
 3 Colour True                                 EMS_2   
 4 Cyanide Weak Acid Dissociable               EMS_157 
 5 Cyanobacterial Toxins: MicrocystinLR        EMS_MCYS
 6 Cyanobacterial Toxins: Microcystins Total   EMS_MCYS
 7 Enterococci                                 EMS_148 
 8 Escherichia coli                            EMS_147 
 9 Ethylbenzene                                EMS_B023
10 Ethylbenzene                                EMS_B024
11 Organic Carbon Total                        EMS_103 
12 Phenols Total                               EMS_117 
13 Resin acids Total                           EMS_128 
14 Salinity                                    EMS_130 
15 Sulfolane                                   EMS_SULF
16 Sulphate Total                              EMS_121 
17 Sulphide Total                              EMS_125 
18 Total dissolved solids (filterable residue) EMS_7

Condition logic

There are many Conditions that look like this:
EMS_0107 | EMS_1107 > 10

I assume in R expression this should look like:
EMS_0107 > 10 | EMS_1107 > 10

If we look at 'Fluoride Total' - 'Freshwater Life - Aquatic', there are two possible guidelines depending on Conditions:

EMS_0107 > 10 | EMS_1107 > 10
EMS_0107 <= 10 | EMS_1107 <= 10

if EMS_0107 is 9 and EMS_1107 is 12, then both conditions evaluate to being TRUE and two guidelines will be shown.

is this the desired behaviour?

Rename Average to Statistic?

and geometric mean to geomean and quantile (90) to quantile90

Try and move notes into coding and remove

What does `Interim Sediment Quality Guideline` mean in the notes?

And why are they all type Working not Interim?

In the notes where it says `Minimum 5 samples` I'm assuming short term ie 1 day

Can you confirm?

Rename Type to Term

and recode aesthetic and primary contact to term

Have single EMS_Code value for a substance

Type vs. Days/Samples

This relates to: https://github.com/poissonconsulting/shinywqg/issues/22

We need to organize Type vs. Days/Samples better. Here are all the unique combinations of these so far:

See for example cases where Long term chronic have Days/Samples of 1 - this seems wrong.
Also cases where Type is Primary Contact but can have different Days/Samples.

I think we need to create a new column that defines the Time (Short term vs. long term) and a column that defines the Type - I feel that the current Type column is mixing these things,

Ensure maximum is actual R expressions

in particular remove "," and replace "duration in minutes"

keep "Narrative" so can view but not calculate?

bcgov / wqg_data Goto Github PK

wqg_data's People

Contributors

Stargazers

Watchers

Forkers

wqg_data's Issues

Recommend Projects

Recommend Topics

Recommend Org