0

I've 5 data frames that I downloaded. They all are similar with the exception that they show data from different years 2015, 2016, 2017, 2018 and 2019:

book_15 <- read_excel("data/book_15.xlsx")

book_16 <- read_excel("data/book_16.xlsx")

book_17 <- read_excel("data/book_17.xlsx")

book_18 <- read_excel("data/book_18.xlsx")

book_19 <- read_excel("data/book_19.xlsx")

Now I want to combine these in a single tibble that looks like this: arrange(year)

Code        year 
  A         2015
  B         2015
  C         2015
  D         2015
  E         2015
  F         2015
#With 750 more rows

Should I use join function or something similar?

Henry Oufh
  • 135
  • 1
  • 1
  • 8
  • `file_list <- list.files(..., full.names = TRUE)` creates a vector of file paths/names, then `dplyr::bind_rows(lapply(file_list, read_excel))` produces a single-frame from all of them combined. – r2evans Jul 23 '22 at 11:04
  • This is not a "join" operation, that is adding/replacing columns based on a set of common fields; if you're curious about it, see https://stackoverflow.com/q/1299871/3358272 and https://stackoverflow.com/q/5706437/3358272. What you're doing here is more of a "row-concatenate" (`rbind`) operation. – r2evans Jul 23 '22 at 11:05
  • So in this context, it would be: file_list <- list.files(book_15, book_16, book_17, book_18, book_19, full.names = TRUE) ? dplyr::bind_rows(lapply(file_list, read_excel)) @r2evans – Henry Oufh Jul 23 '22 at 11:10
  • No. `c("book_15.xlsx", "book_16.xlsx", ...)`, most likely formed with `list.files(pattern="xlsx$")`. If there are not in the current directory, then use `list.files("path/to/dir", "xlsx$", full.names=TRUE)` instead. – r2evans Jul 23 '22 at 11:25
  • The files I read in excel is in the current directory. I'm sorry if I don't get it (beginner) but I tried this which didn't work: file_list <- list.files(c("book_15", "book_16", ...)) dplyr::bind_rows(lapply(file_list, read_excel)) @r2evans – Henry Oufh Jul 23 '22 at 11:32
  • I have listed twice now the argument `full.names=TRUE`. I strongly urge you get used to searching for and reading the documentation; in this case, `?list.files` will give you something like [this](https://stat.ethz.ch/R-manual/R-devel/library/base/html/list.files.html). – r2evans Jul 23 '22 at 11:34
  • So *literally*, run `file_list <- list.files("path/to/dir", pattern="xlsx$", full.names=TRUE); alldat <- dplyr::bind_rows(lapply(file_list, readxl::read_excel))`. – r2evans Jul 23 '22 at 11:42

0 Answers0