Pandas: get string value with most occurrence in group

Question

I have the following DataFrame:

item    response
1       A       
1       A       
1       B       
2       A       
2       A

I want to add a column with the most given response for an item. which should result in:

item    response  mostGivenResponse
1       A          A
1       A          A      
1       B          A       
2       C          C
2       C          C

I tried something like this:

df["responseCount"] = df.groupby(["ItemCode", "Response"])["Response"].transform("count")

df["mostGivenResponse"] = df.groupby(['ItemCode'])['responseCount'].transform(max)

But mostGivenResponse is now the count of the response in stead of the response itself.

Possible duplicate of [Group by pandas dataframe and select most common string factor](https://stackoverflow.com/questions/15222754/group-by-pandas-dataframe-and-select-most-common-string-factor) — pault, Jul 11 '18 at 14:58

score 10 · Answer 1 · edited Jul 11 '18 at 15:09

10

There is pd.Series.mode:

df.groupby('item').response.transform(pd.Series.mode)
Out[28]: 
0    A
1    A
2    A
3    C
4    C
Name: response, dtype: object

edited Jul 11 '18 at 15:09

jpp

159,742
34
281
339

answered Jul 11 '18 at 14:58

BENY

317,841
20
164
234

Thanks! I tried df['mostGivenResponse'] = df.groupby('item').response.transform(pd.Series.mode) but then it says: ValueError: Length of passed values is 1, index implies 162 (sorry I started learning Python today) – Marcel Hoekstra Jul 11 '18 at 18:13
1

@MarcelHoekstra `df.groupby('item').response.transform(lambda x : x.mode().iloc[0])` you have multiple value have the same frequency within in one group – BENY Jul 11 '18 at 18:17
I get: IndexError: single positional indexer is out-of-bound – Marcel Hoekstra Jul 11 '18 at 18:24
probably to do with my whole dataset. Maybe null values of the response or something. The only suggested answer that works on my whole set is: .transform(lambda x: Counter(x).most_common(1)[0][0])) do you have any idea what can cause the error using your solution. – Marcel Hoekstra Jul 11 '18 at 19:26
@MarcelHoekstra - I find problem, check edited answer. – jezrael Jul 11 '18 at 20:28

jezrael · Accepted Answer · 2018-07-11T20:28:27.747

Use value_counts and return first index value:

df["responseCount"] = (df.groupby("item")["response"]
                        .transform(lambda x: x.value_counts().index[0]))

print (df)
   item response responseCount
0     1        A             A
1     1        A             A
2     1        B             A
3     2        C             C
4     2        C             C

Or collections.Counter.most_common:

from collections import Counter

df["responseCount"] = (df.groupby("item")["response"]
                         .transform(lambda x: Counter(x).most_common(1)[0][0]))

print (df)
   item response responseCount
0     1        A             A
1     1        A             A
2     1        B             A
3     2        C             C
4     2        C             C

EDIT:

Problem is with one or multiple NaNs only groups, solution is filter with if-else:

print (df)
   item response
0     1        A
1     1        A
2     2      NaN
3     2      NaN
4     3      NaN

def f(x):
    s = x.value_counts()
    print (s)

    A    2
    Name: 1, dtype: int64
    Series([], Name: 2, dtype: int64)
    Series([], Name: 3, dtype: int64)

    #return np.nan if s.empty else s.index[0]
    return np.nan if len(s) == 0 else s.index[0]

df["responseCount"] = df.groupby("item")["response"].transform(f)
print (df)
   item response responseCount
0     1        A             A
1     1        A             A
2     2      NaN           NaN
3     2      NaN           NaN
4     3      NaN           NaN

score 1 · Answer 3 · answered Jul 11 '18 at 15:08

You can use statistics.mode from standard library:

from statistics import mode

df['mode'] = df.groupby('item')['response'].transform(mode)

print(df)

   item response mode
0     1        A    A
1     1        A    A
2     1        B    A
3     2        C    C
4     2        C    C

Pandas: get string value with most occurrence in group

3 Answers3

Linked