求助：在R中合并Excel多工作表并提取指定列

Hey there! Let's work through this Excel sheet merging task together—it's simpler than it sounds once we use the right tools. Here's a step-by-step solution tailored to your needs:

Step 1: Load the necessary packages #

We'll use readxl to read the Excel file, dplyr for data manipulation, and purrr to easily iterate over all 21 worksheets. If you don't have these installed yet, run install.packages(c("readxl", "dplyr", "purrr")) first.

library(readxl)
library(dplyr)
library(purrr)

Step 2: Get the names of all worksheets #

First, we'll pull the names of every sheet in your Excel file—this lets us loop through each one without typing all 21 names manually.

# Replace "your_file.xlsx" with the actual path to your Excel file
sheet_names <- excel_sheets("your_file.xlsx")

Step 3: Read and merge all worksheets #

We'll use purrr::map_dfr to read each sheet, add a column to track which sheet the data came from (optional but super helpful for debugging), and bind all the rows into a single tibble/dataframe. Since all your sheets have identical columns and row counts, this will work seamlessly.

merged_data <- map_dfr(sheet_names, function(sheet) {
  # Read the current sheet
  read_excel("your_file.xlsx", sheet = sheet) %>%
    # Add a column to store the sheet name (remove this line if you don't need it)
    mutate(source_sheet = sheet)
})

Step 4: Select your desired columns #

Now that you have one combined dataset, use dplyr::select to pick exactly the columns you need. Just replace the example column names with your actual column names.

# Example: If your columns are "column_a" and "column_b", plus the optional source_sheet
final_df <- merged_data %>%
  select(column_a, column_b, source_sheet) # Adjust to match your column names

Quick notes: #

• If you don't need to track which sheet each row came from, just remove the mutate(source_sheet = sheet) line from Step 3.
• If your column names ever have minor inconsistencies (like different capitalization), you can standardize them first with something like rename_all(tolower) before merging.

That's it! You'll end up with a clean, combined dataframe ready for whatever analysis you need next.

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