Merge/Append pandas dataframes but update overlapping rows

Question

I have two (or more) dataframes that I want to append under each other (or outer merge, in a way). How do I make sure that I can append the two dataframes, but at the same time, if an index is the same, I want to update the value of the variable with the second (dfB) dataframe. As an illustration:

dfA = 
Index Var1
A     5 
B     6
C     7

dfB = 
Index Var1
A     6
D     8
E     10

Desired output should look like

output = 
Index Var1
A     6
B     6
C     7
D     8
E     10

Any help would be greatly appreciated!

Thanks

Celius Stingher · Accepted Answer · 2020-01-30 13:59:06Z

1

For this particular case, considering the update, you can use pd.concat() with the argument ignore_index=True and drop_duplicates(['index'])

output = pd.concat([dfA,dfB],ignore_index=True)drop_duplicates(['index'],keep='last')

Example:

A = {'Index':['A','B','C'],'Var1':[5,6,7]}
B = {'Index':['A','D','E'],'Var1':[6,7,8]}
dfA = pd.DataFrame(A)
dfB = pd.DataFrame(B)
output = pd.concat([dfA,dfB],ignore_index=True).drop_duplicates(['Index'],keep='last')
print(output)

  Index  Var1
1     B     6
2     C     7
3     A     6
4     D     7
5     E     8

After this you can use set_index() or sort_values() if you want to sort your dataframe in alphabetical order given the column Index

edited Jan 30, 2020 at 13:59

answered Jan 30, 2020 at 13:51

Celius Stingher

18.4k6 gold badges26 silver badges54 bronze badges

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

Comments

anky · Accepted Answer · 2020-01-30 14:00:05Z

1

You can also merge and fillna:

final = (df1.merge(df2,on='Index',how='outer',suffixes=('_x',''))
       .assign(Var1 = lambda x: x['Var1'].fillna(x['Var1_x']))[df1.columns])

  Index  Var1
0     A   6.0
1     B   6.0
2     C   7.0
3     D   8.0
4     E  10.0

edited Jan 30, 2020 at 14:00

answered Jan 30, 2020 at 13:54

anky

75.3k11 gold badges46 silver badges76 bronze badges

Collectives™ on Stack Overflow

Merge/Append pandas dataframes but update overlapping rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related