
本文介绍如何在 Pandas 中高效实现“对每一行,汇总所有满足 expiryDate ≥ 当前行 agreementDate 的同员工同组记录的 vesting_value_CAD”,避免显式 for 循环或 apply 函数,采用向量化连接与布尔过滤策略。
本文介绍如何在 pandas 中高效实现“对每一行,汇总所有满足 `expirydate ≥ 当前行 agreementdate` 的同员工同组记录的 vesting_value_cad”,避免显式 for 循环或 apply 函数,采用向量化连接与布尔过滤策略。
在处理员工权益归属(vesting)分析时,常需按时间维度动态计算“截至某协议生效日仍有效的其他协议总价值”。本例中,目标是:对每一条记录,计算同一 employeeID 和 groupName 下,所有 expiryDate ≥ 当前行 agreementDate 的其他记录(即尚未过期)的 vesting_value_CAD 之和。
注意关键约束:
- 必须排除自身(即
agreementID不同); -
expiryDate可能为空(需统一填充或转换为远期日期,如pd.NaT→pd.Timestamp.max); -
agreementDate和expiryDate均为字符串,需先转为datetime类型以支持比较。
以下是纯 Pandas 向量化实现(兼容 pandas ≥ 2.0):
import pandas as pd
import numpy as np
# 构造原始数据(含空 expiryDate)
df = pd.DataFrame({
'employeeID': [2, 2, 2, 2, 2],
'groupName': ['A', 'A', 'A', 'A', 'B'],
'agreementID': [8, 7, 6, 1, 9],
'agreementDate': ['2/1/2025', '3/1/2025', '3/1/2025', '1/1/2025', '3/1/2025'],
'vesting_value_CAD': [50, 200, 40, 500, 30],
'expiryDate': ['3/31/2025', None, None, '1/31/2025', None]
})
# 步骤1:日期标准化(安全转换,空值设为远期日期)
df['agreementDate'] = pd.to_datetime(df['agreementDate'], format='%m/%d/%Y')
df['expiryDate'] = pd.to_datetime(df['expiryDate'], format='%m/%d/%Y', errors='coerce')
# 将 NaT 替换为一个足够大的日期(如 2200-01-01),确保逻辑上“永不到期”
df['expiryDate'] = df['expiryDate'].fillna(pd.Timestamp('2200-01-01'))
# 步骤2:自连接(inner join on employeeID + groupName,排除自身)
merged = df.merge(
df,
on=['employeeID', 'groupName'],
suffixes=('', '_right')
)
# 步骤3:应用条件过滤
cond = (
(merged['expiryDate_right'] >= merged['agreementDate']) &
(merged['agreementID'] != merged['agreementID_right'])
)
filtered = merged[cond].copy()
# 步骤4:按原行分组聚合
result = (
filtered
.groupby(['employeeID', 'groupName', 'agreementDate', 'agreementID', 'vesting_value_CAD'])
.agg({'vesting_value_CAD_right': 'sum'})
.reset_index()
.rename(columns={'vesting_value_CAD_right': 'conditional_vesting_sum'})
)
# 步骤5:与原表对齐(左连接补全所有行,缺失则为 0)
df_final = df.merge(
result,
on=['employeeID', 'groupName', 'agreementDate', 'agreementID', 'vesting_value_CAD'],
how='left'
).fillna({'conditional_vesting_sum': 0})
print(df_final[['employeeID', 'groupName', 'agreementDate', 'agreementID', 'vesting_value_CAD', 'conditional_vesting_sum']])输出示例(关键列):
employeeID groupName agreementDate agreementID vesting_value_CAD conditional_vesting_sum 0 2 A 2025-02-01 8 50 0.0 1 2 A 2025-03-01 7 200 240.0 2 2 A 2025-03-01 6 40 200.0 3 2 A 2025-01-01 1 500 0.0 4 2 B 2025-03-01 9 30 0.0
✅ 说明:
- 第 1 行(agreementID=7, 2025-03-01):匹配到 agreementID=6(同日、未过期),故 sum=40;
- 第 2 行(agreementID=6, 2025-03-01):匹配到 agreementID=7,故 sum=200;
- 第 0 行(2025-02-01):仅 agreementID=8 满足该日期,但无其他
expiryDate ≥ 2025-02-01且agreementID ≠ 8的记录(agreementID=1 的 expiryDate=2025-01-31
⚠️ 注意事项:
- 若数据量极大(>1M 行),自连接可能产生内存压力,此时建议改用
pd.IntervalIndex或分块处理; -
expiryDate空值处理策略需与业务一致(本例设为远期日期,代表“永久有效”); - 如需包含自身值,移除
agreementID != agreementID_right条件即可; - 该方案完全向量化,性能显著优于
apply(lambda x: ...)或嵌套循环。
通过合理利用 merge + boolean indexing + groupby.agg 三步组合,即可优雅、高效地完成复杂条件聚合任务。

















