euros_2024_web_scraping.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c2a5ed37",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: mysql-connector-python in /Users/ayannair/anaconda3/lib/python3.11/site-packages (8.3.0)\r\n"
     ]
    }
   ],
   "source": [
    "!pip install mysql-connector-python #doesn't need to be ran anymore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "06e0f661",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "from bs4 import BeautifulSoup\n",
    "import mysql.connector"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8ff20a8",
   "metadata": {},
   "source": [
    "**Array of Qualifying countries**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "1a8fb236",
   "metadata": {},
   "outputs": [],
   "source": [
    "cnx = mysql.connector.connect(user = 'wsa', host = '34.68.250.121', database = 'Tutorials-Winter2024', \n",
    "                              password = 'LeBron>MJ!')\n",
    "cursor = cnx.cursor(buffered = True)\n",
    "qualifying_countries = [\"Albania\", \"Austria\", \"Belgium\", \"Croatia\", \"Czech Republic\", \"Denmark\", \"England\", \"France\", \"Germany\", \"Georgia\", \"Hungary\", \"Italy\", \"Netherlands\", \"Poland\", \"Portugal\", \"Romania\", \"Scotland\", \"Serbia\", \"Slovakia\", \"Slovenia\", \"Spain\", \"Switzerland\", \"Türkiye\", \"Ukraine\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ba688a2",
   "metadata": {},
   "source": [
    "**Euros 2021 Database Scraping**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6e5342c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Austria', 4.3, 53.5, 1.15, 1.15, 1.17, -0.02]\n",
      "['Belgium', 5.0, 53.8, 1.6, 1.2, 1.18, 0.42]\n",
      "['Croatia', 4.3, 50.0, 1.38, 1.15, 1.27, 0.11]\n",
      "['Czech Republic', 5.0, 48.2, 1.2, 0.8, 1.23, -0.03]\n",
      "['Denmark', 6.3, 53.3, 1.89, 1.26, 1.57, 0.32]\n",
      "['England', 7.7, 50.9, 1.3, 1.04, 1.44, -0.14]\n",
      "['France', 4.3, 52.8, 1.38, 0.46, 1.72, -0.34]\n",
      "['Germany', 4.0, 61.8, 1.0, 0.75, 1.77, -0.77]\n",
      "['Hungary', 3.0, 30.0, 1.0, 1.0, 0.48, 0.52]\n",
      "['Italy', 8.0, 54.7, 1.5, 1.13, 1.64, -0.14]\n",
      "['Netherlands', 4.0, 54.8, 2.0, 1.25, 2.15, -0.15]\n",
      "['Poland', 3.0, 49.3, 1.33, 1.33, 1.55, -0.22]\n",
      "['Portugal', 4.0, 54.3, 1.75, 1.0, 1.97, -0.22]\n",
      "['Scotland', 3.0, 43.7, 0.33, 0.0, 1.3, -0.97]\n",
      "['Slovakia', 3.0, 45.0, 0.33, 0.33, 0.34, -0.01]\n",
      "['Spain', 7.0, 72.0, 1.43, 1.29, 2.38, -0.95]\n",
      "['Switzerland', 5.7, 48.0, 1.41, 1.41, 1.26, 0.15]\n",
      "['Türkiye', 3.0, 49.0, 0.33, 0.33, 1.01, -0.68]\n",
      "['Ukraine', 5.3, 48.4, 1.13, 1.13, 1.17, -0.04]\n"
     ]
    }
   ],
   "source": [
    "url = requests.get('https://fbref.com/en/comps/676/2021/stats/2021-European-Championship-Stats.html')\n",
    "\n",
    "soup = BeautifulSoup(url.text, 'html.parser')\n",
    "#print(soup)\n",
    "#soup.prettify()\n",
    "\n",
    "table = soup.find('div', attrs = {'id' : 'div_stats_squads_standard_for'}).find('table').find('tbody')\n",
    "#print(table)\n",
    "\n",
    "rows = table.find_all('tr')\n",
    "\n",
    "for row in rows:\n",
    "    #print(row)\n",
    "    #print('-----------')\n",
    "    columns = row.find_all('td')\n",
    "    \n",
    "    \n",
    "    if len(columns) > 0:\n",
    "        squad = row.find('th').find('a').text\n",
    "        if squad in qualifying_countries:\n",
    "        \n",
    "            played90s = float(columns[6].text)\n",
    "\n",
    "            #progcarry = float(columns[19].text)\n",
    "            #progpass = float(columns[20].text)\n",
    "            avgpossession = float(columns[2].text)\n",
    "\n",
    "            #progcarryx90 = round(progcarry/played90s, 2)\n",
    "            #progpassx90 = round(progpass/played90s, 2)\n",
    "\n",
    "            goalsx90 = float(columns[21].text)\n",
    "            assistsx90 = float(columns[22].text)\n",
    "            expgoalsx90 = float(columns[26].text)\n",
    "            #expassistsx90 = float(columns[27].text)\n",
    "\n",
    "            diffgoalx90 = round(goalsx90 - expgoalsx90, 2)\n",
    "            #diffassistx90 = round(assistsx90 - expassistsx90, 2)\n",
    "        \n",
    "            values = [squad, played90s, avgpossession, goalsx90, assistsx90, expgoalsx90, diffgoalx90]\n",
    "            print(values)\n",
    "            statement = \"INSERT INTO EURO_2021 (squad, played90s, avgpossession, goalsx90, assistsx90, expgoalsx90, diffgoalx90) VALUES (%s, %s, %s, %s, %s, %s, %s)\"\n",
    "            cursor.execute(statement, values)\n",
    "            cnx.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f904515c",
   "metadata": {},
   "source": [
    "**Nations League 2022/23 Database Scraping**\n",
    "\n",
    "Note: no progcarries/progpasses or expected goals/assists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a776afa8",
   "metadata": {},
   "outputs": [],
   "source": [
    "expgoals_nations2023 = [4.6,6.5,10.9,11.4,8.7,7.6,8.5,9.3,9.3,14.1,8.3,11.9,13.5,5.5,10.8,8.6,10.3,9.9,6.6,6.1,11.8,6.7,16.4,8.5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "86c5eceb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Albania', 4.0, 54.5, 1.0, 0.5, 1.15, -0.15]\n",
      "['Austria', 6.0, 43.5, 1.0, 1.0, 1.08, -0.08]\n",
      "['Belgium', 6.0, 62.3, 1.83, 1.83, 1.82, 0.01]\n",
      "['Croatia', 8.7, 51.3, 1.38, 0.69, 1.31, 0.07]\n",
      "['Czech Republic', 6.0, 35.0, 0.67, 0.5, 1.45, -0.78]\n",
      "['Denmark', 6.0, 47.7, 1.5, 1.33, 1.27, 0.23]\n",
      "['England', 6.0, 53.3, 0.67, 0.33, 1.42, -0.75]\n",
      "['France', 6.0, 57.7, 0.83, 0.67, 1.55, -0.72]\n",
      "['Georgia', 6.0, 52.2, 2.33, 1.67, 1.55, 0.78]\n",
      "['Germany', 6.0, 65.7, 1.83, 1.17, 2.35, -0.52]\n",
      "['Hungary', 6.0, 38.5, 1.17, 1.0, 1.38, -0.21]\n",
      "['Italy', 8.0, 41.8, 1.5, 1.25, 1.49, 0.01]\n",
      "['Netherlands', 8.3, 55.8, 2.16, 1.68, 1.63, 0.53]\n",
      "['Poland', 6.0, 40.3, 1.0, 0.83, 0.92, 0.08]\n",
      "['Portugal', 6.0, 50.7, 1.83, 1.83, 1.8, 0.03]\n",
      "['Romania', 6.0, 49.3, 1.0, 1.0, 1.43, -0.43]\n",
      "['Scotland', 6.0, 59.0, 1.83, 1.5, 1.72, 0.11]\n",
      "['Serbia', 6.0, 57.8, 2.17, 2.17, 1.65, 0.52]\n",
      "['Slovakia', 6.0, 69.3, 0.83, 0.5, 1.1, -0.27]\n",
      "['Slovenia', 6.0, 41.3, 1.0, 0.83, 1.02, -0.02]\n",
      "['Spain', 8.3, 67.8, 1.2, 0.84, 1.42, -0.22]\n",
      "['Switzerland', 6.0, 43.7, 1.0, 0.83, 1.12, -0.12]\n",
      "['Türkiye', 6.0, 66.2, 2.83, 1.83, 2.73, 0.1]\n",
      "['Ukraine', 6.0, 59.8, 1.67, 1.17, 1.42, 0.25]\n"
     ]
    }
   ],
   "source": [
    "url = requests.get('https://fbref.com/en/comps/677/stats/UEFA-Nations-League-Stats.html')\n",
    "\n",
    "soup = BeautifulSoup(url.text, 'html.parser')\n",
    "#print(soup)\n",
    "#soup.prettify()\n",
    "\n",
    "table = soup.find('div', attrs = {'id' : 'switcher_stats_squads_standard'}).find('table').find('tbody')\n",
    "#print(table)\n",
    "\n",
    "rows = table.find_all('tr')\n",
    "i = 0;\n",
    "\n",
    "for row in rows:\n",
    "    #print(row)\n",
    "    #print('-----------')\n",
    "    columns = row.find_all('td')\n",
    "    \n",
    "    if len(columns) > 0:\n",
    "        squad = row.find('th').find('a').text\n",
    "        if squad in qualifying_countries:\n",
    "        \n",
    "            played90s = float(columns[6].text)\n",
    "            avgpossession = float(columns[2].text)\n",
    "\n",
    "            #progcarry = float(columns[19].text)\n",
    "            #progpass = float(columns[20].text)\n",
    "\n",
    "            #progcarryx90 = round(progcarry/played90s, 2)\n",
    "            #progpassx90 = round(progpass/played90s, 2)\n",
    "\n",
    "            goalsx90 = float(columns[15].text)\n",
    "            assistsx90 = float(columns[16].text)\n",
    "            expgoalsx90 = round(expgoals_nations2023[i]/played90s,2)\n",
    "            #expgoalsx90 = float(columns[26].text)\n",
    "            #expassistsx90 = float(columns[27].text)\n",
    "\n",
    "            diffgoalx90 = round(goalsx90 - expgoalsx90, 2)\n",
    "            #diffassistx90 = round(assistsx90 - expassistsx90, 2)\n",
    "\n",
    "            values = [squad, played90s, avgpossession, goalsx90, assistsx90,expgoalsx90,diffgoalx90]\n",
    "            print(values)\n",
    "            i+=1\n",
    "            statement = \"INSERT INTO NL_2022_Stats (squad, played90s, avgpossession, goalsx90, assistsx90, expgoalsx90, diffgoalx90) VALUES (%s, %s, %s, %s, %s, %s, %s)\"\n",
    "            cursor.execute(statement, values)\n",
    "            cnx.commit()\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5970399c",
   "metadata": {},
   "source": [
    "**World Cup 2022 Database Scraping**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "1878f7d6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Belgium', 3.0, 57.0, 0.33, 0.33, 1.57, -1.24]\n",
      "['Croatia', 7.7, 54.3, 1.04, 1.04, 0.91, 0.13]\n",
      "['Denmark', 3.0, 60.0, 0.33, 0.33, 0.9, -0.57]\n",
      "['England', 5.0, 62.8, 2.6, 2.2, 1.72, 0.88]\n",
      "['France', 7.3, 51.3, 2.18, 1.64, 1.88, 0.3]\n",
      "['Germany', 3.0, 59.3, 2.0, 1.67, 3.35, -1.35]\n",
      "['Netherlands', 5.3, 53.0, 1.87, 1.5, 0.86, 1.01]\n",
      "['Poland', 4.0, 37.3, 0.75, 0.25, 1.14, -0.39]\n",
      "['Portugal', 5.0, 60.4, 2.4, 2.0, 1.57, 0.83]\n",
      "['Serbia', 3.0, 51.3, 1.67, 1.33, 1.12, 0.55]\n",
      "['Spain', 4.3, 75.8, 2.08, 1.15, 1.42, 0.66]\n",
      "['Switzerland', 4.0, 49.0, 1.25, 1.0, 1.37, -0.12]\n"
     ]
    }
   ],
   "source": [
    "url = requests.get('https://fbref.com/en/comps/1/stats/World-Cup-Stats.html')\n",
    "\n",
    "soup = BeautifulSoup(url.text, 'html.parser')\n",
    "#print(soup)\n",
    "#soup.prettify()\n",
    "\n",
    "table = soup.find('div', attrs = {'id' : 'div_stats_squads_standard_for'}).find('table').find('tbody')\n",
    "#print(table)\n",
    "\n",
    "rows = table.find_all('tr')\n",
    "\n",
    "european_countries = ['Belgium', 'Croatia', 'Denmark', 'England', 'Finland', 'France', 'Germany', 'Netherlands', 'Poland', 'Portugal', 'Serbia', 'Spain', 'Switzerland', 'Wales']\n",
    "\n",
    "for row in rows:\n",
    "    #print(row)\n",
    "    #print('-----------')\n",
    "    columns = row.find_all('td')\n",
    "    \n",
    "    \n",
    "    if len(columns) > 0:\n",
    "        squad = row.find('th').find('a').text\n",
    "        if squad in qualifying_countries:\n",
    "            played90s = float(columns[6].text)\n",
    "        \n",
    "            #progcarry = float(columns[19].text)\n",
    "            #progpass = float(columns[20].text)\n",
    "        \n",
    "            #progcarryx90 = round(progcarry/played90s, 2)\n",
    "            #progpassx90 = round(progpass/played90s, 2)\n",
    "            avgpossession = float(columns[2].text)\n",
    "        \n",
    "            goalsx90 = float(columns[21].text)\n",
    "            assistsx90 = float(columns[22].text)\n",
    "            expgoalsx90 = float(columns[26].text)\n",
    "            #expassistsx90 = float(columns[27].text)\n",
    "        \n",
    "            diffgoalx90 = round(goalsx90 - expgoalsx90, 2)\n",
    "            #diffassistx90 = round(assistsx90 - expassistsx90, 2)\n",
    "            \n",
    "            values = [squad, played90s, avgpossession, goalsx90, assistsx90, expgoalsx90, diffgoalx90]\n",
    "            print(values)\n",
    "            \n",
    "            statement = \"INSERT INTO WC_2022_Stats (squad, played90s, avgpossession, goalsx90, assistsx90, expgoalsx90, diffgoalx90) VALUES (%s, %s, %s, %s, %s, %s, %s)\"\n",
    "            cursor.execute(statement, values)\n",
    "            cnx.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86108aec",
   "metadata": {},
   "source": [
    "**World Cup 2018 Database Scraping**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "76d193f5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Belgium', 7.0, 52.9, 2.14, 1.71, 1.9, 0.24]\n",
      "['Croatia', 8.0, 56.7, 1.62, 1.0, 1.4, 0.22]\n",
      "['Denmark', 4.3, 43.0, 0.69, 0.69, 0.68, 0.01]\n",
      "['England', 7.7, 54.1, 1.57, 0.78, 1.46, 0.11]\n",
      "['France', 7.0, 48.3, 1.71, 0.86, 1.3, 0.41]\n",
      "['Germany', 3.0, 71.7, 0.67, 0.67, 1.85, -1.18]\n",
      "['Portugal', 4.0, 54.8, 1.5, 1.0, 1.21, 0.29]\n",
      "['Serbia', 3.0, 42.7, 0.67, 0.33, 1.07, -0.4]\n",
      "['Spain', 4.3, 74.0, 1.38, 0.69, 1.89, -0.51]\n",
      "['Switzerland', 4.0, 59.3, 1.25, 1.0, 1.27, -0.02]\n"
     ]
    }
   ],
   "source": [
    "url = requests.get('https://fbref.com/en/comps/1/2018/stats/2018-World-Cup-Stats')\n",
    "\n",
    "soup = BeautifulSoup(url.text, 'html.parser')\n",
    "#print(soup)\n",
    "#soup.prettify()\n",
    "\n",
    "table = soup.find('div', attrs = {'id' : 'div_stats_squads_standard_for'}).find('table').find('tbody')\n",
    "#print(table)\n",
    "\n",
    "rows = table.find_all('tr')\n",
    "\n",
    "european_countries = ['Belgium', 'Croatia', 'Denmark', 'England', 'Finland', 'France', 'Germany', 'Netherlands', 'Poland', 'Portugal', 'Serbia', 'Spain', 'Switzerland', 'Wales']\n",
    "\n",
    "for row in rows:\n",
    "    #print(row)\n",
    "    #print('-----------')\n",
    "    columns = row.find_all('td')\n",
    "    \n",
    "    \n",
    "    if len(columns) > 0:\n",
    "        squad = row.find('th').find('a').text\n",
    "        if squad in qualifying_countries:\n",
    "            played90s = float(columns[6].text)\n",
    "            avgpossession = float(columns[2].text)\n",
    "        \n",
    "            #progcarry = float(columns[19].text)\n",
    "            #progpass = float(columns[20].text)\n",
    "        \n",
    "            #progcarryx90 = round(progcarry/played90s, 2)\n",
    "            #progpassx90 = round(progpass/played90s, 2)\n",
    "        \n",
    "            goalsx90 = float(columns[21].text)\n",
    "            assistsx90 = float(columns[22].text)\n",
    "            expgoalsx90 = float(columns[26].text)\n",
    "            #expassistsx90 = float(columns[27].text)\n",
    "        \n",
    "            diffgoalx90 = round(goalsx90 - expgoalsx90, 2)\n",
    "            #diffassistx90 = round(assistsx90 - expassistsx90, 2)\n",
    "            \n",
    "            values = [squad, played90s, avgpossession, goalsx90, assistsx90, expgoalsx90, diffgoalx90]\n",
    "            print(values)\n",
    "            \n",
    "            statement = \"INSERT INTO WC_2018_Stats (squad, played90s, avgpossession, goalsx90, assistsx90, expgoalsx90, diffgoalx90) VALUES (%s, %s, %s, %s, %s, %s, %s)\"\n",
    "            cursor.execute(statement, values)\n",
    "            cnx.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed2ff0ad",
   "metadata": {},
   "source": [
    "**Euros 2016 Database Scraping**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a0a7f0e5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Albania', 3.0, 40.3, 0.33, 0.33]\n",
      "['Austria', 3.0, 54.0, 0.33, 0.33]\n",
      "['Belgium', 5.0, 52.4, 1.8, 1.6]\n",
      "['Croatia', 4.3, 48.8, 1.15, 0.92]\n",
      "['Czech Republic', 3.0, 42.3, 0.67, 0.33]\n",
      "['England', 4.0, 62.0, 1.0, 0.25]\n",
      "['France', 7.3, 54.4, 1.77, 1.5]\n",
      "['Germany', 6.3, 66.8, 1.11, 0.95]\n",
      "['Hungary', 4.0, 52.5, 1.25, 0.5]\n",
      "['Italy', 5.3, 44.8, 1.13, 0.94]\n",
      "['Poland', 5.7, 46.4, 0.71, 0.71]\n",
      "['Portugal', 8.0, 53.0, 1.13, 1.13]\n",
      "['Romania', 3.0, 44.3, 0.67, 0.0]\n",
      "['Slovakia', 4.0, 43.5, 0.75, 0.75]\n",
      "['Spain', 4.0, 65.0, 1.25, 1.25]\n",
      "['Switzerland', 4.3, 59.5, 0.69, 0.69]\n",
      "['Türkiye', 3.0, 45.3, 0.67, 0.67]\n",
      "['Ukraine', 3.0, 55.7, 0.0, 0.0]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "url = requests.get('https://fbref.com/en/comps/676/2016/stats/2016-European-Championship-Stats.html')\n",
    "\n",
    "soup = BeautifulSoup(url.text, 'html.parser')\n",
    "#print(soup)\n",
    "#soup.prettify()\n",
    "\n",
    "table = soup.find('div', attrs = {'id' : 'div_stats_squads_standard_for'}).find('table').find('tbody')\n",
    "#print(table)\n",
    "\n",
    "rows = table.find_all('tr')\n",
    "\n",
    "for row in rows:\n",
    "    #print(row)\n",
    "    #print('-----------')\n",
    "    columns = row.find_all('td')\n",
    "    \n",
    "    \n",
    "    if len(columns) > 0:\n",
    "        squad = row.find('th').find('a').text\n",
    "        if squad in qualifying_countries:\n",
    "        \n",
    "            played90s = float(columns[6].text)\n",
    "            avgpossession = float(columns[2].text)\n",
    "\n",
    "            #progcarry = float(columns[19].text)\n",
    "            #progpass = float(columns[20].text)\n",
    "\n",
    "            #progcarryx90 = round(progcarry/played90s, 2)\n",
    "            #progpassx90 = round(progpass/played90s, 2)\n",
    "\n",
    "            goalsx90 = float(columns[15].text)\n",
    "            assistsx90 = float(columns[16].text)\n",
    "            #expgoalsx90 = float(columns[26].text)\n",
    "            #expassistsx90 = float(columns[27].text)\n",
    "\n",
    "            #diffgoalx90 = round(goalsx90 - expgoalsx90, 2)\n",
    "            #diffassistx90 = round(assistsx90 - expassistsx90, 2)\n",
    "        \n",
    "            values = [squad, played90s, avgpossession, goalsx90, assistsx90]\n",
    "            print(values)\n",
    "            \n",
    "            statement = \"INSERT INTO Euro2016_Stats_Monkey (squad, played90s, avgpossession, goalsx90, assistsx90) VALUES (%s, %s, %s, %s, %s)\"\n",
    "            cursor.execute(statement, values)\n",
    "            cnx.commit()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c878cb8d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}