R Notebook - RANN Lakes northern lower pennisula of Michigan - 1973-1977

library(tidyverse)
library(readxl)
library(tidyr)
library(dplyr)
library(hexbin)
library(RSQLite)
library(dataMaid)
library(janitor)
library(purrr)
library(readr)

#the orginal dataset contained catagorical values in a coded value system. These operations below add the correct names to the codes for future easy of use by users

Physiochemical_parameters <- data.frame("Para." = c("1", "2", "3" ,
"4", "5", "6", "7" , "8", "9", "10", "11" , "12", "13", "14" , "15", "16", "17", 
"18", "19", "20") , phyChem_parameter_Description= c("Secchi Depth and Color (meters)", "Temperature (celsius)" , "Light Temperature (% TRN)" , "Turbidity (PPM Mg/L)" , "pH" , "Dissolved Oxygen (% SAT)" , "Alkalinity (Mg/L)" , "Conductivity (UMHOS/cm)" , "Calcium (Mg/L)" , "Magnesium (PPM Mg/L)" , "Potassium (PPM Mg/L)" , "Sodium (PPM Mg/L)" , "Chloride (PPM Mg/L)" , "Chlorophyll a (PPB)" , "SiO2 (PPB)" , "Soluble (PO4)" , 
"Total PO4 (PPB)" , "NO3-N (PPB)" , "NH3-N (PPB)" , "Phaeopigments (PPB)") , stringsAsFactors = FALSE) %>% 
  mutate(Para. = as.numeric(Para.))


Morphological_Parameters <- data.frame("Para._Morpho." = c("1" , "2" , "3" , "4" , "5" , "6" , "7" , "8" , "9" , "10 " , "11" , "12") , Morphological_parameter_description = c("Maximum Depth" , "Mean Depth" , "Maximum Length" , "Maximum Width" , "Mean Width" , "Shoreline Length" , "Surface Area" , "Watershed Area" , "Volume" , "Shoreline Development Factor" , "Volume Development Factor" , "Watershed Area/Surface Area Ration")) %>% 
  mutate(Para._Morpho. = as.numeric(Para._Morpho.))


Lake_key <- data.frame(Lake= c("1" , "2" , "3" , "4" , "5" , "6" , "7" , "8" , "9" , "10" , "11" , "12" , "13" , "14" , "15" , "16" , "17" , "18" , "19" , " 20" , "21" , "22" ,"23" , "24" , "25" , "26" , "27" , "28" , "29" , "30" , "31" , "32" , "33" , "34" , "35" , "36" , "37" , "38" , "39" , "40" , "41" , "42" , "43" , "44" , "45" , "46" , "47" , "48" , "49") , Lake_name = c("Arnott" , "Black" , "Bryant's Bog" , "Burt" , "Carp" , "Charlevoix" , "Cochran" , "Cornwall Creek" , "Crooked" , "Deveraux" , "Dog" ,"Alverno Pond" , "French Farm" , "Hackett" , "Hoop" , "Kleber Pond" , "Larks", "Lancaster" , "Lance" , "Livingston Bog" , "Long" , "McLavey" , "Mud Lake Bog" , "Mud (Emmet Co.)" ,  "Spring" , "Mullett" , "Munro" , "Osmun" , "Pickerel" , "Roberts" , "Round" , "Silver-(Cheboygan Co.)" , "Silver (BSA)" , "Twin - A" , "Twin - B", "Twin - C" , "Twin - D" , "Twin - E" , "Twi n- F" , "Twin - G" , "Twin (Isolated)" , "Vincent" , "Walloon" , "Weber" , "Wildwood" , "Wycamp" , "Stoney Creek" , "Twin (Total)" , "Douglas")) %>% 
  mutate(Lake = as.numeric(Lake))
Lake_key

Season_key <- data.frame(Seas.= c("1" , "2" , "3" , "4" , "5" , "6" , "7" ,"8" , "9" , "10") , Season_and_year = c("Fall_1972" , "Winter_1973" , "Spring_1973" , "Summer_1973" , "Fall_1973" , "Winter_1974" , "Spring_1974" , "Summer_1974" , "Fall_1974" , "Winter_1975")) %>% 
  mutate(Seas. = as.numeric(Seas.))

write_csv(Physiochemical_parameters, "../physiochem_parameters.csv")
write_csv(Morphological_Parameters, "../morphological_parameters.csv")
write_csv(Lake_key, "../lake_key.csv")
write_csv(Season_key, "../season_key.csv")

Seas_column <- function(x) {x %>%  inner_join(Season_key)}
Lake_column <- function(x) {x %>% inner_join(Lake_key)}
phychem_column <- function(x) {x %>% inner_join(Physiochemical_parameters)}
renaming <- function(x) {rename(x, Lake_code = Lake , Season_code = Seas. , Depth_meters = Depth , Phys_chem_parameter_code = Para. , Station = Stat.)}
arranging <- function(x) {select(x, Lake_code, Lake_name, Season_code, Season_and_year, Phys_chem_parameter_code, phyChem_parameter_Description, Value, Depth_meters)}
ordering <- function(x) {arrange(x, Season_code)}

#creates a list of files to run through upcoming next map:reduce operation
test <- list.files("~/Desktop/RANNLakes/Master_lake_list_Douglas/", pattern = 'xlsx')

#map:reduce loops over the list of files in the directory and binds the files together as long as they have the same data structure
master_data <- test %>%
  map(read_excel) %>%
  reduce(rbind)

Warning: Expecting numeric in G1887 / R1887C7: got '0.01?'

master_data

#uses functions created above to join categorical variables with code values, rename and reorder variables in final table
RANNMasterlist <- Seas_column(master_data) %>% 
  Lake_column() %>% 
  phychem_column() %>% 
  renaming() %>% 
  arranging() %>% 
  ordering()

Joining, by = "Seas."Joining, by = "Lake"Joining, by = "Para."

#print aggregated data
RANNMasterlist 

#write data to csv
write_csv(RANNMasterlist, "../RANNLakes_allYears_allSites.csv")