如何用另一个数据框的值替换PANDAS DataFrame？

Question

我有2个dataframes（signal_df和price_df），可以使用以下代码生成。

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',
    ],
})


# convert the column (it's a string) to datetime type
datetime_series = pd.to_datetime(signal_df['date'])
# create datetime index passing the datetime series
datetime_index = pd.DatetimeIndex(datetime_series.values)
signal_df = signal_df.set_index(datetime_index)
signal_df.drop('date',axis=1,inplace=True)
print(signal_df)

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', 
    ]
})

# convert the column (it's a string) to datetime type
datetime_series = pd.to_datetime(price_df['date'])
# create datetime index passing the datetime series
datetime_index = pd.DatetimeIndex(datetime_series.values)
price_df = price_df.set_index(datetime_index)
price_df.drop('date',axis=1,inplace=True)
print(price_df)

如何创建看起来如下的第三个DataFrame（new_df）？

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

new_df是通过首先查找true值创建的Price_df中的相应值。

Answer 1

您可以做：

(price_df.loc[signal_df.where(signal_df.long).index].mask(
         signal_df.long.ne(True)).rename(columns={'close_price':'long'}))

输出：

                            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

Answer 2

您可以尝试bask long signal> signal_df列中的真实值

out = (signal_df['long'].mask(signal_df['long'].eq(True),
                              price_df.loc[signal_df.index, 'close_price'])
       .to_frame())

print(out)

                    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