<jupyter_start><jupyter_text>IntroductionIn this notebook, we want to study the two datasets in `beerAdv_beer_brewery.tsv` and in `rateBeer_beer_brewery.tsv`. The columns of these datasets are like this:                              beer_id ¦ beer_name ¦ brewery_id ¦ brewery_name            The idea of this notebook is to create a similarity measure in order to find the same elements in both these datasets. Once we have this similarity measure, we want to find the limit X such that:- All pairs with a similarity value of $X' > X$ are considered as the same beer- All pairs with a similarity value of $X'' < X$ are not taken into account.In order to achieve this, this notebook is composed of three parts. The first part is some sort of simple Data Analysis. Then there will be the creation of the pairs and the similarity value. Finally the part to find the best value $X$. Table of contents1. [Data Analysis](data_analysis)2. [Similarity Measure](similarity)3. [Matching the beers](matching) 4. [Further work](further_work)Each parts is separated. Therefore, you don't have to run the previous parts in order to run a specific part.<jupyter_code># Useful imports
import pandas as pd
import numpy as np
import codecs
from IPython.display import HTML
import matplotlib.pyplot as plt
import unicodedata
import random

from fuzzywuzzy import fuzz
import multiprocessing
from joblib import Parallel, delayed
import pickle

# For the Pyth§on notebook
%matplotlib inline
%reload_ext autoreload
%autoreload 2<jupyter_output><empty_output><jupyter_text>Data AnalysisFirst, we need to analyze the data. It's a simple data analysis since we only have two usefull features: `beer_name` and `brewery_name`.<jupyter_code># Datasets
beerAdvocate_dataset = './data/beerAdv_beer_brewery.tsv'
rateBeer_dataset = './data/rateBeer_beer_brewery.tsv'
columns = ['beer_id', 'beer_name', 'brewery_id', 'brewery_name']
# Load the dataset with pandas
beerAdvocate = pd.read_table(beerAdvocate_dataset, header=None)
beerAdvocate.columns = columns
rateBeer = pd.read_table(rateBeer_dataset, header=None, encoding='utf-8')
rateBeer.columns = columns<jupyter_output><empty_output><jupyter_text>Print the two datasets<jupyter_code>beerAdvocate.head()
rateBeer.head()<jupyter_output><empty_output><jupyter_text>We just stop two minutes here. We can see a problem with the encoding of the file for *beerAdvocate*.<jupyter_code>name = beerAdvocate.ix[12].beer_name
print(name)<jupyter_output>Master PolotmavÃ½ 13Â°<jupyter_text>The encoding here is **latin_1**. Therefore, we can find the UTF-8 encoding by doing this:<jupyter_code>new_name = bytes(name,'latin_1').decode('utf-8')
print(new_name)<jupyter_output>Master Polotmavý 13°<jupyter_text>Let's do it everywhere for *beerAdvocate*!<jupyter_code>def decode_from_latin_1(string):
    try:
        return bytes(string ,'latin_1').decode('utf-8')
    except:
        return string
for i in range(len(beerAdvocate)):
    # Change the name of the beer
    beerAdvocate.set_value(i, 'beer_name', decode_from_latin_1(beerAdvocate.ix[i].beer_name))
    beerAdvocate.set_value(i, 'brewery_name', decode_from_latin_1(beerAdvocate.ix[i].brewery_name))

beerAdvocate.ix[12].beer_name<jupyter_output><empty_output><jupyter_text>For string comparison, it is better to remove all the accents. You will see later that we are using a library called `fuzzywuzzy`. And if we compare two letters, one with accent and one without it won't work.<jupyter_code>fuzz.ratio("é", "e")<jupyter_output><empty_output><jupyter_text>So, let's remove all the accents in the datasets.<jupyter_code>def strip_accents(text):
    """
    Strip accents from input String.

    :param text: The input string.
    :type text: String.

    :returns: The processed String.
    :rtype: String.
    """
    try:
        text = unicode(text, 'utf-8')
    except NameError: # unicode is a default on python 3 
        pass
    text = unicodedata.normalize('NFD', text)
    text = text.encode('ascii', 'ignore')
    text = text.decode("utf-8")
    return str(text)
