How to make pandas show all maximum values?

user_1970684 0
Viewed 61

I want to get the date and max value of a column in a dataframe, but I want all records with the max value if there are multiple. I'm only getting the first record when I do:

df_max = df.loc[df.groupby('date')['quantity'].idxmax]
pandas python
3 Answers

Try with transform

df_max = df.loc[df.groupby('date')['quantity'].transform('max') ==df['quantity'] ]
user_79645270

I am able to get all the rows. Here's what i did.

import pandas as pd
df = pd.DataFrame({"date":['2021-01-01','2021-01-01','2021-01-01','2021-01-01',
                        '2021-01-02','2021-01-02','2021-01-03','2021-01-03'],  
                   "quantity":[11, 2, 5, 8, 11, 8, 11, 4]})
print (df)

dfmax = df.loc[df.groupby('date')['quantity'].idxmax()]

print (dfmax)

Original DataFrame:

         date  quantity
0  2021-01-01        11
1  2021-01-01         2
2  2021-01-01         5
3  2021-01-01         8
4  2021-01-02        11
5  2021-01-02         8
6  2021-01-03        11
7  2021-01-03         4

Max Values using idxmax:

         date  quantity
0  2021-01-01        11
4  2021-01-02        11
6  2021-01-03        11
user_138739800

Here's an example of how to do it with apply:

dfmax = df[df.groupby('date')['quantity'].apply(lambda x: x==x.max())]
user_17178280
Related