icehockeygames_update_2020.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Prepare icehockey data from swehockey"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import datetime\n",
    "\n",
    "import swehockey.swehockey_scraper as swe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make copy of old data\n",
    "filename = datetime.datetime.now().strftime(\"%Y-%m-%d-%H-%M.csv\")\n",
    "\n",
    "df_games_old = pd.read_csv(\"C:/Users/marcu/Documents/projects/icehockey_analytics/data/df_games.csv\", error_bad_lines=False)\n",
    "df_games_old.to_csv('C:/Users/marcu/Documents/projects/icehockey_analytics/data/00_backup/df_games_' + filename)\n",
    "\n",
    "\n",
    "df_teams_old = pd.read_csv(\"C:/Users/marcu/Documents/projects/icehockey_analytics/data/df_teams.csv\", error_bad_lines=False)\n",
    "df_teams_old.to_csv('C:/Users/marcu/Documents/projects/icehockey_analytics/data/00_backup/df_teams_' + filename)\n",
    "\n",
    "# Predictions\n",
    "df_2020_prediction_old = pd.read_csv(\"C:/Users/marcu/Documents/projects/icehockey_analytics/data/df_2020_prediction.csv\", error_bad_lines=False)\n",
    "df_2020_prediction_old.to_csv('C:/Users/marcu/Documents/projects/icehockey_analytics/data/00_backup/df_2020_prediction_' + filename)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in needed schedule-ids\n",
    "df_scheduleid = pd.read_csv(\"https://raw.githubusercontent.com/msjoelin/swehockey_scraper/master/data/scheduleid.csv\", \n",
    "                           error_bad_lines=False,\n",
    "                          dtype=str)\n",
    "\n",
    "# get games for schedule ids\n",
    "games = swe.getGames(df_scheduleid)\n",
    "\n",
    "# Clean up the output with cleanGames\n",
    "df_games_clean = swe.cleanGames(games.copy())\n",
    "\n",
    "df_games_clean.to_csv(\"data/df_games.csv\", index=False)\n",
    "\n",
    "# Create dataframe on teamlevel\n",
    "df_teams = swe.getTeamData(df_games_clean)\n",
    "\n",
    "df_teams.to_csv(\"data/df_teams.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sklearn will be used to build the model\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn import preprocessing\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "\n",
    "# To save the model\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "9912  matches imported\n",
      "19824  teamrows imported\n",
      "Data current season: 149\n"
     ]
    }
   ],
   "source": [
    "# Import game data. Only keep relevant columns\n",
    "\n",
    "df_games = pd.read_csv(\"C:/Users/marcu/Documents/projects/icehockey_analytics/data/df_games.csv\", error_bad_lines=False)[['date', 'game', 'score', 'league', 'season', 'game_id', \n",
    "                'home', 'away', 'score_home', 'score_away', 'result']]\n",
    "\n",
    "print(df_games.shape[0], \" matches imported\")\n",
    "\n",
    "df_teams = pd.read_csv(\"C:/Users/marcu/Documents/projects/icehockey_analytics/data/df_teams.csv\", error_bad_lines=False)[['game_id', 'date', 'team', 'h_a', \n",
    "                     'win_R5', 'draw_R5', 'lost_R5',  \n",
    "                     'H2H_W', 'H2H_D', 'H2H_L',\n",
    "                    'scored_avg_R5', 'conceded_avg_R5',\n",
    "                    'points_cum_prev_avg', 'points_cum_h_a_prev_avg']]\n",
    "print(df_teams.shape[0], \" teamrows imported\")\n",
    "\n",
    "\n",
    "# Split into home and away data\n",
    "df_home = df_teams[df_teams['h_a']=='home'].copy().add_suffix('_home').rename(columns={\"date_home\": \"date\", \"team_home\": \"team\"})\n",
    "df_away = df_teams[df_teams['h_a']=='away'].copy().add_suffix('_away').rename(columns={\"date_away\": \"date\", \"team_away\": \"team\"})\n",
    "\n",
    "\n",
    "df_games_trend = pd.merge(df_games, df_home, \n",
    "                          left_on=['date', 'home'], \n",
    "                          right_on=['date', 'team'],                \n",
    "                          how='left')\n",
    "\n",
    "df_games_trend = pd.merge(df_games_trend, df_away, \n",
    "                          left_on=['date', 'away'], \n",
    "                          right_on=['date', 'team'],\n",
    "                          how='left')\n",
    "\n",
    "df_games_trend.drop_duplicates(subset =['date', 'game'], keep = False, inplace = True) \n",
    "\n",
    "# Introduce binary outcome variables\n",
    "\n",
    "\n",
    "df_games_trend=df_games_trend.drop(['H2H_W_away', 'H2H_D_away', 'H2H_L_away', \n",
    "                                    'team_x', 'team_y',\n",
    "                                   'game_id_home', 'game_id_away', \n",
    "                                   'h_a_home', 'h_a_away'], axis=1)\n",
    "\n",
    "\n",
    "df_games_trend = df_games_trend.dropna(subset=['win_R5_home', 'draw_R5_home', 'lost_R5_home', \n",
    "         'H2H_W_home', 'H2H_D_home', 'H2H_L_home', \n",
    "         'scored_avg_R5_home', 'conceded_avg_R5_home',\n",
    "         'points_cum_prev_avg_home', 'points_cum_h_a_prev_avg_home',\n",
    "         'win_R5_away', 'draw_R5_away', 'lost_R5_away',\n",
    "         'scored_avg_R5_away', 'conceded_avg_R5_away',\n",
    "         'points_cum_prev_avg_away', 'points_cum_h_a_prev_avg_away']).reset_index(drop=True)\n",
    "\n",
    "df_2020 = df_games_trend[df_games_trend['season']=='2020/21']\n",
    "\n",
    "print(\"Data current season: \" + str(df_2020.shape[0]))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "X_2020 = df_2020.iloc[:,11:].values\n",
    "y_2020 = df_2020.iloc[:,10].values\n",
    "info_2020 = df_2020.iloc[:,0:9].values\n",
    "\n",
    "scaler = StandardScaler()\n",
    "X_2020 = scaler.fit_transform(X_2020)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\marcu\\Anaconda3\\lib\\site-packages\\sklearn\\base.py:334: UserWarning: Trying to unpickle estimator DecisionTreeClassifier from version 0.22.1 when using version 0.23.2. This might lead to breaking code or invalid results. Use at your own risk.\n",
      "  UserWarning)\n",
      "C:\\Users\\marcu\\Anaconda3\\lib\\site-packages\\sklearn\\base.py:334: UserWarning: Trying to unpickle estimator RandomForestClassifier from version 0.22.1 when using version 0.23.2. This might lead to breaking code or invalid results. Use at your own risk.\n",
      "  UserWarning)\n"
     ]
    }
   ],
   "source": [
    "rf = pickle.load(open(\"C:/Users/marcu/Documents/projects/icehockey_analytics/ml_models/rf_predict_game.sav\", 'rb'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>date</th>\n",
       "      <th>game</th>\n",
       "      <th>score</th>\n",
       "      <th>league</th>\n",
       "      <th>season</th>\n",
       "      <th>game_id</th>\n",
       "      <th>home</th>\n",
       "      <th>away</th>\n",
       "      <th>score_home</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Leksands IF  - Örebro HK</td>\n",
       "      <td>3 - 5</td>\n",
       "      <td>shl</td>\n",
       "      <td>2020/21</td>\n",
       "      <td>490420</td>\n",
       "      <td>Leksands IF</td>\n",
       "      <td>Örebro HK</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Luleå HF  - HV 71</td>\n",
       "      <td>5 - 4</td>\n",
       "      <td>shl</td>\n",
       "      <td>2020/21</td>\n",
       "      <td>490424</td>\n",
       "      <td>Luleå HF</td>\n",
       "      <td>HV 71</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Skellefteå AIK  - Frölunda HC</td>\n",
       "      <td>3 - 1</td>\n",
       "      <td>shl</td>\n",
       "      <td>2020/21</td>\n",
       "      <td>490425</td>\n",
       "      <td>Skellefteå AIK</td>\n",
       "      <td>Frölunda HC</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Växjö Lakers HC  - Rögle BK</td>\n",
       "      <td>2 - 1</td>\n",
       "      <td>shl</td>\n",
       "      <td>2020/21</td>\n",
       "      <td>490427</td>\n",
       "      <td>Växjö Lakers HC</td>\n",
       "      <td>Rögle BK</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Linköping HC  - Brynäs IF</td>\n",
       "      <td>5 - 4</td>\n",
       "      <td>shl</td>\n",
       "      <td>2020/21</td>\n",
       "      <td>490428</td>\n",
       "      <td>Linköping HC</td>\n",
       "      <td>Brynäs IF</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date                           game  score league   season game_id  \\\n",
       "0  2020-10-01       Leksands IF  - Örebro HK  3 - 5    shl  2020/21  490420   \n",
       "1  2020-10-01              Luleå HF  - HV 71  5 - 4    shl  2020/21  490424   \n",
       "2  2020-10-01  Skellefteå AIK  - Frölunda HC  3 - 1    shl  2020/21  490425   \n",
       "3  2020-10-01    Växjö Lakers HC  - Rögle BK  2 - 1    shl  2020/21  490427   \n",
       "4  2020-10-01      Linköping HC  - Brynäs IF  5 - 4    shl  2020/21  490428   \n",
       "\n",
       "              home         away score_home  \n",
       "0      Leksands IF    Örebro HK          3  \n",
       "1         Luleå HF        HV 71          5  \n",
       "2   Skellefteå AIK  Frölunda HC          3  \n",
       "3  Växjö Lakers HC     Rögle BK          2  \n",
       "4     Linköping HC    Brynäs IF          5  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y_pred2020_rf = rf.predict(X_2020)\n",
    "y_pred2020_rf_prob = rf.predict_proba(X_2020)\n",
    "\n",
    "df_2020 = pd.DataFrame(info_2020)\n",
    "\n",
    "df_2020.columns = ['date', 'game', 'score', 'league', 'season', 'game_id', 'home', 'away','score_home']\n",
    "\n",
    "df_2020.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Correct ratio by prediction\n"
     ]
    }
   ],
   "source": [
    "# Set the outcome variables and probabilities\n",
    "df_2020['actual'] = y_2020\n",
    "df_2020['predicted'] = y_pred2020_rf\n",
    "df_2020['prob_home'] = y_pred2020_rf_prob[:,0]\n",
    "df_2020['prob_draw'] = y_pred2020_rf_prob[:,1]\n",
    "df_2020['prob_away'] = y_pred2020_rf_prob[:,2]\n",
    "\n",
    "# Map back sign outcome\n",
    "mapping = {0:'home', 1:'draw', 2:'away'}\n",
    "df_2020 = df_2020.replace({'actual': mapping, 'predicted': mapping})\n",
    "\n",
    "df_2020['correct']=0\n",
    "df_2020.loc[df_2020['actual']==df_2020['predicted'],'correct']=1\n",
    "\n",
    "# Check mean values by sign\n",
    "print(\"Correct ratio by prediction\")\n",
    "df_2020.groupby(['predicted'])['correct'].mean()\n",
    "\n",
    "df_2020.to_csv(\"C:/Users/marcu/Documents/projects/icehockey_analytics/data/df_2020_prediction.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your browser has been opened to visit:\n",
      "\n",
      "    https://accounts.google.com/o/oauth2/auth?client_id=71573049861-amtqabdfctmnqg31vqgcrkdfnnuvf4sj.apps.googleusercontent.com&redirect_uri=http%3A%2F%2Flocalhost%3A8080%2F&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive&access_type=offline&response_type=code\n",
      "\n",
      "Authentication successful.\n"
     ]
    }
   ],
   "source": [
    "from pydrive.auth import GoogleAuth\n",
    "from pydrive.drive import GoogleDrive\n",
    "\n",
    "gauth = GoogleAuth()\n",
    "gauth.LocalWebserverAuth()\n",
    "\n",
    "drive = GoogleDrive(gauth)\n",
    "\n",
    "update_file = drive.CreateFile({\"id\": \"15A9q85U8KiA5bC-rRINlz64QZJFSLvq9JafLUT1h5bw\"})\n",
    "update_file.SetContentFile(\"C:/Users/marcu/Documents/projects/icehockey_analytics/data/df_2020_prediction.csv\")\n",
    "update_file.Upload()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    " # Link to report https://datastudio.google.com/u/0/reporting/59fa9193-7c85-414e-a49a-582ce03d29c5/page/LLkjB/edit"
   ]
  }
 ],
 "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
}