CODEWITHSUNDEEP
pythonpandas
Amani
Amani

Reputation: 18113

Merging two dataframes with same index in pandas

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.

Upvotes: 0

Views: 1587

Answers (1)

jezrael
jezrael

Reputation: 862406

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

Upvotes: 4

Related Questions