How to apply different functions to different columns after groupby like sum and .apply(list)? (Python)

user_14022582 0
Viewed 374

I have a dataframe where I want to group rows based on a column. Some of the columns in the rows I want to sum up and the others I want to aggregate as a list.

#creating sample data
df = pd.DataFrame(np.random.rand(4,4), columns=list('abcd'))
df['id'] = [1,2,1,4]
df['group'] = [[0,1,2,3] , [0,2,3,4], [1,1,1,1], 1]
df

Out[5]: 
          a         b         c         d  id         group
0  0.850058  0.160497  0.742296  0.354296   1  [0, 1, 2, 3]
1  0.598759  0.399200  0.799157  0.908174   2  [0, 2, 3, 4]
2  0.160764  0.671702  0.414800  0.429992   1  [1, 1, 1, 1]
3  0.011089  0.581518  0.718829  0.610140   4             1

Here I want to combine row 0 and row 2 as they have the same id. When doing this, I want to sum up the values in columns a, b, c and d but for column group, I want the lists to be appended. How can I do this?

My expected output is:

          a         b         c         d  id         group
0  1.155671 1.670582   0.392744  0.681494   1  [0, 1, 2, 3, 1, 1, 1, 1]
1  0.598759  0.399200  0.799157  0.908174   2  [0, 2, 3, 4]
2  0.011089  0.581518  0.718829  0.610140   4             1

(When I use only the sum or df.groupby(['id'])['group'].apply(list), the other columns are dropped. )

pandas-groupby pandas dataframe python
3 Answers

Use groupby.aggregate

df.groupby('id').agg({k: sum for k in ['a', 'b', 'c', 'd', 'group']})

A one-liner alternative would be using numeric_only flag. But be careful with the columns you are feeding in.

df.groupby('id').sum(numeric_only=False)

Output

           a         b         c         d                     group
id
1   1.488778  0.802794  0.949768  0.952676  [0, 1, 2, 3, 1, 1, 1, 1]
2   0.488390  0.512301  0.064922  0.233875              [0, 2, 3, 4]
4   0.649945  0.267125  0.229313  0.156696                         1
user_51200490

First Solution:

We can arrive at the task in 2 steps, the 1st step using GroupBy.sum to get the grouped sum of the first 4 columns. The 2nd step acting on the column group only and concat the lists also by GroupBy.sum

df.groupby('id').sum().join(df.groupby('id')['group'].sum()).reset_index()

Input (Different values owing to the different random numbers generated)

          a         b         c         d  id         group
0  0.758148  0.781987  0.310849  0.600912   1  [0, 1, 2, 3]
1  0.694848  0.755622  0.947359  0.708422   2  [0, 2, 3, 4]
2  0.515446  0.454484  0.169883  0.697287   1  [1, 1, 1, 1]
3  0.361939  0.325718  0.143510  0.077142   4             1

Output:


   id         a         b         c         d                     group
0   1  1.273594  1.236471  0.480732  1.298199  [0, 1, 2, 3, 1, 1, 1, 1]
1   2  0.694848  0.755622  0.947359  0.708422              [0, 2, 3, 4]
2   4  0.361939  0.325718  0.143510  0.077142                         1

Second Solution

We can also use GroupBy.agg with named aggegation, as follows:

df.groupby('id', as_index=False).agg(a=('a', 'sum'), b=('b', 'sum'), c=('c', 'sum'), d=('d', 'sum'), group=('group', 'sum'))

Result:

   id         a         b         c         d                     group
0   1  1.273594  1.236471  0.480732  1.298199  [0, 1, 2, 3, 1, 1, 1, 1]
1   2  0.694848  0.755622  0.947359  0.708422              [0, 2, 3, 4]
2   4  0.361939  0.325718  0.143510  0.077142                         1
user_150706970

Does this work:

pd.merge(df.groupby('id', as_index = False).sum(), df.groupby('id')['group'].apply(sum).reset_index(), on = 'id')
    id  a           b           c           d           group
0   1   1.241602    0.839409    0.779673    0.639509    [0, 1, 2, 3, 1, 1, 1, 1]
1   2   0.967984    0.838906    0.313017    0.498611    [0, 2, 3, 4]
2   4   0.042871    0.367209    0.676656    0.178939    1
user_107227520
Related