print("Name with accents and weird char: %s"%beerAdvocate.ix[12].beer_name)
print("Name without accents and weird char: %s"%strip_accents(beerAdvocate.ix[12].beer_name))<jupyter_output>Name with accents and weird char: <NAME> 13°
Name without accents and weird char: <NAME> 13<jupyter_text>The function `strip_accents` works pretty well. Let's apply it to all the elements in the two datasets.<jupyter_code>for i in range(len(beerAdvocate)):
    # Change the name of the beer
    beerAdvocate.set_value(i, 'beer_name', strip_accents(beerAdvocate.ix[i].beer_name))
    beerAdvocate.set_value(i, 'brewery_name', strip_accents(beerAdvocate.ix[i].brewery_name))

for i in range(len(rateBeer)):
    # Change the name of the beer
    rateBeer.set_value(i, 'beer_name', strip_accents(rateBeer.ix[i].beer_name))
    rateBeer.set_value(i, 'brewery_name', strip_accents(rateBeer.ix[i].brewery_name))<jupyter_output><empty_output><jupyter_text>Let's print the number of entries in each dataset.<jupyter_code>print("Number of rows in beerAdvocate: %i"%(len(beerAdvocate)))
print("Number of rows in rateBeer: %i"%(len(rateBeer)))<jupyter_output>Number of rows in beerAdvocate: 66056
Number of rows in rateBeer: 110359<jupyter_text>Let's check the number of **unique** beers to see if it matches the number of rows in the dataset.<jupyter_code>unique_beers_beerAdvocate = beerAdvocate.beer_name.unique()
unique_beers_rateBeer = rateBeer.beer_name.unique()

print("Number of unique beers in beerAdvocate: %i"%(len(unique_beers_beerAdvocate)))
print("Number of unique beers in rateBeer: %i"%(len(unique_beers_rateBeer)))<jupyter_output>Number of unique beers in beerAdvocate: 56814
Number of unique beers in rateBeer: 110302<jupyter_text>It's already interesting to see that some beers have the same name in both datasets. Let's take one of them and see if they have the same `brewery_name`.<jupyter_code>duplicated_beers_beerAdvocate = beerAdvocate[beerAdvocate.beer_name.duplicated()].beer_name.unique()
duplicated_beers_rateBeer = rateBeer[rateBeer.beer_name.duplicated()].beer_name.unique()

print("Number of duplicated beer names in beerAdvocate: %i"%(len(duplicated_beers_beerAdvocate)))
print("Number of duplicated beer names in rateBeer: %i"%(len(duplicated_beers_rateBeer)))
dup_beer_dup_brewery = []

# Create list of tuples. 
#   First entry is the name of the duplicated beer
#   Second entry is the list of Brewery that is duplicated
#   Third entry is the list of indices to remove them easily.
for dup_beer in duplicated_beers_beerAdvocate:
    subdf = beerAdvocate[beerAdvocate.beer_name == dup_beer]
    if any(subdf.brewery_name.duplicated()):
        dup_beer_dup_brewery.append((dup_beer, list(subdf[subdf.brewery_name.duplicated()]["brewery_name"]), list(subdf[subdf.brewery_name.duplicated()].index)))
print("Number of beers with duplicated brewery in beerAdvocate: %i"%(len(dup_beer_dup_brewery)))<jupyter_output>Number of beers with duplicated brewery in beerAdvocate: 287<jupyter_text>Just check how the function `duplicated` works. For example, if we have two times the same brewery, this means that we would have 3 times the same beer and the same brewery in the data set.<jupyter_code>dup_beer_dup_brewery[3]
beerAdvocate[(beerAdvocate.beer_name == "Saison") & (beerAdvocate.brewery_name == "Triumph Brewing Company")]<jupyter_output><empty_output><jupyter_text>At this point, we imagine that the three entries are the same. But we directly see that the brewery_id is different. And if we go check the breweries on beerAdvocate, we see that the three Breweries are not at the same place but they have the same name. Therefore, we cannot say that these three beers are the same. In order to get a better similarity measure, we need to add a bit mopre information about the breweries and the beers. Here are the features we scrap from the websites:- For the brewery:    - The address- For the beer:    - The ABV (Alcohol by Volume)    - The StyleAt this point, we will not scrap the websites nor use other data. This will be done later in the project. Therefore, we just remove the duplicate indices.<jupyter_code>indices_to_remove = []
for i in range(len(dup_beer_dup_brewery)):
    indices_to_remove.extend(dup_beer_dup_brewery[i][2])
