# 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 Squaw Creek watershed
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(HUC12 %in% c(70801050301:70801050307))

Number of samples collected by site from 2000-2017

# 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)
# Selects a site of interest and a reference site for comparison
# Here Squaw Creek at South Duff and Squaw Creek in Hamilton County
site_a <- 985040
site_ref <- 940010
sites_comp <- c(site_a, site_ref)

MatchingDates<- iowater_data %>%
  group_by(Date) %>%
  filter(site_a %in% Site_no & site_ref %in% Site_no) %>%
  ungroup() %>%
  select(Date) %>%
  distinct()

MatchingMonth<- iowater_data %>%
  group_by(Year, Month) %>%
  filter(site_a %in% Site_no & site_ref %in% Site_no) %>%
  ungroup() %>%
  select(Date) %>%
  distinct()

The upstream sites were not sampled on the same schedule as downstream sites.

# An overview of timing (month and years) for the whole record
filter(iowater_data, Site_no %in% sites_comp) %>%
  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")

This meant that some of the comparisons made in the Squaw Creek Management Plan are incorrect. For the months when both sites were measured, nitrate was actually the same or higher at the Hamilton County site, not lower as reported in the Management Plan.

filter(iowater_data, Site_no %in% sites_comp) %>%
  ggplot(aes(x = SiteName, y = NitrateN, color = SiteName)) +
  geom_boxplot() +
    stat_summary(fun=mean, geom="point", shape=24, size=4, 
               aes(group = SiteName), color = "black", position = position_dodge2(1)) +
  labs(title = "Comparison of sites, all dates", y = "Nitrate (mg/L)") +
   theme(legend.position = "bottom", axis.title.x = element_blank(), axis.text.x = element_blank(),
         axis.ticks.x = element_blank())

filter(iowater_data, Site_no %in% sites_comp, as.Date(Date) %in% MatchingMonth$Date) %>%
  ggplot(aes(x = SiteName, y = NitrateN, color = SiteName)) +
  geom_boxplot() +
    stat_summary(fun=mean, geom="point", shape=24, size=4, 
               aes(group = SiteName), color = "black", position = position_dodge2(1)) +
  labs(title = "Comparison of sites, data from same months and years", y = "Nitrate (mg/L)") +
   theme(legend.position = "bottom", axis.title.x = element_blank(), axis.text.x = element_blank(),
         axis.ticks.x = element_blank())