Read different excel files with a range of sheet

Question

I have a file per month and within there are different sheets (2:7) that group days in the given month. I need to merge all of that into a single dataframe. I made a for loop but it only save the first sheet with data (2) in each month.

library(readxl)
library(dplyr)
files <- list.files(path = "***/2023", full.names = T)

TrafMonth <- list() #this should save all the sheets each month
TrafMonth2 <- list() #this should save all the sheets and months
for (i in 1:length(files)) {
  Month <- files[i]
  for (j in 1:6) {
    TrafMonth[[j]] <- read_excel(path = Month, sheet = j+1)
  }
  TrafMonth2[i] <- TrafMonth
}

TrafMonth2 only saved the first sheet of each month and TrafMonth saved the all sheets, this happens because TrafMonth gives only the first element of the list to TrafMonth2.

How do I fix this?

Answer 1

I took the following approach:

frames <- lapply(1:length(files), function(x) {
  lapply(1:6, function(y) {
    read_excel(path = files[x], sheet = y+1)
  })
})

frames <- bind_rows(frames)