# Remove the indices in the dataset
beerAdvocate = beerAdvocate.drop(indices_to_remove, axis=0)
beerAdvocate.index = range(len(beerAdvocate))
beerAdvocate.tail()<jupyter_output><empty_output><jupyter_text>Now, we need to the same for the rateBeer dataset.<jupyter_code>dup_beer_dup_brewery = []

# Create list of tuples. 
#   First entry is the name of the duplicated beer
#   Second entry is the list of Brewery that is duplicated
#   Third entry is the list of indices to remove them easily.
for dup_beer in duplicated_beers_rateBeer:
    subdf = rateBeer[rateBeer.beer_name == dup_beer]
    if any(subdf.brewery_name.duplicated()):
        dup_beer_dup_brewery.append((dup_beer, list(subdf[subdf.brewery_name.duplicated()]["brewery_name"]), list(subdf[subdf.brewery_name.duplicated()].index)))
print("Number of beers with duplicated brewery in beerRate: %i"%(len(dup_beer_dup_brewery)))
dup_beer_dup_brewery
indices_to_remove = []
for i in range(len(dup_beer_dup_brewery)):
    indices_to_remove.extend(dup_beer_dup_brewery[i][2])
# Remove the indices in the dataset
rateBeer = rateBeer.drop(indices_to_remove, axis=0)
rateBeer.index = range(len(rateBeer))
rateBeer.tail()<jupyter_output><empty_output><jupyter_text>Save the cleaned data setNow that the datasets are cleaned, we can save them in a CSV format.<jupyter_code>beerAdvocate.to_csv('./data/beerAdvocate_cleaned.csv', index=False, encoding='utf-8')
rateBeer.to_csv('./data/rateBeer_cleaned.csv', index=False, encoding='utf-8')<jupyter_output><empty_output><jupyter_text>SimilarityIn this part, we want to create a similarity measure of the beers between the two datasets. Ideally, we would create a matrix of size $N\times M$, $N$ being the size of *RateBeer* dataset and $M$ being the size of *BeerAdvocate* dataset. But given the size of the two datasets, it's not possible. Therefore, the similarity function given [here](sim_func) will return the five most similar objects.<jupyter_code># Read the cleaned datasets
beerAdvocate = pd.read_csv('./data/beerAdvocate_cleaned.csv', dtype=str)
rateBeer = pd.read_csv('./data/rateBeer_cleaned.csv', dtype=str)
beerAdvocate.head()
rateBeer.head()
unique_rateBeer_breweries = rateBeer.brewery_name.unique()<jupyter_output><empty_output><jupyter_text>Similarity measureThe similarity measure has been thought to be the most efficient possible. Instead of computing the whole matrix of similarities, we compute the similarities using the two features separately. This function takes a line of the *beerAdvocate* dataset and test it against the *rateBeer* dataset.First, we start with the `brewery_name`. Indeed, it is possible to have beers with the same name but with different brewery. If it's the case, we cannot consider that they are the same. Therefore, we simply get the most similar `brewery_name` in the *rateBeer* dataset to the given entry of the *beerAdvocate* dataset. Then, we compute the similarity of the `beer_name` between the beers of the most similar brewery in *rateBeer* and the entry of the *beerAdvocate* dataset.To compute the similarities, we use the library [fuzzywuzzy](https://github.com/seatgeek/fuzzywuzzy). For the `brewery_name`, it's enough to use the function `token_set_ratio`. But for the beer, it's a little more complex due to all kind of changes in the name. First, we use the function `token_set_ratio`. Then, given if the name of the beer is a unique "generic" name or not, we use the function `ratio` or `partial_ratio` respectively. This helps to remove the elements matching together just by tokens.<jupyter_code>def get_most_similar_brewery(brewery_test):
    """
        Return the most similar brewery in the dataset rateBeer given
        a brewery name in the beerAdvocate dataset.
        
        INPUT:
            brewery_test: String of a brewery name in the beerAdvocate dataset
            
        OUTPUT:
            brewery_sim: Similarity value between the brewery in the beerAdvocate dataset
                         and the most similar brewery name in the rateBeer dataset
                         
            idx_best_match_brewery: Index to get the name of the brewery in the rateBeer dataset
            
    
    """
    # Similarity of brewery_name
    brewery_sim = np.zeros(len(unique_rateBeer_breweries))
    for i in range(len(unique_rateBeer_breweries)):
        brewery_sim[i] = fuzz.token_set_ratio(brewery_test, unique_rateBeer_breweries[i])/100.0
        
    # Get the best match
    idx_best_match_brewery = np.argmax(brewery_sim)
    brewery_sim = np.max(brewery_sim) 
    
    return brewery_sim, idx_best_match_brewery

