project_EDA_v2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "941edfb1",
   "metadata": {},
   "source": [
    "# EDA\n",
    "Files:\n",
    "- A pre-merged dataset containing statistics for period 1 game that includes:\n",
    "    - game.csv\n",
    "    - game_teams_stats.csv\n",
    "    - game_plays.csv\n",
    "\n",
    "- game_skater_stats.csv\n",
    "- game_goalie_stats.csv\n",
    "- player_info.csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5e79270d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import copy\n",
    "import math\n",
    "\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "2f502d07",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = 'nhl_data/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "60a1ac9a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(26305, 15)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_id</th>\n",
       "      <th>season</th>\n",
       "      <th>type</th>\n",
       "      <th>date_time_GMT</th>\n",
       "      <th>away_team_id</th>\n",
       "      <th>home_team_id</th>\n",
       "      <th>away_goals</th>\n",
       "      <th>home_goals</th>\n",
       "      <th>outcome</th>\n",
       "      <th>home_rink_side_start</th>\n",
       "      <th>venue</th>\n",
       "      <th>venue_link</th>\n",
       "      <th>venue_time_zone_id</th>\n",
       "      <th>venue_time_zone_offset</th>\n",
       "      <th>venue_time_zone_tz</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>20162017</td>\n",
       "      <td>R</td>\n",
       "      <td>2016-10-19T00:30:00Z</td>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>home win REG</td>\n",
       "      <td>right</td>\n",
       "      <td>United Center</td>\n",
       "      <td>/api/v1/venues/null</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>-5</td>\n",
       "      <td>CDT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>20172018</td>\n",
       "      <td>R</td>\n",
       "      <td>2018-02-07T00:00:00Z</td>\n",
       "      <td>24</td>\n",
       "      <td>7</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>away win OT</td>\n",
       "      <td>left</td>\n",
       "      <td>KeyBank Center</td>\n",
       "      <td>/api/v1/venues/null</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>-4</td>\n",
       "      <td>EDT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015020314</td>\n",
       "      <td>20152016</td>\n",
       "      <td>R</td>\n",
       "      <td>2015-11-24T01:00:00Z</td>\n",
       "      <td>21</td>\n",
       "      <td>52</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>away win REG</td>\n",
       "      <td>right</td>\n",
       "      <td>MTS Centre</td>\n",
       "      <td>/api/v1/venues/null</td>\n",
       "      <td>America/Winnipeg</td>\n",
       "      <td>-5</td>\n",
       "      <td>CDT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015020849</td>\n",
       "      <td>20152016</td>\n",
       "      <td>R</td>\n",
       "      <td>2016-02-17T00:00:00Z</td>\n",
       "      <td>52</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>home win REG</td>\n",
       "      <td>right</td>\n",
       "      <td>PNC Arena</td>\n",
       "      <td>/api/v1/venues/null</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>-4</td>\n",
       "      <td>EDT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017020586</td>\n",
       "      <td>20172018</td>\n",
       "      <td>R</td>\n",
       "      <td>2017-12-30T03:00:00Z</td>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>home win REG</td>\n",
       "      <td>left</td>\n",
       "      <td>Honda Center</td>\n",
       "      <td>/api/v1/venues/null</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>-7</td>\n",
       "      <td>PDT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id    season type         date_time_GMT  away_team_id  \\\n",
       "0  2016020045  20162017    R  2016-10-19T00:30:00Z             4   \n",
       "1  2017020812  20172018    R  2018-02-07T00:00:00Z            24   \n",
       "2  2015020314  20152016    R  2015-11-24T01:00:00Z            21   \n",
       "3  2015020849  20152016    R  2016-02-17T00:00:00Z            52   \n",
       "4  2017020586  20172018    R  2017-12-30T03:00:00Z            20   \n",
       "\n",
       "   home_team_id  away_goals  home_goals       outcome home_rink_side_start  \\\n",
       "0            16           4           7  home win REG                right   \n",
       "1             7           4           3   away win OT                 left   \n",
       "2            52           4           1  away win REG                right   \n",
       "3            12           1           2  home win REG                right   \n",
       "4            24           1           2  home win REG                 left   \n",
       "\n",
       "            venue           venue_link   venue_time_zone_id  \\\n",
       "0   United Center  /api/v1/venues/null      America/Chicago   \n",
       "1  KeyBank Center  /api/v1/venues/null     America/New_York   \n",
       "2      MTS Centre  /api/v1/venues/null     America/Winnipeg   \n",
       "3       PNC Arena  /api/v1/venues/null     America/New_York   \n",
       "4    Honda Center  /api/v1/venues/null  America/Los_Angeles   \n",
       "\n",
       "   venue_time_zone_offset venue_time_zone_tz  \n",
       "0                      -5                CDT  \n",
       "1                      -4                EDT  \n",
       "2                      -5                CDT  \n",
       "3                      -4                EDT  \n",
       "4                      -7                PDT  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "games = pd.read_csv(data_path+'game.csv')\n",
    "print(games.shape)\n",
    "games.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6bf03313",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(52610, 17)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>HoA</th>\n",
       "      <th>won</th>\n",
       "      <th>settled_in</th>\n",
       "      <th>head_coach</th>\n",
       "      <th>goals</th>\n",
       "      <th>shots</th>\n",
       "      <th>hits</th>\n",
       "      <th>pim</th>\n",
       "      <th>powerPlayOpportunities</th>\n",
       "      <th>powerPlayGoals</th>\n",
       "      <th>faceOffWinPercentage</th>\n",
       "      <th>giveaways</th>\n",
       "      <th>takeaways</th>\n",
       "      <th>blocked</th>\n",
       "      <th>startRinkSide</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>4</td>\n",
       "      <td>away</td>\n",
       "      <td>False</td>\n",
       "      <td>REG</td>\n",
       "      <td>Dave Hakstol</td>\n",
       "      <td>4.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>50.9</td>\n",
       "      <td>12.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>left</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>16</td>\n",
       "      <td>home</td>\n",
       "      <td>True</td>\n",
       "      <td>REG</td>\n",
       "      <td>Joel Quenneville</td>\n",
       "      <td>7.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>49.1</td>\n",
       "      <td>16.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>left</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>24</td>\n",
       "      <td>away</td>\n",
       "      <td>True</td>\n",
       "      <td>OT</td>\n",
       "      <td>Randy Carlyle</td>\n",
       "      <td>4.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>43.8</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>right</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>7</td>\n",
       "      <td>home</td>\n",
       "      <td>False</td>\n",
       "      <td>OT</td>\n",
       "      <td>Phil Housley</td>\n",
       "      <td>3.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>56.2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>right</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015020314</td>\n",
       "      <td>21</td>\n",
       "      <td>away</td>\n",
       "      <td>True</td>\n",
       "      <td>REG</td>\n",
       "      <td>Patrick Roy</td>\n",
       "      <td>4.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>left</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id  team_id   HoA    won settled_in        head_coach  goals  \\\n",
       "0  2016020045        4  away  False        REG      Dave Hakstol    4.0   \n",
       "1  2016020045       16  home   True        REG  Joel Quenneville    7.0   \n",
       "2  2017020812       24  away   True         OT     Randy Carlyle    4.0   \n",
       "3  2017020812        7  home  False         OT      Phil Housley    3.0   \n",
       "4  2015020314       21  away   True        REG       Patrick Roy    4.0   \n",
       "\n",
       "   shots  hits  pim  powerPlayOpportunities  powerPlayGoals  \\\n",
       "0   27.0  30.0  6.0                     4.0             2.0   \n",
       "1   28.0  20.0  8.0                     3.0             2.0   \n",
       "2   34.0  16.0  6.0                     3.0             1.0   \n",
       "3   33.0  17.0  8.0                     2.0             1.0   \n",
       "4   29.0  17.0  9.0                     3.0             1.0   \n",
       "\n",
       "   faceOffWinPercentage  giveaways  takeaways  blocked startRinkSide  \n",
       "0                  50.9       12.0        9.0     11.0          left  \n",
       "1                  49.1       16.0        8.0      9.0          left  \n",
       "2                  43.8        7.0        4.0     14.0         right  \n",
       "3                  56.2        5.0        6.0     14.0         right  \n",
       "4                  45.7       13.0        5.0     20.0          left  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "game_stats = pd.read_csv(data_path+'game_teams_stats.csv')\n",
    "print(game_stats.shape)\n",
    "game_stats.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e120afd6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(945830, 22)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_id</th>\n",
       "      <th>player_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>timeOnIce</th>\n",
       "      <th>assists</th>\n",
       "      <th>goals</th>\n",
       "      <th>shots</th>\n",
       "      <th>hits</th>\n",
       "      <th>powerPlayGoals</th>\n",
       "      <th>powerPlayAssists</th>\n",
       "      <th>penaltyMinutes</th>\n",
       "      <th>faceOffWins</th>\n",
       "      <th>faceoffTaken</th>\n",
       "      <th>takeaways</th>\n",
       "      <th>giveaways</th>\n",
       "      <th>shortHandedGoals</th>\n",
       "      <th>shortHandedAssists</th>\n",
       "      <th>blocked</th>\n",
       "      <th>plusMinus</th>\n",
       "      <th>evenTimeOnIce</th>\n",
       "      <th>shortHandedTimeOnIce</th>\n",
       "      <th>powerPlayTimeOnIce</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8468513</td>\n",
       "      <td>4</td>\n",
       "      <td>955</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.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.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>858</td>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8476906</td>\n",
       "      <td>4</td>\n",
       "      <td>1396</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1177</td>\n",
       "      <td>0</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8474668</td>\n",
       "      <td>4</td>\n",
       "      <td>915</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>805</td>\n",
       "      <td>0</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8473512</td>\n",
       "      <td>4</td>\n",
       "      <td>1367</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1083</td>\n",
       "      <td>19</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8471762</td>\n",
       "      <td>4</td>\n",
       "      <td>676</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>613</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id  player_id  team_id  timeOnIce  assists  goals  shots  hits  \\\n",
       "0  2016020045    8468513        4        955        1      0      0   2.0   \n",
       "1  2016020045    8476906        4       1396        1      0      4   2.0   \n",
       "2  2016020045    8474668        4        915        0      0      1   1.0   \n",
       "3  2016020045    8473512        4       1367        3      0      0   0.0   \n",
       "4  2016020045    8471762        4        676        0      0      3   2.0   \n",
       "\n",
       "   powerPlayGoals  powerPlayAssists  penaltyMinutes  faceOffWins  \\\n",
       "0               0                 0               0            0   \n",
       "1               0                 0               2            0   \n",
       "2               0                 0               0            0   \n",
       "3               0                 2               0           11   \n",
       "4               0                 0               0            0   \n",
       "\n",
       "   faceoffTaken  takeaways  giveaways  shortHandedGoals  shortHandedAssists  \\\n",
       "0             0        1.0        1.0                 0                   0   \n",
       "1             0        1.0        2.0                 0                   0   \n",
       "2             0        2.0        0.0                 0                   0   \n",
       "3            27        0.0        0.0                 0                   0   \n",
       "4             0        0.0        1.0                 0                   0   \n",
       "\n",
       "   blocked  plusMinus  evenTimeOnIce  shortHandedTimeOnIce  powerPlayTimeOnIce  \n",
       "0      1.0          1            858                    97                   0  \n",
       "1      2.0          0           1177                     0                 219  \n",
       "2      0.0         -1            805                     0                 110  \n",
       "3      0.0         -1           1083                    19                 265  \n",
       "4      0.0         -1            613                    63                   0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "game_skater_stats = pd.read_csv(data_path+'game_skater_stats.csv')\n",
    "print(game_skater_stats.shape)\n",
    "game_skater_stats.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "19b231ff",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(56656, 19)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_id</th>\n",
       "      <th>player_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>timeOnIce</th>\n",
       "      <th>assists</th>\n",
       "      <th>goals</th>\n",
       "      <th>pim</th>\n",
       "      <th>shots</th>\n",
       "      <th>saves</th>\n",
       "      <th>powerPlaySaves</th>\n",
       "      <th>shortHandedSaves</th>\n",
       "      <th>evenSaves</th>\n",
       "      <th>shortHandedShotsAgainst</th>\n",
       "      <th>evenShotsAgainst</th>\n",
       "      <th>powerPlayShotsAgainst</th>\n",
       "      <th>decision</th>\n",
       "      <th>savePercentage</th>\n",
       "      <th>powerPlaySavePercentage</th>\n",
       "      <th>evenStrengthSavePercentage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8473607</td>\n",
       "      <td>4</td>\n",
       "      <td>1504</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>33.333333</td>\n",
       "      <td>84.615385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8473461</td>\n",
       "      <td>4</td>\n",
       "      <td>2011</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>L</td>\n",
       "      <td>81.818182</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>80.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8470645</td>\n",
       "      <td>16</td>\n",
       "      <td>3598</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>23</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>4</td>\n",
       "      <td>W</td>\n",
       "      <td>85.185185</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>91.304348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>8468011</td>\n",
       "      <td>24</td>\n",
       "      <td>3696</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>27</td>\n",
       "      <td>3</td>\n",
       "      <td>28</td>\n",
       "      <td>2</td>\n",
       "      <td>W</td>\n",
       "      <td>90.909091</td>\n",
       "      <td>50.000000</td>\n",
       "      <td>96.428571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>8475215</td>\n",
       "      <td>7</td>\n",
       "      <td>3539</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>33</td>\n",
       "      <td>29</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>5</td>\n",
       "      <td>L</td>\n",
       "      <td>87.878788</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>88.888889</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id  player_id  team_id  timeOnIce  assists  goals  pim  shots  \\\n",
       "0  2016020045    8473607        4       1504        0      0    0     16   \n",
       "1  2016020045    8473461        4       2011        0      0    0     11   \n",
       "2  2016020045    8470645       16       3598        0      0    0     27   \n",
       "3  2017020812    8468011       24       3696        0      0    0     33   \n",
       "4  2017020812    8475215        7       3539        0      0    0     33   \n",
       "\n",
       "   saves  powerPlaySaves  shortHandedSaves  evenSaves  \\\n",
       "0     12               1                 0         11   \n",
       "1      9               1                 0          8   \n",
       "2     23               2                 0         21   \n",
       "3     30               1                 2         27   \n",
       "4     29               4                 1         24   \n",
       "\n",
       "   shortHandedShotsAgainst  evenShotsAgainst  powerPlayShotsAgainst decision  \\\n",
       "0                        0                13                      3      NaN   \n",
       "1                        0                10                      1        L   \n",
       "2                        0                23                      4        W   \n",
       "3                        3                28                      2        W   \n",
       "4                        1                27                      5        L   \n",
       "\n",
       "   savePercentage  powerPlaySavePercentage  evenStrengthSavePercentage  \n",
       "0       75.000000                33.333333                   84.615385  \n",
       "1       81.818182               100.000000                   80.000000  \n",
       "2       85.185185                50.000000                   91.304348  \n",
       "3       90.909091                50.000000                   96.428571  \n",
       "4       87.878788                80.000000                   88.888889  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "game_goalie_stats = pd.read_csv(data_path+'game_goalie_stats.csv')\n",
    "print(game_goalie_stats.shape)\n",
    "game_goalie_stats.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "06da54bd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3925, 12)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>player_id</th>\n",
       "      <th>firstName</th>\n",
       "      <th>lastName</th>\n",
       "      <th>nationality</th>\n",
       "      <th>birthCity</th>\n",
       "      <th>primaryPosition</th>\n",
       "      <th>birthDate</th>\n",
       "      <th>birthStateProvince</th>\n",
       "      <th>height</th>\n",
       "      <th>height_cm</th>\n",
       "      <th>weight</th>\n",
       "      <th>shootsCatches</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8466148</td>\n",
       "      <td>Marian</td>\n",
       "      <td>Hossa</td>\n",
       "      <td>SVK</td>\n",
       "      <td>Stará Lubovna</td>\n",
       "      <td>RW</td>\n",
       "      <td>1979-01-12 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6' 1\"</td>\n",
       "      <td>185.42</td>\n",
       "      <td>207.0</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8465058</td>\n",
       "      <td>Michal</td>\n",
       "      <td>Rozsival</td>\n",
       "      <td>CZE</td>\n",
       "      <td>Vlasim</td>\n",
       "      <td>D</td>\n",
       "      <td>1978-09-03 01:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6' 1\"</td>\n",
       "      <td>185.42</td>\n",
       "      <td>210.0</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8476906</td>\n",
       "      <td>Shayne</td>\n",
       "      <td>Gostisbehere</td>\n",
       "      <td>USA</td>\n",
       "      <td>Pembroke Pines</td>\n",
       "      <td>D</td>\n",
       "      <td>1993-04-20 01:00:00</td>\n",
       "      <td>FL</td>\n",
       "      <td>5' 11\"</td>\n",
       "      <td>180.34</td>\n",
       "      <td>180.0</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8466285</td>\n",
       "      <td>Brian</td>\n",
       "      <td>Campbell</td>\n",
       "      <td>CAN</td>\n",
       "      <td>Strathroy</td>\n",
       "      <td>D</td>\n",
       "      <td>1979-05-23 01:00:00</td>\n",
       "      <td>ON</td>\n",
       "      <td>5' 10\"</td>\n",
       "      <td>177.80</td>\n",
       "      <td>192.0</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8470607</td>\n",
       "      <td>Brent</td>\n",
       "      <td>Seabrook</td>\n",
       "      <td>CAN</td>\n",
       "      <td>Richmond</td>\n",
       "      <td>D</td>\n",
       "      <td>1985-04-20 01:00:00</td>\n",
       "      <td>BC</td>\n",
       "      <td>6' 3\"</td>\n",
       "      <td>190.50</td>\n",
       "      <td>220.0</td>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   player_id firstName      lastName nationality       birthCity  \\\n",
       "0    8466148    Marian         Hossa         SVK   Stará Lubovna   \n",
       "1    8465058    Michal      Rozsival         CZE          Vlasim   \n",
       "2    8476906    Shayne  Gostisbehere         USA  Pembroke Pines   \n",
       "3    8466285     Brian      Campbell         CAN       Strathroy   \n",
       "4    8470607     Brent      Seabrook         CAN        Richmond   \n",
       "\n",
       "  primaryPosition            birthDate birthStateProvince  height  height_cm  \\\n",
       "0              RW  1979-01-12 00:00:00                NaN   6' 1\"     185.42   \n",
       "1               D  1978-09-03 01:00:00                NaN   6' 1\"     185.42   \n",
       "2               D  1993-04-20 01:00:00                 FL  5' 11\"     180.34   \n",
       "3               D  1979-05-23 01:00:00                 ON  5' 10\"     177.80   \n",
       "4               D  1985-04-20 01:00:00                 BC   6' 3\"     190.50   \n",
       "\n",
       "   weight shootsCatches  \n",
       "0   207.0             L  \n",
       "1   210.0             R  \n",
       "2   180.0             L  \n",
       "3   192.0             L  \n",
       "4   220.0             R  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "game_player = pd.read_csv(data_path+'player_info.csv')\n",
    "print(game_player.shape)\n",
    "game_player.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c3bfae1",
   "metadata": {},
   "source": [
    "## Merge other data \n",
    "- drop duplicates\n",
    "- append columns to the 'Period_1_Game_Stats.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfbbdbef",
   "metadata": {},
   "source": [
    "### Merge game_skater_stats and period 1 data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a010f715",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['game_id', 'team_id_for', 'HoA', 'won', 'settled_in', 'head_coach',\n",
      "       'goals', 'shots', 'hits', 'pim', 'powerPlayOpportunities',\n",
      "       'powerPlayGoals', 'faceOffWinPercentage', 'giveaways', 'takeaways',\n",
      "       'blocked', 'startRinkSide', 'Shots', 'Goals', 'Takeaways', 'Hits',\n",
      "       'Blocked Shots', 'Giveaways', 'Missed Shots', 'Penalities',\n",
      "       '#Won Faceoffs'],\n",
      "      dtype='object')\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_id</th>\n",
       "      <th>team_id_for</th>\n",
       "      <th>HoA</th>\n",
       "      <th>won</th>\n",
       "      <th>settled_in</th>\n",
       "      <th>head_coach</th>\n",
       "      <th>pim</th>\n",
       "      <th>powerPlayOpportunities</th>\n",
       "      <th>powerPlayGoals</th>\n",
       "      <th>faceOffWinPercentage</th>\n",
       "      <th>startRinkSide</th>\n",
       "      <th>Shots</th>\n",
       "      <th>Goals</th>\n",
       "      <th>Takeaways</th>\n",
       "      <th>Hits</th>\n",
       "      <th>Blocked Shots</th>\n",
       "      <th>Giveaways</th>\n",
       "      <th>Missed Shots</th>\n",
       "      <th>Penalities</th>\n",
       "      <th>#Won Faceoffs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>4</td>\n",
       "      <td>away</td>\n",
       "      <td>False</td>\n",
       "      <td>REG</td>\n",
       "      <td>Dave Hakstol</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>50.9</td>\n",
       "      <td>left</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>16</td>\n",
       "      <td>home</td>\n",
       "      <td>True</td>\n",
       "      <td>REG</td>\n",
       "      <td>Joel Quenneville</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>49.1</td>\n",
       "      <td>left</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>24</td>\n",
       "      <td>away</td>\n",
       "      <td>True</td>\n",
       "      <td>OT</td>\n",
       "      <td>Randy Carlyle</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>43.8</td>\n",
       "      <td>right</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>7</td>\n",
       "      <td>home</td>\n",
       "      <td>False</td>\n",
       "      <td>OT</td>\n",
       "      <td>Phil Housley</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>56.2</td>\n",
       "      <td>right</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015020314</td>\n",
       "      <td>21</td>\n",
       "      <td>away</td>\n",
       "      <td>True</td>\n",
       "      <td>REG</td>\n",
       "      <td>Patrick Roy</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>left</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id  team_id_for   HoA    won settled_in        head_coach  pim  \\\n",
       "0  2016020045            4  away  False        REG      Dave Hakstol  6.0   \n",
       "1  2016020045           16  home   True        REG  Joel Quenneville  8.0   \n",
       "2  2017020812           24  away   True         OT     Randy Carlyle  6.0   \n",
       "3  2017020812            7  home  False         OT      Phil Housley  8.0   \n",
       "4  2015020314           21  away   True        REG       Patrick Roy  9.0   \n",
       "\n",
       "   powerPlayOpportunities  powerPlayGoals  faceOffWinPercentage startRinkSide  \\\n",
       "0                     4.0             2.0                  50.9          left   \n",
       "1                     3.0             2.0                  49.1          left   \n",
       "2                     3.0             1.0                  43.8         right   \n",
       "3                     2.0             1.0                  56.2         right   \n",
       "4                     3.0             1.0                  45.7          left   \n",
       "\n",
       "   Shots  Goals  Takeaways  Hits  Blocked Shots  Giveaways  Missed Shots  \\\n",
       "0    8.0    0.0        1.0  14.0            3.0        6.0           4.0   \n",
       "1    8.0    3.0        3.0   5.0            3.0        7.0           0.0   \n",
       "2   11.0    0.0        0.0   4.0            6.0        2.0           6.0   \n",
       "3   12.0    1.0        2.0   4.0            8.0        0.0           3.0   \n",
       "4    9.0    0.0        3.0   4.0            7.0        7.0           3.0   \n",
       "\n",
       "   Penalities  #Won Faceoffs  \n",
       "0         1.0           10.0  \n",
       "1         1.0           12.0  \n",
       "2         1.0           11.0  \n",
       "3         3.0            9.0  \n",
       "4         2.0            7.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "period1 = pd.read_csv(data_path+'Period_1_Game_Stats.csv')\n",
    "print(period1.columns)\n",
    "period1 = period1.drop(columns = ['goals', 'shots', 'hits', 'giveaways', 'takeaways', 'blocked'])\n",
    "period1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c2e48231",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['game_id', 'player_id', 'team_id', 'timeOnIce', 'assists', 'goals',\n",
      "       'shots', 'hits', 'powerPlayGoals', 'powerPlayAssists', 'penaltyMinutes',\n",
      "       'faceOffWins', 'faceoffTaken', 'takeaways', 'giveaways',\n",
      "       'shortHandedGoals', 'shortHandedAssists', 'blocked', 'plusMinus',\n",
      "       'evenTimeOnIce', 'shortHandedTimeOnIce', 'powerPlayTimeOnIce'],\n",
      "      dtype='object')\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_id</th>\n",
       "      <th>player_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>timeOnIce</th>\n",
       "      <th>assists</th>\n",
       "      <th>goals</th>\n",
       "      <th>shots</th>\n",
       "      <th>hits</th>\n",
       "      <th>powerPlayGoals</th>\n",
       "      <th>powerPlayAssists</th>\n",
       "      <th>penaltyMinutes</th>\n",
       "      <th>faceOffWins</th>\n",
       "      <th>faceoffTaken</th>\n",
       "      <th>takeaways</th>\n",
       "      <th>giveaways</th>\n",
       "      <th>shortHandedGoals</th>\n",
       "      <th>shortHandedAssists</th>\n",
       "      <th>blocked</th>\n",
       "      <th>plusMinus</th>\n",
       "      <th>evenTimeOnIce</th>\n",
       "      <th>shortHandedTimeOnIce</th>\n",
       "      <th>powerPlayTimeOnIce</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8468513</td>\n",
       "      <td>4</td>\n",
       "      <td>955</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.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.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>858</td>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8476906</td>\n",
       "      <td>4</td>\n",
       "      <td>1396</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1177</td>\n",
       "      <td>0</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8474668</td>\n",
       "      <td>4</td>\n",
       "      <td>915</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>805</td>\n",
       "      <td>0</td>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8473512</td>\n",
       "      <td>4</td>\n",
       "      <td>1367</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>27</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>1083</td>\n",
       "      <td>19</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>8471762</td>\n",
       "      <td>4</td>\n",
       "      <td>676</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1</td>\n",
       "      <td>613</td>\n",
       "      <td>63</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id  player_id  team_id  timeOnIce  assists  goals  shots  hits  \\\n",
       "0  2016020045    8468513        4        955        1      0      0   2.0   \n",
       "1  2016020045    8476906        4       1396        1      0      4   2.0   \n",
       "2  2016020045    8474668        4        915        0      0      1   1.0   \n",
       "3  2016020045    8473512        4       1367        3      0      0   0.0   \n",
       "4  2016020045    8471762        4        676        0      0      3   2.0   \n",
       "\n",
       "   powerPlayGoals  powerPlayAssists  penaltyMinutes  faceOffWins  \\\n",
       "0               0                 0               0            0   \n",
       "1               0                 0               2            0   \n",
       "2               0                 0               0            0   \n",
       "3               0                 2               0           11   \n",
       "4               0                 0               0            0   \n",
       "\n",
       "   faceoffTaken  takeaways  giveaways  shortHandedGoals  shortHandedAssists  \\\n",
       "0             0        1.0        1.0                 0                   0   \n",
       "1             0        1.0        2.0                 0                   0   \n",
       "2             0        2.0        0.0                 0                   0   \n",
       "3            27        0.0        0.0                 0                   0   \n",
       "4             0        0.0        1.0                 0                   0   \n",
       "\n",
       "   blocked  plusMinus  evenTimeOnIce  shortHandedTimeOnIce  powerPlayTimeOnIce  \n",
       "0      1.0          1            858                    97                   0  \n",
       "1      2.0          0           1177                     0                 219  \n",
       "2      0.0         -1            805                     0                 110  \n",
       "3      0.0         -1           1083                    19                 265  \n",
       "4      0.0         -1            613                    63                   0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "game_skater_stats = game_skater_stats.drop_duplicates()\n",
    "print(game_skater_stats.columns)\n",
    "game_skater_stats.head()\n",
    "# selected columns: timeOnIce, evenTimeOnIce, shortHandedTimeOnIce, powerPlayTimeOnIce"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1b31a688",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>timeOnIce</th>\n",
       "      <th>evenTimeOnIce</th>\n",
       "      <th>shortHandedTimeOnIce</th>\n",
       "      <th>powerPlayTimeOnIce</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2000020001</td>\n",
       "      <td>21</td>\n",
       "      <td>1013.944444</td>\n",
       "      <td>583.611111</td>\n",
       "      <td>169.722222</td>\n",
       "      <td>260.611111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2000020001</td>\n",
       "      <td>25</td>\n",
       "      <td>999.222222</td>\n",
       "      <td>582.944444</td>\n",
       "      <td>205.888889</td>\n",
       "      <td>210.388889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2000020002</td>\n",
       "      <td>6</td>\n",
       "      <td>1029.388889</td>\n",
       "      <td>687.111111</td>\n",
       "      <td>116.055556</td>\n",
       "      <td>226.222222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2000020002</td>\n",
       "      <td>9</td>\n",
       "      <td>1008.500000</td>\n",
       "      <td>685.333333</td>\n",
       "      <td>178.722222</td>\n",
       "      <td>144.444444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2000020003</td>\n",
       "      <td>7</td>\n",
       "      <td>951.000000</td>\n",
       "      <td>667.555556</td>\n",
       "      <td>166.833333</td>\n",
       "      <td>116.611111</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id  team_id    timeOnIce  evenTimeOnIce  shortHandedTimeOnIce  \\\n",
       "0  2000020001       21  1013.944444     583.611111            169.722222   \n",
       "1  2000020001       25   999.222222     582.944444            205.888889   \n",
       "2  2000020002        6  1029.388889     687.111111            116.055556   \n",
       "3  2000020002        9  1008.500000     685.333333            178.722222   \n",
       "4  2000020003        7   951.000000     667.555556            166.833333   \n",
       "\n",
       "   powerPlayTimeOnIce  \n",
       "0          260.611111  \n",
       "1          210.388889  \n",
       "2          226.222222  \n",
       "3          144.444444  \n",
       "4          116.611111  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "skater_agg_cols = game_skater_stats.groupby(['game_id', 'team_id'], as_index=False)[['timeOnIce', 'evenTimeOnIce', 'shortHandedTimeOnIce', 'powerPlayTimeOnIce']].mean()\n",
    "skater_agg_cols.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0e4227bc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(52610, 24)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_id</th>\n",
       "      <th>HoA</th>\n",
       "      <th>won</th>\n",
       "      <th>settled_in</th>\n",
       "      <th>head_coach</th>\n",
       "      <th>pim</th>\n",
       "      <th>powerPlayOpportunities</th>\n",
       "      <th>powerPlayGoals</th>\n",
       "      <th>faceOffWinPercentage</th>\n",
       "      <th>startRinkSide</th>\n",
       "      <th>Shots</th>\n",
       "      <th>Goals</th>\n",
       "      <th>Takeaways</th>\n",
       "      <th>Hits</th>\n",
       "      <th>Blocked Shots</th>\n",
       "      <th>Giveaways</th>\n",
       "      <th>Missed Shots</th>\n",
       "      <th>Penalities</th>\n",
       "      <th>#Won Faceoffs</th>\n",
       "      <th>team_id</th>\n",
       "      <th>timeOnIce</th>\n",
       "      <th>evenTimeOnIce</th>\n",
       "      <th>shortHandedTimeOnIce</th>\n",
       "      <th>powerPlayTimeOnIce</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>away</td>\n",
       "      <td>False</td>\n",
       "      <td>REG</td>\n",
       "      <td>Dave Hakstol</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>50.9</td>\n",
       "      <td>left</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>990.833333</td>\n",
       "      <td>841.388889</td>\n",
       "      <td>55.555556</td>\n",
       "      <td>93.888889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>home</td>\n",
       "      <td>True</td>\n",
       "      <td>REG</td>\n",
       "      <td>Joel Quenneville</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>49.1</td>\n",
       "      <td>left</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>981.333333</td>\n",
       "      <td>836.777778</td>\n",
       "      <td>75.111111</td>\n",
       "      <td>69.444444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>away</td>\n",
       "      <td>True</td>\n",
       "      <td>OT</td>\n",
       "      <td>Randy Carlyle</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>43.8</td>\n",
       "      <td>right</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1002.222222</td>\n",
       "      <td>879.611111</td>\n",
       "      <td>28.444444</td>\n",
       "      <td>94.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>home</td>\n",
       "      <td>False</td>\n",
       "      <td>OT</td>\n",
       "      <td>Phil Housley</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>56.2</td>\n",
       "      <td>right</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>999.222222</td>\n",
       "      <td>888.333333</td>\n",
       "      <td>75.333333</td>\n",
       "      <td>35.555556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015020314</td>\n",
       "      <td>away</td>\n",
       "      <td>True</td>\n",
       "      <td>REG</td>\n",
       "      <td>Patrick Roy</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>left</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>986.666667</td>\n",
       "      <td>844.722222</td>\n",
       "      <td>53.333333</td>\n",
       "      <td>88.611111</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id   HoA    won settled_in        head_coach  pim  \\\n",
       "0  2016020045  away  False        REG      Dave Hakstol  6.0   \n",
       "1  2016020045  home   True        REG  Joel Quenneville  8.0   \n",
       "2  2017020812  away   True         OT     Randy Carlyle  6.0   \n",
       "3  2017020812  home  False         OT      Phil Housley  8.0   \n",
       "4  2015020314  away   True        REG       Patrick Roy  9.0   \n",
       "\n",
       "   powerPlayOpportunities  powerPlayGoals  faceOffWinPercentage startRinkSide  \\\n",
       "0                     4.0             2.0                  50.9          left   \n",
       "1                     3.0             2.0                  49.1          left   \n",
       "2                     3.0             1.0                  43.8         right   \n",
       "3                     2.0             1.0                  56.2         right   \n",
       "4                     3.0             1.0                  45.7          left   \n",
       "\n",
       "   Shots  Goals  Takeaways  Hits  Blocked Shots  Giveaways  Missed Shots  \\\n",
       "0    8.0    0.0        1.0  14.0            3.0        6.0           4.0   \n",
       "1    8.0    3.0        3.0   5.0            3.0        7.0           0.0   \n",
       "2   11.0    0.0        0.0   4.0            6.0        2.0           6.0   \n",
       "3   12.0    1.0        2.0   4.0            8.0        0.0           3.0   \n",
       "4    9.0    0.0        3.0   4.0            7.0        7.0           3.0   \n",
       "\n",
       "   Penalities  #Won Faceoffs  team_id    timeOnIce  evenTimeOnIce  \\\n",
       "0         1.0           10.0      4.0   990.833333     841.388889   \n",
       "1         1.0           12.0     16.0   981.333333     836.777778   \n",
       "2         1.0           11.0     24.0  1002.222222     879.611111   \n",
       "3         3.0            9.0      7.0   999.222222     888.333333   \n",
       "4         2.0            7.0     21.0   986.666667     844.722222   \n",
       "\n",
       "   shortHandedTimeOnIce  powerPlayTimeOnIce  \n",
       "0             55.555556           93.888889  \n",
       "1             75.111111           69.444444  \n",
       "2             28.444444           94.166667  \n",
       "3             75.333333           35.555556  \n",
       "4             53.333333           88.611111  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined = period1.merge(skater_agg_cols, how='left', left_on=['game_id', 'team_id_for'], right_on=['game_id', 'team_id'])\n",
    "combined = combined.drop(columns = ['team_id_for'])\n",
    "print(combined.shape)\n",
    "combined.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "64e5c705",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['game_id', 'HoA', 'won', 'settled_in', 'head_coach', 'pim',\n",
       "       'powerPlayOpportunities', 'powerPlayGoals', 'faceOffWinPercentage',\n",
       "       'startRinkSide', 'Shots', 'Goals', 'Takeaways', 'Hits', 'Blocked Shots',\n",
       "       'Giveaways', 'Missed Shots', 'Penalities', '#Won Faceoffs', 'team_id',\n",
       "       'timeOnIce', 'evenTimeOnIce', 'shortHandedTimeOnIce',\n",
       "       'powerPlayTimeOnIce'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8334a328",
   "metadata": {},
   "source": [
    "### Merge game_goalie_stats and combined dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5b47d3a4",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['game_id', 'player_id', 'team_id', 'timeOnIce', 'assists', 'goals',\n",
       "       'pim', 'shots', 'saves', 'powerPlaySaves', 'shortHandedSaves',\n",
       "       'evenSaves', 'shortHandedShotsAgainst', 'evenShotsAgainst',\n",
       "       'powerPlayShotsAgainst', 'decision', 'savePercentage',\n",
       "       'powerPlaySavePercentage', 'evenStrengthSavePercentage'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "game_goalie_stats = game_goalie_stats.drop_duplicates()\n",
    "game_goalie_stats.columns\n",
    "# selected: team_id (is there a backup goalie?)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "38a96ec6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(52610, 25)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_id</th>\n",
       "      <th>HoA</th>\n",
       "      <th>won</th>\n",
       "      <th>settled_in</th>\n",
       "      <th>head_coach</th>\n",
       "      <th>pim</th>\n",
       "      <th>powerPlayOpportunities</th>\n",
       "      <th>powerPlayGoals</th>\n",
       "      <th>faceOffWinPercentage</th>\n",
       "      <th>startRinkSide</th>\n",
       "      <th>Shots</th>\n",
       "      <th>Goals</th>\n",
       "      <th>Takeaways</th>\n",
       "      <th>Hits</th>\n",
       "      <th>Blocked Shots</th>\n",
       "      <th>Giveaways</th>\n",
       "      <th>Missed Shots</th>\n",
       "      <th>Penalities</th>\n",
       "      <th>#Won Faceoffs</th>\n",
       "      <th>team_id</th>\n",
       "      <th>timeOnIce</th>\n",
       "      <th>evenTimeOnIce</th>\n",
       "      <th>shortHandedTimeOnIce</th>\n",
       "      <th>powerPlayTimeOnIce</th>\n",
       "      <th>goalie_replacement</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>away</td>\n",
       "      <td>False</td>\n",
       "      <td>REG</td>\n",
       "      <td>Dave Hakstol</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>50.9</td>\n",
       "      <td>left</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>990.833333</td>\n",
       "      <td>841.388889</td>\n",
       "      <td>55.555556</td>\n",
       "      <td>93.888889</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>home</td>\n",
       "      <td>True</td>\n",
       "      <td>REG</td>\n",
       "      <td>Joel Quenneville</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>49.1</td>\n",
       "      <td>left</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>981.333333</td>\n",
       "      <td>836.777778</td>\n",
       "      <td>75.111111</td>\n",
       "      <td>69.444444</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>away</td>\n",
       "      <td>True</td>\n",
       "      <td>OT</td>\n",
       "      <td>Randy Carlyle</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>43.8</td>\n",
       "      <td>right</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1002.222222</td>\n",
       "      <td>879.611111</td>\n",
       "      <td>28.444444</td>\n",
       "      <td>94.166667</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>home</td>\n",
       "      <td>False</td>\n",
       "      <td>OT</td>\n",
       "      <td>Phil Housley</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>56.2</td>\n",
       "      <td>right</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>999.222222</td>\n",
       "      <td>888.333333</td>\n",
       "      <td>75.333333</td>\n",
       "      <td>35.555556</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015020314</td>\n",
       "      <td>away</td>\n",
       "      <td>True</td>\n",
       "      <td>REG</td>\n",
       "      <td>Patrick Roy</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>45.7</td>\n",
       "      <td>left</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>986.666667</td>\n",
       "      <td>844.722222</td>\n",
       "      <td>53.333333</td>\n",
       "      <td>88.611111</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id   HoA    won settled_in        head_coach  pim  \\\n",
       "0  2016020045  away  False        REG      Dave Hakstol  6.0   \n",
       "1  2016020045  home   True        REG  Joel Quenneville  8.0   \n",
       "2  2017020812  away   True         OT     Randy Carlyle  6.0   \n",
       "3  2017020812  home  False         OT      Phil Housley  8.0   \n",
       "4  2015020314  away   True        REG       Patrick Roy  9.0   \n",
       "\n",
       "   powerPlayOpportunities  powerPlayGoals  faceOffWinPercentage startRinkSide  \\\n",
       "0                     4.0             2.0                  50.9          left   \n",
       "1                     3.0             2.0                  49.1          left   \n",
       "2                     3.0             1.0                  43.8         right   \n",
       "3                     2.0             1.0                  56.2         right   \n",
       "4                     3.0             1.0                  45.7          left   \n",
       "\n",
       "   Shots  Goals  Takeaways  Hits  Blocked Shots  Giveaways  Missed Shots  \\\n",
       "0    8.0    0.0        1.0  14.0            3.0        6.0           4.0   \n",
       "1    8.0    3.0        3.0   5.0            3.0        7.0           0.0   \n",
       "2   11.0    0.0        0.0   4.0            6.0        2.0           6.0   \n",
       "3   12.0    1.0        2.0   4.0            8.0        0.0           3.0   \n",
       "4    9.0    0.0        3.0   4.0            7.0        7.0           3.0   \n",
       "\n",
       "   Penalities  #Won Faceoffs  team_id    timeOnIce  evenTimeOnIce  \\\n",
       "0         1.0           10.0      4.0   990.833333     841.388889   \n",
       "1         1.0           12.0     16.0   981.333333     836.777778   \n",
       "2         1.0           11.0     24.0  1002.222222     879.611111   \n",
       "3         3.0            9.0      7.0   999.222222     888.333333   \n",
       "4         2.0            7.0     21.0   986.666667     844.722222   \n",
       "\n",
       "   shortHandedTimeOnIce  powerPlayTimeOnIce goalie_replacement  \n",
       "0             55.555556           93.888889                Yes  \n",
       "1             75.111111           69.444444                 No  \n",
       "2             28.444444           94.166667                 No  \n",
       "3             75.333333           35.555556                 No  \n",
       "4             53.333333           88.611111                 No  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "game_goalie_stats_subset = game_goalie_stats[['game_id', 'team_id']]\n",
    "game_goalie_stats_subset = game_goalie_stats_subset.groupby(['game_id', 'team_id'])[\"team_id\"].count().reset_index(name=\"goalie_count\")\n",
    "game_goalie_stats_subset['goalie_replacement'] = np.where(game_goalie_stats_subset['goalie_count'] == 2, \"Yes\", \"No\")\n",
    "goalie_agg_cols = game_goalie_stats_subset[['game_id', 'team_id', 'goalie_replacement']]\n",
    "\n",
    "combined = combined.merge(goalie_agg_cols, how='left', left_on=['game_id', 'team_id'], right_on=['game_id', 'team_id'])\n",
    "print(combined.shape)\n",
    "combined.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3bf7677",
   "metadata": {},
   "source": [
    "### Dealing with timing issue\n",
    "\n",
    "Assumption: Holding events occur evenly over each period. For the columns below,\n",
    "- pim\n",
    "- powerPlayOpportunities\n",
    "- powerPlayGoals\n",
    "- timeOnIce\n",
    "- evenTimeOnIce\n",
    "- shortHandedTimeOnIce\n",
    "- powerPlayTimeOnIce\n",
    "\n",
    "statistics in these columns will be divided by 3 to have values for just period 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "07645df4",
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = ['pim', 'powerPlayOpportunities', 'powerPlayGoals', 'timeOnIce', \n",
    "           'evenTimeOnIce', 'shortHandedTimeOnIce', 'powerPlayTimeOnIce']\n",
    "\n",
    "for column in columns:\n",
    "    combined[column] = (combined[column]/3).round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6905830a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>game_id</th>\n",
       "      <th>HoA</th>\n",
       "      <th>won</th>\n",
       "      <th>settled_in</th>\n",
       "      <th>head_coach</th>\n",
       "      <th>pim</th>\n",
       "      <th>powerPlayOpportunities</th>\n",
       "      <th>powerPlayGoals</th>\n",
       "      <th>faceOffWinPercentage</th>\n",
       "      <th>startRinkSide</th>\n",
       "      <th>Shots</th>\n",
       "      <th>Goals</th>\n",
       "      <th>Takeaways</th>\n",
       "      <th>Hits</th>\n",
       "      <th>Blocked Shots</th>\n",
       "      <th>Giveaways</th>\n",
       "      <th>Missed Shots</th>\n",
       "      <th>Penalities</th>\n",
       "      <th>#Won Faceoffs</th>\n",
       "      <th>team_id</th>\n",
       "      <th>timeOnIce</th>\n",
       "      <th>evenTimeOnIce</th>\n",
       "      <th>shortHandedTimeOnIce</th>\n",
       "      <th>powerPlayTimeOnIce</th>\n",
       "      <th>goalie_replacement</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>away</td>\n",
       "      <td>False</td>\n",
       "      <td>REG</td>\n",
       "      <td>Dave Hakstol</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1.33</td>\n",
       "      <td>0.67</td>\n",
       "      <td>50.9</td>\n",
       "      <td>left</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>330.28</td>\n",
       "      <td>280.46</td>\n",
       "      <td>18.52</td>\n",
       "      <td>31.30</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016020045</td>\n",
       "      <td>home</td>\n",
       "      <td>True</td>\n",
       "      <td>REG</td>\n",
       "      <td>Joel Quenneville</td>\n",
       "      <td>2.67</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.67</td>\n",
       "      <td>49.1</td>\n",
       "      <td>left</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>327.11</td>\n",
       "      <td>278.93</td>\n",
       "      <td>25.04</td>\n",
       "      <td>23.15</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>away</td>\n",
       "      <td>True</td>\n",
       "      <td>OT</td>\n",
       "      <td>Randy Carlyle</td>\n",
       "      <td>2.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.33</td>\n",
       "      <td>43.8</td>\n",
       "      <td>right</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>334.07</td>\n",
       "      <td>293.20</td>\n",
       "      <td>9.48</td>\n",
       "      <td>31.39</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017020812</td>\n",
       "      <td>home</td>\n",
       "      <td>False</td>\n",
       "      <td>OT</td>\n",
       "      <td>Phil Housley</td>\n",
       "      <td>2.67</td>\n",
       "      <td>0.67</td>\n",
       "      <td>0.33</td>\n",
       "      <td>56.2</td>\n",
       "      <td>right</td>\n",
       "      <td>12.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>333.07</td>\n",
       "      <td>296.11</td>\n",
       "      <td>25.11</td>\n",
       "      <td>11.85</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015020314</td>\n",
       "      <td>away</td>\n",
       "      <td>True</td>\n",
       "      <td>REG</td>\n",
       "      <td>Patrick Roy</td>\n",
       "      <td>3.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.33</td>\n",
       "      <td>45.7</td>\n",
       "      <td>left</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>328.89</td>\n",
       "      <td>281.57</td>\n",
       "      <td>17.78</td>\n",
       "      <td>29.54</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      game_id   HoA    won settled_in        head_coach   pim  \\\n",
       "0  2016020045  away  False        REG      Dave Hakstol  2.00   \n",
       "1  2016020045  home   True        REG  Joel Quenneville  2.67   \n",
       "2  2017020812  away   True         OT     Randy Carlyle  2.00   \n",
       "3  2017020812  home  False         OT      Phil Housley  2.67   \n",
       "4  2015020314  away   True        REG       Patrick Roy  3.00   \n",
       "\n",
       "   powerPlayOpportunities  powerPlayGoals  faceOffWinPercentage startRinkSide  \\\n",
       "0                    1.33            0.67                  50.9          left   \n",
       "1                    1.00            0.67                  49.1          left   \n",
       "2                    1.00            0.33                  43.8         right   \n",
       "3                    0.67            0.33                  56.2         right   \n",
       "4                    1.00            0.33                  45.7          left   \n",
       "\n",
       "   Shots  Goals  Takeaways  Hits  Blocked Shots  Giveaways  Missed Shots  \\\n",
       "0    8.0    0.0        1.0  14.0            3.0        6.0           4.0   \n",
       "1    8.0    3.0        3.0   5.0            3.0        7.0           0.0   \n",
       "2   11.0    0.0        0.0   4.0            6.0        2.0           6.0   \n",
       "3   12.0    1.0        2.0   4.0            8.0        0.0           3.0   \n",
       "4    9.0    0.0        3.0   4.0            7.0        7.0           3.0   \n",
       "\n",
       "   Penalities  #Won Faceoffs  team_id  timeOnIce  evenTimeOnIce  \\\n",
       "0         1.0           10.0      4.0     330.28         280.46   \n",
       "1         1.0           12.0     16.0     327.11         278.93   \n",
       "2         1.0           11.0     24.0     334.07         293.20   \n",
       "3         3.0            9.0      7.0     333.07         296.11   \n",
       "4         2.0            7.0     21.0     328.89         281.57   \n",
       "\n",
       "   shortHandedTimeOnIce  powerPlayTimeOnIce goalie_replacement  \n",
       "0                 18.52               31.30                Yes  \n",
       "1                 25.04               23.15                 No  \n",
       "2                  9.48               31.39                 No  \n",
       "3                 25.11               11.85                 No  \n",
       "4                 17.78               29.54                 No  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "a3ef2c97",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['game_id', 'HoA', 'won', 'settled_in', 'head_coach', 'pim',\n",
       "       'powerPlayOpportunities', 'powerPlayGoals', 'faceOffWinPercentage',\n",
       "       'startRinkSide', 'Shots', 'Goals', 'Takeaways', 'Hits', 'Blocked Shots',\n",
       "       'Giveaways', 'Missed Shots', 'Penalities', '#Won Faceoffs', 'team_id',\n",
       "       'timeOnIce', 'evenTimeOnIce', 'shortHandedTimeOnIce',\n",
       "       'powerPlayTimeOnIce', 'goalie_replacement'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined.columns"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}