Python中两个Excel导入DataFrame的逐单元格比对及导出需求

Hey there! Let's work through this cell-by-cell comparison problem together—this is a super common task when working with Excel data in pandas, so I’ve got you covered.

Step 1: Align Your DataFrames Properly #

Since your two DataFrames have different row counts, we need to make sure we’re comparing the right rows. There are two key scenarios to handle:

• If you have a unique identifier column (like an ID number present in both files): This is the most reliable approach, as it avoids mismatches caused by row ordering differences.
• If you don’t have a unique ID: We’ll compare rows by their position (index), but note this only works if rows are in the exact same order in both files.

Step 2: Run the Cell-by-Cell Comparison #

Let’s dive into the code, with explanations for each scenario:

Scenario 1: Using a Unique Identifier Column

import pandas as pd

# Assume you've already loaded your data (adjust file paths as needed)
prod_df = pd.read_excel('Prod1.xlsx')
proj_df = pd.read_excel('Proj1.xlsx')

# Merge the two DataFrames on your unique key (replace 'id' with your actual column name)
merged_data = pd.merge(
    prod_df,
    proj_df,
    on='id',
    suffixes=('_prod', '_proj'),
    how='outer'  # Includes all rows from both DataFrames
)

# Create an empty DataFrame to store match/mismatch results
comparison_results = pd.DataFrame()

# Loop through each column (excluding the unique key) to compare values
for col in prod_df.columns.drop('id'):
    # Mark mismatches as TRUE, matches as FALSE
    comparison_results[col] = merged_data[f'{col}_prod'] != merged_data[f'{col}_proj']
    # Handle rows that exist in one DataFrame but not the other (fill NaNs with TRUE)
    comparison_results[col] = comparison_results[col].fillna(True)

# Convert boolean values to 'TRUE'/'FALSE' strings (as requested)
comparison_results = comparison_results.replace({True: 'TRUE', False: 'FALSE'})

Scenario 2: No Unique Identifier (Compare by Row Index)

import pandas as pd

# Load your data
prod_df = pd.read_excel('Prod1.xlsx')
proj_df = pd.read_excel('Proj1.xlsx')

# Perform element-wise comparison (aligns automatically by index and column names)
comparison_results = prod_df.eq(proj_df)

# Replace NaNs (rows present in one DataFrame but not the other) with TRUE
comparison_results = comparison_results.fillna(True)

# Convert booleans to 'TRUE'/'FALSE' strings
comparison_results = comparison_results.replace({True: 'TRUE', False: 'FALSE'})

Step 3: Export Results to Excel #

Once you have your comparison_results DataFrame, exporting it takes just one line of code:

# Export to Excel (set index=False if you don't want row numbers in the output)
comparison_results.to_excel('Data_Comparison_Results.xlsx', index=False)

Quick Tips #

• The eq() method checks for exact matches—if you need to account for floating-point precision issues, swap it out for np.isclose() instead.
• If your columns have mismatched data types (e.g., one has strings and the other has numbers), standardize them first using astype() before comparing.

Let me know if you hit any snags—happy to help tweak this to fit your specific data!

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