def similarity_between_beers(beer_test, beers_name_beerRate):
    """
        Return the list of similarity values between the beer_test from the 
        beerAdvocate dataset and a set of beers in the rateBeer dataset.
        
        INPUT:
            beer_test: Name of an active beer in the beerAdvocate dataset
            
            beer_names_beerRate: List of beers name in the beerRate dataset
            
        OUTPUT:
            beers_sim: List of similarities between the beer_test and the beers_name_beerRate
    
    """
    
    N = len(beers_name_beerRate)

    # Test the beers in previous list against beer from beerAdvocate
    beers_sim = np.zeros(N)
    for i in range(N):
        # First test is done with the token_set_ratio
        val1 = fuzz.token_set_ratio(beer_test, beers_name_beerRate[i])/100.0
        
        # Second test depends if the name of the beer_test has a space character.
        # If it has a space character, a partial ratio works fine due to the possibility
        # that the "words" can be swapped.
        #
        # If it doesn't have a space character, we need to be more strict, therefore using
        # the ratio is better. We also add the condition that if the two names are not 
        # exactly the same, it means that the beer in beers_name_beerRate is composed
        # of the generic name in beer_test plus some additional words => it's not the same
        # beer. (Think about Heineken and Heinken white.)
        
        if " " in beer_test:
            val2 = fuzz.partial_ratio(beer_test, beers_name_beerRate[i])/100.0
        else:
            val2 = fuzz.ratio(beer_test, beers_name_beerRate[i])/100.0
            if val2 < 1.0:
                val2 = val2/2.0

        beers_sim[i] = val1*val2
        
    return beers_sim

def tuple_matches(active_beer, beers_name, brewery_name, beers_sim, brewery_sim, nbr_match=5):
    """
        Return a list of tuples of size number_matches with the most similar beers to the 
        test beer.
        
        INPUT:
            active_beer: Name of the beer in the beerAdvocate dataset
            
            beer_names: List of beers name in the beerRate dataset
            
            breweri_name: Name of the brewery in the beerRate dataset that 
                          has the highest similarity.
                          
            beers_sim: List of similarity values for the beers in the beerRate dataset
            
            brewery_sim: Similarity value of the most matching brewery.
            
            number_matches: Number of best matches to return. Default is 5.
            
        OUTPUT:
            matches: List of tuples with the most matching beers to the active beer
                     in the beerAdvocate dataset.
    
    """ 
    
    N = len(beers_name)
    
    # Get the best similarities
    best_idx = np.argsort(beers_sim)[::-1]
    
    if N < 5:
        nbr_match = N
    
    # Get the 5 best matches and create a tuple
    matches = []
    for i in range(nbr_match):
        matches.append((beers_name[best_idx[i]], brewery_name, beers_sim[best_idx[i]]*brewery_sim))
                
    ## Double check 
    # If the elements have the same best values, we sort them by the 
    # difference of length between the beer names. 
    # This problem comes from the tokenisation for the similarity
    best_sim = matches[0][2]
    idx = [0]
    diff_length_beer_name = [abs(len(matches[0][0])-len(active_beer))]
    for i in range(1,nbr_match):
        if matches[i][2] == best_sim:
            idx.append(i)
            diff_length_beer_name.append(abs(len(matches[i][0])-len(active_beer)))
            
    best_idx = np.argsort(diff_length_beer_name)
    idx = np.asarray(idx)
    
    matches = np.asarray(matches)
    
    matches[idx] = matches[best_idx]
    
    return matches
