如何在DataFrame中按列容差查找重复项（金额允许1美元误差）

原始数据 #

我们有如下员工费用报销的DataFrame：

import pandas as pd

data = {'Claim ID': [1, 2, 3, 4, 5, 6, 7],
        'User': ['John', 'John', 'Jake', 'Bob', 'Bob', 'Tom', 'Tom'],
        'Category': ['Meal', 'Meal', 'Stationary', 'Phone Charges', 'Phone Charges', 'Transport', 'Transport'],
        'Amount': [12.00, 13.00, 20.00, 30, 30, 60, 60]}

df = pd.DataFrame(data)

输出结果：

Claim ID  User       Category  Amount
0         1  John           Meal    12.0
1         2  John           Meal    13.0
2         3  Jake     Stationary    20.0
3         4   Bob  Phone Charges    30.0
4         5   Bob  Phone Charges    30.0
5         6   Tom      Transport    60.0
6         7   Tom      Transport    60.0

原精确匹配实现 #

此前通过User、Amount、Category精确匹配查找重复项，并为每组重复项分配唯一组号，代码及结果如下：

# 为每组重复项分配唯一组号
conditions = ['User', 'Amount', 'Category']
df['Group'] = df.groupby(conditions).ngroup().add(1)

# 过滤掉仅含单条记录的组
df = df[df.groupby('Group')['Group'].transform('count') > 1]

输出结果：

Claim ID User       Category  Amount  Group
3         4  Bob  Phone Charges    30.0      1
4         5  Bob  Phone Charges    30.0      1
5         6  Tom      Transport    60.0      5
6         7  Tom      Transport    60.0      5

需求调整 #

现在需要调整规则：仅要求User和Category相同，金额无需完全一致，允许±1美元的容差，同时为符合条件的重复项分配唯一组号，并过滤掉单条记录的组。预期输出如下：

Claim ID  User       Category  Amount  Group
0         1  John           Meal    12.0      1
1         2  John           Meal    13.0      1
3         4   Bob  Phone Charges    30.0      2
4         5   Bob  Phone Charges    30.0      2
5         6   Tom      Transport    60.0      3
6         7   Tom      Transport    60.0      3

解决方案代码 #

可以利用DBSCAN聚类算法，在每个User-Category组内根据金额容差聚类，实现如下：

import pandas as pd
from sklearn.cluster import DBSCAN

# 初始化原始数据
data = {'Claim ID': [1, 2, 3, 4, 5, 6, 7],
        'User': ['John', 'John', 'Jake', 'Bob', 'Bob', 'Tom', 'Tom'],
        'Category': ['Meal', 'Meal', 'Stationary', 'Phone Charges', 'Phone Charges', 'Transport', 'Transport'],
        'Amount': [12.00, 13.00, 20.00, 30, 30, 60, 60]}
df = pd.DataFrame(data)

# 定义函数：在组内根据金额容差分配组号
def assign_group(group):
    # DBSCAN聚类，eps=1表示金额差≤1的归为一类
    dbscan = DBSCAN(eps=1, min_samples=2)
    clusters = dbscan.fit_predict(group[['Amount']])
    group['Cluster'] = clusters
    # 仅保留聚类成功的组（排除-1，即孤立点）
    group = group[group['Cluster'] != -1]
    return group

# 按User和Category分组处理
df_grouped = df.groupby(['User', 'Category'], group_keys=False).apply(assign_group)

# 全局分配唯一组号
df_grouped['Group'] = df_grouped.groupby(['User', 'Category', 'Cluster']).ngroup().add(1)
# 清理临时列
df_result = df_grouped.drop('Cluster', axis=1)

print(df_result)

执行后输出结果与预期一致。

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