CODEWITHSUNDEEP
pythonpandas
user2723494
user2723494

Reputation: 1218

Find max of group return entire row pandas

Forgive if this is a duplicate. It seems like it should be, but I've searched all the suggested and more.

I have this table

     Look_Back_Months  Total Spread Return     sector
10                11             0.038961  Apartment
20                21             0.078029  Apartment
30                31             0.079272  Apartment
40                 5             0.013499     Office
50                15             0.018679     Office
60                25            -0.003378     Office

I'd like to return

    Look_Back_Months  Total Spread Return     sector
30                31             0.079272  Apartment
50                15             0.018679     Office

Have tried groupby, agg and I keep returning either the max Look_Back_Months and the Total Spread Return. Or just one or the other.

Thanks

Upvotes: 1

Views: 47

Answers (2)

BENY
BENY

Reputation: 323226

By using

df.sort_values('TotalSpreadReturn').drop_duplicates('sector',keep='last')
Out[270]: 
    Look_Back_Months  TotalSpreadReturn     sector
50                15           0.018679     Office
30                31           0.079272  Apartment

Upvotes: 4

jpp
jpp

Reputation: 164613

You can use groupby.max with transform.

g = df.groupby('sector')['TotalSpreadReturn'].transform('max')
res = df[df['TotalSpreadReturn'] == g]

print(res)

    Look_Back_Months  TotalSpreadReturn     sector
30                31           0.079272  Apartment
50                15           0.018679     Office

If it matters, this includes duplicate maximums and maintains index order.

Upvotes: 1

Related Questions