def similarity(test):
    """
        Given a line in the beerAdvocate dataset, it returns a list of tuples containing the best matches in the 
        rateBeer dataset.
        
        INPUT:
            test: A line in the beerAdvocate dataset
            
        OUTPUT:
            matches: a list of tuples containing the best matches in the beerRate dataset to the INPUT line.
    """
    # Get brewery and beer name from beerAdvocate
    brewery = test.brewery_name.replace("'", "")
    beer = test.beer_name.replace("'", "")
    
    # Get the brewery in rateBeer that matches best the active brewery in the 
    # beerAdvocate dataset
    brewery_sim, idx_best_match_brewery = get_most_similar_brewery(brewery)    
    
    # List of beers in rateBeer with the brewery being the best match
    beers_name_rateBeer = list(rateBeer[rateBeer.brewery_name == unique_rateBeer_breweries[idx_best_match_brewery]].beer_name)
    
    # Get the similarity values for all beers in beer_names
    beers_sim = similarity_between_beers(beer, beers_name_rateBeer)
            
    # Get the best matches 
    matches = tuple_matches(beer, beers_name_rateBeer, unique_rateBeer_breweries[idx_best_match_brewery], beers_sim, brewery_sim)
                
    return matches<jupyter_output><empty_output><jupyter_text>We can test here the similarity measure on single line of the *beerAdvocate* dataset.<jupyter_code>bb=32
print(beerAdvocate.ix[bb])
print()
print(similarity(beerAdvocate.ix[bb]))<jupyter_output>beer_id                               1814
beer_name       <NAME> Porter
brewery_id                             100
brewery_name             McNeill's Brewery
Name: 32, dtype: object

[['<NAME> Porter' 'McNeills Brewery' '1.0']
 ['<NAME>' 'McNeills Brewery' '0.5767']
 ['<NAME>' 'McNeills Brewery' '0.511']
 ['<NAME>' 'McNeills Brewery' '0.5025']
 ['<NAME>' '<NAME>' '0.469']]<jupyter_text>Prepare the Parallel implementation<jupyter_code>nbr_jobs = multiprocessing.cpu_count()
nbr_jobs<jupyter_output><empty_output><jupyter_text>Compute the list of similarities. **Warning**: On a Intel i7-6820HK CPU with 4 cores and 8 threads, it took 45 minutes.<jupyter_code>%%time
M = len(beerAdvocate)
N = len(rateBeer)

sim = Parallel(n_jobs=nbr_jobs)(delayed(similarity)(beerAdvocate.ix[idx]) for idx in range(M))<jupyter_output>CPU times: user 49.6 s, sys: 1.7 s, total: 51.3 s
Wall time: 44min 17s<jupyter_text>Save the list as a pickle file.<jupyter_code>pickle.dump(sim, open('./data/similarity.pickle', "wb"))<jupyter_output><empty_output><jupyter_text>Matching the beersNow, we want to match the beers together and create a new pandas DataFrame with the beers that are the same in the two datasets.First, we can load the pickle file. **Warning**: Don't forget to unzip it if you did not run the [similarity](similarity) section.<jupyter_code># Read the cleaned datasets
beerAdvocate = pd.read_csv('./data/beerAdvocate_cleaned.csv', dtype=str)
rateBeer = pd.read_csv('./data/rateBeer_cleaned.csv', dtype=str)

