A new and more generic question has been posted in pandas groupby: TOP 3 values in each group and store in DataFrame and a working solution has been answered there.
In this example I create a dataframe df
with some random data spaced 5 minutes.
I want to create a dataframe gdf
(grouped df) where the 3 highest values for each hour are listed.
I.e.: from this series of values
VAL
TIME
2017-12-08 00:00:00 29
2017-12-08 00:05:00 56
2017-12-08 00:10:00 82
2017-12-08 00:15:00 13
2017-12-08 00:20:00 35
2017-12-08 00:25:00 53
2017-12-08 00:30:00 25
2017-12-08 00:35:00 23
2017-12-08 00:40:00 21
2017-12-08 00:45:00 12
2017-12-08 00:50:00 15
2017-12-08 00:55:00 9
2017-12-08 01:00:00 13
2017-12-08 01:05:00 87
2017-12-08 01:10:00 9
2017-12-08 01:15:00 63
2017-12-08 01:20:00 62
2017-12-08 01:25:00 52
2017-12-08 01:30:00 43
2017-12-08 01:35:00 77
2017-12-08 01:40:00 95
2017-12-08 01:45:00 79
2017-12-08 01:50:00 77
2017-12-08 01:55:00 5
2017-12-08 02:00:00 78
2017-12-08 02:05:00 41
2017-12-08 02:10:00 10
2017-12-08 02:15:00 10
2017-12-08 02:20:00 88
?I am very close to the solution but I cannot find the correct syntax for the last step. What I get up to now (largest3
) is:
VAL
TIME TIME
2017-12-08 00:00:00 2017-12-08 00:10:00 82
2017-12-08 00:05:00 56
2017-12-08 00:25:00 53
2017-12-08 01:00:00 2017-12-08 01:40:00 95
2017-12-08 01:05:00 87
2017-12-08 01:45:00 79
2017-12-08 02:00:00 2017-12-08 02:20:00 88
2017-12-08 02:00:00 78
2017-12-08 02:05:00 41
from which I would like to obtain this gdf
(the time when each maximum was reached is not important):
VAL1 VAL2 VAL3
TIME
2017-12-08 00:00:00 82 56 53
2017-12-08 01:00:00 95 87 79
2017-12-08 02:00:00 88 78 41
This is the code:
import pandas as pd
from datetime import *
import numpy as np
# test data
df = pd.DataFrame()
date_ref = datetime(2017,12,8,0,0,0)
days = pd.date_range(date_ref, date_ref + timedelta(0.1), freq='5min')
np.random.seed(seed=1111)
data1 = np.random.randint(1, high=100, size=len(days))
df = pd.DataFrame({'TIME': days, 'VAL': data1})
df = df.set_index('TIME')
print(df)
print("----")
# groupby
group1 = df.groupby(pd.Grouper(freq='1H'))
largest3 = pd.DataFrame(group1['VAL'].nlargest(3))
print(largest3)
gdf = pd.DataFrame()
# ???? <-------------------
Thank you in advance.
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…