Multiple Coastal Stressors - Environmental Data
Gabriel Barrett
2022-22-08
All Site Information
leaflet interactive map
Fig. 1 leaflet map showing site positions with popups containing site metadata
Buzzards Bay Water Quality Coalition
Water Quality data based Buzzards Bay Water Quality Coalition is placed with WHOI archives: https://darchive.mblwhoilibrary.org/handle/1912/25762
qc_exclude <- c(2,4,6,7,8,9)
buz <- read_excel("D:/Puritz_Lab/NSF/NSF_EnvironmentalData/data/bbcdata1992to2020-ver07May2021.xlsx",sheet=2) %>%
left_join(.,read_excel("D:/Puritz_Lab/NSF/NSF_EnvironmentalData/data/bbcdata1992to2020-ver07May2021.xlsx",sheet = 6,
col_names = T,skip=1),by="STN_ID") %>%
filter(
!(TEMP_QC %in% qc_exclude),
!(SAL_QC %in% qc_exclude),
!(DO_QC %in% qc_exclude),
!(PH_QC %in% qc_exclude),
!(TIME_QC %in% qc_exclude),
!(is.na(STN_ID))) %>%
mutate(
time = format(as.POSIXct(TIME, format='%Y-%m-%d %H:%M:%S'),format='%H:%M:%S'),
time_stamp = as.POSIXct(paste(SAMP_DATE,time,sep=" "),format = '%Y-%m-%d %H:%M:%S'),
coalition="buzzards")
# This step transforms datasets so that they are able to be rbinded
buz_curated <- buz %>%
select(stn_id=STN_ID,
location=WQI_Area,
town=Town,
TEMP=TEMP_C,
DO=DO_MGL,
PH=PH,
SAL=SAL_FIELD,
depth=TOTDEP_M,
lat=LATITUDE,
long=LONGITUDE,
coalition,time_stamp)| STN_ID | STN_EQUIV | SAMP_DATE | UNIQUE_ID | SOURCE | LAB | GEN_QC | TIME | TIME_QC | DUP | SAMPDEP_M | SAMPDEP_QC | SECCHI_M | SECC_QC | TOTDEP_M | TOTDEP_QC | TEMP_C | TEMP_QC | SAL_FIELD | SAL_METH | SAL_QC | SAL_LAB | SAL_LAB_QC | COND | DO_MGL | DO_SAT | DO_METH | DO_QC | PO4_UM | PO4_QC | TP_UM | TP_QC | NH4_UM | NH4_QC | NO2NO3_UM | NO2NO3_QC | TDN_UM | TDN_QC | PON_UM | PON_QC | TN_UM | TN_QC | POC_UM | POC_QC | CHLA_UGL_LAB | CHLA_LAB_QC | CHLA_LAB_METH | CHLA_UGL_ SONDE | CHLA_SONDE_QC | CHLA_SONDE_METH | PHEO_UGL | PHEO_QC | DOC_UM | DOC_QC | PH | PH_QC | BEAU | BEAU_QC | TIDE | WHTR | PREC | WIND | OBS_COMM | QA_COM | OLDNAME_X | OLDNAME_N | STAT_EQUIV | COMMENTS | LATITUDE | LONGITUDE | Town | EMBAYMENT | WQI_Area | time | time_stamp | coalition |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 105E | 105E | 1992-04-05 | 105E-X-1992-04-05-0930-S-0.15-0 | BBC | NA | NA | 1899-12-31 09:30:00 | 12 | 0 | 0.15 | NA | NA | NA | 1.02 | NA | 6.44 | 1 | 19 | S1 | 11 | NA | NA | NA | 9.5 | 0.874 | DO1 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | F | 2 | NA | S | NA | NA | 105E | NA | 105E | Station moved to dock | 41.55674 | -71.06931 | Westport | Westport Rivers | Westport River East Inner | 09:30:00 | 1992-04-05 09:30:00 | buzzards |
| 106E | 106E | 1992-04-06 | 106E-X-1992-04-06-0830-S-0.15-0 | BBC | NA | NA | 1899-12-31 08:30:00 | NA | 0 | 0.15 | NA | NA | NA | 0.61 | NA | 7.00 | NA | 18 | S1 | 11 | NA | NA | NA | 10.5 | 0.973 | DO1 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | F | 2 | NA | NW | NA | NA | 106E | NA | 106E | NA | 41.55676 | -71.05599 | Westport | Westport Rivers | Westport River East Inner | 08:30:00 | 1992-04-06 08:30:00 | buzzards |
| 102W | 102W | 1992-04-07 | 102W-X-1992-04-07-0858-S-0.15-0 | BBC | NA | NA | 1899-12-31 08:58:00 | NA | 0 | 0.15 | NA | NA | NA | 0.20 | NA | 6.72 | 1 | 31 | S1 | 11 | NA | NA | NA | 11.5 | 1.152 | DO1 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | F | 2 | NA | NA | NA | NA | NA | NA | 102W | NA | 41.53145 | -71.10542 | Westport | Westport Rivers | Westport River West Outer | 08:58:00 | 1992-04-07 08:58:00 | buzzards |
| 102W | 102W | 1992-04-09 | 102W-X-1992-04-09-0710-S-0.15-0 | BBC | NA | NA | 1899-12-31 07:10:00 | NA | 0 | 0.15 | NA | NA | NA | 0.14 | 2 | 6.16 | 1 | 29 | S1 | 11 | NA | NA | NA | 8.5 | 0.829 | DO1 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 1 | NA | NA | NA | NA | NA | NA | 102W | NA | 41.53145 | -71.10542 | Westport | Westport Rivers | Westport River West Outer | 07:10:00 | 1992-04-09 07:10:00 | buzzards |
| 104E | 104E | 1992-04-09 | 104E-X-1992-04-09-0935-S-0.15-0 | BBC | NA | NA | 1899-12-31 09:35:00 | 12 | 0 | 0.15 | NA | NA | NA | 0.46 | NA | 6.72 | 1 | 18 | S1 | 11 | NA | NA | NA | 7.5 | 0.690 | DO1 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 2 | NA | F | 1 | NA | SE | NA | NA | 104E | NA | 104E | NA | 41.55897 | -71.06406 | Westport | Westport Rivers | Westport River East Inner | 09:35:00 | 1992-04-09 09:35:00 | buzzards |
| 109E | 109E | 1992-04-09 | 109E-X-1992-04-09-0900-S-0.15-0 | BBC | NA | NA | 1899-12-31 09:00:00 | NA | 0 | 0.15 | NA | NA | NA | NA | NA | 8.96 | 1 | 26 | S1 | 11 | NA | NA | NA | 10.0 | 1.022 | DO1 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 1 | NA | F | 1 | NA | SSE | NA | NA | 109E | NA | 109E | NA | 41.51578 | -71.07107 | Westport | Westport Rivers | Westport River East Outer | 09:00:00 | 1992-04-09 09:00:00 | buzzards |
Table 1. Buzzards Bay Water Quality Coalition example dataframe
Went on to exclude data based on quality control codes provided…
| code | description | include_exclude |
|---|---|---|
| 1 | centrigade recalculate from Fahrenheit | include |
| 2 | value needs to be reviewed for accuracy | exclude |
| 3 | value reviewed then confirmed, rerun, or revised | include |
| 4 | flagged as unusual value, with comments about possible reasons if known | exclude |
| 6 | substitute value with comment for details | exclude |
| 7 | data below method detection limit | exclude |
| 8 | value highly anomalous, although laboratory measurments were validated, sample may have been compromised during collection | exclude |
| 9 | value rejected | exclude |
| 10 | laboratory salinity substituded for missing field salinity | include |
| 11 | field salinity restored to original value for 1992-1995 for samples that had a correction factor applied at the time | include |
| 12 | time of D.O. collection too late for D.O. value to be used in buzzards bay coalition health index calculations | include |
Table 2. quality control codes excluded from dataset
Time Series
Fig. 1 Dissolved oxygen in mg/L across 4 sites w/in Buzzards Bay, demonstrating seasonal sampling
Chesapeak Bay: “eyes on the bay”
Water quality data from “Eyes on the Bay” data query portal. Data quality control was done prior to uploading on the eyes on the bay website. Data was converted to wide format and subsetted to surface water samples.
ches <- read.table("D:/Puritz_Lab/NSF/NSF_EnvironmentalData/data/waterquality_do_temp_sal_ph_chesapeakbay_Jan1990_Aug2022.txt",
sep="\t",header=T,
nrow=length(count.fields("D:/Puritz_Lab/NSF/NSF_EnvironmentalData/data/waterquality_do_temp_sal_ph_chesapeakbay_Jan1990_Aug2022.txt")) - 1,
na.strings = "NA", fill = TRUE) %>%
left_join(.,read.delim("D:/Puritz_Lab/NSF/NSF_EnvironmentalData/data/WaterQualityStationHUC8_ChesapeakebayDataHub.txt",
sep="\t",header=TRUE), by="Station") %>%
filter(Layer == "S ") %>%
mutate(row=row_number(),
time_stamp=as.POSIXct(paste(SampleDate,SampleTime,sep=" "),format="%m/%d/%Y %H:%M:%S"),
coalition="eyesonthebay") %>%
pivot_wider(.,names_from = Parameter, values_from = MeasureValue)
ches_curated <- ches %>%
select(stn_id=Station,
location=StationDescription,
town=CountyCity,
TEMP=WTEMP,
DO,
PH,
SAL=SALINITY,
depth=TotalDepth,
lat=Latitude.x,long=Longitude.x,
coalition,time_stamp)| HUC8.x | EventId | Cruise | Program | Project | Agency | Source | Station | SampleDate | SampleTime | TotalDepth | UpperPycnocline | LowerPycnocline | Depth | Layer | SampleType | SampleReplicateType | Qualifier | Unit | Method | Lab | Problem | PrecisionPC | BiasPC | Details | Latitude.x | Longitude.x | TierLevel | StationDescription | CBSeg2003 | CBSeg2003Description | CBSegmentShed2009 | CBSegmentShed2009Description | HUC8.y | HUC12 | FIPS | State | CountyCity | USGSGage | FallLine | Latitude.y | Longitude.y | UTMX | UTMY | LLDatum | row | time_stamp | coalition | DO | PH | SALINITY | WTEMP |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2050306 | 2296 | BAY438 | TWQM | MAIN | MDDNR | MDDNR | CB1.1 | 5/10/2006 | 12:38:00 | 5.0 | NA | NA | 0.5 | S | ISM | M1 | MG/L | F01 | NA | NA | 39.54794 | -76.08481 | T3 | Mouth Of Susquehanna River; Head Of Bay; Mid-Channel | CB1TF | Chesapeake Bay-Tidal Fresh Region | 2050306 | 20503061713 | 24025 | MD | Harford | NA | B | 39.54794 | -76.08481 | 406792 | 4378146 | NAD83 | 1 | 2006-05-10 12:38:00 | eyesonthebay | 8.7 | NA | NA | NA | ||||||
| 2050306 | 2640 | BAY439 | TWQM | MAIN | MDDNR | MDDNR | CB1.1 | 5/24/2006 | 13:00:00 | 6.0 | NA | NA | 0.5 | S | ISM | M1 | MG/L | F01 | NA | NA | 39.54794 | -76.08481 | T3 | Mouth Of Susquehanna River; Head Of Bay; Mid-Channel | CB1TF | Chesapeake Bay-Tidal Fresh Region | 2050306 | 20503061713 | 24025 | MD | Harford | NA | B | 39.54794 | -76.08481 | 406792 | 4378146 | NAD83 | 2 | 2006-05-24 13:00:00 | eyesonthebay | 8.6 | NA | NA | NA | ||||||
| 2050306 | 2918 | BAY440 | TWQM | MAIN | MDDNR | MDDNR | CB1.1 | 6/7/2006 | 10:02:00 | 7.4 | NA | NA | 0.5 | S | ISM | M1 | MG/L | F01 | NA | NA | 39.54794 | -76.08481 | T3 | Mouth Of Susquehanna River; Head Of Bay; Mid-Channel | CB1TF | Chesapeake Bay-Tidal Fresh Region | 2050306 | 20503061713 | 24025 | MD | Harford | NA | B | 39.54794 | -76.08481 | 406792 | 4378146 | NAD83 | 3 | 2006-06-07 10:02:00 | eyesonthebay | 6.6 | NA | NA | NA | ||||||
| 2050306 | 3288 | BAY441 | TWQM | MAIN | MDDNR | MDDNR | CB1.1 | 6/21/2006 | 10:36:00 | 7.0 | NA | NA | 0.5 | S | ISM | M1 | MG/L | F01 | NA | NA | 39.54794 | -76.08481 | T3 | Mouth Of Susquehanna River; Head Of Bay; Mid-Channel | CB1TF | Chesapeake Bay-Tidal Fresh Region | 2050306 | 20503061713 | 24025 | MD | Harford | NA | B | 39.54794 | -76.08481 | 406792 | 4378146 | NAD83 | 4 | 2006-06-21 10:36:00 | eyesonthebay | 7.4 | NA | NA | NA | ||||||
| 2050306 | 3437 | BAY441 | TWQM | TRIB | MDDNR | MDDNR | CB1.0 | 6/27/2006 | 11:58:00 | NA | NA | NA | 0.0 | S | ISM | M1 | MG/L | F01 | NA | NA | 39.65622 | -76.17504 | T3 | Susquehanna River At Conowingo Dam; Somewhat Downstream From The Dam Overflow. | NTGISS | NonTidal Station Outside of Segment Coverage | CB1TF | Chesapeake Bay-Tidal Fresh Region | 2050306 | 20503061713 | 24025 | MD | Harford | NA | A | 39.65622 | -76.17504 | 399196 | 4390261 | NAD83 | 5 | 2006-06-27 11:58:00 | eyesonthebay | 7.0 | NA | NA | NA | ||||
| 2050306 | 3671 | BAY442 | TWQM | TRIB | MDDNR | MDDNR | CB1.0 | 7/6/2006 | 09:15:00 | NA | NA | NA | 0.0 | S | ISM | M1 | MG/L | F01 | NA | NA | 39.65622 | -76.17504 | T3 | Susquehanna River At Conowingo Dam; Somewhat Downstream From The Dam Overflow. | NTGISS | NonTidal Station Outside of Segment Coverage | CB1TF | Chesapeake Bay-Tidal Fresh Region | 2050306 | 20503061713 | 24025 | MD | Harford | NA | A | 39.65622 | -76.17504 | 399196 | 4390261 | NAD83 | 6 | 2006-07-06 09:15:00 | eyesonthebay | 8.2 | NA | NA | NA |
Time Series
Fig.2 Dissolved oxygen in mg/L across across 4 sites
Water Quality Portal (WQP)
Data was queryed via https://www.waterqualitydata.us/
Search was narrowed based on state, county, site type == “estuary”, sample media == “Water (NWIS,STEWARDS,STORET)”, characteristic group == “Inorganic, Minor, Metals”, and data profiles == “Sample Results (narrow)”
variables <- c("Dissolved oxygen (DO)", "Oxygen", "Temperature, water", 'Salinity', "pH") # Measurments We Want
fairfield <- read.csv("D:/Puritz_Lab/NSF/NSF_EnvironmentalData/data/WQP_CT_middlesex.csv",na.strings=c("","NA")) %>%
filter(CharacteristicName %in% variables) %>%
mutate(time_stamp=as.POSIXct(ActivityStartDate,format="%Y-%M-%d"))
sites <- fairfield %>%
distinct(MonitoringLocationIdentifier,.keep_all = TRUE) %>%
group_by(MonitoringLocationIdentifier) %>%
dplyr::mutate(end_date=max(time_stamp)) %>%
#dplyr::filter(end_date >= "2015-01-01") %>%
dplyr::select(site=MonitoringLocationIdentifier,end_date)
siteLoadedList <- lapply(sites$site,function(i){whatWQPdata(siteid=i)}) # One line information pertaining to sites activity, location, ect.
sitemeta <- do.call(rbind,siteLoadedList) # combine into one df
# Place Curated datasets within subsets list
curated_fairfield <- fairfield %>%
dplyr::group_by(MonitoringLocationIdentifier) %>%
dplyr::mutate(row=dplyr::row_number(),
result = as.numeric(ResultMeasureValue),
datatype = gsub("([A-Za-z]+).*", "\\1",CharacteristicName),
time_stamp=as.POSIXct(ActivityStartDate,format="%Y-%M-%d")) %>%
# Remove unwanted units
filter(!(ResultMeasure.MeasureUnitCode == "ppth" || ResultMeasure.MeasureUnitCode == "deg F" || is.na(ResultMeasure.MeasureUnitCode) == TRUE || ResultMeasure.MeasureUnitCode == "%" || ResultMeasure.MeasureUnitCode == "ppm")) %>%
# Must subset or else pivot wider does not fully combine Results into one row but multiple in different columns
select(MonitoringLocationIdentifier,datatype,time_stamp,result) %>%
pivot_wider(.,names_from="datatype",values_from="result", values_fn = ~mean(.x, na.rm = TRUE)) %>%
# Get meta information
left_join(.,sitemeta,by="MonitoringLocationIdentifier") %>%
# Final DataFrame Structure
summarise(
stn_id=MonitoringLocationIdentifier,
location=MonitoringLocationName,
town=CountyName,
TEMP=as.numeric(Temperature),
DO=as.numeric(Dissolved),
PH=as.numeric(pH),
SAL=as.numeric(Salinity),
depth=NA,
lat=lat,
long=lon,
coalition="WQP",
time_stamp=time_stamp)| OrganizationIdentifier | OrganizationFormalName | ActivityIdentifier | ActivityStartDate | ActivityStartTime.Time | ActivityStartTime.TimeZoneCode | MonitoringLocationIdentifier | ResultIdentifier | DataLoggerLine | ResultDetectionConditionText | MethodSpecificationName | CharacteristicName | ResultSampleFractionText | ResultMeasureValue | ResultMeasure.MeasureUnitCode | MeasureQualifierCode | ResultStatusIdentifier | StatisticalBaseCode | ResultValueTypeName | ResultWeightBasisText | ResultTimeBasisText | ResultTemperatureBasisText | ResultParticleSizeBasisText | PrecisionValue | DataQuality.BiasValue | ConfidenceIntervalValue | UpperConfidenceLimitValue | LowerConfidenceLimitValue | ResultCommentText | USGSPCode | ResultDepthHeightMeasure.MeasureValue | ResultDepthHeightMeasure.MeasureUnitCode | ResultDepthAltitudeReferencePointText | ResultSamplingPointName | BiologicalIntentName | BiologicalIndividualIdentifier | SubjectTaxonomicName | UnidentifiedSpeciesIdentifier | SampleTissueAnatomyName | GroupSummaryCountWeight.MeasureValue | GroupSummaryCountWeight.MeasureUnitCode | CellFormName | CellShapeName | HabitName | VoltismName | TaxonomicPollutionTolerance | TaxonomicPollutionToleranceScaleText | TrophicLevelName | FunctionalFeedingGroupName | TaxonomicDetailsCitation.ResourceTitleName | TaxonomicDetailsCitation.ResourceCreatorName | TaxonomicDetailsCitation.ResourceSubjectText | TaxonomicDetailsCitation.ResourcePublisherName | TaxonomicDetailsCitation.ResourceDate | TaxonomicDetailsCitation.ResourceIdentifier | FrequencyClassInformationUrl | ResultAnalyticalMethod.MethodIdentifier | ResultAnalyticalMethod.MethodIdentifierContext | ResultAnalyticalMethod.MethodName | ResultAnalyticalMethod.MethodUrl | ResultAnalyticalMethod.MethodQualifierTypeName | MethodDescriptionText | LaboratoryName | AnalysisStartDate | AnalysisStartTime.Time | AnalysisStartTime.TimeZoneCode | AnalysisEndDate | AnalysisEndTime.Time | AnalysisEndTime.TimeZoneCode | ResultLaboratoryCommentCode | ResultLaboratoryCommentText | ResultDetectionQuantitationLimitUrl | LaboratoryAccreditationIndicator | LaboratoryAccreditationAuthorityName | TaxonomistAccreditationIndicator | TaxonomistAccreditationAuthorityName | LabSamplePreparationUrl | ProviderName | time_stamp |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| USGS-CT | USGS Connecticut Water Science Center | nwisma.01.99201246 | 1992-05-11 | 09:35:00 | EST | USGS-01193750 | NWIS-67971793 | NA | NA | NA | Temperature, water | NA | 11.5 | deg C | NA | Historical | NA | Actual | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 10 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NWIS | 1992-11-11 00:05:00 |
| USGS-CT | USGS Connecticut Water Science Center | nwisma.01.99201246 | 1992-05-11 | 09:35:00 | EST | USGS-01193750 | NWIS-67971800 | NA | NA | NA | Oxygen | Dissolved | 10.1 | mg/l | NA | Historical | NA | Actual | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 300 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NWIS | 1992-11-11 00:05:00 |
| USGS-CT | USGS Connecticut Water Science Center | nwisma.01.99201246 | 1992-05-11 | 09:35:00 | EST | USGS-01193750 | NWIS-67971801 | NA | NA | NA | Oxygen | Dissolved | 92 | % saturatn | NA | Historical | NA | Actual | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 301 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NWIS | 1992-11-11 00:05:00 |
| USGS-CT | USGS Connecticut Water Science Center | nwisma.01.99201246 | 1992-05-11 | 09:35:00 | EST | USGS-01193750 | NWIS-67971802 | NA | NA | NA | pH | Total | 7.3 | std units | NA | Historical | NA | Actual | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 400 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NWIS | 1992-11-11 00:05:00 |
| USGS-CT | USGS Connecticut Water Science Center | nwisma.01.99201246 | 1992-05-11 | 09:35:00 | EST | USGS-01193750 | NWIS-67971803 | NA | NA | NA | pH | Total | 7.4 | std units | NA | Historical | NA | Actual | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 403 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | EL006 | USGS | pH, lab, auto glass electrode | NA | NA | USGS TWRI 5-A1/1989, p 363 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NWIS | 1992-11-11 00:05:00 |
| USGS-CT | USGS Connecticut Water Science Center | nwisma.01.99200790 | 1992-03-13 | 10:00:00 | EST | USGS-01193750 | NWIS-67964038 | NA | NA | NA | Temperature, water | NA | 1.5 | deg C | NA | Historical | NA | Actual | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | 10 | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NA | NWIS | 1992-11-13 00:03:00 |
Fig.2 Dissolved oxygen in mg/L across across 4 sites
RIDOH
obtained dataset via emailing eherron@uri.edu. Values were obtained by taking the average across sampling depths and sampling times per day.
ri_parametercodes <- read_xlsx("D:/Puritz_Lab/NSF/NSF_EnvironmentalData/data/RIDOH_Supporting_Files.xlsx",sheet=2) %>%
mutate(ParameterName = gsub("\\ -.*","",ParameterName))
ri_sites <- read_xlsx("D:/Puritz_Lab/NSF/NSF_EnvironmentalData/data/RIDOH_Supporting_Files.xlsx",sheet=5)
ridoh <- read.delim("D:/Puritz_Lab/NSF/NSF_EnvironmentalData/data/RIDOH_1988_2020.csv",sep = ',') %>% # description - code; removing description
mutate(parameter_code = gsub(".* - ","",Parameter..),
date = mdy(Date.of.Sample),
time = hms(Time), # Some strings are missing time, only evaluated based date and avg across time for a given day
# time_stamp = mdy_hms(paste0(Date.of.Sample," ",Time)),
measured_value = as.numeric(Concentration),
id = row_number()) %>%
filter(parameter_code %in% c("00300","00400","00480","00011"), # dissolved oxygen = 00300, pH = 00400, salinity = 00480, temperature = 00011
!(Qualifier.Code %in% c("N","U","I","V")))
ridoh_curated <- ridoh %>%
group_by(date, WW.ID, parameter_code) %>% # pooled different depths and samples from the same day
summarise(avg_measuredvalue = mean(Concentration,na.rm=TRUE)) %>% # Took the average across these avg. depths and samples taken that day
pivot_wider(names_from=parameter_code, values_from = avg_measuredvalue, names_prefix = "env_") %>%
left_join(.,ri_sites, by=c("WW.ID"="WW_Station")) %>%
ungroup() %>%
summarise(
stn_id=WW.ID,
location=Site_DESCR,
town=Town,
TEMP=env_00011,
DO=env_00300,
PH=env_00400,
SAL=env_00480,
depth=NA,
lat=LAT_DD,
long=LON_DD,
coalition="ridoh",
time_stamp=date)| Source.Name | WW.ID | Date.of.Sample | Time | Sample.Type | Sample.Media | Depth | Parameter.. | Concentration | Unit | Qualifier.Code | Detection.Limit | Detection.Limit.Unit | Quantitation.Level | Quantitation.Level.Unit | Lab.Name | Analytical.Method.Number | Sediment.Particle.Size | Particle.Size.Unit | Fish.Sample.Type | Fish.Taxa | Comments | Monitoring.location | Watershed | Watershed.Code | MONITOR.1 | MONITOR.2 | parameter_code | date | time | measured_value | id |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| URIWW-1988-Lakes.csv | WW001 | 6/8/1988 | 4:30:00 PM | Grab | Water | 1 | Temperature - 00011 | 20.0 | C | 0 | C | 0 | C | URIWW | 2550: Temperature of Water by Thermometer | NA | NA | Alton Pond | Lower Wood River | WD | 00011 | 1988-06-08 | 4H 30M 0S | 20.0 | 10 | ||||||
| URIWW-1989-Lakes.csv | WW001 | 5/6/1989 | Grab | Water | 1 | pH - 00400 | 5.5 | S.U. | 1 | S.U. | 1 | S.U. | URIWW | 4500-H: pH in Water | NA | NA | Alton Pond | Lower Wood River | WD | 00400 | 1989-05-06 | NA | 5.5 | 22 | |||||||
| URIWW-1989-Lakes.csv | WW001 | 5/6/1989 | 11:00:00 AM | Grab | Water | 1 | Temperature - 00011 | 15.0 | C | 0 | C | 0 | C | URIWW | 2550: Temperature of Water by Thermometer | NA | NA | Alton Pond | Lower Wood River | WD | 00011 | 1989-05-06 | 11H 0M 0S | 15.0 | 26 | ||||||
| URIWW-1989-Lakes.csv | WW001 | 5/13/1989 | 11:00:00 AM | Grab | Water | 1 | Temperature - 00011 | 14.0 | C | 0 | C | 0 | C | URIWW | 2550: Temperature of Water by Thermometer | NA | NA | Alton Pond | Lower Wood River | WD | 00011 | 1989-05-13 | 11H 0M 0S | 14.0 | 30 | ||||||
| URIWW-1989-Lakes.csv | WW001 | 6/5/1989 | 2:00:00 PM | Grab | Water | 1 | Temperature - 00011 | 22.0 | C | 0 | C | 0 | C | URIWW | 2550: Temperature of Water by Thermometer | NA | NA | Alton Pond | Lower Wood River | WD | 00011 | 1989-06-05 | 2H 0M 0S | 22.0 | 36 | ||||||
| URIWW-1989-Lakes.csv | WW001 | 6/17/1989 | 1:00:00 PM | Grab | Water | 1 | Temperature - 00011 | 19.0 | C | 0 | C | 0 | C | URIWW | 2550: Temperature of Water by Thermometer | NA | NA | Alton Pond | Lower Wood River | WD | 00011 | 1989-06-17 | 1H 0M 0S | 19.0 | 41 |
Values Were exclude based on being tagged for the below qualifier codes. If a record is absent from a qualifier code that it was assumed to be reflect accurate reading.
qualifier_codes <- data.frame(
"Qualifier Code" = c("N","U","I","V","Z"),
"Description" = c("No Results (see comments)","Indicates that the compound was analyzed but not detected. This code
shall be used to indicate that the lab value reported is less than the Method Detection Limit and is reported for informational purposes.","Value reported is equal to or greater than the Method Detection Limit but less
than the Quantitation Level (Reporting Level). Data shall be deemed invalid.","Analyte was detected in both the sample and the associated method blank.","Actual value is greater than the value reported. For bacteria analyses: Too many colonies were present (too numerous to count TNTC - membrane filtration) or all wells positive (IDEXX). Other analyses - off scale."),
"include_exclude" = c("exclude","exclude","exclude","exclude","include"))
qualifier_codes %>% kbl(caption = "A Tale of Exclusion") %>% kable_classic(full_width=F)| Qualifier.Code | Description | include_exclude |
|---|---|---|
| N | No Results (see comments) | exclude |
| U | Indicates that the compound was analyzed but not detected. This code shall be used to indicate that the lab value reported is less than the Method Detection Limit and is reported for informational purposes. | exclude |
| I | Value reported is equal to or greater than the Method Detection Limit but less than the Quantitation Level (Reporting Level). Data shall be deemed invalid. | exclude |
| V | Analyte was detected in both the sample and the associated method blank. | exclude |
| Z | Actual value is greater than the value reported. For bacteria analyses: Too many colonies were present (too numerous to count TNTC - membrane filtration) or all wells positive (IDEXX). Other analyses - off scale. | include |
Water Treatment Facilities
obtained from epa data portal. Points of National Pollutant Discharge Elimination System (NPDES) and Permit Compliance Systems (PCS) programs as reported in Integrated Compliance Information Systems (ICIS). This dataset is updated weekly.
included only public facilities