如何在Pandas中对DataFrame列动态应用依赖历史行与列的函数

问题需求 #

需要在Pandas DataFrame的多行上应用自定义函数calcPerf，该函数依赖前一行数据以及对应前置列的On/Off状态值，具体要求：

• 所有以perf开头的列需更新，例如perf 60-40对应使用60-40列的状态值，perf 30-30对应30-30列的状态值
• 实际场景包含3000个此类列，必须实现动态批量处理
• 首行因无前置数据，默认保留0值

示例代码 #

import pandas as pd
df = {
    'open': ['4001','4010','4043','3924','4000'],
    'close': ['4002','4030','3901','3970','4009'],
    '50-20': ['On','On','On','On','On'],
    'perf 50-20':[0,0,0,0,0],
    '60-40': ['Off','Off','On','On','On'],
    'perf 60-40':[0,0,0,0,0],
    '30-30': ['On','Off','On','On','Off'],
    'perf 30-30':[0,0,0,0,0]
}
df = pd.DataFrame(df)

def calcPerf(currClose, prevClose, currOnOff,prevOnOff, lastCloseWhenOn, prevPerf, currOpen):
    if currOnOff == "On" and prevOnOff == 'On':
        return ((currClose/prevClose)-1)*100
    if currOnOff == "On" and prevOnOff == 'Off':
        return ((currOpen / lastCloseWhenOn )-1)*100
    if currOnOff == "Off" and prevOnOff == 'On':
        return ((currOpen / prevClose )-1)*100
    if currOnOff == "Off" and prevOnOff == 'Off':
        return 0

解决方案 #

1. 转换数据类型 #

首先将open和close列转为数值类型，否则无法执行计算：

df['open'] = df['open'].astype(float)
df['close'] = df['close'].astype(float)

2. 批量处理所有perf列 #

提取所有perf列，逐个匹配对应的状态列，逐行计算结果（同时跟踪lastCloseWhenOn状态变量）：

# 获取所有以perf开头的列
perf_cols = [col for col in df.columns if col.startswith('perf')]

for perf_col in perf_cols:
    # 匹配对应的状态列（如"perf 50-20"对应"50-20"）
    status_col = perf_col.replace('perf ', '')
    
    # 初始化状态变量：记录上一次On状态时的close值
    last_close_when_on = df.loc[0, 'close'] if df.loc[0, status_col] == 'On' else None
    
    # 从第二行开始逐行计算
    for i in range(1, len(df)):
        # 获取当前行和前一行的所需数据
        curr_close = df.loc[i, 'close']
        prev_close = df.loc[i-1, 'close']
        curr_onoff = df.loc[i, status_col]
        prev_onoff = df.loc[i-1, status_col]
        curr_open = df.loc[i, 'open']
        prev_perf = df.loc[i-1, perf_col]
        
        # 更新last_close_when_on：当前状态为On时，记录当前close值
        if curr_onoff == 'On':
            last_close_when_on = curr_close
        
        # 调用calcPerf计算当前perf值
        df.loc[i, perf_col] = calcPerf(curr_close, prev_close, curr_onoff, prev_onoff, last_close_when_on, prev_perf, curr_open)

3. 优化建议（针对3000列场景） #

如果3000列的循环效率不足，可做以下优化：

• 减少df.loc的调用次数：提前将需要的列转为数组，直接通过索引访问
• 使用numba库对循环进行JIT编译，加速数值计算
• 若状态逻辑允许，尝试将部分计算向量化（但因lastCloseWhenOn是状态变量，逐行处理仍是最直接的方案）

预期输出示例 #

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