2_data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 338 µs (started: 2021-12-26 15:12:55 +00:00)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Timing for each cell to run\n",
    "%load_ext autotime\n",
    "# %unload_ext autotime\n",
    "\n",
    "# Warnings & display\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "pd.set_option(\"display.max_columns\", 60)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read in raw data from local directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "jupyter": {
     "source_hidden": 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>FPL_id</th>\n",
       "      <th>Understat_id</th>\n",
       "      <th>player_name</th>\n",
       "      <th>team</th>\n",
       "      <th>team_title</th>\n",
       "      <th>position</th>\n",
       "      <th>element_type</th>\n",
       "      <th>event</th>\n",
       "      <th>finished</th>\n",
       "      <th>opponent_team_title</th>\n",
       "      <th>opponent_team</th>\n",
       "      <th>home_or_away</th>\n",
       "      <th>value</th>\n",
       "      <th>status</th>\n",
       "      <th>chance_of_playing_this_round</th>\n",
       "      <th>chance_of_playing_next_round</th>\n",
       "      <th>total_points</th>\n",
       "      <th>goals_season</th>\n",
       "      <th>shots_season</th>\n",
       "      <th>xG_season</th>\n",
       "      <th>time_season</th>\n",
       "      <th>xA_season</th>\n",
       "      <th>assists_season</th>\n",
       "      <th>key_passes_season</th>\n",
       "      <th>npg_season</th>\n",
       "      <th>npxG_season</th>\n",
       "      <th>xGChain_season</th>\n",
       "      <th>xGBuildup_season</th>\n",
       "      <th>goals_WMA</th>\n",
       "      <th>shots_WMA</th>\n",
       "      <th>xG_WMA</th>\n",
       "      <th>time_WMA</th>\n",
       "      <th>xA_WMA</th>\n",
       "      <th>assists_WMA</th>\n",
       "      <th>key_passes_WMA</th>\n",
       "      <th>npg_WMA</th>\n",
       "      <th>npxG_WMA</th>\n",
       "      <th>xGChain_WMA</th>\n",
       "      <th>xGBuildup_WMA</th>\n",
       "      <th>team_xG_season</th>\n",
       "      <th>team_goals_season</th>\n",
       "      <th>team_xGA_season</th>\n",
       "      <th>team_goals_against_season</th>\n",
       "      <th>team_xG_WMA</th>\n",
       "      <th>team_goals_WMA</th>\n",
       "      <th>team_xGA_WMA</th>\n",
       "      <th>team_goals_against_WMA</th>\n",
       "      <th>opponent_xG_season</th>\n",
       "      <th>opponent_goals_season</th>\n",
       "      <th>opponent_xGA_season</th>\n",
       "      <th>opponent_goals_against_season</th>\n",
       "      <th>opponent_xG_WMA</th>\n",
       "      <th>opponent_goals_WMA</th>\n",
       "      <th>opponent_xGA_WMA</th>\n",
       "      <th>opponent_goals_against_WMA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>181</td>\n",
       "      <td>Bernd Leno</td>\n",
       "      <td>1</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>goalkeeper</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Brentford</td>\n",
       "      <td>3</td>\n",
       "      <td>A</td>\n",
       "      <td>50.0</td>\n",
       "      <td>a</td>\n",
       "      <td>75.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>181</td>\n",
       "      <td>Bernd Leno</td>\n",
       "      <td>1</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>goalkeeper</td>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Chelsea</td>\n",
       "      <td>6</td>\n",
       "      <td>H</td>\n",
       "      <td>50.0</td>\n",
       "      <td>a</td>\n",
       "      <td>75.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>1.02385</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.88818</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.02385</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.88818</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.18709</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.321701</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.18709</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.321701</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FPL_id  Understat_id player_name  team team_title    position  \\\n",
       "0       1           181  Bernd Leno     1    Arsenal  goalkeeper   \n",
       "1       1           181  Bernd Leno     1    Arsenal  goalkeeper   \n",
       "\n",
       "   element_type  event  finished opponent_team_title  opponent_team  \\\n",
       "0             1    1.0      True           Brentford              3   \n",
       "1             1    2.0      True             Chelsea              6   \n",
       "\n",
       "  home_or_away  value status  chance_of_playing_this_round  \\\n",
       "0            A   50.0      a                          75.0   \n",
       "1            H   50.0      a                          75.0   \n",
       "\n",
       "   chance_of_playing_next_round  total_points  goals_season  shots_season  \\\n",
       "0                         100.0           1.0           0.0           0.0   \n",
       "1                         100.0           2.0           0.0           0.0   \n",
       "\n",
       "   xG_season  time_season  xA_season  assists_season  key_passes_season  \\\n",
       "0        0.0         90.0        0.0             0.0                0.0   \n",
       "1        0.0         90.0        0.0             0.0                0.0   \n",
       "\n",
       "   npg_season  npxG_season  xGChain_season  xGBuildup_season  goals_WMA  \\\n",
       "0         0.0          0.0        0.097028          0.097028        0.0   \n",
       "1         0.0          0.0        0.097028          0.097028        0.0   \n",
       "\n",
       "   shots_WMA  xG_WMA  time_WMA  xA_WMA  assists_WMA  key_passes_WMA  npg_WMA  \\\n",
       "0        0.0     0.0      90.0     0.0          0.0             0.0      0.0   \n",
       "1        0.0     0.0      90.0     0.0          0.0             0.0      0.0   \n",
       "\n",
       "   npxG_WMA  xGChain_WMA  xGBuildup_WMA  team_xG_season  team_goals_season  \\\n",
       "0       0.0     0.097028       0.097028             NaN                NaN   \n",
       "1       0.0     0.097028       0.097028         1.02385                0.0   \n",
       "\n",
       "   team_xGA_season  team_goals_against_season  team_xG_WMA  team_goals_WMA  \\\n",
       "0              NaN                        NaN          NaN             NaN   \n",
       "1          1.88818                        2.0      1.02385             0.0   \n",
       "\n",
       "   team_xGA_WMA  team_goals_against_WMA  opponent_xG_season  \\\n",
       "0           NaN                     NaN                 NaN   \n",
       "1       1.88818                     2.0             1.18709   \n",
       "\n",
       "   opponent_goals_season  opponent_xGA_season  opponent_goals_against_season  \\\n",
       "0                    NaN                  NaN                            NaN   \n",
       "1                    3.0             0.321701                            0.0   \n",
       "\n",
       "   opponent_xG_WMA  opponent_goals_WMA  opponent_xGA_WMA  \\\n",
       "0              NaN                 NaN               NaN   \n",
       "1          1.18709                 3.0          0.321701   \n",
       "\n",
       "   opponent_goals_against_WMA  \n",
       "0                         NaN  \n",
       "1                         0.0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 193 ms (started: 2021-12-26 15:12:55 +00:00)\n"
     ]
    }
   ],
   "source": [
    "df_raw_data = pd.read_csv(\"/Users/samharrison/Documents/data_sci/fpl_points_predictor/data/raw_data.csv\")\n",
    "df_raw_data.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Manipulate DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "jupyter": {
     "source_hidden": 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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>finished</th>\n",
       "      <th>value</th>\n",
       "      <th>chance_of_playing_next_round</th>\n",
       "      <th>home_flag</th>\n",
       "      <th>goalkeeper_flag</th>\n",
       "      <th>defender_flag</th>\n",
       "      <th>midfielder_flag</th>\n",
       "      <th>forward_flag</th>\n",
       "      <th>status</th>\n",
       "      <th>goals_season</th>\n",
       "      <th>shots_season</th>\n",
       "      <th>xG_season</th>\n",
       "      <th>time_season</th>\n",
       "      <th>xA_season</th>\n",
       "      <th>assists_season</th>\n",
       "      <th>key_passes_season</th>\n",
       "      <th>npg_season</th>\n",
       "      <th>npxG_season</th>\n",
       "      <th>xGChain_season</th>\n",
       "      <th>xGBuildup_season</th>\n",
       "      <th>goals_WMA</th>\n",
       "      <th>shots_WMA</th>\n",
       "      <th>xG_WMA</th>\n",
       "      <th>time_WMA</th>\n",
       "      <th>xA_WMA</th>\n",
       "      <th>assists_WMA</th>\n",
       "      <th>key_passes_WMA</th>\n",
       "      <th>npg_WMA</th>\n",
       "      <th>npxG_WMA</th>\n",
       "      <th>xGChain_WMA</th>\n",
       "      <th>xGBuildup_WMA</th>\n",
       "      <th>team_xG_season</th>\n",
       "      <th>team_goals_season</th>\n",
       "      <th>team_xGA_season</th>\n",
       "      <th>team_goals_against_season</th>\n",
       "      <th>team_xG_WMA</th>\n",
       "      <th>team_goals_WMA</th>\n",
       "      <th>team_xGA_WMA</th>\n",
       "      <th>team_goals_against_WMA</th>\n",
       "      <th>opponent_xG_season</th>\n",
       "      <th>opponent_goals_season</th>\n",
       "      <th>opponent_xGA_season</th>\n",
       "      <th>opponent_goals_against_season</th>\n",
       "      <th>opponent_xG_WMA</th>\n",
       "      <th>opponent_goals_WMA</th>\n",
       "      <th>opponent_xGA_WMA</th>\n",
       "      <th>total_points</th>\n",
       "      <th>opponent_goals_against_WMA</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>position</th>\n",
       "      <th>team_title</th>\n",
       "      <th>event</th>\n",
       "      <th>opponent_team_title</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",
       "      <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",
       "      <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 rowspan=\"2\" valign=\"top\">midfielder</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Liverpool</th>\n",
       "      <th>1.0</th>\n",
       "      <th>Norwich</th>\n",
       "      <td>True</td>\n",
       "      <td>125.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.0</th>\n",
       "      <th>Burnley</th>\n",
       "      <td>True</td>\n",
       "      <td>126.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>a</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.239134</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.868721</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.239134</td>\n",
       "      <td>1.069634</td>\n",
       "      <td>0.100011</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.239134</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.868721</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.239134</td>\n",
       "      <td>1.069634</td>\n",
       "      <td>0.100011</td>\n",
       "      <td>1.78728</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.3333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.78728</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.3333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.79548</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.6853</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.79548</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.6853</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 finished  value  \\\n",
       "position   team_title event opponent_team_title                    \n",
       "midfielder Liverpool  1.0   Norwich                  True  125.0   \n",
       "                      2.0   Burnley                  True  126.0   \n",
       "\n",
       "                                                 chance_of_playing_next_round  \\\n",
       "position   team_title event opponent_team_title                                 \n",
       "midfielder Liverpool  1.0   Norwich                                       NaN   \n",
       "                      2.0   Burnley                                       NaN   \n",
       "\n",
       "                                                 home_flag  goalkeeper_flag  \\\n",
       "position   team_title event opponent_team_title                               \n",
       "midfielder Liverpool  1.0   Norwich                      0                0   \n",
       "                      2.0   Burnley                      1                0   \n",
       "\n",
       "                                                 defender_flag  \\\n",
       "position   team_title event opponent_team_title                  \n",
       "midfielder Liverpool  1.0   Norwich                          0   \n",
       "                      2.0   Burnley                          0   \n",
       "\n",
       "                                                 midfielder_flag  \\\n",
       "position   team_title event opponent_team_title                    \n",
       "midfielder Liverpool  1.0   Norwich                            1   \n",
       "                      2.0   Burnley                            1   \n",
       "\n",
       "                                                 forward_flag status  \\\n",
       "position   team_title event opponent_team_title                        \n",
       "midfielder Liverpool  1.0   Norwich                         0      a   \n",
       "                      2.0   Burnley                         0      a   \n",
       "\n",
       "                                                 goals_season  shots_season  \\\n",
       "position   team_title event opponent_team_title                               \n",
       "midfielder Liverpool  1.0   Norwich                       NaN           NaN   \n",
       "                      2.0   Burnley                       1.0           5.0   \n",
       "\n",
       "                                                 xG_season  time_season  \\\n",
       "position   team_title event opponent_team_title                           \n",
       "midfielder Liverpool  1.0   Norwich                    NaN          NaN   \n",
       "                      2.0   Burnley               0.239134         90.0   \n",
       "\n",
       "                                                 xA_season  assists_season  \\\n",
       "position   team_title event opponent_team_title                              \n",
       "midfielder Liverpool  1.0   Norwich                    NaN             NaN   \n",
       "                      2.0   Burnley               0.868721             2.0   \n",
       "\n",
       "                                                 key_passes_season  \\\n",
       "position   team_title event opponent_team_title                      \n",
       "midfielder Liverpool  1.0   Norwich                            NaN   \n",
       "                      2.0   Burnley                            3.0   \n",
       "\n",
       "                                                 npg_season  npxG_season  \\\n",
       "position   team_title event opponent_team_title                            \n",
       "midfielder Liverpool  1.0   Norwich                     NaN          NaN   \n",
       "                      2.0   Burnley                     1.0     0.239134   \n",
       "\n",
       "                                                 xGChain_season  \\\n",
       "position   team_title event opponent_team_title                   \n",
       "midfielder Liverpool  1.0   Norwich                         NaN   \n",
       "                      2.0   Burnley                    1.069634   \n",
       "\n",
       "                                                 xGBuildup_season  goals_WMA  \\\n",
       "position   team_title event opponent_team_title                                \n",
       "midfielder Liverpool  1.0   Norwich                           NaN        NaN   \n",
       "                      2.0   Burnley                      0.100011        1.0   \n",
       "\n",
       "                                                 shots_WMA    xG_WMA  \\\n",
       "position   team_title event opponent_team_title                        \n",
       "midfielder Liverpool  1.0   Norwich                    NaN       NaN   \n",
       "                      2.0   Burnley                    5.0  0.239134   \n",
       "\n",
       "                                                 time_WMA    xA_WMA  \\\n",
       "position   team_title event opponent_team_title                       \n",
       "midfielder Liverpool  1.0   Norwich                   NaN       NaN   \n",
       "                      2.0   Burnley                  90.0  0.868721   \n",
       "\n",
       "                                                 assists_WMA  key_passes_WMA  \\\n",
       "position   team_title event opponent_team_title                                \n",
       "midfielder Liverpool  1.0   Norwich                      NaN             NaN   \n",
       "                      2.0   Burnley                      2.0             3.0   \n",
       "\n",
       "                                                 npg_WMA  npxG_WMA  \\\n",
       "position   team_title event opponent_team_title                      \n",
       "midfielder Liverpool  1.0   Norwich                  NaN       NaN   \n",
       "                      2.0   Burnley                  1.0  0.239134   \n",
       "\n",
       "                                                 xGChain_WMA  xGBuildup_WMA  \\\n",
       "position   team_title event opponent_team_title                               \n",
       "midfielder Liverpool  1.0   Norwich                      NaN            NaN   \n",
       "                      2.0   Burnley                 1.069634       0.100011   \n",
       "\n",
       "                                                 team_xG_season  \\\n",
       "position   team_title event opponent_team_title                   \n",
       "midfielder Liverpool  1.0   Norwich                         NaN   \n",
       "                      2.0   Burnley                     1.78728   \n",
       "\n",
       "                                                 team_goals_season  \\\n",
       "position   team_title event opponent_team_title                      \n",
       "midfielder Liverpool  1.0   Norwich                            NaN   \n",
       "                      2.0   Burnley                            3.0   \n",
       "\n",
       "                                                 team_xGA_season  \\\n",
       "position   team_title event opponent_team_title                    \n",
       "midfielder Liverpool  1.0   Norwich                          NaN   \n",
       "                      2.0   Burnley                       1.3333   \n",
       "\n",
       "                                                 team_goals_against_season  \\\n",
       "position   team_title event opponent_team_title                              \n",
       "midfielder Liverpool  1.0   Norwich                                    NaN   \n",
       "                      2.0   Burnley                                    0.0   \n",
       "\n",
       "                                                 team_xG_WMA  team_goals_WMA  \\\n",
       "position   team_title event opponent_team_title                                \n",
       "midfielder Liverpool  1.0   Norwich                      NaN             NaN   \n",
       "                      2.0   Burnley                  1.78728             3.0   \n",
       "\n",
       "                                                 team_xGA_WMA  \\\n",
       "position   team_title event opponent_team_title                 \n",
       "midfielder Liverpool  1.0   Norwich                       NaN   \n",
       "                      2.0   Burnley                    1.3333   \n",
       "\n",
       "                                                 team_goals_against_WMA  \\\n",
       "position   team_title event opponent_team_title                           \n",
       "midfielder Liverpool  1.0   Norwich                                 NaN   \n",
       "                      2.0   Burnley                                 0.0   \n",
       "\n",
       "                                                 opponent_xG_season  \\\n",
       "position   team_title event opponent_team_title                       \n",
       "midfielder Liverpool  1.0   Norwich                             NaN   \n",
       "                      2.0   Burnley                         1.79548   \n",
       "\n",
       "                                                 opponent_goals_season  \\\n",
       "position   team_title event opponent_team_title                          \n",
       "midfielder Liverpool  1.0   Norwich                                NaN   \n",
       "                      2.0   Burnley                                1.0   \n",
       "\n",
       "                                                 opponent_xGA_season  \\\n",
       "position   team_title event opponent_team_title                        \n",
       "midfielder Liverpool  1.0   Norwich                              NaN   \n",
       "                      2.0   Burnley                           1.6853   \n",
       "\n",
       "                                                 opponent_goals_against_season  \\\n",
       "position   team_title event opponent_team_title                                  \n",
       "midfielder Liverpool  1.0   Norwich                                        NaN   \n",
       "                      2.0   Burnley                                        2.0   \n",
       "\n",
       "                                                 opponent_xG_WMA  \\\n",
       "position   team_title event opponent_team_title                    \n",
       "midfielder Liverpool  1.0   Norwich                          NaN   \n",
       "                      2.0   Burnley                      1.79548   \n",
       "\n",
       "                                                 opponent_goals_WMA  \\\n",
       "position   team_title event opponent_team_title                       \n",
       "midfielder Liverpool  1.0   Norwich                             NaN   \n",
       "                      2.0   Burnley                             1.0   \n",
       "\n",
       "                                                 opponent_xGA_WMA  \\\n",
       "position   team_title event opponent_team_title                     \n",
       "midfielder Liverpool  1.0   Norwich                           NaN   \n",
       "                      2.0   Burnley                        1.6853   \n",
       "\n",
       "                                                 total_points  \\\n",
       "position   team_title event opponent_team_title                 \n",
       "midfielder Liverpool  1.0   Norwich                      17.0   \n",
       "                      2.0   Burnley                       3.0   \n",
       "\n",
       "                                                 opponent_goals_against_WMA  \n",
       "position   team_title event opponent_team_title                              \n",
       "midfielder Liverpool  1.0   Norwich                                     NaN  \n",
       "                      2.0   Burnley                                     2.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 121 ms (started: 2021-12-26 15:12:56 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Drop unnecesary acolumns \n",
    "df = df_raw_data.drop(columns={'FPL_id','Understat_id','team','opponent_team','element_type','chance_of_playing_this_round'})\n",
    "\n",
    "# Make home_or_away feature a binary variable\n",
    "df.insert(8, 'home_flag', df['home_or_away'].apply(lambda x: 1 if x == 'H' else 0))\n",
    "df = df.drop(columns={'home_or_away'})\n",
    "\n",
    "# Make binary position variables\n",
    "df.insert(8, 'goalkeeper_flag', df['position'].apply(lambda x: 1 if x == 'goalkeeper' else 0))\n",
    "df.insert(9, 'defender_flag', df['position'].apply(lambda x: 1 if x == 'defender' else 0))\n",
    "df.insert(10, 'midfielder_flag', df['position'].apply(lambda x: 1 if x == 'midfielder' else 0))\n",
    "df.insert(11, 'forward_flag', df['position'].apply(lambda x: 1 if x == 'forward' else 0))\n",
    "\n",
    "# Set index and reorder columns\n",
    "df = df.set_index(['player_name','position','team_title','event','opponent_team_title'])\n",
    "df.insert(2, 'chance_of_playing_next_round', df.pop('chance_of_playing_next_round'))\n",
    "df.insert(46, 'total_points', df.pop('total_points'))\n",
    "\n",
    "# Trim the dataset to look forward only 5 gameweeks into the future\n",
    "prev_gw = df[df['finished']==True].index.get_level_values('event').max()\n",
    "gameweek_range = list(range(0,int(prev_gw)+6))\n",
    "df = df[df.index.get_level_values('event').isin(gameweek_range)]\n",
    "\n",
    "df.loc['Mohamed Salah'].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Handle null values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 25.7 ms (started: 2021-12-26 15:12:56 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Remove u = \"unavailable\" players from dataset\n",
    "df = df[df['status']!='u'].drop(columns={'status'})\n",
    "\n",
    "# Remove players/rows where the player hasn't registered any minutes for ease (this will also remove GW1 for all players)\n",
    "df[~((df['time_season'].isna())&(df['finished']==True))]\n",
    "\n",
    "# Remove any duplicate rows\n",
    "df = df[~((df['finished'].isna())&(df['value'].isna()))]\n",
    "df = df[~df['home_flag'].isna()]\n",
    "\n",
    "# Replace null values in playing chance column with 100% \n",
    "df['chance_of_playing_next_round'] = df['chance_of_playing_next_round'].fillna(100)\n",
    "\n",
    "# Remove rows where it was the opponents team's first game of the season\n",
    "df = df[~df['opponent_xG_season'].isna()]\n",
    "\n",
    "# Remove rows where players hadn't yet featured\n",
    "df = df[~df['goals_WMA'].isna()]\n",
    " \n",
    "# # Check there are no null values remaining\n",
    "# if df.drop(columns={'total_points'}).isna().sum().sum() == 0:\n",
    "#     print('There are no more remaining null values\\n')\n",
    "# else:\n",
    "#     print('There exists null values - Investigate\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transform the  \"_season\" variables\n",
    "Notice, for a given player, our \"_season\" variables get larger every gameweek. This was deliberate. These variables were created to capture the season-long performance of our players. However, by this design, we should probably transform these variables first before modelling with them. Namely, we will transform every \"_season\" variable via. the mapping, \n",
    "\n",
    "<img src=\"img/mapping.png\" /> \n",
    "\n",
    "defined by:\n",
    "\n",
    "<img src=\"img/mapping_def.png\" />    \n",
    "\n",
    "Without this transformation, these variables are very heavily time-dependent which we do not want. In essence, what we are doing here is calculating the \"per gameweek\" rate of each variable. Therefore, we will label these variables using **\"_pgw\"** as our notation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(8048, 47)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>finished</th>\n",
       "      <th>value</th>\n",
       "      <th>chance_of_playing_next_round</th>\n",
       "      <th>home_flag</th>\n",
       "      <th>goalkeeper_flag</th>\n",
       "      <th>defender_flag</th>\n",
       "      <th>midfielder_flag</th>\n",
       "      <th>forward_flag</th>\n",
       "      <th>goals_WMA</th>\n",
       "      <th>shots_WMA</th>\n",
       "      <th>xG_WMA</th>\n",
       "      <th>time_WMA</th>\n",
       "      <th>xA_WMA</th>\n",
       "      <th>assists_WMA</th>\n",
       "      <th>key_passes_WMA</th>\n",
       "      <th>npg_WMA</th>\n",
       "      <th>npxG_WMA</th>\n",
       "      <th>xGChain_WMA</th>\n",
       "      <th>xGBuildup_WMA</th>\n",
       "      <th>team_xG_WMA</th>\n",
       "      <th>team_goals_WMA</th>\n",
       "      <th>team_xGA_WMA</th>\n",
       "      <th>team_goals_against_WMA</th>\n",
       "      <th>opponent_xG_WMA</th>\n",
       "      <th>opponent_goals_WMA</th>\n",
       "      <th>opponent_xGA_WMA</th>\n",
       "      <th>opponent_goals_against_WMA</th>\n",
       "      <th>goals_pgw</th>\n",
       "      <th>shots_pgw</th>\n",
       "      <th>xG_pgw</th>\n",
       "      <th>time_pgw</th>\n",
       "      <th>xA_pgw</th>\n",
       "      <th>assists_pgw</th>\n",
       "      <th>key_passes_pgw</th>\n",
       "      <th>npg_pgw</th>\n",
       "      <th>npxG_pgw</th>\n",
       "      <th>xGChain_pgw</th>\n",
       "      <th>xGBuildup_pgw</th>\n",
       "      <th>team_xG_pgw</th>\n",
       "      <th>team_goals_pgw</th>\n",
       "      <th>team_xGA_pgw</th>\n",
       "      <th>team_goals_against_pgw</th>\n",
       "      <th>opponent_xG_pgw</th>\n",
       "      <th>opponent_goals_pgw</th>\n",
       "      <th>opponent_xGA_pgw</th>\n",
       "      <th>opponent_goals_against_pgw</th>\n",
       "      <th>total_points</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>player_name</th>\n",
       "      <th>position</th>\n",
       "      <th>team_title</th>\n",
       "      <th>event</th>\n",
       "      <th>opponent_team_title</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",
       "      <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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Bernd Leno</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">goalkeeper</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Arsenal</th>\n",
       "      <th>2.0</th>\n",
       "      <th>Chelsea</th>\n",
       "      <td>True</td>\n",
       "      <td>50.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>1.023850</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.88818</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.187090</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.321701</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>0.097028</td>\n",
       "      <td>1.023850</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.88818</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.187090</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.321701</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.0</th>\n",
       "      <th>Manchester City</th>\n",
       "      <td>True</td>\n",
       "      <td>49.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.032343</td>\n",
       "      <td>0.032343</td>\n",
       "      <td>0.761349</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.05694</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.328137</td>\n",
       "      <td>3.333333</td>\n",
       "      <td>0.370053</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.048514</td>\n",
       "      <td>0.048514</td>\n",
       "      <td>0.826975</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.76475</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.272565</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0.542574</td>\n",
       "      <td>0.5</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                             finished  value  \\\n",
       "player_name position   team_title event opponent_team_title                    \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                  True   50.0   \n",
       "                                  3.0   Manchester City          True   49.0   \n",
       "\n",
       "                                                             chance_of_playing_next_round  \\\n",
       "player_name position   team_title event opponent_team_title                                 \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                                     100.0   \n",
       "                                  3.0   Manchester City                             100.0   \n",
       "\n",
       "                                                             home_flag  \\\n",
       "player_name position   team_title event opponent_team_title              \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                      1   \n",
       "                                  3.0   Manchester City              0   \n",
       "\n",
       "                                                             goalkeeper_flag  \\\n",
       "player_name position   team_title event opponent_team_title                    \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                            1   \n",
       "                                  3.0   Manchester City                    1   \n",
       "\n",
       "                                                             defender_flag  \\\n",
       "player_name position   team_title event opponent_team_title                  \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                          0   \n",
       "                                  3.0   Manchester City                  0   \n",
       "\n",
       "                                                             midfielder_flag  \\\n",
       "player_name position   team_title event opponent_team_title                    \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                            0   \n",
       "                                  3.0   Manchester City                    0   \n",
       "\n",
       "                                                             forward_flag  \\\n",
       "player_name position   team_title event opponent_team_title                 \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                         0   \n",
       "                                  3.0   Manchester City                 0   \n",
       "\n",
       "                                                             goals_WMA  \\\n",
       "player_name position   team_title event opponent_team_title              \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                    0.0   \n",
       "                                  3.0   Manchester City            0.0   \n",
       "\n",
       "                                                             shots_WMA  \\\n",
       "player_name position   team_title event opponent_team_title              \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                    0.0   \n",
       "                                  3.0   Manchester City            0.0   \n",
       "\n",
       "                                                             xG_WMA  time_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                     \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                 0.0      90.0   \n",
       "                                  3.0   Manchester City         0.0      90.0   \n",
       "\n",
       "                                                             xA_WMA  \\\n",
       "player_name position   team_title event opponent_team_title           \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                 0.0   \n",
       "                                  3.0   Manchester City         0.0   \n",
       "\n",
       "                                                             assists_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                      0.0   \n",
       "                                  3.0   Manchester City              0.0   \n",
       "\n",
       "                                                             key_passes_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                   \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                         0.0   \n",
       "                                  3.0   Manchester City                 0.0   \n",
       "\n",
       "                                                             npg_WMA  \\\n",
       "player_name position   team_title event opponent_team_title            \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                  0.0   \n",
       "                                  3.0   Manchester City          0.0   \n",
       "\n",
       "                                                             npxG_WMA  \\\n",
       "player_name position   team_title event opponent_team_title             \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                   0.0   \n",
       "                                  3.0   Manchester City           0.0   \n",
       "\n",
       "                                                             xGChain_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                 0.097028   \n",
       "                                  3.0   Manchester City         0.032343   \n",
       "\n",
       "                                                             xGBuildup_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                  \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                   0.097028   \n",
       "                                  3.0   Manchester City           0.032343   \n",
       "\n",
       "                                                             team_xG_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                 1.023850   \n",
       "                                  3.0   Manchester City         0.761349   \n",
       "\n",
       "                                                             team_goals_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                   \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                         0.0   \n",
       "                                  3.0   Manchester City                 0.0   \n",
       "\n",
       "                                                             team_xGA_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                 \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                   1.88818   \n",
       "                                  3.0   Manchester City           3.05694   \n",
       "\n",
       "                                                             team_goals_against_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                           \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                                 2.0   \n",
       "                                  3.0   Manchester City                         2.0   \n",
       "\n",
       "                                                             opponent_xG_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                    \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                     1.187090   \n",
       "                                  3.0   Manchester City             2.328137   \n",
       "\n",
       "                                                             opponent_goals_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                       \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                        3.000000   \n",
       "                                  3.0   Manchester City                3.333333   \n",
       "\n",
       "                                                             opponent_xGA_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                     \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                      0.321701   \n",
       "                                  3.0   Manchester City              0.370053   \n",
       "\n",
       "                                                             opponent_goals_against_WMA  \\\n",
       "player_name position   team_title event opponent_team_title                               \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                                0.000000   \n",
       "                                  3.0   Manchester City                        0.333333   \n",
       "\n",
       "                                                             goals_pgw  \\\n",
       "player_name position   team_title event opponent_team_title              \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                    0.0   \n",
       "                                  3.0   Manchester City            0.0   \n",
       "\n",
       "                                                             shots_pgw  \\\n",
       "player_name position   team_title event opponent_team_title              \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                    0.0   \n",
       "                                  3.0   Manchester City            0.0   \n",
       "\n",
       "                                                             xG_pgw  time_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                     \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                 0.0      90.0   \n",
       "                                  3.0   Manchester City         0.0      90.0   \n",
       "\n",
       "                                                             xA_pgw  \\\n",
       "player_name position   team_title event opponent_team_title           \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                 0.0   \n",
       "                                  3.0   Manchester City         0.0   \n",
       "\n",
       "                                                             assists_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                      0.0   \n",
       "                                  3.0   Manchester City              0.0   \n",
       "\n",
       "                                                             key_passes_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                   \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                         0.0   \n",
       "                                  3.0   Manchester City                 0.0   \n",
       "\n",
       "                                                             npg_pgw  \\\n",
       "player_name position   team_title event opponent_team_title            \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                  0.0   \n",
       "                                  3.0   Manchester City          0.0   \n",
       "\n",
       "                                                             npxG_pgw  \\\n",
       "player_name position   team_title event opponent_team_title             \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                   0.0   \n",
       "                                  3.0   Manchester City           0.0   \n",
       "\n",
       "                                                             xGChain_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                 0.097028   \n",
       "                                  3.0   Manchester City         0.048514   \n",
       "\n",
       "                                                             xGBuildup_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                  \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                   0.097028   \n",
       "                                  3.0   Manchester City           0.048514   \n",
       "\n",
       "                                                             team_xG_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                 1.023850   \n",
       "                                  3.0   Manchester City         0.826975   \n",
       "\n",
       "                                                             team_goals_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                   \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                         0.0   \n",
       "                                  3.0   Manchester City                 0.0   \n",
       "\n",
       "                                                             team_xGA_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                 \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                   1.88818   \n",
       "                                  3.0   Manchester City           2.76475   \n",
       "\n",
       "                                                             team_goals_against_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                           \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                                 2.0   \n",
       "                                  3.0   Manchester City                         2.0   \n",
       "\n",
       "                                                             opponent_xG_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                    \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                     1.187090   \n",
       "                                  3.0   Manchester City             2.272565   \n",
       "\n",
       "                                                             opponent_goals_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                       \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                             3.0   \n",
       "                                  3.0   Manchester City                     2.5   \n",
       "\n",
       "                                                             opponent_xGA_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                     \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                      0.321701   \n",
       "                                  3.0   Manchester City              0.542574   \n",
       "\n",
       "                                                             opponent_goals_against_pgw  \\\n",
       "player_name position   team_title event opponent_team_title                               \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                                     0.0   \n",
       "                                  3.0   Manchester City                             0.5   \n",
       "\n",
       "                                                             total_points  \n",
       "player_name position   team_title event opponent_team_title                \n",
       "Bernd Leno  goalkeeper Arsenal    2.0   Chelsea                       2.0  \n",
       "                                  3.0   Manchester City               1.0  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 52.9 ms (started: 2021-12-26 15:12:56 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Define season variables\n",
    "season_variables = ['goals_season','shots_season','xG_season','time_season','xA_season','assists_season',\n",
    "                    'key_passes_season','npg_season','npxG_season','xGChain_season','xGBuildup_season', 'team_xG_season', \n",
    "                    'team_goals_season','team_xGA_season','team_goals_against_season','opponent_xG_season',\n",
    "                    'opponent_goals_season','opponent_xGA_season','opponent_goals_against_season']\n",
    "\n",
    "# Define event (i.e. gameweek) column\n",
    "df['event'] = df.index.get_level_values('event')\n",
    "\n",
    "# For every season variable, transform via. the mapping defined above\n",
    "for var in season_variables:\n",
    "    df[f'{var[:-7]}_pgw'] = df[var]/(df['event']-1)\n",
    "\n",
    "# Drop old season variables\n",
    "df = df.loc[:,~df.columns.isin(season_variables)]\n",
    "df = df.drop(columns={'event'})\n",
    "\n",
    "# Reorder columns\n",
    "print(df.shape)\n",
    "df.insert(46, 'total_points', df.pop('total_points'))\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overwrite cleaned data in local directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 443 ms (started: 2021-12-26 15:12:56 +00:00)\n"
     ]
    }
   ],
   "source": [
    "df.to_csv(index=True, path_or_buf=\"/Users/samharrison/Documents/data_sci/fpl_points_predictor/data/cleaned_data.csv\") "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also store a historic version of the cleaned data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 478 ms (started: 2021-12-26 15:12:56 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# First, we find the gameweek number (i.e. the upcoming gameweek)\n",
    "gameweek_num = df[df['finished']==False].index.get_level_values('event').min()\n",
    "\n",
    "# Now, we can save\n",
    "df.to_csv(index=True, path_or_buf=\"/Users/samharrison/Documents/data_sci/fpl_points_predictor/data/data_archive/cleaned_data_gw\"+str(gameweek_num)+\".csv\")"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}