<jupyter_start><jupyter_text>Load Data<jupyter_code>import os
import pandas as pd 
import numpy as np
import logging<jupyter_output><empty_output><jupyter_text>Setup<jupyter_code>folder = 'internal_path_to_file'
input_file = 'yellow_yelp_all_pypostal.csv'
output_file = 'yellow_yelp_all_pypostal_output1.csv'
settings_file = 'yellow_yelp_all_pypostal_learned_settings1'
training_file = 'yellow_yelp_all_pypostal_training1.json'
fp = os.path.join(folder, input_file)
matches_file = os.path.join(folder, 'yellow_yelp_label2.csv')
log_level = logging.INFO
log_level = logging.DEBUG
logging.getLogger().setLevel(log_level)<jupyter_output><empty_output><jupyter_text>Dataframe view<jupyter_code>input_df = pd.read_csv(fp, sep=',', quotechar='"', dtype={'postalcode':'str'})
def get_clean_postalcode(x):
    
    if x is not None:
        subparts = str(x).split('.')
        return subparts[0]
    else:
        return None
input_df['postalcode'] = input_df['postalcode'].apply(lambda x: get_clean_postalcode(x))
input_df.to_csv(fp, sep=',', quotechar='"', index=False)
categories = list(input_df['category'].unique())
categories = [x for x in categories if str(x) != 'nan']
#category_corpus = input_df[['name', 'category']].drop_duplicates().to_dict(orient='records')
category_corpus = input_df.drop_duplicates().to_dict(orient='records')
category_corpus[0]
type(category_corpus)
phone_corpus = input_df[['name', 'phone']].to_dict(orient='records')<jupyter_output><empty_output><jupyter_text>Dedupe  Import modules<jupyter_code>import os
import csv
import re
import logging
import optparse

import dedupe
from unidecode import unidecode

from itertools import product
def pre_process(val):
    """
    Do a little bit of data cleaning with the help of Unidecode and Regex.
    Things like casing, extra spaces, quotes and new lines can be ignored.
    """
    try:
        val = re.sub('  +', ' ', val)
        val = re.sub('\n', ' ', val)
        val = val.strip().strip('"').strip("'").lower().strip()
        # If data is missing, indicate that by setting the value to `None`
        if not val:
            val = None
    except Exception as e:
        print(e)
    return val
def get_clean_data_dict(file_path):
    data_d = {}
    with open(fp) as f:
        reader = csv.DictReader(f)
        for row in reader:
            clean_row = [(k, pre_process(v)) for (k, v) in row.items()]
            row_id = int(row['id'])
            data_d[row_id] = dict(clean_row)

    return data_d<jupyter_output><empty_output><jupyter_text>Get Data in needed format<jupyter_code>data_dict = get_clean_data_dict(fp)
data_dict<jupyter_output><empty_output><jupyter_text>Define the Fields for dedupe<jupyter_code>fields = [
    {'field' : 'name', 'type': 'Name'},
    {'field' : 'category', 
     'type': 'FuzzyCategorical',
     'categories': categories,
     'corpus': category_corpus,
     'has missing' : True},
    {'field' : 'name', 'type': 'String'},
    {'field': 'postalcode', 'variable name': 'postalcode', 'type': 'Exact'},
    {'field' : 'address', 'type': 'Address'},
    {'field' : 'city', 'type': 'ShortString'},
    {'field' : 'phone', 'type': 'String'},
    {'field' : 'street', 'type': 'String', 'has missing' : True},
    {'field' : 'house_number', 'type': 'Exists', 'has missing' : True},
    {'field' : 'house', 'type': 'String', 'has missing' : True},
    
]<jupyter_output><empty_output><jupyter_text>Instantiate Dedupe<jupyter_code>deduper = dedupe.Dedupe(fields)
deduper.prepare_training(data_dict, blocked_proportion=0.7)
dedupe.consoleLabel(deduper)
deduper.train()
with open(training_file, 'w') as tf:
    deduper.writeTraining(tf)
with open(settings_file, 'wb') as sf:
    deduper.writeSettings(sf)<jupyter_output><empty_output><jupyter_text>run dedupe based on prior settings file<jupyter_code>deduper = None
with open(settings_file, 'rb') as f:
    deduper = dedupe.StaticDedupe(f)<jupyter_output><empty_output><jupyter_text>------<jupyter_code>threshold = deduper.threshold(data_dict, recall_weight=1)

