scraper_for_players_best5leagues.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[     Unnamed: 0_level_0 Unnamed: 1_level_0 Unnamed: 2_level_0  \\\n",
       "                      Rk             Player             Nation   \n",
       " 0                     1         Max Aarons            eng ENG   \n",
       " 1                     2   Brenden Aaronson             us USA   \n",
       " 2                     3    Paxten Aaronson             us USA   \n",
       " 3                     4  Keyliane Abdallah             fr FRA   \n",
       " 4                     5   Yunis Abdelhamid             ma MAR   \n",
       " ...                 ...                ...                ...   \n",
       " 2962               2850    Martin Ødegaard             no NOR   \n",
       " 2963                 Rk             Player             Nation   \n",
       " 2964               2851        Milan Đurić             ba BIH   \n",
       " 2965               2852        Milan Đurić             ba BIH   \n",
       " 2966               2853   Mateusz Łęgowski             pl POL   \n",
       " \n",
       "      Unnamed: 3_level_0 Unnamed: 4_level_0  Unnamed: 5_level_0  \\\n",
       "                     Pos              Squad                Comp   \n",
       " 0                    DF        Bournemouth  eng Premier League   \n",
       " 1                 MF,FW       Union Berlin       de Bundesliga   \n",
       " 2                    MF     Eint Frankfurt       de Bundesliga   \n",
       " 3                    FW          Marseille          fr Ligue 1   \n",
       " 4                    DF              Reims          fr Ligue 1   \n",
       " ...                 ...                ...                 ...   \n",
       " 2962                 MF            Arsenal  eng Premier League   \n",
       " 2963                Pos              Squad                Comp   \n",
       " 2964                 FW      Hellas Verona          it Serie A   \n",
       " 2965                 FW              Monza          it Serie A   \n",
       " 2966                 MF        Salernitana          it Serie A   \n",
       " \n",
       "      Unnamed: 6_level_0 Unnamed: 7_level_0 Unnamed: 8_level_0  SCA  ...  \\\n",
       "                     Age               Born                90s  SCA  ...   \n",
       " 0                    23               2000               13.7   23  ...   \n",
       " 1                    22               2000               14.1   53  ...   \n",
       " 2                    19               2003                1.1    1  ...   \n",
       " 3                    17               2006                0.0    0  ...   \n",
       " 4                    35               1987               30.9   24  ...   \n",
       " ...                 ...                ...                ...  ...  ...   \n",
       " 2962                 24               1998               34.3  220  ...   \n",
       " 2963                Age               Born                90s  SCA  ...   \n",
       " 2964                 33               1990               13.4   33  ...   \n",
       " 2965                 33               1990               14.0   13  ...   \n",
       " 2966                 20               2003               12.7   18  ...   \n",
       " \n",
       "      SCA Types  GCA        GCA Types                              \\\n",
       "            Def  GCA  GCA90  PassLive  PassDead  TO  Sh  Fld  Def   \n",
       " 0            0    2   0.15         2         0   0   0    0    0   \n",
       " 1            0    8   0.57         6         0   2   0    0    0   \n",
       " 2            0    1   0.89         1         0   0   0    0    0   \n",
       " 3            0    0   0.00         0         0   0   0    0    0   \n",
       " 4            0    1   0.03         0         0   0   1    0    0   \n",
       " ...        ...  ...    ...       ...       ...  ..  ..  ...  ...   \n",
       " 2962         1   23   0.67        18         2   0   2    1    0   \n",
       " 2963       Def  GCA  GCA90  PassLive  PassDead  TO  Sh  Fld  Def   \n",
       " 2964         1    4   0.30         1         0   0   0    2    1   \n",
       " 2965         0    1   0.07         1         0   0   0    0    0   \n",
       " 2966         1    0   0.00         0         0   0   0    0    0   \n",
       " \n",
       "      Unnamed: 25_level_0  \n",
       "                  Matches  \n",
       " 0                Matches  \n",
       " 1                Matches  \n",
       " 2                Matches  \n",
       " 3                Matches  \n",
       " 4                Matches  \n",
       " ...                  ...  \n",
       " 2962             Matches  \n",
       " 2963             Matches  \n",
       " 2964             Matches  \n",
       " 2965             Matches  \n",
       " 2966             Matches  \n",
       " \n",
       " [2967 rows x 26 columns]]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# libraries\n",
    "import pandas as pd\n",
    "\n",
    "# fbref table link\n",
    "url_df = 'https://fbref.com/en/comps/Big5/gca/players/Big-5-European-Leagues-Stats#stats_gca'\n",
    "\n",
    "df = pd.read_html(url_df)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0_level_0</th>\n",
       "      <th>Unnamed: 1_level_0</th>\n",
       "      <th>Unnamed: 2_level_0</th>\n",
       "      <th>Unnamed: 3_level_0</th>\n",
       "      <th>Unnamed: 4_level_0</th>\n",
       "      <th>Unnamed: 5_level_0</th>\n",
       "      <th>Unnamed: 6_level_0</th>\n",
       "      <th>Unnamed: 7_level_0</th>\n",
       "      <th>Unnamed: 8_level_0</th>\n",
       "      <th>SCA</th>\n",
       "      <th>...</th>\n",
       "      <th>SCA Types</th>\n",
       "      <th colspan=\"2\" halign=\"left\">GCA</th>\n",
       "      <th colspan=\"6\" halign=\"left\">GCA Types</th>\n",
       "      <th>Unnamed: 25_level_0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Rk</th>\n",
       "      <th>Player</th>\n",
       "      <th>Nation</th>\n",
       "      <th>Pos</th>\n",
       "      <th>Squad</th>\n",
       "      <th>Comp</th>\n",
       "      <th>Age</th>\n",
       "      <th>Born</th>\n",
       "      <th>90s</th>\n",
       "      <th>SCA</th>\n",
       "      <th>...</th>\n",
       "      <th>Def</th>\n",
       "      <th>GCA</th>\n",
       "      <th>GCA90</th>\n",
       "      <th>PassLive</th>\n",
       "      <th>PassDead</th>\n",
       "      <th>TO</th>\n",
       "      <th>Sh</th>\n",
       "      <th>Fld</th>\n",
       "      <th>Def</th>\n",
       "      <th>Matches</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Max Aarons</td>\n",
       "      <td>eng ENG</td>\n",
       "      <td>DF</td>\n",
       "      <td>Bournemouth</td>\n",
       "      <td>eng Premier League</td>\n",
       "      <td>23</td>\n",
       "      <td>2000</td>\n",
       "      <td>13.7</td>\n",
       "      <td>23</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.15</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Brenden Aaronson</td>\n",
       "      <td>us USA</td>\n",
       "      <td>MF,FW</td>\n",
       "      <td>Union Berlin</td>\n",
       "      <td>de Bundesliga</td>\n",
       "      <td>22</td>\n",
       "      <td>2000</td>\n",
       "      <td>14.1</td>\n",
       "      <td>53</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0.57</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Paxten Aaronson</td>\n",
       "      <td>us USA</td>\n",
       "      <td>MF</td>\n",
       "      <td>Eint Frankfurt</td>\n",
       "      <td>de Bundesliga</td>\n",
       "      <td>19</td>\n",
       "      <td>2003</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.89</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Keyliane Abdallah</td>\n",
       "      <td>fr FRA</td>\n",
       "      <td>FW</td>\n",
       "      <td>Marseille</td>\n",
       "      <td>fr Ligue 1</td>\n",
       "      <td>17</td>\n",
       "      <td>2006</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Yunis Abdelhamid</td>\n",
       "      <td>ma MAR</td>\n",
       "      <td>DF</td>\n",
       "      <td>Reims</td>\n",
       "      <td>fr Ligue 1</td>\n",
       "      <td>35</td>\n",
       "      <td>1987</td>\n",
       "      <td>30.9</td>\n",
       "      <td>24</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0_level_0 Unnamed: 1_level_0 Unnamed: 2_level_0 Unnamed: 3_level_0  \\\n",
       "                  Rk             Player             Nation                Pos   \n",
       "0                  1         Max Aarons            eng ENG                 DF   \n",
       "1                  2   Brenden Aaronson             us USA              MF,FW   \n",
       "2                  3    Paxten Aaronson             us USA                 MF   \n",
       "3                  4  Keyliane Abdallah             fr FRA                 FW   \n",
       "4                  5   Yunis Abdelhamid             ma MAR                 DF   \n",
       "\n",
       "  Unnamed: 4_level_0  Unnamed: 5_level_0 Unnamed: 6_level_0  \\\n",
       "               Squad                Comp                Age   \n",
       "0        Bournemouth  eng Premier League                 23   \n",
       "1       Union Berlin       de Bundesliga                 22   \n",
       "2     Eint Frankfurt       de Bundesliga                 19   \n",
       "3          Marseille          fr Ligue 1                 17   \n",
       "4              Reims          fr Ligue 1                 35   \n",
       "\n",
       "  Unnamed: 7_level_0 Unnamed: 8_level_0 SCA  ... SCA Types GCA        \\\n",
       "                Born                90s SCA  ...       Def GCA GCA90   \n",
       "0               2000               13.7  23  ...         0   2  0.15   \n",
       "1               2000               14.1  53  ...         0   8  0.57   \n",
       "2               2003                1.1   1  ...         0   1  0.89   \n",
       "3               2006                0.0   0  ...         0   0  0.00   \n",
       "4               1987               30.9  24  ...         0   1  0.03   \n",
       "\n",
       "  GCA Types                        Unnamed: 25_level_0  \n",
       "   PassLive PassDead TO Sh Fld Def             Matches  \n",
       "0         2        0  0  0   0   0             Matches  \n",
       "1         6        0  2  0   0   0             Matches  \n",
       "2         1        0  0  0   0   0             Matches  \n",
       "3         0        0  0  0   0   0             Matches  \n",
       "4         0        0  0  1   0   0             Matches  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_html(url_df)[0]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Unnamed: 0_level_0 Rk</th>\n",
       "      <th>Unnamed: 1_level_0 Player</th>\n",
       "      <th>Unnamed: 2_level_0 Nation</th>\n",
       "      <th>Unnamed: 3_level_0 Pos</th>\n",
       "      <th>Unnamed: 4_level_0 Squad</th>\n",
       "      <th>Unnamed: 5_level_0 Comp</th>\n",
       "      <th>Unnamed: 6_level_0 Age</th>\n",
       "      <th>Unnamed: 7_level_0 Born</th>\n",
       "      <th>Unnamed: 8_level_0 90s</th>\n",
       "      <th>SCA SCA</th>\n",
       "      <th>...</th>\n",
       "      <th>SCA Types Def</th>\n",
       "      <th>GCA GCA</th>\n",
       "      <th>GCA GCA90</th>\n",
       "      <th>GCA Types PassLive</th>\n",
       "      <th>GCA Types PassDead</th>\n",
       "      <th>GCA Types TO</th>\n",
       "      <th>GCA Types Sh</th>\n",
       "      <th>GCA Types Fld</th>\n",
       "      <th>GCA Types Def</th>\n",
       "      <th>Unnamed: 25_level_0 Matches</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Max Aarons</td>\n",
       "      <td>eng ENG</td>\n",
       "      <td>DF</td>\n",
       "      <td>Bournemouth</td>\n",
       "      <td>eng Premier League</td>\n",
       "      <td>23</td>\n",
       "      <td>2000</td>\n",
       "      <td>13.7</td>\n",
       "      <td>23</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.15</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Brenden Aaronson</td>\n",
       "      <td>us USA</td>\n",
       "      <td>MF,FW</td>\n",
       "      <td>Union Berlin</td>\n",
       "      <td>de Bundesliga</td>\n",
       "      <td>22</td>\n",
       "      <td>2000</td>\n",
       "      <td>14.1</td>\n",
       "      <td>53</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0.57</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Paxten Aaronson</td>\n",
       "      <td>us USA</td>\n",
       "      <td>MF</td>\n",
       "      <td>Eint Frankfurt</td>\n",
       "      <td>de Bundesliga</td>\n",
       "      <td>19</td>\n",
       "      <td>2003</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.89</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Keyliane Abdallah</td>\n",
       "      <td>fr FRA</td>\n",
       "      <td>FW</td>\n",
       "      <td>Marseille</td>\n",
       "      <td>fr Ligue 1</td>\n",
       "      <td>17</td>\n",
       "      <td>2006</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Yunis Abdelhamid</td>\n",
       "      <td>ma MAR</td>\n",
       "      <td>DF</td>\n",
       "      <td>Reims</td>\n",
       "      <td>fr Ligue 1</td>\n",
       "      <td>35</td>\n",
       "      <td>1987</td>\n",
       "      <td>30.9</td>\n",
       "      <td>24</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Unnamed: 0_level_0 Rk Unnamed: 1_level_0 Player Unnamed: 2_level_0 Nation  \\\n",
       "0                     1                Max Aarons                   eng ENG   \n",
       "1                     2          Brenden Aaronson                    us USA   \n",
       "2                     3           Paxten Aaronson                    us USA   \n",
       "3                     4         Keyliane Abdallah                    fr FRA   \n",
       "4                     5          Yunis Abdelhamid                    ma MAR   \n",
       "\n",
       "  Unnamed: 3_level_0 Pos Unnamed: 4_level_0 Squad Unnamed: 5_level_0 Comp  \\\n",
       "0                     DF              Bournemouth      eng Premier League   \n",
       "1                  MF,FW             Union Berlin           de Bundesliga   \n",
       "2                     MF           Eint Frankfurt           de Bundesliga   \n",
       "3                     FW                Marseille              fr Ligue 1   \n",
       "4                     DF                    Reims              fr Ligue 1   \n",
       "\n",
       "  Unnamed: 6_level_0 Age Unnamed: 7_level_0 Born Unnamed: 8_level_0 90s  \\\n",
       "0                     23                    2000                   13.7   \n",
       "1                     22                    2000                   14.1   \n",
       "2                     19                    2003                    1.1   \n",
       "3                     17                    2006                    0.0   \n",
       "4                     35                    1987                   30.9   \n",
       "\n",
       "  SCA SCA  ... SCA Types Def GCA GCA GCA GCA90 GCA Types PassLive  \\\n",
       "0      23  ...             0       2      0.15                  2   \n",
       "1      53  ...             0       8      0.57                  6   \n",
       "2       1  ...             0       1      0.89                  1   \n",
       "3       0  ...             0       0      0.00                  0   \n",
       "4      24  ...             0       1      0.03                  0   \n",
       "\n",
       "  GCA Types PassDead GCA Types TO GCA Types Sh GCA Types Fld GCA Types Def  \\\n",
       "0                  0            0            0             0             0   \n",
       "1                  0            2            0             0             0   \n",
       "2                  0            0            0             0             0   \n",
       "3                  0            0            0             0             0   \n",
       "4                  0            0            1             0             0   \n",
       "\n",
       "  Unnamed: 25_level_0 Matches  \n",
       "0                     Matches  \n",
       "1                     Matches  \n",
       "2                     Matches  \n",
       "3                     Matches  \n",
       "4                     Matches  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating a data with the same headers but without multi indexing\n",
    "df.columns = [' '.join(col).strip() for col in df.columns]\n",
    "\n",
    "df = df.reset_index(drop=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Rk</th>\n",
       "      <th>Player</th>\n",
       "      <th>Nation</th>\n",
       "      <th>Pos</th>\n",
       "      <th>Squad</th>\n",
       "      <th>Comp</th>\n",
       "      <th>Age</th>\n",
       "      <th>Born</th>\n",
       "      <th>90s</th>\n",
       "      <th>SCA SCA</th>\n",
       "      <th>...</th>\n",
       "      <th>SCA Types Def</th>\n",
       "      <th>GCA GCA</th>\n",
       "      <th>GCA GCA90</th>\n",
       "      <th>GCA Types PassLive</th>\n",
       "      <th>GCA Types PassDead</th>\n",
       "      <th>GCA Types TO</th>\n",
       "      <th>GCA Types Sh</th>\n",
       "      <th>GCA Types Fld</th>\n",
       "      <th>GCA Types Def</th>\n",
       "      <th>Matches</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Max Aarons</td>\n",
       "      <td>eng ENG</td>\n",
       "      <td>DF</td>\n",
       "      <td>Bournemouth</td>\n",
       "      <td>eng Premier League</td>\n",
       "      <td>23</td>\n",
       "      <td>2000</td>\n",
       "      <td>13.7</td>\n",
       "      <td>23</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.15</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Brenden Aaronson</td>\n",
       "      <td>us USA</td>\n",
       "      <td>MF,FW</td>\n",
       "      <td>Union Berlin</td>\n",
       "      <td>de Bundesliga</td>\n",
       "      <td>22</td>\n",
       "      <td>2000</td>\n",
       "      <td>14.1</td>\n",
       "      <td>53</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0.57</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Paxten Aaronson</td>\n",
       "      <td>us USA</td>\n",
       "      <td>MF</td>\n",
       "      <td>Eint Frankfurt</td>\n",
       "      <td>de Bundesliga</td>\n",
       "      <td>19</td>\n",
       "      <td>2003</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.89</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Keyliane Abdallah</td>\n",
       "      <td>fr FRA</td>\n",
       "      <td>FW</td>\n",
       "      <td>Marseille</td>\n",
       "      <td>fr Ligue 1</td>\n",
       "      <td>17</td>\n",
       "      <td>2006</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Yunis Abdelhamid</td>\n",
       "      <td>ma MAR</td>\n",
       "      <td>DF</td>\n",
       "      <td>Reims</td>\n",
       "      <td>fr Ligue 1</td>\n",
       "      <td>35</td>\n",
       "      <td>1987</td>\n",
       "      <td>30.9</td>\n",
       "      <td>24</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Matches</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Rk             Player   Nation    Pos           Squad                Comp  \\\n",
       "0  1         Max Aarons  eng ENG     DF     Bournemouth  eng Premier League   \n",
       "1  2   Brenden Aaronson   us USA  MF,FW    Union Berlin       de Bundesliga   \n",
       "2  3    Paxten Aaronson   us USA     MF  Eint Frankfurt       de Bundesliga   \n",
       "3  4  Keyliane Abdallah   fr FRA     FW       Marseille          fr Ligue 1   \n",
       "4  5   Yunis Abdelhamid   ma MAR     DF           Reims          fr Ligue 1   \n",
       "\n",
       "  Age  Born   90s SCA SCA  ... SCA Types Def GCA GCA GCA GCA90  \\\n",
       "0  23  2000  13.7      23  ...             0       2      0.15   \n",
       "1  22  2000  14.1      53  ...             0       8      0.57   \n",
       "2  19  2003   1.1       1  ...             0       1      0.89   \n",
       "3  17  2006   0.0       0  ...             0       0      0.00   \n",
       "4  35  1987  30.9      24  ...             0       1      0.03   \n",
       "\n",
       "  GCA Types PassLive GCA Types PassDead GCA Types TO GCA Types Sh  \\\n",
       "0                  2                  0            0            0   \n",
       "1                  6                  0            2            0   \n",
       "2                  1                  0            0            0   \n",
       "3                  0                  0            0            0   \n",
       "4                  0                  0            0            1   \n",
       "\n",
       "  GCA Types Fld GCA Types Def  Matches  \n",
       "0             0             0  Matches  \n",
       "1             0             0  Matches  \n",
       "2             0             0  Matches  \n",
       "3             0             0  Matches  \n",
       "4             0             0  Matches  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating a list with new names\n",
    "new_columns = []\n",
    "for col in df.columns:\n",
    "  if 'level_0' in col:\n",
    "      new_col = col.split()[-1]  # takes the last name\n",
    "  else:\n",
    "      new_col = col\n",
    "  new_columns.append(new_col)\n",
    "\n",
    "# rename columns\n",
    "df.columns = new_columns\n",
    "df = df.fillna(0)\n",
    "\n",
    "df.head() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Age'] = df['Age'].str[:2]\n",
    "df['Position_2'] = df['Pos'].str[3:]\n",
    "df['Position'] = df['Pos'].str[:2]\n",
    "df['Nation'] = df['Nation'].str.split(' ').str.get(1)\n",
    "df['League'] = df['Comp'].str.split(' ').str.get(1)\n",
    "df['League_'] = df['Comp'].str.split(' ').str.get(2)\n",
    "df['League'] = df['League'] + ' ' + df['League_']\n",
    "df = df.drop(columns=['League_', 'Comp', 'Rk', 'Pos','Matches'])\n",
    "\n",
    "df['Position'] = df['Position'].replace({'MF': 'Midfielder', 'DF': 'Defender', 'FW': 'Forward', 'GK': 'Goalkeeper'})\n",
    "df['Position_2'] = df['Position_2'].replace({'MF': 'Midfielder', 'DF': 'Defender',\n",
    "                                                 'FW': 'Forward', 'GK': 'Goalkeeper'})\n",
    "df['League'] = df['League'].fillna('Bundesliga')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Premier League' 'Bundesliga' 'Ligue 1' 'La Liga' 'Serie A']\n"
     ]
    }
   ],
   "source": [
    "# df.head()\n",
    "print(df[\"League\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"best5leagues_datasetplayers.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}