how to merge two pandas dataframe by column with camparing values from another column

aminulpalash 0
Viewed 20

I've two data-frame, that I want to merge.

df1

sample,items,score
a,p1,0.9
b,p2,0.8
c,p3,0.6

df2

sample,items,score
d,p2,0.75
e,p3,0.7
f,p4,0.65

merge data-frame will be

sample,items,score,df_name
a,p1,0.9,df1
b,p2,0.8,df1
e,p3,0.7,df2

Condition:

  • same items value columns compared with score. If score large then , large value data frame row will add.
merge pandas
1 Answers

You can use merge to align the dataframes, then use boolean indexing for in place modification:

df3 = df1[['items']].merge(df2, on='items', how='left')[df1.columns]

df1.loc[df1['score'].lt(df3['score'])] = df3

modified df1:

  sample items  score
0      a    p1    0.9
1      b    p2    0.8
2      e    p3    0.7

To add the indicator:

df1['df_name'] = 'df1'
df1.loc[df1['score'].lt(df3['score'])] = df3.assign(df_name='df2')

output:

  sample items  score df_name
0      a    p1    0.9     df1
1      b    p2    0.8     df1
2      e    p3    0.7     df2

Alternative using a temporary index:

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

df1['df_name'] = 'df1'
df1.loc[df1['score'].lt(df2['score'])] = df2.assign(df_name='df2')

df1 = df1.reset_index()
mozway0
Related