build_database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Stage 1: Build a database"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Basic match dataframe setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import pandas as pd\n",
    "from time import time\n",
    "\n",
    "start = time()\n",
    "\n",
    "path = \"./\"\n",
    "database = path + 'football_database.sqlite'\n",
    "\n",
    "conn = sqlite3.connect(database)\n",
    "\n",
    "df = pd.read_sql_query(\"\"\"\n",
    "        SELECT id, country_id, league_id, season, stage, date, \n",
    "            match_api_id, home_team_api_id, away_team_api_id, \n",
    "            home_team_goal, away_team_goal, home_player_1, home_player_2, \n",
    "            home_player_3, home_player_4, home_player_5, \n",
    "            home_player_6, home_player_7, home_player_8, \n",
    "            home_player_9, home_player_10, home_player_11, \n",
    "            away_player_1, away_player_2, away_player_3, \n",
    "            away_player_4, away_player_5, away_player_6, \n",
    "            away_player_7, away_player_8, away_player_9, \n",
    "            away_player_10, away_player_11, goal, shoton, \n",
    "            shotoff, foulcommit, card, cross, corner, possession\n",
    "        FROM match\n",
    "        \"\"\", conn)\n",
    "\n",
    "df.set_index('id', drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(13325, 40)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop null values\n",
    "df.dropna(inplace=True)\n",
    "\n",
    "#Replace the previous line with this to drop nans in certain columns only\n",
    "#df.dropna(subset=['home_player_1', 'home_player_2'], inplace=True)\n",
    "\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change player ids to int instead of float\n",
    "\n",
    "home_fields = [f'home_player_{i}' for i in range(1, 12)]\n",
    "away_fields = [f'away_player_{i}' for i in range(1, 12)]\n",
    "\n",
    "df[home_fields] = df[home_fields].astype(int)\n",
    "df[away_fields] = df[away_fields].astype(int)\n",
    "\n",
    "df['date'] = df['date'].apply(pd.to_datetime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 40)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Make df smaller for faster processing\n",
    "df = df.iloc[12325:13325]\n",
    "\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country_id</th>\n",
       "      <th>league_id</th>\n",
       "      <th>season</th>\n",
       "      <th>stage</th>\n",
       "      <th>date</th>\n",
       "      <th>match_api_id</th>\n",
       "      <th>home_team_api_id</th>\n",
       "      <th>away_team_api_id</th>\n",
       "      <th>home_team_goal</th>\n",
       "      <th>away_team_goal</th>\n",
       "      <th>...</th>\n",
       "      <th>away_player_11</th>\n",
       "      <th>goal</th>\n",
       "      <th>shoton</th>\n",
       "      <th>shotoff</th>\n",
       "      <th>foulcommit</th>\n",
       "      <th>card</th>\n",
       "      <th>cross</th>\n",
       "      <th>corner</th>\n",
       "      <th>possession</th>\n",
       "      <th>match_result</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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",
       "      <th></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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>2013</td>\n",
       "      <td>26</td>\n",
       "      <td>2014-03-02</td>\n",
       "      <td>1506071</td>\n",
       "      <td>8370</td>\n",
       "      <td>10267</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>241825</td>\n",
       "      <td>&lt;goal&gt;&lt;value&gt;&lt;comment&gt;n&lt;/comment&gt;&lt;stats&gt;&lt;goals...</td>\n",
       "      <td>&lt;shoton /&gt;</td>\n",
       "      <td>&lt;shotoff /&gt;</td>\n",
       "      <td>&lt;foulcommit /&gt;</td>\n",
       "      <td>&lt;card&gt;&lt;value&gt;&lt;comment&gt;y&lt;/comment&gt;&lt;stats&gt;&lt;ycard...</td>\n",
       "      <td>&lt;cross /&gt;</td>\n",
       "      <td>&lt;corner /&gt;</td>\n",
       "      <td>&lt;possession /&gt;</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>2013</td>\n",
       "      <td>27</td>\n",
       "      <td>2014-03-09</td>\n",
       "      <td>1506072</td>\n",
       "      <td>10267</td>\n",
       "      <td>8315</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>33028</td>\n",
       "      <td>&lt;goal&gt;&lt;value&gt;&lt;comment&gt;n&lt;/comment&gt;&lt;stats&gt;&lt;goals...</td>\n",
       "      <td>&lt;shoton /&gt;</td>\n",
       "      <td>&lt;shotoff /&gt;</td>\n",
       "      <td>&lt;foulcommit /&gt;</td>\n",
       "      <td>&lt;card&gt;&lt;value&gt;&lt;comment&gt;y&lt;/comment&gt;&lt;stats&gt;&lt;ycard...</td>\n",
       "      <td>&lt;cross /&gt;</td>\n",
       "      <td>&lt;corner /&gt;</td>\n",
       "      <td>&lt;possession /&gt;</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>2013</td>\n",
       "      <td>27</td>\n",
       "      <td>2014-03-08</td>\n",
       "      <td>1506073</td>\n",
       "      <td>7878</td>\n",
       "      <td>10205</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>150644</td>\n",
       "      <td>&lt;goal&gt;&lt;value&gt;&lt;comment&gt;p&lt;/comment&gt;&lt;stats&gt;&lt;penal...</td>\n",
       "      <td>&lt;shoton /&gt;</td>\n",
       "      <td>&lt;shotoff /&gt;</td>\n",
       "      <td>&lt;foulcommit /&gt;</td>\n",
       "      <td>&lt;card&gt;&lt;value&gt;&lt;comment&gt;y&lt;/comment&gt;&lt;stats&gt;&lt;ycard...</td>\n",
       "      <td>&lt;cross /&gt;</td>\n",
       "      <td>&lt;corner /&gt;</td>\n",
       "      <td>&lt;possession /&gt;</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>2013</td>\n",
       "      <td>27</td>\n",
       "      <td>2014-03-08</td>\n",
       "      <td>1506074</td>\n",
       "      <td>8603</td>\n",
       "      <td>8305</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>38573</td>\n",
       "      <td>&lt;goal&gt;&lt;value&gt;&lt;comment&gt;n&lt;/comment&gt;&lt;stats&gt;&lt;goals...</td>\n",
       "      <td>&lt;shoton /&gt;</td>\n",
       "      <td>&lt;shotoff /&gt;</td>\n",
       "      <td>&lt;foulcommit /&gt;</td>\n",
       "      <td>&lt;card&gt;&lt;value&gt;&lt;comment&gt;y&lt;/comment&gt;&lt;stats&gt;&lt;ycard...</td>\n",
       "      <td>&lt;cross /&gt;</td>\n",
       "      <td>&lt;corner /&gt;</td>\n",
       "      <td>&lt;possession /&gt;</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>2013</td>\n",
       "      <td>27</td>\n",
       "      <td>2014-03-09</td>\n",
       "      <td>1506075</td>\n",
       "      <td>8558</td>\n",
       "      <td>10268</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>500791</td>\n",
       "      <td>&lt;goal&gt;&lt;value&gt;&lt;comment&gt;n&lt;/comment&gt;&lt;stats&gt;&lt;goals...</td>\n",
       "      <td>&lt;shoton /&gt;</td>\n",
       "      <td>&lt;shotoff /&gt;</td>\n",
       "      <td>&lt;foulcommit /&gt;</td>\n",
       "      <td>&lt;card&gt;&lt;value&gt;&lt;comment&gt;y&lt;/comment&gt;&lt;stats&gt;&lt;ycard...</td>\n",
       "      <td>&lt;cross /&gt;</td>\n",
       "      <td>&lt;corner /&gt;</td>\n",
       "      <td>&lt;possession /&gt;</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       country_id  league_id season  stage       date  match_api_id  \\\n",
       "id                                                                    \n",
       "23607       21518      21518   2013     26 2014-03-02       1506071   \n",
       "23608       21518      21518   2013     27 2014-03-09       1506072   \n",
       "23609       21518      21518   2013     27 2014-03-08       1506073   \n",
       "23610       21518      21518   2013     27 2014-03-08       1506074   \n",
       "23611       21518      21518   2013     27 2014-03-09       1506075   \n",
       "\n",
       "       home_team_api_id  away_team_api_id  home_team_goal  away_team_goal  \\\n",
       "id                                                                          \n",
       "23607              8370             10267               1               0   \n",
       "23608             10267              8315               1               1   \n",
       "23609              7878             10205               2               0   \n",
       "23610              8603              8305               2               0   \n",
       "23611              8558             10268               3               1   \n",
       "\n",
       "           ...       away_player_11  \\\n",
       "id         ...                        \n",
       "23607      ...               241825   \n",
       "23608      ...                33028   \n",
       "23609      ...               150644   \n",
       "23610      ...                38573   \n",
       "23611      ...               500791   \n",
       "\n",
       "                                                    goal      shoton  \\\n",
       "id                                                                     \n",
       "23607  <goal><value><comment>n</comment><stats><goals...  <shoton />   \n",
       "23608  <goal><value><comment>n</comment><stats><goals...  <shoton />   \n",
       "23609  <goal><value><comment>p</comment><stats><penal...  <shoton />   \n",
       "23610  <goal><value><comment>n</comment><stats><goals...  <shoton />   \n",
       "23611  <goal><value><comment>n</comment><stats><goals...  <shoton />   \n",
       "\n",
       "           shotoff      foulcommit  \\\n",
       "id                                   \n",
       "23607  <shotoff />  <foulcommit />   \n",
       "23608  <shotoff />  <foulcommit />   \n",
       "23609  <shotoff />  <foulcommit />   \n",
       "23610  <shotoff />  <foulcommit />   \n",
       "23611  <shotoff />  <foulcommit />   \n",
       "\n",
       "                                                    card      cross  \\\n",
       "id                                                                    \n",
       "23607  <card><value><comment>y</comment><stats><ycard...  <cross />   \n",
       "23608  <card><value><comment>y</comment><stats><ycard...  <cross />   \n",
       "23609  <card><value><comment>y</comment><stats><ycard...  <cross />   \n",
       "23610  <card><value><comment>y</comment><stats><ycard...  <cross />   \n",
       "23611  <card><value><comment>y</comment><stats><ycard...  <cross />   \n",
       "\n",
       "           corner      possession  match_result  \n",
       "id                                               \n",
       "23607  <corner />  <possession />             1  \n",
       "23608  <corner />  <possession />             3  \n",
       "23609  <corner />  <possession />             1  \n",
       "23610  <corner />  <possession />             1  \n",
       "23611  <corner />  <possession />             1  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add match_result\n",
    "\n",
    "df.loc[df['home_team_goal'] > df['away_team_goal'], 'match_result'] = 1\n",
    "df.loc[df['home_team_goal'] < df['away_team_goal'], 'match_result'] = 2\n",
    "df.loc[df['home_team_goal'] == df['away_team_goal'], 'match_result'] = 3\n",
    "df['match_result'] = df['match_result'].astype(int)\n",
    "\n",
    "# Get the first year of season\n",
    "df['season'] = df['season'].apply(lambda x: x[:4])\n",
    "\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>birthday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>505942</td>\n",
       "      <td>1992-02-29 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>155782</td>\n",
       "      <td>1989-12-15 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>162549</td>\n",
       "      <td>1991-05-13 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>30572</td>\n",
       "      <td>1982-05-08 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>23780</td>\n",
       "      <td>1979-11-08 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id             birthday\n",
       "0  505942  1992-02-29 00:00:00\n",
       "1  155782  1989-12-15 00:00:00\n",
       "2  162549  1991-05-13 00:00:00\n",
       "3   30572  1982-05-08 00:00:00\n",
       "4   23780  1979-11-08 00:00:00"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Calculate lineup average age\n",
    "\n",
    "# Load birth year data\n",
    "birth_day = pd.read_sql_query(\"\"\"\n",
    "        SELECT player_api_id as id, birthday\n",
    "        FROM player\n",
    "\"\"\", conn, params=())\n",
    "\n",
    "birth_day.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "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>home_player_1</th>\n",
       "      <th>home_player_2</th>\n",
       "      <th>home_player_3</th>\n",
       "      <th>home_player_4</th>\n",
       "      <th>home_player_5</th>\n",
       "      <th>home_player_6</th>\n",
       "      <th>home_player_7</th>\n",
       "      <th>home_player_8</th>\n",
       "      <th>home_player_9</th>\n",
       "      <th>home_player_10</th>\n",
       "      <th>home_player_11</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>1984-06-22 00:00:00</td>\n",
       "      <td>1985-07-11 00:00:00</td>\n",
       "      <td>1989-06-06 00:00:00</td>\n",
       "      <td>1983-01-13 00:00:00</td>\n",
       "      <td>1981-05-26 00:00:00</td>\n",
       "      <td>1981-02-28 00:00:00</td>\n",
       "      <td>1994-11-21 00:00:00</td>\n",
       "      <td>1991-03-23 00:00:00</td>\n",
       "      <td>1988-03-20 00:00:00</td>\n",
       "      <td>1990-01-04 00:00:00</td>\n",
       "      <td>1984-08-20 00:00:00</td>\n",
       "      <td>2014-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>1985-06-24 00:00:00</td>\n",
       "      <td>1984-02-25 00:00:00</td>\n",
       "      <td>1985-02-14 00:00:00</td>\n",
       "      <td>1983-10-29 00:00:00</td>\n",
       "      <td>1993-03-01 00:00:00</td>\n",
       "      <td>1989-04-16 00:00:00</td>\n",
       "      <td>1980-01-16 00:00:00</td>\n",
       "      <td>1989-12-26 00:00:00</td>\n",
       "      <td>1984-04-01 00:00:00</td>\n",
       "      <td>1993-01-20 00:00:00</td>\n",
       "      <td>1993-08-30 00:00:00</td>\n",
       "      <td>2014-03-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>1979-01-25 00:00:00</td>\n",
       "      <td>1988-05-10 00:00:00</td>\n",
       "      <td>1992-02-20 00:00:00</td>\n",
       "      <td>1992-05-27 00:00:00</td>\n",
       "      <td>1989-11-02 00:00:00</td>\n",
       "      <td>1987-08-03 00:00:00</td>\n",
       "      <td>1984-06-23 00:00:00</td>\n",
       "      <td>1991-01-11 00:00:00</td>\n",
       "      <td>1980-08-11 00:00:00</td>\n",
       "      <td>1987-02-03 00:00:00</td>\n",
       "      <td>1990-02-08 00:00:00</td>\n",
       "      <td>2014-03-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>1987-05-13 00:00:00</td>\n",
       "      <td>1988-09-11 00:00:00</td>\n",
       "      <td>1984-04-10 00:00:00</td>\n",
       "      <td>1987-05-16 00:00:00</td>\n",
       "      <td>1989-06-09 00:00:00</td>\n",
       "      <td>1990-03-06 00:00:00</td>\n",
       "      <td>1991-06-13 00:00:00</td>\n",
       "      <td>1984-03-18 00:00:00</td>\n",
       "      <td>1992-08-26 00:00:00</td>\n",
       "      <td>1981-06-27 00:00:00</td>\n",
       "      <td>1992-03-08 00:00:00</td>\n",
       "      <td>2014-03-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>1986-10-02 00:00:00</td>\n",
       "      <td>1986-01-21 00:00:00</td>\n",
       "      <td>1981-03-10 00:00:00</td>\n",
       "      <td>1988-01-17 00:00:00</td>\n",
       "      <td>1990-01-05 00:00:00</td>\n",
       "      <td>1987-09-08 00:00:00</td>\n",
       "      <td>1989-10-09 00:00:00</td>\n",
       "      <td>1986-10-12 00:00:00</td>\n",
       "      <td>1989-10-06 00:00:00</td>\n",
       "      <td>1979-10-31 00:00:00</td>\n",
       "      <td>1983-06-09 00:00:00</td>\n",
       "      <td>2014-03-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             home_player_1        home_player_2        home_player_3  \\\n",
       "id                                                                     \n",
       "23607  1984-06-22 00:00:00  1985-07-11 00:00:00  1989-06-06 00:00:00   \n",
       "23608  1985-06-24 00:00:00  1984-02-25 00:00:00  1985-02-14 00:00:00   \n",
       "23609  1979-01-25 00:00:00  1988-05-10 00:00:00  1992-02-20 00:00:00   \n",
       "23610  1987-05-13 00:00:00  1988-09-11 00:00:00  1984-04-10 00:00:00   \n",
       "23611  1986-10-02 00:00:00  1986-01-21 00:00:00  1981-03-10 00:00:00   \n",
       "\n",
       "             home_player_4        home_player_5        home_player_6  \\\n",
       "id                                                                     \n",
       "23607  1983-01-13 00:00:00  1981-05-26 00:00:00  1981-02-28 00:00:00   \n",
       "23608  1983-10-29 00:00:00  1993-03-01 00:00:00  1989-04-16 00:00:00   \n",
       "23609  1992-05-27 00:00:00  1989-11-02 00:00:00  1987-08-03 00:00:00   \n",
       "23610  1987-05-16 00:00:00  1989-06-09 00:00:00  1990-03-06 00:00:00   \n",
       "23611  1988-01-17 00:00:00  1990-01-05 00:00:00  1987-09-08 00:00:00   \n",
       "\n",
       "             home_player_7        home_player_8        home_player_9  \\\n",
       "id                                                                     \n",
       "23607  1994-11-21 00:00:00  1991-03-23 00:00:00  1988-03-20 00:00:00   \n",
       "23608  1980-01-16 00:00:00  1989-12-26 00:00:00  1984-04-01 00:00:00   \n",
       "23609  1984-06-23 00:00:00  1991-01-11 00:00:00  1980-08-11 00:00:00   \n",
       "23610  1991-06-13 00:00:00  1984-03-18 00:00:00  1992-08-26 00:00:00   \n",
       "23611  1989-10-09 00:00:00  1986-10-12 00:00:00  1989-10-06 00:00:00   \n",
       "\n",
       "            home_player_10       home_player_11       date  \n",
       "id                                                          \n",
       "23607  1990-01-04 00:00:00  1984-08-20 00:00:00 2014-03-02  \n",
       "23608  1993-01-20 00:00:00  1993-08-30 00:00:00 2014-03-09  \n",
       "23609  1987-02-03 00:00:00  1990-02-08 00:00:00 2014-03-08  \n",
       "23610  1981-06-27 00:00:00  1992-03-08 00:00:00 2014-03-08  \n",
       "23611  1979-10-31 00:00:00  1983-06-09 00:00:00 2014-03-09  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get average age of a line up for home players\n",
    "\n",
    "dates = df[home_fields].astype(int).replace(to_replace=birth_day['id'].tolist(), value=birth_day['birthday'].tolist())\n",
    "dates['date'] = df['date']\n",
    "\n",
    "dates.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id\n",
       "23607    27.5\n",
       "23608    26.7\n",
       "23609    26.7\n",
       "23610    26.1\n",
       "23611    27.9\n",
       "Name: avg_home_ages, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate average difference from player ages\n",
    "dates = dates.apply(pd.to_datetime)\n",
    "\n",
    "df['avg_home_ages'] = dates[home_fields].apply(lambda s: dates['date']-s).mean(axis=1).astype('timedelta64[D]').apply(lambda s: s/365).round(1)\n",
    "\n",
    "df['avg_home_ages'].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>away_player_1</th>\n",
       "      <th>away_player_2</th>\n",
       "      <th>away_player_3</th>\n",
       "      <th>away_player_4</th>\n",
       "      <th>away_player_5</th>\n",
       "      <th>away_player_6</th>\n",
       "      <th>away_player_7</th>\n",
       "      <th>away_player_8</th>\n",
       "      <th>away_player_9</th>\n",
       "      <th>away_player_10</th>\n",
       "      <th>away_player_11</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>1985-06-24 00:00:00</td>\n",
       "      <td>1987-06-12 00:00:00</td>\n",
       "      <td>1985-02-14 00:00:00</td>\n",
       "      <td>1983-10-29 00:00:00</td>\n",
       "      <td>1993-03-01 00:00:00</td>\n",
       "      <td>1992-05-21 00:00:00</td>\n",
       "      <td>1980-01-16 00:00:00</td>\n",
       "      <td>1989-12-26 00:00:00</td>\n",
       "      <td>1989-11-20 00:00:00</td>\n",
       "      <td>1988-07-29 00:00:00</td>\n",
       "      <td>1993-08-30 00:00:00</td>\n",
       "      <td>2014-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>1981-03-06 00:00:00</td>\n",
       "      <td>1989-04-14 00:00:00</td>\n",
       "      <td>1980-08-19 00:00:00</td>\n",
       "      <td>1994-05-27 00:00:00</td>\n",
       "      <td>1988-02-29 00:00:00</td>\n",
       "      <td>1989-03-08 00:00:00</td>\n",
       "      <td>1987-12-14 00:00:00</td>\n",
       "      <td>1989-08-14 00:00:00</td>\n",
       "      <td>1984-11-04 00:00:00</td>\n",
       "      <td>1992-12-19 00:00:00</td>\n",
       "      <td>1981-02-11 00:00:00</td>\n",
       "      <td>2014-03-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>1989-06-28 00:00:00</td>\n",
       "      <td>1990-11-24 00:00:00</td>\n",
       "      <td>1990-11-22 00:00:00</td>\n",
       "      <td>1984-06-12 00:00:00</td>\n",
       "      <td>1988-03-18 00:00:00</td>\n",
       "      <td>1994-06-23 00:00:00</td>\n",
       "      <td>1991-10-17 00:00:00</td>\n",
       "      <td>1987-10-14 00:00:00</td>\n",
       "      <td>1994-11-10 00:00:00</td>\n",
       "      <td>1984-01-05 00:00:00</td>\n",
       "      <td>1987-05-12 00:00:00</td>\n",
       "      <td>2014-03-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>1982-04-27 00:00:00</td>\n",
       "      <td>1984-12-21 00:00:00</td>\n",
       "      <td>1989-09-01 00:00:00</td>\n",
       "      <td>1985-04-09 00:00:00</td>\n",
       "      <td>1985-08-30 00:00:00</td>\n",
       "      <td>1982-04-01 00:00:00</td>\n",
       "      <td>1984-03-15 00:00:00</td>\n",
       "      <td>1986-11-24 00:00:00</td>\n",
       "      <td>1992-05-11 00:00:00</td>\n",
       "      <td>1982-07-02 00:00:00</td>\n",
       "      <td>1984-08-07 00:00:00</td>\n",
       "      <td>2014-03-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>1981-09-29 00:00:00</td>\n",
       "      <td>1988-04-27 00:00:00</td>\n",
       "      <td>1989-01-27 00:00:00</td>\n",
       "      <td>1987-06-05 00:00:00</td>\n",
       "      <td>1982-02-09 00:00:00</td>\n",
       "      <td>1986-05-11 00:00:00</td>\n",
       "      <td>1986-02-06 00:00:00</td>\n",
       "      <td>1983-01-05 00:00:00</td>\n",
       "      <td>1992-11-02 00:00:00</td>\n",
       "      <td>1984-02-25 00:00:00</td>\n",
       "      <td>1991-03-01 00:00:00</td>\n",
       "      <td>2014-03-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             away_player_1        away_player_2        away_player_3  \\\n",
       "id                                                                     \n",
       "23607  1985-06-24 00:00:00  1987-06-12 00:00:00  1985-02-14 00:00:00   \n",
       "23608  1981-03-06 00:00:00  1989-04-14 00:00:00  1980-08-19 00:00:00   \n",
       "23609  1989-06-28 00:00:00  1990-11-24 00:00:00  1990-11-22 00:00:00   \n",
       "23610  1982-04-27 00:00:00  1984-12-21 00:00:00  1989-09-01 00:00:00   \n",
       "23611  1981-09-29 00:00:00  1988-04-27 00:00:00  1989-01-27 00:00:00   \n",
       "\n",
       "             away_player_4        away_player_5        away_player_6  \\\n",
       "id                                                                     \n",
       "23607  1983-10-29 00:00:00  1993-03-01 00:00:00  1992-05-21 00:00:00   \n",
       "23608  1994-05-27 00:00:00  1988-02-29 00:00:00  1989-03-08 00:00:00   \n",
       "23609  1984-06-12 00:00:00  1988-03-18 00:00:00  1994-06-23 00:00:00   \n",
       "23610  1985-04-09 00:00:00  1985-08-30 00:00:00  1982-04-01 00:00:00   \n",
       "23611  1987-06-05 00:00:00  1982-02-09 00:00:00  1986-05-11 00:00:00   \n",
       "\n",
       "             away_player_7        away_player_8        away_player_9  \\\n",
       "id                                                                     \n",
       "23607  1980-01-16 00:00:00  1989-12-26 00:00:00  1989-11-20 00:00:00   \n",
       "23608  1987-12-14 00:00:00  1989-08-14 00:00:00  1984-11-04 00:00:00   \n",
       "23609  1991-10-17 00:00:00  1987-10-14 00:00:00  1994-11-10 00:00:00   \n",
       "23610  1984-03-15 00:00:00  1986-11-24 00:00:00  1992-05-11 00:00:00   \n",
       "23611  1986-02-06 00:00:00  1983-01-05 00:00:00  1992-11-02 00:00:00   \n",
       "\n",
       "            away_player_10       away_player_11       date  \n",
       "id                                                          \n",
       "23607  1988-07-29 00:00:00  1993-08-30 00:00:00 2014-03-02  \n",
       "23608  1992-12-19 00:00:00  1981-02-11 00:00:00 2014-03-09  \n",
       "23609  1984-01-05 00:00:00  1987-05-12 00:00:00 2014-03-08  \n",
       "23610  1982-07-02 00:00:00  1984-08-07 00:00:00 2014-03-08  \n",
       "23611  1984-02-25 00:00:00  1991-03-01 00:00:00 2014-03-09  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get average age of a line up for away players\n",
    "\n",
    "dates = df[away_fields].astype(int).replace(to_replace=birth_day['id'].tolist(), value=birth_day['birthday'].tolist())\n",
    "dates['date'] = df['date']\n",
    "\n",
    "dates.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id\n",
       "23607    26.0\n",
       "23608    27.0\n",
       "23609    24.7\n",
       "23610    28.7\n",
       "23611    27.6\n",
       "Name: avg_away_ages, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate average difference from player ages\n",
    "dates = dates.apply(pd.to_datetime)\n",
    "\n",
    "df['avg_away_ages'] = dates[away_fields].apply(lambda s: dates['date']-s).mean(axis=1).astype('timedelta64[D]').apply(lambda s: s/365).round(1)\n",
    "\n",
    "df['avg_away_ages'].head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 1A: Parse XML fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a helper function that counts team nodes\n",
    "from collections import Counter\n",
    "import xml.etree.ElementTree as ET\n",
    "\n",
    "def count_teams(xml_string):\n",
    "    c = Counter()\n",
    "    root = ET.fromstring(xml_string)\n",
    "    for t in root.iter('team'):\n",
    "        c.update([t.text])\n",
    "    return c"
   ]
  },
  {
   "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 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>country_id</th>\n",
       "      <th>league_id</th>\n",
       "      <th>season</th>\n",
       "      <th>stage</th>\n",
       "      <th>date</th>\n",
       "      <th>match_api_id</th>\n",
       "      <th>home_team_api_id</th>\n",
       "      <th>away_team_api_id</th>\n",
       "      <th>home_team_goal</th>\n",
       "      <th>away_team_goal</th>\n",
       "      <th>...</th>\n",
       "      <th>avg_home_ages</th>\n",
       "      <th>avg_away_ages</th>\n",
       "      <th>home_shoton</th>\n",
       "      <th>away_shoton</th>\n",
       "      <th>home_shotoff</th>\n",
       "      <th>away_shotoff</th>\n",
       "      <th>home_foulcommit</th>\n",
       "      <th>away_foulcommit</th>\n",
       "      <th>home_corner</th>\n",
       "      <th>away_corner</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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",
       "      <th></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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>2013</td>\n",
       "      <td>26</td>\n",
       "      <td>2014-03-02</td>\n",
       "      <td>1506071</td>\n",
       "      <td>8370</td>\n",
       "      <td>10267</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>27.5</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>2013</td>\n",
       "      <td>27</td>\n",
       "      <td>2014-03-09</td>\n",
       "      <td>1506072</td>\n",
       "      <td>10267</td>\n",
       "      <td>8315</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>26.7</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>2013</td>\n",
       "      <td>27</td>\n",
       "      <td>2014-03-08</td>\n",
       "      <td>1506073</td>\n",
       "      <td>7878</td>\n",
       "      <td>10205</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>26.7</td>\n",
       "      <td>24.7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>2013</td>\n",
       "      <td>27</td>\n",
       "      <td>2014-03-08</td>\n",
       "      <td>1506074</td>\n",
       "      <td>8603</td>\n",
       "      <td>8305</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>26.1</td>\n",
       "      <td>28.7</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>2013</td>\n",
       "      <td>27</td>\n",
       "      <td>2014-03-09</td>\n",
       "      <td>1506075</td>\n",
       "      <td>8558</td>\n",
       "      <td>10268</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>27.9</td>\n",
       "      <td>27.6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       country_id  league_id season  stage       date  match_api_id  \\\n",
       "id                                                                    \n",
       "23607       21518      21518   2013     26 2014-03-02       1506071   \n",
       "23608       21518      21518   2013     27 2014-03-09       1506072   \n",
       "23609       21518      21518   2013     27 2014-03-08       1506073   \n",
       "23610       21518      21518   2013     27 2014-03-08       1506074   \n",
       "23611       21518      21518   2013     27 2014-03-09       1506075   \n",
       "\n",
       "       home_team_api_id  away_team_api_id  home_team_goal  away_team_goal  \\\n",
       "id                                                                          \n",
       "23607              8370             10267               1               0   \n",
       "23608             10267              8315               1               1   \n",
       "23609              7878             10205               2               0   \n",
       "23610              8603              8305               2               0   \n",
       "23611              8558             10268               3               1   \n",
       "\n",
       "          ...       avg_home_ages  avg_away_ages  home_shoton  away_shoton  \\\n",
       "id        ...                                                                \n",
       "23607     ...                27.5           26.0            0            0   \n",
       "23608     ...                26.7           27.0            0            0   \n",
       "23609     ...                26.7           24.7            0            0   \n",
       "23610     ...                26.1           28.7            0            0   \n",
       "23611     ...                27.9           27.6            0            0   \n",
       "\n",
       "       home_shotoff  away_shotoff  home_foulcommit  away_foulcommit  \\\n",
       "id                                                                    \n",
       "23607             0             0                0                0   \n",
       "23608             0             0                0                0   \n",
       "23609             0             0                0                0   \n",
       "23610             0             0                0                0   \n",
       "23611             0             0                0                0   \n",
       "\n",
       "       home_corner  away_corner  \n",
       "id                               \n",
       "23607            0            0  \n",
       "23608            0            0  \n",
       "23609            0            0  \n",
       "23610            0            0  \n",
       "23611            0            0  \n",
       "\n",
       "[5 rows x 51 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add common XML fields to the main dataframe\n",
    "common_xml_fields = ['shoton', 'shotoff', 'foulcommit', 'corner']\n",
    "\n",
    "for field in common_xml_fields:\n",
    "    df[f'home_{field}'] = df.apply(lambda s: count_teams(s[field])[str(s['home_team_api_id'])], axis=1)\n",
    "    df[f'away_{field}'] = df.apply(lambda s: count_teams(s[field])[str(s['away_team_api_id'])], axis=1)\n",
    "\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>home_ycard</th>\n",
       "      <th>home_rcard</th>\n",
       "      <th>away_ycard</th>\n",
       "      <th>away_rcard</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       home_ycard  home_rcard  away_ycard  away_rcard\n",
       "id                                                   \n",
       "23607           3           0           2           0\n",
       "23608           3           0           2           0\n",
       "23609           4           0           1           0\n",
       "23610           3           0           3           0\n",
       "23611           1           0           3           0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get card details\n",
    "\n",
    "def get_cards(xml_string, card_type):\n",
    "    c = Counter()\n",
    "    for val in ET.fromstring(xml_string).iter('value'):\n",
    "        if val.find('comment').text == card_type:\n",
    "            try:\n",
    "                c.update([val.find('team').text])\n",
    "            except AttributeError as e:\n",
    "                pass\n",
    "    return c\n",
    "\n",
    "df['home_ycard'] = df.apply(lambda s: get_cards(s['card'], 'y')[str(s['home_team_api_id'])], axis=1)\n",
    "df['home_rcard'] = df.apply(lambda s: get_cards(s['card'], 'r')[str(s['home_team_api_id'])], axis=1)\n",
    "df['away_ycard'] = df.apply(lambda s: get_cards(s['card'], 'y')[str(s['away_team_api_id'])], axis=1)\n",
    "df['away_rcard'] = df.apply(lambda s: get_cards(s['card'], 'r')[str(s['away_team_api_id'])], axis=1)\n",
    "\n",
    "df[['home_ycard', 'home_rcard', 'away_ycard', 'away_rcard']].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>away_pos</th>\n",
       "      <th>home_pos</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      away_pos home_pos\n",
       "id                     \n",
       "23607       50       50\n",
       "23608       50       50\n",
       "23609       50       50\n",
       "23610       50       50\n",
       "23611       50       50"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get possession details for each team\n",
    "\n",
    "def get_pos(xml_string, filter):\n",
    "    try:\n",
    "        val = ET.fromstring(xml_string).findall(filter)[-1].text\n",
    "        return val\n",
    "    except IndexError as e:\n",
    "        return 50\n",
    "\n",
    "df['away_pos'] = df['possession'].apply(lambda s: get_pos(s, './value/awaypos'))\n",
    "df['home_pos'] = df['possession'].apply(lambda s: get_pos(s, './value/homepos'))\n",
    "\n",
    "df[['away_pos', 'home_pos']].head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 1B: Parsing player attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>player_api_id</th>\n",
       "      <th>date</th>\n",
       "      <th>overall_rating</th>\n",
       "      <th>crossing</th>\n",
       "      <th>finishing</th>\n",
       "      <th>heading_accuracy</th>\n",
       "      <th>short_passing</th>\n",
       "      <th>volleys</th>\n",
       "      <th>dribbling</th>\n",
       "      <th>curve</th>\n",
       "      <th>...</th>\n",
       "      <th>strength</th>\n",
       "      <th>long_shots</th>\n",
       "      <th>aggression</th>\n",
       "      <th>interceptions</th>\n",
       "      <th>positioning</th>\n",
       "      <th>vision</th>\n",
       "      <th>penalties</th>\n",
       "      <th>marking</th>\n",
       "      <th>standing_tackle</th>\n",
       "      <th>sliding_tackle</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>505942</td>\n",
       "      <td>2016-02-18</td>\n",
       "      <td>67.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>...</td>\n",
       "      <td>76.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>505942</td>\n",
       "      <td>2015-11-19</td>\n",
       "      <td>67.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>...</td>\n",
       "      <td>76.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>505942</td>\n",
       "      <td>2015-09-21</td>\n",
       "      <td>62.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>...</td>\n",
       "      <td>76.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>505942</td>\n",
       "      <td>2015-03-20</td>\n",
       "      <td>61.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>...</td>\n",
       "      <td>76.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>505942</td>\n",
       "      <td>2007-02-22</td>\n",
       "      <td>61.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>70.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>...</td>\n",
       "      <td>76.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   player_api_id       date  overall_rating  crossing  finishing  \\\n",
       "0         505942 2016-02-18            67.0      49.0       44.0   \n",
       "1         505942 2015-11-19            67.0      49.0       44.0   \n",
       "2         505942 2015-09-21            62.0      49.0       44.0   \n",
       "3         505942 2015-03-20            61.0      48.0       43.0   \n",
       "4         505942 2007-02-22            61.0      48.0       43.0   \n",
       "\n",
       "   heading_accuracy  short_passing  volleys  dribbling  curve       ...        \\\n",
       "0              71.0           61.0     44.0       51.0   45.0       ...         \n",
       "1              71.0           61.0     44.0       51.0   45.0       ...         \n",
       "2              71.0           61.0     44.0       51.0   45.0       ...         \n",
       "3              70.0           60.0     43.0       50.0   44.0       ...         \n",
       "4              70.0           60.0     43.0       50.0   44.0       ...         \n",
       "\n",
       "   strength  long_shots  aggression  interceptions  positioning  vision  \\\n",
       "0      76.0        35.0        71.0           70.0         45.0    54.0   \n",
       "1      76.0        35.0        71.0           70.0         45.0    54.0   \n",
       "2      76.0        35.0        63.0           41.0         45.0    54.0   \n",
       "3      76.0        34.0        62.0           40.0         44.0    53.0   \n",
       "4      76.0        34.0        62.0           40.0         44.0    53.0   \n",
       "\n",
       "   penalties  marking  standing_tackle  sliding_tackle  \n",
       "0       48.0     65.0             69.0            69.0  \n",
       "1       48.0     65.0             69.0            69.0  \n",
       "2       48.0     65.0             66.0            69.0  \n",
       "3       47.0     62.0             63.0            66.0  \n",
       "4       47.0     62.0             63.0            66.0  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load Player attributes\n",
    "player_attrib = pd.read_sql_query(\"\"\"\n",
    "        SELECT  player_api_id, date, overall_rating, crossing, finishing, heading_accuracy,\n",
    "                short_passing, volleys, dribbling, curve, free_kick_accuracy,\n",
    "                long_passing, ball_control, acceleration, sprint_speed, agility,\n",
    "                reactions, balance, shot_power, jumping, stamina, strength,\n",
    "                long_shots, aggression, interceptions, positioning, vision,\n",
    "                penalties, marking, standing_tackle, sliding_tackle\n",
    "        FROM player_attributes\n",
    "\"\"\", conn, params=())\n",
    "\n",
    "player_attrib['date'] = player_attrib['date'].apply(pd.to_datetime)\n",
    "\n",
    "player_attrib.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prepare helper functions and variables\n",
    "\n",
    "def get_latest_attribute_idx(player, col_name):\n",
    "    ''' Takes a player and a field and returns the id of the most recent attribute '''\n",
    "    earlier_dates = player_attrib.loc[(player_attrib['player_api_id'] == player[col_name]) \n",
    "                                      & (player_attrib['date'] < pd.to_datetime(player['date']))]\n",
    "    return earlier_dates[earlier_dates['date'] == earlier_dates['date'].max()].index[0]\n",
    "\n",
    "player_attribs = ['overall_rating', 'crossing', 'finishing', 'heading_accuracy',\n",
    "                    'short_passing', 'volleys', 'dribbling', 'curve', 'free_kick_accuracy',\n",
    "                    'long_passing', 'ball_control', 'acceleration', 'sprint_speed', 'agility',\n",
    "                    'reactions', 'balance', 'shot_power', 'jumping', 'stamina', 'strength',\n",
    "                    'long_shots', 'aggression', 'interceptions', 'positioning', 'vision',\n",
    "                    'penalties', 'marking', 'standing_tackle', 'sliding_tackle'\n",
    "                 ]\n",
    "\n",
    "index_list = player_attrib.index.tolist()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>home_player_2</th>\n",
       "      <th>home_player_3</th>\n",
       "      <th>home_player_4</th>\n",
       "      <th>home_player_5</th>\n",
       "      <th>home_player_6</th>\n",
       "      <th>home_player_7</th>\n",
       "      <th>home_player_8</th>\n",
       "      <th>home_player_9</th>\n",
       "      <th>home_player_10</th>\n",
       "      <th>home_player_11</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>172420</td>\n",
       "      <td>5563</td>\n",
       "      <td>183374</td>\n",
       "      <td>147353</td>\n",
       "      <td>151244</td>\n",
       "      <td>158190</td>\n",
       "      <td>154521</td>\n",
       "      <td>4783</td>\n",
       "      <td>71464</td>\n",
       "      <td>83159</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>82943</td>\n",
       "      <td>143411</td>\n",
       "      <td>80604</td>\n",
       "      <td>90119</td>\n",
       "      <td>35850</td>\n",
       "      <td>161654</td>\n",
       "      <td>164219</td>\n",
       "      <td>85500</td>\n",
       "      <td>54549</td>\n",
       "      <td>58329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>9065</td>\n",
       "      <td>8121</td>\n",
       "      <td>79991</td>\n",
       "      <td>23117</td>\n",
       "      <td>57627</td>\n",
       "      <td>108930</td>\n",
       "      <td>89001</td>\n",
       "      <td>74339</td>\n",
       "      <td>182543</td>\n",
       "      <td>180964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>90915</td>\n",
       "      <td>34309</td>\n",
       "      <td>87294</td>\n",
       "      <td>43370</td>\n",
       "      <td>8676</td>\n",
       "      <td>102873</td>\n",
       "      <td>156303</td>\n",
       "      <td>100587</td>\n",
       "      <td>154193</td>\n",
       "      <td>27007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>78275</td>\n",
       "      <td>43795</td>\n",
       "      <td>69299</td>\n",
       "      <td>90654</td>\n",
       "      <td>177068</td>\n",
       "      <td>39836</td>\n",
       "      <td>33074</td>\n",
       "      <td>144323</td>\n",
       "      <td>162799</td>\n",
       "      <td>161137</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       home_player_2  home_player_3  home_player_4  home_player_5  \\\n",
       "id                                                                  \n",
       "23607         172420           5563         183374         147353   \n",
       "23608          82943         143411          80604          90119   \n",
       "23609           9065           8121          79991          23117   \n",
       "23610          90915          34309          87294          43370   \n",
       "23611          78275          43795          69299          90654   \n",
       "\n",
       "       home_player_6  home_player_7  home_player_8  home_player_9  \\\n",
       "id                                                                  \n",
       "23607         151244         158190         154521           4783   \n",
       "23608          35850         161654         164219          85500   \n",
       "23609          57627         108930          89001          74339   \n",
       "23610           8676         102873         156303         100587   \n",
       "23611         177068          39836          33074         144323   \n",
       "\n",
       "       home_player_10  home_player_11  \n",
       "id                                     \n",
       "23607           71464           83159  \n",
       "23608           54549           58329  \n",
       "23609          182543          180964  \n",
       "23610          154193           27007  \n",
       "23611          162799          161137  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the attributes for each player closest to match point\n",
    "home_attribs = df[['date'] + home_fields[1:]].copy()\n",
    "\n",
    "\n",
    "# Map the ids to the corresponding attribute ids\n",
    "for col in home_fields[1:]:\n",
    "    home_attribs[col] = home_attribs.apply(lambda s: get_latest_attribute_idx(s, col), axis=1)\n",
    "\n",
    "# Show mapped attribute ids\n",
    "home_attribs[home_fields[1:]].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>home_overall_rating</th>\n",
       "      <th>home_crossing</th>\n",
       "      <th>home_finishing</th>\n",
       "      <th>home_heading_accuracy</th>\n",
       "      <th>home_short_passing</th>\n",
       "      <th>home_volleys</th>\n",
       "      <th>home_dribbling</th>\n",
       "      <th>home_curve</th>\n",
       "      <th>home_free_kick_accuracy</th>\n",
       "      <th>home_long_passing</th>\n",
       "      <th>...</th>\n",
       "      <th>home_strength</th>\n",
       "      <th>home_long_shots</th>\n",
       "      <th>home_aggression</th>\n",
       "      <th>home_interceptions</th>\n",
       "      <th>home_positioning</th>\n",
       "      <th>home_vision</th>\n",
       "      <th>home_penalties</th>\n",
       "      <th>home_marking</th>\n",
       "      <th>home_standing_tackle</th>\n",
       "      <th>home_sliding_tackle</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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",
       "      <th></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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>72.5</td>\n",
       "      <td>57.2</td>\n",
       "      <td>54.6</td>\n",
       "      <td>61.4</td>\n",
       "      <td>68.7</td>\n",
       "      <td>54.0</td>\n",
       "      <td>62.9</td>\n",
       "      <td>60.0</td>\n",
       "      <td>58.6</td>\n",
       "      <td>66.8</td>\n",
       "      <td>...</td>\n",
       "      <td>69.3</td>\n",
       "      <td>57.5</td>\n",
       "      <td>65.4</td>\n",
       "      <td>56.5</td>\n",
       "      <td>59.9</td>\n",
       "      <td>59.4</td>\n",
       "      <td>57.0</td>\n",
       "      <td>52.8</td>\n",
       "      <td>59.3</td>\n",
       "      <td>56.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>75.8</td>\n",
       "      <td>65.4</td>\n",
       "      <td>59.7</td>\n",
       "      <td>60.1</td>\n",
       "      <td>72.4</td>\n",
       "      <td>59.3</td>\n",
       "      <td>69.7</td>\n",
       "      <td>68.9</td>\n",
       "      <td>62.5</td>\n",
       "      <td>66.0</td>\n",
       "      <td>...</td>\n",
       "      <td>65.5</td>\n",
       "      <td>66.4</td>\n",
       "      <td>67.0</td>\n",
       "      <td>60.8</td>\n",
       "      <td>68.4</td>\n",
       "      <td>66.7</td>\n",
       "      <td>62.2</td>\n",
       "      <td>55.2</td>\n",
       "      <td>60.8</td>\n",
       "      <td>58.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>72.1</td>\n",
       "      <td>61.3</td>\n",
       "      <td>48.2</td>\n",
       "      <td>60.1</td>\n",
       "      <td>67.3</td>\n",
       "      <td>54.0</td>\n",
       "      <td>65.8</td>\n",
       "      <td>61.8</td>\n",
       "      <td>50.6</td>\n",
       "      <td>63.7</td>\n",
       "      <td>...</td>\n",
       "      <td>71.1</td>\n",
       "      <td>57.9</td>\n",
       "      <td>62.8</td>\n",
       "      <td>62.7</td>\n",
       "      <td>57.0</td>\n",
       "      <td>62.2</td>\n",
       "      <td>50.5</td>\n",
       "      <td>53.2</td>\n",
       "      <td>62.0</td>\n",
       "      <td>60.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>72.5</td>\n",
       "      <td>60.7</td>\n",
       "      <td>52.7</td>\n",
       "      <td>61.7</td>\n",
       "      <td>67.9</td>\n",
       "      <td>47.4</td>\n",
       "      <td>66.6</td>\n",
       "      <td>59.2</td>\n",
       "      <td>48.9</td>\n",
       "      <td>62.7</td>\n",
       "      <td>...</td>\n",
       "      <td>70.5</td>\n",
       "      <td>57.1</td>\n",
       "      <td>62.2</td>\n",
       "      <td>59.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>63.6</td>\n",
       "      <td>50.2</td>\n",
       "      <td>49.6</td>\n",
       "      <td>57.2</td>\n",
       "      <td>50.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>73.2</td>\n",
       "      <td>63.6</td>\n",
       "      <td>55.5</td>\n",
       "      <td>61.2</td>\n",
       "      <td>67.2</td>\n",
       "      <td>53.5</td>\n",
       "      <td>66.5</td>\n",
       "      <td>63.3</td>\n",
       "      <td>56.9</td>\n",
       "      <td>65.6</td>\n",
       "      <td>...</td>\n",
       "      <td>67.9</td>\n",
       "      <td>58.3</td>\n",
       "      <td>70.1</td>\n",
       "      <td>56.1</td>\n",
       "      <td>59.8</td>\n",
       "      <td>64.7</td>\n",
       "      <td>62.4</td>\n",
       "      <td>50.5</td>\n",
       "      <td>56.3</td>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       home_overall_rating  home_crossing  home_finishing  \\\n",
       "id                                                          \n",
       "23607                 72.5           57.2            54.6   \n",
       "23608                 75.8           65.4            59.7   \n",
       "23609                 72.1           61.3            48.2   \n",
       "23610                 72.5           60.7            52.7   \n",
       "23611                 73.2           63.6            55.5   \n",
       "\n",
       "       home_heading_accuracy  home_short_passing  home_volleys  \\\n",
       "id                                                               \n",
       "23607                   61.4                68.7          54.0   \n",
       "23608                   60.1                72.4          59.3   \n",
       "23609                   60.1                67.3          54.0   \n",
       "23610                   61.7                67.9          47.4   \n",
       "23611                   61.2                67.2          53.5   \n",
       "\n",
       "       home_dribbling  home_curve  home_free_kick_accuracy  home_long_passing  \\\n",
       "id                                                                              \n",
       "23607            62.9        60.0                     58.6               66.8   \n",
       "23608            69.7        68.9                     62.5               66.0   \n",
       "23609            65.8        61.8                     50.6               63.7   \n",
       "23610            66.6        59.2                     48.9               62.7   \n",
       "23611            66.5        63.3                     56.9               65.6   \n",
       "\n",
       "              ...           home_strength  home_long_shots  home_aggression  \\\n",
       "id            ...                                                             \n",
       "23607         ...                    69.3             57.5             65.4   \n",
       "23608         ...                    65.5             66.4             67.0   \n",
       "23609         ...                    71.1             57.9             62.8   \n",
       "23610         ...                    70.5             57.1             62.2   \n",
       "23611         ...                    67.9             58.3             70.1   \n",
       "\n",
       "       home_interceptions  home_positioning  home_vision  home_penalties  \\\n",
       "id                                                                         \n",
       "23607                56.5              59.9         59.4            57.0   \n",
       "23608                60.8              68.4         66.7            62.2   \n",
       "23609                62.7              57.0         62.2            50.5   \n",
       "23610                59.0              65.0         63.6            50.2   \n",
       "23611                56.1              59.8         64.7            62.4   \n",
       "\n",
       "       home_marking  home_standing_tackle  home_sliding_tackle  \n",
       "id                                                              \n",
       "23607          52.8                  59.3                 56.9  \n",
       "23608          55.2                  60.8                 58.0  \n",
       "23609          53.2                  62.0                 60.6  \n",
       "23610          49.6                  57.2                 50.6  \n",
       "23611          50.5                  56.3                 54.0  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the desired home player attributes\n",
    "\n",
    "for attrib in player_attribs:\n",
    "    df[f'home_{attrib}'] = home_attribs[home_fields[1:]].replace(to_replace=index_list, value=player_attrib[attrib].tolist()) \\\n",
    "                            .mean(axis=1).round(1)\n",
    "\n",
    "df[[f'home_{attrib}' for attrib in player_attribs]].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>away_overall_rating</th>\n",
       "      <th>away_crossing</th>\n",
       "      <th>away_finishing</th>\n",
       "      <th>away_heading_accuracy</th>\n",
       "      <th>away_short_passing</th>\n",
       "      <th>away_volleys</th>\n",
       "      <th>away_dribbling</th>\n",
       "      <th>away_curve</th>\n",
       "      <th>away_free_kick_accuracy</th>\n",
       "      <th>away_long_passing</th>\n",
       "      <th>...</th>\n",
       "      <th>away_strength</th>\n",
       "      <th>away_long_shots</th>\n",
       "      <th>away_aggression</th>\n",
       "      <th>away_interceptions</th>\n",
       "      <th>away_positioning</th>\n",
       "      <th>away_vision</th>\n",
       "      <th>away_penalties</th>\n",
       "      <th>away_marking</th>\n",
       "      <th>away_standing_tackle</th>\n",
       "      <th>away_sliding_tackle</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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",
       "      <th></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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>73.2</td>\n",
       "      <td>64.9</td>\n",
       "      <td>58.4</td>\n",
       "      <td>60.7</td>\n",
       "      <td>69.3</td>\n",
       "      <td>58.2</td>\n",
       "      <td>65.3</td>\n",
       "      <td>63.9</td>\n",
       "      <td>59.8</td>\n",
       "      <td>66.1</td>\n",
       "      <td>...</td>\n",
       "      <td>66.4</td>\n",
       "      <td>65.0</td>\n",
       "      <td>61.9</td>\n",
       "      <td>59.8</td>\n",
       "      <td>67.3</td>\n",
       "      <td>63.8</td>\n",
       "      <td>59.7</td>\n",
       "      <td>55.5</td>\n",
       "      <td>59.8</td>\n",
       "      <td>57.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>76.4</td>\n",
       "      <td>61.4</td>\n",
       "      <td>60.1</td>\n",
       "      <td>60.9</td>\n",
       "      <td>74.7</td>\n",
       "      <td>54.7</td>\n",
       "      <td>68.9</td>\n",
       "      <td>67.3</td>\n",
       "      <td>57.1</td>\n",
       "      <td>69.2</td>\n",
       "      <td>...</td>\n",
       "      <td>69.4</td>\n",
       "      <td>59.7</td>\n",
       "      <td>72.9</td>\n",
       "      <td>65.8</td>\n",
       "      <td>68.0</td>\n",
       "      <td>70.1</td>\n",
       "      <td>53.0</td>\n",
       "      <td>52.9</td>\n",
       "      <td>61.4</td>\n",
       "      <td>57.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>72.3</td>\n",
       "      <td>61.3</td>\n",
       "      <td>52.0</td>\n",
       "      <td>57.7</td>\n",
       "      <td>70.6</td>\n",
       "      <td>49.8</td>\n",
       "      <td>71.3</td>\n",
       "      <td>62.3</td>\n",
       "      <td>57.5</td>\n",
       "      <td>65.9</td>\n",
       "      <td>...</td>\n",
       "      <td>60.9</td>\n",
       "      <td>60.7</td>\n",
       "      <td>61.7</td>\n",
       "      <td>59.8</td>\n",
       "      <td>63.3</td>\n",
       "      <td>66.9</td>\n",
       "      <td>57.7</td>\n",
       "      <td>46.1</td>\n",
       "      <td>56.4</td>\n",
       "      <td>52.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>73.7</td>\n",
       "      <td>66.2</td>\n",
       "      <td>54.5</td>\n",
       "      <td>62.1</td>\n",
       "      <td>70.8</td>\n",
       "      <td>54.5</td>\n",
       "      <td>68.3</td>\n",
       "      <td>66.1</td>\n",
       "      <td>59.7</td>\n",
       "      <td>67.6</td>\n",
       "      <td>...</td>\n",
       "      <td>68.1</td>\n",
       "      <td>60.9</td>\n",
       "      <td>66.6</td>\n",
       "      <td>59.9</td>\n",
       "      <td>61.3</td>\n",
       "      <td>66.1</td>\n",
       "      <td>57.5</td>\n",
       "      <td>49.6</td>\n",
       "      <td>57.0</td>\n",
       "      <td>56.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>71.4</td>\n",
       "      <td>59.6</td>\n",
       "      <td>60.5</td>\n",
       "      <td>58.7</td>\n",
       "      <td>67.5</td>\n",
       "      <td>52.1</td>\n",
       "      <td>64.8</td>\n",
       "      <td>66.4</td>\n",
       "      <td>61.2</td>\n",
       "      <td>66.9</td>\n",
       "      <td>...</td>\n",
       "      <td>70.6</td>\n",
       "      <td>64.0</td>\n",
       "      <td>62.2</td>\n",
       "      <td>55.2</td>\n",
       "      <td>62.3</td>\n",
       "      <td>59.5</td>\n",
       "      <td>59.2</td>\n",
       "      <td>49.4</td>\n",
       "      <td>55.0</td>\n",
       "      <td>53.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       away_overall_rating  away_crossing  away_finishing  \\\n",
       "id                                                          \n",
       "23607                 73.2           64.9            58.4   \n",
       "23608                 76.4           61.4            60.1   \n",
       "23609                 72.3           61.3            52.0   \n",
       "23610                 73.7           66.2            54.5   \n",
       "23611                 71.4           59.6            60.5   \n",
       "\n",
       "       away_heading_accuracy  away_short_passing  away_volleys  \\\n",
       "id                                                               \n",
       "23607                   60.7                69.3          58.2   \n",
       "23608                   60.9                74.7          54.7   \n",
       "23609                   57.7                70.6          49.8   \n",
       "23610                   62.1                70.8          54.5   \n",
       "23611                   58.7                67.5          52.1   \n",
       "\n",
       "       away_dribbling  away_curve  away_free_kick_accuracy  away_long_passing  \\\n",
       "id                                                                              \n",
       "23607            65.3        63.9                     59.8               66.1   \n",
       "23608            68.9        67.3                     57.1               69.2   \n",
       "23609            71.3        62.3                     57.5               65.9   \n",
       "23610            68.3        66.1                     59.7               67.6   \n",
       "23611            64.8        66.4                     61.2               66.9   \n",
       "\n",
       "              ...           away_strength  away_long_shots  away_aggression  \\\n",
       "id            ...                                                             \n",
       "23607         ...                    66.4             65.0             61.9   \n",
       "23608         ...                    69.4             59.7             72.9   \n",
       "23609         ...                    60.9             60.7             61.7   \n",
       "23610         ...                    68.1             60.9             66.6   \n",
       "23611         ...                    70.6             64.0             62.2   \n",
       "\n",
       "       away_interceptions  away_positioning  away_vision  away_penalties  \\\n",
       "id                                                                         \n",
       "23607                59.8              67.3         63.8            59.7   \n",
       "23608                65.8              68.0         70.1            53.0   \n",
       "23609                59.8              63.3         66.9            57.7   \n",
       "23610                59.9              61.3         66.1            57.5   \n",
       "23611                55.2              62.3         59.5            59.2   \n",
       "\n",
       "       away_marking  away_standing_tackle  away_sliding_tackle  \n",
       "id                                                              \n",
       "23607          55.5                  59.8                 57.0  \n",
       "23608          52.9                  61.4                 57.2  \n",
       "23609          46.1                  56.4                 52.1  \n",
       "23610          49.6                  57.0                 56.4  \n",
       "23611          49.4                  55.0                 53.3  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the average attributes for the away players\n",
    "away_attribs = df[['date'] + away_fields[1:]].copy()\n",
    "\n",
    "\n",
    "# Map the ids to the corresponding attribute ids\n",
    "for col in away_fields[1:]:\n",
    "    away_attribs[col] = away_attribs.apply(lambda s: get_latest_attribute_idx(s, col), axis=1)\n",
    "\n",
    "# Get the desired away player attributes\n",
    "\n",
    "for attrib in player_attribs:\n",
    "    df[f'away_{attrib}'] = away_attribs[away_fields[1:]].replace(to_replace=index_list, value=player_attrib[attrib].tolist()) \\\n",
    "                            .mean(axis=1).round(1)\n",
    "\n",
    "df[[f'away_{attrib}' for attrib in player_attribs]].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>player_api_id</th>\n",
       "      <th>date</th>\n",
       "      <th>gk_diving</th>\n",
       "      <th>gk_rating</th>\n",
       "      <th>gk_handling</th>\n",
       "      <th>gk_kicking</th>\n",
       "      <th>gk_positioning</th>\n",
       "      <th>gk_reflexes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>505942</td>\n",
       "      <td>2016-02-18</td>\n",
       "      <td>6.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>505942</td>\n",
       "      <td>2015-11-19</td>\n",
       "      <td>6.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>505942</td>\n",
       "      <td>2015-09-21</td>\n",
       "      <td>6.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>505942</td>\n",
       "      <td>2015-03-20</td>\n",
       "      <td>5.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>505942</td>\n",
       "      <td>2007-02-22</td>\n",
       "      <td>5.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   player_api_id       date  gk_diving  gk_rating  gk_handling  gk_kicking  \\\n",
       "0         505942 2016-02-18        6.0       67.0         11.0        10.0   \n",
       "1         505942 2015-11-19        6.0       67.0         11.0        10.0   \n",
       "2         505942 2015-09-21        6.0       62.0         11.0        10.0   \n",
       "3         505942 2015-03-20        5.0       61.0         10.0         9.0   \n",
       "4         505942 2007-02-22        5.0       61.0         10.0         9.0   \n",
       "\n",
       "   gk_positioning  gk_reflexes  \n",
       "0             8.0          8.0  \n",
       "1             8.0          8.0  \n",
       "2             8.0          8.0  \n",
       "3             7.0          7.0  \n",
       "4             7.0          7.0  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load GK Attributes\n",
    "gk_attrib = pd.read_sql_query(\"\"\"\n",
    "        SELECT  player_api_id, date, gk_diving, overall_rating as gk_rating,\n",
    "                gk_handling, gk_kicking, gk_positioning, gk_reflexes\n",
    "        FROM player_attributes\n",
    "\"\"\", conn)\n",
    "\n",
    "gk_attrib['date'] = gk_attrib['date'].apply(pd.to_datetime)\n",
    "\n",
    "gk_attrib.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>home_gk_diving</th>\n",
       "      <th>home_gk_handling</th>\n",
       "      <th>home_gk_kicking</th>\n",
       "      <th>home_gk_positioning</th>\n",
       "      <th>home_gk_reflexes</th>\n",
       "      <th>home_gk_rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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>23607</th>\n",
       "      <td>78.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>72.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>90.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>77.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>70.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>78.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       home_gk_diving  home_gk_handling  home_gk_kicking  home_gk_positioning  \\\n",
       "id                                                                              \n",
       "23607            78.0              66.0             63.0                 65.0   \n",
       "23608            90.0              72.0             74.0                 73.0   \n",
       "23609            77.0              68.0             72.0                 69.0   \n",
       "23610            70.0              76.0             67.0                 64.0   \n",
       "23611            78.0              79.0             62.0                 77.0   \n",
       "\n",
       "       home_gk_reflexes  home_gk_rating  \n",
       "id                                       \n",
       "23607              81.0            72.0  \n",
       "23608              90.0            81.0  \n",
       "23609              80.0            73.0  \n",
       "23610              78.0            71.0  \n",
       "23611              75.0            76.0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load GK attributes for home team\n",
    "def get_latest_attribute_idx(gk, col_name):\n",
    "    earlier_dates = gk_attrib.loc[(gk_attrib['player_api_id'] == gk[col_name]) \n",
    "                                      & (gk_attrib['date'] < pd.to_datetime(gk['date']))]\n",
    "    return earlier_dates[earlier_dates['date'] == earlier_dates['date'].max()].index[0]\n",
    "\n",
    "gk_attributes = ['gk_diving', 'gk_handling', 'gk_kicking', 'gk_positioning', 'gk_reflexes', 'gk_rating']\n",
    "\n",
    "for attrib in gk_attributes:\n",
    "    df[f'home_{attrib}'] = df[['date', 'home_player_1']].apply(lambda s: get_latest_attribute_idx(s, 'home_player_1'), axis=1) \\\n",
    "                             .replace(to_replace=index_list, value=gk_attrib[attrib].tolist())\n",
    "\n",
    "df[[f'home_{attrib}' for attrib in gk_attributes]].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>away_gk_diving</th>\n",
       "      <th>away_gk_handling</th>\n",
       "      <th>away_gk_kicking</th>\n",
       "      <th>away_gk_positioning</th>\n",
       "      <th>away_gk_reflexes</th>\n",
       "      <th>away_gk_rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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>23607</th>\n",
       "      <td>90.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>67.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>81.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>77.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>65.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>73.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       away_gk_diving  away_gk_handling  away_gk_kicking  away_gk_positioning  \\\n",
       "id                                                                              \n",
       "23607            90.0              72.0             74.0                 73.0   \n",
       "23608            67.0              79.0             75.0                 80.0   \n",
       "23609            81.0              74.0             68.0                 69.0   \n",
       "23610            77.0              67.0             73.0                 65.0   \n",
       "23611            73.0              72.0             66.0                 75.0   \n",
       "\n",
       "       away_gk_reflexes  away_gk_rating  \n",
       "id                                       \n",
       "23607              90.0            81.0  \n",
       "23608              68.0            74.0  \n",
       "23609              74.0            74.0  \n",
       "23610              76.0            71.0  \n",
       "23611              79.0            74.0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load GK attributes for away team\n",
    "\n",
    "for attrib in gk_attributes:\n",
    "    df[f'away_{attrib}'] = df[['date', 'away_player_1']].apply(lambda s: get_latest_attribute_idx(s, 'away_player_1'), axis=1) \\\n",
    "                             .replace(to_replace=index_list, value=gk_attrib[attrib].tolist())\n",
    "\n",
    "df[[f'away_{attrib}' for attrib in gk_attributes]].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>home_total_overall_rating</th>\n",
       "      <th>away_total_overall_rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>72.5</td>\n",
       "      <td>73.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>76.3</td>\n",
       "      <td>76.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>72.2</td>\n",
       "      <td>72.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>72.4</td>\n",
       "      <td>73.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>73.5</td>\n",
       "      <td>71.6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       home_total_overall_rating  away_total_overall_rating\n",
       "id                                                         \n",
       "23607                       72.5                       73.9\n",
       "23608                       76.3                       76.2\n",
       "23609                       72.2                       72.5\n",
       "23610                       72.4                       73.5\n",
       "23611                       73.5                       71.6"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate total_overall_rating\n",
    "\n",
    "df['home_total_overall_rating'] = (( 10 * df['home_overall_rating'] + df['home_gk_rating'] ) / 11).round(1)\n",
    "df['away_total_overall_rating'] = (( 10 * df['away_overall_rating'] + df['away_gk_rating'] ) / 11).round(1)\n",
    "\n",
    "df[['home_total_overall_rating', 'away_total_overall_rating']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 1C: Team attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team_api_id</th>\n",
       "      <th>date</th>\n",
       "      <th>buildUpPlaySpeed</th>\n",
       "      <th>buildUpPlaySpeedClass</th>\n",
       "      <th>buildUpPlayDribblingClass</th>\n",
       "      <th>buildUpPlayPassing</th>\n",
       "      <th>buildUpPlayPassingClass</th>\n",
       "      <th>buildUpPlayPositioningClass</th>\n",
       "      <th>chanceCreationPassing</th>\n",
       "      <th>chanceCreationPassingClass</th>\n",
       "      <th>...</th>\n",
       "      <th>chanceCreationShooting</th>\n",
       "      <th>chanceCreationShootingClass</th>\n",
       "      <th>chanceCreationPositioningClass</th>\n",
       "      <th>defencePressure</th>\n",
       "      <th>defencePressureClass</th>\n",
       "      <th>defenceAggression</th>\n",
       "      <th>defenceAggressionClass</th>\n",
       "      <th>defenceTeamWidth</th>\n",
       "      <th>defenceTeamWidthClass</th>\n",
       "      <th>defenceDefenderLineClass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9930</td>\n",
       "      <td>2010-02-22</td>\n",
       "      <td>60</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>Little</td>\n",
       "      <td>50</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>60</td>\n",
       "      <td>Normal</td>\n",
       "      <td>...</td>\n",
       "      <td>55</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>50</td>\n",
       "      <td>Medium</td>\n",
       "      <td>55</td>\n",
       "      <td>Press</td>\n",
       "      <td>45</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9930</td>\n",
       "      <td>2014-09-19</td>\n",
       "      <td>52</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>Normal</td>\n",
       "      <td>56</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>54</td>\n",
       "      <td>Normal</td>\n",
       "      <td>...</td>\n",
       "      <td>64</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>47</td>\n",
       "      <td>Medium</td>\n",
       "      <td>44</td>\n",
       "      <td>Press</td>\n",
       "      <td>54</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9930</td>\n",
       "      <td>2015-09-10</td>\n",
       "      <td>47</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>Normal</td>\n",
       "      <td>54</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>54</td>\n",
       "      <td>Normal</td>\n",
       "      <td>...</td>\n",
       "      <td>64</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>47</td>\n",
       "      <td>Medium</td>\n",
       "      <td>44</td>\n",
       "      <td>Press</td>\n",
       "      <td>54</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8485</td>\n",
       "      <td>2010-02-22</td>\n",
       "      <td>70</td>\n",
       "      <td>Fast</td>\n",
       "      <td>Little</td>\n",
       "      <td>70</td>\n",
       "      <td>Long</td>\n",
       "      <td>Organised</td>\n",
       "      <td>70</td>\n",
       "      <td>Risky</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>Lots</td>\n",
       "      <td>Organised</td>\n",
       "      <td>60</td>\n",
       "      <td>Medium</td>\n",
       "      <td>70</td>\n",
       "      <td>Double</td>\n",
       "      <td>70</td>\n",
       "      <td>Wide</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8485</td>\n",
       "      <td>2011-02-22</td>\n",
       "      <td>47</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>Little</td>\n",
       "      <td>52</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>53</td>\n",
       "      <td>Normal</td>\n",
       "      <td>...</td>\n",
       "      <td>52</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>47</td>\n",
       "      <td>Medium</td>\n",
       "      <td>47</td>\n",
       "      <td>Press</td>\n",
       "      <td>52</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   team_api_id       date  buildUpPlaySpeed buildUpPlaySpeedClass  \\\n",
       "0         9930 2010-02-22                60              Balanced   \n",
       "1         9930 2014-09-19                52              Balanced   \n",
       "2         9930 2015-09-10                47              Balanced   \n",
       "3         8485 2010-02-22                70                  Fast   \n",
       "4         8485 2011-02-22                47              Balanced   \n",
       "\n",
       "  buildUpPlayDribblingClass  buildUpPlayPassing buildUpPlayPassingClass  \\\n",
       "0                    Little                  50                   Mixed   \n",
       "1                    Normal                  56                   Mixed   \n",
       "2                    Normal                  54                   Mixed   \n",
       "3                    Little                  70                    Long   \n",
       "4                    Little                  52                   Mixed   \n",
       "\n",
       "  buildUpPlayPositioningClass  chanceCreationPassing  \\\n",
       "0                   Organised                     60   \n",
       "1                   Organised                     54   \n",
       "2                   Organised                     54   \n",
       "3                   Organised                     70   \n",
       "4                   Organised                     53   \n",
       "\n",
       "  chanceCreationPassingClass            ...             \\\n",
       "0                     Normal            ...              \n",
       "1                     Normal            ...              \n",
       "2                     Normal            ...              \n",
       "3                      Risky            ...              \n",
       "4                     Normal            ...              \n",
       "\n",
       "   chanceCreationShooting chanceCreationShootingClass  \\\n",
       "0                      55                      Normal   \n",
       "1                      64                      Normal   \n",
       "2                      64                      Normal   \n",
       "3                      70                        Lots   \n",
       "4                      52                      Normal   \n",
       "\n",
       "   chanceCreationPositioningClass defencePressure defencePressureClass  \\\n",
       "0                       Organised              50               Medium   \n",
       "1                       Organised              47               Medium   \n",
       "2                       Organised              47               Medium   \n",
       "3                       Organised              60               Medium   \n",
       "4                       Organised              47               Medium   \n",
       "\n",
       "   defenceAggression defenceAggressionClass  defenceTeamWidth  \\\n",
       "0                 55                  Press                45   \n",
       "1                 44                  Press                54   \n",
       "2                 44                  Press                54   \n",
       "3                 70                 Double                70   \n",
       "4                 47                  Press                52   \n",
       "\n",
       "  defenceTeamWidthClass  defenceDefenderLineClass  \n",
       "0                Normal                     Cover  \n",
       "1                Normal                     Cover  \n",
       "2                Normal                     Cover  \n",
       "3                  Wide                     Cover  \n",
       "4                Normal                     Cover  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load Player attributes\n",
    "team_attrib = pd.read_sql_query(\"\"\"\n",
    "        SELECT  team_api_id, date, buildUpPlaySpeed, buildUpPlaySpeedClass, buildUpPlayDribblingClass,\n",
    "                buildUpPlayPassing, buildUpPlayPassingClass, buildUpPlayPositioningClass, chanceCreationPassing,\n",
    "                chanceCreationPassingClass, chanceCreationCrossing, chanceCreationCrossingClass, chanceCreationShooting,\n",
    "                chanceCreationShootingClass, chanceCreationPositioningClass, defencePressure, defencePressureClass,\n",
    "                defenceAggression, defenceAggressionClass, defenceTeamWidth, defenceTeamWidthClass, defenceDefenderLineClass\n",
    "        FROM team_attributes\n",
    "\"\"\", conn)\n",
    "\n",
    "team_attrib['date'] = team_attrib['date'].apply(pd.to_datetime)\n",
    "\n",
    "team_attrib.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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_api_id</th>\n",
       "      <th>away_team_api_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>2014-03-02</td>\n",
       "      <td>8370</td>\n",
       "      <td>10267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>2014-03-09</td>\n",
       "      <td>10267</td>\n",
       "      <td>8315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>2014-03-08</td>\n",
       "      <td>7878</td>\n",
       "      <td>10205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>2014-03-08</td>\n",
       "      <td>8603</td>\n",
       "      <td>8305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>2014-03-09</td>\n",
       "      <td>8558</td>\n",
       "      <td>10268</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            date  home_team_api_id  away_team_api_id\n",
       "id                                                  \n",
       "23607 2014-03-02              8370             10267\n",
       "23608 2014-03-09             10267              8315\n",
       "23609 2014-03-08              7878             10205\n",
       "23610 2014-03-08              8603              8305\n",
       "23611 2014-03-09              8558             10268"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Change all dates before the min date in team attributes to the minimum\n",
    "\n",
    "df_copy = df[['date', 'home_team_api_id', 'away_team_api_id']].copy()\n",
    "\n",
    "df_copy.loc[df_copy['date'] < team_attrib['date'].min(), 'date'] = team_attrib['date'].min()\n",
    "\n",
    "df_copy.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set up helper functions and variables\n",
    "\n",
    "def get_latest_attribute_idx(team, col_name):\n",
    "    try:\n",
    "        earlier_dates = team_attrib.loc[(team_attrib['team_api_id'] == team[col_name])\n",
    "                                    & (team_attrib['date'] <= pd.to_datetime(team['date']))]\n",
    "        index = earlier_dates[earlier_dates['date'] == earlier_dates['date'].max()].index[0]\n",
    "    except IndexError as e:\n",
    "        # No avilable team attributes before the match date, so we take the earliest available date\n",
    "        earlier_dates = team_attrib.loc[team_attrib['team_api_id'] == team[col_name]]\n",
    "        try:\n",
    "            index = earlier_dates[earlier_dates['date'] == earlier_dates['date'].min()].index[0]\n",
    "        except IndexError as e:\n",
    "            index = 9930\n",
    "    return index\n",
    "\n",
    "\n",
    "index_list = team_attrib.index.tolist()\n",
    "\n",
    "team_attribs = ['buildUpPlaySpeed', 'buildUpPlaySpeedClass', 'buildUpPlayDribblingClass',\n",
    "                'buildUpPlayPassing', 'buildUpPlayPassingClass', 'buildUpPlayPositioningClass', 'chanceCreationPassing',\n",
    "                'chanceCreationPassingClass', 'chanceCreationCrossing', 'chanceCreationCrossingClass', 'chanceCreationShooting',\n",
    "                'chanceCreationShootingClass', 'chanceCreationPositioningClass', 'defencePressure',\n",
    "                'defencePressureClass', 'defenceAggression', 'defenceAggressionClass', 'defenceTeamWidth',\n",
    "                'defenceTeamWidthClass', 'defenceDefenderLineClass']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>home_buildUpPlaySpeed</th>\n",
       "      <th>home_buildUpPlaySpeedClass</th>\n",
       "      <th>home_buildUpPlayDribblingClass</th>\n",
       "      <th>home_buildUpPlayPassing</th>\n",
       "      <th>home_buildUpPlayPassingClass</th>\n",
       "      <th>home_buildUpPlayPositioningClass</th>\n",
       "      <th>home_chanceCreationPassing</th>\n",
       "      <th>home_chanceCreationPassingClass</th>\n",
       "      <th>home_chanceCreationCrossing</th>\n",
       "      <th>home_chanceCreationCrossingClass</th>\n",
       "      <th>home_chanceCreationShooting</th>\n",
       "      <th>home_chanceCreationShootingClass</th>\n",
       "      <th>home_chanceCreationPositioningClass</th>\n",
       "      <th>home_defencePressure</th>\n",
       "      <th>home_defencePressureClass</th>\n",
       "      <th>home_defenceAggression</th>\n",
       "      <th>home_defenceAggressionClass</th>\n",
       "      <th>home_defenceTeamWidth</th>\n",
       "      <th>home_defenceTeamWidthClass</th>\n",
       "      <th>home_defenceDefenderLineClass</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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",
       "      <th></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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>45</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>Little</td>\n",
       "      <td>44</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Free Form</td>\n",
       "      <td>44</td>\n",
       "      <td>Normal</td>\n",
       "      <td>44</td>\n",
       "      <td>Normal</td>\n",
       "      <td>47</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>63</td>\n",
       "      <td>Medium</td>\n",
       "      <td>51</td>\n",
       "      <td>Press</td>\n",
       "      <td>59</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>20</td>\n",
       "      <td>Slow</td>\n",
       "      <td>Little</td>\n",
       "      <td>35</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>38</td>\n",
       "      <td>Normal</td>\n",
       "      <td>56</td>\n",
       "      <td>Normal</td>\n",
       "      <td>55</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>51</td>\n",
       "      <td>Medium</td>\n",
       "      <td>38</td>\n",
       "      <td>Press</td>\n",
       "      <td>59</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>52</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>Little</td>\n",
       "      <td>37</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>72</td>\n",
       "      <td>Risky</td>\n",
       "      <td>76</td>\n",
       "      <td>Lots</td>\n",
       "      <td>52</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>49</td>\n",
       "      <td>Medium</td>\n",
       "      <td>45</td>\n",
       "      <td>Press</td>\n",
       "      <td>68</td>\n",
       "      <td>Wide</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>59</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>Little</td>\n",
       "      <td>37</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>59</td>\n",
       "      <td>Normal</td>\n",
       "      <td>43</td>\n",
       "      <td>Normal</td>\n",
       "      <td>53</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>58</td>\n",
       "      <td>Medium</td>\n",
       "      <td>49</td>\n",
       "      <td>Press</td>\n",
       "      <td>58</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>50</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>Little</td>\n",
       "      <td>44</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>52</td>\n",
       "      <td>Normal</td>\n",
       "      <td>60</td>\n",
       "      <td>Normal</td>\n",
       "      <td>40</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Free Form</td>\n",
       "      <td>37</td>\n",
       "      <td>Medium</td>\n",
       "      <td>40</td>\n",
       "      <td>Press</td>\n",
       "      <td>45</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       home_buildUpPlaySpeed home_buildUpPlaySpeedClass  \\\n",
       "id                                                        \n",
       "23607                     45                   Balanced   \n",
       "23608                     20                       Slow   \n",
       "23609                     52                   Balanced   \n",
       "23610                     59                   Balanced   \n",
       "23611                     50                   Balanced   \n",
       "\n",
       "      home_buildUpPlayDribblingClass  home_buildUpPlayPassing  \\\n",
       "id                                                              \n",
       "23607                         Little                       44   \n",
       "23608                         Little                       35   \n",
       "23609                         Little                       37   \n",
       "23610                         Little                       37   \n",
       "23611                         Little                       44   \n",
       "\n",
       "      home_buildUpPlayPassingClass home_buildUpPlayPositioningClass  \\\n",
       "id                                                                    \n",
       "23607                        Mixed                        Free Form   \n",
       "23608                        Mixed                        Organised   \n",
       "23609                        Mixed                        Organised   \n",
       "23610                        Mixed                        Organised   \n",
       "23611                        Mixed                        Organised   \n",
       "\n",
       "       home_chanceCreationPassing home_chanceCreationPassingClass  \\\n",
       "id                                                                  \n",
       "23607                          44                          Normal   \n",
       "23608                          38                          Normal   \n",
       "23609                          72                           Risky   \n",
       "23610                          59                          Normal   \n",
       "23611                          52                          Normal   \n",
       "\n",
       "       home_chanceCreationCrossing home_chanceCreationCrossingClass  \\\n",
       "id                                                                    \n",
       "23607                           44                           Normal   \n",
       "23608                           56                           Normal   \n",
       "23609                           76                             Lots   \n",
       "23610                           43                           Normal   \n",
       "23611                           60                           Normal   \n",
       "\n",
       "       home_chanceCreationShooting home_chanceCreationShootingClass  \\\n",
       "id                                                                    \n",
       "23607                           47                           Normal   \n",
       "23608                           55                           Normal   \n",
       "23609                           52                           Normal   \n",
       "23610                           53                           Normal   \n",
       "23611                           40                           Normal   \n",
       "\n",
       "      home_chanceCreationPositioningClass  home_defencePressure  \\\n",
       "id                                                                \n",
       "23607                           Organised                    63   \n",
       "23608                           Organised                    51   \n",
       "23609                           Organised                    49   \n",
       "23610                           Organised                    58   \n",
       "23611                           Free Form                    37   \n",
       "\n",
       "      home_defencePressureClass  home_defenceAggression  \\\n",
       "id                                                        \n",
       "23607                    Medium                      51   \n",
       "23608                    Medium                      38   \n",
       "23609                    Medium                      45   \n",
       "23610                    Medium                      49   \n",
       "23611                    Medium                      40   \n",
       "\n",
       "      home_defenceAggressionClass  home_defenceTeamWidth  \\\n",
       "id                                                         \n",
       "23607                       Press                     59   \n",
       "23608                       Press                     59   \n",
       "23609                       Press                     68   \n",
       "23610                       Press                     58   \n",
       "23611                       Press                     45   \n",
       "\n",
       "      home_defenceTeamWidthClass home_defenceDefenderLineClass  \n",
       "id                                                              \n",
       "23607                     Normal                         Cover  \n",
       "23608                     Normal                         Cover  \n",
       "23609                       Wide                         Cover  \n",
       "23610                     Normal                         Cover  \n",
       "23611                     Normal                         Cover  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get home team attributes\n",
    "\n",
    "for attrib in team_attribs:\n",
    "    df[f'home_{attrib}'] = df_copy[['date', 'home_team_api_id']] \\\n",
    "                                     .apply(lambda s: get_latest_attribute_idx(s, 'home_team_api_id'), axis=1) \\\n",
    "                                     .replace(to_replace=index_list, value=team_attrib[attrib].tolist())\n",
    "\n",
    "df[[f'home_{attrib}' for attrib in team_attribs]].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>away_buildUpPlaySpeed</th>\n",
       "      <th>away_buildUpPlaySpeedClass</th>\n",
       "      <th>away_buildUpPlayDribblingClass</th>\n",
       "      <th>away_buildUpPlayPassing</th>\n",
       "      <th>away_buildUpPlayPassingClass</th>\n",
       "      <th>away_buildUpPlayPositioningClass</th>\n",
       "      <th>away_chanceCreationPassing</th>\n",
       "      <th>away_chanceCreationPassingClass</th>\n",
       "      <th>away_chanceCreationCrossing</th>\n",
       "      <th>away_chanceCreationCrossingClass</th>\n",
       "      <th>away_chanceCreationShooting</th>\n",
       "      <th>away_chanceCreationShootingClass</th>\n",
       "      <th>away_chanceCreationPositioningClass</th>\n",
       "      <th>away_defencePressure</th>\n",
       "      <th>away_defencePressureClass</th>\n",
       "      <th>away_defenceAggression</th>\n",
       "      <th>away_defenceAggressionClass</th>\n",
       "      <th>away_defenceTeamWidth</th>\n",
       "      <th>away_defenceTeamWidthClass</th>\n",
       "      <th>away_defenceDefenderLineClass</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</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",
       "      <th></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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23607</th>\n",
       "      <td>20</td>\n",
       "      <td>Slow</td>\n",
       "      <td>Little</td>\n",
       "      <td>35</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>38</td>\n",
       "      <td>Normal</td>\n",
       "      <td>56</td>\n",
       "      <td>Normal</td>\n",
       "      <td>55</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>51</td>\n",
       "      <td>Medium</td>\n",
       "      <td>38</td>\n",
       "      <td>Press</td>\n",
       "      <td>59</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23608</th>\n",
       "      <td>46</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>Little</td>\n",
       "      <td>38</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>52</td>\n",
       "      <td>Normal</td>\n",
       "      <td>73</td>\n",
       "      <td>Lots</td>\n",
       "      <td>37</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Free Form</td>\n",
       "      <td>37</td>\n",
       "      <td>Medium</td>\n",
       "      <td>60</td>\n",
       "      <td>Press</td>\n",
       "      <td>68</td>\n",
       "      <td>Wide</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23609</th>\n",
       "      <td>24</td>\n",
       "      <td>Slow</td>\n",
       "      <td>Little</td>\n",
       "      <td>26</td>\n",
       "      <td>Short</td>\n",
       "      <td>Organised</td>\n",
       "      <td>43</td>\n",
       "      <td>Normal</td>\n",
       "      <td>57</td>\n",
       "      <td>Normal</td>\n",
       "      <td>58</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>51</td>\n",
       "      <td>Medium</td>\n",
       "      <td>51</td>\n",
       "      <td>Press</td>\n",
       "      <td>56</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23610</th>\n",
       "      <td>29</td>\n",
       "      <td>Slow</td>\n",
       "      <td>Little</td>\n",
       "      <td>40</td>\n",
       "      <td>Mixed</td>\n",
       "      <td>Organised</td>\n",
       "      <td>58</td>\n",
       "      <td>Normal</td>\n",
       "      <td>42</td>\n",
       "      <td>Normal</td>\n",
       "      <td>52</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>49</td>\n",
       "      <td>Medium</td>\n",
       "      <td>45</td>\n",
       "      <td>Press</td>\n",
       "      <td>47</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23611</th>\n",
       "      <td>55</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>Little</td>\n",
       "      <td>73</td>\n",
       "      <td>Long</td>\n",
       "      <td>Organised</td>\n",
       "      <td>53</td>\n",
       "      <td>Normal</td>\n",
       "      <td>54</td>\n",
       "      <td>Normal</td>\n",
       "      <td>57</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>49</td>\n",
       "      <td>Medium</td>\n",
       "      <td>51</td>\n",
       "      <td>Press</td>\n",
       "      <td>58</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       away_buildUpPlaySpeed away_buildUpPlaySpeedClass  \\\n",
       "id                                                        \n",
       "23607                     20                       Slow   \n",
       "23608                     46                   Balanced   \n",
       "23609                     24                       Slow   \n",
       "23610                     29                       Slow   \n",
       "23611                     55                   Balanced   \n",
       "\n",
       "      away_buildUpPlayDribblingClass  away_buildUpPlayPassing  \\\n",
       "id                                                              \n",
       "23607                         Little                       35   \n",
       "23608                         Little                       38   \n",
       "23609                         Little                       26   \n",
       "23610                         Little                       40   \n",
       "23611                         Little                       73   \n",
       "\n",
       "      away_buildUpPlayPassingClass away_buildUpPlayPositioningClass  \\\n",
       "id                                                                    \n",
       "23607                        Mixed                        Organised   \n",
       "23608                        Mixed                        Organised   \n",
       "23609                        Short                        Organised   \n",
       "23610                        Mixed                        Organised   \n",
       "23611                         Long                        Organised   \n",
       "\n",
       "       away_chanceCreationPassing away_chanceCreationPassingClass  \\\n",
       "id                                                                  \n",
       "23607                          38                          Normal   \n",
       "23608                          52                          Normal   \n",
       "23609                          43                          Normal   \n",
       "23610                          58                          Normal   \n",
       "23611                          53                          Normal   \n",
       "\n",
       "       away_chanceCreationCrossing away_chanceCreationCrossingClass  \\\n",
       "id                                                                    \n",
       "23607                           56                           Normal   \n",
       "23608                           73                             Lots   \n",
       "23609                           57                           Normal   \n",
       "23610                           42                           Normal   \n",
       "23611                           54                           Normal   \n",
       "\n",
       "       away_chanceCreationShooting away_chanceCreationShootingClass  \\\n",
       "id                                                                    \n",
       "23607                           55                           Normal   \n",
       "23608                           37                           Normal   \n",
       "23609                           58                           Normal   \n",
       "23610                           52                           Normal   \n",
       "23611                           57                           Normal   \n",
       "\n",
       "      away_chanceCreationPositioningClass  away_defencePressure  \\\n",
       "id                                                                \n",
       "23607                           Organised                    51   \n",
       "23608                           Free Form                    37   \n",
       "23609                           Organised                    51   \n",
       "23610                           Organised                    49   \n",
       "23611                           Organised                    49   \n",
       "\n",
       "      away_defencePressureClass  away_defenceAggression  \\\n",
       "id                                                        \n",
       "23607                    Medium                      38   \n",
       "23608                    Medium                      60   \n",
       "23609                    Medium                      51   \n",
       "23610                    Medium                      45   \n",
       "23611                    Medium                      51   \n",
       "\n",
       "      away_defenceAggressionClass  away_defenceTeamWidth  \\\n",
       "id                                                         \n",
       "23607                       Press                     59   \n",
       "23608                       Press                     68   \n",
       "23609                       Press                     56   \n",
       "23610                       Press                     47   \n",
       "23611                       Press                     58   \n",
       "\n",
       "      away_defenceTeamWidthClass away_defenceDefenderLineClass  \n",
       "id                                                              \n",
       "23607                     Normal                         Cover  \n",
       "23608                       Wide                         Cover  \n",
       "23609                     Normal                         Cover  \n",
       "23610                     Normal                         Cover  \n",
       "23611                     Normal                         Cover  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get away team attributes\n",
    "for attrib in team_attribs:\n",
    "    df[f'away_{attrib}'] = df_copy[['date', 'away_team_api_id']] \\\n",
    "                                     .apply(lambda s: get_latest_attribute_idx(s, 'away_team_api_id'), axis=1) \\\n",
    "                                     .replace(to_replace=index_list, value=team_attrib[attrib].tolist())\n",
    "\n",
    "df[[f'away_{attrib}' for attrib in team_attribs]].head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Task 1D: Stadium Information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team_api_id</th>\n",
       "      <th>capacity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8722</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8350</td>\n",
       "      <td>49780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8165</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9905</td>\n",
       "      <td>34000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8485</td>\n",
       "      <td>22199</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  team_api_id  capacity\n",
       "0        8722     50000\n",
       "1        8350     49780\n",
       "2        8165     50000\n",
       "3        9905     34000\n",
       "4        8485     22199"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load Stadium Info\n",
    "stadiums = pd.read_sql_query(\"\"\"\n",
    "        SELECT  team_api_id, capacity\n",
    "        FROM stadiums\n",
    "\"\"\", conn)\n",
    "\n",
    "stadiums.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>team_api_id</th>\n",
       "      <th>capacity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>8654</td>\n",
       "      <td>35303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>120</th>\n",
       "      <td>8528</td>\n",
       "      <td>25133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>8602</td>\n",
       "      <td>27828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>4087</td>\n",
       "      <td>15660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>8576</td>\n",
       "      <td>10660</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     team_api_id  capacity\n",
       "119         8654     35303\n",
       "120         8528     25133\n",
       "121         8602     27828\n",
       "122         4087     15660\n",
       "123         8576     10660"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add stadium info\n",
    "import numpy as np\n",
    "\n",
    "stadiums = stadiums.replace(r'', np.nan).dropna().astype(int)\n",
    "\n",
    "stadiums.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    15489.0\n",
       "1    55000.0\n",
       "2    22524.0\n",
       "3    52500.0\n",
       "4    40500.0\n",
       "Name: capacity, dtype: float64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.merge(stadiums, left_on='home_team_api_id', right_on='team_api_id', how='left')\n",
    "\n",
    "df.drop('team_api_id', axis=1, inplace=True)\n",
    "\n",
    "df['capacity'].head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save dataframe to csv\n",
    "\n",
    "df.drop(['shoton', 'card', 'shotoff', 'goal', 'foulcommit', 'cross', 'corner', 'possession'], axis=1, inplace=True)\n",
    "\n",
    "df.to_csv('result.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "end = time()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total code run tie in 13.4 minutes\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(\"Total code run tie in {:.1f} minutes\".format((end-start)/60) + '\\n')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}