{"cells":[{"metadata":{},"cell_type":"markdown","source":"I built this notebook to see how the 538 probabilities perform. Based on my review, they don't appear to perform very well (0.68 log loss in round 1).\n\nI pulled their predictions at the end of the play-in round to avoid any data leakage. \n\n* For example, see: https://projects.fivethirtyeight.com/2019-march-madness-predictions/ click table, select \"Forecast from: play-in\""},{"metadata":{},"cell_type":"markdown","source":"**Load packages**"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Load data**"},{"metadata":{"trusted":true},"cell_type":"code","source":"tourney_df = pd.read_csv('../input/google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1/MNCAATourneyCompactResults.csv', sep='\\,', engine='python')\ntourney_df = tourney_df[['Season','DayNum','WTeamID','WScore','LTeamID','LScore']]\ntourney_df = tourney_df[tourney_df['Season']>2015]\n\ntourney_df1 = tourney_df.copy()\ntourney_df2 = tourney_df.copy()\n\ntourney_df1.columns = ['Season','DayNum','T1_Team_ID','T1_Score','T2_Team_ID','T2_Score']\ntourney_df1['T1_Won'] = 1\n\ntourney_df2.columns = ['Season','DayNum','T2_Team_ID','T2_Score','T1_Team_ID','T1_Score']\ntourney_df2['T1_Won'] = 0\n\ntourney_all = tourney_df1.append(tourney_df2, sort=False)\n\ntourney_all['Rd'] = 1\ntourney_all.loc[(tourney_all['DayNum']==138) | (tourney_all['DayNum']==139), 'Rd'] = 2\ntourney_all.loc[(tourney_all['DayNum']==143) | (tourney_all['DayNum']==144), 'Rd'] = 3\ntourney_all.loc[(tourney_all['DayNum']==145) | (tourney_all['DayNum']==146), 'Rd'] = 4\ntourney_all.loc[tourney_all['DayNum']==152, 'Rd'] = 5\ntourney_all.loc[tourney_all['DayNum']==154, 'Rd'] = 6\ntourney_all","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Note: 538 calculates probabilities of reaching a given round. In the excel workbook we load in, I divide by the probability from the prior round to estimate the current round's probability**\n\nFor example, if Team A has a 50% probability of making round 2 and a 10% probability of reaching round 3, the probability in round 1 is 50% and the probability in round 2 is 20% (10%/50% = 20%)"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_538 = pd.read_excel(open('../input/538-data/538.xlsx', 'rb'),sheet_name='All')  \ndf_538 = df_538[['Season','TEAM','POWER RATING','Round1','Round2','Round3','Round4','Round5','Round6']]\n\n# string cleaning to attach team ids\ndf_538['TEAM'] = df_538['TEAM'].str.lower()\ndf_538['TEAM'] = df_538['TEAM'].str.extract('([a-z. ]+)', expand=True) \n\ndf_538.loc[df_538['TEAM']==\"miss. state\",\"TEAM\"] = \"mississippi st\"\ndf_538.loc[df_538['TEAM']==\"st. mary\",\"TEAM\"] = \"saint mary's\"\ndf_538.loc[df_538['TEAM']==\"nm state\",\"TEAM\"] = \"new mexico st\"\ndf_538.loc[df_538['TEAM']==\"neastern\",\"TEAM\"] = \"northeastern\"\ndf_538.loc[df_538['TEAM']==\"uc\",\"TEAM\"] = \"uc irvine\"\ndf_538.loc[df_538['TEAM']==\"n. kentucky\",\"TEAM\"] = \"northern kentucky\"\ndf_538.loc[df_538['TEAM']==\"st. louis\",\"TEAM\"] = \"saint louis\"\ndf_538.loc[df_538['TEAM']==\"old dom.\",\"TEAM\"] = \"old dominion\"\ndf_538.loc[df_538['TEAM']==\"gardner\",\"TEAM\"] = \"gardner webb\"\ndf_538.loc[df_538['TEAM']==\"abilene chr.\",\"TEAM\"] = \"abilene chr\"\ndf_538.loc[df_538['TEAM']==\"nd state\",\"TEAM\"] = \"north dakota state\"\ndf_538.loc[df_538['TEAM']==\"f. dickinson\",\"TEAM\"] = \"f dickinson\"\ndf_538.loc[df_538['TEAM']==\"w. virginia\",\"TEAM\"] = \"west virginia\"\ndf_538.loc[df_538['TEAM']==\"miami\",\"TEAM\"] = \"miami fl\"\ndf_538.loc[df_538['TEAM']==\"texas a\",\"TEAM\"] = \"texas a&m\"\ndf_538.loc[df_538['TEAM']==\"sdsu\",\"TEAM\"] = \"san diego st\"\ndf_538.loc[df_538['TEAM']==\"st. bon.\",\"TEAM\"] = \"st bonaventure\"\ndf_538.loc[df_538['TEAM']==\"loyola \",\"TEAM\"] = \"loyola chicago\"\ndf_538.loc[df_538['TEAM']==\"s. dakota st.\",\"TEAM\"] = \"s dakota st\"\ndf_538.loc[df_538['TEAM']==\"csu\",\"TEAM\"] = \"csu bakersfield\"\ndf_538.loc[df_538['TEAM']==\"txso\",\"TEAM\"] = \"texas southern\"\ndf_538.loc[df_538['TEAM']==\"okla. state\",\"TEAM\"] = \"oklahoma state\"\ndf_538.loc[df_538['TEAM']==\"s. carolina\",\"TEAM\"] = \"south carolina\"\ndf_538.loc[df_538['TEAM']==\"mid. tenn.\",\"TEAM\"] = \"middle tennessee\"\ndf_538.loc[df_538['TEAM']==\"nwestern\",\"TEAM\"] = \"northwestern\"\ndf_538.loc[df_538['TEAM']==\"e. tenn. st.\",\"TEAM\"] = \"east tennessee state\"\ndf_538.loc[df_538['TEAM']==\"n. dakota\",\"TEAM\"] = \"north dakota\"\ndf_538.loc[df_538['TEAM']==\"jax. state\",\"TEAM\"] = \"jacksonville state\"\ndf_538.loc[df_538['TEAM']==\"m. st. mary\",\"TEAM\"] = \"mount st. mary's\"\ndf_538.loc[df_538['TEAM']==\"st. joseph\",\"TEAM\"] = \"st joseph's\"\ndf_538.loc[df_538['TEAM']==\"n. iowa\",\"TEAM\"] = \"northern iowa\"\ndf_538.loc[df_538['TEAM']==\"ark.\",\"TEAM\"] = \"arkansas\"\ndf_538.loc[df_538['TEAM']==\"chatt.\",\"TEAM\"] = \"chattanooga\"\ndf_538.loc[df_538['TEAM']==\"csu bakers.\",\"TEAM\"] = \"csu bakersfield\"\n\ndf_538.head(25)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Match IDs to 538 data**"},{"metadata":{"trusted":true},"cell_type":"code","source":"teams_df = pd.read_csv('../input/google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1/MTeamSpellings.csv', sep='\\,', engine='python')\nteams_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_538 = pd.merge(df_538, teams_df, left_on=['TEAM'], right_on = ['TeamNameSpelling'], how='left')\ndf_538 = df_538.drop(['TeamNameSpelling'], axis=1)\ndf_538","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Make data head to head**"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_538_long = df_538.melt(id_vars=['Season','TEAM','TeamID','POWER RATING'], var_name=\"Rd\", value_name=\"Prob538\")\ndf_538_long['Rd'] = df_538_long['Rd'].str.extract('(\\d)', expand=True)\ndf_538_long","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_538_probs = df_538_long[['Season','TeamID','Rd','Prob538','POWER RATING']]\ndf_538_probs['TeamID'] = df_538_probs['TeamID'].astype(int)\ndf_538_probs['Rd'] = df_538_probs['Rd'].astype(int)\ndf_538_probs","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"tourney_all_merged = pd.merge(tourney_all, df_538_probs, left_on=['Season','T1_Team_ID','Rd'], right_on = ['Season','TeamID','Rd'], how='left')\ntourney_all_merged.rename(columns={'Prob538': 'T1_Prob538'}, inplace=True)\n\ntourney_all_merged = pd.merge(tourney_all_merged, df_538_probs, left_on=['Season','T2_Team_ID','Rd'], right_on = ['Season','TeamID','Rd'], how='left')\ntourney_all_merged.rename(columns={'Prob538': 'T2_Prob538'}, inplace=True)\n\ntourney_all_merged = tourney_all_merged[['Season','Rd','T1_Won','T1_Team_ID','T2_Team_ID','T1_Prob538','T2_Prob538']]\n\ntourney_all_merged","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# fix missings (investigate these further)\ntourney_all_merged.loc[tourney_all_merged['T1_Prob538'].isna(), 'T1_Prob538'] = 1-tourney_all_merged['T2_Prob538']\ntourney_all_merged.loc[tourney_all_merged['T2_Prob538'].isna(), 'T2_Prob538'] = 1-tourney_all_merged['T1_Prob538']\ntourney_all_merged","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# average head-to-head\ntourney_all_merged['Prob538'] = (tourney_all_merged['T1_Prob538']+(1-tourney_all_merged['T2_Prob538']))/2\ntourney_all_merged ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Evaluate 538 probs**"},{"metadata":{"trusted":true},"cell_type":"code","source":"def LogLoss(predictions, realizations):\n    predictions_use = predictions.clip(0)\n    realizations_use = realizations.clip(0)\n    LogLoss = -np.mean( (realizations_use * np.log(predictions_use)) + \n                        (1 - realizations_use) * np.log(1 - predictions_use) )\n    return LogLoss","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# all rounds\n\nlosses = 0\nseasons = [2016,2017,2018,2019]\nfor season in seasons:\n    temp = tourney_all_merged[tourney_all_merged['Season']==season]\n    loss = LogLoss(temp['Prob538'], temp['T1_Won'])\n\n    print(\"Season\",season,\"valid:\",loss)\n    losses = losses+loss\n    \nlosses_avg = losses/4\nprint(\"Average :\",losses_avg)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# just round 1\n\nlosses = 0\nseasons = [2016,2017,2018,2019]\nfor season in seasons:\n    temp = tourney_all_merged[tourney_all_merged['Season']==season]\n    temp = temp[temp['Rd']==1]\n    loss = LogLoss(temp['Prob538'], temp['T1_Won'])\n\n    print(\"Season\",season,\"valid:\",loss)\n    losses = losses+loss\n    \nlosses_avg = losses/4\nprint(\"Average :\",losses_avg)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}