Read multiple files but keep track of which file is which dataframe in R

user_11279262 0
Viewed 94

Let's say I have these files:

N1.xlsx
N2.xlsx
N3.xlsx
N4.xlsx

I want them in a list, but each dataframe must be named according to the file it was read from, like

mylist = 
N1
N2
N3
N4

I'm using:

fnames =  mixedsort(sort(list.files("filepath", pattern = '*.xlsx', full.names = F)))

mylist <- lapply(fnames, function(x) {
 read_xlsx(paste0(x),  col_names = TRUE)
})

But this code creates a list without identification

mylist = 
[[1]]
[[2]]
[[3]]
[[4]]

Its important to keep the names of each file in each dataframe, so I can export them correctly later!

lapply r
4 Answers

There is a package called libr that does exactly what you are asking for. You can do it in one line of code. The syntax is this:

libname(mylist, "<path to files>", "xlsx")

Above code will put the data in the variable mylist, with each dataset named according to the file name.

user_107361630

Maybe

if you try your code:

fnames =  mixedsort(sort(list.files("filepath", pattern = '*.xlsx', full.names = F)))

mylist <- lapply(fnames, function(x) {
 read_xlsx(paste0(x),  col_names = TRUE)
})

followed by a function to rename list elements?

names(mylist)<-str_remove(fnames, '.xlsx')

Another option would be to include the renaming step into the function that creates the list:

fnames =  mixedsort(sort(list.files("filepath", pattern = '*.xlsx', full.names = F)))

create_list<-function(fnames){
mylist<-lapply(fnames, function(x) {
 read_xlsx(paste0(x),  col_names = TRUE)
})
names(mylist)<-str_remove(fnames, '.xlsx')
}

create_list(fnames)

example of the first strategy:

mylist<-list(c(1:4), c(1:5), c('a','b'), c('d','e','f'))
fnames<-c("N1.xlsx","N2.xlsx","N3.xlsx","N4.xlsx")

names(mylist)<-str_remove(fnames, '.xlsx')

> mylist
$N1
[1] 1 2 3 4

$N2
[1] 1 2 3 4 5

$N3
[1] "a" "b"

$N4
[1] "d" "e" "f"
user_139723330

You could name mylist with the names you already have in fnames.

names(mylist) <- tools::file_path_sans_ext(fnames)
mylist

file_path_sans_ext remove extension from the filenames.

If you want to rename anything in the file name (for example N1 any text.xlsx), you could use

names(mylist) <- tools::file_path_sans_ext(str_remove(fnames, " any  text"))
mylist

Or even:

names(mylist) <- tools::file_path_sans_ext(str_replace(fnames, " any  text", "other text"))
mylist
user_39629140

One strategy could be to make fnames without extension first, then paste0 the extension in the lapply with the advantage that you can use fnames to setNames.

fnames <- gtools::mixedsort(gsub(".xlsx", "", list.files("filepath", pattern="*.xlsx")))
mylist <- setNames(lapply(fnames, function(x)
  openxlsx::read.xlsx(paste0("filepath/", x, ".xlsx"))), fnames)
# $N1
#   X1 X2 X3 X4
# 1  1  4  7 10
# 2  2  5  8 11
# 3  3  6  9 12
# 
# $N2
#   X1 X2 X3 X4
# 1  1  4  7 10
# 2  2  5  8 11
# 3  3  6  9 12
# 
# $N3
#   X1 X2 X3 X4
# 1  1  4  7 10
# 2  2  5  8 11
# 3  3  6  9 12
# 
# $N4
#   X1 X2 X3 X4
# 1  1  4  7 10
# 2  2  5  8 11
# 3  3  6  9 12

Note: I used openxlsx::read.xlsx since you didn't reference your library that read_xlsx stems from, but this should work the same way with it.

Data:

N1 <- N2 <- N3 <- N4 <- data.frame(matrix(1:12, 3, 4))
sapply(ls(pattern="^N"), function(p) 
  openxlsx::write.xlsx(mget(p, envir=.GlobalEnv), paste0("filepath/", p, ".xlsx")))
user_65740380
Related