Pandas groupby two columns and get max value

Question

I have grouped data with multiindex

                        
    Model  VehicleType  VehicleType            
    100    sedan                278
           wagon                109
           coupe                  2
           convertible            1
    145    small                 19
    ...                         ...
    zafira sedan                 22
           small                 11
           suv                    7
           convertible            1
           coupe                  1

I need to get max value of count (right column) with corresponding model and VehicleType, like this:

                        
    Model  VehicleType  VehicleType            
    100    sedan                278
    145    small                 19
    ...                         ...
    zafira sedan                 22

Thanks for solutions!

Duplicate question. Probably you can find the answer here https://stackoverflow.com/questions/26789935/how-to-get-the-max-value-of-a-multiple-column-group-by-pandas, https://stackoverflow.com/questions/52457014/pandas-group-by-with-multiple-columns-and-max-value — OnlyDryClean Codzy, Aug 11 '20 at 09:00

jezrael · Accepted Answer · 2020-08-11T08:47:03.663

0

Use DataFrameGroupBy.idxmax for indices by maximum value and then select by DataFrame.loc:

df = df.loc[df.groupby(level=0)['VehicleType'].idxmax()]
print (df)
                    VehicleType
Model  VehicleType             
100    sedan                278
145    small                 19
zafira sedan                 22

edited Aug 11 '20 at 08:47

answered Aug 11 '20 at 08:35

jezrael

822,522
95
1,334
1,252

Pandas groupby two columns and get max value

1 Answers1