Pandas条件连接与计算：基于日期和股票代码的左连接及匹配值相乘

问题需求 #

现有两个DataFrame：

• df_stock_prices：宽格式，日期为索引，列是股票代码，值为当日股价
• df_sentiment_mean：长格式，日期为索引，包含ticker（股票代码）和diff（情绪差值）列

需要完成：

1. 基于Date和ticker做左连接，匹配对应日期的股票数据
2. 匹配成功的行，将股价与情绪差值相乘
3. 无匹配项的行，保留原股价数据

数据示例 #

df_stock_prices #

AAPL.OQ  ABBV.N   ABT.N   ACN.N  ADBE.OQ  AIG.N  AMD.OQ  AMGN.OQ  
Date                                                                           
2021-11-29   160.24  116.89  128.03  365.82   687.49  54.95  161.91   203.47   
2021-11-30   165.30  115.28  125.77  357.40   669.85  52.60  158.37   198.88   
2021-12-01   164.77  115.91  126.74  360.14   657.41  51.72  149.11   200.80   

df_sentiment_mean #

ticker      diff
Date                          
2021-11-29  AAPL.OQ  0.163266
2021-11-29   ABBV.N -0.165520
2021-11-29    ABT.N  0.149920
2021-11-29  ADBE.OQ -0.014639
2021-11-29    AIG.N -0.448595

期望结果（首行） #

AAPL.OQ         ABBV.N      ABT.N        ACN.N     ADBE.OQ       AIG.N  …
Date
2021-11-29  26.16174384    -19.3476328  19.1942576  365.82    -10.06416611  -24.65029525  …

解决方案代码 #

import pandas as pd

# 1. 将宽格式的股价表转成长格式，方便匹配
df_prices_long = df_stock_prices.reset_index().melt(
    id_vars='Date',
    var_name='ticker',
    value_name='price'
)

# 2. 左连接情绪表，获取对应的diff值
df_merged = df_prices_long.merge(
    df_sentiment_mean.reset_index(),
    on=['Date', 'ticker'],
    how='left'
)

# 3. 计算结果：匹配到diff则相乘，否则保留原price
df_merged['result'] = df_merged.apply(
    lambda row: row['price'] * row['diff'] if pd.notna(row['diff']) else row['price'],
    axis=1
)

# 4. 将长格式转回宽格式，恢复原表结构
df_new = df_merged.pivot(
    index='Date',
    columns='ticker',
    values='result'
).reindex(columns=df_stock_prices.columns)  # 保证列顺序和原表一致

代码解释 #

1. 转成长格式：用melt把原宽表的股票列转成ticker行，和情绪表结构对齐，方便按Date和ticker匹配。
2. 左连接：通过merge的how='left'确保所有原股价数据都被保留，匹配不到情绪值的行，diff列会是NaN。
3. 计算乘积：用apply判断diff是否存在，存在则计算股价×差值，否则直接用原股价。
4. 转回宽格式：用pivot恢复成原表的宽格式，并用reindex保证列顺序和原股价表完全一致。

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