如何在DataFrame中按日期聚合并生成基于涨跌均值的二元交易标签变量

需求说明 #

给DataFrame新增Expected二元分类列，取值为buy或sell：

• 按Date分组，计算该日期下Outcome为up和dn的Share Price均值
• 若up的均值高于dn的均值，标记为buy，否则标记为sell

示例数据构造 #

import pandas as pd

data = {
    'timestamp': ['10/05/2022 08:04', '10/05/2022 08:04', '10/05/2022 08:04', '10/05/2022 08:04', '11/05/2022 00:54', '11/05/2022 00:54'],
    'IFPID': ['SP3K4K5']*6,
    'Outcome': ['dn', 'up', 'up', 'dn', 'up', 'dn'],
    'Share Price': [36, 64, 65, 35, 57, 43],
    'Trade Qty': [100, 100, 100, 100, 64, 64],
    'Date': ['2022-10-05']*4 + ['2022-11-05']*2
}
df = pd.DataFrame(data)

实现步骤 #

1. 计算日期-结果维度的均值

先按Date和Outcome分组计算Share Price的均值，再转成宽表方便后续比较：

# 分组计算均值并转置为宽表
mean_price_df = df.groupby(['Date', 'Outcome'])['Share Price'].mean().unstack()
# 重命名列便于识别
mean_price_df.columns = ['dn_avg', 'up_avg']

2. 生成日期到标签的映射

对每个日期比较up_avg和dn_avg，生成buy/sell的映射字典：

date_label_map = mean_price_df.apply(
    lambda row: 'buy' if row['up_avg'] > row['dn_avg'] else 'sell',
    axis=1
).to_dict()

3. 给原表添加标签列

用map方法将映射应用到原DataFrame的Date列，生成Expected列：

df['Expected'] = df['Date'].map(date_label_map)

最终结果 #

执行上述代码后，原DataFrame会新增Expected列：

timestamp   IFPID Outcome  Share Price  Trade Qty        Date Expected
0  10/05/2022 08:04  SP3K4K5      dn           36        100  2022-10-05      buy
1  10/05/2022 08:04  SP3K4K5      up           64        100  2022-10-05      buy
2  10/05/2022 08:04  SP3K4K5      up           65        100  2022-10-05      buy
3  10/05/2022 08:04  SP3K4K5      dn           35        100  2022-10-05      buy
4  11/05/2022 00:54  SP3K4K5      up           57         64  2022-11-05      buy
5  11/05/2022 00:54  SP3K4K5      dn           43         64  2022-11-05      buy

简化实现（合并一步） #

也可以用merge直接将均值表合并到原表，再生成标签：

# 计算均值并合并到原表
mean_price_df = df.groupby(['Date', 'Outcome'])['Share Price'].mean().unstack().reset_index()
df = df.merge(mean_price_df, on='Date')
# 生成标签并清理临时列
df['Expected'] = df.apply(lambda row: 'buy' if row['up'] > row['dn'] else 'sell', axis=1)
df.drop(columns=['up', 'dn'], inplace=True)

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