Pandas排他左外连接重复行计数异常问题求助

我正在开发一款每日更新数据库的交易导入工具，通过Pandas读取包含全月交易数据的Excel文件，尝试将新DataFrame（df1）与现有DataFrame（df2）合并以筛选出仅有的新交易。使用Pandas merge执行排他左外连接时，遇到了完全重复行的计数问题：原数据中df1有两行相同的'A'交易，df2中有一行，按逻辑应该保留一行'A'在结果里，但当前方法把两行都删了。

补充说明：同日交易无时间信息仅含日期，且可追加历史日期的新交易。

示例代码 #

import pandas as pd
import numpy as np

df1 = pd.DataFrame(np.array([[pd.Timestamp('2023-1-1'), 'A', 10]
                            , [pd.Timestamp('2023-1-1'), 'A', 10]
                            , [pd.Timestamp('2023-1-1'), 'B', 11]
                            , [pd.Timestamp('2023-1-2'), 'C', 12]
                            , [pd.Timestamp('2023-1-2'), 'D', 13]
                            , [pd.Timestamp('2023-1-2'), 'E', 14]
                            , [pd.Timestamp('2023-1-3'), 'F', 15]]),
                   columns=['Date', 'Title', 'Amount'])

df2 = pd.DataFrame(np.array([[pd.Timestamp('2023-1-1'), 'A', 10]
                            , [pd.Timestamp('2023-1-1'), 'B', 11]
                            , [pd.Timestamp('2023-1-2'), 'C', 12]]),
                   columns=['Date', 'Title', 'Amount'])

df3 = pd.merge(df1, df2, on=['Date', 'Title', 'Amount'], how="outer", indicator=True)
df3 = df3[df3['_merge'] == 'left_only']

print(df1)
print(df2)
print(df3) # Both 'A' rows deleted while one 'A' row is new and should be in df3

当前输出 #

Date Title Amount
0 2023-01-01     A     10
1 2023-01-01     A     10
2 2023-01-01     B     11
3 2023-01-02     C     12
4 2023-01-02     D     13
5 2023-01-02     E     14
6 2023-01-03     F     15
        Date Title Amount
0 2023-01-01     A     10
1 2023-01-01     B     11
2 2023-01-02     C     12
        Date Title Amount     _merge
4 2023-01-02     D     13  left_only
5 2023-01-02     E     14  left_only
6 2023-01-03     F     15  left_only

核心问题是merge默认会匹配所有相同内容的行，无法区分同一交易组内的重复次数。解决思路是给每个交易组添加唯一序号，让重复行拥有可区分的标识，再基于标识做连接筛选。

方法一：添加组内序号后连接 #

这是最直观的方案，能保留原数据的行顺序和索引：

import pandas as pd
import numpy as np

df1 = pd.DataFrame(np.array([[pd.Timestamp('2023-1-1'), 'A', 10]
                            , [pd.Timestamp('2023-1-1'), 'A', 10]
                            , [pd.Timestamp('2023-1-1'), 'B', 11]
                            , [pd.Timestamp('2023-1-2'), 'C', 12]
                            , [pd.Timestamp('2023-1-2'), 'D', 13]
                            , [pd.Timestamp('2023-1-2'), 'E', 14]
                            , [pd.Timestamp('2023-1-3'), 'F', 15]]),
                   columns=['Date', 'Title', 'Amount'])

df2 = pd.DataFrame(np.array([[pd.Timestamp('2023-1-1'), 'A', 10]
                            , [pd.Timestamp('2023-1-1'), 'B', 11]
                            , [pd.Timestamp('2023-1-2'), 'C', 12]]),
                   columns=['Date', 'Title', 'Amount'])

# 给每个交易组添加序号，同一组内从1开始递增
df1['count'] = df1.groupby(['Date', 'Title', 'Amount']).cumcount() + 1
df2['count'] = df2.groupby(['Date', 'Title', 'Amount']).cumcount() + 1

# 基于包含序号的字段做排他左外连接
df3 = pd.merge(df1, df2, on=['Date', 'Title', 'Amount', 'count'], how="outer", indicator=True)
df3 = df3[df3['_merge'] == 'left_only']

# 移除临时添加的辅助列
df3 = df3.drop(['count', '_merge'], axis=1)

print(df3)

正确输出 #

Date Title Amount
1 2023-01-01     A     10
4 2023-01-02     D     13
5 2023-01-02     E     14
6 2023-01-03     F     15

方法二：基于计数差提取新行 #

如果不需要保留原索引，可先统计每组交易的数量差，再从df1中提取超出的行：

import pandas as pd
import numpy as np

df1 = pd.DataFrame(np.array([[pd.Timestamp('2023-1-1'), 'A', 10]
                            , [pd.Timestamp('2023-1-1'), 'A', 10]
                            , [pd.Timestamp('2023-1-1'), 'B', 11]
                            , [pd.Timestamp('2023-1-2'), 'C', 12]
                            , [pd.Timestamp('2023-1-2'), 'D', 13]
                            , [pd.Timestamp('2023-1-2'), 'E', 14]
                            , [pd.Timestamp('2023-1-3'), 'F', 15]]),
                   columns=['Date', 'Title', 'Amount'])

df2 = pd.DataFrame(np.array([[pd.Timestamp('2023-1-1'), 'A', 10]
                            , [pd.Timestamp('2023-1-1'), 'B', 11]
                            , [pd.Timestamp('2023-1-2'), 'C', 12]]),
                   columns=['Date', 'Title', 'Amount'])

# 统计两个DataFrame中每组交易的数量
count_df1 = df1.groupby(['Date', 'Title', 'Amount']).size().reset_index(name='cnt1')
count_df2 = df2.groupby(['Date', 'Title', 'Amount']).size().reset_index(name='cnt2')

# 合并计数并计算数量差，筛选出df1中数量更多的组
count_diff = pd.merge(count_df1, count_df2, on=['Date', 'Title', 'Amount'], how='left').fillna(0)
count_diff['diff'] = count_diff['cnt1'] - count_diff['cnt2']
count_diff = count_diff[count_diff['diff'] > 0]

# 从df1中提取每组超出的行
result = pd.DataFrame()
for _, row in count_diff.iterrows():
    group = df1[(df1['Date'] == row['Date']) & 
                (df1['Title'] == row['Title']) & 
                (df1['Amount'] == row['Amount'])]
    # 取该组最后N行（N为数量差），对应新增的交易
    result = pd.concat([result, group.tail(int(row['diff']))])

# 重置索引
result = result.reset_index(drop=True)
print(result)

输出结果 #

Date Title Amount
0 2023-01-01     A     10
1 2023-01-02     D     13
2 2023-01-02     E     14
3 2023-01-03     F     15

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