1_data_collection.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: 7.22 ms (started: 2022-03-18 16:48:48 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Data manipulation\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import timedelta, date, datetime\n",
    "import sys\n",
    "\n",
    "from understat import Understat\n",
    "\n",
    "# Asynchronous programming\n",
    "import asyncio\n",
    "import nest_asyncio\n",
    "nest_asyncio.apply()\n",
    "\n",
    "# Working with JSON files\n",
    "import json\n",
    "\n",
    "# Server for asyncio\n",
    "import aiohttp\n",
    "\n",
    "# Module to avoid 'Error: 429, message='Too Many Requests''\n",
    "import backoff \n",
    "\n",
    "# Package for record linkage\n",
    "import pandas_dedupe \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)\n",
    "\n",
    "# Progress bar\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Collection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Gather FPL data from https://fantasy.premierleague.com/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 2.95 ms (started: 2022-03-18 16:48:48 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Access https://fantasy.premierleague.com/ via fpl class\n",
    "# We first, however, add exponential back-off to the method to avoid 429 (Too many requests) Error\n",
    "@backoff.on_exception(backoff.expo, aiohttp.ClientResponseError, max_tries=8, giveup=lambda e: e.status != 429)\n",
    "async def fpl_data():\n",
    "    \n",
    "    \"\"\"Returns a DataFrame with player data from https://fantasy.premierleague.com/, \n",
    "    e.g. player_name/team/opponent_team/value/total_points etc.\n",
    "\n",
    "        Note: the DataFrame consists of 1 row per player per gameweek\n",
    "\n",
    "    :param None\n",
    "    :rtype: DataFrame df_fpl: A DataFrame containing all of the player data from the \n",
    "    official FPL website.\n",
    "    \"\"\"    \n",
    "\n",
    "    async with aiohttp.ClientSession() as session:\n",
    "        fpl = FPL(session)\n",
    "        \n",
    "        # Use get_players function from FPL class\n",
    "        players_dict = await fpl.get_players(include_summary = True, return_json = True)\n",
    "        df_players = pd.DataFrame.from_dict(players_dict)\n",
    "        \n",
    "        # Drop unnecessary columns\n",
    "        df_players = df_players[['id','first_name','second_name','web_name','team','element_type','status',\n",
    "                                 'chance_of_playing_this_round','chance_of_playing_next_round']]\n",
    "        \n",
    "        # Create new player name column\n",
    "        df_players['player_name'] = df_players[\"first_name\"] + \" \" + df_players[\"second_name\"]\n",
    "        df_players.insert(3, \"player_name\", df_players.pop(\"player_name\"))\n",
    "        df_players = df_players.drop(columns=[\"first_name\",\"second_name\",\"web_name\"])\n",
    "        \n",
    "        # For every player, access their upcoming gameweeks/gameweek history\n",
    "        players_by_gameweek_array = []\n",
    "        for id in df_players['id'].unique():\n",
    "            \n",
    "            # Use get_player_summary function from FPL class\n",
    "            df_player_summary_dict = await fpl.get_player_summary(id, return_json = True)\n",
    "            \n",
    "            # 1/3) Access the upcoming gameweeks for the player\n",
    "            df_player_upcoming_fixtures = pd.DataFrame.from_dict(df_player_summary_dict['fixtures'])\n",
    "                        \n",
    "            # Double-check the game hasn't already happened yet\n",
    "            finished_col = []\n",
    "            for i in range(0,len(df_player_upcoming_fixtures)):\n",
    "                game = df_player_upcoming_fixtures.iloc[i]\n",
    "                \n",
    "                if (game['team_h_score'] is None) & (game['team_a_score'] is None):\n",
    "                    finished_col.append(False)\n",
    "                else:\n",
    "                    finished_col.append(True)\n",
    "            df_player_upcoming_fixtures['finished'] = finished_col\n",
    "            df_player_upcoming_fixtures = df_player_upcoming_fixtures[df_player_upcoming_fixtures['finished']==False]\n",
    "\n",
    "            # Drop unnecessary columns\n",
    "            df_player_upcoming_fixtures = df_player_upcoming_fixtures.drop(columns={'code','id','team_h_score',\n",
    "                        'team_a_score','provisional_start_time', 'kickoff_time','event_name','minutes','difficulty'})\n",
    "\n",
    "            # Create opponent_team/home_or_away/total_points variables\n",
    "            oppenent_team = []\n",
    "            home_or_away = []\n",
    "            for i in range(0,len(df_player_upcoming_fixtures)):\n",
    "                fixture = df_player_upcoming_fixtures.iloc[i]\n",
    "                if fixture['is_home']:\n",
    "                    oppenent_team.append(fixture['team_a'])\n",
    "                    home_or_away.append('H')\n",
    "                else:\n",
    "                    oppenent_team.append(fixture['team_h'])\n",
    "                    home_or_away.append('A')\n",
    "            df_player_upcoming_fixtures['opponent_team'] = oppenent_team\n",
    "            df_player_upcoming_fixtures['home_or_away'] = home_or_away\n",
    "            df_player_upcoming_fixtures['value'] = np.nan\n",
    "            df_player_upcoming_fixtures['total_points'] = np.nan\n",
    "            df_player_upcoming_fixtures = df_player_upcoming_fixtures.drop(columns={'team_h','team_a','is_home'})\n",
    "\n",
    "            # 2/3) Access gameweek history for the player\n",
    "            df_player_history = pd.DataFrame.from_dict(df_player_summary_dict['history'])\n",
    "\n",
    "            # # Drop unnecessary columns\n",
    "            df_player_history = df_player_history[['round','opponent_team','was_home','team_h_score','team_a_score',\n",
    "                                                   'value','total_points']]\n",
    "            \n",
    "            # Create home_or_away/finished variable\n",
    "            home_or_away = []\n",
    "            for i in range(0,len(df_player_history)):\n",
    "                fixture = df_player_history.iloc[i]\n",
    "                if fixture['was_home']:\n",
    "                    home_or_away.append('H')\n",
    "                else: \n",
    "                    home_or_away.append('A')\n",
    "            df_player_history['home_or_away'] = home_or_away\n",
    "            \n",
    "            # Double-check the game has actually finished\n",
    "            finished_col = []\n",
    "            for i in range(0,len(df_player_history)):\n",
    "                game = df_player_history.iloc[i]\n",
    "                \n",
    "                if (pd.isna(game['team_h_score'])) & (pd.isna(game['team_a_score'])):\n",
    "                    finished_col.append(False)\n",
    "                else:\n",
    "                    finished_col.append(True)\n",
    "            df_player_history['finished'] = finished_col\n",
    "            df_player_history = df_player_history[df_player_history['finished']==True]\n",
    "            \n",
    "            # Formatting\n",
    "            df_player_history = df_player_history.rename(columns={'round':'event'})\n",
    "            df_player_history = df_player_history[['event','finished','opponent_team','home_or_away','value',\n",
    "                                                   'total_points']]\n",
    "\n",
    "            # 3/3) Concatenate the two DataFrames we have just constructed\n",
    "            df_player_by_gameweek = pd.concat([df_player_upcoming_fixtures, df_player_history]).sort_values(by='event')\n",
    "            df_player_by_gameweek = df_player_by_gameweek.reset_index().drop(columns={'index'})\n",
    "            df_player_by_gameweek['id'] = id\n",
    "            \n",
    "            # Append to array outside loop\n",
    "            players_by_gameweek_array.append(df_player_by_gameweek)\n",
    "\n",
    "        # Concatenate players_by_gameweek_array into single DataFrame\n",
    "        df_players_by_gameweek = pd.concat(players_by_gameweek_array)\n",
    "        \n",
    "        # Merge df_players AND df_player_by_gameweek to create final DataFrame which will be outputted\n",
    "        df_fpl = pd.merge(df_players_by_gameweek, df_players, on='id', how='left')\n",
    "        df_fpl = df_fpl[['id','player_name','team','element_type','event','finished','opponent_team','home_or_away',\n",
    "                         'value','status','chance_of_playing_this_round','chance_of_playing_next_round','total_points']]\n",
    "        df_fpl = df_fpl.rename(columns={'id':'FPL_id'})\n",
    "    \n",
    "        # Note - before outputting the final DataFrame, we create position / team_title / opponent_team_title variables \n",
    "        # Create position variable\n",
    "        position_col = []\n",
    "        for i in range(0, len(df_fpl)):\n",
    "            position = df_fpl['element_type'].iloc[i]\n",
    "\n",
    "            if position==1:\n",
    "                position_col.append('goalkeeper')\n",
    "            elif position==2:\n",
    "                position_col.append('defender')\n",
    "            elif position==3:\n",
    "                position_col.append('midfielder')\n",
    "            elif position==4:\n",
    "                position_col.append('forward')\n",
    "        df_fpl['position'] = position_col\n",
    "        df_fpl.insert(3, \"position\", df_fpl.pop(\"position\"))\n",
    "        \n",
    "        # Create team_title / opponent_team_title variables\n",
    "        team_title_dictionary = {1:'Arsenal',2:'Aston Villa',3:'Brentford',4:'Brighton',5:'Burnley',6:'Chelsea',\n",
    "                                 7:'Crystal Palace',8:'Everton',9:'Leicester',10:'Leeds',11:'Liverpool',12:'Manchester City',\n",
    "                                 13:'Manchester United',14:'Newcastle United',15:'Norwich',16:'Southampton',\n",
    "                                 17:'Tottenham',18:'Watford',19:'West Ham',20:'Wolverhampton Wanderers'}\n",
    "        team_title_col = []\n",
    "        opponent_team_title_col = []\n",
    "        for i in range(0, len(df_fpl)):\n",
    "\n",
    "            # Initialise local variables & Append\n",
    "            player = df_fpl.iloc[i]\n",
    "            team_title = team_title_dictionary[player['team']] \n",
    "            team_title_col.append(team_title)\n",
    "            opponent_team_title = team_title_dictionary[player['opponent_team']] \n",
    "            opponent_team_title_col.append(opponent_team_title)\n",
    "\n",
    "        # Set New variable(s)\n",
    "        df_fpl['team_title'] = team_title_col\n",
    "        df_fpl.insert(3, 'team_title', df_fpl.pop('team_title'))\n",
    "        df_fpl['opponent_team_title'] = opponent_team_title_col\n",
    "        df_fpl.insert(9, 'opponent_team_title', df_fpl.pop('opponent_team_title'))\n",
    "        \n",
    "        return df_fpl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(25302, 16)\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>FPL_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</th>\n",
       "      <th>opponent_team_title</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13285</th>\n",
       "      <td>233</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>True</td>\n",
       "      <td>15</td>\n",
       "      <td>Norwich</td>\n",
       "      <td>A</td>\n",
       "      <td>125.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13286</th>\n",
       "      <td>233</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>5</td>\n",
       "      <td>Burnley</td>\n",
       "      <td>H</td>\n",
       "      <td>126.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       FPL_id    player_name  team team_title    position  element_type  \\\n",
       "13285     233  Mohamed Salah    11  Liverpool  midfielder             3   \n",
       "13286     233  Mohamed Salah    11  Liverpool  midfielder             3   \n",
       "\n",
       "       event  finished  opponent_team opponent_team_title home_or_away  value  \\\n",
       "13285    1.0      True             15             Norwich            A  125.0   \n",
       "13286    2.0      True              5             Burnley            H  126.0   \n",
       "\n",
       "      status  chance_of_playing_this_round  chance_of_playing_next_round  \\\n",
       "13285      a                         100.0                         100.0   \n",
       "13286      a                         100.0                         100.0   \n",
       "\n",
       "       total_points  \n",
       "13285          17.0  \n",
       "13286           3.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 35.3 s (started: 2022-03-18 16:48:48 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Event loop\n",
    "loop = asyncio.get_event_loop()\n",
    "df_fpl = loop.run_until_complete(fpl_data());\n",
    "\n",
    "print(df_fpl.shape)\n",
    "df_fpl[df_fpl['player_name']=='Mohamed Salah'].head(2)                              "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Check: Before continuing any further - determine whether we either:\n",
    "* A) Gather the latest data and retrain our model\n",
    "* B) Do nothing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Gameweek status: \n",
      "All of the games this gameweek have been played\n",
      "Most recent data has not been gathered \n",
      "\n",
      "Action:\n",
      "Gather latest data\n",
      "\n",
      "time: 223 ms (started: 2022-03-18 16:49:23 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Read in previous raw data\n",
    "df_raw_data = pd.read_csv(\"/Users/samharrison/Documents/data_sci/fpl/data/raw_data.csv\")\n",
    "\n",
    "# Find the minimum gameweek where we have unobserved data\n",
    "min_unobserved = df_fpl[df_fpl['finished']==False]['event'].min()\n",
    "\n",
    "# Find the maximum gameweek where we have observed data\n",
    "max_observed = df_fpl[df_fpl['finished']==True]['event'].max() \n",
    "\n",
    "# Decision to choose action A) or B) starts below\n",
    "# If the above two variables are equal - then NOT ALL of the games have played out this gameweek\n",
    "if min_unobserved == max_observed:\n",
    "    print('Gameweek status: \\nThere are still games left to be played this gameweek\\n\\nAction:\\nDo nothing\\n')\n",
    "    \n",
    "    # End script\n",
    "    sys.exit()\n",
    "\n",
    "# In the above two variables differ by 1 - ALL of the games have finished this gameweek\n",
    "elif min_unobserved == (max_observed+1):\n",
    "    print('Gameweek status: \\nAll of the games this gameweek have been played')\n",
    "\n",
    "    # Have we gathered the most recent data?\n",
    "    if max_observed == df_raw_data[df_raw_data['finished']==True]['event'].max():\n",
    "        print('Most recent data has been gathered \\n\\nAction:\\nDo nothing\\n')\n",
    "        \n",
    "        # End script\n",
    "        sys.exit()\n",
    "        \n",
    "    else: # max_observed == (df_raw_data[df_raw_data['finished']==True]['event'].max()+1):\n",
    "        print('Most recent data has not been gathered \\n\\nAction:\\nGather latest data\\n')    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define the upcoming gameweek number is. \n",
    "\n",
    "*[Note: This variable will be used (much later on) in file paths.]*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "30.0\n",
      "time: 300 µs (started: 2022-03-18 16:49:23 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Set gameweek number as a variable\n",
    "gameweek_num = min_unobserved\n",
    "print(gameweek_num)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Gather gameweek deadlines & dates from https://fantasy.premierleague.com/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 588 µs (started: 2022-03-18 16:49:23 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Access https://fantasy.premierleague.com/ via fpl class\n",
    "async def gameweek_data():\n",
    "    \n",
    "    \"\"\"Returns a DataFrame with gameweek data from https://fantasy.premierleague.com/,\n",
    "    which includes columns such as:\n",
    "\n",
    "        finished: has the gameweek finished?\n",
    "        end_dt: end date for the gameweek (12 hours before gameweek deadline)\n",
    "        60_days_before_end_dt: 60 days before end date (will be used later on when we calculate moving averages)\n",
    "\n",
    "    :param None\n",
    "    :rtype: DataFrame df_gameweeks: A DataFrame containing all of the gameweek data \n",
    "    from the official FPL website.\n",
    "    \"\"\"\n",
    "        \n",
    "    async with aiohttp.ClientSession() as session:\n",
    "        fpl = FPL(session)\n",
    "        \n",
    "        df_gameweeks_array = []\n",
    "        for i in range(1,39):\n",
    "            # Use get_gameweeks function from FPL class\n",
    "            gameweek_dict = await fpl.get_gameweeks(gameweek_ids = [i], return_json = True)\n",
    "            df_gameweek = pd.DataFrame.from_dict(gameweek_dict)\n",
    "            \n",
    "            # Append to array outside loop\n",
    "            df_gameweeks_array.append(df_gameweek)\n",
    "            \n",
    "        # Concatenate all DataFrames in the array\n",
    "        df_gameweeks = pd.concat(df_gameweeks_array)\n",
    "        \n",
    "        # Create the variables: end_dt / 60_days_before_end_dt / season_start_dt \n",
    "        df_gameweeks = df_gameweeks[['id','finished','name','deadline_time']]\n",
    "        df_gameweeks['deadline_time'] = pd.to_datetime(df_gameweeks['deadline_time']) #.dt.date\n",
    "        df_gameweeks['end_dt'] = df_gameweeks['deadline_time'] - timedelta(hours=12)\n",
    "        df_gameweeks['60_days_before_end_dt'] = df_gameweeks['deadline_time'] - timedelta(days=60)\n",
    "        df_gameweeks['season_start_dt'] = df_gameweeks['deadline_time'].min() - timedelta(days=1)\n",
    "\n",
    "        # Rename column\n",
    "        df_gameweeks = df_gameweeks.rename(columns={'id':'event'})\n",
    "        df_gameweeks = df_gameweeks.reset_index(drop=True)\n",
    "        \n",
    "        return df_gameweeks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(38, 7)\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>event</th>\n",
       "      <th>finished</th>\n",
       "      <th>name</th>\n",
       "      <th>deadline_time</th>\n",
       "      <th>end_dt</th>\n",
       "      <th>60_days_before_end_dt</th>\n",
       "      <th>season_start_dt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>Gameweek 1</td>\n",
       "      <td>2021-08-13 17:30:00+00:00</td>\n",
       "      <td>2021-08-13 05:30:00+00:00</td>\n",
       "      <td>2021-06-14 17:30:00+00:00</td>\n",
       "      <td>2021-08-12 17:30:00+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>Gameweek 2</td>\n",
       "      <td>2021-08-21 10:00:00+00:00</td>\n",
       "      <td>2021-08-20 22:00:00+00:00</td>\n",
       "      <td>2021-06-22 10:00:00+00:00</td>\n",
       "      <td>2021-08-12 17:30:00+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   event  finished        name             deadline_time  \\\n",
       "0      1      True  Gameweek 1 2021-08-13 17:30:00+00:00   \n",
       "1      2      True  Gameweek 2 2021-08-21 10:00:00+00:00   \n",
       "\n",
       "                     end_dt     60_days_before_end_dt  \\\n",
       "0 2021-08-13 05:30:00+00:00 2021-06-14 17:30:00+00:00   \n",
       "1 2021-08-20 22:00:00+00:00 2021-06-22 10:00:00+00:00   \n",
       "\n",
       "            season_start_dt  \n",
       "0 2021-08-12 17:30:00+00:00  \n",
       "1 2021-08-12 17:30:00+00:00  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 317 ms (started: 2022-03-18 16:49:23 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Event loop\n",
    "loop = asyncio.get_event_loop()\n",
    "df_gameweeks = loop.run_until_complete(gameweek_data());\n",
    "\n",
    "print(df_gameweeks.shape)\n",
    "df_gameweeks.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Gather underlying player statistics from https://understat.com/¶"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Helper function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 521 µs (started: 2022-03-18 16:49:24 +00:00)\n"
     ]
    }
   ],
   "source": [
    "def wma(df, col):\n",
    "    \n",
    "    \"\"\"Helper function used within underlying_player_stats() to \n",
    "    help calculate weighted moving average data  \n",
    "\n",
    "    :param DataFrame df\n",
    "    :rtype: DataFrame df\n",
    "    \"\"\"\n",
    "    \n",
    "    # How many games are in the moving average period?\n",
    "    n = len(df)\n",
    "\n",
    "    # Calculate weights and weighted moving averages\n",
    "    weights = np.arange(1, n + 1)\n",
    "    wmas = df[col].rolling(n).apply(lambda x: np.dot(x, weights) / weights.sum(), raw=True).to_list()\n",
    "\n",
    "    # Add column \n",
    "    df[f'{col}_WMA'] = wmas\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Player statistics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 2.73 ms (started: 2022-03-18 16:49:24 +00:00)\n"
     ]
    }
   ],
   "source": [
    "async def underlying_player_stats():\n",
    "    \n",
    "    \"\"\"Returns a DataFrame with underlying player statistics from https://understat.com/, \n",
    "    e.g. player_name/team/opponent_team/value/total_points etc.\n",
    "\n",
    "        Notes: \n",
    "            * The DataFrame consists of 1 row per player per gameweek\n",
    "            * This function takes a significant time to run ~60 minutes\n",
    "\n",
    "    :param None\n",
    "    :rtype: DataFrame df_understat_players: A DataFrame containing all of the underlying player \n",
    "    statistics available from https://understat.com/.\n",
    "    \"\"\"\n",
    "        \n",
    "    async with aiohttp.ClientSession() as session:\n",
    "        understat = Understat(session)\n",
    "        \n",
    "        # Use get_league_players function from Understat class\n",
    "        players_dict = await understat.get_league_players('EPL','2021')\n",
    "        df_players = pd.DataFrame.from_dict(players_dict)\n",
    "        \n",
    "        # Initialise array to store ALL player stats\n",
    "        player_stats_array = []\n",
    "        \n",
    "        # For every player\n",
    "        for i in tqdm(range(0, len(df_players))):                 \n",
    "            \n",
    "            # Initialise array to store SINGLE player stats (by gameweek)\n",
    "            gameweek_stats_array = []           \n",
    "            \n",
    "            # Initalise local variables\n",
    "            id = df_players['id'].iloc[i]\n",
    "            team_title = df_players['team_title'].iloc[i]           \n",
    "            player_name = df_players['player_name'].iloc[i]\n",
    "\n",
    "            # For every gameweek, access their underlying stats for the 1) the season so far, and 2) the last 60 days\n",
    "            for i in range(0,len(df_gameweeks)):            \n",
    "                gameweek = df_gameweeks.iloc[i]\n",
    "                    \n",
    "                # Find all of their matches \n",
    "                matches = await understat.get_player_matches(id)\n",
    "\n",
    "                # Manipulate Python object into (temporary) DataFrame\n",
    "                match_stats_str = json.dumps(matches)\n",
    "                match_stats_dict = json.loads(match_stats_str)\n",
    "                df_match_stats = pd.DataFrame.from_dict(match_stats_dict)\n",
    "\n",
    "                # Create new columns to identify player/gameweek\n",
    "                df_match_stats['id'] = id\n",
    "                df_match_stats['event'] = gameweek['event']\n",
    "                df_match_stats['end_dt'] = gameweek['end_dt']\n",
    "                df_match_stats['60_days_before_end_dt'] = gameweek['60_days_before_end_dt']\n",
    "                df_match_stats['season_start_dt'] = gameweek['season_start_dt']\n",
    "\n",
    "                # Convert date column to datetime\n",
    "                df_match_stats['date'] = pd.to_datetime(df_match_stats['date'], format='%Y-%m-%d', utc=True)\n",
    "\n",
    "                # Reorder columns\n",
    "                df_match_stats.insert(0, \"id\", df_match_stats.pop(\"id\"))\n",
    "                df_match_stats.insert(1, \"event\", df_match_stats.pop(\"event\"))\n",
    "                df_match_stats.insert(2, \"date\", df_match_stats.pop(\"date\"))\n",
    "                df_match_stats.insert(3, \"end_dt\", df_match_stats.pop(\"end_dt\"))\n",
    "                df_match_stats.insert(4, \"60_days_before_end_dt\", df_match_stats.pop(\"60_days_before_end_dt\"))\n",
    "                df_match_stats.insert(5, \"season_start_dt\", df_match_stats.pop(\"season_start_dt\"))\n",
    "\n",
    "                # Drop columns\n",
    "                df_match_stats = df_match_stats.drop(columns={'position','h_team','a_team','season','roster_id',\n",
    "                                                              'h_goals','a_goals'})\n",
    "\n",
    "                # Find matches within date range\n",
    "                df_match_stats = df_match_stats[(df_match_stats['date'] >= df_match_stats['season_start_dt']) & \n",
    "                                                (df_match_stats['date'] <= df_match_stats['end_dt'])]  \n",
    "\n",
    "                # Create 'within_last_60_days' field to help identify which rows should be aggregated together\n",
    "                within_last_60_days = []\n",
    "                for i in range(0,len(df_match_stats)):\n",
    "                    match = df_match_stats.iloc[i]\n",
    "\n",
    "                    # Has the match occured within the last 60 days?\n",
    "                    if match['date'] >= match['60_days_before_end_dt']:\n",
    "                        within_last_60_days.append(True)\n",
    "                    else:\n",
    "                        within_last_60_days.append(False)\n",
    "                df_match_stats['within_last_60_days'] = within_last_60_days \n",
    "\n",
    "                # Formatting\n",
    "                df_match_stats.insert(6, \"within_last_60_days\", df_match_stats.pop(\"within_last_60_days\"))\n",
    "                df_match_stats = df_match_stats.drop(columns={'end_dt','60_days_before_end_dt','season_start_dt'})\n",
    "\n",
    "                # Change datatypes\n",
    "                for col in df_match_stats.columns:\n",
    "                    if col not in ['date','within_last_60_days']:\n",
    "                        df_match_stats[col] = pd.to_numeric(df_match_stats[col])\n",
    "\n",
    "                #  Now we need to aggregate matches on two levels:\n",
    "                #  1/2) ALL matches so far to give Understats for the Season, so far\n",
    "\n",
    "                # Aggregate Match Stats for the season\n",
    "                df_season_stats = df_match_stats.drop(columns='within_last_60_days').groupby(['id','event']).sum()\n",
    "                df_season_stats = df_season_stats.reset_index()\n",
    "                \n",
    "                # Rename columns for season DataFrame\n",
    "                new_headers = []\n",
    "                for col in df_season_stats.columns:\n",
    "                    if col in ['id','event']:\n",
    "                        new_headers.append(col)\n",
    "                    else:\n",
    "                        new_headers.append(col+'_season')\n",
    "                df_season_stats.columns = new_headers\n",
    "        \n",
    "                #  2/2) Only matches within the last 60 days to give Understats for the recent period\n",
    "        \n",
    "                # Find matches in the MA period\n",
    "                df_games_in_MA_period = df_match_stats[df_match_stats['within_last_60_days']==True]\n",
    "                df_games_in_MA_period = df_games_in_MA_period.sort_values(by='date',ascending=True)\n",
    "                \n",
    "                # Call wma function to calculate WMA for each variable\n",
    "                for var in ['goals','shots','xG','time','xA','assists','key_passes','npg','npxG','xGChain','xGBuildup']:\n",
    "                    df_games_in_MA_period = wma(df_games_in_MA_period, col=var)\n",
    "\n",
    "                # Check there's games in the MA period    \n",
    "                if len(df_games_in_MA_period) != 0:\n",
    "\n",
    "                    # Redefine WMA stats as a new DataFrame\n",
    "                    df_WMA_stats = df_games_in_MA_period.iloc[[len(df_games_in_MA_period)-1]].drop(columns={'date',\n",
    "                                                    'within_last_60_days','goals','shots','xG','time','xA','assists',\n",
    "                                                    'key_passes','npg','npxG','xGChain','xGBuildup'})    \n",
    "\n",
    "                    # Now we can merge the two DataFrames together & append to array outside loop\n",
    "                    df_gameweek_stats = pd.merge(df_season_stats, df_WMA_stats, on=['id','event'], how='left')\n",
    "                    gameweek_stats_array.append(df_gameweek_stats)\n",
    "                    \n",
    "                # We have to treat the special case when there no games in the MA period seperately \n",
    "                # (i.e. think of gameweek 1, or even gameweek 2) \n",
    "                elif len(df_games_in_MA_period) == 0:\n",
    "                    pass\n",
    "                \n",
    "            # Convert gameweek_stats_array into single DataFrame\n",
    "            df_player_stats = pd.concat(gameweek_stats_array)\n",
    "            df_player_stats.insert(0, \"id\", df_player_stats.pop(\"id\"))\n",
    "            df_player_stats.insert(1, \"event\", df_player_stats.pop(\"event\"))\n",
    "            \n",
    "            # Create team_title/player_name columns\n",
    "            df_player_stats['team_title'] = team_title\n",
    "            df_player_stats.insert(2, \"team_title\", df_player_stats.pop(\"team_title\"))\n",
    "            df_player_stats['player_name'] = player_name\n",
    "            df_player_stats.insert(3, \"player_name\", df_player_stats.pop(\"player_name\"))\n",
    "\n",
    "            # Rename id column\n",
    "            df_player_stats = df_player_stats.rename(columns={'id':'Understat_id'})\n",
    "\n",
    "            # Append to player_stats_array\n",
    "            player_stats_array.append(df_player_stats)\n",
    "            \n",
    "        # Convert player_stats_array into single DataFrame\n",
    "        df_understat_players = pd.concat(player_stats_array)\n",
    "        df_understat_players = df_understat_players.reset_index(drop=True)\n",
    "        \n",
    "        return df_understat_players"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 518/518 [1:01:55<00:00,  7.17s/it]\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(14455, 26)\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>Understat_id</th>\n",
       "      <th>event</th>\n",
       "      <th>team_title</th>\n",
       "      <th>player_name</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1250</td>\n",
       "      <td>2</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0.239134</td>\n",
       "      <td>90</td>\n",
       "      <td>0.868721</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0.239134</td>\n",
       "      <td>1.069634</td>\n",
       "      <td>0.100011</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.239134</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.868721</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.239134</td>\n",
       "      <td>1.069634</td>\n",
       "      <td>0.100011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1250</td>\n",
       "      <td>3</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>0.589793</td>\n",
       "      <td>180</td>\n",
       "      <td>0.980238</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.589793</td>\n",
       "      <td>1.979391</td>\n",
       "      <td>0.596978</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.313484</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.363918</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.313484</td>\n",
       "      <td>0.963049</td>\n",
       "      <td>0.364648</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Understat_id  event team_title    player_name  goals_season  shots_season  \\\n",
       "0          1250      2  Liverpool  Mohamed Salah             1             5   \n",
       "1          1250      3  Liverpool  Mohamed Salah             1            10   \n",
       "\n",
       "   xG_season  time_season  xA_season  assists_season  key_passes_season  \\\n",
       "0   0.239134           90   0.868721               2                  3   \n",
       "1   0.589793          180   0.980238               2                  5   \n",
       "\n",
       "   npg_season  npxG_season  xGChain_season  xGBuildup_season  goals_WMA  \\\n",
       "0           1     0.239134        1.069634          0.100011   1.000000   \n",
       "1           1     0.589793        1.979391          0.596978   0.333333   \n",
       "\n",
       "   shots_WMA    xG_WMA  time_WMA    xA_WMA  assists_WMA  key_passes_WMA  \\\n",
       "0        5.0  0.239134      90.0  0.868721     2.000000        3.000000   \n",
       "1        5.0  0.313484      90.0  0.363918     0.666667        2.333333   \n",
       "\n",
       "    npg_WMA  npxG_WMA  xGChain_WMA  xGBuildup_WMA  \n",
       "0  1.000000  0.239134     1.069634       0.100011  \n",
       "1  0.333333  0.313484     0.963049       0.364648  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 1h 1min 56s (started: 2022-03-18 17:01:54 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Event loop\n",
    "loop = asyncio.get_event_loop()\n",
    "df_understat_players = loop.run_until_complete(underlying_player_stats())\n",
    "\n",
    "print(df_understat_players.shape)\n",
    "df_understat_players[df_understat_players['player_name']=='Mohamed Salah'].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Save down player statistics as an intermediate step (due to significant runtime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 60.2 ms (started: 2022-03-18 18:06:48 +00:00)\n"
     ]
    }
   ],
   "source": [
    "path = \"/Users/samharrison/Documents/data_sci/fpl_points_predictor/data/understat_players.csv\"\n",
    "# df_understat_players.to_csv(index=False, path_or_buf = path) \n",
    "df_understat_players = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also store a historic version of the player statistics data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 213 µs (started: 2022-03-18 18:06:48 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# path = \"/Users/samharrison/Documents/data_sci/fpl_points_predictor/data/data_archive/understat_players\"+str(gameweek_num)+\".csv\"\n",
    "# df_understat_players.to_csv(index=False, path_or_buf = path) \n",
    "# df_understat_players = pd.read_csv(path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Gather underlying team statistics from https://understat.com/¶"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 4.81 ms (started: 2022-03-18 18:06:48 +00:00)\n"
     ]
    }
   ],
   "source": [
    "async def underlying_team_stats():\n",
    "    \n",
    "    \"\"\"Returns a DataFrame with underlying team statistics from https://understat.com/, \n",
    "    e.g. goals/xG/goals_against/xGA etc.\n",
    "\n",
    "        Note: the DataFrame consists of 1 row per team per gameweek\n",
    "\n",
    "    :param None\n",
    "    :rtype: DataFrame df_understat_teamss: A DataFrame containing all of the underlying team \n",
    "    statistics available from https://understat.com/.\n",
    "    \"\"\"\n",
    "        \n",
    "    async with aiohttp.ClientSession() as session:\n",
    "        understat = Understat(session)\n",
    "        \n",
    "        # Use get_league_results function from Understat class\n",
    "        results_dict = await understat.get_league_results(\"epl\", 2021)\n",
    "        df_results = pd.DataFrame.from_dict(results_dict)\n",
    "        \n",
    "        # Initialise columns\n",
    "        h_team_title_col = []\n",
    "        a_team_title_col = []\n",
    "        h_team_goals_col = []\n",
    "        a_team_goals_col = []\n",
    "        h_team_xG_col = []\n",
    "        a_team_xG_col = []\n",
    "\n",
    "        # For every result\n",
    "        for i in range(0, len(df_results)):\n",
    "            game = df_results.iloc[i]\n",
    "\n",
    "            # Retrieve data from dictionary keys and append to columns\n",
    "            home_team_title = game['h']['title']\n",
    "            away_team_title = game['a']['title']\n",
    "            h_team_title_col.append(home_team_title)\n",
    "            a_team_title_col.append(away_team_title)\n",
    "\n",
    "            home_team_goals = game['goals']['h'] \n",
    "            away_team_goals = game['goals']['a']\n",
    "            h_team_goals_col.append(home_team_goals)\n",
    "            a_team_goals_col.append(away_team_goals)\n",
    "\n",
    "            home_team_xG = game['xG']['h']\n",
    "            away_team_xG = game['xG']['a']\n",
    "            h_team_xG_col.append(home_team_xG)\n",
    "            a_team_xG_col.append(away_team_xG)\n",
    "\n",
    "        # Set new variables\n",
    "        df_results['h_team_title'] = h_team_title_col\n",
    "        df_results['a_team_title'] = a_team_title_col\n",
    "        df_results['h_team_goals'] = h_team_goals_col\n",
    "        df_results['a_team_goals'] = a_team_goals_col\n",
    "        df_results['h_team_xG'] = h_team_xG_col\n",
    "        df_results['a_team_xG'] = a_team_xG_col\n",
    "        \n",
    "        # Drop columns and format datetime column\n",
    "        df_results = df_results[['datetime','h_team_title','a_team_title','h_team_goals','a_team_goals',\n",
    "                                 'h_team_xG','a_team_xG']]\n",
    "        df_results['datetime'] = pd.to_datetime(df_results['datetime'], format='%Y-%m-%d %H:%M:%S', utc=True)\n",
    "        \n",
    "        # Now, we'll iterate across every team and every gameweek to calculate the final output\n",
    "        # First however, we find all the unique teams\n",
    "        team_titles = set(df_results['h_team_title'].unique().tolist() + df_results['a_team_title'].unique().tolist())\n",
    "        \n",
    "        # Initialise array to store data for each individual team\n",
    "        all_teams_results_by_gameweek_array = []\n",
    "        \n",
    "        # For every team\n",
    "        for team_title in team_titles:\n",
    "            \n",
    "            # Find all results the given team has played in\n",
    "            df_team_results = df_results[(df_results['h_team_title']==team_title) | (df_results['a_team_title']==team_title)]\n",
    "            \n",
    "            # Initialise array to store results for each individual team AND gameweek\n",
    "            team_results_by_gameweek_array = []\n",
    "            \n",
    "            # For every gameweek\n",
    "            for i in range(0,len(df_gameweeks)):            \n",
    "                gameweek = df_gameweeks.iloc[i]\n",
    "                \n",
    "                # Define local variables\n",
    "                df_team_results['team_title'] = team_title\n",
    "                df_team_results['event'] = gameweek['event']\n",
    "                df_team_results['end_dt'] = gameweek['end_dt']\n",
    "                df_team_results['60_days_before_end_dt'] = gameweek['60_days_before_end_dt']\n",
    "                df_team_results['season_start_dt'] = gameweek['season_start_dt']\n",
    "                \n",
    "                # Find relevant gameweeks \n",
    "                df_team_gameweek_results = df_team_results[(df_team_results['datetime'] >= df_team_results['season_start_dt']) \n",
    "                                                         & (df_team_results['datetime'] <= df_team_results['end_dt'])]\n",
    "                \n",
    "                # Create 'within_last_60_days' variable to help identify which rows should be aggregated together\n",
    "                within_last_60_days = []\n",
    "                for i in range(0,len(df_team_gameweek_results)):\n",
    "                    result = df_team_gameweek_results.iloc[i]\n",
    "\n",
    "                    # Has the match occured within the last 60 days?\n",
    "                    if result['datetime'] >= result['60_days_before_end_dt']:\n",
    "                        within_last_60_days.append(True)\n",
    "                    else:\n",
    "                        within_last_60_days.append(False)\n",
    "                df_team_gameweek_results['within_last_60_days'] = within_last_60_days \n",
    "                \n",
    "                # Formatting\n",
    "                df_team_gameweek_results.insert(3, \"team_title\", df_team_gameweek_results.pop(\"team_title\"))\n",
    "                df_team_gameweek_results.insert(9, \"within_last_60_days\", df_team_gameweek_results.pop(\"within_last_60_days\"))\n",
    "                df_team_gameweek_results = df_team_gameweek_results.drop(columns={'end_dt','60_days_before_end_dt',\n",
    "                                                                                  'season_start_dt'})\n",
    "               \n",
    "                # Create feature columns\n",
    "                xG_col = []                \n",
    "                goals_col = []\n",
    "                xGA_col = []\n",
    "                goals_against_col = [] \n",
    "                for i in range(0,len(df_team_gameweek_results)):\n",
    "                    result = df_team_gameweek_results.iloc[i]\n",
    "                    \n",
    "                    if result['h_team_title']==team_title:\n",
    "                        xG_col.append(result['h_team_xG'])\n",
    "                        goals_col.append(result['h_team_goals'])\n",
    "                        xGA_col.append(result['a_team_xG'])\n",
    "                        goals_against_col.append(result['a_team_goals'])\n",
    "                    else:\n",
    "                        xG_col.append(result['a_team_xG'])\n",
    "                        goals_col.append(result['a_team_goals'])\n",
    "                        xGA_col.append(result['h_team_xG'])\n",
    "                        goals_against_col.append(result['h_team_goals'])\n",
    "                \n",
    "                # Set new features\n",
    "                df_team_gameweek_results['xG'] = xG_col\n",
    "                df_team_gameweek_results.insert(3, \"xG\", df_team_gameweek_results.pop(\"xG\"))\n",
    "                df_team_gameweek_results['goals'] = goals_col\n",
    "                df_team_gameweek_results.insert(4, \"goals\", df_team_gameweek_results.pop(\"goals\"))\n",
    "                df_team_gameweek_results['xGA'] = xGA_col\n",
    "                df_team_gameweek_results.insert(5, \"xGA\", df_team_gameweek_results.pop(\"xGA\"))\n",
    "                df_team_gameweek_results['goals_against'] = goals_against_col\n",
    "                df_team_gameweek_results.insert(6, \"goals_against\", df_team_gameweek_results.pop(\"goals_against\"))\n",
    "\n",
    "                # Change datatypes\n",
    "                for col in df_team_gameweek_results.columns:\n",
    "                    if col not in ['datetime','h_team_title','a_team_title','team_title','within_last_60_days']:\n",
    "                        df_team_gameweek_results[col] = pd.to_numeric(df_team_gameweek_results[col])\n",
    "                        \n",
    "                # Formatting \n",
    "                df_team_gameweek_results = df_team_gameweek_results[['datetime','xG','goals','xGA','goals_against',\n",
    "                                                                     'event','within_last_60_days']]\n",
    "                        \n",
    "                #  Now we need to aggregate matches on two levels:        \n",
    "                #  1/2) ALL matches so far to give Understats for the Season, so far\n",
    "\n",
    "                # Aggregate Match Stats for the season\n",
    "                df_season_stats = df_team_gameweek_results.drop(columns=['within_last_60_days',\n",
    "                                                                         'datetime']).groupby(['event']).sum().reset_index()\n",
    "                \n",
    "                # Rename columns for season DataFrame\n",
    "                new_headers = []\n",
    "                for col in df_season_stats.columns:\n",
    "                    if col in ['id','event']:\n",
    "                        new_headers.append(col)\n",
    "                    else:\n",
    "                        new_headers.append(col+'_season')\n",
    "                df_season_stats.columns = new_headers\n",
    "        \n",
    "                #  2/2) Only matches within the last 60 days to give Understats for the recent period\n",
    "                # Find matches in the MA period\n",
    "                df_games_in_MA_period = df_team_gameweek_results[df_team_gameweek_results['within_last_60_days']==True]\n",
    "                df_games_in_MA_period = df_games_in_MA_period.sort_values(by='datetime',ascending=True)\n",
    "                \n",
    "                # Call wma function to calculate WMA for each variable\n",
    "                for var in ['xG','goals','xGA','goals_against']:\n",
    "                    df_games_in_MA_period = wma(df_games_in_MA_period, col=var)\n",
    "                \n",
    "                # Check there's games in the MA period    \n",
    "                if len(df_games_in_MA_period) != 0:\n",
    "                    \n",
    "                    # Redefine WMA stats as a new DataFrame\n",
    "                    df_WMA_stats = df_games_in_MA_period.iloc[[len(df_games_in_MA_period)-1]]\n",
    "                    df_WMA_stats = df_WMA_stats.drop(columns={'datetime','xG','goals','xGA','goals_against',\n",
    "                                                              'within_last_60_days'})    \n",
    "                    \n",
    "                    # Now we can merge the two DataFrames together & append to array outside loop\n",
    "                    df_team_gameweek_results = pd.merge(df_season_stats, df_WMA_stats, on=['event'], how='left')\n",
    "                    team_results_by_gameweek_array.append(df_team_gameweek_results)\n",
    "                    \n",
    "                # We have to treat the special case when there no games in the MA period seperately \n",
    "                # (i.e. think of gameweek 1, or even gameweek 2) \n",
    "                elif len(df_games_in_MA_period) == 0:\n",
    "                    pass\n",
    "        \n",
    "            # Convert team_results_by_gameweek_array into single DataFrame\n",
    "            df_team_results_by_gameweek = pd.concat(team_results_by_gameweek_array)\n",
    "            df_team_results_by_gameweek['team_title'] = team_title\n",
    "            df_team_results_by_gameweek.insert(0, \"team_title\", df_team_results_by_gameweek.pop(\"team_title\"))\n",
    "            df_team_results_by_gameweek.insert(1, \"event\", df_team_results_by_gameweek.pop(\"event\"))\n",
    "        \n",
    "            # Append to array of DataFrame containing results for all teams\n",
    "            all_teams_results_by_gameweek_array.append(df_team_results_by_gameweek)\n",
    "\n",
    "            # Convert all_teams_results_by_gameweek_array into single DataFrame\n",
    "            df_understat_teams = pd.concat(all_teams_results_by_gameweek_array).reset_index(drop=True)\n",
    "            df_understat_opponents = pd.concat(all_teams_results_by_gameweek_array).reset_index(drop=True)\n",
    "\n",
    "            # Rename column headers to identify 'team' metrics\n",
    "            new_headers = []\n",
    "            for col in df_understat_teams.columns:\n",
    "                if col not in ['team_title','event']:\n",
    "                    new_headers.append('team_'+col)\n",
    "                else: \n",
    "                    new_headers.append(col)\n",
    "            df_understat_teams.columns = new_headers\n",
    "            \n",
    "            # Rename column headers to identify 'opponent' metrics\n",
    "            new_headers = []\n",
    "            for col in df_understat_opponents.columns:\n",
    "                if col not in ['event']:\n",
    "                    new_headers.append('opponent_'+col)\n",
    "                else: \n",
    "                    new_headers.append(col)\n",
    "            df_understat_opponents.columns = new_headers\n",
    "            \n",
    "            # Reset indexes\n",
    "            df_understat_teams = df_understat_teams.reset_index(drop=True)\n",
    "            df_understat_opponents = df_understat_opponents.reset_index(drop=True)\n",
    "                \n",
    "        return df_understat_teams, df_understat_opponents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(706, 10)\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>team_title</th>\n",
       "      <th>event</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>Liverpool</td>\n",
       "      <td>2</td>\n",
       "      <td>1.78728</td>\n",
       "      <td>3</td>\n",
       "      <td>1.333300</td>\n",
       "      <td>0</td>\n",
       "      <td>1.787280</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.333300</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>Liverpool</td>\n",
       "      <td>3</td>\n",
       "      <td>4.42673</td>\n",
       "      <td>5</td>\n",
       "      <td>2.023764</td>\n",
       "      <td>0</td>\n",
       "      <td>2.355393</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>0.904743</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    team_title  event  team_xG_season  team_goals_season  team_xGA_season  \\\n",
       "494  Liverpool      2         1.78728                  3         1.333300   \n",
       "495  Liverpool      3         4.42673                  5         2.023764   \n",
       "\n",
       "     team_goals_against_season  team_xG_WMA  team_goals_WMA  team_xGA_WMA  \\\n",
       "494                          0     1.787280        3.000000      1.333300   \n",
       "495                          0     2.355393        2.333333      0.904743   \n",
       "\n",
       "     team_goals_against_WMA  \n",
       "494                     0.0  \n",
       "495                     0.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 19.5 s (started: 2022-03-18 18:06:48 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Event loop\n",
    "loop = asyncio.get_event_loop()\n",
    "df_understat_teams, df_understat_opponents = loop.run_until_complete(underlying_team_stats())\n",
    "\n",
    "print(df_understat_teams.shape)\n",
    "df_understat_teams[df_understat_teams['team_title']=='Liverpool'].head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(706, 10)\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>opponent_team_title</th>\n",
       "      <th>event</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>36</th>\n",
       "      <td>Chelsea</td>\n",
       "      <td>2</td>\n",
       "      <td>1.18709</td>\n",
       "      <td>3</td>\n",
       "      <td>0.321701</td>\n",
       "      <td>0</td>\n",
       "      <td>1.187090</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.321701</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Chelsea</td>\n",
       "      <td>3</td>\n",
       "      <td>4.82841</td>\n",
       "      <td>5</td>\n",
       "      <td>0.951800</td>\n",
       "      <td>0</td>\n",
       "      <td>2.823243</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>0.527300</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   opponent_team_title  event  opponent_xG_season  opponent_goals_season  \\\n",
       "36             Chelsea      2             1.18709                      3   \n",
       "37             Chelsea      3             4.82841                      5   \n",
       "\n",
       "    opponent_xGA_season  opponent_goals_against_season  opponent_xG_WMA  \\\n",
       "36             0.321701                              0         1.187090   \n",
       "37             0.951800                              0         2.823243   \n",
       "\n",
       "    opponent_goals_WMA  opponent_xGA_WMA  opponent_goals_against_WMA  \n",
       "36            3.000000          0.321701                         0.0  \n",
       "37            2.333333          0.527300                         0.0  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 10 ms (started: 2022-03-18 18:07:07 +00:00)\n"
     ]
    }
   ],
   "source": [
    "print(df_understat_opponents.shape)\n",
    "df_understat_opponents[df_understat_opponents['opponent_team_title']=='Chelsea'].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combine data from different sources into a single DataFrame\n",
    "## That is, match the **players** from https://fantasy.premierleague.com/ to https://understat.com/ \n",
    "* i.e. Match *df_fpl* and *df_understat_players*\n",
    "* The stages for this matching process were:\n",
    "    1. Match all rows where **player_name and team_title align**\n",
    "    2. Match as many rows as possible using **pandas_dedupe**\n",
    "    3. Match any remaining unmatched players **by hand**\n",
    "    4. Finally, perform the join itself!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stage 1:\n",
      "Successfully matched players: 81.9% \n",
      "Unmatched players: 85\n",
      "\n",
      "Importing data ...\n",
      "Reading from link_dataframes_learned_settings\n",
      "Clustering...\n",
      "# duplicate sets 76\n",
      "\n",
      "Stage 2:\n",
      "Successfully matched players: 98.1% \n",
      "Unmatched players: 9\n",
      "\n",
      "Stage 3:\n",
      "Successfully matched players: 98.5% \n",
      "Unmatched players: 7\n",
      "\n",
      "Sense check:\n",
      "Number of keys in FPL_to_Understat_dictionary: 462 \n",
      "Number of players on https://understat.com/: 470\n",
      "time: 12.4 s (started: 2022-03-18 18:07:08 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# For the FPL Players Data, we create a team_title column that can be used for matching\n",
    "team_title_dictionary = {1:'Arsenal',2:'Aston Villa',3:'Brentford',4:'Brighton',5:'Burnley',6:'Chelsea',\n",
    "                         7:'Crystal Palace',8:'Everton',9:'Leicester',10:'Leeds',11:'Liverpool',12:'Manchester City',\n",
    "                         13:'Manchester United',14:'Newcastle United',15:'Norwich',16:'Southampton',\n",
    "                         17:'Tottenham',18:'Watford',19:'West Ham',20:'Wolverhampton Wanderers'}\n",
    "team_title_col = []\n",
    "opponent_team_title_col = []\n",
    "for i in range(0, len(df_fpl)):\n",
    "\n",
    "    # Initialise local variables & Append\n",
    "    player = df_fpl.iloc[i]\n",
    "    team_title = team_title_dictionary[player['team']] \n",
    "    team_title_col.append(team_title)\n",
    "    opponent_team_title = team_title_dictionary[player['opponent_team']] \n",
    "    opponent_team_title_col.append(opponent_team_title)\n",
    "\n",
    "# Set New variable(s)\n",
    "df_fpl['team_title'] = team_title_col\n",
    "df_fpl.insert(3, 'team_title', df_fpl.pop('team_title'))\n",
    "df_fpl['opponent_team_title'] = opponent_team_title_col\n",
    "df_fpl.insert(8, 'opponent_team_title', df_fpl.pop('opponent_team_title'))\n",
    "\n",
    "# To begin with, we drop duplicated rows from our DataFrames, so that we have only 1 row per player\n",
    "df_fpl_unique = df_fpl[['FPL_id','player_name','team_title']]\n",
    "df_fpl_unique = df_fpl_unique.drop_duplicates(subset=['FPL_id','player_name','team_title'])\n",
    "df_understat_players_unique = df_understat_players[['Understat_id','player_name','team_title']]\n",
    "df_understat_players_unique = df_understat_players_unique.drop_duplicates(subset=['Understat_id','player_name','team_title'])\n",
    "\n",
    "# Change datatypes\n",
    "df_fpl_unique['FPL_id'] = df_fpl_unique['FPL_id'].astype(float)\n",
    "df_understat_players_unique['Understat_id'] = df_understat_players_unique['Understat_id'].astype(float)\n",
    "\n",
    "# Stage 1/3 of MATCHING: Match all rows where player_name and team_title align\n",
    "# Initalise \n",
    "FPL_to_Understat_dictionary = {} # Dictionary object\n",
    "matched_players = 0              # Counting variable\n",
    "unmatched_players_array = []     # Array of unmatchable players\n",
    "\n",
    "# For every unique player in df_understat_players\n",
    "for i in range(0, len(df_understat_players_unique)):\n",
    "    \n",
    "    # Flag variable\n",
    "    matched_flag = 0\n",
    "        \n",
    "    # For every unique player in df_fpl\n",
    "    for j in range(0, len(df_fpl_unique)):\n",
    "    \n",
    "        # If player_name and team_title are equal => We match the two records\n",
    "        if (df_fpl_unique['player_name'].iloc[j] == df_understat_players_unique['player_name'].iloc[i]) & (df_fpl_unique['team_title'].iloc[j] == df_understat_players_unique['team_title'].iloc[i]):\n",
    "            \n",
    "            # Create key in dictionary\n",
    "            FPL_to_Understat_dictionary[df_fpl_unique['FPL_id'].iloc[j]] = df_understat_players_unique['Understat_id'].iloc[i]\n",
    "            \n",
    "            # Update Count and Flag variable\n",
    "            matched_players = matched_players + 1 \n",
    "            matched_flag = 1\n",
    "        else:\n",
    "            pass\n",
    "    \n",
    "    # If Flag variable is still equal to 0, we define the player to be currently 'unmatched'\n",
    "    if matched_flag == 0:\n",
    "        \n",
    "        # Append player to unmatched_players_array\n",
    "        unmatched_players_array.append(df_understat_players_unique['Understat_id'].iloc[i])        \n",
    "    else:\n",
    "        pass\n",
    "    \n",
    "print('Stage 1:\\nSuccessfully matched players: '+str(round((matched_players/len(df_understat_players_unique))*100, 1))+'%', \n",
    "      '\\nUnmatched players: '+str(len(unmatched_players_array)))\n",
    "print(\"\")\n",
    "\n",
    "# Stage 2/3 of MATCHING: Match as many rows as possible using pandas_dedupe (machine learning)\n",
    "# Focus on remaining unmatched players DataFrame\n",
    "df_understat_players_unmatched = df_understat_players_unique[df_understat_players_unique['Understat_id'].isin(unmatched_players_array)].reset_index(drop=True)\n",
    "\n",
    "# Now, we use the pandas_dedupe package\n",
    "df_matched_by_clustering = pandas_dedupe.link_dataframes(df_understat_players_unmatched, df_fpl_unique, ['player_name','team_title'])\n",
    "df_matched_by_clustering = df_matched_by_clustering[['cluster id', 'player_name', 'team_title', 'confidence', 'Understat_id', 'FPL_id']]\n",
    "\n",
    "# Drop null values (these are players that have been already matched / or haven't been matched by the clustering algorithm)\n",
    "df_matched_by_clustering = df_matched_by_clustering.dropna(subset = ['cluster id'])\n",
    "\n",
    "# For every cluster pair created by the algorithm\n",
    "for cluster in df_matched_by_clustering['cluster id'].unique():\n",
    "    \n",
    "    # Find the rows belonging to that cluster\n",
    "    df_matched_by_clustering_temp = df_matched_by_clustering[df_matched_by_clustering['cluster id'] == cluster]\n",
    "    \n",
    "    # Initialise local variables\n",
    "    Understat_id = float(df_matched_by_clustering_temp['Understat_id'].dropna().iloc[0])\n",
    "    FPL_id = float(df_matched_by_clustering_temp['FPL_id'].dropna().iloc[0])\n",
    "        \n",
    "    # Create key in dictionary\n",
    "    FPL_to_Understat_dictionary[FPL_id] = Understat_id\n",
    "    \n",
    "    # Update Count & unmatched_players_array\n",
    "    matched_players = matched_players + 1         \n",
    "    unmatched_players_array.remove(Understat_id)\n",
    "\n",
    "print(\"\")\n",
    "print('Stage 2:\\nSuccessfully matched players: '+str(round((matched_players/len(df_understat_players_unique))*100, 1))+'%', \n",
    "      '\\nUnmatched players: '+str(len(unmatched_players_array)))\n",
    "print(\"\")\n",
    "\n",
    "# Stage 3/3 of MATCHING: Match any remaining players by hand \n",
    "# Define new DataFrame with remaining unmatched players\n",
    "df_understat_players_unmatched = df_understat_players_unique[df_understat_players_unique['Understat_id'].isin(unmatched_players_array)]\n",
    "df_understat_players_unmatched\n",
    "\n",
    "# Manually add remaining players\n",
    "remaining_players = [[287, 5595],\n",
    "                     [594, 6030]] # Daniel James\n",
    "\n",
    "# For every unmatched player\n",
    "for player in remaining_players:\n",
    "\n",
    "    # Create key in dictionary\n",
    "    FPL_to_Understat_dictionary[float(player[0])] = float(player[1])\n",
    "    \n",
    "    # Update Count & unmatched_players_array\n",
    "    matched_players = matched_players + 1 \n",
    "    unmatched_players_array.remove(player[1])\n",
    "    \n",
    "print('Stage 3:\\nSuccessfully matched players: '+str(round((matched_players/len(df_understat_players_unique))*100, 1))+'%', '\\nUnmatched players: '+str(len(unmatched_players_array)))\n",
    "print(\"\\nSense check:\")\n",
    "print('Number of keys in FPL_to_Understat_dictionary: '+str(len(FPL_to_Understat_dictionary)), '\\nNumber of players on https://understat.com/: '+str(len(df_understat_players['Understat_id'].unique())))\n",
    "\n",
    "# Finally, we perform the join between Data sources!\n",
    "# First, we create a Understat_id column in df_fpl using the dictionary we have just created\n",
    "Understat_id_col = []\n",
    "for i in range(0,len(df_fpl)):\n",
    "    # Not all players have underlying statistics available, e.g. due to gametime\n",
    "    if df_fpl['FPL_id'].iloc[i] not in FPL_to_Understat_dictionary.keys():\n",
    "        Understat_id_col.append('NaN')\n",
    "    else:\n",
    "        Understat_id_col.append(FPL_to_Understat_dictionary[df_fpl['FPL_id'].iloc[i]])\n",
    "\n",
    "# Set New variable\n",
    "df_fpl['Understat_id'] = Understat_id_col\n",
    "df_fpl.insert(1, 'Understat_id', df_fpl.pop('Understat_id'))\n",
    "\n",
    "# Replace nulls (these are players without underlying statistics available) and change datatypes\n",
    "df_fpl['Understat_id'] = df_fpl['Understat_id'].replace('NaN',0).astype(np.int64)\n",
    "\n",
    "# Join the two DataFrames/Data sources\n",
    "pd.set_option(\"display.max_rows\", 25)\n",
    "df_raw_data = pd.merge(df_fpl, df_understat_players.drop(columns={'player_name'}), \n",
    "                       on=['Understat_id','event','team_title'], how='left').reset_index(drop=True)"
   ]
  },
  {
   "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>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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13285</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Norwich</td>\n",
       "      <td>15</td>\n",
       "      <td>A</td>\n",
       "      <td>125.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>17.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13286</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Burnley</td>\n",
       "      <td>5</td>\n",
       "      <td>H</td>\n",
       "      <td>126.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>3.0</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.000000</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.239134</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.868721</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.239134</td>\n",
       "      <td>1.069634</td>\n",
       "      <td>0.100011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13287</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Chelsea</td>\n",
       "      <td>6</td>\n",
       "      <td>H</td>\n",
       "      <td>126.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.589793</td>\n",
       "      <td>180.0</td>\n",
       "      <td>0.980238</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.589793</td>\n",
       "      <td>1.979391</td>\n",
       "      <td>0.596978</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.313484</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.363918</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>2.333333</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.313484</td>\n",
       "      <td>0.963049</td>\n",
       "      <td>0.364648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13288</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Leeds</td>\n",
       "      <td>10</td>\n",
       "      <td>A</td>\n",
       "      <td>125.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>1.513792</td>\n",
       "      <td>270.0</td>\n",
       "      <td>1.092932</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.752624</td>\n",
       "      <td>2.552213</td>\n",
       "      <td>0.894274</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.618742</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.238306</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>2.666667</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>0.238157</td>\n",
       "      <td>0.767935</td>\n",
       "      <td>0.330972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13289</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>5.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Crystal Palace</td>\n",
       "      <td>7</td>\n",
       "      <td>H</td>\n",
       "      <td>125.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>2.785960</td>\n",
       "      <td>360.0</td>\n",
       "      <td>1.463667</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.024791</td>\n",
       "      <td>5.250166</td>\n",
       "      <td>2.879377</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>3.6</td>\n",
       "      <td>0.880112</td>\n",
       "      <td>90.0</td>\n",
       "      <td>0.291278</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>3.200000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.651761</td>\n",
       "      <td>1.539943</td>\n",
       "      <td>0.992624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13318</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>34.0</td>\n",
       "      <td>False</td>\n",
       "      <td>Everton</td>\n",
       "      <td>8</td>\n",
       "      <td>H</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13319</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>35.0</td>\n",
       "      <td>False</td>\n",
       "      <td>Newcastle United</td>\n",
       "      <td>14</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13320</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>36.0</td>\n",
       "      <td>False</td>\n",
       "      <td>Tottenham</td>\n",
       "      <td>17</td>\n",
       "      <td>H</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13321</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>37.0</td>\n",
       "      <td>False</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>16</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13322</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>38.0</td>\n",
       "      <td>False</td>\n",
       "      <td>Wolverhampton Wanderers</td>\n",
       "      <td>20</td>\n",
       "      <td>H</td>\n",
       "      <td>NaN</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>38 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       FPL_id  Understat_id    player_name  team team_title    position  \\\n",
       "13285     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "13286     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "13287     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "13288     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "13289     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "...       ...           ...            ...   ...        ...         ...   \n",
       "13318     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "13319     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "13320     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "13321     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "13322     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "\n",
       "       element_type  event  finished      opponent_team_title  opponent_team  \\\n",
       "13285             3    1.0      True                  Norwich             15   \n",
       "13286             3    2.0      True                  Burnley              5   \n",
       "13287             3    3.0      True                  Chelsea              6   \n",
       "13288             3    4.0      True                    Leeds             10   \n",
       "13289             3    5.0      True           Crystal Palace              7   \n",
       "...             ...    ...       ...                      ...            ...   \n",
       "13318             3   34.0     False                  Everton              8   \n",
       "13319             3   35.0     False         Newcastle United             14   \n",
       "13320             3   36.0     False                Tottenham             17   \n",
       "13321             3   37.0     False              Southampton             16   \n",
       "13322             3   38.0     False  Wolverhampton Wanderers             20   \n",
       "\n",
       "      home_or_away  value status  chance_of_playing_this_round  \\\n",
       "13285            A  125.0      a                         100.0   \n",
       "13286            H  126.0      a                         100.0   \n",
       "13287            H  126.0      a                         100.0   \n",
       "13288            A  125.0      a                         100.0   \n",
       "13289            H  125.0      a                         100.0   \n",
       "...            ...    ...    ...                           ...   \n",
       "13318            H    NaN      a                         100.0   \n",
       "13319            A    NaN      a                         100.0   \n",
       "13320            H    NaN      a                         100.0   \n",
       "13321            A    NaN      a                         100.0   \n",
       "13322            H    NaN      a                         100.0   \n",
       "\n",
       "       chance_of_playing_next_round  total_points  goals_season  shots_season  \\\n",
       "13285                         100.0          17.0           NaN           NaN   \n",
       "13286                         100.0           3.0           1.0           5.0   \n",
       "13287                         100.0          10.0           1.0          10.0   \n",
       "13288                         100.0           8.0           2.0          13.0   \n",
       "13289                         100.0          12.0           3.0          16.0   \n",
       "...                             ...           ...           ...           ...   \n",
       "13318                         100.0           NaN           NaN           NaN   \n",
       "13319                         100.0           NaN           NaN           NaN   \n",
       "13320                         100.0           NaN           NaN           NaN   \n",
       "13321                         100.0           NaN           NaN           NaN   \n",
       "13322                         100.0           NaN           NaN           NaN   \n",
       "\n",
       "       xG_season  time_season  xA_season  assists_season  key_passes_season  \\\n",
       "13285        NaN          NaN        NaN             NaN                NaN   \n",
       "13286   0.239134         90.0   0.868721             2.0                3.0   \n",
       "13287   0.589793        180.0   0.980238             2.0                5.0   \n",
       "13288   1.513792        270.0   1.092932             2.0                8.0   \n",
       "13289   2.785960        360.0   1.463667             2.0               12.0   \n",
       "...          ...          ...        ...             ...                ...   \n",
       "13318        NaN          NaN        NaN             NaN                NaN   \n",
       "13319        NaN          NaN        NaN             NaN                NaN   \n",
       "13320        NaN          NaN        NaN             NaN                NaN   \n",
       "13321        NaN          NaN        NaN             NaN                NaN   \n",
       "13322        NaN          NaN        NaN             NaN                NaN   \n",
       "\n",
       "       npg_season  npxG_season  xGChain_season  xGBuildup_season  goals_WMA  \\\n",
       "13285         NaN          NaN             NaN               NaN        NaN   \n",
       "13286         1.0     0.239134        1.069634          0.100011   1.000000   \n",
       "13287         1.0     0.589793        1.979391          0.596978   0.333333   \n",
       "13288         1.0     0.752624        2.552213          0.894274   0.666667   \n",
       "13289         2.0     2.024791        5.250166          2.879377   0.800000   \n",
       "...           ...          ...             ...               ...        ...   \n",
       "13318         NaN          NaN             NaN               NaN        NaN   \n",
       "13319         NaN          NaN             NaN               NaN        NaN   \n",
       "13320         NaN          NaN             NaN               NaN        NaN   \n",
       "13321         NaN          NaN             NaN               NaN        NaN   \n",
       "13322         NaN          NaN             NaN               NaN        NaN   \n",
       "\n",
       "       shots_WMA    xG_WMA  time_WMA    xA_WMA  assists_WMA  key_passes_WMA  \\\n",
       "13285        NaN       NaN       NaN       NaN          NaN             NaN   \n",
       "13286        5.0  0.239134      90.0  0.868721     2.000000        3.000000   \n",
       "13287        5.0  0.313484      90.0  0.363918     0.666667        2.333333   \n",
       "13288        4.0  0.618742      90.0  0.238306     0.333333        2.666667   \n",
       "13289        3.6  0.880112      90.0  0.291278     0.200000        3.200000   \n",
       "...          ...       ...       ...       ...          ...             ...   \n",
       "13318        NaN       NaN       NaN       NaN          NaN             NaN   \n",
       "13319        NaN       NaN       NaN       NaN          NaN             NaN   \n",
       "13320        NaN       NaN       NaN       NaN          NaN             NaN   \n",
       "13321        NaN       NaN       NaN       NaN          NaN             NaN   \n",
       "13322        NaN       NaN       NaN       NaN          NaN             NaN   \n",
       "\n",
       "        npg_WMA  npxG_WMA  xGChain_WMA  xGBuildup_WMA  \n",
       "13285       NaN       NaN          NaN            NaN  \n",
       "13286  1.000000  0.239134     1.069634       0.100011  \n",
       "13287  0.333333  0.313484     0.963049       0.364648  \n",
       "13288  0.166667  0.238157     0.767935       0.330972  \n",
       "13289  0.500000  0.651761     1.539943       0.992624  \n",
       "...         ...       ...          ...            ...  \n",
       "13318       NaN       NaN          NaN            NaN  \n",
       "13319       NaN       NaN          NaN            NaN  \n",
       "13320       NaN       NaN          NaN            NaN  \n",
       "13321       NaN       NaN          NaN            NaN  \n",
       "13322       NaN       NaN          NaN            NaN  \n",
       "\n",
       "[38 rows x 39 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 51.5 ms (started: 2022-03-18 18:07:20 +00:00)\n"
     ]
    }
   ],
   "source": [
    "df_raw_data[df_raw_data['player_name']=='Mohamed Salah']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(25302, 39)\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>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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13285</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Norwich</td>\n",
       "      <td>15</td>\n",
       "      <td>A</td>\n",
       "      <td>125.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>17.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13286</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Burnley</td>\n",
       "      <td>5</td>\n",
       "      <td>H</td>\n",
       "      <td>126.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>3.0</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       FPL_id  Understat_id    player_name  team team_title    position  \\\n",
       "13285     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "13286     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "\n",
       "       element_type  event  finished opponent_team_title  opponent_team  \\\n",
       "13285             3    1.0      True             Norwich             15   \n",
       "13286             3    2.0      True             Burnley              5   \n",
       "\n",
       "      home_or_away  value status  chance_of_playing_this_round  \\\n",
       "13285            A  125.0      a                         100.0   \n",
       "13286            H  126.0      a                         100.0   \n",
       "\n",
       "       chance_of_playing_next_round  total_points  goals_season  shots_season  \\\n",
       "13285                         100.0          17.0           NaN           NaN   \n",
       "13286                         100.0           3.0           1.0           5.0   \n",
       "\n",
       "       xG_season  time_season  xA_season  assists_season  key_passes_season  \\\n",
       "13285        NaN          NaN        NaN             NaN                NaN   \n",
       "13286   0.239134         90.0   0.868721             2.0                3.0   \n",
       "\n",
       "       npg_season  npxG_season  xGChain_season  xGBuildup_season  goals_WMA  \\\n",
       "13285         NaN          NaN             NaN               NaN        NaN   \n",
       "13286         1.0     0.239134        1.069634          0.100011        1.0   \n",
       "\n",
       "       shots_WMA    xG_WMA  time_WMA    xA_WMA  assists_WMA  key_passes_WMA  \\\n",
       "13285        NaN       NaN       NaN       NaN          NaN             NaN   \n",
       "13286        5.0  0.239134      90.0  0.868721          2.0             3.0   \n",
       "\n",
       "       npg_WMA  npxG_WMA  xGChain_WMA  xGBuildup_WMA  \n",
       "13285      NaN       NaN          NaN            NaN  \n",
       "13286      1.0  0.239134     1.069634       0.100011  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 23.3 ms (started: 2022-03-18 18:07:20 +00:00)\n"
     ]
    }
   ],
   "source": [
    "print(df_raw_data.shape)\n",
    "df_raw_data[df_raw_data['player_name']=='Mohamed Salah'].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bring in team/opponent https://understat.com/ data for every player and every gameweek"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 56.2 ms (started: 2022-03-18 18:07:20 +00:00)\n"
     ]
    }
   ],
   "source": [
    "# Bring in team data\n",
    "df_raw_data = pd.merge(df_raw_data, df_understat_teams, on=['event','team_title'], how='left').reset_index(drop=True)\n",
    "\n",
    "# Bring in opponent data\n",
    "df_raw_data = pd.merge(df_raw_data, df_understat_opponents, on=['event','opponent_team_title'], how='left')\n",
    "df_raw_data = df_raw_data.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(25302, 55)\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>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>13285</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Norwich</td>\n",
       "      <td>15</td>\n",
       "      <td>A</td>\n",
       "      <td>125.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>17.0</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>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13286</th>\n",
       "      <td>233</td>\n",
       "      <td>1250</td>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>11</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>midfielder</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Burnley</td>\n",
       "      <td>5</td>\n",
       "      <td>H</td>\n",
       "      <td>126.0</td>\n",
       "      <td>a</td>\n",
       "      <td>100.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>3.0</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>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       FPL_id  Understat_id    player_name  team team_title    position  \\\n",
       "13285     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "13286     233          1250  Mohamed Salah    11  Liverpool  midfielder   \n",
       "\n",
       "       element_type  event  finished opponent_team_title  opponent_team  \\\n",
       "13285             3    1.0      True             Norwich             15   \n",
       "13286             3    2.0      True             Burnley              5   \n",
       "\n",
       "      home_or_away  value status  chance_of_playing_this_round  \\\n",
       "13285            A  125.0      a                         100.0   \n",
       "13286            H  126.0      a                         100.0   \n",
       "\n",
       "       chance_of_playing_next_round  total_points  goals_season  shots_season  \\\n",
       "13285                         100.0          17.0           NaN           NaN   \n",
       "13286                         100.0           3.0           1.0           5.0   \n",
       "\n",
       "       xG_season  time_season  xA_season  assists_season  key_passes_season  \\\n",
       "13285        NaN          NaN        NaN             NaN                NaN   \n",
       "13286   0.239134         90.0   0.868721             2.0                3.0   \n",
       "\n",
       "       npg_season  npxG_season  xGChain_season  xGBuildup_season  goals_WMA  \\\n",
       "13285         NaN          NaN             NaN               NaN        NaN   \n",
       "13286         1.0     0.239134        1.069634          0.100011        1.0   \n",
       "\n",
       "       shots_WMA    xG_WMA  time_WMA    xA_WMA  assists_WMA  key_passes_WMA  \\\n",
       "13285        NaN       NaN       NaN       NaN          NaN             NaN   \n",
       "13286        5.0  0.239134      90.0  0.868721          2.0             3.0   \n",
       "\n",
       "       npg_WMA  npxG_WMA  xGChain_WMA  xGBuildup_WMA  team_xG_season  \\\n",
       "13285      NaN       NaN          NaN            NaN             NaN   \n",
       "13286      1.0  0.239134     1.069634       0.100011         1.78728   \n",
       "\n",
       "       team_goals_season  team_xGA_season  team_goals_against_season  \\\n",
       "13285                NaN              NaN                        NaN   \n",
       "13286                3.0           1.3333                        0.0   \n",
       "\n",
       "       team_xG_WMA  team_goals_WMA  team_xGA_WMA  team_goals_against_WMA  \\\n",
       "13285          NaN             NaN           NaN                     NaN   \n",
       "13286      1.78728             3.0        1.3333                     0.0   \n",
       "\n",
       "       opponent_xG_season  opponent_goals_season  opponent_xGA_season  \\\n",
       "13285                 NaN                    NaN                  NaN   \n",
       "13286             1.79548                    1.0               1.6853   \n",
       "\n",
       "       opponent_goals_against_season  opponent_xG_WMA  opponent_goals_WMA  \\\n",
       "13285                            NaN              NaN                 NaN   \n",
       "13286                            2.0          1.79548                 1.0   \n",
       "\n",
       "       opponent_xGA_WMA  opponent_goals_against_WMA  \n",
       "13285               NaN                         NaN  \n",
       "13286            1.6853                         2.0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 44.1 ms (started: 2022-03-18 18:07:20 +00:00)\n"
     ]
    }
   ],
   "source": [
    "print(df_raw_data.shape)\n",
    "df_raw_data[df_raw_data['player_name']=='Mohamed Salah'].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overwrite raw data in local directory"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 1.06 s (started: 2022-03-18 18:07:20 +00:00)\n"
     ]
    }
   ],
   "source": [
    "path = \"/Users/samharrison/Documents/data_sci/fpl_points_predictor/data/raw_data.csv\"\n",
    "df_raw_data.to_csv(index=False, path_or_buf = path) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We also store a historic version of the raw data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "time: 1.38 s (started: 2022-03-18 18:07:21 +00:00)\n"
     ]
    }
   ],
   "source": [
    "path = \"/Users/samharrison/Documents/data_sci/fpl_points_predictor/data/data_archive/raw_data_gw\"+str(gameweek_num)+\".csv\"\n",
    "df_raw_data.to_csv(index=False, path_or_buf = path) "
   ]
  }
 ],
 "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
}