<jupyter_start><jupyter_text>Fuzzy Matching with RL - 247 & Rivals> Leveraging the RL library to determine approximate matching over a range of fields using various string methods methods.  This specifically focuses on 247 & Rivals first.<jupyter_code>import json
import pandas
import time
import os
import recordlinkage
import csv
import core_constants as cc
import functions as fx
import sqlite3 as sql

#not currently using jellyfish
import jellyfish as jf<jupyter_output><empty_output><jupyter_text>Load and Merge Source Files Then Create a List of Dicts for each Dataset> This was originally set up for all of the keys in the sourcefiles.json config.  Since this file is currently only going to serve 247 & Rivals, I've hardcoded the keys to fetch<jupyter_code>dfFinal = fx.doFuzzyMatching('NFL', 'Sports247')
dfFinal.head()
conn = sql.connect(cc.databaseName) 
          
sql_query = pandas.read_sql_query ('''
                               SELECT
                               *
                               FROM SourcedPlayers
                               WHERE KeyDataSet = 1
                               ''', conn)

df_247 = pandas.DataFrame(sql_query, columns = ['IDYR', 'College', 'Year'])
df_247.set_index('IDYR', append=False, inplace=True)
sql_query = pandas.read_sql_query ('''
                               SELECT
                               *
                               FROM SourcedPlayers
                               WHERE KeyDataSet = 3
                               ''', conn)

df_NFL = pandas.DataFrame(sql_query, columns = ['ID', 'College', 'Year'])
df_NFL.set_index('ID', append=False, inplace=True)

#dfFinal.reset_index(drop=True, inplace=True)
#dfFinal.set_index(['sourceID', 'targetID'], append=False, inplace=True)

dfFinal.head()
miFinal = pandas.MultiIndex.from_frame(dfFinal)
miFinal
recordlinkage.write_annotation_file(
    "annotation_nfl.json",
    miFinal[0:1100],
    df_NFL,
    df_247,
    dataset_a_name="NFL",
    dataset_b_name="Master"
)
dfFinal.head()<jupyter_output><empty_output><jupyter_text>Create Blockers> I swear this isn't working.  And honestly I guess I'm ok with it not working since I'm doing string operations later on this script.  This used to take longer than 20 minutes but has been fixed.<jupyter_code>indexer = recordlinkage.BlockIndex(on=['College', 'Year'])
candidate_links = indexer.index(sports247, rivals)<jupyter_output><empty_output><jupyter_text>Define Features & Generate Comparison Vector Set> These are all pretty straightforward.  The toughest one to assess is position - since the services don't always categorize players in the same way or have the same abbreviation for a single position.  Since this is often only a 2 or 3 letter string, I decided to do an exact match.<jupyter_code>c = recordlinkage.Compare()

c.exact('IDYR', 'IDYR', label='IDYR')
c.string('PlayerName', 'PlayerName', method='damerau_levenshtein', label='PlayerName')
c.string('City', 'City', label='City')
c.exact('State', 'State', label='State')
c.string('HighSchool', 'HighSchool', label='HighSchool')
c.exact('Position', 'Position', label='Position')

try:
    features = c.compute(candidate_links, sports247, rivals)
except KeyError as e:
    print(e)<jupyter_output><empty_output><jupyter_text>Create Sum> Final value will be between 0 and 1.  Sum represents a %confidence% level during fuzzy matching. Since an exact match on ID dictates a 100% confident match, I've removed that column from the sum.<jupyter_code>features['Sum'] = (features['PlayerName'] + features['City'] + features['State'] + features['HighSchool']  + features['Position'])/5
features.head()<jupyter_output><empty_output><jupyter_text>Filter by Threshold> Agreed upon confidence level for 247 & Rivals is .6943.  This takes longer than expected.<jupyter_code>count = 0
filteredList = []
noMatch = []

features['sports247_IDYR'] = features.index.get_level_values(0)
features['rivals_IDYR'] = features.index.get_level_values(1)

for idx, data in features.groupby(level=0):
    data = data.loc[data['Sum'].idxmax()]
    if (data['IDYR'] == 1):
        filteredList.append(data)
    elif (data['IDYR'] != 1 and data['Sum'] > .6943):
        filteredList.append(data)
    else:
        noMatch.append(data)<jupyter_output><empty_output><jupyter_text>Convert Series to Dataframe> output of the above is a Pandas Series and needs to be pushed to a dataframe to be accessible<jupyter_code>dfFinal = pandas.DataFrame()
dfFinal = dfFinal.append(filteredList)
dfFinal.sort_values(by='SUM')
dict_dfFinal = dfFinal.to_dict('records')
dict_dfFinal[0]
for record in dict_dfFinal:
    Values = [record['sports247_IDYR'], record['rivals_IDYR'], 2, 3, record['Sum']]
    query = '''INSERT INTO RecordLinks(MasterID, TargetID, KeyDataSet, KeyLinkType, LinkConfidence)
        VALUES (?,?,?,?,?)'''
    
    conn = sql.connect(cc.databaseName)
    c = conn.cursor()
    
    c.execute(query, Values)
    conn.commit()
    
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11019835704267682, 'fraction_numerical': 0.014225606090963735, 'mean_word_length': 3.9279368213228034, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 37, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '176241', 'license': 'Apache-2.0', 'n_tokens_mistral': 1609, 'n_tokens_neox': 1491, 'n_words': 432, 'path': 'j_notebooks/Fuzzy Matching with RL.ipynb', 'repo_name': 'andrewt-cville/fb_recruiting', 'size': '4991'}