Pandas: Creating aggregated column in DataFrame

Question

With the DataFrame below as an example,

In [83]:
df = pd.DataFrame({'A':[1,1,2,2],'B':[1,2,1,2],'values':np.arange(10,30,5)})
df
Out[83]:
   A  B  values
0  1  1      10
1  1  2      15
2  2  1      20
3  2  2      25

What would be a simple way to generate a new column containing some aggregation of the data over one of the columns?

For example, if I sum values over items in A

In [84]:
df.groupby('A').sum()['values']
Out[84]:
A
1    25
2    45
Name: values

How can I get

   A  B  values  sum_values_A
0  1  1      10            25
1  1  2      15            25
2  2  1      20            45
3  2  2      25            45

Related: (1) stackoverflow.com/questions/45346986/… (2) stackoverflow.com/questions/17432944/… — Anton Tarasenko
– Anton Tarasenko, Commented May 24, 2019 at 12:05

Wouter Overmeire · Accepted Answer · 2012-11-06 19:07:38Z

51

In [20]: df = pd.DataFrame({'A':[1,1,2,2],'B':[1,2,1,2],'values':np.arange(10,30,5)})

In [21]: df
Out[21]:
   A  B  values
0  1  1      10
1  1  2      15
2  2  1      20
3  2  2      25

In [22]: df['sum_values_A'] = df.groupby('A')['values'].transform(np.sum)

In [23]: df
Out[23]:
   A  B  values  sum_values_A
0  1  1      10            25
1  1  2      15            25
2  2  1      20            45
3  2  2      25            45

answered Nov 6, 2012 at 19:07

Wouter Overmeire

69.7k10 gold badges67 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

foglerit · Accepted Answer · 2012-11-06 18:36:37Z

8

I found a way using join:

In [101]:
aggregated = df.groupby('A').sum()['values']
aggregated.name = 'sum_values_A'
df.join(aggregated,on='A')

Out[101]:
   A  B  values  sum_values_A
0  1  1      10            25
1  1  2      15            25
2  2  1      20            45
3  2  2      25            45

Anyone has a simpler way to do it?

answered Nov 6, 2012 at 18:36

foglerit

8,4469 gold badges53 silver badges70 bronze badges

Comments

joaquin · Accepted Answer · 2012-11-06 18:49:45Z

4

This is not so direct but I found it very intuitive (the use of map to create new columns from another column) and can be applied to many other cases:

gb = df.groupby('A').sum()['values']

def getvalue(x):
    return gb[x]

df['sum'] = df['A'].map(getvalue)
df

answered Nov 6, 2012 at 18:49

joaquin

86k31 gold badges146 silver badges155 bronze badges

1 Comment

foglerit Over a year ago

Thanks, the map method seems pretty powerful. Will certainly use it often.

Garrett · Accepted Answer · 2012-11-06 21:26:44Z

3

In [15]: def sum_col(df, col, new_col):
   ....:     df[new_col] = df[col].sum()
   ....:     return df

In [16]: df.groupby("A").apply(sum_col, 'values', 'sum_values_A')
Out[16]: 
   A  B  values  sum_values_A
0  1  1      10            25
1  1  2      15            25
2  2  1      20            45
3  2  2      25            45

answered Nov 6, 2012 at 21:26

Garrett

50.3k6 gold badges64 silver badges51 bronze badges

Collectives™ on Stack Overflow

Pandas: Creating aggregated column in DataFrame

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related