first_try.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# This is just a first tests. Don't judge me"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Importing and processing raw data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "matches.shape:  (44109, 9)\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>date</th>\n",
       "      <th>home_team</th>\n",
       "      <th>away_team</th>\n",
       "      <th>home_score</th>\n",
       "      <th>away_score</th>\n",
       "      <th>tournament</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>neutral</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1872-11-30</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1873-03-08</td>\n",
       "      <td>England</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>London</td>\n",
       "      <td>England</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1874-03-07</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1875-03-06</td>\n",
       "      <td>England</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>London</td>\n",
       "      <td>England</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1876-03-04</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date home_team away_team  home_score  away_score tournament     city  \\\n",
       "0  1872-11-30  Scotland   England           0           0   Friendly  Glasgow   \n",
       "1  1873-03-08   England  Scotland           4           2   Friendly   London   \n",
       "2  1874-03-07  Scotland   England           2           1   Friendly  Glasgow   \n",
       "3  1875-03-06   England  Scotland           2           2   Friendly   London   \n",
       "4  1876-03-04  Scotland   England           3           0   Friendly  Glasgow   \n",
       "\n",
       "    country  neutral  \n",
       "0  Scotland    False  \n",
       "1   England    False  \n",
       "2  Scotland    False  \n",
       "3   England    False  \n",
       "4  Scotland    False  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from sklearn import metrics\n",
    "\n",
    "# read in csv data\n",
    "matches = pd.read_csv(\"AI Match Results 150years_extended_20221205.csv\")\n",
    "#matches = pd.read_csv(\"with_add_Features.csv\")\n",
    "print(\"matches.shape: \", matches.shape)\n",
    "#print(\"matches.dtypes: \\n\", matches.dtypes)\n",
    "matches.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating new overall features\n",
    "\n",
    "- calculating the strength of each team using ELO https://en.wikipedia.org/wiki/World_Football_Elo_Ratings\n",
    "- calculating goals achived (fGoals) and goals against (aGoals) for eacht team overall\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>home_team</th>\n",
       "      <th>away_team</th>\n",
       "      <th>home_score</th>\n",
       "      <th>away_score</th>\n",
       "      <th>tournament</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>neutral</th>\n",
       "      <th>ELO_home</th>\n",
       "      <th>...</th>\n",
       "      <th>t2t_fGoals_home</th>\n",
       "      <th>t2t_fGoals_away</th>\n",
       "      <th>t2t_fGoals_p_match_home</th>\n",
       "      <th>t2t_fGoals_p_match_away</th>\n",
       "      <th>t2t_num_wins_home</th>\n",
       "      <th>t2t_num_wins_away</th>\n",
       "      <th>t2t_num_draw</th>\n",
       "      <th>t2t_p_wins_home</th>\n",
       "      <th>t2t_p_wins_away</th>\n",
       "      <th>t2t_p_draw</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1872-11-30</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1873-03-08</td>\n",
       "      <td>England</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>London</td>\n",
       "      <td>England</td>\n",
       "      <td>False</td>\n",
       "      <td>1002.801300</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1874-03-07</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>990.147921</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1875-03-06</td>\n",
       "      <td>England</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>London</td>\n",
       "      <td>England</td>\n",
       "      <td>False</td>\n",
       "      <td>1002.122893</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1876-03-04</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1000.790631</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1876-03-25</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>Wales</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1007.947444</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1877-03-03</td>\n",
       "      <td>England</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>London</td>\n",
       "      <td>England</td>\n",
       "      <td>False</td>\n",
       "      <td>992.052556</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1877-03-05</td>\n",
       "      <td>Wales</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Wrexham</td>\n",
       "      <td>Wales</td>\n",
       "      <td>False</td>\n",
       "      <td>993.010717</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1878-03-02</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>England</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1044.776295</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1878-03-23</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>Wales</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>Friendly</td>\n",
       "      <td>Glasgow</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>False</td>\n",
       "      <td>1055.945242</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date home_team away_team  home_score  away_score tournament     city  \\\n",
       "0 1872-11-30  Scotland   England           0           0   Friendly  Glasgow   \n",
       "1 1873-03-08   England  Scotland           4           2   Friendly   London   \n",
       "2 1874-03-07  Scotland   England           2           1   Friendly  Glasgow   \n",
       "3 1875-03-06   England  Scotland           2           2   Friendly   London   \n",
       "4 1876-03-04  Scotland   England           3           0   Friendly  Glasgow   \n",
       "5 1876-03-25  Scotland     Wales           4           0   Friendly  Glasgow   \n",
       "6 1877-03-03   England  Scotland           1           3   Friendly   London   \n",
       "7 1877-03-05     Wales  Scotland           0           2   Friendly  Wrexham   \n",
       "8 1878-03-02  Scotland   England           7           2   Friendly  Glasgow   \n",
       "9 1878-03-23  Scotland     Wales           9           0   Friendly  Glasgow   \n",
       "\n",
       "    country  neutral     ELO_home  ...  t2t_fGoals_home  t2t_fGoals_away  \\\n",
       "0  Scotland    False  1000.000000  ...                0                0   \n",
       "1   England    False  1002.801300  ...                0                0   \n",
       "2  Scotland    False   990.147921  ...                0                0   \n",
       "3   England    False  1002.122893  ...                0                0   \n",
       "4  Scotland    False  1000.790631  ...                0                0   \n",
       "5  Scotland    False  1007.947444  ...                0                0   \n",
       "6   England    False   992.052556  ...                0                0   \n",
       "7     Wales    False   993.010717  ...                0                0   \n",
       "8  Scotland    False  1044.776295  ...                0                0   \n",
       "9  Scotland    False  1055.945242  ...                0                0   \n",
       "\n",
       "   t2t_fGoals_p_match_home  t2t_fGoals_p_match_away  t2t_num_wins_home  \\\n",
       "0                      0.0                      0.0                  0   \n",
       "1                      0.0                      0.0                  0   \n",
       "2                      0.0                      0.0                  0   \n",
       "3                      0.0                      0.0                  0   \n",
       "4                      0.0                      0.0                  0   \n",
       "5                      0.0                      0.0                  0   \n",
       "6                      0.0                      0.0                  0   \n",
       "7                      0.0                      0.0                  0   \n",
       "8                      0.0                      0.0                  0   \n",
       "9                      0.0                      0.0                  0   \n",
       "\n",
       "   t2t_num_wins_away  t2t_num_draw  t2t_p_wins_home  t2t_p_wins_away  \\\n",
       "0                  0             0              0.0              0.0   \n",
       "1                  0             0              0.0              0.0   \n",
       "2                  0             0              0.0              0.0   \n",
       "3                  0             0              0.0              0.0   \n",
       "4                  0             0              0.0              0.0   \n",
       "5                  0             0              0.0              0.0   \n",
       "6                  0             0              0.0              0.0   \n",
       "7                  0             0              0.0              0.0   \n",
       "8                  0             0              0.0              0.0   \n",
       "9                  0             0              0.0              0.0   \n",
       "\n",
       "   t2t_p_draw  \n",
       "0         0.0  \n",
       "1         0.0  \n",
       "2         0.0  \n",
       "3         0.0  \n",
       "4         0.0  \n",
       "5         0.0  \n",
       "6         0.0  \n",
       "7         0.0  \n",
       "8         0.0  \n",
       "9         0.0  \n",
       "\n",
       "[10 rows x 35 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create dict for tournament type -> moved to K_val.py\n",
    "from K_val import K\n",
    "\n",
    "def calcNewELO( R_old, R_opp, tournament_type, own_score, opp_score, is_home_team, neutral):\n",
    "    # R_new = R_old + K * G * (W - W_e)\n",
    "    #   R_new = The new team rating\n",
    "    #   R_old = The old team rating\n",
    "    #   K = Weight index regarding the tournament of the match\n",
    "    #       World Cup, Olympic Games (1908–1980) \t60\n",
    "    #       Continental championship and intercontinental tournaments \t50\n",
    "    #       World Cup and Continental qualifiers and major tournaments \t40\n",
    "    #       All other tournaments \t30\n",
    "    #       Friendly matches \t30\n",
    "    #   G = A number from the index of goal differences\n",
    "    #   W = The result of the match\n",
    "    #   W_e = The expected result\n",
    "    G=1\n",
    "    W=1\n",
    "\n",
    "    # set G according to http://www.eloratings.net/about\n",
    "    if (own_score >= (opp_score+4)):\n",
    "        N = own_score-opp_score\n",
    "        G = 1 + 3/4 + (N-3)/8\n",
    "    elif (own_score > opp_score+3):\n",
    "        G = 1.5\n",
    "\n",
    "    # dr equals the difference in ratings plus 100 points for a team playing at home. \n",
    "    if (neutral):\n",
    "        dr = R_old - R_opp\n",
    "    elif (is_home_team):\n",
    "        dr = R_old + 100 - R_opp\n",
    "    else:\n",
    "        dr = R_old - (R_opp +100)\n",
    "    \n",
    "    # We = 1 / ( (10^(-dr/400)) + 1)\n",
    "    We = 1 / ( pow(10, (-dr/400)) + 1)\n",
    "\n",
    "    if (own_score > opp_score):\n",
    "        W = 1\n",
    "    elif (own_score ==  opp_score):\n",
    "        W = 0.5\n",
    "    else:\n",
    "        W = 0\n",
    "\n",
    "    R_new = R_old + K[tournament_type] * G * (W - We)\n",
    "    \n",
    "    return R_new\n",
    "\n",
    "# MAIN\n",
    "\n",
    "# update matches data with ELO, fGoals, aGoals of each team\n",
    "matches[\"ELO_home\"] = 0.0\n",
    "matches[\"ELO_away\"] = 0.0\n",
    "matches[\"fGoals_home\"] = 0\n",
    "matches[\"aGoals_home\"] = 0\n",
    "matches[\"fGoals_away\"] = 0\n",
    "matches[\"aGoals_away\"] = 0\n",
    "matches[\"fGoals_p_match_home\"] = 0.0\n",
    "matches[\"aGoals_p_match_home\"] = 0.0\n",
    "matches[\"fGoals_p_match_away\"] = 0.0\n",
    "matches[\"aGoals_p_match_away\"] = 0.0\n",
    "matches[\"num_matches_home\"] = 0\n",
    "matches[\"num_matches_away\"] = 0\n",
    "matches[\"faGoals_ratio_home\"] = 0.0\n",
    "matches[\"faGoals_ratio_away\"] = 0.0\n",
    "\n",
    "# make a list of all teams using \"home_team\" and \"away_team\"\n",
    "uteams = (matches[\"home_team\"]._append( matches[\"away_team\"] )).unique()\n",
    "\n",
    "# initialie ELO with 1000\n",
    "team_stats = pd.DataFrame({\n",
    "                   \"index\": uteams,\n",
    "                   \"name\": uteams,\n",
    "                   \"ELO\": 1000.0,\n",
    "                   \"fGoals\": 1, # every team starts with fGoals/aGoals score of 1 otherwise the ration would be Inf at the beginning\n",
    "                   \"aGoals\": 1, # since the overall goals will be a high value this offset of 1 should influence much\n",
    "                   \"num_matches\": 0,\n",
    "                   \"fGoals_p_match\": 0.0,\n",
    "                   \"aGoals_p_match\": 0.0})\n",
    "team_stats.set_index(\"index\", inplace = True)\n",
    "\n",
    "# generate a team code for each team in team_stats dataframe\n",
    "team_stats[\"team_code\"] = team_stats[\"name\"].astype(\"category\").cat.codes\n",
    "\n",
    "\n",
    "for ind in matches.index:\n",
    "    # store home and away team name\n",
    "    ht = matches[\"home_team\"][ind]\n",
    "    at = matches[\"away_team\"][ind]\n",
    "\n",
    "    ############################ Team Codes ##############################\n",
    "    matches.at[ind, \"home_team_code\"] = team_stats.at[ht, \"team_code\"]\n",
    "    matches.at[ind, \"away_team_code\"] = team_stats.at[at, \"team_code\"]\n",
    "     \n",
    "    ################################ ELO #################################\n",
    "    \n",
    "    # get ELO from team_stats DataFrame (before match) and write into matches DataFrame \n",
    "    matches.at[ind, \"ELO_home\"] = team_stats.at[ht, \"ELO\"]\n",
    "    matches.at[ind, \"ELO_away\"] = team_stats.at[at, \"ELO\"]\n",
    "    # calc new ELO\n",
    "    Rn_home = calcNewELO(matches[\"ELO_home\"][ind], matches[\"ELO_away\"][ind], matches[\"tournament\"][ind], matches[\"home_score\"][ind], matches[\"away_score\"][ind], not matches[\"neutral\"][ind], matches[\"neutral\"][ind])\n",
    "    Rn_away = calcNewELO(matches[\"ELO_away\"][ind], matches[\"ELO_home\"][ind], matches[\"tournament\"][ind], matches[\"away_score\"][ind], matches[\"home_score\"][ind], False, matches[\"neutral\"][ind])\n",
    "    # Update ELO after match\n",
    "    team_stats.at[ht, \"ELO\"] = float(Rn_home)\n",
    "    team_stats.at[at, \"ELO\"] = float(Rn_away)\n",
    "\n",
    "    ######################### For/Against Goals #########################\n",
    "    # store home and away team goals before match\n",
    "    matches.at[ind, \"fGoals_home\"] = team_stats.at[ht, \"fGoals\"]\n",
    "    matches.at[ind, \"aGoals_home\"] = team_stats.at[ht, \"aGoals\"]\n",
    "    matches.at[ind, \"fGoals_away\"] = team_stats.at[at, \"fGoals\"]\n",
    "    matches.at[ind, \"aGoals_away\"] = team_stats.at[at, \"aGoals\"]\n",
    "    # update foa and against goals\n",
    "    team_stats.at[ht, \"fGoals\"] = team_stats.at[ht, \"fGoals\"] + matches[\"home_score\"][ind]\n",
    "    team_stats.at[at, \"fGoals\"] = team_stats.at[at, \"fGoals\"] + matches[\"away_score\"][ind]\n",
    "    team_stats.at[ht, \"aGoals\"] = team_stats.at[ht, \"aGoals\"] + matches[\"away_score\"][ind]\n",
    "    team_stats.at[at, \"aGoals\"] = team_stats.at[at, \"aGoals\"] + matches[\"home_score\"][ind]\n",
    "\n",
    "    matches.at[ind, \"faGoals_ratio_home\"] = team_stats.at[ht, \"fGoals\"] / team_stats.at[ht, \"aGoals\"]\n",
    "    matches.at[ind, \"faGoals_ratio_away\"] = team_stats.at[at, \"fGoals\"] / team_stats.at[at, \"aGoals\"]\n",
    "\n",
    "    ##################### Average Goals per match ########################\n",
    "    # store home and away team goals before match\n",
    "    matches.at[ind, \"num_matches_home\"] = team_stats.at[ht, \"num_matches\"]\n",
    "    matches.at[ind, \"num_matches_away\"] = team_stats.at[at, \"num_matches\"]\n",
    "    matches.at[ind, \"fGoals_p_match_home\"] = team_stats.at[ht, \"fGoals_p_match\"]\n",
    "    matches.at[ind, \"aGoals_p_match_home\"] = team_stats.at[ht, \"aGoals_p_match\"]\n",
    "    matches.at[ind, \"fGoals_p_match_away\"] = team_stats.at[at, \"fGoals_p_match\"]\n",
    "    matches.at[ind, \"aGoals_p_match_away\"] = team_stats.at[at, \"aGoals_p_match\"]\n",
    "    \n",
    "    # update after match\n",
    "    ht_num_matches = team_stats.at[ht, \"num_matches\"]+1\n",
    "    at_num_matches = team_stats.at[at, \"num_matches\"]+1\n",
    "    team_stats.at[ht, \"num_matches\"] = ht_num_matches\n",
    "    team_stats.at[at, \"num_matches\"] = at_num_matches\n",
    "\n",
    "    team_stats.at[ht, \"fGoals_p_match\"] = team_stats.at[ht, \"fGoals\"] / ht_num_matches\n",
    "    team_stats.at[ht, \"aGoals_p_match\"] = team_stats.at[ht, \"aGoals\"] / ht_num_matches\n",
    "    team_stats.at[at, \"fGoals_p_match\"] = team_stats.at[at, \"fGoals\"] / at_num_matches\n",
    "    team_stats.at[at, \"aGoals_p_match\"] = team_stats.at[at, \"aGoals\"] / at_num_matches\n",
    "\n",
    "\n",
    "    \n",
    "matches.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating team-to-team features\n",
    "\n",
    "- calculating goals achived (fGoals) and goals against (aGoals) and match statistics (win/lose/draw) for eacht team against the specific opponent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this will take a while....\n",
    "import datetime as dt\n",
    "\n",
    "def get_t2t_data(ht, at, date):\n",
    "    t2t_fGoals_home = 0\n",
    "    t2t_fGoals_away = 0\n",
    "    t2t_fGoals_p_match_home = 0.0\n",
    "    t2t_fGoals_p_match_away = 0.0\n",
    "    t2t_num_wins_home = 0\n",
    "    t2t_num_wins_away = 0\n",
    "    t2t_num_draw = 0\n",
    "    t2t_p_wins_home = 0.0\n",
    "    t2t_p_wins_away = 0.0\n",
    "    t2t_p_draw = 0.0\n",
    "\n",
    "    # get two subset of matches where the two teams played against each other home-vs-away and away-vs-home before the current match\n",
    "    t2t_matches1 = matches.loc[(matches[\"home_team\"] == ht) & (matches[\"away_team\"] == at) & (matches[\"date\"] < date)]\n",
    "    t2t_matches2 = matches.loc[(matches[\"home_team\"] == at) & (matches[\"away_team\"] == ht) & (matches[\"date\"] < date)]\n",
    "    # invert home_team and away_team for the second subset\n",
    "    idx = t2t_matches2.index\n",
    "    t2t_matches2.loc[idx,[\"home_team\",\"away_team\",\"home_score\",\"away_score\"]] = t2t_matches2.loc[idx,[\"away_team\",\"home_team\",\"away_score\",\"home_score\"]].values\n",
    "    # append both dataframes to one\n",
    "    t2t_matches = t2t_matches1._append(t2t_matches2)\n",
    "\n",
    "    # exctract features\n",
    "    num_matches = t2t_matches.shape[0]\n",
    "\n",
    "    if (num_matches > 0):\n",
    "        # number of goals ooverall for each team\n",
    "        t2t_fGoals_home = t2t_matches[\"home_score\"].sum()\n",
    "        t2t_fGoals_away = t2t_matches[\"away_score\"].sum()\n",
    "        # number of goals per match\n",
    "        t2t_fGoals_p_match_home = t2t_fGoals_home/num_matches\n",
    "        t2t_fGoals_p_match_away = t2t_fGoals_away/num_matches\n",
    "        # number of wins/lose/draw\n",
    "        t2t_num_wins_home = t2t_matches.loc[t2t_matches[\"home_score\"] > t2t_matches[\"away_score\"]].shape[0]\n",
    "        t2t_num_wins_away = t2t_matches.loc[t2t_matches[\"home_score\"] < t2t_matches[\"away_score\"]].shape[0]\n",
    "        t2t_num_draw = t2t_matches.loc[t2t_matches[\"home_score\"] == t2t_matches[\"away_score\"]].shape[0]\n",
    "        # percetage of wins/lose/draw\n",
    "        t2t_p_wins_home = t2t_num_wins_home/num_matches\n",
    "        t2t_p_wins_away = t2t_num_wins_away/num_matches\n",
    "        t2t_p_draw = t2t_num_draw/num_matches\n",
    "\n",
    "    return [t2t_fGoals_home, \n",
    "            t2t_fGoals_away, \n",
    "            t2t_fGoals_p_match_home,\n",
    "            t2t_fGoals_p_match_away,\n",
    "            t2t_num_wins_home,\n",
    "            t2t_num_wins_away,\n",
    "            t2t_num_draw,\n",
    "            t2t_p_wins_home,\n",
    "            t2t_p_wins_away,\n",
    "            t2t_p_draw]\n",
    "\n",
    "\n",
    "# Save the current time\n",
    "t = dt.datetime.now()\n",
    "\n",
    "#convert date into usable format\n",
    "matches[\"date\"] = pd.to_datetime(matches[\"date\"])\n",
    "\n",
    "matches[\"t2t_fGoals_home\"] = 0\n",
    "matches[\"t2t_fGoals_away\"] = 0\n",
    "matches[\"t2t_fGoals_p_match_home\"] = 0.0\n",
    "matches[\"t2t_fGoals_p_match_away\"] = 0.0\n",
    "matches[\"t2t_num_wins_home\"] = 0\n",
    "matches[\"t2t_num_wins_away\"] = 0\n",
    "matches[\"t2t_num_draw\"] = 0\n",
    "matches[\"t2t_p_wins_home\"] = 0.0\n",
    "matches[\"t2t_p_wins_away\"] = 0.0\n",
    "matches[\"t2t_p_draw\"] = 0.0\n",
    "\n",
    "# go through matches\n",
    "# for ind in matches.index:\n",
    "#     # new team to team features\n",
    "#     ht = matches[\"home_team\"][ind]\n",
    "#     at = matches[\"away_team\"][ind]\n",
    "#     date = matches[\"date\"][ind]\n",
    "\n",
    "#     #print(ht, at, da)\n",
    "#     matches.at[ind, [   \"t2t_fGoals_home\",\n",
    "#                         \"t2t_fGoals_away\",\n",
    "#                         \"t2t_fGoals_p_match_home\",\n",
    "#                         \"t2t_fGoals_p_match_away\",\n",
    "#                         \"t2t_num_wins_home\",\n",
    "#                         \"t2t_num_wins_away\",\n",
    "#                         \"t2t_num_draw\",\n",
    "#                         \"t2t_p_wins_home\",\n",
    "#                         \"t2t_p_wins_away\",\n",
    "#                         \"t2t_p_draw\"]] = get_t2t_data(ht, at, date)\n",
    "\n",
    "\n",
    "#     delta = dt.datetime.now()-t\n",
    "#     if delta.seconds >= 30:\n",
    "#         # print progress every minute\n",
    "#         #print(\"Currently at match at: \", da)\n",
    "#         print(\"Progress: {:.1f}\".format(ind*100/matches.shape[0]), \"%\")\n",
    "#         # Update 't' variable to new time\n",
    "#         t = dt.datetime.now()\n",
    "\n",
    "# matches[[   \"t2t_fGoals_home\",\n",
    "#             \"t2t_fGoals_away\",\n",
    "#             \"t2t_fGoals_p_match_home\",\n",
    "#             \"t2t_fGoals_p_match_away\",\n",
    "#             \"t2t_num_wins_home\",\n",
    "#             \"t2t_num_wins_away\",\n",
    "#             \"t2t_num_draw\",\n",
    "#             \"t2t_p_wins_home\",\n",
    "#             \"t2t_p_wins_away\",\n",
    "#             \"t2t_p_draw\"]] = matches.apply(lambda row: get_t2t_data(row['home_team'], row['away_team'], row['date']), axis=1, result_type='expand')\n",
    "# matches.head(20)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "matches.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# store data to csv\n",
    "matches.to_csv(\"matches_with_new_features.csv\")\n",
    "team_stats.to_csv(\"team_stats.csv\")  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Skip all above if no new features and import pre-generated sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "matches.shape:  (44109, 37)\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>name</th>\n",
       "      <th>ELO</th>\n",
       "      <th>fGoals</th>\n",
       "      <th>aGoals</th>\n",
       "      <th>num_matches</th>\n",
       "      <th>fGoals_p_match</th>\n",
       "      <th>aGoals_p_match</th>\n",
       "      <th>team_code</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>index</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Scotland</th>\n",
       "      <td>Scotland</td>\n",
       "      <td>1413.571029</td>\n",
       "      <td>1395</td>\n",
       "      <td>996</td>\n",
       "      <td>813</td>\n",
       "      <td>1.715867</td>\n",
       "      <td>1.225092</td>\n",
       "      <td>241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>England</th>\n",
       "      <td>England</td>\n",
       "      <td>1649.420210</td>\n",
       "      <td>2292</td>\n",
       "      <td>1017</td>\n",
       "      <td>1047</td>\n",
       "      <td>2.189112</td>\n",
       "      <td>0.971347</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wales</th>\n",
       "      <td>Wales</td>\n",
       "      <td>1385.198241</td>\n",
       "      <td>859</td>\n",
       "      <td>1122</td>\n",
       "      <td>692</td>\n",
       "      <td>1.241329</td>\n",
       "      <td>1.621387</td>\n",
       "      <td>300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northern Ireland</th>\n",
       "      <td>Northern Ireland</td>\n",
       "      <td>1206.894299</td>\n",
       "      <td>703</td>\n",
       "      <td>1255</td>\n",
       "      <td>673</td>\n",
       "      <td>1.044577</td>\n",
       "      <td>1.864785</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United States</th>\n",
       "      <td>United States</td>\n",
       "      <td>1524.948033</td>\n",
       "      <td>1078</td>\n",
       "      <td>945</td>\n",
       "      <td>728</td>\n",
       "      <td>1.480769</td>\n",
       "      <td>1.298077</td>\n",
       "      <td>291</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              name          ELO  fGoals  aGoals  num_matches  \\\n",
       "index                                                                          \n",
       "Scotland                  Scotland  1413.571029    1395     996          813   \n",
       "England                    England  1649.420210    2292    1017         1047   \n",
       "Wales                        Wales  1385.198241     859    1122          692   \n",
       "Northern Ireland  Northern Ireland  1206.894299     703    1255          673   \n",
       "United States        United States  1524.948033    1078     945          728   \n",
       "\n",
       "                  fGoals_p_match  aGoals_p_match  team_code  \n",
       "index                                                        \n",
       "Scotland                1.715867        1.225092        241  \n",
       "England                 2.189112        0.971347         85  \n",
       "Wales                   1.241329        1.621387        300  \n",
       "Northern Ireland        1.044577        1.864785        198  \n",
       "United States           1.480769        1.298077        291  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from sklearn import metrics\n",
    "\n",
    "# read in csv data\n",
    "matches = pd.read_csv(\"matches_with_new_features.csv\")\n",
    "team_stats = pd.read_csv(\"team_stats.csv\")\n",
    "team_stats.set_index(\"index\", inplace = True)\n",
    "print(\"matches.shape: \", matches.shape)\n",
    "#print(\"matches.dtypes: \\n\", matches.dtypes)\n",
    "team_stats.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### preprocess data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Full set:             44109\n",
      "Reduced set:          26411\n",
      "Train data raw:       21129\n",
      "Train data unskew:    19654\n",
      "Test data:            5282\n"
     ]
    }
   ],
   "source": [
    "from sklearn.preprocessing import StandardScaler\n",
    "import numpy as np\n",
    "\n",
    "#convert date into usable format\n",
    "matches[\"date\"] = pd.to_datetime(matches[\"date\"])\n",
    "\n",
    "################ coding type<object> data ################\n",
    "# convert object columns into integers\n",
    "\n",
    "# categorize tournament\n",
    "matches[\"tournament_code\"] = matches[\"tournament\"].astype(\"category\").cat.codes\n",
    "\n",
    "################ scale data ################\n",
    "\n",
    "\n",
    "## TODo: Scale train set only and apply same scale to testset after \n",
    "goal_scaler = StandardScaler()\n",
    "goal_pm_scaler = StandardScaler()\n",
    "ELO_scaler = StandardScaler()\n",
    "tournament_scaler = StandardScaler()\n",
    "team_scaler = StandardScaler()\n",
    "num_win_scaler = StandardScaler()\n",
    "\n",
    "matches[[   \"fGoals_home_sc\", \n",
    "            \"aGoals_home_sc\", \n",
    "            \"fGoals_away_sc\", \n",
    "            \"aGoals_away_sc\",\n",
    "            \"t2t_fGoals_home_sc\",\n",
    "            \"t2t_fGoals_away_sc\" ]] = goal_scaler.fit_transform(matches[[\"fGoals_home\", \"aGoals_home\", \"fGoals_away\", \"aGoals_away\", \"t2t_fGoals_home\", \"t2t_fGoals_away\"]])\n",
    "\n",
    "matches[[   \"fGoals_p_match_home_sc\", \n",
    "            \"aGoals_p_match_home_sc\", \n",
    "            \"fGoals_p_match_away_sc\", \n",
    "            \"aGoals_p_match_away_sc\",\n",
    "            \"t2t_fGoals_p_match_home_sc\",\n",
    "            \"t2t_fGoals_p_match_away_sc\" ]] = goal_pm_scaler.fit_transform(matches[[\"fGoals_p_match_home\", \"aGoals_p_match_home\", \"fGoals_p_match_away\", \"aGoals_p_match_away\",\"t2t_fGoals_p_match_home\", \"t2t_fGoals_p_match_away\"]])\n",
    "\n",
    "matches[[   \"ELO_home_sc\", \n",
    "            \"ELO_away_sc\"]] = ELO_scaler.fit_transform(matches[[\"ELO_home\", \"ELO_away\"]])\n",
    "\n",
    "matches[[   \"tournament_code_sc\" ]] = tournament_scaler.fit_transform(matches[[\"tournament_code\" ]])\n",
    "\n",
    "matches[[   \"home_team_code_sc\", \n",
    "            \"away_team_code_sc\"]] = team_scaler.fit_transform(matches[[\"home_team_code\", \"away_team_code\"]])\n",
    "\n",
    "matches[[   \"t2t_num_wins_home_sc\", \n",
    "            \"t2t_num_wins_away_sc\",\n",
    "            \"t2t_num_draw_sc\" ]] = num_win_scaler.fit_transform(matches[[\"t2t_num_wins_home\", \"t2t_num_wins_away\", \"t2t_num_draw\"]])\n",
    "            \n",
    "\n",
    "\n",
    "################ calculate desired output ################\n",
    "# convert result into boolean statements\n",
    "matches[\"home_win\"] = (matches[\"home_score\"] > matches[\"away_score\"])\n",
    "matches[\"away_win\"] = (matches[\"home_score\"] < matches[\"away_score\"])\n",
    "matches[\"draw\"] = (matches[\"home_score\"] == matches[\"away_score\"])\n",
    "\n",
    "matches[\"result\"] = \"home_win\"\n",
    "matches.loc[matches[\"home_score\"] < matches[\"away_score\"], \"result\"] = \"away_win\"\n",
    "matches.loc[matches[\"home_score\"] == matches[\"away_score\"], \"result\"] = \"draw\"\n",
    "matches[\"result_code\"] = matches[\"result\"].astype(\"category\").cat.codes\n",
    "matches[\"result_cat\"] = matches[\"result\"].astype(\"category\")\n",
    "\n",
    "matches[\"goal_diff\"] = abs(matches[\"home_score\"] - matches[\"away_score\"])\n",
    "matches[\"loser_score\"] = matches[[\"home_score\", \"away_score\"]].min(axis=1)\n",
    "\n",
    "\n",
    "################ reduce dataset ################\n",
    "\n",
    "#drop old data before a certain date\n",
    "red_matches  = matches.drop(matches[matches[\"date\"] < \"1993-01-01\"].index)\n",
    "\n",
    "# drop first third of data since ELO algortithm itself needs time to be adjusted:\n",
    "# a = int(0.33*matches.shape[0])\n",
    "# red_matches = matches.iloc[a:, :]   # around 2.5% precission improvent\n",
    "\n",
    "################ split train and test data ################\n",
    "\n",
    "#use 80% for training and the remaining for testing\n",
    "train_raw = red_matches.sample(frac = 0.8)\n",
    "test  = red_matches.drop(train_raw.index)\n",
    "\n",
    "# unskew train data\n",
    "train0 = train_raw.loc[train_raw[\"result_code\"] == 0]\n",
    "train1 = train_raw.loc[train_raw[\"result_code\"] == 1]\n",
    "train2 = train_raw.loc[train_raw[\"result_code\"] == 2]\n",
    "\n",
    "num_to_keep_of_every_class = int((train0.shape[0]+train2.shape[0]+train2.shape[0])/3) # keep average of all classes\n",
    "train0 = train0.sample(min(train0.shape[0], num_to_keep_of_every_class))\n",
    "train1 = train1.sample(min(train1.shape[0], num_to_keep_of_every_class))\n",
    "train2 = train2.sample(min(train2.shape[0], num_to_keep_of_every_class))\n",
    "\n",
    "train_unskew_result = train0._append(train1)._append(train2).sample(frac = 1) # concate everything and mix\n",
    "\n",
    "print(\"Full set:            \", matches.shape[0])\n",
    "print(\"Reduced set:         \", red_matches.shape[0])\n",
    "print(\"Train data raw:      \", train_raw.shape[0])\n",
    "print(\"Train data unskew:   \", train_unskew_result.shape[0])\n",
    "#print(\"Train data skew val: \", train_unskew_result[[\"date\", \"result_code\"]].skew(axis=0)[0])\n",
    "print(\"Test data:           \", test.shape[0])\n",
    "\n",
    "#train.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5639"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# unskewing goal_diff data\n",
    "\n",
    "num_to_keep_of_every_class = int(np.mean(train_raw.groupby(['goal_diff']).size()))\n",
    "\n",
    "#create unique list of names\n",
    "UniqueKeys = train_raw.goal_diff.unique()\n",
    "\n",
    "train_unskew_goals = pd.DataFrame(columns = train_raw.columns)\n",
    "\n",
    "#create a data frame dictionary to store your data frames\n",
    "DataFrameDict = {elem : pd.DataFrame() for elem in UniqueKeys}\n",
    "\n",
    "for key in UniqueKeys:\n",
    "    DataFrameDict = train_raw[train_raw[\"goal_diff\"] == key]\n",
    "    DataFrameDict= DataFrameDict.sample(min(DataFrameDict.shape[0], num_to_keep_of_every_class))\n",
    "    train_unskew_goals = train_unskew_goals._append(DataFrameDict)\n",
    "\n",
    "\n",
    "train_unskew_goals.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Match result classification"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Baseline: Decide exclusively based on ELO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              precision    recall  f1-score   support\n",
      "\n",
      "    away_win       0.45      0.69      0.55      1457\n",
      "        draw       0.25      0.09      0.13      1287\n",
      "    home_win       0.67      0.68      0.67      2538\n",
      "\n",
      "    accuracy                           0.54      5282\n",
      "   macro avg       0.46      0.49      0.45      5282\n",
      "weighted avg       0.51      0.54      0.51      5282\n",
      "\n"
     ]
    }
   ],
   "source": [
    "#bl_predictions = test[\"ELO_home\"] > test[\"ELO_away\"]\n",
    "\n",
    "bl_predictions = test.copy()\n",
    "\n",
    "bl_predictions[\"ELO_result\"] = \"draw\"\n",
    "bl_predictions.loc[test[\"ELO_home\"] < test[\"ELO_away\"]-20, \"ELO_result\"] = \"away_win\"\n",
    "bl_predictions.loc[test[\"ELO_home\"] > test[\"ELO_away\"]+20, \"ELO_result\"] = \"home_win\"\n",
    "\n",
    "\n",
    "# Scores\n",
    "#acc_score = metrics.accuracy_score(test[targets], bl_predictions)\n",
    "#prec_score = metrics.precision_score(test[targets], bl_predictions)\n",
    "\n",
    "#print(\"Accuracy:   \", acc_score)\n",
    "#print(\"Precission: \", prec_score)\n",
    "\n",
    "BL_Elo_Report = metrics.classification_report(bl_predictions[\"result\"], bl_predictions[\"ELO_result\"])\n",
    "print(BL_Elo_Report)\n",
    "#bl_predictions[[\"ELO_home\", \"ELO_away\", \"result\", \"ELO_result\"]].head()\n",
    "\n",
    "#bl_predictions.to_excel(\"BL.xlsx\")  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MLP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fitting\n",
    "from sklearn.neural_network import MLPRegressor\n",
    "\n",
    "train = train_unskew_result\n",
    "\n",
    "mlp_features = [\"home_team_code_sc\", \n",
    "                \"away_team_code_sc\", \n",
    "                \"ELO_home_sc\", \n",
    "                \"ELO_away_sc\", \n",
    "                \"neutral\", \n",
    "                \"fGoals_p_match_home_sc\", \n",
    "                \"aGoals_p_match_home_sc\", \n",
    "                \"fGoals_p_match_away_sc\", \n",
    "                \"aGoals_p_match_away_sc\", \n",
    "                \"t2t_p_wins_home\", \n",
    "                \"t2t_p_wins_away\",\n",
    "                \"t2t_p_draw\"]\n",
    "mlp_targets = [\"home_win\", \"draw\", \"away_win\"]#, \"goal_diff\", \"loser_score\"]\n",
    "\n",
    "mlp = MLPRegressor(solver=\"lbfgs\", alpha=1e-5, hidden_layer_sizes=(50, 20), random_state=1)\n",
    "\n",
    "mlp.fit(train[mlp_features], train[mlp_targets])\n",
    "\n",
    "# make predictions\n",
    "mlp_predictions_raw = mlp.predict(test[mlp_features])\n",
    "mlp_predictions = pd.DataFrame(mlp_predictions_raw, columns = mlp_targets)\n",
    "# tranlate result probs into result\n",
    "mlp_predictions[\"result\"] = mlp_predictions[[\"home_win\", \"away_win\", \"draw\"]].idxmax(axis=1)\n",
    "\n",
    "# generate report\n",
    "MLP_Report = metrics.classification_report(test[\"result\"], mlp_predictions[\"result\"])\n",
    "print(MLP_Report)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# visualize prediction (not needed)\n",
    "mlp_predictions.reset_index(drop=True, inplace=True)\n",
    "test.reset_index(drop=True, inplace=True)\n",
    "a = pd.concat([test[[\"home_team\", \"away_team\", \"ELO_home\", \"ELO_away\", \"neutral\", \"fGoals_home\", \"aGoals_home\", \"fGoals_away\", \"aGoals_away\", \"result\"]], mlp_predictions], axis=1, keys=[\"Train Data\", \"Prediction\"])\n",
    "a.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### RandomForestClassifier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestClassifier\n",
    "\n",
    "train = train_unskew_result\n",
    "\n",
    "# fitting\n",
    "rf = RandomForestClassifier(n_estimators=100, min_samples_split=5, random_state=1)\n",
    "\n",
    "rf_features = [ \"home_team_code_sc\", \n",
    "                \"away_team_code_sc\", \n",
    "                \"ELO_home_sc\", \n",
    "                \"ELO_away_sc\", \n",
    "                \"neutral\", \n",
    "                \"fGoals_p_match_home_sc\", \n",
    "                \"aGoals_p_match_home_sc\", \n",
    "                \"fGoals_p_match_away_sc\", \n",
    "                \"aGoals_p_match_away_sc\", \n",
    "                \"t2t_p_wins_home\", \n",
    "                \"t2t_p_wins_away\",\n",
    "                \"t2t_p_draw\"]\n",
    "rf_targets = [\"result\"]\n",
    "\n",
    "rf.fit(train[rf_features], train[rf_targets])\n",
    "\n",
    "# make predictions\n",
    "rf_predictions = rf.predict(test[rf_features])\n",
    "\n",
    "# generate report\n",
    "RF_Report = metrics.classification_report(test[\"result\"], rf_predictions)\n",
    "print(RF_Report)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# visualize prediction (not needed)\n",
    "a = test.copy()\n",
    "a[\"prediction\"] = rf_predictions\n",
    "\n",
    "a[[\"home_team\", \"away_team\", \"ELO_home\", \"ELO_away\", \"neutral\", \"fGoals_home\", \"aGoals_home\", \"fGoals_away\", \"aGoals_away\", \"result\", \"prediction\"]].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### XGBoost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\toolbase\\python\\3.11.4.2\\Lib\\site-packages\\xgboost\\core.py:160: UserWarning: [08:25:23] WARNING: C:\\buildkite-agent\\builds\\buildkite-windows-cpu-autoscaling-group-i-0b3782d1791676daf-1\\xgboost\\xgboost-ci-windows\\src\\objective\\regression_obj.cu:209: reg:linear is now deprecated in favor of reg:squarederror.\n",
      "  warnings.warn(smsg, UserWarning)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              precision    recall  f1-score   support\n",
      "\n",
      "    away_win       0.52      0.60      0.56      1487\n",
      "        draw       0.33      0.09      0.14      1256\n",
      "    home_win       0.62      0.79      0.70      2539\n",
      "\n",
      "    accuracy                           0.57      5282\n",
      "   macro avg       0.49      0.49      0.47      5282\n",
      "weighted avg       0.53      0.57      0.53      5282\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import xgboost as xgb\n",
    "\n",
    "train = train_unskew_result\n",
    "\n",
    "xg_reg = xgb.XGBRegressor(objective ='reg:linear', colsample_bytree = 0.3, learning_rate = 0.1,\n",
    "                max_depth = 6, alpha = 10, n_estimators = 200)\n",
    "\n",
    "xg_features = [ \"home_team_code_sc\", \n",
    "                \"away_team_code_sc\", \n",
    "                \"ELO_home_sc\", \n",
    "                \"ELO_away_sc\", \n",
    "                \"neutral\", \n",
    "                \"fGoals_p_match_home_sc\", \n",
    "                \"aGoals_p_match_home_sc\", \n",
    "                \"fGoals_p_match_away_sc\", \n",
    "                \"aGoals_p_match_away_sc\", \n",
    "                \"t2t_p_wins_home\", \n",
    "                \"t2t_p_wins_away\",\n",
    "                \"t2t_p_draw\"]\n",
    "xg_targets = [\"home_win\", \"draw\", \"away_win\"]\n",
    "\n",
    "xg_reg.fit(train[xg_features], train[xg_targets])\n",
    "\n",
    "# make predictions\n",
    "xg_predictions_raw =  xg_reg.predict(test[xg_features])\n",
    "xg_predictions = pd.DataFrame(xg_predictions_raw, columns = xg_targets)\n",
    "# tranlate result probs into result\n",
    "xg_predictions[\"result\"] = xg_predictions[[\"home_win\", \"away_win\", \"draw\"]].idxmax(axis=1)\n",
    "\n",
    "# generate report\n",
    "XG_Report = metrics.classification_report(test[\"result\"], xg_predictions[\"result\"])\n",
    "print(XG_Report)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"10\" halign=\"left\">Train Data</th>\n",
       "      <th colspan=\"4\" halign=\"left\">Prediction</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>home_team</th>\n",
       "      <th>away_team</th>\n",
       "      <th>ELO_home</th>\n",
       "      <th>ELO_away</th>\n",
       "      <th>neutral</th>\n",
       "      <th>fGoals_home</th>\n",
       "      <th>aGoals_home</th>\n",
       "      <th>fGoals_away</th>\n",
       "      <th>aGoals_away</th>\n",
       "      <th>result</th>\n",
       "      <th>home_win</th>\n",
       "      <th>draw</th>\n",
       "      <th>away_win</th>\n",
       "      <th>result</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Gabon</td>\n",
       "      <td>Burkina Faso</td>\n",
       "      <td>1123.572383</td>\n",
       "      <td>1082.654529</td>\n",
       "      <td>False</td>\n",
       "      <td>117</td>\n",
       "      <td>140</td>\n",
       "      <td>125</td>\n",
       "      <td>172</td>\n",
       "      <td>draw</td>\n",
       "      <td>0.576545</td>\n",
       "      <td>0.257931</td>\n",
       "      <td>0.187953</td>\n",
       "      <td>home_win</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kuwait</td>\n",
       "      <td>Lebanon</td>\n",
       "      <td>1152.753141</td>\n",
       "      <td>956.913306</td>\n",
       "      <td>False</td>\n",
       "      <td>340</td>\n",
       "      <td>224</td>\n",
       "      <td>43</td>\n",
       "      <td>96</td>\n",
       "      <td>home_win</td>\n",
       "      <td>0.682081</td>\n",
       "      <td>0.222097</td>\n",
       "      <td>0.118393</td>\n",
       "      <td>home_win</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>Togo</td>\n",
       "      <td>1124.190310</td>\n",
       "      <td>1040.581562</td>\n",
       "      <td>True</td>\n",
       "      <td>127</td>\n",
       "      <td>169</td>\n",
       "      <td>169</td>\n",
       "      <td>220</td>\n",
       "      <td>draw</td>\n",
       "      <td>0.442463</td>\n",
       "      <td>0.205268</td>\n",
       "      <td>0.356331</td>\n",
       "      <td>home_win</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Uganda</td>\n",
       "      <td>Tanzania</td>\n",
       "      <td>1125.830547</td>\n",
       "      <td>967.326526</td>\n",
       "      <td>False</td>\n",
       "      <td>563</td>\n",
       "      <td>437</td>\n",
       "      <td>318</td>\n",
       "      <td>451</td>\n",
       "      <td>home_win</td>\n",
       "      <td>0.808721</td>\n",
       "      <td>0.228151</td>\n",
       "      <td>0.059168</td>\n",
       "      <td>home_win</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>South Africa</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>1154.135644</td>\n",
       "      <td>1338.097182</td>\n",
       "      <td>False</td>\n",
       "      <td>91</td>\n",
       "      <td>60</td>\n",
       "      <td>412</td>\n",
       "      <td>293</td>\n",
       "      <td>draw</td>\n",
       "      <td>0.192838</td>\n",
       "      <td>0.300800</td>\n",
       "      <td>0.515032</td>\n",
       "      <td>away_win</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Botswana</td>\n",
       "      <td>Ivory Coast</td>\n",
       "      <td>874.408752</td>\n",
       "      <td>1262.039697</td>\n",
       "      <td>False</td>\n",
       "      <td>25</td>\n",
       "      <td>94</td>\n",
       "      <td>379</td>\n",
       "      <td>282</td>\n",
       "      <td>draw</td>\n",
       "      <td>0.023373</td>\n",
       "      <td>0.189668</td>\n",
       "      <td>0.738871</td>\n",
       "      <td>away_win</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Togo</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>1044.123140</td>\n",
       "      <td>1162.512984</td>\n",
       "      <td>False</td>\n",
       "      <td>169</td>\n",
       "      <td>220</td>\n",
       "      <td>235</td>\n",
       "      <td>209</td>\n",
       "      <td>away_win</td>\n",
       "      <td>0.437576</td>\n",
       "      <td>0.260351</td>\n",
       "      <td>0.386548</td>\n",
       "      <td>home_win</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Guinea-Bissau</td>\n",
       "      <td>Guinea</td>\n",
       "      <td>984.975654</td>\n",
       "      <td>1088.836903</td>\n",
       "      <td>False</td>\n",
       "      <td>70</td>\n",
       "      <td>97</td>\n",
       "      <td>326</td>\n",
       "      <td>302</td>\n",
       "      <td>home_win</td>\n",
       "      <td>0.500019</td>\n",
       "      <td>0.238040</td>\n",
       "      <td>0.207155</td>\n",
       "      <td>home_win</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Italy</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>1479.033528</td>\n",
       "      <td>1380.371907</td>\n",
       "      <td>False</td>\n",
       "      <td>862</td>\n",
       "      <td>514</td>\n",
       "      <td>656</td>\n",
       "      <td>423</td>\n",
       "      <td>home_win</td>\n",
       "      <td>0.521809</td>\n",
       "      <td>0.232172</td>\n",
       "      <td>0.227009</td>\n",
       "      <td>home_win</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>Peru</td>\n",
       "      <td>895.606049</td>\n",
       "      <td>1096.102453</td>\n",
       "      <td>False</td>\n",
       "      <td>76</td>\n",
       "      <td>241</td>\n",
       "      <td>439</td>\n",
       "      <td>510</td>\n",
       "      <td>draw</td>\n",
       "      <td>0.198062</td>\n",
       "      <td>0.245300</td>\n",
       "      <td>0.566684</td>\n",
       "      <td>away_win</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Train Data                                                              \\\n",
       "       home_team     away_team     ELO_home     ELO_away neutral fGoals_home   \n",
       "0          Gabon  Burkina Faso  1123.572383  1082.654529   False         117   \n",
       "1         Kuwait       Lebanon  1152.753141   956.913306   False         340   \n",
       "2   Sierra Leone          Togo  1124.190310  1040.581562    True         127   \n",
       "3         Uganda      Tanzania  1125.830547   967.326526   False         563   \n",
       "4   South Africa       Nigeria  1154.135644  1338.097182   False          91   \n",
       "5       Botswana   Ivory Coast   874.408752  1262.039697   False          25   \n",
       "6           Togo      Zimbabwe  1044.123140  1162.512984   False         169   \n",
       "7  Guinea-Bissau        Guinea   984.975654  1088.836903   False          70   \n",
       "8          Italy        Mexico  1479.033528  1380.371907   False         862   \n",
       "9      Venezuela          Peru   895.606049  1096.102453   False          76   \n",
       "\n",
       "                                                Prediction            \\\n",
       "  aGoals_home fGoals_away aGoals_away    result   home_win      draw   \n",
       "0         140         125         172      draw   0.576545  0.257931   \n",
       "1         224          43          96  home_win   0.682081  0.222097   \n",
       "2         169         169         220      draw   0.442463  0.205268   \n",
       "3         437         318         451  home_win   0.808721  0.228151   \n",
       "4          60         412         293      draw   0.192838  0.300800   \n",
       "5          94         379         282      draw   0.023373  0.189668   \n",
       "6         220         235         209  away_win   0.437576  0.260351   \n",
       "7          97         326         302  home_win   0.500019  0.238040   \n",
       "8         514         656         423  home_win   0.521809  0.232172   \n",
       "9         241         439         510      draw   0.198062  0.245300   \n",
       "\n",
       "                       \n",
       "   away_win    result  \n",
       "0  0.187953  home_win  \n",
       "1  0.118393  home_win  \n",
       "2  0.356331  home_win  \n",
       "3  0.059168  home_win  \n",
       "4  0.515032  away_win  \n",
       "5  0.738871  away_win  \n",
       "6  0.386548  home_win  \n",
       "7  0.207155  home_win  \n",
       "8  0.227009  home_win  \n",
       "9  0.566684  away_win  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# visualize prediction (not needed)\n",
    "xg_predictions.reset_index(drop=True, inplace=True)\n",
    "test.reset_index(drop=True, inplace=True)\n",
    "a = pd.concat([test[[\"home_team\", \"away_team\", \"ELO_home\", \"ELO_away\", \"neutral\", \"fGoals_home\", \"aGoals_home\", \"fGoals_away\", \"aGoals_away\", \"result\"]], xg_predictions], axis=1, keys=[\"Train Data\", \"Prediction\"])\n",
    "a.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Goals prediction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MLP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       0.99      1.00      1.00      1256\n",
      "           1       0.58      0.25      0.35      1830\n",
      "           2       0.27      0.60      0.37      1056\n",
      "           3       0.20      0.19      0.19       553\n",
      "           4       0.13      0.09      0.11       273\n",
      "           5       0.12      0.05      0.07       140\n",
      "           6       0.03      0.01      0.02        73\n",
      "           7       0.00      0.00      0.00        43\n",
      "           8       0.00      0.00      0.00        18\n",
      "           9       0.11      0.05      0.07        19\n",
      "          10       0.00      0.00      0.00         8\n",
      "          12       0.00      0.00      0.00         2\n",
      "          17       0.00      0.00      0.00         0\n",
      "          18       0.00      0.00      0.00         0\n",
      "          19       0.00      0.00      0.00         0\n",
      "          22       0.00      0.00      0.00         0\n",
      "          29       0.00      0.00      0.00         0\n",
      "\n",
      "   micro avg       0.47      0.47      0.47      5271\n",
      "   macro avg       0.14      0.13      0.13      5271\n",
      "weighted avg       0.52      0.47      0.46      5271\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\toolbase\\python\\3.11.4.2\\Lib\\site-packages\\sklearn\\neural_network\\_multilayer_perceptron.py:546: ConvergenceWarning: lbfgs failed to converge (status=1):\n",
      "STOP: TOTAL NO. of ITERATIONS REACHED LIMIT.\n",
      "\n",
      "Increase the number of iterations (max_iter) or scale the data as shown in:\n",
      "    https://scikit-learn.org/stable/modules/preprocessing.html\n",
      "  self.n_iter_ = _check_optimize_result(\"lbfgs\", opt_res, self.max_iter)\n",
      "c:\\toolbase\\python\\3.11.4.2\\Lib\\site-packages\\sklearn\\metrics\\_classification.py:1469: UndefinedMetricWarning: Recall and F-score are ill-defined and being set to 0.0 in labels with no true samples. Use `zero_division` parameter to control this behavior.\n",
      "  _warn_prf(average, modifier, msg_start, len(result))\n",
      "c:\\toolbase\\python\\3.11.4.2\\Lib\\site-packages\\sklearn\\metrics\\_classification.py:1469: UndefinedMetricWarning: Recall and F-score are ill-defined and being set to 0.0 in labels with no true samples. Use `zero_division` parameter to control this behavior.\n",
      "  _warn_prf(average, modifier, msg_start, len(result))\n",
      "c:\\toolbase\\python\\3.11.4.2\\Lib\\site-packages\\sklearn\\metrics\\_classification.py:1469: UndefinedMetricWarning: Recall and F-score are ill-defined and being set to 0.0 in labels with no true samples. Use `zero_division` parameter to control this behavior.\n",
      "  _warn_prf(average, modifier, msg_start, len(result))\n"
     ]
    }
   ],
   "source": [
    "# fitting\n",
    "from sklearn.neural_network import MLPRegressor\n",
    "\n",
    "train = train_raw\n",
    "#train  = train.drop(train[train[\"draw\"] == True].index)\n",
    "\n",
    "#middle: mlpG_features = [\"home_team_code_sc\", \"away_team_code_sc\", \"ELO_home_sc\", \"ELO_away_sc\", \"neutral\", \"fGoals_home_sc\", \"aGoals_home_sc\", \"fGoals_away_sc\", \"aGoals_away_sc\", \"result_code\"]\n",
    "#worst: mlpG_features = [\"home_team_code_sc\", \"away_team_code_sc\", \"ELO_home_sc\", \"ELO_away_sc\", \"neutral\", \"fGoals_p_match_home_sc\", \"aGoals_p_match_home_sc\", \"fGoals_p_match_away_sc\", \"aGoals_p_match_away_sc\"]\n",
    "mlpG_features = [   \"home_team_code_sc\", \n",
    "                    \"away_team_code_sc\", \n",
    "                    \"ELO_home_sc\", \n",
    "                    \"ELO_away_sc\", \n",
    "                    \"fGoals_home_sc\", \n",
    "                    \"aGoals_home_sc\", \n",
    "                    \"fGoals_away_sc\", \n",
    "                    \"aGoals_away_sc\", \n",
    "                    \"result_code\", \n",
    "                    \"fGoals_p_match_home_sc\", \n",
    "                    \"aGoals_p_match_home_sc\", \n",
    "                    \"fGoals_p_match_away_sc\", \n",
    "                    \"aGoals_p_match_away_sc\",\n",
    "                    \"t2t_fGoals_home_sc\",\n",
    "                    \"t2t_fGoals_away_sc\",\n",
    "                    \"t2t_fGoals_p_match_home_sc\", \n",
    "                    \"t2t_fGoals_p_match_away_sc\"]\n",
    "mlpG_targets = [\"goal_diff\", \"loser_score\"]\n",
    "\n",
    "mlpG = MLPRegressor(solver=\"lbfgs\", alpha=1e-5, hidden_layer_sizes=(100, 50, 5), random_state=1)\n",
    "\n",
    "mlpG.fit(train[mlpG_features], train[mlpG_targets])\n",
    "\n",
    "# make predictions\n",
    "mlpG_predictions_raw = mlpG.predict(test[mlpG_features])\n",
    "mlpG_predictions = pd.DataFrame(mlpG_predictions_raw, columns = mlpG_targets)\n",
    "\n",
    "# round values and set to int\n",
    "mlpG_predictions = mlpG_predictions.round().astype(int)\n",
    "# if there is ever any negative value set it to 0\n",
    "mlpG_predictions[mlpG_predictions < 0] = 0\n",
    "\n",
    "\n",
    "# convert goal_diff and loser_score to actual score\n",
    "\n",
    "# first attach result to prediction\n",
    "mlpG_predictions.reset_index(drop=True, inplace=True)\n",
    "mlpG_predictions[\"result\"] = test[\"result\"] \n",
    "\n",
    "# if home_win then home_score = loser_score+goal_diff and away_score = loser_score\n",
    "mlpG_predictions.loc[mlpG_predictions[\"result\"]  == \"home_win\", \"home_score\"] =  mlpG_predictions[\"loser_score\"] + mlpG_predictions[\"goal_diff\"]\n",
    "mlpG_predictions.loc[mlpG_predictions[\"result\"]  == \"home_win\", \"away_score\"] =  mlpG_predictions[\"loser_score\"]\n",
    "# if away_win vice versa\n",
    "mlpG_predictions.loc[mlpG_predictions[\"result\"]  == \"away_win\", \"home_score\"] =  mlpG_predictions[\"loser_score\"]\n",
    "mlpG_predictions.loc[mlpG_predictions[\"result\"]  == \"away_win\", \"away_score\"] =  mlpG_predictions[\"loser_score\"] + mlpG_predictions[\"goal_diff\"]\n",
    "# if draw then both scores are loser_score\n",
    "mlpG_predictions.loc[mlpG_predictions[\"result\"]  == \"draw\", \"home_score\"] =  mlpG_predictions[\"loser_score\"]\n",
    "mlpG_predictions.loc[mlpG_predictions[\"result\"]  == \"draw\", \"away_score\"] =  mlpG_predictions[\"loser_score\"]\n",
    "\n",
    "# report\n",
    "MLPG_Report_GoalDiff = metrics.classification_report(test[\"goal_diff\"], mlpG_predictions[\"goal_diff\"], labels=np.unique(mlpG_predictions[\"goal_diff\"]))\n",
    "print(MLPG_Report_GoalDiff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# LOSER SCORE\n",
    "MLPG_Report_LoserScore = metrics.classification_report(test[\"loser_score\"], mlpG_predictions[\"loser_score\"], labels=np.unique(mlpG_predictions[\"loser_score\"]))\n",
    "print(MLPG_Report_LoserScore)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">Train Data</th>\n",
       "      <th colspan=\"5\" halign=\"left\">Prediction</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>home_team</th>\n",
       "      <th>away_team</th>\n",
       "      <th>ELO_home</th>\n",
       "      <th>ELO_away</th>\n",
       "      <th>neutral</th>\n",
       "      <th>home_score</th>\n",
       "      <th>away_score</th>\n",
       "      <th>result</th>\n",
       "      <th>goal_diff</th>\n",
       "      <th>loser_score</th>\n",
       "      <th>result</th>\n",
       "      <th>home_score</th>\n",
       "      <th>away_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Gabon</td>\n",
       "      <td>Burkina Faso</td>\n",
       "      <td>1123.572383</td>\n",
       "      <td>1082.654529</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>draw</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>draw</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kuwait</td>\n",
       "      <td>Lebanon</td>\n",
       "      <td>1152.753141</td>\n",
       "      <td>956.913306</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>home_win</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>home_win</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Sierra Leone</td>\n",
       "      <td>Togo</td>\n",
       "      <td>1124.190310</td>\n",
       "      <td>1040.581562</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>draw</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>draw</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Uganda</td>\n",
       "      <td>Tanzania</td>\n",
       "      <td>1125.830547</td>\n",
       "      <td>967.326526</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>home_win</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>home_win</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>South Africa</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>1154.135644</td>\n",
       "      <td>1338.097182</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>draw</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>draw</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Botswana</td>\n",
       "      <td>Ivory Coast</td>\n",
       "      <td>874.408752</td>\n",
       "      <td>1262.039697</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>draw</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>draw</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Togo</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>1044.123140</td>\n",
       "      <td>1162.512984</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>away_win</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>away_win</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Guinea-Bissau</td>\n",
       "      <td>Guinea</td>\n",
       "      <td>984.975654</td>\n",
       "      <td>1088.836903</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>home_win</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>home_win</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Italy</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>1479.033528</td>\n",
       "      <td>1380.371907</td>\n",
       "      <td>False</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>home_win</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>home_win</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>Peru</td>\n",
       "      <td>895.606049</td>\n",
       "      <td>1096.102453</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>draw</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>draw</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Train Data                                                             \\\n",
       "       home_team     away_team     ELO_home     ELO_away neutral home_score   \n",
       "0          Gabon  Burkina Faso  1123.572383  1082.654529   False          1   \n",
       "1         Kuwait       Lebanon  1152.753141   956.913306   False          2   \n",
       "2   Sierra Leone          Togo  1124.190310  1040.581562    True          0   \n",
       "3         Uganda      Tanzania  1125.830547   967.326526   False          2   \n",
       "4   South Africa       Nigeria  1154.135644  1338.097182   False          0   \n",
       "5       Botswana   Ivory Coast   874.408752  1262.039697   False          0   \n",
       "6           Togo      Zimbabwe  1044.123140  1162.512984   False          1   \n",
       "7  Guinea-Bissau        Guinea   984.975654  1088.836903   False          2   \n",
       "8          Italy        Mexico  1479.033528  1380.371907   False          2   \n",
       "9      Venezuela          Peru   895.606049  1096.102453   False          0   \n",
       "\n",
       "                       Prediction                                              \n",
       "  away_score    result  goal_diff loser_score    result home_score away_score  \n",
       "0          1      draw          0           1      draw        1.0        1.0  \n",
       "1          0  home_win          3           0  home_win        3.0        0.0  \n",
       "2          0      draw          0           1      draw        1.0        1.0  \n",
       "3          0  home_win          2           1  home_win        3.0        1.0  \n",
       "4          0      draw          0           1      draw        1.0        1.0  \n",
       "5          0      draw          0           1      draw        1.0        1.0  \n",
       "6          2  away_win          2           0  away_win        0.0        2.0  \n",
       "7          1  home_win          2           0  home_win        2.0        0.0  \n",
       "8          0  home_win          2           0  home_win        2.0        0.0  \n",
       "9          0      draw          0           1      draw        1.0        1.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# visualize prediction\n",
    "mlpG_predictions.reset_index(drop=True, inplace=True)\n",
    "test.reset_index(drop=True, inplace=True)\n",
    "b = pd.concat([test[[\"home_team\", \"away_team\", \"ELO_home\", \"ELO_away\", \"neutral\", \"home_score\", \"away_score\", \"result\"]], mlpG_predictions], axis=1, keys=[\"Train Data\", \"Prediction\"])\n",
    "b.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### RF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.ensemble import RandomForestClassifier\n",
    "\n",
    "train = train_raw\n",
    "rfG = RandomForestClassifier(n_estimators=100, min_samples_split=5, random_state=1)\n",
    "\n",
    "rfG_features = [   \"home_team_code_sc\", \n",
    "                    \"away_team_code_sc\", \n",
    "                    \"ELO_home_sc\", \n",
    "                    \"ELO_away_sc\", \n",
    "                    \"fGoals_home_sc\", \n",
    "                    \"aGoals_home_sc\", \n",
    "                    \"fGoals_away_sc\", \n",
    "                    \"aGoals_away_sc\", \n",
    "                    \"result_code\", \n",
    "                    \"fGoals_p_match_home_sc\", \n",
    "                    \"aGoals_p_match_home_sc\", \n",
    "                    \"fGoals_p_match_away_sc\", \n",
    "                    \"aGoals_p_match_away_sc\",\n",
    "                    \"t2t_fGoals_home_sc\",\n",
    "                    \"t2t_fGoals_away_sc\",\n",
    "                    \"t2t_fGoals_p_match_home_sc\", \n",
    "                    \"t2t_fGoals_p_match_away_sc\"]\n",
    "rfG_targets = [\"goal_diff\", \"loser_score\"]\n",
    "\n",
    "rfG.fit(train[rfG_features], train[rfG_targets])\n",
    "\n",
    "# make predictions\n",
    "rfG_predictions_raw =  rfG.predict(test[rfG_features])\n",
    "rfG_predictions = pd.DataFrame(rfG_predictions_raw, columns = rfG_targets)\n",
    "\n",
    "# round values and set to int\n",
    "rfG_predictions = rfG_predictions.round().astype(int)\n",
    "# if there is ever any negative value set it to 0\n",
    "rfG_predictions[rfG_predictions < 0] = 0\n",
    "\n",
    "# convert goal_diff and loser_score to actual score\n",
    "\n",
    "# first attach result to prediction\n",
    "rfG_predictions.reset_index(drop=True, inplace=True)\n",
    "rfG_predictions[\"result\"] = test[\"result\"] \n",
    "\n",
    "# if home_win then home_score = loser_score+goal_diff and away_score = loser_score\n",
    "rfG_predictions.loc[rfG_predictions[\"result\"]  == \"home_win\", \"home_score\"] =  rfG_predictions[\"loser_score\"] + rfG_predictions[\"goal_diff\"]\n",
    "rfG_predictions.loc[rfG_predictions[\"result\"]  == \"home_win\", \"away_score\"] =  rfG_predictions[\"loser_score\"]\n",
    "# if away_win vice versa\n",
    "rfG_predictions.loc[rfG_predictions[\"result\"]  == \"away_win\", \"home_score\"] =  rfG_predictions[\"loser_score\"]\n",
    "rfG_predictions.loc[rfG_predictions[\"result\"]  == \"away_win\", \"away_score\"] =  rfG_predictions[\"loser_score\"] + rfG_predictions[\"goal_diff\"]\n",
    "# if draw then both scores are loser_score\n",
    "rfG_predictions.loc[rfG_predictions[\"result\"]  == \"draw\", \"home_score\"] =  rfG_predictions[\"loser_score\"]\n",
    "rfG_predictions.loc[rfG_predictions[\"result\"]  == \"draw\", \"away_score\"] =  rfG_predictions[\"loser_score\"]\n",
    "\n",
    "# generate report\n",
    "rfG_Report_GoalDiff = metrics.classification_report(test[\"goal_diff\"], rfG_predictions[\"goal_diff\"], labels=np.unique(rfG_predictions[\"goal_diff\"]))\n",
    "print(rfG_Report_GoalDiff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# LOSER SCORE\n",
    "rfG_Report_LoserScore = metrics.classification_report(test[\"loser_score\"], rfG_predictions[\"loser_score\"], labels=np.unique(rfG_predictions[\"loser_score\"]))\n",
    "print(rfG_Report_LoserScore)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### XGBoost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\toolbase\\python\\3.11.4.2\\Lib\\site-packages\\xgboost\\core.py:160: UserWarning: [12:11:07] WARNING: C:\\buildkite-agent\\builds\\buildkite-windows-cpu-autoscaling-group-i-0b3782d1791676daf-1\\xgboost\\xgboost-ci-windows\\src\\objective\\regression_obj.cu:209: reg:linear is now deprecated in favor of reg:squarederror.\n",
      "  warnings.warn(smsg, UserWarning)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       1.00      0.97      0.98      1216\n",
      "           1       0.61      0.14      0.23      1878\n",
      "           2       0.28      0.76      0.41      1066\n",
      "           3       0.21      0.18      0.19       563\n",
      "           4       0.17      0.09      0.12       267\n",
      "           5       0.23      0.10      0.14       141\n",
      "           6       0.09      0.04      0.05        53\n",
      "           7       0.07      0.03      0.04        40\n",
      "           8       0.00      0.00      0.00        18\n",
      "           9       0.00      0.00      0.00        14\n",
      "          10       0.00      0.00      0.00         5\n",
      "          14       0.00      0.00      0.00         0\n",
      "\n",
      "   micro avg       0.45      0.46      0.46      5261\n",
      "   macro avg       0.22      0.19      0.18      5261\n",
      "weighted avg       0.54      0.46      0.42      5261\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\toolbase\\python\\3.11.4.2\\Lib\\site-packages\\sklearn\\metrics\\_classification.py:1469: UndefinedMetricWarning: Recall and F-score are ill-defined and being set to 0.0 in labels with no true samples. Use `zero_division` parameter to control this behavior.\n",
      "  _warn_prf(average, modifier, msg_start, len(result))\n",
      "c:\\toolbase\\python\\3.11.4.2\\Lib\\site-packages\\sklearn\\metrics\\_classification.py:1469: UndefinedMetricWarning: Recall and F-score are ill-defined and being set to 0.0 in labels with no true samples. Use `zero_division` parameter to control this behavior.\n",
      "  _warn_prf(average, modifier, msg_start, len(result))\n",
      "c:\\toolbase\\python\\3.11.4.2\\Lib\\site-packages\\sklearn\\metrics\\_classification.py:1469: UndefinedMetricWarning: Recall and F-score are ill-defined and being set to 0.0 in labels with no true samples. Use `zero_division` parameter to control this behavior.\n",
      "  _warn_prf(average, modifier, msg_start, len(result))\n"
     ]
    }
   ],
   "source": [
    "import xgboost as xgb\n",
    "\n",
    "train = train_raw\n",
    "xgG = xgb.XGBRegressor(objective ='reg:linear', colsample_bytree = 0.3, learning_rate = 0.1,\n",
    "                max_depth = 6, alpha = 10, n_estimators = 200)\n",
    "\n",
    "xgG_features = [   \"home_team_code_sc\", \n",
    "                    \"away_team_code_sc\", \n",
    "                    \"ELO_home_sc\", \n",
    "                    \"ELO_away_sc\", \n",
    "                    \"fGoals_home_sc\", \n",
    "                    \"aGoals_home_sc\", \n",
    "                    \"fGoals_away_sc\", \n",
    "                    \"aGoals_away_sc\", \n",
    "                    \"result_code\", \n",
    "                    \"fGoals_p_match_home_sc\", \n",
    "                    \"aGoals_p_match_home_sc\", \n",
    "                    \"fGoals_p_match_away_sc\", \n",
    "                    \"aGoals_p_match_away_sc\",\n",
    "                    \"t2t_fGoals_home_sc\",\n",
    "                    \"t2t_fGoals_away_sc\",\n",
    "                    \"t2t_fGoals_p_match_home_sc\", \n",
    "                    \"t2t_fGoals_p_match_away_sc\"]\n",
    "xgG_targets = [\"goal_diff\", \"loser_score\"]\n",
    "\n",
    "xgG.fit(train[xgG_features], train[xgG_targets])\n",
    "\n",
    "# make predictions\n",
    "xgG_predictions_raw =  xgG.predict(test[xgG_features])\n",
    "xgG_predictions = pd.DataFrame(xgG_predictions_raw, columns = xgG_targets)\n",
    "\n",
    "# round values and set to int\n",
    "xgG_predictions = xgG_predictions.round().astype(int)\n",
    "# if there is ever any negative value set it to 0\n",
    "xgG_predictions[xgG_predictions < 0] = 0\n",
    "\n",
    "# convert goal_diff and loser_score to actual score\n",
    "\n",
    "# first attach result to prediction\n",
    "xgG_predictions.reset_index(drop=True, inplace=True)\n",
    "xgG_predictions[\"result\"] = test[\"result\"] \n",
    "\n",
    "# if home_win then home_score = loser_score+goal_diff and away_score = loser_score\n",
    "xgG_predictions.loc[xgG_predictions[\"result\"]  == \"home_win\", \"home_score\"] =  xgG_predictions[\"loser_score\"] + xgG_predictions[\"goal_diff\"]\n",
    "xgG_predictions.loc[xgG_predictions[\"result\"]  == \"home_win\", \"away_score\"] =  xgG_predictions[\"loser_score\"]\n",
    "# if away_win vice versa\n",
    "xgG_predictions.loc[xgG_predictions[\"result\"]  == \"away_win\", \"home_score\"] =  xgG_predictions[\"loser_score\"]\n",
    "xgG_predictions.loc[xgG_predictions[\"result\"]  == \"away_win\", \"away_score\"] =  xgG_predictions[\"loser_score\"] + xgG_predictions[\"goal_diff\"]\n",
    "# if draw then both scores are loser_score\n",
    "xgG_predictions.loc[xgG_predictions[\"result\"]  == \"draw\", \"home_score\"] =  xgG_predictions[\"loser_score\"]\n",
    "xgG_predictions.loc[xgG_predictions[\"result\"]  == \"draw\", \"away_score\"] =  xgG_predictions[\"loser_score\"]\n",
    "\n",
    "# generate report\n",
    "xgG_Report_GoalDiff = metrics.classification_report(test[\"goal_diff\"], xgG_predictions[\"goal_diff\"], labels=np.unique(xgG_predictions[\"goal_diff\"]))\n",
    "print(xgG_Report_GoalDiff)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              precision    recall  f1-score   support\n",
      "\n",
      "           0       0.65      0.55      0.60      3012\n",
      "           1       0.38      0.59      0.47      1777\n",
      "\n",
      "   micro avg       0.51      0.57      0.54      4789\n",
      "   macro avg       0.52      0.57      0.53      4789\n",
      "weighted avg       0.55      0.57      0.55      4789\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# LOSER SCORE\n",
    "xgG_Report_LoserScore = metrics.classification_report(test[\"loser_score\"], xgG_predictions[\"loser_score\"], labels=np.unique(xgG_predictions[\"loser_score\"]))\n",
    "print(xgG_Report_LoserScore)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# TODOs\n",
    "\n",
    "* [X] fill tournament type dict with suitable values\n",
    "* [/] maybe weight the more recent games more?\n",
    "* [X] plot ELO graphs and compare to ELO database for plausibility\n",
    "* [_] classify draw and compare\n",
    "* [X] try out XG Boost  https://xgboost.readthedocs.io/en/latest/python/index.html\n",
    "* [X] Estimation: Result: Homewin/draw/Homelose, Goal difference, Goals of loser\n",
    "* [_] Need to Scale the Data Before splitting into test and train -> best practice to male scaling only based on traindata\n",
    "* [_] Backwards calculation of ELO values from known 2022 values\n",
    "* [_] Remove all non-neutral matches from training data since they are not relvant for WM2022\n",
    "\n",
    "\n",
    "Ideas for features:\n",
    "* [X] Goal difference\n",
    "* [X] How many goals has a team achived versus the exact opponent on average\n",
    "* [X] How many wins etc.. has a team achived versus the exact opponent on average -> Probability\n",
    "* [X] fGoals/aGoals average per game as feature\n",
    "* [_] Use FIFA ranking (only after 1993 available -> still 26k matches) https://teams.microsoft.com/l/message/19:y6u7WdyvEHElLDbrRf470PJO9wMuxvbKqz14mIfXqCg1@thread.tacv2/1668699442716?tenantId=0ae51e19-07c8-4e4b-bb6d-648ee58410f4&groupId=99c50a52-2e25-476d-af08-f96444fdb78c&parentMessageId=1668699442716&teamName=AI%20Match%20-%20The%20Bosch%20AI%20World%20Cup%20Challenge&channelName=General&createdTime=1668699442716&allowXTenantAccess=false\n",
    "* [_] Use FIFA player information (available since 2017 -> 5159 matches still)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Observations\n",
    "\n",
    "Overall:\n",
    "* Including Goal estimations (difference, offset) in one MLP together with result makes predictions of way worse -> therfore splitting into two steps\n",
    "* Droping data before 1993 leads to no performance loss\n",
    "* Even droping data before 2017 leads to no noticable performance loss\n",
    "\n",
    "Result prediction:\n",
    "* RF and XG is way faster in learning than MLP, results however comparable\n",
    "* removing team codes does not make predictions worse\n",
    "* switching to f/aGoals_p_match does (very) slightly improve estimation compared to absolute f/aGoals\n",
    "* f/aGoals etc. does only have a very small influence on result pred\n",
    "* unskewed data improves performance a lot!\n",
    "\n",
    "Goal pred\n",
    "* using f/aGoals or f/aGolas_ration does not make a big difference, including both makes a slight improvement (~1% on diff and 7% on loser_goals)\n",
    "* tried unskewing data based on goal_diff but doesn't help estimation in the same way as for result\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Make a prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def make_a_prediction(ht, at, neutral, algo_result, algo_goals):\n",
    "\n",
    "    gameData= pd.DataFrame()\n",
    "    # derive data\n",
    "    gameData.loc[0, \"neutral\"] = int(neutral)\n",
    "    gameData.loc[0, \"home_team_code\"] = team_stats.at[ht, \"team_code\"]\n",
    "    gameData.loc[0, \"away_team_code\"] = team_stats.at[at, \"team_code\"]\n",
    "    gameData.loc[0, \"ELO_home\"] = team_stats.at[ht, \"ELO\"]\n",
    "    gameData.loc[0, \"ELO_away\"] = team_stats.at[at, \"ELO\"]\n",
    "    gameData.loc[0, \"fGoals_home\"] = team_stats.at[ht, \"fGoals\"]\n",
    "    gameData.loc[0, \"aGoals_home\"] = team_stats.at[ht, \"aGoals\"]\n",
    "    gameData.loc[0, \"fGoals_away\"] = team_stats.at[at, \"fGoals\"]\n",
    "    gameData.loc[0, \"aGoals_away\"] = team_stats.at[at, \"aGoals\"]\n",
    "    gameData.loc[0, \"fGoals_p_match_home\"] = team_stats.at[ht, \"fGoals_p_match\"]\n",
    "    gameData.loc[0, \"aGoals_p_match_home\"] = team_stats.at[ht, \"aGoals_p_match\"]\n",
    "    gameData.loc[0, \"fGoals_p_match_away\"] = team_stats.at[at, \"fGoals_p_match\"]\n",
    "    gameData.loc[0, \"aGoals_p_match_away\"] = team_stats.at[at, \"aGoals_p_match\"]\n",
    "    # add t2t data\n",
    "    gameData.loc[0, [   \"t2t_fGoals_home\",\n",
    "                        \"t2t_fGoals_away\",\n",
    "                        \"t2t_fGoals_p_match_home\",\n",
    "                        \"t2t_fGoals_p_match_away\",\n",
    "                        \"t2t_num_wins_home\",\n",
    "                        \"t2t_num_wins_away\",\n",
    "                        \"t2t_num_draw\",\n",
    "                        \"t2t_p_wins_home\",\n",
    "                        \"t2t_p_wins_away\",\n",
    "                        \"t2t_p_draw\"]] = get_t2t_data(ht, at, \"2033-01-01\")\n",
    "\n",
    "    # scale\n",
    "    gameData[[  \"fGoals_home_sc\", \n",
    "                \"aGoals_home_sc\", \n",
    "                \"fGoals_away_sc\", \n",
    "                \"aGoals_away_sc\",\n",
    "                \"t2t_fGoals_home_sc\",\n",
    "                \"t2t_fGoals_away_sc\" ]] = goal_scaler.transform(gameData[[\"fGoals_home\", \"aGoals_home\", \"fGoals_away\", \"aGoals_away\", \"t2t_fGoals_home\", \"t2t_fGoals_away\"]])\n",
    "\n",
    "    gameData[[  \"fGoals_p_match_home_sc\", \n",
    "                \"aGoals_p_match_home_sc\", \n",
    "                \"fGoals_p_match_away_sc\", \n",
    "                \"aGoals_p_match_away_sc\",\n",
    "                \"t2t_fGoals_p_match_home_sc\",\n",
    "                \"t2t_fGoals_p_match_away_sc\" ]] = goal_pm_scaler.transform(gameData[[\"fGoals_p_match_home\", \"aGoals_p_match_home\", \"fGoals_p_match_away\", \"aGoals_p_match_away\",\"t2t_fGoals_p_match_home\", \"t2t_fGoals_p_match_away\"]])\n",
    "\n",
    "    gameData[[  \"ELO_home_sc\", \n",
    "                \"ELO_away_sc\"]] = ELO_scaler.transform(gameData[[\"ELO_home\", \"ELO_away\"]])\n",
    "\n",
    "    # gameData[[   \"tournament_code_sc\" ]] = tournament_scaler.transform(gameData[[\"tournament_code\" ]])\n",
    "\n",
    "    gameData[[  \"home_team_code_sc\", \n",
    "                \"away_team_code_sc\"]] = team_scaler.transform(gameData[[\"home_team_code\", \"away_team_code\"]])\n",
    "\n",
    "    gameData[[  \"t2t_num_wins_home_sc\", \n",
    "                \"t2t_num_wins_away_sc\",\n",
    "                \"t2t_num_draw_sc\" ]] = num_win_scaler.transform(gameData[[\"t2t_num_wins_home\", \"t2t_num_wins_away\", \"t2t_num_draw\"]])\n",
    "\n",
    "    ######### GAME RESULT PREDICTION ################\n",
    "\n",
    "    # algo_dependend start\n",
    "    if (algo_result == \"rf\"):\n",
    "        game_redictions_raw =  rf.predict(gameData[rf_features])\n",
    "        game_predictions = pd.DataFrame(game_redictions_raw, columns = rf_targets)\n",
    "    elif (algo_result == \"xg\"):\n",
    "        game_redictions_raw =  xg_reg.predict(gameData[xg_features])\n",
    "        game_predictions = pd.DataFrame(game_redictions_raw, columns = xg_targets)\n",
    "        # translate result probs into result\n",
    "        game_predictions[\"result\"] = game_predictions[[\"home_win\", \"away_win\", \"draw\"]].idxmax(axis=1) # needed \n",
    "    elif (algo_result == \"mlp\"):\n",
    "        game_redictions_raw =  mlp.predict(gameData[mlp_features])\n",
    "        game_predictions = pd.DataFrame(game_redictions_raw, columns = mlp_targets)\n",
    "        # translate result probs into result\n",
    "        game_predictions[\"result\"] = game_predictions[[\"home_win\", \"away_win\", \"draw\"]].idxmax(axis=1) # needed \n",
    "    else:\n",
    "        print(\"ERROR: algo_result unknown\")\n",
    "    \n",
    "    # algo_dependend end\n",
    "\n",
    "    print(\"--------------- Result  --------------------\")\n",
    "    # print(\"Prediction of game result: \" + xg_game_ppredictions[\"result\"][0])\n",
    "    if (game_predictions[\"result\"][0] == \"home_win\"):\n",
    "        print(ht + \" wins!\")\n",
    "        gameData[\"result_code\"] = 2  # magic number,how to retrive?\n",
    "    elif (game_predictions[\"result\"][0] == \"away_win\"):\n",
    "        print(at + \" wins!\")\n",
    "        gameData[\"result_code\"] = 0  # magic number,how to retrive?\n",
    "    else:\n",
    "        print(\"draw!\")\n",
    "        gameData[\"result_code\"] = 1  # magic number,how to retrive?\n",
    "\n",
    "\n",
    "    ######### GOAL PREDICTION ################\n",
    "\n",
    "    # add result from previus step into input vector \n",
    "    gameData[\"result_code\"] = game_predictions[\"result\"].astype(\"category\").cat.codes\n",
    "\n",
    "    # algo_dependend start\n",
    "    if (algo_goals == \"mlp\"):\n",
    "        game_goals_prediction_raw = mlpG.predict(gameData[mlpG_features])\n",
    "        # postprocess\n",
    "        game_goals_prediction = pd.DataFrame(game_goals_prediction_raw, columns = mlpG_targets)\n",
    "    elif (algo_goals == \"xg\"):\n",
    "        game_goals_prediction_raw = xgG_reg.predict(gameData[xgG_features])\n",
    "        # postprocess\n",
    "        game_goals_prediction = pd.DataFrame(game_goals_prediction_raw, columns = xgG_targets)\n",
    "    elif (algo_goals == \"rf\"):\n",
    "        game_goals_prediction_raw =  rfG.predict(gameData[rfG_features])\n",
    "        game_goals_prediction = pd.DataFrame(game_goals_prediction_raw, columns = rfG_targets)\n",
    "    else:\n",
    "        print(\"ERROR: algo_goals unknown\")\n",
    "    # algo_dependend end\n",
    "\n",
    "    \n",
    "    # round values and set to int\n",
    "    game_goals_prediction = game_goals_prediction.round().astype(int)\n",
    "    # if there is ever any negative value set it to 0\n",
    "    game_goals_prediction[game_goals_prediction < 0] = 0\n",
    "\n",
    "    if (game_predictions[\"result\"][0] == \"home_win\"):\n",
    "        goals_away = int(game_goals_prediction[\"loser_score\"])\n",
    "        goals_home = int(game_goals_prediction[\"loser_score\"] + game_goals_prediction[\"goal_diff\"])\n",
    "    elif (game_predictions[\"result\"][0] == \"away_win\"):\n",
    "        goals_away = int(game_goals_prediction[\"loser_score\"] + game_goals_prediction[\"goal_diff\"])\n",
    "        goals_home = int(game_goals_prediction[\"loser_score\"])\n",
    "    else:\n",
    "        goals_away = int(game_goals_prediction[\"loser_score\"])\n",
    "        goals_home = int(game_goals_prediction[\"loser_score\"])\n",
    "\n",
    "\n",
    "    print(\"Goals\", ht, \":\", goals_home)\n",
    "    print(\"Goals\", at, \":\", goals_away)\n",
    "    print(\"\\n\")\n",
    "    print(\"--------------- Input Data --------------------\")\n",
    "    print(\"ELO\", ht, \":\", gameData[\"ELO_home\"][0])\n",
    "    print(\"ELO\", at, \":\", gameData[\"ELO_away\"][0])\n",
    "    print(\"t2t_fGoals_p_match\", ht, \":\", gameData[\"t2t_fGoals_p_match_home\"][0])\n",
    "    print(\"t2t_fGoals_p_match\", at, \":\", gameData[\"t2t_fGoals_p_match_away\"][0])\n",
    "    print(\"t2t_num_wins\", ht, \":\", gameData[\"t2t_num_wins_home\"][0])\n",
    "    print(\"t2t_num_wins\", at, \":\", gameData[\"t2t_num_wins_away\"][0])\n",
    "    print(\"t2t_num_draw :\", gameData[\"t2t_num_draw\"][0])\n",
    "\n",
    "    gameData.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------------- Result  --------------------\n",
      "England wins!\n",
      "Goals Spain : 1\n",
      "Goals England : 2\n",
      "\n",
      "\n",
      "--------------- Input Data --------------------\n",
      "ELO Spain : 1633.7069709552934\n",
      "ELO England : 1649.4202101649344\n",
      "t2t_fGoals_p_match Spain : 1.1851851851851851\n",
      "t2t_fGoals_p_match England : 1.6666666666666667\n",
      "t2t_num_wins Spain : 10.0\n",
      "t2t_num_wins England : 13.0\n",
      "t2t_num_draw : 4.0\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\dar7si\\AppData\\Local\\Temp\\ipykernel_30148\\3103502340.py:122: FutureWarning: Calling int on a single element Series is deprecated and will raise a TypeError in the future. Use int(ser.iloc[0]) instead\n",
      "  goals_away = int(game_goals_prediction[\"loser_score\"] + game_goals_prediction[\"goal_diff\"])\n",
      "C:\\Users\\dar7si\\AppData\\Local\\Temp\\ipykernel_30148\\3103502340.py:123: FutureWarning: Calling int on a single element Series is deprecated and will raise a TypeError in the future. Use int(ser.iloc[0]) instead\n",
      "  goals_home = int(game_goals_prediction[\"loser_score\"])\n"
     ]
    }
   ],
   "source": [
    "####### USER INPUT ##########\n",
    "ht = \"Spain\"\n",
    "at = \"England\"\n",
    "neutral = True\n",
    "####### USER INPUT END ##########\n",
    "\n",
    "make_a_prediction(ht, at, neutral, algo_result=\"xg\", algo_goals=\"mlp\")\n",
    "# make_a_prediction(at, ht, neutral, algo_result=\"xg\", algo_goals=\"mlp\")\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.7 ('base')",
   "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.11.4"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "deb4792152b8b9767403eeef0a1b0f34b83d442136ccee9184cd7d1131f09aa0"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}