Calculating food avaiability

Importing data

library(readxl)
library(dplyr)
library(tidyr)
library(aqm)
library(ggplot2)
library(DT)

## Pull out a vector with the core identifiers

f<-function(sheet= "SWNR Measurements Sept 2018"){
core<- as.vector(t(read_excel("LGI September 2018 Abundance and Biometric Data - reformated.xlsx", 
    sheet = sheet, 
    col_names = FALSE, skip = 2, n_max = 1)[,-1]))
## Pull out a vector with the type of core identifiers 

type<-as.vector(t(read_excel("LGI September 2018 Abundance and Biometric Data - reformated.xlsx", 
    sheet = sheet, 
    col_names = FALSE, skip = 3, n_max = 1)[,-1]))

d <- read_excel("LGI September 2018 Abundance and Biometric Data - reformated.xlsx", 
    sheet = sheet, 
    skip = 4)

names(d)<-tolower(names(d)) ## Change all to lower case
names(d)<-gsub("[^\\p{L}']+", " ", names(d), perl = TRUE) ## Strip anything that's not a letter or a space.
names(d)<- gsub(" $","", names(d), perl=T) ## Strip trailing spaces
names(d)<- gsub(" ","_", names(d), perl=T) ## Underscore for space

## Now paste the core ids onto all the names except the first one

names(d)[-1]<-paste(names(d)[-1],type,core,sep="-")

d %>% pivot_longer(cols=c(-1), values_drop_na=TRUE) %>% separate(name, "-", into = c("Taxon","Type","Core")) %>% separate(size_class, "-",into=c("Lower","Higher"),remove=TRUE) %>% mutate (Lower=aqm::clean(Lower), Higher=aqm::clean(Higher), Mid_class=round((Lower+Higher)/2, 1)) -> d
d
}

d1<-f("MF Measurements Sept 2018")
d1$site<-"MF"
d2<-f("SFF Measurements Sept 2018")
d2$site<-"SFF"
d3<-f("SWNR Measurements Sept 2018")
d3$site<-"SWNR"

d<-rbind(d1,d2,d3)


d %>% filter(Type !="A") %>% group_by(site,Taxon,Core,Lower,Higher,Mid_class) %>% summarise(value=sum(value)) -> d

taxa <- read_excel("London Gateway Invertebrate taxa.xlsx")
names(taxa)[2]<-"Taxon_group"
taxa$Taxon<-tolower(taxa$Taxon)
taxa$Taxon<-gsub("\\(", "", taxa$Taxon)
taxa$Taxon<-gsub("\\)", "", taxa$Taxon)
taxa$Taxon<-gsub(" ", "_", taxa$Taxon)

d$Taxon<-gsub("cappitellidae","capitellidae",d$Taxon )
# unique(d$Taxon) %in% taxa$Taxon
d %>% left_join(taxa, by = "Taxon") -> d 

## Join to add the site area in km2 

sites<-data.frame(site=c("MF","SWNR","SFF"),area=c(2.71, 0.41,0.56))
d %>% left_join(sites, by = "site") -> d

## Warning: Column `site` joining character vector and factor, coercing into
## character vector

conversion<-function(tg,x){
  if(tg=="Mollusc") afdm<-exp(-3.8521+2.5525*log(x)+0.5*0.14)
  if(tg=="Worm") afdm<-exp(-4.8+1.88*log(x)+0.5*0.175)
  if(tg=="Crustacea") afdm<-exp(-5.2531+2.6753*log(x)+0.5*0.0787)
  afdm
}
  
## Use the function to find the afdm of each size class for each taxon group
d$afdm<-unlist(mapply(conversion, d$Taxon_group, d$Mid_class))

## Change the value column to be the density in numbers per m2

d %>% mutate(density=round(value/ 0.003,0)) -> d 

## Now multiply by the value column to get afdm per m2 for all combinations
d %>% mutate(afdm=afdm*density) -> d

dt(d)

d %>% group_by(site,area,Taxon_group,Mid_class) %>% summarise(mean_density=round(mean(density),0),mean_afdm=round(mean(afdm),0)) %>% mutate(total_number=mean_density*area, total_afdm=mean_afdm*area/10^3)-> sm
dt(sm)  %>% formatCurrency('total_number',currency = "", interval = 3, mark = ",")