groupby and select mode and join back onto original dataframe

user_2820289 0
Viewed 166

I have data that looks like this:

df = pd.DataFrame({'Name' : ['John', 'John', 'John', 'Darrel','Darrel', 'Nick'], 
                  'Ocupation' : ['An','An', 'An', 'Se', 'So', 'Ik'],
                  'Numbers' : ['12','12','54','2', '3', '55']})

I want to group by Name and for each group in Name I want to select the mode (most frequent/prevalent value) of Numbers. I do this with the following code:

df.groupby(['Name'])['Numbers'].agg(lambda x: pd.Series.mode(x)[0]).reset_index(False)

, and now I want to join back the modes onto df. Is there any way to do this in one go?

Right now I have to do the maybe not so elegant:

df.merge(df.groupby(['Name'])['Numbers'].agg(lambda x: pd.Series.mode(x)[0]).reset_index(False),
       left_on='Name', right_on='Name', how = 'left')
pandas aggregate group-by mode python
2 Answers

If you want mode you can use:

from statistics import mode
df['Mode'] = df.groupby(['Name'])['Numbers'].transform(mode)
user_154380330

Use groupby().transform() instead of groupby().agg():

df['Mode'] = df.groupby('Name')['Numbers'].transform(lambda x: x.mode()[0])

Output:

     Name Ocupation Numbers Mode
0    John        An      12   12
1    John        An      12   12
2    John        An      54   12
3  Darrel        Se       2    2
4  Darrel        So       3    2
5    Nick        Ik      55   55
user_42384080
Related