notebooks/cleaning/combining_fbref_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combining all 140 FBREF Dataframes into 1 Final FBREF Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:85% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:85% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from functools import reduce\n",
    "import tools.helpers as th"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fbref():\n",
    "\n",
    "    #Importing English Premier League data from the 20/21 season. The column names were modified by me in MS Excel before importing them here.\n",
    "    england_std21 = pd.read_excel('data/league_data/england/21/england_std.xlsx')\n",
    "    england_shooting21 = pd.read_excel('data/league_data/england/21/england_shooting.xlsx')\n",
    "    england_shot_creation21 = pd.read_excel('data/league_data/england/21/england_shot_creation.xlsx')\n",
    "    england_possession21 = pd.read_excel('data/league_data/england/21/england_possession.xlsx')\n",
    "    england_passing21 = pd.read_excel('data/league_data/england/21/england_passing.xlsx')\n",
    "    england_defend21 = pd.read_excel('data/league_data/england/21/england_defending.xlsx')\n",
    "    england_misc21 = pd.read_excel('data/league_data/england/21/england_misc.xlsx')\n",
    "\n",
    "\n",
    "\n",
    "    # The column names were only edited for the files imported above. Therefore their column names will be used as a template to change the remaining columns\n",
    "    std_cols = england_std21.columns\n",
    "    shooting_cols = england_shooting21.columns\n",
    "    shot_creation_cols = england_shot_creation21.columns\n",
    "    possession_cols = england_possession21.columns\n",
    "    passing_cols = england_passing21.columns\n",
    "    defending_cols = england_defend21.columns\n",
    "    misc_cols = england_misc21.columns\n",
    "\n",
    "\n",
    "    #Importing English Premier League data from the 19/20 season.\n",
    "    england_std20 = pd.read_excel('data/league_data/england/20/england_std.xlsx')\n",
    "    england_std20.columns = std_cols\n",
    "\n",
    "    england_shooting20 = pd.read_excel('data/league_data/england/20/england_shooting.xlsx')\n",
    "    england_shooting20.columns = shooting_cols\n",
    "\n",
    "\n",
    "    england_shot_creation20 = pd.read_excel('data/league_data/england/20/england_shot_creation.xlsx')\n",
    "    england_shot_creation20.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    england_possession20 = pd.read_excel('data/league_data/england/20/england_possession.xlsx')\n",
    "    england_possession20.columns = possession_cols\n",
    "\n",
    "\n",
    "    england_passing20 = pd.read_excel('data/league_data/england/20/england_passing.xlsx')\n",
    "    england_passing20.columns = passing_cols\n",
    "\n",
    "\n",
    "    england_defend20 = pd.read_excel('data/league_data/england/20/england_defending.xlsx')\n",
    "    england_defend20.columns = defending_cols\n",
    "\n",
    "\n",
    "    england_misc20 = pd.read_excel('data/league_data/england/20/england_misc.xlsx')\n",
    "    england_misc20.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    #Importing English Premier League data from the 18/19 season.\n",
    "    england_std19 = pd.read_excel('data/league_data/england/19/england_std.xlsx')\n",
    "    england_std19.columns = std_cols\n",
    "\n",
    "    england_shooting19 = pd.read_excel('data/league_data/england/19/england_shooting.xlsx')\n",
    "    england_shooting19.columns = shooting_cols\n",
    "\n",
    "\n",
    "    england_shot_creation19 = pd.read_excel('data/league_data/england/19/england_shot_creation.xlsx')\n",
    "    england_shot_creation19.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    england_possession19 = pd.read_excel('data/league_data/england/19/england_possession.xlsx')\n",
    "    england_possession19.columns = possession_cols\n",
    "\n",
    "\n",
    "    england_passing19 = pd.read_excel('data/league_data/england/19/england_passing.xlsx')\n",
    "    england_passing19.columns = passing_cols\n",
    "\n",
    "\n",
    "    england_defend19 = pd.read_excel('data/league_data/england/19/england_defending.xlsx')\n",
    "    england_defend19.columns = defending_cols\n",
    "\n",
    "\n",
    "    england_misc19 = pd.read_excel('data/league_data/england/19/england_misc.xlsx')\n",
    "    england_misc19.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    #Importing English Premier League data from the 17/18 season.\n",
    "    england_std18 = pd.read_excel('data/league_data/england/18/england_std.xlsx')\n",
    "    england_std18.columns = std_cols\n",
    "\n",
    "    england_shooting18 = pd.read_excel('data/league_data/england/18/england_shooting.xlsx')\n",
    "    england_shooting18.columns = shooting_cols\n",
    "\n",
    "\n",
    "    england_shot_creation18 = pd.read_excel('data/league_data/england/18/england_shot_creation.xlsx')\n",
    "    england_shot_creation18.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    england_possession18 = pd.read_excel('data/league_data/england/18/england_possession.xlsx')\n",
    "    england_possession18.columns = possession_cols\n",
    "\n",
    "\n",
    "    england_passing18 = pd.read_excel('data/league_data/england/18/england_passing.xlsx')\n",
    "    england_passing18.columns = passing_cols\n",
    "\n",
    "\n",
    "    england_defend18 = pd.read_excel('data/league_data/england/18/england_defending.xlsx')\n",
    "    england_defend18.columns = defending_cols\n",
    "\n",
    "\n",
    "    england_misc18 = pd.read_excel('data/league_data/england/18/england_misc.xlsx')\n",
    "    england_misc18.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    #Importing French Ligue 1 data from the 20/21 season.\n",
    "    france_std21 = pd.read_excel('data/league_data/france/21/france_std.xlsx')\n",
    "    france_std21.columns = std_cols\n",
    "\n",
    "    france_shooting21 = pd.read_excel('data/league_data/france/21/france_shooting.xlsx')\n",
    "    france_shooting21.columns = shooting_cols\n",
    "\n",
    "\n",
    "    france_shot_creation21 = pd.read_excel('data/league_data/france/21/france_shot_creation.xlsx')\n",
    "    france_shot_creation21.columns = shot_creation_cols\n",
    "\n",
    "    france_possession21 = pd.read_excel('data/league_data/france/21/france_possession.xlsx')\n",
    "    france_possession21.columns = possession_cols\n",
    "\n",
    "\n",
    "    france_passing21 = pd.read_excel('data/league_data/france/21/france_passing.xlsx')\n",
    "    france_passing21.columns = passing_cols\n",
    "\n",
    "\n",
    "    france_defend21 = pd.read_excel('data/league_data/france/21/france_defending.xlsx')\n",
    "    france_defend21.columns = defending_cols\n",
    "\n",
    "\n",
    "    france_misc21 = pd.read_excel('data/league_data/france/21/france_misc.xlsx')\n",
    "    france_misc21.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    #Importing French Ligue 1 data from the 19/20 season.\n",
    "    france_std20 = pd.read_excel('data/league_data/france/20/france_std.xlsx')\n",
    "    france_std20.columns = std_cols\n",
    "\n",
    "    france_shooting20 = pd.read_excel('data/league_data/france/20/france_shooting.xlsx')\n",
    "    france_shooting20.columns = shooting_cols\n",
    "\n",
    "\n",
    "    france_shot_creation20 = pd.read_excel('data/league_data/france/20/france_shot_creation.xlsx')\n",
    "    france_shot_creation20.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    france_possession20 = pd.read_excel('data/league_data/france/20/france_possession.xlsx')\n",
    "    france_possession20.columns = possession_cols\n",
    "\n",
    "\n",
    "    france_passing20 = pd.read_excel('data/league_data/france/20/france_passing.xlsx')\n",
    "    france_passing20.columns = passing_cols\n",
    "\n",
    "\n",
    "    france_defend20 = pd.read_excel('data/league_data/france/20/france_defending.xlsx')\n",
    "    france_defend20.columns = defending_cols\n",
    "\n",
    "\n",
    "    france_misc20 = pd.read_excel('data/league_data/france/20/france_misc.xlsx')\n",
    "    france_misc20.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "    #Importing French Ligue 1 data from the 18/19 season.\n",
    "    france_std19 = pd.read_excel('data/league_data/france/19/france_std.xlsx')\n",
    "    france_std19.columns = std_cols\n",
    "\n",
    "    france_shooting19 = pd.read_excel('data/league_data/france/19/france_shooting.xlsx')\n",
    "    france_shooting19.columns = shooting_cols\n",
    "\n",
    "\n",
    "    france_shot_creation19 = pd.read_excel('data/league_data/france/19/france_shot_creation.xlsx')\n",
    "    france_shot_creation19.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    france_possession19 = pd.read_excel('data/league_data/france/19/france_possession.xlsx')\n",
    "    france_possession19.columns = possession_cols\n",
    "\n",
    "\n",
    "    france_passing19 = pd.read_excel('data/league_data/france/19/france_passing.xlsx')\n",
    "    france_passing19.columns = passing_cols\n",
    "\n",
    "\n",
    "    france_defend19 = pd.read_excel('data/league_data/france/19/france_defending.xlsx')\n",
    "    france_defend19.columns = defending_cols\n",
    "\n",
    "\n",
    "    france_misc19 = pd.read_excel('data/league_data/france/19/france_misc.xlsx')\n",
    "    france_misc19.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    #Importing French Ligue 1 data from the 17/18 season.\n",
    "    france_std18 = pd.read_excel('data/league_data/france/18/france_std.xlsx')\n",
    "    france_std18.columns = std_cols\n",
    "\n",
    "    france_shooting18 = pd.read_excel('data/league_data/france/18/france_shooting.xlsx')\n",
    "    france_shooting18.columns = shooting_cols\n",
    "\n",
    "\n",
    "    france_shot_creation18 = pd.read_excel('data/league_data/france/18/france_shot_creation.xlsx')\n",
    "    france_shot_creation18.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    france_possession18 = pd.read_excel('data/league_data/france/18/france_possession.xlsx')\n",
    "    france_possession18.columns = possession_cols\n",
    "\n",
    "\n",
    "    france_passing18 = pd.read_excel('data/league_data/france/18/france_passing.xlsx')\n",
    "    france_passing18.columns = passing_cols\n",
    "\n",
    "\n",
    "    france_defend18 = pd.read_excel('data/league_data/france/18/france_defending.xlsx')\n",
    "    france_defend18.columns = defending_cols\n",
    "\n",
    "\n",
    "    france_misc18 = pd.read_excel('data/league_data/france/18/france_misc.xlsx')\n",
    "    france_misc18.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "    #Importing German Bundesliga data from the 20/21 season.\n",
    "    germany_std21 = pd.read_excel('data/league_data/germany/21/germany_std.xlsx')\n",
    "    germany_std21.columns = std_cols\n",
    "\n",
    "    germany_shooting21 = pd.read_excel('data/league_data/germany/21/germany_shooting.xlsx')\n",
    "    germany_shooting21.columns = shooting_cols\n",
    "\n",
    "\n",
    "    germany_shot_creation21 = pd.read_excel('data/league_data/germany/21/germany_shot_creation.xlsx')\n",
    "    germany_shot_creation21.columns = shot_creation_cols\n",
    "\n",
    "    germany_possession21 = pd.read_excel('data/league_data/germany/21/germany_possession.xlsx')\n",
    "    germany_possession21.columns = possession_cols\n",
    "\n",
    "\n",
    "    germany_passing21 = pd.read_excel('data/league_data/germany/21/germany_passing.xlsx')\n",
    "    germany_passing21.columns = passing_cols\n",
    "\n",
    "\n",
    "    germany_defend21 = pd.read_excel('data/league_data/germany/21/germany_defending.xlsx')\n",
    "    germany_defend21.columns = defending_cols\n",
    "\n",
    "\n",
    "    germany_misc21 = pd.read_excel('data/league_data/germany/21/germany_misc.xlsx')\n",
    "    germany_misc21.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    #Importing German Bundesliga data from the 19/20 season.\n",
    "    germany_std20 = pd.read_excel('data/league_data/germany/20/germany_std.xlsx')\n",
    "    germany_std20.columns = std_cols\n",
    "\n",
    "    germany_shooting20 = pd.read_excel('data/league_data/germany/20/germany_shooting.xlsx')\n",
    "    germany_shooting20.columns = shooting_cols\n",
    "\n",
    "\n",
    "    germany_shot_creation20 = pd.read_excel('data/league_data/germany/20/germany_shot_creation.xlsx')\n",
    "    germany_shot_creation20.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    germany_possession20 = pd.read_excel('data/league_data/germany/20/germany_possession.xlsx')\n",
    "    germany_possession20.columns = possession_cols\n",
    "\n",
    "\n",
    "    germany_passing20 = pd.read_excel('data/league_data/germany/20/germany_passing.xlsx')\n",
    "    germany_passing20.columns = passing_cols\n",
    "\n",
    "\n",
    "    germany_defend20 = pd.read_excel('data/league_data/germany/20/germany_defending.xlsx')\n",
    "    germany_defend20.columns = defending_cols\n",
    "\n",
    "\n",
    "    germany_misc20 = pd.read_excel('data/league_data/germany/20/germany_misc.xlsx')\n",
    "    germany_misc20.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    #Importing German Bundesliga data from the 18/19 season.\n",
    "    germany_std19 = pd.read_excel('data/league_data/germany/19/germany_std.xlsx')\n",
    "    germany_std19.columns = std_cols\n",
    "\n",
    "    germany_shooting19 = pd.read_excel('data/league_data/germany/19/germany_shooting.xlsx')\n",
    "    germany_shooting19.columns = shooting_cols\n",
    "\n",
    "\n",
    "    germany_shot_creation19 = pd.read_excel('data/league_data/germany/19/germany_shot_creation.xlsx')\n",
    "    germany_shot_creation19.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    germany_possession19 = pd.read_excel('data/league_data/germany/19/germany_possession.xlsx')\n",
    "    germany_possession19.columns = possession_cols\n",
    "\n",
    "\n",
    "    germany_passing19 = pd.read_excel('data/league_data/germany/19/germany_passing.xlsx')\n",
    "    germany_passing19.columns = passing_cols\n",
    "\n",
    "\n",
    "    germany_defend19 = pd.read_excel('data/league_data/germany/19/germany_defending.xlsx')\n",
    "    germany_defend19.columns = defending_cols\n",
    "\n",
    "\n",
    "    germany_misc19 = pd.read_excel('data/league_data/germany/19/germany_misc.xlsx')\n",
    "    germany_misc19.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "    #Importing German Bundesliga data from the 17/18 season.\n",
    "    germany_std18 = pd.read_excel('data/league_data/germany/18/germany_std.xlsx')\n",
    "    germany_std18.columns = std_cols\n",
    "\n",
    "    germany_shooting18 = pd.read_excel('data/league_data/germany/18/germany_shooting.xlsx')\n",
    "    germany_shooting18.columns = shooting_cols\n",
    "\n",
    "\n",
    "    germany_shot_creation18 = pd.read_excel('data/league_data/germany/18/germany_shot_creation.xlsx')\n",
    "    germany_shot_creation18.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    germany_possession18 = pd.read_excel('data/league_data/germany/18/germany_possession.xlsx')\n",
    "    germany_possession18.columns = possession_cols\n",
    "\n",
    "\n",
    "    germany_passing18 = pd.read_excel('data/league_data/germany/18/germany_passing.xlsx')\n",
    "    germany_passing18.columns = passing_cols\n",
    "\n",
    "\n",
    "    germany_defend18 = pd.read_excel('data/league_data/germany/18/germany_defending.xlsx')\n",
    "    germany_defend18.columns = defending_cols\n",
    "\n",
    "\n",
    "    germany_misc18 = pd.read_excel('data/league_data/germany/18/germany_misc.xlsx')\n",
    "    germany_misc18.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "    #Importing Italian Serie A data from the 20/21 season.\n",
    "    italy_std21 = pd.read_excel('data/league_data/italy/21/italy_std.xlsx')\n",
    "    italy_std21.columns = std_cols\n",
    "\n",
    "    italy_shooting21 = pd.read_excel('data/league_data/italy/21/italy_shooting.xlsx')\n",
    "    italy_shooting21.columns = shooting_cols\n",
    "\n",
    "\n",
    "    italy_shot_creation21 = pd.read_excel('data/league_data/italy/21/italy_shot_creation.xlsx')\n",
    "    italy_shot_creation21.columns = shot_creation_cols\n",
    "\n",
    "    italy_possession21 = pd.read_excel('data/league_data/italy/21/italy_possession.xlsx')\n",
    "    italy_possession21.columns = possession_cols\n",
    "\n",
    "\n",
    "    italy_passing21 = pd.read_excel('data/league_data/italy/21/italy_passing.xlsx')\n",
    "    italy_passing21.columns = passing_cols\n",
    "\n",
    "\n",
    "    italy_defend21 = pd.read_excel('data/league_data/italy/21/italy_defending.xlsx')\n",
    "    italy_defend21.columns = defending_cols\n",
    "\n",
    "\n",
    "    italy_misc21 = pd.read_excel('data/league_data/italy/21/italy_misc.xlsx')\n",
    "    italy_misc21.columns = misc_cols\n",
    "\n",
    "\n",
    "    #Importing Italian Serie A data from the 19/20 season.\n",
    "    italy_std20 = pd.read_excel('data/league_data/italy/20/italy_std.xlsx')\n",
    "    italy_std20.columns = std_cols\n",
    "\n",
    "    italy_shooting20 = pd.read_excel('data/league_data/italy/20/italy_shooting.xlsx')\n",
    "    italy_shooting20.columns = shooting_cols\n",
    "\n",
    "\n",
    "    italy_shot_creation20 = pd.read_excel('data/league_data/italy/20/italy_shot_creation.xlsx')\n",
    "    italy_shot_creation20.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    italy_possession20 = pd.read_excel('data/league_data/italy/20/italy_possession.xlsx')\n",
    "    italy_possession20.columns = possession_cols\n",
    "\n",
    "\n",
    "    italy_passing20 = pd.read_excel('data/league_data/italy/20/italy_passing.xlsx')\n",
    "    italy_passing20.columns = passing_cols\n",
    "\n",
    "\n",
    "    italy_defend20 = pd.read_excel('data/league_data/italy/20/italy_defending.xlsx')\n",
    "    italy_defend20.columns = defending_cols\n",
    "\n",
    "\n",
    "    italy_misc20 = pd.read_excel('data/league_data/italy/20/italy_misc.xlsx')\n",
    "    italy_misc20.columns = misc_cols\n",
    "\n",
    "\n",
    "    #Importing Italian Serie A data from the 18/19 season.\n",
    "    italy_std19 = pd.read_excel('data/league_data/italy/19/italy_std.xlsx')\n",
    "    italy_std19.columns = std_cols\n",
    "\n",
    "    italy_shooting19 = pd.read_excel('data/league_data/italy/19/italy_shooting.xlsx')\n",
    "    italy_shooting19.columns = shooting_cols\n",
    "\n",
    "\n",
    "    italy_shot_creation19 = pd.read_excel('data/league_data/italy/19/italy_shot_creation.xlsx')\n",
    "    italy_shot_creation19.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    italy_possession19 = pd.read_excel('data/league_data/italy/19/italy_possession.xlsx')\n",
    "    italy_possession19.columns = possession_cols\n",
    "\n",
    "\n",
    "    italy_passing19 = pd.read_excel('data/league_data/italy/19/italy_passing.xlsx')\n",
    "    italy_passing19.columns = passing_cols\n",
    "\n",
    "\n",
    "    italy_defend19 = pd.read_excel('data/league_data/italy/19/italy_defending.xlsx')\n",
    "    italy_defend19.columns = defending_cols\n",
    "\n",
    "\n",
    "    italy_misc19 = pd.read_excel('data/league_data/italy/19/italy_misc.xlsx')\n",
    "    italy_misc19.columns = misc_cols\n",
    "\n",
    "\n",
    "    #Importing Italian Serie A data from the 17/18 season.\n",
    "    italy_std18 = pd.read_excel('data/league_data/italy/18/italy_std.xlsx')\n",
    "    italy_std18.columns = std_cols\n",
    "\n",
    "    italy_shooting18 = pd.read_excel('data/league_data/italy/18/italy_shooting.xlsx')\n",
    "    italy_shooting18.columns = shooting_cols\n",
    "\n",
    "\n",
    "    italy_shot_creation18 = pd.read_excel('data/league_data/italy/18/italy_shot_creation.xlsx')\n",
    "    italy_shot_creation18.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    italy_possession18 = pd.read_excel('data/league_data/italy/18/italy_possession.xlsx')\n",
    "    italy_possession18.columns = possession_cols\n",
    "\n",
    "\n",
    "    italy_passing18 = pd.read_excel('data/league_data/italy/18/italy_passing.xlsx')\n",
    "    italy_passing18.columns = passing_cols\n",
    "\n",
    "\n",
    "    italy_defend18 = pd.read_excel('data/league_data/italy/18/italy_defending.xlsx')\n",
    "    italy_defend18.columns = defending_cols\n",
    "\n",
    "\n",
    "    italy_misc18 = pd.read_excel('data/league_data/italy/18/italy_misc.xlsx')\n",
    "    italy_misc18.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "    #Importing Spanish La Liga data from the 20/21 season.\n",
    "    spain_std21 = pd.read_excel('data/league_data/spain/21/spain_std.xlsx')\n",
    "    spain_std21.columns = std_cols\n",
    "\n",
    "    spain_shooting21 = pd.read_excel('data/league_data/spain/21/spain_shooting.xlsx')\n",
    "    spain_shooting21.columns = shooting_cols\n",
    "\n",
    "\n",
    "    spain_shot_creation21 = pd.read_excel('data/league_data/spain/21/spain_shot_creation.xlsx')\n",
    "    spain_shot_creation21.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    spain_possession21 = pd.read_excel('data/league_data/spain/21/spain_possession.xlsx')\n",
    "    spain_possession21.columns = possession_cols\n",
    "\n",
    "\n",
    "    spain_passing21 = pd.read_excel('data/league_data/spain/21/spain_passing.xlsx')\n",
    "    spain_passing21.columns = passing_cols\n",
    "\n",
    "\n",
    "    spain_defend21 = pd.read_excel('data/league_data/spain/21/spain_defending.xlsx')\n",
    "    spain_defend21.columns = defending_cols\n",
    "\n",
    "\n",
    "    spain_misc21 = pd.read_excel('data/league_data/spain/21/spain_misc.xlsx')\n",
    "    spain_misc21.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "    #Importing Spanish La Liga data from the 19/20 season.\n",
    "    spain_std20 = pd.read_excel('data/league_data/spain/20/spain_std.xlsx')\n",
    "    spain_std20.columns = std_cols\n",
    "\n",
    "    spain_shooting20 = pd.read_excel('data/league_data/spain/20/spain_shooting.xlsx')\n",
    "    spain_shooting20.columns = shooting_cols\n",
    "\n",
    "\n",
    "    spain_shot_creation20 = pd.read_excel('data/league_data/spain/20/spain_shot_creation.xlsx')\n",
    "    spain_shot_creation20.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    spain_possession20 = pd.read_excel('data/league_data/spain/20/spain_possession.xlsx')\n",
    "    spain_possession20.columns = possession_cols\n",
    "\n",
    "\n",
    "    spain_passing20 = pd.read_excel('data/league_data/spain/20/spain_passing.xlsx')\n",
    "    spain_passing20.columns = passing_cols\n",
    "\n",
    "\n",
    "    spain_defend20 = pd.read_excel('data/league_data/spain/20/spain_defending.xlsx')\n",
    "    spain_defend20.columns = defending_cols\n",
    "\n",
    "\n",
    "    spain_misc20 = pd.read_excel('data/league_data/spain/20/spain_misc.xlsx')\n",
    "    spain_misc20.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "    #Importing Spanish La Liga data from the 18/19 season.\n",
    "    spain_std19 = pd.read_excel('data/league_data/spain/19/spain_std.xlsx')\n",
    "    spain_std19.columns = std_cols\n",
    "\n",
    "    spain_shooting19 = pd.read_excel('data/league_data/spain/19/spain_shooting.xlsx')\n",
    "    spain_shooting19.columns = shooting_cols\n",
    "\n",
    "\n",
    "    spain_shot_creation19 = pd.read_excel('data/league_data/spain/19/spain_shot_creation.xlsx')\n",
    "    spain_shot_creation19.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    spain_possession19 = pd.read_excel('data/league_data/spain/19/spain_possession.xlsx')\n",
    "    spain_possession19.columns = possession_cols\n",
    "\n",
    "    spain_passing19 = pd.read_excel('data/league_data/spain/19/spain_passing.xlsx')\n",
    "    spain_passing19.columns = passing_cols\n",
    "\n",
    "\n",
    "    spain_defend19 = pd.read_excel('data/league_data/spain/19/spain_defending.xlsx')\n",
    "    spain_defend19.columns = defending_cols\n",
    "\n",
    "\n",
    "    spain_misc19 = pd.read_excel('data/league_data/spain/19/spain_misc.xlsx')\n",
    "    spain_misc19.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "    #Importing Spanish La Liga data from the 17/18 season.\n",
    "    spain_std18 = pd.read_excel('data/league_data/spain/18/spain_std.xlsx')\n",
    "    spain_std18.columns = std_cols\n",
    "\n",
    "    spain_shooting18 = pd.read_excel('data/league_data/spain/18/spain_shooting.xlsx')\n",
    "    spain_shooting18.columns = shooting_cols\n",
    "\n",
    "\n",
    "    spain_shot_creation18 = pd.read_excel('data/league_data/spain/18/spain_shot_creation.xlsx')\n",
    "    spain_shot_creation18.columns = shot_creation_cols\n",
    "\n",
    "\n",
    "    spain_possession18 = pd.read_excel('data/league_data/spain/18/spain_possession.xlsx')\n",
    "    spain_possession18.columns = possession_cols\n",
    "\n",
    "\n",
    "    spain_passing18 = pd.read_excel('data/league_data/spain/18/spain_passing.xlsx')\n",
    "    spain_passing18.columns = passing_cols\n",
    "\n",
    "\n",
    "    spain_defend18 = pd.read_excel('data/league_data/spain/18/spain_defending.xlsx')\n",
    "    spain_defend18.columns = defending_cols\n",
    "\n",
    "\n",
    "    spain_misc18 = pd.read_excel('data/league_data/spain/18/spain_misc.xlsx')\n",
    "    spain_misc18.columns = misc_cols\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    # Grouping data by category for each season\n",
    "    s21_std = pd.concat([england_std21,france_std21,germany_std21,italy_std21,spain_std21])\n",
    "    s20_std = pd.concat([england_std20,france_std20,germany_std20,italy_std20,spain_std20])\n",
    "    s19_std = pd.concat([england_std19,france_std19,germany_std19,italy_std19,spain_std19])\n",
    "    s18_std = pd.concat([england_std18,france_std18,germany_std18,italy_std18,spain_std18])\n",
    "\n",
    "\n",
    "    s21_shooting = pd.concat([england_shooting21,france_shooting21,germany_shooting21,italy_shooting21,spain_shooting21])\n",
    "    s20_shooting = pd.concat([england_shooting20,france_shooting20,germany_shooting20,italy_shooting20,spain_shooting20])\n",
    "    s19_shooting = pd.concat([england_shooting19,france_shooting19,germany_shooting19,italy_shooting19,spain_shooting19])\n",
    "    s18_shooting = pd.concat([england_shooting18,france_shooting18,germany_shooting18,italy_shooting18,spain_shooting18])\n",
    "\n",
    "\n",
    "    s21_shot_creation = pd.concat([england_shot_creation21,france_shot_creation21,germany_shot_creation21,italy_shot_creation21,spain_shot_creation21])\n",
    "    s20_shot_creation = pd.concat([england_shot_creation20,france_shot_creation20,germany_shot_creation20,italy_shot_creation20,spain_shot_creation20])\n",
    "    s19_shot_creation = pd.concat([england_shot_creation19,france_shot_creation19,germany_shot_creation19,italy_shot_creation19,spain_shot_creation19])\n",
    "    s18_shot_creation = pd.concat([england_shot_creation18,france_shot_creation18,germany_shot_creation18,italy_shot_creation18,spain_shot_creation18])\n",
    "\n",
    "\n",
    "    s21_possession = pd.concat([england_possession21,france_possession21,germany_possession21,italy_possession21,spain_possession21])\n",
    "    s20_possession = pd.concat([england_possession20,france_possession20,germany_possession20,italy_possession20,spain_possession20])\n",
    "    s19_possession = pd.concat([england_possession19,france_possession19,germany_possession19,italy_possession19,spain_possession19])\n",
    "    s18_possession = pd.concat([england_possession18,france_possession18,germany_possession18,italy_possession18,spain_possession18])\n",
    "\n",
    "\n",
    "    s21_passing = pd.concat([england_passing21,france_passing21,germany_passing21,italy_passing21,spain_passing21])\n",
    "    s20_passing = pd.concat([england_passing20,france_passing20,germany_passing20,italy_passing20,spain_passing20])\n",
    "    s19_passing = pd.concat([england_passing19,france_passing19,germany_passing19,italy_passing19,spain_passing19])\n",
    "    s18_passing = pd.concat([england_passing18,france_passing18,germany_passing18,italy_passing18,spain_passing18])\n",
    "\n",
    "\n",
    "    s21_defending = pd.concat([england_defend21,france_defend21,germany_defend21,italy_defend21,spain_defend21])\n",
    "    s20_defending = pd.concat([england_defend20,france_defend20,germany_defend20,italy_defend20,spain_defend20])\n",
    "    s19_defending = pd.concat([england_defend19,france_defend19,germany_defend19,italy_defend19,spain_defend19])\n",
    "    s18_defending = pd.concat([england_defend18,france_defend18,germany_defend18,italy_defend18,spain_defend18])\n",
    "\n",
    "\n",
    "    s21_misc = pd.concat([england_misc21,france_misc21,germany_misc21,italy_misc21,spain_misc21])\n",
    "    s20_misc = pd.concat([england_misc20,france_misc20,germany_misc20,italy_misc20,spain_misc20])\n",
    "    s19_misc = pd.concat([england_misc19,france_misc19,germany_misc19,italy_misc19,spain_misc19])\n",
    "    s18_misc = pd.concat([england_misc18,france_misc18,germany_misc18,italy_misc18,spain_misc18])\n",
    "\n",
    "\n",
    "    #Combining all data for ALL leagues season by season\n",
    "\n",
    "    #20/21 Season\n",
    "    s21 = [th.clean_std(th.clean_char(s21_std)),\n",
    "           th.clean_shooting(th.clean_char(s21_shooting)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Gls','Penalties Scored','Penalties Attempted','xG','Non-Penalty xG'],axis=1),\n",
    "           th.clean_shot_creation(th.clean_char(s21_shot_creation)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_possession(th.clean_char(s21_possession)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_passing(th.clean_char(s21_passing)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Passes that lead to a Shot'],axis=1),\n",
    "           th.clean_defending(th.clean_char(s21_defending)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_misc(th.clean_char(s21_misc)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Yellow Cards', 'Red Cards','Tackles Won'],axis=1)]\n",
    "\n",
    "    df21 = reduce(lambda left,right: pd.merge(left,right,on='Player'), s21)\n",
    "    df21.columns = [a+' (20/21)' for a in df21.columns]\n",
    "    df21.rename(columns={\"Player (20/21)\": \"Player\"},inplace=True)\n",
    "\n",
    "\n",
    "    #19/20 Season\n",
    "    s20 = [th.clean_std(th.clean_char(s20_std)),\n",
    "           th.clean_shooting(th.clean_char(s20_shooting)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Gls','Penalties Scored','Penalties Attempted','xG','Non-Penalty xG'],axis=1),\n",
    "           th.clean_shot_creation(th.clean_char(s20_shot_creation)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_possession(th.clean_char(s20_possession)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_passing(th.clean_char(s20_passing)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Passes that lead to a Shot'],axis=1),\n",
    "           th.clean_defending(th.clean_char(s20_defending)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_misc(th.clean_char(s20_misc)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Yellow Cards', 'Red Cards','Tackles Won'],axis=1)]\n",
    "\n",
    "    df20 = reduce(lambda left,right: pd.merge(left,right,on='Player'), s20)\n",
    "    df20.columns = [a+' (19/20)' for a in df20.columns]\n",
    "    df20.rename(columns={\"Player (19/20)\": \"Player\"},inplace=True)\n",
    "\n",
    "\n",
    "    #18/19 Season\n",
    "    s19 = [th.clean_std(th.clean_char(s19_std)),\n",
    "           th.clean_shooting(th.clean_char(s19_shooting)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Gls','Penalties Scored','Penalties Attempted','xG','Non-Penalty xG'],axis=1),\n",
    "           th.clean_shot_creation(th.clean_char(s19_shot_creation)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_possession(th.clean_char(s19_possession)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_passing(th.clean_char(s19_passing)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Passes that lead to a Shot'],axis=1),\n",
    "           th.clean_defending(th.clean_char(s19_defending)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_misc(th.clean_char(s19_misc)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Yellow Cards', 'Red Cards','Tackles Won'],axis=1)]\n",
    "\n",
    "    df19 = reduce(lambda left,right: pd.merge(left,right,on='Player'), s19)\n",
    "    df19.columns = [a+' (18/19)' for a in df19.columns]\n",
    "    df19.rename(columns={\"Player (18/19)\": \"Player\"},inplace=True)\n",
    "\n",
    "\n",
    "    #17/18 Season\n",
    "    s18 = [th.clean_std(th.clean_char(s18_std)),\n",
    "           th.clean_shooting(th.clean_char(s18_shooting)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Gls','Penalties Scored','Penalties Attempted','xG','Non-Penalty xG'],axis=1),\n",
    "           th.clean_shot_creation(th.clean_char(s18_shot_creation)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_possession(th.clean_char(s18_possession)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_passing(th.clean_char(s18_passing)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Passes that lead to a Shot'],axis=1),\n",
    "           th.clean_defending(th.clean_char(s18_defending)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s'],axis=1),\n",
    "           th.clean_misc(th.clean_char(s18_misc)).drop(['Rk','Nation', 'Pos', 'Squad', 'Age', 'Born', '90s','Yellow Cards', 'Red Cards','Tackles Won'],axis=1)]\n",
    "\n",
    "    df18 = reduce(lambda left,right: pd.merge(left,right,on='Player'), s18)\n",
    "    df18.columns = [a+' (17/18)' for a in df18.columns]\n",
    "    df18.rename(columns={\"Player (17/18)\": \"Player\"},inplace=True)\n",
    "\n",
    "\n",
    "\n",
    "    #Combining all data into one large final dataframe\n",
    "\n",
    "    #Dataset containing information about the 20/21 and 19/20 seasons.\n",
    "    two_season = pd.merge(df21, df20, how='outer')\n",
    "\n",
    "    #Dataset containing information about the 20/21, 19/20 and 18/19 seasons.\n",
    "    three_season = pd.merge(two_season, df19, how='outer')\n",
    "\n",
    "    #Dataset containing information about the 20/21, 19/20, 18/19 and 17/18 seasons.\n",
    "    four_season = pd.merge(three_season,df18, how='outer')\n",
    "\n",
    "    return four_season"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fbref_combined = fbref()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exporting the combined dataframe into another Excel spreadsheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Install gitpath below if not already available on your environment.\n",
    "#! pip install git+https://github.com/maxnoe/python-gitpath\n",
    "import gitpath\n",
    "\n",
    "#Getting the file path for the data file.\n",
    "file_path = str(gitpath.root()) + 'data/Complete_FBREF.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fbref_combined.to_excel(file_path,index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}