step1_scrape_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import libraries & UDFs\n",
    "## Import essentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-16T16:26:50.979883Z",
     "start_time": "2021-07-16T16:26:50.976059Z"
    }
   },
   "outputs": [],
   "source": [
    "from splinter import Browser\n",
    "from bs4 import BeautifulSoup as bs\n",
    "\n",
    "import caffeine\n",
    "import time\n",
    "from tqdm import tqdm\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import json\n",
    "from flatten_json import flatten"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## User defined functions\n",
    "\n",
    "### Generate tag string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-16T16:26:51.508132Z",
     "start_time": "2021-07-16T16:26:51.497355Z"
    }
   },
   "outputs": [],
   "source": [
    "def tag_string(tag, html_element):\n",
    "    typ = html_element.split(\"=\")[0]\n",
    "    name = html_element.split(\"=\")[1]\n",
    "    return tag + f\"[{typ}=\" + \"\\\"\" + name + \"\\\"]\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Display always on\n",
    "\n",
    "For when some cell take too long to run"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-16T16:26:55.443753Z",
     "start_time": "2021-07-16T16:26:55.439878Z"
    }
   },
   "outputs": [],
   "source": [
    "def on(): \n",
    "    caffeine.on(display=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Launch driver"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-15T22:56:19.158028Z",
     "start_time": "2021-07-15T22:56:19.153949Z"
    }
   },
   "outputs": [],
   "source": [
    "def launch_driver():\n",
    "    global browser\n",
    "    \n",
    "    path = {\"executable_path\": \"/Users/marclamy/Desktop/code/chromedriver6\"}\n",
    "\n",
    "    # Starting driver. Headless displays the browser. \n",
    "    browser = Browser(\"chrome\") \n",
    "    \n",
    "    # Let's go the the results page\n",
    "    browser.visit(\"https://www.premierleague.com/results?co=1&se=1&cl=-1\")\n",
    "\n",
    "#     # Thanks for the cookies I take them all 🍪\n",
    "#     browser.find_by_tag(tag_string(\"div\", \"class=btn-primary cookies-notice-accept\")).click()\n",
    "\n",
    "#     # There is an ad that is only temporary \n",
    "#     browser.find_by_tag(tag_string(\"a\", \"id=advertClose\")).click()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get data\n",
    "\n",
    "To get each match, I will scrape the Premier League's website which is made as follow for each game.\n",
    "\n",
    "*premierleague.com / match /* ***{id of the game}***\n",
    "\n",
    "First, I need the id of each game so I can loop from the list after. \n",
    "\n",
    "\n",
    "## Get season_id \n",
    "\n",
    "To scrape the ids of each game, I'll first need [chrome webdriver](https://chromedriver.chromium.org/downloads).\n",
    "\n",
    "### Start the driver"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-15T21:48:44.516602Z",
     "start_time": "2021-07-15T21:48:38.301157Z"
    }
   },
   "outputs": [],
   "source": [
    "launch_driver()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accept the cookies and close the ad "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-23T14:50:04.575544Z",
     "start_time": "2021-06-23T14:50:00.988597Z"
    }
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get each season id \n",
    "\n",
    "I choose from 95 as until then there were 22 teams (82 more games per year) and some seasons were incomplete, not all match were listed. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-15T22:56:29.031782Z",
     "start_time": "2021-07-15T22:56:29.026403Z"
    }
   },
   "outputs": [],
   "source": [
    "all_seasons = []\n",
    "\n",
    "for x in range(1992, 2021):\n",
    "    second = f\"0{x+1}\" if x+1 < 10 else x+1\n",
    "    all_seasons.append(f\"{x}/{str(x+1)[2:]}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "browser.find_by_tag(tag_string(\"div\", \"data-dropdown-current=compSeasons\")).click()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-15T23:07:17.978398Z",
     "start_time": "2021-07-15T23:07:13.819036Z"
    },
    "cell_style": "center",
    "code_folding": [],
    "scrolled": false
   },
   "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>season</th>\n",
       "      <th>season_id</th>\n",
       "      <th>nb_games</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020/21</td>\n",
       "      <td>363</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019/20</td>\n",
       "      <td>274</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018/19</td>\n",
       "      <td>210</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017/18</td>\n",
       "      <td>79</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016/17</td>\n",
       "      <td>54</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2015/16</td>\n",
       "      <td>42</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2014/15</td>\n",
       "      <td>27</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013/14</td>\n",
       "      <td>22</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2012/13</td>\n",
       "      <td>21</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2011/12</td>\n",
       "      <td>20</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2010/11</td>\n",
       "      <td>19</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2009/10</td>\n",
       "      <td>18</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2008/09</td>\n",
       "      <td>17</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2007/08</td>\n",
       "      <td>16</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2006/07</td>\n",
       "      <td>15</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2005/06</td>\n",
       "      <td>14</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2004/05</td>\n",
       "      <td>13</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2003/04</td>\n",
       "      <td>12</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2002/03</td>\n",
       "      <td>11</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2001/02</td>\n",
       "      <td>10</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2000/01</td>\n",
       "      <td>9</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1999/00</td>\n",
       "      <td>8</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1998/99</td>\n",
       "      <td>7</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1997/98</td>\n",
       "      <td>6</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1996/97</td>\n",
       "      <td>5</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1995/96</td>\n",
       "      <td>4</td>\n",
       "      <td>380</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1994/95</td>\n",
       "      <td>3</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1993/94</td>\n",
       "      <td>2</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1992/93</td>\n",
       "      <td>1</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     season season_id  nb_games\n",
       "0   2020/21       363       380\n",
       "1   2019/20       274       380\n",
       "2   2018/19       210       380\n",
       "3   2017/18        79       380\n",
       "4   2016/17        54       380\n",
       "5   2015/16        42       380\n",
       "6   2014/15        27       380\n",
       "7   2013/14        22       380\n",
       "8   2012/13        21       380\n",
       "9   2011/12        20       380\n",
       "10  2010/11        19       380\n",
       "11  2009/10        18       380\n",
       "12  2008/09        17       380\n",
       "13  2007/08        16       380\n",
       "14  2006/07        15       380\n",
       "15  2005/06        14       380\n",
       "16  2004/05        13       380\n",
       "17  2003/04        12       380\n",
       "18  2002/03        11       380\n",
       "19  2001/02        10       380\n",
       "20  2000/01         9       380\n",
       "21  1999/00         8       380\n",
       "22  1998/99         7       380\n",
       "23  1997/98         6       380\n",
       "24  1996/97         5       380\n",
       "25  1995/96         4       380\n",
       "26  1994/95         3       462\n",
       "27  1993/94         2       462\n",
       "28  1992/93         1       462"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "launch_driver()\n",
    "\n",
    "df_season_ids = pd.DataFrame(columns=[\"season\", \"season_id\"])\n",
    "\n",
    "browser.find_by_tag(tag_string(\"div\", \"data-dropdown-current=compSeasons\"), wait_time=10).click()\n",
    "\n",
    "\n",
    "for element in browser.find_by_tag(\"li\"): \n",
    "    season_id = element[\"data-option-id\"]\n",
    "    season = element[\"data-option-name\"]\n",
    "    if season_id != None and season in all_seasons:\n",
    "        df_season_ids.loc[len(df_season_ids)] = [season, season_id]\n",
    "\n",
    "df_season_ids[\"nb_games\"] = 380\n",
    "df_season_ids.loc[[26, 27, 28], \"nb_games\"] = 462\n",
    "df_season_ids"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Save season_id in a csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-15T23:07:22.053845Z",
     "start_time": "2021-07-15T23:07:22.045436Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_season_ids = df_season_ids.sort_values(by=\"season\").reset_index(drop=True)\n",
    "df_season_ids.to_csv(\"data/season_ids.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get match_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-15T23:29:52.780340Z",
     "start_time": "2021-07-15T23:10:33.459246Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2020/21 380 7                      \r"
     ]
    }
   ],
   "source": [
    "on()\n",
    "\n",
    "df_season_ids = pd.read_csv(\"data/season_ids.csv\")\n",
    "df_match_ids = pd.DataFrame(columns=[\"season\", \"season_id\", \"match_id\", \"nb_games\"])\n",
    "\n",
    "launch_driver()\n",
    "\n",
    "for index, season_id in enumerate(df_season_ids[\"season_id\"]):\n",
    "    all_match_ids = set() # for unique values directly\n",
    "    browser.visit(f\"https://www.premierleague.com/results?co=1&se={season_id}&cl=-1\")\n",
    "    \n",
    "    # There is a total of 380 games per season\n",
    "    count = 0\n",
    "    seasson_games_count = df_season_ids.loc[index, \"nb_games\"]\n",
    "    while len(all_match_ids) != seasson_games_count: \n",
    "        tag = browser.find_by_tag(\"li\", wait_time=10)\n",
    "        all_match_ids = {e[\"data-comp-match-item\"] for e in tag if e[\"data-comp-match-item\"] != None} \n",
    "        \n",
    "        browser.execute_script(f\"window.scroll(0, document.body.scrollHeight)\")\n",
    "        \n",
    "        if count % 2 == 0: \n",
    "            browser.execute_script(f\"window.scroll(0, 0)\")\n",
    "            \n",
    "            \n",
    "        print(df_season_ids.loc[index, \"season\"], len(all_match_ids), count, \" \"*20, end=\"\\r\")\n",
    "        \n",
    "        count += 1\n",
    "        \n",
    "        if count == 40:\n",
    "            all_match_ids = set()\n",
    "            browser.visit(f\"https://www.premierleague.com/results?co=1&se={season_id}&cl=-1\")\n",
    "            count = 0\n",
    "        \n",
    "        \n",
    "    for match_id in all_match_ids:\n",
    "        df_match_ids.loc[len(df_match_ids)] = [df_season_ids.loc[index, \"season\"], season_id, \n",
    "                                               match_id, seasson_games_count]\n",
    "    \n",
    "    \n",
    "\n",
    "browser.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-15T23:30:54.961951Z",
     "start_time": "2021-07-15T23:30:54.925438Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(11266, 4)\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>season</th>\n",
       "      <th>season_id</th>\n",
       "      <th>match_id</th>\n",
       "      <th>nb_games</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1992/93</td>\n",
       "      <td>1</td>\n",
       "      <td>136</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1992/93</td>\n",
       "      <td>1</td>\n",
       "      <td>108</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1992/93</td>\n",
       "      <td>1</td>\n",
       "      <td>263</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1992/93</td>\n",
       "      <td>1</td>\n",
       "      <td>101</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1992/93</td>\n",
       "      <td>1</td>\n",
       "      <td>241</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    season  season_id match_id  nb_games\n",
       "0  1992/93          1      136       462\n",
       "1  1992/93          1      108       462\n",
       "2  1992/93          1      263       462\n",
       "3  1992/93          1      101       462\n",
       "4  1992/93          1      241       462"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(df_match_ids.shape)\n",
    "display(df_match_ids.head())\n",
    "\n",
    "df_match_ids.to_csv(\"data/match_ids.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-15T23:31:50.546301Z",
     "start_time": "2021-07-15T23:31:50.539686Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          136\n",
       "1          108\n",
       "2          263\n",
       "3          101\n",
       "4          241\n",
       "         ...  \n",
       "11261    59062\n",
       "11262    58999\n",
       "11263    59197\n",
       "11264    59244\n",
       "11265    58975\n",
       "Name: match_id, Length: 11266, dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_match_ids[\"match_id\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-10T16:52:42.455649Z",
     "start_time": "2021-07-10T16:52:41.829328Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df_match_ids = pd.read_csv(\"data/match_ids.csv\")\n",
    "\n",
    "# df_players = pd.read_csv('data/all_players_r.csv')\n",
    "# df_events = pd.read_csv('data/all_events_r.csv')\n",
    "# df_games = pd.read_csv('data/all_games_r.csv')\n",
    "\n",
    "df_players = pd.DataFrame()\n",
    "df_events = pd.DataFrame()\n",
    "df_games = pd.DataFrame()\n",
    "\n",
    "# manquants = set(df_match_ids[\"match_id\"]) - set(df_players[\"match_id\"])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-06-23T13:15:23.992356Z",
     "start_time": "2021-06-23T13:15:23.973593Z"
    }
   },
   "source": [
    "## Get matchs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>matchPosition</th>\n",
       "      <th>matchShirtNumber</th>\n",
       "      <th>captain</th>\n",
       "      <th>playerId</th>\n",
       "      <th>info_position</th>\n",
       "      <th>info_positionInfo</th>\n",
       "      <th>nationalTeam_isoCode</th>\n",
       "      <th>nationalTeam_country</th>\n",
       "      <th>nationalTeam_demonym</th>\n",
       "      <th>birth_date_millis</th>\n",
       "      <th>...</th>\n",
       "      <th>name_display</th>\n",
       "      <th>name_first</th>\n",
       "      <th>name_last</th>\n",
       "      <th>id</th>\n",
       "      <th>altIds_opta</th>\n",
       "      <th>substitute</th>\n",
       "      <th>team_id</th>\n",
       "      <th>match_id</th>\n",
       "      <th>birth_place</th>\n",
       "      <th>info_shirtNum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>G</td>\n",
       "      <td>99</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>G</td>\n",
       "      <td>Goalkeeper</td>\n",
       "      <td>GB-ENG</td>\n",
       "      <td>England</td>\n",
       "      <td>English</td>\n",
       "      <td>-340934400000</td>\n",
       "      <td>...</td>\n",
       "      <td>Clive Baker</td>\n",
       "      <td>Clive</td>\n",
       "      <td>Baker</td>\n",
       "      <td>199</td>\n",
       "      <td>p16899</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>D</td>\n",
       "      <td>99</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>Defender</td>\n",
       "      <td>GB-ENG</td>\n",
       "      <td>England</td>\n",
       "      <td>English</td>\n",
       "      <td>-157075200000</td>\n",
       "      <td>...</td>\n",
       "      <td>David Linighan</td>\n",
       "      <td>David</td>\n",
       "      <td>Linighan</td>\n",
       "      <td>206</td>\n",
       "      <td>p2729</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>M</td>\n",
       "      <td>99</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>Midfielder</td>\n",
       "      <td>GB-ENG</td>\n",
       "      <td>England</td>\n",
       "      <td>English</td>\n",
       "      <td>24364800000</td>\n",
       "      <td>...</td>\n",
       "      <td>Gavin Johnson</td>\n",
       "      <td>Gavin</td>\n",
       "      <td>Johnson</td>\n",
       "      <td>209</td>\n",
       "      <td>p3649</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D</td>\n",
       "      <td>99</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>Defender</td>\n",
       "      <td>GB-SCT</td>\n",
       "      <td>Scotland</td>\n",
       "      <td>Scottish</td>\n",
       "      <td>-391651200000</td>\n",
       "      <td>...</td>\n",
       "      <td>John Wark</td>\n",
       "      <td>John</td>\n",
       "      <td>Wark</td>\n",
       "      <td>204</td>\n",
       "      <td>p17092</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>M</td>\n",
       "      <td>99</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>Midfielder</td>\n",
       "      <td>GB-ENG</td>\n",
       "      <td>England</td>\n",
       "      <td>English</td>\n",
       "      <td>-65145600000</td>\n",
       "      <td>...</td>\n",
       "      <td>Jason Dozzell</td>\n",
       "      <td>Jason</td>\n",
       "      <td>Dozzell</td>\n",
       "      <td>212</td>\n",
       "      <td>p3215</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>136</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>M</td>\n",
       "      <td>99</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>Midfielder</td>\n",
       "      <td>GB-ENG</td>\n",
       "      <td>England</td>\n",
       "      <td>English</td>\n",
       "      <td>89856000000</td>\n",
       "      <td>...</td>\n",
       "      <td>Garry Flitcroft</td>\n",
       "      <td>Garry</td>\n",
       "      <td>Flitcroft</td>\n",
       "      <td>300</td>\n",
       "      <td>p1249</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>233</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>D</td>\n",
       "      <td>99</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>Defender</td>\n",
       "      <td>IE</td>\n",
       "      <td>Ireland</td>\n",
       "      <td>Irish</td>\n",
       "      <td>-88300800000</td>\n",
       "      <td>...</td>\n",
       "      <td>Terry Phelan</td>\n",
       "      <td>Terry</td>\n",
       "      <td>Phelan</td>\n",
       "      <td>291</td>\n",
       "      <td>p1770</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>233</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>M</td>\n",
       "      <td>99</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>Midfielder</td>\n",
       "      <td>GB-ENG</td>\n",
       "      <td>England</td>\n",
       "      <td>English</td>\n",
       "      <td>-264038400000</td>\n",
       "      <td>...</td>\n",
       "      <td>Steve McMahon</td>\n",
       "      <td>Steve</td>\n",
       "      <td>McMahon</td>\n",
       "      <td>294</td>\n",
       "      <td>p10790</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>233</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>G</td>\n",
       "      <td>99</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>G</td>\n",
       "      <td>Goalkeeper</td>\n",
       "      <td>GB-ENG</td>\n",
       "      <td>England</td>\n",
       "      <td>English</td>\n",
       "      <td>-272073600000</td>\n",
       "      <td>...</td>\n",
       "      <td>Tony Coton</td>\n",
       "      <td>Tony</td>\n",
       "      <td>Coton</td>\n",
       "      <td>279</td>\n",
       "      <td>p15664</td>\n",
       "      <td>0</td>\n",
       "      <td>11</td>\n",
       "      <td>233</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>M</td>\n",
       "      <td>99</td>\n",
       "      <td>False</td>\n",
       "      <td>0</td>\n",
       "      <td>M</td>\n",
       "      <td>Midfielder</td>\n",
       "      <td>GB-ENG</td>\n",
       "      <td>England</td>\n",
       "      <td>English</td>\n",
       "      <td>-427075200000</td>\n",
       "      <td>...</td>\n",
       "      <td>Peter Reid</td>\n",
       "      <td>Peter</td>\n",
       "      <td>Reid</td>\n",
       "      <td>301</td>\n",
       "      <td>p8821</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>233</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>187 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   matchPosition  matchShirtNumber  captain  playerId info_position  \\\n",
       "0              G                99    False         0             G   \n",
       "1              D                99    False         0             D   \n",
       "2              M                99    False         0             M   \n",
       "3              D                99    False         0             D   \n",
       "4              M                99    False         0             M   \n",
       "..           ...               ...      ...       ...           ...   \n",
       "18             M                99    False         0             M   \n",
       "19             D                99    False         0             D   \n",
       "20             M                99    False         0             M   \n",
       "21             G                99    False         0             G   \n",
       "22             M                99    False         0             M   \n",
       "\n",
       "   info_positionInfo nationalTeam_isoCode nationalTeam_country  \\\n",
       "0         Goalkeeper               GB-ENG              England   \n",
       "1           Defender               GB-ENG              England   \n",
       "2         Midfielder               GB-ENG              England   \n",
       "3           Defender               GB-SCT             Scotland   \n",
       "4         Midfielder               GB-ENG              England   \n",
       "..               ...                  ...                  ...   \n",
       "18        Midfielder               GB-ENG              England   \n",
       "19          Defender                   IE              Ireland   \n",
       "20        Midfielder               GB-ENG              England   \n",
       "21        Goalkeeper               GB-ENG              England   \n",
       "22        Midfielder               GB-ENG              England   \n",
       "\n",
       "   nationalTeam_demonym  birth_date_millis  ...     name_display name_first  \\\n",
       "0               English      -340934400000  ...      Clive Baker      Clive   \n",
       "1               English      -157075200000  ...   David Linighan      David   \n",
       "2               English        24364800000  ...    Gavin Johnson      Gavin   \n",
       "3              Scottish      -391651200000  ...        John Wark       John   \n",
       "4               English       -65145600000  ...    Jason Dozzell      Jason   \n",
       "..                  ...                ...  ...              ...        ...   \n",
       "18              English        89856000000  ...  Garry Flitcroft      Garry   \n",
       "19                Irish       -88300800000  ...     Terry Phelan      Terry   \n",
       "20              English      -264038400000  ...    Steve McMahon      Steve   \n",
       "21              English      -272073600000  ...       Tony Coton       Tony   \n",
       "22              English      -427075200000  ...       Peter Reid      Peter   \n",
       "\n",
       "    name_last   id altIds_opta substitute team_id match_id  birth_place  \\\n",
       "0       Baker  199      p16899          0       8      136          NaN   \n",
       "1    Linighan  206       p2729          0       8      136          NaN   \n",
       "2     Johnson  209       p3649          0       8      136          NaN   \n",
       "3        Wark  204      p17092          0       8      136          NaN   \n",
       "4     Dozzell  212       p3215          0       8      136          NaN   \n",
       "..        ...  ...         ...        ...     ...      ...          ...   \n",
       "18  Flitcroft  300       p1249          0      11      233          NaN   \n",
       "19     Phelan  291       p1770          0      11      233          NaN   \n",
       "20    McMahon  294      p10790          0      11      233          NaN   \n",
       "21      Coton  279      p15664          0      11      233          NaN   \n",
       "22       Reid  301       p8821          1      11      233          NaN   \n",
       "\n",
       "   info_shirtNum  \n",
       "0            NaN  \n",
       "1            NaN  \n",
       "2            NaN  \n",
       "3            NaN  \n",
       "4            NaN  \n",
       "..           ...  \n",
       "18           NaN  \n",
       "19           NaN  \n",
       "20           NaN  \n",
       "21           NaN  \n",
       "22           NaN  \n",
       "\n",
       "[187 rows x 25 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_players = pd.concat([df_players, playersdf], axis=0)\n",
    "df_players"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2021-07-16T16:32:03.484473Z",
     "start_time": "2021-07-16T16:29:14.719454Z"
    }
   },
   "outputs": [],
   "source": [
    "on()\n",
    "df_match_ids = pd.read_csv(\"data/match_ids.csv\")\n",
    "\n",
    "# df_match_ids = pd.read_csv(\"real_data/match_ids.csv\")\n",
    "\n",
    "# df_players = pd.read_csv('real_data/all_players_92.csv')\n",
    "# df_events = pd.read_csv('real_data/all_events_92.csv')\n",
    "# df_games = pd.read_csv('real_data/all_games_92.csv')\n",
    "\n",
    "df_players = pd.DataFrame()\n",
    "df_events = pd.DataFrame()\n",
    "df_games = pd.DataFrame()\n",
    "\n",
    "# old = df_match_ids[\"match_id\"]\n",
    "# new = df_players[\"match_id\"]\n",
    "\n",
    "# ids = set(old) - set(new)\n",
    "\n",
    "\n",
    "ids = df_match_ids[\"match_id\"]\n",
    "# manquants = set(df_match_ids[\"match_id\"]) - set(df_games[\"match_id\"])\n",
    "\n",
    "\n",
    "print(len(ids))\n",
    "# print(fghjds)\n",
    "\n",
    "\n",
    "\n",
    "# the code is running only the first 400 ids right now, remove that if you dont wanna test it\n",
    "for index, match_id in enumerate(tqdm(ids)):\n",
    "    url = 'https://www.premierleague.com/match/' + str(match_id)\n",
    "    resp = requests.get(url)\n",
    "    soup = BeautifulSoup(resp.text, 'html.parser')\n",
    "    data = json.loads(soup.find('div', class_='mcTabsContainer').attrs['data-fixture'])\n",
    "\n",
    "    players = []\n",
    "    teams = []\n",
    "    for team in data['teamLists']:\n",
    "        team_id = team[\"teamId\"]\n",
    "    \n",
    "        for player in team[\"lineup\"]:\n",
    "            player = flatten(player)\n",
    "            player[\"substitute\"] = \"0\"\n",
    "            player[\"team_id\"] = team_id\n",
    "            players.append(flatten(player))\n",
    "\n",
    "        for player in team[\"substitutes\"]:\n",
    "            player = flatten(player)\n",
    "            player[\"substitute\"] = \"1\"\n",
    "            player[\"team_id\"] = team_id\n",
    "            players.append(flatten(player))\n",
    "            \n",
    "    playersdf = pd.DataFrame(players)\n",
    "    playersdf['match_id'] = match_id\n",
    "    df_players = pd.concat([df_players, playersdf], axis=0)\n",
    "\n",
    "    events = []\n",
    "    for event in data['events']:\n",
    "        events.append(flatten(event))\n",
    "    eventsdf = pd.DataFrame(events)\n",
    "    eventsdf['match_id'] = match_id\n",
    "    df_events = pd.concat([df_events, eventsdf], axis=0)\n",
    "\n",
    "    data['teamLists'] = ''\n",
    "    data['events'] = ''\n",
    "    match = pd.DataFrame([flatten(data)])\n",
    "    match['match_id'] = match_id\n",
    "    df_games = pd.concat([df_games, match], axis=0)\n",
    "    \n",
    "    \n",
    "#     if index % 50 == 0:\n",
    "    df_games.to_csv('data/all_games.csv', index=False)\n",
    "    df_players.to_csv('data/all_players.csv', index=False)\n",
    "    df_events.to_csv('data/all_events.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.13 ('main_env')",
   "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.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "vscode": {
   "interpreter": {
    "hash": "06d12861cad82bdcdde1b56bd9eda52e91f7df29dabbeda8f3d9112222750302"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}