Pandas如何用另一DataFrame的值替换目标DataFrame对应值

需求描述 #

现有两个带Datetime时间索引的DataFrame：

• signal_df：存储交易多空信号，long列为True时代表对应时间点触发做多信号，其余值为NaN
• price_df：存储固定频率K线收盘价，列名为close_price，时间索引覆盖signal_df的所有时间点
  需要生成新表new_df，规则如下：

1. 索引和signal_df完全一致
2. 仅在signal_df的long列值为True的行，填充同时间点price_df对应的close_price值
3. 其余行的long列值保留为NaN

预期输出结构：

new_df
                     long 
2020-01-01 19:15:00  2
2020-01-01 20:00:00  15
2020-01-01 22:15:00   NaN
2020-01-01 22:45:00  16
2020-01-02 00:30:00   NaN

测试数据生成代码 #

import pandas as pd
import numpy as np

signal_df = pd.DataFrame({
    'long':[
        True ,True, np.nan, True, np.nan
    ],
    'short':[
        np.nan, np.nan, True, np.nan, True  
    ],
    'date':[
        '2020-01-01 19:15', '2020-01-01 20:00', '2020-01-01 22:15', '2020-01-01 22:45', '2020-01-02 00:30',
    ],
})

# 转换字符串日期为datetime类型并设置为索引
datetime_series = pd.to_datetime(signal_df['date'])
datetime_index = pd.DatetimeIndex(datetime_series.values)
signal_df = signal_df.set_index(datetime_index)
signal_df.drop('date',axis=1,inplace=True)

price_df = pd.DataFrame({
    'close_price':[
        30,  2,  3, 29,
        15,  6, 19, 56,
        9 , 38, 41, 12,
        23, 14, 15, 16,
        38, 18, 19, 20,
        21, 22, 23, 33,
        25, 26, 10, 28
    ],
    'date':[
        '2020-01-01 19:00', '2020-01-01 19:15', '2020-01-01 19:30', '2020-01-01 19:45', 
        '2020-01-01 20:00', '2020-01-01 20:15', '2020-01-01 20:30', '2020-01-01 20:45', 
        '2020-01-01 21:00', '2020-01-01 21:15', '2020-01-01 21:30', '2020-01-01 21:45', 
        '2020-01-01 22:00', '2020-01-01 22:15', '2020-01-01 22:30', '2020-01-01 22:45', 
        '2020-01-01 23:00', '2020-01-01 23:15', '2020-01-01 23:30', '2020-01-01 23:45', 
        '2020-01-02 00:00', '2020-01-02 00:15', '2020-01-02 00:30', '2020-01-02 00:45', 
        '2020-01-02 01:00', '2020-01-02 01:15', '2020-01-02 01:30', '2020-01-02 01:45', 
    ]
})

datetime_series = pd.to_datetime(price_df['date'])
datetime_index = pd.DatetimeIndex(datetime_series.values)
price_df = price_df.set_index(datetime_index)
price_df.drop('date',axis=1,inplace=True)

实现代码 #

利用pandas的索引自动对齐特性，无需手写循环，3行代码即可完成需求：

# 初始化新表，索引和signal_df保持一致
new_df = pd.DataFrame(index=signal_df.index)
# 仅筛选long为True的时间点，匹配对应收盘价，其余位置自动填充NaN
new_df['long'] = price_df.loc[signal_df['long'].eq(True), 'close_price']

结果验证 #

运行代码后打印new_df即可得到完全符合预期的结果：

long
2020-01-01 19:15:00   2.0
2020-01-01 20:00:00  15.0
2020-01-01 22:15:00   NaN
2020-01-01 22:45:00  16.0
2020-01-02 00:30:00   NaN

如果需要同步处理short列的信号，直接追加一行代码即可：
new_df['short'] = price_df.loc[signal_df['short'].eq(True), 'close_price']

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