CODEWITHSUNDEEP
rdataframeimportreadxl
user42485
user42485

Reputation: 811

Import multiple sheets from excel spreadsheet into r

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?

Upvotes: 1

Views: 6945

Answers (3)

Jake Kaupp
Jake Kaupp

Reputation: 8072

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.

Upvotes: 3

ASH
ASH

Reputation: 20342

Does this do what you want?

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

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

Upvotes: 1

svaradan
svaradan

Reputation: 11

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)))

Upvotes: 0

Related Questions