Combining multiple Excel files with single sheets into one dataframe.

Hi R community. I have 10 excel files having same column names with single sheets in each file. How can I combine the into one single file using R?
I want to know how to code after the portion shown below.

tripdata_2021_oct <- read_excel('202110-divvy-tripdata.xlsx')
tripdata_2021_nov <- read_excel('202111-divvy-tripdata.xlsx')
tripdata_2021_dec <- read_excel('202112-divvy-tripdata.xlsx')
tripdata_2022_jan <- read_excel('202201-divvy-tripdata.xlsx')
tripdata_2022_feb <- read_excel('202202-divvy-tripdata.xlsx')
tripdata_2022_mar <- read_excel('202203-divvy-tripdata.xlsx')
tripdata_2022_apr <- read_excel('202204-divvy-tripdata.xlsx')
tripdata_2022_may <- read_excel('202205-divvy-tripdata.xlsx')
tripdata_2022_jun <- read_excel('202206-divvy-tripdata.xlsx')
tripdata_2022_jul <- read_excel('202207-divvy-tripdata.xlsx')
tripdata_2022_aug <- read_excel('202208-divvy-tripdata.xlsx')
tripdata_2022_sep <- read_excel('202209-divvy-tripdata.xlsx')


there are several options here. One solution could be to use lapply() with a list of the relevant sheets (e.g. generated with list.files()) and the function read_excel() you already used. This will result in a list of data.frames, which can be combined using e.g. data.table::rbindlist(). This functions takes the list as an argument and you can tell it to add an id column (which will be numeric 1:length(list)). You will have to specify the corresponding values of your Id column with an data.table::fcase() or dplyr::case_when() statement, if you wish not to keep the numeric default.

Kind regards

1 Like

Another option


all_data <- list.files(pattern = "\\.xlsx$") %>% 

Thank you for your responses. It was helpful.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.