Merge with replacement of smaller dataframe to larger dataframe-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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