CODEWITHSUNDEEP
python-3.xpandasdataframe
datnwust
datnwust

Reputation: 23

Sum two dataframes for equal entries

I have two dataframes with same entries in column A, but different entries in columns B and C. One dataframe has multiple entries for one entry in A.

df1
         A   B   C
0     this   3   4
1       is   4   6
2       an   7   9
3  example  12  20

df2
         A   B   C
0     this  11  11
1     this   5   9
2     this  18   7
3       is  12  14
4       an   1   4
5       an   8  12
6  example   3  17
7  example   9   5
8  example  19   6
9  example   7   1

I want to sum the two dataframes for same entries in column A. The result shoul look like this:

df3
         A   B   C
0     this  14  15
1     this   8  13
2     this  21  11
3       is  16  20
4       an   8  13
5       an  15  21
6  example  15  37
7  example  21  25
8  example  31  26
9  example  19  21

How can I calculate this in a fast way in pandas?

Upvotes: 2

Views: 183

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71689

Use DataFrame.merge to left merge the dataframe df2 with df1 on column A then add the columns B, C of df2 to the columns B, C of df3:

df3 = df2[['A']].merge(df1, on='A', how='left')
df3[['B', 'C']] += df2[['B', 'C']]

Result:

print(df3)
         A   B   C
0     this  14  15
1     this   8  13
2     this  21  11
3       is  16  20
4       an   8  13
5       an  15  21
6  example  15  37
7  example  21  25
8  example  31  26
9  example  19  21

OR another possible idea if order is not important:

df3 = df2.set_index('A').add(df1.set_index('A')).reset_index()

print(df3)
         A   B   C
0       an   8  13
1       an  15  21
2  example  15  37
3  example  21  25
4  example  31  26
5  example  19  21
6       is  16  20
7     this  14  15
8     this   8  13
9     this  21  11

Upvotes: 5

Related Questions