deduper.predicates
clustered_dupes = deduper.match(data_dict, 0)
print('# duplicate sets', len(clustered_dupes))
cluster_membership = {}
cluster_id = 0
for (cluster_id, cluster) in enumerate(clustered_dupes):
    id_set, scores = cluster
    cluster_d = [data_dict[c] for c in id_set]
    canonical_rep = dedupe.canonicalize(cluster_d)
    for record_id, score in zip(id_set, scores):
        cluster_membership[record_id] = {
            "cluster id" : cluster_id,
            "canonical representation" : canonical_rep,
            "confidence": score
        }
singleton_id = cluster_id + 1
with open(output_file, 'w') as f_output, open(fp) as f_input:
    writer = csv.writer(f_output)
    reader = csv.reader(f_input)

    heading_row = next(reader)
    heading_row.insert(0, 'confidence_score')
    heading_row.insert(0, 'Cluster ID')
    canonical_keys = canonical_rep.keys()
    for key in canonical_keys:
        heading_row.append('canonical_' + key)

    writer.writerow(heading_row)

    for row in reader:
        row_id = int(row[0])
        if row_id in cluster_membership:
            cluster_id = cluster_membership[row_id]["cluster id"]
            canonical_rep = cluster_membership[row_id]["canonical representation"]
            row.insert(0, cluster_membership[row_id]['confidence'])
            row.insert(0, cluster_id)
            for key in canonical_keys:
                row.append(canonical_rep[key].encode('utf8'))
        else:
            row.insert(0, None)
            row.insert(0, singleton_id)
            singleton_id += 1
            for key in canonical_keys:
                row.append(None)
        writer.writerow(row)<jupyter_output><empty_output><jupyter_text>Predictions<jupyter_code>df = pd.read_csv(output_file)
df.columns
import pandas as pd
df = pd.read_csv(output_file)
df.sort_values(['Cluster ID'], inplace=True)
relevant_data = df[['Cluster ID', 'confidence_score', 'source', 'id']]
df = pd.read_csv(output_file)
df.sort_values(['Cluster ID'], inplace=True)
relevant_data = df[['Cluster ID', 'confidence_score', 'source', 'id']]

predictions = []
cluster_ids = relevant_data['Cluster ID'].value_counts()
for cluster_id in cluster_ids[cluster_ids>1].index:
    
    fodors_ids = relevant_data[
        (relevant_data['Cluster ID'] == cluster_id) & 
        (relevant_data['source'] == 'yellow_pages')
    ].id.values
    zagats_ids = relevant_data[
        (relevant_data['Cluster ID'] == cluster_id) & 
        (relevant_data['source'] == 'yelp')
    ].id.values
    
    match_interim = list(product(fodors_ids, zagats_ids))
    predictions.append(match_interim)
    
m = []
for cluster in predictions: 
    for combo in cluster: 
        m.append([combo[0], combo[1]])
        
predictions = pd.DataFrame(m, columns=['yellow_pages_id', 'yelp_id'])

predictions['yp-y'] = predictions.apply(lambda row: f"{row['yellow_pages_id']}-{row['yelp_id']}", axis=1)


len(predictions)
results = pd.read_csv(matches_file)
results['yp-y'] = results.apply(lambda row: f"{row['yellow_pages_id']}-{row['yelp_id']}", axis=1)


len(results)
non_duplicates = results[results['duplicate'] == 0]
non_dup_set = set(non_duplicates['yp-y'].values.tolist())
fp = (pred_set & non_dup_set)
fp
len(non_duplicates)
len(duplicates)
yellow_pages_dup_ids = set(duplicates['yellow_pages_id'].to_list())
yelp_dup_ids = set(duplicates['yelp_id'].to_list())
def filter_entries(x):
    use_entry = False
    
    if x['yellow_pages_id'] in yellow_pages_dup_ids:
        use_entry = True
    elif  x['yelp_id'] in yelp_dup_ids:
        use_entry = True
    
    if use_entry:
        return None
    else:
        return x
    
    
    
preds_comparable_with_duplicates = predictions[(predictions['yellow_pages_id'].isin(yellow_pages_dup_ids) == True) 
   | (predictions['yelp_id'].isin(yelp_dup_ids) == True) 
]
len(predictions)
len(preds_comparable_with_duplicates)
pred_set = set(preds_comparable_with_duplicates['yp-y'].values.tolist())
duplicates = results[results['duplicate'] == 1]
results = results[results['duplicate'] == 1]
len(results)
res_set = set(duplicates['yp-y'].values.tolist())
#pred_set = set(predictions['yp-y'].values.tolist())
tp = len(res_set & pred_set)
fn = len(res_set-pred_set)
fp = len(pred_set-res_set)

print(f'tp: {tp} fp: {fp} fn: {fn}')

folder = r'/Hadoco/1_Standard Data Integration/Sample Datasets/Unprocessed Data/customer_samples/fodors_zagats_restaurants'
predictions[predictions['zagats_id']==220]
results[results['zagats_id']==220]
results.head()
df[df['Cluster ID']==0]



