# Import volunteer chemical monitoring data collected by IOWATER volunteers
# Data dump was provided by Steve Konrady on 2018-02-16
# And contains data uploaded to the IOWATER database thru 2017-09-17, when the database was shut down
# Site and data tables were combined using VLOOKUP in Excel
# Cleanup of text comments in numeric fields was done manually with search and replace in Excel
# This version is limited to sites within the South Skunk River basin (HUC 07080105)
library(tidyverse)
library(lubridate)
iowater_data <- read_csv("data/IOWATER_07080105.csv", 
                             col_types = cols(Adults = col_skip(), 
                                              Air_Temp_Fahrenheit = col_double(), 
                                              Avg_Ecoli100 = col_integer(), Avg_General_Coliform100 = col_skip(), 
                                              Children = col_skip(), Chloride = col_double(), 
                                              DateMonitored = col_date(format = "%m/%d/%Y"), 
                                              DateTimeMonitored = col_skip(), Day = col_skip(), 
                                              Dissolved_oxygen = col_double(), 
                                              HUC12 = col_character(), ID = col_skip(), 
                                              Incubation_Temp_Fahr = col_skip(), 
                                              Incubation_period = col_skip(), Monitor_id = col_character(), 
                                              Month = col_skip(), NitrateN = col_double(), 
                                              NitriteN = col_double(), `OBJECTID *` = col_skip(), 
                                              Phosphate = col_double(), Precip_24_hours = col_double(), 
                                              Rep1_Ecoli = col_skip(), Rep1_Ecoli100 = col_skip(), 
                                              Rep1_General_Coliform = col_skip(), 
                                              Rep1_General_Coliform100 = col_skip(), 
                                              Rep1_Total_Coliform100 = col_skip(), 
                                              Rep2_Ecoli = col_skip(), Rep2_Ecoli100 = col_skip(), 
                                              Rep2_General_Coliform = col_skip(), 
                                              Rep2_General_Coliform100 = col_skip(), 
                                              Rep2_Total_Coliform100 = col_skip(), 
                                              Rep3_Ecoli = col_skip(), Rep3_Ecoli100 = col_skip(), 
                                              Rep3_General_Coliform = col_skip(), 
                                              Rep3_General_Coliform100 = col_skip(), 
                                              Rep3_Total_Coliform100 = col_skip(), 
                                              `Site_no *` = col_character(), TimeMonitored = col_time(format = "%H%M"), 
                                              Weather_Calm = col_skip(), Weather_Cloudy = col_skip(), 
                                              Weather_Partly_Sunny = col_skip(), 
                                              Weather_Rain_Snow = col_logical(), 
                                              Weather_Sunny = col_skip(), Weather_Windy = col_skip(), 
                                              Year = col_skip(), Water_Temp_Fahrenheit = col_integer(),
                                              Transparency = col_integer(), pH = col_integer())) %>%
  rename(Site_no = `Site_no *`) %>%
  mutate(Date = DateMonitored,
          Year = year(Date), Month = month(Date, label = TRUE),
         Week = week(Date), Day = wday(Date, label = TRUE)) %>%
  filter(County == "Story")

Volunteers with the Iowater program tested water quality at 92 sites in Story County between 2000 and 2017, when the program was ended. This table shows how many times each site was tested.

# Summarise data available by year, and total across years
# This is useful for a table, but not a graph
iowater_summary <- iowater_data %>%
  arrange(DateMonitored) %>%
  group_by(Year, Site_no, SiteName) %>%
  summarise(n = n()) %>%
  pivot_wider(names_from = Year, values_from = n, names_prefix = "y") %>%
  ungroup()
iowater_summary <- iowater_summary %>%
  mutate_all(~replace(., is.na(.), 0)) %>%
    mutate(y2000_2017 = rowSums(select_if(iowater_summary, is.numeric), na.rm = TRUE)) %>%
  arrange(desc(y2000_2017))
iowater_summary %>% select(Site_no, SiteName, y2000_2017)

Much of the effort was focused on the Squaw Creek watershed. Some sites were tested monthly but most were tested at twice yearly “water quality snapshot” events. The frequency of sampling generally tapered off over time.

# An overview of timing (month and years) for the whole record
filter(iowater_data, County == "Story", HUC12 %in% c(70801050301:70801050307)) %>%
  ggplot(aes(x = Date, y = SiteName, color = Month)) +
  scale_x_date(date_breaks = "4 year", date_minor_breaks =  "1 year", date_labels = "%Y") +
  geom_point() +
  theme_light() +
  theme(legend.position = "bottom") +
  labs(y = "Site", x = "Week of Year", 
       title = "Timing of Iowater sampling", subtitle = "Squaw Creek watershed")

However, other sites in Story County were also tested. The upper south Skunk and several tributaries were tested on a monthly basis from 20020. So there is data avaialble, but challenging to put it on a common basis, as water quality varied year to year and not all sites were sampled during hte same years, or same months of the year.

# An overview of timing (month and years) for the whole record
filter(iowater_data, County == "Story", !(HUC12 %in% c(70801050301:70801050307))) %>%
  ggplot(aes(x = Date, y = SiteName, color = Month)) +
  geom_point() +
  theme_light() +
  scale_x_date(date_breaks = "4 year", date_minor_breaks =  "1 year", date_labels = "%Y") +
  theme(legend.position = "bottom") +
  labs(y = "Site", x = "Year", 
       title = "Timing of Iowater sampling", subtitle = "Other watersheds")

Sites that were sampled monthly were generally not sampled on the same week or day, unless the same person monitored two sites.

# Show timing of samples within single year
filter(iowater_data, County == "Story", Year == 2002) %>%
  ggplot(aes(x = Week, y = SiteName, color = Day)) +
  geom_point() +
  theme_light() +
  theme(legend.position = "bottom") +
  scale_x_continuous(minor_breaks = seq(0,52, 1)) +
  labs(y = "Site", x = "Week of Year", 
       title = "Timing of sampling, 2008", subtitle = "Iowater sites in Story County")