library(readxl)
library(stringi)
library(tidyr)

#Import each sheet
df1 <- read_excel('/home/treeder/SportFish_132/knb.92123.1.xlsx', sheet = 1, skip = 7)    
df2 <- read_excel('/home/treeder/SportFish_132/knb.92123.1.xlsx', sheet = 2, skip = 6) 
df3 <- read_excel('/home/treeder/SportFish_132/knb.92123.1.xlsx', sheet = 3, skip = 4) 
sheet4 <- read_excel('/home/treeder/SportFish_132/knb.92123.1.xlsx', sheet = 4) 

#Convert to data frames
Harvest <- data.frame(df1)
Catch <- data.frame(df2)
Sites <- data.frame(df3)

#Rename columns in harvest
x <- Harvest %>%
    rename(harvest_KS = KS, harvest_SS = SS, harvest_RS = RS, harvest_PS = PS, harvest_CS = CS, resp_KS = resp_ks, resp_SS = resp_ss, resp_RS = resp_rs, resp_PS = resp_ps, resp_CS = resp_cs)

#Gather separate and spread (Harvest)
x <- gather(x, c(-year, -SASAP_region, -region_name, -RESTYPE, -guipri, -TYPE, -DF, -FISH_TOTAL), key = "key", value = "number") 
x<- separate(x, key, into = c("harvest", "species"))
x <- spread(x, key = "harvest", value = "number")
Harvest <- x

#Rename columns in catch
x <- Catch %>%
    rename(catch_KS = KS, catch_SS = SS, catch_RS = RS, catch_PS = PS, catch_CS = CS, resp_KS = resp_ks, resp_SS = resp_ss, resp_RS = resp_rs, resp_PS = resp_ps, resp_CS = resp_cs)

#Gather separate and spread (Catch)
x <- gather(x, c(-year, -SASAP_region, -region_name, -RESTYPE, -guipri, -TYPE, -DF, -FISH_TOTAL), key = "key", value = "number") 
x<- separate(x, key, into = c("catch", "species"))
x <- spread(x, key = "catch", value = "number")
Catch <- x

#rename res column
Harvest <- Harvest %>%
    rename(num_responses = resp)
Catch <- Catch %>%
    rename(num_responses = resp)

#Change species abbreviations to species names
#Harvest
species1 <- gsub('KS','chinook', Harvest$species)
species1 <- gsub('SS','coho', species1)    
species1 <- gsub('RS','sockeye', species1) 
species1 <- gsub('PS','pink', species1) 
species1 <- gsub('CS','chum', species1) 
Harvest$species <- species1

#Catch
species2 <- gsub('KS','chinook', Catch$species)
species2 <- gsub('SS','coho', species2)    
species2 <- gsub('RS','sockeye', species2) 
species2 <- gsub('PS','pink', species2) 
species2 <- gsub('CS','chum', species2) 
Catch$species <- species2

#write csvs
write.csv(Harvest, "/home/treeder/SportFish_132/harvestEstimates.csv", row.names=F)
write.csv(Catch, "/home/treeder/SportFish_132/catchEstimates.csv", row.names=F)
write.csv(Sites, "/home/treeder/SportFish_132/sitesAllocation.csv", row.names=F)