text_html_output_1.png
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

def cleanResults(raceColumns,dfResultsTemp,appendScore):
    for raceCol in raceColumns:
        
        dfResultsTemp.index = dfResultsTemp.index.str.replace(r"(\w)([A-Z])", r"\1 \2")
        dfResultsTemp.index = dfResultsTemp.index.str.title()
        dfResultsTemp.index = dfResultsTemp.index.str.replace('\([A-Z\ 0-9]*\)','')
        dfResultsTemp.index = dfResultsTemp.index.str.strip()
        dfResultsTemp.index = dfResultsTemp.index.str.replace('Riccardo Andrea Leccese','Rikki Leccese')
        
        dfResultsTemp[raceCol] = dfResultsTemp[raceCol].astype(str)
        dfResultsTemp[raceCol] = dfResultsTemp[raceCol].str.replace('^DNF$',str(len(dfResults)+1))
        dfResultsTemp[raceCol] = dfResultsTemp[raceCol].str.replace('\(|\)|UFD|DNF|RET|SCP|RDG|RCT|DCT|DNS-[0-9]*|DNC-[0-9]*|OCS-[0-9]*|[0-9\.]*DNC|-|\/','')
        dfResultsTemp[raceCol] = pd.to_numeric(dfResultsTemp[raceCol])  
        dfResultsTemp[raceCol] = dfResultsTemp[raceCol] + appendScore
        
    return dfResultsTemp
    

def mergeResults(raceColumns, raceName, dfResultsTemp, dfResults):
    for raceCol in raceColumns:
        raceIndex = raceName + '-' + raceCol
        dfResultsTemp[raceIndex] = dfResultsTemp[raceCol]
        del dfResultsTemp[raceCol]
        dfResults = pd.merge(dfResults, dfResultsTemp[[raceIndex]], left_index=True, right_index=True, how='outer')
    return dfResults

dfResults = pd.DataFrame()

raceName = '20160323-LaVentana-HydrofoilProTour'
raceColumns = ['Q2', 'R1', 'R2', 'R3', 'R4', 'R5', 'R6']
dfResultsTempGold = pd.read_csv('../input/' + raceName + '-Gold.csv')
dfResultsTempGold = dfResultsTempGold.set_index(dfResultsTempGold['Name'] + ' ' + dfResultsTempGold['LastName'])
dfResultsTempGold = cleanResults(raceColumns, dfResultsTempGold, 0)
dfResultsTempSilver = pd.read_csv('../input/' + raceName + '-Silver.csv')
dfResultsTempSilver = dfResultsTempSilver.set_index(dfResultsTempSilver['Name'] + ' ' + dfResultsTempSilver['LastName'])
dfResultsTempSilver = cleanResults(raceColumns, dfResultsTempSilver, len(dfResultsTempGold))
dfResultsTemp = dfResultsTempGold.append(dfResultsTempSilver)
dfResults = mergeResults(raceColumns, raceName, dfResultsTemp, dfResults)

raceName = '20160717-Gizzeria-IKAGoldCup'
raceColumns = ['CF 2', 'F 1', 'F 2', 'F 3', 'F 4', 'F 5', 'F 6', 'F 7', 'F 8', 'F 9', 'F 10']
dfResultsTempGold = pd.read_csv('../input/' + raceName + '-Gold.csv')
dfResultsTempGold = dfResultsTempGold.set_index(dfResultsTempGold['Name'])
dfResultsTempGold = cleanResults(raceColumns, dfResultsTempGold, 0)
raceColumns = ['CF 2', 'F 1', 'F 2', 'F 3', 'F 4', 'F 5', 'F 6', 'F 8']
dfResultsTempSilver = pd.read_csv('../input/' + raceName + '-Silver.csv')
dfResultsTempSilver = dfResultsTempSilver.set_index(dfResultsTempSilver['Name'])
dfResultsTempSilver = cleanResults(raceColumns, dfResultsTempSilver, len(dfResultsTempGold))
raceColumns = ['CF 2', 'F 1', 'F 2', 'F 3', 'F 4', 'F 5', 'F 6']
dfResultsTemp = dfResultsTempGold.append(dfResultsTempSilver)
dfResultsTempBronze = pd.read_csv('../input/' + raceName + '-Bronze.csv', encoding='ISO-8859-1')
dfResultsTempBronze = dfResultsTempBronze.set_index(dfResultsTempBronze['Name'])
dfResultsTempBronze = cleanResults(raceColumns, dfResultsTempBronze, len(dfResultsTemp))
dfResultsTemp = dfResultsTemp.append(dfResultsTempBronze)
dfResults = mergeResults(raceColumns, raceName, dfResultsTemp, dfResults)

raceName = '20160807-SanFracisco-HydrofoilProTour'
dfResultsTemp = pd.read_csv('../input/' + raceName + '.csv')
dfResultsTemp = dfResultsTemp.set_index(dfResultsTemp['Name'])
raceColumns = ['R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', 'R10', 'R11', 'R12', 'R13', 'R14', 'R15', 'R16']
dfResultsTemp = cleanResults(raceColumns, dfResultsTemp, 0)
dfResults = mergeResults(raceColumns, raceName, dfResultsTemp, dfResults)