Pandas：如何基于时序历史值生成cond2列及差值计算？

原始数据 #

import pandas as pd

data = {
    'index': ['2022-05-12', '2022-05-13', '2022-05-14', '2022-05-15', '2022-05-16',
              '2022-05-17', '2022-05-18', '2022-05-19', '2022-05-20', '2022-05-21',
              '2022-05-22', '2022-05-23', '2022-05-24', '2022-05-25'],
    'val1': [10,14,20,25,12,11,32,35,40,30,28,15,18,31],
    'cond1': [False,True,False,False,False,True,False,False,False,False,True,False,False,False],
    'calc1': [pd.NA,32,32,32,32,40,40,40,40,40,31,31,31,31]
}
df = pd.DataFrame(data).set_index('index')

输出的原始DataFrame：

val1  cond1 calc1
index                        
2022-05-12    10  False   NaN
2022-05-13    14   True    32
2022-05-14    20  False    32
2022-05-15    25  False    32
2022-05-16    12  False    32
2022-05-17    11   True    40
2022-05-18    32  False    40
2022-05-19    35  False    40
2022-05-20    40  False    40
2022-05-21    30  False    40
2022-05-22    28   True    31
2022-05-23    15  False    31
2022-05-24    18  False    31
2022-05-25    31  False    31

需求说明 #

• 新增cond2列：当cond1为True触发分组后，**该分组内首次出现val1等于对应calc1**的行标记为True，其余行（包括cond1为True的行）为False
• 新增diff列：仅在cond2为True的行，计算当前val1与对应分组中cond1为True行的val1的差值

错误尝试：直接使用apply匹配 #

df['cond2'] = df.apply(lambda x: True if x['val1'] == x['calc1'] else False, axis=1)

该方法仅逐行匹配val1和calc1，未考虑“cond1触发后首次匹配”的分组逻辑，导致结果不符合预期：

val1  cond1 calc1  cond2
index                        
2022-05-12    10  False   NaN  False
2022-05-13    14   True    32  False
2022-05-14    20  False    32  False
2022-05-15    25  False    32  False
2022-05-16    12  False    32  False
2022-05-17    11   True    40  False
2022-05-18    32  False    40  False
2022-05-19    35  False    40  False
2022-05-20    40  False    40   True
2022-05-21    30  False    40  False
2022-05-22    28   True    31  False
2022-05-23    15  False    31  False
2022-05-24    18  False    31  False
2022-05-25    31  False    31   True

高效解决方案（向量化操作，避免循环） #

步骤1：按cond1触发点生成分组标识 #

通过cumsum()为每个cond1为True的行及后续行分配唯一组号：

# 生成组标签：每次cond1为True时组号递增
df['group'] = df['cond1'].cumsum()
# 初始无触发的行（group=0）标记为缺失值
df.loc[df['group'] == 0, 'group'] = pd.NA

步骤2：生成cond2列 #

在每个分组内，标记首次出现val1 == calc1的行，同时排除cond1为True的本身行：

# 标记所有val1等于calc1的行
match_mask = df['val1'] == df['calc1']
# 对每个组，保留首次匹配的行（cumsum()==1表示首次出现True）
first_match = match_mask.groupby(df['group'], dropna=False).transform(lambda x: x.cumsum() == 1)
# 排除cond1为True的行，生成cond2
df['cond2'] = first_match & ~df['cond1']

步骤3：计算diff列 #

提取每个分组中cond1为True的val1基准值，匹配到对应cond2为True的行计算差值：

# 提取每个组的基准val1值（cond1为True的行）
group_base = df.loc[df['cond1'], ['group', 'val1']].set_index('group')['val1']
# 匹配基准值到对应组，仅在cond2为True时计算差值
df['diff'] = df['group'].map(group_base)
df['diff'] = df.apply(lambda x: x['val1'] - x['diff'] if x['cond2'] else pd.NA, axis=1)

步骤4：清理临时列（可选） #

df = df.drop('group', axis=1)

最终结果 #

val1  cond1 calc1  cond2  diff
index                        
2022-05-12    10  False   NaN  False   NaN
2022-05-13    14   True    32  False   NaN
2022-05-14    20  False    32  False   NaN
2022-05-15    25  False    32  False   NaN
2022-05-16    12  False    32  False   NaN
2022-05-17    11   True    40  False   NaN
2022-05-18    32  False    40   True  18.0
2022-05-19    35  False    40  False   NaN
2022-05-20    40  False    40  False   NaN
2022-05-21    30  False    40  False   NaN
2022-05-22    28   True    31  False   NaN
2022-05-23    15  False    31  False   NaN
2022-05-24    18  False    31  False   NaN
2022-05-25    31  False    31   True   3.0

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