Notes

import pandas as pd
df = pd.read_excel("20210323 Payer reference database - Scynexis v0.2vr.xlsx") # refernce file
df1 = df.iloc[3:,2:11]
df1.columns = df1.iloc[0,:].str.strip().tolist()
df1 = df1.iloc[1:,:]
df1 = df1.dropna(subset=["Author Name"])
df1.columns = df1.columns.str.strip().tolist()
df1['Author Name'] = df1['Author Name'].str.lower()
df2 = df1[['INT_ID', 'Ref_ID','Author Name','First Name', 'Middle Name', 'Last Name']]
df3 = df2.drop_duplicates(subset=['Author Name',"INT_ID"]) #dropping duplicates to reduce file size
#using the full name¶
df_main = pd.read_csv("DAC_NationalDownloadableFile.csv") # input file
df_main.columns = df_main.columns.str.strip()
df_main.fillna("",inplace=True)
df_main["merged_name"] = df_main['frst_nm'].str.lower() + " " + df_main['mid_nm'].str.lower() + " " + df_main['lst_nm'].str.lower()
df_main["merged_name"] = df_main["merged_name"].str.strip()
df_main1 = df_main[['NPI','lst_nm', 'frst_nm', 'mid_nm','merged_name']] # taking the required column only
df_main2 = df_main1.drop_duplicates(subset=['merged_name',"NPI"]) #dropping duplicates to reduce file size
df_main2.head()
#using only 1st name and last name
df_main = pd.read_csv("DAC_NationalDownloadableFile.csv") # input file
df_main.columns = df_main.columns.str.strip()
df_main.fillna("",inplace=True)
df_main["merged_name"] = df_main['frst_nm'].str.lower() + " " + df_main['lst_nm'].str.lower()
df_main["merged_name"] = df_main["merged_name"].str.strip()
df_main1 = df_main[['NPI','lst_nm', 'frst_nm', 'mid_nm','merged_name']] # taking the required column only
df_main2 = df_main1.drop_duplicates(subset=['merged_name',"NPI"]) #dropping duplicates to reduce file size
df_main2.head()
#using only mid name and last name
df_main = pd.read_csv("DAC_NationalDownloadableFile.csv") # input file
df_main.columns = df_main.columns.str.strip()
df_main.fillna("",inplace=True)
df_main["merged_name"] = df_main['mid_nm'].str.lower() + " " + df_main['lst_nm'].str.lower()
df_main["merged_name"] = df_main["merged_name"].str.strip()
df_main1 = df_main[['NPI','lst_nm', 'frst_nm', 'mid_nm','merged_name']] # taking the required column only
df_main2 = df_main1.drop_duplicates(subset=['merged_name',"NPI"]) #dropping duplicates to reduce file size
df_main2.head()
#loading package and testing
import difflib
l = df3['Author Name'].str.strip().tolist()
df3['Author Name'] = l
#len(l)
l1 = df_main2["merged_name"].str.strip().tolist()
len(l1)
choices = l1

def difflib_match2(input_string):
v1={}
lis = difflib.get_close_matches(input_string,choices,n=3,cutoff=0.95) #cutoff = 0.60 - 0.90 limit 2,3
if len(lis) > 0:
for v in lis:

score = difflib.SequenceMatcher(None, input_string, v).ratio()
v1[v] = score

return [lis,v1]
difflib_match2("andrew nelson")
from tqdm._tqdm_notebook import tqdm_notebook
tqdm_notebook.pandas()
import string
df3["fuzzy_match_95_above_score"] = df3['Author Name'].progress_apply(difflib_match2)
df3
l_=[]
l__=[]
import ast

for i in df3["fuzzy_match_95_above_score"].tolist():
l_.append(i[0])
l__.append(i[1])

df3["fuzzy_match"] = l_
df3["score"] = l__
df_score = pd.DataFrame()
items =[]
values =[]
for dic in l__:
for k,l in dic.items():
items.append(k)
values.append(l)
df_score["fuzz"] = items
df_score["diff_lib_score"] = values

df3_ = df3.explode("fuzzy_match")
final_df = pd.merge(df3_,df_score,left_on = "fuzzy_match",right_on = "fuzz",how="left")
final_df1 = final_df[['INT_ID', 'Ref_ID', 'Author Name', 'First Name', 'Middle Name',
'Last Name','fuzzy_match', 'diff_lib_score']]
final_df2 = final_df1.merge(df_main,left_on="fuzzy_match",right_on="merged_name",how="left")
final_df4 = final_df2.drop_duplicates(subset=["INT_ID","fuzzy_match"])
final_df4
final_df4.to_csv('fuzzy_match_new4.csv',index=False)