%matplotlib inline
import matplotlib as plt
import pandas as pd
import numpy as np
import seaborn as sns
import scipy as spy
from sklearn.model_selection import train_test_split

conf = pd.read_csv("TeamConferences.csv")
teams = pd.read_csv("Teams.csv")
coaches = pd.read_csv("TeamCoaches.csv")

allTeams = coaches.merge(teams[["TeamID", "TeamName"]], on="TeamID").merge(conf, on=["Season", "TeamID"])
allTeams.drop(["FirstDayNum","LastDayNum"], axis=1, inplace=True)
allTeams.drop_duplicates(inplace=True)
allTeams.head(25)

results = pd.read_csv("NCAATourneyCompactResults.csv")
seeds = pd.read_csv("NCAATourneySeeds.csv")

# convert and remove string characters from seed values
# borrowed from the Kaggle NCAA 2018 Men's competition tutorial notebook.
def seed_to_int(seed):
    #Get just the digits from the seeding. Return as int
    s_int = int(seed[1:3])
    return s_int

seeds['seed_int'] = seeds.Seed.apply(seed_to_int)
seeds.drop(labels=['Seed'], inplace=True, axis=1) # This is the string label

seedResults =  results.merge(seeds, right_on=["TeamID","Season"], left_on= ["WTeamID","Season"]).merge(seeds, right_on=["TeamID","Season"], left_on= ["LTeamID","Season"])
seedResults.drop(["TeamID_x", "TeamID_y"],axis = 1, inplace=True)
seedResults.rename(index=str, columns={"seed_int_x":"WSeed", "seed_int_y":"LSeed"}, inplace=True)
seedResults.head(10)

allResults =  seedResults.merge(allTeams, right_on=["TeamID","Season"], left_on= ["WTeamID","Season"]).merge(allTeams, right_on=["TeamID","Season"], left_on= ["LTeamID","Season"])

def xyToWL(colNames):
    """
    Convert suffix _x and _y to prefix W and L respectively.

    Parameters
    ----------
    colNames : list
        List of column names to convert

    Returns
    -------
    list
        New column names converted to WColumnName from ColumnName_x

    """
    newCols = []
    for col in colNames:
        if col.endswith("_x"):
            newCol = col.replace("_x", "")
            newCol = "W" + newCol
            newCols.append(newCol)
        elif col.endswith("_y"):
            newCol = col.replace("_y", "")
            newCol = "L" + newCol
            newCols.append(newCol)
        else:
            newCols.append(col)
    return newCols

# drop repeated w team id and l team id
allResults.drop(["TeamID_x", "TeamID_y"], axis=1, inplace=True)
allResults.drop_duplicates(inplace=True)

# Convert suffixed columns
allResults.columns = xyToWL(allResults.columns)
allResults = allResults[~((allResults.WSeed ==16) & (allResults.LSeed == 16))]
allResults.sort_values(["Season","DayNum"], inplace=True)
allResults.head(15)

bySeed = mergeWL(allResults, "Seed")
# EWins is yearly expected wins per team by their seed. --> totalWins / 4 * number of seasons   (4 due to 4 different seeds every year)
bySeed["EWins"] = bySeed.totalWins/(4*seedResults.Season.nunique())
bySeed = bySeed.round(decimals=3)
bySeed.head(16)

# helper function mergeWL
def mergeWL(resDF, onCol):
    '''
    Group resDF by onCol and count totalWins, total Losses and WPct

    Parameters
    ----------
    resDF : pandas.dataframe
        Dataframe of results to filter and group wins and losses

    onCol: str
        String of column to group by

    Returns
    -------
    pandas.dataframe
        New dataframe with wins, losses, and WPct calculated.

    '''
    wins = resDF.groupby("W"+onCol, as_index=False)["WTeamID"].agg(['count']).rename(columns={'count': 'totalWins'})
    loss = resDF.groupby("L"+onCol, as_index=False)["LTeamID"].agg(['count']).rename(columns={'count': 'totalLosses'})
    comb = wins.merge(loss, how="right", left_index = True, right_index = True)
    comb.reset_index(inplace=True)
    comb.rename(columns={comb.columns[0]:onCol}, inplace=True)
    comb["WPct"] = comb.totalWins/(comb.totalWins + comb.totalLosses)
    comb.fillna(0, inplace=True)
    return comb

byCoach = mergeWL(allResults, "CoachName")
byCoach.head(10)

avgSeed = byCoachSeed.groupby("CoachName", as_index=False).agg({'Seed': "mean", 'Season': 'count'})
avgSeed.rename(columns={"Seed":"AvgSeed", "Season":"Appearances"}, inplace=True)
byCoach = byCoach.merge(avgSeed,on="CoachName").round(3)
byCoach["totalWins"] = byCoach["totalWins"].astype(int)
byCoach.head(10)

winCoachSeed = allResults.groupby(["WCoachName", "Season"], as_index=False)["WSeed"].mean()
lossCoachSeed = allResults.groupby(["LCoachName", "Season"], as_index=False)["LSeed"].mean()
winCoachSeed.rename(columns={"WCoachName":"CoachName","WSeed":"Seed"}, inplace=True)
lossCoachSeed.rename(columns={"LCoachName":"CoachName","LSeed":"Seed"}, inplace=True)
byCoachSeed = winCoachSeed.append(lossCoachSeed,ignore_index=True).drop_duplicates().sort_values(["CoachName", "Season"])
byCoachSeed.reset_index(drop=True, inplace=True)

coachWins = allResults.groupby(["WCoachName", "Season"], as_index=False)["WSeed"].count().rename(columns={"WSeed":"TourneyWins","WCoachName":"CoachName"})
byCoachSeed = byCoachSeed.merge(coachWins, how="left",on=["CoachName", "Season"]).fillna(0)
byCoachSeed["TourneyWins"] = byCoachSeed["TourneyWins"].astype(int)

byCoachSeed = byCoachSeed.merge(bySeed[["Seed", "EWins"]], how="left", on="Seed").fillna(0)
byCoachSeed["Diff"] = byCoachSeed.TourneyWins - byCoachSeed.EWins
byCoachSeed.head(15)

coachTotalEWins = byCoachSeed.groupby("CoachName", as_index=False)["Diff"].mean()
byCoach = byCoach.merge(coachTotalEWins, how="left", on="CoachName").rename(columns={"Diff":"AvgWinsAdded"})
byCoach.sort_values("AvgWinsAdded",ascending=False, inplace=True)
byCoach.reset_index(drop=True, inplace=True)
byCoach.head(15)

byCoach3App = byCoach[byCoach["Appearances"] >= 3].reset_index(drop=True)
byCoach3App.head(50)

byCoach3App.tail(50)