This may be a duplicate please let me know.
I have a pandas df like this:
| id | name | Common |
|---|---|---|
| One | A | |
| One | A | |
| One | A | |
| One | B | |
| Two | C |
I'd like to output something like this:
Where the most common name for each id is placed in the common column.
| id | name | Common |
|---|---|---|
| One | A | A |
| One | A | A |
| One | A | A |
| One | B | A |
| Two | C | C |
I've tried this but at this point i'm throwing darts in the dark
df.groupby(['id', 'name']).agg(lambda x:x.value_counts().index[0])
This works:
df['Common'] = df.groupby('id')['name'].transform(lambda x: x.mode()[0])
Output:
>>> df
id name Common
0 One A A
1 One A A
2 One A A
3 One B A
4 Two C C
A longer (potentially slower) process is to pivot and map:
df.assign(Common = df.id.map(df.pivot(None, 'id', 'name').mode().T.squeeze()))
id name Common
0 One A A
1 One A A
2 One A A
3 One B A
4 Two C C