我有一些财务数据,并希望只获得特定时间段(小时,天,月……)的最后一笔交易.
例：
>>dftime price_BRL qt time_dt1312001297 23.49 1.00 2011-07-30 04:48:171312049148 23.40 1.00 2011-07-30 18:05:481312121523 23.49 2.00 2011-07-31 14:12:031312121523 23.50 6.50 2011-07-31 14:12:031312177622 23.40 2.00 2011-08-01 05:47:021312206416 23.25 1.00 2011-08-01 13:46:561312637929 18.95 1.50 2011-08-06 13:38:491312637929 18.95 4.00 2011-08-06 13:38:491312817114 0.80 0.01 2011-08-08 15:25:141312818289 0.10 0.01 2011-08-08 15:44:491312819795 6.00 0.09 2011-08-08 16:09:551312847064 16.00 0.86 2011-08-08 23:44:241312849282 16.00 6.14 2011-08-09 00:21:221312898146 19.90 1.00 2011-08-09 13:55:461312915666 6.00 0.01 2011-08-09 18:47:461312934897 19.90 1.00 2011-08-10 00:08:17>>filter_by_last_day(df)time price_BRL qt time_dt1312049148 23.40 1.00 2011-07-30 18:05:481312121523 23.50 6.50 2011-07-31 14:12:031312206416 23.25 1.00 2011-08-01 13:46:561312637929 18.95 4.00 2011-08-06 13:38:491312847064 16.00 0.86 2011-08-08 23:44:241312915666 6.00 0.01 2011-08-09 18:47:461312934897 19.90 1.00 2011-08-10 00:08:17
我正在考虑使用groupby()并获得当天的平均值()这个解决方案也可以解决我的问题,但不完全正确)但不知道如何选择df.groupby这样的日子(‘time.day ‘).持续()
最佳答案
您可以在
dt.date之前使用groupby并在last之前聚合：
#if necessery convert to datetimedf.time_dt = pd.to_datetime(df.time_dt)df = df.groupby(df.time_dt.dt.date).last().reset_index(drop=True)print (df)time price_BRL qt time_dt0 1312049148 23.40 1.00 2011-07-30 18:05:481 1312121523 23.50 6.50 2011-07-31 14:12:032 1312206416 23.25 1.00 2011-08-01 13:46:563 1312637929 18.95 4.00 2011-08-06 13:38:494 1312847064 16.00 0.86 2011-08-08 23:44:245 1312915666 6.00 0.01 2011-08-09 18:47:466 1312934897 19.90 1.00 2011-08-10 00:08:17
谢谢MaxU的另一个解决方案 – 为返回DataFrame添加参数as_index = False：
df = df.groupby(df.time_dt.dt.date,as_index=False).last()print (df)time price_BRL qt time_dt0 1312049148 23.40 1.00 2011-07-30 18:05:481 1312121523 23.50 6.50 2011-07-31 14:12:032 1312206416 23.25 1.00 2011-08-01 13:46:563 1312637929 18.95 4.00 2011-08-06 13:38:494 1312847064 16.00 0.86 2011-08-08 23:44:245 1312915666 6.00 0.01 2011-08-09 18:47:466 1312934897 19.90 1.00 2011-08-10 00:08:17
使用resample的解决方案,但必须在dropna之前删除NaN行：
df = df.resample('d',on='time_dt').last().dropna(how='all').reset_index(drop=True)#cast column time to intdf.time = df.time.astype(int)print (df)time price_BRL qt time_dt0 1312049148 23.40 1.00 2011-07-30 18:05:481 1312121523 23.50 6.50 2011-07-31 14:12:032 1312206416 23.25 1.00 2011-08-01 13:46:563 1312637929 18.95 4.00 2011-08-06 13:38:494 1312847064 16.00 0.86 2011-08-08 23:44:245 1312915666 6.00 0.01 2011-08-09 18:47:466 1312934897 19.90 1.00 2011-08-10 00:08:17
—
你也可以使用dt.month：
df = df.groupby(df.time_dt.dt.month).last().reset_index(drop=True)print (df)time price_BRL qt time_dt0 1312121523 23.5 6.5 2011-07-31 14:12:031 1312934897 19.9 1.0 2011-08-10 00:08:17
有几个小时它有点复杂,如果需要groupby按日期和小时一起,解决方案是用astype将分钟和秒数替换为0：
hours = df.time_dt.values.astype('