This report is automatically generated with the R package knitr (version 1.39) .

---
title: "01_Tributaries"
author: "Mercury Program and Basin Planning Unit"
date: '2022-07-08'
output:
  html_document:
    code_folding: show
    toc: TRUE
    toc_float: TRUE
    toc_depth: 3
runtime: shiny
assets:
  css:
    - "http://fonts.googleapis.com/css?family=Raleway:300"
    - "http://fonts.googleapis.com/css?family=Oxygen"
---

<style>
body{
  font-family: 'Oxygen', sans-serif;
  font-size: 16px;
  line-height: 24px;
}

h1,h2,h3,h4 {
  font-family: 'Raleway', sans-serif;
}

.container { width: 1250px; }
h3 {
  background-color: #D4DAEC;
  text-indent: 50px; 
}
h4 {
  text-indent: 75px;
  margin-top: 35px;
  margin-bottom: 5px;
}
</style>

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo=TRUE, warning=FALSE, message=FALSE, fig.width=9.5)
```


```{r Libraries, echo=FALSE}
library(shiny)

# Had issue with runtime:shiny resetting the WD of rproj so used getActiveProject()
wd <- rstudioapi::getActiveProject()

source(paste0(wd, "/R Functions/functions_estimate NDDNQ values.R"))
source(paste0(wd, "/R Functions/functions_QA data.R"))
```

# Load and Tidy Data
## MeHg: Tributary Data

###Define columns variable for repeat use later
```{r}
# # Columns to select
# columns_select <- c("StationCode", "StationName", "Tributary", "SampleDate", "SampleTime", "Unit", "Result", "MDL", "RL", "ResultQualCode", "MeHgDataSource", "SourceRow", "SampleMonth", "SampleYear", "WaterYear")
```

###Load data
```{r}
trib_data <- readxl::read_xlsx(paste0(wd, "/Reeval_Source_Analysis/Source Data/01_Tributaries/01_Tributaries_MeHg.xlsx"), sheet = "All_MeHg") %>% 
  chara_to_NumDate
```

###Tidy data 

```{r}
trib_clean_format <- trib_data %>%
  #Clean up dates
  mutate(SampleDate = as.Date(SampleDate, origin = "1899-12-30"))
```


###Find Daily MeHg Median and Mean Concentrations by Tributary

```{r}
trib_dailymedian <- trib_clean_format %>%  
  group_by(Tributary, SampleDate) %>% 
  mutate(DailyMedian = median(Result, na.rm=T), DailyMean = mean(Result, na.rm=T)) %>% 
  distinct(Tributary, DailyMedian, .keep_all = TRUE) %>% 
  select(Tributary, DailyMedian, DailyMean, Unit, SampleMonth)
```

###Create CCSB OUtflow and Weir Data Frame

```{r}
ccsb <- trib_dailymedian %>% 
  filter(Tributary %in% c("Cache Creek Settling Basin Outflow", "Cache Creek Settling Basin Weir")) %>%
  mutate(Month = month(SampleDate))

```

###Find CCSB Monthly Median and Mean Concentrations

```{r}
ccsb_median <- ccsb %>% 
  group_by(Tributary, Month) %>%
  summarise(MonthlyMedian = median(DailyMedian, na.rm=T),
            MonthlyMean = mean(DailyMean, na.rm=T),
            N = n(),
            MonthlyMax = max(DailyMedian, na.rm=T),
            MonthlyMin = min(DailyMedian, na.rm=T))

```

###Create Tributary Data Frame, no CCSB

```{r}
trib_noCCSB <- trib_dailymedian %>% 
  filter(Tributary %are not% c("Cache Creek Settling Basin Outflow", "Cache Creek Settling Basin Weir"))
```

###Find Tributary Monthly MeHg Median and Mean Concentrations for all but CCSB

```{r}
trib_monthlymedian <- trib_noCCSB %>%  
  group_by(Tributary, SampleMonth) %>% 
  summarise(TributaryMedian = median(DailyMedian, na.rm=T),
            TributaryMean = mean(DailyMean, na.rm=T),
            N = n(),
            TributaryMax = max(DailyMedian, na.rm=T),
            TributaryMin = min(DailyMedian, na.rm=T))
```

###Find Tributary MeHg Median and Mean Concentrations

```{r}
trib_median <- trib_dailymedian %>%  
  group_by(Tributary) %>% 
  summarise(TributaryMedian = median(DailyMedian, na.rm=T),
            TributaryMean = mean(DailyMean, na.rm=T),
            N = n(),
            TributaryMax = max(DailyMedian, na.rm=T),
            TributaryMin = min(DailyMedian, na.rm=T))
