如何在R中导入Excel含指定字符串的多工作表并合并为数据框

Hey there! Let's walk through how to tackle this task—importing all Excel sheets that contain a specific string (like "samples" in your example) and merging them into a single data frame. We'll use R for this, with the readxl package for Excel handling and dplyr for easy merging.

First, set up the required packages #

Make sure you have these installed and loaded (skip the install.packages lines if you already have them):

# Install packages if needed
install.packages("readxl")
install.packages("dplyr")

# Load the packages
library(readxl)
library(dplyr)

Step 1: Get all sheet names from your Excel file #

First, we'll pull a list of every sheet in your 'data.xlsx' file:

all_sheet_names <- excel_sheets("data.xlsx")

Step 2: Filter sheets to keep only those with your target string #

We'll use grepl() to find sheets where the name includes "samples". This is case-sensitive by default—add ignore.case = TRUE inside grepl() if you need it to match "Samples" or "SAMPLES" too:

target_sheets <- all_sheet_names[grepl("samples", all_sheet_names)]

Step 3: Read the filtered sheets into a list of data frames #

Use lapply() to read each of the target sheets into a list. Each item in the list will be a data frame for one sheet:

sheet_data_list <- lapply(target_sheets, function(sheet_name) {
  read_excel(path = "data.xlsx", sheet = sheet_name)
})

Step 4: Merge all data frames into one #

bind_rows() from dplyr is perfect here—it handles mismatched columns gracefully by filling missing values with NA:

merged_data <- bind_rows(sheet_data_list)

If you prefer to use base R instead of dplyr, you can use do.call() with rbind():

merged_data <- do.call(rbind, sheet_data_list)

Quick notes: #

• If your sheets have different column structures, bind_rows() will still combine them, which is usually more useful than throwing an error.
• Double-check your target string spelling—grepl() looks for an exact match unless you use regular expressions (e.g., grepl("sample", all_sheet_names) would match "samples", "sample1", etc.).

内容的提问来源于stack exchange，提问作者user42485