Pandas按Symbol分组计算Lower Low时遇KeyError: 'Helper_L'求助

我有一个包含多种Symbol及对应价格的mod_df数据框，['Lower Low']列用于识别Lower Low价格值。以下未分组的代码可正常运行：

import pandas as pd
import numpy as np

data = {'Symbol': ['A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
        'Date': ['2023-05-15 15:00:00', '2023-05-15 22:00:00', '2023-05-16 07:00:00', '2023-05-16 14:00:00',
                 '2023-05-17 07:00:00', '2023-05-17 20:00:00', '2023-05-18 02:00:00', '2023-05-18 16:00:00',
                 '2023-05-19 07:00:00', '2023-05-22 09:00:00', '2023-05-15 00:00:00',
                 '2023-05-16 12:00:00', '2023-05-17 06:00:00', '2023-05-18 02:00:00'],
        'Price': [0.90065, 0.90042, 0.89841, 0.89462, 0.89437, 0.89455, 0.89248, 0.89013, 0.89405, 0.89424, 0.59601,
                  0.59548, 0.59444, 0.59527],
        'Helper_L': [0, 0, 0, 0, 0, 1, 1, 1, 2, 3, 0, 0, 0, 1],
        }

mod_df = pd.DataFrame(data)
mod_df['Lower Low'] = np.where((mod_df['Helper_L'] != mod_df['Helper_L'].shift(-1))
                               & (mod_df['Price'] < mod_df['Price'].shift(1))
                               & (mod_df['Price'] < mod_df['Price'].shift(-1)), 'Lower Low', '')

print(mod_df)

运行输出：

Symbol                 Date   Price  Helper_L Lower Low
0       A  2023-05-15 15:00:00  0.90065         0          
1       A  2023-05-15 22:00:00  0.90042         0          
2       A  2023-05-16 07:00:00  0.89841         0          
3       A  2023-05-16 14:00:00  0.89462         0          
4       A  2023-05-17 07:00:00  0.89437         0  Lower Low
5       A  2023-05-17 20:00:00  0.89455         1          
6       A  2023-05-18 02:00:00  0.89248         1          
7       A  2023-05-18 16:00:00  0.89013         1  Lower Low
8       A  2023-05-19 07:00:00  0.89405         2          
9       A  2023-05-22 09:00:00  0.89424         3          
10      B  2023-05-15 00:00:00  0.59601         0          
11      B  2023-05-16 12:00:00  0.59548         0          
12      B  2023-05-17 06:00:00  0.59444         0  Lower Low
13      B  2023-05-18 02:00:00  0.59527         1          

由于数据框包含不同Symbol，我尝试用以下按Symbol分组的代码生成['Lower Low']列，却报错KeyError: 'Helper_L'：

mod_df['Lower Low'] = mod_df.groupby('Symbol')[['Helper_L','Price']]\
    .transform(lambda df: np.where((df['Helper_L'] !=df['Helper_L'].shift(-1)) & (df['Price'] < df['Price'].shift(1))
                                   & (df['Price'] < df['Price'].shift(-1)), 'Lower Low', ''))

修复方案 #

替换为以下代码即可解决问题：

mod_df['Lower Low'] = mod_df.groupby('Symbol').apply(
    lambda group: np.where(
        (group['Helper_L'] != group['Helper_L'].shift(-1))
        & (group['Price'] < group['Price'].shift(1))
        & (group['Price'] < group['Price'].shift(-1)),
        'Lower Low',
        ''
    )
).explode().values

修复说明 #

• 用groupby.apply替代transform：apply支持对每个分组的子DataFrame执行完整逻辑，返回结果可通过explode展平后与原数据框索引对齐。
• 确保shift操作在当前Symbol的分组内执行，实现每个Symbol独立判断Lower Low的需求，而非全局偏移。

运行后结果与原未分组逻辑一致，且实现了按Symbol分组判断的目标。

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