sim = pickle.load(open('./data/similarity.pickle', "rb"))<jupyter_output><empty_output><jupyter_text>Print an example of the `sim` vector.<jupyter_code>sim[8]<jupyter_output><empty_output><jupyter_text>Here, we can choose to check the first and second (or even more) best matches. But after checking many different examples, we see that if we also use the second element it won't be better. Therefore, we simply want to create the array of best maching beers and breweries by taking the first element in the `sim` list.<jupyter_code>best_sim = np.zeros(len(sim))
best_match_beer = []
best_match_brewery = []
for i in range(len(sim)):
    best_sim[i] = sim[i][0][2]
    best_match_beer.append(sim[i][0][0])
    best_match_brewery.append(sim[i][0][1])<jupyter_output><empty_output><jupyter_text>Create a new DataFrame.<jupyter_code>matching_beers = beerAdvocate.copy()
matching_beers.columns = [s + '_beerAdvocate' for s in matching_beers.columns]
matching_beers['beer_name_rateBeer'] = best_match_beer
matching_beers['brewery_name_rateBeer'] = best_match_brewery
matching_beers['similarity'] = best_sim
matching_beers.head()<jupyter_output><empty_output><jupyter_text>Let's sort the DataFrame by the similarity value.<jupyter_code>matching_beers.sort_values(by='similarity', ascending=False, inplace=True)
matching_beers.index = range(len(matching_beers))
matching_beers.similarity.describe()<jupyter_output><empty_output><jupyter_text>Let's plot the histogram of the similarity values.<jupyter_code>plt.hist(list(matching_beers.similarity), 50)
plt.xlabel('Similarity value')
plt.ylabel('Number of entries')
plt.show<jupyter_output><empty_output><jupyter_text>We plot it again but without the similarity equal to 1.<jupyter_code>plt.hist(list(matching_beers[matching_beers.similarity < 1].similarity), 50)
plt.xlabel('Similarity value')
plt.ylabel('Number of entries')
plt.show<jupyter_output><empty_output><jupyter_text>This histogram is really interesting. We see that we have a lot of beers where the matching is really good. So, if we want to be sure with a very high probability that the beers are the same, then we can choose to take only the entries with a similarity value of 1. But we will loose some beers.In order to choose a good threshold value, we need to compute the recall and precision. In order to compute it, we first manually labelled some matching as `True` for True positive and `False` as False positive. The first thing we need to do is to compute intervals (on the similarity values) such that the number of entries in each interval is the same.<jupyter_code>def intervals(matching_beers, nbr_intervals):
    """
        Create intervals with same number of entries in each interval.
        
        The first interval is when the similarity is equal to 1. 
    """
    df_without_one = matching_beers[matching_beers.similarity < 1]
    N = len(df_without_one)
    
    nbr_per_interval = round(N/nbr_intervals)
    
    sim_values = [1.0]
    indices = [0]
    
    for i in range(nbr_intervals):
        idx = df_without_one.index[i*nbr_per_interval]
        indices.append(idx)
        sim_values.append(df_without_one.similarity[idx])
        
    idx = df_without_one.index[-1]
    indices.append(idx+1)
    sim_values.append(df_without_one.similarity[idx])
    
    return indices, sim_values 

def pick_random_entries(dataframe, index_start, index_end, number_entries):
    ""
    list_random_index = random.sample(range(index_start, index_end), number_entries)
    list_random_index.sort()
    
    random_entries = dataframe.ix[list_random_index][['beer_name_beerAdvocate', 'beer_name_rateBeer', 'brewery_name_beerAdvocate', 'brewery_name_rateBeer', 'similarity']]
    
    for i in random_entries.index:
        print(i)
    
    return random_entries
    
indices, sim_values = intervals(matching_beers, 30)
print(indices)
print(sim_values)
interval = 0
pick_random_entries(matching_beers, indices[interval], indices[interval+1]-1, 20)<jupyter_output>491
528
1311
3722
3874
4640
5386
5501
6044
6323
7450
8336
9108
11047
11945
13582
14211
16921
17274
17473<jupyter_text>After doing the hand labeling, we can load the file.<jupyter_code>handLabels = pd.read_csv('./data/hand_labels.csv')
handLabels.head()<jupyter_output><empty_output><jupyter_text>Prepare some vectors for interprating the results and choose the threshold.<jupyter_code>accuracy = np.zeros(len(sim_values)-1)
# True positive rate
tpr = np.zeros(len(sim_values)-1)
# False positive rate
fpr = np.zeros(len(sim_values)-1)

