Python 比较两个 dataframe 差异并输出结果

设计思路

假设两个dataframe为df1和df2，三个排序列名为list_cols。

步骤

1. 对df1和df2按list_cols排序，然后重置索引（drop=True，这样不会保留原索引）

2. 对两个dataframe的object列应用str.strip()

3. 创建一个与df1（排序后）形状相同的布尔dataframe，初始为True

4. 遍历每一行和每一列，比较df1和df2的每个单元格，如果不相等，则将布尔dataframe的对应位置设为False

5. 然后删除布尔dataframe中全为True的行和列

6. 利用布尔dataframe的行索引和列索引，从排序后的df1和df2中提取相应的行和列，注意：行索引是布尔dataframe的行索引，列索引是布尔dataframe的列索引

7. 将提取的两个dataframe分别写入Excel的两个sheet

V1.0

import pandas as pd
import numpy as np
import openpyxl

def compare_dataframes(df1, df2, sort_columns, df1_name='df1', df2_name='df2'):
    """
    比较两个结构相同的dataframe，输出差异结果到Excel文件
    
    Parameters:
    -----------
    df1, df2 : pandas.DataFrame
        要比较的两个dataframe
    sort_columns : list
        用于排序的三个列名
    df1_name, df2_name : str
        两个dataframe的名称，用于Excel sheet命名
    """
    
    # 1. 复制数据避免修改原数据
    df1_processed = df1.copy()
    df2_processed = df2.copy()
    
    # 对object类型列使用str.strip()去掉空格
    for df in [df1_processed, df2_processed]:
        for col in df.select_dtypes(include=['object']).columns:
            df[col] = df[col].astype(str).str.strip()
    
    # 根据排序列名重新排序并重置索引
    df1_sorted = df1_processed.sort_values(by=sort_columns).reset_index(drop=True)
    df2_sorted = df2_processed.sort_values(by=sort_columns).reset_index(drop=True)
    
    # 检查两个dataframe是否具有相同的形状
    if df1_sorted.shape != df2_sorted.shape:
        raise ValueError("两个dataframe的形状不一致，无法比较")
    
    # 2. 构建比较dataframe，标记差异
    comparison_df = pd.DataFrame(True, index=df1_sorted.index, columns=df1_sorted.columns)
    
    # 标记不同的单元格为False
    for col in df1_sorted.columns:
        # 处理可能的NaN值比较
        mask = (df1_sorted[col] != df2_sorted[col])
        # 处理两个都是NaN的情况（应该被认为是相同的）
        both_nan = df1_sorted[col].isna() & df2_sorted[col].isna()
        mask[both_nan] = False
        
        comparison_df.loc[mask, col] = False
    
    # 3. 删除全为TRUE的行和列，仅保留有差异的
    # 删除全为True的行
    rows_with_differences = ~comparison_df.all(axis=1)
    comparison_df = comparison_df[rows_with_differences]
    
    # 删除全为True的列
    cols_with_differences = ~comparison_df.all(axis=0)
    comparison_df = comparison_df.loc[:, cols_with_differences]
    
    # 如果没有差异，直接返回
    if comparison_df.empty:
        print("两个dataframe完全相同，没有差异")
        return
    
    # 4. 根据差异位置筛选原始dataframe，并输出到Excel
    # 获取有差异的行索引和列名
    diff_row_indices = comparison_df.index
    diff_columns = comparison_df.columns
    
    # 筛选出有差异的数据
    df1_diff = df1_sorted.loc[diff_row_indices, diff_columns].copy()
    df2_diff = df2_sorted.loc[diff_row_indices, diff_columns].copy()
    
    # 添加排序列以便查看上下文
    for col in sort_columns:
        if col not in diff_columns:
            df1_diff[col] = df1_sorted.loc[diff_row_indices, col]
            df2_diff[col] = df2_sorted.loc[diff_row_indices, col]
    
    # 重新排列列的顺序，将排序列放在前面
    all_columns = sort_columns + [col for col in diff_columns if col not in sort_columns]
    df1_diff = df1_diff[all_columns]
    df2_diff = df2_diff[all_columns]
    
    # 输出到Excel文件
    output_filename = f"{df1_name}_vs_{df2_name}_comparison.xlsx"
    
    with pd.ExcelWriter(output_filename, engine='openpyxl') as writer:
        df1_diff.to_excel(writer, sheet_name=df1_name, index=False)
        df2_diff.to_excel(writer, sheet_name=df2_name, index=False)
    
    print(f"比较完成！结果已保存到: {output_filename}")
    print(f"发现差异的行数: {len(diff_row_indices)}")
    print(f"发现差异的列数: {len(diff_columns)}")
    
    return comparison_df, df1_diff, df2_diff

# 示例用法
if __name__ == "__main__":
    # 创建示例数据
    data1 = {
        'id': [1, 2, 3, 4, 5],
        'name': ['Alice', 'Bob  ', 'Charlie', 'David', 'Eve'],
        'age': [25, 30, 35, 40, 45],
        'city': ['New York', 'London', 'Tokyo', 'Paris', 'Berlin'],
        'score': [85.5, 92.0, 78.5, 88.0, 95.5]
    }
    
    data2 = {
        'id': [1, 2, 3, 4, 5],
        'name': ['Alice', 'Bob', 'Charlie', 'David', 'Eve'],
        'age': [25, 31, 35, 40, 45],  # Bob的年龄不同
        'city': ['New York', 'London', 'Tokyo', 'Paris', 'Berlin'],
        'score': [85.5, 92.0, 78.5, 88.5, 95.5]  # David的分数不同
    }
    
    df1 = pd.DataFrame(data1)
    df2 = pd.DataFrame(data2)
    
    # 使用函数进行比较
    sort_cols = ['id', 'name', 'city']  # 三个排序列
    result = compare_dataframes(df1, df2, sort_cols, '原始数据', '修改后数据')

1. 数据预处理：

   • 复制原始数据避免修改
   • 对所有object类型列使用str.strip()去除空格
   • 根据指定的三个列名排序并重置索引

2. 差异检测：

   • 创建与原始dataframe相同形状的布尔dataframe
   • 标记不同的单元格为False，相同的为True
   • 正确处理NaN值的比较

3. 结果过滤：

   • 删除全为True的行和列，只保留有差异的部分

4. 输出结果：

   • 将有差异的数据保存到Excel文件的两个sheet中
   • 自动添加排序列作为上下文信息
   • 提供详细的执行反馈