collect_playerdata.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from eliteprospect import eliteprospect_scraper as ep\n",
    "import numpy as np\n",
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on module eliteprospect.eliteprospect_scraper in eliteprospect:\n",
      "\n",
      "NAME\n",
      "    eliteprospect.eliteprospect_scraper\n",
      "\n",
      "DESCRIPTION\n",
      "    Functions for collecting data from eliteprospects\n",
      "    Can be used together to extract data for players, matches etc.\n",
      "\n",
      "FUNCTIONS\n",
      "    dataprep_players(playerstats)\n",
      "        Takes data from players, clean it and return data frame that can be used for further analysis\n",
      "    \n",
      "    getPlayerMetadata(dfplayers)\n",
      "        Create dataframe with metadata by players. \n",
      "        Input is dataframe created with function getPlayers\n",
      "    \n",
      "    getPlayerStats(playerlinks)\n",
      "        Takes series of playerlinks to eliteprospect-profiles, \n",
      "        Return dataframe with stats by player and season\n",
      "    \n",
      "    getPlayers(league, year)\n",
      "        Get all players for specific year and league; returns dataframe\n",
      "        League input in format '2018-19'\n",
      "    \n",
      "    getSeasonStat(dfplayers)\n",
      "        Create dataframe with aggregated statistics by season and position (forward/defenceman) \n",
      "        Input is dataframe created with function getPlayers\n",
      "    \n",
      "    getTeamStat(dfplayers)\n",
      "        Create dataframe with aggregated statistics by team, season and position (forward/defenceman) \n",
      "        Input is dataframe created with function getPlayers\n",
      "    \n",
      "    tableDataText(table)\n",
      "        # Extract data from table - return list with rows\n",
      "\n",
      "FILE\n",
      "    c:\\users\\marcu\\anaconda3\\lib\\site-packages\\eliteprospect\\eliteprospect_scraper.py\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(ep)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>player</th>\n",
       "      <th>team</th>\n",
       "      <th>gp</th>\n",
       "      <th>g</th>\n",
       "      <th>a</th>\n",
       "      <th>tp</th>\n",
       "      <th>ppg</th>\n",
       "      <th>pim</th>\n",
       "      <th>+/-</th>\n",
       "      <th>link</th>\n",
       "      <th>season</th>\n",
       "      <th>league</th>\n",
       "      <th>playername</th>\n",
       "      <th>position</th>\n",
       "      <th>fw_def</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bud Holloway (RW)</td>\n",
       "      <td>Skellefteå AIK</td>\n",
       "      <td>55</td>\n",
       "      <td>20</td>\n",
       "      <td>51</td>\n",
       "      <td>71</td>\n",
       "      <td>1.29</td>\n",
       "      <td>36</td>\n",
       "      <td>25</td>\n",
       "      <td>https://www.eliteprospects.com/player/11620/bu...</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>shl</td>\n",
       "      <td>Bud Holloway</td>\n",
       "      <td>RW</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Carl Söderberg (C)</td>\n",
       "      <td>Linköping HC</td>\n",
       "      <td>54</td>\n",
       "      <td>31</td>\n",
       "      <td>29</td>\n",
       "      <td>60</td>\n",
       "      <td>1.11</td>\n",
       "      <td>48</td>\n",
       "      <td>18</td>\n",
       "      <td>https://www.eliteprospects.com/player/665/carl...</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>shl</td>\n",
       "      <td>Carl Söderberg</td>\n",
       "      <td>C</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Joakim Lindström (RW)</td>\n",
       "      <td>Skellefteå AIK</td>\n",
       "      <td>53</td>\n",
       "      <td>18</td>\n",
       "      <td>36</td>\n",
       "      <td>54</td>\n",
       "      <td>1.02</td>\n",
       "      <td>56</td>\n",
       "      <td>11</td>\n",
       "      <td>https://www.eliteprospects.com/player/229/joak...</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>shl</td>\n",
       "      <td>Joakim Lindström</td>\n",
       "      <td>RW</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pär Arlbrandt (RW)</td>\n",
       "      <td>Linköping HC</td>\n",
       "      <td>54</td>\n",
       "      <td>21</td>\n",
       "      <td>32</td>\n",
       "      <td>53</td>\n",
       "      <td>0.98</td>\n",
       "      <td>28</td>\n",
       "      <td>24</td>\n",
       "      <td>https://www.eliteprospects.com/player/825/par-...</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>shl</td>\n",
       "      <td>Pär Arlbrandt</td>\n",
       "      <td>RW</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Broc Little (LW)</td>\n",
       "      <td>AIK</td>\n",
       "      <td>55</td>\n",
       "      <td>16</td>\n",
       "      <td>30</td>\n",
       "      <td>46</td>\n",
       "      <td>0.84</td>\n",
       "      <td>24</td>\n",
       "      <td>-3</td>\n",
       "      <td>https://www.eliteprospects.com/player/34071/br...</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>shl</td>\n",
       "      <td>Broc Little</td>\n",
       "      <td>LW</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  player            team  gp   g   a  tp   ppg pim +/-  \\\n",
       "0      Bud Holloway (RW)  Skellefteå AIK  55  20  51  71  1.29  36  25   \n",
       "1     Carl Söderberg (C)    Linköping HC  54  31  29  60  1.11  48  18   \n",
       "2  Joakim Lindström (RW)  Skellefteå AIK  53  18  36  54  1.02  56  11   \n",
       "3     Pär Arlbrandt (RW)    Linköping HC  54  21  32  53  0.98  28  24   \n",
       "4       Broc Little (LW)             AIK  55  16  30  46  0.84  24  -3   \n",
       "\n",
       "                                                link   season league  \\\n",
       "0  https://www.eliteprospects.com/player/11620/bu...  2012-13    shl   \n",
       "1  https://www.eliteprospects.com/player/665/carl...  2012-13    shl   \n",
       "2  https://www.eliteprospects.com/player/229/joak...  2012-13    shl   \n",
       "3  https://www.eliteprospects.com/player/825/par-...  2012-13    shl   \n",
       "4  https://www.eliteprospects.com/player/34071/br...  2012-13    shl   \n",
       "\n",
       "          playername position fw_def  \n",
       "0      Bud Holloway        RW     FW  \n",
       "1    Carl Söderberg         C     FW  \n",
       "2  Joakim Lindström        RW     FW  \n",
       "3     Pär Arlbrandt        RW     FW  \n",
       "4       Broc Little        LW     FW  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get players SHL seasons\n",
    "shl_2012 = ep.getPlayers('shl', '2012-13')\n",
    "shl_2013 = ep.getPlayers('shl', '2013-14')\n",
    "shl_2014 = ep.getPlayers('shl', '2014-15')\n",
    "shl_2015 = ep.getPlayers('shl', '2015-16')\n",
    "shl_2016 = ep.getPlayers('shl', '2016-17')\n",
    "shl_2017 = ep.getPlayers('shl', '2017-18')\n",
    "shl_2018 = ep.getPlayers('shl', '2018-19')\n",
    "shl_2019 = ep.getPlayers('shl', '2019-20')\n",
    "\n",
    "players_shl = pd.concat([shl_2012,shl_2013,shl_2014,shl_2015,shl_2016,shl_2017,shl_2018,shl_2019])\n",
    "\n",
    "players_shl.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>player</th>\n",
       "      <th>team</th>\n",
       "      <th>gp</th>\n",
       "      <th>g</th>\n",
       "      <th>a</th>\n",
       "      <th>tp</th>\n",
       "      <th>ppg</th>\n",
       "      <th>pim</th>\n",
       "      <th>+/-</th>\n",
       "      <th>link</th>\n",
       "      <th>season</th>\n",
       "      <th>league</th>\n",
       "      <th>playername</th>\n",
       "      <th>position</th>\n",
       "      <th>fw_def</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Evan McGrath (C)</td>\n",
       "      <td>IK Oskarshamn</td>\n",
       "      <td>52</td>\n",
       "      <td>22</td>\n",
       "      <td>28</td>\n",
       "      <td>50</td>\n",
       "      <td>0.96</td>\n",
       "      <td>38</td>\n",
       "      <td>6</td>\n",
       "      <td>https://www.eliteprospects.com/player/9491/eva...</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>allsvenskan</td>\n",
       "      <td>Evan McGrath</td>\n",
       "      <td>C</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Jared Aulin (C/RW)</td>\n",
       "      <td>Örebro HK</td>\n",
       "      <td>48</td>\n",
       "      <td>16</td>\n",
       "      <td>34</td>\n",
       "      <td>50</td>\n",
       "      <td>1.04</td>\n",
       "      <td>34</td>\n",
       "      <td>8</td>\n",
       "      <td>https://www.eliteprospects.com/player/9521/jar...</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>allsvenskan</td>\n",
       "      <td>Jared Aulin</td>\n",
       "      <td>C/RW</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Matt Fornataro (C)</td>\n",
       "      <td>VIK Västerås HK</td>\n",
       "      <td>48</td>\n",
       "      <td>16</td>\n",
       "      <td>32</td>\n",
       "      <td>48</td>\n",
       "      <td>1.00</td>\n",
       "      <td>40</td>\n",
       "      <td>8</td>\n",
       "      <td>https://www.eliteprospects.com/player/15568/ma...</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>allsvenskan</td>\n",
       "      <td>Matt Fornataro</td>\n",
       "      <td>C</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Damien Fleury (RW/LW)</td>\n",
       "      <td>Södertälje SK</td>\n",
       "      <td>51</td>\n",
       "      <td>29</td>\n",
       "      <td>18</td>\n",
       "      <td>47</td>\n",
       "      <td>0.92</td>\n",
       "      <td>50</td>\n",
       "      <td>19</td>\n",
       "      <td>https://www.eliteprospects.com/player/24848/da...</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>allsvenskan</td>\n",
       "      <td>Damien Fleury</td>\n",
       "      <td>RW/LW</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Michael Raffl (LW/RW)</td>\n",
       "      <td>Leksands IF</td>\n",
       "      <td>49</td>\n",
       "      <td>24</td>\n",
       "      <td>22</td>\n",
       "      <td>46</td>\n",
       "      <td>0.94</td>\n",
       "      <td>40</td>\n",
       "      <td>24</td>\n",
       "      <td>https://www.eliteprospects.com/player/15216/mi...</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>allsvenskan</td>\n",
       "      <td>Michael Raffl</td>\n",
       "      <td>LW/RW</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  player             team  gp   g   a  tp   ppg pim +/-  \\\n",
       "0       Evan McGrath (C)    IK Oskarshamn  52  22  28  50  0.96  38   6   \n",
       "1     Jared Aulin (C/RW)        Örebro HK  48  16  34  50  1.04  34   8   \n",
       "2     Matt Fornataro (C)  VIK Västerås HK  48  16  32  48  1.00  40   8   \n",
       "3  Damien Fleury (RW/LW)    Södertälje SK  51  29  18  47  0.92  50  19   \n",
       "4  Michael Raffl (LW/RW)      Leksands IF  49  24  22  46  0.94  40  24   \n",
       "\n",
       "                                                link   season       league  \\\n",
       "0  https://www.eliteprospects.com/player/9491/eva...  2012-13  allsvenskan   \n",
       "1  https://www.eliteprospects.com/player/9521/jar...  2012-13  allsvenskan   \n",
       "2  https://www.eliteprospects.com/player/15568/ma...  2012-13  allsvenskan   \n",
       "3  https://www.eliteprospects.com/player/24848/da...  2012-13  allsvenskan   \n",
       "4  https://www.eliteprospects.com/player/15216/mi...  2012-13  allsvenskan   \n",
       "\n",
       "        playername position fw_def  \n",
       "0    Evan McGrath         C     FW  \n",
       "1     Jared Aulin      C/RW     FW  \n",
       "2  Matt Fornataro         C     FW  \n",
       "3   Damien Fleury     RW/LW     FW  \n",
       "4   Michael Raffl     LW/RW     FW  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get players allsvenskan seasons\n",
    "allsvenskan_2012 = ep.getPlayers('allsvenskan', '2012-13')\n",
    "allsvenskan_2013 = ep.getPlayers('allsvenskan', '2013-14')\n",
    "allsvenskan_2014 = ep.getPlayers('allsvenskan', '2014-15')\n",
    "allsvenskan_2015 = ep.getPlayers('allsvenskan', '2015-16')\n",
    "allsvenskan_2016 = ep.getPlayers('allsvenskan', '2016-17')\n",
    "allsvenskan_2017 = ep.getPlayers('allsvenskan', '2017-18')\n",
    "allsvenskan_2018 = ep.getPlayers('allsvenskan', '2018-19')\n",
    "allsvenskan_2019 = ep.getPlayers('allsvenskan', '2019-20')\n",
    "\n",
    "players_allsvenskan = pd.concat([allsvenskan_2012,allsvenskan_2013,allsvenskan_2014,\n",
    "                         allsvenskan_2015,allsvenskan_2016,allsvenskan_2017,\n",
    "                         allsvenskan_2018,allsvenskan_2019])\n",
    "\n",
    "players_allsvenskan.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>team</th>\n",
       "      <th>season</th>\n",
       "      <th>fw_def</th>\n",
       "      <th>gp</th>\n",
       "      <th>g</th>\n",
       "      <th>a</th>\n",
       "      <th>tp</th>\n",
       "      <th>pim</th>\n",
       "      <th>avg_g_team</th>\n",
       "      <th>avg_a_team</th>\n",
       "      <th>avg_tp_team</th>\n",
       "      <th>avg_pim_team</th>\n",
       "      <th>avg_+/-_team</th>\n",
       "      <th>nbr_players_team</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AIK</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>DEF</td>\n",
       "      <td>383</td>\n",
       "      <td>19</td>\n",
       "      <td>58</td>\n",
       "      <td>77</td>\n",
       "      <td>126</td>\n",
       "      <td>0.049608</td>\n",
       "      <td>0.151436</td>\n",
       "      <td>0.201044</td>\n",
       "      <td>0.328982</td>\n",
       "      <td>-6.363636</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AIK</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>FW</td>\n",
       "      <td>681</td>\n",
       "      <td>104</td>\n",
       "      <td>135</td>\n",
       "      <td>239</td>\n",
       "      <td>290</td>\n",
       "      <td>0.152717</td>\n",
       "      <td>0.198238</td>\n",
       "      <td>0.350954</td>\n",
       "      <td>0.425844</td>\n",
       "      <td>-5.190476</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AIK</td>\n",
       "      <td>2013-14</td>\n",
       "      <td>DEF</td>\n",
       "      <td>403</td>\n",
       "      <td>21</td>\n",
       "      <td>54</td>\n",
       "      <td>75</td>\n",
       "      <td>229</td>\n",
       "      <td>0.052109</td>\n",
       "      <td>0.133995</td>\n",
       "      <td>0.186104</td>\n",
       "      <td>0.568238</td>\n",
       "      <td>-5.000000</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AIK</td>\n",
       "      <td>2013-14</td>\n",
       "      <td>FW</td>\n",
       "      <td>679</td>\n",
       "      <td>103</td>\n",
       "      <td>126</td>\n",
       "      <td>229</td>\n",
       "      <td>368</td>\n",
       "      <td>0.151694</td>\n",
       "      <td>0.185567</td>\n",
       "      <td>0.337261</td>\n",
       "      <td>0.541973</td>\n",
       "      <td>-5.869565</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AIK</td>\n",
       "      <td>2014-15</td>\n",
       "      <td>DEF</td>\n",
       "      <td>327</td>\n",
       "      <td>17</td>\n",
       "      <td>60</td>\n",
       "      <td>77</td>\n",
       "      <td>237</td>\n",
       "      <td>0.051988</td>\n",
       "      <td>0.183486</td>\n",
       "      <td>0.235474</td>\n",
       "      <td>0.724771</td>\n",
       "      <td>-2.916667</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  team   season fw_def   gp    g    a   tp  pim  avg_g_team  avg_a_team  \\\n",
       "0  AIK  2012-13    DEF  383   19   58   77  126    0.049608    0.151436   \n",
       "1  AIK  2012-13     FW  681  104  135  239  290    0.152717    0.198238   \n",
       "2  AIK  2013-14    DEF  403   21   54   75  229    0.052109    0.133995   \n",
       "3  AIK  2013-14     FW  679  103  126  229  368    0.151694    0.185567   \n",
       "4  AIK  2014-15    DEF  327   17   60   77  237    0.051988    0.183486   \n",
       "\n",
       "   avg_tp_team  avg_pim_team  avg_+/-_team  nbr_players_team  \n",
       "0     0.201044      0.328982     -6.363636                11  \n",
       "1     0.350954      0.425844     -5.190476                21  \n",
       "2     0.186104      0.568238     -5.000000                15  \n",
       "3     0.337261      0.541973     -5.869565                23  \n",
       "4     0.235474      0.724771     -2.916667                12  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Use the output from getPlayers as input to get teamstatistics\n",
    "teamstat = ep.getTeamStat(pd.concat([players_shl, players_allsvenskan]))\n",
    "teamstat.rename(columns={ \"nbr_players\":\"nbr_players_team\"}, inplace=True)\n",
    "\n",
    "\n",
    "teamstat.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>season</th>\n",
       "      <th>fw_def</th>\n",
       "      <th>gp</th>\n",
       "      <th>g</th>\n",
       "      <th>a</th>\n",
       "      <th>tp</th>\n",
       "      <th>pim</th>\n",
       "      <th>avg_g_season</th>\n",
       "      <th>avg_a_season</th>\n",
       "      <th>avg_tp_season</th>\n",
       "      <th>avg_pim_season</th>\n",
       "      <th>avg_+/-_season</th>\n",
       "      <th>nbr_players_season</th>\n",
       "      <th>league</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-13</td>\n",
       "      <td>DEF</td>\n",
       "      <td>4636</td>\n",
       "      <td>289</td>\n",
       "      <td>802</td>\n",
       "      <td>1091</td>\n",
       "      <td>2479</td>\n",
       "      <td>0.062338</td>\n",
       "      <td>0.172994</td>\n",
       "      <td>0.235332</td>\n",
       "      <td>0.534728</td>\n",
       "      <td>0.136691</td>\n",
       "      <td>139</td>\n",
       "      <td>SHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012-13</td>\n",
       "      <td>FW</td>\n",
       "      <td>8325</td>\n",
       "      <td>1291</td>\n",
       "      <td>1793</td>\n",
       "      <td>3084</td>\n",
       "      <td>4125</td>\n",
       "      <td>0.155075</td>\n",
       "      <td>0.215375</td>\n",
       "      <td>0.370450</td>\n",
       "      <td>0.495495</td>\n",
       "      <td>-0.303419</td>\n",
       "      <td>234</td>\n",
       "      <td>SHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-14</td>\n",
       "      <td>DEF</td>\n",
       "      <td>4712</td>\n",
       "      <td>286</td>\n",
       "      <td>770</td>\n",
       "      <td>1056</td>\n",
       "      <td>2643</td>\n",
       "      <td>0.060696</td>\n",
       "      <td>0.163413</td>\n",
       "      <td>0.224109</td>\n",
       "      <td>0.560908</td>\n",
       "      <td>0.191489</td>\n",
       "      <td>141</td>\n",
       "      <td>SHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2013-14</td>\n",
       "      <td>FW</td>\n",
       "      <td>8380</td>\n",
       "      <td>1456</td>\n",
       "      <td>1851</td>\n",
       "      <td>3307</td>\n",
       "      <td>4659</td>\n",
       "      <td>0.173747</td>\n",
       "      <td>0.220883</td>\n",
       "      <td>0.394630</td>\n",
       "      <td>0.555967</td>\n",
       "      <td>-0.500000</td>\n",
       "      <td>246</td>\n",
       "      <td>SHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-15</td>\n",
       "      <td>DEF</td>\n",
       "      <td>4644</td>\n",
       "      <td>256</td>\n",
       "      <td>839</td>\n",
       "      <td>1095</td>\n",
       "      <td>2536</td>\n",
       "      <td>0.055125</td>\n",
       "      <td>0.180663</td>\n",
       "      <td>0.235788</td>\n",
       "      <td>0.546081</td>\n",
       "      <td>0.079137</td>\n",
       "      <td>139</td>\n",
       "      <td>SHL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    season fw_def    gp     g     a    tp   pim  avg_g_season  avg_a_season  \\\n",
       "0  2012-13    DEF  4636   289   802  1091  2479      0.062338      0.172994   \n",
       "1  2012-13     FW  8325  1291  1793  3084  4125      0.155075      0.215375   \n",
       "2  2013-14    DEF  4712   286   770  1056  2643      0.060696      0.163413   \n",
       "3  2013-14     FW  8380  1456  1851  3307  4659      0.173747      0.220883   \n",
       "4  2014-15    DEF  4644   256   839  1095  2536      0.055125      0.180663   \n",
       "\n",
       "   avg_tp_season  avg_pim_season  avg_+/-_season  nbr_players_season league  \n",
       "0       0.235332        0.534728        0.136691                 139    SHL  \n",
       "1       0.370450        0.495495       -0.303419                 234    SHL  \n",
       "2       0.224109        0.560908        0.191489                 141    SHL  \n",
       "3       0.394630        0.555967       -0.500000                 246    SHL  \n",
       "4       0.235788        0.546081        0.079137                 139    SHL  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get seasonstats for leagues\n",
    "leaguestat_shl = ep.getSeasonStat(players_shl)\n",
    "leaguestat_shl['league'] = 'SHL'\n",
    "leaguestat_allsvenskan = ep.getSeasonStat(players_allsvenskan)\n",
    "leaguestat_allsvenskan['league'] = 'allsvenskan'\n",
    "\n",
    "\n",
    "\n",
    "leaguestat_allsvenskan.rename(columns={ \"avg_+/-_team\":\"avg_+/-_season\"}, inplace=True)\n",
    "leaguestat_shl.rename(columns={ \"avg_+/-_team\":\"avg_+/-_season\"}, inplace=True)\n",
    "\n",
    "leaguestat_allsvenskan.rename(columns={ \"nbr_players\":\"nbr_players_season\"}, inplace=True)\n",
    "leaguestat_shl.rename(columns={ \"nbr_players\":\"nbr_players_season\"}, inplace=True)\n",
    "\n",
    "# Merge \n",
    "\n",
    "leaguestat = leaguestat_shl.append(pd.DataFrame(data = leaguestat_allsvenskan), ignore_index=True)\n",
    "leaguestat.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>link</th>\n",
       "      <th>playername</th>\n",
       "      <th>fw_def</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1163</th>\n",
       "      <td>https://www.eliteprospects.com/player/99865/au...</td>\n",
       "      <td>Austin Farley</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1906</th>\n",
       "      <td>https://www.eliteprospects.com/player/99862/er...</td>\n",
       "      <td>Erik Flood</td>\n",
       "      <td>DEF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>551</th>\n",
       "      <td>https://www.eliteprospects.com/player/99859/fr...</td>\n",
       "      <td>Fredrik Forsberg</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>https://www.eliteprospects.com/player/9985/den...</td>\n",
       "      <td>Dennis Rasmussen</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>https://www.eliteprospects.com/player/9938/mik...</td>\n",
       "      <td>Mikael Eriksson</td>\n",
       "      <td>FW</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   link         playername  \\\n",
       "1163  https://www.eliteprospects.com/player/99865/au...     Austin Farley    \n",
       "1906  https://www.eliteprospects.com/player/99862/er...        Erik Flood    \n",
       "551   https://www.eliteprospects.com/player/99859/fr...  Fredrik Forsberg    \n",
       "33    https://www.eliteprospects.com/player/9985/den...  Dennis Rasmussen    \n",
       "278   https://www.eliteprospects.com/player/9938/mik...   Mikael Eriksson    \n",
       "\n",
       "     fw_def  \n",
       "1163     FW  \n",
       "1906    DEF  \n",
       "551      FW  \n",
       "33       FW  \n",
       "278      FW  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create metadata from players dataframe\n",
    "playersmeta = ep.getPlayerMetadata(pd.concat([players_shl, players_allsvenskan]))\n",
    "playersmeta.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Get the links players and send to function getPLayerStats\n",
    "playerlinks = playersmeta['link']\n",
    "# playerstats=ep.getPlayerStats(playerlinks)\n",
    "# playerstats.to_csv(\"C:/Users/marcu/Documents/projects/icehockey/data/playerstats.csv\")\n",
    "\n",
    "playerstats = pd.read_csv(\"C:/Users/marcu/Documents/projects/icehockey/data/playerstats.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>season</th>\n",
       "      <th>Team</th>\n",
       "      <th>League</th>\n",
       "      <th>GP</th>\n",
       "      <th>G</th>\n",
       "      <th>A</th>\n",
       "      <th>TP</th>\n",
       "      <th>PIM</th>\n",
       "      <th>+/-</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>POST</th>\n",
       "      <th>GP.1</th>\n",
       "      <th>G.1</th>\n",
       "      <th>A.1</th>\n",
       "      <th>TP.1</th>\n",
       "      <th>PIM.1</th>\n",
       "      <th>+/-.1</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2006-07</td>\n",
       "      <td>Team Illinois Bantam Minor AAA</td>\n",
       "      <td>T1EMBHL</td>\n",
       "      <td>31</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "      <td>36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>|</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.eliteprospects.com/player/99865/au...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007-08</td>\n",
       "      <td>CYA Bantam Major AAA</td>\n",
       "      <td>T1EBHL</td>\n",
       "      <td>28</td>\n",
       "      <td>21</td>\n",
       "      <td>8</td>\n",
       "      <td>29</td>\n",
       "      <td>176</td>\n",
       "      <td>NaN</td>\n",
       "      <td>|</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.eliteprospects.com/player/99865/au...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2008-09</td>\n",
       "      <td>Team Illinois U16</td>\n",
       "      <td>T1EHL U16</td>\n",
       "      <td>29</td>\n",
       "      <td>7</td>\n",
       "      <td>18</td>\n",
       "      <td>25</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>|</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.eliteprospects.com/player/99865/au...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-10</td>\n",
       "      <td>Chicago Mission U16</td>\n",
       "      <td>T1EHL U16</td>\n",
       "      <td>38</td>\n",
       "      <td>19</td>\n",
       "      <td>19</td>\n",
       "      <td>38</td>\n",
       "      <td>44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>|</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.eliteprospects.com/player/99865/au...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010-11</td>\n",
       "      <td>Chicago Mission U18</td>\n",
       "      <td>T1EHL U18</td>\n",
       "      <td>16</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>17</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>|</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.eliteprospects.com/player/99865/au...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    season                            Team     League  GP   G   A  TP  PIM  \\\n",
       "0  2006-07  Team Illinois Bantam Minor AAA    T1EMBHL  31   5  12  17   36   \n",
       "1  2007-08            CYA Bantam Major AAA     T1EBHL  28  21   8  29  176   \n",
       "2  2008-09               Team Illinois U16  T1EHL U16  29   7  18  25    4   \n",
       "3  2009-10             Chicago Mission U16  T1EHL U16  38  19  19  38   44   \n",
       "4  2010-11             Chicago Mission U18  T1EHL U18  16  10   7  17   35   \n",
       "\n",
       "   +/- Unnamed: 9 POST GP.1  G.1  A.1 TP.1 PIM.1 +/-.1  \\\n",
       "0  NaN          |  NaN  NaN  NaN  NaN  NaN   NaN   NaN   \n",
       "1  NaN          |  NaN  NaN  NaN  NaN  NaN   NaN   NaN   \n",
       "2  NaN          |  NaN  NaN  NaN  NaN  NaN   NaN   NaN   \n",
       "3  NaN          |  NaN  NaN  NaN  NaN  NaN   NaN   NaN   \n",
       "4  NaN          |  NaN  NaN  NaN  NaN  NaN   NaN   NaN   \n",
       "\n",
       "                                                link  \n",
       "0  https://www.eliteprospects.com/player/99865/au...  \n",
       "1  https://www.eliteprospects.com/player/99865/au...  \n",
       "2  https://www.eliteprospects.com/player/99865/au...  \n",
       "3  https://www.eliteprospects.com/player/99865/au...  \n",
       "4  https://www.eliteprospects.com/player/99865/au...  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "playerstats.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clean output from playerstats\n",
    "#df_players_clean = ep.dataprep_players(playerstats)\n",
    "\n",
    "df_players_clean = ep.dataprep_players(playerstats)\n",
    "\n",
    "\n",
    "df_players_clean['team'] =  df_players_clean['team'].str.replace('“A”','',case=True)\n",
    "df_players_clean['team'] =  df_players_clean['team'].str.replace('“C”','',case=True)\n",
    "\n",
    "df_players_clean['league'] = df_players_clean['league'].str.lower()\n",
    "leaguestat['league'] = leaguestat['league'].str.lower()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>link</th>\n",
       "      <th>season</th>\n",
       "      <th>team</th>\n",
       "      <th>league</th>\n",
       "      <th>gp</th>\n",
       "      <th>g</th>\n",
       "      <th>a</th>\n",
       "      <th>tp</th>\n",
       "      <th>pim</th>\n",
       "      <th>+/-</th>\n",
       "      <th>primary_team</th>\n",
       "      <th>league_seasons</th>\n",
       "      <th>team_seasons</th>\n",
       "      <th>avg_g</th>\n",
       "      <th>avg_a</th>\n",
       "      <th>avg_tp</th>\n",
       "      <th>avg_pim</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.eliteprospects.com/player/10014/me...</td>\n",
       "      <td>2005-06</td>\n",
       "      <td>Lycksele SK</td>\n",
       "      <td>division 2</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.111111</td>\n",
       "      <td>0.222222</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.eliteprospects.com/player/10014/me...</td>\n",
       "      <td>2006-07</td>\n",
       "      <td>Skellefteå AIK J18</td>\n",
       "      <td>j18 allsvenskan</td>\n",
       "      <td>14.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.357143</td>\n",
       "      <td>0.285714</td>\n",
       "      <td>0.642857</td>\n",
       "      <td>1.428571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.eliteprospects.com/player/10014/me...</td>\n",
       "      <td>2007-08</td>\n",
       "      <td>Skellefteå AIK J18</td>\n",
       "      <td>j18 elit</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.625000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>1.125000</td>\n",
       "      <td>0.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.eliteprospects.com/player/10014/me...</td>\n",
       "      <td>2008-09</td>\n",
       "      <td>Skellefteå AIK J20</td>\n",
       "      <td>superelit</td>\n",
       "      <td>34.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.294118</td>\n",
       "      <td>0.411765</td>\n",
       "      <td>0.705882</td>\n",
       "      <td>1.705882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.eliteprospects.com/player/10014/me...</td>\n",
       "      <td>2009-10</td>\n",
       "      <td>Skellefteå AIK</td>\n",
       "      <td>shl</td>\n",
       "      <td>36.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.055556</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.055556</td>\n",
       "      <td>0.222222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                link   season  \\\n",
       "0  https://www.eliteprospects.com/player/10014/me...  2005-06   \n",
       "1  https://www.eliteprospects.com/player/10014/me...  2006-07   \n",
       "2  https://www.eliteprospects.com/player/10014/me...  2007-08   \n",
       "3  https://www.eliteprospects.com/player/10014/me...  2008-09   \n",
       "4  https://www.eliteprospects.com/player/10014/me...  2009-10   \n",
       "\n",
       "                 team           league    gp     g     a    tp   pim  +/-  \\\n",
       "0         Lycksele SK       division 2   9.0   1.0   2.0   3.0   0.0  NaN   \n",
       "1  Skellefteå AIK J18  j18 allsvenskan  14.0   5.0   4.0   9.0  20.0  NaN   \n",
       "2  Skellefteå AIK J18         j18 elit  16.0  10.0   8.0  18.0   4.0  NaN   \n",
       "3  Skellefteå AIK J20        superelit  34.0  10.0  14.0  24.0  58.0  NaN   \n",
       "4      Skellefteå AIK              shl  36.0   2.0   0.0   2.0   8.0 -2.0   \n",
       "\n",
       "  primary_team  league_seasons  team_seasons     avg_g     avg_a    avg_tp  \\\n",
       "0         True               1             1  0.111111  0.222222  0.333333   \n",
       "1         True               1             1  0.357143  0.285714  0.642857   \n",
       "2         True               1             2  0.625000  0.500000  1.125000   \n",
       "3         True               1             1  0.294118  0.411765  0.705882   \n",
       "4         True               1             1  0.055556  0.000000  0.055556   \n",
       "\n",
       "    avg_pim  \n",
       "0  0.000000  \n",
       "1  1.428571  \n",
       "2  0.250000  \n",
       "3  1.705882  \n",
       "4  0.222222  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_players_clean.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nbr players 30992\n",
      "Nbr players 30992\n",
      "Nbr players 30992\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print('Nbr players', len(df_players_clean))\n",
    "\n",
    "# Add metadata\n",
    "df_players = pd.merge(df_players_clean, playersmeta, \n",
    "                     how='left')\n",
    "\n",
    "print('Nbr players', len(df_players))\n",
    "\n",
    "# Add seasondata\n",
    "df_players = pd.merge(df_players, leaguestat[['league', 'season', 'fw_def',\n",
    "                                              'avg_g_season', 'avg_a_season', 'avg_tp_season', \n",
    "                                              'avg_pim_season', 'avg_+/-_season',\n",
    "                                              'nbr_players_season']],\n",
    "                     how='left',\n",
    "                     on = ['league', 'fw_def', 'season'])\n",
    "\n",
    "# Add teamdata\n",
    "df_players = pd.merge(df_players, teamstat[['team', 'season', 'fw_def',\n",
    "                                              'avg_g_team', 'avg_a_team', 'avg_tp_team', \n",
    "                                              'avg_pim_team', 'avg_+/-_team',\n",
    "                                              'nbr_players_team']],\n",
    "                     how='left',\n",
    "                     on = ['team', 'fw_def', 'season'])\n",
    "\n",
    "\n",
    "print('Nbr players', len(df_players))\n",
    "\n",
    "# Calculate difference to averages\n",
    "\n",
    "df_players['avg_g_vs_team'] = (df_players['avg_g']-df_players['avg_g_team']) / df_players['avg_g_team']\n",
    "df_players['avg_a_vs_team'] = (df_players['avg_a']-df_players['avg_a_team']) / df_players['avg_a_team']\n",
    "df_players['avg_tp_vs_team'] = (df_players['avg_tp']-df_players['avg_tp_team']) / df_players['avg_tp_team']\n",
    "\n",
    "\n",
    "df_players['avg_g_vs_season'] = (df_players['avg_g']-df_players['avg_g_season']) / df_players['avg_g_season']\n",
    "df_players['avg_a_vs_season'] = (df_players['avg_a']-df_players['avg_a_season']) / df_players['avg_a_season']\n",
    "df_players['avg_tp_vs_season'] = (df_players['avg_tp']-df_players['avg_tp_season']) / df_players['avg_tp_season']\n",
    "\n",
    "df_players['avg_g_team_vs_season'] = (df_players['avg_g_team']-df_players['avg_g_season']) / df_players['avg_g_season']\n",
    "df_players['avg_a_team_vs_season'] = (df_players['avg_a_team']-df_players['avg_a_season']) / df_players['avg_a_season']\n",
    "df_players['avg_tp_team_vs_season'] = (df_players['avg_tp_team']-df_players['avg_tp_season']) / df_players['avg_tp_season']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>link</th>\n",
       "      <th>season</th>\n",
       "      <th>team</th>\n",
       "      <th>league</th>\n",
       "      <th>gp</th>\n",
       "      <th>g</th>\n",
       "      <th>a</th>\n",
       "      <th>tp</th>\n",
       "      <th>pim</th>\n",
       "      <th>+/-</th>\n",
       "      <th>...</th>\n",
       "      <th>avg_g_vs_team_prev</th>\n",
       "      <th>avg_a_vs_team_prev</th>\n",
       "      <th>avg_tp_vs_team_prev</th>\n",
       "      <th>avg_g_vs_season_prev</th>\n",
       "      <th>avg_a_vs_season_prev</th>\n",
       "      <th>avg_tp_vs_season_prev</th>\n",
       "      <th>avg_g_team_vs_season_prev</th>\n",
       "      <th>avg_a_team_vs_season_prev</th>\n",
       "      <th>avg_tp_team_vs_season_prev</th>\n",
       "      <th>league_prev</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.eliteprospects.com/player/10014/me...</td>\n",
       "      <td>2005-06</td>\n",
       "      <td>Lycksele SK</td>\n",
       "      <td>division 2</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.eliteprospects.com/player/10014/me...</td>\n",
       "      <td>2006-07</td>\n",
       "      <td>Skellefteå AIK J18</td>\n",
       "      <td>j18 allsvenskan</td>\n",
       "      <td>14.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>division 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.eliteprospects.com/player/10014/me...</td>\n",
       "      <td>2007-08</td>\n",
       "      <td>Skellefteå AIK J18</td>\n",
       "      <td>j18 elit</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>j18 allsvenskan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.eliteprospects.com/player/10014/me...</td>\n",
       "      <td>2008-09</td>\n",
       "      <td>Skellefteå AIK J20</td>\n",
       "      <td>superelit</td>\n",
       "      <td>34.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>j18 elit</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.eliteprospects.com/player/10014/me...</td>\n",
       "      <td>2009-10</td>\n",
       "      <td>Skellefteå AIK</td>\n",
       "      <td>shl</td>\n",
       "      <td>36.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>superelit</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                link   season  \\\n",
       "0  https://www.eliteprospects.com/player/10014/me...  2005-06   \n",
       "1  https://www.eliteprospects.com/player/10014/me...  2006-07   \n",
       "2  https://www.eliteprospects.com/player/10014/me...  2007-08   \n",
       "3  https://www.eliteprospects.com/player/10014/me...  2008-09   \n",
       "4  https://www.eliteprospects.com/player/10014/me...  2009-10   \n",
       "\n",
       "                 team           league    gp     g     a    tp   pim  +/-  \\\n",
       "0         Lycksele SK       division 2   9.0   1.0   2.0   3.0   0.0  NaN   \n",
       "1  Skellefteå AIK J18  j18 allsvenskan  14.0   5.0   4.0   9.0  20.0  NaN   \n",
       "2  Skellefteå AIK J18         j18 elit  16.0  10.0   8.0  18.0   4.0  NaN   \n",
       "3  Skellefteå AIK J20        superelit  34.0  10.0  14.0  24.0  58.0  NaN   \n",
       "4      Skellefteå AIK              shl  36.0   2.0   0.0   2.0   8.0 -2.0   \n",
       "\n",
       "   ... avg_g_vs_team_prev  avg_a_vs_team_prev  avg_tp_vs_team_prev  \\\n",
       "0  ...                NaN                 NaN                  NaN   \n",
       "1  ...                NaN                 NaN                  NaN   \n",
       "2  ...                NaN                 NaN                  NaN   \n",
       "3  ...                NaN                 NaN                  NaN   \n",
       "4  ...                NaN                 NaN                  NaN   \n",
       "\n",
       "   avg_g_vs_season_prev  avg_a_vs_season_prev  avg_tp_vs_season_prev  \\\n",
       "0                   NaN                   NaN                    NaN   \n",
       "1                   NaN                   NaN                    NaN   \n",
       "2                   NaN                   NaN                    NaN   \n",
       "3                   NaN                   NaN                    NaN   \n",
       "4                   NaN                   NaN                    NaN   \n",
       "\n",
       "   avg_g_team_vs_season_prev avg_a_team_vs_season_prev  \\\n",
       "0                        NaN                       NaN   \n",
       "1                        NaN                       NaN   \n",
       "2                        NaN                       NaN   \n",
       "3                        NaN                       NaN   \n",
       "4                        NaN                       NaN   \n",
       "\n",
       "  avg_tp_team_vs_season_prev      league_prev  \n",
       "0                        NaN              NaN  \n",
       "1                        NaN       division 2  \n",
       "2                        NaN  j18 allsvenskan  \n",
       "3                        NaN         j18 elit  \n",
       "4                        NaN        superelit  \n",
       "\n",
       "[5 rows x 54 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Shift rows to get previous season (used for analysis)\n",
    "\n",
    "df_players['avg_g_prev'] = df_players.groupby(['link'])['avg_g'].shift(1)\n",
    "df_players['avg_a_prev'] = df_players.groupby(['link'])['avg_a'].shift(1)\n",
    "df_players['avg_tp_prev'] = df_players.groupby(['link'])['avg_tp'].shift(1)\n",
    "df_players['+/-_prev'] = df_players.groupby(['link'])['+/-'].shift(1)\n",
    "\n",
    "df_players['avg_g_vs_team_prev'] = df_players.groupby(['link'])['avg_g_vs_team'].shift(1)\n",
    "df_players['avg_a_vs_team_prev'] = df_players.groupby(['link'])['avg_a_vs_team'].shift(1)\n",
    "df_players['avg_tp_vs_team_prev'] = df_players.groupby(['link'])['avg_tp_vs_team'].shift(1)\n",
    "\n",
    "df_players['avg_g_vs_season_prev'] = df_players.groupby(['link'])['avg_g_vs_season'].shift(1)\n",
    "df_players['avg_a_vs_season_prev'] = df_players.groupby(['link'])['avg_a_vs_season'].shift(1)\n",
    "df_players['avg_tp_vs_season_prev'] = df_players.groupby(['link'])['avg_tp_vs_season'].shift(1)\n",
    "\n",
    "df_players['avg_g_team_vs_season_prev'] = df_players.groupby(['link'])['avg_g_team_vs_season'].shift(1)\n",
    "df_players['avg_a_team_vs_season_prev'] = df_players.groupby(['link'])['avg_a_team_vs_season'].shift(1)\n",
    "df_players['avg_tp_team_vs_season_prev'] = df_players.groupby(['link'])['avg_tp_team_vs_season'].shift(1)\n",
    "\n",
    "df_players['league_prev'] = df_players.groupby(['link'])['league'].shift(1)\n",
    "\n",
    "\n",
    "df_players.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_players.to_csv(\"C:/Users/marcu/Documents/projects/icehockey/data/df_players.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}