CODEWITHSUNDEEP
pythonpandasdataframejoin
Amiclone
Amiclone

Reputation: 408

Join DataFrame by Comparing columns

I have two dataframe:

df1:

enter image description here

df2:

enter image description here

I want update column 'D' of df1 such that if df2 have a lesser value for column d for same value of 'A' and 'B' column df.d will replace df1.d for that row.

expected output is:

enter image description here

Could someone help me with the python code for this?

Thanks.

Upvotes: 0

Views: 39

Answers (1)

wwnde
wwnde

Reputation: 26676

df_new=pd.merge(df1,df2, how='left', on=['A','B'],suffixes=('', '_r'))#merge the two frames on A and B and suffix df2['D'] WITH R
df_new['D']=np.where(df_new['D']>df_new['D_r'],df_new['D_r'],df_new['D'])#Use np.where to replace column D with the right value as per condition
df_new.drop('D_r',1, inplace=True)#Drop the D_r column



   A  B    C    D
0  x  2    f  1.0
1  x  3    2  1.0
2  y  2    4  3.0
3  y  5  dfs  2.0
4  z  1  sds  5.0

Upvotes: 2

Related Questions