predict_euros.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pickle\n",
    "from scipy.stats import poisson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_table = pickle.load(open('dict_table', 'rb'))\n",
    "df_historical_data = pd.read_csv('clean_Uefa_euros_historical_data.csv')\n",
    "df_fixture = pd.read_csv('clean_Uefa_euros_fixture.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Calculate Team Strength"
   ]
  },
  {
   "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>HomeTeam</th>\n",
       "      <th>AwayTeam</th>\n",
       "      <th>Year</th>\n",
       "      <th>HomeGoals</th>\n",
       "      <th>AwayGoals</th>\n",
       "      <th>TotalGoals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Czechoslovakia</td>\n",
       "      <td>West Germany</td>\n",
       "      <td>1980</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>West Germany</td>\n",
       "      <td>1980</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Czechoslovakia</td>\n",
       "      <td>Italy</td>\n",
       "      <td>1980</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Italy</td>\n",
       "      <td>Belgium</td>\n",
       "      <td>1980</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>England</td>\n",
       "      <td>Italy</td>\n",
       "      <td>1980</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         HomeTeam      AwayTeam  Year  HomeGoals  AwayGoals  TotalGoals\n",
       "0  Czechoslovakia  West Germany  1980          0          1           1\n",
       "1         Belgium  West Germany  1980          1          2           3\n",
       "2  Czechoslovakia         Italy  1980          1          1           2\n",
       "3           Italy       Belgium  1980          0          0           0\n",
       "4         England         Italy  1980          0          1           1"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_historical_data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_home = df_historical_data[['HomeTeam', 'HomeGoals', 'AwayGoals']]\n",
    "df_away = df_historical_data[['AwayTeam', 'HomeGoals', 'AwayGoals']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_home = df_home.rename(columns={'HomeTeam': 'Team', 'HomeGoals': 'GoalsScored', 'AwayGoals': 'GoalsConceded'})\n",
    "df_away = df_away.rename(columns={'AwayTeam': 'Team', 'HomeGoals': 'GoalsConceded', 'AwayGoals': 'GoalsScored'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>GoalsScored</th>\n",
       "      <th>GoalsConceded</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Team</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Albania</th>\n",
       "      <td>0.333333</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Austria</th>\n",
       "      <td>0.700000</td>\n",
       "      <td>1.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Belgium</th>\n",
       "      <td>1.400000</td>\n",
       "      <td>1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bulgaria</th>\n",
       "      <td>0.666667</td>\n",
       "      <td>2.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CIS</th>\n",
       "      <td>0.333333</td>\n",
       "      <td>1.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Croatia</th>\n",
       "      <td>1.363636</td>\n",
       "      <td>1.272727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Czech Republic</th>\n",
       "      <td>1.241379</td>\n",
       "      <td>1.275862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Czechoslovakia</th>\n",
       "      <td>1.250000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Denmark</th>\n",
       "      <td>1.322581</td>\n",
       "      <td>1.419355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>England</th>\n",
       "      <td>1.361111</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FR Yugoslavia</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Finland</th>\n",
       "      <td>0.333333</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>France</th>\n",
       "      <td>1.585366</td>\n",
       "      <td>1.048780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Germany</th>\n",
       "      <td>1.394737</td>\n",
       "      <td>1.105263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Greece</th>\n",
       "      <td>0.875000</td>\n",
       "      <td>1.250000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungary</th>\n",
       "      <td>1.285714</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iceland</th>\n",
       "      <td>1.600000</td>\n",
       "      <td>1.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Italy</th>\n",
       "      <td>1.166667</td>\n",
       "      <td>0.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Latvia</th>\n",
       "      <td>0.333333</td>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Netherlands</th>\n",
       "      <td>1.648649</td>\n",
       "      <td>0.972973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Macedonia</th>\n",
       "      <td>0.666667</td>\n",
       "      <td>2.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Northern Ireland</th>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Norway</th>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Poland</th>\n",
       "      <td>0.785714</td>\n",
       "      <td>1.071429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Portugal</th>\n",
       "      <td>1.435897</td>\n",
       "      <td>0.974359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Republic of Ireland</th>\n",
       "      <td>0.600000</td>\n",
       "      <td>1.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Romania</th>\n",
       "      <td>0.625000</td>\n",
       "      <td>1.312500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russia</th>\n",
       "      <td>1.100000</td>\n",
       "      <td>1.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Scotland</th>\n",
       "      <td>0.555556</td>\n",
       "      <td>1.111111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slovakia</th>\n",
       "      <td>0.714286</td>\n",
       "      <td>1.857143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Slovenia</th>\n",
       "      <td>1.333333</td>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Soviet Union</th>\n",
       "      <td>1.400000</td>\n",
       "      <td>0.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spain</th>\n",
       "      <td>1.454545</td>\n",
       "      <td>0.909091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sweden</th>\n",
       "      <td>1.250000</td>\n",
       "      <td>1.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Switzerland</th>\n",
       "      <td>0.888889</td>\n",
       "      <td>1.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Turkey</th>\n",
       "      <td>0.777778</td>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ukraine</th>\n",
       "      <td>0.727273</td>\n",
       "      <td>1.727273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wales</th>\n",
       "      <td>1.300000</td>\n",
       "      <td>1.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Germany</th>\n",
       "      <td>1.272727</td>\n",
       "      <td>0.727273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yugoslavia</th>\n",
       "      <td>0.666667</td>\n",
       "      <td>3.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     GoalsScored  GoalsConceded\n",
       "Team                                           \n",
       "Albania                 0.333333       1.000000\n",
       "Austria                 0.700000       1.200000\n",
       "Belgium                 1.400000       1.250000\n",
       "Bulgaria                0.666667       2.166667\n",
       "CIS                     0.333333       1.333333\n",
       "Croatia                 1.363636       1.272727\n",
       "Czech Republic          1.241379       1.275862\n",
       "Czechoslovakia          1.250000       1.000000\n",
       "Denmark                 1.322581       1.419355\n",
       "England                 1.361111       1.000000\n",
       "FR Yugoslavia           2.000000       3.250000\n",
       "Finland                 0.333333       1.000000\n",
       "France                  1.585366       1.048780\n",
       "Germany                 1.394737       1.105263\n",
       "Greece                  0.875000       1.250000\n",
       "Hungary                 1.285714       2.000000\n",
       "Iceland                 1.600000       1.800000\n",
       "Italy                   1.166667       0.714286\n",
       "Latvia                  0.333333       1.666667\n",
       "Netherlands             1.648649       0.972973\n",
       "North Macedonia         0.666667       2.666667\n",
       "Northern Ireland        0.500000       0.750000\n",
       "Norway                  0.333333       0.333333\n",
       "Poland                  0.785714       1.071429\n",
       "Portugal                1.435897       0.974359\n",
       "Republic of Ireland     0.600000       1.700000\n",
       "Romania                 0.625000       1.312500\n",
       "Russia                  1.100000       1.800000\n",
       "Scotland                0.555556       1.111111\n",
       "Slovakia                0.714286       1.857143\n",
       "Slovenia                1.333333       1.666667\n",
       "Soviet Union            1.400000       0.800000\n",
       "Spain                   1.454545       0.909091\n",
       "Sweden                  1.250000       1.166667\n",
       "Switzerland             0.888889       1.333333\n",
       "Turkey                  0.777778       1.666667\n",
       "Ukraine                 0.727273       1.727273\n",
       "Wales                   1.300000       1.200000\n",
       "West Germany            1.272727       0.727273\n",
       "Yugoslavia              0.666667       3.333333"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_team_strength = pd.concat([df_home, df_away], ignore_index=True).groupby('Team').mean()\n",
    "\n",
    "df_team_strength"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Function Predict Points"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def predict_points(home, away):\n",
    "    if home in df_team_strength.index and away in df_team_strength.index:\n",
    "        # goals_scored * goals_conceded\n",
    "        lamb_home = df_team_strength.at[home,'GoalsScored'] * df_team_strength.at[away,'GoalsConceded']\n",
    "        lamb_away = df_team_strength.at[away,'GoalsScored'] * df_team_strength.at[home,'GoalsConceded']\n",
    "        prob_home, prob_away, prob_draw = 0, 0, 0\n",
    "        for x in range(0,11): #number of goals home team\n",
    "            for y in range(0, 11): #number of goals away team\n",
    "                p = poisson.pmf(x, lamb_home) * poisson.pmf(y, lamb_away)\n",
    "                if x == y:\n",
    "                    prob_draw += p\n",
    "                elif x > y:\n",
    "                    prob_home += p\n",
    "                else:\n",
    "                    prob_away += p\n",
    "        \n",
    "        points_home = 3 * prob_home + prob_draw\n",
    "        points_away = 3 * prob_away + prob_draw\n",
    "        return (points_home, points_away)\n",
    "    else:\n",
    "        return (0, 0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Predicting Euros"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Group Stage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_table['Group A']['Team'] = dict_table['Group A']['Team'].replace({'Germany (H)': 'Germany'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fixture_group_36 = df_fixture[:36].copy()\n",
    "df_fixture_knockout = df_fixture[36:44].copy()\n",
    "df_fixture_quarter = df_fixture[44:48].copy()\n",
    "df_fixture_semi = df_fixture[48:50].copy()\n",
    "df_fixture_final = df_fixture[50:].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for group in dict_table:\n",
    "    teams_in_group = dict_table[group]['Team'].values\n",
    "    df_fixture_group_6 = df_fixture_group_36[df_fixture_group_36['home'].isin(teams_in_group)]\n",
    "    for index, row in df_fixture_group_6.iterrows():\n",
    "        home, away = row['home'], row['away']\n",
    "        points_home, points_away = predict_points(home, away)\n",
    "        dict_table[group].loc[dict_table[group]['Team'] == home, 'Pts'] += points_home\n",
    "        dict_table[group].loc[dict_table[group]['Team'] == away, 'Pts'] += points_away\n",
    "\n",
    "    dict_table[group] = dict_table[group].sort_values('Pts', ascending=False).reset_index()\n",
    "    dict_table[group] = dict_table[group][['Team', 'Pts']]\n",
    "    dict_table[group] = dict_table[group].round(0)"
   ]
  },
  {
   "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>Team</th>\n",
       "      <th>Pts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Germany</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Hungary</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Scotland</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Team  Pts\n",
       "0      Germany  6.0\n",
       "1  Switzerland  4.0\n",
       "2      Hungary  4.0\n",
       "3     Scotland  3.0"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_table['Group A']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Best Losers "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "best_losers = []\n",
    "for group in dict_table:\n",
    "    third_place = dict_table[group].loc[2]\n",
    "    best_losers.append(third_place)\n",
    "\n",
    "df_best_loser = pd.DataFrame(best_losers)\n",
    "df_best_loser = df_best_loser.sort_values('Pts', ascending=False, ignore_index=True)\n",
    "df_best_loser = df_best_loser[:4]"
   ]
  },
  {
   "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>Team</th>\n",
       "      <th>Pts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hungary</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Croatia</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Poland</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ukraine</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Team  Pts\n",
       "0  Hungary  4.0\n",
       "1  Croatia  4.0\n",
       "2   Poland  3.0\n",
       "3  Ukraine  3.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_best_loser"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fixtures based on best loser"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_third_place_fixtures = pd.read_csv('third_place_fixtures.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Group A</th>\n",
       "      <th>Group B</th>\n",
       "      <th>Group C</th>\n",
       "      <th>Group D</th>\n",
       "      <th>Group E</th>\n",
       "      <th>Group F</th>\n",
       "      <th>1B vs</th>\n",
       "      <th>1C vs</th>\n",
       "      <th>1E vs</th>\n",
       "      <th>1F vs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3A</td>\n",
       "      <td>3D</td>\n",
       "      <td>3B</td>\n",
       "      <td>3C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3A</td>\n",
       "      <td>3E</td>\n",
       "      <td>3B</td>\n",
       "      <td>3C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>3A</td>\n",
       "      <td>3F</td>\n",
       "      <td>3B</td>\n",
       "      <td>3C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>E</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3D</td>\n",
       "      <td>3E</td>\n",
       "      <td>3A</td>\n",
       "      <td>3B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>3D</td>\n",
       "      <td>3F</td>\n",
       "      <td>3A</td>\n",
       "      <td>3B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>A</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "      <td>3E</td>\n",
       "      <td>3F</td>\n",
       "      <td>3B</td>\n",
       "      <td>3A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>E</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3E</td>\n",
       "      <td>3D</td>\n",
       "      <td>3C</td>\n",
       "      <td>3A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>3F</td>\n",
       "      <td>3D</td>\n",
       "      <td>3C</td>\n",
       "      <td>3A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "      <td>3E</td>\n",
       "      <td>3F</td>\n",
       "      <td>3C</td>\n",
       "      <td>3A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "      <td>3E</td>\n",
       "      <td>3F</td>\n",
       "      <td>3D</td>\n",
       "      <td>3A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>E</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3E</td>\n",
       "      <td>3D</td>\n",
       "      <td>3B</td>\n",
       "      <td>3C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>F</td>\n",
       "      <td>3F</td>\n",
       "      <td>3D</td>\n",
       "      <td>3C</td>\n",
       "      <td>3B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "      <td>3F</td>\n",
       "      <td>3E</td>\n",
       "      <td>3C</td>\n",
       "      <td>3B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>D</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "      <td>3F</td>\n",
       "      <td>3E</td>\n",
       "      <td>3D</td>\n",
       "      <td>3B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>E</td>\n",
       "      <td>F</td>\n",
       "      <td>3F</td>\n",
       "      <td>3E</td>\n",
       "      <td>3D</td>\n",
       "      <td>3C</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Group A Group B Group C Group D Group E Group F 1B vs 1C vs 1E vs 1F vs\n",
       "0        A       B       C       D     NaN     NaN    3A    3D    3B    3C\n",
       "1        A       B       C     NaN       E     NaN    3A    3E    3B    3C\n",
       "2        A       B       C     NaN     NaN       F    3A    3F    3B    3C\n",
       "3        A       B     NaN       D       E     NaN    3D    3E    3A    3B\n",
       "4        A       B     NaN       D     NaN       F    3D    3F    3A    3B\n",
       "5        A       B     NaN     NaN       E       F    3E    3F    3B    3A\n",
       "6        A     NaN       C       D       E     NaN    3E    3D    3C    3A\n",
       "7        A     NaN       C       D     NaN       F    3F    3D    3C    3A\n",
       "8        A     NaN       C     NaN       E       F    3E    3F    3C    3A\n",
       "9        A     NaN     NaN       D       E       F    3E    3F    3D    3A\n",
       "10     NaN       B       C       D       E     NaN    3E    3D    3B    3C\n",
       "11     NaN       B       C       D     NaN       F    3F    3D    3C    3B\n",
       "12     NaN       B       C     NaN       E       F    3F    3E    3C    3B\n",
       "13     NaN       B     NaN       D       E       F    3F    3E    3D    3B\n",
       "14     NaN     NaN       C       D       E       F    3F    3E    3D    3C"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_third_place_fixtures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Group A', 'Group B', 'Group D', 'Group E']"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract the groups that the best losers qualify from\n",
    "qualifying_groups = []\n",
    "\n",
    "# Iterate through each team in the provided list\n",
    "for team in df_best_loser['Team']:\n",
    "    # Check each group in dict_table to find the team\n",
    "    for group, df in dict_table.items():\n",
    "        if team in df['Team'].values:\n",
    "            qualifying_groups.append(group)\n",
    "            break \n",
    "\n",
    "\n",
    "qualifying_groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Group E'"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qualifying_groups[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['3A', '3D', '3B', '3C']\n"
     ]
    }
   ],
   "source": [
    "# Find the row in comb_table that matches the qualifying groups\n",
    "for index, row in df_third_place_fixtures.iterrows():\n",
    "    groups = row[:6].dropna().tolist()\n",
    "    if all(group in qualifying_groups for groups in groups):\n",
    "        third_place_combinations = row[6:].dropna().tolist()        \n",
    "        break\n",
    "print(third_place_combinations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'B'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "third_place_combinations[2][1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Winner Group B': '3rd Group A',\n",
       " 'Winner Group C': '3rd Group D',\n",
       " 'Winner Group E': '3rd Group B',\n",
       " 'Winner Group F': '3rd Group C'}"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "third_place_fixtures_dict = {'Winner Group B': f'3rd Group {third_place_combinations[0][1]}', \n",
    "                             'Winner Group C': f'3rd Group {third_place_combinations[1][1]}', \n",
    "                             'Winner Group E': f'3rd Group {third_place_combinations[2][1]}', \n",
    "                             'Winner Group F': f'3rd Group {third_place_combinations[3][1]}' }\n",
    "\n",
    "third_place_fixtures_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Runner-up Group A</td>\n",
       "      <td>Match 38</td>\n",
       "      <td>Runner-up Group B</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Winner Group A</td>\n",
       "      <td>Match 37</td>\n",
       "      <td>Runner-up Group C</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Winner Group C</td>\n",
       "      <td>Match 40</td>\n",
       "      <td>3rd Group D/E/F</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Winner Group B</td>\n",
       "      <td>Match 39</td>\n",
       "      <td>3rd Group A/D/E/F</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Runner-up Group D</td>\n",
       "      <td>Match 42</td>\n",
       "      <td>Runner-up Group E</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Winner Group F</td>\n",
       "      <td>Match 41</td>\n",
       "      <td>3rd Group A/B/C</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Winner Group E</td>\n",
       "      <td>Match 43</td>\n",
       "      <td>3rd Group A/B/C/D</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Winner Group D</td>\n",
       "      <td>Match 44</td>\n",
       "      <td>Runner-up Group F</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 home     score               away  year\n",
       "36  Runner-up Group A  Match 38  Runner-up Group B  2024\n",
       "37     Winner Group A  Match 37  Runner-up Group C  2024\n",
       "38     Winner Group C  Match 40    3rd Group D/E/F  2024\n",
       "39     Winner Group B  Match 39  3rd Group A/D/E/F  2024\n",
       "40  Runner-up Group D  Match 42  Runner-up Group E  2024\n",
       "41     Winner Group F  Match 41    3rd Group A/B/C  2024\n",
       "42     Winner Group E  Match 43  3rd Group A/B/C/D  2024\n",
       "43     Winner Group D  Match 44  Runner-up Group F  2024"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fixture_knockout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "def update_fixtures_with_third_place_teams(df_fixture_knockout, third_place_fixtures_dict, dict_table):\n",
    "    # Iterate through the third place fixture dictionary\n",
    "    for winner, third_placeholder in third_place_fixtures_dict.items():\n",
    "        # Extract the group of the third place team from the placeholder\n",
    "        third_group = third_placeholder.split()[-1]\n",
    "        full_third_group = f'Group {third_group}'\n",
    "        \n",
    "        # Get the actual third place team from the corresponding group\n",
    "        third_place_team = dict_table[full_third_group].loc[2, 'Team']\n",
    "        \n",
    "        # Replace the placeholder with the actual team in the fixtures\n",
    "        df_fixture_knockout.loc[df_fixture_knockout['home'] == winner, 'away'] = third_place_team\n",
    "    \n",
    "    return df_fixture_knockout\n",
    "\n",
    "     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Runner-up Group A</td>\n",
       "      <td>Match 38</td>\n",
       "      <td>Runner-up Group B</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Winner Group A</td>\n",
       "      <td>Match 37</td>\n",
       "      <td>Runner-up Group C</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Winner Group C</td>\n",
       "      <td>Match 40</td>\n",
       "      <td>Poland</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Winner Group B</td>\n",
       "      <td>Match 39</td>\n",
       "      <td>Hungary</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Runner-up Group D</td>\n",
       "      <td>Match 42</td>\n",
       "      <td>Runner-up Group E</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Winner Group F</td>\n",
       "      <td>Match 41</td>\n",
       "      <td>Slovenia</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Winner Group E</td>\n",
       "      <td>Match 43</td>\n",
       "      <td>Croatia</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Winner Group D</td>\n",
       "      <td>Match 44</td>\n",
       "      <td>Runner-up Group F</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 home     score               away  year\n",
       "36  Runner-up Group A  Match 38  Runner-up Group B  2024\n",
       "37     Winner Group A  Match 37  Runner-up Group C  2024\n",
       "38     Winner Group C  Match 40             Poland  2024\n",
       "39     Winner Group B  Match 39            Hungary  2024\n",
       "40  Runner-up Group D  Match 42  Runner-up Group E  2024\n",
       "41     Winner Group F  Match 41           Slovenia  2024\n",
       "42     Winner Group E  Match 43            Croatia  2024\n",
       "43     Winner Group D  Match 44  Runner-up Group F  2024"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "updated_fixtures = update_fixtures_with_third_place_teams(df_fixture_knockout, third_place_fixtures_dict, dict_table)\n",
    "updated_fixtures"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Round of 16"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Runner-up Group A</td>\n",
       "      <td>Match 38</td>\n",
       "      <td>Runner-up Group B</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Winner Group A</td>\n",
       "      <td>Match 37</td>\n",
       "      <td>Runner-up Group C</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Winner Group C</td>\n",
       "      <td>Match 40</td>\n",
       "      <td>Poland</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Winner Group B</td>\n",
       "      <td>Match 39</td>\n",
       "      <td>Hungary</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Runner-up Group D</td>\n",
       "      <td>Match 42</td>\n",
       "      <td>Runner-up Group E</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Winner Group F</td>\n",
       "      <td>Match 41</td>\n",
       "      <td>Slovenia</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Winner Group E</td>\n",
       "      <td>Match 43</td>\n",
       "      <td>Croatia</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Winner Group D</td>\n",
       "      <td>Match 44</td>\n",
       "      <td>Runner-up Group F</td>\n",
       "      <td>2024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 home     score               away  year\n",
       "36  Runner-up Group A  Match 38  Runner-up Group B  2024\n",
       "37     Winner Group A  Match 37  Runner-up Group C  2024\n",
       "38     Winner Group C  Match 40             Poland  2024\n",
       "39     Winner Group B  Match 39            Hungary  2024\n",
       "40  Runner-up Group D  Match 42  Runner-up Group E  2024\n",
       "41     Winner Group F  Match 41           Slovenia  2024\n",
       "42     Winner Group E  Match 43            Croatia  2024\n",
       "43     Winner Group D  Match 44  Runner-up Group F  2024"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fixture_knockout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>Match 38</td>\n",
       "      <td>Italy</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Germany</td>\n",
       "      <td>Match 37</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>England</td>\n",
       "      <td>Match 40</td>\n",
       "      <td>Poland</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Match 39</td>\n",
       "      <td>Hungary</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>France</td>\n",
       "      <td>Match 42</td>\n",
       "      <td>Romania</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>Match 41</td>\n",
       "      <td>Slovenia</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>Match 43</td>\n",
       "      <td>Croatia</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Match 44</td>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           home     score            away  year winner\n",
       "36  Switzerland  Match 38           Italy  2024      ?\n",
       "37      Germany  Match 37         Denmark  2024      ?\n",
       "38      England  Match 40          Poland  2024      ?\n",
       "39        Spain  Match 39         Hungary  2024      ?\n",
       "40       France  Match 42         Romania  2024      ?\n",
       "41     Portugal  Match 41        Slovenia  2024      ?\n",
       "42      Belgium  Match 43         Croatia  2024      ?\n",
       "43  Netherlands  Match 44  Czech Republic  2024      ?"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for group in dict_table:\n",
    "    group_winner = dict_table[group].loc[0, 'Team']\n",
    "    runners_up = dict_table[group].loc[1, 'Team']\n",
    "    df_fixture_knockout.replace({f'Winner {group}':group_winner,\n",
    "                                 f'Runner-up {group}':runners_up}, inplace=True)\n",
    "\n",
    "df_fixture_knockout['winner'] = '?'\n",
    "df_fixture_knockout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_winner(df_fixture_updated):\n",
    "    for index, row in df_fixture_updated.iterrows():\n",
    "        home, away = row['home'], row['away']\n",
    "        points_home, points_away = predict_points(home, away)\n",
    "        if points_home > points_away:\n",
    "            winner = home\n",
    "        else:\n",
    "            winner = away\n",
    "        df_fixture_updated.loc[index, 'winner'] = winner\n",
    "    return df_fixture_updated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Switzerland</td>\n",
       "      <td>Match 38</td>\n",
       "      <td>Italy</td>\n",
       "      <td>2024</td>\n",
       "      <td>Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Germany</td>\n",
       "      <td>Match 37</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>2024</td>\n",
       "      <td>Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>England</td>\n",
       "      <td>Match 40</td>\n",
       "      <td>Poland</td>\n",
       "      <td>2024</td>\n",
       "      <td>England</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Match 39</td>\n",
       "      <td>Hungary</td>\n",
       "      <td>2024</td>\n",
       "      <td>Spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>France</td>\n",
       "      <td>Match 42</td>\n",
       "      <td>Romania</td>\n",
       "      <td>2024</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>Match 41</td>\n",
       "      <td>Slovenia</td>\n",
       "      <td>2024</td>\n",
       "      <td>Portugal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>Match 43</td>\n",
       "      <td>Croatia</td>\n",
       "      <td>2024</td>\n",
       "      <td>Belgium</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Match 44</td>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>2024</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           home     score            away  year       winner\n",
       "36  Switzerland  Match 38           Italy  2024        Italy\n",
       "37      Germany  Match 37         Denmark  2024      Germany\n",
       "38      England  Match 40          Poland  2024      England\n",
       "39        Spain  Match 39         Hungary  2024        Spain\n",
       "40       France  Match 42         Romania  2024       France\n",
       "41     Portugal  Match 41        Slovenia  2024     Portugal\n",
       "42      Belgium  Match 43         Croatia  2024      Belgium\n",
       "43  Netherlands  Match 44  Czech Republic  2024  Netherlands"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_winner(df_fixture_knockout)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Quarter Finals "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "def update_table(df_fixture_round_1, df_fixture_round_2):\n",
    "    for index, row in df_fixture_round_1.iterrows():\n",
    "        winner = df_fixture_round_1.loc[index, 'winner']\n",
    "        match = df_fixture_round_1.loc[index, 'score']\n",
    "        df_fixture_round_2.replace({f'Winner {match}':winner}, inplace=True)\n",
    "    df_fixture_round_2['winner'] = '?'\n",
    "    return df_fixture_round_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Match 45</td>\n",
       "      <td>Germany</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>Match 46</td>\n",
       "      <td>France</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>England</td>\n",
       "      <td>Match 48</td>\n",
       "      <td>Italy</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>Match 47</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        home     score         away  year winner\n",
       "44     Spain  Match 45      Germany  2024      ?\n",
       "45  Portugal  Match 46       France  2024      ?\n",
       "46   England  Match 48        Italy  2024      ?\n",
       "47   Belgium  Match 47  Netherlands  2024      ?"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "update_table(df_fixture_knockout, df_fixture_quarter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Match 45</td>\n",
       "      <td>Germany</td>\n",
       "      <td>2024</td>\n",
       "      <td>Spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Portugal</td>\n",
       "      <td>Match 46</td>\n",
       "      <td>France</td>\n",
       "      <td>2024</td>\n",
       "      <td>France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>England</td>\n",
       "      <td>Match 48</td>\n",
       "      <td>Italy</td>\n",
       "      <td>2024</td>\n",
       "      <td>Italy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Belgium</td>\n",
       "      <td>Match 47</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2024</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        home     score         away  year       winner\n",
       "44     Spain  Match 45      Germany  2024        Spain\n",
       "45  Portugal  Match 46       France  2024       France\n",
       "46   England  Match 48        Italy  2024        Italy\n",
       "47   Belgium  Match 47  Netherlands  2024  Netherlands"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_winner(df_fixture_quarter)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Semi Finals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Match 49</td>\n",
       "      <td>France</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Match 50</td>\n",
       "      <td>Italy</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           home     score    away  year winner\n",
       "48        Spain  Match 49  France  2024      ?\n",
       "49  Netherlands  Match 50   Italy  2024      ?"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "update_table(df_fixture_quarter, df_fixture_semi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Match 49</td>\n",
       "      <td>France</td>\n",
       "      <td>2024</td>\n",
       "      <td>Spain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Match 50</td>\n",
       "      <td>Italy</td>\n",
       "      <td>2024</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           home     score    away  year       winner\n",
       "48        Spain  Match 49  France  2024        Spain\n",
       "49  Netherlands  Match 50   Italy  2024  Netherlands"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_winner(df_fixture_semi)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Match 51</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2024</td>\n",
       "      <td>?</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     home     score         away  year winner\n",
       "50  Spain  Match 51  Netherlands  2024      ?"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "update_table( df_fixture_semi, df_fixture_final)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>home</th>\n",
       "      <th>score</th>\n",
       "      <th>away</th>\n",
       "      <th>year</th>\n",
       "      <th>winner</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Spain</td>\n",
       "      <td>Match 51</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>2024</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     home     score         away  year       winner\n",
       "50  Spain  Match 51  Netherlands  2024  Netherlands"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_winner(df_fixture_final)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "euros_env",
   "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}