Web Scraping.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Web Scraping to Obtain Team and Player Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this notebook, we retrieved data from an html website on two teams: the New York Yankees and the Kansas City Royals.\n",
    "\n",
    "Not only did we acquire data on the overall team statistics, but for every year that we looked at, we would perform a second webscrape in order to get a deeper look at the data.\n",
    "\n",
    "The second webscrape gave us information and statistics on each specific player of the team."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ],
      "text/vnd.plotly.v1+html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import requests\n",
    "import plotly.offline as py\n",
    "import matplotlib.pyplot as plt\n",
    "from plotly.graph_objs import *\n",
    "from sklearn.linear_model import LinearRegression, LogisticRegression\n",
    "from sklearn.neighbors import KNeighborsRegressor\n",
    "from sklearn.model_selection import cross_val_score\n",
    "from sklearn.cluster import KMeans\n",
    "from bs4 import BeautifulSoup\n",
    "pd.set_option(\"max_r\", 15)\n",
    "py.init_notebook_mode(connected=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# website with overall team stats\n",
    "req = requests.get(\"http://www.baseball-reference.com/teams/NYY/index.shtml\")\n",
    "soup = BeautifulSoup(req.text, \"html.parser\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "tbody = soup.find(\"tbody\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Get all links to NYY's yearly rosters & player stats.\n",
    "url = \"http://www.baseball-reference.com\"\n",
    "year = 2017\n",
    "nyy_data = {\n",
    "    \"year\": [],\n",
    "    \"link\": [],\n",
    "    \"wins\": [],\n",
    "    'runs': [],\n",
    "    'runs_allowed': []\n",
    "}\n",
    "for row in tbody.find_all(\"tr\"):\n",
    "    nyy_data[\"year\"].append(year)\n",
    "    year = year - 1\n",
    "    for td in row.find_all(\"td\"):\n",
    "        statname = td.get(\"data-stat\")\n",
    "        if statname == \"team_name\":\n",
    "            link = url + td.find('a')[\"href\"]\n",
    "            nyy_data[\"link\"].append(link)\n",
    "        elif statname == 'W':\n",
    "            wins = int(td.text)\n",
    "            nyy_data['wins'].append(wins)\n",
    "        elif statname == 'R':\n",
    "            runs = int(td.text)\n",
    "            nyy_data['runs'].append(runs)\n",
    "        elif statname == 'RA':\n",
    "            runs_allowed = int(td.text)\n",
    "            nyy_data['runs_allowed'].append(runs_allowed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>link</th>\n",
       "      <th>runs</th>\n",
       "      <th>runs_allowed</th>\n",
       "      <th>wins</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.baseball-reference.com/teams/NYY/20...</td>\n",
       "      <td>680</td>\n",
       "      <td>702</td>\n",
       "      <td>84</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.baseball-reference.com/teams/NYY/20...</td>\n",
       "      <td>764</td>\n",
       "      <td>698</td>\n",
       "      <td>87</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.baseball-reference.com/teams/NYY/20...</td>\n",
       "      <td>633</td>\n",
       "      <td>664</td>\n",
       "      <td>84</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.baseball-reference.com/teams/NYY/20...</td>\n",
       "      <td>650</td>\n",
       "      <td>671</td>\n",
       "      <td>85</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>http://www.baseball-reference.com/teams/NYY/20...</td>\n",
       "      <td>804</td>\n",
       "      <td>668</td>\n",
       "      <td>95</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                link  runs  runs_allowed  \\\n",
       "1  http://www.baseball-reference.com/teams/NYY/20...   680           702   \n",
       "2  http://www.baseball-reference.com/teams/NYY/20...   764           698   \n",
       "3  http://www.baseball-reference.com/teams/NYY/20...   633           664   \n",
       "4  http://www.baseball-reference.com/teams/NYY/20...   650           671   \n",
       "5  http://www.baseball-reference.com/teams/NYY/20...   804           668   \n",
       "\n",
       "   wins  year  \n",
       "1    84  2016  \n",
       "2    87  2015  \n",
       "3    84  2014  \n",
       "4    85  2013  \n",
       "5    95  2012  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean data\n",
    "nyy = pd.DataFrame(nyy_data)\n",
    "nyy = nyy.drop(0, axis=0)\n",
    "nyy.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# repeat scraping for the second team\n",
    "req = requests.get(\"http://www.baseball-reference.com/teams/KCR/index.shtml\")\n",
    "soup = BeautifulSoup(req.text, \"html.parser\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tbody = soup.find(\"tbody\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Get all links to KCR's yearly rosters & player stats.\n",
    "url = \"http://www.baseball-reference.com\"\n",
    "year = 2017\n",
    "kcr_data = {\n",
    "    \"year\": [],\n",
    "    \"link\": [],\n",
    "    \"wins\": [],\n",
    "    'runs': [],\n",
    "    'runs_allowed': []\n",
    "}\n",
    "for row in tbody.find_all(\"tr\"):\n",
    "    kcr_data[\"year\"].append(year)\n",
    "    year = year - 1\n",
    "    for td in row.find_all(\"td\"):\n",
    "        statname = td.get(\"data-stat\")\n",
    "        if statname == \"team_name\":\n",
    "            link = url + td.find('a')[\"href\"]\n",
    "            kcr_data[\"link\"].append(link)\n",
    "        elif statname == 'W':\n",
    "            wins = int(td.text)\n",
    "            kcr_data['wins'].append(wins)\n",
    "        elif statname == 'R':\n",
    "            runs = int(td.text)\n",
    "            kcr_data['runs'].append(runs)\n",
    "        elif statname == 'RA':\n",
    "            runs_allowed = int(td.text)\n",
    "            kcr_data['runs_allowed'].append(runs_allowed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>link</th>\n",
       "      <th>runs</th>\n",
       "      <th>runs_allowed</th>\n",
       "      <th>wins</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.baseball-reference.com/teams/KCR/20...</td>\n",
       "      <td>675</td>\n",
       "      <td>712</td>\n",
       "      <td>81</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.baseball-reference.com/teams/KCR/20...</td>\n",
       "      <td>724</td>\n",
       "      <td>641</td>\n",
       "      <td>95</td>\n",
       "      <td>2015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.baseball-reference.com/teams/KCR/20...</td>\n",
       "      <td>651</td>\n",
       "      <td>624</td>\n",
       "      <td>89</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.baseball-reference.com/teams/KCR/20...</td>\n",
       "      <td>648</td>\n",
       "      <td>601</td>\n",
       "      <td>86</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>http://www.baseball-reference.com/teams/KCR/20...</td>\n",
       "      <td>676</td>\n",
       "      <td>746</td>\n",
       "      <td>72</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                link  runs  runs_allowed  \\\n",
       "1  http://www.baseball-reference.com/teams/KCR/20...   675           712   \n",
       "2  http://www.baseball-reference.com/teams/KCR/20...   724           641   \n",
       "3  http://www.baseball-reference.com/teams/KCR/20...   651           624   \n",
       "4  http://www.baseball-reference.com/teams/KCR/20...   648           601   \n",
       "5  http://www.baseball-reference.com/teams/KCR/20...   676           746   \n",
       "\n",
       "   wins  year  \n",
       "1    81  2016  \n",
       "2    95  2015  \n",
       "3    89  2014  \n",
       "4    86  2013  \n",
       "5    72  2012  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# clean up data\n",
    "kcr = pd.DataFrame(kcr_data)\n",
    "kcr = kcr.drop(0, axis=0)\n",
    "kcr.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Get NYY data on all players for all necessary years.\n",
    "runs_all = list(nyy.runs_allowed.values)\n",
    "dic = {\n",
    "    \"year\": [],\n",
    "    \"runs_allowed\": []\n",
    "}\n",
    "year = 2017\n",
    "for i in range(1, 23):\n",
    "    year = year - 1\n",
    "    ra = runs_all[i-1]\n",
    "    req = requests.get(nyy.loc[i, \"link\"])\n",
    "    soup = BeautifulSoup(req.text, \"html.parser\")\n",
    "    tbody = soup.find(\"tbody\")\n",
    "    for td in tbody.find_all(\"td\"):\n",
    "        statname = td.get(\"data-stat\")\n",
    "        if statname == 'G':\n",
    "            dic[\"year\"].append(year)\n",
    "            dic[\"runs_allowed\"].append(ra)\n",
    "        if statname not in dic.keys():\n",
    "            dic[statname] = []\n",
    "        dic[statname].append(td.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>2B</th>\n",
       "      <th>3B</th>\n",
       "      <th>AB</th>\n",
       "      <th>BB</th>\n",
       "      <th>CS</th>\n",
       "      <th>G</th>\n",
       "      <th>GIDP</th>\n",
       "      <th>H</th>\n",
       "      <th>HBP</th>\n",
       "      <th>HR</th>\n",
       "      <th>...</th>\n",
       "      <th>age</th>\n",
       "      <th>batting_avg</th>\n",
       "      <th>onbase_perc</th>\n",
       "      <th>onbase_plus_slugging</th>\n",
       "      <th>onbase_plus_slugging_plus</th>\n",
       "      <th>player</th>\n",
       "      <th>pos</th>\n",
       "      <th>runs_allowed</th>\n",
       "      <th>slugging_perc</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>429</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>130</td>\n",
       "      <td>15</td>\n",
       "      <td>104</td>\n",
       "      <td>7</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>32</td>\n",
       "      <td>.242</td>\n",
       "      <td>.335</td>\n",
       "      <td>.748</td>\n",
       "      <td>97</td>\n",
       "      <td>Brian McCann</td>\n",
       "      <td>C</td>\n",
       "      <td>702</td>\n",
       "      <td>.413</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>387</td>\n",
       "      <td>47</td>\n",
       "      <td>0</td>\n",
       "      <td>116</td>\n",
       "      <td>7</td>\n",
       "      <td>79</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>36</td>\n",
       "      <td>.204</td>\n",
       "      <td>.292</td>\n",
       "      <td>.654</td>\n",
       "      <td>73</td>\n",
       "      <td>Mark Teixeira</td>\n",
       "      <td>1B</td>\n",
       "      <td>702</td>\n",
       "      <td>.362</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>577</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>151</td>\n",
       "      <td>15</td>\n",
       "      <td>156</td>\n",
       "      <td>3</td>\n",
       "      <td>21</td>\n",
       "      <td>...</td>\n",
       "      <td>26</td>\n",
       "      <td>.270</td>\n",
       "      <td>.300</td>\n",
       "      <td>.734</td>\n",
       "      <td>92</td>\n",
       "      <td>Starlin Castro</td>\n",
       "      <td>2B</td>\n",
       "      <td>702</td>\n",
       "      <td>.433</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>562</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>153</td>\n",
       "      <td>9</td>\n",
       "      <td>155</td>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>26</td>\n",
       "      <td>.276</td>\n",
       "      <td>.304</td>\n",
       "      <td>.751</td>\n",
       "      <td>96</td>\n",
       "      <td>Didi Gregorius</td>\n",
       "      <td>SS</td>\n",
       "      <td>702</td>\n",
       "      <td>.447</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>467</td>\n",
       "      <td>51</td>\n",
       "      <td>2</td>\n",
       "      <td>140</td>\n",
       "      <td>7</td>\n",
       "      <td>118</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>...</td>\n",
       "      <td>32</td>\n",
       "      <td>.253</td>\n",
       "      <td>.331</td>\n",
       "      <td>.716</td>\n",
       "      <td>90</td>\n",
       "      <td>Chase Headley</td>\n",
       "      <td>3B</td>\n",
       "      <td>702</td>\n",
       "      <td>.385</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   2B 3B   AB  BB CS    G GIDP    H HBP  HR  ...  age batting_avg onbase_perc  \\\n",
       "0  13  0  429  54  0  130   15  104   7  20  ...   32        .242        .335   \n",
       "1  16  0  387  47  0  116    7   79   2  15  ...   36        .204        .292   \n",
       "2  29  1  577  24  0  151   15  156   3  21  ...   26        .270        .300   \n",
       "3  32  2  562  19  1  153    9  155   6  20  ...   26        .276        .304   \n",
       "4  18  1  467  51  2  140    7  118   6  14  ...   32        .253        .331   \n",
       "\n",
       "  onbase_plus_slugging onbase_plus_slugging_plus          player pos  \\\n",
       "0                 .748                        97    Brian McCann   C   \n",
       "1                 .654                        73   Mark Teixeira  1B   \n",
       "2                 .734                        92  Starlin Castro  2B   \n",
       "3                 .751                        96  Didi Gregorius  SS   \n",
       "4                 .716                        90   Chase Headley  3B   \n",
       "\n",
       "  runs_allowed slugging_perc  year  \n",
       "0          702          .413  2016  \n",
       "1          702          .362  2016  \n",
       "2          702          .433  2016  \n",
       "3          702          .447  2016  \n",
       "4          702          .385  2016  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# data had extra symbols we needed to remove\n",
    "def clean_name(name):\n",
    "        return name.strip(\"*#\")\n",
    "\n",
    "dataNYY = pd.DataFrame(dic)\n",
    "dataNYY[\"player\"] = dataNYY[\"player\"].apply(clean_name)\n",
    "dataNYY.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Get KCR data on all players for all necessary years.\n",
    "runs_all = list(kcr.runs_allowed.values)\n",
    "dic = {\n",
    "    \"year\": [],\n",
    "    \"runs_allowed\": []\n",
    "}\n",
    "year = 2017\n",
    "for i in range(1, 23):\n",
    "    year = year - 1\n",
    "    ra = runs_all[i-1]\n",
    "    req = requests.get(kcr.loc[i, \"link\"])\n",
    "    soup = BeautifulSoup(req.text, \"html.parser\")\n",
    "    tbody = soup.find(\"tbody\")\n",
    "    for td in tbody.find_all(\"td\"):\n",
    "        statname = td.get(\"data-stat\")\n",
    "        if statname == 'G':\n",
    "            dic[\"year\"].append(year)\n",
    "            dic[\"runs_allowed\"].append(ra)\n",
    "        if statname not in dic.keys():\n",
    "            dic[statname] = []\n",
    "        dic[statname].append(td.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>2B</th>\n",
       "      <th>3B</th>\n",
       "      <th>AB</th>\n",
       "      <th>BB</th>\n",
       "      <th>CS</th>\n",
       "      <th>G</th>\n",
       "      <th>GIDP</th>\n",
       "      <th>H</th>\n",
       "      <th>HBP</th>\n",
       "      <th>HR</th>\n",
       "      <th>...</th>\n",
       "      <th>age</th>\n",
       "      <th>batting_avg</th>\n",
       "      <th>onbase_perc</th>\n",
       "      <th>onbase_plus_slugging</th>\n",
       "      <th>onbase_plus_slugging_plus</th>\n",
       "      <th>player</th>\n",
       "      <th>pos</th>\n",
       "      <th>runs_allowed</th>\n",
       "      <th>slugging_perc</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>28</td>\n",
       "      <td>2</td>\n",
       "      <td>514</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>139</td>\n",
       "      <td>12</td>\n",
       "      <td>127</td>\n",
       "      <td>8</td>\n",
       "      <td>22</td>\n",
       "      <td>...</td>\n",
       "      <td>26</td>\n",
       "      <td>.247</td>\n",
       "      <td>.288</td>\n",
       "      <td>.725</td>\n",
       "      <td>90</td>\n",
       "      <td>Salvador Perez</td>\n",
       "      <td>C</td>\n",
       "      <td>712</td>\n",
       "      <td>.438</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>605</td>\n",
       "      <td>57</td>\n",
       "      <td>3</td>\n",
       "      <td>158</td>\n",
       "      <td>18</td>\n",
       "      <td>161</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>...</td>\n",
       "      <td>26</td>\n",
       "      <td>.266</td>\n",
       "      <td>.328</td>\n",
       "      <td>.761</td>\n",
       "      <td>102</td>\n",
       "      <td>Eric Hosmer</td>\n",
       "      <td>1B</td>\n",
       "      <td>712</td>\n",
       "      <td>.433</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22</td>\n",
       "      <td>3</td>\n",
       "      <td>311</td>\n",
       "      <td>19</td>\n",
       "      <td>3</td>\n",
       "      <td>81</td>\n",
       "      <td>1</td>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>27</td>\n",
       "      <td>.283</td>\n",
       "      <td>.323</td>\n",
       "      <td>.716</td>\n",
       "      <td>91</td>\n",
       "      <td>Whit Merrifield</td>\n",
       "      <td>2B</td>\n",
       "      <td>712</td>\n",
       "      <td>.392</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>24</td>\n",
       "      <td>6</td>\n",
       "      <td>637</td>\n",
       "      <td>27</td>\n",
       "      <td>4</td>\n",
       "      <td>162</td>\n",
       "      <td>16</td>\n",
       "      <td>166</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>29</td>\n",
       "      <td>.261</td>\n",
       "      <td>.292</td>\n",
       "      <td>.642</td>\n",
       "      <td>71</td>\n",
       "      <td>Alcides Escobar</td>\n",
       "      <td>SS</td>\n",
       "      <td>712</td>\n",
       "      <td>.350</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>475</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>128</td>\n",
       "      <td>14</td>\n",
       "      <td>130</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>23</td>\n",
       "      <td>.274</td>\n",
       "      <td>.318</td>\n",
       "      <td>.731</td>\n",
       "      <td>94</td>\n",
       "      <td>Cheslor Cuthbert</td>\n",
       "      <td>3B</td>\n",
       "      <td>712</td>\n",
       "      <td>.413</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   2B 3B   AB  BB CS    G GIDP    H HBP  HR  ...  age batting_avg onbase_perc  \\\n",
       "0  28  2  514  22  0  139   12  127   8  22  ...   26        .247        .288   \n",
       "1  24  1  605  57  3  158   18  161   1  25  ...   26        .266        .328   \n",
       "2  22  3  311  19  3   81    1   88   0   2  ...   27        .283        .323   \n",
       "3  24  6  637  27  4  162   16  166   3   7  ...   29        .261        .292   \n",
       "4  28  1  475  32  0  128   14  130   0  12  ...   23        .274        .318   \n",
       "\n",
       "  onbase_plus_slugging onbase_plus_slugging_plus            player pos  \\\n",
       "0                 .725                        90    Salvador Perez   C   \n",
       "1                 .761                       102       Eric Hosmer  1B   \n",
       "2                 .716                        91   Whit Merrifield  2B   \n",
       "3                 .642                        71   Alcides Escobar  SS   \n",
       "4                 .731                        94  Cheslor Cuthbert  3B   \n",
       "\n",
       "  runs_allowed slugging_perc  year  \n",
       "0          712          .438  2016  \n",
       "1          712          .433  2016  \n",
       "2          712          .392  2016  \n",
       "3          712          .350  2016  \n",
       "4          712          .413  2016  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataKCR = pd.DataFrame(dic)\n",
    "dataKCR[\"player\"] = dataKCR[\"player\"].apply(clean_name)\n",
    "dataKCR.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>2B</th>\n",
       "      <th>3B</th>\n",
       "      <th>AB</th>\n",
       "      <th>BB</th>\n",
       "      <th>CS</th>\n",
       "      <th>G</th>\n",
       "      <th>GIDP</th>\n",
       "      <th>H</th>\n",
       "      <th>HBP</th>\n",
       "      <th>HR</th>\n",
       "      <th>...</th>\n",
       "      <th>age</th>\n",
       "      <th>batting_avg</th>\n",
       "      <th>onbase_perc</th>\n",
       "      <th>onbase_plus_slugging</th>\n",
       "      <th>onbase_plus_slugging_plus</th>\n",
       "      <th>player</th>\n",
       "      <th>pos</th>\n",
       "      <th>runs_allowed</th>\n",
       "      <th>slugging_perc</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "      <td>429</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>130</td>\n",
       "      <td>15</td>\n",
       "      <td>104</td>\n",
       "      <td>7</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>32</td>\n",
       "      <td>0.242</td>\n",
       "      <td>0.335</td>\n",
       "      <td>0.748</td>\n",
       "      <td>97.0</td>\n",
       "      <td>Brian McCann</td>\n",
       "      <td>C</td>\n",
       "      <td>702</td>\n",
       "      <td>0.413</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>387</td>\n",
       "      <td>47</td>\n",
       "      <td>0</td>\n",
       "      <td>116</td>\n",
       "      <td>7</td>\n",
       "      <td>79</td>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>36</td>\n",
       "      <td>0.204</td>\n",
       "      <td>0.292</td>\n",
       "      <td>0.654</td>\n",
       "      <td>73.0</td>\n",
       "      <td>Mark Teixeira</td>\n",
       "      <td>1B</td>\n",
       "      <td>702</td>\n",
       "      <td>0.362</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>29</td>\n",
       "      <td>1</td>\n",
       "      <td>577</td>\n",
       "      <td>24</td>\n",
       "      <td>0</td>\n",
       "      <td>151</td>\n",
       "      <td>15</td>\n",
       "      <td>156</td>\n",
       "      <td>3</td>\n",
       "      <td>21</td>\n",
       "      <td>...</td>\n",
       "      <td>26</td>\n",
       "      <td>0.270</td>\n",
       "      <td>0.300</td>\n",
       "      <td>0.734</td>\n",
       "      <td>92.0</td>\n",
       "      <td>Starlin Castro</td>\n",
       "      <td>2B</td>\n",
       "      <td>702</td>\n",
       "      <td>0.433</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>32</td>\n",
       "      <td>2</td>\n",
       "      <td>562</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>153</td>\n",
       "      <td>9</td>\n",
       "      <td>155</td>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>26</td>\n",
       "      <td>0.276</td>\n",
       "      <td>0.304</td>\n",
       "      <td>0.751</td>\n",
       "      <td>96.0</td>\n",
       "      <td>Didi Gregorius</td>\n",
       "      <td>SS</td>\n",
       "      <td>702</td>\n",
       "      <td>0.447</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>467</td>\n",
       "      <td>51</td>\n",
       "      <td>2</td>\n",
       "      <td>140</td>\n",
       "      <td>7</td>\n",
       "      <td>118</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "      <td>...</td>\n",
       "      <td>32</td>\n",
       "      <td>0.253</td>\n",
       "      <td>0.331</td>\n",
       "      <td>0.716</td>\n",
       "      <td>90.0</td>\n",
       "      <td>Chase Headley</td>\n",
       "      <td>3B</td>\n",
       "      <td>702</td>\n",
       "      <td>0.385</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   2B  3B   AB  BB  CS    G  GIDP    H  HBP  HR  ...   age  batting_avg  \\\n",
       "0  13   0  429  54   0  130    15  104    7  20  ...    32        0.242   \n",
       "1  16   0  387  47   0  116     7   79    2  15  ...    36        0.204   \n",
       "2  29   1  577  24   0  151    15  156    3  21  ...    26        0.270   \n",
       "3  32   2  562  19   1  153     9  155    6  20  ...    26        0.276   \n",
       "4  18   1  467  51   2  140     7  118    6  14  ...    32        0.253   \n",
       "\n",
       "   onbase_perc  onbase_plus_slugging  onbase_plus_slugging_plus  \\\n",
       "0        0.335                 0.748                       97.0   \n",
       "1        0.292                 0.654                       73.0   \n",
       "2        0.300                 0.734                       92.0   \n",
       "3        0.304                 0.751                       96.0   \n",
       "4        0.331                 0.716                       90.0   \n",
       "\n",
       "           player  pos  runs_allowed  slugging_perc  year  \n",
       "0    Brian McCann    C           702          0.413  2016  \n",
       "1   Mark Teixeira   1B           702          0.362  2016  \n",
       "2  Starlin Castro   2B           702          0.433  2016  \n",
       "3  Didi Gregorius   SS           702          0.447  2016  \n",
       "4   Chase Headley   3B           702          0.385  2016  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert to numeric values.\n",
    "to_numbers = dataNYY.columns.values\n",
    "to_numbers = np.delete(to_numbers, [24,25])\n",
    "for i in range(0, len(to_numbers)):\n",
    "    dataNYY[to_numbers[i]] = pd.to_numeric(dataNYY[to_numbers[i]])\n",
    "dataNYY.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2B                             int64\n",
       "3B                             int64\n",
       "AB                             int64\n",
       "BB                             int64\n",
       "CS                             int64\n",
       "G                              int64\n",
       "GIDP                           int64\n",
       "                              ...   \n",
       "onbase_plus_slugging         float64\n",
       "onbase_plus_slugging_plus    float64\n",
       "player                        object\n",
       "pos                           object\n",
       "runs_allowed                   int64\n",
       "slugging_perc                float64\n",
       "year                           int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataNYY.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>2B</th>\n",
       "      <th>3B</th>\n",
       "      <th>AB</th>\n",
       "      <th>BB</th>\n",
       "      <th>CS</th>\n",
       "      <th>G</th>\n",
       "      <th>GIDP</th>\n",
       "      <th>H</th>\n",
       "      <th>HBP</th>\n",
       "      <th>HR</th>\n",
       "      <th>...</th>\n",
       "      <th>age</th>\n",
       "      <th>batting_avg</th>\n",
       "      <th>onbase_perc</th>\n",
       "      <th>onbase_plus_slugging</th>\n",
       "      <th>onbase_plus_slugging_plus</th>\n",
       "      <th>player</th>\n",
       "      <th>pos</th>\n",
       "      <th>runs_allowed</th>\n",
       "      <th>slugging_perc</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>28</td>\n",
       "      <td>2</td>\n",
       "      <td>514</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>139</td>\n",
       "      <td>12</td>\n",
       "      <td>127</td>\n",
       "      <td>8</td>\n",
       "      <td>22</td>\n",
       "      <td>...</td>\n",
       "      <td>26</td>\n",
       "      <td>0.247</td>\n",
       "      <td>0.288</td>\n",
       "      <td>0.725</td>\n",
       "      <td>90.0</td>\n",
       "      <td>Salvador Perez</td>\n",
       "      <td>C</td>\n",
       "      <td>712</td>\n",
       "      <td>0.438</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>605</td>\n",
       "      <td>57</td>\n",
       "      <td>3</td>\n",
       "      <td>158</td>\n",
       "      <td>18</td>\n",
       "      <td>161</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>...</td>\n",
       "      <td>26</td>\n",
       "      <td>0.266</td>\n",
       "      <td>0.328</td>\n",
       "      <td>0.761</td>\n",
       "      <td>102.0</td>\n",
       "      <td>Eric Hosmer</td>\n",
       "      <td>1B</td>\n",
       "      <td>712</td>\n",
       "      <td>0.433</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>22</td>\n",
       "      <td>3</td>\n",
       "      <td>311</td>\n",
       "      <td>19</td>\n",
       "      <td>3</td>\n",
       "      <td>81</td>\n",
       "      <td>1</td>\n",
       "      <td>88</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>27</td>\n",
       "      <td>0.283</td>\n",
       "      <td>0.323</td>\n",
       "      <td>0.716</td>\n",
       "      <td>91.0</td>\n",
       "      <td>Whit Merrifield</td>\n",
       "      <td>2B</td>\n",
       "      <td>712</td>\n",
       "      <td>0.392</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>24</td>\n",
       "      <td>6</td>\n",
       "      <td>637</td>\n",
       "      <td>27</td>\n",
       "      <td>4</td>\n",
       "      <td>162</td>\n",
       "      <td>16</td>\n",
       "      <td>166</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>29</td>\n",
       "      <td>0.261</td>\n",
       "      <td>0.292</td>\n",
       "      <td>0.642</td>\n",
       "      <td>71.0</td>\n",
       "      <td>Alcides Escobar</td>\n",
       "      <td>SS</td>\n",
       "      <td>712</td>\n",
       "      <td>0.350</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>475</td>\n",
       "      <td>32</td>\n",
       "      <td>0</td>\n",
       "      <td>128</td>\n",
       "      <td>14</td>\n",
       "      <td>130</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>23</td>\n",
       "      <td>0.274</td>\n",
       "      <td>0.318</td>\n",
       "      <td>0.731</td>\n",
       "      <td>94.0</td>\n",
       "      <td>Cheslor Cuthbert</td>\n",
       "      <td>3B</td>\n",
       "      <td>712</td>\n",
       "      <td>0.413</td>\n",
       "      <td>2016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   2B  3B   AB  BB  CS    G  GIDP    H  HBP  HR  ...   age  batting_avg  \\\n",
       "0  28   2  514  22   0  139    12  127    8  22  ...    26        0.247   \n",
       "1  24   1  605  57   3  158    18  161    1  25  ...    26        0.266   \n",
       "2  22   3  311  19   3   81     1   88    0   2  ...    27        0.283   \n",
       "3  24   6  637  27   4  162    16  166    3   7  ...    29        0.261   \n",
       "4  28   1  475  32   0  128    14  130    0  12  ...    23        0.274   \n",
       "\n",
       "   onbase_perc  onbase_plus_slugging  onbase_plus_slugging_plus  \\\n",
       "0        0.288                 0.725                       90.0   \n",
       "1        0.328                 0.761                      102.0   \n",
       "2        0.323                 0.716                       91.0   \n",
       "3        0.292                 0.642                       71.0   \n",
       "4        0.318                 0.731                       94.0   \n",
       "\n",
       "             player  pos  runs_allowed  slugging_perc  year  \n",
       "0    Salvador Perez    C           712          0.438  2016  \n",
       "1       Eric Hosmer   1B           712          0.433  2016  \n",
       "2   Whit Merrifield   2B           712          0.392  2016  \n",
       "3   Alcides Escobar   SS           712          0.350  2016  \n",
       "4  Cheslor Cuthbert   3B           712          0.413  2016  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert to numeric values.\n",
    "to_numbers = dataKCR.columns.values\n",
    "to_numbers = np.delete(to_numbers, [24,25])\n",
    "for i in range(0, len(to_numbers)):\n",
    "    dataKCR[to_numbers[i]] = pd.to_numeric(dataKCR[to_numbers[i]])\n",
    "dataKCR.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2B                             int64\n",
       "3B                             int64\n",
       "AB                             int64\n",
       "BB                             int64\n",
       "CS                             int64\n",
       "G                              int64\n",
       "GIDP                           int64\n",
       "                              ...   \n",
       "onbase_plus_slugging         float64\n",
       "onbase_plus_slugging_plus    float64\n",
       "player                        object\n",
       "pos                           object\n",
       "runs_allowed                   int64\n",
       "slugging_perc                float64\n",
       "year                           int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataKCR.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# AL pitchers don't bat unless they're visiting an NL team (interleague play).\n",
    "dataNYY = dataNYY.fillna(0)\n",
    "dataKCR = dataKCR.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Player Swap: Derek Jeter for Mike Sweeney"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have team and player data over the course of several years for both the New York Yankees and the Kansas City Royals, we want to perform a single player swap to see how these teams would have performed if the swap had actually happened.\n",
    "\n",
    "To do that, we go into our player data and remove Sweeney from his true team, the Kansas City Royals, and instead place Derek Jeter's data in with the KCR players. We do the same thing for the New York Yankees, removing Derek Jeter's data from them and replacing it with Mike Sweeney's."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>2B</th>\n",
       "      <th>3B</th>\n",
       "      <th>AB</th>\n",
       "      <th>BB</th>\n",
       "      <th>CS</th>\n",
       "      <th>G</th>\n",
       "      <th>GIDP</th>\n",
       "      <th>H</th>\n",
       "      <th>HBP</th>\n",
       "      <th>HR</th>\n",
       "      <th>...</th>\n",
       "      <th>age</th>\n",
       "      <th>batting_avg</th>\n",
       "      <th>onbase_perc</th>\n",
       "      <th>onbase_plus_slugging</th>\n",
       "      <th>onbase_plus_slugging_plus</th>\n",
       "      <th>player</th>\n",
       "      <th>pos</th>\n",
       "      <th>runs_allowed</th>\n",
       "      <th>slugging_perc</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>458</th>\n",
       "      <td>39</td>\n",
       "      <td>4</td>\n",
       "      <td>639</td>\n",
       "      <td>56</td>\n",
       "      <td>8</td>\n",
       "      <td>156</td>\n",
       "      <td>21</td>\n",
       "      <td>206</td>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>33</td>\n",
       "      <td>0.322</td>\n",
       "      <td>0.388</td>\n",
       "      <td>0.840</td>\n",
       "      <td>121.0</td>\n",
       "      <td>Derek Jeter</td>\n",
       "      <td>SS</td>\n",
       "      <td>777</td>\n",
       "      <td>0.452</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>39</td>\n",
       "      <td>3</td>\n",
       "      <td>623</td>\n",
       "      <td>69</td>\n",
       "      <td>5</td>\n",
       "      <td>154</td>\n",
       "      <td>13</td>\n",
       "      <td>214</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>...</td>\n",
       "      <td>32</td>\n",
       "      <td>0.343</td>\n",
       "      <td>0.417</td>\n",
       "      <td>0.900</td>\n",
       "      <td>132.0</td>\n",
       "      <td>Derek Jeter</td>\n",
       "      <td>SS</td>\n",
       "      <td>767</td>\n",
       "      <td>0.483</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>556</th>\n",
       "      <td>25</td>\n",
       "      <td>5</td>\n",
       "      <td>654</td>\n",
       "      <td>77</td>\n",
       "      <td>5</td>\n",
       "      <td>159</td>\n",
       "      <td>15</td>\n",
       "      <td>202</td>\n",
       "      <td>11</td>\n",
       "      <td>19</td>\n",
       "      <td>...</td>\n",
       "      <td>31</td>\n",
       "      <td>0.309</td>\n",
       "      <td>0.389</td>\n",
       "      <td>0.839</td>\n",
       "      <td>125.0</td>\n",
       "      <td>Derek Jeter</td>\n",
       "      <td>SS</td>\n",
       "      <td>789</td>\n",
       "      <td>0.450</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>607</th>\n",
       "      <td>44</td>\n",
       "      <td>1</td>\n",
       "      <td>643</td>\n",
       "      <td>46</td>\n",
       "      <td>4</td>\n",
       "      <td>154</td>\n",
       "      <td>19</td>\n",
       "      <td>188</td>\n",
       "      <td>14</td>\n",
       "      <td>23</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>0.292</td>\n",
       "      <td>0.352</td>\n",
       "      <td>0.823</td>\n",
       "      <td>114.0</td>\n",
       "      <td>Derek Jeter</td>\n",
       "      <td>SS</td>\n",
       "      <td>808</td>\n",
       "      <td>0.471</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650</th>\n",
       "      <td>25</td>\n",
       "      <td>3</td>\n",
       "      <td>482</td>\n",
       "      <td>43</td>\n",
       "      <td>5</td>\n",
       "      <td>119</td>\n",
       "      <td>10</td>\n",
       "      <td>156</td>\n",
       "      <td>13</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>29</td>\n",
       "      <td>0.324</td>\n",
       "      <td>0.393</td>\n",
       "      <td>0.844</td>\n",
       "      <td>125.0</td>\n",
       "      <td>Derek Jeter</td>\n",
       "      <td>SS</td>\n",
       "      <td>716</td>\n",
       "      <td>0.450</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     2B  3B   AB  BB  CS    G  GIDP    H  HBP  HR  ...   age  batting_avg  \\\n",
       "458  39   4  639  56   8  156    21  206   14  12  ...    33        0.322   \n",
       "507  39   3  623  69   5  154    13  214   12  14  ...    32        0.343   \n",
       "556  25   5  654  77   5  159    15  202   11  19  ...    31        0.309   \n",
       "607  44   1  643  46   4  154    19  188   14  23  ...    30        0.292   \n",
       "650  25   3  482  43   5  119    10  156   13  10  ...    29        0.324   \n",
       "\n",
       "     onbase_perc  onbase_plus_slugging  onbase_plus_slugging_plus  \\\n",
       "458        0.388                 0.840                      121.0   \n",
       "507        0.417                 0.900                      132.0   \n",
       "556        0.389                 0.839                      125.0   \n",
       "607        0.352                 0.823                      114.0   \n",
       "650        0.393                 0.844                      125.0   \n",
       "\n",
       "          player  pos  runs_allowed  slugging_perc  year  \n",
       "458  Derek Jeter   SS           777          0.452  2007  \n",
       "507  Derek Jeter   SS           767          0.483  2006  \n",
       "556  Derek Jeter   SS           789          0.450  2005  \n",
       "607  Derek Jeter   SS           808          0.471  2004  \n",
       "650  Derek Jeter   SS           716          0.450  2003  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we needed data less than or equal to 2007 because in 2007, Mike Sweeney left his team\n",
    "df = dataNYY[dataNYY[\"year\"] <= 2007]\n",
    "# get all of Jeters statistics\n",
    "jeter = df[df[\"player\"] == \"Derek Jeter\"]\n",
    "jeter.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "scrolled": 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>2B</th>\n",
       "      <th>3B</th>\n",
       "      <th>AB</th>\n",
       "      <th>BB</th>\n",
       "      <th>CS</th>\n",
       "      <th>G</th>\n",
       "      <th>GIDP</th>\n",
       "      <th>H</th>\n",
       "      <th>HBP</th>\n",
       "      <th>HR</th>\n",
       "      <th>...</th>\n",
       "      <th>age</th>\n",
       "      <th>batting_avg</th>\n",
       "      <th>onbase_perc</th>\n",
       "      <th>onbase_plus_slugging</th>\n",
       "      <th>onbase_plus_slugging_plus</th>\n",
       "      <th>player</th>\n",
       "      <th>pos</th>\n",
       "      <th>runs_allowed</th>\n",
       "      <th>slugging_perc</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>408</th>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>265</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "      <td>74</td>\n",
       "      <td>9</td>\n",
       "      <td>69</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>33</td>\n",
       "      <td>0.260</td>\n",
       "      <td>0.315</td>\n",
       "      <td>0.719</td>\n",
       "      <td>88.0</td>\n",
       "      <td>Mike Sweeney</td>\n",
       "      <td>DH</td>\n",
       "      <td>778</td>\n",
       "      <td>0.404</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>217</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>60</td>\n",
       "      <td>5</td>\n",
       "      <td>56</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>32</td>\n",
       "      <td>0.258</td>\n",
       "      <td>0.349</td>\n",
       "      <td>0.787</td>\n",
       "      <td>102.0</td>\n",
       "      <td>Mike Sweeney</td>\n",
       "      <td>DH</td>\n",
       "      <td>971</td>\n",
       "      <td>0.438</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506</th>\n",
       "      <td>39</td>\n",
       "      <td>0</td>\n",
       "      <td>470</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>122</td>\n",
       "      <td>16</td>\n",
       "      <td>141</td>\n",
       "      <td>4</td>\n",
       "      <td>21</td>\n",
       "      <td>...</td>\n",
       "      <td>31</td>\n",
       "      <td>0.300</td>\n",
       "      <td>0.347</td>\n",
       "      <td>0.864</td>\n",
       "      <td>127.0</td>\n",
       "      <td>Mike Sweeney</td>\n",
       "      <td>DH</td>\n",
       "      <td>935</td>\n",
       "      <td>0.517</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>552</th>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>411</td>\n",
       "      <td>33</td>\n",
       "      <td>2</td>\n",
       "      <td>106</td>\n",
       "      <td>7</td>\n",
       "      <td>118</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>...</td>\n",
       "      <td>30</td>\n",
       "      <td>0.287</td>\n",
       "      <td>0.347</td>\n",
       "      <td>0.851</td>\n",
       "      <td>118.0</td>\n",
       "      <td>Mike Sweeney</td>\n",
       "      <td>DH</td>\n",
       "      <td>905</td>\n",
       "      <td>0.504</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>610</th>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>392</td>\n",
       "      <td>64</td>\n",
       "      <td>2</td>\n",
       "      <td>108</td>\n",
       "      <td>13</td>\n",
       "      <td>115</td>\n",
       "      <td>2</td>\n",
       "      <td>16</td>\n",
       "      <td>...</td>\n",
       "      <td>29</td>\n",
       "      <td>0.293</td>\n",
       "      <td>0.391</td>\n",
       "      <td>0.858</td>\n",
       "      <td>120.0</td>\n",
       "      <td>Mike Sweeney</td>\n",
       "      <td>DH</td>\n",
       "      <td>867</td>\n",
       "      <td>0.467</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     2B  3B   AB  BB  CS    G  GIDP    H  HBP  HR  ...   age  batting_avg  \\\n",
       "408  15   1  265  17   0   74     9   69    5   7  ...    33        0.260   \n",
       "451  15   0  217  28   0   60     5   56    4   8  ...    32        0.258   \n",
       "506  39   0  470  33   0  122    16  141    4  21  ...    31        0.300   \n",
       "552  23   0  411  33   2  106     7  118    6  22  ...    30        0.287   \n",
       "610  18   1  392  64   2  108    13  115    2  16  ...    29        0.293   \n",
       "\n",
       "     onbase_perc  onbase_plus_slugging  onbase_plus_slugging_plus  \\\n",
       "408        0.315                 0.719                       88.0   \n",
       "451        0.349                 0.787                      102.0   \n",
       "506        0.347                 0.864                      127.0   \n",
       "552        0.347                 0.851                      118.0   \n",
       "610        0.391                 0.858                      120.0   \n",
       "\n",
       "           player  pos  runs_allowed  slugging_perc  year  \n",
       "408  Mike Sweeney   DH           778          0.404  2007  \n",
       "451  Mike Sweeney   DH           971          0.438  2006  \n",
       "506  Mike Sweeney   DH           935          0.517  2005  \n",
       "552  Mike Sweeney   DH           905          0.504  2004  \n",
       "610  Mike Sweeney   DH           867          0.467  2003  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# like before, we want to swap these two players so we can only go up to 2007 because we want data for both in our swap\n",
    "df = dataKCR[dataKCR[\"year\"] <= 2007]\n",
    "# retrieve all of Sweeney's statistics\n",
    "sweeney = df[df[\"player\"] == \"Mike Sweeney\"]\n",
    "sweeney.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "year\n",
       "1995    745\n",
       "1996    790\n",
       "1997    805\n",
       "1998    870\n",
       "1999    867\n",
       "2000    857\n",
       "2001    791\n",
       "       ... \n",
       "2010    748\n",
       "2011    783\n",
       "2012    705\n",
       "2013    642\n",
       "2014    586\n",
       "2015    764\n",
       "2016    680\n",
       "Name: R, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove Derek Jeter from his real team (the New York Yankees)\n",
    "no_jeter = dataNYY[dataNYY[\"player\"] != \"Derek Jeter\"]\n",
    "# add Sweeney into the NYY player stats (perform the swap)\n",
    "sweeney_in_NYY = pd.concat([no_jeter, sweeney], axis=0)\n",
    "sweeney_in_NYY.groupby(\"year\")['R'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "year\n",
       "1995    633\n",
       "1996    827\n",
       "1997    833\n",
       "1998    809\n",
       "1999    889\n",
       "2000    893\n",
       "2001    742\n",
       "       ... \n",
       "2010    676\n",
       "2011    730\n",
       "2012    676\n",
       "2013    648\n",
       "2014    651\n",
       "2015    724\n",
       "2016    675\n",
       "Name: R, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# remove Sweeney from his real team the Kansas City Royals\n",
    "no_sweeney = dataKCR[dataKCR[\"player\"] != \"Mike Sweeney\"]\n",
    "# add Jeter into the KCR player stats\n",
    "jeter_in_KCR = pd.concat([no_sweeney, jeter], axis=0)\n",
    "jeter_in_KCR.groupby(\"year\")['R'].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# create CSVs for later use\n",
    "sweeney_in_NYY.to_csv(\"sweeney_in_NYY.csv\", index=False)\n",
    "jeter_in_KCR.to_csv(\"jeter_in_KCR.csv\", index=False)\n",
    "dataNYY.to_csv(\"nyy.csv\", index=False)\n",
    "dataKCR.to_csv(\"kcr.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "nyy.to_csv(\"nyy_links.csv\", index=False)\n",
    "kcr.to_csv(\"kcr_links.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}