Pandas: get string value with most occurrence in group

  • A+
Category:Languages

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.

 


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 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: