agents_scraper.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from bs4 import BeautifulSoup\n",
    "from urllib.request import urlopen, Request\n",
    "import requests\n",
    "import re\n",
    "import time\n",
    "import concurrent.futures\n",
    "\n",
    "from tqdm import tqdm\n",
    "tqdm.pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(r\"dataset\\transfers_complete.csv\")\n",
    "df[\"agent\"] = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "HEADERS = {\n",
    "    'accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8',\n",
    "    'accept-encoding': 'gzip, deflate, br',\n",
    "    'accept-language': 'ru-RU,ru;q=0.9,en-US;q=0.8,en;q=0.7',\n",
    "    'cache-control': 'max-age=0',\n",
    "    'upgrade-insecure-requests': '1',\n",
    "    'user-agent': 'Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36 OPR/62.0.3331.116',\n",
    "}\n",
    "\n",
    "session = requests.session()\n",
    "session.headers.update(HEADERS)\n",
    "\n",
    "def scrape_agent(session, player_id, wait=10):\n",
    "    time.sleep(wait)\n",
    "    try:\n",
    "        url = f\"https://www.transfermarkt.com/generic-player/profil/spieler/{player_id}\"\n",
    "        #print(url)\n",
    "        \n",
    "        resp = session.get(url)\n",
    "        #print(resp)\n",
    "\n",
    "        html = BeautifulSoup(resp.text)\n",
    "        \n",
    "        subsection = html.find(\"div\", class_=\"box viewport-tracking\").find(\"div\", class_=\"large-6 large-pull-6 small-12 columns spielerdatenundfakten\")\n",
    "        agent_section = subsection.find(\"span\", class_=\"info-table__content info-table__content--bold info-table__content--flex\")\n",
    "        try:\n",
    "            agent = re.findall(r'><span class=\"cp\" title=\"'+ \"(.*?)\" +'\">', str(agent_section))[0]\n",
    "        except IndexError:\n",
    "            agent = None\n",
    "        \n",
    "        # Se il primo metodo fallisce, tenta di estrarre il nome dell'agente dal tag <a> con onclick specifico\n",
    "        if agent is None:\n",
    "            agent_link = agent_section.find('a', onclick=re.compile(r'tmEvent\\(\\\"spielerprofil\\\"'))\n",
    "            if agent_link:\n",
    "                agent = agent_link.text.strip()\n",
    "        \n",
    "        if agent:\n",
    "            return agent\n",
    "        else:\n",
    "            return \"no_agent\"\n",
    "\n",
    "    except Exception as e:\n",
    "        print(f\"Error: {e}\")\n",
    "        return \"no_agent\""
   ]
  },
  {
   "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>league</th>\n",
       "      <th>season</th>\n",
       "      <th>window</th>\n",
       "      <th>team_id</th>\n",
       "      <th>team_name</th>\n",
       "      <th>team_country</th>\n",
       "      <th>dir</th>\n",
       "      <th>player_id</th>\n",
       "      <th>player_name</th>\n",
       "      <th>player_age</th>\n",
       "      <th>...</th>\n",
       "      <th>counter_team_name</th>\n",
       "      <th>counter_team_country</th>\n",
       "      <th>transfer_fee_amnt</th>\n",
       "      <th>market_val_amnt</th>\n",
       "      <th>is_free</th>\n",
       "      <th>is_loan</th>\n",
       "      <th>is_loan_end</th>\n",
       "      <th>is_retired</th>\n",
       "      <th>transfer_id</th>\n",
       "      <th>agent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>82377</th>\n",
       "      <td>NL1</td>\n",
       "      <td>2024</td>\n",
       "      <td>s</td>\n",
       "      <td>724</td>\n",
       "      <td>FC Volendam</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>left</td>\n",
       "      <td>1014243</td>\n",
       "      <td>Vivaldo Semedo</td>\n",
       "      <td>19.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Udinese Calcio</td>\n",
       "      <td>Italy</td>\n",
       "      <td>NaN</td>\n",
       "      <td>500.0</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>4844834</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82378</th>\n",
       "      <td>NL1</td>\n",
       "      <td>2024</td>\n",
       "      <td>s</td>\n",
       "      <td>724</td>\n",
       "      <td>FC Volendam</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>left</td>\n",
       "      <td>925584</td>\n",
       "      <td>Axel Guessand</td>\n",
       "      <td>19.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Udinese Calcio</td>\n",
       "      <td>Italy</td>\n",
       "      <td>NaN</td>\n",
       "      <td>500.0</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>4832825</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82379</th>\n",
       "      <td>NL1</td>\n",
       "      <td>2024</td>\n",
       "      <td>s</td>\n",
       "      <td>724</td>\n",
       "      <td>FC Volendam</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>left</td>\n",
       "      <td>746838</td>\n",
       "      <td>Zach Booth</td>\n",
       "      <td>20.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Leicester City U21</td>\n",
       "      <td>England</td>\n",
       "      <td>NaN</td>\n",
       "      <td>500.0</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>4623166</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82380</th>\n",
       "      <td>NL1</td>\n",
       "      <td>2024</td>\n",
       "      <td>s</td>\n",
       "      <td>724</td>\n",
       "      <td>FC Volendam</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>left</td>\n",
       "      <td>967948</td>\n",
       "      <td>Garang Kuol</td>\n",
       "      <td>19.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Newcastle United</td>\n",
       "      <td>England</td>\n",
       "      <td>NaN</td>\n",
       "      <td>700.0</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>4569682</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82381</th>\n",
       "      <td>NL1</td>\n",
       "      <td>2024</td>\n",
       "      <td>w</td>\n",
       "      <td>1304</td>\n",
       "      <td>Heracles Almelo</td>\n",
       "      <td>Moldova</td>\n",
       "      <td>in</td>\n",
       "      <td>240889</td>\n",
       "      <td>Anas Ouahim</td>\n",
       "      <td>27.0</td>\n",
       "      <td>...</td>\n",
       "      <td>FC Sheriff Tiraspol</td>\n",
       "      <td>Moldova</td>\n",
       "      <td>NaN</td>\n",
       "      <td>700.0</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>4923979</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      league  season window  team_id        team_name team_country   dir  \\\n",
       "82377    NL1    2024      s      724      FC Volendam      Belgium  left   \n",
       "82378    NL1    2024      s      724      FC Volendam      Belgium  left   \n",
       "82379    NL1    2024      s      724      FC Volendam      Belgium  left   \n",
       "82380    NL1    2024      s      724      FC Volendam      Belgium  left   \n",
       "82381    NL1    2024      w     1304  Heracles Almelo      Moldova    in   \n",
       "\n",
       "       player_id     player_name  player_age  ...    counter_team_name  \\\n",
       "82377    1014243  Vivaldo Semedo        19.0  ...       Udinese Calcio   \n",
       "82378     925584   Axel Guessand        19.0  ...       Udinese Calcio   \n",
       "82379     746838      Zach Booth        20.0  ...   Leicester City U21   \n",
       "82380     967948     Garang Kuol        19.0  ...     Newcastle United   \n",
       "82381     240889     Anas Ouahim        27.0  ...  FC Sheriff Tiraspol   \n",
       "\n",
       "      counter_team_country transfer_fee_amnt market_val_amnt is_free is_loan  \\\n",
       "82377                Italy               NaN           500.0   False    True   \n",
       "82378                Italy               NaN           500.0   False    True   \n",
       "82379              England               NaN           500.0   False    True   \n",
       "82380              England               NaN           700.0   False    True   \n",
       "82381              Moldova               NaN           700.0   False    True   \n",
       "\n",
       "       is_loan_end  is_retired  transfer_id  agent  \n",
       "82377         True       False      4844834   None  \n",
       "82378         True       False      4832825   None  \n",
       "82379         True       False      4623166   None  \n",
       "82380         True       False      4569682   None  \n",
       "82381         True       False      4923979   None  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp = df[-5:]\n",
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# temp[\"agent\"] = temp[\"player_id\"].progress_apply(lambda x: scrape_agent(session, x, 5))\n",
    "# temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df[\"is_free\"] == False]\n",
    "df = df[df[\"is_loan\"] == False]\n",
    "df = df[df[\"is_loan_end\"] == False]\n",
    "df = df[df[\"is_retired\"] == False]\n",
    "# df = df[df[\"season\"].isin([2024, 2023,2022,2021,2020])]\n",
    "df_unique = df.groupby('player_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[33544 62049 43261 73538  8198 42411 64484 15449  3881  5957]\n"
     ]
    }
   ],
   "source": [
    "unique_player_ids = df[\"player_id\"].unique()\n",
    "print(unique_player_ids[:10])\n",
    "def scrape_agent_wrapper(player_id):\n",
    "    return player_id, scrape_agent(session, player_id, 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 28%|██▊       | 3790/13422 [24:34<1:16:39,  2.09it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error: 'NoneType' object has no attribute 'find'\n",
      "Error: 'NoneType' object has no attribute 'find'\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 28%|██▊       | 3802/13422 [24:37<55:12,  2.90it/s]  "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error: 'NoneType' object has no attribute 'find'\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 43%|████▎     | 5722/13422 [38:04<35:05,  3.66it/s]  "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error: 'NoneType' object has no attribute 'find'\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 91%|█████████ | 12164/13422 [1:19:55<05:55,  3.54it/s] "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error: 'NoneType' object has no attribute 'find'\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 13422/13422 [1:28:08<00:00,  2.54it/s]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>league</th>\n",
       "      <th>season</th>\n",
       "      <th>window</th>\n",
       "      <th>team_id</th>\n",
       "      <th>team_name</th>\n",
       "      <th>team_country</th>\n",
       "      <th>dir</th>\n",
       "      <th>player_id</th>\n",
       "      <th>player_name</th>\n",
       "      <th>player_age</th>\n",
       "      <th>...</th>\n",
       "      <th>counter_team_name</th>\n",
       "      <th>counter_team_country</th>\n",
       "      <th>transfer_fee_amnt</th>\n",
       "      <th>market_val_amnt</th>\n",
       "      <th>is_free</th>\n",
       "      <th>is_loan</th>\n",
       "      <th>is_loan_end</th>\n",
       "      <th>is_retired</th>\n",
       "      <th>transfer_id</th>\n",
       "      <th>agent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GB1</td>\n",
       "      <td>2009</td>\n",
       "      <td>s</td>\n",
       "      <td>985</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>England</td>\n",
       "      <td>in</td>\n",
       "      <td>33544</td>\n",
       "      <td>Antonio Valencia</td>\n",
       "      <td>23.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Wigan Athletic</td>\n",
       "      <td>England</td>\n",
       "      <td>18800000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>310832</td>\n",
       "      <td>JP PACHON SPORTS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GB1</td>\n",
       "      <td>2009</td>\n",
       "      <td>s</td>\n",
       "      <td>985</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>England</td>\n",
       "      <td>in</td>\n",
       "      <td>62049</td>\n",
       "      <td>Mame Diouf</td>\n",
       "      <td>21.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Molde FK</td>\n",
       "      <td>Norway</td>\n",
       "      <td>4500000.0</td>\n",
       "      <td>1600000.0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>319841</td>\n",
       "      <td>Amin Sakman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>GB1</td>\n",
       "      <td>2009</td>\n",
       "      <td>s</td>\n",
       "      <td>985</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>England</td>\n",
       "      <td>in</td>\n",
       "      <td>43261</td>\n",
       "      <td>Gabriel Obertan</td>\n",
       "      <td>20.0</td>\n",
       "      <td>...</td>\n",
       "      <td>FC Girondins Bordeaux</td>\n",
       "      <td>France</td>\n",
       "      <td>4000000.0</td>\n",
       "      <td>400000.0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>315185</td>\n",
       "      <td>JL Sport Consulting</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>GB1</td>\n",
       "      <td>2009</td>\n",
       "      <td>s</td>\n",
       "      <td>985</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>England</td>\n",
       "      <td>in</td>\n",
       "      <td>73538</td>\n",
       "      <td>Scott Moffatt</td>\n",
       "      <td>18.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Manchester United U18</td>\n",
       "      <td>England</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>339015</td>\n",
       "      <td>no_agent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>GB1</td>\n",
       "      <td>2009</td>\n",
       "      <td>s</td>\n",
       "      <td>985</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>England</td>\n",
       "      <td>left</td>\n",
       "      <td>8198</td>\n",
       "      <td>Cristiano Ronaldo</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Real Madrid</td>\n",
       "      <td>Spain</td>\n",
       "      <td>94000000.0</td>\n",
       "      <td>45000000.0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>308498</td>\n",
       "      <td>Gestifute</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82371</th>\n",
       "      <td>NL1</td>\n",
       "      <td>2024</td>\n",
       "      <td>s</td>\n",
       "      <td>724</td>\n",
       "      <td>FC Volendam</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>left</td>\n",
       "      <td>672100</td>\n",
       "      <td>Darius Johnson</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Without Club</td>\n",
       "      <td>Without Club</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250.0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>4958717</td>\n",
       "      <td>Sporting Talent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82372</th>\n",
       "      <td>NL1</td>\n",
       "      <td>2024</td>\n",
       "      <td>s</td>\n",
       "      <td>724</td>\n",
       "      <td>FC Volendam</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>left</td>\n",
       "      <td>508642</td>\n",
       "      <td>Ibrahim El Kadiri</td>\n",
       "      <td>22.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Without Club</td>\n",
       "      <td>Without Club</td>\n",
       "      <td>NaN</td>\n",
       "      <td>300.0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>4958716</td>\n",
       "      <td>ESA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82373</th>\n",
       "      <td>NL1</td>\n",
       "      <td>2024</td>\n",
       "      <td>s</td>\n",
       "      <td>724</td>\n",
       "      <td>FC Volendam</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>left</td>\n",
       "      <td>638666</td>\n",
       "      <td>Josh Flint</td>\n",
       "      <td>23.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Without Club</td>\n",
       "      <td>Without Club</td>\n",
       "      <td>NaN</td>\n",
       "      <td>450.0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>4958707</td>\n",
       "      <td>CAA Base Ltd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82374</th>\n",
       "      <td>NL1</td>\n",
       "      <td>2024</td>\n",
       "      <td>s</td>\n",
       "      <td>724</td>\n",
       "      <td>FC Volendam</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>left</td>\n",
       "      <td>474838</td>\n",
       "      <td>Brian Plat</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Without Club</td>\n",
       "      <td>Without Club</td>\n",
       "      <td>NaN</td>\n",
       "      <td>600.0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>4958706</td>\n",
       "      <td>no_agent</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82375</th>\n",
       "      <td>NL1</td>\n",
       "      <td>2024</td>\n",
       "      <td>s</td>\n",
       "      <td>724</td>\n",
       "      <td>FC Volendam</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>left</td>\n",
       "      <td>257986</td>\n",
       "      <td>Damon Mirani</td>\n",
       "      <td>28.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Without Club</td>\n",
       "      <td>Without Club</td>\n",
       "      <td>NaN</td>\n",
       "      <td>550.0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>4958703</td>\n",
       "      <td>PUM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>26867 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      league  season window  team_id          team_name team_country   dir  \\\n",
       "0        GB1    2009      s      985  Manchester United      England    in   \n",
       "1        GB1    2009      s      985  Manchester United      England    in   \n",
       "2        GB1    2009      s      985  Manchester United      England    in   \n",
       "4        GB1    2009      s      985  Manchester United      England    in   \n",
       "12       GB1    2009      s      985  Manchester United      England  left   \n",
       "...      ...     ...    ...      ...                ...          ...   ...   \n",
       "82371    NL1    2024      s      724        FC Volendam      Belgium  left   \n",
       "82372    NL1    2024      s      724        FC Volendam      Belgium  left   \n",
       "82373    NL1    2024      s      724        FC Volendam      Belgium  left   \n",
       "82374    NL1    2024      s      724        FC Volendam      Belgium  left   \n",
       "82375    NL1    2024      s      724        FC Volendam      Belgium  left   \n",
       "\n",
       "       player_id        player_name  player_age  ...      counter_team_name  \\\n",
       "0          33544   Antonio Valencia        23.0  ...         Wigan Athletic   \n",
       "1          62049         Mame Diouf        21.0  ...               Molde FK   \n",
       "2          43261    Gabriel Obertan        20.0  ...  FC Girondins Bordeaux   \n",
       "4          73538      Scott Moffatt        18.0  ...  Manchester United U18   \n",
       "12          8198  Cristiano Ronaldo        24.0  ...            Real Madrid   \n",
       "...          ...                ...         ...  ...                    ...   \n",
       "82371     672100     Darius Johnson        24.0  ...           Without Club   \n",
       "82372     508642  Ibrahim El Kadiri        22.0  ...           Without Club   \n",
       "82373     638666         Josh Flint        23.0  ...           Without Club   \n",
       "82374     474838         Brian Plat        24.0  ...           Without Club   \n",
       "82375     257986       Damon Mirani        28.0  ...           Without Club   \n",
       "\n",
       "      counter_team_country transfer_fee_amnt market_val_amnt is_free is_loan  \\\n",
       "0                  England        18800000.0             NaN   False   False   \n",
       "1                   Norway         4500000.0       1600000.0   False   False   \n",
       "2                   France         4000000.0        400000.0   False   False   \n",
       "4                  England               NaN             NaN   False   False   \n",
       "12                   Spain        94000000.0      45000000.0   False   False   \n",
       "...                    ...               ...             ...     ...     ...   \n",
       "82371         Without Club               NaN           250.0   False   False   \n",
       "82372         Without Club               NaN           300.0   False   False   \n",
       "82373         Without Club               NaN           450.0   False   False   \n",
       "82374         Without Club               NaN           600.0   False   False   \n",
       "82375         Without Club               NaN           550.0   False   False   \n",
       "\n",
       "       is_loan_end  is_retired  transfer_id                agent  \n",
       "0            False       False       310832     JP PACHON SPORTS  \n",
       "1            False       False       319841          Amin Sakman  \n",
       "2            False       False       315185  JL Sport Consulting  \n",
       "4            False       False       339015             no_agent  \n",
       "12           False       False       308498            Gestifute  \n",
       "...            ...         ...          ...                  ...  \n",
       "82371        False       False      4958717      Sporting Talent  \n",
       "82372        False       False      4958716                  ESA  \n",
       "82373        False       False      4958707         CAA Base Ltd  \n",
       "82374        False       False      4958706             no_agent  \n",
       "82375        False       False      4958703                  PUM  \n",
       "\n",
       "[26867 rows x 24 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with concurrent.futures.ThreadPoolExecutor() as executor:\n",
    "    results = list(tqdm(executor.map(scrape_agent_wrapper, unique_player_ids), total=len(unique_player_ids)))\n",
    "agent_dict = dict(results)\n",
    "df['agent'] = df['player_id'].map(agent_dict)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"dataset/transfers_with_agents.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['JP PACHON SPORTS', 'Amin Sakman', 'JL Sport Consulting', ...,\n",
       "       'Meforyo Sportsmanagement', 'Dries Management',\n",
       "       'Forwardzone Football Limited'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"agent\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "agent\n",
       "no_agent                        8618\n",
       "Wasserman                        557\n",
       "CAA Stellar                      424\n",
       "Unique Sports Group              329\n",
       "Gestifute                        271\n",
       "                                ... \n",
       "Bpoint                             1\n",
       "Rabona Sports                      1\n",
       "Youonegoal                         1\n",
       "Mostar Sports Management           1\n",
       "Forwardzone Football Limited       1\n",
       "Name: count, Length: 2097, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[\"agent\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}