Find the majority in dataframe using pandas.DataFrame.mode and groupby

Question

how to get a new column for the majority of each group?

sample dataframe

    who        state
0   peopleA     CA
1   peopleA     CA
2   peopleA     CA
3   peopleA     NJ
4   peopleB     FL
5   peopleB     FL
6   peopleB     CA

this's not the right way to code

df['new_column'] = df.groupby('who').mode()

my expected output

    who        new_column
0   peopleA      CA
1   peopleB      FL

Bonus question: Is there a way to set a threshold to control if the count greater than 70%, then consider as majority, if less than 70%, return null

Is this what you want?: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.count.html Slice on the 'who' and count the 'State'? — Jeff Ellen, Jun 18 '18 at 23:03

harvpan · Answer 1 · 2018-06-18T23:14:03.550

2

pandas.groupby does not have mode(). A workaround is here. Basically after grouping, you can use .apply() to the grouper and find mode specific to that group.

df.groupby('who').state.apply(lambda x: x.mode()).reset_index(0)

Output:

      who   state
0   peopleA CA
0   peopleB FL

edited Jun 18 '18 at 23:14

answered Jun 18 '18 at 23:08

harvpan

8,571
2
18
36

it works, but the index number become 0 and 0 – Learn Jun 18 '18 at 23:24

score 2 · Accepted Answer · answered Jun 18 '18 at 23:08

2

We can group by column who, then apply mode function on the df groupby object and then call reset_index and pass param drop=True so that the multi-index is not added back as a column

>>> df
       who state
0  peopleA    CA
1  peopleA    CA
2  peopleA    CA
3  peopleA    NJ
4  peopleB    FL
5  peopleB    FL
6  peopleB    CA
>>> 
>>> df.groupby('who').apply(pd.DataFrame.mode).reset_index(drop=True)
       who state
0  peopleA    CA
1  peopleB    FL
>>>

answered Jun 18 '18 at 23:08

Sunitha

11,777
2
20
23

Is there a way to set a threshold to control if the count greater than 70%, then consider as majority, if less than 70%, return null? – Learn Jun 18 '18 at 23:48
how to specify the column's name for mode function if I have more that two columns? – Learn Jun 19 '18 at 00:04

Find the majority in dataframe using pandas.DataFrame.mode and groupby

2 Answers2