Obtain mode from column in groupby

Question

I'm trying to obtain the mode of a column in a groupby object, but I'm getting this error: incompatible index of inserted column with frame index.

This is the line I'm getting this on, and I'm not sure how to fix it. Any help would be appreciated.

dfBitSeq['KMeans'] = df.groupby('OnBitSeq')['KMeans'].apply(lambda x: x.mode())

Pandas mode returns a data frame unlike mean and median which return a scalar. So you just need to select the slice using x.mode().iloc[0] — Vaishali, Feb 06 '18 at 14:58
This is exactly what I needed. Can you submit this as an answer, and I'll mark it as accepted? — John, Feb 06 '18 at 15:18

score 12 · Accepted Answer · answered Feb 06 '18 at 17:33

12

Pandas mode returns a data frame unlike mean and median which return a scalar. So you just need to select the slice using x.mode().iloc[0]

dfBitSeq['KMeans'] = df.groupby('OnBitSeq')['KMeans'].apply(lambda x: x.mode().iloc[0])

answered Feb 06 '18 at 17:33

Vaishali

37,545
5
58
86

1

it works to me. – EduardoUstarez Jun 15 '20 at 00:48
This was erroring for me, and instead I needed to use `df.groupby('OnBitSeq')['KMeans'].apply(lambda x: pd.Series.mode(x)).reset_index().drop_duplicates(['OnBitSeq']).drop(columns=['level_1'])` – butterflyeffect May 04 '23 at 22:36

score 2 · Answer 2 · answered Feb 06 '18 at 14:38

You can use scipy.stats.mode. Example below.

from scipy.stats import mode

df = pd.DataFrame([[1, 5], [2, 3], [3, 5], [2, 4], [2, 3], [1, 4], [1, 5]],
                  columns=['OnBitSeq', 'KMeans'])

#    OnBitSeq  KMeans
# 0         1       5
# 1         2       3
# 2         3       5
# 3         2       4
# 4         2       3
# 5         1       4
# 6         1       5

modes = df.groupby('OnBitSeq')['KMeans'].apply(lambda x: mode(x)[0][0]).reset_index()

#    OnBitSeq  KMeans
# 0         1       5
# 1         2       3
# 2         3       5

If you need to add this back to the original dataframe:

df['Mode'] = df['OnBitSeq'].map(modes.set_index('OnBitSeq')['KMeans'])

score 0 · Answer 3 · answered Feb 06 '18 at 14:34

0

You could look at Attach a calculated column to an existing dataframe.

This error looks similar and the answer is pretty useful.

answered Feb 06 '18 at 14:34

Red Panda

43
9

Obtain mode from column in groupby

3 Answers3

Linked