web_scrapping_understatcom_for_xG_dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "-OvKzXnCFM3_"
   },
   "source": [
    "## Intro"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "c1c08_fkEkrE"
   },
   "source": [
    "![understat.JPG](images/understat.JPG)\n",
    "\n",
    "In this notebook I will describe the process of scraping data from web portal [understat.com](https://understat.com) that has a lot of statistical information about all games in top 5 European football leagues.\n",
    "\n",
    "From [understat.com](https://understat.com) home page:\n",
    "\n",
    "* Expected goals (xG) is the new revolutionary football metric, which allows you to evaluate team and player performance.\n",
    "\n",
    "* In a low-scoring game such as football, final match score does not provide a clear picture of performance.\n",
    "\n",
    "* This is why more and more sports analytics turn to the advanced models like xG, which is a statistical measure of the quality of chances created and conceded.\n",
    "\n",
    "* Our goal was to create the most precise method for shot quality evaluation.\n",
    "\n",
    "* For this case, we trained neural network prediction algorithms with the large dataset (>100,000 shots, over 10 parameters for each).\n",
    "\n",
    "* On this site, you will find our detailed xG statistics for the top European leagues.\n",
    "\n",
    "At this moment they have not only xG metric, but much more, that makes this site perfect for scraping statistical data about football games.\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "iVFq1VeQGvyH"
   },
   "source": [
    "We start by importing libraries that will be used in this project:\n",
    "* numpy - fundamental package for scientific computing with Python\n",
    "* pandas - library providing high-performance, easy-to-use data structures and data analysis tools\n",
    "* requests - is the only Non-GMO HTTP library for Python, safe for human consumption. (love this line from official docs :D)\n",
    "* BeautifulSoup - a Python library for pulling data out of HTML and XML files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
    "colab": {},
    "colab_type": "code",
    "id": "WAoZi6Mlg121"
   },
   "outputs": [],
   "source": [
    "import numpy as np # linear algebra\n",
    "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n",
    "import requests\n",
    "from bs4 import BeautifulSoup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "imMT_rB3Gj88"
   },
   "source": [
    "## Website research and structure of data"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "YbNEL_vxII0e"
   },
   "source": [
    "On the home page we can notice that the site has data for 6 European Leagues:\n",
    "\n",
    "![leagues.jpg](images/leagues.jpg)\n",
    "\n",
    "*   La Liga\n",
    "*   EPL\n",
    "*   BundesLiga\n",
    "*   Serie A\n",
    "*   Ligue 1\n",
    "*   RFPL\n",
    "\n",
    "And we also see that the collected starting from season 2014/2015. Another notion we make is the structure of URL. It is '`https://understat.com/league'` + '`/name_of_the_league`' + '`/year_start_of_the_season`'\n",
    "\n",
    "![seasons.jpg](images/seasons.jpg)\n",
    "\n",
    "So we create global variables with this data to be able to select any of those."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a",
    "colab": {},
    "colab_type": "code",
    "id": "5GdbwW7Tg127"
   },
   "outputs": [],
   "source": [
    "# create urls for all seasons of all leagues\n",
    "base_url = 'https://understat.com/league'\n",
    "leagues = ['La_liga', 'EPL', 'Bundesliga', 'Serie_A', 'Ligue_1', 'RFPL']\n",
    "seasons = ['2014', '2015', '2016', '2017', '2018']"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "X6TMbq27P5UP"
   },
   "source": [
    "Next step is to understan where the data is located on the web-page. For this we open Developer Tools in Chrome, go to tab \"Network\", find file with data (in this case 2018) and check the \"Response\" tab. This is what we will get after running *requests.get(URL)*\n",
    "\n",
    "![requests_response_1.jpg](images/requests_response_1.jpg)\n",
    "\n",
    "After going through content of the web-page we find that the data is stored under \"script\" tag and it is JSON encoded. So we will need to find this tag, get JSON from it and convert it into Python readable data structure.\n",
    "\n",
    "![requests_response_2.jpg](images/requests_response_2.jpg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "iXPittjCP4V6"
   },
   "outputs": [],
   "source": [
    "# Starting with latest data for Spanish league, because I'm a Barcelona fan\n",
    "url = base_url+'/'+leagues[0]+'/'+seasons[4]\n",
    "res = requests.get(url)\n",
    "soup = BeautifulSoup(res.content, \"lxml\")\n",
    "\n",
    "# Based on the structure of the webpage, I found that data is in the JSON variable, under <script> tags\n",
    "scripts = soup.find_all('script')\n",
    "\n",
    "# Check our <script> tags\n",
    "# for el in scripts:\n",
    "#   print('*'*50)\n",
    "#   print(el.text)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "6ZiH8okNTlS_"
   },
   "source": [
    "### Working with JSON"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "GfGeAu6zTuZh"
   },
   "source": [
    "We found that the data interesting us is stored in teamsData variable, after creating a soup of html tags it becomes just a string, so we find that text and extract JSON from it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "rkB-izz7aTtK"
   },
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "string_with_json_obj = ''\n",
    "\n",
    "# Find data for teams\n",
    "for el in scripts:\n",
    "    if 'teamsData' in str(el):\n",
    "      string_with_json_obj = str(el).strip()\n",
    "      \n",
    "# print(string_with_json_obj)\n",
    "\n",
    "# strip unnecessary symbols and get only JSON data\n",
    "ind_start = string_with_json_obj.index(\"('\")+2\n",
    "ind_end = string_with_json_obj.index(\"')\")\n",
    "json_data = string_with_json_obj[ind_start:ind_end]\n",
    "\n",
    "json_data = json_data.encode('utf8').decode('unicode_escape')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "_foRghUdURG2"
   },
   "source": [
    "Once we have gotten our JSON and cleaned it up we can convert it into Python dictionary and check how it looks (uncomment print statement to do that)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "0PIcc9QcaJcO"
   },
   "source": [
    "### Understanding data with Python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 201
    },
    "colab_type": "code",
    "id": "vTs5rZ8GG-BH",
    "outputId": "94819da3-5b2b-4d18-ebf7-963b4bd2bffc"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dict_keys(['138', '140', '141', '142', '143', '145', '146', '147', '148', '150', '151', '152', '153', '154', '156', '158', '159', '223', '231', '232'])\n",
      "==================================================\n",
      "dict_keys(['id', 'title', 'history'])\n",
      "==================================================\n",
      "138\n",
      "==================================================\n",
      "Sevilla\n",
      "==================================================\n",
      "{'h_a': 'a', 'xG': 3.34997, 'xGA': 2.39239, 'npxG': 3.34997, 'npxGA': 1.64976, 'ppda': {'att': 252, 'def': 19}, 'ppda_allowed': {'att': 263, 'def': 20}, 'deep': 12, 'deep_allowed': 5, 'scored': 4, 'missed': 1, 'xpts': 1.9829999999999999, 'result': 'w', 'date': '2018-08-19 21:15:00', 'wins': 1, 'draws': 0, 'loses': 0, 'pts': 3, 'npxGD': 1.70021}\n"
     ]
    }
   ],
   "source": [
    "# convert JSON data into Python dictionary\n",
    "data = json.loads(json_data)\n",
    "print(data.keys())\n",
    "print('='*50)\n",
    "print(data['138'].keys())\n",
    "print('='*50)\n",
    "print(data['138']['id'])\n",
    "print('='*50)\n",
    "print(data['138']['title'])\n",
    "print('='*50)\n",
    "print(data['138']['history'][0])\n",
    "\n",
    "# Print pretty JSON data to check out what we have there\n",
    "# s = json.dumps(data, indent=4, sort_keys=True)\n",
    "# print(s)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "7cC7J9w9Vu7O"
   },
   "source": [
    "If you want to check how the entire <code>data</code> looks, just uncomment respective lines. (it is commented for the sake of saving screen space and do not oversaturate the view of notebook).\n",
    "\n",
    "When we start to research the <code>data</code> we understand that this is a dictionary of dictionaries of 3 keys: *`id`*, *`title`* and *`history`*. The first layer of dictionary uses ids as keys too.\n",
    "\n",
    "Also from this we understand that *`history`* has data regarding every single match the team played in its own league (League Cup or Champions League games are not included).\n",
    "\n",
    "We can gather teams names if go over the first layer dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "2m-eSXDPHlio"
   },
   "outputs": [],
   "source": [
    "# Get teams and their relevant ids and put them into separate dictionary\n",
    "teams = {}\n",
    "for id in data.keys():\n",
    "  teams[id] = data[id]['title']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "LDZ-yinqZ5iu"
   },
   "source": [
    "The *`history`* is the array of dictionaries where keys are names of metrics (read column names) and values are values, despite how tautological is that :D.\n",
    "\n",
    "We understand that column names repeat over and over again so we add them to separate list. Also checking how the sample values look like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 74
    },
    "colab_type": "code",
    "id": "_T06_ZzQNZUO",
    "outputId": "79224546-2a4a-4fd2-d873-6be7140e694e"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['h_a', 'xG', 'xGA', 'npxG', 'npxGA', 'ppda', 'ppda_allowed', 'deep', 'deep_allowed', 'scored', 'missed', 'xpts', 'result', 'date', 'wins', 'draws', 'loses', 'pts', 'npxGD']\n",
      "['a', 3.34997, 2.39239, 3.34997, 1.64976, {'att': 252, 'def': 19}, {'att': 263, 'def': 20}, 12, 5, 4, 1, 1.9829999999999999, 'w', '2018-08-19 21:15:00', 1, 0, 0, 3, 1.70021]\n"
     ]
    }
   ],
   "source": [
    "# EDA to get a feeling of how the JSON is structured\n",
    "# Column names are all the same, so we just use first element\n",
    "columns = []\n",
    "# Check the sample of values per each column\n",
    "values = []\n",
    "for id in data.keys():\n",
    "  columns = list(data[id]['history'][0].keys())\n",
    "  values = list(data[id]['history'][0].values())\n",
    "  break\n",
    "\n",
    "print(columns)\n",
    "print(values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "8b4R26sxbVyj"
   },
   "source": [
    "Found that Sevilla has the id=138, so getting all the data for this team to be able to reproduce the same steps for all teams in the league."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 143
    },
    "colab_type": "code",
    "id": "dQ99jyMqOVPQ",
    "outputId": "025bab62-68fc-4aaf-ecbd-7bcbb2606746"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>h_a</th>\n",
       "      <th>xG</th>\n",
       "      <th>xGA</th>\n",
       "      <th>npxG</th>\n",
       "      <th>npxGA</th>\n",
       "      <th>ppda</th>\n",
       "      <th>ppda_allowed</th>\n",
       "      <th>deep</th>\n",
       "      <th>deep_allowed</th>\n",
       "      <th>scored</th>\n",
       "      <th>missed</th>\n",
       "      <th>xpts</th>\n",
       "      <th>result</th>\n",
       "      <th>date</th>\n",
       "      <th>wins</th>\n",
       "      <th>draws</th>\n",
       "      <th>loses</th>\n",
       "      <th>pts</th>\n",
       "      <th>npxGD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>3.34997</td>\n",
       "      <td>2.392390</td>\n",
       "      <td>3.34997</td>\n",
       "      <td>1.649760</td>\n",
       "      <td>{'att': 252, 'def': 19}</td>\n",
       "      <td>{'att': 263, 'def': 20}</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1.9830</td>\n",
       "      <td>w</td>\n",
       "      <td>2018-08-19 21:15:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.700210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>h</td>\n",
       "      <td>1.97161</td>\n",
       "      <td>0.671429</td>\n",
       "      <td>1.97161</td>\n",
       "      <td>0.671429</td>\n",
       "      <td>{'att': 262, 'def': 16}</td>\n",
       "      <td>{'att': 237, 'def': 26}</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2.3331</td>\n",
       "      <td>d</td>\n",
       "      <td>2018-08-26 21:15:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.300181</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  h_a       xG       xGA     npxG     npxGA                     ppda  \\\n",
       "0   a  3.34997  2.392390  3.34997  1.649760  {'att': 252, 'def': 19}   \n",
       "1   h  1.97161  0.671429  1.97161  0.671429  {'att': 262, 'def': 16}   \n",
       "\n",
       "              ppda_allowed  deep  deep_allowed  scored  missed    xpts result  \\\n",
       "0  {'att': 263, 'def': 20}    12             5       4       1  1.9830      w   \n",
       "1  {'att': 237, 'def': 26}    11             3       0       0  2.3331      d   \n",
       "\n",
       "                  date  wins  draws  loses  pts     npxGD  \n",
       "0  2018-08-19 21:15:00     1      0      0    3  1.700210  \n",
       "1  2018-08-26 21:15:00     0      1      0    1  1.300181  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sevilla_data = []\n",
    "for row in data['138']['history']:\n",
    "  sevilla_data.append(list(row.values()))\n",
    "  \n",
    "df = pd.DataFrame(sevilla_data, columns=columns)\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "wX5TEoItbfab"
   },
   "source": [
    "Wualya! We have the data for all matches of Sevilla in season 2018-2019 within La Liga!\n",
    "\n",
    "Now we want to do that for all Spanish teams. Let's loop through that bites baby!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 381
    },
    "colab_type": "code",
    "id": "Ea8kpU2kspBO",
    "outputId": "59a91082-fcde-4b69-ca75-936b1bd946db"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Added data for Sevilla.\n",
      "Added data for Real Sociedad.\n",
      "Added data for Espanyol.\n",
      "Added data for Getafe.\n",
      "Added data for Atletico Madrid.\n",
      "Added data for Rayo Vallecano.\n",
      "Added data for Valencia.\n",
      "Added data for Athletic Club.\n",
      "Added data for Barcelona.\n",
      "Added data for Real Madrid.\n",
      "Added data for Levante.\n",
      "Added data for Celta Vigo.\n",
      "Added data for Real Betis.\n",
      "Added data for Villarreal.\n",
      "Added data for Eibar.\n",
      "Added data for Alaves.\n",
      "Added data for Leganes.\n",
      "Added data for Girona.\n",
      "Added data for Real Valladolid.\n",
      "Added data for SD Huesca.\n"
     ]
    }
   ],
   "source": [
    "# Getting data for all teams\n",
    "dataframes = {}\n",
    "for id, team in teams.items():\n",
    "  teams_data = []\n",
    "  for row in data[id]['history']:\n",
    "    teams_data.append(list(row.values()))\n",
    "    \n",
    "  df = pd.DataFrame(teams_data, columns=columns)\n",
    "  dataframes[team] = df\n",
    "  print('Added data for {}.'.format(team))\n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "6w1O55gecEKa"
   },
   "source": [
    "Now we have a dictionary of DataFrames where key is the name of the team and value is the DataFrame with all games of that team."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 143
    },
    "colab_type": "code",
    "id": "QVV7KQ1xtS53",
    "outputId": "9c8c35d7-2020-42b4-ea72-9ed64ac6b1ac"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>h_a</th>\n",
       "      <th>xG</th>\n",
       "      <th>xGA</th>\n",
       "      <th>npxG</th>\n",
       "      <th>npxGA</th>\n",
       "      <th>ppda</th>\n",
       "      <th>ppda_allowed</th>\n",
       "      <th>deep</th>\n",
       "      <th>deep_allowed</th>\n",
       "      <th>scored</th>\n",
       "      <th>missed</th>\n",
       "      <th>xpts</th>\n",
       "      <th>result</th>\n",
       "      <th>date</th>\n",
       "      <th>wins</th>\n",
       "      <th>draws</th>\n",
       "      <th>loses</th>\n",
       "      <th>pts</th>\n",
       "      <th>npxGD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>h</td>\n",
       "      <td>3.26753</td>\n",
       "      <td>0.248353</td>\n",
       "      <td>3.26753</td>\n",
       "      <td>0.248353</td>\n",
       "      <td>{'att': 118, 'def': 17}</td>\n",
       "      <td>{'att': 407, 'def': 13}</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2.9009</td>\n",
       "      <td>w</td>\n",
       "      <td>2018-08-18 23:15:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3.019177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a</td>\n",
       "      <td>1.20392</td>\n",
       "      <td>0.510742</td>\n",
       "      <td>1.20392</td>\n",
       "      <td>0.510742</td>\n",
       "      <td>{'att': 163, 'def': 16}</td>\n",
       "      <td>{'att': 316, 'def': 15}</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1.9865</td>\n",
       "      <td>w</td>\n",
       "      <td>2018-08-25 23:15:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0.693178</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  h_a       xG       xGA     npxG     npxGA                     ppda  \\\n",
       "0   h  3.26753  0.248353  3.26753  0.248353  {'att': 118, 'def': 17}   \n",
       "1   a  1.20392  0.510742  1.20392  0.510742  {'att': 163, 'def': 16}   \n",
       "\n",
       "              ppda_allowed  deep  deep_allowed  scored  missed    xpts result  \\\n",
       "0  {'att': 407, 'def': 13}    20             0       3       0  2.9009      w   \n",
       "1  {'att': 316, 'def': 15}    15             4       1       0  1.9865      w   \n",
       "\n",
       "                  date  wins  draws  loses  pts     npxGD  \n",
       "0  2018-08-18 23:15:00     1      0      0    3  3.019177  \n",
       "1  2018-08-25 23:15:00     1      0      0    3  0.693178  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sample check of our newly created DataFrame\n",
    "dataframes['Barcelona'].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "oW_jK5u5pAJp"
   },
   "source": [
    "### Manipulations to make data as in the original source"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "EqKVfmTWcSq4"
   },
   "source": [
    "We can notice that here such metrics as PPDA and OPPDA (ppda and ppda_allowed) are represented as total amounts of attacking/defensive actions, but in the original table it is shown as coeficcient. Let's fix that!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 232
    },
    "colab_type": "code",
    "id": "5rp4BFQYUNsJ",
    "outputId": "a00d50c8-fb17-40aa-f48c-3d0845a4aef6"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>h_a</th>\n",
       "      <th>xG</th>\n",
       "      <th>xGA</th>\n",
       "      <th>npxG</th>\n",
       "      <th>npxGA</th>\n",
       "      <th>ppda</th>\n",
       "      <th>ppda_allowed</th>\n",
       "      <th>deep</th>\n",
       "      <th>deep_allowed</th>\n",
       "      <th>scored</th>\n",
       "      <th>...</th>\n",
       "      <th>xpts</th>\n",
       "      <th>result</th>\n",
       "      <th>date</th>\n",
       "      <th>wins</th>\n",
       "      <th>draws</th>\n",
       "      <th>loses</th>\n",
       "      <th>pts</th>\n",
       "      <th>npxGD</th>\n",
       "      <th>ppda_coef</th>\n",
       "      <th>oppda_coef</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>a</td>\n",
       "      <td>3.34997</td>\n",
       "      <td>2.392390</td>\n",
       "      <td>3.34997</td>\n",
       "      <td>1.649760</td>\n",
       "      <td>{'att': 252, 'def': 19}</td>\n",
       "      <td>{'att': 263, 'def': 20}</td>\n",
       "      <td>12</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>1.9830</td>\n",
       "      <td>w</td>\n",
       "      <td>2018-08-19 21:15:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.700210</td>\n",
       "      <td>13.263158</td>\n",
       "      <td>13.150000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>h</td>\n",
       "      <td>1.97161</td>\n",
       "      <td>0.671429</td>\n",
       "      <td>1.97161</td>\n",
       "      <td>0.671429</td>\n",
       "      <td>{'att': 262, 'def': 16}</td>\n",
       "      <td>{'att': 237, 'def': 26}</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.3331</td>\n",
       "      <td>d</td>\n",
       "      <td>2018-08-26 21:15:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.300181</td>\n",
       "      <td>16.375000</td>\n",
       "      <td>9.115385</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  h_a       xG       xGA     npxG     npxGA                     ppda  \\\n",
       "0   a  3.34997  2.392390  3.34997  1.649760  {'att': 252, 'def': 19}   \n",
       "1   h  1.97161  0.671429  1.97161  0.671429  {'att': 262, 'def': 16}   \n",
       "\n",
       "              ppda_allowed  deep  deep_allowed  scored     ...        xpts  \\\n",
       "0  {'att': 263, 'def': 20}    12             5       4     ...      1.9830   \n",
       "1  {'att': 237, 'def': 26}    11             3       0     ...      2.3331   \n",
       "\n",
       "   result                 date wins  draws  loses  pts     npxGD  ppda_coef  \\\n",
       "0       w  2018-08-19 21:15:00    1      0      0    3  1.700210  13.263158   \n",
       "1       d  2018-08-26 21:15:00    0      1      0    1  1.300181  16.375000   \n",
       "\n",
       "   oppda_coef  \n",
       "0   13.150000  \n",
       "1    9.115385  \n",
       "\n",
       "[2 rows x 21 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for team, df in dataframes.items():\n",
    "  dataframes[team]['ppda_coef'] = dataframes[team]['ppda'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)\n",
    "  dataframes[team]['oppda_coef'] = dataframes[team]['ppda_allowed'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)\n",
    "  \n",
    "# And check how our new dataframes look based on Sevilla dataframe\n",
    "dataframes['Sevilla'].head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "iY4Ig3GOdUcc"
   },
   "source": [
    "Now we have all our numbers, but for every single game. What we need is the totals for the team. Let's find out the columns we have to sum up. For this we go back to original table at [understat.com](https://understat.com/league/La_liga/2018) and we find that all metrics shoud be summed up and only PPDA and OPPDA are means in the end."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "jKIs5btTK83S"
   },
   "outputs": [],
   "source": [
    "cols_to_sum = ['xG', 'xGA', 'npxG', 'npxGA', 'deep', 'deep_allowed', 'scored', 'missed', 'xpts', 'wins', 'draws', 'loses', 'pts', 'npxGD']\n",
    "cols_to_mean = ['ppda_coef', 'oppda_coef']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "pyvGIw0KeJl8"
   },
   "source": [
    "We are ready to calculate our totals and means. For this we loop through dictionary of dataframes and call .sum() and .mean() DataFrame methods that return Series, that's why we add .transpose() to those calls. We put these new DataFrames into a list and after that concat them into a new DataFrame `full_stat`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Owv3orawUvEq"
   },
   "outputs": [],
   "source": [
    "frames = []\n",
    "for team, df in dataframes.items():\n",
    "  sum_data = pd.DataFrame(df[cols_to_sum].sum()).transpose()\n",
    "  mean_data = pd.DataFrame(df[cols_to_mean].mean()).transpose()\n",
    "  final_df = sum_data.join(mean_data)\n",
    "  final_df['team'] = team\n",
    "  final_df['matches'] = len(df)\n",
    "  frames.append(final_df)\n",
    "  \n",
    "full_stat = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Esc_PKrxnx32"
   },
   "source": [
    "Next we reorder columns for better readability, sort rows based on points, reset index and add column 'position'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "DB_FQAsvYJiY"
   },
   "outputs": [],
   "source": [
    "full_stat = full_stat[['team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'npxG', 'xGA', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts']]\n",
    "full_stat.sort_values('pts', ascending=False, inplace=True)\n",
    "full_stat.reset_index(inplace=True, drop=True)\n",
    "full_stat['position'] = range(1,len(full_stat)+1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "-crmm5rgokNH"
   },
   "source": [
    "Also in the original table we have values of differences between expected metrics and real. Let's add those too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "QdX6bilkuDM9"
   },
   "outputs": [],
   "source": [
    "full_stat['xG_diff'] = full_stat['xG'] - full_stat['scored']\n",
    "full_stat['xGA_diff'] = full_stat['xGA'] - full_stat['missed']\n",
    "full_stat['xpts_diff'] = full_stat['xpts'] - full_stat['pts']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "paYvvD8fphzH"
   },
   "source": [
    "Converting floats to integers where apropriate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "LA2gEhIuwvZt"
   },
   "outputs": [],
   "source": [
    "cols_to_int = ['wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'deep', 'deep_allowed']\n",
    "full_stat[cols_to_int] = full_stat[cols_to_int].astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "L1KVyRkCpuJo"
   },
   "source": [
    "Prettifying output and final view of a DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 437
    },
    "colab_type": "code",
    "id": "gPAF9Yetyt7B",
    "outputId": "74b29818-a8ab-47bf-d67c-15fc2b04584c"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>position</th>\n",
       "      <th>team</th>\n",
       "      <th>matches</th>\n",
       "      <th>wins</th>\n",
       "      <th>draws</th>\n",
       "      <th>loses</th>\n",
       "      <th>scored</th>\n",
       "      <th>missed</th>\n",
       "      <th>pts</th>\n",
       "      <th>xG</th>\n",
       "      <th>...</th>\n",
       "      <th>xGA</th>\n",
       "      <th>xGA_diff</th>\n",
       "      <th>npxGA</th>\n",
       "      <th>npxGD</th>\n",
       "      <th>ppda_coef</th>\n",
       "      <th>oppda_coef</th>\n",
       "      <th>deep</th>\n",
       "      <th>deep_allowed</th>\n",
       "      <th>xpts</th>\n",
       "      <th>xpts_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Barcelona</td>\n",
       "      <td>38</td>\n",
       "      <td>26</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>90</td>\n",
       "      <td>36</td>\n",
       "      <td>87</td>\n",
       "      <td>83.28</td>\n",
       "      <td>...</td>\n",
       "      <td>44.93</td>\n",
       "      <td>8.93</td>\n",
       "      <td>43.44</td>\n",
       "      <td>33.14</td>\n",
       "      <td>9.02</td>\n",
       "      <td>16.40</td>\n",
       "      <td>417</td>\n",
       "      <td>171</td>\n",
       "      <td>73.96</td>\n",
       "      <td>-13.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Atletico Madrid</td>\n",
       "      <td>38</td>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>6</td>\n",
       "      <td>55</td>\n",
       "      <td>29</td>\n",
       "      <td>76</td>\n",
       "      <td>51.87</td>\n",
       "      <td>...</td>\n",
       "      <td>41.43</td>\n",
       "      <td>12.43</td>\n",
       "      <td>37.72</td>\n",
       "      <td>11.01</td>\n",
       "      <td>11.07</td>\n",
       "      <td>11.10</td>\n",
       "      <td>252</td>\n",
       "      <td>190</td>\n",
       "      <td>59.43</td>\n",
       "      <td>-16.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Real Madrid</td>\n",
       "      <td>38</td>\n",
       "      <td>21</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>46</td>\n",
       "      <td>68</td>\n",
       "      <td>68.65</td>\n",
       "      <td>...</td>\n",
       "      <td>48.68</td>\n",
       "      <td>2.68</td>\n",
       "      <td>42.73</td>\n",
       "      <td>19.24</td>\n",
       "      <td>8.90</td>\n",
       "      <td>14.78</td>\n",
       "      <td>341</td>\n",
       "      <td>168</td>\n",
       "      <td>64.77</td>\n",
       "      <td>-3.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Valencia</td>\n",
       "      <td>38</td>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "      <td>7</td>\n",
       "      <td>51</td>\n",
       "      <td>35</td>\n",
       "      <td>61</td>\n",
       "      <td>61.88</td>\n",
       "      <td>...</td>\n",
       "      <td>42.85</td>\n",
       "      <td>7.85</td>\n",
       "      <td>36.91</td>\n",
       "      <td>19.66</td>\n",
       "      <td>12.96</td>\n",
       "      <td>9.47</td>\n",
       "      <td>278</td>\n",
       "      <td>215</td>\n",
       "      <td>65.16</td>\n",
       "      <td>4.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Sevilla</td>\n",
       "      <td>38</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>13</td>\n",
       "      <td>62</td>\n",
       "      <td>47</td>\n",
       "      <td>59</td>\n",
       "      <td>69.16</td>\n",
       "      <td>...</td>\n",
       "      <td>46.71</td>\n",
       "      <td>-0.29</td>\n",
       "      <td>41.51</td>\n",
       "      <td>23.03</td>\n",
       "      <td>10.65</td>\n",
       "      <td>10.02</td>\n",
       "      <td>321</td>\n",
       "      <td>211</td>\n",
       "      <td>65.08</td>\n",
       "      <td>6.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Getafe</td>\n",
       "      <td>38</td>\n",
       "      <td>15</td>\n",
       "      <td>14</td>\n",
       "      <td>9</td>\n",
       "      <td>48</td>\n",
       "      <td>35</td>\n",
       "      <td>59</td>\n",
       "      <td>47.03</td>\n",
       "      <td>...</td>\n",
       "      <td>44.23</td>\n",
       "      <td>9.23</td>\n",
       "      <td>39.02</td>\n",
       "      <td>3.56</td>\n",
       "      <td>8.77</td>\n",
       "      <td>5.70</td>\n",
       "      <td>186</td>\n",
       "      <td>196</td>\n",
       "      <td>53.19</td>\n",
       "      <td>-5.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Espanyol</td>\n",
       "      <td>38</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "      <td>13</td>\n",
       "      <td>48</td>\n",
       "      <td>50</td>\n",
       "      <td>53</td>\n",
       "      <td>50.16</td>\n",
       "      <td>...</td>\n",
       "      <td>54.62</td>\n",
       "      <td>4.62</td>\n",
       "      <td>48.55</td>\n",
       "      <td>-1.36</td>\n",
       "      <td>9.86</td>\n",
       "      <td>9.82</td>\n",
       "      <td>241</td>\n",
       "      <td>241</td>\n",
       "      <td>50.09</td>\n",
       "      <td>-2.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Athletic Club</td>\n",
       "      <td>38</td>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "      <td>41</td>\n",
       "      <td>45</td>\n",
       "      <td>53</td>\n",
       "      <td>44.44</td>\n",
       "      <td>...</td>\n",
       "      <td>47.16</td>\n",
       "      <td>2.16</td>\n",
       "      <td>43.44</td>\n",
       "      <td>-4.53</td>\n",
       "      <td>8.30</td>\n",
       "      <td>11.30</td>\n",
       "      <td>221</td>\n",
       "      <td>185</td>\n",
       "      <td>50.01</td>\n",
       "      <td>-2.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Real Sociedad</td>\n",
       "      <td>38</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>45</td>\n",
       "      <td>46</td>\n",
       "      <td>50</td>\n",
       "      <td>47.99</td>\n",
       "      <td>...</td>\n",
       "      <td>48.09</td>\n",
       "      <td>2.09</td>\n",
       "      <td>45.68</td>\n",
       "      <td>-5.13</td>\n",
       "      <td>9.94</td>\n",
       "      <td>9.49</td>\n",
       "      <td>194</td>\n",
       "      <td>208</td>\n",
       "      <td>51.13</td>\n",
       "      <td>1.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Alaves</td>\n",
       "      <td>38</td>\n",
       "      <td>13</td>\n",
       "      <td>11</td>\n",
       "      <td>14</td>\n",
       "      <td>39</td>\n",
       "      <td>50</td>\n",
       "      <td>50</td>\n",
       "      <td>40.87</td>\n",
       "      <td>...</td>\n",
       "      <td>54.53</td>\n",
       "      <td>4.53</td>\n",
       "      <td>50.07</td>\n",
       "      <td>-11.43</td>\n",
       "      <td>11.23</td>\n",
       "      <td>7.10</td>\n",
       "      <td>129</td>\n",
       "      <td>270</td>\n",
       "      <td>44.02</td>\n",
       "      <td>-5.98</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   position             team  matches  wins  draws  loses  scored  missed  \\\n",
       "0         1        Barcelona       38    26      9      3      90      36   \n",
       "1         2  Atletico Madrid       38    22     10      6      55      29   \n",
       "2         3      Real Madrid       38    21      5     12      63      46   \n",
       "3         4         Valencia       38    15     16      7      51      35   \n",
       "4         5          Sevilla       38    17      8     13      62      47   \n",
       "5         6           Getafe       38    15     14      9      48      35   \n",
       "6         7         Espanyol       38    14     11     13      48      50   \n",
       "7         8    Athletic Club       38    13     14     11      41      45   \n",
       "8         9    Real Sociedad       38    13     11     14      45      46   \n",
       "9        10           Alaves       38    13     11     14      39      50   \n",
       "\n",
       "   pts    xG    ...       xGA  xGA_diff  npxGA  npxGD  ppda_coef  oppda_coef  \\\n",
       "0   87 83.28    ...     44.93      8.93  43.44  33.14       9.02       16.40   \n",
       "1   76 51.87    ...     41.43     12.43  37.72  11.01      11.07       11.10   \n",
       "2   68 68.65    ...     48.68      2.68  42.73  19.24       8.90       14.78   \n",
       "3   61 61.88    ...     42.85      7.85  36.91  19.66      12.96        9.47   \n",
       "4   59 69.16    ...     46.71     -0.29  41.51  23.03      10.65       10.02   \n",
       "5   59 47.03    ...     44.23      9.23  39.02   3.56       8.77        5.70   \n",
       "6   53 50.16    ...     54.62      4.62  48.55  -1.36       9.86        9.82   \n",
       "7   53 44.44    ...     47.16      2.16  43.44  -4.53       8.30       11.30   \n",
       "8   50 47.99    ...     48.09      2.09  45.68  -5.13       9.94        9.49   \n",
       "9   50 40.87    ...     54.53      4.53  50.07 -11.43      11.23        7.10   \n",
       "\n",
       "   deep  deep_allowed  xpts  xpts_diff  \n",
       "0   417           171 73.96     -13.04  \n",
       "1   252           190 59.43     -16.57  \n",
       "2   341           168 64.77      -3.23  \n",
       "3   278           215 65.16       4.16  \n",
       "4   321           211 65.08       6.08  \n",
       "5   186           196 53.19      -5.81  \n",
       "6   241           241 50.09      -2.91  \n",
       "7   221           185 50.01      -2.99  \n",
       "8   194           208 51.13       1.13  \n",
       "9   129           270 44.02      -5.98  \n",
       "\n",
       "[10 rows x 22 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "col_order = ['position','team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'xG_diff', 'npxG', 'xGA', 'xGA_diff', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts', 'xpts_diff']\n",
    "full_stat = full_stat[col_order]\n",
    "pd.options.display.float_format = '{:,.2f}'.format\n",
    "full_stat.head(10)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Original table\n",
    "\n",
    "![full_table.JPG](images/full_table.JPG)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "hjvCDJRpqXEL"
   },
   "source": [
    "## Scrapping data for all teams of all leagues of all seasons"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "USqlXBaHqhOA"
   },
   "source": [
    "Testing the flow before going full into the process"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 854
    },
    "colab_type": "code",
    "id": "383biFfVd4H-",
    "outputId": "17dbe477-fa30-4a60-acc5-dcd3f7079b18"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'2018':     position             team  matches  wins  draws  loses  scored  missed  \\\n",
      "0          1        Barcelona       38    26      9      3      90      36   \n",
      "1          2  Atletico Madrid       38    22     10      6      55      29   \n",
      "2          3      Real Madrid       38    21      5     12      63      46   \n",
      "3          4         Valencia       38    15     16      7      51      35   \n",
      "4          5          Sevilla       38    17      8     13      62      47   \n",
      "5          6           Getafe       38    15     14      9      48      35   \n",
      "6          7         Espanyol       38    14     11     13      48      50   \n",
      "7          8    Athletic Club       38    13     14     11      41      45   \n",
      "8          9    Real Sociedad       38    13     11     14      45      46   \n",
      "9         10           Alaves       38    13     11     14      39      50   \n",
      "10        11       Real Betis       38    14      8     16      44      52   \n",
      "11        12            Eibar       38    11     14     13      46      50   \n",
      "12        13          Leganes       38    11     12     15      37      43   \n",
      "13        14          Levante       38    11     11     16      59      66   \n",
      "14        15       Villarreal       38    10     14     14      49      52   \n",
      "15        16       Celta Vigo       38    10     11     17      53      62   \n",
      "16        17  Real Valladolid       38    10     11     17      32      51   \n",
      "17        18           Girona       38     9     10     19      37      53   \n",
      "18        19        SD Huesca       38     7     12     19      43      65   \n",
      "19        20   Rayo Vallecano       38     8      8     22      41      70   \n",
      "\n",
      "    pts    xG    ...       xGA  xGA_diff  npxGA  npxGD  ppda_coef  oppda_coef  \\\n",
      "0    87 83.28    ...     44.93      8.93  43.44  33.14       9.02       16.40   \n",
      "1    76 51.87    ...     41.43     12.43  37.72  11.01      11.07       11.10   \n",
      "2    68 68.65    ...     48.68      2.68  42.73  19.24       8.90       14.78   \n",
      "3    61 61.88    ...     42.85      7.85  36.91  19.66      12.96        9.47   \n",
      "4    59 69.16    ...     46.71     -0.29  41.51  23.03      10.65       10.02   \n",
      "5    59 47.03    ...     44.23      9.23  39.02   3.56       8.77        5.70   \n",
      "6    53 50.16    ...     54.62      4.62  48.55  -1.36       9.86        9.82   \n",
      "7    53 44.44    ...     47.16      2.16  43.44  -4.53       8.30       11.30   \n",
      "8    50 47.99    ...     48.09      2.09  45.68  -5.13       9.94        9.49   \n",
      "9    50 40.87    ...     54.53      4.53  50.07 -11.43      11.23        7.10   \n",
      "10   50 51.68    ...     53.54      1.54  49.83  -3.48       9.32       12.38   \n",
      "11   47 56.63    ...     47.88     -2.12  44.91   5.78       7.07       11.22   \n",
      "12   45 41.25    ...     43.94      0.94  40.80  -3.26      12.47        9.96   \n",
      "13   44 54.09    ...     78.86     12.86  72.01 -23.13      10.58        7.04   \n",
      "14   44 54.80    ...     56.12      4.12  52.40  -2.08      11.15       11.17   \n",
      "15   41 48.70    ...     63.43      1.43  58.23 -14.73      10.77       11.76   \n",
      "16   41 41.64    ...     57.65      6.65  50.83 -14.39      11.45        9.83   \n",
      "17   37 42.99    ...     55.33      2.33  49.39 -10.86      11.39        9.86   \n",
      "18   33 51.38    ...     63.43     -1.57  56.74 -10.56      10.22        8.70   \n",
      "19   32 47.23    ...     62.32     -7.68  54.00 -10.48       9.53        7.52   \n",
      "\n",
      "    deep  deep_allowed  xpts  xpts_diff  \n",
      "0    417           171 73.96     -13.04  \n",
      "1    252           190 59.43     -16.57  \n",
      "2    341           168 64.77      -3.23  \n",
      "3    278           215 65.16       4.16  \n",
      "4    321           211 65.08       6.08  \n",
      "5    186           196 53.19      -5.81  \n",
      "6    241           241 50.09      -2.91  \n",
      "7    221           185 50.01      -2.99  \n",
      "8    194           208 51.13       1.13  \n",
      "9    129           270 44.02      -5.98  \n",
      "10   241           220 52.39       2.39  \n",
      "11   235           177 56.75       9.75  \n",
      "12   155           190 51.15       6.15  \n",
      "13   206           295 40.23      -3.77  \n",
      "14   232           263 52.18       8.18  \n",
      "15   195           248 41.63       0.63  \n",
      "16   139           243 41.80       0.80  \n",
      "17   177           261 45.17       8.17  \n",
      "18   171           259 44.96      11.96  \n",
      "19   133           253 43.02      11.02  \n",
      "\n",
      "[20 rows x 22 columns]}\n",
      "{'La_liga': {'2018':     position             team  matches  wins  draws  loses  scored  missed  \\\n",
      "0          1        Barcelona       38    26      9      3      90      36   \n",
      "1          2  Atletico Madrid       38    22     10      6      55      29   \n",
      "2          3      Real Madrid       38    21      5     12      63      46   \n",
      "3          4         Valencia       38    15     16      7      51      35   \n",
      "4          5          Sevilla       38    17      8     13      62      47   \n",
      "5          6           Getafe       38    15     14      9      48      35   \n",
      "6          7         Espanyol       38    14     11     13      48      50   \n",
      "7          8    Athletic Club       38    13     14     11      41      45   \n",
      "8          9    Real Sociedad       38    13     11     14      45      46   \n",
      "9         10           Alaves       38    13     11     14      39      50   \n",
      "10        11       Real Betis       38    14      8     16      44      52   \n",
      "11        12            Eibar       38    11     14     13      46      50   \n",
      "12        13          Leganes       38    11     12     15      37      43   \n",
      "13        14          Levante       38    11     11     16      59      66   \n",
      "14        15       Villarreal       38    10     14     14      49      52   \n",
      "15        16       Celta Vigo       38    10     11     17      53      62   \n",
      "16        17  Real Valladolid       38    10     11     17      32      51   \n",
      "17        18           Girona       38     9     10     19      37      53   \n",
      "18        19        SD Huesca       38     7     12     19      43      65   \n",
      "19        20   Rayo Vallecano       38     8      8     22      41      70   \n",
      "\n",
      "    pts    xG    ...       xGA  xGA_diff  npxGA  npxGD  ppda_coef  oppda_coef  \\\n",
      "0    87 83.28    ...     44.93      8.93  43.44  33.14       9.02       16.40   \n",
      "1    76 51.87    ...     41.43     12.43  37.72  11.01      11.07       11.10   \n",
      "2    68 68.65    ...     48.68      2.68  42.73  19.24       8.90       14.78   \n",
      "3    61 61.88    ...     42.85      7.85  36.91  19.66      12.96        9.47   \n",
      "4    59 69.16    ...     46.71     -0.29  41.51  23.03      10.65       10.02   \n",
      "5    59 47.03    ...     44.23      9.23  39.02   3.56       8.77        5.70   \n",
      "6    53 50.16    ...     54.62      4.62  48.55  -1.36       9.86        9.82   \n",
      "7    53 44.44    ...     47.16      2.16  43.44  -4.53       8.30       11.30   \n",
      "8    50 47.99    ...     48.09      2.09  45.68  -5.13       9.94        9.49   \n",
      "9    50 40.87    ...     54.53      4.53  50.07 -11.43      11.23        7.10   \n",
      "10   50 51.68    ...     53.54      1.54  49.83  -3.48       9.32       12.38   \n",
      "11   47 56.63    ...     47.88     -2.12  44.91   5.78       7.07       11.22   \n",
      "12   45 41.25    ...     43.94      0.94  40.80  -3.26      12.47        9.96   \n",
      "13   44 54.09    ...     78.86     12.86  72.01 -23.13      10.58        7.04   \n",
      "14   44 54.80    ...     56.12      4.12  52.40  -2.08      11.15       11.17   \n",
      "15   41 48.70    ...     63.43      1.43  58.23 -14.73      10.77       11.76   \n",
      "16   41 41.64    ...     57.65      6.65  50.83 -14.39      11.45        9.83   \n",
      "17   37 42.99    ...     55.33      2.33  49.39 -10.86      11.39        9.86   \n",
      "18   33 51.38    ...     63.43     -1.57  56.74 -10.56      10.22        8.70   \n",
      "19   32 47.23    ...     62.32     -7.68  54.00 -10.48       9.53        7.52   \n",
      "\n",
      "    deep  deep_allowed  xpts  xpts_diff  \n",
      "0    417           171 73.96     -13.04  \n",
      "1    252           190 59.43     -16.57  \n",
      "2    341           168 64.77      -3.23  \n",
      "3    278           215 65.16       4.16  \n",
      "4    321           211 65.08       6.08  \n",
      "5    186           196 53.19      -5.81  \n",
      "6    241           241 50.09      -2.91  \n",
      "7    221           185 50.01      -2.99  \n",
      "8    194           208 51.13       1.13  \n",
      "9    129           270 44.02      -5.98  \n",
      "10   241           220 52.39       2.39  \n",
      "11   235           177 56.75       9.75  \n",
      "12   155           190 51.15       6.15  \n",
      "13   206           295 40.23      -3.77  \n",
      "14   232           263 52.18       8.18  \n",
      "15   195           248 41.63       0.63  \n",
      "16   139           243 41.80       0.80  \n",
      "17   177           261 45.17       8.17  \n",
      "18   171           259 44.96      11.96  \n",
      "19   133           253 43.02      11.02  \n",
      "\n",
      "[20 rows x 22 columns]}}\n"
     ]
    }
   ],
   "source": [
    "season_data = dict()\n",
    "season_data[seasons[4]] = full_stat\n",
    "print(season_data)\n",
    "full_data = dict()\n",
    "full_data[leagues[0]] = season_data\n",
    "print(full_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "wdJtI3xCqu9w"
   },
   "source": [
    "Putting all the previous code into loops to get all data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 300
    },
    "colab_type": "code",
    "id": "YTHZPUJmmK2F",
    "outputId": "c454be03-424c-4a5a-df43-b99dd08e6ec8"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>team</th>\n",
       "      <th>matches</th>\n",
       "      <th>wins</th>\n",
       "      <th>draws</th>\n",
       "      <th>loses</th>\n",
       "      <th>scored</th>\n",
       "      <th>missed</th>\n",
       "      <th>pts</th>\n",
       "      <th>xG</th>\n",
       "      <th>xG_diff</th>\n",
       "      <th>...</th>\n",
       "      <th>xGA</th>\n",
       "      <th>xGA_diff</th>\n",
       "      <th>npxGA</th>\n",
       "      <th>npxGD</th>\n",
       "      <th>ppda_coef</th>\n",
       "      <th>oppda_coef</th>\n",
       "      <th>deep</th>\n",
       "      <th>deep_allowed</th>\n",
       "      <th>xpts</th>\n",
       "      <th>xpts_diff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>position</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Bundesliga</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">2014</th>\n",
       "      <th>1</th>\n",
       "      <td>Bayern Munich</td>\n",
       "      <td>34</td>\n",
       "      <td>25</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>80</td>\n",
       "      <td>18</td>\n",
       "      <td>79</td>\n",
       "      <td>66.21</td>\n",
       "      <td>-13.79</td>\n",
       "      <td>...</td>\n",
       "      <td>21.94</td>\n",
       "      <td>3.94</td>\n",
       "      <td>20.42</td>\n",
       "      <td>41.24</td>\n",
       "      <td>5.76</td>\n",
       "      <td>25.02</td>\n",
       "      <td>388</td>\n",
       "      <td>83</td>\n",
       "      <td>73.61</td>\n",
       "      <td>-5.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Wolfsburg</td>\n",
       "      <td>34</td>\n",
       "      <td>20</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>72</td>\n",
       "      <td>38</td>\n",
       "      <td>69</td>\n",
       "      <td>58.35</td>\n",
       "      <td>-13.65</td>\n",
       "      <td>...</td>\n",
       "      <td>38.82</td>\n",
       "      <td>0.82</td>\n",
       "      <td>36.42</td>\n",
       "      <td>18.90</td>\n",
       "      <td>9.14</td>\n",
       "      <td>10.29</td>\n",
       "      <td>172</td>\n",
       "      <td>164</td>\n",
       "      <td>59.95</td>\n",
       "      <td>-9.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Borussia M.Gladbach</td>\n",
       "      <td>34</td>\n",
       "      <td>19</td>\n",
       "      <td>9</td>\n",
       "      <td>6</td>\n",
       "      <td>53</td>\n",
       "      <td>26</td>\n",
       "      <td>66</td>\n",
       "      <td>51.74</td>\n",
       "      <td>-1.26</td>\n",
       "      <td>...</td>\n",
       "      <td>36.34</td>\n",
       "      <td>10.34</td>\n",
       "      <td>33.31</td>\n",
       "      <td>16.16</td>\n",
       "      <td>12.75</td>\n",
       "      <td>12.78</td>\n",
       "      <td>221</td>\n",
       "      <td>198</td>\n",
       "      <td>57.01</td>\n",
       "      <td>-8.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bayer Leverkusen</td>\n",
       "      <td>34</td>\n",
       "      <td>17</td>\n",
       "      <td>10</td>\n",
       "      <td>7</td>\n",
       "      <td>62</td>\n",
       "      <td>37</td>\n",
       "      <td>61</td>\n",
       "      <td>49.16</td>\n",
       "      <td>-12.84</td>\n",
       "      <td>...</td>\n",
       "      <td>34.72</td>\n",
       "      <td>-2.28</td>\n",
       "      <td>30.17</td>\n",
       "      <td>18.22</td>\n",
       "      <td>5.69</td>\n",
       "      <td>7.35</td>\n",
       "      <td>246</td>\n",
       "      <td>108</td>\n",
       "      <td>55.34</td>\n",
       "      <td>-5.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Augsburg</td>\n",
       "      <td>34</td>\n",
       "      <td>15</td>\n",
       "      <td>4</td>\n",
       "      <td>15</td>\n",
       "      <td>43</td>\n",
       "      <td>43</td>\n",
       "      <td>49</td>\n",
       "      <td>43.91</td>\n",
       "      <td>0.91</td>\n",
       "      <td>...</td>\n",
       "      <td>46.66</td>\n",
       "      <td>3.66</td>\n",
       "      <td>43.63</td>\n",
       "      <td>-5.91</td>\n",
       "      <td>7.63</td>\n",
       "      <td>10.40</td>\n",
       "      <td>134</td>\n",
       "      <td>221</td>\n",
       "      <td>45.50</td>\n",
       "      <td>-3.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         team  matches  wins  draws  loses  \\\n",
       "                position                                                     \n",
       "Bundesliga 2014 1               Bayern Munich       34    25      4      5   \n",
       "                2                   Wolfsburg       34    20      9      5   \n",
       "                3         Borussia M.Gladbach       34    19      9      6   \n",
       "                4            Bayer Leverkusen       34    17     10      7   \n",
       "                5                    Augsburg       34    15      4     15   \n",
       "\n",
       "                          scored  missed  pts    xG  xG_diff    ...       xGA  \\\n",
       "                position                                        ...             \n",
       "Bundesliga 2014 1             80      18   79 66.21   -13.79    ...     21.94   \n",
       "                2             72      38   69 58.35   -13.65    ...     38.82   \n",
       "                3             53      26   66 51.74    -1.26    ...     36.34   \n",
       "                4             62      37   61 49.16   -12.84    ...     34.72   \n",
       "                5             43      43   49 43.91     0.91    ...     46.66   \n",
       "\n",
       "                          xGA_diff  npxGA  npxGD  ppda_coef  oppda_coef  deep  \\\n",
       "                position                                                        \n",
       "Bundesliga 2014 1             3.94  20.42  41.24       5.76       25.02   388   \n",
       "                2             0.82  36.42  18.90       9.14       10.29   172   \n",
       "                3            10.34  33.31  16.16      12.75       12.78   221   \n",
       "                4            -2.28  30.17  18.22       5.69        7.35   246   \n",
       "                5             3.66  43.63  -5.91       7.63       10.40   134   \n",
       "\n",
       "                          deep_allowed  xpts  xpts_diff  \n",
       "                position                                 \n",
       "Bundesliga 2014 1                   83 73.61      -5.39  \n",
       "                2                  164 59.95      -9.05  \n",
       "                3                  198 57.01      -8.99  \n",
       "                4                  108 55.34      -5.66  \n",
       "                5                  221 45.50      -3.50  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "full_data = dict()\n",
    "for league in leagues:\n",
    "  \n",
    "  season_data = dict()\n",
    "  for season in seasons:    \n",
    "    url = base_url+'/'+league+'/'+season\n",
    "    res = requests.get(url)\n",
    "    soup = BeautifulSoup(res.content, \"lxml\")\n",
    "\n",
    "    # Based on the structure of the webpage, I found that data is in the JSON variable, under <script> tags\n",
    "    scripts = soup.find_all('script')\n",
    "    \n",
    "    string_with_json_obj = ''\n",
    "\n",
    "    # Find data for teams\n",
    "    for el in scripts:\n",
    "        if 'teamsData' in str(el):\n",
    "          string_with_json_obj = str(el).strip()\n",
    "\n",
    "    # print(string_with_json_obj)\n",
    "\n",
    "    # strip unnecessary symbols and get only JSON data\n",
    "    ind_start = string_with_json_obj.index(\"('\")+2\n",
    "    ind_end = string_with_json_obj.index(\"')\")\n",
    "    json_data = string_with_json_obj[ind_start:ind_end]\n",
    "    json_data = json_data.encode('utf8').decode('unicode_escape')\n",
    "    \n",
    "    \n",
    "    # convert JSON data into Python dictionary\n",
    "    data = json.loads(json_data)\n",
    "    \n",
    "    # Get teams and their relevant ids and put them into separate dictionary\n",
    "    teams = {}\n",
    "    for id in data.keys():\n",
    "      teams[id] = data[id]['title']\n",
    "      \n",
    "    # EDA to get a feeling of how the JSON is structured\n",
    "    # Column names are all the same, so we just use first element\n",
    "    columns = []\n",
    "    # Check the sample of values per each column\n",
    "    values = []\n",
    "    for id in data.keys():\n",
    "      columns = list(data[id]['history'][0].keys())\n",
    "      values = list(data[id]['history'][0].values())\n",
    "      break\n",
    "      \n",
    "    # Getting data for all teams\n",
    "    dataframes = {}\n",
    "    for id, team in teams.items():\n",
    "      teams_data = []\n",
    "      for row in data[id]['history']:\n",
    "        teams_data.append(list(row.values()))\n",
    "\n",
    "      df = pd.DataFrame(teams_data, columns=columns)\n",
    "      dataframes[team] = df\n",
    "      # print('Added data for {}.'.format(team))\n",
    "      \n",
    "    \n",
    "    for team, df in dataframes.items():\n",
    "      dataframes[team]['ppda_coef'] = dataframes[team]['ppda'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)\n",
    "      dataframes[team]['oppda_coef'] = dataframes[team]['ppda_allowed'].apply(lambda x: x['att']/x['def'] if x['def'] != 0 else 0)\n",
    "      \n",
    "    cols_to_sum = ['xG', 'xGA', 'npxG', 'npxGA', 'deep', 'deep_allowed', 'scored', 'missed', 'xpts', 'wins', 'draws', 'loses', 'pts', 'npxGD']\n",
    "    cols_to_mean = ['ppda_coef', 'oppda_coef']\n",
    "    \n",
    "    frames = []\n",
    "    for team, df in dataframes.items():\n",
    "      sum_data = pd.DataFrame(df[cols_to_sum].sum()).transpose()\n",
    "      mean_data = pd.DataFrame(df[cols_to_mean].mean()).transpose()\n",
    "      final_df = sum_data.join(mean_data)\n",
    "      final_df['team'] = team\n",
    "      final_df['matches'] = len(df)\n",
    "      frames.append(final_df)\n",
    "\n",
    "    full_stat = pd.concat(frames)\n",
    "    \n",
    "    full_stat = full_stat[['team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'npxG', 'xGA', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts']]\n",
    "    full_stat.sort_values('pts', ascending=False, inplace=True)\n",
    "    full_stat.reset_index(inplace=True, drop=True)\n",
    "    full_stat['position'] = range(1,len(full_stat)+1)\n",
    "    \n",
    "    full_stat['xG_diff'] = full_stat['xG'] - full_stat['scored']\n",
    "    full_stat['xGA_diff'] = full_stat['xGA'] - full_stat['missed']\n",
    "    full_stat['xpts_diff'] = full_stat['xpts'] - full_stat['pts']\n",
    "    \n",
    "    cols_to_int = ['wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'deep', 'deep_allowed']\n",
    "    full_stat[cols_to_int] = full_stat[cols_to_int].astype(int)\n",
    "    \n",
    "    col_order = ['position', 'team', 'matches', 'wins', 'draws', 'loses', 'scored', 'missed', 'pts', 'xG', 'xG_diff', 'npxG', 'xGA', 'xGA_diff', 'npxGA', 'npxGD', 'ppda_coef', 'oppda_coef', 'deep', 'deep_allowed', 'xpts', 'xpts_diff']\n",
    "    full_stat = full_stat[col_order]\n",
    "    full_stat = full_stat.set_index('position')\n",
    "    # print(full_stat.head(20))\n",
    "    \n",
    "    season_data[season] = full_stat\n",
    "  \n",
    "  df_season = pd.concat(season_data)\n",
    "  full_data[league] = df_season\n",
    "  \n",
    "data = pd.concat(full_data)\n",
    "data.head()\n",
    "  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "mKNgtcN6q8qg"
   },
   "source": [
    "## Exporting data to CSV file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "nFJ16xAwHwUD"
   },
   "outputs": [],
   "source": [
    "data.to_csv('understat.com.csv')"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "web-scrapping-understatcom-for-xG-dataset.ipynb",
   "provenance": [],
   "toc_visible": true,
   "version": "0.3.2"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}