retrieved = 0
true_positive = 0
false_positive = 0

for i in range(len(sim_values)-1):
    df = handLabels[(handLabels['indices'] >= indices[i]) & (handLabels['indices'] <= indices[i+1]-1)]
    retrieved += len(df)
    true_positive += len(df[df.matching])
    false_positive += len(df)-len(df[df.matching])
    
    accuracy[i] = (true_positive)/retrieved
    tpr[i] = true_positive
    fpr[i] = false_positive

tpr = tpr/true_positive
fpr = fpr/false_positive<jupyter_output><empty_output><jupyter_text>First, we want to evaluate the ROC curve and the area under the ROC curve.<jupyter_code>area = np.trapz(tpr, fpr)
plt.plot(fpr, tpr, '-o')
plt.xlabel('True Positive Rate')
plt.ylabel('False Positive Rate')
plt.title('ROC curve - Area under the curve: %.2f'%area)
plt.show()<jupyter_output><empty_output><jupyter_text>We see that the area under the ROC curve is **0.96** which is excellent. Now, in order to choose the best threshold, we want to have more than 99% of accuracy. So, let's plot the accuracy.<jupyter_code>plt.plot(sim_values[1:], accuracy, '-o')
plt.ylabel('Precision value')
plt.xlabel('Threshold for similarity value')
plt.show()
plt.plot(sim_values[:7], accuracy[:7], '-o')
plt.plot([0.75, 1], [0.99, 0.99], '--', color='k')
plt.xticks(sim_values[:7], ["%.2f"%i for i in sim_values[:7]], rotation='vertical')
plt.ylabel('Precision value')
plt.xlabel('Threshold for similarity value')
plt.show()<jupyter_output><empty_output><jupyter_text>With the last graph, we see that if we want a precision of at least 99%, then we have to choose a threshold of **0.81**.<jupyter_code>threshold = 0.81 # Chosen empirically
good_matching_beers = matching_beers[matching_beers.similarity >= threshold]
good_matching_beers.index = range(len(good_matching_beers))
print("Number of good matching beers: %i"%len(good_matching_beers))<jupyter_output>Number of good matching beers: 24038<jupyter_text>Now, we want to be sure that we don't have duplicated values.<jupyter_code>len(good_matching_beers[good_matching_beers.beer_name_rateBeer.duplicated()])<jupyter_output><empty_output><jupyter_text>Sadly, we have some duplicated values for the `beer_name_rateBeer`. Therefore, we need to find a way to remove them.<jupyter_code>duplicated_beer_names = good_matching_beers[good_matching_beers.beer_name_rateBeer.duplicated()].beer_name_rateBeer.unique()<jupyter_output><empty_output><jupyter_text>We decide that we will class the duplicated beers using the function `ratio` of the `fuzzywuzzy` library. The entry with the best ratio on the `beer_name` and the `brewery_name` will stay. The other entries will be deleted.There's just a special case. The beers with a "generic" name. We decide that we remove all of them if the ratio is smaller then 90%. The reason is that if we don't do that, it will match the generic name to a beer with the generic name and the smallest addition. For example, the generic name being *Heineken*, the beer *Heineken dry* will be more similary than the beer *Heineken white*. And both these beers are different from the beer with the generic name.<jupyter_code>index_to_remove = []
for bn in duplicated_beer_names:
    df = good_matching_beers[good_matching_beers.beer_name_rateBeer == bn]
    ratio = []
    for i in df.index:
        val1 = fuzz.ratio(df.ix[i]['beer_name_beerAdvocate'], df.ix[i]['beer_name_rateBeer'])/100
        val2 = fuzz.ratio(df.ix[i]['brewery_name_beerAdvocate'], df.ix[i]['brewery_name_rateBeer'])/100
        ratio.append(val1*val2)
    df['ratio'] = ratio
    max_ratio = np.max(ratio)
    beer_test = df.ix[df.index[0]].beer_name_rateBeer
    if " " in beer_test:
        index_to_remove.extend(list(df[df.ratio != max_ratio].index))
    else:
        if max_ratio < 0.9:
            index_to_remove.extend(list(df.index))
        else:
            index_to_remove.extend(list(df[df.ratio != max_ratio].index))
