如何使用R按州名前缀合并目录中的.xls文件并生成多工作表.xlsx

Hey there, I’ve put together a straightforward R solution to tackle your file merging task. Let’s break it down step by step:

Step 1: Install & Load Required Packages #

We’ll use five packages to handle Excel I/O, string manipulation, and batch processing. Run this first to set them up:

# Install packages (run once)
install.packages(c("readxl", "writexl", "stringr", "dplyr", "purrr"))

# Load packages
library(readxl)    # For reading .xls files
library(writexl)   # For writing .xlsx files with multiple sheets
library(stringr)   # For extracting state names and file types from filenames
library(dplyr)     # For organizing file metadata
library(purrr)     # For batch processing across states

Step 2: Set Your Working Directory #

Point R to the folder where your 100 .xls files are stored. Replace the path below with your actual directory:

# Replace with your folder path (use forward slashes or double backslashes)
setwd("C:/Your/Excel/Files/Directory")

Step 3: Organize Files & Batch Merge #

This code will automatically group files by state, read each pair of Schools/Universities data, and write them to a single .xlsx file per state with named sheets:

# Get list of all .xls files in the directory
xls_files <- list.files(pattern = "\\.xls$", full.names = TRUE)

# Create a data frame to track file details: path, name, state, and data type
file_metadata <- tibble(
  file_path = xls_files,
  file_name = basename(file_path),
  # Extract state name (text before first "-") and trim whitespace
  state = str_trim(str_extract(file_name, "^[^-]+")),
  # Extract data type (Schools/Universities) and trim whitespace
  data_type = str_trim(str_extract(file_name, "(?<= - )(Schools|Universities)(?= - )"))
)

# Group files by state
state_file_groups <- split(file_metadata, file_metadata$state)

# Process each state group to merge files
walk(state_file_groups, function(state_group) {
  # Skip if a state is missing one of the file types (optional error check)
  if (length(unique(state_group$data_type)) != 2) {
    cat("Skipping", state_group$state[1], "- missing Schools/Universities file\n")
    return()
  }
  
  # Read Schools data
  schools_df <- read_excel(state_group$file_path[state_group$data_type == "Schools"])
  # Read Universities data
  unis_df <- read_excel(state_group$file_path[state_group$data_type == "Universities"])
  
  # Create a list of sheets to write (names become sheet names in the .xlsx)
  output_sheets <- list(
    schools = schools_df,
    universities = unis_df
  )
  
  # Define output filename (e.g., "Alabama.xlsx")
  output_filename <- paste0(state_group$state[1], ".xlsx")
  
  # Write the combined .xlsx file
  write_xlsx(output_sheets, path = output_filename)
  
  # Print progress update
  cat("Successfully created:", output_filename, "\n")
})

Important Notes #

• File Validation: The code includes a check to skip states missing either a Schools or Universities file. You can remove this if you’re certain all states have both files.
• Excel Formatting: If your .xls files have complex formatting (e.g., merged cells, hidden sheets), you may need to adjust the read_excel() function with parameters like sheet = 1 (to specify the first sheet) or range (to target specific cells).
• Overwrite Warning: The code will overwrite existing .xlsx files with the same state name in your working directory. Make sure to back up any existing files before running.

内容的提问来源于stack exchange，提问作者Natasha R.