Pandas weighted mode in groupby

user_8912587 0
Viewed 225

I've dataframe that contains

data = np.array([('a', 'i', 'x', 10), ('a', 'j', 'y', 20), ('b', 'j', 'x', 30), 
                 ('b', 'k', 'z', 10), ('b', 'j', 'z', 15), ('c', 'k', 'y', 13), 
                 ('c', np.NaN, 'z', 3), ('d', np.NaN, 'x', 0)], dtype=[('col1', 'U1'), 
                 ('col2', object), ('col3', 'U1'), ('col4', 'i4')])
df = pd.DataFrame(data)

  col1 col2 col3 col4
0    a    i    x   10
1    a    j    y   20
2    b    j    x   30
3    b    k    z   10
4    b    j    z   15
5    c    k    y   13
6    c  NaN    z    3
7    d  NaN    x    0

This table is a subject of grouping by col1 in order to return total sum of col4, but besides that I'd like to display top 1 item of all other colums (col2 and col3) in relation not to frequency but to its max contribution in resulting total sum of col4.

I stuck at the top1 frequencies and have no clue how can get to the desired solution:

df.groupby(by=['col1'], dropna=False).aggregate(
           total_sum=('col4', 'sum'), 
           top_c2=('col2', lambda x: x.value_counts(dropna=False).index[0]), 
           top_c3=('col3', lambda x: x.value_counts(dropna=False).index[0])).reset_index()

What I have:

  col1  total_sum top_c2 top_c3
0    a         30      i      x
1    b         55      j      z
2    c         16      k      z
3    d          0    NaN      x

Expected outcome:

  col1  total_sum top_c2 top_c3
0    a         30      i      y
1    b         55      j      x
2    c         16      k      y
3    d          0    NaN      x
pandas-groupby pandas dataframe python
1 Answers

Idea is convert columns col2 and col3 to MultiIndex and then get values by Series.idxmax with selecting first and second tuple by column col4:

df = (df.set_index(['col2','col3'])
        .groupby(by=['col1'], dropna=False).aggregate(
            total_sum=('col4', 'sum'), 
            top_c2=('col4', lambda x: x.idxmax()[0]), 
            top_c3=('col4', lambda x: x.idxmax()[1])).reset_index())
print (df)
  col1  total_sum top_c2 top_c3
0    a         30      j      y
1    b         55      j      x
2    c         16      k      y
3    d          0    NaN      x
user_29010020
Related