```

### Export CCSB to excel
```{r}
writexl::write_xlsx(ccsb_median, paste0(wd, '/Reeval_Source_Analysis/Source Data/01_Tributaries/Output/01_CCSB Monthly MeHg Median Concentration', today(), '.xlsx'))
```

### Export Tributaries to excel
```{r}
# Save MeHg Data Used for Trib Calculations
trib_data_save <- trib_clean_format %>%
  filter(!is.na(Tributary) & !grepl("Settling Basin Outflow|Settling Basin Weir", Tributary)) %>% # exclude unlabeled tributaries and outflows of CCSB
  # rename(ReferenceCode = Project) %>% 
  mutate(SourceID = case_when(grepl("CEDEN", SourceID) ~ "CEDEN",
                              T ~ SourceID),
         ReferenceCode = case_when(SourceID == "R5AQ" ~ Project,
                                   SourceID == "AR" ~ CitationCode,
                                   T ~ NA_character_),
         ) %>% 
  select(Tributary, SourceID, ReferenceCode, StationCode, StationName, SampleDate, Unit, Result, MDL, RL, ResultQualCode) %>%
  arrange(Tributary, SampleDate)


writexl::write_xlsx(list("6.2.1 Tributary Inflows"=trib_data_save),
                         paste0(wd, '/Reeval_Source_Analysis/Source Data/01_Tributaries/Output/Appdx A_Data Compilation_Tributaries.xlsx'))

# Save MeHg Data Used for CCSB Calculations
ccsb_data_save <- trib_clean_format %>%
  filter(grepl("Settling Basin Outflow|Settling Basin Weir", Tributary)) %>% # use only outflows of CCSB
  select(Tributary, SourceID, Project, StationCode, StationName, SampleDate, Unit, Result, MDL, RL, ResultQualCode) %>%
  rename(ReferenceCode = Project) %>% 
  mutate(ReferenceCode = case_when(ReferenceCode == "0" ~ NA_character_,
                                   SourceID == "R5AQ" ~ ReferenceCode,
                                   T ~ NA_character_),
         ) %>%
  arrange(Tributary, SampleDate)


writexl::write_xlsx(list("6.3.7 CCSB"=ccsb_data_save),
                         paste0(wd, '/Reeval_Source_Analysis/Source Data/01_Tributaries/Output/Appdx A_Data Compilation_CCSB.xlsx'))


# output tributary monthly median/mean concentration
writexl::write_xlsx(trib_monthlymedian, paste0(wd, '/Reeval_Source_Analysis/Source Data/01_Tributaries/Output/01_Tributaries Monthly MeHg Median Concentration', today(), '.xlsx'))

# output tributary pooled median/mean concentration
 writexl::write_xlsx(trib_median, paste0(wd, '/Reeval_Source_Analysis/Source Data/01_Tributaries/Output/01_Tributaries MeHg Median Concentration', today(), '.xlsx'))
```
## Error: <text>:18:1: unexpected '<'
## 17: 
## 18: <
##     ^

The R session information (including the OS info, R version and all packages used):

    sessionInfo()
## R version 4.2.2 (2022-10-31 ucrt)
## Platform: x86_64-w64-mingw32/x64 (64-bit)
## Running under: Windows 10 x64 (build 22621)
## 
## Matrix products: default
## 
## locale:
## [1] LC_COLLATE=English_United States.utf8  LC_CTYPE=English_United States.utf8   
## [3] LC_MONETARY=English_United States.utf8 LC_NUMERIC=C                          
## [5] LC_TIME=English_United States.utf8    
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## loaded via a namespace (and not attached):
##  [1] rstudioapi_0.13    knitr_1.39         magrittr_2.0.3     tidyselect_1.1.2  
##  [5] R6_2.5.1           rlang_1.0.2        fastmap_1.1.0      fansi_1.0.3       
##  [9] stringr_1.4.0      highr_0.9          dplyr_1.0.9        tools_4.2.2       
## [13] xfun_0.31          utf8_1.2.2         DBI_1.1.2          cli_3.3.0         
## [17] htmltools_0.5.2    ellipsis_0.3.2     assertthat_0.2.1   yaml_2.3.5        
## [21] readxl_1.4.0       digest_0.6.29      tibble_3.1.7       lifecycle_1.0.1   
## [25] crayon_1.5.1       RColorBrewer_1.1-3 purrr_0.3.4        vctrs_0.4.1       
## [29] glue_1.6.2         evaluate_0.15      rmarkdown_2.14     stringi_1.7.6     
## [33] compiler_4.2.2     pillar_1.7.0       cellranger_1.1.0   generics_0.1.2    
## [37] writexl_1.4.0      pkgconfig_2.0.3
    Sys.time()
## [1] "2023-12-26 16:03:57 PST"