Using R to transform Excel files to be combined

Question

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.

Answer 1

I have not figured out all processing you need to do, so my solution will not give you the exact same data.frame, but it should be close enough for you to get by with minimal post-processing.

General consideration

• You are using nested for loops to fill a data.frame. This is usually a bad idea, there are almost always better ways to go about it. If you really need to use loops, read about preallocation first (for example in this question on stackoverflow question)
• Think about the structure of your data. Do you really need one row for each single individual, or is a count variable enough? (see below)
• Your task can be split up into two distinct steps: Reshaping the input, and replicating the rows. A function should always do one thing and not several (though in this case I agree that it is not obvious that you are actually doing two things)

Reshaping the input

The first function restructures your data so that it is easier to deal with. We use the reshape2 package for that:

parse_chiro <- function(x){
  taxa <- names(x)[[1]]
  reshape2::melt(
    Chiro.1,
    id.vars = c("Habitat", taxa),
    variable.name = "Size",
    value.name = "count"
  )
}

res <- parse_chiro(Chiro.1)

This gives you a data.frame with all the columns you want, + a count variable that contains how often you want the row to be replicated. The syntax for reshape is a bit hard to explain and I refer you to the package documentation for that. You can achieve something similar with the newer tidyr package, but I personally prefer reshape.

Expanding the data

The next part is expanding the data.frame with the count variable. This is where most if not all of the speed improvement to your original function comes from. I cannot think of an application where you would want your data like that, but if you still need to do it, go about it like this:

expand_chiro <- function(x){
  # Removes all rows with NA count. I am not sure if you really want that?
  x <- x[!is.na(x$count), ]  
  x$rowid <- seq_len(nrow(x))
  rep_rowid <- rep(x$rowid, round(x$count))
  x[rep_rowid, ]
}

Using the vectorized function rep() and data.frame row indexing will yield a significant speed increases to your nested loops. There were also a few things wrong with your loop that could have improved the performance, but the way I propose is much cleaner, so I am not going into that.

Remarks

• rbind all your parsed data.frames before you do the expanding (so that you only need to expand once)
• I would recommend to stick to the tidyverse style guide for naming your functions and variables. It is currently the most popular style guide for R, and you are already using tidyverse packages (readxl, dplyr)
• 2M rows are no problem in R, the limit is your RAM. You can use format(object.size(x), "auto") to display the size of your object in human readable form. In theory, you can work with objects about 1/3 - 1/2 the size of your available RAM, but if you are not super careful and know what you are doing you might crash R. Your data.frame should be only a few hundred MB so no need to worry.