How to map values from different dataset using Partial match condition in python

Viewed 88

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.

0 Answers
Related