Mission193Solutions.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "import csv\n",
    "\n",
    "pd.set_option('max_columns', 180)\n",
    "pd.set_option('max_rows', 200000)\n",
    "pd.set_option('max_colwidth', 5000)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Getting to Know the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(171907, 161)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_game</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>v_name</th>\n",
       "      <th>v_league</th>\n",
       "      <th>v_game_number</th>\n",
       "      <th>h_name</th>\n",
       "      <th>h_league</th>\n",
       "      <th>h_game_number</th>\n",
       "      <th>v_score</th>\n",
       "      <th>h_score</th>\n",
       "      <th>length_outs</th>\n",
       "      <th>day_night</th>\n",
       "      <th>completion</th>\n",
       "      <th>forefeit</th>\n",
       "      <th>protest</th>\n",
       "      <th>park_id</th>\n",
       "      <th>attendance</th>\n",
       "      <th>length_minutes</th>\n",
       "      <th>v_line_score</th>\n",
       "      <th>h_line_score</th>\n",
       "      <th>v_at_bats</th>\n",
       "      <th>v_hits</th>\n",
       "      <th>v_doubles</th>\n",
       "      <th>v_triples</th>\n",
       "      <th>v_homeruns</th>\n",
       "      <th>v_rbi</th>\n",
       "      <th>v_sacrifice_hits</th>\n",
       "      <th>v_sacrifice_flies</th>\n",
       "      <th>v_hit_by_pitch</th>\n",
       "      <th>v_walks</th>\n",
       "      <th>v_intentional_walks</th>\n",
       "      <th>v_strikeouts</th>\n",
       "      <th>v_stolen_bases</th>\n",
       "      <th>v_caught_stealing</th>\n",
       "      <th>v_grounded_into_double</th>\n",
       "      <th>v_first_catcher_interference</th>\n",
       "      <th>v_left_on_base</th>\n",
       "      <th>v_pitchers_used</th>\n",
       "      <th>v_individual_earned_runs</th>\n",
       "      <th>v_team_earned_runs</th>\n",
       "      <th>v_wild_pitches</th>\n",
       "      <th>v_balks</th>\n",
       "      <th>v_putouts</th>\n",
       "      <th>v_assists</th>\n",
       "      <th>v_errors</th>\n",
       "      <th>v_passed_balls</th>\n",
       "      <th>v_double_plays</th>\n",
       "      <th>v_triple_plays</th>\n",
       "      <th>h_at_bats</th>\n",
       "      <th>h_hits</th>\n",
       "      <th>h_doubles</th>\n",
       "      <th>h_triples</th>\n",
       "      <th>h_homeruns</th>\n",
       "      <th>h_rbi</th>\n",
       "      <th>h_sacrifice_hits</th>\n",
       "      <th>h_sacrifice_flies</th>\n",
       "      <th>h_hit_by_pitch</th>\n",
       "      <th>h_walks</th>\n",
       "      <th>h_intentional_walks</th>\n",
       "      <th>h_strikeouts</th>\n",
       "      <th>h_stolen_bases</th>\n",
       "      <th>h_caught_stealing</th>\n",
       "      <th>h_grounded_into_double</th>\n",
       "      <th>h_first_catcher_interference</th>\n",
       "      <th>h_left_on_base</th>\n",
       "      <th>h_pitchers_used</th>\n",
       "      <th>h_individual_earned_runs</th>\n",
       "      <th>h_team_earned_runs</th>\n",
       "      <th>h_wild_pitches</th>\n",
       "      <th>h_balks</th>\n",
       "      <th>h_putouts</th>\n",
       "      <th>h_assists</th>\n",
       "      <th>h_errors</th>\n",
       "      <th>h_passed_balls</th>\n",
       "      <th>h_double_plays</th>\n",
       "      <th>h_triple_plays</th>\n",
       "      <th>hp_umpire_id</th>\n",
       "      <th>hp_umpire_name</th>\n",
       "      <th>1b_umpire_id</th>\n",
       "      <th>1b_umpire_name</th>\n",
       "      <th>2b_umpire_id</th>\n",
       "      <th>2b_umpire_name</th>\n",
       "      <th>3b_umpire_id</th>\n",
       "      <th>3b_umpire_name</th>\n",
       "      <th>lf_umpire_id</th>\n",
       "      <th>lf_umpire_name</th>\n",
       "      <th>rf_umpire_id</th>\n",
       "      <th>rf_umpire_name</th>\n",
       "      <th>v_manager_id</th>\n",
       "      <th>v_manager_name</th>\n",
       "      <th>h_manager_id</th>\n",
       "      <th>h_manager_name</th>\n",
       "      <th>winning_pitcher_id</th>\n",
       "      <th>winning_pitcher_name</th>\n",
       "      <th>losing_pitcher_id</th>\n",
       "      <th>losing_pitcher_name</th>\n",
       "      <th>saving_pitcher_id</th>\n",
       "      <th>saving_pitcher_name</th>\n",
       "      <th>winning_rbi_batter_id</th>\n",
       "      <th>winning_rbi_batter_id_name</th>\n",
       "      <th>v_starting_pitcher_id</th>\n",
       "      <th>v_starting_pitcher_name</th>\n",
       "      <th>h_starting_pitcher_id</th>\n",
       "      <th>h_starting_pitcher_name</th>\n",
       "      <th>v_player_1_id</th>\n",
       "      <th>v_player_1_name</th>\n",
       "      <th>v_player_1_def_pos</th>\n",
       "      <th>v_player_2_id</th>\n",
       "      <th>v_player_2_name</th>\n",
       "      <th>v_player_2_def_pos</th>\n",
       "      <th>v_player_3_id</th>\n",
       "      <th>v_player_3_name</th>\n",
       "      <th>v_player_3_def_pos</th>\n",
       "      <th>v_player_4_id</th>\n",
       "      <th>v_player_4_name</th>\n",
       "      <th>v_player_4_def_pos</th>\n",
       "      <th>v_player_5_id</th>\n",
       "      <th>v_player_5_name</th>\n",
       "      <th>v_player_5_def_pos</th>\n",
       "      <th>v_player_6_id</th>\n",
       "      <th>v_player_6_name</th>\n",
       "      <th>v_player_6_def_pos</th>\n",
       "      <th>v_player_7_id</th>\n",
       "      <th>v_player_7_name</th>\n",
       "      <th>v_player_7_def_pos</th>\n",
       "      <th>v_player_8_id</th>\n",
       "      <th>v_player_8_name</th>\n",
       "      <th>v_player_8_def_pos</th>\n",
       "      <th>v_player_9_id</th>\n",
       "      <th>v_player_9_name</th>\n",
       "      <th>v_player_9_def_pos</th>\n",
       "      <th>h_player_1_id</th>\n",
       "      <th>h_player_1_name</th>\n",
       "      <th>h_player_1_def_pos</th>\n",
       "      <th>h_player_2_id</th>\n",
       "      <th>h_player_2_name</th>\n",
       "      <th>h_player_2_def_pos</th>\n",
       "      <th>h_player_3_id</th>\n",
       "      <th>h_player_3_name</th>\n",
       "      <th>h_player_3_def_pos</th>\n",
       "      <th>h_player_4_id</th>\n",
       "      <th>h_player_4_name</th>\n",
       "      <th>h_player_4_def_pos</th>\n",
       "      <th>h_player_5_id</th>\n",
       "      <th>h_player_5_name</th>\n",
       "      <th>h_player_5_def_pos</th>\n",
       "      <th>h_player_6_id</th>\n",
       "      <th>h_player_6_name</th>\n",
       "      <th>h_player_6_def_pos</th>\n",
       "      <th>h_player_7_id</th>\n",
       "      <th>h_player_7_name</th>\n",
       "      <th>h_player_7_def_pos</th>\n",
       "      <th>h_player_8_id</th>\n",
       "      <th>h_player_8_name</th>\n",
       "      <th>h_player_8_def_pos</th>\n",
       "      <th>h_player_9_id</th>\n",
       "      <th>h_player_9_name</th>\n",
       "      <th>h_player_9_def_pos</th>\n",
       "      <th>additional_info</th>\n",
       "      <th>acquisition_info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18710504</td>\n",
       "      <td>0</td>\n",
       "      <td>Thu</td>\n",
       "      <td>CL1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>FW1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>54.0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FOR01</td>\n",
       "      <td>200.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>000000000</td>\n",
       "      <td>010010000</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>boakj901</td>\n",
       "      <td>John Boake</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>paboc101</td>\n",
       "      <td>Charlie Pabor</td>\n",
       "      <td>lennb101</td>\n",
       "      <td>Bill Lennon</td>\n",
       "      <td>mathb101</td>\n",
       "      <td>Bobby Mathews</td>\n",
       "      <td>prata101</td>\n",
       "      <td>Al Pratt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>prata101</td>\n",
       "      <td>Al Pratt</td>\n",
       "      <td>mathb101</td>\n",
       "      <td>Bobby Mathews</td>\n",
       "      <td>whitd102</td>\n",
       "      <td>Deacon White</td>\n",
       "      <td>2.0</td>\n",
       "      <td>kimbg101</td>\n",
       "      <td>Gene Kimball</td>\n",
       "      <td>4.0</td>\n",
       "      <td>paboc101</td>\n",
       "      <td>Charlie Pabor</td>\n",
       "      <td>7.0</td>\n",
       "      <td>allia101</td>\n",
       "      <td>Art Allison</td>\n",
       "      <td>8.0</td>\n",
       "      <td>white104</td>\n",
       "      <td>Elmer White</td>\n",
       "      <td>9.0</td>\n",
       "      <td>prata101</td>\n",
       "      <td>Al Pratt</td>\n",
       "      <td>1.0</td>\n",
       "      <td>sutte101</td>\n",
       "      <td>Ezra Sutton</td>\n",
       "      <td>5.0</td>\n",
       "      <td>carlj102</td>\n",
       "      <td>Jim Carleton</td>\n",
       "      <td>3.0</td>\n",
       "      <td>bassj101</td>\n",
       "      <td>John Bass</td>\n",
       "      <td>6.0</td>\n",
       "      <td>selmf101</td>\n",
       "      <td>Frank Sellman</td>\n",
       "      <td>5.0</td>\n",
       "      <td>mathb101</td>\n",
       "      <td>Bobby Mathews</td>\n",
       "      <td>1.0</td>\n",
       "      <td>foraj101</td>\n",
       "      <td>Jim Foran</td>\n",
       "      <td>3.0</td>\n",
       "      <td>goldw101</td>\n",
       "      <td>Wally Goldsmith</td>\n",
       "      <td>6.0</td>\n",
       "      <td>lennb101</td>\n",
       "      <td>Bill Lennon</td>\n",
       "      <td>2.0</td>\n",
       "      <td>caret101</td>\n",
       "      <td>Tom Carey</td>\n",
       "      <td>4.0</td>\n",
       "      <td>mince101</td>\n",
       "      <td>Ed Mincher</td>\n",
       "      <td>7.0</td>\n",
       "      <td>mcdej101</td>\n",
       "      <td>James McDermott</td>\n",
       "      <td>8.0</td>\n",
       "      <td>kellb105</td>\n",
       "      <td>Bill Kelly</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18710505</td>\n",
       "      <td>0</td>\n",
       "      <td>Fri</td>\n",
       "      <td>BS1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>WS3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>18</td>\n",
       "      <td>54.0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WAS01</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>107000435</td>\n",
       "      <td>640113030</td>\n",
       "      <td>41.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>dobsh901</td>\n",
       "      <td>Henry Dobson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>wrigh101</td>\n",
       "      <td>Harry Wright</td>\n",
       "      <td>younn801</td>\n",
       "      <td>Nick Young</td>\n",
       "      <td>spala101</td>\n",
       "      <td>Al Spalding</td>\n",
       "      <td>braia102</td>\n",
       "      <td>Asa Brainard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>spala101</td>\n",
       "      <td>Al Spalding</td>\n",
       "      <td>braia102</td>\n",
       "      <td>Asa Brainard</td>\n",
       "      <td>wrigg101</td>\n",
       "      <td>George Wright</td>\n",
       "      <td>6.0</td>\n",
       "      <td>barnr102</td>\n",
       "      <td>Ross Barnes</td>\n",
       "      <td>4.0</td>\n",
       "      <td>birdd102</td>\n",
       "      <td>Dave Birdsall</td>\n",
       "      <td>9.0</td>\n",
       "      <td>mcvec101</td>\n",
       "      <td>Cal McVey</td>\n",
       "      <td>2.0</td>\n",
       "      <td>wrigh101</td>\n",
       "      <td>Harry Wright</td>\n",
       "      <td>8.0</td>\n",
       "      <td>goulc101</td>\n",
       "      <td>Charlie Gould</td>\n",
       "      <td>3.0</td>\n",
       "      <td>schah101</td>\n",
       "      <td>Harry Schafer</td>\n",
       "      <td>5.0</td>\n",
       "      <td>conef101</td>\n",
       "      <td>Fred Cone</td>\n",
       "      <td>7.0</td>\n",
       "      <td>spala101</td>\n",
       "      <td>Al Spalding</td>\n",
       "      <td>1.0</td>\n",
       "      <td>watef102</td>\n",
       "      <td>Fred Waterman</td>\n",
       "      <td>5.0</td>\n",
       "      <td>forcd101</td>\n",
       "      <td>Davy Force</td>\n",
       "      <td>6.0</td>\n",
       "      <td>mille105</td>\n",
       "      <td>Everett Mills</td>\n",
       "      <td>3.0</td>\n",
       "      <td>allid101</td>\n",
       "      <td>Doug Allison</td>\n",
       "      <td>2.0</td>\n",
       "      <td>hallg101</td>\n",
       "      <td>George Hall</td>\n",
       "      <td>7.0</td>\n",
       "      <td>leona101</td>\n",
       "      <td>Andy Leonard</td>\n",
       "      <td>4.0</td>\n",
       "      <td>braia102</td>\n",
       "      <td>Asa Brainard</td>\n",
       "      <td>1.0</td>\n",
       "      <td>burrh101</td>\n",
       "      <td>Henry Burroughs</td>\n",
       "      <td>9.0</td>\n",
       "      <td>berth101</td>\n",
       "      <td>Henry Berthrong</td>\n",
       "      <td>8.0</td>\n",
       "      <td>HTBF</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18710506</td>\n",
       "      <td>0</td>\n",
       "      <td>Sat</td>\n",
       "      <td>CL1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>RC1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>54.0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>RCK01</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>610020003</td>\n",
       "      <td>010020100</td>\n",
       "      <td>49.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>mawnj901</td>\n",
       "      <td>J.H. Manny</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>paboc101</td>\n",
       "      <td>Charlie Pabor</td>\n",
       "      <td>hasts101</td>\n",
       "      <td>Scott Hastings</td>\n",
       "      <td>prata101</td>\n",
       "      <td>Al Pratt</td>\n",
       "      <td>fishc102</td>\n",
       "      <td>Cherokee Fisher</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>prata101</td>\n",
       "      <td>Al Pratt</td>\n",
       "      <td>fishc102</td>\n",
       "      <td>Cherokee Fisher</td>\n",
       "      <td>whitd102</td>\n",
       "      <td>Deacon White</td>\n",
       "      <td>2.0</td>\n",
       "      <td>kimbg101</td>\n",
       "      <td>Gene Kimball</td>\n",
       "      <td>4.0</td>\n",
       "      <td>paboc101</td>\n",
       "      <td>Charlie Pabor</td>\n",
       "      <td>7.0</td>\n",
       "      <td>allia101</td>\n",
       "      <td>Art Allison</td>\n",
       "      <td>8.0</td>\n",
       "      <td>white104</td>\n",
       "      <td>Elmer White</td>\n",
       "      <td>9.0</td>\n",
       "      <td>prata101</td>\n",
       "      <td>Al Pratt</td>\n",
       "      <td>1.0</td>\n",
       "      <td>sutte101</td>\n",
       "      <td>Ezra Sutton</td>\n",
       "      <td>5.0</td>\n",
       "      <td>carlj102</td>\n",
       "      <td>Jim Carleton</td>\n",
       "      <td>3.0</td>\n",
       "      <td>bassj101</td>\n",
       "      <td>John Bass</td>\n",
       "      <td>6.0</td>\n",
       "      <td>mackd101</td>\n",
       "      <td>Denny Mack</td>\n",
       "      <td>3.0</td>\n",
       "      <td>addyb101</td>\n",
       "      <td>Bob Addy</td>\n",
       "      <td>4.0</td>\n",
       "      <td>fishc102</td>\n",
       "      <td>Cherokee Fisher</td>\n",
       "      <td>1.0</td>\n",
       "      <td>hasts101</td>\n",
       "      <td>Scott Hastings</td>\n",
       "      <td>8.0</td>\n",
       "      <td>ham-r101</td>\n",
       "      <td>Ralph Ham</td>\n",
       "      <td>5.0</td>\n",
       "      <td>ansoc101</td>\n",
       "      <td>Cap Anson</td>\n",
       "      <td>2.0</td>\n",
       "      <td>sagep101</td>\n",
       "      <td>Pony Sager</td>\n",
       "      <td>6.0</td>\n",
       "      <td>birdg101</td>\n",
       "      <td>George Bird</td>\n",
       "      <td>7.0</td>\n",
       "      <td>stirg101</td>\n",
       "      <td>Gat Stires</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18710508</td>\n",
       "      <td>0</td>\n",
       "      <td>Mon</td>\n",
       "      <td>CL1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>CH1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>54.0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHI01</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>101403111</td>\n",
       "      <td>077000000</td>\n",
       "      <td>46.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>willg901</td>\n",
       "      <td>Gardner Willard</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>paboc101</td>\n",
       "      <td>Charlie Pabor</td>\n",
       "      <td>woodj106</td>\n",
       "      <td>Jimmy Wood</td>\n",
       "      <td>zettg101</td>\n",
       "      <td>George Zettlein</td>\n",
       "      <td>prata101</td>\n",
       "      <td>Al Pratt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>prata101</td>\n",
       "      <td>Al Pratt</td>\n",
       "      <td>zettg101</td>\n",
       "      <td>George Zettlein</td>\n",
       "      <td>whitd102</td>\n",
       "      <td>Deacon White</td>\n",
       "      <td>2.0</td>\n",
       "      <td>kimbg101</td>\n",
       "      <td>Gene Kimball</td>\n",
       "      <td>4.0</td>\n",
       "      <td>paboc101</td>\n",
       "      <td>Charlie Pabor</td>\n",
       "      <td>7.0</td>\n",
       "      <td>allia101</td>\n",
       "      <td>Art Allison</td>\n",
       "      <td>8.0</td>\n",
       "      <td>white104</td>\n",
       "      <td>Elmer White</td>\n",
       "      <td>9.0</td>\n",
       "      <td>prata101</td>\n",
       "      <td>Al Pratt</td>\n",
       "      <td>1.0</td>\n",
       "      <td>sutte101</td>\n",
       "      <td>Ezra Sutton</td>\n",
       "      <td>5.0</td>\n",
       "      <td>carlj102</td>\n",
       "      <td>Jim Carleton</td>\n",
       "      <td>3.0</td>\n",
       "      <td>bassj101</td>\n",
       "      <td>John Bass</td>\n",
       "      <td>6.0</td>\n",
       "      <td>mcatb101</td>\n",
       "      <td>Bub McAtee</td>\n",
       "      <td>3.0</td>\n",
       "      <td>kingm101</td>\n",
       "      <td>Marshall King</td>\n",
       "      <td>8.0</td>\n",
       "      <td>hodec101</td>\n",
       "      <td>Charlie Hodes</td>\n",
       "      <td>2.0</td>\n",
       "      <td>woodj106</td>\n",
       "      <td>Jimmy Wood</td>\n",
       "      <td>4.0</td>\n",
       "      <td>simmj101</td>\n",
       "      <td>Joe Simmons</td>\n",
       "      <td>9.0</td>\n",
       "      <td>folet101</td>\n",
       "      <td>Tom Foley</td>\n",
       "      <td>7.0</td>\n",
       "      <td>duffe101</td>\n",
       "      <td>Ed Duffy</td>\n",
       "      <td>6.0</td>\n",
       "      <td>pinke101</td>\n",
       "      <td>Ed Pinkham</td>\n",
       "      <td>5.0</td>\n",
       "      <td>zettg101</td>\n",
       "      <td>George Zettlein</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18710509</td>\n",
       "      <td>0</td>\n",
       "      <td>Tue</td>\n",
       "      <td>BS1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>TRO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>54.0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRO01</td>\n",
       "      <td>3250.0</td>\n",
       "      <td>145.0</td>\n",
       "      <td>000002232</td>\n",
       "      <td>101003000</td>\n",
       "      <td>46.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>leroi901</td>\n",
       "      <td>Isaac Leroy</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>wrigh101</td>\n",
       "      <td>Harry Wright</td>\n",
       "      <td>pikel101</td>\n",
       "      <td>Lip Pike</td>\n",
       "      <td>spala101</td>\n",
       "      <td>Al Spalding</td>\n",
       "      <td>mcmuj101</td>\n",
       "      <td>John McMullin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>spala101</td>\n",
       "      <td>Al Spalding</td>\n",
       "      <td>mcmuj101</td>\n",
       "      <td>John McMullin</td>\n",
       "      <td>wrigg101</td>\n",
       "      <td>George Wright</td>\n",
       "      <td>6.0</td>\n",
       "      <td>barnr102</td>\n",
       "      <td>Ross Barnes</td>\n",
       "      <td>4.0</td>\n",
       "      <td>birdd102</td>\n",
       "      <td>Dave Birdsall</td>\n",
       "      <td>9.0</td>\n",
       "      <td>mcvec101</td>\n",
       "      <td>Cal McVey</td>\n",
       "      <td>2.0</td>\n",
       "      <td>wrigh101</td>\n",
       "      <td>Harry Wright</td>\n",
       "      <td>8.0</td>\n",
       "      <td>goulc101</td>\n",
       "      <td>Charlie Gould</td>\n",
       "      <td>3.0</td>\n",
       "      <td>schah101</td>\n",
       "      <td>Harry Schafer</td>\n",
       "      <td>5.0</td>\n",
       "      <td>conef101</td>\n",
       "      <td>Fred Cone</td>\n",
       "      <td>7.0</td>\n",
       "      <td>spala101</td>\n",
       "      <td>Al Spalding</td>\n",
       "      <td>1.0</td>\n",
       "      <td>flync101</td>\n",
       "      <td>Clipper Flynn</td>\n",
       "      <td>9.0</td>\n",
       "      <td>mcgem101</td>\n",
       "      <td>Mike McGeary</td>\n",
       "      <td>2.0</td>\n",
       "      <td>yorkt101</td>\n",
       "      <td>Tom York</td>\n",
       "      <td>8.0</td>\n",
       "      <td>mcmuj101</td>\n",
       "      <td>John McMullin</td>\n",
       "      <td>1.0</td>\n",
       "      <td>kings101</td>\n",
       "      <td>Steve King</td>\n",
       "      <td>7.0</td>\n",
       "      <td>beave101</td>\n",
       "      <td>Edward Beavens</td>\n",
       "      <td>4.0</td>\n",
       "      <td>bells101</td>\n",
       "      <td>Steve Bellan</td>\n",
       "      <td>5.0</td>\n",
       "      <td>pikel101</td>\n",
       "      <td>Lip Pike</td>\n",
       "      <td>3.0</td>\n",
       "      <td>cravb101</td>\n",
       "      <td>Bill Craver</td>\n",
       "      <td>6.0</td>\n",
       "      <td>HTBF</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date  number_of_game day_of_week v_name v_league  v_game_number h_name  \\\n",
       "0  18710504               0         Thu    CL1      NaN              1    FW1   \n",
       "1  18710505               0         Fri    BS1      NaN              1    WS3   \n",
       "2  18710506               0         Sat    CL1      NaN              2    RC1   \n",
       "3  18710508               0         Mon    CL1      NaN              3    CH1   \n",
       "4  18710509               0         Tue    BS1      NaN              2    TRO   \n",
       "\n",
       "  h_league  h_game_number  v_score  h_score  length_outs day_night completion  \\\n",
       "0      NaN              1        0        2         54.0         D        NaN   \n",
       "1      NaN              1       20       18         54.0         D        NaN   \n",
       "2      NaN              1       12        4         54.0         D        NaN   \n",
       "3      NaN              1       12       14         54.0         D        NaN   \n",
       "4      NaN              1        9        5         54.0         D        NaN   \n",
       "\n",
       "  forefeit protest park_id  attendance  length_minutes v_line_score  \\\n",
       "0      NaN     NaN   FOR01       200.0           120.0    000000000   \n",
       "1      NaN     NaN   WAS01      5000.0           145.0    107000435   \n",
       "2      NaN     NaN   RCK01      1000.0           140.0    610020003   \n",
       "3      NaN     NaN   CHI01      5000.0           150.0    101403111   \n",
       "4      NaN     NaN   TRO01      3250.0           145.0    000002232   \n",
       "\n",
       "  h_line_score  v_at_bats  v_hits  v_doubles  v_triples  v_homeruns  v_rbi  \\\n",
       "0    010010000       30.0     4.0        1.0        0.0         0.0    0.0   \n",
       "1    640113030       41.0    13.0        1.0        2.0         0.0   13.0   \n",
       "2    010020100       49.0    11.0        1.0        1.0         0.0    8.0   \n",
       "3    077000000       46.0    15.0        2.0        1.0         2.0   10.0   \n",
       "4    101003000       46.0    17.0        4.0        1.0         0.0    6.0   \n",
       "\n",
       "   v_sacrifice_hits  v_sacrifice_flies  v_hit_by_pitch  v_walks  \\\n",
       "0               0.0                0.0             0.0      1.0   \n",
       "1               0.0                0.0             0.0     18.0   \n",
       "2               0.0                0.0             0.0      0.0   \n",
       "3               0.0                0.0             0.0      0.0   \n",
       "4               0.0                0.0             0.0      2.0   \n",
       "\n",
       "   v_intentional_walks  v_strikeouts  v_stolen_bases  v_caught_stealing  \\\n",
       "0                  NaN           6.0             1.0                NaN   \n",
       "1                  NaN           5.0             3.0                NaN   \n",
       "2                  NaN           1.0             0.0                NaN   \n",
       "3                  NaN           1.0             0.0                NaN   \n",
       "4                  NaN           0.0             1.0                NaN   \n",
       "\n",
       "   v_grounded_into_double  v_first_catcher_interference  v_left_on_base  \\\n",
       "0                    -1.0                           NaN             4.0   \n",
       "1                    -1.0                           NaN            12.0   \n",
       "2                    -1.0                           NaN            10.0   \n",
       "3                    -1.0                           NaN             7.0   \n",
       "4                    -1.0                           NaN            12.0   \n",
       "\n",
       "   v_pitchers_used  v_individual_earned_runs  v_team_earned_runs  \\\n",
       "0              1.0                       1.0                 1.0   \n",
       "1              1.0                       6.0                 6.0   \n",
       "2              1.0                       0.0                 0.0   \n",
       "3              1.0                       6.0                 6.0   \n",
       "4              1.0                       2.0                 2.0   \n",
       "\n",
       "   v_wild_pitches  v_balks  v_putouts  v_assists  v_errors  v_passed_balls  \\\n",
       "0             0.0      0.0       27.0        9.0       0.0             3.0   \n",
       "1             1.0      0.0       27.0       13.0      10.0             1.0   \n",
       "2             2.0      0.0       27.0       12.0       8.0             5.0   \n",
       "3             0.0      0.0       27.0       15.0      11.0             6.0   \n",
       "4             0.0      0.0       27.0       12.0       5.0             0.0   \n",
       "\n",
       "   v_double_plays  v_triple_plays  h_at_bats  h_hits  h_doubles  h_triples  \\\n",
       "0             0.0             0.0       31.0     4.0        1.0        0.0   \n",
       "1             2.0             0.0       49.0    14.0        2.0        0.0   \n",
       "2             0.0             0.0       36.0     7.0        2.0        1.0   \n",
       "3             0.0             0.0       43.0    11.0        2.0        0.0   \n",
       "4             1.0             0.0       36.0     9.0        0.0        0.0   \n",
       "\n",
       "   h_homeruns  h_rbi  h_sacrifice_hits  h_sacrifice_flies  h_hit_by_pitch  \\\n",
       "0         0.0    2.0               0.0                0.0             0.0   \n",
       "1         0.0   11.0               0.0                0.0             0.0   \n",
       "2         0.0    2.0               0.0                0.0             0.0   \n",
       "3         0.0    8.0               0.0                0.0             0.0   \n",
       "4         0.0    2.0               0.0                0.0             0.0   \n",
       "\n",
       "   h_walks  h_intentional_walks  h_strikeouts  h_stolen_bases  \\\n",
       "0      1.0                  NaN           0.0             0.0   \n",
       "1     10.0                  NaN           2.0             1.0   \n",
       "2      0.0                  NaN           3.0             5.0   \n",
       "3      4.0                  NaN           2.0             1.0   \n",
       "4      3.0                  NaN           0.0             2.0   \n",
       "\n",
       "   h_caught_stealing  h_grounded_into_double  h_first_catcher_interference  \\\n",
       "0                NaN                    -1.0                           NaN   \n",
       "1                NaN                    -1.0                           NaN   \n",
       "2                NaN                    -1.0                           NaN   \n",
       "3                NaN                    -1.0                           NaN   \n",
       "4                NaN                    -1.0                           NaN   \n",
       "\n",
       "   h_left_on_base  h_pitchers_used  h_individual_earned_runs  \\\n",
       "0             3.0              1.0                       0.0   \n",
       "1            14.0              1.0                       7.0   \n",
       "2             5.0              1.0                       3.0   \n",
       "3             6.0              1.0                       4.0   \n",
       "4             7.0              1.0                       3.0   \n",
       "\n",
       "   h_team_earned_runs  h_wild_pitches  h_balks  h_putouts  h_assists  \\\n",
       "0                 0.0             0.0      0.0       27.0        3.0   \n",
       "1                 7.0             0.0      0.0       27.0       20.0   \n",
       "2                 3.0             1.0      0.0       27.0       12.0   \n",
       "3                 4.0             0.0      0.0       27.0       14.0   \n",
       "4                 3.0             1.0      0.0       27.0       11.0   \n",
       "\n",
       "   h_errors  h_passed_balls  h_double_plays  h_triple_plays hp_umpire_id  \\\n",
       "0       3.0             1.0             1.0             0.0     boakj901   \n",
       "1      10.0             2.0             3.0             0.0     dobsh901   \n",
       "2      13.0             3.0             0.0             0.0     mawnj901   \n",
       "3       7.0             2.0             0.0             0.0     willg901   \n",
       "4       7.0             3.0             0.0             0.0     leroi901   \n",
       "\n",
       "    hp_umpire_name 1b_umpire_id 1b_umpire_name 2b_umpire_id 2b_umpire_name  \\\n",
       "0       John Boake          NaN            NaN          NaN            NaN   \n",
       "1     Henry Dobson          NaN            NaN          NaN            NaN   \n",
       "2       J.H. Manny          NaN            NaN          NaN            NaN   \n",
       "3  Gardner Willard          NaN            NaN          NaN            NaN   \n",
       "4      Isaac Leroy          NaN            NaN          NaN            NaN   \n",
       "\n",
       "  3b_umpire_id 3b_umpire_name lf_umpire_id lf_umpire_name rf_umpire_id  \\\n",
       "0          NaN            NaN          NaN            NaN          NaN   \n",
       "1          NaN            NaN          NaN            NaN          NaN   \n",
       "2          NaN            NaN          NaN            NaN          NaN   \n",
       "3          NaN            NaN          NaN            NaN          NaN   \n",
       "4          NaN            NaN          NaN            NaN          NaN   \n",
       "\n",
       "  rf_umpire_name v_manager_id v_manager_name h_manager_id  h_manager_name  \\\n",
       "0            NaN     paboc101  Charlie Pabor     lennb101     Bill Lennon   \n",
       "1            NaN     wrigh101   Harry Wright     younn801      Nick Young   \n",
       "2            NaN     paboc101  Charlie Pabor     hasts101  Scott Hastings   \n",
       "3            NaN     paboc101  Charlie Pabor     woodj106      Jimmy Wood   \n",
       "4            NaN     wrigh101   Harry Wright     pikel101        Lip Pike   \n",
       "\n",
       "  winning_pitcher_id winning_pitcher_name losing_pitcher_id  \\\n",
       "0           mathb101        Bobby Mathews          prata101   \n",
       "1           spala101          Al Spalding          braia102   \n",
       "2           prata101             Al Pratt          fishc102   \n",
       "3           zettg101      George Zettlein          prata101   \n",
       "4           spala101          Al Spalding          mcmuj101   \n",
       "\n",
       "  losing_pitcher_name saving_pitcher_id saving_pitcher_name  \\\n",
       "0            Al Pratt               NaN                 NaN   \n",
       "1        Asa Brainard               NaN                 NaN   \n",
       "2     Cherokee Fisher               NaN                 NaN   \n",
       "3            Al Pratt               NaN                 NaN   \n",
       "4       John McMullin               NaN                 NaN   \n",
       "\n",
       "  winning_rbi_batter_id winning_rbi_batter_id_name v_starting_pitcher_id  \\\n",
       "0                   NaN                        NaN              prata101   \n",
       "1                   NaN                        NaN              spala101   \n",
       "2                   NaN                        NaN              prata101   \n",
       "3                   NaN                        NaN              prata101   \n",
       "4                   NaN                        NaN              spala101   \n",
       "\n",
       "  v_starting_pitcher_name h_starting_pitcher_id h_starting_pitcher_name  \\\n",
       "0                Al Pratt              mathb101           Bobby Mathews   \n",
       "1             Al Spalding              braia102            Asa Brainard   \n",
       "2                Al Pratt              fishc102         Cherokee Fisher   \n",
       "3                Al Pratt              zettg101         George Zettlein   \n",
       "4             Al Spalding              mcmuj101           John McMullin   \n",
       "\n",
       "  v_player_1_id v_player_1_name  v_player_1_def_pos v_player_2_id  \\\n",
       "0      whitd102    Deacon White                 2.0      kimbg101   \n",
       "1      wrigg101   George Wright                 6.0      barnr102   \n",
       "2      whitd102    Deacon White                 2.0      kimbg101   \n",
       "3      whitd102    Deacon White                 2.0      kimbg101   \n",
       "4      wrigg101   George Wright                 6.0      barnr102   \n",
       "\n",
       "  v_player_2_name  v_player_2_def_pos v_player_3_id v_player_3_name  \\\n",
       "0    Gene Kimball                 4.0      paboc101   Charlie Pabor   \n",
       "1     Ross Barnes                 4.0      birdd102   Dave Birdsall   \n",
       "2    Gene Kimball                 4.0      paboc101   Charlie Pabor   \n",
       "3    Gene Kimball                 4.0      paboc101   Charlie Pabor   \n",
       "4     Ross Barnes                 4.0      birdd102   Dave Birdsall   \n",
       "\n",
       "   v_player_3_def_pos v_player_4_id v_player_4_name  v_player_4_def_pos  \\\n",
       "0                 7.0      allia101     Art Allison                 8.0   \n",
       "1                 9.0      mcvec101       Cal McVey                 2.0   \n",
       "2                 7.0      allia101     Art Allison                 8.0   \n",
       "3                 7.0      allia101     Art Allison                 8.0   \n",
       "4                 9.0      mcvec101       Cal McVey                 2.0   \n",
       "\n",
       "  v_player_5_id v_player_5_name  v_player_5_def_pos v_player_6_id  \\\n",
       "0      white104     Elmer White                 9.0      prata101   \n",
       "1      wrigh101    Harry Wright                 8.0      goulc101   \n",
       "2      white104     Elmer White                 9.0      prata101   \n",
       "3      white104     Elmer White                 9.0      prata101   \n",
       "4      wrigh101    Harry Wright                 8.0      goulc101   \n",
       "\n",
       "  v_player_6_name  v_player_6_def_pos v_player_7_id v_player_7_name  \\\n",
       "0        Al Pratt                 1.0      sutte101     Ezra Sutton   \n",
       "1   Charlie Gould                 3.0      schah101   Harry Schafer   \n",
       "2        Al Pratt                 1.0      sutte101     Ezra Sutton   \n",
       "3        Al Pratt                 1.0      sutte101     Ezra Sutton   \n",
       "4   Charlie Gould                 3.0      schah101   Harry Schafer   \n",
       "\n",
       "   v_player_7_def_pos v_player_8_id v_player_8_name  v_player_8_def_pos  \\\n",
       "0                 5.0      carlj102    Jim Carleton                 3.0   \n",
       "1                 5.0      conef101       Fred Cone                 7.0   \n",
       "2                 5.0      carlj102    Jim Carleton                 3.0   \n",
       "3                 5.0      carlj102    Jim Carleton                 3.0   \n",
       "4                 5.0      conef101       Fred Cone                 7.0   \n",
       "\n",
       "  v_player_9_id v_player_9_name  v_player_9_def_pos h_player_1_id  \\\n",
       "0      bassj101       John Bass                 6.0      selmf101   \n",
       "1      spala101     Al Spalding                 1.0      watef102   \n",
       "2      bassj101       John Bass                 6.0      mackd101   \n",
       "3      bassj101       John Bass                 6.0      mcatb101   \n",
       "4      spala101     Al Spalding                 1.0      flync101   \n",
       "\n",
       "  h_player_1_name  h_player_1_def_pos h_player_2_id h_player_2_name  \\\n",
       "0   Frank Sellman                 5.0      mathb101   Bobby Mathews   \n",
       "1   Fred Waterman                 5.0      forcd101      Davy Force   \n",
       "2      Denny Mack                 3.0      addyb101        Bob Addy   \n",
       "3      Bub McAtee                 3.0      kingm101   Marshall King   \n",
       "4   Clipper Flynn                 9.0      mcgem101    Mike McGeary   \n",
       "\n",
       "   h_player_2_def_pos h_player_3_id  h_player_3_name  h_player_3_def_pos  \\\n",
       "0                 1.0      foraj101        Jim Foran                 3.0   \n",
       "1                 6.0      mille105    Everett Mills                 3.0   \n",
       "2                 4.0      fishc102  Cherokee Fisher                 1.0   \n",
       "3                 8.0      hodec101    Charlie Hodes                 2.0   \n",
       "4                 2.0      yorkt101         Tom York                 8.0   \n",
       "\n",
       "  h_player_4_id  h_player_4_name  h_player_4_def_pos h_player_5_id  \\\n",
       "0      goldw101  Wally Goldsmith                 6.0      lennb101   \n",
       "1      allid101     Doug Allison                 2.0      hallg101   \n",
       "2      hasts101   Scott Hastings                 8.0      ham-r101   \n",
       "3      woodj106       Jimmy Wood                 4.0      simmj101   \n",
       "4      mcmuj101    John McMullin                 1.0      kings101   \n",
       "\n",
       "  h_player_5_name  h_player_5_def_pos h_player_6_id h_player_6_name  \\\n",
       "0     Bill Lennon                 2.0      caret101       Tom Carey   \n",
       "1     George Hall                 7.0      leona101    Andy Leonard   \n",
       "2       Ralph Ham                 5.0      ansoc101       Cap Anson   \n",
       "3     Joe Simmons                 9.0      folet101       Tom Foley   \n",
       "4      Steve King                 7.0      beave101  Edward Beavens   \n",
       "\n",
       "   h_player_6_def_pos h_player_7_id h_player_7_name  h_player_7_def_pos  \\\n",
       "0                 4.0      mince101      Ed Mincher                 7.0   \n",
       "1                 4.0      braia102    Asa Brainard                 1.0   \n",
       "2                 2.0      sagep101      Pony Sager                 6.0   \n",
       "3                 7.0      duffe101        Ed Duffy                 6.0   \n",
       "4                 4.0      bells101    Steve Bellan                 5.0   \n",
       "\n",
       "  h_player_8_id  h_player_8_name  h_player_8_def_pos h_player_9_id  \\\n",
       "0      mcdej101  James McDermott                 8.0      kellb105   \n",
       "1      burrh101  Henry Burroughs                 9.0      berth101   \n",
       "2      birdg101      George Bird                 7.0      stirg101   \n",
       "3      pinke101       Ed Pinkham                 5.0      zettg101   \n",
       "4      pikel101         Lip Pike                 3.0      cravb101   \n",
       "\n",
       "   h_player_9_name  h_player_9_def_pos additional_info acquisition_info  \n",
       "0       Bill Kelly                 9.0             NaN                Y  \n",
       "1  Henry Berthrong                 8.0            HTBF                Y  \n",
       "2       Gat Stires                 9.0             NaN                Y  \n",
       "3  George Zettlein                 1.0             NaN                Y  \n",
       "4      Bill Craver                 6.0            HTBF                Y  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log = pd.read_csv(\"game_log.csv\",low_memory=False)\n",
    "print(log.shape)\n",
    "log.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_game</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>v_name</th>\n",
       "      <th>v_league</th>\n",
       "      <th>v_game_number</th>\n",
       "      <th>h_name</th>\n",
       "      <th>h_league</th>\n",
       "      <th>h_game_number</th>\n",
       "      <th>v_score</th>\n",
       "      <th>h_score</th>\n",
       "      <th>length_outs</th>\n",
       "      <th>day_night</th>\n",
       "      <th>completion</th>\n",
       "      <th>forefeit</th>\n",
       "      <th>protest</th>\n",
       "      <th>park_id</th>\n",
       "      <th>attendance</th>\n",
       "      <th>length_minutes</th>\n",
       "      <th>v_line_score</th>\n",
       "      <th>h_line_score</th>\n",
       "      <th>v_at_bats</th>\n",
       "      <th>v_hits</th>\n",
       "      <th>v_doubles</th>\n",
       "      <th>v_triples</th>\n",
       "      <th>v_homeruns</th>\n",
       "      <th>v_rbi</th>\n",
       "      <th>v_sacrifice_hits</th>\n",
       "      <th>v_sacrifice_flies</th>\n",
       "      <th>v_hit_by_pitch</th>\n",
       "      <th>v_walks</th>\n",
       "      <th>v_intentional_walks</th>\n",
       "      <th>v_strikeouts</th>\n",
       "      <th>v_stolen_bases</th>\n",
       "      <th>v_caught_stealing</th>\n",
       "      <th>v_grounded_into_double</th>\n",
       "      <th>v_first_catcher_interference</th>\n",
       "      <th>v_left_on_base</th>\n",
       "      <th>v_pitchers_used</th>\n",
       "      <th>v_individual_earned_runs</th>\n",
       "      <th>v_team_earned_runs</th>\n",
       "      <th>v_wild_pitches</th>\n",
       "      <th>v_balks</th>\n",
       "      <th>v_putouts</th>\n",
       "      <th>v_assists</th>\n",
       "      <th>v_errors</th>\n",
       "      <th>v_passed_balls</th>\n",
       "      <th>v_double_plays</th>\n",
       "      <th>v_triple_plays</th>\n",
       "      <th>h_at_bats</th>\n",
       "      <th>h_hits</th>\n",
       "      <th>h_doubles</th>\n",
       "      <th>h_triples</th>\n",
       "      <th>h_homeruns</th>\n",
       "      <th>h_rbi</th>\n",
       "      <th>h_sacrifice_hits</th>\n",
       "      <th>h_sacrifice_flies</th>\n",
       "      <th>h_hit_by_pitch</th>\n",
       "      <th>h_walks</th>\n",
       "      <th>h_intentional_walks</th>\n",
       "      <th>h_strikeouts</th>\n",
       "      <th>h_stolen_bases</th>\n",
       "      <th>h_caught_stealing</th>\n",
       "      <th>h_grounded_into_double</th>\n",
       "      <th>h_first_catcher_interference</th>\n",
       "      <th>h_left_on_base</th>\n",
       "      <th>h_pitchers_used</th>\n",
       "      <th>h_individual_earned_runs</th>\n",
       "      <th>h_team_earned_runs</th>\n",
       "      <th>h_wild_pitches</th>\n",
       "      <th>h_balks</th>\n",
       "      <th>h_putouts</th>\n",
       "      <th>h_assists</th>\n",
       "      <th>h_errors</th>\n",
       "      <th>h_passed_balls</th>\n",
       "      <th>h_double_plays</th>\n",
       "      <th>h_triple_plays</th>\n",
       "      <th>hp_umpire_id</th>\n",
       "      <th>hp_umpire_name</th>\n",
       "      <th>1b_umpire_id</th>\n",
       "      <th>1b_umpire_name</th>\n",
       "      <th>2b_umpire_id</th>\n",
       "      <th>2b_umpire_name</th>\n",
       "      <th>3b_umpire_id</th>\n",
       "      <th>3b_umpire_name</th>\n",
       "      <th>lf_umpire_id</th>\n",
       "      <th>lf_umpire_name</th>\n",
       "      <th>rf_umpire_id</th>\n",
       "      <th>rf_umpire_name</th>\n",
       "      <th>v_manager_id</th>\n",
       "      <th>v_manager_name</th>\n",
       "      <th>h_manager_id</th>\n",
       "      <th>h_manager_name</th>\n",
       "      <th>winning_pitcher_id</th>\n",
       "      <th>winning_pitcher_name</th>\n",
       "      <th>losing_pitcher_id</th>\n",
       "      <th>losing_pitcher_name</th>\n",
       "      <th>saving_pitcher_id</th>\n",
       "      <th>saving_pitcher_name</th>\n",
       "      <th>winning_rbi_batter_id</th>\n",
       "      <th>winning_rbi_batter_id_name</th>\n",
       "      <th>v_starting_pitcher_id</th>\n",
       "      <th>v_starting_pitcher_name</th>\n",
       "      <th>h_starting_pitcher_id</th>\n",
       "      <th>h_starting_pitcher_name</th>\n",
       "      <th>v_player_1_id</th>\n",
       "      <th>v_player_1_name</th>\n",
       "      <th>v_player_1_def_pos</th>\n",
       "      <th>v_player_2_id</th>\n",
       "      <th>v_player_2_name</th>\n",
       "      <th>v_player_2_def_pos</th>\n",
       "      <th>v_player_3_id</th>\n",
       "      <th>v_player_3_name</th>\n",
       "      <th>v_player_3_def_pos</th>\n",
       "      <th>v_player_4_id</th>\n",
       "      <th>v_player_4_name</th>\n",
       "      <th>v_player_4_def_pos</th>\n",
       "      <th>v_player_5_id</th>\n",
       "      <th>v_player_5_name</th>\n",
       "      <th>v_player_5_def_pos</th>\n",
       "      <th>v_player_6_id</th>\n",
       "      <th>v_player_6_name</th>\n",
       "      <th>v_player_6_def_pos</th>\n",
       "      <th>v_player_7_id</th>\n",
       "      <th>v_player_7_name</th>\n",
       "      <th>v_player_7_def_pos</th>\n",
       "      <th>v_player_8_id</th>\n",
       "      <th>v_player_8_name</th>\n",
       "      <th>v_player_8_def_pos</th>\n",
       "      <th>v_player_9_id</th>\n",
       "      <th>v_player_9_name</th>\n",
       "      <th>v_player_9_def_pos</th>\n",
       "      <th>h_player_1_id</th>\n",
       "      <th>h_player_1_name</th>\n",
       "      <th>h_player_1_def_pos</th>\n",
       "      <th>h_player_2_id</th>\n",
       "      <th>h_player_2_name</th>\n",
       "      <th>h_player_2_def_pos</th>\n",
       "      <th>h_player_3_id</th>\n",
       "      <th>h_player_3_name</th>\n",
       "      <th>h_player_3_def_pos</th>\n",
       "      <th>h_player_4_id</th>\n",
       "      <th>h_player_4_name</th>\n",
       "      <th>h_player_4_def_pos</th>\n",
       "      <th>h_player_5_id</th>\n",
       "      <th>h_player_5_name</th>\n",
       "      <th>h_player_5_def_pos</th>\n",
       "      <th>h_player_6_id</th>\n",
       "      <th>h_player_6_name</th>\n",
       "      <th>h_player_6_def_pos</th>\n",
       "      <th>h_player_7_id</th>\n",
       "      <th>h_player_7_name</th>\n",
       "      <th>h_player_7_def_pos</th>\n",
       "      <th>h_player_8_id</th>\n",
       "      <th>h_player_8_name</th>\n",
       "      <th>h_player_8_def_pos</th>\n",
       "      <th>h_player_9_id</th>\n",
       "      <th>h_player_9_name</th>\n",
       "      <th>h_player_9_def_pos</th>\n",
       "      <th>additional_info</th>\n",
       "      <th>acquisition_info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>171902</th>\n",
       "      <td>20161002</td>\n",
       "      <td>0</td>\n",
       "      <td>Sun</td>\n",
       "      <td>MIL</td>\n",
       "      <td>NL</td>\n",
       "      <td>162</td>\n",
       "      <td>COL</td>\n",
       "      <td>NL</td>\n",
       "      <td>162</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>60.0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DEN02</td>\n",
       "      <td>27762.0</td>\n",
       "      <td>203.0</td>\n",
       "      <td>0200000202</td>\n",
       "      <td>1100100010</td>\n",
       "      <td>39.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>barrs901</td>\n",
       "      <td>Scott Barry</td>\n",
       "      <td>woodt901</td>\n",
       "      <td>Tom Woodring</td>\n",
       "      <td>randt901</td>\n",
       "      <td>Tony Randazzo</td>\n",
       "      <td>ortir901</td>\n",
       "      <td>Roberto Ortiz</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>counc001</td>\n",
       "      <td>Craig Counsell</td>\n",
       "      <td>weisw001</td>\n",
       "      <td>Walt Weiss</td>\n",
       "      <td>thort001</td>\n",
       "      <td>Tyler Thornburg</td>\n",
       "      <td>rusic001</td>\n",
       "      <td>Chris Rusin</td>\n",
       "      <td>knebc001</td>\n",
       "      <td>Corey Knebel</td>\n",
       "      <td>susaa001</td>\n",
       "      <td>Andrew Susac</td>\n",
       "      <td>cravt001</td>\n",
       "      <td>Tyler Cravy</td>\n",
       "      <td>marqg001</td>\n",
       "      <td>German Marquez</td>\n",
       "      <td>villj001</td>\n",
       "      <td>Jonathan Villar</td>\n",
       "      <td>5.0</td>\n",
       "      <td>genns001</td>\n",
       "      <td>Scooter Gennett</td>\n",
       "      <td>4.0</td>\n",
       "      <td>cartc002</td>\n",
       "      <td>Chris Carter</td>\n",
       "      <td>3.0</td>\n",
       "      <td>santd002</td>\n",
       "      <td>Domingo Santana</td>\n",
       "      <td>9.0</td>\n",
       "      <td>pereh001</td>\n",
       "      <td>Hernan Perez</td>\n",
       "      <td>8.0</td>\n",
       "      <td>arcio002</td>\n",
       "      <td>Orlando Arcia</td>\n",
       "      <td>6.0</td>\n",
       "      <td>susaa001</td>\n",
       "      <td>Andrew Susac</td>\n",
       "      <td>2.0</td>\n",
       "      <td>elmoj001</td>\n",
       "      <td>Jake Elmore</td>\n",
       "      <td>7.0</td>\n",
       "      <td>cravt001</td>\n",
       "      <td>Tyler Cravy</td>\n",
       "      <td>1.0</td>\n",
       "      <td>blacc001</td>\n",
       "      <td>Charlie Blackmon</td>\n",
       "      <td>8.0</td>\n",
       "      <td>dahld001</td>\n",
       "      <td>David Dahl</td>\n",
       "      <td>7.0</td>\n",
       "      <td>arenn001</td>\n",
       "      <td>Nolan Arenado</td>\n",
       "      <td>5.0</td>\n",
       "      <td>gonzc001</td>\n",
       "      <td>Carlos Gonzalez</td>\n",
       "      <td>9.0</td>\n",
       "      <td>murpt002</td>\n",
       "      <td>Tom Murphy</td>\n",
       "      <td>2.0</td>\n",
       "      <td>pattj005</td>\n",
       "      <td>Jordan Patterson</td>\n",
       "      <td>3.0</td>\n",
       "      <td>valap001</td>\n",
       "      <td>Pat Valaika</td>\n",
       "      <td>4.0</td>\n",
       "      <td>adamc001</td>\n",
       "      <td>Cristhian Adames</td>\n",
       "      <td>6.0</td>\n",
       "      <td>marqg001</td>\n",
       "      <td>German Marquez</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171903</th>\n",
       "      <td>20161002</td>\n",
       "      <td>0</td>\n",
       "      <td>Sun</td>\n",
       "      <td>NYN</td>\n",
       "      <td>NL</td>\n",
       "      <td>162</td>\n",
       "      <td>PHI</td>\n",
       "      <td>NL</td>\n",
       "      <td>162</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>51.0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PHI13</td>\n",
       "      <td>36935.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>000001100</td>\n",
       "      <td>00100031x</td>\n",
       "      <td>33.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>barkl901</td>\n",
       "      <td>Lance Barksdale</td>\n",
       "      <td>herna901</td>\n",
       "      <td>Angel Hernandez</td>\n",
       "      <td>barrt901</td>\n",
       "      <td>Ted Barrett</td>\n",
       "      <td>littw901</td>\n",
       "      <td>Will Little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>collt801</td>\n",
       "      <td>Terry Collins</td>\n",
       "      <td>mackp101</td>\n",
       "      <td>Pete Mackanin</td>\n",
       "      <td>murrc002</td>\n",
       "      <td>Colton Murray</td>\n",
       "      <td>goede001</td>\n",
       "      <td>Erik Goeddel</td>\n",
       "      <td>nerih001</td>\n",
       "      <td>Hector Neris</td>\n",
       "      <td>hernc005</td>\n",
       "      <td>Cesar Hernandez</td>\n",
       "      <td>ynoag001</td>\n",
       "      <td>Gabriel Ynoa</td>\n",
       "      <td>eickj001</td>\n",
       "      <td>Jerad Eickhoff</td>\n",
       "      <td>granc001</td>\n",
       "      <td>Curtis Granderson</td>\n",
       "      <td>8.0</td>\n",
       "      <td>cabra002</td>\n",
       "      <td>Asdrubal Cabrera</td>\n",
       "      <td>6.0</td>\n",
       "      <td>brucj001</td>\n",
       "      <td>Jay Bruce</td>\n",
       "      <td>9.0</td>\n",
       "      <td>dudal001</td>\n",
       "      <td>Lucas Duda</td>\n",
       "      <td>3.0</td>\n",
       "      <td>johnk003</td>\n",
       "      <td>Kelly Johnson</td>\n",
       "      <td>4.0</td>\n",
       "      <td>confm001</td>\n",
       "      <td>Michael Conforto</td>\n",
       "      <td>7.0</td>\n",
       "      <td>campe001</td>\n",
       "      <td>Eric Campbell</td>\n",
       "      <td>5.0</td>\n",
       "      <td>plawk001</td>\n",
       "      <td>Kevin Plawecki</td>\n",
       "      <td>2.0</td>\n",
       "      <td>ynoag001</td>\n",
       "      <td>Gabriel Ynoa</td>\n",
       "      <td>1.0</td>\n",
       "      <td>hernc005</td>\n",
       "      <td>Cesar Hernandez</td>\n",
       "      <td>4.0</td>\n",
       "      <td>parej002</td>\n",
       "      <td>Jimmy Paredes</td>\n",
       "      <td>7.0</td>\n",
       "      <td>herro001</td>\n",
       "      <td>Odubel Herrera</td>\n",
       "      <td>8.0</td>\n",
       "      <td>franm004</td>\n",
       "      <td>Maikel Franco</td>\n",
       "      <td>5.0</td>\n",
       "      <td>howar001</td>\n",
       "      <td>Ryan Howard</td>\n",
       "      <td>3.0</td>\n",
       "      <td>ruppc001</td>\n",
       "      <td>Cameron Rupp</td>\n",
       "      <td>2.0</td>\n",
       "      <td>blana001</td>\n",
       "      <td>Andres Blanco</td>\n",
       "      <td>6.0</td>\n",
       "      <td>altha001</td>\n",
       "      <td>Aaron Altherr</td>\n",
       "      <td>9.0</td>\n",
       "      <td>eickj001</td>\n",
       "      <td>Jerad Eickhoff</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171904</th>\n",
       "      <td>20161002</td>\n",
       "      <td>0</td>\n",
       "      <td>Sun</td>\n",
       "      <td>LAN</td>\n",
       "      <td>NL</td>\n",
       "      <td>162</td>\n",
       "      <td>SFN</td>\n",
       "      <td>NL</td>\n",
       "      <td>162</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>51.0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SFO03</td>\n",
       "      <td>41445.0</td>\n",
       "      <td>184.0</td>\n",
       "      <td>000100000</td>\n",
       "      <td>23000002x</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>knigb901</td>\n",
       "      <td>Brian Knight</td>\n",
       "      <td>westj901</td>\n",
       "      <td>Joe West</td>\n",
       "      <td>fleta901</td>\n",
       "      <td>Andy Fletcher</td>\n",
       "      <td>danlk901</td>\n",
       "      <td>Kerwin Danley</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>robed001</td>\n",
       "      <td>Dave Roberts</td>\n",
       "      <td>bochb002</td>\n",
       "      <td>Bruce Bochy</td>\n",
       "      <td>moorm003</td>\n",
       "      <td>Matt Moore</td>\n",
       "      <td>maedk001</td>\n",
       "      <td>Kenta Maeda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>poseb001</td>\n",
       "      <td>Buster Posey</td>\n",
       "      <td>maedk001</td>\n",
       "      <td>Kenta Maeda</td>\n",
       "      <td>moorm003</td>\n",
       "      <td>Matt Moore</td>\n",
       "      <td>kendh001</td>\n",
       "      <td>Howie Kendrick</td>\n",
       "      <td>7.0</td>\n",
       "      <td>turnj001</td>\n",
       "      <td>Justin Turner</td>\n",
       "      <td>5.0</td>\n",
       "      <td>seagc001</td>\n",
       "      <td>Corey Seager</td>\n",
       "      <td>6.0</td>\n",
       "      <td>puigy001</td>\n",
       "      <td>Yasiel Puig</td>\n",
       "      <td>9.0</td>\n",
       "      <td>gonza003</td>\n",
       "      <td>Adrian Gonzalez</td>\n",
       "      <td>3.0</td>\n",
       "      <td>grany001</td>\n",
       "      <td>Yasmani Grandal</td>\n",
       "      <td>2.0</td>\n",
       "      <td>pedej001</td>\n",
       "      <td>Joc Pederson</td>\n",
       "      <td>8.0</td>\n",
       "      <td>utlec001</td>\n",
       "      <td>Chase Utley</td>\n",
       "      <td>4.0</td>\n",
       "      <td>maedk001</td>\n",
       "      <td>Kenta Maeda</td>\n",
       "      <td>1.0</td>\n",
       "      <td>spand001</td>\n",
       "      <td>Denard Span</td>\n",
       "      <td>8.0</td>\n",
       "      <td>beltb001</td>\n",
       "      <td>Brandon Belt</td>\n",
       "      <td>3.0</td>\n",
       "      <td>poseb001</td>\n",
       "      <td>Buster Posey</td>\n",
       "      <td>2.0</td>\n",
       "      <td>pench001</td>\n",
       "      <td>Hunter Pence</td>\n",
       "      <td>9.0</td>\n",
       "      <td>crawb001</td>\n",
       "      <td>Brandon Crawford</td>\n",
       "      <td>6.0</td>\n",
       "      <td>pagaa001</td>\n",
       "      <td>Angel Pagan</td>\n",
       "      <td>7.0</td>\n",
       "      <td>panij002</td>\n",
       "      <td>Joe Panik</td>\n",
       "      <td>4.0</td>\n",
       "      <td>gillc001</td>\n",
       "      <td>Conor Gillaspie</td>\n",
       "      <td>5.0</td>\n",
       "      <td>moorm003</td>\n",
       "      <td>Matt Moore</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171905</th>\n",
       "      <td>20161002</td>\n",
       "      <td>0</td>\n",
       "      <td>Sun</td>\n",
       "      <td>PIT</td>\n",
       "      <td>NL</td>\n",
       "      <td>162</td>\n",
       "      <td>SLN</td>\n",
       "      <td>NL</td>\n",
       "      <td>162</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>51.0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>STL10</td>\n",
       "      <td>44615.0</td>\n",
       "      <td>192.0</td>\n",
       "      <td>000020200</td>\n",
       "      <td>00100360x</td>\n",
       "      <td>35.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>cuzzp901</td>\n",
       "      <td>Phil Cuzzi</td>\n",
       "      <td>ticht901</td>\n",
       "      <td>Todd Tichenor</td>\n",
       "      <td>vanol901</td>\n",
       "      <td>Larry Vanover</td>\n",
       "      <td>marqa901</td>\n",
       "      <td>Alfonso Marquez</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>hurdc001</td>\n",
       "      <td>Clint Hurdle</td>\n",
       "      <td>mathm001</td>\n",
       "      <td>Mike Matheny</td>\n",
       "      <td>broxj001</td>\n",
       "      <td>Jonathan Broxton</td>\n",
       "      <td>nicaj001</td>\n",
       "      <td>Juan Nicasio</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>piscs001</td>\n",
       "      <td>Stephen Piscotty</td>\n",
       "      <td>voger001</td>\n",
       "      <td>Ryan Vogelsong</td>\n",
       "      <td>waina001</td>\n",
       "      <td>Adam Wainwright</td>\n",
       "      <td>jasoj001</td>\n",
       "      <td>John Jaso</td>\n",
       "      <td>3.0</td>\n",
       "      <td>polag001</td>\n",
       "      <td>Gregory Polanco</td>\n",
       "      <td>9.0</td>\n",
       "      <td>mccua001</td>\n",
       "      <td>Andrew McCutchen</td>\n",
       "      <td>8.0</td>\n",
       "      <td>kangj001</td>\n",
       "      <td>Jung Ho Kang</td>\n",
       "      <td>5.0</td>\n",
       "      <td>joycm001</td>\n",
       "      <td>Matt Joyce</td>\n",
       "      <td>7.0</td>\n",
       "      <td>hansa001</td>\n",
       "      <td>Alen Hanson</td>\n",
       "      <td>4.0</td>\n",
       "      <td>fryee001</td>\n",
       "      <td>Eric Fryer</td>\n",
       "      <td>2.0</td>\n",
       "      <td>florp001</td>\n",
       "      <td>Pedro Florimon</td>\n",
       "      <td>6.0</td>\n",
       "      <td>voger001</td>\n",
       "      <td>Ryan Vogelsong</td>\n",
       "      <td>1.0</td>\n",
       "      <td>carpm002</td>\n",
       "      <td>Matt Carpenter</td>\n",
       "      <td>3.0</td>\n",
       "      <td>diaza003</td>\n",
       "      <td>Aledmys Diaz</td>\n",
       "      <td>6.0</td>\n",
       "      <td>moliy001</td>\n",
       "      <td>Yadier Molina</td>\n",
       "      <td>2.0</td>\n",
       "      <td>piscs001</td>\n",
       "      <td>Stephen Piscotty</td>\n",
       "      <td>9.0</td>\n",
       "      <td>peraj001</td>\n",
       "      <td>Jhonny Peralta</td>\n",
       "      <td>5.0</td>\n",
       "      <td>mossb001</td>\n",
       "      <td>Brandon Moss</td>\n",
       "      <td>7.0</td>\n",
       "      <td>gyorj001</td>\n",
       "      <td>Jedd Gyorko</td>\n",
       "      <td>4.0</td>\n",
       "      <td>gricr001</td>\n",
       "      <td>Randal Grichuk</td>\n",
       "      <td>8.0</td>\n",
       "      <td>waina001</td>\n",
       "      <td>Adam Wainwright</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171906</th>\n",
       "      <td>20161002</td>\n",
       "      <td>0</td>\n",
       "      <td>Sun</td>\n",
       "      <td>MIA</td>\n",
       "      <td>NL</td>\n",
       "      <td>161</td>\n",
       "      <td>WAS</td>\n",
       "      <td>NL</td>\n",
       "      <td>162</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>51.0</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WAS11</td>\n",
       "      <td>28730.0</td>\n",
       "      <td>216.0</td>\n",
       "      <td>000230020</td>\n",
       "      <td>03023002x</td>\n",
       "      <td>38.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>tumpj901</td>\n",
       "      <td>John Tumpane</td>\n",
       "      <td>porta901</td>\n",
       "      <td>Alan Porter</td>\n",
       "      <td>onorb901</td>\n",
       "      <td>Brian O'Nora</td>\n",
       "      <td>kellj901</td>\n",
       "      <td>Jeff Kellogg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>mattd001</td>\n",
       "      <td>Don Mattingly</td>\n",
       "      <td>baked002</td>\n",
       "      <td>Dusty Baker</td>\n",
       "      <td>schem001</td>\n",
       "      <td>Max Scherzer</td>\n",
       "      <td>brica001</td>\n",
       "      <td>Austin Brice</td>\n",
       "      <td>melam001</td>\n",
       "      <td>Mark Melancon</td>\n",
       "      <td>difow001</td>\n",
       "      <td>Wilmer Difo</td>\n",
       "      <td>koeht001</td>\n",
       "      <td>Tom Koehler</td>\n",
       "      <td>schem001</td>\n",
       "      <td>Max Scherzer</td>\n",
       "      <td>gordd002</td>\n",
       "      <td>Dee Gordon</td>\n",
       "      <td>4.0</td>\n",
       "      <td>telit001</td>\n",
       "      <td>Tomas Telis</td>\n",
       "      <td>2.0</td>\n",
       "      <td>pradm001</td>\n",
       "      <td>Martin Prado</td>\n",
       "      <td>5.0</td>\n",
       "      <td>yelic001</td>\n",
       "      <td>Christian Yelich</td>\n",
       "      <td>8.0</td>\n",
       "      <td>bourj002</td>\n",
       "      <td>Justin Bour</td>\n",
       "      <td>3.0</td>\n",
       "      <td>scrux001</td>\n",
       "      <td>Xavier Scruggs</td>\n",
       "      <td>7.0</td>\n",
       "      <td>hoodd001</td>\n",
       "      <td>Destin Hood</td>\n",
       "      <td>9.0</td>\n",
       "      <td>hecha001</td>\n",
       "      <td>Adeiny Hechavarria</td>\n",
       "      <td>6.0</td>\n",
       "      <td>koeht001</td>\n",
       "      <td>Tom Koehler</td>\n",
       "      <td>1.0</td>\n",
       "      <td>turnt001</td>\n",
       "      <td>Trea Turner</td>\n",
       "      <td>8.0</td>\n",
       "      <td>reveb001</td>\n",
       "      <td>Ben Revere</td>\n",
       "      <td>7.0</td>\n",
       "      <td>harpb003</td>\n",
       "      <td>Bryce Harper</td>\n",
       "      <td>9.0</td>\n",
       "      <td>zimmr001</td>\n",
       "      <td>Ryan Zimmerman</td>\n",
       "      <td>3.0</td>\n",
       "      <td>drews001</td>\n",
       "      <td>Stephen Drew</td>\n",
       "      <td>5.0</td>\n",
       "      <td>difow001</td>\n",
       "      <td>Wilmer Difo</td>\n",
       "      <td>4.0</td>\n",
       "      <td>espid001</td>\n",
       "      <td>Danny Espinosa</td>\n",
       "      <td>6.0</td>\n",
       "      <td>lobaj001</td>\n",
       "      <td>Jose Lobaton</td>\n",
       "      <td>2.0</td>\n",
       "      <td>schem001</td>\n",
       "      <td>Max Scherzer</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            date  number_of_game day_of_week v_name v_league  v_game_number  \\\n",
       "171902  20161002               0         Sun    MIL       NL            162   \n",
       "171903  20161002               0         Sun    NYN       NL            162   \n",
       "171904  20161002               0         Sun    LAN       NL            162   \n",
       "171905  20161002               0         Sun    PIT       NL            162   \n",
       "171906  20161002               0         Sun    MIA       NL            161   \n",
       "\n",
       "       h_name h_league  h_game_number  v_score  h_score  length_outs  \\\n",
       "171902    COL       NL            162        6        4         60.0   \n",
       "171903    PHI       NL            162        2        5         51.0   \n",
       "171904    SFN       NL            162        1        7         51.0   \n",
       "171905    SLN       NL            162        4       10         51.0   \n",
       "171906    WAS       NL            162        7       10         51.0   \n",
       "\n",
       "       day_night completion forefeit protest park_id  attendance  \\\n",
       "171902         D        NaN      NaN     NaN   DEN02     27762.0   \n",
       "171903         D        NaN      NaN     NaN   PHI13     36935.0   \n",
       "171904         D        NaN      NaN     NaN   SFO03     41445.0   \n",
       "171905         D        NaN      NaN     NaN   STL10     44615.0   \n",
       "171906         D        NaN      NaN     NaN   WAS11     28730.0   \n",
       "\n",
       "        length_minutes v_line_score h_line_score  v_at_bats  v_hits  \\\n",
       "171902           203.0   0200000202   1100100010       39.0    10.0   \n",
       "171903           159.0    000001100    00100031x       33.0     8.0   \n",
       "171904           184.0    000100000    23000002x       30.0     4.0   \n",
       "171905           192.0    000020200    00100360x       35.0     9.0   \n",
       "171906           216.0    000230020    03023002x       38.0    14.0   \n",
       "\n",
       "        v_doubles  v_triples  v_homeruns  v_rbi  v_sacrifice_hits  \\\n",
       "171902        4.0        1.0         2.0    6.0               0.0   \n",
       "171903        3.0        0.0         0.0    2.0               0.0   \n",
       "171904        0.0        0.0         0.0    1.0               0.0   \n",
       "171905        0.0        0.0         1.0    4.0               0.0   \n",
       "171906        1.0        1.0         2.0    7.0               1.0   \n",
       "\n",
       "        v_sacrifice_flies  v_hit_by_pitch  v_walks  v_intentional_walks  \\\n",
       "171902                0.0             1.0      4.0                  0.0   \n",
       "171903                0.0             0.0      2.0                  0.0   \n",
       "171904                0.0             0.0      2.0                  0.0   \n",
       "171905                0.0             0.0      4.0                  0.0   \n",
       "171906                0.0             0.0      3.0                  2.0   \n",
       "\n",
       "        v_strikeouts  v_stolen_bases  v_caught_stealing  \\\n",
       "171902          12.0             2.0                1.0   \n",
       "171903           9.0             1.0                1.0   \n",
       "171904           7.0             0.0                0.0   \n",
       "171905          11.0             0.0                1.0   \n",
       "171906          10.0             1.0                1.0   \n",
       "\n",
       "        v_grounded_into_double  v_first_catcher_interference  v_left_on_base  \\\n",
       "171902                     0.0                           0.0             8.0   \n",
       "171903                     1.0                           0.0             6.0   \n",
       "171904                     1.0                           0.0             4.0   \n",
       "171905                     0.0                           0.0             8.0   \n",
       "171906                     1.0                           0.0             8.0   \n",
       "\n",
       "        v_pitchers_used  v_individual_earned_runs  v_team_earned_runs  \\\n",
       "171902              7.0                       4.0                 4.0   \n",
       "171903              6.0                       3.0                 3.0   \n",
       "171904              7.0                       7.0                 7.0   \n",
       "171905              6.0                       8.0                 8.0   \n",
       "171906              7.0                      10.0                10.0   \n",
       "\n",
       "        v_wild_pitches  v_balks  v_putouts  v_assists  v_errors  \\\n",
       "171902             1.0      0.0       30.0       12.0       1.0   \n",
       "171903             0.0      0.0       24.0       12.0       3.0   \n",
       "171904             0.0      0.0       24.0        5.0       1.0   \n",
       "171905             0.0      0.0       24.0        2.0       2.0   \n",
       "171906             1.0      0.0       24.0       11.0       0.0   \n",
       "\n",
       "        v_passed_balls  v_double_plays  v_triple_plays  h_at_bats  h_hits  \\\n",
       "171902             0.0             0.0             0.0       41.0    13.0   \n",
       "171903             1.0             2.0             0.0       33.0    10.0   \n",
       "171904             0.0             0.0             0.0       39.0    16.0   \n",
       "171905             0.0             0.0             0.0       36.0    12.0   \n",
       "171906             0.0             1.0             0.0       30.0    10.0   \n",
       "\n",
       "        h_doubles  h_triples  h_homeruns  h_rbi  h_sacrifice_hits  \\\n",
       "171902        4.0        0.0         1.0    4.0               1.0   \n",
       "171903        1.0        0.0         0.0    3.0               0.0   \n",
       "171904        3.0        1.0         0.0    7.0               0.0   \n",
       "171905        2.0        0.0         1.0   10.0               0.0   \n",
       "171906        2.0        0.0         1.0   10.0               1.0   \n",
       "\n",
       "        h_sacrifice_flies  h_hit_by_pitch  h_walks  h_intentional_walks  \\\n",
       "171902                0.0             1.0      3.0                  0.0   \n",
       "171903                1.0             0.0      2.0                  0.0   \n",
       "171904                0.0             0.0      4.0                  1.0   \n",
       "171905                2.0             0.0      4.0                  0.0   \n",
       "171906                1.0             1.0      8.0                  0.0   \n",
       "\n",
       "        h_strikeouts  h_stolen_bases  h_caught_stealing  \\\n",
       "171902          11.0             0.0                1.0   \n",
       "171903           3.0             0.0                0.0   \n",
       "171904          11.0             2.0                1.0   \n",
       "171905           5.0             0.0                0.0   \n",
       "171906           3.0             2.0                0.0   \n",
       "\n",
       "        h_grounded_into_double  h_first_catcher_interference  h_left_on_base  \\\n",
       "171902                     0.0                           0.0            12.0   \n",
       "171903                     2.0                           0.0             7.0   \n",
       "171904                     0.0                           0.0            12.0   \n",
       "171905                     0.0                           0.0             8.0   \n",
       "171906                     1.0                           0.0             7.0   \n",
       "\n",
       "        h_pitchers_used  h_individual_earned_runs  h_team_earned_runs  \\\n",
       "171902              5.0                       6.0                 6.0   \n",
       "171903              5.0                       2.0                 2.0   \n",
       "171904              2.0                       1.0                 1.0   \n",
       "171905              3.0                       4.0                 4.0   \n",
       "171906              6.0                       7.0                 7.0   \n",
       "\n",
       "        h_wild_pitches  h_balks  h_putouts  h_assists  h_errors  \\\n",
       "171902             0.0      0.0       30.0       13.0       0.0   \n",
       "171903             0.0      0.0       27.0        7.0       0.0   \n",
       "171904             0.0      0.0       27.0        7.0       0.0   \n",
       "171905             0.0      0.0       27.0        7.0       0.0   \n",
       "171906             1.0      0.0       27.0       11.0       0.0   \n",
       "\n",
       "        h_passed_balls  h_double_plays  h_triple_plays hp_umpire_id  \\\n",
       "171902             0.0             0.0             0.0     barrs901   \n",
       "171903             0.0             1.0             0.0     barkl901   \n",
       "171904             0.0             1.0             0.0     knigb901   \n",
       "171905             0.0             1.0             0.0     cuzzp901   \n",
       "171906             0.0             1.0             0.0     tumpj901   \n",
       "\n",
       "         hp_umpire_name 1b_umpire_id   1b_umpire_name 2b_umpire_id  \\\n",
       "171902      Scott Barry     woodt901     Tom Woodring     randt901   \n",
       "171903  Lance Barksdale     herna901  Angel Hernandez     barrt901   \n",
       "171904     Brian Knight     westj901         Joe West     fleta901   \n",
       "171905       Phil Cuzzi     ticht901    Todd Tichenor     vanol901   \n",
       "171906     John Tumpane     porta901      Alan Porter     onorb901   \n",
       "\n",
       "       2b_umpire_name 3b_umpire_id   3b_umpire_name lf_umpire_id  \\\n",
       "171902  Tony Randazzo     ortir901    Roberto Ortiz          NaN   \n",
       "171903    Ted Barrett     littw901      Will Little          NaN   \n",
       "171904  Andy Fletcher     danlk901    Kerwin Danley          NaN   \n",
       "171905  Larry Vanover     marqa901  Alfonso Marquez          NaN   \n",
       "171906   Brian O'Nora     kellj901     Jeff Kellogg          NaN   \n",
       "\n",
       "       lf_umpire_name rf_umpire_id rf_umpire_name v_manager_id  \\\n",
       "171902            NaN          NaN            NaN     counc001   \n",
       "171903            NaN          NaN            NaN     collt801   \n",
       "171904            NaN          NaN            NaN     robed001   \n",
       "171905            NaN          NaN            NaN     hurdc001   \n",
       "171906            NaN          NaN            NaN     mattd001   \n",
       "\n",
       "        v_manager_name h_manager_id h_manager_name winning_pitcher_id  \\\n",
       "171902  Craig Counsell     weisw001     Walt Weiss           thort001   \n",
       "171903   Terry Collins     mackp101  Pete Mackanin           murrc002   \n",
       "171904    Dave Roberts     bochb002    Bruce Bochy           moorm003   \n",
       "171905    Clint Hurdle     mathm001   Mike Matheny           broxj001   \n",
       "171906   Don Mattingly     baked002    Dusty Baker           schem001   \n",
       "\n",
       "       winning_pitcher_name losing_pitcher_id losing_pitcher_name  \\\n",
       "171902      Tyler Thornburg          rusic001         Chris Rusin   \n",
       "171903        Colton Murray          goede001        Erik Goeddel   \n",
       "171904           Matt Moore          maedk001         Kenta Maeda   \n",
       "171905     Jonathan Broxton          nicaj001        Juan Nicasio   \n",
       "171906         Max Scherzer          brica001        Austin Brice   \n",
       "\n",
       "       saving_pitcher_id saving_pitcher_name winning_rbi_batter_id  \\\n",
       "171902          knebc001        Corey Knebel              susaa001   \n",
       "171903          nerih001        Hector Neris              hernc005   \n",
       "171904               NaN                 NaN              poseb001   \n",
       "171905               NaN                 NaN              piscs001   \n",
       "171906          melam001       Mark Melancon              difow001   \n",
       "\n",
       "       winning_rbi_batter_id_name v_starting_pitcher_id  \\\n",
       "171902               Andrew Susac              cravt001   \n",
       "171903            Cesar Hernandez              ynoag001   \n",
       "171904               Buster Posey              maedk001   \n",
       "171905           Stephen Piscotty              voger001   \n",
       "171906                Wilmer Difo              koeht001   \n",
       "\n",
       "       v_starting_pitcher_name h_starting_pitcher_id h_starting_pitcher_name  \\\n",
       "171902             Tyler Cravy              marqg001          German Marquez   \n",
       "171903            Gabriel Ynoa              eickj001          Jerad Eickhoff   \n",
       "171904             Kenta Maeda              moorm003              Matt Moore   \n",
       "171905          Ryan Vogelsong              waina001         Adam Wainwright   \n",
       "171906             Tom Koehler              schem001            Max Scherzer   \n",
       "\n",
       "       v_player_1_id    v_player_1_name  v_player_1_def_pos v_player_2_id  \\\n",
       "171902      villj001    Jonathan Villar                 5.0      genns001   \n",
       "171903      granc001  Curtis Granderson                 8.0      cabra002   \n",
       "171904      kendh001     Howie Kendrick                 7.0      turnj001   \n",
       "171905      jasoj001          John Jaso                 3.0      polag001   \n",
       "171906      gordd002         Dee Gordon                 4.0      telit001   \n",
       "\n",
       "         v_player_2_name  v_player_2_def_pos v_player_3_id   v_player_3_name  \\\n",
       "171902   Scooter Gennett                 4.0      cartc002      Chris Carter   \n",
       "171903  Asdrubal Cabrera                 6.0      brucj001         Jay Bruce   \n",
       "171904     Justin Turner                 5.0      seagc001      Corey Seager   \n",
       "171905   Gregory Polanco                 9.0      mccua001  Andrew McCutchen   \n",
       "171906       Tomas Telis                 2.0      pradm001      Martin Prado   \n",
       "\n",
       "        v_player_3_def_pos v_player_4_id   v_player_4_name  \\\n",
       "171902                 3.0      santd002   Domingo Santana   \n",
       "171903                 9.0      dudal001        Lucas Duda   \n",
       "171904                 6.0      puigy001       Yasiel Puig   \n",
       "171905                 8.0      kangj001      Jung Ho Kang   \n",
       "171906                 5.0      yelic001  Christian Yelich   \n",
       "\n",
       "        v_player_4_def_pos v_player_5_id  v_player_5_name  v_player_5_def_pos  \\\n",
       "171902                 9.0      pereh001     Hernan Perez                 8.0   \n",
       "171903                 3.0      johnk003    Kelly Johnson                 4.0   \n",
       "171904                 9.0      gonza003  Adrian Gonzalez                 3.0   \n",
       "171905                 5.0      joycm001       Matt Joyce                 7.0   \n",
       "171906                 8.0      bourj002      Justin Bour                 3.0   \n",
       "\n",
       "       v_player_6_id   v_player_6_name  v_player_6_def_pos v_player_7_id  \\\n",
       "171902      arcio002     Orlando Arcia                 6.0      susaa001   \n",
       "171903      confm001  Michael Conforto                 7.0      campe001   \n",
       "171904      grany001   Yasmani Grandal                 2.0      pedej001   \n",
       "171905      hansa001       Alen Hanson                 4.0      fryee001   \n",
       "171906      scrux001    Xavier Scruggs                 7.0      hoodd001   \n",
       "\n",
       "       v_player_7_name  v_player_7_def_pos v_player_8_id     v_player_8_name  \\\n",
       "171902    Andrew Susac                 2.0      elmoj001         Jake Elmore   \n",
       "171903   Eric Campbell                 5.0      plawk001      Kevin Plawecki   \n",
       "171904    Joc Pederson                 8.0      utlec001         Chase Utley   \n",
       "171905      Eric Fryer                 2.0      florp001      Pedro Florimon   \n",
       "171906     Destin Hood                 9.0      hecha001  Adeiny Hechavarria   \n",
       "\n",
       "        v_player_8_def_pos v_player_9_id v_player_9_name  v_player_9_def_pos  \\\n",
       "171902                 7.0      cravt001     Tyler Cravy                 1.0   \n",
       "171903                 2.0      ynoag001    Gabriel Ynoa                 1.0   \n",
       "171904                 4.0      maedk001     Kenta Maeda                 1.0   \n",
       "171905                 6.0      voger001  Ryan Vogelsong                 1.0   \n",
       "171906                 6.0      koeht001     Tom Koehler                 1.0   \n",
       "\n",
       "       h_player_1_id   h_player_1_name  h_player_1_def_pos h_player_2_id  \\\n",
       "171902      blacc001  Charlie Blackmon                 8.0      dahld001   \n",
       "171903      hernc005   Cesar Hernandez                 4.0      parej002   \n",
       "171904      spand001       Denard Span                 8.0      beltb001   \n",
       "171905      carpm002    Matt Carpenter                 3.0      diaza003   \n",
       "171906      turnt001       Trea Turner                 8.0      reveb001   \n",
       "\n",
       "       h_player_2_name  h_player_2_def_pos h_player_3_id h_player_3_name  \\\n",
       "171902      David Dahl                 7.0      arenn001   Nolan Arenado   \n",
       "171903   Jimmy Paredes                 7.0      herro001  Odubel Herrera   \n",
       "171904    Brandon Belt                 3.0      poseb001    Buster Posey   \n",
       "171905    Aledmys Diaz                 6.0      moliy001   Yadier Molina   \n",
       "171906      Ben Revere                 7.0      harpb003    Bryce Harper   \n",
       "\n",
       "        h_player_3_def_pos h_player_4_id   h_player_4_name  \\\n",
       "171902                 5.0      gonzc001   Carlos Gonzalez   \n",
       "171903                 8.0      franm004     Maikel Franco   \n",
       "171904                 2.0      pench001      Hunter Pence   \n",
       "171905                 2.0      piscs001  Stephen Piscotty   \n",
       "171906                 9.0      zimmr001    Ryan Zimmerman   \n",
       "\n",
       "        h_player_4_def_pos h_player_5_id   h_player_5_name  \\\n",
       "171902                 9.0      murpt002        Tom Murphy   \n",
       "171903                 5.0      howar001       Ryan Howard   \n",
       "171904                 9.0      crawb001  Brandon Crawford   \n",
       "171905                 9.0      peraj001    Jhonny Peralta   \n",
       "171906                 3.0      drews001      Stephen Drew   \n",
       "\n",
       "        h_player_5_def_pos h_player_6_id   h_player_6_name  \\\n",
       "171902                 2.0      pattj005  Jordan Patterson   \n",
       "171903                 3.0      ruppc001      Cameron Rupp   \n",
       "171904                 6.0      pagaa001       Angel Pagan   \n",
       "171905                 5.0      mossb001      Brandon Moss   \n",
       "171906                 5.0      difow001       Wilmer Difo   \n",
       "\n",
       "        h_player_6_def_pos h_player_7_id h_player_7_name  h_player_7_def_pos  \\\n",
       "171902                 3.0      valap001     Pat Valaika                 4.0   \n",
       "171903                 2.0      blana001   Andres Blanco                 6.0   \n",
       "171904                 7.0      panij002       Joe Panik                 4.0   \n",
       "171905                 7.0      gyorj001     Jedd Gyorko                 4.0   \n",
       "171906                 4.0      espid001  Danny Espinosa                 6.0   \n",
       "\n",
       "       h_player_8_id   h_player_8_name  h_player_8_def_pos h_player_9_id  \\\n",
       "171902      adamc001  Cristhian Adames                 6.0      marqg001   \n",
       "171903      altha001     Aaron Altherr                 9.0      eickj001   \n",
       "171904      gillc001   Conor Gillaspie                 5.0      moorm003   \n",
       "171905      gricr001    Randal Grichuk                 8.0      waina001   \n",
       "171906      lobaj001      Jose Lobaton                 2.0      schem001   \n",
       "\n",
       "        h_player_9_name  h_player_9_def_pos additional_info acquisition_info  \n",
       "171902   German Marquez                 1.0             NaN                Y  \n",
       "171903   Jerad Eickhoff                 1.0             NaN                Y  \n",
       "171904       Matt Moore                 1.0             NaN                Y  \n",
       "171905  Adam Wainwright                 1.0             NaN                Y  \n",
       "171906     Max Scherzer                 1.0             NaN                Y  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "It looks like the game log has a record of over 170,000 games.  It looks like these games are chronologically ordered and occur between 1871 and 2016.\n",
    "\n",
    "For each game we have the following:\n",
    "\n",
    "- General information on the game\n",
    "- Team level stats for each team\n",
    "- A list of players from each team, numbered, with their defensive positions\n",
    "- The umpires who officiated the game\n",
    "- Some awards, like winning and losing pitcher\n",
    "\n",
    "We have a `game_log_fields.txt` file that tells us that the player number corresponds to the order in which they batted.\n",
    "\n",
    "It's worth noting that there is no natural primary key column for this table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(20494, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>last</th>\n",
       "      <th>first</th>\n",
       "      <th>player_debut</th>\n",
       "      <th>mgr_debut</th>\n",
       "      <th>coach_debut</th>\n",
       "      <th>ump_debut</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>aardd001</td>\n",
       "      <td>Aardsma</td>\n",
       "      <td>David</td>\n",
       "      <td>04/06/2004</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>aaroh101</td>\n",
       "      <td>Aaron</td>\n",
       "      <td>Hank</td>\n",
       "      <td>04/13/1954</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>aarot101</td>\n",
       "      <td>Aaron</td>\n",
       "      <td>Tommie</td>\n",
       "      <td>04/10/1962</td>\n",
       "      <td>NaN</td>\n",
       "      <td>04/06/1979</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aased001</td>\n",
       "      <td>Aase</td>\n",
       "      <td>Don</td>\n",
       "      <td>07/26/1977</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>abada001</td>\n",
       "      <td>Abad</td>\n",
       "      <td>Andy</td>\n",
       "      <td>09/10/2001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id     last   first player_debut mgr_debut coach_debut ump_debut\n",
       "0  aardd001  Aardsma   David   04/06/2004       NaN         NaN       NaN\n",
       "1  aaroh101    Aaron    Hank   04/13/1954       NaN         NaN       NaN\n",
       "2  aarot101    Aaron  Tommie   04/10/1962       NaN  04/06/1979       NaN\n",
       "3  aased001     Aase     Don   07/26/1977       NaN         NaN       NaN\n",
       "4  abada001     Abad    Andy   09/10/2001       NaN         NaN       NaN"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "person = pd.read_csv('person_codes.csv')\n",
    "print(person.shape)\n",
    "person.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "This seems to be a list of people with IDs. The IDs look like they match up with those used in the game log. There are debut dates for players, managers, coaches, and umpires. We can see that some people might have played one or more of these roles.\n",
    "\n",
    "It also looks like coaches and managers are two different things in baseball. After some research, managers are what we would called a *coach* or *head coach* in other sports, and coaches are more specialized, like base coaches.  It also seems that coaches aren't recorded in the game log."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(252, 9)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>park_id</th>\n",
       "      <th>name</th>\n",
       "      <th>aka</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "      <th>league</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALB01</td>\n",
       "      <td>Riverside Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albany</td>\n",
       "      <td>NY</td>\n",
       "      <td>09/11/1880</td>\n",
       "      <td>05/30/1882</td>\n",
       "      <td>NL</td>\n",
       "      <td>TRN:9/11/80;6/15&amp;9/10/1881;5/16-5/18&amp;5/30/1882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALT01</td>\n",
       "      <td>Columbia Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Altoona</td>\n",
       "      <td>PA</td>\n",
       "      <td>04/30/1884</td>\n",
       "      <td>05/31/1884</td>\n",
       "      <td>UA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANA01</td>\n",
       "      <td>Angel Stadium of Anaheim</td>\n",
       "      <td>Edison Field; Anaheim Stadium</td>\n",
       "      <td>Anaheim</td>\n",
       "      <td>CA</td>\n",
       "      <td>04/19/1966</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ARL01</td>\n",
       "      <td>Arlington Stadium</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Arlington</td>\n",
       "      <td>TX</td>\n",
       "      <td>04/21/1972</td>\n",
       "      <td>10/03/1993</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ARL02</td>\n",
       "      <td>Rangers Ballpark in Arlington</td>\n",
       "      <td>The Ballpark in Arlington; Ameriquest Fl</td>\n",
       "      <td>Arlington</td>\n",
       "      <td>TX</td>\n",
       "      <td>04/11/1994</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  park_id                           name  \\\n",
       "0   ALB01                 Riverside Park   \n",
       "1   ALT01                  Columbia Park   \n",
       "2   ANA01       Angel Stadium of Anaheim   \n",
       "3   ARL01              Arlington Stadium   \n",
       "4   ARL02  Rangers Ballpark in Arlington   \n",
       "\n",
       "                                        aka       city state       start  \\\n",
       "0                                       NaN     Albany    NY  09/11/1880   \n",
       "1                                       NaN    Altoona    PA  04/30/1884   \n",
       "2             Edison Field; Anaheim Stadium    Anaheim    CA  04/19/1966   \n",
       "3                                       NaN  Arlington    TX  04/21/1972   \n",
       "4  The Ballpark in Arlington; Ameriquest Fl  Arlington    TX  04/11/1994   \n",
       "\n",
       "          end league                                           notes  \n",
       "0  05/30/1882     NL  TRN:9/11/80;6/15&9/10/1881;5/16-5/18&5/30/1882  \n",
       "1  05/31/1884     UA                                             NaN  \n",
       "2         NaN     AL                                             NaN  \n",
       "3  10/03/1993     AL                                             NaN  \n",
       "4         NaN     AL                                             NaN  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "park = pd.read_csv('park_codes.csv')\n",
    "print(park.shape)\n",
    "park.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "This seems to be a list of all baseball parks.  There are IDs that seem to match with the game log, as well as names, nicknames, city, and league."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(150, 8)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team_id</th>\n",
       "      <th>league</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "      <th>city</th>\n",
       "      <th>nickname</th>\n",
       "      <th>franch_id</th>\n",
       "      <th>seq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALT</td>\n",
       "      <td>UA</td>\n",
       "      <td>1884</td>\n",
       "      <td>1884</td>\n",
       "      <td>Altoona</td>\n",
       "      <td>Mountain Cities</td>\n",
       "      <td>ALT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ARI</td>\n",
       "      <td>NL</td>\n",
       "      <td>1998</td>\n",
       "      <td>0</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Diamondbacks</td>\n",
       "      <td>ARI</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BFN</td>\n",
       "      <td>NL</td>\n",
       "      <td>1879</td>\n",
       "      <td>1885</td>\n",
       "      <td>Buffalo</td>\n",
       "      <td>Bisons</td>\n",
       "      <td>BFN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BFP</td>\n",
       "      <td>PL</td>\n",
       "      <td>1890</td>\n",
       "      <td>1890</td>\n",
       "      <td>Buffalo</td>\n",
       "      <td>Bisons</td>\n",
       "      <td>BFP</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BL1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1872</td>\n",
       "      <td>1874</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>Canaries</td>\n",
       "      <td>BL1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  team_id league  start   end       city         nickname franch_id  seq\n",
       "0     ALT     UA   1884  1884    Altoona  Mountain Cities       ALT    1\n",
       "1     ARI     NL   1998     0    Arizona     Diamondbacks       ARI    1\n",
       "2     BFN     NL   1879  1885    Buffalo           Bisons       BFN    1\n",
       "3     BFP     PL   1890  1890    Buffalo           Bisons       BFP    1\n",
       "4     BL1    NaN   1872  1874  Baltimore         Canaries       BL1    1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team = pd.read_csv('team_codes.csv')\n",
    "print(team.shape)\n",
    "team.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "This seems to be a list of all teams, with team_ids that seem to match the game log. Interestingly, there is a `franch_id`, let's take a look at this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "BS1    4\n",
       "TRN    3\n",
       "LAA    3\n",
       "SE1    3\n",
       "BR3    3\n",
       "Name: franch_id, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team[\"franch_id\"].value_counts().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "We might have `franch_id` occurring a few times for some teams. Let's look at the first one in more detail."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team_id</th>\n",
       "      <th>league</th>\n",
       "      <th>start</th>\n",
       "      <th>end</th>\n",
       "      <th>city</th>\n",
       "      <th>nickname</th>\n",
       "      <th>franch_id</th>\n",
       "      <th>seq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>BS1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1871</td>\n",
       "      <td>1875</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Braves</td>\n",
       "      <td>BS1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>BSN</td>\n",
       "      <td>NL</td>\n",
       "      <td>1876</td>\n",
       "      <td>1952</td>\n",
       "      <td>Boston</td>\n",
       "      <td>Braves</td>\n",
       "      <td>BS1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>MLN</td>\n",
       "      <td>NL</td>\n",
       "      <td>1953</td>\n",
       "      <td>1965</td>\n",
       "      <td>Milwaukee</td>\n",
       "      <td>Braves</td>\n",
       "      <td>BS1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ATL</td>\n",
       "      <td>NL</td>\n",
       "      <td>1966</td>\n",
       "      <td>0</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>Braves</td>\n",
       "      <td>BS1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   team_id league  start   end       city nickname franch_id  seq\n",
       "21     BS1    NaN   1871  1875     Boston   Braves       BS1    1\n",
       "22     BSN     NL   1876  1952     Boston   Braves       BS1    2\n",
       "23     MLN     NL   1953  1965  Milwaukee   Braves       BS1    3\n",
       "24     ATL     NL   1966     0    Atlanta   Braves       BS1    4"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team[team[\"franch_id\"] == 'BS1']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "It appears that teams move between leagues and cities.  The team_id changes when this happens, `franch_id` (which is probably *Franchise*) helps us tie all of this together."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**Defensive Positions**\n",
    "\n",
    "In the game log, each player has a defensive position listed, which seems to be a number between 1-10. Doing some research, we find [this article](http://probaseballinsider.com/baseball-instruction/baseball-basics/baseball-basics-positions/), which gives us a list of names for each numbered position:\n",
    "\n",
    "1. Pitcher\n",
    "2. Catcher\n",
    "3. 1st Base\n",
    "4. 2nd Base\n",
    "5. 3rd Base\n",
    "6. Shortstop\n",
    "7. Left Field\n",
    "8. Center Field\n",
    "9. Right Field\n",
    "\n",
    "The 10th position isn't included. It may be a way of describing a designated hitter that does not field. We can find a retrosheet page that indicates that position `0` is used for this, but we don't have any position 0 in our data. We have chosen to make this an *Unknown Position*, so we're not including data based on a hunch.\n",
    "\n",
    "**Leagues**\n",
    "\n",
    "Wikipedia tells us there are currently two leagues — the American (AL) and National (NL). Let's start by determining which leagues are listed in the main game log:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NL    88867\n",
       "AL    74712\n",
       "AA     5039\n",
       "FL     1243\n",
       "PL      532\n",
       "UA      428\n",
       "Name: h_league, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "log[\"h_league\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "It looks like most of our games fall into the two current leagues, but there are four other leagues. Let's write a quick function to get some info on the years of these leagues:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "nan went from nan to nan\n",
      "NL went from 18760422 to 20161002\n",
      "AA went from 18820502 to 18911006\n",
      "UA went from 18840417 to 18841019\n",
      "PL went from 18900419 to 18901004\n",
      "AL went from 19010424 to 20161002\n",
      "FL went from 19140413 to 19151003\n"
     ]
    }
   ],
   "source": [
    "def league_info(league):\n",
    "    league_games = log[log[\"h_league\"] == league]\n",
    "    earliest = league_games[\"date\"].min()\n",
    "    latest = league_games[\"date\"].max()\n",
    "    print(\"{} went from {} to {}\".format(league,earliest,latest))\n",
    "\n",
    "for league in log[\"h_league\"].unique():\n",
    "    league_info(league)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Now we have some years, which will help us do some research. After some googling we come up with this list:\n",
    "\n",
    "- `NL`: National League\n",
    "- `AL`: American League\n",
    "- `AA`: [American Association](https://en.wikipedia.org/wiki/American_Association_%2819th_century%29)\n",
    "- `FL`: [Federal League](https://en.wikipedia.org/wiki/Federal_League)\n",
    "- `PL`: [Players League](https://en.wikipedia.org/wiki/Players%27_League)\n",
    "- `UA`: [Union Association](https://en.wikipedia.org/wiki/Union_Association)\n",
    "\n",
    "It also looks like we have about 1,000 games where the home team doesn't have a value for league."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing Data into SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# These helper functions will be useful as we work\n",
    "# with the SQLite database from python\n",
    "\n",
    "DB = \"mlb.db\"\n",
    "\n",
    "def run_query(q):\n",
    "    with sqlite3.connect(DB) as conn:\n",
    "        return pd.read_sql(q,conn)\n",
    "\n",
    "def run_command(c):\n",
    "    with sqlite3.connect(DB) as conn:\n",
    "        conn.execute('PRAGMA foreign_keys = ON;')\n",
    "        conn.isolation_level = None\n",
    "        conn.execute(c)\n",
    "\n",
    "def show_tables():\n",
    "    q = '''\n",
    "    SELECT\n",
    "        name,\n",
    "        type\n",
    "    FROM sqlite_master\n",
    "    WHERE type IN (\"table\",\"view\");\n",
    "    '''\n",
    "    return run_query(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tables = {\n",
    "    \"game_log\": log,\n",
    "    \"person_codes\": person,\n",
    "    \"team_codes\": team,\n",
    "    \"park_codes\": park\n",
    "}\n",
    "\n",
    "with sqlite3.connect(DB) as conn:    \n",
    "    for name, data in tables.items():\n",
    "        conn.execute(\"DROP TABLE IF EXISTS {};\".format(name))\n",
    "        data.to_sql(name,conn,index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>game_log</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>park_codes</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>team_codes</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>person_codes</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name   type\n",
       "0      game_log  table\n",
       "1    park_codes  table\n",
       "2    team_codes  table\n",
       "3  person_codes  table"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "show_tables()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "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_id</th>\n",
       "      <th>date</th>\n",
       "      <th>h_name</th>\n",
       "      <th>number_of_game</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18710504FW10</td>\n",
       "      <td>18710504</td>\n",
       "      <td>FW1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18710505WS30</td>\n",
       "      <td>18710505</td>\n",
       "      <td>WS3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18710506RC10</td>\n",
       "      <td>18710506</td>\n",
       "      <td>RC1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18710508CH10</td>\n",
       "      <td>18710508</td>\n",
       "      <td>CH1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18710509TRO0</td>\n",
       "      <td>18710509</td>\n",
       "      <td>TRO</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        game_id      date h_name  number_of_game\n",
       "0  18710504FW10  18710504    FW1               0\n",
       "1  18710505WS30  18710505    WS3               0\n",
       "2  18710506RC10  18710506    RC1               0\n",
       "3  18710508CH10  18710508    CH1               0\n",
       "4  18710509TRO0  18710509    TRO               0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1 = \"\"\"\n",
    "ALTER TABLE game_log\n",
    "ADD COLUMN game_id TEXT;\n",
    "\"\"\"\n",
    "\n",
    "# try/except loop since ALTER TABLE\n",
    "# doesn't support IF NOT EXISTS\n",
    "try:\n",
    "    run_command(c1)\n",
    "except:\n",
    "    pass\n",
    "\n",
    "c2 = \"\"\"\n",
    "UPDATE game_log\n",
    "SET game_id = date || h_name || number_of_game\n",
    "/* WHERE prevents this if it has already been done */\n",
    "WHERE game_id IS NULL; \n",
    "\"\"\"\n",
    "\n",
    "run_command(c2)\n",
    "\n",
    "q = \"\"\"\n",
    "SELECT\n",
    "    game_id,\n",
    "    date,\n",
    "    h_name,\n",
    "    number_of_game\n",
    "FROM game_log\n",
    "LIMIT 5;\n",
    "\"\"\"\n",
    "\n",
    "run_query(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Looking for Normalization Opportunities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "The following are opportunities for normalization of our data:\n",
    "\n",
    "- In `person_codes`, all the debut dates will be able to be reproduced using game log data.\n",
    "- In `team_codes`, the start, end, and sequence columns will be able to be reproduced using game log data.\n",
    "- In `park_codes`, the start and end years will be able to be reproduced using game log data. While technically the state is an attribute of the city, we might not want to have a an incomplete city/state table, so we will leave this in.\n",
    "- There are many places in `game` log where we have a player ID followed by the players name. We will be able to remove this and use the name data in `person_codes`.\n",
    "- In `game_log`, all offensive and defensive stats are repeated for the home team and the visiting team. We could break these out and have a table that lists each game twice, one for each team, and cut out this column repetition.\n",
    "- Similarly, in `game_log`, we have a listing for 9 players on each team with their positions — we can remove these and have one table that tracks player appearances and their positions.\n",
    "- We can do a similar thing with the umpires from `game_log`. Instead of listing all four positions as columns, we can put the umpires either in their own table or make one table for players, umpires, and managers.\n",
    "- We have several awards in `game_log`, like winning pitcher and losing pitcher. We can either break these out into their own table, have a table for awards, or combine the awards in with general appearances like the players and umpires."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Planning a Normalized Schema\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "The following schema was planned using [DbDesigner.net](https://dbdesigner.net/):\n",
    "\n",
    "![schema](images/schema-screenshot.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Tables Without Foreign Keys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>person_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>aardd001</td>\n",
       "      <td>David</td>\n",
       "      <td>Aardsma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>aaroh101</td>\n",
       "      <td>Hank</td>\n",
       "      <td>Aaron</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>aarot101</td>\n",
       "      <td>Tommie</td>\n",
       "      <td>Aaron</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>aased001</td>\n",
       "      <td>Don</td>\n",
       "      <td>Aase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>abada001</td>\n",
       "      <td>Andy</td>\n",
       "      <td>Abad</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  person_id first_name last_name\n",
       "0  aardd001      David   Aardsma\n",
       "1  aaroh101       Hank     Aaron\n",
       "2  aarot101     Tommie     Aaron\n",
       "3  aased001        Don      Aase\n",
       "4  abada001       Andy      Abad"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1 = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS person (\n",
    "    person_id TEXT PRIMARY KEY,\n",
    "    first_name TEXT,\n",
    "    last_name TEXT\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "c2 = \"\"\"\n",
    "INSERT OR IGNORE INTO person\n",
    "SELECT\n",
    "    id,\n",
    "    first,\n",
    "    last\n",
    "FROM person_codes;\n",
    "\"\"\"\n",
    "\n",
    "q = \"\"\"\n",
    "SELECT * FROM person\n",
    "LIMIT 5;\n",
    "\"\"\"\n",
    "\n",
    "run_command(c1)\n",
    "run_command(c2)\n",
    "run_query(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>park_id</th>\n",
       "      <th>name</th>\n",
       "      <th>nickname</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALB01</td>\n",
       "      <td>Riverside Park</td>\n",
       "      <td>None</td>\n",
       "      <td>Albany</td>\n",
       "      <td>NY</td>\n",
       "      <td>TRN:9/11/80;6/15&amp;9/10/1881;5/16-5/18&amp;5/30/1882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ALT01</td>\n",
       "      <td>Columbia Park</td>\n",
       "      <td>None</td>\n",
       "      <td>Altoona</td>\n",
       "      <td>PA</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANA01</td>\n",
       "      <td>Angel Stadium of Anaheim</td>\n",
       "      <td>Edison Field; Anaheim Stadium</td>\n",
       "      <td>Anaheim</td>\n",
       "      <td>CA</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ARL01</td>\n",
       "      <td>Arlington Stadium</td>\n",
       "      <td>None</td>\n",
       "      <td>Arlington</td>\n",
       "      <td>TX</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ARL02</td>\n",
       "      <td>Rangers Ballpark in Arlington</td>\n",
       "      <td>The Ballpark in Arlington; Ameriquest Fl</td>\n",
       "      <td>Arlington</td>\n",
       "      <td>TX</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  park_id                           name  \\\n",
       "0   ALB01                 Riverside Park   \n",
       "1   ALT01                  Columbia Park   \n",
       "2   ANA01       Angel Stadium of Anaheim   \n",
       "3   ARL01              Arlington Stadium   \n",
       "4   ARL02  Rangers Ballpark in Arlington   \n",
       "\n",
       "                                   nickname       city state  \\\n",
       "0                                      None     Albany    NY   \n",
       "1                                      None    Altoona    PA   \n",
       "2             Edison Field; Anaheim Stadium    Anaheim    CA   \n",
       "3                                      None  Arlington    TX   \n",
       "4  The Ballpark in Arlington; Ameriquest Fl  Arlington    TX   \n",
       "\n",
       "                                            notes  \n",
       "0  TRN:9/11/80;6/15&9/10/1881;5/16-5/18&5/30/1882  \n",
       "1                                            None  \n",
       "2                                            None  \n",
       "3                                            None  \n",
       "4                                            None  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1 = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS park (\n",
    "    park_id TEXT PRIMARY KEY,\n",
    "    name TEXT,\n",
    "    nickname TEXT,\n",
    "    city TEXT,\n",
    "    state TEXT,\n",
    "    notes TEXT\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "c2 = \"\"\"\n",
    "INSERT OR IGNORE INTO park\n",
    "SELECT\n",
    "    park_id,\n",
    "    name,\n",
    "    aka,\n",
    "    city,\n",
    "    state,\n",
    "    notes\n",
    "FROM park_codes;\n",
    "\"\"\"\n",
    "\n",
    "q = \"\"\"\n",
    "SELECT * FROM park\n",
    "LIMIT 5;\n",
    "\"\"\"\n",
    "\n",
    "run_command(c1)\n",
    "run_command(c2)\n",
    "run_query(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>league_id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NL</td>\n",
       "      <td>National League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>American League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA</td>\n",
       "      <td>American Association</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>FL</td>\n",
       "      <td>Federal League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PL</td>\n",
       "      <td>Players League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>UA</td>\n",
       "      <td>Union Association</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  league_id                  name\n",
       "0        NL       National League\n",
       "1        AL       American League\n",
       "2        AA  American Association\n",
       "3        FL        Federal League\n",
       "4        PL        Players League\n",
       "5        UA     Union Association"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1 = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS league (\n",
    "    league_id TEXT PRIMARY KEY,\n",
    "    name TEXT\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "c2 = \"\"\"\n",
    "INSERT OR IGNORE INTO league\n",
    "VALUES\n",
    "    (\"NL\", \"National League\"),\n",
    "    (\"AL\", \"American League\"),\n",
    "    (\"AA\", \"American Association\"),\n",
    "    (\"FL\", \"Federal League\"),\n",
    "    (\"PL\", \"Players League\"),\n",
    "    (\"UA\", \"Union Association\")\n",
    ";\n",
    "\"\"\"\n",
    "\n",
    "q = \"\"\"\n",
    "SELECT * FROM league\n",
    "\"\"\"\n",
    "\n",
    "run_command(c1)\n",
    "run_command(c2)\n",
    "run_query(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>appearance_type_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>O1</td>\n",
       "      <td>Batter 1</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>O2</td>\n",
       "      <td>Batter 2</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>O3</td>\n",
       "      <td>Batter 3</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>O4</td>\n",
       "      <td>Batter 4</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>O5</td>\n",
       "      <td>Batter 5</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>O6</td>\n",
       "      <td>Batter 6</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>O7</td>\n",
       "      <td>Batter 7</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>O8</td>\n",
       "      <td>Batter 8</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>O9</td>\n",
       "      <td>Batter 9</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>D1</td>\n",
       "      <td>Pitcher</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>D2</td>\n",
       "      <td>Catcher</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>D3</td>\n",
       "      <td>1st Base</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>D4</td>\n",
       "      <td>2nd Base</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>D5</td>\n",
       "      <td>3rd Base</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>D6</td>\n",
       "      <td>Shortstop</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>D7</td>\n",
       "      <td>Left Field</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>D8</td>\n",
       "      <td>Center Field</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>D9</td>\n",
       "      <td>Right Field</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>D10</td>\n",
       "      <td>Unknown Position</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>UHP</td>\n",
       "      <td>Home Plate</td>\n",
       "      <td>umpire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>U1B</td>\n",
       "      <td>First Base</td>\n",
       "      <td>umpire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>U2B</td>\n",
       "      <td>Second Base</td>\n",
       "      <td>umpire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>U3B</td>\n",
       "      <td>Third Base</td>\n",
       "      <td>umpire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>ULF</td>\n",
       "      <td>Left Field</td>\n",
       "      <td>umpire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>URF</td>\n",
       "      <td>Right Field</td>\n",
       "      <td>umpire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>MM</td>\n",
       "      <td>Manager</td>\n",
       "      <td>manager</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>AWP</td>\n",
       "      <td>Winning Pitcher</td>\n",
       "      <td>award</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>ALP</td>\n",
       "      <td>Losing Pitcher</td>\n",
       "      <td>award</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>ASP</td>\n",
       "      <td>Saving Pitcher</td>\n",
       "      <td>award</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AWB</td>\n",
       "      <td>Winning RBI Batter</td>\n",
       "      <td>award</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>PSP</td>\n",
       "      <td>Starting Pitcher</td>\n",
       "      <td>pitcher</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   appearance_type_id                name category\n",
       "0                  O1            Batter 1  offense\n",
       "1                  O2            Batter 2  offense\n",
       "2                  O3            Batter 3  offense\n",
       "3                  O4            Batter 4  offense\n",
       "4                  O5            Batter 5  offense\n",
       "5                  O6            Batter 6  offense\n",
       "6                  O7            Batter 7  offense\n",
       "7                  O8            Batter 8  offense\n",
       "8                  O9            Batter 9  offense\n",
       "9                  D1             Pitcher  defense\n",
       "10                 D2             Catcher  defense\n",
       "11                 D3            1st Base  defense\n",
       "12                 D4            2nd Base  defense\n",
       "13                 D5            3rd Base  defense\n",
       "14                 D6           Shortstop  defense\n",
       "15                 D7          Left Field  defense\n",
       "16                 D8        Center Field  defense\n",
       "17                 D9         Right Field  defense\n",
       "18                D10    Unknown Position  defense\n",
       "19                UHP          Home Plate   umpire\n",
       "20                U1B          First Base   umpire\n",
       "21                U2B         Second Base   umpire\n",
       "22                U3B          Third Base   umpire\n",
       "23                ULF          Left Field   umpire\n",
       "24                URF         Right Field   umpire\n",
       "25                 MM             Manager  manager\n",
       "26                AWP     Winning Pitcher    award\n",
       "27                ALP      Losing Pitcher    award\n",
       "28                ASP      Saving Pitcher    award\n",
       "29                AWB  Winning RBI Batter    award\n",
       "30                PSP    Starting Pitcher  pitcher"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1 = \"DROP TABLE IF EXISTS appearance_type;\"\n",
    "\n",
    "run_command(c1)\n",
    "\n",
    "c2 = \"\"\"\n",
    "CREATE TABLE appearance_type (\n",
    "    appearance_type_id TEXT PRIMARY KEY,\n",
    "    name TEXT,\n",
    "    category TEXT\n",
    ");\n",
    "\"\"\"\n",
    "run_command(c2)\n",
    "\n",
    "appearance_type = pd.read_csv('appearance_type.csv')\n",
    "\n",
    "with sqlite3.connect('mlb.db') as conn:\n",
    "    appearance_type.to_sql('appearance_type',\n",
    "                           conn,\n",
    "                           index=False,\n",
    "                           if_exists='append')\n",
    "\n",
    "q = \"\"\"\n",
    "SELECT * FROM appearance_type;\n",
    "\"\"\"\n",
    "\n",
    "run_query(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding the Team and Game Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team_id</th>\n",
       "      <th>league_id</th>\n",
       "      <th>city</th>\n",
       "      <th>nickname</th>\n",
       "      <th>franch_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ALT</td>\n",
       "      <td>UA</td>\n",
       "      <td>Altoona</td>\n",
       "      <td>Mountain Cities</td>\n",
       "      <td>ALT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ARI</td>\n",
       "      <td>NL</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Diamondbacks</td>\n",
       "      <td>ARI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BFN</td>\n",
       "      <td>NL</td>\n",
       "      <td>Buffalo</td>\n",
       "      <td>Bisons</td>\n",
       "      <td>BFN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BFP</td>\n",
       "      <td>PL</td>\n",
       "      <td>Buffalo</td>\n",
       "      <td>Bisons</td>\n",
       "      <td>BFP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BL1</td>\n",
       "      <td>None</td>\n",
       "      <td>Baltimore</td>\n",
       "      <td>Canaries</td>\n",
       "      <td>BL1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  team_id league_id       city         nickname franch_id\n",
       "0     ALT        UA    Altoona  Mountain Cities       ALT\n",
       "1     ARI        NL    Arizona     Diamondbacks       ARI\n",
       "2     BFN        NL    Buffalo           Bisons       BFN\n",
       "3     BFP        PL    Buffalo           Bisons       BFP\n",
       "4     BL1      None  Baltimore         Canaries       BL1"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1 = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS team (\n",
    "    team_id TEXT PRIMARY KEY,\n",
    "    league_id TEXT,\n",
    "    city TEXT,\n",
    "    nickname TEXT,\n",
    "    franch_id TEXT,\n",
    "    FOREIGN KEY (league_id) REFERENCES league(league_id)\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "c2 = \"\"\"\n",
    "INSERT OR IGNORE INTO team\n",
    "SELECT\n",
    "    team_id,\n",
    "    league,\n",
    "    city,\n",
    "    nickname,\n",
    "    franch_id\n",
    "FROM team_codes;\n",
    "\"\"\"\n",
    "\n",
    "q = \"\"\"\n",
    "SELECT * FROM team\n",
    "LIMIT 5;\n",
    "\"\"\"\n",
    "\n",
    "run_command(c1)\n",
    "run_command(c2)\n",
    "run_query(q)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "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_id</th>\n",
       "      <th>date</th>\n",
       "      <th>number_of_game</th>\n",
       "      <th>park_id</th>\n",
       "      <th>length_outs</th>\n",
       "      <th>day</th>\n",
       "      <th>completion</th>\n",
       "      <th>forefeit</th>\n",
       "      <th>protest</th>\n",
       "      <th>attendance</th>\n",
       "      <th>legnth_minutes</th>\n",
       "      <th>additional_info</th>\n",
       "      <th>acquisition_info</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18710504FW10</td>\n",
       "      <td>18710504</td>\n",
       "      <td>0</td>\n",
       "      <td>FOR01</td>\n",
       "      <td>54</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>200</td>\n",
       "      <td>120</td>\n",
       "      <td>None</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18710505WS30</td>\n",
       "      <td>18710505</td>\n",
       "      <td>0</td>\n",
       "      <td>WAS01</td>\n",
       "      <td>54</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5000</td>\n",
       "      <td>145</td>\n",
       "      <td>HTBF</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18710506RC10</td>\n",
       "      <td>18710506</td>\n",
       "      <td>0</td>\n",
       "      <td>RCK01</td>\n",
       "      <td>54</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1000</td>\n",
       "      <td>140</td>\n",
       "      <td>None</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>18710508CH10</td>\n",
       "      <td>18710508</td>\n",
       "      <td>0</td>\n",
       "      <td>CHI01</td>\n",
       "      <td>54</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>5000</td>\n",
       "      <td>150</td>\n",
       "      <td>None</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18710509TRO0</td>\n",
       "      <td>18710509</td>\n",
       "      <td>0</td>\n",
       "      <td>TRO01</td>\n",
       "      <td>54</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3250</td>\n",
       "      <td>145</td>\n",
       "      <td>HTBF</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        game_id      date  number_of_game park_id  length_outs  day  \\\n",
       "0  18710504FW10  18710504               0   FOR01           54    1   \n",
       "1  18710505WS30  18710505               0   WAS01           54    1   \n",
       "2  18710506RC10  18710506               0   RCK01           54    1   \n",
       "3  18710508CH10  18710508               0   CHI01           54    1   \n",
       "4  18710509TRO0  18710509               0   TRO01           54    1   \n",
       "\n",
       "  completion forefeit protest  attendance  legnth_minutes additional_info  \\\n",
       "0       None     None    None         200             120            None   \n",
       "1       None     None    None        5000             145            HTBF   \n",
       "2       None     None    None        1000             140            None   \n",
       "3       None     None    None        5000             150            None   \n",
       "4       None     None    None        3250             145            HTBF   \n",
       "\n",
       "  acquisition_info  \n",
       "0                Y  \n",
       "1                Y  \n",
       "2                Y  \n",
       "3                Y  \n",
       "4                Y  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1 = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS game (\n",
    "    game_id TEXT PRIMARY KEY,\n",
    "    date TEXT,\n",
    "    number_of_game INTEGER,\n",
    "    park_id TEXT,\n",
    "    length_outs INTEGER,\n",
    "    day BOOLEAN,\n",
    "    completion TEXT,\n",
    "    forefeit TEXT,\n",
    "    protest TEXT,\n",
    "    attendance INTEGER,\n",
    "    legnth_minutes INTEGER,\n",
    "    additional_info TEXT,\n",
    "    acquisition_info TEXT,\n",
    "    FOREIGN KEY (park_id) REFERENCES park(park_id)\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "c2 = \"\"\"\n",
    "INSERT OR IGNORE INTO game\n",
    "SELECT\n",
    "    game_id,\n",
    "    date,\n",
    "    number_of_game,\n",
    "    park_id,\n",
    "    length_outs,\n",
    "    CASE\n",
    "        WHEN day_night = \"D\" THEN 1\n",
    "        WHEN day_night = \"N\" THEN 0\n",
    "        ELSE NULL\n",
    "        END\n",
    "        AS day,\n",
    "    completion,\n",
    "    forefeit,\n",
    "    protest,\n",
    "    attendance,\n",
    "    length_minutes,\n",
    "    additional_info,\n",
    "    acquisition_info\n",
    "FROM game_log;\n",
    "\"\"\"\n",
    "\n",
    "q = \"\"\"\n",
    "SELECT * FROM game\n",
    "LIMIT 5;\n",
    "\"\"\"\n",
    "\n",
    "run_command(c1)\n",
    "run_command(c2)\n",
    "run_query(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding the Team Appearance Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team_id</th>\n",
       "      <th>game_id</th>\n",
       "      <th>home</th>\n",
       "      <th>league_id</th>\n",
       "      <th>score</th>\n",
       "      <th>line_score</th>\n",
       "      <th>at_bats</th>\n",
       "      <th>hits</th>\n",
       "      <th>doubles</th>\n",
       "      <th>triples</th>\n",
       "      <th>homeruns</th>\n",
       "      <th>rbi</th>\n",
       "      <th>sacrifice_hits</th>\n",
       "      <th>sacrifice_flies</th>\n",
       "      <th>hit_by_pitch</th>\n",
       "      <th>walks</th>\n",
       "      <th>intentional_walks</th>\n",
       "      <th>strikeouts</th>\n",
       "      <th>stolen_bases</th>\n",
       "      <th>caught_stealing</th>\n",
       "      <th>grounded_into_double</th>\n",
       "      <th>first_catcher_interference</th>\n",
       "      <th>left_on_base</th>\n",
       "      <th>pitchers_used</th>\n",
       "      <th>individual_earned_runs</th>\n",
       "      <th>team_earned_runs</th>\n",
       "      <th>wild_pitches</th>\n",
       "      <th>balks</th>\n",
       "      <th>putouts</th>\n",
       "      <th>assists</th>\n",
       "      <th>errors</th>\n",
       "      <th>passed_balls</th>\n",
       "      <th>double_plays</th>\n",
       "      <th>triple_plays</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CL1</td>\n",
       "      <td>18710504FW10</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>000000000</td>\n",
       "      <td>30</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>FW1</td>\n",
       "      <td>18710504FW10</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>010010000</td>\n",
       "      <td>31</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>0</td>\n",
       "      <td>NL</td>\n",
       "      <td>7</td>\n",
       "      <td>000230020</td>\n",
       "      <td>38</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>24</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>1</td>\n",
       "      <td>NL</td>\n",
       "      <td>10</td>\n",
       "      <td>03023002x</td>\n",
       "      <td>30</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  team_id       game_id  home league_id  score line_score  at_bats  hits  \\\n",
       "0     CL1  18710504FW10     0      None      0  000000000       30     4   \n",
       "1     FW1  18710504FW10     1      None      2  010010000       31     4   \n",
       "2     MIA  20161002WAS0     0        NL      7  000230020       38    14   \n",
       "3     WAS  20161002WAS0     1        NL     10  03023002x       30    10   \n",
       "\n",
       "   doubles  triples  homeruns  rbi  sacrifice_hits  sacrifice_flies  \\\n",
       "0        1        0         0    0               0                0   \n",
       "1        1        0         0    2               0                0   \n",
       "2        1        1         2    7               1                0   \n",
       "3        2        0         1   10               1                1   \n",
       "\n",
       "   hit_by_pitch  walks  intentional_walks  strikeouts  stolen_bases  \\\n",
       "0             0      1                NaN           6             1   \n",
       "1             0      1                NaN           0             0   \n",
       "2             0      3                2.0          10             1   \n",
       "3             1      8                0.0           3             2   \n",
       "\n",
       "   caught_stealing  grounded_into_double  first_catcher_interference  \\\n",
       "0              NaN                    -1                         NaN   \n",
       "1              NaN                    -1                         NaN   \n",
       "2              1.0                     1                         0.0   \n",
       "3              0.0                     1                         0.0   \n",
       "\n",
       "   left_on_base  pitchers_used  individual_earned_runs  team_earned_runs  \\\n",
       "0             4              1                       1                 1   \n",
       "1             3              1                       0                 0   \n",
       "2             8              7                      10                10   \n",
       "3             7              6                       7                 7   \n",
       "\n",
       "   wild_pitches  balks  putouts  assists  errors  passed_balls  double_plays  \\\n",
       "0             0      0       27        9       0             3             0   \n",
       "1             0      0       27        3       3             1             1   \n",
       "2             1      0       24       11       0             0             1   \n",
       "3             1      0       27       11       0             0             1   \n",
       "\n",
       "   triple_plays  \n",
       "0             0  \n",
       "1             0  \n",
       "2             0  \n",
       "3             0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c1 = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS team_appearance (\n",
    "    team_id TEXT,\n",
    "    game_id TEXT,\n",
    "    home BOOLEAN,\n",
    "    league_id TEXT,\n",
    "    score INTEGER,\n",
    "    line_score TEXT,\n",
    "    at_bats INTEGER,\n",
    "    hits INTEGER,\n",
    "    doubles INTEGER,\n",
    "    triples INTEGER,\n",
    "    homeruns INTEGER,\n",
    "    rbi INTEGER,\n",
    "    sacrifice_hits INTEGER,\n",
    "    sacrifice_flies INTEGER,\n",
    "    hit_by_pitch INTEGER,\n",
    "    walks INTEGER,\n",
    "    intentional_walks INTEGER,\n",
    "    strikeouts INTEGER,\n",
    "    stolen_bases INTEGER,\n",
    "    caught_stealing INTEGER,\n",
    "    grounded_into_double INTEGER,\n",
    "    first_catcher_interference INTEGER,\n",
    "    left_on_base INTEGER,\n",
    "    pitchers_used INTEGER,\n",
    "    individual_earned_runs INTEGER,\n",
    "    team_earned_runs INTEGER,\n",
    "    wild_pitches INTEGER,\n",
    "    balks INTEGER,\n",
    "    putouts INTEGER,\n",
    "    assists INTEGER,\n",
    "    errors INTEGER,\n",
    "    passed_balls INTEGER,\n",
    "    double_plays INTEGER,\n",
    "    triple_plays INTEGER,\n",
    "    PRIMARY KEY (team_id, game_id),\n",
    "    FOREIGN KEY (team_id) REFERENCES team(team_id),\n",
    "    FOREIGN KEY (game_id) REFERENCES game(game_id),\n",
    "    FOREIGN KEY (team_id) REFERENCES team(team_id)\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "run_command(c1)\n",
    "\n",
    "c2 = \"\"\"\n",
    "INSERT OR IGNORE INTO team_appearance\n",
    "    SELECT\n",
    "        h_name,\n",
    "        game_id,\n",
    "        1 AS home,\n",
    "        h_league,\n",
    "        h_score,\n",
    "        h_line_score,\n",
    "        h_at_bats,\n",
    "        h_hits,\n",
    "        h_doubles,\n",
    "        h_triples,\n",
    "        h_homeruns,\n",
    "        h_rbi,\n",
    "        h_sacrifice_hits,\n",
    "        h_sacrifice_flies,\n",
    "        h_hit_by_pitch,\n",
    "        h_walks,\n",
    "        h_intentional_walks,\n",
    "        h_strikeouts,\n",
    "        h_stolen_bases,\n",
    "        h_caught_stealing,\n",
    "        h_grounded_into_double,\n",
    "        h_first_catcher_interference,\n",
    "        h_left_on_base,\n",
    "        h_pitchers_used,\n",
    "        h_individual_earned_runs,\n",
    "        h_team_earned_runs,\n",
    "        h_wild_pitches,\n",
    "        h_balks,\n",
    "        h_putouts,\n",
    "        h_assists,\n",
    "        h_errors,\n",
    "        h_passed_balls,\n",
    "        h_double_plays,\n",
    "        h_triple_plays\n",
    "    FROM game_log\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT    \n",
    "        v_name,\n",
    "        game_id,\n",
    "        0 AS home,\n",
    "        v_league,\n",
    "        v_score,\n",
    "        v_line_score,\n",
    "        v_at_bats,\n",
    "        v_hits,\n",
    "        v_doubles,\n",
    "        v_triples,\n",
    "        v_homeruns,\n",
    "        v_rbi,\n",
    "        v_sacrifice_hits,\n",
    "        v_sacrifice_flies,\n",
    "        v_hit_by_pitch,\n",
    "        v_walks,\n",
    "        v_intentional_walks,\n",
    "        v_strikeouts,\n",
    "        v_stolen_bases,\n",
    "        v_caught_stealing,\n",
    "        v_grounded_into_double,\n",
    "        v_first_catcher_interference,\n",
    "        v_left_on_base,\n",
    "        v_pitchers_used,\n",
    "        v_individual_earned_runs,\n",
    "        v_team_earned_runs,\n",
    "        v_wild_pitches,\n",
    "        v_balks,\n",
    "        v_putouts,\n",
    "        v_assists,\n",
    "        v_errors,\n",
    "        v_passed_balls,\n",
    "        v_double_plays,\n",
    "        v_triple_plays\n",
    "    from game_log;\n",
    "\"\"\"\n",
    "\n",
    "run_command(c2)\n",
    "\n",
    "q = \"\"\"\n",
    "SELECT * FROM team_appearance\n",
    "WHERE game_id = (\n",
    "                 SELECT MIN(game_id) from game\n",
    "                )\n",
    "   OR game_id = (\n",
    "                 SELECT MAX(game_id) from game\n",
    "                )\n",
    "ORDER By game_id, home;\n",
    "\"\"\"\n",
    "\n",
    "run_query(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding the Person Appearance Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "c0 = \"DROP TABLE IF EXISTS person_appearance\"\n",
    "\n",
    "run_command(c0)\n",
    "\n",
    "c1 = \"\"\"\n",
    "CREATE TABLE person_appearance (\n",
    "    appearance_id INTEGER PRIMARY KEY,\n",
    "    person_id TEXT,\n",
    "    team_id TEXT,\n",
    "    game_id TEXT,\n",
    "    appearance_type_id,\n",
    "    FOREIGN KEY (person_id) REFERENCES person(person_id),\n",
    "    FOREIGN KEY (team_id) REFERENCES team(team_id),\n",
    "    FOREIGN KEY (game_id) REFERENCES game(game_id),\n",
    "    FOREIGN KEY (appearance_type_id) REFERENCES appearance_type(appearance_type_id)\n",
    ");\n",
    "\"\"\"\n",
    "\n",
    "c2 = \"\"\"\n",
    "INSERT OR IGNORE INTO person_appearance (\n",
    "    game_id,\n",
    "    team_id,\n",
    "    person_id,\n",
    "    appearance_type_id\n",
    ") \n",
    "    SELECT\n",
    "        game_id,\n",
    "        NULL,\n",
    "        hp_umpire_id,\n",
    "        \"UHP\"\n",
    "    FROM game_log\n",
    "    WHERE hp_umpire_id IS NOT NULL    \n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        NULL,\n",
    "        [1b_umpire_id],\n",
    "        \"U1B\"\n",
    "    FROM game_log\n",
    "    WHERE \"1b_umpire_id\" IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        NULL,\n",
    "        [2b_umpire_id],\n",
    "        \"U2B\"\n",
    "    FROM game_log\n",
    "    WHERE [2b_umpire_id] IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        NULL,\n",
    "        [3b_umpire_id],\n",
    "        \"U3B\"\n",
    "    FROM game_log\n",
    "    WHERE [3b_umpire_id] IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        NULL,\n",
    "        lf_umpire_id,\n",
    "        \"ULF\"\n",
    "    FROM game_log\n",
    "    WHERE lf_umpire_id IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        NULL,\n",
    "        rf_umpire_id,\n",
    "        \"URF\"\n",
    "    FROM game_log\n",
    "    WHERE rf_umpire_id IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        v_name,\n",
    "        v_manager_id,\n",
    "        \"MM\"\n",
    "    FROM game_log\n",
    "    WHERE v_manager_id IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        h_name,\n",
    "        h_manager_id,\n",
    "        \"MM\"\n",
    "    FROM game_log\n",
    "    WHERE h_manager_id IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        CASE\n",
    "            WHEN h_score > v_score THEN h_name\n",
    "            ELSE v_name\n",
    "            END,\n",
    "        winning_pitcher_id,\n",
    "        \"AWP\"\n",
    "    FROM game_log\n",
    "    WHERE winning_pitcher_id IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        CASE\n",
    "            WHEN h_score < v_score THEN h_name\n",
    "            ELSE v_name\n",
    "            END,\n",
    "        losing_pitcher_id,\n",
    "        \"ALP\"\n",
    "    FROM game_log\n",
    "    WHERE losing_pitcher_id IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        CASE\n",
    "            WHEN h_score > v_score THEN h_name\n",
    "            ELSE v_name\n",
    "            END,\n",
    "        saving_pitcher_id,\n",
    "        \"ASP\"\n",
    "    FROM game_log\n",
    "    WHERE saving_pitcher_id IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        CASE\n",
    "            WHEN h_score > v_score THEN h_name\n",
    "            ELSE v_name\n",
    "            END,\n",
    "        winning_rbi_batter_id,\n",
    "        \"AWB\"\n",
    "    FROM game_log\n",
    "    WHERE winning_rbi_batter_id IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        v_name,\n",
    "        v_starting_pitcher_id,\n",
    "        \"PSP\"\n",
    "    FROM game_log\n",
    "    WHERE v_starting_pitcher_id IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        h_name,\n",
    "        h_starting_pitcher_id,\n",
    "        \"PSP\"\n",
    "    FROM game_log\n",
    "    WHERE h_starting_pitcher_id IS NOT NULL;\n",
    "\"\"\"\n",
    "\n",
    "template = \"\"\"\n",
    "INSERT INTO person_appearance (\n",
    "    game_id,\n",
    "    team_id,\n",
    "    person_id,\n",
    "    appearance_type_id\n",
    ") \n",
    "    SELECT\n",
    "        game_id,\n",
    "        {hv}_name,\n",
    "        {hv}_player_{num}_id,\n",
    "        \"O{num}\"\n",
    "    FROM game_log\n",
    "    WHERE {hv}_player_{num}_id IS NOT NULL\n",
    "\n",
    "UNION\n",
    "\n",
    "    SELECT\n",
    "        game_id,\n",
    "        {hv}_name,\n",
    "        {hv}_player_{num}_id,\n",
    "        \"D\" || CAST({hv}_player_{num}_def_pos AS INT)\n",
    "    FROM game_log\n",
    "    WHERE {hv}_player_{num}_id IS NOT NULL;\n",
    "\"\"\"\n",
    "\n",
    "run_command(c1)\n",
    "run_command(c2)\n",
    "\n",
    "for hv in [\"h\",\"v\"]:\n",
    "    for num in range(1,10):\n",
    "        query_vars = {\n",
    "            \"hv\": hv,\n",
    "            \"num\": num\n",
    "        }\n",
    "        run_command(template.format(**query_vars))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   games_game\n",
      "0      171907\n",
      "   games_person_appearance\n",
      "0                   171907\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>appearance_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>game_id</th>\n",
       "      <th>appearance_type_id</th>\n",
       "      <th>name</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1646109</td>\n",
       "      <td>porta901</td>\n",
       "      <td>None</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>U1B</td>\n",
       "      <td>First Base</td>\n",
       "      <td>umpire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1646108</td>\n",
       "      <td>onorb901</td>\n",
       "      <td>None</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>U2B</td>\n",
       "      <td>Second Base</td>\n",
       "      <td>umpire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1646107</td>\n",
       "      <td>kellj901</td>\n",
       "      <td>None</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>U3B</td>\n",
       "      <td>Third Base</td>\n",
       "      <td>umpire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1646110</td>\n",
       "      <td>tumpj901</td>\n",
       "      <td>None</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>UHP</td>\n",
       "      <td>Home Plate</td>\n",
       "      <td>umpire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1646111</td>\n",
       "      <td>brica001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>ALP</td>\n",
       "      <td>Losing Pitcher</td>\n",
       "      <td>award</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6716279</td>\n",
       "      <td>koeht001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D1</td>\n",
       "      <td>Pitcher</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4744553</td>\n",
       "      <td>telit001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D2</td>\n",
       "      <td>Catcher</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5589581</td>\n",
       "      <td>bourj002</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D3</td>\n",
       "      <td>1st Base</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4462877</td>\n",
       "      <td>gordd002</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D4</td>\n",
       "      <td>2nd Base</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5026229</td>\n",
       "      <td>pradm001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D5</td>\n",
       "      <td>3rd Base</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>6434609</td>\n",
       "      <td>hecha001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D6</td>\n",
       "      <td>Shortstop</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>5871257</td>\n",
       "      <td>scrux001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D7</td>\n",
       "      <td>Left Field</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>5307905</td>\n",
       "      <td>yelic001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D8</td>\n",
       "      <td>Center Field</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>6152933</td>\n",
       "      <td>hoodd001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D9</td>\n",
       "      <td>Right Field</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1646113</td>\n",
       "      <td>mattd001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>MM</td>\n",
       "      <td>Manager</td>\n",
       "      <td>manager</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>4462878</td>\n",
       "      <td>gordd002</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O1</td>\n",
       "      <td>Batter 1</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>4744554</td>\n",
       "      <td>telit001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O2</td>\n",
       "      <td>Batter 2</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5026230</td>\n",
       "      <td>pradm001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O3</td>\n",
       "      <td>Batter 3</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5307906</td>\n",
       "      <td>yelic001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O4</td>\n",
       "      <td>Batter 4</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5589582</td>\n",
       "      <td>bourj002</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O5</td>\n",
       "      <td>Batter 5</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5871258</td>\n",
       "      <td>scrux001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O6</td>\n",
       "      <td>Batter 6</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>6152934</td>\n",
       "      <td>hoodd001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O7</td>\n",
       "      <td>Batter 7</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>6434610</td>\n",
       "      <td>hecha001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O8</td>\n",
       "      <td>Batter 8</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>6716280</td>\n",
       "      <td>koeht001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O9</td>\n",
       "      <td>Batter 9</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1646112</td>\n",
       "      <td>koeht001</td>\n",
       "      <td>MIA</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>PSP</td>\n",
       "      <td>Starting Pitcher</td>\n",
       "      <td>pitcher</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1646116</td>\n",
       "      <td>melam001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>ASP</td>\n",
       "      <td>Saving Pitcher</td>\n",
       "      <td>award</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1646115</td>\n",
       "      <td>difow001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>AWB</td>\n",
       "      <td>Winning RBI Batter</td>\n",
       "      <td>award</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1646117</td>\n",
       "      <td>schem001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>AWP</td>\n",
       "      <td>Winning Pitcher</td>\n",
       "      <td>award</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>4181201</td>\n",
       "      <td>schem001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D1</td>\n",
       "      <td>Pitcher</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>3899525</td>\n",
       "      <td>lobaj001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D2</td>\n",
       "      <td>Catcher</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2772821</td>\n",
       "      <td>zimmr001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D3</td>\n",
       "      <td>1st Base</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>3336173</td>\n",
       "      <td>difow001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D4</td>\n",
       "      <td>2nd Base</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>3054497</td>\n",
       "      <td>drews001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D5</td>\n",
       "      <td>3rd Base</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>3617849</td>\n",
       "      <td>espid001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D6</td>\n",
       "      <td>Shortstop</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2209469</td>\n",
       "      <td>reveb001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D7</td>\n",
       "      <td>Left Field</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1927793</td>\n",
       "      <td>turnt001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D8</td>\n",
       "      <td>Center Field</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2491145</td>\n",
       "      <td>harpb003</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>D9</td>\n",
       "      <td>Right Field</td>\n",
       "      <td>defense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1646114</td>\n",
       "      <td>baked002</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>MM</td>\n",
       "      <td>Manager</td>\n",
       "      <td>manager</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1927794</td>\n",
       "      <td>turnt001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O1</td>\n",
       "      <td>Batter 1</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2209470</td>\n",
       "      <td>reveb001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O2</td>\n",
       "      <td>Batter 2</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2491146</td>\n",
       "      <td>harpb003</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O3</td>\n",
       "      <td>Batter 3</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2772822</td>\n",
       "      <td>zimmr001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O4</td>\n",
       "      <td>Batter 4</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>3054498</td>\n",
       "      <td>drews001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O5</td>\n",
       "      <td>Batter 5</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>3336174</td>\n",
       "      <td>difow001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O6</td>\n",
       "      <td>Batter 6</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>3617850</td>\n",
       "      <td>espid001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O7</td>\n",
       "      <td>Batter 7</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>3899526</td>\n",
       "      <td>lobaj001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O8</td>\n",
       "      <td>Batter 8</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>4181202</td>\n",
       "      <td>schem001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>O9</td>\n",
       "      <td>Batter 9</td>\n",
       "      <td>offense</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1646118</td>\n",
       "      <td>schem001</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20161002WAS0</td>\n",
       "      <td>PSP</td>\n",
       "      <td>Starting Pitcher</td>\n",
       "      <td>pitcher</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    appearance_id person_id team_id       game_id appearance_type_id  \\\n",
       "0         1646109  porta901    None  20161002WAS0                U1B   \n",
       "1         1646108  onorb901    None  20161002WAS0                U2B   \n",
       "2         1646107  kellj901    None  20161002WAS0                U3B   \n",
       "3         1646110  tumpj901    None  20161002WAS0                UHP   \n",
       "4         1646111  brica001     MIA  20161002WAS0                ALP   \n",
       "5         6716279  koeht001     MIA  20161002WAS0                 D1   \n",
       "6         4744553  telit001     MIA  20161002WAS0                 D2   \n",
       "7         5589581  bourj002     MIA  20161002WAS0                 D3   \n",
       "8         4462877  gordd002     MIA  20161002WAS0                 D4   \n",
       "9         5026229  pradm001     MIA  20161002WAS0                 D5   \n",
       "10        6434609  hecha001     MIA  20161002WAS0                 D6   \n",
       "11        5871257  scrux001     MIA  20161002WAS0                 D7   \n",
       "12        5307905  yelic001     MIA  20161002WAS0                 D8   \n",
       "13        6152933  hoodd001     MIA  20161002WAS0                 D9   \n",
       "14        1646113  mattd001     MIA  20161002WAS0                 MM   \n",
       "15        4462878  gordd002     MIA  20161002WAS0                 O1   \n",
       "16        4744554  telit001     MIA  20161002WAS0                 O2   \n",
       "17        5026230  pradm001     MIA  20161002WAS0                 O3   \n",
       "18        5307906  yelic001     MIA  20161002WAS0                 O4   \n",
       "19        5589582  bourj002     MIA  20161002WAS0                 O5   \n",
       "20        5871258  scrux001     MIA  20161002WAS0                 O6   \n",
       "21        6152934  hoodd001     MIA  20161002WAS0                 O7   \n",
       "22        6434610  hecha001     MIA  20161002WAS0                 O8   \n",
       "23        6716280  koeht001     MIA  20161002WAS0                 O9   \n",
       "24        1646112  koeht001     MIA  20161002WAS0                PSP   \n",
       "25        1646116  melam001     WAS  20161002WAS0                ASP   \n",
       "26        1646115  difow001     WAS  20161002WAS0                AWB   \n",
       "27        1646117  schem001     WAS  20161002WAS0                AWP   \n",
       "28        4181201  schem001     WAS  20161002WAS0                 D1   \n",
       "29        3899525  lobaj001     WAS  20161002WAS0                 D2   \n",
       "30        2772821  zimmr001     WAS  20161002WAS0                 D3   \n",
       "31        3336173  difow001     WAS  20161002WAS0                 D4   \n",
       "32        3054497  drews001     WAS  20161002WAS0                 D5   \n",
       "33        3617849  espid001     WAS  20161002WAS0                 D6   \n",
       "34        2209469  reveb001     WAS  20161002WAS0                 D7   \n",
       "35        1927793  turnt001     WAS  20161002WAS0                 D8   \n",
       "36        2491145  harpb003     WAS  20161002WAS0                 D9   \n",
       "37        1646114  baked002     WAS  20161002WAS0                 MM   \n",
       "38        1927794  turnt001     WAS  20161002WAS0                 O1   \n",
       "39        2209470  reveb001     WAS  20161002WAS0                 O2   \n",
       "40        2491146  harpb003     WAS  20161002WAS0                 O3   \n",
       "41        2772822  zimmr001     WAS  20161002WAS0                 O4   \n",
       "42        3054498  drews001     WAS  20161002WAS0                 O5   \n",
       "43        3336174  difow001     WAS  20161002WAS0                 O6   \n",
       "44        3617850  espid001     WAS  20161002WAS0                 O7   \n",
       "45        3899526  lobaj001     WAS  20161002WAS0                 O8   \n",
       "46        4181202  schem001     WAS  20161002WAS0                 O9   \n",
       "47        1646118  schem001     WAS  20161002WAS0                PSP   \n",
       "\n",
       "                  name category  \n",
       "0           First Base   umpire  \n",
       "1          Second Base   umpire  \n",
       "2           Third Base   umpire  \n",
       "3           Home Plate   umpire  \n",
       "4       Losing Pitcher    award  \n",
       "5              Pitcher  defense  \n",
       "6              Catcher  defense  \n",
       "7             1st Base  defense  \n",
       "8             2nd Base  defense  \n",
       "9             3rd Base  defense  \n",
       "10           Shortstop  defense  \n",
       "11          Left Field  defense  \n",
       "12        Center Field  defense  \n",
       "13         Right Field  defense  \n",
       "14             Manager  manager  \n",
       "15            Batter 1  offense  \n",
       "16            Batter 2  offense  \n",
       "17            Batter 3  offense  \n",
       "18            Batter 4  offense  \n",
       "19            Batter 5  offense  \n",
       "20            Batter 6  offense  \n",
       "21            Batter 7  offense  \n",
       "22            Batter 8  offense  \n",
       "23            Batter 9  offense  \n",
       "24    Starting Pitcher  pitcher  \n",
       "25      Saving Pitcher    award  \n",
       "26  Winning RBI Batter    award  \n",
       "27     Winning Pitcher    award  \n",
       "28             Pitcher  defense  \n",
       "29             Catcher  defense  \n",
       "30            1st Base  defense  \n",
       "31            2nd Base  defense  \n",
       "32            3rd Base  defense  \n",
       "33           Shortstop  defense  \n",
       "34          Left Field  defense  \n",
       "35        Center Field  defense  \n",
       "36         Right Field  defense  \n",
       "37             Manager  manager  \n",
       "38            Batter 1  offense  \n",
       "39            Batter 2  offense  \n",
       "40            Batter 3  offense  \n",
       "41            Batter 4  offense  \n",
       "42            Batter 5  offense  \n",
       "43            Batter 6  offense  \n",
       "44            Batter 7  offense  \n",
       "45            Batter 8  offense  \n",
       "46            Batter 9  offense  \n",
       "47    Starting Pitcher  pitcher  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(run_query(\"SELECT COUNT(DISTINCT game_id) games_game FROM game\"))\n",
    "print(run_query(\"SELECT COUNT(DISTINCT game_id) games_person_appearance FROM person_appearance\"))\n",
    "\n",
    "q = \"\"\"\n",
    "SELECT\n",
    "    pa.*,\n",
    "    at.name,\n",
    "    at.category\n",
    "FROM person_appearance pa\n",
    "INNER JOIN appearance_type at on at.appearance_type_id = pa.appearance_type_id\n",
    "WHERE PA.game_id = (\n",
    "                   SELECT max(game_id)\n",
    "                    FROM person_appearance\n",
    "                   )\n",
    "ORDER BY team_id, appearance_type_id\n",
    "\"\"\"\n",
    "\n",
    "run_query(q)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Removing the Original Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>game_log</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>park_codes</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>team_codes</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>person_codes</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>person</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>park</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>league</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>appearance_type</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>team</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>game</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>team_appearance</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>person_appearance</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 name   type\n",
       "0            game_log  table\n",
       "1          park_codes  table\n",
       "2          team_codes  table\n",
       "3        person_codes  table\n",
       "4              person  table\n",
       "5                park  table\n",
       "6              league  table\n",
       "7     appearance_type  table\n",
       "8                team  table\n",
       "9                game  table\n",
       "10    team_appearance  table\n",
       "11  person_appearance  table"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "show_tables()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>person</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>park</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>league</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>appearance_type</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>team</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>game</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>team_appearance</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>person_appearance</td>\n",
       "      <td>table</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                name   type\n",
       "0             person  table\n",
       "1               park  table\n",
       "2             league  table\n",
       "3    appearance_type  table\n",
       "4               team  table\n",
       "5               game  table\n",
       "6    team_appearance  table\n",
       "7  person_appearance  table"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tables = [\n",
    "    \"game_log\",\n",
    "    \"park_codes\",\n",
    "    \"team_codes\",\n",
    "    \"person_codes\"\n",
    "]\n",
    "\n",
    "for t in tables:\n",
    "    c = '''\n",
    "    DROP TABLE {}\n",
    "    '''.format(t)\n",
    "    \n",
    "    run_command(c)\n",
    "\n",
    "show_tables()"
   ]
  }
 ],
 "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.8.5"
  },
  "notify_time": "5"
 },
 "nbformat": 4,
 "nbformat_minor": 2
}