如何索引时间序列 Dataframe 中每一天的第一行和最后一行?

3npbholx  于 2022-09-21  发布在  其他
关注(0)|答案(3)|浏览(136)

因此,我有一个包含分钟股票数据的CSV文件,代码如下所示:

d = pd.read_csv('/Volumes/Seagate Portable/usindex_2020_all_tickers_awvbxk9/SPX_2020_2020.txt')
d.columns = ['Dates', 'Open', 'High', 'Low', 'Close']
d.set_index('Dates', inplace=True)
d.drop(['High', 'Low'], axis=1, inplace=True)
d = d.between_time('9:30', '16:00')

因此,每个指数都有年、日、月和时间。我要做的是通过使用日期,索引出当天的第一个和最后一个报价,在9:30和4:00之间。

输出如下所示:

Open    Close
Dates       
2020-01-02 09:31:00 3247.19 3245.22
2020-01-02 09:32:00 3245.07 3244.66
2020-01-02 09:33:00 3244.89 3247.61
2020-01-02 09:34:00 3247.38 3246.92
2020-01-02 09:35:00 3246.89 3249.09
... ... ...
2020-12-24 13:17:00 3703.06 3703.06
2020-12-24 13:18:00 3703.06 3703.06
2020-12-24 13:19:00 3703.06 3703.06
2020-12-24 13:20:00 3703.06 3703.06
2020-12-24 14:22:00 3703.06 3703.06

如代码所示,第一个和最后一个价格并不总是9:30和4:00,所以我正在尝试找到一种方法,无论何时都只索引第一个和最后一个价格。

0tdrvxhp

0tdrvxhp1#

使用GROUPBY:

d = d.between_time('9:30', '16:00')
d.groupby(pd.Grouper(freq='D')).agg({'Open':'first', 'Close':'last'})
5q4ezhmt

5q4ezhmt2#

如果上面的答案行得通,那就更短了,但我没有让它行得通。

import datetime as dt

d = pd.DataFrame({'Dates': ['2020-01-02 09:15:00', '2020-01-02 09:31:00', '2020-01-02 09:32:00', '2020-01-02 09:33:00', '2020-01-02 09:34:00', '2020-01-03 09:35:00', '2020-01-03 16:00:00'], 'Open': [3247.19, 3247.19, 3245.07, 3244.89, 3247.38, 3246.89, 3247.19], 'Close': [3245.22, 3245.22, 3244.66, 3247.61, 3246.92, 3249.09, 3245.22]})

# df['Dates'].astype('datetime64[ns]')

d['Dates']=d['Dates']

d['Dates'] = pd.to_datetime(d['Dates'])
d['just_date'] = d['Dates'].dt.date
d['just_time'] = d['Dates'].dt.time
d2=df[(d['just_time'] >= dt.time(9,30,0)) & (d['just_time'] <= dt.time(16,0,0))]

dmins=df.groupby('just_date').min()
dmaxs=df.groupby('just_date').max()

d2=dfmins.append(dfmaxs)
d2.sort_index(inplace=True)
d2
pgvzfuti

pgvzfuti3#

稍加修改即可获得更好的效果

import datetime as dt

d = pd.DataFrame({'Dates': ['2020-01-02 09:15:00', '2020-01-02 09:31:00', '2020-01-02 09:32:00', '2020-01-02 09:33:00', '2020-01-02 09:34:00', '2020-01-03 09:35:00', '2020-01-03 16:00:00'], 'Open': [3247.19, 3247.19, 3245.07, 3244.89, 3247.38, 3246.89, 3247.19], 'Close': [3245.22, 3245.22, 3244.66, 3247.61, 3246.92, 3249.09, 3245.22]})

# df['Dates'].astype('datetime64[ns]')

d['Dates']=d['Dates']

d['Dates'] = pd.to_datetime(d['Dates'])
d['just_date'] = d['Dates'].dt.date
d['just_time'] = d['Dates'].dt.time
d2=df[(d['just_time'] >= dt.time(9,30,0)) & (d['just_time'] <= dt.time(16,0,0))]

dmins=df.groupby('just_date').min()
dmaxs=df.groupby('just_date').max()

d2=dfmins.append(dfmaxs)
d2.sort_values(by='date',inplace=True)
d2

相关问题