Pandas sum all columns of group result into a single result

user_1115237 0
Viewed 86

I've generated the following dummy data, where the number of rows per id range from 1 to 5:

import pandas as pd, numpy as np
import random
import functools
import operator
uid = functools.reduce(operator.iconcat, [np.repeat(x,random.randint(1,5)).tolist() for x in range(10)], [])

df = pd.DataFrame(columns=list("ABCD"), data= np.random.randint(1, 5, size=(len(uid), 4)))
df['id'] = uid
df.head()

   A  B  C  D  id
0  1  1  2  2   0
1  1  2  4  4   0
2  2  3  3  2   0
3  4  3  3  1   1
4  1  3  4  4   1

I would like to group by id then sum all the values, I.E:

   A  B  C  D  id
0  1  1  2  2   0
1  1  2  4  4   0
2  2  3  3  2   0 = (1+1+2+2+1+2+4+4+2+2+3+3+2) = 29

Then duplicate the value for the group so the result would be:

   A  B  C  D  id  val
0  1  1  2  2   0  29
1  1  2  4  4   0  29
2  2  3  3  2   0  29

I've tried to call sum df.groupby('id').sum() but it sums each column separately.

pandas group-by python
3 Answers

You can use set_index and then stack folowed by groupby and sum, then series.map

df['val'] = df['id'].map(df.set_index("id").stack().groupby(level=0).sum())

Or as suggested by @jezrael, sum has a level=0 arg which does the same as above:

df['val'] =  df['id'].map(df.set_index("id").stack().sum(level=0))

   A  B  C  D  id  val
0  4  2  4  1   0   34
1  3  4  4  2   0   34
2  2  4  3  1   0   34
3  1  1  1  3   1    6
4  2  3  1  4   2   50
user_98406370

You can first sum all columns without id and then using GroupBy.transform:

df['val'] = df.drop('id',1).sum(axis=1).groupby(df['id']).transform('sum')

Another idea:

df['val'] = df['id'].map( df.groupby('id').sum().sum(axis=1))
user_29010020 
M=df.groupby("id").sum()
np.sum(M,axis=1)

print(M)
print(np.sum(M,axis=1))

First M is the sum of all columns which grouped by id. Then after grouping by id, when we sum all data in a row basically we have wanted result

user_141281250
Related