Replacing variable name based on count

Question

I have a DataFrame that look like this:

player           pos Count of pos
A.J. Derby       FB  1
                 TE  10
A.J. Green       WR  16
A.J. McCarron    QB  3
Aaron Jones      RB  12
Aaron Ripkowski  FB  16
Aaron Rodgers    QB  7
Adam Humphries   TE  1
                 WR  15
Adam Shaheen     TE  13
Adam Thielen     WR  16
Adrian Peterson  RB  10
Akeem Hunt       RB  15
Alan Cross       FB  1
                 TE  7
Albert Wilson    WR  13
Aldrick Robinson WR  16
Alex Armah       CB  1
                 FB  6
                 RB  2
Alex Collins     RB  15
Alex Erickson    WR  16
Alex Smith       QB 15
Alfred Blue      RB 11
Alfred Morris    RB 14
Allen Hurns      WR 10
Allen Robinson   WR 1
Alshon Jeffery   WR 16
Alvin Kamara     FB 1
                 RB 15
Amara Darboh     WR 16
Amari Cooper     TE 2
                 WR 12

For a player that has more than one pos type I would like to replace all the pos types listed for that player with the pos type that has the highest count of pos. So, for the first player his FB type will be replaced with TE.

I've started with:

for p in df.player:
    if df.groupby('player')['pos'].nunique() > 1:

But am struggling with what the next step is for replacing the pos based on count of pos.

Appreciate any help on this. Thanks!

What is `print (df.index[:5])` ? – jezrael Jun 30 '20 at 06:04 — jezrael, Jun 30 '20 at 06:04

score 0 · Accepted Answer · answered Jun 30 '20 at 06:02

Use GroupBy.transform with DataFrameGroupBy.idxmax for pos values by maximum values of Count of pos:

#if necessary
df = df.reset_index()
df['player'] = df['player'].replace('', np.nan).ffill()

df['pos'] = (df.set_index('pos')
               .groupby('player')['Count of pos']
               .transform('idxmax')
               .to_numpy())
print (df)
            player pos  Count of pos
0       A.J. Derby  TE             1
1       A.J. Derby  TE            10
2       A.J. Green  WR            16
3    A.J. McCarron  QB             3
4      Aaron Jones  RB            12
5  Aaron Ripkowski  FB            16
6    Aaron Rodgers  QB             7
7   Adam Humphries  WR             1
8   Adam Humphries  WR            15

Finally got to try this right now and thank you it accomplishes exactly what I needed. I appreciate pointing to the transform and idxmax documentation. I took the time to read further and better learn. Thanks! — sooted, Jun 30 '20 at 17:42

Replacing variable name based on count

1 Answers1