notebooks/scraping/scraping_transfermarkt.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scraping Transfermarkt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Function that scrapes a list of Team Webpage Links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#top_league(n) returns a list of links to the webpages of every club within the top n European leagues.\n",
    "#i.e. Passing 1 as an argument will return links to the no.1 league in Europe- England. Passing 2 as an argument will return links of clubs in the 2 best leagues.\n",
    "def top_league(n):\n",
    "\n",
    "    headers = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.106 Safari/537.36'}\n",
    "\n",
    "    page = \"https://www.transfermarkt.co.uk/wettbewerbe/europa\"\n",
    "    pageTree = requests.get(page, headers=headers)\n",
    "    pageSoup = BeautifulSoup(pageTree.content, 'html.parser')\n",
    "\n",
    "    links = pageSoup.find_all(\"tr\")\n",
    "\n",
    "    site = 'https://www.transfermarkt.co.uk'\n",
    "    s20_string = \"/plus/?saison_id=2020\"\n",
    "\n",
    "    top_leagues = []\n",
    "    for a in list(range(2,((n*2)+1),2)):\n",
    "        top_leagues.append((site+(str(links[a]).split('href=\"',5)[2].split('\"')[0])+s20_string))\n",
    "\n",
    "    teams = []\n",
    "\n",
    "    for a in top_leagues:\n",
    "\n",
    "        page = a\n",
    "        pageTree = requests.get(page, headers=headers)\n",
    "        pageSoup = BeautifulSoup(pageTree.content, 'html.parser')\n",
    "\n",
    "        links2 = pageSoup.find_all(\"a\",{\"class\":\"vereinprofil_tooltip\"})\n",
    "        site = 'https://www.transfermarkt.co.uk'\n",
    "        s21_string = \"/plus/?saison_id=2021\"\n",
    "\n",
    "        if a==\"https://www.transfermarkt.co.uk/bundesliga/startseite/wettbewerb/L1/plus/?saison_id=2020\" or a==\"https://www.transfermarkt.co.uk/liga-portugal-bwin/startseite/wettbewerb/PO1/plus/?saison_id=2020\" or a==\"'https://www.transfermarkt.co.uk/eredivisie/startseite/wettbewerb/NL1/plus/?saison_id=2020'\":\n",
    "            for b in list(range(0,(18*3),3)):\n",
    "                teams.append((site+(str(links2[b]).split('href=\"')[1].split('\">')[0])).split(\"/saison_id\")[0]+s21_string)\n",
    "        elif a== 'https://www.transfermarkt.co.uk/premier-liga/startseite/wettbewerb/RU1/plus/?saison_id=2020':\n",
    "            for b in list(range(0,(16*3),3)):\n",
    "                teams.append((site+(str(links2[b]).split('href=\"')[1].split('\">')[0])).split(\"/saison_id\")[0]+s21_string) \n",
    "        else:\n",
    "            for b in list(range(0,(20*3),3)):\n",
    "                teams.append((site+(str(links2[b]).split('href=\"')[1].split('\">')[0])).split(\"/saison_id\")[0]+s21_string)\n",
    "    return teams"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Function that scrapes a list of Individual Player Webpage Links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#top_league_player(n) returns a list of links to the individual profile webpage for every player participating in Europe's top n number of leagues.\n",
    "def top_league_player(n):\n",
    "\n",
    "    headers = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.106 Safari/537.36'}\n",
    "\n",
    "    page = \"https://www.transfermarkt.co.uk/wettbewerbe/europa\"\n",
    "    pageTree = requests.get(page, headers=headers)\n",
    "    pageSoup = BeautifulSoup(pageTree.content, 'html.parser')\n",
    "\n",
    "    links = pageSoup.find_all(\"tr\")\n",
    "\n",
    "    site = 'https://www.transfermarkt.co.uk'\n",
    "    s20_string = \"/plus/?saison_id=2020\"\n",
    "\n",
    "    top_leagues = []\n",
    "    for a in list(range(2,((n*2)+1),2)):\n",
    "        top_leagues.append((site+(str(links[a]).split('href=\"',5)[2].split('\"')[0])+s20_string))\n",
    "\n",
    "    teams = []\n",
    "\n",
    "    for a in top_leagues:\n",
    "\n",
    "        page = a\n",
    "        pageTree = requests.get(page, headers=headers)\n",
    "        pageSoup = BeautifulSoup(pageTree.content, 'html.parser')\n",
    "\n",
    "        links2 = pageSoup.find_all(\"a\",{\"class\":\"vereinprofil_tooltip\"})\n",
    "        site = 'https://www.transfermarkt.co.uk'\n",
    "        s21_string = \"/plus/?saison_id=2021\"\n",
    "\n",
    "        if a==\"https://www.transfermarkt.co.uk/bundesliga/startseite/wettbewerb/L1/plus/?saison_id=2020\" or a==\"https://www.transfermarkt.co.uk/liga-portugal-bwin/startseite/wettbewerb/PO1/plus/?saison_id=2020\" or a==\"'https://www.transfermarkt.co.uk/eredivisie/startseite/wettbewerb/NL1/plus/?saison_id=2020'\":\n",
    "            for b in list(range(0,(18*3),3)):\n",
    "                teams.append((site+(str(links2[b]).split('href=\"')[1].split('\">')[0])).split(\"/saison_id\")[0]+s21_string)\n",
    "        elif a== 'https://www.transfermarkt.co.uk/premier-liga/startseite/wettbewerb/RU1/plus/?saison_id=2020':\n",
    "            for b in list(range(0,(16*3),3)):\n",
    "                teams.append((site+(str(links2[b]).split('href=\"')[1].split('\">')[0])).split(\"/saison_id\")[0]+s21_string) \n",
    "        else:\n",
    "            for b in list(range(0,(20*3),3)):\n",
    "                teams.append((site+(str(links2[b]).split('href=\"')[1].split('\">')[0])).split(\"/saison_id\")[0]+s21_string)\n",
    "    \n",
    "    player_links = []\n",
    "    for a in teams:\n",
    "        page = a\n",
    "        pageTree = requests.get(page, headers=headers)\n",
    "        pageSoup = BeautifulSoup(pageTree.content, 'html.parser')\n",
    "\n",
    "        site = 'https://www.transfermarkt.co.uk'\n",
    "\n",
    "        links = pageSoup.find_all(\"a\",{\"class\":\"spielprofil_tooltip\"})\n",
    "\n",
    "        for b in list(range(0,len(links),2)):\n",
    "            player_links.append(site+(str(links[b]).split('href=\"')[1].split('\"')[0]))\n",
    "    \n",
    "    return player_links\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Scraping Player Contract Length Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Contract dataframe: contract scrape from individual player pages. #Warning: IT MAY TAKE HOURS TO RUN but it WORKS!!!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#iterating through every link in the list provided to it and then returns a dataframe of player data from the given webpages.\n",
    "#top_league_player(5) returns a list of links to the individual profile webpage for every player participating in Europe's top 5 leagues.\n",
    "\n",
    "site = 'https://www.transfermarkt.co.uk'\n",
    "PlayerList = []\n",
    "ContractList = []\n",
    "\n",
    "headers = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.106 Safari/537.36'}\n",
    "\n",
    "for a in top_league_player(5):\n",
    "\n",
    "    page = a\n",
    "    pageTree = requests.get(page, headers=headers)\n",
    "    pageSoup = BeautifulSoup(pageTree.content, 'html.parser')\n",
    "\n",
    "    Player = pageSoup.find_all(\"meta\",{\"property\":\"og:title\"})\n",
    "    Contract = pageSoup.find_all(\"span\",{\"class\":\"dataValue\"})\n",
    "\n",
    "    PlayerList.append(str(Player).split('content=\"')[1].split(\" - \")[0])\n",
    "    try:\n",
    "        ContractList.append((int(str(Contract[-1]).split(\", \")[1].split(\"</span>\")[0])-2021))\n",
    "    except IndexError:\n",
    "        ContractList.append(\"fail\")\n",
    "\n",
    "contract_df = pd.DataFrame({\"Player\":PlayerList,\"Contract Years Left\":ContractList})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Player</th>\n",
       "      <th>Contract Years Left</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alisson</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Caoimhin Kelleher</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Loris Karius</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Adrián</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Virgil van Dijk</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3168</th>\n",
       "      <td>Matthieu Dreyer</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3169</th>\n",
       "      <td>Théo Le Bris</td>\n",
       "      <td>fail</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3170</th>\n",
       "      <td>Redwan Bourlès</td>\n",
       "      <td>fail</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3171</th>\n",
       "      <td>Enzo Le Fée</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3172</th>\n",
       "      <td>Pierre-Yves Hamel</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3173 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Player Contract Years Left\n",
       "0               Alisson                   6\n",
       "1     Caoimhin Kelleher                   5\n",
       "2          Loris Karius                   1\n",
       "3                Adrián                   1\n",
       "4       Virgil van Dijk                   4\n",
       "...                 ...                 ...\n",
       "3168    Matthieu Dreyer                   2\n",
       "3169       Théo Le Bris                fail\n",
       "3170     Redwan Bourlès                fail\n",
       "3171        Enzo Le Fée                   3\n",
       "3172  Pierre-Yves Hamel                   1\n",
       "\n",
       "[3173 rows x 2 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Observing what our contract df looks like\n",
    "contract_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Function that Scrapes and builds a dataframe of all Players' data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#This function works when providing links of individual club pages.\n",
    "#It iterates through every link in the list provided to it and then returns a dataframe of player data from the given webpages.\n",
    "def build_df(links):\n",
    "\n",
    "    PlayersList = []\n",
    "    AgeList=[]\n",
    "    NationList=[]\n",
    "    ValuesList = []\n",
    "    PositionsList=[]\n",
    "    ClubList = []\n",
    "\n",
    "    for a in links:\n",
    "\n",
    "        headers = {'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/47.0.2526.106 Safari/537.36'}\n",
    "        page = a\n",
    "        pageTree = requests.get(page, headers=headers)\n",
    "        pageSoup = BeautifulSoup(pageTree.content, 'html.parser')\n",
    "\n",
    "        Players = pageSoup.find_all(\"img\", {\"class\": \"bilderrahmen-fixed lazy lazy\"})\n",
    "        Age = pageSoup.find_all(\"td\", {\"class\": \"zentriert\"})\n",
    "        Values = pageSoup.find_all(\"td\", {\"class\": \"rechts hauptlink\"})\n",
    "        Positions = pageSoup.find_all(\"td\", {\"class\": [\"zentriert rueckennummer bg_Torwart\",\"zentriert rueckennummer bg_Abwehr\",\"zentriert rueckennummer bg_Mittelfeld\",\"zentriert rueckennummer bg_Sturm\"]})\n",
    "        Nationality = pageSoup.find_all(\"td\", {\"class\": \"zentriert\"})\n",
    "        Club = pageSoup.find_all(\"meta\")\n",
    "\n",
    "        for i in range(0,len(Values)):\n",
    "            ValuesList.append(Values[i].text)\n",
    "\n",
    "        for i in range(0,len(Players)):\n",
    "            PlayersList.append(str(Players[i]).split('\" class',1)[0].split('<img alt=\"',1)[1])\n",
    "\n",
    "        for i in range(0,len(Positions)):\n",
    "            PositionsList.append(str(Positions[i]).split('title=\"',1)[1].split('\"><div')[0])\n",
    "\n",
    "        for i in range(1,(len(Players)*3),3):\n",
    "            AgeList.append(str(Age[i]).split(\"(\",1)[1].split(\")\",1)[0])\n",
    "\n",
    "        for i in range(2,(len(Players)*3),3):\n",
    "            NationList.append(str(Nationality[i]).split('title=\"',1)[1].split('\"/',1)[0])\n",
    "\n",
    "        for i in range(0,len(Players)):\n",
    "            ClubList.append(str(Club[6]).split('content=\"')[1].split(\",\")[0])\n",
    "            \n",
    "\n",
    "    #Initial uncleaned dataframe initiated\n",
    "    df= pd.DataFrame({\"Players\":PlayersList,\"Club\":ClubList,\"Position\":PositionsList, \"Age\":AgeList, \"Nationality\":NationList,\"Values\":ValuesList,})\n",
    "\n",
    "    #Missing Transfer Values were stored as '\\xa0'. The line below replaces them as None values\n",
    "    df.loc[(df.Values == '\\xa0'),'Values']= None\n",
    "\n",
    "    #Dataframe without missing transfer values\n",
    "    df_droppednull = df.dropna()\n",
    "\n",
    "    #Converting the Transfer Values data to float            \n",
    "    transfer_values = [a[1:len(a)-2] for a in df_droppednull['Values']]\n",
    "\n",
    "    cleaned_values=[]\n",
    "    for a in list(range(len(transfer_values))):\n",
    "        if transfer_values[a].endswith('m'):\n",
    "            cleaned_values.append(float(transfer_values[a][:len(transfer_values[a])-1])*1000000)\n",
    "        elif transfer_values[a].endswith('Th.'):\n",
    "            cleaned_values.append(float(transfer_values[a][:len(transfer_values[a])-3])*1000)\n",
    "        else:\n",
    "            cleaned_values.append(float(a))\n",
    "\n",
    "    #Constructing the Final Dataframe   \n",
    "    final_df= pd.DataFrame({\"Player\":df_droppednull['Players'],\"Club\":df_droppednull['Club'],\n",
    "                    \"Age\":df_droppednull['Age'],\"Position\":df_droppednull['Position'],\n",
    "                    \"Nation\":df_droppednull['Nationality'],\"Value\":cleaned_values})\n",
    "\n",
    "    #df ranked by transfer value\n",
    "    ranked_df = final_df.sort_values('Value',ascending=False)\n",
    "    \n",
    "    return ranked_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Building the dataframe of players from every Club Page"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Building a dataframe by scraping information of the 98 teams from Europe's top 5 leagues - i.e. England, Spain, Italy, Germany & France\n",
    "first_df = build_df(top_league(5))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Seeing what our scraped dataset looks like"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Player</th>\n",
       "      <th>Club</th>\n",
       "      <th>Age</th>\n",
       "      <th>Position</th>\n",
       "      <th>Nation</th>\n",
       "      <th>Value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2132</th>\n",
       "      <td>Kylian Mbappé</td>\n",
       "      <td>Paris Saint-Germain</td>\n",
       "      <td>22</td>\n",
       "      <td>attack</td>\n",
       "      <td>France</td>\n",
       "      <td>144000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1637</th>\n",
       "      <td>Erling Haaland</td>\n",
       "      <td>Borussia Dortmund</td>\n",
       "      <td>21</td>\n",
       "      <td>attack</td>\n",
       "      <td>Norway</td>\n",
       "      <td>117000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>Tottenham Hotspur</td>\n",
       "      <td>28</td>\n",
       "      <td>attack</td>\n",
       "      <td>England</td>\n",
       "      <td>108000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Jadon Sancho</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>21</td>\n",
       "      <td>attack</td>\n",
       "      <td>England</td>\n",
       "      <td>90000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>Liverpool FC</td>\n",
       "      <td>29</td>\n",
       "      <td>attack</td>\n",
       "      <td>Egypt</td>\n",
       "      <td>90000000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1392</th>\n",
       "      <td>Michele Laraspata</td>\n",
       "      <td>Parma Calcio 1913</td>\n",
       "      <td>20</td>\n",
       "      <td>Defender</td>\n",
       "      <td>Italy</td>\n",
       "      <td>45000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1479</th>\n",
       "      <td>Alessandro Berardi</td>\n",
       "      <td>Hellas Verona</td>\n",
       "      <td>30</td>\n",
       "      <td>Goalkeeper</td>\n",
       "      <td>Italy</td>\n",
       "      <td>45000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2473</th>\n",
       "      <td>Mamadou Mbow</td>\n",
       "      <td>Stade Reims</td>\n",
       "      <td>21</td>\n",
       "      <td>Defender</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>45000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1397</th>\n",
       "      <td>Alessandro Martella</td>\n",
       "      <td>Parma Calcio 1913</td>\n",
       "      <td>20</td>\n",
       "      <td>Defender</td>\n",
       "      <td>Italy</td>\n",
       "      <td>23000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1931</th>\n",
       "      <td>Matthias Köbbing</td>\n",
       "      <td>1. FC Köln</td>\n",
       "      <td>24</td>\n",
       "      <td>Goalkeeper</td>\n",
       "      <td>Germany</td>\n",
       "      <td>9000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2635 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Player                 Club Age    Position   Nation  \\\n",
       "2132        Kylian Mbappé  Paris Saint-Germain  22      attack   France   \n",
       "1637       Erling Haaland    Borussia Dortmund  21      attack   Norway   \n",
       "98             Harry Kane    Tottenham Hotspur  28      attack  England   \n",
       "121          Jadon Sancho    Manchester United  21      attack  England   \n",
       "23          Mohamed Salah         Liverpool FC  29      attack    Egypt   \n",
       "...                   ...                  ...  ..         ...      ...   \n",
       "1392    Michele Laraspata    Parma Calcio 1913  20    Defender    Italy   \n",
       "1479   Alessandro Berardi        Hellas Verona  30  Goalkeeper    Italy   \n",
       "2473         Mamadou Mbow          Stade Reims  21    Defender  Senegal   \n",
       "1397  Alessandro Martella    Parma Calcio 1913  20    Defender    Italy   \n",
       "1931     Matthias Köbbing           1. FC Köln  24  Goalkeeper  Germany   \n",
       "\n",
       "            Value  \n",
       "2132  144000000.0  \n",
       "1637  117000000.0  \n",
       "98    108000000.0  \n",
       "121    90000000.0  \n",
       "23     90000000.0  \n",
       "...           ...  \n",
       "1392      45000.0  \n",
       "1479      45000.0  \n",
       "2473      45000.0  \n",
       "1397      23000.0  \n",
       "1931       9000.0  \n",
       "\n",
       "[2635 rows x 6 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Seeing what our dataframe looks like\n",
    "first_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merging the contract length data onto the above dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merging our dataframe with contract information\n",
    "merged_df = pd.merge(first_df, contract_df, on='Player')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding a League Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Grouping Clubs by individual Leagues and making a separate \"League\" column.\n",
    "\n",
    "England = ['Manchester City','Liverpool FC','Crystal Palace','Chelsea FC','Fulham FC','Brighton &amp; Hove Albion','West Bromwich Albion','Newcastle United','Leeds United','Aston Villa','Sheffield United','Manchester United','West Ham United','Leicester City','Tottenham Hotspur','Burnley FC','Southampton FC','Arsenal FC','Wolverhampton Wanderers','Everton FC']\n",
    "Spain = ['SD Eibar','CA Osasuna','Athletic Bilbao','Real Betis Balompié','Valencia CF','Celta de Vigo','Levante UD','Cádiz CF','FC Barcelona','Villarreal CF','SD Huesca','Sevilla FC','Getafe CF','Deportivo Alavés','Real Valladolid CF','Granada CF','Real Madrid','Elche CF','Atlético de Madrid','Real Sociedad']\n",
    "Italy = ['US Sassuolo','AC Milan','Juventus FC','Udinese Calcio','Genoa CFC','Cagliari Calcio','Inter Milan','FC Crotone','Hellas Verona','Benevento Calcio','Parma Calcio 1913','SSC Napoli','AS Roma','Bologna FC 1909','Torino FC','UC Sampdoria','SS Lazio','ACF Fiorentina','Atalanta BC','Spezia Calcio']\n",
    "Germany = ['Bayern Munich','Hertha BSC','Borussia Dortmund','VfB Stuttgart','1.FSV Mainz 05','Arminia Bielefeld','Bayer 04 Leverkusen','SV Werder Bremen','1. FC Köln','FC Augsburg','VfL Wolfsburg','TSG 1899 Hoffenheim','RB Leipzig','SC Freiburg','1.FC Union Berlin','FC Schalke 04','Borussia Mönchengladbach','Eintracht Frankfurt']\n",
    "France = ['AS Monaco','Montpellier HSC','FC Girondins Bordeaux','Paris Saint-Germain','Olympique Marseille','OGC Nice','Nîmes Olympique','FC Nantes','Stade Reims','FC Lorient','Stade Brestois 29','LOSC Lille','Stade Rennais FC','Dijon FCO','RC Strasbourg Alsace','RC Lens','AS Saint-Étienne','FC Metz','Olympique Lyon','SCO Angers']\n",
    "\n",
    "LeagueList = []\n",
    "for a in merged_df['Club']:\n",
    "    if a in England:\n",
    "        LeagueList.append('Premier League')\n",
    "    elif a in Spain:\n",
    "        LeagueList.append('La Liga')\n",
    "    elif a in Italy:\n",
    "        LeagueList.append('Serie A')\n",
    "    elif a in Germany:\n",
    "        LeagueList.append('Bundesliga')\n",
    "    elif a in France:\n",
    "        LeagueList.append('Ligue 1')\n",
    "    else:\n",
    "        LeagueList.append(\"UNKNOWN\")\n",
    "        \n",
    "merged_df['League'] = LeagueList"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Player</th>\n",
       "      <th>Club</th>\n",
       "      <th>Age</th>\n",
       "      <th>Position</th>\n",
       "      <th>Nation</th>\n",
       "      <th>Value</th>\n",
       "      <th>Contract Years Left</th>\n",
       "      <th>League</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kylian Mbappé</td>\n",
       "      <td>Paris Saint-Germain</td>\n",
       "      <td>22</td>\n",
       "      <td>attack</td>\n",
       "      <td>France</td>\n",
       "      <td>144000000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Ligue 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Erling Haaland</td>\n",
       "      <td>Borussia Dortmund</td>\n",
       "      <td>21</td>\n",
       "      <td>attack</td>\n",
       "      <td>Norway</td>\n",
       "      <td>117000000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Bundesliga</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Erling Haaland</td>\n",
       "      <td>Borussia Dortmund</td>\n",
       "      <td>21</td>\n",
       "      <td>attack</td>\n",
       "      <td>Norway</td>\n",
       "      <td>117000000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Bundesliga</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>Tottenham Hotspur</td>\n",
       "      <td>28</td>\n",
       "      <td>attack</td>\n",
       "      <td>England</td>\n",
       "      <td>108000000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Premier League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jadon Sancho</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>21</td>\n",
       "      <td>attack</td>\n",
       "      <td>England</td>\n",
       "      <td>90000000.0</td>\n",
       "      <td>5</td>\n",
       "      <td>Premier League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3100</th>\n",
       "      <td>Michele Laraspata</td>\n",
       "      <td>Parma Calcio 1913</td>\n",
       "      <td>20</td>\n",
       "      <td>Defender</td>\n",
       "      <td>Italy</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Serie A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3101</th>\n",
       "      <td>Alessandro Berardi</td>\n",
       "      <td>Hellas Verona</td>\n",
       "      <td>30</td>\n",
       "      <td>Goalkeeper</td>\n",
       "      <td>Italy</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Serie A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3102</th>\n",
       "      <td>Mamadou Mbow</td>\n",
       "      <td>Stade Reims</td>\n",
       "      <td>21</td>\n",
       "      <td>Defender</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Ligue 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3103</th>\n",
       "      <td>Alessandro Martella</td>\n",
       "      <td>Parma Calcio 1913</td>\n",
       "      <td>20</td>\n",
       "      <td>Defender</td>\n",
       "      <td>Italy</td>\n",
       "      <td>23000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Serie A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3104</th>\n",
       "      <td>Matthias Köbbing</td>\n",
       "      <td>1. FC Köln</td>\n",
       "      <td>24</td>\n",
       "      <td>Goalkeeper</td>\n",
       "      <td>Germany</td>\n",
       "      <td>9000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Bundesliga</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3105 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Player                 Club Age    Position   Nation  \\\n",
       "0           Kylian Mbappé  Paris Saint-Germain  22      attack   France   \n",
       "1          Erling Haaland    Borussia Dortmund  21      attack   Norway   \n",
       "2          Erling Haaland    Borussia Dortmund  21      attack   Norway   \n",
       "3              Harry Kane    Tottenham Hotspur  28      attack  England   \n",
       "4            Jadon Sancho    Manchester United  21      attack  England   \n",
       "...                   ...                  ...  ..         ...      ...   \n",
       "3100    Michele Laraspata    Parma Calcio 1913  20    Defender    Italy   \n",
       "3101   Alessandro Berardi        Hellas Verona  30  Goalkeeper    Italy   \n",
       "3102         Mamadou Mbow          Stade Reims  21    Defender  Senegal   \n",
       "3103  Alessandro Martella    Parma Calcio 1913  20    Defender    Italy   \n",
       "3104     Matthias Köbbing           1. FC Köln  24  Goalkeeper  Germany   \n",
       "\n",
       "            Value Contract Years Left          League  \n",
       "0     144000000.0                   1         Ligue 1  \n",
       "1     117000000.0                   3      Bundesliga  \n",
       "2     117000000.0                   3      Bundesliga  \n",
       "3     108000000.0                   3  Premier League  \n",
       "4      90000000.0                   5  Premier League  \n",
       "...           ...                 ...             ...  \n",
       "3100      45000.0                   3         Serie A  \n",
       "3101      45000.0                   2         Serie A  \n",
       "3102      45000.0                   2         Ligue 1  \n",
       "3103      23000.0                   3         Serie A  \n",
       "3104       9000.0                   1      Bundesliga  \n",
       "\n",
       "[3105 rows x 8 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#looking at our dataframe with contract information appended.\n",
    "merged_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#There are some duplicate rows\n",
    "merged_df.drop_duplicates(keep=\"first\",inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Player</th>\n",
       "      <th>Club</th>\n",
       "      <th>Age</th>\n",
       "      <th>Position</th>\n",
       "      <th>Nation</th>\n",
       "      <th>Value</th>\n",
       "      <th>Contract Years Left</th>\n",
       "      <th>League</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Kylian Mbappé</td>\n",
       "      <td>Paris Saint-Germain</td>\n",
       "      <td>22</td>\n",
       "      <td>attack</td>\n",
       "      <td>France</td>\n",
       "      <td>144000000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Ligue 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Erling Haaland</td>\n",
       "      <td>Borussia Dortmund</td>\n",
       "      <td>21</td>\n",
       "      <td>attack</td>\n",
       "      <td>Norway</td>\n",
       "      <td>117000000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Bundesliga</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Harry Kane</td>\n",
       "      <td>Tottenham Hotspur</td>\n",
       "      <td>28</td>\n",
       "      <td>attack</td>\n",
       "      <td>England</td>\n",
       "      <td>108000000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Premier League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jadon Sancho</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>21</td>\n",
       "      <td>attack</td>\n",
       "      <td>England</td>\n",
       "      <td>90000000.0</td>\n",
       "      <td>5</td>\n",
       "      <td>Premier League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Mohamed Salah</td>\n",
       "      <td>Liverpool FC</td>\n",
       "      <td>29</td>\n",
       "      <td>attack</td>\n",
       "      <td>Egypt</td>\n",
       "      <td>90000000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Premier League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3100</th>\n",
       "      <td>Michele Laraspata</td>\n",
       "      <td>Parma Calcio 1913</td>\n",
       "      <td>20</td>\n",
       "      <td>Defender</td>\n",
       "      <td>Italy</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Serie A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3101</th>\n",
       "      <td>Alessandro Berardi</td>\n",
       "      <td>Hellas Verona</td>\n",
       "      <td>30</td>\n",
       "      <td>Goalkeeper</td>\n",
       "      <td>Italy</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Serie A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3102</th>\n",
       "      <td>Mamadou Mbow</td>\n",
       "      <td>Stade Reims</td>\n",
       "      <td>21</td>\n",
       "      <td>Defender</td>\n",
       "      <td>Senegal</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>2</td>\n",
       "      <td>Ligue 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3103</th>\n",
       "      <td>Alessandro Martella</td>\n",
       "      <td>Parma Calcio 1913</td>\n",
       "      <td>20</td>\n",
       "      <td>Defender</td>\n",
       "      <td>Italy</td>\n",
       "      <td>23000.0</td>\n",
       "      <td>3</td>\n",
       "      <td>Serie A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3104</th>\n",
       "      <td>Matthias Köbbing</td>\n",
       "      <td>1. FC Köln</td>\n",
       "      <td>24</td>\n",
       "      <td>Goalkeeper</td>\n",
       "      <td>Germany</td>\n",
       "      <td>9000.0</td>\n",
       "      <td>1</td>\n",
       "      <td>Bundesliga</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2643 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Player                 Club Age    Position   Nation  \\\n",
       "0           Kylian Mbappé  Paris Saint-Germain  22      attack   France   \n",
       "1          Erling Haaland    Borussia Dortmund  21      attack   Norway   \n",
       "3              Harry Kane    Tottenham Hotspur  28      attack  England   \n",
       "4            Jadon Sancho    Manchester United  21      attack  England   \n",
       "5           Mohamed Salah         Liverpool FC  29      attack    Egypt   \n",
       "...                   ...                  ...  ..         ...      ...   \n",
       "3100    Michele Laraspata    Parma Calcio 1913  20    Defender    Italy   \n",
       "3101   Alessandro Berardi        Hellas Verona  30  Goalkeeper    Italy   \n",
       "3102         Mamadou Mbow          Stade Reims  21    Defender  Senegal   \n",
       "3103  Alessandro Martella    Parma Calcio 1913  20    Defender    Italy   \n",
       "3104     Matthias Köbbing           1. FC Köln  24  Goalkeeper  Germany   \n",
       "\n",
       "            Value Contract Years Left          League  \n",
       "0     144000000.0                   1         Ligue 1  \n",
       "1     117000000.0                   3      Bundesliga  \n",
       "3     108000000.0                   3  Premier League  \n",
       "4      90000000.0                   5  Premier League  \n",
       "5      90000000.0                   2  Premier League  \n",
       "...           ...                 ...             ...  \n",
       "3100      45000.0                   3         Serie A  \n",
       "3101      45000.0                   2         Serie A  \n",
       "3102      45000.0                   2         Ligue 1  \n",
       "3103      23000.0                   3         Serie A  \n",
       "3104       9000.0                   1      Bundesliga  \n",
       "\n",
       "[2643 rows x 8 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exporting the Dataset"
   ]
  },
  {
   "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/transfermarkt_scrape_latest.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Exporting data to .csv format in the repo's data folder.\n",
    "merged_df.to_excel(file_path,index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (learn-env)",
   "language": "python",
   "name": "learn-env"
  },
  "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
}