Document last updated 2021-06-22 17:05:28 by Benjamin Meyer ()


Question: what kind of groundwater data exists in the EQA WQX within the Kenai River Watershed?

Import data

Import data from local csv files. Notes on file downloads:

  • These data are downloaded from the WQX website, www.waterqualitydata.us

  • Parameter result data and location data are provided as separate files. This script joins and matches the two.

# import data from EPA repo

# source data from most recent download
## specify download date
download_date <- "20210224"


# import sample data
dat <- read_csv(paste0("data/WQX_downloads/downloads/download_",download_date,"/narrowresult.csv"))

# import site data
site_dat <- read_csv(paste0("data/WQX_downloads/downloads/download_",download_date,"/station.csv"))

# join site and sample data
dat <- left_join(dat,site_dat) %>%
  # remove empty columns
  select_if(~!(all(is.na(.))))


What kind of sites are present in our data set?

unique(dat$MonitoringLocationTypeName)
## [1] "River/Stream"                    "Lake"                           
## [3] "River/Stream Perennial"          "BEACH Program Site-Ocean"       
## [5] "BEACH Program Site-River/Stream" "Lake, Reservoir, Impoundment"   
## [7] "Well"                            "Stream"                         
## [9] "Spring"


Retain ground water sites only.

# create and apply filter
gw <- "Well"

dat <- dat %>%
  filter(MonitoringLocationTypeName %in% gw)


Exploratory Data Analysis for Groundwater

How many years of data do we have?

dat %>%
  summarise(min_date = min(ActivityStartDate),
            max_date = max(ActivityStartDate))
## # A tibble: 1 x 2
##   min_date   max_date  
##   <date>     <date>    
## 1 1955-04-08 1999-09-21


How many different kinds of substances have been measured ?

dat %>%
  distinct(CharacteristicName) %>%
  count()
## # A tibble: 1 x 1
##       n
##   <int>
## 1   218


What are all the different substances measured?

z <- dat %>%
  distinct(CharacteristicName)


paged_table(z)


How many different sites?

# create summary table
gw_tbl <- dat %>%
  group_by(OrganizationFormalName,MonitoringLocationName,LatitudeMeasure,LongitudeMeasure) %>%
  summarise(min_date = min(ActivityStartDate),
            max_date = max(ActivityStartDate),
            parameters_measured = n()) %>%
  rename("latitude" = "LatitudeMeasure",
         "longitude" = "LongitudeMeasure") %>%
  mutate(data_type = "Groundwater Data", .before = "OrganizationFormalName")

nrow(gw_tbl)
## [1] 206


Export a table of start and end date of monitoring data for all sites, all parameters

# export csv
write.csv(gw_tbl,"data/sampling_sites/groundwater_sample_sites.csv", row.names = F)


How many sites include the parameter “Depth, from ground surface to well water level” ?

tbl <- dat %>%
  filter(CharacteristicName == "Depth, from ground surface to well water level")

paged_table(tbl)


Export csv with sites that contain groundwater depth data

write.csv(tbl, "data/sampling_sites/historical_groundwater_data_sites.csv")

Show a map that includes only those historical groundwater monitoring sites that include water depth data.

#
leaflet(data = tbl) %>% 
  addTiles() %>%
  addMarkers(~LongitudeMeasure, 
             ~LatitudeMeasure, 
             popup = ~as.character(MonitoringLocationIdentifier))



This script titled “groundwater_data.Rmd” is available in a GitHub repository at https://github.com/Kenai-Watershed-Forum/KRBWQM