python - pandas groupby: TOP 3 values for each group

Question

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.

Answer 1

NOTE: This solution works only if each group has at least 3 rows

Try the following approach:

In [59]: x = (df.groupby(pd.Grouper(freq='H'))['VAL']
                .apply(lambda x: x.nlargest(3))
                .reset_index(level=1, drop=True)
                .to_frame('VAL'))

In [60]: x
Out[60]:
                     VAL
TIME
2017-12-08 00:00:00   82
2017-12-08 00:00:00   56
2017-12-08 00:00:00   53
2017-12-08 01:00:00   95
2017-12-08 01:00:00   87
2017-12-08 01:00:00   79
2017-12-08 02:00:00   88
2017-12-08 02:00:00   78
2017-12-08 02:00:00   41

In [61]: x.set_index(np.arange(len(x)) % 3, append=True)['VAL'].unstack().add_prefix('VAL')
Out[61]:
                     VAL0  VAL1  VAL2
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

Some explanation:

In [94]: x.set_index(np.arange(len(x)) % 3, append=True)
Out[94]:
                       VAL
TIME
2017-12-08 00:00:00 0   82
                    1   56
                    2   53
2017-12-08 01:00:00 0   95
                    1   87
                    2   79
2017-12-08 02:00:00 0   88
                    1   78
                    2   41

In [95]: x.set_index(np.arange(len(x)) % 3, append=True)['VAL'].unstack()
Out[95]:
                      0   1   2
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