Merge with replacement of smaller dataframe to larger dataframe

Question

I have two DataFrames that look like this:

DF1:

index  colA colB
 id1    0     0
 id2    0     0
 id3    0     0
 id4    0     0
 id5    0     0

DF2:

index  colA   colB
  id3    A3     B3
  id4    A4     B4
  id6    A6     B6

I want to infuse values from DF2 to DF1. I was trying to merge but it does not replace the values and creates newer columns. The resulting DataFrame I want should look like this:

DF1:

index  colA  colB
  id1     0     0
  id2     0     0
  id3    A3    B3
  id4    A4    B4
  id5     0     0
  id6    A6    B6

Note: it should create a new index in DF1 if DF2 has some index not present in DF1. Also columns index are the index of DataFrames and not column names.

Answer 1

Here's one way using concat + drop_duplicates:

out = pd.concat((df1, df2)).reset_index().drop_duplicates(subset=['index'], keep='last').set_index('index').sort_index()

or use reindex + update:

df1 = df1.reindex(df1.index.union(df2.index))
df1.update(df2)

Output:

  index colA colB
0   id1    0    0
1   id2    0    0
0   id3   A3   B3
1   id4   A4   B4
4   id5    0    0
2   id6   A6   B6

Answer 2

You can try with combine_first

df1 = df1.set_index('index')
df2 = df2.set_index('index')
out = df2.combine_first(df1)
Out[217]: 
      colA colB
index          
id1    0.0  0.0
id2    0.0  0.0
id3     A3   B3
id4     A4   B4
id5    0.0  0.0
id6     A6   B6