import pandas as pd
from pathlib import Path
import fuzzymatcher
import recordlinkage

"""
Explanation: Python Tools for Record Linking and Fuzzy Matching
This notebook accompanies the article on Practical Business Python
This notebook relies on fuzzymatcher and the Python Record Linkage Toolkit
End of explanation
"""


hospital_accounts = pd.read_csv(
    'https://github.com/chris1610/pbpython/raw/master/data/hospital_account_info.csv'
)
hospital_reimbursement = pd.read_csv(
    'https://raw.githubusercontent.com/chris1610/pbpython/master/data/hospital_reimbursement.csv'
)

hospital_accounts.head()

hospital_reimbursement.head()

# Columns to match on from df_left
left_on = ["Facility Name", "Address", "City", "State"]

# Columns to match on from df_right
right_on = [
    "Provider Name", "Provider Street Address", "Provider City",
    "Provider State"
]

# Now perform the match
# It will take several minutes to run on this data set
matched_results = fuzzymatcher.fuzzy_left_join(hospital_accounts,
                                               hospital_reimbursement,
                                               left_on,
                                               right_on,
                                               left_id_col='Account_Num',
                                               right_id_col='Provider_Num')

matched_results.head()

# Reorder the columns to make viewing easier
cols = [
    "best_match_score", "Facility Name", "Provider Name", "Address", "Provider Street Address",
    "Provider City", "City", "Provider State", "State"
]

# Let's see the best matches
matched_results[cols].sort_values(by=['best_match_score'], ascending=False).head(5)

# Now the worst matches
matched_results[cols].sort_values(by=['best_match_score'],
                                  ascending=True).head(5)

# Look at the matches around 1
matched_results[cols].query("best_match_score <= 1").sort_values(
    by=['best_match_score'], ascending=False).head(10)

matched_results[cols].query("best_match_score <= .80").sort_values(
    by=['best_match_score'], ascending=False).head(5)

"""
Explanation: Example using fuzzymatcher
End of explanation
"""


# Re-read in the data using the index_col
hospital_accounts = pd.read_csv(
    'https://github.com/chris1610/pbpython/raw/master/data/hospital_account_info.csv',
    index_col='Account_Num'
)
hospital_reimbursement = pd.read_csv(
    'https://raw.githubusercontent.com/chris1610/pbpython/master/data/hospital_reimbursement.csv',
    index_col='Provider_Num'
)

hospital_accounts.head()

hospital_reimbursement.head()

# Build the indexer
indexer = recordlinkage.Index()
# Can use full or block
#indexer.full()
#indexer.block(left_on='State', right_on='Provider State')

# Use sortedneighbor as a good option if data is not clean
indexer.sortedneighbourhood(left_on='State', right_on='Provider State')

candidates = indexer.index(hospital_accounts, hospital_reimbursement)

# Let's see how many matches we want to do
print(len(candidates))

# Takes 3 minutes using the full index.
# 14s using sorted neighbor
# 7s using blocking
compare = recordlinkage.Compare()
compare.exact('City', 'Provider City', label='City')
compare.string('Facility Name',
               'Provider Name',
               threshold=0.85,
               label='Hosp_Name')
compare.string('Address',
               'Provider Street Address',
               method='jarowinkler',
               threshold=0.85,
               label='Hosp_Address')
features = compare.compute(candidates, hospital_accounts,
                           hospital_reimbursement)

features

# What are the score totals?
features.sum(axis=1).value_counts().sort_index(ascending=False)

# Get the potential matches
potential_matches = features[features.sum(axis=1) > 1].reset_index()

potential_matches['Score'] = potential_matches.loc[:, 'City':'Hosp_Address'].sum(axis=1)
potential_matches.head()

hospital_accounts.loc[51216,:]

hospital_reimbursement.loc[268781,:]

# Add some convenience columns for comparing data
hospital_accounts['Acct_Name_Lookup'] = hospital_accounts[[
    'Facility Name', 'Address', 'City', 'State'
]].apply(lambda x: '_'.join(x), axis=1)

hospital_reimbursement['Reimbursement_Name_Lookup'] = hospital_reimbursement[[
    'Provider Name', 'Provider Street Address', 'Provider City',
    'Provider State'
]].apply(lambda x: '_'.join(x), axis=1)

reimbursement_lookup = hospital_reimbursement[['Reimbursement_Name_Lookup']].reset_index()
account_lookup = hospital_accounts[['Acct_Name_Lookup']].reset_index()

account_lookup.head()

reimbursement_lookup.head()

account_merge = potential_matches.merge(account_lookup, how='left')

account_merge.head()

reimbursement_lookup.head()

# Let's build a dataframe to  compare
final_merge = account_merge.merge(reimbursement_lookup, how='left')

cols = [
    'Account_Num', 'Provider_Num', 'Score', 'Acct_Name_Lookup',
    'Reimbursement_Name_Lookup'
]

final_merge[cols].sort_values(by=['Account_Num', 'Score'], ascending=False)

# If you need to save it to Excel -
#final_merge.sort_values(by=['Account_Num', 'Score'],
#                        ascending=False).to_excel('merge_list.xlsx',
#                                                  index=False)

final_merge[final_merge['Account_Num']==11035][cols]

final_merge[final_merge['Account_Num']==56375][cols]

"""
Explanation: Example using Python Record Linkage Toolkit
End of explanation
"""


hospital_dupes = pd.read_csv(
    'https://github.com/chris1610/pbpython/raw/master/data/hospital_account_dupes.csv',
    index_col='Account_Num')

hospital_dupes.head()

# Deduping follows the same process, you just use 1 single dataframe
dupe_indexer = recordlinkage.Index()
dupe_indexer.sortedneighbourhood(left_on='State')
dupe_candidate_links = dupe_indexer.index(hospital_dupes)


# Comparison step
compare_dupes = recordlinkage.Compare()
compare_dupes.string('City', 'City', threshold=0.85, label='City')
compare_dupes.string('Phone Number',
                     'Phone Number',
                     threshold=0.85,
                     label='Phone_Num')
compare_dupes.string('Facility Name',
                     'Facility Name',
                     threshold=0.80,
                     label='Hosp_Name')
compare_dupes.string('Address',
                     'Address',
                     threshold=0.85,
                     label='Hosp_Address')
dupe_features = compare_dupes.compute(dupe_candidate_links, hospital_dupes)

dupe_features

dupe_features.sum(axis=1).value_counts().sort_index(ascending=False)

potential_dupes = dupe_features[dupe_features.sum(axis=1) > 2].reset_index()
potential_dupes['Score'] = potential_dupes.loc[:, 'City':'Hosp_Address'].sum(axis=1)

potential_dupes.sort_values(by=['Score'], ascending=True)

# Take a look at one of the potential duplicates
hospital_dupes[hospital_dupes.index.isin([51567, 41166])]

"""
Explanation: Dedupe the data
End of explanation
"""


