GetData.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import libraries\n",
    "import statsapi\n",
    "import requests as re\n",
    "from urllib.request import urlopen\n",
    "import pandas as pd\n",
    "import datetime\n",
    "import numpy as np\n",
    "from tqdm import tqdm\n",
    "import time\n",
    "import more_itertools as mit\n",
    "from bs4 import BeautifulSoup\n",
    "from collections import defaultdict\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.linear_model import LinearRegression\n",
    "import pickle\n",
    "pd.set_option('display.max_columns', 500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Web-scrape headers\n",
    "headers = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.64 Safari/537.11', 'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8'}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get team schedules"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get Schedule\n",
    "def get_schedule(year):\n",
    "    df = pd.DataFrame(statsapi.schedule(start_date='01/01/'+year,end_date='12/31/'+year))\n",
    "\n",
    "    # Get regular season games\n",
    "    r = df[df['game_type']=='R']\n",
    "\n",
    "    # Get final games\n",
    "    r = r[~r['winning_pitcher'].isna()]\n",
    "\n",
    "    # Remove games delayed to another day\n",
    "    index = r['game_id'].value_counts().reset_index()\n",
    "    r=r[~r['game_id'].isin(index[index['game_id'] > 1]['index'])]\n",
    "    r=r[r['doubleheader'] == 'N']\n",
    "    return r"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Remove columns we dont need\n",
    "games = r.drop(columns=['game_datetime','game_type','status','doubleheader','game_num','home_probable_pitcher', 'away_probable_pitcher',\n",
    "       'home_pitcher_note', 'away_pitcher_note', 'away_score', 'home_score',\n",
    "       'current_inning', 'inning_state', 'venue_id', 'venue_name', 'summary',\n",
    "       'winning_team', 'losing_team', 'winning_pitcher', 'losing_pitcher',\n",
    "       'save_pitcher'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get game info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████| 2383/2383 [1:05:45<00:00,  1.66s/it]\n"
     ]
    }
   ],
   "source": [
    "# Get game info for each game\n",
    "info = {'game_id':[], 'HP':[], 'temp':[], 'weather':[], 'mag':[], 'dir':[], 'length':[], 'att':[], 'daytime':[], 'delay':[]}\n",
    "\n",
    "for gameid in tqdm(games['game_id']):\n",
    "    #try:\n",
    "    \n",
    "        info['game_id'].append(gameid)\n",
    "        game = statsapi.boxscore_data(gameid)\n",
    "        \n",
    "        # Get game info wind, att, hp...\n",
    "        infobox = game['gameBoxInfo']\n",
    "\n",
    "        HP = [i['value']  for i in infobox if i['label'] == 'Umpires'][0].split('.')[0][4:]\n",
    "        info['HP'].append(HP)\n",
    "\n",
    "        temp, weather = [i['value']  for i in infobox if i['label'] == 'Weather'][0].split(',')\n",
    "        info['temp'].append(temp.split(' ')[0])\n",
    "        info['weather'].append(weather[1:-1])\n",
    "\n",
    "        wind = [i['value']  for i in infobox if i['label'] == 'Wind']\n",
    "        if len(wind) == 0:\n",
    "            info['mag'].append(np.nan)\n",
    "            info['dir'].append(np.nan)\n",
    "        else:\n",
    "            wind = wind[0]\n",
    "            mag, dire = wind.split(', ')\n",
    "            info['mag'].append(mag)\n",
    "            info['dir'].append(dire)\n",
    "        \n",
    "        gametime = [i['value']  for i in infobox if i['label'] == 'T']\n",
    "        if len(gametime) == 0:\n",
    "            info['length'].append(np.nan)\n",
    "        else:\n",
    "            gametime = gametime[0]\n",
    "            if 'delay' in gametime:\n",
    "                info['delay'].append(1)\n",
    "                hour, m = gametime.split(' ')[0].split(':')\n",
    "\n",
    "            else:\n",
    "                info['delay'].append(0)\n",
    "                hour, m = gametime.split(':')\n",
    "\n",
    "            length = int(hour)* 60 + int(m[:2])\n",
    "            info['length'].append(length)\n",
    "\n",
    "        att = [i['value']  for i in infobox if i['label'] == 'Att']\n",
    "        if len(att) == 0:\n",
    "            info['att'].append(np.nan)\n",
    "        else:\n",
    "            info['att'].append(att[0][:-1].replace(',',''))\n",
    "\n",
    "        daytime = [i['value']  for i in infobox if i['label'] == 'First pitch'][0][:-1]\n",
    "        info['daytime'].append(daytime)\n",
    " \n",
    "        time.sleep(1)\n",
    "    #except IndexError:\n",
    "        #print(gameid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert dictionary to dataframe\n",
    "info = pd.DataFrame(info)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge to get team and pitcher info and save it to save memory\n",
    "games.merge(info, left_on=['game_id'], right_on=['game_id']).to_csv('Data/2012.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "scrolled": true
   },
   "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>game_id</th>\n",
       "      <th>game_date</th>\n",
       "      <th>away_name</th>\n",
       "      <th>home_name</th>\n",
       "      <th>away_id</th>\n",
       "      <th>home_id</th>\n",
       "      <th>HP</th>\n",
       "      <th>temp</th>\n",
       "      <th>weather</th>\n",
       "      <th>mag</th>\n",
       "      <th>dir</th>\n",
       "      <th>length</th>\n",
       "      <th>att</th>\n",
       "      <th>daytime</th>\n",
       "      <th>delay</th>\n",
       "      <th>away_LOB</th>\n",
       "      <th>away_RISP</th>\n",
       "      <th>away_GIDP</th>\n",
       "      <th>away_2outrunners</th>\n",
       "      <th>away_2outrbi</th>\n",
       "      <th>home_LOB</th>\n",
       "      <th>home_RISP</th>\n",
       "      <th>home_GIDP</th>\n",
       "      <th>home_2outrunners</th>\n",
       "      <th>home_2outrbi</th>\n",
       "      <th>away_runs</th>\n",
       "      <th>away_doubles</th>\n",
       "      <th>away_triples</th>\n",
       "      <th>away_homeRuns</th>\n",
       "      <th>away_strikeOuts</th>\n",
       "      <th>away_baseOnBalls</th>\n",
       "      <th>away_hits</th>\n",
       "      <th>away_avg</th>\n",
       "      <th>away_atBats</th>\n",
       "      <th>away_obp</th>\n",
       "      <th>away_slg</th>\n",
       "      <th>away_ops</th>\n",
       "      <th>away_stolenBases</th>\n",
       "      <th>away_rbi</th>\n",
       "      <th>away_leftOnBase</th>\n",
       "      <th>home_runs</th>\n",
       "      <th>home_doubles</th>\n",
       "      <th>home_triples</th>\n",
       "      <th>home_homeRuns</th>\n",
       "      <th>home_strikeOuts</th>\n",
       "      <th>home_baseOnBalls</th>\n",
       "      <th>home_hits</th>\n",
       "      <th>home_avg</th>\n",
       "      <th>home_atBats</th>\n",
       "      <th>home_obp</th>\n",
       "      <th>home_slg</th>\n",
       "      <th>home_ops</th>\n",
       "      <th>home_stolenBases</th>\n",
       "      <th>home_rbi</th>\n",
       "      <th>home_leftOnBase</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>346787</td>\n",
       "      <td>2013-03-31</td>\n",
       "      <td>Texas Rangers</td>\n",
       "      <td>Houston Astros</td>\n",
       "      <td>140</td>\n",
       "      <td>117</td>\n",
       "      <td>Sam Holbrook</td>\n",
       "      <td>73</td>\n",
       "      <td>Roof Closed</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>None.</td>\n",
       "      <td>180</td>\n",
       "      <td>41307</td>\n",
       "      <td>7:08 PM</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>2-for-6</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4-for-6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>0.188</td>\n",
       "      <td>32</td>\n",
       "      <td>0.257</td>\n",
       "      <td>0.188</td>\n",
       "      <td>0.445</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>0.281</td>\n",
       "      <td>32</td>\n",
       "      <td>0.343</td>\n",
       "      <td>0.500</td>\n",
       "      <td>0.843</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>346187</td>\n",
       "      <td>2013-04-01</td>\n",
       "      <td>Miami Marlins</td>\n",
       "      <td>Washington Nationals</td>\n",
       "      <td>146</td>\n",
       "      <td>120</td>\n",
       "      <td>Jeff Kellogg</td>\n",
       "      <td>61</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "      <td>15 mph</td>\n",
       "      <td>Out To RF.</td>\n",
       "      <td>130</td>\n",
       "      <td>45274</td>\n",
       "      <td>1:09 PM</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1-for-4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0-for-4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0.103</td>\n",
       "      <td>29</td>\n",
       "      <td>0.133</td>\n",
       "      <td>0.138</td>\n",
       "      <td>0.271</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0.192</td>\n",
       "      <td>26</td>\n",
       "      <td>0.250</td>\n",
       "      <td>0.423</td>\n",
       "      <td>0.673</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>346755</td>\n",
       "      <td>2013-04-01</td>\n",
       "      <td>Boston Red Sox</td>\n",
       "      <td>New York Yankees</td>\n",
       "      <td>111</td>\n",
       "      <td>147</td>\n",
       "      <td>Ted Barrett</td>\n",
       "      <td>62</td>\n",
       "      <td>Partly Cloudy</td>\n",
       "      <td>17 mph</td>\n",
       "      <td>R To L.</td>\n",
       "      <td>217</td>\n",
       "      <td>49514</td>\n",
       "      <td>1:10 PM</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>6-for-18</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "      <td>2-for-9</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>13</td>\n",
       "      <td>0.325</td>\n",
       "      <td>40</td>\n",
       "      <td>0.438</td>\n",
       "      <td>0.400</td>\n",
       "      <td>0.838</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>26</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>6</td>\n",
       "      <td>0.182</td>\n",
       "      <td>33</td>\n",
       "      <td>0.270</td>\n",
       "      <td>0.212</td>\n",
       "      <td>0.482</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>346189</td>\n",
       "      <td>2013-04-01</td>\n",
       "      <td>San Diego Padres</td>\n",
       "      <td>New York Mets</td>\n",
       "      <td>135</td>\n",
       "      <td>121</td>\n",
       "      <td>Tim McClelland</td>\n",
       "      <td>61</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>18 mph</td>\n",
       "      <td>Out To CF.</td>\n",
       "      <td>181</td>\n",
       "      <td>41053</td>\n",
       "      <td>1:13 PM</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>1-for-7</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>7-for-14</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0.129</td>\n",
       "      <td>31</td>\n",
       "      <td>0.206</td>\n",
       "      <td>0.258</td>\n",
       "      <td>0.464</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>13</td>\n",
       "      <td>0.342</td>\n",
       "      <td>38</td>\n",
       "      <td>0.419</td>\n",
       "      <td>0.474</td>\n",
       "      <td>0.892</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>346184</td>\n",
       "      <td>2013-04-01</td>\n",
       "      <td>Chicago Cubs</td>\n",
       "      <td>Pittsburgh Pirates</td>\n",
       "      <td>112</td>\n",
       "      <td>134</td>\n",
       "      <td>Tom Hallion</td>\n",
       "      <td>41</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>12 mph</td>\n",
       "      <td>Out To CF.</td>\n",
       "      <td>179</td>\n",
       "      <td>39078</td>\n",
       "      <td>1:41 PM</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0-for-8</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>1-for-7</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>0.182</td>\n",
       "      <td>33</td>\n",
       "      <td>0.229</td>\n",
       "      <td>0.333</td>\n",
       "      <td>0.562</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0.097</td>\n",
       "      <td>31</td>\n",
       "      <td>0.176</td>\n",
       "      <td>0.129</td>\n",
       "      <td>0.306</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "      <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",
       "      <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",
       "      <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",
       "      <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",
       "      <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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2364</th>\n",
       "      <td>349168</td>\n",
       "      <td>2013-09-29</td>\n",
       "      <td>San Diego Padres</td>\n",
       "      <td>San Francisco Giants</td>\n",
       "      <td>135</td>\n",
       "      <td>137</td>\n",
       "      <td>Quinn Wolcott</td>\n",
       "      <td>67</td>\n",
       "      <td>Cloudy</td>\n",
       "      <td>9 mph</td>\n",
       "      <td>Out To RF.</td>\n",
       "      <td>197</td>\n",
       "      <td>41495</td>\n",
       "      <td>1:06 PM</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2-for-6</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>5-for-15</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>0.245</td>\n",
       "      <td>33</td>\n",
       "      <td>0.308</td>\n",
       "      <td>0.378</td>\n",
       "      <td>0.686</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>0.260</td>\n",
       "      <td>34</td>\n",
       "      <td>0.320</td>\n",
       "      <td>0.381</td>\n",
       "      <td>0.702</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2365</th>\n",
       "      <td>349162</td>\n",
       "      <td>2013-09-29</td>\n",
       "      <td>Colorado Rockies</td>\n",
       "      <td>Los Angeles Dodgers</td>\n",
       "      <td>115</td>\n",
       "      <td>119</td>\n",
       "      <td>Bob Davidson</td>\n",
       "      <td>85</td>\n",
       "      <td>Sunny</td>\n",
       "      <td>5 mph</td>\n",
       "      <td>Out To CF.</td>\n",
       "      <td>188</td>\n",
       "      <td>52396</td>\n",
       "      <td>1:10 PM</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>4-for-14</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>1-for-9</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>0.270</td>\n",
       "      <td>35</td>\n",
       "      <td>0.323</td>\n",
       "      <td>0.418</td>\n",
       "      <td>0.741</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0.264</td>\n",
       "      <td>31</td>\n",
       "      <td>0.326</td>\n",
       "      <td>0.396</td>\n",
       "      <td>0.722</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2366</th>\n",
       "      <td>349169</td>\n",
       "      <td>2013-09-29</td>\n",
       "      <td>Washington Nationals</td>\n",
       "      <td>Arizona Diamondbacks</td>\n",
       "      <td>120</td>\n",
       "      <td>109</td>\n",
       "      <td>Tripp Gibson</td>\n",
       "      <td>74</td>\n",
       "      <td>Roof Closed</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>None.</td>\n",
       "      <td>149</td>\n",
       "      <td>30390</td>\n",
       "      <td>1:10 PM</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>2-for-5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>3-for-8</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>0.251</td>\n",
       "      <td>35</td>\n",
       "      <td>0.313</td>\n",
       "      <td>0.398</td>\n",
       "      <td>0.710</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0.259</td>\n",
       "      <td>31</td>\n",
       "      <td>0.323</td>\n",
       "      <td>0.391</td>\n",
       "      <td>0.715</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2367</th>\n",
       "      <td>349173</td>\n",
       "      <td>2013-09-29</td>\n",
       "      <td>Oakland Athletics</td>\n",
       "      <td>Seattle Mariners</td>\n",
       "      <td>133</td>\n",
       "      <td>136</td>\n",
       "      <td>Marvin Hudson</td>\n",
       "      <td>56</td>\n",
       "      <td>Roof Closed</td>\n",
       "      <td>0 mph</td>\n",
       "      <td>None.</td>\n",
       "      <td>188</td>\n",
       "      <td>17081</td>\n",
       "      <td>1:10 PM</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>6-for-14</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>0-for-5</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>10</td>\n",
       "      <td>0.254</td>\n",
       "      <td>37</td>\n",
       "      <td>0.327</td>\n",
       "      <td>0.419</td>\n",
       "      <td>0.745</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0.237</td>\n",
       "      <td>32</td>\n",
       "      <td>0.306</td>\n",
       "      <td>0.390</td>\n",
       "      <td>0.695</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2368</th>\n",
       "      <td>385292</td>\n",
       "      <td>2013-09-30</td>\n",
       "      <td>Tampa Bay Rays</td>\n",
       "      <td>Texas Rangers</td>\n",
       "      <td>139</td>\n",
       "      <td>140</td>\n",
       "      <td>Jeff Kellogg</td>\n",
       "      <td>82</td>\n",
       "      <td>Clear</td>\n",
       "      <td>8 mph</td>\n",
       "      <td>In From RF.</td>\n",
       "      <td>188</td>\n",
       "      <td>42796</td>\n",
       "      <td>7:08 PM</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>2-for-6</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2-for-9</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>0.257</td>\n",
       "      <td>32</td>\n",
       "      <td>0.329</td>\n",
       "      <td>0.408</td>\n",
       "      <td>0.737</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0.262</td>\n",
       "      <td>32</td>\n",
       "      <td>0.323</td>\n",
       "      <td>0.412</td>\n",
       "      <td>0.735</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2369 rows × 55 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id   game_date             away_name             home_name  \\\n",
       "0      346787  2013-03-31         Texas Rangers        Houston Astros   \n",
       "1      346187  2013-04-01         Miami Marlins  Washington Nationals   \n",
       "2      346755  2013-04-01        Boston Red Sox      New York Yankees   \n",
       "3      346189  2013-04-01      San Diego Padres         New York Mets   \n",
       "4      346184  2013-04-01          Chicago Cubs    Pittsburgh Pirates   \n",
       "...       ...         ...                   ...                   ...   \n",
       "2364   349168  2013-09-29      San Diego Padres  San Francisco Giants   \n",
       "2365   349162  2013-09-29      Colorado Rockies   Los Angeles Dodgers   \n",
       "2366   349169  2013-09-29  Washington Nationals  Arizona Diamondbacks   \n",
       "2367   349173  2013-09-29     Oakland Athletics      Seattle Mariners   \n",
       "2368   385292  2013-09-30        Tampa Bay Rays         Texas Rangers   \n",
       "\n",
       "      away_id  home_id              HP  temp        weather     mag  \\\n",
       "0         140      117    Sam Holbrook    73    Roof Closed   0 mph   \n",
       "1         146      120    Jeff Kellogg    61  Partly Cloudy  15 mph   \n",
       "2         111      147     Ted Barrett    62  Partly Cloudy  17 mph   \n",
       "3         135      121  Tim McClelland    61         Cloudy  18 mph   \n",
       "4         112      134     Tom Hallion    41         Cloudy  12 mph   \n",
       "...       ...      ...             ...   ...            ...     ...   \n",
       "2364      135      137   Quinn Wolcott    67         Cloudy   9 mph   \n",
       "2365      115      119    Bob Davidson    85          Sunny   5 mph   \n",
       "2366      120      109    Tripp Gibson    74    Roof Closed   0 mph   \n",
       "2367      133      136   Marvin Hudson    56    Roof Closed   0 mph   \n",
       "2368      139      140    Jeff Kellogg    82          Clear   8 mph   \n",
       "\n",
       "              dir  length    att  daytime  delay  away_LOB away_RISP  \\\n",
       "0           None.     180  41307  7:08 PM      0         6   2-for-6   \n",
       "1      Out To RF.     130  45274  1:09 PM      0         3   1-for-4   \n",
       "2         R To L.     217  49514  1:10 PM      0        13  6-for-18   \n",
       "3      Out To CF.     181  41053  1:13 PM      0         6   1-for-7   \n",
       "4      Out To CF.     179  39078  1:41 PM      0         5   0-for-8   \n",
       "...           ...     ...    ...      ...    ...       ...       ...   \n",
       "2364   Out To RF.     197  41495  1:06 PM      0         4   2-for-6   \n",
       "2365   Out To CF.     188  52396  1:10 PM      0        12  4-for-14   \n",
       "2366        None.     149  30390  1:10 PM      0         7   2-for-5   \n",
       "2367        None.     188  17081  1:10 PM      0         7  6-for-14   \n",
       "2368  In From RF.     188  42796  7:08 PM      0         8   2-for-6   \n",
       "\n",
       "      away_GIDP  away_2outrunners  away_2outrbi  home_LOB home_RISP  \\\n",
       "0             0                 3             2         3   4-for-6   \n",
       "1             0                 1             0         4   0-for-4   \n",
       "2             0                 8             6         8   2-for-9   \n",
       "3             0                 3             1         8  7-for-14   \n",
       "4             0                 4             0         6   1-for-7   \n",
       "...         ...               ...           ...       ...       ...   \n",
       "2364          1                 3             0        10  5-for-15   \n",
       "2365          2                 6             0         7   1-for-9   \n",
       "2366          1                 2             0         7   3-for-8   \n",
       "2367          0                 5             2         9   0-for-5   \n",
       "2368          0                 4             3         4   2-for-9   \n",
       "\n",
       "      home_GIDP  home_2outrunners  home_2outrbi  away_runs  away_doubles  \\\n",
       "0             0                 1             6          2             0   \n",
       "1             1                 2             1          0             1   \n",
       "2             1                 3             2          8             1   \n",
       "3             0                 4             9          2             1   \n",
       "4             0                 3             0          3             2   \n",
       "...         ...               ...           ...        ...           ...   \n",
       "2364          1                 4             3          6             2   \n",
       "2365          0                 5             0          2             1   \n",
       "2366          0                 3             1          2             0   \n",
       "2367          0                 4             0          9             5   \n",
       "2368          0                 3             0          5             2   \n",
       "\n",
       "      away_triples  away_homeRuns  away_strikeOuts  away_baseOnBalls  \\\n",
       "0                0              0                7                 3   \n",
       "1                0              0                6                 1   \n",
       "2                1              0                8                 8   \n",
       "3                0              1                6                 2   \n",
       "4                0              1               15                 1   \n",
       "...            ...            ...              ...               ...   \n",
       "2364             1              2                8                 4   \n",
       "2365             0              0               10                 5   \n",
       "2366             1              0                5                 1   \n",
       "2367             0              0                9                 6   \n",
       "2368             0              1                8                 7   \n",
       "\n",
       "      away_hits  away_avg  away_atBats  away_obp  away_slg  away_ops  \\\n",
       "0             6     0.188           32     0.257     0.188     0.445   \n",
       "1             3     0.103           29     0.133     0.138     0.271   \n",
       "2            13     0.325           40     0.438     0.400     0.838   \n",
       "3             4     0.129           31     0.206     0.258     0.464   \n",
       "4             6     0.182           33     0.229     0.333     0.562   \n",
       "...         ...       ...          ...       ...       ...       ...   \n",
       "2364          7     0.245           33     0.308     0.378     0.686   \n",
       "2365         11     0.270           35     0.323     0.418     0.741   \n",
       "2366          8     0.251           35     0.313     0.398     0.710   \n",
       "2367         10     0.254           37     0.327     0.419     0.745   \n",
       "2368          7     0.257           32     0.329     0.408     0.737   \n",
       "\n",
       "      away_stolenBases  away_rbi  away_leftOnBase  home_runs  home_doubles  \\\n",
       "0                    0         2               12          8             0   \n",
       "1                    0         0                8          2             0   \n",
       "2                    0         8               26          2             1   \n",
       "3                    0         2               10         11             2   \n",
       "4                    2         3               12          1             1   \n",
       "...                ...       ...              ...        ...           ...   \n",
       "2364                 0         6                9          7             3   \n",
       "2365                 1         2               30          1             1   \n",
       "2366                 1         2               15          3             0   \n",
       "2367                 0         9               17          0             0   \n",
       "2368                 1         4               13          2             3   \n",
       "\n",
       "      home_triples  home_homeRuns  home_strikeOuts  home_baseOnBalls  \\\n",
       "0                2              1               13                 3   \n",
       "1                0              2                5                 2   \n",
       "2                0              0               10                 4   \n",
       "3                0              1               10                 5   \n",
       "4                0              0               10                 2   \n",
       "...            ...            ...              ...               ...   \n",
       "2364             0              1                8                 5   \n",
       "2365             0              0               13                 4   \n",
       "2366             0              0                4                 1   \n",
       "2367             0              0               10                 4   \n",
       "2368             0              0                4                 1   \n",
       "\n",
       "      home_hits  home_avg  home_atBats  home_obp  home_slg  home_ops  \\\n",
       "0             9     0.281           32     0.343     0.500     0.843   \n",
       "1             5     0.192           26     0.250     0.423     0.673   \n",
       "2             6     0.182           33     0.270     0.212     0.482   \n",
       "3            13     0.342           38     0.419     0.474     0.892   \n",
       "4             3     0.097           31     0.176     0.129     0.306   \n",
       "...         ...       ...          ...       ...       ...       ...   \n",
       "2364         11     0.260           34     0.320     0.381     0.702   \n",
       "2365          5     0.264           31     0.326     0.396     0.722   \n",
       "2366          7     0.259           31     0.323     0.391     0.715   \n",
       "2367          5     0.237           32     0.306     0.390     0.695   \n",
       "2368          7     0.262           32     0.323     0.412     0.735   \n",
       "\n",
       "      home_stolenBases  home_rbi  home_leftOnBase  \n",
       "0                    0         7                7  \n",
       "1                    0         2                8  \n",
       "2                    0         2               21  \n",
       "3                    2        11               14  \n",
       "4                    1         1               15  \n",
       "...                ...       ...              ...  \n",
       "2364                 1         7               18  \n",
       "2365                 0         1               18  \n",
       "2366                 0         3               15  \n",
       "2367                 0         0               19  \n",
       "2368                 1         2               11  \n",
       "\n",
       "[2369 rows x 55 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read file and process it further\n",
    "df = pd.read_csv('Data/2013.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get team abbreviations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Get team abbreviations\n",
    "d = {'teamid':[],'teamabb':[]}\n",
    "for team in df['away_id'].unique():\n",
    "    d['teamabb'].append(statsapi.lookup_team(team)[0]['fileCode'].upper())\n",
    "    d['teamid'].append(team)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save file\n",
    "pd.DataFrame(d).to_csv('Data/teamabbre.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert date to days after 01/01/2013 so we can group them in series\n",
    "df['days'] = df['game_date'].apply(lambda x: (datetime.datetime.strptime(x,'%Y-%m-%d') - datetime.datetime.strptime('2013-01-01','%Y-%m-%d')).days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# prepare the columns of a dataframe containing a series of 3 games as a data row\n",
    "cols1 = df.columns.tolist()\n",
    "cols2 = df.columns.tolist()\n",
    "for i in range (len(cols1)):\n",
    "    cols1[i] += '_1'\n",
    "for i in range (len(cols2)):\n",
    "    cols2[i] += '_2'\n",
    "cols = cols1 + cols2 + df.columns.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Format the games into series of 3 games\n",
    "train = pd.DataFrame()\n",
    "for away in df['away_id'].unique():\n",
    "    temp = df[df['away_id'] == away]\n",
    "    for home in df['home_id'].unique():\n",
    "        temp2 = temp[temp['home_id']==home].sort_values('game_date')\n",
    "        \n",
    "        for series in [list(group) for group in mit.consecutive_groups(temp2['days'].tolist())]:\n",
    "            if len(series) == 3:\n",
    "                thisseries = temp2[temp2['days'].isin(series)].sort_values('game_date')\n",
    "                s = pd.concat([thisseries.iloc[0],thisseries.iloc[1],thisseries.iloc[2]]).to_frame().transpose()\n",
    "                s.columns = cols\n",
    "                train = pd.concat([train, s])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save them as initial training data\n",
    "train.to_csv('train/2014.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get pitching and batting info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# collection of all games from 2012 to 2019\n",
    "df= pd.concat([pd.read_csv('Data/2012.csv'),pd.read_csv('Data/2013.csv'),\n",
    "               pd.read_csv('Data/2014.csv'),pd.read_csv('Data/2015.csv'),\n",
    "               pd.read_csv('Data/2016.csv'),pd.read_csv('Data/2017.csv'),\n",
    "               pd.read_csv('Data/2018.csv'),pd.read_csv('Data/2019.csv')])\n",
    "df = df[['game_id','game_date','away_name','home_name','away_id','home_id','temp','daytime']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# team abbreviations file\n",
    "d = pd.read_csv('Data/teamabbre.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge to get team abbreviations\n",
    "df = df.merge(d,how='left',left_on=['away_id'],right_on=['teamid']).merge(d,how='left',left_on=['home_id'],right_on=['teamid'],suffixes=['_away','_home'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop duplicated team ids \n",
    "df.drop(columns=['away_id','home_id'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "game_id         0\n",
       "game_date       0\n",
       "away_name       0\n",
       "home_name       0\n",
       "temp            0\n",
       "daytime         0\n",
       "teamid_away     0\n",
       "teamabb_away    0\n",
       "teamid_home     0\n",
       "teamabb_home    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Observe the missing values for each column\n",
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>game_id</th>\n",
       "      <th>game_date</th>\n",
       "      <th>away_name</th>\n",
       "      <th>home_name</th>\n",
       "      <th>temp</th>\n",
       "      <th>daytime</th>\n",
       "      <th>teamid_away</th>\n",
       "      <th>teamabb_away</th>\n",
       "      <th>teamid_home</th>\n",
       "      <th>teamabb_home</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>317775</td>\n",
       "      <td>2012-03-28</td>\n",
       "      <td>Seattle Mariners</td>\n",
       "      <td>Oakland Athletics</td>\n",
       "      <td>72</td>\n",
       "      <td>7:09 PM</td>\n",
       "      <td>136</td>\n",
       "      <td>SEA</td>\n",
       "      <td>133</td>\n",
       "      <td>OAK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>318969</td>\n",
       "      <td>2012-03-29</td>\n",
       "      <td>Seattle Mariners</td>\n",
       "      <td>Oakland Athletics</td>\n",
       "      <td>72</td>\n",
       "      <td>6:04 PM</td>\n",
       "      <td>136</td>\n",
       "      <td>SEA</td>\n",
       "      <td>133</td>\n",
       "      <td>OAK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>317733</td>\n",
       "      <td>2012-04-04</td>\n",
       "      <td>St. Louis Cardinals</td>\n",
       "      <td>Miami Marlins</td>\n",
       "      <td>79</td>\n",
       "      <td>7:15 PM</td>\n",
       "      <td>138</td>\n",
       "      <td>STL</td>\n",
       "      <td>146</td>\n",
       "      <td>MIA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>317735</td>\n",
       "      <td>2012-04-05</td>\n",
       "      <td>Boston Red Sox</td>\n",
       "      <td>Detroit Tigers</td>\n",
       "      <td>43</td>\n",
       "      <td>1:10 PM</td>\n",
       "      <td>111</td>\n",
       "      <td>BOS</td>\n",
       "      <td>116</td>\n",
       "      <td>DET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>317734</td>\n",
       "      <td>2012-04-05</td>\n",
       "      <td>Atlanta Braves</td>\n",
       "      <td>New York Mets</td>\n",
       "      <td>53</td>\n",
       "      <td>1:18 PM</td>\n",
       "      <td>144</td>\n",
       "      <td>ATL</td>\n",
       "      <td>121</td>\n",
       "      <td>NYM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4747</th>\n",
       "      <td>349168</td>\n",
       "      <td>2013-09-29</td>\n",
       "      <td>San Diego Padres</td>\n",
       "      <td>San Francisco Giants</td>\n",
       "      <td>67</td>\n",
       "      <td>1:06 PM</td>\n",
       "      <td>135</td>\n",
       "      <td>SD</td>\n",
       "      <td>137</td>\n",
       "      <td>SF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4748</th>\n",
       "      <td>349162</td>\n",
       "      <td>2013-09-29</td>\n",
       "      <td>Colorado Rockies</td>\n",
       "      <td>Los Angeles Dodgers</td>\n",
       "      <td>85</td>\n",
       "      <td>1:10 PM</td>\n",
       "      <td>115</td>\n",
       "      <td>COL</td>\n",
       "      <td>119</td>\n",
       "      <td>LA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4749</th>\n",
       "      <td>349169</td>\n",
       "      <td>2013-09-29</td>\n",
       "      <td>Washington Nationals</td>\n",
       "      <td>Arizona Diamondbacks</td>\n",
       "      <td>74</td>\n",
       "      <td>1:10 PM</td>\n",
       "      <td>120</td>\n",
       "      <td>WAS</td>\n",
       "      <td>109</td>\n",
       "      <td>ARI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4750</th>\n",
       "      <td>349173</td>\n",
       "      <td>2013-09-29</td>\n",
       "      <td>Oakland Athletics</td>\n",
       "      <td>Seattle Mariners</td>\n",
       "      <td>56</td>\n",
       "      <td>1:10 PM</td>\n",
       "      <td>133</td>\n",
       "      <td>OAK</td>\n",
       "      <td>136</td>\n",
       "      <td>SEA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4751</th>\n",
       "      <td>385292</td>\n",
       "      <td>2013-09-30</td>\n",
       "      <td>Tampa Bay Rays</td>\n",
       "      <td>Texas Rangers</td>\n",
       "      <td>82</td>\n",
       "      <td>7:08 PM</td>\n",
       "      <td>139</td>\n",
       "      <td>TB</td>\n",
       "      <td>140</td>\n",
       "      <td>TEX</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4752 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id   game_date             away_name             home_name  temp  \\\n",
       "0      317775  2012-03-28      Seattle Mariners     Oakland Athletics    72   \n",
       "1      318969  2012-03-29      Seattle Mariners     Oakland Athletics    72   \n",
       "2      317733  2012-04-04   St. Louis Cardinals         Miami Marlins    79   \n",
       "3      317735  2012-04-05        Boston Red Sox        Detroit Tigers    43   \n",
       "4      317734  2012-04-05        Atlanta Braves         New York Mets    53   \n",
       "...       ...         ...                   ...                   ...   ...   \n",
       "4747   349168  2013-09-29      San Diego Padres  San Francisco Giants    67   \n",
       "4748   349162  2013-09-29      Colorado Rockies   Los Angeles Dodgers    85   \n",
       "4749   349169  2013-09-29  Washington Nationals  Arizona Diamondbacks    74   \n",
       "4750   349173  2013-09-29     Oakland Athletics      Seattle Mariners    56   \n",
       "4751   385292  2013-09-30        Tampa Bay Rays         Texas Rangers    82   \n",
       "\n",
       "      daytime  teamid_away teamabb_away  teamid_home teamabb_home  \n",
       "0     7:09 PM          136          SEA          133          OAK  \n",
       "1     6:04 PM          136          SEA          133          OAK  \n",
       "2     7:15 PM          138          STL          146          MIA  \n",
       "3     1:10 PM          111          BOS          116          DET  \n",
       "4     1:18 PM          144          ATL          121          NYM  \n",
       "...       ...          ...          ...          ...          ...  \n",
       "4747  1:06 PM          135           SD          137           SF  \n",
       "4748  1:10 PM          115          COL          119           LA  \n",
       "4749  1:10 PM          120          WAS          109          ARI  \n",
       "4750  1:10 PM          133          OAK          136          SEA  \n",
       "4751  7:08 PM          139           TB          140          TEX  \n",
       "\n",
       "[4752 rows x 10 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Websrape yearly team batting data from baseball-reference.com\n",
    "s = defaultdict(list)\n",
    "try:\n",
    "    for team in tqdm(df['teamabb_away'].unique()):\n",
    "        if team == 'WAS':\n",
    "            teamm = 'WSN'\n",
    "        elif team == 'CWS':\n",
    "            teamm = 'CHW'\n",
    "        elif team == 'ANA':\n",
    "            teamm = 'LAA'\n",
    "        else:\n",
    "            teamm = team\n",
    "        for year in range (2014,2020,1):\n",
    "            r = BeautifulSoup(re.get('https://www.baseball-reference.com/teams/'+teamm+'/'+str(year)+'-batting.shtml').content,'html.parser')\n",
    "            for td in r.find_all('tr')[-3].find_all('td'):\n",
    "                s[td['data-stat']].append(td.text)\n",
    "            s['year'].append(year)\n",
    "            s['team'].append(team)\n",
    "            time.sleep(2)\n",
    "except IndexError:\n",
    "    print(team,year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save that data\n",
    "pd.DataFrame(s).to_csv('Data/teambatting.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert dictionary to dataframe\n",
    "s = pd.DataFrame(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract year from date\n",
    "df['year'] = df['game_date'].str.extract(r'(\\d{4})-\\d{2}-\\d{2}').astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge to get yearly team batting info\n",
    "df = df.merge(s,left_on=['year','teamabb_away'],right_on=['year','team']).merge(s,left_on=['year','teamabb_home'],right_on=['year','team'],suffixes=['_away','_home'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████| 4752/4752 [1:46:35<00:00,  1.35s/it]\n"
     ]
    }
   ],
   "source": [
    "# Get both starting away and home pitchers data\n",
    "pa = defaultdict(list)\n",
    "ph = defaultdict(list)\n",
    "for gameid in tqdm(df['game_id'].unique()):\n",
    "    for key, value in statsapi.player_stat_data(statsapi.boxscore_data(gameid)['awayPitchers'][1]['personId'], group='pitching',type='career')['stats'][0]['stats'].items():\n",
    "        pa[key].append(value)\n",
    "        \n",
    "    for key, value in statsapi.player_stat_data(statsapi.boxscore_data(gameid)['homePitchers'][1]['personId'], group='pitching',type='career')['stats'][0]['stats'].items():\n",
    "        ph[key].append(value)\n",
    "        \n",
    "    pa['game_id'].append(gameid)\n",
    "    ph['game_id'].append(gameid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save it\n",
    "pd.DataFrame(pa).to_csv('Data/awaypitchers',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save it\n",
    "pd.DataFrame(ph).to_csv('Data/homepitchers',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████████| 4752/4752 [53:57<00:00,  1.47it/s]\n"
     ]
    }
   ],
   "source": [
    "# Gather the runs data for all games\n",
    "runs = {'gameid':[], 'awayruns':[], 'homeruns':[]}\n",
    "for gameid in tqdm(df['game_id'].unique()):\n",
    "    game = statsapi.boxscore_data(gameid)\n",
    "    runs['awayruns'].append(game['away']['teamStats']['batting'])\n",
    "    runs['homeruns'].append(game['home']['teamStats']['batting'])\n",
    "    runs['gameid'].append(gameid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# convert dictionary to dataframe\n",
    "runs = pd.DataFrame(runs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reformat it \n",
    "runs['awayruns'] = runs['awayruns'].apply(lambda x: x['runs'])\n",
    "runs['homeruns'] = runs['homeruns'].apply(lambda x: x['runs'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# compute the total runs\n",
    "runs['total'] = runs['awayruns'] + runs['homeruns']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# compute the run difference\n",
    "runs['diff'] = runs['awayruns'] - runs['homeruns']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>gameid</th>\n",
       "      <th>awayruns</th>\n",
       "      <th>homeruns</th>\n",
       "      <th>total</th>\n",
       "      <th>diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>317775</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>318969</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>-3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>317733</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>317735</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>317734</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4747</th>\n",
       "      <td>349168</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>13</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4748</th>\n",
       "      <td>349162</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4749</th>\n",
       "      <td>349169</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4750</th>\n",
       "      <td>349173</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4751</th>\n",
       "      <td>385292</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4752 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      gameid  awayruns  homeruns  total  diff\n",
       "0     317775         3         1      4     2\n",
       "1     318969         1         4      5    -3\n",
       "2     317733         4         1      5     3\n",
       "3     317735         2         3      5    -1\n",
       "4     317734         0         1      1    -1\n",
       "...      ...       ...       ...    ...   ...\n",
       "4747  349168         6         7     13    -1\n",
       "4748  349162         2         1      3     1\n",
       "4749  349169         2         3      5    -1\n",
       "4750  349173         9         0      9     9\n",
       "4751  385292         5         2      7     3\n",
       "\n",
       "[4752 rows x 5 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "runs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save it\n",
    "runs.to_csv('Data/runs.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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}