python - 使用来自单独数据帧的最新数据更新一个数据帧

Question

我正在尝试使用存储在单独数据框中的价格来更新头寸数据框。我想将最新价格放在“last_price”列中，并将该价格的数据放在“last_date”列中。

import datetime
from scipy import stats
import numpy as np
import pandas as pd


df_portfolio = pd.DataFrame({ 'amount' : np.random.randint(low=0, high=10,     size=(4)),
                    'timestamp' : pd.Timestamp('20130102'),
                    'exch' : pd.Categorical(["e1","e1","e2","e2"]),
                    'token' : pd.Categorical(["BTC","ETH","ETH","LTC"])
               })

df_ETH_price = pd.DataFrame({
    'date': ('2018-08-11','2018-08-12','2018-08-13'),
    'price' : (322.11,319.57,286.50)    
    })

df_portfolio['last_price'] = np.nan
df_portfolio['last_date'] = "?"

print(df_portfolio)
print (df_ETH_price)

预期结果如下:

   amount exch  timestamp token  last_price last_date
0       7   e1 2013-01-02   BTC         NaN         ?
1       4   e1 2013-01-02   ETH         286.50      2018-08-13
2       2   e2 2013-01-02   ETH         286.50      2018-08-13
3       9   e2 2013-01-02   LTC         NaN         ?

Answer 1

首先从 df_ETH_price 中的最新行创建一个数据框:

df_ETH_price['date'] = pd.to_datetime(df_ETH_price['date'])
latest = df_ETH_price.assign(token='ETH').sort_values('date', ascending=False).head(1)

print(latest)

        date  price token
2 2018-08-13  286.5   ETH

然后与df_portfolio合并:

res = pd.merge(df_portfolio, latest, how='left')

print(res)

   amount exch  timestamp token       date  price
0       1   e1 2013-01-02   BTC        NaT    NaN
1       3   e1 2013-01-02   ETH 2018-08-13  286.5
2       6   e2 2013-01-02   ETH 2018-08-13  286.5
3       0   e2 2013-01-02   LTC        NaT    NaN