如何高效基于多条件比较Pandas DataFrame中的每行与其他行？

数据背景 #

我有一个包含多列的Python Pandas DataFrame，列信息及初始化代码如下：

import pandas as pd
import numpy as np

dict = {'Payee Name':["John", "John", "John", "Sam", "Sam"],
        'Amount': [100, 30, 95, 30, 30],
        'Payment Method':['Cheque', 'Electronic', 'Electronic', 'Cheque', 'Electronic'], 
        'Payment Reference Number' : [1,2,3,4,5],
        'Payment Date' : ['1/1/2022', '1/2/2022', '1/3/2022', '1/4/2022','1/5/2022']
        }
 
df = pd.DataFrame(dict)
df['Payment Date'] = pd.to_datetime(df['Payment Date'],format='%d/%m/%Y')

列说明 #

• Payee Name - 收款人姓名
• Amount - 支付金额
• Payment Method - 支付方式（仅为"Cheque"或"Electronic"）
• Payment Reference Number - 支付参考编号
• Payment Date - 支付日期

示例DataFrame内容：

Payee Name  Amount  Payment Method  Payment Reference Number    Payment Date
0   John    100     Cheque          1                           2022-01-01
1   John    30      Electronic      2                           2022-02-01
2   John    95      Electronic      3                           2022-03-01
3   Sam     30      Cheque          4                           2022-04-01
4   Sam     30      Electronic      5                           2022-05-01

需求说明 #

需要生成报告，识别同一收款人、不同支付方式下，金额相同或相差±10%的支付记录。比较需满足以下条件：

1. 同一收款人
2. 不同支付方式
3. 金额相同或差值在10%以内

满足条件时，新增的Check列赋值规则：

1. 金额相同则赋值"Yes - same amount"
2. 金额差值≤10%则赋值"Yes - within 10%"

现有问题 #

我编写了双重循环的代码实现需求，但性能极差：1300行数据耗时约7分钟，实际数据量达20万行，完全无法适用。现有代码如下：

df['Check'] =  0
limit = 0.1  # to set the threshold for the payment difference

for i in df.index:
    for j in df.index:
        if df['Amount'].iloc[i] == df['Amount'].iloc[j] and df['Payee Name'].iloc[i] == df['Payee Name'].iloc[j] and df['Payment Method'].iloc[i] != df['Payment Method'].iloc[j] and i != j:
            df['Check'].iloc[i] = "Yes - same amount"
            break
        else:
            change = df['Amount'].iloc[j] / df['Amount'].iloc[i] - 1
            if change > -limit and change < limit and df['Payee Name'].iloc[i] == df['Payee Name'].iloc[j] and df['Payment Method'].iloc[i] != df['Payment Method'].iloc[j] and i != j:
                df['Check'].iloc[i] = "Yes - within 10%"
                break

执行后预期结果：

Payee Name  Amount  Payment Method  Payment Reference Number    Payment Date    Check
0   John    100     Cheque          1                  2022-01-01   Yes - within 10%
1   John    30      Electronic      2                  2022-02-01   0
2   John    95      Electronic      3                  2022-03-01   Yes - within 10%
3   Sam     30      Cheque          4                  2022-04-01   Yes - same amount
4   Sam     30      Electronic      5                  2022-05-01   Yes - same amount

优化方案 #

思路：分组+合并，利用Pandas矢量化操作替代循环 #

核心是按Payee Name分组，将每个收款人的Cheque和Electronic记录分开，再进行金额匹配，避免全量遍历。

优化代码实现

import pandas as pd
import numpy as np

# 初始化数据（同原代码）
dict = {'Payee Name':["John", "John", "John", "Sam", "Sam"],
        'Amount': [100, 30, 95, 30, 30],
        'Payment Method':['Cheque', 'Electronic', 'Electronic', 'Cheque', 'Electronic'], 
        'Payment Reference Number' : [1,2,3,4,5],
        'Payment Date' : ['1/1/2022', '1/2/2022', '1/3/2022', '1/4/2022','1/5/2022']
        }
 
df = pd.DataFrame(dict)
df['Payment Date'] = pd.to_datetime(df['Payment Date'],format='%d/%m/%Y')
df['Check'] = 0  # 初始化Check列

limit = 0.1

# 按收款人分组处理
for name, group in df.groupby('Payee Name'):
    # 拆分两种支付方式的记录
    cheque = group[group['Payment Method'] == 'Cheque']
    electronic = group[group['Payment Method'] == 'Electronic']
    
    if cheque.empty or electronic.empty:
        continue  # 该收款人只有一种支付方式，跳过
    
    # 1. 匹配金额完全相同的记录
    # 合并同一收款人、金额相同的不同支付方式记录
    same_amount = pd.merge(cheque, electronic, on=['Payee Name', 'Amount'], how='outer')
    # 获取符合条件的索引
    same_idx = pd.concat([same_amount['Payment Reference Number_x'], same_amount['Payment Reference Number_y']]).dropna()
    # 更新Check列
    df.loc[df['Payment Reference Number'].isin(same_idx), 'Check'] = "Yes - same amount"
    
    # 2. 匹配金额差值在10%以内的记录（排除已匹配到相同金额的）
    remaining_cheque = cheque[~cheque['Payment Reference Number'].isin(same_idx)]
    remaining_electronic = electronic[~electronic['Payment Reference Number'].isin(same_idx)]
    
    if remaining_cheque.empty or remaining_electronic.empty:
        continue
    
    # 计算金额的上下限：当前金额的90%到110%
    remaining_cheque['lower'] = remaining_cheque['Amount'] * (1 - limit)
    remaining_cheque['upper'] = remaining_cheque['Amount'] * (1 + limit)
    
    # 交叉合并，检查金额是否在区间内
    cross = remaining_cheque.merge(remaining_electronic, on='Payee Name', suffixes=('_c', '_e'))
    within_range = cross[(cross['Amount_e'] >= cross['lower']) & (cross['Amount_e'] <= cross['upper'])]
    
    # 获取符合条件的索引
    within_idx = pd.concat([within_range['Payment Reference Number_c'], within_range['Payment Reference Number_e']]).dropna()
    df.loc[df['Payment Reference Number'].isin(within_idx), 'Check'] = "Yes - within 10%"

性能提升原理 #

1. 避免全量双重循环：原代码是O(n²)的时间复杂度，优化后按分组处理，时间复杂度大幅降低，适合大样本量。
2. 利用Pandas矢量化操作：merge、isin等操作都是底层优化的矢量化计算，比Python循环快几个数量级。
3. 分层匹配：先匹配完全相同金额，再处理差值范围内的，减少不必要的计算。

进一步优化方向 #

如果数据量特别大（20万行），可以考虑：

• 使用numba对分组内的计算进行加速
• 对Amount列进行分箱预处理，缩小匹配范围
• 利用Dask进行并行处理，适合超大数据集

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