如何修改Pandas代码识别两个DataFrame中7天内的重复交易

我有两个包含重复交易的DataFrame，同一笔交易在两个DataFrame中的记录日期可能存在差异，但均处于7天的时间窗口内。尝试基于±7天的日期范围筛选出重复交易，但未能成功实现。

原代码仅通过完全匹配的Date和Amount进行DataFrame合并，再对比Category字段标注类别差异，代码如下：

import pandas as pd

# convert the 'Date' column of both dataframes to datetime objects
df1['Date'] = pd.to_datetime(df1['Date'])
df2['Date'] = pd.to_datetime(df2['Date'])

# merge the two dataframes based on 'Amount' and 'Date'
merged_df = pd.merge(df1, df2, on=['Amount', 'Date'], how='outer', suffixes=['_df1', '_df2'])

# create a new column 'Differences' to store the differences in category
merged_df['Differences'] = ''

# iterate over the rows of the merged dataframe
for index, row in merged_df.iterrows():
    # if the row is unique to df1
    if pd.isnull(row['Category_df2']):
        merged_df.at[index, 'Differences'] = 'Unique to first dataframe'
    # if the row is unique to df2
    elif pd.isnull(row['Category_df1']):
        merged_df.at[index, 'Differences'] = 'Unique to second dataframe'
    # if the row is present in both dataframes
    else:
        # if the categories are different
        if row['Category_df1'] != row['Category_df2']:
            merged_df.at[index, 'Differences'] = 'Category is different'
        # if the categories are the same
        else:
            merged_df.at[index, 'Differences'] = 'Category is the same'

# print the merged dataframe with the differences highlighted in the 'Differences' column
print(merged_df)

修改后的代码 #

import pandas as pd

# 转换日期列为datetime类型
df1['Date'] = pd.to_datetime(df1['Date'])
df2['Date'] = pd.to_datetime(df2['Date'])

# 先按Amount全外连接，保留所有金额匹配的可能项
merged_pre = pd.merge(df1, df2, on='Amount', how='outer', suffixes=['_df1', '_df2'])

# 计算日期差绝对值，筛选出±7天内的有效匹配
date_diff = abs(merged_pre['Date_df1'] - merged_pre['Date_df2'])
valid_matches = date_diff <= pd.Timedelta(days=7)

# 拆分三类数据：仅df1存在、仅df2存在、7天内匹配成功
df1_unique = merged_pre[merged_pre['Date_df2'].isna()].copy()
df2_unique = merged_pre[merged_pre['Date_df1'].isna()].copy()
matched_rows = merged_pre[valid_matches & ~merged_pre['Date_df1'].isna() & ~merged_pre['Date_df2'].isna()].copy()

# 合并所有结果并生成差异标注列
result = pd.concat([df1_unique, df2_unique, matched_rows], ignore_index=True)

def get_diff_label(row):
    if pd.isna(row['Category_df2']):
        return 'Unique to first dataframe'
    elif pd.isna(row['Category_df1']):
        return 'Unique to second dataframe'
    else:
        return 'Category is different' if row['Category_df1'] != row['Category_df2'] else 'Category is the same'

result['Differences'] = result.apply(get_diff_label, axis=1)

print(result)

关键改动说明 #

• 放弃精确日期匹配，先仅按Amount做全外连接，扩大潜在匹配范围
• 通过计算日期差绝对值并与7天时间间隔对比，筛选出符合时间窗口的交易
• 拆分不同类型的行数据，避免无效的跨金额匹配混入结果
• 用apply替代逐行循环，提升处理效率的同时保留原有的类别差异判断逻辑

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