Find nlargest(2) with corresponding value after groupby

user_8878774 0
Viewed 62

I have a Dataframe as below:

Datetime             Volume       Price
2020-08-05 09:15:00  1033         504
2020-08-05 09:15:00  1960         516
2020-08-05 09:15:00  1724         520
2020-08-05 09:15:00  1870         540
2020-08-05 09:20:00  1024         576
2020-08-05 09:20:00  1960         548
2020-08-05 09:20:00  1426         526
2020-08-05 09:20:00  1968         518
2020-08-05 09:30:00  1458         511
2020-08-05 09:30:00  1333         534
2020-08-05 09:30:00  1322         555
2020-08-05 09:30:00  1425         567
2020-08-05 09:30:00  1245         598

I want to find top two max Volume with corresponding Price after groupby on Datetime column.

Result Dataframe as below:

Datetime             Volume       Price
2020-08-05 09:15:00  1960         516
2020-08-05 09:15:00  1870         540
2020-08-05 09:20:00  1960         548
2020-08-05 09:20:00  1968         518
2020-08-05 09:30:00  1858         511
2020-08-05 09:30:00  1925         567
pandas dataframe python-3.x group-by python
3 Answers

Use sort_values before groupby:

print (df.sort_values("Volume", ascending=False)
         .groupby("Datetime").head(2).sort_index())

               Datetime  Volume  Price
1   2020-08-05 09:15:00    1960    516
3   2020-08-05 09:15:00    1870    540
5   2020-08-05 09:20:00    1960    548
7   2020-08-05 09:20:00    1968    518
8   2020-08-05 09:30:00    1458    511
11  2020-08-05 09:30:00    1425    567
user_92844230

using groupby.rank + boolean indexing:

df[df.groupby("Datetime")['Volume'].rank(ascending=False).le(2)]

              Datetime  Volume  Price
1   2020-08-05 09:15:00    1960    516
3   2020-08-05 09:15:00    1870    540
5   2020-08-05 09:20:00    1960    548
7   2020-08-05 09:20:00    1968    518
8   2020-08-05 09:30:00    1458    511
11  2020-08-05 09:30:00    1425    567
user_98406370

Since you mentioned nlargest

out = df.groupby('Datetime',as_index=False).apply(lambda x : x.nlargest(2, columns=['Volume']))
user_79645270
Related