Import multiple sheets from excel spreadsheet into r

0
votes

I want to import multiple sheets, selected by a common string in the sheet name, from a single .xlsx file and concatenate them into single data frame. For example, if I have an excel file ('data.xlsx') with worksheets named samples1, samples2, samples3, controls1, controls2, controls3. I want to make a list of the worksheet names, such as:

sheet_list <- lapply(excel_sheets('data.xlsx'), read_excel, path = 'data.xlsx')

Then, I want to import all sheets that contain 'samples' in the name and bind them into a data frame called 'samples'. How can I accomplish this efficiently?

3
rdataframeimportreadxl
You may also want to check openxlsx package as it provides a lot of handy functions for reading data from Excel, including workbooks table objects, and exporting to Excel as well. – Konrad

3 Answers

2
votes

You are very close! You can use lapply and such to accomplish this using base R, but I routinely perform tasks like this using the purrr package.

library(purrr)
library(readxl)    

sheets <- excel_sheets('data.xlsx')

sample_sheets <- sheets[grepl("samples", sheets)]

sheet_df <- map_dfr(sample_sheets, ~read_excel(path = 'data.xlsx', sheet = .x), id = .x)

This does:

  1. Get the names of the sheets.
  2. Use grepl to subset the sheets to only those containing "samples" in the name.
  3. Use map_dfr to iterate over the sample sheets, reading each one in and assigning an id column equal to the name of the sheet, then bind all the results together by rows and return a data frame.
1
votes

Does this do what you want?

path <- "C:\\your_path_here\\test.xlsx"

path %>% 
  excel_sheets() %>% 
  set_names() %>% 
  map(read_excel, path = path)
0
votes

Try this 

library(readxl)
list <- excel_sheets("path_to_excel.xlsx")
list_samples <- list[grepl("samples", list)]
df <- rbind(lapply(list_samples, function(x) read_excel("path_to_excel.xlsx", sheet = x)))