python - 如何在 DatetimeIndex 的特定时间插入值

Question

我在下面有一个可重现的代码:

import pandas as pd
import datetime

foo = pd.read_csv("http://m.uploadedit.com/bbtc/1545406250692.txt", header=None, names=["Stock","Date","Time", "Open", "High", "Low", "Close", "Volume", "OI"], dtype={"Stock":"category"}, parse_dates= [['Date', 'Time']], index_col="Date_Time")
foo.sort_index(inplace=True)
bar = foo.between_time('09:00:00', '15:30:00') #Dropping post and pre market data i.e. from index 15:31 - 16:35

#resampling the data by 120 Minutes (2 hours)
twohour = bar.loc["2018-11-22 09:08:00":].resample('120Min',closed = 'right',label = 'left', base=75).agg({'Open': 'first', 'High': 'max', 'Low': 'min','Close': 'last'}).dropna()
twohour.head(7)

Out[]:
                    Close   High    Open    Low
Date_Time               
2018-11-22 07:15:00 321.3   321.30  321.30  321.30
2018-11-22 09:15:00 324.5   326.90  320.10  320.00
2018-11-22 11:15:00 323.2   324.85  324.60  322.20
2018-11-22 13:15:00 319.9   324.35  323.20  319.50
2018-11-22 15:15:00 320.0   320.35  319.85  319.15
2018-11-26 07:15:00 324.90  324.90  324.90  324.90
2018-11-26 09:15:00 311.35  324.40  323.10  309.60

我希望时间为 09:15:00 的索引中 Open 列中的每个值都替换为索引中 Close 列的值随着时间 07:15:00。

简而言之，我需要这个输出:

Out[]:
                    Close   High    Open    Low
Date_Time               
2018-11-22 07:15:00 321.3   321.30  321.30  321.30
2018-11-22 09:15:00 324.5   326.90  321.30  320.00
2018-11-22 11:15:00 323.2   324.85  324.60  322.20
2018-11-22 13:15:00 319.9   324.35  323.20  319.50
2018-11-22 15:15:00 320.0   320.35  319.85  319.15
2018-11-26 07:15:00 324.90  324.90  324.90  324.90
2018-11-26 09:15:00 311.35  324.40  324.90  309.60

我尝试通过将 DateTimeindex 转换为字典然后替换值来使用 .loc。但是字典没有排序，所以它需要对字典进行排序并且代码变得越来越难看。任何帮助将不胜感激。

Answer 1

您可以使用 loc 选择所需的行并将 Open 列设置为 close.shift

import datetime 

df.loc[df.index.time == datetime.time(9, 15), 'Open'] = df['Close'].shift(1)


                    Close   High    Open    Low
Date_Time               
2018-11-22 07:15:00 321.30  321.30  321.30  321.30
2018-11-22 09:15:00 324.50  326.90  321.30  320.00
2018-11-22 11:15:00 323.20  324.85  324.60  322.20
2018-11-22 13:15:00 319.90  324.35  323.20  319.50
2018-11-22 15:15:00 320.00  320.35  319.85  319.15
2018-11-26 07:15:00 324.90  324.90  324.90  324.90
2018-11-26 09:15:00 311.35  324.40  324.90  309.60

编辑:比较时间

import time
start = time.clock()
df.loc[df.index.time == datetime.time(9, 15), 'Open'] = df['Close'].shift(1)
print (time.clock() - start)

0.006845999999999464


start = time.clock()
mask_bool = (df.index - df.index.normalize()) == '09:15:00'
df['Open'] = df['Open'].mask(mask_bool, df['Close'].shift(1))
print (time.clock() - start)

0.009392999999999319