如何用模式匹配合并名称相似列的多Excel文件数据？

Absolutely! Handling similar column names and year-prefixed metrics is a common pain point when combining legacy Excel data—here's a step-by-step solution tailored to your R workflow:

First, we need to formalize which columns represent the same metric despite different naming. We'll handle two scenarios:

• Exact similar column mapping: For columns like Metric Rating - Unit level, Percentage Earning 5 Credit in Third Year and Metric Rating - 5 Credits in 3rd Year - Unit level, create a manual one-to-one mapping to a standard name.
• Year-stripping mapping: For columns with year prefixes (e.g., Target For 2016 - Grad Rate, 6-Year), use regex to remove the year part and unify the core metric name.

Example mapping setup:

# Exact similar column mappings (add more pairs as needed)
exact_mapping <- c(
  "Metric Rating - Unit level, Percentage Earning 5 Credit in Third Year" = "metric_rating_5_credits_unit_level",
  "Metric Rating - 5 Credits in 3rd Year - Unit level" = "metric_rating_5_credits_unit_level"
)

# Regex pattern to match year prefixes (adjust for other patterns if needed)
year_prefix_pattern <- "^Target For \\d{4} - "

Create a reusable function to standardize column names across all files:

clean_colnames <- function(col_names) {
  # Apply exact mappings first
  cleaned <- dplyr::recode(col_names, !!!exact_mapping)
  
  # Strip year prefixes for unmatched columns
  cleaned <- stringr::str_remove(cleaned, year_prefix_pattern)
  
  # Final formatting: lowercase, replace special chars with underscores
  cleaned <- stringr::str_to_lower(cleaned)
  cleaned <- stringr::str_replace_all(cleaned, "[^a-z0-9\\s]", "")
  cleaned <- stringr::str_squish(cleaned)
  cleaned <- stringr::str_replace_all(cleaned, "\\s+", "_")
  
  return(cleaned)
}

Integrate the cleaning logic into your existing code to ensure columns are unified before merging:

library(dplyr)
library(purrr)
library(readxl)
library(stringr)
library(tools)

# Set your file path and target sheet name
filepath_raw_data <- "your/raw/data/path"
sheet_name <- "a" # Repeat this for sheet "b" or loop through both

# Read, clean, and combine all files
df <- list.files(path = filepath_raw_data, full.names = TRUE, pattern = "*.xlsx") %>% 
  set_names(nm = (basename(.) %>% file_path_sans_ext())) %>% 
  map_df(function(file) {
    # Read raw data from the sheet
    raw_df <- read_excel(file, sheet = sheet_name, col_types = "text")
    
    # Clean column names
    colnames(raw_df) <- clean_colnames(colnames(raw_df))
    
    # Add source file identifier and return
    raw_df %>% mutate(file_name = basename(file) %>% file_path_sans_ext())
  })

• After running the code, use colnames(df) to verify all columns are standardized.
• Use table(colnames(df)) to check for accidental duplicates (if any edge cases weren't covered).
• Extend the exact_mapping or regex patterns as needed for other similar column name pairs.

This approach will ensure that semantically identical metrics are merged into a single column, regardless of their original naming or year prefixes.

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