Merge multiple xlsx files into a data frame

Question

I have multiple xlsx data files with different number of rows. How can I merge them into a data frame? My code

library(readxl)

fnames <- list.files()

xlxs <- lapply(fnames, read_xlsx)

a<-data.frame(xlxs)

But the last line does not work since number of rows are not same for all files.

`df <- purrr::map_dfr(list.files(), readxl::read_excel)` – alistaire May 28 '21 at 00:02 — alistaire, May 28 '21 at 00:02

score 0 · Answer 1 · answered May 28 '21 at 00:01

0

We can use

library(dplyr)
a <- bind_rows(xlxs)

Also, it is better to specify the pattern

fnames <- list.files(pattern = "\\.xlsx", full.names = TRUE)

answered May 28 '21 at 00:01

akrun

1

I often also like to have the input filenames referenced in the final data frame. You can use `xlsx <- setNames(xlsx, fnames)`, then add .id = "input_filename" to the bind_rows call to get it easily. – rosscova May 28 '21 at 00:46

score 0 · Answer 2 · edited May 28 '21 at 02:24

0

The purrr option from @alistaire are likely better options, but in case anyone needs/wants to do this in base, you can do:

a <- do.call(rbind, xlsx)

edited May 28 '21 at 02:24

akrun

answered May 28 '21 at 00:49

rosscova

2 Answers2