Merging two dataframes with same index in pandas

Question

I have two data frames. I need to join them so that those index which have same name in both data frames are joined into one and their values summed. Those index which do not exist in the other data frame are created and their valued inserted. See example below.

dataFrame1:

index   col1 col2 col3
A         3    0    4
C         4    1    2
D         3    5    6
G         3    0    0

dataFrame2

index   col1 col2 col3
A         1    1    3
B         4    4    1
C         1    3    0
E         0    2    1
F         1    3    2

I need the following results:

index   col1 col2 col3
A         4    1    7
B         4    4    1
C         5    4    2
D         3    5    6
E         0    2    1
F         1    3    2
G         3    0    0

how can I do this is pandas? Note: no value should be treated as zero unless it is zero or NaN in both data frames.

Possible duplicate of Adding two pandas dataframes

Ani Menon
– Ani Menon

2016-04-21 09:22:50 +00:00
Commented Apr 21, 2016 at 9:22 — Ani Menon
– Ani Menon, Commented Apr 21, 2016 at 9:22

jezrael · Accepted Answer · 2016-04-21 08:48:19Z

4

I think you can use add with combine_first and casting to int by astype:

print df1
       col1  col2  col3
index                  
A         3     0     4
C         4     1     2
D         3     5     6
G         3     0     0

print df2
       col1  col2  col3
index                  
A         1     1     3
B         4     4     1
C         1     3     0
E         0     2     1
F         1     3     2

print df1.add(df2).combine_first(df1).combine_first(df2).astype(int)
       col1  col2  col3
index                  
A         4     1     7
B         4     4     1
C         5     4     2
D         3     5     6
E         0     2     1
F         1     3     2
G         3     0     0

answered Apr 21, 2016 at 8:48

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Merging two dataframes with same index in pandas

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related