In [ ]:

    import pandas
    import os
    import numpy as np
    from fuzzywuzzy import process

    os.chdir(r".\Data\Data_TRI")

    def fuzzyMerge (baseFrame, compareFrame, baseKey, compareKey, threshold=80, limit=1, how='left'):

        #pre-process all strings in the compare frame
        s_mapping = {x: x for x in compareFrame[compareKey]}

        m1 = baseFrame[baseKey].apply(lambda x: process.extract(x, s_mapping, limit=limit))
        baseFrame['Match'] = m1
        baseFrame['Match'] = baseFrame['Match'].apply(lambda x: [i for i in x if i[1] >= threshold])

        scores = baseFrame['Match'].apply(lambda x: ', '.join(str(i[1]) for i in x))
        names = baseFrame['Match'].apply(lambda x: ', '.join(i[0] for i in x))
        baseFrame[compareKey] = names.replace("",np.nan)
        baseFrame['matchedScore'] = scores
        baseFrame.drop(columns=['Match'], inplace=True)
        baseFrame = baseFrame.merge(compareFrame, on=compareKey, how=how)

        return baseFrame

    TRIfirms = pandas.read_stata("./tempTRINames.dta")
    Preqinfirms = pandas.read_stata("./tempPreqinNames.dta")

    TRIfirms_fac = TRIfirms[['trifd', 'subFacilityName_noSpace']].drop_duplicates().replace('',np.nan).dropna(subset=['subFacilityName_noSpace']).reset_index(drop=True)
    TRIfirms_sub = TRIfirms[['trifd', 'subfirmName_noSpace']].drop_duplicates().replace('',np.nan).dropna(subset=['subfirmName_noSpace']).reset_index(drop=True)
    TRIfirms_std = TRIfirms[['trifd', 'subStdFirmName_noSpace']].drop_duplicates().replace('',np.nan).dropna(subset=['subStdFirmName_noSpace']).reset_index(drop=True)
    TRIfirms_vin = TRIfirms[['trifd', 'vintageName_noSpace']].drop_duplicates().replace('',np.nan).dropna(subset=['vintageName_noSpace']).reset_index(drop=True)
    TRIfirms_vinStd = TRIfirms[['trifd', 'vintageStdName_noSpace']].drop_duplicates().replace('',np.nan).dropna(subset=['vintageStdName_noSpace']).reset_index(drop=True)

    TRIfirms_vinStd = fuzzyMerge(TRIfirms_vinStd, Preqinfirms, 'vintageStdName_noSpace', 'd_firmName_noSpace')
    TRIfirms_vinStd.to_csv("./name_matched/vinStdMatched.txt", sep=',', index=False)

    TRIfirms_std = fuzzyMerge(TRIfirms_std, Preqinfirms, 'subStdFirmName_noSpace', 'd_firmName_noSpace')
    TRIfirms_std.to_csv("./name_matched/stdMatched.txt", sep=',', index=False)

    TRIfirms_vin = fuzzyMerge(TRIfirms_vin, Preqinfirms, 'vintageName_noSpace', 'd_firmName_noSpace')
    TRIfirms_vin.to_csv("./name_matched/vinMatched.txt", sep=',', index=False)

    TRIfirms_sub = fuzzyMerge(TRIfirms_sub, Preqinfirms, 'subfirmName_noSpace', 'd_firmName_noSpace')
    TRIfirms_sub.to_csv("./name_matched/subMatched.txt", sep=',', index=False)

    TRIfirms_fac = fuzzyMerge(TRIfirms_fac, Preqinfirms, 'subFacilityName_noSpace', 'd_firmName_noSpace')
    TRIfirms_fac.to_csv("./name_matched/facMatched.txt", sep=',', index=False)

In [ ]: