Python Pandas Dataframe Groupby Sum question

Question

I'm new in Python and I need to combine 2 dataframe with 'id' as the primary key. I need to sum up all the Charges from df1 and df2.

df1:
[df1][1]

id     Name     Charge
1       A         100
1       A         100
2       B         200
2       B         200
5       C         300
6       D         400

df2:
[df2][2]

id     Name      Charge
1       A          100
1       A          100
2       B          200
8       X          200

output:
[output][3]

id       Name      Charge(TOTAL from df1 & df2)
1         A           400
2         B           600
5         C           300
6         D           400
8         X           200

Please don't paste data as images. Data and code should be provided in a [reproducible way](https://stackoverflow.com/questions/20109391). — Bill Huang, Oct 26 '20 at 02:07

score 1 · Answer 1 · answered Oct 26 '20 at 02:05

1

ans = pd.concat([df1, df2], axis=0).groupby(["id", "Name"]).sum().reset_index()

print(ans)
   id Name  Charge
0   1    A     400
1   2    B     600
2   5    C     300
3   6    D     400
4   8    X     200

answered Oct 26 '20 at 02:05

Bill Huang

4,491
2
13
31

Thanks for responding, however the total charge didn't seem to add the "Charge" column from the df2. I only got 200 for Charge in id 1 instead of 400 – karseg24 Oct 26 '20 at 02:28
No. Based on the example on your post, it is 400. And this is sufficient to prove that the logic of our solutions are correct. – Bill Huang Oct 26 '20 at 02:38

score 1 · Accepted Answer · answered Oct 26 '20 at 02:08

1

Try:

pd.concat([df1, df2]).groupby(['id', 'Name'], as_index=False)['Charge'].sum()

Output:

   id Name  Charge
0   1    A     400
1   2    B     600
2   5    C     300
3   6    D     400
4   8    X     200

answered Oct 26 '20 at 02:08

Scott Boston

147,308
15
139
187

Python Pandas Dataframe Groupby Sum question

2 Answers2