I have 50+ excel files that I need to transform (and eventually combine) to use in analyses. I’ve written a code that transforms them into the format I need, but it is extremely slow for files with high species counts (the transformed output will have > 2 million rows). My first question: is there a way to speed up my function? My second question: once all the files are combined, there will be many millions of lines of data. Will I be able to manipulate (e.g. subset, statistically analyze) the data in R? Can R (and/or my laptop) handle that much data? I’ve only used R with several thousand lines and a hundred or so columns before, so I don’t know what’s possible. The first part of the code below is to produce a data frame resembling what my files look like. In reality, my files are larger (45rows, 12-21 columns) and they are not all the same (different number of columns, and the Habitat column is in different locations with different names), thus some of the strange code in the function. I could clean up each file so they are the same before I use my function, if that would be faster.
Sample data
library(dplyr)
#library(readxl) #this isn't used here, but this is what I use to load the data files.
aa<-c('Jul','Jul','Jul','Oct','Oct','Oct')
bb<-c('MA1','MA2','MA3','MA1','MA2','MA3')
a<-c(NA,100:104) #when loaded, the data has both NA's and 0's
b<-seq(1,12,2)
c<-seq(0,35,6)
d<-c(NA,NA,2000,NA,200,0)
e<-c(1:6)
Chiro.1<-data.frame(aa,bb,a,b,c,d,e)
colnames(Chiro.1)<-c('Chironomidae','Habitat','0','0.5','1','2','3')
Chiro.1<-as_tibble(Chiro.1)
Chiro.1$Habitat<-as.character(Chiro.1$Habitat)
Chiro.1$Chironomidae<-as.character(Chiro.1$Chironomidae)
The function
Cedar.bs.fun<-function (x, scol,Hcol) {
#x = dataframe
#scol = column number data starts
#Hcol = column habitat type is in
spe.list<-list()
pb <- txtProgressBar(min = 0, max = nrow(x), style = 3)
for (i in 1:nrow(x)) for (j in scol:ncol(x)){ #not every file has the same number of rows and columns
if (!is.na(x[i,j])) {
dat <- as.data.frame(matrix(ncol=4, nrow=0))
colnames(dat)<-c('Taxa','Habitat','Date','Size')
if (x[i,j]> 0) {
n<-round(sum(x[i,j]),0) #original files have some decimals
for(k in 1:n) {
dat[k,1] = as.character(colnames(x[,1]))
dat[k,2] = as.character((x[i,Hcol]))
dat[k,3] = as.character((x[i,1]))
dat[k,4] = colnames(x[,j])
setTxtProgressBar(pb, i)
}
}
spe.list[[length(spe.list)+1]] <- dat
spe.list.1<-do.call("rbind",spe.list)
}
}
spe.list.1$Size<-as.numeric(as.character(spe.list.1$Size)) #the column headings of the original files are the beginnings of size bins (eg. 1 mm - 2 mm)
spe.list.1$Size<-spe.list.1$Size+0.5 #I need the final size to be the middle of the bin
spe.list.1$Size[spe.list.1$Size==0.5]<-0.25
spe.list.1$Size[spe.list.1$Size==1]<-0.75
close(pb)
return(spe.list.1)
}
Run the function
final<-Cedar.bs.fun(Chiro.1,scol=3,Hcol=2)
#combined.1<-rbind(final,final.2,final.3,etc.) #eventually I will be combining all 50 or so files into one data frame for further manipulation and statistical analysis
And yes, the original files are mine made long before I new much of anything about R and how to organize data. Now I'm paying for it. Still tons to learn.