WorkFlow I - Data Preparation For 3.13 betting.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Collect allplayerGameLogs by 3.12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import pickle\n",
    "from urllib import urlencode\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "from sets import Set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with open('../Data/playid_df.pickle','rb') as data_file:\n",
    "    playid_data = pickle.load(data_file)\n",
    "playid_df = pd.DataFrame(playid_data)\n",
    "ids_ls = playid_df['playerId']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def collectAllPlayerGameLogs(ids_ls):\n",
    "    leagueid = '00'\n",
    "    url = \"http://stats.nba.com/stats/playergamelog?\"\n",
    "    u_a = {'user-agent':'Mozilla/5.0 (Windows NT 10.0; WOW64; rv:43.0) Gecko/20100101 Firefox/43.0'} #header is necessary\n",
    "    allplayerGameLogs_df_ls = []\n",
    "    season='2015-16'\n",
    "    seasontype='Regular Season'\n",
    "    \n",
    "    for playerid in ids_ls:\n",
    "        api_param = (('LeagueID', leagueid),('PlayerID',playerid),('Season',season),('SeasonType',seasontype))\n",
    "        response = requests.get(url, params=api_param,headers={\"USER-AGENT\":u_a})\n",
    "        response_json = response.json()\n",
    "        response_df = pd.DataFrame(response_json['resultSets'][0]['rowSet'],columns=response_json['resultSets'][0]['headers'])\n",
    "        allplayerGameLogs_df_ls.append(response_df)\n",
    "        \n",
    "    allplayerGameLogs_df = pd.concat(allplayerGameLogs_df_ls,axis=0)\n",
    "    return(allplayerGameLogs_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(20875, 27)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allplayerGameLogs_0313 = collectAllPlayerGameLogs(ids_ls)\n",
    "allplayerGameLogs_0313.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('../Data/allplayerGameLogs_0313.pickle', 'wb') as handle:\n",
    "  pickle.dump(allplayerGameLogs_0313, handle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "allplayerGameLogs_0314 = collectAllPlayerGameLogs(ids_ls)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(20984, 27)"
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allplayerGameLogs_0314.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Data Munging to combine different data sources"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "allplayerGameLogs = allplayerGameLogs_0313\n",
    "del allplayerGameLogs_0313"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def data_cleaner(allplayerGameLogs):\n",
    "    #data cleaning 1: add calculated Fantasy Points for each player in each game\n",
    "    allplayerGameLogs['GAME_DATE'] = pd.to_datetime(allplayerGameLogs['GAME_DATE'])\n",
    "    del allplayerGameLogs['VIDEO_AVAILABLE']\n",
    "    dd = ((allplayerGameLogs['PTS']>=10) & (allplayerGameLogs['AST']>=10))| \\\n",
    "         ((allplayerGameLogs['PTS']>=10) & (allplayerGameLogs['REB']>=10))| \\\n",
    "         ((allplayerGameLogs['AST']>=10) & (allplayerGameLogs['REB']>=10))| \\\n",
    "         ((allplayerGameLogs['PTS']>=10) & (allplayerGameLogs['STL']>=10))| \\\n",
    "         ((allplayerGameLogs['REB']>=10) & (allplayerGameLogs['BLK']>=10))| \\\n",
    "         ((allplayerGameLogs['STL']>=10) & (allplayerGameLogs['BLK']>=10))| \\\n",
    "         ((allplayerGameLogs['PTS']>=10) & (allplayerGameLogs['BLK']>=10))\n",
    "    allplayerGameLogs['DouBL']= dd\n",
    "    ttt = (allplayerGameLogs['PTS']>=10) & (allplayerGameLogs['AST']>=10) & (allplayerGameLogs['REB']>=10)| \\\n",
    "          (allplayerGameLogs['PTS']>=10) & (allplayerGameLogs['STL']>=10) & (allplayerGameLogs['REB']>=10)| \\\n",
    "          (allplayerGameLogs['PTS']>=10) & (allplayerGameLogs['BLK']>=10) & (allplayerGameLogs['REB']>=10)| \\\n",
    "          (allplayerGameLogs['AST']>=10) & (allplayerGameLogs['BLK']>=10) & (allplayerGameLogs['REB']>=10)| \\\n",
    "          (allplayerGameLogs['PTS']>=10) & (allplayerGameLogs['STL']>=10) & (allplayerGameLogs['AST']>=10)| \\\n",
    "          (allplayerGameLogs['STL']>=10) & (allplayerGameLogs['AST']>=10) & (allplayerGameLogs['REB']>=10)\n",
    "    allplayerGameLogs['TriBL']= ttt\n",
    "    allplayerGameLogs['FanPTs'] = 3.5 * allplayerGameLogs['FG3M'] + 1*allplayerGameLogs['FTM'] \\\n",
    "    + 2*(allplayerGameLogs['FGM']-allplayerGameLogs['FG3M']) \\\n",
    "    + 1.25*allplayerGameLogs['REB']+1.5*allplayerGameLogs['AST'] \\\n",
    "    + 2*allplayerGameLogs['STL']+ 2*allplayerGameLogs['BLK'] \\\n",
    "    + 1.5*allplayerGameLogs['DouBL'] + 3*allplayerGameLogs['TriBL'] \\\n",
    "    - 0.5*allplayerGameLogs['TOV']\n",
    "    allplayerFantasyGameLogs = allplayerGameLogs.set_index('GAME_DATE')\n",
    "    allplayerFantasyGameLogs = allplayerFantasyGameLogs.sort_index(axis=0)\n",
    "\n",
    "    #data cleaning 2: make sure the players info we collected correspond to our player_id list.\n",
    "    allplayerFantasyGameLogs = pd.merge(allplayerFantasyGameLogs.reset_index(), playid_df[['playerId' \\\n",
    "         ,'fullName']], left_on='Player_ID',right_on='playerId', how='left')\n",
    "    del allplayerFantasyGameLogs['playerId']\n",
    "\n",
    "    #data cleaning 3: add position information for each player\n",
    "    with open('../Data/allPlayerBios.pickle', 'rb') as handle:\n",
    "      playerBios = pickle.load(handle)\n",
    "    allplayerFantasyGameLogs = pd.merge(allplayerFantasyGameLogs,playerBios[['PERSON_ID','position1']], left_on='Player_ID', \\\n",
    "                                    right_on='PERSON_ID', how='left')\n",
    "    del allplayerFantasyGameLogs['PERSON_ID']\n",
    "\n",
    "    #data cleaning 4: add team information for each player\n",
    "    allplayerFantasyGameLogs['Team'] = allplayerFantasyGameLogs['MATCHUP'].map(lambda x: x.split(' ')[0])\n",
    "    allplayerFantasyGameLogs['OpponentTeam'] = allplayerFantasyGameLogs['MATCHUP'].map(lambda x: x.split(' ')[2])\n",
    "    allplayerFantasyGameLogs['HomeGame'] = allplayerFantasyGameLogs['MATCHUP'].map(lambda x: 0 if x.split(' ')[1]=='@' else 1)\n",
    "\n",
    "    #data cleaning 5: Fix some missing values\n",
    "    allplayerFantasyGameLogs.loc[(allplayerFantasyGameLogs.fullName.isnull()) & (allplayerFantasyGameLogs.Player_ID==2403),'fullName'] = 'Nene Hilario'\n",
    "    \n",
    "    return allplayerFantasyGameLogs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "allplayerFantasyGameLogs = data_cleaner(allplayerGameLogs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "allplayerFantasyGameLogs_0314 = data_cleaner(allplayerGameLogs_0314)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GAME_DATE</th>\n",
       "      <th>SEASON_ID</th>\n",
       "      <th>Player_ID</th>\n",
       "      <th>Game_ID</th>\n",
       "      <th>MATCHUP</th>\n",
       "      <th>WL</th>\n",
       "      <th>MIN</th>\n",
       "      <th>FGM</th>\n",
       "      <th>FGA</th>\n",
       "      <th>FG_PCT</th>\n",
       "      <th>...</th>\n",
       "      <th>PTS</th>\n",
       "      <th>PLUS_MINUS</th>\n",
       "      <th>DouBL</th>\n",
       "      <th>TriBL</th>\n",
       "      <th>FanPTs</th>\n",
       "      <th>fullName</th>\n",
       "      <th>position1</th>\n",
       "      <th>Team</th>\n",
       "      <th>OpponentTeam</th>\n",
       "      <th>HomeGame</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-10-27</td>\n",
       "      <td>22015</td>\n",
       "      <td>2738</td>\n",
       "      <td>0021500003</td>\n",
       "      <td>GSW vs. NOP</td>\n",
       "      <td>W</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0.333</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>14.25</td>\n",
       "      <td>Andre Iguodala</td>\n",
       "      <td>SF</td>\n",
       "      <td>GSW</td>\n",
       "      <td>NOP</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-10-27</td>\n",
       "      <td>22015</td>\n",
       "      <td>2544</td>\n",
       "      <td>0021500002</td>\n",
       "      <td>CLE @ CHI</td>\n",
       "      <td>L</td>\n",
       "      <td>36</td>\n",
       "      <td>12</td>\n",
       "      <td>22</td>\n",
       "      <td>0.545</td>\n",
       "      <td>...</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>48.50</td>\n",
       "      <td>LeBron James</td>\n",
       "      <td>SF</td>\n",
       "      <td>CLE</td>\n",
       "      <td>CHI</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-10-27</td>\n",
       "      <td>22015</td>\n",
       "      <td>201565</td>\n",
       "      <td>0021500002</td>\n",
       "      <td>CHI vs. CLE</td>\n",
       "      <td>W</td>\n",
       "      <td>32</td>\n",
       "      <td>8</td>\n",
       "      <td>22</td>\n",
       "      <td>0.364</td>\n",
       "      <td>...</td>\n",
       "      <td>18</td>\n",
       "      <td>-1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>27.75</td>\n",
       "      <td>Derrick Rose</td>\n",
       "      <td>PG</td>\n",
       "      <td>CHI</td>\n",
       "      <td>CLE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-10-27</td>\n",
       "      <td>22015</td>\n",
       "      <td>201168</td>\n",
       "      <td>0021500001</td>\n",
       "      <td>ATL vs. DET</td>\n",
       "      <td>L</td>\n",
       "      <td>16</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0.400</td>\n",
       "      <td>...</td>\n",
       "      <td>4</td>\n",
       "      <td>-3</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>11.00</td>\n",
       "      <td>Tiago Splitter</td>\n",
       "      <td>C</td>\n",
       "      <td>ATL</td>\n",
       "      <td>DET</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015-10-27</td>\n",
       "      <td>22015</td>\n",
       "      <td>203484</td>\n",
       "      <td>0021500001</td>\n",
       "      <td>DET @ ATL</td>\n",
       "      <td>W</td>\n",
       "      <td>37</td>\n",
       "      <td>7</td>\n",
       "      <td>14</td>\n",
       "      <td>0.500</td>\n",
       "      <td>...</td>\n",
       "      <td>21</td>\n",
       "      <td>17</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>30.50</td>\n",
       "      <td>Kentavious Caldwell-Pope</td>\n",
       "      <td>SG</td>\n",
       "      <td>DET</td>\n",
       "      <td>ATL</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   GAME_DATE SEASON_ID  Player_ID     Game_ID      MATCHUP WL  MIN  FGM  FGA  \\\n",
       "0 2015-10-27     22015       2738  0021500003  GSW vs. NOP  W   23    1    3   \n",
       "1 2015-10-27     22015       2544  0021500002    CLE @ CHI  L   36   12   22   \n",
       "2 2015-10-27     22015     201565  0021500002  CHI vs. CLE  W   32    8   22   \n",
       "3 2015-10-27     22015     201168  0021500001  ATL vs. DET  L   16    2    5   \n",
       "4 2015-10-27     22015     203484  0021500001    DET @ ATL  W   37    7   14   \n",
       "\n",
       "   FG_PCT    ...     PTS  PLUS_MINUS  DouBL  TriBL  FanPTs  \\\n",
       "0   0.333    ...       2           1  False  False   14.25   \n",
       "1   0.545    ...      25           1   True  False   48.50   \n",
       "2   0.364    ...      18          -1  False  False   27.75   \n",
       "3   0.400    ...       4          -3  False  False   11.00   \n",
       "4   0.500    ...      21          17  False  False   30.50   \n",
       "\n",
       "                   fullName  position1  Team  OpponentTeam  HomeGame  \n",
       "0            Andre Iguodala         SF   GSW           NOP         1  \n",
       "1              LeBron James         SF   CLE           CHI         0  \n",
       "2              Derrick Rose         PG   CHI           CLE         1  \n",
       "3            Tiago Splitter          C   ATL           DET         1  \n",
       "4  Kentavious Caldwell-Pope         SG   DET           ATL         0  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 242,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allplayerFantasyGameLogs_0314.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "allplayerFantasyGameLogs_0314.set_index(\"GAME_DATE\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "test = allplayerFantasyGameLogs_0314.ix['2016-03-13']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test[test.fullName]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open('../Data/allplayerFantasyGameLogs_0313.pickle', 'wb') as handle:\n",
    "  pickle.dump(allplayerFantasyGameLogs, handle)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Clean it up to Player Features/Stats Table for modeling"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "allplayerFantasyGameLogs['GameMonth'] = allplayerFantasyGameLogs['GAME_DATE'].map(lambda dd: dd.month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "allplayerFantasyGameLogs.set_index('GAME_DATE', inplace=True)\n",
    "\n",
    "def aggr(group):\n",
    "    test_df = pd.DataFrame()    \n",
    "    test_df['LastFanPTs'] = group['FanPTs'][-1:]\n",
    "    test_df['AvgFanPTs'] = group['FanPTs'].mean()\n",
    "    test_df['AvgPTS'] = group['PTS'].mean()\n",
    "    test_df['LastPT'] = group['PTS'][-1:]\n",
    "    test_df['AvgMIN'] = group['MIN'].mean()\n",
    "    test_df['LastMIN'] = group['MIN'][-1:]\n",
    "    test_df['AvgFGM'] = group['FGM'].mean()\n",
    "    test_df['LastFGM'] = group['FGM'][-1:]\n",
    "    test_df['AvgFGA'] = group['FGA'].mean()\n",
    "    test_df['LastFGA'] = group['FGA'][-1:]\n",
    "    test_df['AvgFG3M'] = group['FG3M'].mean()\n",
    "    test_df['LastFG3M'] = group['FG3M'][-1:]\n",
    "    test_df['AvgFG3A'] = group['FG3A'].mean()\n",
    "    test_df['LastFG3A'] = group['FG3A'][-1:]\n",
    "    test_df['AvgREB'] = group['REB'].mean()\n",
    "    test_df['LastREB'] = group['REB'][-1:]\n",
    "    test_df['AvgAST'] = group['AST'].mean()\n",
    "    test_df['LastAST'] = group['AST'][-1:]\n",
    "    test_df['AvgSTL'] = group['STL'].mean()\n",
    "    test_df['AvgTOV'] = group['TOV'].mean() \n",
    "    test_df['LastTOV'] = group['TOV'][-1:]\n",
    "    test_df['AvgPF'] = group['PF'].mean()\n",
    "    test_df['LastPF'] = group['PF'][-1:]\n",
    "    test_df['AvgPLUS_MINUS'] = group['PLUS_MINUS'].mean()\n",
    "    test_df['LastPLUS_MINUS'] = group['PLUS_MINUS'][-1:]\n",
    "    #group['NumDouBL'] = group['DouBL'].sum()\n",
    "    #group['NumTriBL'] = group['TriBL'].sum()\n",
    "\n",
    "    test_df['Last3GameAvgFanPTs'] = group['FanPTs'][-3:].mean()\n",
    "    test_df['Last3GameAvgMIN'] = group['MIN'][-3:].mean()\n",
    "    test_df['Last3GameAvgPTS'] = group['PTS'][-3:].mean()\n",
    "    \n",
    "    num_team = len(group['Team'].unique())\n",
    "    if(num_team==1):\n",
    "        test_df['fullName'] = group['fullName'].unique()\n",
    "        test_df['Player_ID'] = group['Player_ID'].unique()\n",
    "        test_df['Team'] = group['Team'].unique()[0]\n",
    "        test_df['position1'] = group['position1'].unique()[0]\n",
    "    else:\n",
    "        test_df['fullName'] = group['fullName'].unique()\n",
    "        test_df['Player_ID'] = group['Player_ID'].unique()\n",
    "        test_df['Team'] = group['Team'].unique()[num_team-1]\n",
    "        test_df['position1'] = group['position1'].unique()       \n",
    "    \n",
    "    return(test_df)\n",
    "    \n",
    "def aggr_stats(date,allplayerFantasyGameLogs):\n",
    "    interest_columns = ['fullName','Player_ID','Team','position1','MIN','PTS','FGM','FGA', 'FG3M','FG3A', \\\n",
    "                        'REB','AST','STL','TOV','PF','PLUS_MINUS','DouBL','TriBL','FanPTs']\n",
    "    tmp = allplayerFantasyGameLogs.ix['2015-10-27':date]\n",
    "    \n",
    "    playerID_tmp = tmp.reset_index().copy()\n",
    "    tmp.grouped = playerID_tmp[interest_columns].groupby('Player_ID')\n",
    "    Newdf = pd.DataFrame()\n",
    "    ids = playerID_tmp['Player_ID'].unique()\n",
    "    \n",
    "    for id in ids:\n",
    "        group = tmp.grouped.get_group(id)\n",
    "        df = aggr(group)\n",
    "        Newdf = pd.concat([Newdf,df],axis=0)\n",
    "    \n",
    "    bins = [-10, 10, 20, 30, 40, 100]\n",
    "    group_names = ['benchPlayer','belowAvg','average','advanced','top']\n",
    "    Newdf['Rank']= pd.cut(Newdf['AvgFanPTs'],bins,labels=group_names)\n",
    "    \n",
    "    return(Newdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def aggr_teamVSteam(group):\n",
    "        group['TeamStdVSFanPTs'] = group['FanPTs'].std()\n",
    "        group['TeamAvgVSFanPTs'] = group['FanPTs'].mean()\n",
    "        group['TeamMaxVSFanPTs'] = group['FanPTs'].max()\n",
    "        return group\n",
    "\n",
    "def aggr_team(group):\n",
    "        group['TeamStdFanPTs'] = group['TeamStdVSFanPTs'].mean()\n",
    "        group['TeamAvgFanPTs'] = group['TeamAvgVSFanPTs'].mean()\n",
    "        group['TeamMaxFanPTs'] = group['TeamMaxVSFanPTs'].mean()\n",
    "        return group    \n",
    "\n",
    "def generate_team_features(playerGameLogs, playerFeatureTable, date):\n",
    "    tmp = playerGameLogs['2015-10-27': date]\n",
    "    tmp = tmp.reset_index()\n",
    "    bad_players = playerFeatureTable[playerFeatureTable.Rank=='benchPlayer']['Player_ID']\n",
    "    interest_cols = ['fullName','Player_ID','Team','OpponentTeam','position1','FanPTs','MIN']\n",
    "    tmp = tmp[interest_cols]\n",
    "    tmp = tmp[~tmp['Player_ID'].isin(bad_players)]\n",
    "    \n",
    "    newdf = tmp.copy()\n",
    "    newdf_grouped = newdf.groupby(['Team','OpponentTeam'])\n",
    "        \n",
    "    Newdf = newdf_grouped.apply(aggr_teamVSteam)\n",
    "    Newdf.drop(['fullName','Player_ID','MIN','FanPTs','position1'],inplace=True,axis=1)\n",
    "    Newdf.drop_duplicates(['Team','OpponentTeam'],inplace=True)\n",
    "    \n",
    "    Newdf.drop('OpponentTeam',axis=1,inplace=True)\n",
    "    \n",
    "    Newdf2 = Newdf.copy()\n",
    "    Newdf2_grouped = Newdf2.groupby('Team')\n",
    "    \n",
    "    Newdf_overall = Newdf2_grouped.apply(aggr_team)\n",
    "    Newdf_overall.drop(['TeamStdVSFanPTs','TeamAvgVSFanPTs','TeamMaxVSFanPTs'],inplace=True,axis=1)\n",
    "    Newdf_overall.drop_duplicates('Team',inplace=True)\n",
    "    \n",
    "    return(Newdf_overall)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def drop_y(df):\n",
    "    # list comprehension of the cols that end with '_y'\n",
    "    to_drop = [x for x in df if x.endswith('_y')]\n",
    "    df.drop(to_drop, axis=1, inplace=True)\n",
    "\n",
    "def rename_x(df):\n",
    "    for col in df:\n",
    "        if col.endswith('_x'):\n",
    "            df.rename(columns={col:col.rstrip('_x')}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def clean_trainingdata(data):\n",
    "    data['Rank_dup'] = data['Rank']\n",
    "    data = data[data.Rank!='benchPlayer']\n",
    "    var_to_encode = ['Team','OpponentTeam','position1','HomeGame','Rank','GameMonth']\n",
    "    data = pd.get_dummies(data, columns=var_to_encode)\n",
    "    return(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sklearn import cross_validation, metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_train_test(train_date): #format like'2/10/2016' \n",
    "    train_date_index = pd.date_range(start='11/10/2015', end=train_date, freq='D')\n",
    "    data = pd.DataFrame()\n",
    "    \n",
    "    alldates = allplayerFantasyGameLogs.index\n",
    "    trydates = pd.date_range(start='10/27/2015', end='3/12/2016', freq='D') #need to adjust the end data for a specific training\n",
    "    s = set(alldates)\n",
    "    nodates = [x for x in trydates if x not in s]\n",
    "    \n",
    "    for idx in train_date_index:\n",
    "        tmp_idx = idx+1\n",
    "        if tmp_idx not in nodates and idx not in nodates:\n",
    "            #aggregate the statistics from players -> player-level features\n",
    "            trainLogs = allplayerFantasyGameLogs.ix['2015-10-27':idx]\n",
    "            train_player_df = aggr_stats(idx,trainLogs)   \n",
    "            #next we need to collect the player's next game Fantasy Points.\n",
    "            next_date = idx + 1\n",
    "            tmpLogs = allplayerFantasyGameLogs[['fullName', 'Player_ID','Team','OpponentTeam','HomeGame','FanPTs','GameMonth']].ix[next_date]\n",
    "            tmpLogs.rename(columns={'FanPTs':'NewGameFanPTs'},inplace=True)\n",
    "            #join the tmpLogs and player festure table by Player_ID, which is based on the players on a new game day\n",
    "            newgame_df = pd.merge(tmpLogs,train_player_df,how='inner',on='Player_ID')\n",
    "            drop_y(newgame_df)\n",
    "            rename_x(newgame_df)\n",
    "\n",
    "            #get the team features table \n",
    "            train_team_df = generate_team_features(allplayerFantasyGameLogs, train_player_df, idx)\n",
    "            newgame_df = pd.merge(newgame_df,train_team_df,how='left',on='Team')\n",
    "            data = pd.concat([data,newgame_df],axis=0)\n",
    "    \n",
    "    data = clean_trainingdata(data)\n",
    "    target='NewGameFanPTs'\n",
    "    predictors = [x for x in data.columns if x not in [target]]\n",
    "    \n",
    "    X_train = data[predictors]\n",
    "    y_train = data[target]  \n",
    "    \n",
    "    train_df,test_df,y_train,y_test = cross_validation.train_test_split(X_train,y_train,test_size=0.3,random_state=1)\n",
    "    return(train_df,test_df,y_train,y_test) \n",
    "#actually, the test_df is the validation set to control overfitting for our models. \n",
    "#The \"real\" test_set is only the games on a new day."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "train_df,test_df,y_train,y_test = get_train_test('3/11/2016')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "y_train = pd.DataFrame(y_train,columns=['NewGameFanPTs'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "y_test = pd.DataFrame(y_test,columns=['NewGameFanPTs'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "train_df.to_csv('../Data/train_df_0313.csv',index=False)\n",
    "y_train.to_csv('../Data/y_train_0313.csv',index=False)\n",
    "test_df.to_csv('../Data/valid_df_0313.csv',index=False)\n",
    "y_test.to_csv('../Data/y_valid_0313.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##Prepare the Experiment Test Set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def get_experiment_set(games_list, gamedate): #games_list = ['HOU@GWS','SAC@CLE'], gamedate='3/16/2016' (the day before newgameday for feature table)\n",
    "    OpponentTeam = [x.split('@')[0] for x in games_list]\n",
    "    HomeTeam = [x.split('@')[1] for x in games_list]\n",
    "    teams = HomeTeam + OpponentTeam \n",
    "    matchup_map = {}\n",
    "    for (x,y) in zip(HomeTeam,OpponentTeam):\n",
    "        matchup_map[x]=y\n",
    "        matchup_map[y]=x \n",
    "    \n",
    "    GameMonth = gamedate.split('/')[0]\n",
    "    \n",
    "    #get all the players in the games today\n",
    "    #with open('allplayerFantasyGameLogs_0313.pickle', 'rb') as handle:\n",
    "    #    allplayerFantasyGameLogs = pickle.load(handle)\n",
    "    todayPlayers_df = allplayerFantasyGameLogs[allplayerFantasyGameLogs['Team'].isin(pd.Series(teams))][['fullName','Player_ID','position1','Team']] \n",
    "    todayPlayers_df = todayPlayers_df.drop_duplicates()\n",
    "    \n",
    "    todayPlayers_df['HomeGame'] = todayPlayers_df['Team'].map(lambda x:1 if x in HomeTeam else 0)\n",
    "    todayPlayers_df['OpponentTeam'] = todayPlayers_df['Team'].map(lambda x: matchup_map[x])\n",
    "    todayPlayers_df['GameMonth'] = GameMonth\n",
    "    \n",
    "    #integrate the player feature table\n",
    "    tmptrainLogs = allplayerFantasyGameLogs.ix['2015-10-27':gamedate]\n",
    "    train_player_df = aggr_stats(gamedate,tmptrainLogs)   \n",
    "    \n",
    "    #join the information together\n",
    "    newgame_df = pd.merge(todayPlayers_df,train_player_df,how='inner',on='Player_ID')\n",
    "    drop_y(newgame_df)\n",
    "    rename_x(newgame_df)\n",
    "    \n",
    "    #include the team-level features\n",
    "    train_team_df = generate_team_features(allplayerFantasyGameLogs, train_player_df, gamedate)\n",
    "    newgame_df = pd.merge(newgame_df,train_team_df,how='left',on='Team')\n",
    "    ntest_df = newgame_df[newgame_df.Rank!='benchPlayer']\n",
    "    \n",
    "    #clean up the data frame for modeling. Note that we should fill out the data attributes with 0.\n",
    "    var_to_encode = ['Team','OpponentTeam','position1','HomeGame','Rank','GameMonth']\n",
    "    ntest_df = pd.get_dummies(ntest_df, columns=var_to_encode)\n",
    "    team_set = Set(allplayerFantasyGameLogs.Team.unique())\n",
    "    oppteam_set = Set(allplayerFantasyGameLogs.OpponentTeam.unique())\n",
    "    mon_set = Set(allplayerFantasyGameLogs.GameMonth.unique())\n",
    "    \n",
    "    notin_team_ls = list(team_set - Set(teams))\n",
    "    notin_oppteam_ls = list(oppteam_set - Set(teams))\n",
    "    notin_mon_ls = list(mon_set - Set([int(GameMonth)]))\n",
    "\n",
    "    notin_team_ls = ['Team_'+ele for ele in notin_team_ls]\n",
    "    notin_oppteam_ls = ['OpponentTeam_'+ele for ele in notin_oppteam_ls]\n",
    "    notin_mon_ls = ['GameMonth_'+str(ele) for ele in notin_mon_ls]\n",
    "    names_ls = notin_team_ls+notin_oppteam_ls+notin_mon_ls\n",
    "    \n",
    "    nrow = ntest_df.shape[0]\n",
    "    ncol = len(names_ls)\n",
    "    dummy_df = pd.DataFrame(np.zeros((nrow,ncol)),columns=names_ls)\n",
    "    ntest_df.index = range(nrow)\n",
    "    \n",
    "    ntest_df = pd.concat([ntest_df,dummy_df],axis=1)\n",
    "    \n",
    "    return ntest_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ttdf = get_experiment_set(['CLE@LAC','IND@ATL', 'UTA@SAC', 'MIL@BKN', 'NYK@LAL'], '3/12/2016')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(110, 110)"
      ]
     },
     "execution_count": 235,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ttdf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ttdf.to_csv('../Data/experiment_test_df_0313.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}