Python新手求助：编写多Excel文件数据校验脚本，基于指定列提取差异行

Hey there! Let's walk through how to get your data validation script working exactly as you need it. I see you're a Python newbie trying to compare 100 Excel files against a baseline, focusing on primary key columns to spot row differences—great goal, and we can tweak your approach to be more reliable and aligned with pandas best practices.

First, let's address the key issues in your current code: #

• You're using excel_files[14] as your baseline df1 instead of the first file (excel_files[0]) as you described
• The pandasql query uses _key which isn't defined anywhere—we need to explicitly reference your actual primary key columns
• You're not initializing or appending to df3 (your target DataFrame for differences)
• append() is deprecated in pandas; we'll use pd.concat() instead
• Your loop includes the baseline file itself (excel_files[0:100] includes the first file), which we should exclude to avoid unnecessary comparisons

Let's rewrite the script with these fixes #

First, make sure you have the required packages installed:

pip install pandas openpyxl  # openpyxl for reading/writing .xlsx files

Here's the revised code, with comments explaining each step:

import pandas as pd
import glob
import os
import datetime as dt

# ----------------------
# Configuration (adjust these to match your data!)
# ----------------------
BASELINE_FILE_INDEX = 0  # Use the first Excel file as baseline
COMPARE_FILES_RANGE = range(1, 100)  # Compare files 1 to 99 (skip baseline)
PRIMARY_KEY_COLUMNS = ["PrimaryKey1", "PrimaryKey2"]  # Replace with your actual primary key columns
OUTPUT_FILE = "differences_report.xlsx"

# ----------------------
# Initialize baseline DataFrame
# ----------------------
dateTimeObj = dt.datetime.now()
print(f"Starting data validation at: {dateTimeObj.strftime('%x %X')}")

# Get list of all Excel files
files_path = os.path.abspath("mydrive")
excel_files = glob.glob(os.path.join(files_path, "*.xlsx"))

# Read baseline file and clean up unnamed columns
df1 = pd.read_excel(excel_files[BASELINE_FILE_INDEX])
# Remove any unnamed columns that might be present
df1 = df1.loc[:, ~df1.columns.str.contains('^Unnamed')]

# Initialize empty DataFrame to store all differences
df3 = pd.DataFrame()

# ----------------------
# Loop through comparison files
# ----------------------
for idx in COMPARE_FILES_RANGE:
    file_path = excel_files[idx]
    print(f"Comparing file: {os.path.basename(file_path)}")
    
    # Read current comparison file and clean up
    df2 = pd.read_excel(file_path)
    df2 = df2.loc[:, ~df2.columns.str.contains('^Unnamed')]
    
    # Step 1: Merge df1 and df2 on primary keys to find matching rows
    merged = pd.merge(df1, df2, on=PRIMARY_KEY_COLUMNS, how="outer", indicator=True)
    
    # Step 2: Split into three groups:
    # - Rows only in baseline (df1)
    only_in_baseline = merged[merged["_merge"] == "left_only"].drop("_merge", axis=1)
    only_in_baseline["Source"] = f"Only in baseline ({os.path.basename(excel_files[BASELINE_FILE_INDEX])})"
    
    # - Rows only in current file (df2)
    only_in_current = merged[merged["_merge"] == "right_only"].drop("_merge", axis=1)
    only_in_current["Source"] = f"Only in {os.path.basename(file_path)}"
    
    # - Rows with matching keys but differing content
    matching_keys = merged[merged["_merge"] == "both"].drop("_merge", axis=1)
    # Split into df1 and df2 columns for comparison
    df1_cols = [col for col in matching_keys.columns if not col.endswith("_y")]
    df2_cols = [col for col in matching_keys.columns if col.endswith("_y")]
    # Rename columns to remove suffixes
    matching_keys_df1 = matching_keys[df1_cols].rename(columns=lambda x: x.rstrip("_x"))
    matching_keys_df2 = matching_keys[df2_cols].rename(columns=lambda x: x.rstrip("_y"))
    
    # Compare row content for matching keys
    diffs = matching_keys_df1.compare(matching_keys_df2, keep_shape=True, keep_equal=False)
    # If there are differences, add source info and append to df3
    if not diffs.empty:
        # Add primary keys back to the diffs
        diffs = diffs.join(matching_keys[PRIMARY_KEY_COLUMNS])
        diffs["Source"] = f"Difference between baseline and {os.path.basename(file_path)}"
        df3 = pd.concat([df3, diffs], ignore_index=True)
    
    # Append rows that exist only in one file to df3
    df3 = pd.concat([df3, only_in_baseline, only_in_current], ignore_index=True)

# ----------------------
# Save and finish
# ----------------------
if not df3.empty:
    df3.to_excel(OUTPUT_FILE, index=False)
    print(f"Validation complete! Differences saved to {OUTPUT_FILE}")
else:
    print("No differences found across all files.")

print(f"Finished at: {dt.datetime.now().strftime('%x %X')}")

Key improvements explained: #

• Explicit primary key handling: We use pd.merge() to align rows based on your specified primary keys, ensuring we only compare rows that should match
• Clear difference categorization: The script captures three types of differences: rows only in the baseline, rows only in the comparison file, and rows with matching keys but differing content
• Deprecation-free code: Uses pd.concat() instead of the deprecated append() method
• Cleaner file handling: Automatically removes unnamed columns and includes source info for every difference, making your report easier to interpret
• Progress updates: Prints which file is being compared so you can track progress through 100 files

Quick notes for customization: #

• Replace PRIMARY_KEY_COLUMNS with the actual column names in your Excel files that act as unique identifiers
• Adjust COMPARE_FILES_RANGE if you need to compare a different subset of files
• The compare() method shows differences side-by-side; if you prefer a different format, you can modify that section to suit your needs

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