How can I group multiple columns and sum the last one?

user_13856027 0
Viewed 102

I have this problem which I've been trying to solve:

I want the code to take this DataFrame and group multiple columns based on the most frequent number and sum the values on the last column. For example:

df = pd.DataFrame({'A':[1000, 1000, 1000, 1000, 1000, 200, 200, 500, 500],
                   'B':[380, 380, 270, 270, 270, 45, 45, 45, 55],
                   'C':[380, 380, 270, 270, 270, 88, 88, 88, 88],
                   'D':[45, 32, 67, 89, 51, 90, 90, 90, 90]})

df

       A     B     C    D
0   1000   380   380   45
1   1000   380   380   32
2   1000   270   270   67
3   1000   270   270   89
4   1000   270   270   51
5    200    45    88   90
6    200    45    88   90
7    500    45    88   90
8    500    55    88   90

I would like the code to show the result below:

       A     B     C    D
0   1000   380   380   284
1   1000   380   380   284
2   1000   270   270   284
3   1000   270   270   284
4   1000   270   270   284
5    200    45    88   360
6    200    45    88   360
7    500    45    88   360
8    500    55    88   360

Notice that the most frequent value on the first rows is 1000, and this way I group the column 'A' so I get the sum 284 on the column 'D'. However, on the last rows, the most frequent number, which is 88, is not on column 'A', but in column 'C'. I am trying to sum the values on column 'D' by grouping column 'C' and get 360. I am not sure if I made myself clear.

I tried to use the function df['D'] = df.groupby(['A', 'B', 'C'])['D'].transform('sum'), but it does not show the desired result aforementioned.

Is there any pandas-style way of resolving this? Thanks in advance!

pandas-groupby pandas dataframe python
1 Answers

Code

def get_count_sum(col, func):
    return df.groupby(col).D.transform(func)
    
ga = get_count_sum('A', 'count')
gb = get_count_sum('B', 'count')
gc = get_count_sum('C', 'count')


conditions = [
    ((ga > gb) & (ga > gc)),
    ((gb > ga) & (gb > gc)),
    ((gc > ga) & (gc > gb)),
]

choices = [get_count_sum('A', 'sum'), 
           get_count_sum('B', 'sum'),
           get_count_sum('C', 'sum')]

df['D'] = np.select(conditions, choices)
df

Output

    A       B   C   D
0   1000    380 380 284
1   1000    380 380 284
2   1000    270 270 284
3   1000    270 270 284
4   1000    270 270 284
5   200     45  88  360
6   200     45  88  360
7   500     45  88  360
8   500     55  88  360

Explanation

  1. Since we need to group by each column 'A','B' or 'C' considering which one has max repeated number, so first we are checking the max repeated number and storing the groupby output in ga, gb, gc for A,B,C col respectively.

  2. We are checking which col has max frequent number in conditions.

  3. According to the conditions we are applying choices for if else conditions.

  4. np.select is like if-elif-else where we placed the conditions and required output in choices.

user_21069340
Related