2_votes_dataset_creation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5c9991b1",
   "metadata": {},
   "source": [
    "Generate player votes database, containing votes for Serie A matchday and player.\n",
    "\n",
    "Votes data is manually downloaded from https://www.fantacalcio.it/voti-fantacalcio-serie-a\n",
    "\n",
    "Serie A calendar is loaded from another file, to add information not containing in votes files: home/away, team opponent."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b63beb69",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a3c39376",
   "metadata": {},
   "outputs": [],
   "source": [
    "cal = np.array(pd.read_excel('fantacalcio/seriea_calendar.xlsx', header = None))\n",
    "\n",
    "cal_df = pd.DataFrame(columns = ['matchday', 'team1', 'team2'])\n",
    "\n",
    "matchday = 0\n",
    "\n",
    "for i in range(cal.shape[0]):\n",
    "    if(cal[i, 0][0].isnumeric()):\n",
    "        matchday = matchday + 1\n",
    "        continue\n",
    "    \n",
    "    teams = cal[i, 0].split('-')\n",
    "    \n",
    "    frame = pd.DataFrame([[matchday, teams[0], teams[1]]], columns = cal_df.columns)\n",
    "\n",
    "    cal_df = pd.concat([cal_df, frame], ignore_index = True)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "92d3c6fb",
   "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>team1</th>\n",
       "      <th>team2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bologna</td>\n",
       "      <td>Milan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Empoli</td>\n",
       "      <td>Verona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Frosinone</td>\n",
       "      <td>Napoli</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Genoa</td>\n",
       "      <td>Fiorentina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Inter</td>\n",
       "      <td>Monza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>375</th>\n",
       "      <td>38</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Inter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376</th>\n",
       "      <td>38</td>\n",
       "      <td>Juventus</td>\n",
       "      <td>Monza</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377</th>\n",
       "      <td>38</td>\n",
       "      <td>Lazio</td>\n",
       "      <td>Sassuolo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>38</td>\n",
       "      <td>Milan</td>\n",
       "      <td>Salernitana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>379</th>\n",
       "      <td>38</td>\n",
       "      <td>Napoli</td>\n",
       "      <td>Lecce</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>380 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    matchday      team1        team2\n",
       "0          1    Bologna        Milan\n",
       "1          1     Empoli       Verona\n",
       "2          1  Frosinone       Napoli\n",
       "3          1      Genoa   Fiorentina\n",
       "4          1      Inter        Monza\n",
       "..       ...        ...          ...\n",
       "375       38     Verona        Inter\n",
       "376       38   Juventus        Monza\n",
       "377       38      Lazio     Sassuolo\n",
       "378       38      Milan  Salernitana\n",
       "379       38     Napoli        Lecce\n",
       "\n",
       "[380 rows x 3 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cal_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0d4a330d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "loaded votes for matchday 1\n",
      "loaded votes for matchday 2\n",
      "loaded votes for matchday 3\n",
      "loaded votes for matchday 4\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame(columns = ['matchday', 'player', 'team', 'oppteam', 'home', 'vote', 'goals', 'assists', 'cards_malus', 'fantavote'])\n",
    "\n",
    "LAST_MATCH = 38\n",
    "\n",
    "for matchday in range(1, LAST_MATCH + 1):\n",
    "    \n",
    "    votes_file = 'fantacalcio/voti/Voti_Fantacalcio_Stagione_2023_24_Giornata_' + str(matchday) + '.xlsx'\n",
    "\n",
    "    try:\n",
    "        rx = np.array(pd.read_excel(votes_file, header = None))\n",
    "    except:\n",
    "        break\n",
    "\n",
    "    read = 0\n",
    "    for i in range(rx.shape[0]):\n",
    "        if(rx[i, 0] == \"Cod.\"):\n",
    "            read = 1\n",
    "            team = rx[i-1, 0];\n",
    "            continue\n",
    "\n",
    "        if(read):\n",
    "            if(isinstance(rx[i, 0], int)):\n",
    "                if((isinstance(rx[i, 3], float) or isinstance(rx[i, 3], int)) and rx[i, 1] != \"ALL\") :\n",
    "                    player = rx[i, 2];\n",
    "                    vote = float(rx[i, 3])\n",
    "                    goals = rx[i, 4] + rx[i, 8] - rx[i, 5]\n",
    "                    assists = rx[i, 12]\n",
    "                    cards_malus = rx[i, 10] * 0.5 + rx[i, 11]\n",
    "                    \n",
    "                    oppteam = ''\n",
    "                    home = 0\n",
    "                    for j in range(cal_df.shape[0]):\n",
    "                        if(cal_df['matchday'][j] == matchday):\n",
    "                            if(cal_df['team1'][j] == team):\n",
    "                                oppteam = cal_df['team2'][j] \n",
    "                                home = 1\n",
    "                            elif(cal_df['team2'][j] == team):\n",
    "                                oppteam = cal_df['team1'][j] \n",
    "                                home = 0\n",
    "\n",
    "                    goals_gen = goals * 3;\n",
    "                    if(goals < 0):\n",
    "                        goals_gen = goals\n",
    "                        \n",
    "                    fantavote = vote + goals_gen + assists - cards_malus \n",
    "                    \n",
    "                    frame = pd.DataFrame([[matchday, player, team, oppteam, home, vote, goals, assists, cards_malus, fantavote]], columns = df.columns)\n",
    "\n",
    "                    df = pd.concat([df, frame], axis = 0, ignore_index = True)\n",
    "            else:\n",
    "                read = 0\n",
    "                continue\n",
    "    \n",
    "    print('loaded votes for matchday ' + str(matchday))\n",
    "\n",
    "    df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6ade957d",
   "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>Sassuolo</td>\n",
       "      <td>0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Zappacosta</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sassuolo</td>\n",
       "      <td>0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Djimsiti</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sassuolo</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>Kolasinac</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sassuolo</td>\n",
       "      <td>0</td>\n",
       "      <td>6.5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Zortea</td>\n",
       "      <td>Atalanta</td>\n",
       "      <td>Sassuolo</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>...</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>1154</th>\n",
       "      <td>4</td>\n",
       "      <td>Serdar</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Bologna</td>\n",
       "      <td>1</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>1155</th>\n",
       "      <td>4</td>\n",
       "      <td>Suslov</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Bologna</td>\n",
       "      <td>1</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>1156</th>\n",
       "      <td>4</td>\n",
       "      <td>Bonazzoli</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Bologna</td>\n",
       "      <td>1</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>1157</th>\n",
       "      <td>4</td>\n",
       "      <td>Djuric</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Bologna</td>\n",
       "      <td>1</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>1158</th>\n",
       "      <td>4</td>\n",
       "      <td>Ngonge</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Bologna</td>\n",
       "      <td>1</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>1159 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     matchday      player      team   oppteam home  vote goals assists  \\\n",
       "0           1       Musso  Atalanta  Sassuolo    0   6.5     0       0   \n",
       "1           1  Zappacosta  Atalanta  Sassuolo    0   6.5     0       0   \n",
       "2           1    Djimsiti  Atalanta  Sassuolo    0   6.0     0       0   \n",
       "3           1   Kolasinac  Atalanta  Sassuolo    0   6.5     0       0   \n",
       "4           1      Zortea  Atalanta  Sassuolo    0   7.0     1       0   \n",
       "...       ...         ...       ...       ...  ...   ...   ...     ...   \n",
       "1154        4      Serdar    Verona   Bologna    1   6.0     0       0   \n",
       "1155        4      Suslov    Verona   Bologna    1   6.0     0       0   \n",
       "1156        4   Bonazzoli    Verona   Bologna    1   6.0     0       0   \n",
       "1157        4      Djuric    Verona   Bologna    1   5.5     0       0   \n",
       "1158        4      Ngonge    Verona   Bologna    1   5.5     0       0   \n",
       "\n",
       "      cards_malus  fantavote  \n",
       "0             0.0        6.5  \n",
       "1             0.0        6.5  \n",
       "2             0.0        6.0  \n",
       "3             0.0        6.5  \n",
       "4             0.0       10.0  \n",
       "...           ...        ...  \n",
       "1154          0.5        5.5  \n",
       "1155          0.5        5.5  \n",
       "1156          0.0        6.0  \n",
       "1157          0.0        5.5  \n",
       "1158          0.0        5.5  \n",
       "\n",
       "[1159 rows x 10 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "230213fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('mid_outputs/players_votes.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef91ece3",
   "metadata": {},
   "source": [
    "Elaborate data for past seasons.\n",
    "\n",
    "Do not repeat if data is already present."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "da35bdf9",
   "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>team1</th>\n",
       "      <th>team2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Verona</td>\n",
       "      <td>Sassuolo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Inter</td>\n",
       "      <td>Genoa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Empoli</td>\n",
       "      <td>Lazio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Torino</td>\n",
       "      <td>Atalanta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Bologna</td>\n",
       "      <td>Salernitana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>375</th>\n",
       "      <td>38</td>\n",
       "      <td>Spezia</td>\n",
       "      <td>Napoli</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376</th>\n",
       "      <td>38</td>\n",
       "      <td>Sassuolo</td>\n",
       "      <td>Milan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377</th>\n",
       "      <td>38</td>\n",
       "      <td>Inter</td>\n",
       "      <td>Sampdoria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>38</td>\n",
       "      <td>Salernitana</td>\n",
       "      <td>Udinese</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>379</th>\n",
       "      <td>38</td>\n",
       "      <td>Venezia</td>\n",
       "      <td>Cagliari</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>380 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    matchday        team1        team2\n",
       "0          1       Verona     Sassuolo\n",
       "1          1        Inter        Genoa\n",
       "2          1       Empoli        Lazio\n",
       "3          1       Torino     Atalanta\n",
       "4          1      Bologna  Salernitana\n",
       "..       ...          ...          ...\n",
       "375       38       Spezia       Napoli\n",
       "376       38     Sassuolo        Milan\n",
       "377       38        Inter    Sampdoria\n",
       "378       38  Salernitana      Udinese\n",
       "379       38      Venezia     Cagliari\n",
       "\n",
       "[380 rows x 3 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# BACKUP CODE FOR SEASON 2122 and 2021 (different calendar file format than 2223 and 2324)\n",
    "\n",
    "cal_df = pd.read_excel('fantacalcio/season2122/seriea_calendar.xlsx')\n",
    "\n",
    "to_drop = list()\n",
    "\n",
    "for i in range(cal_df.shape[0]):\n",
    "    if(not isinstance(cal_df['matchday'][i], int)):\n",
    "        to_drop.append(i)\n",
    "    \n",
    "cal_df = cal_df.drop(to_drop)\n",
    "\n",
    "cal_df = cal_df.reset_index(drop = True)\n",
    "\n",
    "cal_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "a0b100da",
   "metadata": {},
   "outputs": [],
   "source": [
    "cal = np.array(pd.read_excel('fantacalcio/season2223/seriea_calendar.xlsx', header = None))\n",
    "\n",
    "cal_df = pd.DataFrame(columns = ['matchday', 'team1', 'team2'])\n",
    "\n",
    "matchday = 0\n",
    "\n",
    "for i in range(cal.shape[0]):\n",
    "    if(cal[i, 0][0].isnumeric()):\n",
    "        matchday = matchday + 1\n",
    "        continue\n",
    "    \n",
    "    teams = cal[i, 0].split('-')\n",
    "    \n",
    "    frame = pd.DataFrame([[matchday, teams[0], teams[1]]], columns = cal_df.columns)\n",
    "\n",
    "    cal_df = pd.concat([cal_df, frame], ignore_index = True)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "0313a0f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(columns = ['matchday', 'player', 'team', 'oppteam', 'home', 'vote', 'goals', 'assists', 'cards_malus', 'fantavote'])\n",
    "\n",
    "LAST_MATCH = 38\n",
    "\n",
    "for matchday in range(1, LAST_MATCH + 1):\n",
    "    \n",
    "    votes_file = 'fantacalcio/season2223/voti/Voti_Fantacalcio_Stagione_2022_23_Giornata_' + str(matchday) + '.xlsx'\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    rx = np.array(pd.read_excel(votes_file, header = None))\n",
    "\n",
    "    read = 0\n",
    "    for i in range(rx.shape[0]):\n",
    "        if(rx[i, 0] == \"Cod.\"):\n",
    "            read = 1\n",
    "            team = rx[i-1, 0];\n",
    "            continue\n",
    "\n",
    "        if(read):\n",
    "            if(isinstance(rx[i, 0], int)):\n",
    "                if((isinstance(rx[i, 3], float) or isinstance(rx[i, 3], int)) and rx[i, 1] != \"ALL\") :\n",
    "                    player = rx[i, 2];\n",
    "                    vote = float(rx[i, 3])\n",
    "                    goals = rx[i, 4] + rx[i, 8] - rx[i, 5]\n",
    "                    assists = rx[i, 12]\n",
    "                    cards_malus = rx[i, 10] * 0.5 + rx[i, 11]\n",
    "                    \n",
    "                    oppteam = ''\n",
    "                    home = 0\n",
    "                    for j in range(cal_df.shape[0]):\n",
    "                        if(cal_df['matchday'][j] == matchday):\n",
    "                            if(cal_df['team1'][j] == team):\n",
    "                                oppteam = cal_df['team2'][j] \n",
    "                                home = 1\n",
    "                            elif(cal_df['team2'][j] == team):\n",
    "                                oppteam = cal_df['team1'][j] \n",
    "                                home = 0\n",
    "\n",
    "                    goals_gen = goals * 3;\n",
    "                    if(goals < 0):\n",
    "                        goals_gen = goals\n",
    "                        \n",
    "                    fantavote = vote + goals_gen + assists - cards_malus \n",
    "                    \n",
    "                    frame = pd.DataFrame([[matchday, player, team, oppteam, home, vote, goals, assists, cards_malus, fantavote]], columns = df.columns)\n",
    "\n",
    "                    df = pd.concat([df, frame], axis = 0, ignore_index = True)\n",
    "            else:\n",
    "                read = 0\n",
    "                continue\n",
    "\n",
    "    df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "82b1cfc8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('mid_outputs/season2223/players_votes.xlsx')"
   ]
  }
 ],
 "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
}