如何用Python合并表头不同或表头顺序不同的Excel文件

Alright, let's tackle these two Excel merging scenarios one by one—both are super common when dealing with messy, inconsistent spreadsheets, and pandas is your best friend here for handling the heavy lifting.

1. Merging Two Excel Files with Different Headers #

If your two files have entirely different (or partially overlapping) headers, the goal is to combine all rows while preserving every column from both files. Missing values in columns that don't exist in a file will automatically be filled with NaN (you can replace these later if needed).

Step-by-Step Code: #

import pandas as pd

# Read both Excel files into pandas DataFrames
df_file1 = pd.read_excel("first_file.xlsx")
df_file2 = pd.read_excel("second_file.xlsx")

# Combine the DataFrames. `ignore_index=True` resets row numbers to avoid duplicates
merged_master = pd.concat([df_file1, df_file2], ignore_index=True)

# Save the merged result to a new Excel file (no index column needed)
merged_master.to_excel("combined_master_file.xlsx", index=False)

Bonus: If you need to merge on shared columns #

If there's a common key (like an "ID" column) you want to use to match rows between the two files, use pd.merge instead:

# Merge using "ID" as the common key, keeping all rows from both files
merged_master = pd.merge(df_file1, df_file2, on="ID", how="outer")

2. Unifying Headers First, Then Merging Sheets with Out-of-Order (But Almost Identical) Headers #

When your sheets have the same columns but in different orders, the first step is to standardize the header names and column order across all files. This ensures everything lines up correctly before merging.

Step-by-Step Code: #

import pandas as pd
import os

# First, define your standard header (you can grab this from one of your sheets, or write it manually)
standard_columns = ["Employee ID", "Full Name", "Department", "Hire Date", "Salary"]

# Point to the folder where all your Excel files are stored
excel_folder = "./your_excel_files"
# Get a list of all Excel files in the folder
file_list = [f for f in os.listdir(excel_folder) if f.endswith(".xlsx")]

# Initialize an empty list to hold cleaned DataFrames
cleaned_dfs = []

for file_name in file_list:
    # Build the full path to each file
    file_path = os.path.join(excel_folder, file_name)
    # Read the file into a DataFrame
    df = pd.read_excel(file_path)
    
    # Reorder the columns to match your standard header
    # This works if all column names exactly match (just order is wrong)
    df_clean = df.reindex(columns=standard_columns)
    
    # If some files have slightly different column names (e.g., "Name" instead of "Full Name"),
    # use a mapping to rename them first:
    # column_mapping = {
    #     "Name": "Full Name",
    #     "Join Date": "Hire Date"
    # }
    # df_clean = df.rename(columns=column_mapping).reindex(columns=standard_columns)
    
    # Add the cleaned DataFrame to our list
    cleaned_dfs.append(df_clean)

# Combine all cleaned DataFrames into one
final_merged = pd.concat(cleaned_dfs, ignore_index=True)

# Save the unified master file
final_merged.to_excel("unified_merged_excel.xlsx", index=False)

Quick Tip: Handling Missing Columns #

If some sheets are missing a few columns, reindex will fill those missing cells with NaN. You can replace these with a default value (like "N/A" or 0) using fillna():

df_clean = df.reindex(columns=standard_columns).fillna("N/A")

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