#R code to transform the list "SupplFile2_Uribe_Sogamoso_pollen_chart" into an abundance matrix of samples in columns and taxa in rows and vicerversa

# # # # #
library(reshape2)
library (readxl)

# #  Hits to abundances (from a list to a matrix)

# load hits
us.hits <- read.csv("SupplFile2_Uribe_Sogamoso_pollen_chart", check.names= F)

# change main format  
                                 
abundances <- dcast(us.hits, formula = us.hits[,1] + us.hits[,2] + us.hits[,3] + us.hits[,4] +
us.hits[,5] + us.hits[,6] + us.hits[,7] + us.hits[,8]~  TaxaID, value.var = "counts")

# restore names 
colnames(abundances )[1:8] <- names(us.hits)[1:8]

# sort by DUC
byduc <- order(abundances$Composite)
s_abundances <- abundances[byduc,]
rownames(s_abundances) <- 1:nrow(s_abundances)

# take out EMPTY_SAMPLES column 
s_abundances$EMPTY_SAMPLE <- NULL

# original orientation
us.dataframe <- t(s_abundances)

# check size
dim(us.dataframe) # has one less column becaus it is in the rownames
dim(us)

# (optional) set row names apart for its own column 
us.dataframe <- cbind(rownames(us.dataframe),us.dataframe)
rownames(us.dataframe) <- 1:nrow(us.dataframe)

####


# # Abundances to hits  (from a matrix to a list))
us <- read_excel("SupplFile2_Uribe_Sogamoso_pollen_chart.xlsx",col_names=F)

# format
tus <- as.data.frame(t(us[,-1]))
colnames(tus) <- as.character(unlist(us[,1]))

# add an extra column for empty samples
ntus <- as.data.frame(lapply(tus[,-(1:9)], as.numeric))
tus$EMPTY_SAMPLE <- ifelse(rowSums(ntus, na.rm=T) == 0, "none",NA)

us.hits<-melt(tus,value.name="counts",variable.name= "TaxaID",id.vars=colnames(tus)[1:8],na.rm=TRUE)


# save hits 
write.csv(us.hits,"Uribe_Sogamoso_pollen_hits.csv",row.names = FALSE)