reading excel files into a single dataframe with readxl R

Question

I have a bunch of excel files and I want to read them and merge them into a single data frame. I have the following code:

library(readxl)
files <- list.files()
f <- list()
data_names <- gsub("[.]xls", "", files)

to read each excel file into data frames

for (i in 1:length(files)){
 assign(data_names[i], read_excel(files[i], sheet = 1, skip = 6))
 }

but, if I try to save it in a variable, just saved the last file

for (i in 1:length(files)){
 temp <- read_excel(files[i], sheet = 1, skip = 6)
}

I think that, It saves the last one because at each iteration of the loop it overwrites the previous `i` with the next `i` in the same name `temp`. Have a look at this previous [answer](http://stackoverflow.com/questions/31139169/how-to-saveassign-the-output-from-lapply-into-individual-variables-in-r/31141145#31141145), it does exactly what you want, with the `assign` function. I think there is a large variety of such solutions here on SO. — SabDeM, Jul 01 '15 at 15:34
Another more detailed (but very similar) solution is provided [here](http://stackoverflow.com/questions/30835815/save-imported-csv-data-in-vector-r/30835924#30835924). — SabDeM, Jul 01 '15 at 15:41

Nick Kennedy · Accepted Answer · 2015-07-02T20:09:57.137

1

I would do this using plyr:

library(readxl)
library(plyr)
files <- list.files(".", "\\.xls")
data <- ldply(files, read_excel, sheet = 1, skip = 6)

If you wanted to add a column with the file name, you could instead do:

data <- ldply(files, function(fil) {
  data.frame(File = fil, read_excel(fil, sheet = 1, skip = 6))
}

edited Jul 02 '15 at 20:09

answered Jul 01 '15 at 16:00

Nick Kennedy

score 0 · Answer 2 · answered Jul 01 '15 at 15:44

0

I would recommend to use the list enviourment in R, assign can be quite confusing and you can't determain values with GET.

Should look like this:

l <- list()

for (i in 1:length(files)){
 l[[i]] <- read_excel(files[i], sheet = 1, skip = 6))
 }

ltogether <- do.call("rbind",l)

answered Jul 01 '15 at 15:44

Sebastian

While I completely agree about assign, this would run more efficiently either using `lapply` or pre-allocating the right sized list using `l <- vector("list", length(files))`. – Nick Kennedy Jul 01 '15 at 16:05

2 Answers2