Baseline Model.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# NCAA  - Baseline Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import glob\n",
    "import numpy as np\n",
    "pd.options.display.max_rows = 200\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\Cities.csv\n",
      "1 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\Conferences.csv\n",
      "2 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MConferenceTourneyGames.csv\n",
      "3 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MGameCities.csv\n",
      "4 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MMasseyOrdinals.csv\n",
      "5 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MNCAATourneyCompactResults.csv\n",
      "6 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MNCAATourneyDetailedResults.csv\n",
      "7 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MNCAATourneySeedRoundSlots.csv\n",
      "8 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MNCAATourneySeeds.csv\n",
      "9 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MNCAATourneySlots.csv\n",
      "10 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MRegularSeasonCompactResults.csv\n",
      "11 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MRegularSeasonDetailedResults.csv\n",
      "12 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MSeasons.csv\n",
      "13 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MSecondaryTourneyCompactResults.csv\n",
      "14 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MSecondaryTourneyTeams.csv\n",
      "15 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MTeamCoaches.csv\n",
      "16 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MTeamConferences.csv\n",
      "17 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MTeams.csv\n",
      "18 --> google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1\\MTeamSpellings.csv\n"
     ]
    }
   ],
   "source": [
    "files = glob.glob('google-cloud-ncaa-march-madness-2020-division-1-mens-tournament/MDataFiles_Stage1/*')\n",
    "[print(i,'-->',f) for i,f in enumerate(files)];"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Season Stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def loc_func(x):\n",
    "    if x['WLoc'] == 'N':\n",
    "        return 'N'\n",
    "    elif x['WLoc'] == 'H' and x['result'] == 1:\n",
    "        return 'H'\n",
    "    elif x['WLoc'] == 'A' and x['result'] == 1:\n",
    "        return 'A'\n",
    "    elif x['WLoc'] == 'H' and x['result'] == 0:\n",
    "        return 'A'\n",
    "    elif x['WLoc'] == 'A' and x['result'] == 0:\n",
    "        return 'H'\n",
    "    else:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Season</th>\n",
       "      <th>TeamID</th>\n",
       "      <th>Score</th>\n",
       "      <th>Score_OPP</th>\n",
       "      <th>WinPCT</th>\n",
       "      <th>ScoreDiff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1985</td>\n",
       "      <td>1102</td>\n",
       "      <td>63.083333</td>\n",
       "      <td>68.875000</td>\n",
       "      <td>0.208333</td>\n",
       "      <td>-5.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1985</td>\n",
       "      <td>1103</td>\n",
       "      <td>61.043478</td>\n",
       "      <td>64.086957</td>\n",
       "      <td>0.391304</td>\n",
       "      <td>-3.043478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1985</td>\n",
       "      <td>1104</td>\n",
       "      <td>68.500000</td>\n",
       "      <td>60.700000</td>\n",
       "      <td>0.700000</td>\n",
       "      <td>7.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1985</td>\n",
       "      <td>1106</td>\n",
       "      <td>71.625000</td>\n",
       "      <td>75.416667</td>\n",
       "      <td>0.416667</td>\n",
       "      <td>-3.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1985</td>\n",
       "      <td>1108</td>\n",
       "      <td>83.000000</td>\n",
       "      <td>75.040000</td>\n",
       "      <td>0.760000</td>\n",
       "      <td>7.960000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>1985</td>\n",
       "      <td>1109</td>\n",
       "      <td>53.833333</td>\n",
       "      <td>82.958333</td>\n",
       "      <td>0.041667</td>\n",
       "      <td>-29.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>1985</td>\n",
       "      <td>1110</td>\n",
       "      <td>69.440000</td>\n",
       "      <td>77.200000</td>\n",
       "      <td>0.280000</td>\n",
       "      <td>-7.760000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>1985</td>\n",
       "      <td>1111</td>\n",
       "      <td>67.125000</td>\n",
       "      <td>69.916667</td>\n",
       "      <td>0.416667</td>\n",
       "      <td>-2.791667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>1985</td>\n",
       "      <td>1112</td>\n",
       "      <td>66.518519</td>\n",
       "      <td>59.333333</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>7.185185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>1985</td>\n",
       "      <td>1113</td>\n",
       "      <td>67.703704</td>\n",
       "      <td>71.777778</td>\n",
       "      <td>0.407407</td>\n",
       "      <td>-4.074074</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Season  TeamID      Score  Score_OPP    WinPCT  ScoreDiff\n",
       "0    1985    1102  63.083333  68.875000  0.208333  -5.791667\n",
       "1    1985    1103  61.043478  64.086957  0.391304  -3.043478\n",
       "2    1985    1104  68.500000  60.700000  0.700000   7.800000\n",
       "3    1985    1106  71.625000  75.416667  0.416667  -3.791667\n",
       "4    1985    1108  83.000000  75.040000  0.760000   7.960000\n",
       "5    1985    1109  53.833333  82.958333  0.041667 -29.125000\n",
       "6    1985    1110  69.440000  77.200000  0.280000  -7.760000\n",
       "7    1985    1111  67.125000  69.916667  0.416667  -2.791667\n",
       "8    1985    1112  66.518519  59.333333  0.666667   7.185185\n",
       "9    1985    1113  67.703704  71.777778  0.407407  -4.074074"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SeasonResults = pd.read_csv(files[10])\n",
    "SeasonResults['GameID'] = np.arange(1,len(SeasonResults)+1)\n",
    "SeasonResultsWin = SeasonResults[['Season','DayNum','GameID','WTeamID','WScore','LTeamID','LScore','WLoc']].copy()\n",
    "SeasonResultsWin.columns = ['Season','DayNum','GameID','TeamID','Score','TeamID_OPP','Score_OPP','WLoc']\n",
    "SeasonResultsWin['result'] = 1\n",
    "SeasonResultsLose = SeasonResults[['Season','DayNum','GameID','WTeamID','WScore','LTeamID','LScore','WLoc']].copy()\n",
    "SeasonResultsLose.columns = ['Season','DayNum','GameID','TeamID_OPP','Score_OPP','TeamID','Score','WLoc']\n",
    "SeasonResultsLose['result'] = 0\n",
    "SeasonResultsTeams = pd.concat([SeasonResultsWin,SeasonResultsLose],axis=0,sort=True).sort_values('GameID')\n",
    "SeasonResultsTeams['Loc'] = SeasonResultsTeams.apply(loc_func,axis=1)\n",
    "\n",
    "seasonAve = (SeasonResultsTeams\n",
    "             .groupby(['Season','TeamID'])[['Score','Score_OPP','result']]\n",
    "             .mean()\n",
    "             .rename({'result':'WinPCT'},axis=1)\n",
    "             .reset_index()\n",
    "            )\n",
    "seasonAve['ScoreDiff'] = seasonAve['Score'] - seasonAve['Score_OPP']\n",
    "\n",
    "seasonAve.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rankings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>SystemName</th>\n",
       "      <th>Season</th>\n",
       "      <th>TeamID</th>\n",
       "      <th>POM</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2003</td>\n",
       "      <td>1102</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2003</td>\n",
       "      <td>1103</td>\n",
       "      <td>163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2003</td>\n",
       "      <td>1104</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2003</td>\n",
       "      <td>1105</td>\n",
       "      <td>307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2003</td>\n",
       "      <td>1106</td>\n",
       "      <td>263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2003</td>\n",
       "      <td>1107</td>\n",
       "      <td>312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2003</td>\n",
       "      <td>1108</td>\n",
       "      <td>279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>2003</td>\n",
       "      <td>1110</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>2003</td>\n",
       "      <td>1111</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>2003</td>\n",
       "      <td>1112</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "SystemName  Season  TeamID  POM\n",
       "0             2003    1102  160\n",
       "1             2003    1103  163\n",
       "2             2003    1104   33\n",
       "3             2003    1105  307\n",
       "4             2003    1106  263\n",
       "5             2003    1107  312\n",
       "6             2003    1108  279\n",
       "7             2003    1110  143\n",
       "8             2003    1111  170\n",
       "9             2003    1112    3"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ranking = pd.read_csv(files[4])\n",
    "#rank_methods = ['COL','DOL','MOR','POM','RTH','SAG','WLK','WOL']\n",
    "rank_methods = ['POM']\n",
    "team_rank = (ranking[(ranking['RankingDayNum']==133)&(ranking['SystemName'].isin(rank_methods))]\n",
    "             .groupby(['Season','TeamID','SystemName'])['OrdinalRank']\n",
    "             .mean()\n",
    "             .unstack(2)\n",
    "             .reset_index()\n",
    "            )\n",
    "team_rank.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tournement Seed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Season</th>\n",
       "      <th>TeamID</th>\n",
       "      <th>SeedN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1985</td>\n",
       "      <td>1207</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1985</td>\n",
       "      <td>1210</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1985</td>\n",
       "      <td>1228</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1985</td>\n",
       "      <td>1260</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1985</td>\n",
       "      <td>1374</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>1985</td>\n",
       "      <td>1208</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>1985</td>\n",
       "      <td>1393</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>1985</td>\n",
       "      <td>1396</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>1985</td>\n",
       "      <td>1439</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>1985</td>\n",
       "      <td>1177</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Season  TeamID  SeedN\n",
       "0    1985    1207      1\n",
       "1    1985    1210      2\n",
       "2    1985    1228      3\n",
       "3    1985    1260      4\n",
       "4    1985    1374      5\n",
       "5    1985    1208      6\n",
       "6    1985    1393      7\n",
       "7    1985    1396      8\n",
       "8    1985    1439      9\n",
       "9    1985    1177     10"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TourneySeeds = pd.read_csv(files[8])\n",
    "TourneySeeds['SeedN'] = TourneySeeds['Seed'].apply(lambda x: int(x[1:3]))\n",
    "TourneySeeds.drop('Seed',axis=1,inplace=True)\n",
    "TourneySeeds.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Team Data\n",
    "\n",
    "Merge all the teams information (per season) into one file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Season</th>\n",
       "      <th>TeamID</th>\n",
       "      <th>Score</th>\n",
       "      <th>Score_OPP</th>\n",
       "      <th>WinPCT</th>\n",
       "      <th>ScoreDiff</th>\n",
       "      <th>POM</th>\n",
       "      <th>SeedN</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1985</td>\n",
       "      <td>1102</td>\n",
       "      <td>63.083333</td>\n",
       "      <td>68.875000</td>\n",
       "      <td>0.208333</td>\n",
       "      <td>-5.791667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>1985</td>\n",
       "      <td>1103</td>\n",
       "      <td>61.043478</td>\n",
       "      <td>64.086957</td>\n",
       "      <td>0.391304</td>\n",
       "      <td>-3.043478</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>1985</td>\n",
       "      <td>1104</td>\n",
       "      <td>68.500000</td>\n",
       "      <td>60.700000</td>\n",
       "      <td>0.700000</td>\n",
       "      <td>7.800000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>1985</td>\n",
       "      <td>1106</td>\n",
       "      <td>71.625000</td>\n",
       "      <td>75.416667</td>\n",
       "      <td>0.416667</td>\n",
       "      <td>-3.791667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>1985</td>\n",
       "      <td>1108</td>\n",
       "      <td>83.000000</td>\n",
       "      <td>75.040000</td>\n",
       "      <td>0.760000</td>\n",
       "      <td>7.960000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11236</td>\n",
       "      <td>2019</td>\n",
       "      <td>1462</td>\n",
       "      <td>71.878788</td>\n",
       "      <td>70.606061</td>\n",
       "      <td>0.545455</td>\n",
       "      <td>1.272727</td>\n",
       "      <td>64.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11237</td>\n",
       "      <td>2019</td>\n",
       "      <td>1463</td>\n",
       "      <td>80.892857</td>\n",
       "      <td>73.714286</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>7.178571</td>\n",
       "      <td>82.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11238</td>\n",
       "      <td>2019</td>\n",
       "      <td>1464</td>\n",
       "      <td>73.500000</td>\n",
       "      <td>79.233333</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>-5.733333</td>\n",
       "      <td>263.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11239</td>\n",
       "      <td>2019</td>\n",
       "      <td>1465</td>\n",
       "      <td>75.461538</td>\n",
       "      <td>75.192308</td>\n",
       "      <td>0.461538</td>\n",
       "      <td>0.269231</td>\n",
       "      <td>192.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11240</td>\n",
       "      <td>2019</td>\n",
       "      <td>1466</td>\n",
       "      <td>65.620690</td>\n",
       "      <td>74.931034</td>\n",
       "      <td>0.241379</td>\n",
       "      <td>-9.310345</td>\n",
       "      <td>285.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11241 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Season  TeamID      Score  Score_OPP    WinPCT  ScoreDiff    POM  SeedN\n",
       "0        1985    1102  63.083333  68.875000  0.208333  -5.791667    NaN    NaN\n",
       "1        1985    1103  61.043478  64.086957  0.391304  -3.043478    NaN    NaN\n",
       "2        1985    1104  68.500000  60.700000  0.700000   7.800000    NaN    7.0\n",
       "3        1985    1106  71.625000  75.416667  0.416667  -3.791667    NaN    NaN\n",
       "4        1985    1108  83.000000  75.040000  0.760000   7.960000    NaN    NaN\n",
       "...       ...     ...        ...        ...       ...        ...    ...    ...\n",
       "11236    2019    1462  71.878788  70.606061  0.545455   1.272727   64.0    NaN\n",
       "11237    2019    1463  80.892857  73.714286  0.750000   7.178571   82.0   14.0\n",
       "11238    2019    1464  73.500000  79.233333  0.333333  -5.733333  263.0    NaN\n",
       "11239    2019    1465  75.461538  75.192308  0.461538   0.269231  192.0    NaN\n",
       "11240    2019    1466  65.620690  74.931034  0.241379  -9.310345  285.0    NaN\n",
       "\n",
       "[11241 rows x 8 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "teamData = (seasonAve\n",
    "            .merge(team_rank,on=['Season','TeamID'],how='left')\n",
    "            .merge(TourneySeeds,on=['Season','TeamID'],how='left')\n",
    "           )\n",
    "teamData"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Tournement Data\n",
    "\n",
    "Merge the teams data into the tournement data file.<br>\n",
    "TeamID1 - always the team with the lower TeamID<br>\n",
    "TeamID2 - always the team with the higher TeamID<br>\n",
    "result - for TeamID1<br>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>Season</td>\n",
       "      <td>1985</td>\n",
       "      <td>1985</td>\n",
       "      <td>1985</td>\n",
       "      <td>1985</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>DayNum</td>\n",
       "      <td>136</td>\n",
       "      <td>136</td>\n",
       "      <td>136</td>\n",
       "      <td>136</td>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>WTeamID</td>\n",
       "      <td>1116</td>\n",
       "      <td>1120</td>\n",
       "      <td>1207</td>\n",
       "      <td>1229</td>\n",
       "      <td>1242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>WScore</td>\n",
       "      <td>63</td>\n",
       "      <td>59</td>\n",
       "      <td>68</td>\n",
       "      <td>58</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>LTeamID</td>\n",
       "      <td>1234</td>\n",
       "      <td>1345</td>\n",
       "      <td>1250</td>\n",
       "      <td>1425</td>\n",
       "      <td>1325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>LScore</td>\n",
       "      <td>54</td>\n",
       "      <td>58</td>\n",
       "      <td>43</td>\n",
       "      <td>55</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>WLoc</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>NumOT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>TeamID1</td>\n",
       "      <td>1116</td>\n",
       "      <td>1120</td>\n",
       "      <td>1207</td>\n",
       "      <td>1229</td>\n",
       "      <td>1242</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>TeamID2</td>\n",
       "      <td>1234</td>\n",
       "      <td>1345</td>\n",
       "      <td>1250</td>\n",
       "      <td>1425</td>\n",
       "      <td>1325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>result</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ID</td>\n",
       "      <td>1985_1116_1234</td>\n",
       "      <td>1985_1120_1345</td>\n",
       "      <td>1985_1207_1250</td>\n",
       "      <td>1985_1229_1425</td>\n",
       "      <td>1985_1242_1325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Score_x</td>\n",
       "      <td>65.3333</td>\n",
       "      <td>70.3448</td>\n",
       "      <td>75.7407</td>\n",
       "      <td>71.5926</td>\n",
       "      <td>76.0333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Score_OPP_x</td>\n",
       "      <td>61.697</td>\n",
       "      <td>66.6552</td>\n",
       "      <td>60.0741</td>\n",
       "      <td>65.6296</td>\n",
       "      <td>70.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>WinPCT_x</td>\n",
       "      <td>0.636364</td>\n",
       "      <td>0.62069</td>\n",
       "      <td>0.925926</td>\n",
       "      <td>0.740741</td>\n",
       "      <td>0.766667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ScoreDiff_x</td>\n",
       "      <td>3.63636</td>\n",
       "      <td>3.68966</td>\n",
       "      <td>15.6667</td>\n",
       "      <td>5.96296</td>\n",
       "      <td>5.63333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>POM_x</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>SeedN_x</td>\n",
       "      <td>9</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Score_y</td>\n",
       "      <td>69.7333</td>\n",
       "      <td>69.12</td>\n",
       "      <td>65.7586</td>\n",
       "      <td>68.3929</td>\n",
       "      <td>67.5556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Score_OPP_y</td>\n",
       "      <td>59.2667</td>\n",
       "      <td>65.32</td>\n",
       "      <td>70.2069</td>\n",
       "      <td>64.6071</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>WinPCT_y</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>0.68</td>\n",
       "      <td>0.37931</td>\n",
       "      <td>0.678571</td>\n",
       "      <td>0.740741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ScoreDiff_y</td>\n",
       "      <td>10.4667</td>\n",
       "      <td>3.8</td>\n",
       "      <td>-4.44828</td>\n",
       "      <td>3.78571</td>\n",
       "      <td>4.55556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>POM_y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>SeedN_y</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>16</td>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>SeedDiff</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>-15</td>\n",
       "      <td>1</td>\n",
       "      <td>-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ScoreDiff</td>\n",
       "      <td>-6.8303</td>\n",
       "      <td>-0.110345</td>\n",
       "      <td>20.1149</td>\n",
       "      <td>2.17725</td>\n",
       "      <td>1.07778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>POMDiff</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>WinPCTDiff</td>\n",
       "      <td>-0.030303</td>\n",
       "      <td>-0.0593103</td>\n",
       "      <td>0.546616</td>\n",
       "      <td>0.0621693</td>\n",
       "      <td>0.0259259</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          0               1               2               3  \\\n",
       "Season                 1985            1985            1985            1985   \n",
       "DayNum                  136             136             136             136   \n",
       "WTeamID                1116            1120            1207            1229   \n",
       "WScore                   63              59              68              58   \n",
       "LTeamID                1234            1345            1250            1425   \n",
       "LScore                   54              58              43              55   \n",
       "WLoc                      N               N               N               N   \n",
       "NumOT                     0               0               0               0   \n",
       "TeamID1                1116            1120            1207            1229   \n",
       "TeamID2                1234            1345            1250            1425   \n",
       "result                    1               1               1               1   \n",
       "ID           1985_1116_1234  1985_1120_1345  1985_1207_1250  1985_1229_1425   \n",
       "Score_x             65.3333         70.3448         75.7407         71.5926   \n",
       "Score_OPP_x          61.697         66.6552         60.0741         65.6296   \n",
       "WinPCT_x           0.636364         0.62069        0.925926        0.740741   \n",
       "ScoreDiff_x         3.63636         3.68966         15.6667         5.96296   \n",
       "POM_x                   NaN             NaN             NaN             NaN   \n",
       "SeedN_x                   9              11               1               9   \n",
       "Score_y             69.7333           69.12         65.7586         68.3929   \n",
       "Score_OPP_y         59.2667           65.32         70.2069         64.6071   \n",
       "WinPCT_y           0.666667            0.68         0.37931        0.678571   \n",
       "ScoreDiff_y         10.4667             3.8        -4.44828         3.78571   \n",
       "POM_y                   NaN             NaN             NaN             NaN   \n",
       "SeedN_y                   8               6              16               8   \n",
       "SeedDiff                  1               5             -15               1   \n",
       "ScoreDiff           -6.8303       -0.110345         20.1149         2.17725   \n",
       "POMDiff                 NaN             NaN             NaN             NaN   \n",
       "WinPCTDiff        -0.030303      -0.0593103        0.546616       0.0621693   \n",
       "\n",
       "                          4  \n",
       "Season                 1985  \n",
       "DayNum                  136  \n",
       "WTeamID                1242  \n",
       "WScore                   49  \n",
       "LTeamID                1325  \n",
       "LScore                   38  \n",
       "WLoc                      N  \n",
       "NumOT                     0  \n",
       "TeamID1                1242  \n",
       "TeamID2                1325  \n",
       "result                    1  \n",
       "ID           1985_1242_1325  \n",
       "Score_x             76.0333  \n",
       "Score_OPP_x            70.4  \n",
       "WinPCT_x           0.766667  \n",
       "ScoreDiff_x         5.63333  \n",
       "POM_x                   NaN  \n",
       "SeedN_x                   3  \n",
       "Score_y             67.5556  \n",
       "Score_OPP_y              63  \n",
       "WinPCT_y           0.740741  \n",
       "ScoreDiff_y         4.55556  \n",
       "POM_y                   NaN  \n",
       "SeedN_y                  14  \n",
       "SeedDiff                -11  \n",
       "ScoreDiff           1.07778  \n",
       "POMDiff                 NaN  \n",
       "WinPCTDiff        0.0259259  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TourneyCompactResults = pd.read_csv(files[5])\n",
    "TourneyCompactResults['TeamID1'] = np.minimum(TourneyCompactResults['WTeamID'],TourneyCompactResults['LTeamID'])\n",
    "TourneyCompactResults['TeamID2'] = np.maximum(TourneyCompactResults['WTeamID'],TourneyCompactResults['LTeamID'])\n",
    "TourneyCompactResults['result'] = np.where(TourneyCompactResults['WTeamID']==TourneyCompactResults['TeamID1'],1,0)\n",
    "TourneyCompactResults['ID'] = TourneyCompactResults['Season'].astype(str)+ '_' +TourneyCompactResults['TeamID1'].astype(str)+ '_' +TourneyCompactResults['TeamID2'].astype(str)\n",
    "    \n",
    "TourneyCompactResults = (TourneyCompactResults\n",
    "                         .merge(teamData,left_on=['Season','TeamID1'],right_on=['Season','TeamID'],how='left')\n",
    "                         .drop('TeamID',axis=1)\n",
    "                         .merge(teamData,left_on=['Season','TeamID2'],right_on=['Season','TeamID'],how='left')\n",
    "                         .drop('TeamID',axis=1)\n",
    "                        )\n",
    "\n",
    "TourneyCompactResults['SeedDiff'] = TourneyCompactResults['SeedN_x'] - TourneyCompactResults['SeedN_y']\n",
    "TourneyCompactResults['ScoreDiff'] = TourneyCompactResults['ScoreDiff_x']  - TourneyCompactResults['ScoreDiff_y']\n",
    "TourneyCompactResults['POMDiff'] = TourneyCompactResults['POM_x']  - TourneyCompactResults['POM_y']\n",
    "TourneyCompactResults['WinPCTDiff'] = TourneyCompactResults['WinPCT_x']  - TourneyCompactResults['WinPCT_y']\n",
    "\n",
    "TourneyCompactResults.head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Train Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestClassifier, AdaBoostClassifier\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.svm import SVC\n",
    "from sklearn.neighbors import KNeighborsClassifier\n",
    "from sklearn.model_selection import cross_val_score\n",
    "from sklearn.model_selection import GroupKFold\n",
    "from sklearn.metrics import log_loss\n",
    "from sklearn import preprocessing\n",
    "from sklearn.inspection import permutation_importance\n",
    "from sklearn.ensemble import StackingClassifier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['Score_x',\n",
    " 'Score_OPP_x',\n",
    " 'ScoreDiff_x',\n",
    " 'POM_x',\n",
    " 'SeedN_x',\n",
    " 'Score_y',\n",
    " 'Score_OPP_y',\n",
    " 'ScoreDiff_y',\n",
    " 'POM_y',\n",
    " 'SeedN_y',\n",
    " 'SeedDiff',\n",
    " 'ScoreDiff',\n",
    " 'POMDiff',\n",
    " 'WinPCT_x',\n",
    " 'WinPCT_y',\n",
    " 'WinPCTDiff']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "X = TourneyCompactResults.loc[:,cols]\n",
    "y = TourneyCompactResults[['result']].values.ravel()\n",
    "\n",
    "min_year = 2002"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Trian Models"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Method 1 \n",
    "use all the previous seasons to predict a particular season"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "rf = RandomForestClassifier(n_estimators=100,random_state=42,min_samples_split=100)\n",
    "lr = LogisticRegression(solver='lbfgs',C=0.1,random_state=42,max_iter=500)\n",
    "lsvc = SVC(random_state=42,probability=True,kernel='linear')\n",
    "nnb = KNeighborsClassifier(50)\n",
    "\n",
    "estimators = [\n",
    "    ('rf', RandomForestClassifier(n_estimators=10, random_state=42)),\n",
    "    ('lr',LogisticRegression(solver='lbfgs',C=0.1,random_state=42,max_iter=500)),\n",
    "    ('nnb',KNeighborsClassifier(50)),\n",
    "    ('lsvc',SVC(random_state=42,probability=True,kernel='linear'))]\n",
    "clf = StackingClassifier(estimators=estimators, final_estimator=LogisticRegression())\n",
    "\n",
    "scores = np.zeros((5,5))\n",
    "for ii,s in enumerate(range(2010,2015)):\n",
    "    idxTrain = ((TourneyCompactResults['Season'] < s) & (TourneyCompactResults['Season'] > min_year))\n",
    "    idxTest = (TourneyCompactResults['Season'] == s)\n",
    "    # fit all models\n",
    "    rf.fit(X.loc[idxTrain],y[idxTrain])\n",
    "    lr.fit(X.loc[idxTrain],y[idxTrain])\n",
    "    lsvc.fit(X.loc[idxTrain],y[idxTrain])\n",
    "    nnb.fit(X.loc[idxTrain],y[idxTrain])\n",
    "    clf.fit(X.loc[idxTrain],y[idxTrain])\n",
    "    \n",
    "    ypred_rf = rf.predict_proba(X.loc[idxTest])\n",
    "    ypred_lr = lr.predict_proba(X.loc[idxTest])\n",
    "    ypred_lsvc = lsvc.predict_proba(X.loc[idxTest])\n",
    "    ypred_nnb = nnb.predict_proba(X.loc[idxTest])\n",
    "    ypred_clf = clf.predict_proba(X.loc[idxTest])\n",
    "    \n",
    "    scores[ii,0] = log_loss(y[idxTest],ypred_rf[:,1])\n",
    "    scores[ii,1] = log_loss(y[idxTest],ypred_lr[:,1])\n",
    "    scores[ii,2] = log_loss(y[idxTest],ypred_lsvc[:,1])\n",
    "    scores[ii,3] = log_loss(y[idxTest],ypred_nnb[:,1])\n",
    "    scores[ii,4] = log_loss(y[idxTest],ypred_clf[:,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>average score</th>\n",
       "      <th>score std</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>random forest</td>\n",
       "      <td>0.556771</td>\n",
       "      <td>0.669634</td>\n",
       "      <td>0.560905</td>\n",
       "      <td>0.626996</td>\n",
       "      <td>0.598192</td>\n",
       "      <td>0.602500</td>\n",
       "      <td>0.042301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>logistic regression</td>\n",
       "      <td>0.554581</td>\n",
       "      <td>0.643929</td>\n",
       "      <td>0.581607</td>\n",
       "      <td>0.652802</td>\n",
       "      <td>0.597960</td>\n",
       "      <td>0.606176</td>\n",
       "      <td>0.037236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>linear svc</td>\n",
       "      <td>0.557809</td>\n",
       "      <td>0.623084</td>\n",
       "      <td>0.561783</td>\n",
       "      <td>0.655943</td>\n",
       "      <td>0.627216</td>\n",
       "      <td>0.605167</td>\n",
       "      <td>0.038757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>nearest neighbor</td>\n",
       "      <td>0.594839</td>\n",
       "      <td>0.617833</td>\n",
       "      <td>0.557865</td>\n",
       "      <td>0.641447</td>\n",
       "      <td>0.588438</td>\n",
       "      <td>0.600084</td>\n",
       "      <td>0.028184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>stacking</td>\n",
       "      <td>0.571687</td>\n",
       "      <td>0.648026</td>\n",
       "      <td>0.555573</td>\n",
       "      <td>0.650265</td>\n",
       "      <td>0.615674</td>\n",
       "      <td>0.608245</td>\n",
       "      <td>0.038767</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         2010      2011      2012      2013      2014  \\\n",
       "random forest        0.556771  0.669634  0.560905  0.626996  0.598192   \n",
       "logistic regression  0.554581  0.643929  0.581607  0.652802  0.597960   \n",
       "linear svc           0.557809  0.623084  0.561783  0.655943  0.627216   \n",
       "nearest neighbor     0.594839  0.617833  0.557865  0.641447  0.588438   \n",
       "stacking             0.571687  0.648026  0.555573  0.650265  0.615674   \n",
       "\n",
       "                     average score  score std  \n",
       "random forest             0.602500   0.042301  \n",
       "logistic regression       0.606176   0.037236  \n",
       "linear svc                0.605167   0.038757  \n",
       "nearest neighbor          0.600084   0.028184  \n",
       "stacking                  0.608245   0.038767  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scores_df = pd.DataFrame(scores,\n",
    "                         index = range(2010,2015),\n",
    "                         columns=['random forest','logistic regression','linear svc','nearest neighbor','stacking']).T\n",
    "\n",
    "scores_df['average score'] = scores_df.mean(axis=1)\n",
    "scores_df['score std'] = scores_df.std(axis=1)\n",
    "\n",
    "scores_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Method 2\n",
    "Create groups by seasons and run cv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "idxTrain = ((TourneyCompactResults['Season'] < 2015) & (TourneyCompactResults['Season'] > min_year))\n",
    "group_kfold = GroupKFold(n_splits=6)\n",
    "\n",
    "scores = []\n",
    "for model in [rf,lr,lsvc,nnb,clf]:\n",
    "    scores.append(cross_val_score(model,\n",
    "                            X[idxTrain],\n",
    "                            y[idxTrain],\n",
    "                            groups=TourneyCompactResults.loc[idxTrain,'Season'],\n",
    "                            cv=group_kfold,\n",
    "                            scoring='neg_log_loss'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>average score</th>\n",
       "      <th>score std</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>random forest</td>\n",
       "      <td>0.558016</td>\n",
       "      <td>0.038805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>logistic regression</td>\n",
       "      <td>0.550999</td>\n",
       "      <td>0.038113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>linear svc</td>\n",
       "      <td>0.553969</td>\n",
       "      <td>0.041137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>nearest neighbor</td>\n",
       "      <td>0.568346</td>\n",
       "      <td>0.031287</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>stacking</td>\n",
       "      <td>0.560579</td>\n",
       "      <td>0.033746</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     average score  score std\n",
       "random forest             0.558016   0.038805\n",
       "logistic regression       0.550999   0.038113\n",
       "linear svc                0.553969   0.041137\n",
       "nearest neighbor          0.568346   0.031287\n",
       "stacking                  0.560579   0.033746"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame([-1*np.mean(np.array(scores),axis=1),np.std(np.array(scores),axis=1)],\n",
    "            columns=['random forest','logistic regression','linear svc','nearest neighbor','stacking'],\n",
    "            index = ['average score','score std']).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Choose best model and train for the entire data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "idxTrain = ((TourneyCompactResults['Season'] < 2015) & (TourneyCompactResults['Season'] > min_year))\n",
    "lr.fit(X.loc[idxTrain],y[idxTrain]);\n",
    "rf.fit(X.loc[idxTrain],y[idxTrain]);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare submission file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>Season</th>\n",
       "      <th>TeamID1</th>\n",
       "      <th>TeamID2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2015_1107_1112</td>\n",
       "      <td>2015</td>\n",
       "      <td>1107</td>\n",
       "      <td>1112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2015_1107_1116</td>\n",
       "      <td>2015</td>\n",
       "      <td>1107</td>\n",
       "      <td>1116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2015_1107_1124</td>\n",
       "      <td>2015</td>\n",
       "      <td>1107</td>\n",
       "      <td>1124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2015_1107_1125</td>\n",
       "      <td>2015</td>\n",
       "      <td>1107</td>\n",
       "      <td>1125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2015_1107_1129</td>\n",
       "      <td>2015</td>\n",
       "      <td>1107</td>\n",
       "      <td>1129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2015_1107_1138</td>\n",
       "      <td>2015</td>\n",
       "      <td>1107</td>\n",
       "      <td>1138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2015_1107_1139</td>\n",
       "      <td>2015</td>\n",
       "      <td>1107</td>\n",
       "      <td>1139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>2015_1107_1140</td>\n",
       "      <td>2015</td>\n",
       "      <td>1107</td>\n",
       "      <td>1140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>2015_1107_1153</td>\n",
       "      <td>2015</td>\n",
       "      <td>1107</td>\n",
       "      <td>1153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>2015_1107_1157</td>\n",
       "      <td>2015</td>\n",
       "      <td>1107</td>\n",
       "      <td>1157</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               ID  Season  TeamID1  TeamID2\n",
       "0  2015_1107_1112    2015     1107     1112\n",
       "1  2015_1107_1116    2015     1107     1116\n",
       "2  2015_1107_1124    2015     1107     1124\n",
       "3  2015_1107_1125    2015     1107     1125\n",
       "4  2015_1107_1129    2015     1107     1129\n",
       "5  2015_1107_1138    2015     1107     1138\n",
       "6  2015_1107_1139    2015     1107     1139\n",
       "7  2015_1107_1140    2015     1107     1140\n",
       "8  2015_1107_1153    2015     1107     1153\n",
       "9  2015_1107_1157    2015     1107     1157"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testDF = pd.read_csv('google-cloud-ncaa-march-madness-2020-division-1-mens-tournament\\\\MSampleSubmissionStage1_2020.csv').drop('Pred',axis=1)\n",
    "testDF = pd.concat([testDF,testDF['ID'].str.split('_',expand=True).rename({0:'Season',1:'TeamID1',2:'TeamID2'},axis=1)],axis=1)\n",
    "testDF['Season'] = testDF['Season'].astype('int64')\n",
    "testDF['TeamID1'] = testDF['TeamID1'].astype('int64')\n",
    "testDF['TeamID2'] = testDF['TeamID2'].astype('int64')\n",
    "testDF.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "subFile = (testDF\n",
    "             .merge(teamData,left_on=['Season','TeamID1'],right_on=['Season','TeamID'],how='left')\n",
    "             .drop('TeamID',axis=1)\n",
    "             .merge(teamData,left_on=['Season','TeamID2'],right_on=['Season','TeamID'],how='left')\n",
    "             .drop('TeamID',axis=1)\n",
    "          )\n",
    "\n",
    "subFile['SeedDiff'] = subFile['SeedN_x'] - subFile['SeedN_y']\n",
    "subFile['ScoreDiff'] = subFile['ScoreDiff_x']  - subFile['ScoreDiff_y']\n",
    "subFile['POMDiff'] = subFile['POM_x']  - subFile['POM_y']\n",
    "subFile['WinPCTDiff'] = subFile['WinPCT_x']  - subFile['WinPCT_y']\n",
    "\n",
    "subFile['Pred1'] = lr.predict_proba(subFile.loc[:,cols])[:,1]\n",
    "subFile['Pred2'] = rf.predict_proba(subFile.loc[:,cols])[:,1]\n",
    "\n",
    "subFile['Pred'] = 0.5*(subFile['Pred1']+subFile['Pred2'])\n",
    "subFile = subFile[['ID','Pred']]\n",
    "\n",
    "subFile.to_csv('submission.csv',index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}