Pandas top N records in each group sorted by a column's value

user_1233751 0
Viewed 46
import pandas as pd
d = {
    'resource': [1,2,3,4,5,6,7], 
    'branch': ['a', 'b', 'c', 'a', 'a', 'c', 'b'],
    'utilization': [0.7, 0.76, 0.9, 0.3, 0.55, 0.87, 0.71]
}
df = pd.DataFrame(data=d)

I need to display the top 2 utilized resources by branches

Something like this:

df.groupby('branch')[['resource', 'utilization']].nlargest(2, 'utilization')

I tried the following:

f = lambda x: x.sort_values('utilization', ascending=False)
df.groupby('branch', sort=False).apply(f).nlargest(3, 'utilization')

but it gives me top 3 across all the records when I need top N in each group

        resource    branch  utilization
branch              
c   2          3         c  0.90
    5          6         c  0.87
b   1          2         b  0.76
pandas-groupby pandas
1 Answers

May using sort_values + tail

df.sort_values('utilization').groupby('branch').tail(2)
  branch  resource  utilization
4      a         5         0.55
0      a         1         0.70
6      b         7         0.71
1      b         2         0.76
5      c         6         0.87
2      c         3         0.90
user_79645270
Related