Python实现模糊匹配含'EA'关键词的Excel工作表并合并多文件的方法

Absolutely! This is a super common scenario, and we can build a flexible script to detect any sheet with 'EA' in its name (regardless of extra characters like numbers or suffixes), read those sheets, and merge them all into a single, clean DataFrame. Here's a step-by-step breakdown:

Step 1: Import Required Libraries #

First, make sure you have pandas installed (run pip install pandas if you haven’t already). Then import the modules we’ll need:

import pandas as pd
import os

Step 2: Build a Function to Process Single Excel Files #

This function will take a file path, identify all sheets with 'EA' in their name, read each one, and add helpful metadata (like source file and sheet name) so you can trace data back later:

def read_ea_sheets(file_path):
    # Create an ExcelFile object to access sheet names
    excel_file = pd.ExcelFile(file_path)
    
    # Filter sheet names that contain 'EA' (case-insensitive to catch 'ea', 'Ea', etc.)
    ea_sheets = [sheet for sheet in excel_file.sheet_names if 'EA' in sheet.upper()]
    
    # Return empty list if no EA-related sheets are found
    if not ea_sheets:
        return []
    
    # Read each matching sheet and attach source info
    sheet_dfs = []
    for sheet in ea_sheets:
        df = excel_file.parse(sheet)
        # Add columns to track where the data originated
        df['source_file'] = os.path.basename(file_path)
        df['source_sheet'] = sheet
        sheet_dfs.append(df)
    
    return sheet_dfs

Step 3: Traverse & Merge Data from All Files #

Next, we’ll loop through all Excel files in your target folder, collect all relevant DataFrames, and concatenate them into one master dataset:

def merge_all_ea_data(folder_path):
    all_data_frames = []
    
    # Loop through every file in the specified folder
    for filename in os.listdir(folder_path):
        if filename.endswith(('.xlsx', '.xls')):
            file_path = os.path.join(folder_path, filename)
            print(f"Processing: {filename}")
            # Grab all EA sheets from this file
            file_data = read_ea_sheets(file_path)
            all_data_frames.extend(file_data)
    
    # Merge all collected data into a single DataFrame
    if all_data_frames:
        merged_df = pd.concat(all_data_frames, ignore_index=True, join='outer')
        return merged_df
    else:
        print("No sheets containing 'EA' found in any files.")
        return pd.DataFrame()

Step 4: Run the Script & Use Your Merged Data #

Call the function with your target folder path, then save or analyze the final merged data:

# Replace this with your actual folder path
target_folder = '/path/to/your/excel/files'
final_merged_data = merge_all_ea_data(target_folder)

# Save the merged data to a new Excel file
final_merged_data.to_excel('merged_ea_data.xlsx', index=False)

Key Customizations & Notes #

• Case Sensitivity: The current code uses sheet.upper() to make the check case-insensitive. If you only want to match exact 'EA' (case-sensitive), remove the .upper() part.
• Column Mismatches: The join='outer' parameter in pd.concat keeps all columns from all sheets (filling missing values with NaN). Use join='inner' if you only want columns present in every sheet.
• File Types: The script handles .xlsx and .xls files. Add .xlsm to the endswith check if you need to include macro-enabled Excel files.

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