notebooks/intl_01_world_cup_2018.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# International data - World Cup 2018 predictions\n",
    "\n",
    "Ported from Excel - see original [here](models/World cup 2018 CALC.xlsx)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Input data on team fixtures and performance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.177892Z",
     "iopub.status.busy": "2021-01-25T21:39:27.176896Z",
     "iopub.status.idle": "2021-01-25T21:39:27.519271Z",
     "shell.execute_reply": "2021-01-25T21:39:27.519271Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.526220Z",
     "iopub.status.busy": "2021-01-25T21:39:27.526220Z",
     "iopub.status.idle": "2021-01-25T21:39:27.529212Z",
     "shell.execute_reply": "2021-01-25T21:39:27.529212Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Russia']"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HOME_TEAMS = [\"Russia\"]\n",
    "HOME_TEAMS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.540183Z",
     "iopub.status.busy": "2021-01-25T21:39:27.535198Z",
     "iopub.status.idle": "2021-01-25T21:39:27.551161Z",
     "shell.execute_reply": "2021-01-25T21:39:27.551161Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 64 entries, 1 to 88\n",
      "Data columns (total 4 columns):\n",
      " #   Column  Non-Null Count  Dtype \n",
      "---  ------  --------------  ----- \n",
      " 0   Date    64 non-null     object\n",
      " 1   Time    64 non-null     object\n",
      " 2   Team1   64 non-null     object\n",
      " 3   Team2   64 non-null     object\n",
      "dtypes: object(4)\n",
      "memory usage: 2.5+ KB\n"
     ]
    }
   ],
   "source": [
    "fixtures = pd.read_csv(\"../data/raw/whs/whs_fix/whs_fix_wcm_2018.csv\")\n",
    "fixtures.columns = [\"Date\", \"Time\", \"ignore_1\", \"Team1\", \"ignore_2\", \"Team2\", \"ignore_3\"]\n",
    "fixtures.drop(columns=[\"ignore_1\", \"ignore_2\", \"ignore_3\"], inplace=True)\n",
    "fixtures.Date.fillna(method=\"ffill\", inplace=True)\n",
    "fixtures.dropna(axis=\"index\", subset=[\"Team1\"], inplace=True)\n",
    "fixtures.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.560161Z",
     "iopub.status.busy": "2021-01-25T21:39:27.559165Z",
     "iopub.status.idle": "2021-01-25T21:39:27.568110Z",
     "shell.execute_reply": "2021-01-25T21:39:27.568110Z"
    }
   },
   "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>Time</th>\n",
       "      <th>Team1</th>\n",
       "      <th>Team2</th>\n",
       "      <th>HomeAdv1</th>\n",
       "      <th>HomeAdv2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Thursday, Jun 14 2018</td>\n",
       "      <td>16:00</td>\n",
       "      <td>Russia</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Friday, Jun 15 2018</td>\n",
       "      <td>13:00</td>\n",
       "      <td>Egypt</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Friday, Jun 15 2018</td>\n",
       "      <td>16:00</td>\n",
       "      <td>Morocco</td>\n",
       "      <td>Iran</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Friday, Jun 15 2018</td>\n",
       "      <td>19:00</td>\n",
       "      <td>Portugal</td>\n",
       "      <td>Spain</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Saturday, Jun 16 2018</td>\n",
       "      <td>11:00</td>\n",
       "      <td>France</td>\n",
       "      <td>Australia</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Date   Time     Team1         Team2  HomeAdv1  HomeAdv2\n",
       "1  Thursday, Jun 14 2018  16:00    Russia  Saudi Arabia         1         0\n",
       "3    Friday, Jun 15 2018  13:00     Egypt       Uruguay         0         0\n",
       "4    Friday, Jun 15 2018  16:00   Morocco          Iran         0         0\n",
       "5    Friday, Jun 15 2018  19:00  Portugal         Spain         0         0\n",
       "7  Saturday, Jun 16 2018  11:00    France     Australia         0         0"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fixtures[\"HomeAdv1\"] = 0\n",
    "fixtures[\"HomeAdv2\"] = 0\n",
    "fixtures.loc[fixtures.Team1.isin(HOME_TEAMS), \"HomeAdv1\"] = 1\n",
    "fixtures.loc[fixtures.Team2.isin(HOME_TEAMS), \"HomeAdv2\"] = 1\n",
    "\n",
    "fixtures.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.578082Z",
     "iopub.status.busy": "2021-01-25T21:39:27.577114Z",
     "iopub.status.idle": "2021-01-25T21:39:27.580110Z",
     "shell.execute_reply": "2021-01-25T21:39:27.580110Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 100 entries, 0 to 99\n",
      "Data columns (total 4 columns):\n",
      " #   Column     Non-Null Count  Dtype \n",
      "---  ------     --------------  ----- \n",
      " 0   Team       100 non-null    object\n",
      " 1   EloRank    100 non-null    int64 \n",
      " 2   EloRating  100 non-null    int64 \n",
      " 3   FIFARank   100 non-null    object\n",
      "dtypes: int64(2), object(2)\n",
      "memory usage: 3.2+ KB\n"
     ]
    }
   ],
   "source": [
    "elo = pd.read_csv(\"../data/raw/wkp/wkp_elo/World_Football_Elo_Ratings.csv\")\n",
    "elo.columns = [\"Team\", \"EloRank\", \"ignore_1\", \"ignore_2\", \"ignore_3\", \"EloRating\", \"FIFARank\", \"ignore_4\", \"ignore_5\"]\n",
    "elo.drop(columns=[\"ignore_1\", \"ignore_2\", \"ignore_3\", \"ignore_4\", \"ignore_5\"], inplace=True)\n",
    "elo.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.589053Z",
     "iopub.status.busy": "2021-01-25T21:39:27.588054Z",
     "iopub.status.idle": "2021-01-25T21:39:27.591047Z",
     "shell.execute_reply": "2021-01-25T21:39:27.591047Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Team</th>\n",
       "      <th>EloRank</th>\n",
       "      <th>EloRating</th>\n",
       "      <th>FIFARank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>1</td>\n",
       "      <td>2131</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Germany</td>\n",
       "      <td>2</td>\n",
       "      <td>2092</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Spain</td>\n",
       "      <td>3</td>\n",
       "      <td>2049</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>France</td>\n",
       "      <td>4</td>\n",
       "      <td>1987</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>5</td>\n",
       "      <td>1985</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Team  EloRank  EloRating FIFARank\n",
       "0     Brazil        1       2131        2\n",
       "1    Germany        2       2092        1\n",
       "2      Spain        3       2049        8\n",
       "3     France        4       1987        7\n",
       "4  Argentina        5       1985        5"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "elo.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.595037Z",
     "iopub.status.busy": "2021-01-25T21:39:27.594069Z",
     "iopub.status.idle": "2021-01-25T21:39:27.604012Z",
     "shell.execute_reply": "2021-01-25T21:39:27.604012Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 32 entries, 0 to 31\n",
      "Data columns (total 2 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   Team              32 non-null     object\n",
      " 1   QualifyGoalsRank  32 non-null     int64 \n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 640.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "qualifying = pd.read_csv(\"../data/raw/fif/Qualifying_goals.csv\")\n",
    "qualifying = qualifying[[\"Team\", \"Rank\"]]\n",
    "qualifying.columns = [\"Team\", \"QualifyGoalsRank\"]\n",
    "qualifying.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.609997Z",
     "iopub.status.busy": "2021-01-25T21:39:27.608998Z",
     "iopub.status.idle": "2021-01-25T21:39:27.611992Z",
     "shell.execute_reply": "2021-01-25T21:39:27.611992Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Team</th>\n",
       "      <th>QualifyGoalsRank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Russia</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Egypt</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Team  QualifyGoalsRank\n",
       "0        Russia                16\n",
       "1  Saudi Arabia                 6\n",
       "2         Egypt                26\n",
       "3       Uruguay                11\n",
       "4      Portugal                 5"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qualifying.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.624957Z",
     "iopub.status.busy": "2021-01-25T21:39:27.624957Z",
     "iopub.status.idle": "2021-01-25T21:39:27.634931Z",
     "shell.execute_reply": "2021-01-25T21:39:27.635955Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 64 entries, 0 to 63\n",
      "Data columns (total 14 columns):\n",
      " #   Column             Non-Null Count  Dtype \n",
      "---  ------             --------------  ----- \n",
      " 0   Date               64 non-null     object\n",
      " 1   Time               64 non-null     object\n",
      " 2   Team1              64 non-null     object\n",
      " 3   Team2              64 non-null     object\n",
      " 4   HomeAdv1           64 non-null     int64 \n",
      " 5   HomeAdv2           64 non-null     int64 \n",
      " 6   EloRank1           64 non-null     int64 \n",
      " 7   EloRating1         64 non-null     int64 \n",
      " 8   FIFARank1          64 non-null     object\n",
      " 9   EloRank2           64 non-null     int64 \n",
      " 10  EloRating2         64 non-null     int64 \n",
      " 11  FIFARank2          64 non-null     object\n",
      " 12  QualifyGoalsRank1  64 non-null     int64 \n",
      " 13  QualifyGoalsRank2  64 non-null     int64 \n",
      "dtypes: int64(8), object(6)\n",
      "memory usage: 7.5+ KB\n"
     ]
    }
   ],
   "source": [
    "inputData = fixtures.merge(elo, how=\"inner\", left_on=\"Team1\", right_on=\"Team\", suffixes=[\"\",\"1\"])\\\n",
    "    .drop(columns=[\"Team\"])\\\n",
    "    .merge(elo, how=\"inner\", left_on=\"Team2\", right_on=\"Team\", suffixes=[\"1\",\"2\"])\\\n",
    "    .merge(qualifying, how=\"inner\", left_on=\"Team1\", right_on=\"Team\")\\\n",
    "    .drop(columns=[\"Team_x\", \"Team_y\"])\\\n",
    "    .merge(qualifying, how=\"inner\", left_on=\"Team2\", right_on=\"Team\")\\\n",
    "    .drop(columns=[\"Team\"])\\\n",
    "    .rename(columns={\"QualifyGoalsRank_x\": \"QualifyGoalsRank1\", \"QualifyGoalsRank_y\": \"QualifyGoalsRank2\"})\n",
    "inputData\n",
    "inputData.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Build model and make predictions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.642909Z",
     "iopub.status.busy": "2021-01-25T21:39:27.642909Z",
     "iopub.status.idle": "2021-01-25T21:39:27.646899Z",
     "shell.execute_reply": "2021-01-25T21:39:27.645949Z"
    }
   },
   "outputs": [],
   "source": [
    "def make_predictions(X_input, p_goal_weight, p_goal_boost):\n",
    "    \"\"\"Take input data and parameters and make predictions\n",
    "\n",
    "    Args:\n",
    "        X_input: Input dataframe with columns\n",
    "            ['Date', 'Time', 'Team1', 'Team2', 'HomeAdv1', 'HomeAdv2', 'EloRank1', 'EloRating1', \n",
    "            'FIFARank1', 'EloRank2', 'EloRating2', 'FIFARank2', 'QualifyGoalsRank1', 'QualifyGoalsRank2']\n",
    "        p_goal_weight: Float value weight to apply to calculate raw goal differences\n",
    "        p_goal_boost: Float value rank threshold for applying goal boost\n",
    "\n",
    "    Returns:\n",
    "        X_full: Output dataframe with columns\n",
    "            ['Date', 'Time', 'Team1', 'Team2', 'HomeAdv1', 'HomeAdv2', 'EloRank1', 'EloRating1', \n",
    "            'FIFARank1', 'EloRank2', 'EloRating2', 'FIFARank2', 'QualifyGoalsRank1', 'QualifyGoalsRank2', \n",
    "            'EloRatingDiff', 'EloRatingDiffWithHomeAdv', 'WinExpectency1Square', 'WinExpectency1',\n",
    "            'RawGoalDiff', 'RawGoalDiffAbs', 'EitherWins', 'QualifyGoalsRankAvg', 'ApplyGoalBoost', \n",
    "            'Goals1', 'Goals2', 'GoalDiff', 'GoalDiffAbs', 'GoalTotal']\n",
    "    \"\"\"\n",
    "    X_full = X_input.copy()\n",
    "    X_full[\"EloRatingDiff\"] = X_full[\"EloRating1\"] - X_full[\"EloRating2\"]\n",
    "    X_full[\"EloRatingDiffWithHomeAdv\"] = X_full[\"EloRatingDiff\"] + (100 * X_full.HomeAdv1) - (100 * X_full.HomeAdv2)\n",
    "    X_full[\"WinExpectency1Square\"] = (10**((-X_full.EloRatingDiffWithHomeAdv)/400))+1\n",
    "    X_full[\"WinExpectency1\"] = X_full[\"WinExpectency1Square\"]**-1\n",
    "    X_full[\"RawGoalDiff\"] = (p_goal_weight * (X_full.WinExpectency1 - 0.5)).round(0)\n",
    "    X_full[\"RawGoalDiffAbs\"] = X_full[\"RawGoalDiff\"].abs()\n",
    "    X_full[\"EitherWins\"] = 0\n",
    "    X_full.loc[X_full.RawGoalDiffAbs > 0, \"EitherWins\"] = 1\n",
    "    X_full[\"QualifyGoalsRankAvg\"] = (X_full[\"QualifyGoalsRank1\"] + X_full[\"QualifyGoalsRank2\"]) / 2\n",
    "    X_full[\"ApplyGoalBoost\"] = 0\n",
    "    X_full.loc[X_full.QualifyGoalsRankAvg <= p_goal_boost, \"ApplyGoalBoost\"] = 1\n",
    "    X_full[\"Goals1\"] = X_full[\"ApplyGoalBoost\"]\n",
    "    X_full.loc[X_full.RawGoalDiff > 0, \"Goals1\"] = X_full.RawGoalDiff + X_full.ApplyGoalBoost\n",
    "    X_full[\"Goals2\"] = X_full[\"ApplyGoalBoost\"]\n",
    "    X_full.loc[X_full.RawGoalDiff <= 0, \"Goals2\"] = X_full.ApplyGoalBoost - X_full.RawGoalDiff\n",
    "    X_full[\"GoalDiff\"] = X_full.Goals1 - X_full.Goals2\n",
    "    X_full[\"GoalDiffAbs\"] = X_full.GoalDiff.abs()\n",
    "    X_full[\"GoalTotal\"] = X_full.Goals1 + X_full.Goals2\n",
    "    \n",
    "    return X_full\n",
    "\n",
    "# make_predictions?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.650890Z",
     "iopub.status.busy": "2021-01-25T21:39:27.649892Z",
     "iopub.status.idle": "2021-01-25T21:39:27.653880Z",
     "shell.execute_reply": "2021-01-25T21:39:27.653880Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4.0, 19.0)"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "EXCEL_GOAL_WEIGHT = 4.\n",
    "EXCEL_GOAL_BOOST = 19.\n",
    "EXCEL_GOAL_WEIGHT, EXCEL_GOAL_BOOST"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.684800Z",
     "iopub.status.busy": "2021-01-25T21:39:27.678813Z",
     "iopub.status.idle": "2021-01-25T21:39:27.724715Z",
     "shell.execute_reply": "2021-01-25T21:39:27.723718Z"
    }
   },
   "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>count</th>\n",
       "      <th>unique</th>\n",
       "      <th>top</th>\n",
       "      <th>freq</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Date</th>\n",
       "      <td>64</td>\n",
       "      <td>25</td>\n",
       "      <td>Monday, Jun 25 2018</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Time</th>\n",
       "      <td>64</td>\n",
       "      <td>8</td>\n",
       "      <td>19:00</td>\n",
       "      <td>25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Team1</th>\n",
       "      <td>64</td>\n",
       "      <td>32</td>\n",
       "      <td>France</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Team2</th>\n",
       "      <td>64</td>\n",
       "      <td>32</td>\n",
       "      <td>England</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HomeAdv1</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.046875</td>\n",
       "      <td>0.213042</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HomeAdv2</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.03125</td>\n",
       "      <td>0.175368</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EloRank1</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.1562</td>\n",
       "      <td>15.9187</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>16.5</td>\n",
       "      <td>27</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EloRating1</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1864.58</td>\n",
       "      <td>137.747</td>\n",
       "      <td>1597</td>\n",
       "      <td>1751</td>\n",
       "      <td>1855</td>\n",
       "      <td>1967</td>\n",
       "      <td>2131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FIFARank1</th>\n",
       "      <td>64</td>\n",
       "      <td>32</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EloRank2</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>22.5625</td>\n",
       "      <td>16.9742</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>17</td>\n",
       "      <td>40</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EloRating2</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1832.72</td>\n",
       "      <td>127.319</td>\n",
       "      <td>1597</td>\n",
       "      <td>1714</td>\n",
       "      <td>1853</td>\n",
       "      <td>1935</td>\n",
       "      <td>2131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FIFARank2</th>\n",
       "      <td>64</td>\n",
       "      <td>32</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QualifyGoalsRank1</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14.7031</td>\n",
       "      <td>8.78975</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>14.5</td>\n",
       "      <td>21.25</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QualifyGoalsRank2</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.25</td>\n",
       "      <td>9.12871</td>\n",
       "      <td>1</td>\n",
       "      <td>9.5</td>\n",
       "      <td>17.5</td>\n",
       "      <td>26</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EloRatingDiff</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.8594</td>\n",
       "      <td>189.312</td>\n",
       "      <td>-361</td>\n",
       "      <td>-98</td>\n",
       "      <td>11</td>\n",
       "      <td>201.5</td>\n",
       "      <td>386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EloRatingDiffWithHomeAdv</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.4219</td>\n",
       "      <td>186.007</td>\n",
       "      <td>-361</td>\n",
       "      <td>-94.5</td>\n",
       "      <td>11</td>\n",
       "      <td>198.5</td>\n",
       "      <td>386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WinExpectency1Square</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.42217</td>\n",
       "      <td>1.63615</td>\n",
       "      <td>1.10839</td>\n",
       "      <td>1.31897</td>\n",
       "      <td>1.93866</td>\n",
       "      <td>2.72288</td>\n",
       "      <td>8.98914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WinExpectency1</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.539381</td>\n",
       "      <td>0.226661</td>\n",
       "      <td>0.111245</td>\n",
       "      <td>0.367262</td>\n",
       "      <td>0.515825</td>\n",
       "      <td>0.758166</td>\n",
       "      <td>0.902207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RawGoalDiff</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.109375</td>\n",
       "      <td>0.892956</td>\n",
       "      <td>-2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RawGoalDiffAbs</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.703125</td>\n",
       "      <td>0.554339</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EitherWins</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.65625</td>\n",
       "      <td>0.478714</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>QualifyGoalsRankAvg</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.9766</td>\n",
       "      <td>6.27633</td>\n",
       "      <td>4.5</td>\n",
       "      <td>11.375</td>\n",
       "      <td>15.25</td>\n",
       "      <td>21</td>\n",
       "      <td>28.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ApplyGoalBoost</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.703125</td>\n",
       "      <td>0.460493</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Goals1</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.10938</td>\n",
       "      <td>0.715302</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Goals2</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0.734631</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GoalDiff</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.109375</td>\n",
       "      <td>0.892956</td>\n",
       "      <td>-2</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GoalDiffAbs</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.703125</td>\n",
       "      <td>0.554339</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GoalTotal</th>\n",
       "      <td>64</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.10938</td>\n",
       "      <td>1.1425</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         count unique                  top freq      mean  \\\n",
       "Date                        64     25  Monday, Jun 25 2018    4       NaN   \n",
       "Time                        64      8                19:00   25       NaN   \n",
       "Team1                       64     32               France    5       NaN   \n",
       "Team2                       64     32              England    5       NaN   \n",
       "HomeAdv1                    64    NaN                  NaN  NaN  0.046875   \n",
       "HomeAdv2                    64    NaN                  NaN  NaN   0.03125   \n",
       "EloRank1                    64    NaN                  NaN  NaN   19.1562   \n",
       "EloRating1                  64    NaN                  NaN  NaN   1864.58   \n",
       "FIFARank1                   64     32                    7    5       NaN   \n",
       "EloRank2                    64    NaN                  NaN  NaN   22.5625   \n",
       "EloRating2                  64    NaN                  NaN  NaN   1832.72   \n",
       "FIFARank2                   64     32                   13    5       NaN   \n",
       "QualifyGoalsRank1           64    NaN                  NaN  NaN   14.7031   \n",
       "QualifyGoalsRank2           64    NaN                  NaN  NaN     17.25   \n",
       "EloRatingDiff               64    NaN                  NaN  NaN   31.8594   \n",
       "EloRatingDiffWithHomeAdv    64    NaN                  NaN  NaN   33.4219   \n",
       "WinExpectency1Square        64    NaN                  NaN  NaN   2.42217   \n",
       "WinExpectency1              64    NaN                  NaN  NaN  0.539381   \n",
       "RawGoalDiff                 64    NaN                  NaN  NaN  0.109375   \n",
       "RawGoalDiffAbs              64    NaN                  NaN  NaN  0.703125   \n",
       "EitherWins                  64    NaN                  NaN  NaN   0.65625   \n",
       "QualifyGoalsRankAvg         64    NaN                  NaN  NaN   15.9766   \n",
       "ApplyGoalBoost              64    NaN                  NaN  NaN  0.703125   \n",
       "Goals1                      64    NaN                  NaN  NaN   1.10938   \n",
       "Goals2                      64    NaN                  NaN  NaN         1   \n",
       "GoalDiff                    64    NaN                  NaN  NaN  0.109375   \n",
       "GoalDiffAbs                 64    NaN                  NaN  NaN  0.703125   \n",
       "GoalTotal                   64    NaN                  NaN  NaN   2.10938   \n",
       "\n",
       "                               std       min       25%       50%       75%  \\\n",
       "Date                           NaN       NaN       NaN       NaN       NaN   \n",
       "Time                           NaN       NaN       NaN       NaN       NaN   \n",
       "Team1                          NaN       NaN       NaN       NaN       NaN   \n",
       "Team2                          NaN       NaN       NaN       NaN       NaN   \n",
       "HomeAdv1                  0.213042         0         0         0         0   \n",
       "HomeAdv2                  0.175368         0         0         0         0   \n",
       "EloRank1                   15.9187         1         6      16.5        27   \n",
       "EloRating1                 137.747      1597      1751      1855      1967   \n",
       "FIFARank1                      NaN       NaN       NaN       NaN       NaN   \n",
       "EloRank2                   16.9742         1         8        17        40   \n",
       "EloRating2                 127.319      1597      1714      1853      1935   \n",
       "FIFARank2                      NaN       NaN       NaN       NaN       NaN   \n",
       "QualifyGoalsRank1          8.78975         1         8      14.5     21.25   \n",
       "QualifyGoalsRank2          9.12871         1       9.5      17.5        26   \n",
       "EloRatingDiff              189.312      -361       -98        11     201.5   \n",
       "EloRatingDiffWithHomeAdv   186.007      -361     -94.5        11     198.5   \n",
       "WinExpectency1Square       1.63615   1.10839   1.31897   1.93866   2.72288   \n",
       "WinExpectency1            0.226661  0.111245  0.367262  0.515825  0.758166   \n",
       "RawGoalDiff               0.892956        -2        -1         0         1   \n",
       "RawGoalDiffAbs            0.554339         0         0         1         1   \n",
       "EitherWins                0.478714         0         0         1         1   \n",
       "QualifyGoalsRankAvg        6.27633       4.5    11.375     15.25        21   \n",
       "ApplyGoalBoost            0.460493         0         0         1         1   \n",
       "Goals1                    0.715302         0         1         1         2   \n",
       "Goals2                    0.734631         0         1         1         1   \n",
       "GoalDiff                  0.892956        -2        -1         0         1   \n",
       "GoalDiffAbs               0.554339         0         0         1         1   \n",
       "GoalTotal                   1.1425         0         1         2         3   \n",
       "\n",
       "                               max  \n",
       "Date                           NaN  \n",
       "Time                           NaN  \n",
       "Team1                          NaN  \n",
       "Team2                          NaN  \n",
       "HomeAdv1                         1  \n",
       "HomeAdv2                         1  \n",
       "EloRank1                        63  \n",
       "EloRating1                    2131  \n",
       "FIFARank1                      NaN  \n",
       "EloRank2                        63  \n",
       "EloRating2                    2131  \n",
       "FIFARank2                      NaN  \n",
       "QualifyGoalsRank1               31  \n",
       "QualifyGoalsRank2               31  \n",
       "EloRatingDiff                  386  \n",
       "EloRatingDiffWithHomeAdv       386  \n",
       "WinExpectency1Square       8.98914  \n",
       "WinExpectency1            0.902207  \n",
       "RawGoalDiff                      2  \n",
       "RawGoalDiffAbs                   2  \n",
       "EitherWins                       1  \n",
       "QualifyGoalsRankAvg           28.5  \n",
       "ApplyGoalBoost                   1  \n",
       "Goals1                           3  \n",
       "Goals2                           3  \n",
       "GoalDiff                         2  \n",
       "GoalDiffAbs                      2  \n",
       "GoalTotal                        4  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = make_predictions(inputData, EXCEL_GOAL_WEIGHT, EXCEL_GOAL_BOOST)\n",
    "\n",
    "# data.iloc[:, -14:].head(5)\n",
    "data.describe(include=\"all\").T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.727684Z",
     "iopub.status.busy": "2021-01-25T21:39:27.727684Z",
     "iopub.status.idle": "2021-01-25T21:39:27.734689Z",
     "shell.execute_reply": "2021-01-25T21:39:27.734689Z"
    }
   },
   "outputs": [],
   "source": [
    "data[[\"Date\", \"Time\", \"Team1\", \"Team2\", \"Goals1\", \"Goals2\"]].to_csv(\"../data/interim/intl_01_predictions.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Evaluate predictions against historic trends"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.742644Z",
     "iopub.status.busy": "2021-01-25T21:39:27.741677Z",
     "iopub.status.idle": "2021-01-25T21:39:27.744682Z",
     "shell.execute_reply": "2021-01-25T21:39:27.744682Z"
    }
   },
   "outputs": [],
   "source": [
    "def compare_to_historic(X_full, show_comparison=True):\n",
    "    \"\"\"Take input data and parameters and make predictions\n",
    "\n",
    "    Args:\n",
    "        X_full: Full dataframe with columns\n",
    "            ['Date', 'Time', 'Team1', 'Team2', 'HomeAdv1', 'HomeAdv2', 'EloRank1', 'EloRating1', \n",
    "            'FIFARank1', 'EloRank2', 'EloRating2', 'FIFARank2', 'QualifyGoalsRank1', 'QualifyGoalsRank2', \n",
    "            'EloRatingDiff', 'EloRatingDiffWithHomeAdv', 'WinExpectency1Square', 'WinExpectency1',\n",
    "            'RawGoalDiff', 'RawGoalDiffAbs', 'EitherWins', 'QualifyGoalsRankAvg', 'ApplyGoalBoost', \n",
    "            'Goals1', 'Goals2', 'GoalDiff', 'GoalDiffAbs', 'GoalTotal']\n",
    "        show_comparison: Boolean whether to display historic comparison\n",
    "\n",
    "    Returns:\n",
    "        None\n",
    "    \"\"\"\n",
    "    \n",
    "    poisson = pd.read_csv(\"../data/raw/wkp/wkp_pds/Poisson_goals_World_Cup.csv\")\n",
    "    poisson.columns = [\"k\", \"P_of_k_goals\", \"ignore_1\", \"ignore_2\"]\n",
    "    poisson.drop(columns=[\"ignore_1\", \"ignore_2\"], inplace=True)\n",
    "    poisson[\"label\"] = \"% games \"+poisson.k.astype(str)+\" goals\"\n",
    "    poisson.set_index(\"label\", inplace=True)\n",
    "#     poisson.info()\n",
    "    \n",
    "    totals = pd.read_csv(\"../data/raw/fif/World_Cup_goals.csv\")\n",
    "    totals.columns = [\"Tournament\", \"NoOfTeams\", \"MatchesPlayed\", \"GoalsScored\", \"AverageGoals\", \"AverageAttendance\",\n",
    "                     \"Rolling\", \"ignore_1\", \"ignore_2\"]\n",
    "    totals.drop(columns=[\"ignore_1\", \"ignore_2\"], inplace=True)\n",
    "    totals.dropna(axis=\"index\", subset=[\"Tournament\"], inplace=True)\n",
    "#     totals.info()\n",
    "\n",
    "    historic = pd.concat([poisson.P_of_k_goals.T, totals.iloc[10, -1:]])\n",
    "    historic.rename({\"Rolling\": \"AverageGoalsPerGame\"}, inplace=True)\n",
    "    historic[\"% games drawn\"] = 314 / 1416\n",
    "    historic[\"% games won\"] = 1 - historic[\"% games drawn\"]\n",
    "#     historic\n",
    "    \n",
    "    predictions = pd.DataFrame([{\n",
    "        \"AverageGoalsPerGame\": X_full.GoalTotal.sum() / X_full.shape[0],\n",
    "        \"% games drawn\": X_full[X_full.EitherWins == 0].shape[0] / X_full.shape[0],\n",
    "        \"% games won\": X_full[X_full.EitherWins == 1].shape[0] / X_full.shape[0],\n",
    "    }])\n",
    "    for g in range(8):\n",
    "        predictions[\"% games \"+str(g)+\" goals\"] = X_full[X_full.GoalTotal == g].shape[0] / X_full.shape[0]\n",
    "#     predictions.info()\n",
    "\n",
    "    hist_vs_pred = pd.concat([pd.DataFrame(historic).T, predictions]).T\n",
    "    hist_vs_pred.columns = [\"Historic\", \"Predictions\"]\n",
    "    hist_vs_pred[\"%Diff\"] = 1.0\n",
    "    hist_vs_pred.loc[hist_vs_pred.Predictions > 0, \"%Diff\"] = \\\n",
    "        1 - (hist_vs_pred.loc[hist_vs_pred.Predictions > 0, \"Historic\"]\\\n",
    "            / hist_vs_pred.loc[hist_vs_pred.Predictions > 0, \"Predictions\"])\n",
    "    if show_comparison:\n",
    "        print(hist_vs_pred)\n",
    "    \n",
    "    return None\n",
    "\n",
    "# compare_to_historic?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.757603Z",
     "iopub.status.busy": "2021-01-25T21:39:27.757603Z",
     "iopub.status.idle": "2021-01-25T21:39:27.771575Z",
     "shell.execute_reply": "2021-01-25T21:39:27.771575Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                     Historic Predictions     %Diff\n",
      "% games 0 goals         0.082       0.125     0.344\n",
      "% games 1 goals         0.205    0.171875 -0.192727\n",
      "% games 2 goals         0.257     0.21875 -0.174857\n",
      "% games 3 goals         0.213      0.4375  0.513143\n",
      "% games 4 goals         0.133    0.046875  -1.83733\n",
      "% games 5 goals         0.067           0         1\n",
      "% games 6 goals         0.028           0         1\n",
      "% games 7 goals          0.01           0         1\n",
      "AverageGoalsPerGame       2.5     2.10938 -0.185185\n",
      "% games drawn        0.221751     0.34375  0.354905\n",
      "% games won          0.778249     0.65625 -0.185903\n"
     ]
    }
   ],
   "source": [
    "compare_to_historic(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Compare predictions to actual results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.776554Z",
     "iopub.status.busy": "2021-01-25T21:39:27.775587Z",
     "iopub.status.idle": "2021-01-25T21:39:27.871308Z",
     "shell.execute_reply": "2021-01-25T21:39:27.871308Z"
    }
   },
   "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>Time</th>\n",
       "      <th>Team1</th>\n",
       "      <th>Team2</th>\n",
       "      <th>Actual1</th>\n",
       "      <th>Actual2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Thursday, Jun 14 2018</td>\n",
       "      <td>16:00</td>\n",
       "      <td>Russia</td>\n",
       "      <td>Saudi Arabia</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Friday, Jun 15 2018</td>\n",
       "      <td>13:00</td>\n",
       "      <td>Egypt</td>\n",
       "      <td>Uruguay</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Friday, Jun 15 2018</td>\n",
       "      <td>16:00</td>\n",
       "      <td>Morocco</td>\n",
       "      <td>Iran</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Friday, Jun 15 2018</td>\n",
       "      <td>19:00</td>\n",
       "      <td>Portugal</td>\n",
       "      <td>Spain</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Saturday, Jun 16 2018</td>\n",
       "      <td>11:00</td>\n",
       "      <td>France</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Date   Time     Team1         Team2  Actual1  Actual2\n",
       "1  Thursday, Jun 14 2018  16:00    Russia  Saudi Arabia      5.0      0.0\n",
       "3    Friday, Jun 15 2018  13:00     Egypt       Uruguay      0.0      1.0\n",
       "4    Friday, Jun 15 2018  16:00   Morocco          Iran      0.0      1.0\n",
       "5    Friday, Jun 15 2018  19:00  Portugal         Spain      3.0      3.0\n",
       "7  Saturday, Jun 16 2018  11:00    France     Australia      2.0      1.0"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = pd.read_excel(\"../models/World cup 2018 CALC.xlsx\", sheet_name=\"Fixtures_and_calcs\")\n",
    "results = results.iloc[:, [0,1,3,5,32,33]]\n",
    "results.columns = [\"Date\", \"Time\", \"Team1\", \"Team2\", \"Actual1\", \"Actual2\"]\n",
    "results.Date.fillna(method=\"ffill\", inplace=True)\n",
    "results[\"Time\"] = results[\"Time\"].astype(str).str[:5]\n",
    "results.dropna(axis=\"index\", subset=[\"Team1\"], inplace=True)\n",
    "\n",
    "# results.info()\n",
    "results.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.879280Z",
     "iopub.status.busy": "2021-01-25T21:39:27.879280Z",
     "iopub.status.idle": "2021-01-25T21:39:27.883293Z",
     "shell.execute_reply": "2021-01-25T21:39:27.882297Z"
    }
   },
   "outputs": [],
   "source": [
    "def compare_to_actual(X_full, X_results, show_comparison=True):\n",
    "    \"\"\"Takes full data with predictions and actual results to compare\n",
    "\n",
    "    Args:\n",
    "        X_full: Full dataframe with columns\n",
    "            ['Date', 'Time', 'Team1', 'Team2', 'HomeAdv1', 'HomeAdv2', 'EloRank1', 'EloRating1', \n",
    "            'FIFARank1', 'EloRank2', 'EloRating2', 'FIFARank2', 'QualifyGoalsRank1', 'QualifyGoalsRank2', \n",
    "            'EloRatingDiff', 'EloRatingDiffWithHomeAdv', 'WinExpectency1Square', 'WinExpectency1',\n",
    "            'RawGoalDiff', 'RawGoalDiffAbs', 'EitherWins', 'QualifyGoalsRankAvg', 'ApplyGoalBoost', \n",
    "            'Goals1', 'Goals2', 'GoalDiff', 'GoalDiffAbs', 'GoalTotal']\n",
    "        X_results: Results dataframe with columns\n",
    "            ['Date', 'Time', 'Team1', 'Team2', 'Actual1', 'Actual2']\n",
    "        show_comparison: Boolean whether to display historic comparison\n",
    "\n",
    "    Returns:\n",
    "        percentage_points: Float value percentage of available points achieved by predictions\n",
    "    \"\"\"\n",
    "    \n",
    "    compare = X_full[[\"Date\", \"Time\", \"Team1\", \"Team2\", \"Goals1\", \"Goals2\"]]\\\n",
    "        .merge(X_results, how=\"inner\", on=[\"Date\", \"Time\", \"Team1\", \"Team2\"])\n",
    "    compare.rename(columns={\"Goals1\": \"Prediction1\", \"Goals2\": \"Prediction2\"}, inplace=True)\n",
    "\n",
    "    compare[\"Prediction2\"] = compare.Prediction2.astype(float)\n",
    "\n",
    "    compare[\"CorrectResult\"] = 0\n",
    "    compare.loc[((compare.Prediction1 > compare.Prediction2) & (compare.Actual1 > compare.Actual2))\n",
    "                | ((compare.Prediction1 == compare.Prediction2) & (compare.Actual1 == compare.Actual2))\n",
    "                | ((compare.Prediction1 < compare.Prediction2) & (compare.Actual1 < compare.Actual2))\n",
    "                , \"CorrectResult\"] = 1\n",
    "\n",
    "    compare[\"CorrectGoalDiff\"] = 0\n",
    "    compare.loc[(compare.Prediction1 - compare.Prediction2 == compare.Actual1 - compare.Actual2), \"CorrectGoalDiff\"] = 1\n",
    "\n",
    "    compare[\"CorrectScore\"] = 0\n",
    "    compare.loc[(compare.Prediction1 == compare.Actual1) & (compare.Prediction2 == compare.Actual2), \"CorrectScore\"] = 1\n",
    "\n",
    "    compare[\"PointsScored\"] = compare.CorrectResult + compare.CorrectGoalDiff + compare.CorrectScore\n",
    "\n",
    "#     compare.tail(10)\n",
    "\n",
    "    compare[[\"CorrectResult\", \"CorrectGoalDiff\", \"CorrectScore\", \"PointsScored\"]].sum()\n",
    "\n",
    "    matches_played = compare.shape[0]\n",
    "#     matches_played\n",
    "\n",
    "    pred_vs_actual = pd.concat([compare[[\"CorrectResult\", \"CorrectGoalDiff\", \"CorrectScore\", \"PointsScored\"]].sum(),\n",
    "              pd.Series({\"CorrectResult\": matches_played, \"CorrectGoalDiff\": matches_played, \"CorrectScore\": matches_played, \"PointsScored\": 3 * matches_played})\n",
    "              ], axis=1)\n",
    "    pred_vs_actual.columns = [\"Predictions\", \"Maximum\"]\n",
    "    pred_vs_actual[\"% of Maximum\"] = pred_vs_actual.Predictions / pred_vs_actual.Maximum\n",
    "    if show_comparison:\n",
    "        print(pred_vs_actual)\n",
    "    \n",
    "    percentage_points = pred_vs_actual.loc[\"PointsScored\" , \"% of Maximum\"]\n",
    "    \n",
    "    return percentage_points\n",
    "\n",
    "# compare_to_actual?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.897233Z",
     "iopub.status.busy": "2021-01-25T21:39:27.897233Z",
     "iopub.status.idle": "2021-01-25T21:39:27.900225Z",
     "shell.execute_reply": "2021-01-25T21:39:27.901222Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                 Predictions  Maximum  % of Maximum\n",
      "CorrectResult             34       64      0.531250\n",
      "CorrectGoalDiff           21       64      0.328125\n",
      "CorrectScore               5       64      0.078125\n",
      "PointsScored              60      192      0.312500\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.3125"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "compare_to_actual(data, results)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Improve the model?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.904238Z",
     "iopub.status.busy": "2021-01-25T21:39:27.903240Z",
     "iopub.status.idle": "2021-01-25T21:39:27.906232Z",
     "shell.execute_reply": "2021-01-25T21:39:27.906232Z"
    }
   },
   "outputs": [],
   "source": [
    "import itertools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.910225Z",
     "iopub.status.busy": "2021-01-25T21:39:27.910225Z",
     "iopub.status.idle": "2021-01-25T21:39:27.913208Z",
     "shell.execute_reply": "2021-01-25T21:39:27.913208Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "99"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "weight_options = [1., 2., 3., 4., 5., 6., 7., 8., 9.]\n",
    "boost_options = [14., 15., 16., 17., 18., 19., 20., 21., 22., 23., 24.]\n",
    "\n",
    "param_grid = list(itertools.product(weight_options, boost_options))\n",
    "len(param_grid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:27.926155Z",
     "iopub.status.busy": "2021-01-25T21:39:27.915185Z",
     "iopub.status.idle": "2021-01-25T21:39:31.513607Z",
     "shell.execute_reply": "2021-01-25T21:39:31.513607Z"
    }
   },
   "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>Parameters</th>\n",
       "      <th>%PointsScored</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>(4.0, 16.0)</td>\n",
       "      <td>0.322917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>(4.0, 24.0)</td>\n",
       "      <td>0.322917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>(4.0, 23.0)</td>\n",
       "      <td>0.322917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>(4.0, 14.0)</td>\n",
       "      <td>0.322917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>(4.0, 15.0)</td>\n",
       "      <td>0.322917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>(4.0, 22.0)</td>\n",
       "      <td>0.322917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>(4.0, 17.0)</td>\n",
       "      <td>0.317708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>(4.0, 21.0)</td>\n",
       "      <td>0.317708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>(4.0, 18.0)</td>\n",
       "      <td>0.317708</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>(4.0, 20.0)</td>\n",
       "      <td>0.317708</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Parameters  %PointsScored\n",
       "35  (4.0, 16.0)       0.322917\n",
       "43  (4.0, 24.0)       0.322917\n",
       "42  (4.0, 23.0)       0.322917\n",
       "33  (4.0, 14.0)       0.322917\n",
       "34  (4.0, 15.0)       0.322917\n",
       "41  (4.0, 22.0)       0.322917\n",
       "36  (4.0, 17.0)       0.317708\n",
       "40  (4.0, 21.0)       0.317708\n",
       "37  (4.0, 18.0)       0.317708\n",
       "39  (4.0, 20.0)       0.317708"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "scoring = []\n",
    "for params in param_grid:\n",
    "    df = make_predictions(inputData, params[0], params[1])\n",
    "    compare_to_historic(df, show_comparison=False)\n",
    "    pp = compare_to_actual(df, results, show_comparison=False)\n",
    "    scoring.append({\n",
    "        \"Parameters\": params,\n",
    "        \"%PointsScored\": pp,\n",
    "    })\n",
    "\n",
    "pd.DataFrame(scoring).sort_values(by=\"%PointsScored\", ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:31.528566Z",
     "iopub.status.busy": "2021-01-25T21:39:31.522613Z",
     "iopub.status.idle": "2021-01-25T21:39:31.564470Z",
     "shell.execute_reply": "2021-01-25T21:39:31.565499Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                     Historic Predictions     %Diff\n",
      "% games 0 goals         0.082    0.140625  0.416889\n",
      "% games 1 goals         0.205    0.265625  0.228235\n",
      "% games 2 goals         0.257     0.21875 -0.174857\n",
      "% games 3 goals         0.213     0.34375  0.380364\n",
      "% games 4 goals         0.133     0.03125    -3.256\n",
      "% games 5 goals         0.067           0         1\n",
      "% games 6 goals         0.028           0         1\n",
      "% games 7 goals          0.01           0         1\n",
      "AverageGoalsPerGame       2.5     1.85938 -0.344538\n",
      "% games drawn        0.221751     0.34375  0.354905\n",
      "% games won          0.778249     0.65625 -0.185903\n",
      "                 Predictions  Maximum  % of Maximum\n",
      "CorrectResult             34       64      0.531250\n",
      "CorrectGoalDiff           21       64      0.328125\n",
      "CorrectScore               7       64      0.109375\n",
      "PointsScored              62      192      0.322917\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.3229166666666667"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2 = make_predictions(inputData, 4., 16.)\n",
    "compare_to_historic(data2)\n",
    "compare_to_actual(data2, results)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-01-25T21:39:31.569483Z",
     "iopub.status.busy": "2021-01-25T21:39:31.568488Z",
     "iopub.status.idle": "2021-01-25T21:39:31.574445Z",
     "shell.execute_reply": "2021-01-25T21:39:31.573498Z"
    }
   },
   "outputs": [],
   "source": [
    "data2[[\"Date\", \"Time\", \"Team1\", \"Team2\", \"Goals1\", \"Goals2\"]].to_csv(\"../data/interim/intl_01_predictions_2.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.3 64-bit ('tbir': conda)",
   "language": "python",
   "name": "python38364bittbirconda78a6d6cae240461ea085f416fad96c86"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}