how to select multiple columns after grouping by a single column

Question

I want to find the player with max overall rating in each position. What is the best and compact way to do it in pandas?.

Name            Overall Potential   Club                Position
L. Messi        94      94          FC Barcelona        RF
Ronaldo         94      94          Juventus            ST
Neymar Jr       92      93          Paris Saint-Germain LW
De Gea          91      93          Manchester United   GK
K. De Bruyne    91      92          Manchester City     RCM
E. Hazard       91      91          Chelsea             LF
L. Modrić       91      91          Real Madrid         RCM
L. Suárez       91      91          FC Barcelona        RS
Sergio Ramos    91      91          Real Madrid         RCB
J. Oblak        90      93          Atlético Madrid     GK
R. Lewandowski  90      90          FC Bayern München   ST
T. Kroos        90      90          Real Madrid         LCM

I have tried:

fifa.groupby(by = ["Position"])['Overall'].max()

followed by

fifa.loc[(fifa["Position"] == "CAM") & (fifa['Overall'] == 89),:]

But since there are so many categories in Position, it's a tedious task.

Use `fifa[fifa.groupby('Position')['Overall'].transform(max) == df['Overall']]` — jezrael, Jun 06 '19 at 05:34

score 0 · Answer 1 · answered Jun 06 '19 at 05:03

0

You can try this:

df[df["Overall"]==df["Overall"].max()]

This will help.

answered Jun 06 '19 at 05:03

Amazing Things Around You

896
7
24

score 0 · Answer 2 · answered Jun 06 '19 at 05:15

Use DataFrame.drop_duplicates(assuming Overall column is sorted):

df = df.drop_duplicates(subset=['Position'], keep='first')

print(df)
            Name  Overall  Potential                 Club Position
0       L. Messi       94         94         FC Barcelona       RF
1        Ronaldo       94         94             Juventus       ST
2      Neymar Jr       92         93  Paris Saint-Germain       LW
3         De Gea       91         93    Manchester United       GK
4   K. De Bruyne       91         92      Manchester City      RCM
5      E. Hazard       91         91              Chelsea       LF
7      L. Suárez       91         91         FC Barcelona       RS
8   Sergio Ramos       91         91          Real Madrid      RCB
11      T. Kroos       90         90          Real Madrid      LCM

score 0 · Answer 3 · answered Jun 06 '19 at 05:23

You could merge your intermediate result with the original dataframe to get the full rows:

pd.DataFrame(df.groupby('Position')['Overall'].max()).reset_index().merge(df,
                    on=['Position', 'Overall'])

It gives:

  Position  Overall          Name  Potential                 Club
0       GK       91        De Gea         93    Manchester United
1      LCM       90      T. Kroos         90          Real Madrid
2       LF       91     E. Hazard         91              Chelsea
3       LW       92     Neymar Jr         93  Paris Saint-Germain
4      RCB       91  Sergio Ramos         91          Real Madrid
5      RCM       91  K. De Bruyne         92      Manchester City
6      RCM       91     L. Modrić         91          Real Madrid
7       RF       94      L. Messi         94         FC Barcelona
8       RS       91     L. Suárez         91         FC Barcelona
9       ST       94       Ronaldo         94             Juventus

You can note the 2 ex-aequo for RCM position.

how to select multiple columns after grouping by a single column

3 Answers3