4b_player_match_dataset_creation_oldseasons.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b22c6ee8",
   "metadata": {},
   "source": [
    "player_match_dataset_creation code for past seasons\n",
    "\n",
    "Select the season in season variable, and repeat the code if needed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8a74364d",
   "metadata": {},
   "outputs": [],
   "source": [
    "season = '2223'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "8a6867a5",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Peppe\\AppData\\Local\\Temp\\ipykernel_7752\\813865162.py:21: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  team_data['team_idx'][i] = team_data['team_idx'][i].split(' ')[-1]\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>team</th>\n",
       "      <th>team_players_used</th>\n",
       "      <th>team_possession</th>\n",
       "      <th>team_games</th>\n",
       "      <th>team_games_starts</th>\n",
       "      <th>team_minutes</th>\n",
       "      <th>team_goals</th>\n",
       "      <th>team_assists</th>\n",
       "      <th>team_pens_made</th>\n",
       "      <th>team_pens_att</th>\n",
       "      <th>...</th>\n",
       "      <th>vs_team_fouls</th>\n",
       "      <th>vs_team_fouled</th>\n",
       "      <th>vs_team_offsides</th>\n",
       "      <th>vs_team_pens_won</th>\n",
       "      <th>vs_team_pens_conceded</th>\n",
       "      <th>vs_team_own_goals</th>\n",
       "      <th>vs_team_ball_recoveries</th>\n",
       "      <th>vs_team_aerials_won</th>\n",
       "      <th>vs_team_aerials_lost</th>\n",
       "      <th>vs_team_aerials_won_pct</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>team_idx</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Atalanta</th>\n",
       "      <td>Atalanta</td>\n",
       "      <td>27.0</td>\n",
       "      <td>49.9</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>406.0</td>\n",
       "      <td>438.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2239.0</td>\n",
       "      <td>483.0</td>\n",
       "      <td>575.0</td>\n",
       "      <td>45.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bologna</th>\n",
       "      <td>Bologna</td>\n",
       "      <td>27.0</td>\n",
       "      <td>54.7</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>451.0</td>\n",
       "      <td>452.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2005.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>369.0</td>\n",
       "      <td>54.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Cremonese</th>\n",
       "      <td>Cremonese</td>\n",
       "      <td>33.0</td>\n",
       "      <td>42.1</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>...</td>\n",
       "      <td>434.0</td>\n",
       "      <td>432.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2131.0</td>\n",
       "      <td>730.0</td>\n",
       "      <td>562.0</td>\n",
       "      <td>56.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Empoli</th>\n",
       "      <td>Empoli</td>\n",
       "      <td>32.0</td>\n",
       "      <td>47.3</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>488.0</td>\n",
       "      <td>422.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1953.0</td>\n",
       "      <td>519.0</td>\n",
       "      <td>434.0</td>\n",
       "      <td>54.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiorentina</th>\n",
       "      <td>Fiorentina</td>\n",
       "      <td>30.0</td>\n",
       "      <td>56.1</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>542.0</td>\n",
       "      <td>459.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1910.0</td>\n",
       "      <td>517.0</td>\n",
       "      <td>588.0</td>\n",
       "      <td>46.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Verona</th>\n",
       "      <td>Hellas Verona</td>\n",
       "      <td>36.0</td>\n",
       "      <td>41.2</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>410.0</td>\n",
       "      <td>518.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2125.0</td>\n",
       "      <td>735.0</td>\n",
       "      <td>810.0</td>\n",
       "      <td>47.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Inter</th>\n",
       "      <td>Inter</td>\n",
       "      <td>25.0</td>\n",
       "      <td>55.7</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>436.0</td>\n",
       "      <td>412.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1767.0</td>\n",
       "      <td>415.0</td>\n",
       "      <td>500.0</td>\n",
       "      <td>45.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Juventus</th>\n",
       "      <td>Juventus</td>\n",
       "      <td>29.0</td>\n",
       "      <td>49.6</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>427.0</td>\n",
       "      <td>412.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1893.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>486.0</td>\n",
       "      <td>49.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lazio</th>\n",
       "      <td>Lazio</td>\n",
       "      <td>24.0</td>\n",
       "      <td>52.1</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>...</td>\n",
       "      <td>500.0</td>\n",
       "      <td>376.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2041.0</td>\n",
       "      <td>381.0</td>\n",
       "      <td>399.0</td>\n",
       "      <td>48.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lecce</th>\n",
       "      <td>Lecce</td>\n",
       "      <td>29.0</td>\n",
       "      <td>41.3</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>461.0</td>\n",
       "      <td>521.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2030.0</td>\n",
       "      <td>728.0</td>\n",
       "      <td>567.0</td>\n",
       "      <td>56.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Milan</th>\n",
       "      <td>Milan</td>\n",
       "      <td>30.0</td>\n",
       "      <td>55.6</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>462.0</td>\n",
       "      <td>430.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1972.0</td>\n",
       "      <td>478.0</td>\n",
       "      <td>561.0</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Monza</th>\n",
       "      <td>Monza</td>\n",
       "      <td>31.0</td>\n",
       "      <td>55.2</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>534.0</td>\n",
       "      <td>466.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1983.0</td>\n",
       "      <td>441.0</td>\n",
       "      <td>448.0</td>\n",
       "      <td>49.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Napoli</th>\n",
       "      <td>Napoli</td>\n",
       "      <td>27.0</td>\n",
       "      <td>61.8</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>75.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>495.0</td>\n",
       "      <td>363.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1901.0</td>\n",
       "      <td>403.0</td>\n",
       "      <td>483.0</td>\n",
       "      <td>45.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Roma</th>\n",
       "      <td>Roma</td>\n",
       "      <td>30.0</td>\n",
       "      <td>48.6</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>533.0</td>\n",
       "      <td>417.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>429.0</td>\n",
       "      <td>543.0</td>\n",
       "      <td>44.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Salernitana</th>\n",
       "      <td>Salernitana</td>\n",
       "      <td>30.0</td>\n",
       "      <td>44.9</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>478.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2106.0</td>\n",
       "      <td>585.0</td>\n",
       "      <td>528.0</td>\n",
       "      <td>52.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sampdoria</th>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>40.0</td>\n",
       "      <td>46.1</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>530.0</td>\n",
       "      <td>480.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2079.0</td>\n",
       "      <td>636.0</td>\n",
       "      <td>617.0</td>\n",
       "      <td>50.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sassuolo</th>\n",
       "      <td>Sassuolo</td>\n",
       "      <td>31.0</td>\n",
       "      <td>49.2</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>479.0</td>\n",
       "      <td>376.0</td>\n",
       "      <td>119.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>438.0</td>\n",
       "      <td>375.0</td>\n",
       "      <td>53.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spezia</th>\n",
       "      <td>Spezia</td>\n",
       "      <td>34.0</td>\n",
       "      <td>47.4</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>...</td>\n",
       "      <td>407.0</td>\n",
       "      <td>478.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2038.0</td>\n",
       "      <td>570.0</td>\n",
       "      <td>535.0</td>\n",
       "      <td>51.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Torino</th>\n",
       "      <td>Torino</td>\n",
       "      <td>28.0</td>\n",
       "      <td>53.2</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>413.0</td>\n",
       "      <td>476.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1940.0</td>\n",
       "      <td>629.0</td>\n",
       "      <td>590.0</td>\n",
       "      <td>51.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Udinese</th>\n",
       "      <td>Udinese</td>\n",
       "      <td>30.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>418.0</td>\n",
       "      <td>3420.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>505.0</td>\n",
       "      <td>458.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1933.0</td>\n",
       "      <td>406.0</td>\n",
       "      <td>477.0</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20 rows × 303 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      team  team_players_used  team_possession  team_games  \\\n",
       "team_idx                                                                     \n",
       "Atalanta          Atalanta               27.0             49.9        38.0   \n",
       "Bologna            Bologna               27.0             54.7        38.0   \n",
       "Cremonese        Cremonese               33.0             42.1        38.0   \n",
       "Empoli              Empoli               32.0             47.3        38.0   \n",
       "Fiorentina      Fiorentina               30.0             56.1        38.0   \n",
       "Verona       Hellas Verona               36.0             41.2        38.0   \n",
       "Inter                Inter               25.0             55.7        38.0   \n",
       "Juventus          Juventus               29.0             49.6        38.0   \n",
       "Lazio                Lazio               24.0             52.1        38.0   \n",
       "Lecce                Lecce               29.0             41.3        38.0   \n",
       "Milan                Milan               30.0             55.6        38.0   \n",
       "Monza                Monza               31.0             55.2        38.0   \n",
       "Napoli              Napoli               27.0             61.8        38.0   \n",
       "Roma                  Roma               30.0             48.6        38.0   \n",
       "Salernitana    Salernitana               30.0             44.9        38.0   \n",
       "Sampdoria        Sampdoria               40.0             46.1        38.0   \n",
       "Sassuolo          Sassuolo               31.0             49.2        38.0   \n",
       "Spezia              Spezia               34.0             47.4        38.0   \n",
       "Torino              Torino               28.0             53.2        38.0   \n",
       "Udinese            Udinese               30.0             48.0        38.0   \n",
       "\n",
       "             team_games_starts  team_minutes  team_goals  team_assists  \\\n",
       "team_idx                                                                 \n",
       "Atalanta                 418.0        3420.0        64.0          42.0   \n",
       "Bologna                  418.0        3420.0        52.0          41.0   \n",
       "Cremonese                418.0        3420.0        35.0          18.0   \n",
       "Empoli                   418.0        3420.0        35.0          20.0   \n",
       "Fiorentina               418.0        3420.0        51.0          37.0   \n",
       "Verona                   418.0        3420.0        29.0          22.0   \n",
       "Inter                    418.0        3420.0        68.0          50.0   \n",
       "Juventus                 418.0        3420.0        56.0          41.0   \n",
       "Lazio                    418.0        3420.0        59.0          37.0   \n",
       "Lecce                    418.0        3420.0        31.0          21.0   \n",
       "Milan                    418.0        3420.0        61.0          49.0   \n",
       "Monza                    418.0        3420.0        46.0          31.0   \n",
       "Napoli                   418.0        3420.0        75.0          57.0   \n",
       "Roma                     418.0        3420.0        50.0          33.0   \n",
       "Salernitana              418.0        3420.0        46.0          34.0   \n",
       "Sampdoria                418.0        3420.0        23.0          18.0   \n",
       "Sassuolo                 418.0        3420.0        46.0          30.0   \n",
       "Spezia                   418.0        3420.0        29.0          18.0   \n",
       "Torino                   418.0        3420.0        41.0          34.0   \n",
       "Udinese                  418.0        3420.0        46.0          41.0   \n",
       "\n",
       "             team_pens_made  team_pens_att  ...  vs_team_fouls  \\\n",
       "team_idx                                    ...                  \n",
       "Atalanta                6.0            8.0  ...          406.0   \n",
       "Bologna                 6.0            6.0  ...          451.0   \n",
       "Cremonese               5.0            7.0  ...          434.0   \n",
       "Empoli                  2.0            2.0  ...          488.0   \n",
       "Fiorentina              4.0            6.0  ...          542.0   \n",
       "Verona                  1.0            1.0  ...          410.0   \n",
       "Inter                   4.0            5.0  ...          436.0   \n",
       "Juventus                3.0            6.0  ...          427.0   \n",
       "Lazio                   6.0            8.0  ...          500.0   \n",
       "Lecce                   4.0            6.0  ...          461.0   \n",
       "Milan                   5.0            5.0  ...          462.0   \n",
       "Monza                   5.0            6.0  ...          534.0   \n",
       "Napoli                  8.0           10.0  ...          495.0   \n",
       "Roma                    7.0           10.0  ...          533.0   \n",
       "Salernitana             2.0            2.0  ...          478.0   \n",
       "Sampdoria               1.0            2.0  ...          530.0   \n",
       "Sassuolo                9.0           10.0  ...          479.0   \n",
       "Spezia                  4.0            4.0  ...          407.0   \n",
       "Torino                  2.0            2.0  ...          413.0   \n",
       "Udinese                 1.0            2.0  ...          505.0   \n",
       "\n",
       "             vs_team_fouled  vs_team_offsides  vs_team_pens_won  \\\n",
       "team_idx                                                          \n",
       "Atalanta              438.0              50.0               1.0   \n",
       "Bologna               452.0              58.0               6.0   \n",
       "Cremonese             432.0              72.0               4.0   \n",
       "Empoli                422.0              75.0               4.0   \n",
       "Fiorentina            459.0              93.0               5.0   \n",
       "Verona                518.0              44.0               2.0   \n",
       "Inter                 412.0              36.0               3.0   \n",
       "Juventus              412.0              53.0               2.0   \n",
       "Lazio                 376.0              63.0               2.0   \n",
       "Lecce                 521.0              64.0               5.0   \n",
       "Milan                 430.0              41.0               5.0   \n",
       "Monza                 466.0              55.0               1.0   \n",
       "Napoli                363.0              50.0               1.0   \n",
       "Roma                  417.0              22.0               2.0   \n",
       "Salernitana           447.0              72.0              11.0   \n",
       "Sampdoria             480.0              90.0               8.0   \n",
       "Sassuolo              376.0             119.0               2.0   \n",
       "Spezia                478.0              76.0               5.0   \n",
       "Torino                476.0              42.0               4.0   \n",
       "Udinese               458.0              61.0               5.0   \n",
       "\n",
       "             vs_team_pens_conceded  vs_team_own_goals  \\\n",
       "team_idx                                                \n",
       "Atalanta                       8.0                2.0   \n",
       "Bologna                        6.0                1.0   \n",
       "Cremonese                      7.0                1.0   \n",
       "Empoli                         2.0                2.0   \n",
       "Fiorentina                     6.0                2.0   \n",
       "Verona                         1.0                2.0   \n",
       "Inter                          5.0                3.0   \n",
       "Juventus                       6.0                0.0   \n",
       "Lazio                          8.0                1.0   \n",
       "Lecce                          6.0                2.0   \n",
       "Milan                          5.0                3.0   \n",
       "Monza                          6.0                2.0   \n",
       "Napoli                        10.0                2.0   \n",
       "Roma                           9.0                0.0   \n",
       "Salernitana                    2.0                2.0   \n",
       "Sampdoria                      2.0                1.0   \n",
       "Sassuolo                      10.0                1.0   \n",
       "Spezia                         4.0                2.0   \n",
       "Torino                         2.0                1.0   \n",
       "Udinese                        2.0                1.0   \n",
       "\n",
       "             vs_team_ball_recoveries  vs_team_aerials_won  \\\n",
       "team_idx                                                    \n",
       "Atalanta                      2239.0                483.0   \n",
       "Bologna                       2005.0                447.0   \n",
       "Cremonese                     2131.0                730.0   \n",
       "Empoli                        1953.0                519.0   \n",
       "Fiorentina                    1910.0                517.0   \n",
       "Verona                        2125.0                735.0   \n",
       "Inter                         1767.0                415.0   \n",
       "Juventus                      1893.0                477.0   \n",
       "Lazio                         2041.0                381.0   \n",
       "Lecce                         2030.0                728.0   \n",
       "Milan                         1972.0                478.0   \n",
       "Monza                         1983.0                441.0   \n",
       "Napoli                        1901.0                403.0   \n",
       "Roma                          1979.0                429.0   \n",
       "Salernitana                   2106.0                585.0   \n",
       "Sampdoria                     2079.0                636.0   \n",
       "Sassuolo                      1928.0                438.0   \n",
       "Spezia                        2038.0                570.0   \n",
       "Torino                        1940.0                629.0   \n",
       "Udinese                       1933.0                406.0   \n",
       "\n",
       "             vs_team_aerials_lost  vs_team_aerials_won_pct  \n",
       "team_idx                                                    \n",
       "Atalanta                    575.0                     45.7  \n",
       "Bologna                     369.0                     54.8  \n",
       "Cremonese                   562.0                     56.5  \n",
       "Empoli                      434.0                     54.5  \n",
       "Fiorentina                  588.0                     46.8  \n",
       "Verona                      810.0                     47.6  \n",
       "Inter                       500.0                     45.4  \n",
       "Juventus                    486.0                     49.5  \n",
       "Lazio                       399.0                     48.8  \n",
       "Lecce                       567.0                     56.2  \n",
       "Milan                       561.0                     46.0  \n",
       "Monza                       448.0                     49.6  \n",
       "Napoli                      483.0                     45.5  \n",
       "Roma                        543.0                     44.1  \n",
       "Salernitana                 528.0                     52.6  \n",
       "Sampdoria                   617.0                     50.8  \n",
       "Sassuolo                    375.0                     53.9  \n",
       "Spezia                      535.0                     51.6  \n",
       "Torino                      590.0                     51.6  \n",
       "Udinese                     477.0                     46.0  \n",
       "\n",
       "[20 rows x 303 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "rcsv = pd.read_csv('fbref_data/season' + season + '/teams.csv')   \n",
    "team = pd.DataFrame(rcsv)\n",
    "\n",
    "rcsv = pd.read_csv('fbref_data/season' + season + '/teams_vs.csv')   \n",
    "vs_team = pd.DataFrame(rcsv)\n",
    "\n",
    "for i in range(1, team.shape[1]):\n",
    "    team = team.rename(columns = {team.columns[i] : 'team_' + team.columns[i]})\n",
    "    \n",
    "for i in range(1, vs_team.shape[1]):\n",
    "    vs_team = vs_team.rename(columns = {vs_team.columns[i] : 'vs_team_' + vs_team.columns[i]})\n",
    "    \n",
    "vs_team.pop('team')\n",
    "\n",
    "team_data = pd.concat([team, vs_team], axis = 1)\n",
    "\n",
    "team_data['team_idx'] = team_data['team']\n",
    "for i in range(team_data.shape[0]):\n",
    "    team_data.loc[i, 'team_idx'] = team_data.loc[i, 'team_idx'].split(' ')[-1]\n",
    "\n",
    "team_data = team_data.set_index('team_idx')\n",
    "\n",
    "team_data\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b6a3db98",
   "metadata": {},
   "outputs": [],
   "source": [
    "team_data.to_excel('mid_outputs/season' + season + '/team_data.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c34eb398",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>id</th>\n",
       "      <th>r</th>\n",
       "      <th>team</th>\n",
       "      <th>surname</th>\n",
       "      <th>initial</th>\n",
       "      <th>fb_ID</th>\n",
       "      <th>age</th>\n",
       "      <th>birth_year</th>\n",
       "      <th>games</th>\n",
       "      <th>...</th>\n",
       "      <th>gk_pct_goal_kicks_launched</th>\n",
       "      <th>gk_goal_kick_length_avg</th>\n",
       "      <th>gk_crosses</th>\n",
       "      <th>gk_crosses_stopped</th>\n",
       "      <th>gk_crosses_stopped_pct</th>\n",
       "      <th>gk_def_actions_outside_pen_area</th>\n",
       "      <th>gk_def_actions_outside_pen_area_per90</th>\n",
       "      <th>gk_avg_distance_def_actions</th>\n",
       "      <th>vote_avg</th>\n",
       "      <th>vote_std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Meret</th>\n",
       "      <td>0</td>\n",
       "      <td>572</td>\n",
       "      <td>P</td>\n",
       "      <td>Napoli</td>\n",
       "      <td>Meret</td>\n",
       "      <td>NaN</td>\n",
       "      <td>620</td>\n",
       "      <td>25</td>\n",
       "      <td>1997</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>20.2</td>\n",
       "      <td>26.9</td>\n",
       "      <td>335.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>3.3</td>\n",
       "      <td>39.0</td>\n",
       "      <td>1.15</td>\n",
       "      <td>17.0</td>\n",
       "      <td>6.176471</td>\n",
       "      <td>0.400043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Provedel</th>\n",
       "      <td>1</td>\n",
       "      <td>2814</td>\n",
       "      <td>P</td>\n",
       "      <td>Lazio</td>\n",
       "      <td>Provedel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>632</td>\n",
       "      <td>28</td>\n",
       "      <td>1994</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>33.3</td>\n",
       "      <td>33.2</td>\n",
       "      <td>493.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>4.3</td>\n",
       "      <td>55.0</td>\n",
       "      <td>1.45</td>\n",
       "      <td>16.5</td>\n",
       "      <td>6.263158</td>\n",
       "      <td>0.409378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vicario</th>\n",
       "      <td>2</td>\n",
       "      <td>4964</td>\n",
       "      <td>P</td>\n",
       "      <td>Empoli</td>\n",
       "      <td>Vicario</td>\n",
       "      <td>NaN</td>\n",
       "      <td>649</td>\n",
       "      <td>25</td>\n",
       "      <td>1996</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>50.0</td>\n",
       "      <td>42.9</td>\n",
       "      <td>606.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>5.6</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.71</td>\n",
       "      <td>11.5</td>\n",
       "      <td>6.403226</td>\n",
       "      <td>0.447562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Szczesny</th>\n",
       "      <td>3</td>\n",
       "      <td>453</td>\n",
       "      <td>P</td>\n",
       "      <td>Juventus</td>\n",
       "      <td>Szczesny</td>\n",
       "      <td>NaN</td>\n",
       "      <td>644</td>\n",
       "      <td>32</td>\n",
       "      <td>1990</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>43.1</td>\n",
       "      <td>38.3</td>\n",
       "      <td>389.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.76</td>\n",
       "      <td>14.7</td>\n",
       "      <td>6.107143</td>\n",
       "      <td>0.309295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Falcone</th>\n",
       "      <td>4</td>\n",
       "      <td>2134</td>\n",
       "      <td>P</td>\n",
       "      <td>Lecce</td>\n",
       "      <td>Falcone</td>\n",
       "      <td>NaN</td>\n",
       "      <td>612</td>\n",
       "      <td>27</td>\n",
       "      <td>1995</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>79.1</td>\n",
       "      <td>53.5</td>\n",
       "      <td>534.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>4.3</td>\n",
       "      <td>42.0</td>\n",
       "      <td>1.11</td>\n",
       "      <td>14.1</td>\n",
       "      <td>6.250000</td>\n",
       "      <td>0.547122</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>De Luca</th>\n",
       "      <td>538</td>\n",
       "      <td>5512</td>\n",
       "      <td>A</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>Luca</td>\n",
       "      <td>NaN</td>\n",
       "      <td>151</td>\n",
       "      <td>24</td>\n",
       "      <td>1998</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.871240</td>\n",
       "      <td>0.394770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Voelkerling Persson</th>\n",
       "      <td>539</td>\n",
       "      <td>5837</td>\n",
       "      <td>A</td>\n",
       "      <td>Lecce</td>\n",
       "      <td>Persson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>565</td>\n",
       "      <td>19</td>\n",
       "      <td>2003</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.132748</td>\n",
       "      <td>0.474211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Montevago</th>\n",
       "      <td>540</td>\n",
       "      <td>6113</td>\n",
       "      <td>A</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>Montevago</td>\n",
       "      <td>NaN</td>\n",
       "      <td>365</td>\n",
       "      <td>19</td>\n",
       "      <td>2003</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.722482</td>\n",
       "      <td>0.229188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Krollis</th>\n",
       "      <td>541</td>\n",
       "      <td>6143</td>\n",
       "      <td>A</td>\n",
       "      <td>Spezia</td>\n",
       "      <td>Krollis</td>\n",
       "      <td>NaN</td>\n",
       "      <td>288</td>\n",
       "      <td>20</td>\n",
       "      <td>2001</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.357731</td>\n",
       "      <td>0.405367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vivaldo</th>\n",
       "      <td>542</td>\n",
       "      <td>6160</td>\n",
       "      <td>A</td>\n",
       "      <td>Udinese</td>\n",
       "      <td>Vivaldo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.006617</td>\n",
       "      <td>0.274006</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>543 rows × 160 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Unnamed: 0    id  r       team    surname initial  fb_ID  \\\n",
       "name                                                                            \n",
       "Meret                         0   572  P     Napoli      Meret     NaN    620   \n",
       "Provedel                      1  2814  P      Lazio   Provedel     NaN    632   \n",
       "Vicario                       2  4964  P     Empoli    Vicario     NaN    649   \n",
       "Szczesny                      3   453  P   Juventus   Szczesny     NaN    644   \n",
       "Falcone                       4  2134  P      Lecce    Falcone     NaN    612   \n",
       "...                         ...   ... ..        ...        ...     ...    ...   \n",
       "De Luca                     538  5512  A  Sampdoria       Luca     NaN    151   \n",
       "Voelkerling Persson         539  5837  A      Lecce    Persson     NaN    565   \n",
       "Montevago                   540  6113  A  Sampdoria  Montevago     NaN    365   \n",
       "Krollis                     541  6143  A     Spezia    Krollis     NaN    288   \n",
       "Vivaldo                     542  6160  A    Udinese    Vivaldo     NaN     -1   \n",
       "\n",
       "                     age  birth_year  games  ...  gk_pct_goal_kicks_launched  \\\n",
       "name                                         ...                               \n",
       "Meret                 25        1997      0  ...                        20.2   \n",
       "Provedel              28        1994      0  ...                        33.3   \n",
       "Vicario               25        1996      0  ...                        50.0   \n",
       "Szczesny              32        1990      0  ...                        43.1   \n",
       "Falcone               27        1995      0  ...                        79.1   \n",
       "...                  ...         ...    ...  ...                         ...   \n",
       "De Luca               24        1998      2  ...                         0.0   \n",
       "Voelkerling Persson   19        2003      9  ...                         0.0   \n",
       "Montevago             19        2003      6  ...                         0.0   \n",
       "Krollis               20        2001      4  ...                         0.0   \n",
       "Vivaldo                0           0      0  ...                         0.0   \n",
       "\n",
       "                     gk_goal_kick_length_avg  gk_crosses  gk_crosses_stopped  \\\n",
       "name                                                                           \n",
       "Meret                                   26.9       335.0                11.0   \n",
       "Provedel                                33.2       493.0                21.0   \n",
       "Vicario                                 42.9       606.0                34.0   \n",
       "Szczesny                                38.3       389.0                11.0   \n",
       "Falcone                                 53.5       534.0                23.0   \n",
       "...                                      ...         ...                 ...   \n",
       "De Luca                                  0.0         0.0                 0.0   \n",
       "Voelkerling Persson                      0.0         0.0                 0.0   \n",
       "Montevago                                0.0         0.0                 0.0   \n",
       "Krollis                                  0.0         0.0                 0.0   \n",
       "Vivaldo                                  0.0         0.0                 0.0   \n",
       "\n",
       "                     gk_crosses_stopped_pct  gk_def_actions_outside_pen_area  \\\n",
       "name                                                                           \n",
       "Meret                                   3.3                             39.0   \n",
       "Provedel                                4.3                             55.0   \n",
       "Vicario                                 5.6                             22.0   \n",
       "Szczesny                                2.8                             21.0   \n",
       "Falcone                                 4.3                             42.0   \n",
       "...                                     ...                              ...   \n",
       "De Luca                                 0.0                              0.0   \n",
       "Voelkerling Persson                     0.0                              0.0   \n",
       "Montevago                               0.0                              0.0   \n",
       "Krollis                                 0.0                              0.0   \n",
       "Vivaldo                                 0.0                              0.0   \n",
       "\n",
       "                     gk_def_actions_outside_pen_area_per90  \\\n",
       "name                                                         \n",
       "Meret                                                 1.15   \n",
       "Provedel                                              1.45   \n",
       "Vicario                                               0.71   \n",
       "Szczesny                                              0.76   \n",
       "Falcone                                               1.11   \n",
       "...                                                    ...   \n",
       "De Luca                                               0.00   \n",
       "Voelkerling Persson                                   0.00   \n",
       "Montevago                                             0.00   \n",
       "Krollis                                               0.00   \n",
       "Vivaldo                                               0.00   \n",
       "\n",
       "                     gk_avg_distance_def_actions  vote_avg  vote_std  \n",
       "name                                                                  \n",
       "Meret                                       17.0  6.176471  0.400043  \n",
       "Provedel                                    16.5  6.263158  0.409378  \n",
       "Vicario                                     11.5  6.403226  0.447562  \n",
       "Szczesny                                    14.7  6.107143  0.309295  \n",
       "Falcone                                     14.1  6.250000  0.547122  \n",
       "...                                          ...       ...       ...  \n",
       "De Luca                                      0.0  5.871240  0.394770  \n",
       "Voelkerling Persson                          0.0  6.132748  0.474211  \n",
       "Montevago                                    0.0  5.722482  0.229188  \n",
       "Krollis                                      0.0  6.357731  0.405367  \n",
       "Vivaldo                                      0.0  6.006617  0.274006  \n",
       "\n",
       "[543 rows x 160 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rx = pd.read_excel('mid_outputs/season' + season + '/players_stats.xlsx', index_col = 3)   \n",
    "players = pd.DataFrame(rx)\n",
    "\n",
    "players\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f9f7f88c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def player_match_data(player, pteam, oppteam):\n",
    "    if(not(player in players.index)):\n",
    "        return None\n",
    "    \n",
    "    pdata = players.loc[[player]]\n",
    "    \n",
    "    pteam_stats = team_data.loc[[pteam]].rename(index = {pteam : player})\n",
    "    \n",
    "    oppteam_stats = team_data.loc[[oppteam]].rename(index = {oppteam : player})\n",
    "    \n",
    "    oppteam_stats = oppteam_stats.rename(lambda x: 'opp_' + x, axis='columns')\n",
    "    \n",
    "    out = pd.concat([pdata, pteam_stats, oppteam_stats], axis = 1)\n",
    "    \n",
    "    return(out)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7eb4e666",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>id</th>\n",
       "      <th>r</th>\n",
       "      <th>team</th>\n",
       "      <th>surname</th>\n",
       "      <th>initial</th>\n",
       "      <th>fb_ID</th>\n",
       "      <th>age</th>\n",
       "      <th>birth_year</th>\n",
       "      <th>games</th>\n",
       "      <th>...</th>\n",
       "      <th>opp_vs_team_fouls</th>\n",
       "      <th>opp_vs_team_fouled</th>\n",
       "      <th>opp_vs_team_offsides</th>\n",
       "      <th>opp_vs_team_pens_won</th>\n",
       "      <th>opp_vs_team_pens_conceded</th>\n",
       "      <th>opp_vs_team_own_goals</th>\n",
       "      <th>opp_vs_team_ball_recoveries</th>\n",
       "      <th>opp_vs_team_aerials_won</th>\n",
       "      <th>opp_vs_team_aerials_lost</th>\n",
       "      <th>opp_vs_team_aerials_won_pct</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Immobile</th>\n",
       "      <td>448</td>\n",
       "      <td>785</td>\n",
       "      <td>A</td>\n",
       "      <td>Lazio</td>\n",
       "      <td>Immobile</td>\n",
       "      <td>NaN</td>\n",
       "      <td>264</td>\n",
       "      <td>32</td>\n",
       "      <td>1990</td>\n",
       "      <td>31</td>\n",
       "      <td>...</td>\n",
       "      <td>406.0</td>\n",
       "      <td>438.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2239.0</td>\n",
       "      <td>483.0</td>\n",
       "      <td>575.0</td>\n",
       "      <td>45.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 766 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Unnamed: 0   id  r   team   surname initial  fb_ID  age  birth_year  \\\n",
       "Immobile         448  785  A  Lazio  Immobile     NaN    264   32        1990   \n",
       "\n",
       "          games  ...  opp_vs_team_fouls  opp_vs_team_fouled  \\\n",
       "Immobile     31  ...              406.0               438.0   \n",
       "\n",
       "          opp_vs_team_offsides  opp_vs_team_pens_won  \\\n",
       "Immobile                  50.0                   1.0   \n",
       "\n",
       "          opp_vs_team_pens_conceded  opp_vs_team_own_goals  \\\n",
       "Immobile                        8.0                    2.0   \n",
       "\n",
       "          opp_vs_team_ball_recoveries  opp_vs_team_aerials_won  \\\n",
       "Immobile                       2239.0                    483.0   \n",
       "\n",
       "          opp_vs_team_aerials_lost  opp_vs_team_aerials_won_pct  \n",
       "Immobile                     575.0                         45.7  \n",
       "\n",
       "[1 rows x 766 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = player_match_data('Immobile', 'Lazio', 'Atalanta')\n",
    "test.to_excel('tmp/playermatchdata.xlsx')\n",
    "\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6b4c1b00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Lazio'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pteam = 'Lazio'\n",
    "pteam_stats = team_data.loc[[pteam]]\n",
    "pteam_stats.index[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6ea6df1f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['r',\n",
       " 'fb_ID',\n",
       " 'games',\n",
       " 'games_starts',\n",
       " 'minutes',\n",
       " 'goals',\n",
       " 'assists',\n",
       " 'cards_yellow',\n",
       " 'cards_red',\n",
       " 'xg',\n",
       " 'npxg',\n",
       " 'shots_on_target',\n",
       " 'shots_on_target_pct',\n",
       " 'goals_per_shot',\n",
       " 'goals_per_shot_on_target',\n",
       " 'passes_completed',\n",
       " 'passes_pct',\n",
       " 'passes_into_final_third',\n",
       " 'passes_into_penalty_area',\n",
       " 'progressive_passes',\n",
       " 'passes_live',\n",
       " 'passes_dead',\n",
       " 'through_balls',\n",
       " 'passes_switches',\n",
       " 'crosses',\n",
       " 'corner_kicks',\n",
       " 'dribble_tackles',\n",
       " 'dribbles_vs',\n",
       " 'dribble_tackles_pct',\n",
       " 'dribbled_past',\n",
       " 'blocks',\n",
       " 'blocked_shots',\n",
       " 'blocked_passes',\n",
       " 'interceptions',\n",
       " 'clearances',\n",
       " 'errors',\n",
       " 'touches',\n",
       " 'touches_def_pen_area',\n",
       " 'touches_def_3rd',\n",
       " 'touches_mid_3rd',\n",
       " 'touches_att_3rd',\n",
       " 'touches_att_pen_area',\n",
       " 'touches_live_ball',\n",
       " 'dribbles_completed',\n",
       " 'dribbles',\n",
       " 'dribbles_completed_pct',\n",
       " 'carries',\n",
       " 'progressive_carries',\n",
       " 'carries_into_final_third',\n",
       " 'carries_into_penalty_area',\n",
       " 'passes_received',\n",
       " 'miscontrols',\n",
       " 'dispossessed',\n",
       " 'fouls',\n",
       " 'fouled',\n",
       " 'aerials_won',\n",
       " 'aerials_lost',\n",
       " 'aerials_won_pct',\n",
       " 'team_possession',\n",
       " 'team_goals_assists_per90',\n",
       " 'team_goals_pens_per90',\n",
       " 'team_goals_assists_pens_per90',\n",
       " 'team_xg_per90',\n",
       " 'team_gk_goals_against_per90',\n",
       " 'team_gk_save_pct',\n",
       " 'team_gk_clean_sheets_pct',\n",
       " 'team_passes_pct',\n",
       " 'team_passes_pct_medium',\n",
       " 'team_passes_pct_long',\n",
       " 'team_sca_per90',\n",
       " 'team_gca_per90',\n",
       " 'team_dribble_tackles_pct',\n",
       " 'team_aerials_won_pct',\n",
       " 'vs_team_possession',\n",
       " 'vs_team_goals_per90',\n",
       " 'vs_team_assists_per90',\n",
       " 'vs_team_xg_per90',\n",
       " 'vs_team_gk_save_pct',\n",
       " 'vs_team_gk_clean_sheets_pct',\n",
       " 'vs_team_gk_pct_passes_launched',\n",
       " 'vs_team_gk_crosses_stopped_pct',\n",
       " 'vs_team_shots_on_target_per90',\n",
       " 'vs_team_passes_pct',\n",
       " 'vs_team_passes_pct_short',\n",
       " 'vs_team_passes_pct_medium',\n",
       " 'vs_team_passes_pct_long',\n",
       " 'vs_team_sca_per90',\n",
       " 'vs_team_gca_per90',\n",
       " 'vs_team_dribble_tackles_pct',\n",
       " 'vs_team_dribbles_completed_pct',\n",
       " 'vs_team_aerials_won_pct']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rx = pd.read_excel('tmp/playermatchdata_sel.xlsx', index_col = 0, header = None)   \n",
    "\n",
    "out = list()\n",
    "\n",
    "for i in range(1, rx.shape[1]):\n",
    "        if(rx[i][2] == \"x\"):\n",
    "            out.append(rx[i][0])\n",
    "            \n",
    "out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "47bc651f",
   "metadata": {},
   "outputs": [],
   "source": [
    "features_abs = ['r',\n",
    " 'games',\n",
    " 'games_starts',                \n",
    " 'minutes',\n",
    " 'shots_on_target_pct',\n",
    " 'goals_per_shot',\n",
    " 'goals_per_shot_on_target',\n",
    " 'passes_pct',\n",
    " #'dribble_tackles_pct',\n",
    " #'dribbles_completed_pct',\n",
    " 'aerials_won_pct',\n",
    " 'team_possession',\n",
    " 'team_goals_assists_per90',\n",
    " 'team_goals_pens_per90',\n",
    " 'team_goals_assists_pens_per90',\n",
    " 'team_xg_per90',\n",
    " 'team_gk_goals_against_per90',\n",
    " 'team_gk_save_pct',\n",
    " 'team_gk_clean_sheets_pct',\n",
    " 'team_passes_pct',\n",
    " 'team_passes_pct_medium',\n",
    " 'team_passes_pct_long',\n",
    " 'team_sca_per90',\n",
    " 'team_gca_per90',\n",
    " #'team_dribble_tackles_pct',\n",
    " 'team_aerials_won_pct',\n",
    " 'vs_team_possession',\n",
    " 'vs_team_goals_per90',\n",
    " 'vs_team_assists_per90',\n",
    " 'vs_team_xg_per90',\n",
    " 'vs_team_gk_save_pct',\n",
    " 'vs_team_gk_clean_sheets_pct',\n",
    " 'vs_team_gk_pct_passes_launched',\n",
    " 'vs_team_gk_crosses_stopped_pct',\n",
    " 'vs_team_shots_on_target_per90',\n",
    " 'vs_team_passes_pct',\n",
    " 'vs_team_passes_pct_short',\n",
    " 'vs_team_passes_pct_medium',\n",
    " 'vs_team_passes_pct_long',\n",
    " 'vs_team_sca_per90',\n",
    " 'vs_team_gca_per90',\n",
    " #'vs_team_dribble_tackles_pct',\n",
    " #'vs_team_dribbles_completed_pct',\n",
    " 'vs_team_aerials_won_pct',\n",
    " 'opp_team_possession',\n",
    " 'opp_team_goals_assists_per90',\n",
    " 'opp_team_goals_pens_per90',\n",
    " 'opp_team_goals_assists_pens_per90',\n",
    " 'opp_team_xg_per90',\n",
    " 'opp_team_gk_goals_against_per90',\n",
    " 'opp_team_gk_save_pct',\n",
    " 'opp_team_gk_clean_sheets_pct',\n",
    " 'opp_team_passes_pct',\n",
    " 'opp_team_passes_pct_medium',\n",
    " 'opp_team_passes_pct_long',\n",
    " 'opp_team_sca_per90',\n",
    " 'opp_team_gca_per90',\n",
    " #'opp_team_dribble_tackles_pct',\n",
    " 'opp_team_aerials_won_pct',\n",
    " 'opp_vs_team_possession',\n",
    " 'opp_vs_team_goals_per90',\n",
    " 'opp_vs_team_assists_per90',\n",
    " 'opp_vs_team_xg_per90',\n",
    " 'opp_vs_team_gk_save_pct',\n",
    " 'opp_vs_team_gk_clean_sheets_pct',\n",
    " 'opp_vs_team_gk_pct_passes_launched',\n",
    " 'opp_vs_team_gk_crosses_stopped_pct',\n",
    " 'opp_vs_team_shots_on_target_per90',\n",
    " 'opp_vs_team_passes_pct',\n",
    " 'opp_vs_team_passes_pct_short',\n",
    " 'opp_vs_team_passes_pct_medium',\n",
    " 'opp_vs_team_passes_pct_long',\n",
    " 'opp_vs_team_sca_per90',\n",
    " 'opp_vs_team_gca_per90',\n",
    " #'opp_vs_team_dribble_tackles_pct',\n",
    " #'opp_vs_team_dribbles_completed_pct',\n",
    " 'opp_vs_team_aerials_won_pct',\n",
    "                \n",
    " 'vote_avg',\n",
    " 'vote_std']\n",
    "\n",
    "features_rel = [\n",
    " 'goals',\n",
    " 'assists',\n",
    " 'cards_yellow',\n",
    " 'cards_red',\n",
    " 'xg',\n",
    " 'npxg',\n",
    " 'shots_on_target',\n",
    " 'passes_completed',\n",
    " 'passes_into_final_third',\n",
    " 'passes_into_penalty_area',\n",
    " 'progressive_passes',\n",
    " 'passes_live',\n",
    " 'passes_dead',\n",
    " 'through_balls',\n",
    " 'passes_switches',\n",
    " 'crosses',\n",
    " 'corner_kicks',\n",
    " #'dribble_tackles',\n",
    " #'dribbles_vs',\n",
    " #'dribbled_past',\n",
    " 'blocks',\n",
    " 'blocked_shots',\n",
    " 'blocked_passes',\n",
    " 'interceptions',\n",
    " 'clearances',\n",
    " 'errors',\n",
    " 'touches',\n",
    " 'touches_def_pen_area',\n",
    " 'touches_def_3rd',\n",
    " 'touches_mid_3rd',\n",
    " 'touches_att_3rd',\n",
    " 'touches_att_pen_area',\n",
    " 'touches_live_ball',\n",
    " #'dribbles_completed',\n",
    " #'dribbles',\n",
    " 'passes_received',\n",
    " 'miscontrols',\n",
    " 'dispossessed',\n",
    " 'fouls',\n",
    " 'fouled',\n",
    " 'aerials_won',\n",
    " 'aerials_lost',\n",
    " 'carries',\n",
    " 'progressive_carries',\n",
    " 'carries_into_final_third',\n",
    " 'carries_into_penalty_area']\n",
    "\n",
    "features_rel_gamecorr = [\n",
    "    'goals',\n",
    "    'assists',\n",
    "    'xg',\n",
    "    'npxg',\n",
    "    'cards_yellow',\n",
    "    'cards_red'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3f43d509",
   "metadata": {},
   "outputs": [],
   "source": [
    "def player_match_data_ext(player, pteam, oppteam):\n",
    "    pdata = player_match_data(player, pteam, oppteam)\n",
    "    \n",
    "    if(not isinstance(pdata, pd.DataFrame)):\n",
    "        return None\n",
    "    \n",
    "    out = pd.concat([pdata[features_abs], pdata[features_rel]], axis = 1)\n",
    "    \n",
    "    out[features_rel] = out[features_rel] / max(pdata['minutes'][0], 1)\n",
    "    \n",
    "    out[features_rel_gamecorr] = out[features_rel_gamecorr] * (pdata['minutes'][0] / max(pdata['games'][0], 1) / 90)\n",
    "    \n",
    "    return out\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "930d00c7",
   "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>r</th>\n",
       "      <th>games</th>\n",
       "      <th>games_starts</th>\n",
       "      <th>minutes</th>\n",
       "      <th>shots_on_target_pct</th>\n",
       "      <th>goals_per_shot</th>\n",
       "      <th>goals_per_shot_on_target</th>\n",
       "      <th>passes_pct</th>\n",
       "      <th>aerials_won_pct</th>\n",
       "      <th>team_possession</th>\n",
       "      <th>...</th>\n",
       "      <th>miscontrols</th>\n",
       "      <th>dispossessed</th>\n",
       "      <th>fouls</th>\n",
       "      <th>fouled</th>\n",
       "      <th>aerials_won</th>\n",
       "      <th>aerials_lost</th>\n",
       "      <th>carries</th>\n",
       "      <th>progressive_carries</th>\n",
       "      <th>carries_into_final_third</th>\n",
       "      <th>carries_into_penalty_area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Immobile</th>\n",
       "      <td>A</td>\n",
       "      <td>31</td>\n",
       "      <td>27</td>\n",
       "      <td>2219</td>\n",
       "      <td>41.7</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.27</td>\n",
       "      <td>75.0</td>\n",
       "      <td>29.8</td>\n",
       "      <td>52.1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.028391</td>\n",
       "      <td>0.009914</td>\n",
       "      <td>0.009464</td>\n",
       "      <td>0.010816</td>\n",
       "      <td>0.006309</td>\n",
       "      <td>0.014872</td>\n",
       "      <td>0.226679</td>\n",
       "      <td>0.018026</td>\n",
       "      <td>0.009464</td>\n",
       "      <td>0.012168</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 112 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          r  games  games_starts  minutes  shots_on_target_pct  \\\n",
       "Immobile  A     31            27     2219                 41.7   \n",
       "\n",
       "          goals_per_shot  goals_per_shot_on_target  passes_pct  \\\n",
       "Immobile            0.11                      0.27        75.0   \n",
       "\n",
       "          aerials_won_pct  team_possession  ...  miscontrols  dispossessed  \\\n",
       "Immobile             29.8             52.1  ...     0.028391      0.009914   \n",
       "\n",
       "             fouls    fouled  aerials_won  aerials_lost   carries  \\\n",
       "Immobile  0.009464  0.010816     0.006309      0.014872  0.226679   \n",
       "\n",
       "          progressive_carries  carries_into_final_third  \\\n",
       "Immobile             0.018026                  0.009464   \n",
       "\n",
       "          carries_into_penalty_area  \n",
       "Immobile                   0.012168  \n",
       "\n",
       "[1 rows x 112 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = player_match_data_ext('Immobile', 'Lazio', 'Atalanta')\n",
    "\n",
    "add_columns = test.columns\n",
    "\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3ae718d2",
   "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>matchday</th>\n",
       "      <th>player</th>\n",
       "      <th>team</th>\n",
       "      <th>oppteam</th>\n",
       "      <th>home</th>\n",
       "      <th>vote</th>\n",
       "      <th>goals</th>\n",
       "      <th>assists</th>\n",
       "      <th>cards_malus</th>\n",
       "      <th>fantavote</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Musso</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Toloi</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Djimsiti</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Hateboer</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Okoli</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10805</th>\n",
       "      <td>38</td>\n",
       "      <td>Miguel Veloso</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10806</th>\n",
       "      <td>38</td>\n",
       "      <td>Tameze</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10807</th>\n",
       "      <td>38</td>\n",
       "      <td>Sulemana I.</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10808</th>\n",
       "      <td>38</td>\n",
       "      <td>Djuric</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10809</th>\n",
       "      <td>38</td>\n",
       "      <td>Ngonge</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10810 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       matchday         player      team    oppteam  home  vote  goals  \\\n",
       "0             1          Musso  Atalanta  Sampdoria     0   6.0      0   \n",
       "1             1          Toloi  Atalanta  Sampdoria     0   7.0      1   \n",
       "2             1       Djimsiti  Atalanta  Sampdoria     0   6.0      0   \n",
       "3             1       Hateboer  Atalanta  Sampdoria     0   6.0      0   \n",
       "4             1          Okoli  Atalanta  Sampdoria     0   5.5      0   \n",
       "...         ...            ...       ...        ...   ...   ...    ...   \n",
       "10805        38  Miguel Veloso    Verona      Milan     0   5.5      0   \n",
       "10806        38         Tameze    Verona      Milan     0   5.5      0   \n",
       "10807        38    Sulemana I.    Verona      Milan     0   6.0      0   \n",
       "10808        38         Djuric    Verona      Milan     0   5.5      0   \n",
       "10809        38         Ngonge    Verona      Milan     0   5.5      0   \n",
       "\n",
       "       assists  cards_malus  fantavote  \n",
       "0            0          0.5        5.5  \n",
       "1            0          0.0       10.0  \n",
       "2            0          0.0        6.0  \n",
       "3            0          0.5        5.5  \n",
       "4            0          0.5        5.0  \n",
       "...        ...          ...        ...  \n",
       "10805        0          0.0        5.5  \n",
       "10806        0          0.0        5.5  \n",
       "10807        0          0.5        5.5  \n",
       "10808        0          0.0        5.5  \n",
       "10809        0          0.0        5.5  \n",
       "\n",
       "[10810 rows x 10 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "votes = pd.read_excel('mid_outputs/season' + season + '/players_votes.xlsx', index_col = 0)\n",
    "votes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ec0bc56b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "100\n",
      "200\n",
      "300\n",
      "400\n",
      "500\n",
      "700\n",
      "800\n",
      "900\n",
      "1000\n",
      "1100\n",
      "1300\n",
      "1400\n",
      "1500\n",
      "1600\n",
      "1700\n",
      "1800\n",
      "1900\n",
      "2000\n",
      "2200\n",
      "2300\n",
      "2400\n",
      "2500\n",
      "2600\n",
      "2700\n",
      "2800\n",
      "2900\n",
      "3000\n",
      "3100\n",
      "3200\n",
      "3300\n",
      "3400\n",
      "3500\n",
      "3600\n",
      "3700\n",
      "3800\n",
      "3900\n",
      "4000\n",
      "4100\n",
      "4200\n",
      "4300\n",
      "4400\n",
      "4500\n",
      "4600\n",
      "4700\n",
      "4800\n",
      "4900\n",
      "5000\n",
      "5100\n",
      "5200\n",
      "5300\n",
      "5400\n",
      "5500\n",
      "5600\n",
      "5700\n",
      "5800\n",
      "5900\n",
      "6000\n",
      "6100\n",
      "6200\n",
      "6300\n",
      "6400\n",
      "6500\n",
      "6600\n",
      "6700\n",
      "6800\n",
      "6900\n",
      "7000\n",
      "7100\n",
      "7300\n",
      "7400\n",
      "7500\n",
      "7600\n",
      "7700\n",
      "7800\n",
      "7900\n",
      "8000\n",
      "8100\n",
      "8200\n",
      "8300\n",
      "8400\n",
      "8500\n",
      "8600\n",
      "8700\n",
      "8800\n",
      "8900\n",
      "9000\n",
      "9100\n",
      "9200\n",
      "9300\n",
      "9400\n",
      "9500\n",
      "9600\n",
      "9700\n",
      "9800\n",
      "9900\n",
      "10000\n",
      "10100\n",
      "10200\n",
      "10300\n",
      "10400\n",
      "10500\n",
      "10600\n",
      "10700\n",
      "10800\n"
     ]
    }
   ],
   "source": [
    "db = votes.copy()\n",
    "\n",
    "delta = pd.DataFrame(columns = add_columns)\n",
    "\n",
    "for i in range(db.shape[0]):\n",
    "    ext_data = player_match_data_ext(db['player'][i], db['team'][i], db['oppteam'][i])\n",
    "    \n",
    "    if(not isinstance(ext_data, pd.DataFrame)):\n",
    "        continue\n",
    "    \n",
    "    ext_data = ext_data.rename(index = {db['player'][i] : i})\n",
    "    \n",
    "    delta = pd.concat([delta, ext_data], axis = 0)\n",
    "    \n",
    "    if(i % 100 == 0):\n",
    "        print(i)\n",
    "    \n",
    "db = pd.concat([db, delta], axis = 1)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "b292f9f9",
   "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>matchday</th>\n",
       "      <th>player</th>\n",
       "      <th>team</th>\n",
       "      <th>oppteam</th>\n",
       "      <th>home</th>\n",
       "      <th>vote</th>\n",
       "      <th>goals</th>\n",
       "      <th>assists</th>\n",
       "      <th>cards_malus</th>\n",
       "      <th>fantavote</th>\n",
       "      <th>...</th>\n",
       "      <th>miscontrols</th>\n",
       "      <th>dispossessed</th>\n",
       "      <th>fouls</th>\n",
       "      <th>fouled</th>\n",
       "      <th>aerials_won</th>\n",
       "      <th>aerials_lost</th>\n",
       "      <th>carries</th>\n",
       "      <th>progressive_carries</th>\n",
       "      <th>carries_into_final_third</th>\n",
       "      <th>carries_into_penalty_area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Musso</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Toloi</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.007543</td>\n",
       "      <td>0.001796</td>\n",
       "      <td>0.013649</td>\n",
       "      <td>0.007184</td>\n",
       "      <td>0.015445</td>\n",
       "      <td>0.011853</td>\n",
       "      <td>0.393319</td>\n",
       "      <td>0.007543</td>\n",
       "      <td>0.015086</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Djimsiti</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005565</td>\n",
       "      <td>0.002782</td>\n",
       "      <td>0.008347</td>\n",
       "      <td>0.004452</td>\n",
       "      <td>0.032832</td>\n",
       "      <td>0.023372</td>\n",
       "      <td>0.389538</td>\n",
       "      <td>0.003895</td>\n",
       "      <td>0.003895</td>\n",
       "      <td>0.000556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Hateboer</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005727</td>\n",
       "      <td>0.005011</td>\n",
       "      <td>0.013601</td>\n",
       "      <td>0.002147</td>\n",
       "      <td>0.017180</td>\n",
       "      <td>0.010021</td>\n",
       "      <td>0.284896</td>\n",
       "      <td>0.011453</td>\n",
       "      <td>0.010021</td>\n",
       "      <td>0.000716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Okoli</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.012500</td>\n",
       "      <td>0.005208</td>\n",
       "      <td>0.015625</td>\n",
       "      <td>0.008333</td>\n",
       "      <td>0.044792</td>\n",
       "      <td>0.026042</td>\n",
       "      <td>0.276042</td>\n",
       "      <td>0.004167</td>\n",
       "      <td>0.005208</td>\n",
       "      <td>0.000000</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>10805</th>\n",
       "      <td>38</td>\n",
       "      <td>Miguel Veloso</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.006019</td>\n",
       "      <td>0.006019</td>\n",
       "      <td>0.017197</td>\n",
       "      <td>0.006879</td>\n",
       "      <td>0.012038</td>\n",
       "      <td>0.012038</td>\n",
       "      <td>0.265692</td>\n",
       "      <td>0.014617</td>\n",
       "      <td>0.011178</td>\n",
       "      <td>0.000860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10806</th>\n",
       "      <td>38</td>\n",
       "      <td>Tameze</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.012867</td>\n",
       "      <td>0.011217</td>\n",
       "      <td>0.010558</td>\n",
       "      <td>0.010228</td>\n",
       "      <td>0.008908</td>\n",
       "      <td>0.010228</td>\n",
       "      <td>0.235236</td>\n",
       "      <td>0.010558</td>\n",
       "      <td>0.011217</td>\n",
       "      <td>0.001320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10807</th>\n",
       "      <td>38</td>\n",
       "      <td>Sulemana I.</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.015361</td>\n",
       "      <td>0.013825</td>\n",
       "      <td>0.016897</td>\n",
       "      <td>0.004608</td>\n",
       "      <td>0.015361</td>\n",
       "      <td>0.018433</td>\n",
       "      <td>0.201229</td>\n",
       "      <td>0.006144</td>\n",
       "      <td>0.010753</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10808</th>\n",
       "      <td>38</td>\n",
       "      <td>Djuric</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.019034</td>\n",
       "      <td>0.010981</td>\n",
       "      <td>0.017570</td>\n",
       "      <td>0.021230</td>\n",
       "      <td>0.144217</td>\n",
       "      <td>0.041728</td>\n",
       "      <td>0.191801</td>\n",
       "      <td>0.001464</td>\n",
       "      <td>0.003660</td>\n",
       "      <td>0.002196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10809</th>\n",
       "      <td>38</td>\n",
       "      <td>Ngonge</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.030872</td>\n",
       "      <td>0.016107</td>\n",
       "      <td>0.017450</td>\n",
       "      <td>0.014765</td>\n",
       "      <td>0.022819</td>\n",
       "      <td>0.046980</td>\n",
       "      <td>0.242953</td>\n",
       "      <td>0.024161</td>\n",
       "      <td>0.014765</td>\n",
       "      <td>0.012081</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10810 rows × 122 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       matchday         player      team    oppteam  home  vote  goals  \\\n",
       "0             1          Musso  Atalanta  Sampdoria     0   6.0      0   \n",
       "1             1          Toloi  Atalanta  Sampdoria     0   7.0      1   \n",
       "2             1       Djimsiti  Atalanta  Sampdoria     0   6.0      0   \n",
       "3             1       Hateboer  Atalanta  Sampdoria     0   6.0      0   \n",
       "4             1          Okoli  Atalanta  Sampdoria     0   5.5      0   \n",
       "...         ...            ...       ...        ...   ...   ...    ...   \n",
       "10805        38  Miguel Veloso    Verona      Milan     0   5.5      0   \n",
       "10806        38         Tameze    Verona      Milan     0   5.5      0   \n",
       "10807        38    Sulemana I.    Verona      Milan     0   6.0      0   \n",
       "10808        38         Djuric    Verona      Milan     0   5.5      0   \n",
       "10809        38         Ngonge    Verona      Milan     0   5.5      0   \n",
       "\n",
       "       assists  cards_malus  fantavote  ... miscontrols dispossessed  \\\n",
       "0            0          0.5        5.5  ...    0.000000     0.000000   \n",
       "1            0          0.0       10.0  ...    0.007543     0.001796   \n",
       "2            0          0.0        6.0  ...    0.005565     0.002782   \n",
       "3            0          0.5        5.5  ...    0.005727     0.005011   \n",
       "4            0          0.5        5.0  ...    0.012500     0.005208   \n",
       "...        ...          ...        ...  ...         ...          ...   \n",
       "10805        0          0.0        5.5  ...    0.006019     0.006019   \n",
       "10806        0          0.0        5.5  ...    0.012867     0.011217   \n",
       "10807        0          0.5        5.5  ...    0.015361     0.013825   \n",
       "10808        0          0.0        5.5  ...    0.019034     0.010981   \n",
       "10809        0          0.0        5.5  ...    0.030872     0.016107   \n",
       "\n",
       "          fouls    fouled  aerials_won  aerials_lost   carries  \\\n",
       "0      0.000000  0.000000     0.000000      0.000000  0.000000   \n",
       "1      0.013649  0.007184     0.015445      0.011853  0.393319   \n",
       "2      0.008347  0.004452     0.032832      0.023372  0.389538   \n",
       "3      0.013601  0.002147     0.017180      0.010021  0.284896   \n",
       "4      0.015625  0.008333     0.044792      0.026042  0.276042   \n",
       "...         ...       ...          ...           ...       ...   \n",
       "10805  0.017197  0.006879     0.012038      0.012038  0.265692   \n",
       "10806  0.010558  0.010228     0.008908      0.010228  0.235236   \n",
       "10807  0.016897  0.004608     0.015361      0.018433  0.201229   \n",
       "10808  0.017570  0.021230     0.144217      0.041728  0.191801   \n",
       "10809  0.017450  0.014765     0.022819      0.046980  0.242953   \n",
       "\n",
       "       progressive_carries  carries_into_final_third  \\\n",
       "0                 0.000000                  0.000000   \n",
       "1                 0.007543                  0.015086   \n",
       "2                 0.003895                  0.003895   \n",
       "3                 0.011453                  0.010021   \n",
       "4                 0.004167                  0.005208   \n",
       "...                    ...                       ...   \n",
       "10805             0.014617                  0.011178   \n",
       "10806             0.010558                  0.011217   \n",
       "10807             0.006144                  0.010753   \n",
       "10808             0.001464                  0.003660   \n",
       "10809             0.024161                  0.014765   \n",
       "\n",
       "       carries_into_penalty_area  \n",
       "0                       0.000000  \n",
       "1                       0.000000  \n",
       "2                       0.000556  \n",
       "3                       0.000716  \n",
       "4                       0.000000  \n",
       "...                          ...  \n",
       "10805                   0.000860  \n",
       "10806                   0.001320  \n",
       "10807                   0.000000  \n",
       "10808                   0.002196  \n",
       "10809                   0.012081  \n",
       "\n",
       "[10810 rows x 122 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1db9f77f",
   "metadata": {},
   "outputs": [],
   "source": [
    "db = db.drop(db[db.r == 'P'].index) # remove goalkeepers\n",
    "db = db.drop(db[db.r != db.r].index) # remove non extisting players\n",
    "db = db.drop(db[db.r != db.r].index) # remove non extisting players\n",
    "db = db.drop(db[db.xg != db.xg].index) \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f56df3ca",
   "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>matchday</th>\n",
       "      <th>player</th>\n",
       "      <th>team</th>\n",
       "      <th>oppteam</th>\n",
       "      <th>home</th>\n",
       "      <th>vote</th>\n",
       "      <th>goals</th>\n",
       "      <th>assists</th>\n",
       "      <th>cards_malus</th>\n",
       "      <th>fantavote</th>\n",
       "      <th>...</th>\n",
       "      <th>miscontrols</th>\n",
       "      <th>dispossessed</th>\n",
       "      <th>fouls</th>\n",
       "      <th>fouled</th>\n",
       "      <th>aerials_won</th>\n",
       "      <th>aerials_lost</th>\n",
       "      <th>carries</th>\n",
       "      <th>progressive_carries</th>\n",
       "      <th>carries_into_final_third</th>\n",
       "      <th>carries_into_penalty_area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Toloi</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.007543</td>\n",
       "      <td>0.001796</td>\n",
       "      <td>0.013649</td>\n",
       "      <td>0.007184</td>\n",
       "      <td>0.015445</td>\n",
       "      <td>0.011853</td>\n",
       "      <td>0.393319</td>\n",
       "      <td>0.007543</td>\n",
       "      <td>0.015086</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Djimsiti</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005565</td>\n",
       "      <td>0.002782</td>\n",
       "      <td>0.008347</td>\n",
       "      <td>0.004452</td>\n",
       "      <td>0.032832</td>\n",
       "      <td>0.023372</td>\n",
       "      <td>0.389538</td>\n",
       "      <td>0.003895</td>\n",
       "      <td>0.003895</td>\n",
       "      <td>0.000556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Hateboer</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005727</td>\n",
       "      <td>0.005011</td>\n",
       "      <td>0.013601</td>\n",
       "      <td>0.002147</td>\n",
       "      <td>0.017180</td>\n",
       "      <td>0.010021</td>\n",
       "      <td>0.284896</td>\n",
       "      <td>0.011453</td>\n",
       "      <td>0.010021</td>\n",
       "      <td>0.000716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Okoli</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.012500</td>\n",
       "      <td>0.005208</td>\n",
       "      <td>0.015625</td>\n",
       "      <td>0.008333</td>\n",
       "      <td>0.044792</td>\n",
       "      <td>0.026042</td>\n",
       "      <td>0.276042</td>\n",
       "      <td>0.004167</td>\n",
       "      <td>0.005208</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>Zortea</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sampdoria</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.018265</td>\n",
       "      <td>0.007610</td>\n",
       "      <td>0.016743</td>\n",
       "      <td>0.009132</td>\n",
       "      <td>0.009132</td>\n",
       "      <td>0.012177</td>\n",
       "      <td>0.420091</td>\n",
       "      <td>0.036530</td>\n",
       "      <td>0.028919</td>\n",
       "      <td>0.004566</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>10805</th>\n",
       "      <td>38</td>\n",
       "      <td>Miguel Veloso</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.006019</td>\n",
       "      <td>0.006019</td>\n",
       "      <td>0.017197</td>\n",
       "      <td>0.006879</td>\n",
       "      <td>0.012038</td>\n",
       "      <td>0.012038</td>\n",
       "      <td>0.265692</td>\n",
       "      <td>0.014617</td>\n",
       "      <td>0.011178</td>\n",
       "      <td>0.000860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10806</th>\n",
       "      <td>38</td>\n",
       "      <td>Tameze</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.012867</td>\n",
       "      <td>0.011217</td>\n",
       "      <td>0.010558</td>\n",
       "      <td>0.010228</td>\n",
       "      <td>0.008908</td>\n",
       "      <td>0.010228</td>\n",
       "      <td>0.235236</td>\n",
       "      <td>0.010558</td>\n",
       "      <td>0.011217</td>\n",
       "      <td>0.001320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10807</th>\n",
       "      <td>38</td>\n",
       "      <td>Sulemana I.</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.015361</td>\n",
       "      <td>0.013825</td>\n",
       "      <td>0.016897</td>\n",
       "      <td>0.004608</td>\n",
       "      <td>0.015361</td>\n",
       "      <td>0.018433</td>\n",
       "      <td>0.201229</td>\n",
       "      <td>0.006144</td>\n",
       "      <td>0.010753</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10808</th>\n",
       "      <td>38</td>\n",
       "      <td>Djuric</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.019034</td>\n",
       "      <td>0.010981</td>\n",
       "      <td>0.017570</td>\n",
       "      <td>0.021230</td>\n",
       "      <td>0.144217</td>\n",
       "      <td>0.041728</td>\n",
       "      <td>0.191801</td>\n",
       "      <td>0.001464</td>\n",
       "      <td>0.003660</td>\n",
       "      <td>0.002196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10809</th>\n",
       "      <td>38</td>\n",
       "      <td>Ngonge</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Milan</td>\n",
       "      <td>0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.030872</td>\n",
       "      <td>0.016107</td>\n",
       "      <td>0.017450</td>\n",
       "      <td>0.014765</td>\n",
       "      <td>0.022819</td>\n",
       "      <td>0.046980</td>\n",
       "      <td>0.242953</td>\n",
       "      <td>0.024161</td>\n",
       "      <td>0.014765</td>\n",
       "      <td>0.012081</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9769 rows × 122 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       matchday         player      team    oppteam  home  vote  goals  \\\n",
       "1             1          Toloi  Atalanta  Sampdoria     0   7.0      1   \n",
       "2             1       Djimsiti  Atalanta  Sampdoria     0   6.0      0   \n",
       "3             1       Hateboer  Atalanta  Sampdoria     0   6.0      0   \n",
       "4             1          Okoli  Atalanta  Sampdoria     0   5.5      0   \n",
       "5             1         Zortea  Atalanta  Sampdoria     0   6.0      0   \n",
       "...         ...            ...       ...        ...   ...   ...    ...   \n",
       "10805        38  Miguel Veloso    Verona      Milan     0   5.5      0   \n",
       "10806        38         Tameze    Verona      Milan     0   5.5      0   \n",
       "10807        38    Sulemana I.    Verona      Milan     0   6.0      0   \n",
       "10808        38         Djuric    Verona      Milan     0   5.5      0   \n",
       "10809        38         Ngonge    Verona      Milan     0   5.5      0   \n",
       "\n",
       "       assists  cards_malus  fantavote  ... miscontrols dispossessed  \\\n",
       "1            0          0.0       10.0  ...    0.007543     0.001796   \n",
       "2            0          0.0        6.0  ...    0.005565     0.002782   \n",
       "3            0          0.5        5.5  ...    0.005727     0.005011   \n",
       "4            0          0.5        5.0  ...    0.012500     0.005208   \n",
       "5            0          0.5        5.5  ...    0.018265     0.007610   \n",
       "...        ...          ...        ...  ...         ...          ...   \n",
       "10805        0          0.0        5.5  ...    0.006019     0.006019   \n",
       "10806        0          0.0        5.5  ...    0.012867     0.011217   \n",
       "10807        0          0.5        5.5  ...    0.015361     0.013825   \n",
       "10808        0          0.0        5.5  ...    0.019034     0.010981   \n",
       "10809        0          0.0        5.5  ...    0.030872     0.016107   \n",
       "\n",
       "          fouls    fouled  aerials_won  aerials_lost   carries  \\\n",
       "1      0.013649  0.007184     0.015445      0.011853  0.393319   \n",
       "2      0.008347  0.004452     0.032832      0.023372  0.389538   \n",
       "3      0.013601  0.002147     0.017180      0.010021  0.284896   \n",
       "4      0.015625  0.008333     0.044792      0.026042  0.276042   \n",
       "5      0.016743  0.009132     0.009132      0.012177  0.420091   \n",
       "...         ...       ...          ...           ...       ...   \n",
       "10805  0.017197  0.006879     0.012038      0.012038  0.265692   \n",
       "10806  0.010558  0.010228     0.008908      0.010228  0.235236   \n",
       "10807  0.016897  0.004608     0.015361      0.018433  0.201229   \n",
       "10808  0.017570  0.021230     0.144217      0.041728  0.191801   \n",
       "10809  0.017450  0.014765     0.022819      0.046980  0.242953   \n",
       "\n",
       "       progressive_carries  carries_into_final_third  \\\n",
       "1                 0.007543                  0.015086   \n",
       "2                 0.003895                  0.003895   \n",
       "3                 0.011453                  0.010021   \n",
       "4                 0.004167                  0.005208   \n",
       "5                 0.036530                  0.028919   \n",
       "...                    ...                       ...   \n",
       "10805             0.014617                  0.011178   \n",
       "10806             0.010558                  0.011217   \n",
       "10807             0.006144                  0.010753   \n",
       "10808             0.001464                  0.003660   \n",
       "10809             0.024161                  0.014765   \n",
       "\n",
       "       carries_into_penalty_area  \n",
       "1                       0.000000  \n",
       "2                       0.000556  \n",
       "3                       0.000716  \n",
       "4                       0.000000  \n",
       "5                       0.004566  \n",
       "...                          ...  \n",
       "10805                   0.000860  \n",
       "10806                   0.001320  \n",
       "10807                   0.000000  \n",
       "10808                   0.002196  \n",
       "10809                   0.012081  \n",
       "\n",
       "[9769 rows x 122 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "df26c8e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "db.to_excel('mid_outputs/season' + season + '/database_entries.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "626ba83b",
   "metadata": {},
   "source": [
    "Goalkeepers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "ba7b838c",
   "metadata": {},
   "outputs": [],
   "source": [
    "features_abs_gk = [\n",
    " 'gk_games',\n",
    " 'gk_games_starts',\n",
    " 'gk_minutes',\n",
    " 'gk_goals_against_per90', \n",
    " 'gk_save_pct',\n",
    " 'gk_clean_sheets_pct',\n",
    " 'gk_psxg_net_per90',\n",
    " 'gk_passes_pct_launched',\n",
    " 'gk_pct_passes_launched',\n",
    " 'gk_passes_length_avg',\n",
    " 'gk_pct_goal_kicks_launched',\n",
    " 'gk_goal_kick_length_avg',\n",
    " 'gk_crosses_stopped_pct',\n",
    " 'gk_def_actions_outside_pen_area_per90',\n",
    " 'gk_avg_distance_def_actions',\n",
    "    \n",
    " 'team_possession',\n",
    " 'team_goals_assists_per90',\n",
    " 'team_goals_pens_per90',\n",
    " 'team_goals_assists_pens_per90',\n",
    " 'team_xg_per90',\n",
    " 'team_gk_goals_against_per90',\n",
    " 'team_gk_save_pct',\n",
    " 'team_gk_clean_sheets_pct',\n",
    " 'team_passes_pct',\n",
    " 'team_passes_pct_medium',\n",
    " 'team_passes_pct_long',\n",
    " 'team_sca_per90',\n",
    " 'team_gca_per90',\n",
    " #'team_dribble_tackles_pct',\n",
    " 'team_aerials_won_pct',\n",
    " 'vs_team_possession',\n",
    " 'vs_team_goals_per90',\n",
    " 'vs_team_assists_per90',\n",
    " 'vs_team_xg_per90',\n",
    " 'vs_team_gk_save_pct',\n",
    " 'vs_team_gk_clean_sheets_pct',\n",
    " 'vs_team_gk_pct_passes_launched',\n",
    " 'vs_team_gk_crosses_stopped_pct',\n",
    " 'vs_team_shots_on_target_per90',\n",
    " 'vs_team_passes_pct',\n",
    " 'vs_team_passes_pct_short',\n",
    " 'vs_team_passes_pct_medium',\n",
    " 'vs_team_passes_pct_long',\n",
    " 'vs_team_sca_per90',\n",
    " 'vs_team_gca_per90',\n",
    " #'vs_team_dribble_tackles_pct',\n",
    " #'vs_team_dribbles_completed_pct',\n",
    " 'vs_team_aerials_won_pct',\n",
    " 'opp_team_possession',\n",
    " 'opp_team_goals_assists_per90',\n",
    " 'opp_team_goals_pens_per90',\n",
    " 'opp_team_goals_assists_pens_per90',\n",
    " 'opp_team_xg_per90',\n",
    " 'opp_team_gk_goals_against_per90',\n",
    " 'opp_team_gk_save_pct',\n",
    " 'opp_team_gk_clean_sheets_pct',\n",
    " 'opp_team_passes_pct',\n",
    " 'opp_team_passes_pct_medium',\n",
    " 'opp_team_passes_pct_long',\n",
    " 'opp_team_sca_per90',\n",
    " 'opp_team_gca_per90',\n",
    " #'opp_team_dribble_tackles_pct',\n",
    " 'opp_team_aerials_won_pct',\n",
    " 'opp_vs_team_possession',\n",
    " 'opp_vs_team_goals_per90',\n",
    " 'opp_vs_team_assists_per90',\n",
    " 'opp_vs_team_xg_per90',\n",
    " 'opp_vs_team_gk_save_pct',\n",
    " 'opp_vs_team_gk_clean_sheets_pct',\n",
    " 'opp_vs_team_gk_pct_passes_launched',\n",
    " 'opp_vs_team_gk_crosses_stopped_pct',\n",
    " 'opp_vs_team_shots_on_target_per90',\n",
    " 'opp_vs_team_passes_pct',\n",
    " 'opp_vs_team_passes_pct_short',\n",
    " 'opp_vs_team_passes_pct_medium',\n",
    " 'opp_vs_team_passes_pct_long',\n",
    " 'opp_vs_team_sca_per90',\n",
    " 'opp_vs_team_gca_per90',\n",
    " #'opp_vs_team_dribble_tackles_pct',\n",
    " #'opp_vs_team_dribbles_completed_pct',\n",
    " 'opp_vs_team_aerials_won_pct',\n",
    "                \n",
    " 'vote_avg',\n",
    " 'vote_std']\n",
    "\n",
    "features_rel_gk = [\n",
    " 'gk_shots_on_target_against',\n",
    " 'gk_saves',\n",
    " 'gk_free_kick_goals_against',\n",
    " 'gk_corner_kick_goals_against',\n",
    " 'gk_own_goals_against',\n",
    " 'gk_psxg',\n",
    " 'gk_psnpxg_per_shot_on_target_against',\n",
    " 'gk_psxg_net',\n",
    " 'gk_passes_completed_launched',\n",
    " 'gk_passes_launched',\n",
    " 'gk_passes',\n",
    " 'gk_passes_throws',\n",
    " 'gk_goal_kicks',\n",
    " 'gk_crosses',\n",
    " 'gk_crosses_stopped',\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "1d2682b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def player_match_data_ext_gk(player, pteam, oppteam):\n",
    "    pdata = player_match_data(player, pteam, oppteam)\n",
    "    \n",
    "    if(not isinstance(pdata, pd.DataFrame)):\n",
    "        return None\n",
    "    \n",
    "    if(pdata['gk_games'][0] <= 0):\n",
    "        return None\n",
    "    \n",
    "    out = pd.concat([pdata[features_abs_gk], pdata[features_rel_gk]], axis = 1)\n",
    "    \n",
    "    out[features_rel_gk] = out[features_rel_gk] / max(pdata['minutes'][0], 1)\n",
    "\n",
    "    return out\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "bc6e9d52",
   "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>gk_games</th>\n",
       "      <th>gk_games_starts</th>\n",
       "      <th>gk_minutes</th>\n",
       "      <th>gk_goals_against_per90</th>\n",
       "      <th>gk_save_pct</th>\n",
       "      <th>gk_clean_sheets_pct</th>\n",
       "      <th>gk_psxg_net_per90</th>\n",
       "      <th>gk_passes_pct_launched</th>\n",
       "      <th>gk_pct_passes_launched</th>\n",
       "      <th>gk_passes_length_avg</th>\n",
       "      <th>...</th>\n",
       "      <th>gk_psxg</th>\n",
       "      <th>gk_psnpxg_per_shot_on_target_against</th>\n",
       "      <th>gk_psxg_net</th>\n",
       "      <th>gk_passes_completed_launched</th>\n",
       "      <th>gk_passes_launched</th>\n",
       "      <th>gk_passes</th>\n",
       "      <th>gk_passes_throws</th>\n",
       "      <th>gk_goal_kicks</th>\n",
       "      <th>gk_crosses</th>\n",
       "      <th>gk_crosses_stopped</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Consigli</th>\n",
       "      <td>35</td>\n",
       "      <td>35</td>\n",
       "      <td>3150</td>\n",
       "      <td>1.57</td>\n",
       "      <td>55.6</td>\n",
       "      <td>25.7</td>\n",
       "      <td>-0.41</td>\n",
       "      <td>40.4</td>\n",
       "      <td>30.4</td>\n",
       "      <td>33.3</td>\n",
       "      <td>...</td>\n",
       "      <td>38.5</td>\n",
       "      <td>0.31</td>\n",
       "      <td>-14.5</td>\n",
       "      <td>176.0</td>\n",
       "      <td>436.0</td>\n",
       "      <td>1140.0</td>\n",
       "      <td>187.0</td>\n",
       "      <td>285.0</td>\n",
       "      <td>467.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 92 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          gk_games  gk_games_starts  gk_minutes  gk_goals_against_per90  \\\n",
       "Consigli        35               35        3150                    1.57   \n",
       "\n",
       "          gk_save_pct  gk_clean_sheets_pct  gk_psxg_net_per90  \\\n",
       "Consigli         55.6                 25.7              -0.41   \n",
       "\n",
       "          gk_passes_pct_launched  gk_pct_passes_launched  \\\n",
       "Consigli                    40.4                    30.4   \n",
       "\n",
       "          gk_passes_length_avg  ...  gk_psxg  \\\n",
       "Consigli                  33.3  ...     38.5   \n",
       "\n",
       "          gk_psnpxg_per_shot_on_target_against  gk_psxg_net  \\\n",
       "Consigli                                  0.31        -14.5   \n",
       "\n",
       "          gk_passes_completed_launched  gk_passes_launched  gk_passes  \\\n",
       "Consigli                         176.0               436.0     1140.0   \n",
       "\n",
       "          gk_passes_throws  gk_goal_kicks  gk_crosses  gk_crosses_stopped  \n",
       "Consigli             187.0          285.0       467.0                28.0  \n",
       "\n",
       "[1 rows x 92 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = player_match_data_ext_gk('Consigli', 'Sassuolo', 'Atalanta')\n",
    "\n",
    "add_columns_gk = test.columns\n",
    "\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "6f08e718",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "100\n",
      "200\n",
      "300\n",
      "400\n",
      "500\n",
      "600\n",
      "700\n",
      "800\n",
      "900\n",
      "1000\n",
      "1100\n",
      "1200\n",
      "1300\n",
      "1400\n",
      "1500\n",
      "1600\n",
      "1700\n",
      "1800\n",
      "1900\n",
      "2000\n",
      "2100\n",
      "2200\n",
      "2300\n",
      "2400\n",
      "2500\n",
      "2600\n",
      "2700\n",
      "2800\n",
      "2900\n",
      "3000\n",
      "3100\n",
      "3200\n",
      "3300\n",
      "3400\n",
      "3500\n",
      "3600\n",
      "3700\n",
      "3800\n",
      "3900\n",
      "4000\n",
      "4100\n",
      "4200\n",
      "4300\n",
      "4400\n",
      "4500\n",
      "4600\n",
      "4700\n",
      "4800\n",
      "4900\n",
      "5000\n",
      "5100\n",
      "5200\n",
      "5300\n",
      "5400\n",
      "5500\n",
      "5600\n",
      "5700\n",
      "5800\n",
      "5900\n",
      "6000\n",
      "6100\n",
      "6200\n",
      "6300\n",
      "6400\n",
      "6500\n",
      "6600\n",
      "6700\n",
      "6800\n",
      "6900\n",
      "7000\n",
      "7100\n",
      "7200\n",
      "7300\n",
      "7400\n",
      "7500\n",
      "7600\n",
      "7700\n",
      "7800\n",
      "7900\n",
      "8000\n",
      "8100\n",
      "8200\n",
      "8300\n",
      "8400\n",
      "8500\n",
      "8600\n",
      "8700\n",
      "8800\n",
      "8900\n",
      "9000\n",
      "9100\n",
      "9200\n",
      "9300\n",
      "9400\n",
      "9500\n",
      "9600\n",
      "9700\n",
      "9800\n",
      "9900\n",
      "10000\n",
      "10100\n",
      "10200\n",
      "10300\n",
      "10400\n",
      "10500\n",
      "10600\n",
      "10700\n",
      "10800\n"
     ]
    }
   ],
   "source": [
    "db_gk = votes.copy()\n",
    "\n",
    "delta = pd.DataFrame(columns = add_columns_gk)\n",
    "\n",
    "for i in range(db_gk.shape[0]):\n",
    "    ext_data = player_match_data_ext_gk(db_gk['player'][i], db_gk['team'][i], db_gk['oppteam'][i])\n",
    "    \n",
    "    if(i % 100 == 0):\n",
    "        print(i)\n",
    "        \n",
    "    if(not isinstance(ext_data, pd.DataFrame)):\n",
    "        continue\n",
    "    \n",
    "    ext_data = ext_data.rename(index = {db_gk['player'][i] : i})\n",
    "    \n",
    "    delta = pd.concat([delta, ext_data], axis = 0)\n",
    "    \n",
    "    \n",
    "    \n",
    "db_gk = pd.concat([db_gk, delta], axis = 1)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2b540670",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_gk = db_gk.drop(db_gk[db_gk.gk_games != db_gk.gk_games].index) # remove non extisting players"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "35495581",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_gk.to_excel('mid_outputs/season' + season + '/database_entries_gk.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43b85925",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}