good_matching_beers = good_matching_beers.drop(index_to_remove)
good_matching_beers.index = range(len(good_matching_beers))<jupyter_output><empty_output><jupyter_text>Now that we removed some duplicated beers, we will only have the beers with the same ratio left. So, let's check the duplicated beers again.<jupyter_code>len(good_matching_beers[good_matching_beers.beer_name_rateBeer.duplicated()])
duplicated_beer_names = good_matching_beers[good_matching_beers.beer_name_rateBeer.duplicated()].beer_name_rateBeer.unique()<jupyter_output><empty_output><jupyter_text>Print the duplicated beers that remain to see what is the problem.<jupyter_code>index_to_remove = []
for bn in duplicated_beer_names:
    df = good_matching_beers[good_matching_beers.beer_name_rateBeer == bn]
    ratio = []
    for i in df.index:
        val1 = fuzz.ratio(df.ix[i]['beer_name_beerAdvocate'], df.ix[i]['beer_name_rateBeer'])/100
        val2 = fuzz.ratio(df.ix[i]['beer_name_rateBeer'], df.ix[i]['beer_name_beerAdvocate'])/100
        ratio.append(val1*val2)
    df['ratio'] = ratio 
    index_to_remove.extend(list(df.index))
    print(df[['beer_name_rateBeer', 'beer_name_beerAdvocate', 'similarity', 'ratio']])
    print()<jupyter_output>/home/gael/anaconda3/lib/python3.5/site-packages/ipykernel/__main__.py:9: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy<jupyter_text>Most of the remaining duplicates are difficult to match. A good example can be this one:                                        beer_name_rateBeer         |          beer_name_beerAdvocate                    KB Double Chocolate Ale        |  KB Double Chocolate Ale Second Edition                       KB Double Chocolate Ale        |  KB Double Chocolate Ale  First Edition                With just the name information, we cannot say which `beer_name` in the *beerAdvocate* dataset is corresponding to the one in the *rateBeer* dataset. Therefore, we decide to remove these remaining duplicates.<jupyter_code>good_matching_beers = good_matching_beers.drop(index_to_remove)
good_matching_beers.index = range(len(good_matching_beers))<jupyter_output><empty_output><jupyter_text>We have now our **final** DataFrame of beers that matched together. We just print the number of beers we could matched and save it in a CSV file.<jupyter_code>print("Number of beers that matched: %i"%len(good_matching_beers))<jupyter_output>Number of beers that matched: 23330<jupyter_text>We just add the ID of the *rateBeer* dataset for easier research later.<jupyter_code>beer_id_rateBeer = rateBeer.set_index('beer_name').to_dict()['beer_id']
brewery_id_rateBeer = rateBeer.set_index('brewery_name').to_dict()['brewery_id']
beer_id_rateBeer_list = []
brewery_id_rateBeer_list = []
for i in range(len(good_matching_beers)):
    beer_id_rateBeer_list.append(beer_id_rateBeer[good_matching_beers.ix[i].beer_name_rateBeer])
    brewery_id_rateBeer_list.append(brewery_id_rateBeer[good_matching_beers.ix[i].brewery_name_rateBeer])
good_matching_beers['beer_id_rateBeer'] = beer_id_rateBeer_list
good_matching_beers['brewery_id_rateBeer'] = brewery_id_rateBeer_list
# Rearrange columns to be beautiful =)
good_matching_beers = good_matching_beers[['beer_id_beerAdvocate', 'beer_name_beerAdvocate', 'brewery_id_beerAdvocate', 'brewery_name_beerAdvocate', 'beer_id_rateBeer', 'beer_name_rateBeer', 'brewery_id_rateBeer', 'brewery_name_rateBeer', 'similarity']]
# Save the Cleaned CSV
good_matching_beers.to_csv('./data/matching_beers.csv', index=False)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup