<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <p style="text-align
Step1:  Bad match dates
Step2:  Setup MySQL connection
Login credentials for connecting to MySQL database.
Step3:  All import statements here.
Step4:  Try to connect to the tennis database on the local mysql host.  If successful,  print out the MySQL version number, if unsuccessful, exit gracefully.
Step11:  Extract data from MySQL database
We'll focus on cleaning the seven years of data between 2010 and 2016.
Step12:  Note some issues already
Step13:  Step I
Step14:  For each tournament (group), extract features to assist in "tourney-matching", including the number of matches in the tournament, the maxium and minimum listed dates, the location (in odds) and the tournament name (in matches.)
Step15:  Define lookup-table connecting Location in odds to tourney_name in matches.  Establishing this matching is not totally straightforward
Step17:  Define a function that will take as input a set of features from one tournament in odds, and try to match it to one tournament in matches.  The algorithm make sure that dates are roughly the same, and that there is a correspondence between Location and tourney_name.
Step18:  Perform the matches.
Step19:  Merge match numbers back into bigger odds table, checking that the sizes of each "matched" tournament are the same.
Step20:  Step II
Step21:  We'll use round information to help with the task of pairing individual matches .  To use round information effectively, we need to establish a correspondence between round signifiers in the two datasets.  The exact correspondence depends on how many tennis matches are in each tournmanet.
Step22:  With the round lookup tables defind, define a function that takes a row of odds, and figures how to map its round information to the round information in matches.
Step23:  We'll apply that mapping to each row of the odds dataframe.
Step24:  Before going on, a quick sanity check
Step25:  Looks good.  Now pare down the matches dataframe to contain only records in odds.
Step26:  To do player name matching, we split each name, and attempt to match on substrings.
Step27:  Match individual matches as follows
Step28:  Take a quick peak at the remaining names to get a sense for what the issues are
Step30:  Now match across all substrings in a name.  To do so, we need a function that will return True if there is a match between one or more strings in two string lists.
Step32:  We also need a function that will take each row of odds, and try to find a match for some appropriate subchunk of matches.
Step33:  Update match list, and check progress.
Step34:  Take a peak at the remaining names and see what the problems are.
Step35:  Some rounds are wrong.  Try re-matching both winner and loser last names, without insisting on round information.
Step37:  That solved some.  Now try matching unusual names, ignorning rounds. This involves slightly modifying the comparison function.
Step38:  Still some errors.  Let's take a look.
Step39:  Two big problems.  One is 'delbonis' vs. 'del bonis'.
Step40:  What remains is exclusively a mismatch of winner and loser. First sort and match keys, then check who really won and correct the data.
Step41:  To see who really won, calculate who played the most rounds.
Step42:  Correct mistakes any mistakes in either table.
Step43:  Now we can assign a single key for both odds and matches which corresponds on the match level.  We can also standardize match numbers within tournaments.
Step44:  Finally, we save our cleansed and matched datasets.
<ASSISTANT_TASK:>
Python Code:
import IPython as IP
IP.display.Image("example_of_name_matching_problems_mod.png",width=400,height=200,embed=True)
Explanation: <p style="text-align: center">  Merging "odds" and "player" data</p>
Author:  Carl Toews  
File:   merge_datasets.ipynb  
Description:
An obvious metric for assessing the quality of learning algorithms is to compare their profitability against on-line betting markets.  The notebook setup_mysql.ipynb loads into a MySQL database files from two distinct sources, one containing characteristics of the players (good for learning), the other containing odds for each match (good for assessing the learning).   Each record from the latter presumably corresponds to a unique record from the former.
Unfortunately, there are a couple of issues that make establishing this correspondence difficult, including each of the following:
1.  Incorrect match dates
2.  Incorrect spellings of player names
3.  Listing the winner as the loser, and vice versa.
4.  Inconsistent tournament names
5.  Inconsistent tournament ID numbers
This notebook records some of the methods I used to establish the correspondence.  It ultimately produces two DataFrames, one called matches (with player characteristics) and one called odds (with betting characteristics) of the same size and a one-to-one numerical key uniquely identifying each match across both datasets.  
Examples of bad data
Bad player names
End of explanation
IP.display.Image("../aux/bad_csv_data_mod.png",width=500,height=500,embed=True)
Explanation: Bad match dates
End of explanation
# name of database
db_name = "tennis"
# name of db user
username = "testuser"
# db password for db user
password = "test623"
# location of atp data files
atpfile_directory = "../data/tennis_atp-master/"
# location of odds data files
oddsfiles_directory = "../data/odds_data/"
# we'll read and write pickle files here
pickle_dir = '../pickle_files/'
Explanation: Setup MySQL connection
Login credentials for connecting to MySQL database.
End of explanation
import sqlalchemy # pandas-mysql interface library
import sqlalchemy.exc # exception handling
from   sqlalchemy import create_engine  # needed to define db interface
import sys # for defining behavior under errors
import numpy as np # numerical libraries
import scipy as sp
import pandas as pd # for data analysis
import pandas.io.sql as sql # for interfacing with MySQL database
import matplotlib as mpl # a big library with plotting functionality
import matplotlib.pyplot as plt # a subset of matplotlib with most of the useful tools
import IPython as IP
%matplotlib inline 
import pdb
#%qtconsole
Explanation: All import statements here.
End of explanation
# create an engine for interacting with the MySQL database
try:
    eng_str = 'mysql+mysqldb://' + username + ':' + password + '@localhost/' + db_name
    engine = create_engine(eng_str)
    connection = engine.connect()
    version = connection.execute("SELECT VERSION()")
    print("Database version : ")
    print(version.fetchone())
    
# report what went wrong if this fails.    
except sqlalchemy.exc.DatabaseError as e:
    reason = e.message
    print("Error %s:" % (reason))
    sys.exit(1)
# close the connection
finally:            
    if connection:    
        connection.close()
    else:
        print("Failed to create connection.")
Explanation: Try to connect to the tennis database on the local mysql host.  If successful,  print out the MySQL version number, if unsuccessful, exit gracefully.
End of explanation
# focus on most recent data; exclude Davis Cup stuff
startdate = '20100101'
enddate = '20161231'
with engine.begin() as connection:
    odds = pd.read_sql_query(SELECT * FROM odds \
                                WHERE DATE >= ' + startdate + ' \
                                AND DATE <= ' + enddate + ';, connection)
    
with engine.begin() as connection:
    matches = pd.read_sql_query(SELECT * FROM matches \
                                WHERE tourney_date >= ' + startdate + ' \
                                AND tourney_date <= ' + enddate + ' \
                                AND tourney_name NOT LIKE 'Davis%%';, connection)
    
# view results
IP.display.display(odds[['ATP','Location','Tournament','Date','Round',
                         'Winner','Loser']].sort_values('Date')[0:5])
IP.display.display(matches[['tourney_id','tourney_name','tourney_date','round',
                            'winner_name','loser_name']].sort_values('tourney_date')[0:5])
Explanation: Extract data from MySQL database
We'll focus on cleaning the seven years of data between 2010 and 2016.
End of explanation
odds[['Location','Winner','Loser']] = \
    odds[['Location','Winner','Loser']].\
    apply(lambda x: x.str.strip().str.lower().str.replace('-',' '),axis=1)
matches[['tourney_name','winner_name','loser_name']] = \
    matches[['tourney_name','winner_name','loser_name']].\
    apply(lambda x: x.str.strip().str.lower().str.replace('-',' '),axis=1)
Explanation: Note some issues already:
1.  Date in matches are often pegged to the start date of the tournament, while dates in odds are often tied to the match itself.  Thus time-ordering leads to different results.
2.  The variable tourney_name in matches often corresponds to the variable Location in odds (but not always!)
3.  Rounds are denoted in different ways.  "1st round" in odds can match to R32, R64, or R128 on matches.
4.  Name formats are different.
5.  While matches has a unique tournament ID for each tournament, odds recycles tournament ids each year.
Before doing any serious data processing, tidy up the strings (strip whitespace, convert to lowercase, replace dashes by space)
End of explanation
# matches tournament identifiers are unique
g_matches = matches.groupby('tourney_id')
# odds tournament identifiers are recycled every year
g_odds= odds.groupby(['ATP','fname'])
Explanation: Step I:  pair unique tournaments from each set
Each unique tournament has an identifying key in both odds and matches, but the keys are different, and not one to one.  Our first task is to associate unique tournaments with one another.  We'll start by grouping both odds and matches by unique tournament number.
End of explanation
def extract_odds_features(group):
    sizes = len(group)
    min_date = group['Date'].min()
    max_date = group['Date'].max()
    location = group['Location'].unique()[0]
    return pd.Series({'size': sizes,'min_date':min_date,\
                      'max_date':max_date,'location':location})
def extract_matches_features(group):
    sizes = len(group)
    min_date = group['tourney_date'].min()
    max_date = group['tourney_date'].max()
    tourney_name = group['tourney_name'].unique()[0]
    return pd.Series({'size': sizes,'min_date':min_date,\
                      'max_date':max_date,'tourney_name':tourney_name})
    
g_odds = g_odds.apply(extract_odds_features).reset_index()
g_matches = g_matches.apply(extract_matches_features).reset_index()
Explanation: For each tournament (group), extract features to assist in "tourney-matching", including the number of matches in the tournament, the maxium and minimum listed dates, the location (in odds) and the tournament name (in matches.)
End of explanation
tourney_lookup = pd.read_pickle(pickle_dir + 'tourney_lookup.pkl')
print("Snapshot of lookup table:")
IP.display.display(tourney_lookup.sort_values('o_name')[15:25])
Explanation: Define lookup-table connecting Location in odds to tourney_name in matches.  Establishing this matching is not totally straightforward:  in  many cases, the two columns are identical, but in others they are not, with no obvious connection. (Eg. "Roland Garros" is the tourney_name listed for the French Open in matches, while it's Location is "Paris".) Of the 100 or so difference tennis tournaments, about 60 have a direct correspondence, and the others need some massaging.  The derived lookup table is the product of considerable grubby data wrangling.
End of explanation
def get_tourney_ID(o_row):
    
    function:  get_tourney_ID(o_row)
    Input:   row from dataframe g_odds
    Output:  a Series object with two elements: 1) a match ID,
                and 2), a flag of True if the sizes of the two tournmanets are identical
    
    # calculate the diffence in start/stop dates between this tournament and those in `matches`.
    min_date_delta = np.abs(g_matches['min_date'] - o_row['min_date']).apply(lambda x: x.days)
    max_date_delta = np.abs(g_matches['max_date'] - o_row['max_date']).apply(lambda x: x.days)
    # find a list of candidate tournament names, based on lookup table 
    mtchs = (tourney_lookup['o_name']==o_row['location'])
    if sum(mtchs)>0:
        m_name = tourney_lookup.loc[mtchs,'m_name']
    else:
        print('no match found for record {}'.format(o_row['location']))
        return ['Nan','Nan']
    
    # the "right" tournament has the right name, and reasonable close start or stop dates
    idx = ((min_date_delta <=3) | (max_date_delta <=1)) & (g_matches['tourney_name'].isin(m_name))
    record = g_matches.loc[idx,'tourney_id']
    
    # if there are no matches, print some diagnostic information and don't assign a match
    if len(record)<1:
        print("Warning:  no match found for `odds` match {}, year {}".format(o_row.ATP, o_row.fname))
        print("min date delta: {}, max date delta: {}, g_matches: {}".format(np.min(min_date_delta), \
                                                                             np.min(max_date_delta), \
                                                                             g_matches.loc[g_matches['tourney_name'].isin(m_name),'tourney_name'])) 
        return pd.Series({'ID':'None','size':'NA'})
    
    # if there are too many matches, print a warning and don't assign a match.
    elif (len(record)>1):
        print("Warning:  multiple matches found for `odds` match {}".format(o_row.ATP))
        return pd.Series({'ID':'Multiple','size':'NA'})
    # otherwise, assign a match, and check if the sizes of the matches are consistent (a good double-check)
    else:
        size_flag = (g_matches.loc[idx,'size']==o_row['size'])
        return pd.Series({'ID':record.iloc[0],'size':size_flag.iloc[0]})
Explanation: Define a function that will take as input a set of features from one tournament in odds, and try to match it to one tournament in matches.  The algorithm make sure that dates are roughly the same, and that there is a correspondence between Location and tourney_name.
End of explanation
# add columns to g_odds to hold match ID and also info about size-correspondence
g_odds.insert(len(g_odds.columns),'ID','None')
g_odds.insert(len(g_odds.columns),'sizes_match','NA')
# perform the match
g_odds[['ID','sizes_match']] = g_odds.apply(get_tourney_ID,axis=1).values
Explanation: Perform the matches.
End of explanation
# add "size" columns to both dataframes
odds = pd.merge(g_odds[['ATP','fname','ID','size']],odds,how='inner',on=['ATP','fname'])
matches = pd.merge(g_matches[['tourney_id','size']],matches,how='inner',on=['tourney_id'])
# sum the sizes
if sum(g_odds['sizes_match']==True) != len(g_odds):
    print("Warning:  at least one tournament in `odds` is matched to a \
    tournament in `matches` of a different size.")
else:
    print("Sizes seem to match up.")
Explanation: Merge match numbers back into bigger odds table, checking that the sizes of each "matched" tournament are the same.
End of explanation
# for each tournament, label match numbers from 1 to n_tourneys
odds.insert(5,'match_num',0)
grouped = odds[['ID','match_num']].groupby('ID')
odds['match_num'] = grouped.transform(lambda x: 1+np.arange(len(x)))
# add keys to both odds and match data
odds.insert(len(odds.columns),'key',np.arange(len(odds)))
matches.insert(len(matches.columns),'key',np.arange(len(matches)))
Explanation: Step II:  Pair matches within each tournament.
To help in this process, we first insert an integer index key into both odds and matches.  Also assign a match number to each match within a tournament in odds.
End of explanation
# figure out how many discrete sizes there are
print("size in odds:  ", odds['size'].unique())
print("size in matches:  ", matches['size'].unique())
print("unique round designators in odds:  ", odds.Round.unique())
print("unique round designators in matches:  ", matches['round'].unique())
# create a lookup table to be able to match on rounds
m_rounds = ['R128','R64','R32','R16','QF','SF','F','RR']
o_rounds = ['1st Round','2nd Round','3rd Round','4th Round', \
            'Quarterfinals','Semifinals','The Final','Round Robin']
round_lookup_small = pd.DataFrame({'m_rounds': m_rounds[2:-1],\
                                   'o_rounds':o_rounds[0:2]+o_rounds[4:-1]})
round_lookup_medium = pd.DataFrame({'m_rounds': m_rounds[1:-1],\
                                   'o_rounds':o_rounds[0:3]+o_rounds[4:-1]})
round_lookup_large = pd.DataFrame({'m_rounds': m_rounds[0:-1],\
                                   'o_rounds':o_rounds[0:-1]})
round_lookup_RR  = pd.DataFrame({'m_rounds':m_rounds[5:],\
                                 'o_rounds':o_rounds[5:]})
Explanation: We'll use round information to help with the task of pairing individual matches .  To use round information effectively, we need to establish a correspondence between round signifiers in the two datasets.  The exact correspondence depends on how many tennis matches are in each tournmanet.
End of explanation
def map_rounds(x):
    cur_name = x['Round']
    t_size = x['size']
    if t_size in [27,31]:
        new_name = round_lookup_small.loc[round_lookup_small.o_rounds==cur_name,'m_rounds']
    elif t_size in [47,55]:
        new_name = round_lookup_medium.loc[round_lookup_medium.o_rounds==cur_name,'m_rounds']
    elif t_size in [95, 127]:
        new_name = round_lookup_large.loc[round_lookup_large.o_rounds==cur_name,'m_rounds']
    else:
        new_name = round_lookup_RR.loc[round_lookup_RR.o_rounds==cur_name,'m_rounds'] 
    return new_name.iloc[0]
Explanation: With the round lookup tables defind, define a function that takes a row of odds, and figures how to map its round information to the round information in matches.
End of explanation
# translate round indentifier appropriately
odds.insert(4,'round','TBD')
odds['round'] = odds.apply(map_rounds,axis=1).values
IP.display.display(odds[0:4])
IP.display.display(matches[0:4])
Explanation: We'll apply that mapping to each row of the odds dataframe.
End of explanation
t1=odds.ID.drop_duplicates().sort_values()
t2=matches.tourney_id.drop_duplicates().sort_values()
m_sizes=matches.loc[matches.tourney_id.isin(t1),['tourney_id','size']].drop_duplicates()
o_sizes=odds.loc[odds.ID.isin(t2),['ID','size']].drop_duplicates()
#comp = pd.merge(o_sizes,m_sizes,how='outer',left_on='ID',right_on='tourney_id')
print('sum of sizes of tournaments in odds: ', np.sum(o_sizes['size']))
print('sum of sizes of tournaments in matches: ', np.sum(m_sizes['size']))
Explanation: Before going on, a quick sanity check:  is the set of matches in matches that are in odds the same size as the set of matches in odds that are in matches?   Does the size column have the correct data?
End of explanation
matches = matches.loc[matches.tourney_id.isin(t1),:]
print("number of records in `odds`:  ", len(odds))
print("number of records in `matches`:  ", len(matches))
Explanation: Looks good.  Now pare down the matches dataframe to contain only records in odds.
End of explanation
# extract dataframe with player names split into discrete 'words'
m_players = pd.merge(matches.winner_name.str.split(pat=' ',expand=True), \
                     matches.loser_name.str.split(pat=' ',expand=True), \
                    how='inner',left_index=True, right_index=True,suffixes=('_W','_L'))  
# add on tournament, round, and match identifiers
m_players = pd.merge(matches[['tourney_id','match_num', 'round','key']], m_players,\
                    how='inner',left_index=True, right_index=True).sort_values(['tourney_id','round','1_W','1_L'])
# extract dataframe with player names split into discrete 'words'
o_players = pd.merge(odds.Winner.str.split(pat=' ',expand=True), \
                     odds.Loser.str.split(pat=' ',expand=True), \
                    how='inner',left_index=True, right_index=True,suffixes=('_W','_L')) 
# add on tournament and round identifiers
o_players = pd.merge(odds[['ID','round','match_num','key']], o_players,\
                    how='inner',left_index=True, right_index=True).sort_values(['ID','round','0_W','0_L'])
print("m_players: ")
IP.display.display(m_players[0:5])
print("o_players")
IP.display.display(o_players[0:5])
Explanation: To do player name matching, we split each name, and attempt to match on substrings.
End of explanation
# try for an exact match on last names of both winner and loser
A = pd.merge(m_players[['tourney_id','round','key','1_W','1_L']],\
             o_players[['ID','round','key','0_W','0_L']],how='inner',\
             left_on=['tourney_id','round','1_W','1_L'],\
             right_on=['ID','round','0_W','0_L'],suffixes=['_m','_o'])
m_extras = m_players.loc[~m_players.key.isin(A.key_m),:]
o_extras = o_players.loc[~o_players.key.isin(A.key_o),:]
print("A total of {} matches down.  {} remain.".format(len(A),len(m_extras)))
A.key_o.unique().size
Explanation: Match individual matches as follows: assuming there is a match on both tournament number and round, then
1.  First try to match both winner and loser names
2.  In the compliment of the result, try to match just winner name or just loser names.
3.  Merge 1. and 2., see what's left over.
End of explanation
IP.display.display(m_extras[0:10])
IP.display.display(o_extras[0:10])
Explanation: Take a quick peak at the remaining names to get a sense for what the issues are
End of explanation
def comp_str_lists(a,b):
    checks to see if any of the strings in list a are also in list b
    for i in a:
        if i in b:
            return True        
    return False
Explanation: Now match across all substrings in a name.  To do so, we need a function that will return True if there is a match between one or more strings in two string lists.
End of explanation
def comp_all_cols(o_row):
    
    input:  row of o_players
    output: 
    
    
    m_chunk = m_extras.loc[(m_extras.tourney_id==o_row['ID']) & (m_extras['round']==o_row['round'])]
    o_winner = list(o_row[['0_W','1_W','2_W','3_W','4_W']].dropna())
    o_loser = list(o_row[['0_L','1_L','2_L','3_L','4_L']].dropna())
    
    pairing = []
    if len(m_chunk)==0:
        print("warning:  no match/round pairing found for o_row key {}".format(o_row['key']))
        return 0
    
    for i, m_row in m_chunk.iterrows():
        
        m_winner = list(m_row[['0_W','1_W','2_W','3_W','4_W']].dropna())
        m_loser = list(m_row[['0_L','1_L','2_L','3_L','4_L']].dropna())
        
        pairing.append(comp_str_lists(o_winner,m_winner) & (comp_str_lists(o_loser,o_loser)))
    
    
    if sum(pairing) == 1:
        m_row = m_chunk.iloc[np.array(pairing),:]
        return pd.Series({'key_o':o_row['key'],'key_m':m_row['key'].iloc[0]})
    elif sum(pairing)<1:
        print("warning:  no name matches for o_row key {}".format(o_row['key']))
        return 0
    
    else:
        print("warning:  multiple name matches for o_row key {}".format(o_row['key']))
        return 0
new_matches = o_extras.apply(comp_all_cols,axis=1)
Explanation: We also need a function that will take each row of odds, and try to find a match for some appropriate subchunk of matches.
End of explanation
new_matches = new_matches.loc[(new_matches.key_m!=0)&(new_matches.key_o!=0),:]
A = pd.concat([A[['key_m','key_o']],new_matches])
m_extras = m_players.loc[~m_players.key.isin(A.key_m),:]
o_extras = o_players.loc[~o_players.key.isin(A.key_o),:]
print("A total of {} matches down.  {} remain.".format(len(A),len(m_extras)))
Explanation: Update match list, and check progress.
End of explanation
IP.display.display(m_extras.sort_values('0_W')[0:10])
IP.display.display(o_extras.sort_values('1_L')[0:10])
Explanation: Take a peak at the remaining names and see what the problems are.
End of explanation
B = pd.merge(m_extras[['tourney_id','key','1_W','1_L']],\
             o_extras[['ID','round','key','0_W','0_L']],how='inner',\
             left_on=['tourney_id','1_W','1_L'],\
             right_on=['ID','0_W','0_L'],suffixes=['_m','_o'])
A = pd.concat([A,B[['key_m','key_o']]])
m_extras = m_players.loc[~m_players.key.isin(A.key_m),:]
o_extras = o_players.loc[~o_players.key.isin(A.key_o),:]
print("A total of {} matches down.  {} remain.".format(len(A),len(m_extras)))
IP.display.display(m_extras[0:4])
IP.display.display(o_extras[0:4])
Explanation: Some rounds are wrong.  Try re-matching both winner and loser last names, without insisting on round information.
End of explanation
def comp_all_cols_no_rounds(o_row):
    
    input:  row of o_players
    output: 
    
    
    m_chunk = m_extras.loc[(m_extras.tourney_id==o_row['ID'])]
    o_winner = list(o_row[['0_W','1_W','2_W','3_W','4_W']].dropna())
    o_loser = list(o_row[['0_L','1_L','2_L','3_L','4_L']].dropna())
    
    pairing = []
    if len(m_chunk)==0:
        print("warning:  no match/round pairing found for o_row key {}".format(o_row['key']))
        return 0
    
    for i, m_row in m_chunk.iterrows():
        
        m_winner = list(m_row[['0_W','1_W','2_W','3_W','4_W']].dropna())
        m_loser = list(m_row[['0_L','1_L','2_L','3_L','4_L']].dropna())
        
        pairing.append(comp_str_lists(o_winner,m_winner) & (comp_str_lists(o_loser,m_loser)))
    
    
    if sum(pairing) == 1:
        m_row = m_chunk.iloc[np.array(pairing),:]
        return pd.Series({'key_o':o_row['key'],'key_m':m_row['key'].iloc[0]})
    elif sum(pairing)<1:
        print("warning:  no name matches for o_row key {}".format(o_row['key']))
        return pd.Series({'key_o':0,'key_m':0})
    else:
        print("warning:  multiple name matches for o_row key {}".format(o_row['key']))
        print(m_chunk.iloc[np.array(pairing),:])
        return pd.Series({'key_o':0,'key_m':0})
new_matches = o_extras.apply(comp_all_cols_no_rounds,axis=1)
new_matches = new_matches.loc[(new_matches.key_m!=0)&(new_matches.key_o!=0),:]
A = pd.concat([A[['key_m','key_o']],new_matches])
Explanation: That solved some.  Now try matching unusual names, ignorning rounds. This involves slightly modifying the comparison function.
End of explanation
m_extras = m_players.loc[~m_players.key.isin(A.key_m),:]
o_extras = o_players.loc[~o_players.key.isin(A.key_o),:]
print("A total of {} matches down.  {} remain.".format(len(A),len(m_extras)))
IP.display.display(m_extras)
IP.display.display(o_extras)
Explanation: Still some errors.  Let's take a look.
End of explanation
# solve the delbonis problem
o_extras.loc[o_extras['1_W']=='bonis',('0_W','1_W')]  = ['delbonis',None]
B = pd.merge(m_extras[['tourney_id','key','1_W','1_L']],\
             o_extras[['ID','round','key','0_W','0_L']],how='inner',\
             left_on=['tourney_id','1_W','1_L'],\
             right_on=['ID','0_W','0_L'],suffixes=['_m','_o'])
A = pd.concat([A,B[['key_m','key_o']]])
m_extras = m_players.loc[~m_players.key.isin(A.key_m),:]
o_extras = o_players.loc[~o_players.key.isin(A.key_o),:]
print("A total of {} matches down.  {} remain.".format(len(A),len(m_extras)))
IP.display.display(m_extras)
IP.display.display(o_extras)
Explanation: Two big problems.  One is 'delbonis' vs. 'del bonis'.
End of explanation
m_extras = m_extras.sort_values(['tourney_id','1_W'])
o_extras = o_extras.sort_values(['ID','0_L'])
dregs=pd.DataFrame(list(zip(m_extras['key'].values, \
                            o_extras['key'].values)),\
                        columns=['key_m','key_o'])
A = pd.concat([A,dregs[['key_m','key_o']]])
Explanation: What remains is exclusively a mismatch of winner and loser. First sort and match keys, then check who really won and correct the data.
End of explanation
def find_winner(o_row):
    ID = o_row['ID']
    # nominal winner in `odds`
    name1 = o_row['0_W']
    # nominal loser in `odds`
    name2 = o_row['0_L']
    # number of rounds played by nominal winner
    rnds1 = len(odds.loc[(odds.ID==ID) & \
                         (odds.Winner.str.contains(name1) | \
                          odds.Loser.str.contains(name1)),:])
    # number of rounds played by nominal loser
    rnds2 = len(odds.loc[(odds.ID==ID) & \
                         (odds.Winner.str.contains(name2) | \
                          odds.Loser.str.contains(name2)),:])
    # if nominal winner played more rounds, `odds` is right and `matches` is wrong
    if rnds1>rnds2:
        print('Winner:  ', name1)
        return 'm'
    # otherwise, `odds` is wrong and `matches` is right.
    elif rnds1<rnds2:
        print('Winner:  ', name2)
        return 'o'
    else:
        print("function find_winner:  ambigous outcome")
        return np.nan
mistake_idx = o_extras.apply(find_winner,axis=1)
Explanation: To see who really won, calculate who played the most rounds.
End of explanation
# fix messed up `odds` records
o_errs = o_extras.loc[mistake_idx.values=='o',:]
if len(o_errs)!=0:
    temp = odds.loc[odds.key.isin(o_errs['key']),'Winner']
    odds.loc[odds.key.isin(o_errs['key']),'Winner']=\
        odds.loc[odds.key.isin(o_errs['key']),'Loser'].values
    odds.loc[odds.key.isin(o_errs['key']),'Loser']=temp.values
# fix messed up `matches` records
m_errs = m_extras.loc[mistake_idx.values=='m',:]
if len(m_errs)!=0:
    temp = matches.loc[matches.key.isin(o_errs['key']),'winner_name']
    matches.loc[matches.key.isin(o_errs['key']),'winner_name']=\
        matches.loc[matches.key.isin(o_errs['key']),'loser_name'].values
    matches.loc[matches.key.isin(o_errs['key']),'loser_name']=temp.values
#sanity check
print("odds has {} records".format(len(odds)))
print("our lookup table is of size {}".format(len(A)))
print("the table has {} unique keys for `matches`".format(len(A.key_m.unique())))
print("the table has {} unique keys for `odds`".format(len(A.key_o.unique())))
Explanation: Correct mistakes any mistakes in either table.
End of explanation
# take the key originally assigned to `matches` to be the main key
A.rename(columns = {'key_m':'key'},inplace=True)
# change name of `odds` key to match that in `A`
odds.rename(columns = {'key':'key_o'},inplace=True)
# add `matches` key to `odds`, and get rid of `odds` key
odds = pd.merge(odds,A,how='inner',on='key_o')
del odds['key_o']
# use the `odds` match numbers on `matches`
matches = matches.rename(columns={'match_num':'match_num_old'})
matches = pd.merge(matches,odds[['match_num','key']],how='inner',on='key')
Explanation: Now we can assign a single key for both odds and matches which corresponds on the match level.  We can also standardize match numbers within tournaments.
End of explanation
matches.to_pickle(pickle_dir + 'matches.pkl')
odds.to_pickle(pickle_dir + 'odds.pkl')
Explanation: Finally, we save our cleansed and matched datasets.
End of explanation
<END_TASK>