library(readxl)
library(dplyr)
library(tidyr)
library(aqm)
## Pull out a vector with the core identifiers
core<- as.vector(t(read_excel("LGI September 2018 Abundance and Biometric Data - reformated.xlsx",
sheet = "MF Measurements Sept 2018",
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 = "MF Measurements Sept 2018",
col_names = FALSE, skip = 3, n_max = 1)[,-1]))
## Read in the data matrix
MF <- read_excel("LGI September 2018 Abundance and Biometric Data - reformated.xlsx",
sheet = "MF Measurements Sept 2018",
skip = 4)names(MF)<-tolower(names(MF)) ## Change all to lower case
names(MF)<-gsub("[^\\p{L}']+", " ", names(MF), perl = TRUE) ## Strip anything that's not a letter or a space.
names(MF)<- gsub(" $","", names(MF), perl=T) ## Strip trailing spaces
names(MF)<- gsub(" ","_", names(MF), perl=T) ## Underscore for space
## Now paste the core ids onto all the names except the first one
names(MF)[-1]<-paste(names(MF)[-1],type,core,sep="-")This should now produce a proper data frame.
MF %>% pivot_longer(cols=c(-1), values_drop_na=TRUE) %>% separate(name, "-", into = c("Taxon","Type","Core")) %>% separate(size_class, "-",into=c("Lower","Higher")) %>% mutate (Lower=aqm::clean(Lower), Higher=aqm::clean(Higher), Mid_class=round((Lower+Higher)/2, 1)) -> MF
dt(MF)table(MF$Taxon)##
## abra_juv capitellidae cappitellidae
## 1 10 1
## cirratulidae corophiidae crangon_crangon
## 30 8 1
## hydrobiidae macoma_balthica nematoda
## 19 38 8
## nephtyidae nereididae oligochaeta
## 12 10 99
## phyllodocidae scrobicularia_plana spionidae
## 1 61 30