将多个表格从Excel电子表格导入到R中 [英] Import multiple sheets from excel spreadsheet into r

问题描述

我想从一个.xlsx文件中导入由工作表名称中的公用字符串选择的多个工作表，并将它们连接到单个数据帧中.例如，如果我有一个Excel文件("data.xlsx")，其工作表名为samples1，samples2，samples3，controls1，controls2，controls3.我要列出工作表名称，例如:

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

然后，我要导入名称中包含"samples"的所有工作表，并将它们绑定到名为"samples"的数据框中.我怎样才能有效地做到这一点?

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?

推荐答案

您非常亲密！您可以使用 lapply 等通过base R完成此操作，但是我通常使用 purrr 包执行类似的任务.

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

这样做:

1. 获取工作表的名称.
2. 使用 grepl 将工作表的子集仅包含名称中包含样本"的工作表.
3. 使用 map_dfr 遍历样本表，读取每个样本并分配一个与表名称相同的id列，然后将所有结果按行绑定在一起并返回一个数据框.

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.

这篇关于将多个表格从Excel电子表格导入到R中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！