Get most common value for each value in row - pandas df

Question

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])

df.groupby(['id'])['name'].transform(lambda x: x.value_counts().index[0]) or use x.mode()[0] inside. — Quang Hoang
– Quang Hoang, Commented Dec 9, 2021 at 18:03

user17242583 · Accepted Answer · 2021-12-09 18:10:21Z

3

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

answered Dec 9, 2021 at 18:10

user17242583

Sign up to request clarification or add additional context in comments.

Comments

sammywemmy · Accepted Answer · 2021-12-10 01:09:14Z

0

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

edited Dec 10, 2021 at 1:09

answered Dec 9, 2021 at 23:45

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

Get most common value for each value in row - pandas df

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related