Looking to map values from dataframe2 to dataframe1 based on conditional statement. Need to map the values from df2 to df1 where matching percentage based on df1['id_number'] & df2['identity_No'] values are highest.
For Eg: if row1 from df1 will match across all rows of df2 based on a specific column, and has highest match percentage wrt. row4 of df2, and its more than 75%, it will copy the respective data to df1.
Dataframe1
score id_number company_name company_code match_acc action_reqd
20 IN2231D AXN pvt Ltd IN225 Yes
45 UK654IN Aviva Intl Ltd IN115 No
65 SL1432H Ship Incorporations CZ555 Yes
35 LK0678G Oppo Mobiles pvt ltd PQ795 Yes
59 NG5678J Nokia Inc RS885 No
20 IN2231D AXN pvt Ltd IN215 Yes
Dataframe2
OR_score identity_No comp_name comp_code
51 UK654IN Aviva Int.L Ltd IN515
25 SL6752J Ship Inc Traders CZ555
79 NG5678K Nokia Inc RS005
20 IN22312 AXN pvt Ltd IN255
38 LK0665G Oppo Mobiles ltd PQ895
I need to check the matching accuracy percentage where for Eg. row1 from df1 ("id_number") will match with each row from df2 (identity_No) based on the highest matching percentage (whichever row from df2 will have highest matching percentage) will map values from df2 to df1. Same will be continued for each row of df1.
Expected output:
score id_number company_name company_code match_acc action_reqd
20 IN22312 AXN pvt Ltd IN225 90 Yes
51 UK654IN Aviva Int.L Ltd IN115 100 No
25 SL1432H Ship Incorporations CZ555 30 Yes
38 LK0665G Oppo Mobiles ltd PQ795 80 Yes
79 NG5678K Nokia Inc RS885 85 No
Code i have been trying
cross = df1[['id_number']].merge(df2[['identity_no']].assign(tmp=0), how='outer', on='tmp').drop(columns='tmp'))
cross['match'] = cross.apply(lambda x: fuzz.ratio(x.id_number, x.identity_no), axis=1)
df1['match_acc'] = df1.id_number.map(cross.groupby('id_number').match.max())
for index, row in df1.iterrows():
for index2, config2 in df2.iterrows():
if row['match_acc'] >= 75:
df1['id_number'][index] = config2['identity_No']
df1['company_name'][index] = config2['comp_name']
df1['company_code'][index] = config2['comp_code']
df1['score'][index] = config2['OR_Score']
Not getting the expected answer. it copies row1 from df2 to the entire row of df1 where match_acc is >=75.