设计思路1.0
假设两个dataframe为df1和df2，三个排序列名为list_cols。
步骤
-
对df1和df2按list_cols排序，然后重置索引（drop=True，这样不会保留原索引）
-
对两个dataframe的object列应用str.strip()
-
创建一个与df1（排序后）形状相同的布尔dataframe，初始为True
-
遍历每一行和每一列，比较df1和df2的每个单元格，如果不相等，则将布尔dataframe的对应位置设为False
-
然后删除布尔dataframe中全为True的行和列
-
利用布尔dataframe的行索引和列索引，从排序后的df1和df2中提取相应的行和列，注意：行索引是布尔dataframe的行索引，列索引是布尔dataframe的列索引
-
将提取的两个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, '原始数据', '修改后数据')
-
数据预处理：
- 复制原始数据避免修改
- 对所有object类型列使用
str.strip()去除空格 - 根据指定的三个列名排序并重置索引
-
差异检测：
- 创建与原始dataframe相同形状的布尔dataframe
- 标记不同的单元格为False，相同的为True
- 正确处理NaN值的比较
-
结果过滤：
- 删除全为True的行和列，只保留有差异的部分
-
输出结果：
- 将有差异的数据保存到Excel文件的两个sheet中
- 自动添加排序列作为上下文信息
- 提供详细的执行反馈
设计思路 2.0
-
输入两个dataframe（df1, df2），以及指定的参数：核心列列表、完全一致列列表、差额列列表、阈值、输出路径。
-
检查两个dataframe的行数是否相等，并且所有需要的列都存在。
-
提取两个dataframe中需要的列（核心列+完全一致列+差额列），然后按照核心列排序。
-
比较完全一致列：逐列比较，如果某列所有行都一致，则删除该列（核心列不删除）。如果某列存在不一致，则保留该列。
-
比较差额列：计算两个dataframe对应列的差额，如果所有行的差额都小于阈值，则删除该列；否则保留该列（并且保留核心列）。
-
将处理后的两个dataframe输出到同一个Excel文件的两个sheet中，如果两个dataframe都为空，则输出提示信息。
import pandas as pd
import numpy as np
def compare_dataframes(
df1: pd.DataFrame,
df2: pd.DataFrame,
sort_cols: list,
exact_cols: list,
diff_cols: list,
threshold: float,
output_path: str
) -> None:
"""
比较两个DataFrame并输出差异报告
参数:
df1, df2 -- 要比较的两个DataFrame
sort_cols -- 用于排序的核心列名列表
exact_cols -- 需要精确比较是否完全一致的列名列表
diff_cols -- 需要计算差额的列名列表
threshold -- 可接受的金额差额阈值
output_path -- 输出Excel文件路径
"""
# 1. 提取需要的列并创建副本
all_cols = list(set(sort_cols + exact_cols + diff_cols))
df1_sub = df1[all_cols].copy()
df2_sub = df2[all_cols].copy()
# 2. 按指定列排序
df1_sorted = df1_sub.sort_values(by=sort_cols).reset_index(drop=True)
df2_sorted = df2_sub.sort_values(by=sort_cols).reset_index(drop=True)
# 3. 处理完全匹配列
cols_to_drop = []
for col in exact_cols:
if col in sort_cols: # 核心列始终保留
continue
if df1_sorted[col].equals(df2_sorted[col]):
cols_to_drop.append(col)
df1_filtered = df1_sorted.drop(columns=cols_to_drop, errors='ignore')
df2_filtered = df2_sorted.drop(columns=cols_to_drop, errors='ignore')
# 4. 处理差额列
diff_mask = pd.Series(False, index=df1_filtered.index)
cols_to_drop_diff = []
for col in diff_cols:
if not (pd.api.types.is_numeric_dtype(df1_filtered[col]) and
pd.api.types.is_numeric_dtype(df2_filtered[col])):
continue
# 计算绝对差额
diff = (df1_filtered[col] - df2_filtered[col]).abs()
# 检查是否所有差额都小于阈值
if (diff < threshold).all():
cols_to_drop_diff.append(col)
else:
# 标记超过阈值的行
diff_mask |= (diff >= threshold)
# 删除无差异的列
df1_final = df1_filtered.drop(columns=cols_to_drop_diff, errors='ignore')
df2_final = df2_filtered.drop(columns=cols_to_drop_diff, errors='ignore')
# 5. 标记有差异的行
exact_mask = pd.Series(False, index=df1_final.index)
remaining_exact_cols = [col for col in exact_cols
if col in df1_final.columns and col in df2_final.columns]
for col in remaining_exact_cols:
exact_mask |= (df1_final[col] != df2_final[col])
# 合并差异标记
final_mask = exact_mask | diff_mask
df1_result = df1_final[final_mask]
df2_result = df2_final[final_mask]
# 6. 保存结果到Excel
with pd.ExcelWriter(output_path) as writer:
df1_result.to_excel(writer, sheet_name='Source1', index=False)
df2_result.to_excel(writer, sheet_name='Source2', index=False)
# 示例用法
if __name__ == "__main__":
# 创建示例数据
data1 = {
'ID': [1, 2, 3, 4],
'Name': ['A', 'B', 'C', 'D'],
'Dept': ['X', 'Y', 'X', 'Y'],
'Salary': [5000, 6000, 5500, 6200],
'Bonus': [500, 600, 550, 620]
}
data2 = {
'ID': [1, 2, 3, 4],
'Name': ['A', 'B', 'C', 'E'], # D->E 修改
'Dept': ['X', 'Y', 'X', 'Z'], # Y->Z 修改
'Salary': [5000, 6100, 5500, 6300], # 6000->6100, 6200->6300
'Bonus': [500, 600, 560, 620] # 550->560
}
df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)
# 设置参数
sort_columns = ['ID']
exact_columns = ['Name', 'Dept'] # 需要完全匹配的列
diff_columns = ['Salary', 'Bonus'] # 需要计算差额的列
threshold_value = 100 # 可接受的差额阈值
output_file = "comparison_results.xlsx"
# 执行比较
compare_dataframes(
df1=df1,
df2=df2,
sort_cols=sort_columns,
exact_cols=exact_columns,
diff_cols=diff_columns,
threshold=threshold_value,
output_path=output_file
)
代码功能说明：
-
数据准备：
- 提取指定的列（排序列+精确比较列+差额计算列）
- 按排序列排序两个DataFrame
-
精确匹配列处理：
- 检查exact_cols中每列是否完全一致
- 删除完全一致的列（核心列除外）
-
差额计算列处理：
- 计算数值列的绝对差额
- 删除所有差额都小于阈值的列
- 标记超过阈值的行
-
差异行筛选：
- 标记所有存在差异的行（精确匹配差异或差额超阈值）
- 过滤保留有差异的行
-
结果输出：
- 将处理后的两个DataFrame保存到Excel的不同Sheet
- 包含三张表：源数据1、源数据2、差异汇总（本实现简化为两个Sheet）
使用示例：
df_source1 = pd.read_csv("source1.csv")
df_source2 = pd.read_csv("source2.csv")
compare_dataframes(
df1=df_source1,
df2=df_source2,
sort_cols=['Date', 'ProductID', 'Region'], # 三重排序
exact_cols=['ProductName', 'Category'],
diff_cols=['Revenue', 'Cost', 'Profit'],
threshold=0.05, # 允许5%的差额
output_path="financial_comparison.xlsx"
)
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
