如何对比两个CSV文件并按类型输出差异？求解KeyError问题

Hey James, let's work through your CSV comparison problem step by step. First, let's fix that KeyError: ['XID'] not in index issue you ran into, then build out the exact cell-level logic you need to handle text and integer values differently.

First: Fixing the KeyError #

That error means your input CSV files don't have a column named XID (or there's a case mismatch, like xid instead of XID). Before you run any comparison logic, double-check:

• Both Book1.csv and Book2.csv have the same column names, including your intended primary key column (like XID).
• There are no typos or whitespace in the column headers (e.g., X ID instead of XID).

Once your columns are aligned, we can implement the cell-level comparison you want.

Implementing Type-Specific Cell Comparison #

Your core requirement is to check each cell, distinguish between text and integer values, and add custom annotations when changes happen. Using pandas is the right approach here—it handles data types and row matching much more reliably than raw line-by-line reading.

Here's a complete, annotated solution that meets your needs:

import pandas as pd

# Configuration
OLD_CSV = "Book1.csv"
NEW_CSV = "Book2.csv"
OUTPUT_CSV = "update.csv"
PRIMARY_KEY = "XID"  # Make sure this matches your actual primary key column name

# Step 1: Read and align the CSV data
# Read CSVs, set primary key as index to ensure rows match correctly
old_df = pd.read_csv(OLD_CSV).set_index(PRIMARY_KEY)
new_df = pd.read_csv(NEW_CSV).set_index(PRIMARY_KEY)

# Ensure both DataFrames have the same columns (drop extra, add missing with NaN)
common_cols = old_df.columns.intersection(new_df.columns)
old_df = old_df[common_cols]
new_df = new_df[common_cols]

# Step 2: Identify data types (text vs integer)
text_cols = new_df.select_dtypes(include=["object"]).columns
int_cols = new_df.select_dtypes(include=["int64", "int32"]).columns

# Step 3: Create a result DataFrame to hold annotated values
result_df = new_df.copy()

# Handle text columns: append "True" if value changed
for col in text_cols:
    # Compare old and new values; mark changes
    changed = old_df[col] != new_df[col]
    result_df.loc[changed, col] = new_df.loc[changed, col].astype(str) + " True"

# Handle integer columns: append direction-specific message if value changed
for col in int_cols:
    # Calculate value difference
    diff = new_df[col] - old_df[col]
    # Identify rows where value changed
    changed = diff != 0
    # Append positive/negative message based on difference direction
    result_df.loc[changed & (diff > 0), col] = (
        new_df.loc[changed & (diff > 0), col].astype(str)
        + " Result is positive: change in value"
    )
    result_df.loc[changed & (diff < 0), col] = (
        new_df.loc[changed & (diff < 0), col].astype(str)
        + " Result is negative: change in value"
    )

# Step 4: Handle rows that are new or removed
# Add rows that exist only in new CSV (no old value to compare)
new_rows = new_df[~new_df.index.isin(old_df.index)]
result_df = pd.concat([result_df, new_rows])

# Add rows that exist only in old CSV (mark as removed)
removed_rows = old_df[~old_df.index.isin(new_df.index)]
removed_rows = removed_rows.applymap(lambda x: f"[REMOVED] {x}")
result_df = pd.concat([result_df, removed_rows])

# Step 5: Save the result to CSV
result_df.reset_index().to_csv(OUTPUT_CSV, index=False)

Key Features of This Code #

• Row Alignment: Uses your primary key (like XID) to match rows, so even if row order changes between CSVs, comparisons are accurate.
• Type Detection: Automatically identifies text and integer columns, so you don't have to hardcode column types.
• Custom Annotations:
  • Text cells: Appends True when the value changes.
  • Integer cells: Appends a direction-specific message based on whether the value increased or decreased.
• Handles New/Removed Rows: Marks rows that only exist in the old CSV as [REMOVED] and includes rows that only exist in the new CSV as-is.

Why Your Original Code Didn't Work #

• First Code (csv module): It only compares entire lines, not individual cells. It can't distinguish between text and integer changes, and it fails if rows are reordered between the two CSVs.
• Second Code (pandas): The KeyError was due to a missing XID column, but even without that error, it only shows value changes as old --> new instead of adding your custom annotations for text/integer types.

Let me know if you need to adjust this for edge cases (like float values, missing data, or specific formatting rules)!

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