Overwrite columns in DataFrames of different pandas sizes

Question

I have the following two data frames:

df1 = pd.DataFrame({'ids':[1,2,3,4,5],'cost':[0,0,1,1,0]})
df2 = pd.DataFrame({'ids':[1,5],'cost':[1,4]})

And I want to update the df1 values ​​with instances on df2 whenever there is a match in the ids. The required information frame is as follows:

df_result = pd.DataFrame({'ids':[1,2,3,4,5],'cost':[1,0,1,1,4]})

How can I get this from the two above frames?

I tried using merge, but fewer records, and it stores both columns:

results = pd.merge(df1,df2,on='ids')
results.to_dict()
{'cost_x': {0: 0, 1: 0}, 'cost_y': {0: 1, 1: 4}, 'ids': {0: 1, 1: 5}}

Answer 1

You can use set_index and combine first to give priority to the values ​​in df2

df_result = df2.set_index('ids').combine_first(df1.set_index('ids'))
df_result.reset_index()

You get

   ids  cost
0   1   1
1   2   0
2   3   1
3   4   1
4   5   4

Answer 2

You can do this with left merge:

merged = pd.merge(df1, df2, on='ids', how='left')
merged['cost'] = merged.cost_x.where(merged.cost_y.isnull(), merged['cost_y'])
result = merged[['ids','cost']]

( ), ; pandas :

df1 = df1.set_index('ids')
df2 = df2.set_index('ids')

df1.cost.where(~df1.index.isin(df2.index), df2.cost)
ids
1    1.0
2    0.0
3    1.0
4    1.0
5    4.0
Name: cost, dtype: float64

Answer 3

Another way to do this is by using a temporary unified data framework that you can undo after use.

import pandas as pd

df1 = pd.DataFrame({'ids':[1,2,3,4,5],'cost':[0,0,1,1,0]})
df2 = pd.DataFrame({'ids':[1,5],'cost':[1,4]})

dftemp = df1.merge(df2,on='ids',how='left', suffixes=('','_r'))
print(dftemp)

df1.loc[~pd.isnull(dftemp.cost_r), 'cost'] = dftemp.loc[~pd.isnull(dftemp.cost_r), 'cost_r']
del dftemp 

df1 = df1[['ids','cost']]
print(df1)


OUTPUT-----:
dftemp:
   cost  ids  cost_r
0     0    1     1.0
1     0    2     NaN
2     1    3     NaN
3     1    4     NaN
4     0    5     4.0

df1:
   ids  cost
0    1   1.0
1    2   0.0
2    3   1.0
3    4   1.0
4    5   4.0