Sum column based on another column in Pandas DataFrame

Question

I have a pandas DataFrame like this:

>>> df = pd.DataFrame({'MONTREGL':[10,10,2222,35,200,56,5555],'SINID':['aaa','aaa','aaa','bbb','bbb','ccc','ccc'],'EXTRA':[400,400,400,500,500,333,333]})
>>> df
   MONTREGL SINID EXTRA
0        10   aaa   400
1        10   aaa   400
2      2222   aaa   400
3        35   bbb   500
4       200   bbb   500
5        56   ccc   333
6      5555   ccc   333

I want to sum the column MONTREGL for each groupby SINID...

So I get 2242 for aaa and so on... ALSO I want to keep the value of column EXTRA.

This is the expected result:

   MONTREGL SINID EXTRA
0      2242   aaa   400
1       235   bbb   500
2      5611   ccc   333

Thanks for your help in advance!

can you do a print(df.info()) and share the resulting print — dimitris_ps
– dimitris_ps, Commented May 29, 2019 at 13:11
The solution proposed before by @yatu worked but not in production dfd= df.groupby('SINID').MONTREGL.sum().reset_index().assign(EXTRA=df.EXTRA.unique()) I get the error Length of values does not match length of index — Soufiane Sabiri
– Soufiane Sabiri, Commented May 29, 2019 at 13:19

Soufiane Sabiri · Accepted Answer · 2019-05-30 09:11:28Z

17

I ended up using this script:

dff = df.groupby(["SINID","EXTRA"]).MONTREGL.sum().reset_index()

And it works in this test and production.

answered May 30, 2019 at 9:11

Soufiane Sabiri

7681 gold badge7 silver badges22 bronze badges

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

Comments

mOna · Accepted Answer · 2020-10-30 00:10:42Z

6

I know this post is old, but this might be helpful for others:

Using loc: df.loc[df['SINID'] == aaa].MONTREGL.sum()

Using groupby: df.groupby('SINID')['MONTREGL'].sum()

A similar question is answered in the following link (check Alex Riley's response):

How do I sum values in a column that match a given condition using pandas?

Good luck,

answered Oct 30, 2020 at 0:10

mOna

2,45910 gold badges39 silver badges67 bronze badges

Comments

Ryan M · Accepted Answer · 2020-11-12 05:33:30Z

4

The code below works for your example:

df1 = df.groupby(["SINID"]).sum()
df1['EXTRA'] = df.groupby(["SINID"]).mean()['EXTRA']

Result :

       MONTREGL  EXTRA
SINID                 
aaa        2242  400.0
bbb         235  500.0
ccc        5611  333.0

edited Nov 12, 2020 at 5:33

Ryan M♦

20.6k35 gold badges75 silver badges85 bronze badges

answered May 29, 2019 at 13:26

Hippolyte BRINGER

8631 gold badge11 silver badges37 bronze badges

Comments

Azer Gorai · Accepted Answer · 2019-06-06 07:34:46Z

-1

my suggestion would be to filter you dataframe with conditions related to other columns then apply sum function,

it goes something like this.

import pandas as pd

df=pd.Dataframe({a:[1,2,3],b:[2001,2015,2019],c:[1,0,1]})

aux=df[df.c>0]

sa=aux.a.sum()

sb=aux.b.sum()

My syntax may not be correct ( i didnt run the code ) but it will probably work and lead you to your answer

Good luck.

edited Jun 6, 2019 at 7:34

answered May 29, 2019 at 13:16

Azer Gorai

331 silver badge9 bronze badges

Collectives™ on Stack Overflow

Sum column based on another column in Pandas DataFrame

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related