df[df['Id'].isin(['534', '219', '221'])]<jupyter_output><empty_output><jupyter_text>New<jupyter_code>output_file
df = pd.read_csv(output_file)
df.sort_values(['Cluster ID'], inplace=True)
relevant_data = df[['Cluster ID', 'confidence_score', 'source', 'Id']]
relevant_data.head()
len(relevant_data['Cluster ID'].unique())
len(relevant_data['Cluster ID'].value_counts())
predictions = []
cluster_ids = relevant_data['Cluster ID'].value_counts()
def prepare_matches_file(matches_filepath, source1_name, source2_name):
    results = pd.read_csv('matches_fodors_zagats.csv')
    
    source1_col_name = source1_name + '_id'
    source2_col_name = source2_name + '_id'
    combo_col_name = source1_name[0] + '-' + source2_name[0] 
    
    results[combo_col_name] = results.apply(lambda row: 
                                   f"{row[source1_col_name]}-{row[source2_col_name]}", axis=1)
    return results

def prepare_predictions_file(output_file_path, source1_name, source2_name):
    df = pd.read_csv(output_file)
    df.sort_values(['Cluster ID'], inplace=True)
    relevant_data = df[['Cluster ID', 'confidence_score', 'source', 'Id']]
   

    predictions = []

    cluster_ids = relevant_data['Cluster ID'].value_counts()
    for cluster_id in cluster_ids[cluster_ids>1].index:

        source1_ids = relevant_data[
            (relevant_data['Cluster ID'] == cluster_id) & 
            (relevant_data['source'] == source1_name)
        ].Id.values
        source2_ids = relevant_data[
            (relevant_data['Cluster ID'] == cluster_id) & 
            (relevant_data['source'] == source2_name)
        ].Id.values

        match_interim = list(product(source1_ids, source2_ids))
        predictions.append(match_interim)

    m = []
    for cluster in predictions: 
        for combo in cluster: 
            m.append([combo[0], combo[1]])
     
    print()
    source1_col_name = source1_name + '_id'
    source2_col_name = source2_name + '_id'
    
    combo_col_name = source1_name[0] + '-' + source2_name[0] 
    
    predictions = pd.DataFrame(m, columns=[source1_col_name, source2_col_name])

    predictions[combo_col_name] = predictions.apply(lambda row: 
                                                    f"{row[source1_col_name]}-{row[source2_col_name]}", axis=1)
    return predictions

def calculate_f1_stats(match_set, prediction_set, mismatch_set=False):
    calculate_f1_score = False
    tn = None
    precision = None
    recall = None
    f1 = None
    
    if mismatch_set:
        calculate_f1_score = True
        tn = len(mismatch_set - pred_set)

    
    tp = len(match_set & pred_set)
    fn = len(match_set-pred_set)
    fp = len(pred_set-res_set)
    
    if tp > 0:
        precision = tp/(tp + fp)
    
    if calculate_f1_score:    
        recall = tp/(tp + fn)
        f1 = 2 * ((precision*recall)/(precision+recall)) 
    
    stats = {
    'f1_score': f1,
    'recall': recall,
    'precision': precision,
    'true_positive': tp,
    'true_negative': tn,
    'false_positive': fp,
    'false_negative': fn,
    }
        
    return stats    


def get_f1_stats(predictions_file_path, matches_file_path,
                       source1_name, source2_name):
    
    predictions = prepare_predictions_file(
        output_file_path = predictions_file_path,
        source1_name = source1_name,
        source2_name = source2_name,
    )
    matches = prepare_matches_file(
        matches_filepath = matches_file_path,
        source1_name = source1_name,
        source2_name = source2_name,
    )
    
    match_set = set(matches['f-z'].values.tolist())
    predictions_set = set(predictions['f-z'].values.tolist())
    
    
    stats = calculate_f1_stats(
        match_set = match_set,
        prediction_set = predictions_set
    )
    
    return stats
get_f1_stats(
    predictions_file_path = output_file,
    matches_file_path = matches_filepath,
    source1_name = 'fodors',
    source2_name = 'zagats'
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13296133917895575, 'fraction_numerical': 0.008609007572738143, 'mean_word_length': 3.868451688009313, 'pattern_counts': {'":': 1, '<': 45, '<?xml version=': 0, '>': 48, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '291788', 'license': 'MIT', 'n_tokens_mistral': 4450, 'n_tokens_neox': 4306, 'n_words': 794, 'path': 'dedupe/Customer Data/YellowPages_Yelp2_Example.ipynb', 'repo_name': 'MauriceStr/StandardizedDI_Clients_SDASP', 'size': '12545'}