agg_featselect_player_team_att.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "dir = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "league_df = pd.read_csv(dir + \"league.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_df = pd.read_csv(dir + \"player_attributes_by_match.csv\")\n",
    "\n",
    "player_by_match_df = player_by_match_df.drop('Unnamed: 0',axis=1)\n",
    "\n",
    "player_by_match_df = player_by_match_df.drop([\"preferred_foot\", \"day_diff\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'match_id', 'player_id', 'overall_rating', 'potential',\n",
       "       'crossing', 'finishing', 'heading_accuracy', 'short_passing',\n",
       "       'dribbling', 'free_kick_accuracy', 'long_passing', 'ball_control',\n",
       "       'acceleration', 'sprint_speed', 'reactions', 'shot_power', 'stamina',\n",
       "       'strength', 'long_shots', 'aggression', 'interceptions', 'positioning',\n",
       "       'penalties', 'marking', 'standing_tackle', 'gk_diving', 'gk_handling',\n",
       "       'gk_kicking', 'gk_positioning', 'gk_reflexes',\n",
       "       'attacking_work_rate_imp', 'defensive_work_rate_imp', 'volleys_imp',\n",
       "       'curve_imp', 'agility_imp', 'balance_imp', 'jumping_imp', 'vision_imp',\n",
       "       'sliding_tackle_imp', 'team_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "match_df = pd.read_csv(dir + \"match_clean.csv\")\n",
    "\n",
    "match_df = match_df.drop('Unnamed: 0',axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    541991\n",
       "2        13\n",
       "Name: team_id, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_df.groupby([\"match_id\", \"player_id\"])[\"team_id\"].nunique().value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Odds by match"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['B365H', 'B365D', 'B365A']\n",
      "['BWH', 'BWD', 'BWA']\n",
      "['IWH', 'IWD', 'IWA']\n",
      "['LBH', 'LBD', 'LBA']\n",
      "['PSH', 'PSD', 'PSA']\n",
      "['WHH', 'WHD', 'WHA']\n",
      "['SJH', 'SJD', 'SJA']\n",
      "['VCH', 'VCD', 'VCA']\n",
      "['GBH', 'GBD', 'GBA']\n",
      "['BSH', 'BSD', 'BSA']\n"
     ]
    }
   ],
   "source": [
    "odds_col = match_df.columns[32:63].tolist()\n",
    "\n",
    "odds_df = match_df[[\"date\", \"match_id\", \"home_team_id\", \"away_team_id\"]]\n",
    "\n",
    "for i in range(0, len(odds_col), 3):\n",
    "    print(odds_col[i:i+3])\n",
    "    \n",
    "    cur_odds_df = match_df.loc[~match_df[odds_col[i]].isnull(), \n",
    "                               [\"date\", \"match_id\", \"home_team_id\"] + odds_col[i:i+3]]\n",
    "    \n",
    "    inv_odds = (1/cur_odds_df[odds_col[i:i+3]])\n",
    "\n",
    "    booksum = inv_odds.sum(axis=1)\n",
    "\n",
    "    margin = booksum - 1\n",
    "\n",
    "    inv_odds[\"booksum\"] = booksum\n",
    "\n",
    "    bookprob = pd.concat([inv_odds[odds_col[i]]/inv_odds[\"booksum\"], \n",
    "                          inv_odds[odds_col[i+1]]/inv_odds[\"booksum\"], \n",
    "                          inv_odds[odds_col[i+2]]/inv_odds[\"booksum\"]], axis=1)\n",
    "\n",
    "    bookprob.columns = odds_col[i:i+3]\n",
    "    \n",
    "    bookprob[\"{}_margin\".format(odds_col[i][:-1])] = margin\n",
    "    \n",
    "    bookprob[[\"date\", \"match_id\", \"home_team_id\"]] = cur_odds_df[[\"date\", \"match_id\", \"home_team_id\"]]\n",
    "    \n",
    "    odds_df = odds_df.merge(bookprob, \n",
    "                            on=[\"date\", \"match_id\", \"home_team_id\"],\n",
    "                            how=\"left\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Stack Match Result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    26231\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_df.groupby(\"match_id\").size().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['country_id', 'league_id', 'season', 'stage', 'date', 'match_id',\n",
       "       'home_team_id', 'away_team_id', 'home_team_goal', 'away_team_goal',\n",
       "       'home_player_1', 'home_player_2', 'home_player_3', 'home_player_4',\n",
       "       'home_player_5', 'home_player_6', 'home_player_7', 'home_player_8',\n",
       "       'home_player_9', 'home_player_10', 'home_player_11', 'away_player_1',\n",
       "       'away_player_2', 'away_player_3', 'away_player_4', 'away_player_5',\n",
       "       'away_player_6', 'away_player_7', 'away_player_8', 'away_player_9',\n",
       "       'away_player_10', 'away_player_11', 'B365H', 'B365D', 'B365A', 'BWH',\n",
       "       'BWD', 'BWA', 'IWH', 'IWD', 'IWA', 'LBH', 'LBD', 'LBA', 'PSH', 'PSD',\n",
       "       'PSA', 'WHH', 'WHD', 'WHA', 'SJH', 'SJD', 'SJA', 'VCH', 'VCD', 'VCA',\n",
       "       'GBH', 'GBD', 'GBA', 'BSH', 'BSD', 'BSA'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-12-39cd4820d52d>:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\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",
      "  starting_players_df[\"total_missing\"] = match_df[starting_players_col].isnull().sum(axis=1)\n",
      "<ipython-input-12-39cd4820d52d>:7: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\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",
      "  starting_players_df[\"none_missing\"] = np.where(starting_players_df[\"total_missing\"]==0,1,0)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.8144180549731234"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "starting_players_col = list(match_df.columns[10:32])\n",
    "\n",
    "starting_players_df = match_df[[\"match_id\"] + starting_players_col]\n",
    "\n",
    "starting_players_df[\"total_missing\"] = match_df[starting_players_col].isnull().sum(axis=1)\n",
    "\n",
    "starting_players_df[\"none_missing\"] = np.where(starting_players_df[\"total_missing\"]==0,1,0)\n",
    "\n",
    "starting_players_df[\"none_missing\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8144180549731234"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_df[\"no_player_missing\"] = starting_players_df[\"none_missing\"]\n",
    "\n",
    "match_df[\"no_player_missing\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>league_id</th>\n",
       "      <th>country_id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Belgium Jupiler League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1729</td>\n",
       "      <td>1729</td>\n",
       "      <td>England Premier League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4769</td>\n",
       "      <td>4769</td>\n",
       "      <td>France Ligue 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7809</td>\n",
       "      <td>7809</td>\n",
       "      <td>Germany 1. Bundesliga</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10257</td>\n",
       "      <td>10257</td>\n",
       "      <td>Italy Serie A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>13274</td>\n",
       "      <td>13274</td>\n",
       "      <td>Netherlands Eredivisie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>15722</td>\n",
       "      <td>15722</td>\n",
       "      <td>Poland Ekstraklasa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>17642</td>\n",
       "      <td>17642</td>\n",
       "      <td>Portugal Liga ZON Sagres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>19694</td>\n",
       "      <td>19694</td>\n",
       "      <td>Scotland Premier League</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>21518</td>\n",
       "      <td>21518</td>\n",
       "      <td>Spain LIGA BBVA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>24558</td>\n",
       "      <td>24558</td>\n",
       "      <td>Switzerland Super League</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    league_id  country_id                      name\n",
       "0           1           1    Belgium Jupiler League\n",
       "1        1729        1729    England Premier League\n",
       "2        4769        4769            France Ligue 1\n",
       "3        7809        7809     Germany 1. Bundesliga\n",
       "4       10257       10257             Italy Serie A\n",
       "5       13274       13274    Netherlands Eredivisie\n",
       "6       15722       15722        Poland Ekstraklasa\n",
       "7       17642       17642  Portugal Liga ZON Sagres\n",
       "8       19694       19694   Scotland Premier League\n",
       "9       21518       21518           Spain LIGA BBVA\n",
       "10      24558       24558  Switzerland Super League"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "league_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "league_id\n",
       "1        0.615542\n",
       "1729     0.974342\n",
       "4769     0.942105\n",
       "7809     0.970588\n",
       "10257    0.903321\n",
       "13274    0.831291\n",
       "15722    0.244271\n",
       "17642    0.623782\n",
       "19694    0.844846\n",
       "21518    0.890461\n",
       "24558    0.828411\n",
       "Name: no_player_missing, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_df.groupby(\"league_id\")[\"no_player_missing\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "season\n",
       "2008/2009    0.530066\n",
       "2009/2010    0.762229\n",
       "2010/2011    0.825767\n",
       "2011/2012    0.863706\n",
       "2012/2013    0.883436\n",
       "2013/2014    0.848466\n",
       "2014/2015    0.896272\n",
       "2015/2016    0.907396\n",
       "Name: no_player_missing, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_df.groupby(\"season\")[\"no_player_missing\"].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "match_df = match_df.loc[match_df[\"no_player_missing\"]==1]\n",
    "\n",
    "match_df = match_df.drop(\"no_player_missing\", axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "match_df[\"home_result\"] = 0\n",
    "match_df.loc[match_df[\"home_team_goal\"] > match_df[\"away_team_goal\"], \"home_result\"] = 1\n",
    "match_df.loc[match_df[\"home_team_goal\"] < match_df[\"away_team_goal\"], \"home_result\"] = -1\n",
    "\n",
    "match_df[\"away_result\"] = 0\n",
    "match_df.loc[match_df[\"home_team_goal\"] < match_df[\"away_team_goal\"], \"away_result\"] = 1\n",
    "match_df.loc[match_df[\"home_team_goal\"] > match_df[\"away_team_goal\"], \"away_result\"] = -1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(21363, 6)\n",
      "(21363, 6)\n",
      "(42726, 6)\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-19-27912321809a>:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\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",
      "  stacked_match_df[\"home\"] = 1\n",
      "<ipython-input-19-27912321809a>:11: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\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",
      "  temp[\"home\"] = 0\n"
     ]
    }
   ],
   "source": [
    "stacked_match_df = match_df[[\"date\", \"league_id\", \"match_id\", \"home_team_id\", \"home_result\"]]\n",
    "\n",
    "stacked_match_df[\"home\"] = 1\n",
    "\n",
    "stacked_match_df.columns = [\"date\", \"league_id\", \"match_id\", \"team_id\", \"result\", \"home\"]\n",
    "\n",
    "print(stacked_match_df.shape)\n",
    "\n",
    "temp = match_df[[\"date\", \"league_id\", \"match_id\", \"away_team_id\", \"away_result\"]]\n",
    "\n",
    "temp[\"home\"] = 0\n",
    "\n",
    "temp.columns = [\"date\", \"league_id\", \"match_id\", \"team_id\", \"result\", \"home\"]\n",
    "\n",
    "print(temp.shape)\n",
    "\n",
    "stacked_match_df = pd.concat([stacked_match_df, temp])\n",
    "\n",
    "print(stacked_match_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "stacked_match_df = stacked_match_df.sort_values([\"date\", \"match_id\", \"home\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_df = player_by_match_df[[\"date\"]+ player_by_match_df.select_dtypes([np.number]).columns.tolist()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>date</th>\n",
       "      <th>league_id</th>\n",
       "      <th>match_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>result</th>\n",
       "      <th>home</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3716</th>\n",
       "      <td>2009-09-12</td>\n",
       "      <td>1</td>\n",
       "      <td>665362</td>\n",
       "      <td>8203</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3716</th>\n",
       "      <td>2009-09-12</td>\n",
       "      <td>1</td>\n",
       "      <td>665362</td>\n",
       "      <td>9985</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            date  league_id  match_id  team_id  result  home\n",
       "3716  2009-09-12          1    665362     8203      -1     0\n",
       "3716  2009-09-12          1    665362     9985       1     1"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked_match_df.loc[stacked_match_df[\"match_id\"]==665362]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(player_by_match_df.loc[player_by_match_df[\"match_id\"]==665362, \"player_id\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(42726, 6)\n",
      "(469985, 44)\n"
     ]
    }
   ],
   "source": [
    "print(stacked_match_df.shape)\n",
    "stacked_match_df = stacked_match_df.merge(player_by_match_df, on=[\"date\", \"match_id\", \"team_id\"])\n",
    "print(stacked_match_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "stacked_match_df = stacked_match_df.loc[stacked_match_df[\"match_id\"]!=665362]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    21362\n",
       "Name: result, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked_match_df.groupby(\"match_id\")[\"result\"].mean().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5    21362\n",
       "Name: home, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked_match_df.groupby(\"match_id\")[\"home\"].mean().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['overall_rating', 'potential', 'crossing', 'finishing',\n",
       "       'heading_accuracy', 'short_passing', 'dribbling', 'free_kick_accuracy',\n",
       "       'long_passing', 'ball_control', 'acceleration', 'sprint_speed',\n",
       "       'reactions', 'shot_power', 'stamina', 'strength', 'long_shots',\n",
       "       'aggression', 'interceptions', 'positioning', 'penalties', 'marking',\n",
       "       'standing_tackle', 'gk_diving', 'gk_handling', 'gk_kicking',\n",
       "       'gk_positioning', 'gk_reflexes', 'attacking_work_rate_imp',\n",
       "       'defensive_work_rate_imp', 'volleys_imp', 'curve_imp', 'agility_imp',\n",
       "       'balance_imp', 'jumping_imp', 'vision_imp', 'sliding_tackle_imp'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_col = stacked_match_df.columns[7:]\n",
    "\n",
    "feat_df = stacked_match_df[stacked_match_df.columns[:6]].drop_duplicates()\n",
    "\n",
    "feat_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(42724, 6)\n",
      "(42724, 7)\n",
      "(42724, 7)\n",
      "(42724, 8)\n",
      "(42724, 8)\n",
      "(42724, 9)\n",
      "(42724, 9)\n",
      "(42724, 10)\n",
      "(42724, 10)\n",
      "(42724, 11)\n",
      "(42724, 11)\n",
      "(42724, 12)\n",
      "(42724, 12)\n",
      "(42724, 13)\n",
      "(42724, 13)\n",
      "(42724, 14)\n",
      "(42724, 14)\n",
      "(42724, 15)\n",
      "(42724, 15)\n",
      "(42724, 16)\n",
      "(42724, 16)\n",
      "(42724, 17)\n",
      "(42724, 17)\n",
      "(42724, 18)\n",
      "(42724, 18)\n",
      "(42724, 19)\n",
      "(42724, 19)\n",
      "(42724, 20)\n",
      "(42724, 20)\n",
      "(42724, 21)\n",
      "(42724, 21)\n",
      "(42724, 22)\n",
      "(42724, 22)\n",
      "(42724, 23)\n",
      "(42724, 23)\n",
      "(42724, 24)\n",
      "(42724, 24)\n",
      "(42724, 25)\n",
      "(42724, 25)\n",
      "(42724, 26)\n",
      "(42724, 26)\n",
      "(42724, 27)\n",
      "(42724, 27)\n",
      "(42724, 28)\n",
      "(42724, 28)\n",
      "(42724, 29)\n",
      "(42724, 29)\n",
      "(42724, 30)\n",
      "(42724, 30)\n",
      "(42724, 31)\n",
      "(42724, 31)\n",
      "(42724, 32)\n",
      "(42724, 32)\n",
      "(42724, 33)\n",
      "(42724, 33)\n",
      "(42724, 34)\n",
      "(42724, 34)\n",
      "(42724, 35)\n",
      "(42724, 35)\n",
      "(42724, 36)\n",
      "(42724, 36)\n",
      "(42724, 37)\n",
      "(42724, 37)\n",
      "(42724, 38)\n",
      "(42724, 38)\n",
      "(42724, 39)\n",
      "(42724, 39)\n",
      "(42724, 40)\n",
      "(42724, 40)\n",
      "(42724, 41)\n",
      "(42724, 41)\n",
      "(42724, 42)\n",
      "(42724, 42)\n",
      "(42724, 43)\n"
     ]
    }
   ],
   "source": [
    "for col in feat_col:\n",
    "    \n",
    "    if \"gk_\" in col:\n",
    "        \n",
    "        max_df = (stacked_match_df.groupby([\"date\", \"match_id\", \"team_id\"])[col].max().reset_index())\n",
    "\n",
    "        max_df.columns = [\"date\", \"match_id\", \"team_id\", \"max_{}\".format(col)]\n",
    "        \n",
    "        print(feat_df.shape)\n",
    "        feat_df = feat_df.merge(max_df, on=[\"date\", \"match_id\", \"team_id\"])\n",
    "        print(feat_df.shape)\n",
    "        \n",
    "    else:\n",
    "        avg_df = (stacked_match_df.groupby([\"date\", \"match_id\", \"team_id\"])[col].mean().reset_index())\n",
    "\n",
    "        avg_df.columns = [\"date\", \"match_id\", \"team_id\", \"avg_{}\".format(col)]\n",
    "    \n",
    "        print(feat_df.shape)\n",
    "        feat_df = feat_df.merge(avg_df, on=[\"date\", \"match_id\", \"team_id\"])\n",
    "        print(feat_df.shape)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(42724, 43)\n",
      "(42724, 44)\n",
      "(42724, 44)\n",
      "(42724, 45)\n",
      "(42724, 45)\n",
      "(42724, 46)\n",
      "(42724, 46)\n",
      "(42724, 47)\n",
      "(42724, 47)\n",
      "(42724, 48)\n",
      "(42724, 48)\n",
      "(42724, 49)\n",
      "(42724, 49)\n",
      "(42724, 50)\n",
      "(42724, 50)\n",
      "(42724, 51)\n",
      "(42724, 51)\n",
      "(42724, 52)\n",
      "(42724, 52)\n",
      "(42724, 53)\n",
      "(42724, 53)\n",
      "(42724, 54)\n",
      "(42724, 54)\n",
      "(42724, 55)\n",
      "(42724, 55)\n",
      "(42724, 56)\n",
      "(42724, 56)\n",
      "(42724, 57)\n",
      "(42724, 57)\n",
      "(42724, 58)\n",
      "(42724, 58)\n",
      "(42724, 59)\n",
      "(42724, 59)\n",
      "(42724, 60)\n",
      "(42724, 60)\n",
      "(42724, 61)\n",
      "(42724, 61)\n",
      "(42724, 62)\n",
      "(42724, 62)\n",
      "(42724, 63)\n",
      "(42724, 63)\n",
      "(42724, 64)\n",
      "(42724, 64)\n",
      "(42724, 65)\n",
      "(42724, 65)\n",
      "(42724, 66)\n",
      "(42724, 66)\n",
      "(42724, 67)\n",
      "(42724, 67)\n",
      "(42724, 68)\n",
      "(42724, 68)\n",
      "(42724, 69)\n",
      "(42724, 69)\n",
      "(42724, 70)\n",
      "(42724, 70)\n",
      "(42724, 71)\n",
      "(42724, 71)\n",
      "(42724, 72)\n",
      "(42724, 72)\n",
      "(42724, 73)\n",
      "(42724, 73)\n",
      "(42724, 74)\n",
      "(42724, 74)\n",
      "(42724, 75)\n"
     ]
    }
   ],
   "source": [
    "feat_col = stacked_match_df.columns[7:]\n",
    "\n",
    "for col in feat_col:\n",
    "    \n",
    "    if \"gk_\" not in col:\n",
    "        top3avg_df = (stacked_match_df\n",
    "                      .sort_values(col)\n",
    "                      .groupby([\"date\", \"match_id\", \"team_id\"])\n",
    "                      .tail(3)\n",
    "                      .groupby([\"date\", \"match_id\", \"team_id\"])[col].mean()\n",
    "                      .reset_index())\n",
    "\n",
    "        top3avg_df.columns = [\"date\", \"match_id\", \"team_id\", \"top3avg_{}\".format(col)]\n",
    "\n",
    "        print(feat_df.shape)\n",
    "        feat_df = feat_df.merge(top3avg_df, on=[\"date\", \"match_id\", \"team_id\"])\n",
    "        print(feat_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'league_id', 'match_id', 'team_id', 'result', 'home',\n",
       "       'avg_overall_rating', 'avg_potential', 'avg_crossing', 'avg_finishing',\n",
       "       'avg_heading_accuracy', 'avg_short_passing', 'avg_dribbling',\n",
       "       'avg_free_kick_accuracy', 'avg_long_passing', 'avg_ball_control',\n",
       "       'avg_acceleration', 'avg_sprint_speed', 'avg_reactions',\n",
       "       'avg_shot_power', 'avg_stamina', 'avg_strength', 'avg_long_shots',\n",
       "       'avg_aggression', 'avg_interceptions', 'avg_positioning',\n",
       "       'avg_penalties', 'avg_marking', 'avg_standing_tackle', 'max_gk_diving',\n",
       "       'max_gk_handling', 'max_gk_kicking', 'max_gk_positioning',\n",
       "       'max_gk_reflexes', 'avg_attacking_work_rate_imp',\n",
       "       'avg_defensive_work_rate_imp', 'avg_volleys_imp', 'avg_curve_imp',\n",
       "       'avg_agility_imp', 'avg_balance_imp', 'avg_jumping_imp',\n",
       "       'avg_vision_imp', 'avg_sliding_tackle_imp', 'top3avg_overall_rating',\n",
       "       'top3avg_potential', 'top3avg_crossing', 'top3avg_finishing',\n",
       "       'top3avg_heading_accuracy', 'top3avg_short_passing',\n",
       "       'top3avg_dribbling', 'top3avg_free_kick_accuracy',\n",
       "       'top3avg_long_passing', 'top3avg_ball_control', 'top3avg_acceleration',\n",
       "       'top3avg_sprint_speed', 'top3avg_reactions', 'top3avg_shot_power',\n",
       "       'top3avg_stamina', 'top3avg_strength', 'top3avg_long_shots',\n",
       "       'top3avg_aggression', 'top3avg_interceptions', 'top3avg_positioning',\n",
       "       'top3avg_penalties', 'top3avg_marking', 'top3avg_standing_tackle',\n",
       "       'top3avg_attacking_work_rate_imp', 'top3avg_defensive_work_rate_imp',\n",
       "       'top3avg_volleys_imp', 'top3avg_curve_imp', 'top3avg_agility_imp',\n",
       "       'top3avg_balance_imp', 'top3avg_jumping_imp', 'top3avg_vision_imp',\n",
       "       'top3avg_sliding_tackle_imp'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(42724, 75)\n",
      "(42724, 76)\n"
     ]
    }
   ],
   "source": [
    "print(feat_df.shape)\n",
    "feat_df = feat_df.merge(match_df[[\"season\", \"match_id\"]].drop_duplicates(), on=\"match_id\", how=\"left\")\n",
    "print(feat_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df.to_csv(dir+\"feat_df.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Stacked matches"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df = pd.read_csv(dir+\"feat_df.csv\")\n",
    "\n",
    "feat_df = feat_df.drop(\"Unnamed: 0\", axis=1)\n",
    "\n",
    "feat_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for l in feat_df[\"league_id\"].unique():\n",
    "    for s in feat_df[\"season\"].unique():\n",
    "        for col in feat_df.columns[6:-1]:\n",
    "            \n",
    "            mu = feat_df.loc[(feat_df[\"season\"]==s) & (feat_df[\"league_id\"]==l), col].mean()\n",
    "            sd = feat_df.loc[(feat_df[\"season\"]==s) & (feat_df[\"league_id\"]==l), col].std()\n",
    "            \n",
    "            feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                        (feat_df[\"league_id\"]==l), col] = feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                                                                      (feat_df[\"league_id\"]==l), col] - mu\n",
    "            \n",
    "            feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                        (feat_df[\"league_id\"]==l), col] = feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                                                                      (feat_df[\"league_id\"]==l), col] / sd\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df.groupby(\"season\").size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df.groupby(\"season\")[\"match_id\"].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seasons = feat_df[\"season\"].unique()\n",
    "\n",
    "ctree_df = feat_df.loc[feat_df[\"season\"].isin(seasons[0:4])]\n",
    "ctree_df[\"fold\"] = 1\n",
    "ctree_df[\"test_set\"] = 1\n",
    "ctree_df.loc[ctree_df[\"season\"].isin(seasons[0:3]), \"test_set\"] = 0\n",
    "\n",
    "for i in range(1, len(seasons)-4):\n",
    "    c_df = feat_df.loc[feat_df[\"season\"].isin(seasons[i:i+4])]\n",
    "    c_df[\"fold\"] = i+1\n",
    "    c_df[\"test_set\"] = 1\n",
    "    c_df.loc[c_df[\"season\"].isin(seasons[i:i+3]), \"test_set\"] = 0\n",
    "    \n",
    "    ctree_df = pd.concat([ctree_df,c_df])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctree_df.groupby([\"fold\", \"test_set\"]).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctree_df = ctree_df[ctree_df.columns[4:]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctree_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctree_df.to_csv(dir+\"ctree_df.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Home matches/difference in attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'league_id', 'match_id', 'team_id', 'result', 'home',\n",
       "       'avg_overall_rating', 'avg_potential', 'avg_crossing', 'avg_finishing',\n",
       "       'avg_heading_accuracy', 'avg_short_passing', 'avg_dribbling',\n",
       "       'avg_free_kick_accuracy', 'avg_long_passing', 'avg_ball_control',\n",
       "       'avg_acceleration', 'avg_sprint_speed', 'avg_reactions',\n",
       "       'avg_shot_power', 'avg_stamina', 'avg_strength', 'avg_long_shots',\n",
       "       'avg_aggression', 'avg_interceptions', 'avg_positioning',\n",
       "       'avg_penalties', 'avg_marking', 'avg_standing_tackle', 'max_gk_diving',\n",
       "       'max_gk_handling', 'max_gk_kicking', 'max_gk_positioning',\n",
       "       'max_gk_reflexes', 'avg_attacking_work_rate_imp',\n",
       "       'avg_defensive_work_rate_imp', 'avg_volleys_imp', 'avg_curve_imp',\n",
       "       'avg_agility_imp', 'avg_balance_imp', 'avg_jumping_imp',\n",
       "       'avg_vision_imp', 'avg_sliding_tackle_imp', 'top3avg_overall_rating',\n",
       "       'top3avg_potential', 'top3avg_crossing', 'top3avg_finishing',\n",
       "       'top3avg_heading_accuracy', 'top3avg_short_passing',\n",
       "       'top3avg_dribbling', 'top3avg_free_kick_accuracy',\n",
       "       'top3avg_long_passing', 'top3avg_ball_control', 'top3avg_acceleration',\n",
       "       'top3avg_sprint_speed', 'top3avg_reactions', 'top3avg_shot_power',\n",
       "       'top3avg_stamina', 'top3avg_strength', 'top3avg_long_shots',\n",
       "       'top3avg_aggression', 'top3avg_interceptions', 'top3avg_positioning',\n",
       "       'top3avg_penalties', 'top3avg_marking', 'top3avg_standing_tackle',\n",
       "       'top3avg_attacking_work_rate_imp', 'top3avg_defensive_work_rate_imp',\n",
       "       'top3avg_volleys_imp', 'top3avg_curve_imp', 'top3avg_agility_imp',\n",
       "       'top3avg_balance_imp', 'top3avg_jumping_imp', 'top3avg_vision_imp',\n",
       "       'top3avg_sliding_tackle_imp', 'season'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df = pd.read_csv(dir+\"feat_df.csv\")\n",
    "\n",
    "feat_df = feat_df.drop(\"Unnamed: 0\", axis=1)\n",
    "\n",
    "feat_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df = feat_df.sort_values([\"match_id\", \"home\"], ascending=[True, True])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>league_id</th>\n",
       "      <th>match_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>result</th>\n",
       "      <th>home</th>\n",
       "      <th>avg_overall_rating</th>\n",
       "      <th>avg_potential</th>\n",
       "      <th>avg_crossing</th>\n",
       "      <th>avg_finishing</th>\n",
       "      <th>...</th>\n",
       "      <th>top3avg_attacking_work_rate_imp</th>\n",
       "      <th>top3avg_defensive_work_rate_imp</th>\n",
       "      <th>top3avg_volleys_imp</th>\n",
       "      <th>top3avg_curve_imp</th>\n",
       "      <th>top3avg_agility_imp</th>\n",
       "      <th>top3avg_balance_imp</th>\n",
       "      <th>top3avg_jumping_imp</th>\n",
       "      <th>top3avg_vision_imp</th>\n",
       "      <th>top3avg_sliding_tackle_imp</th>\n",
       "      <th>season</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2008-08-09</td>\n",
       "      <td>4769</td>\n",
       "      <td>483130</td>\n",
       "      <td>7819</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>69.454545</td>\n",
       "      <td>74.727273</td>\n",
       "      <td>56.272727</td>\n",
       "      <td>53.454545</td>\n",
       "      <td>...</td>\n",
       "      <td>2.400000</td>\n",
       "      <td>2.800000</td>\n",
       "      <td>67.900000</td>\n",
       "      <td>70.666667</td>\n",
       "      <td>76.111111</td>\n",
       "      <td>75.400000</td>\n",
       "      <td>76.811111</td>\n",
       "      <td>76.333333</td>\n",
       "      <td>69.666667</td>\n",
       "      <td>2008/2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008-08-09</td>\n",
       "      <td>4769</td>\n",
       "      <td>483130</td>\n",
       "      <td>9827</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>74.454545</td>\n",
       "      <td>83.090909</td>\n",
       "      <td>53.090909</td>\n",
       "      <td>47.636364</td>\n",
       "      <td>...</td>\n",
       "      <td>2.666667</td>\n",
       "      <td>2.900000</td>\n",
       "      <td>76.333333</td>\n",
       "      <td>80.333333</td>\n",
       "      <td>83.666667</td>\n",
       "      <td>81.333333</td>\n",
       "      <td>88.000000</td>\n",
       "      <td>85.333333</td>\n",
       "      <td>83.000000</td>\n",
       "      <td>2008/2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2008-08-09</td>\n",
       "      <td>4769</td>\n",
       "      <td>483131</td>\n",
       "      <td>9831</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>68.818182</td>\n",
       "      <td>75.909091</td>\n",
       "      <td>50.818182</td>\n",
       "      <td>50.181818</td>\n",
       "      <td>...</td>\n",
       "      <td>2.388889</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>71.333333</td>\n",
       "      <td>68.666667</td>\n",
       "      <td>74.666667</td>\n",
       "      <td>78.000000</td>\n",
       "      <td>75.888889</td>\n",
       "      <td>77.000000</td>\n",
       "      <td>72.333333</td>\n",
       "      <td>2008/2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2008-08-09</td>\n",
       "      <td>4769</td>\n",
       "      <td>483131</td>\n",
       "      <td>9746</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>64.909091</td>\n",
       "      <td>72.454545</td>\n",
       "      <td>44.090909</td>\n",
       "      <td>43.636364</td>\n",
       "      <td>...</td>\n",
       "      <td>2.377778</td>\n",
       "      <td>2.411111</td>\n",
       "      <td>68.000000</td>\n",
       "      <td>61.333333</td>\n",
       "      <td>71.333333</td>\n",
       "      <td>71.666667</td>\n",
       "      <td>80.333333</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>66.666667</td>\n",
       "      <td>2008/2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2008-08-10</td>\n",
       "      <td>4769</td>\n",
       "      <td>483133</td>\n",
       "      <td>9941</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>70.363636</td>\n",
       "      <td>78.000000</td>\n",
       "      <td>55.727273</td>\n",
       "      <td>45.454545</td>\n",
       "      <td>...</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>68.666667</td>\n",
       "      <td>77.666667</td>\n",
       "      <td>77.666667</td>\n",
       "      <td>82.000000</td>\n",
       "      <td>77.666667</td>\n",
       "      <td>79.333333</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>2008/2009</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 76 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          date  league_id  match_id  team_id  result  home  \\\n",
       "0   2008-08-09       4769    483130     7819      -1     0   \n",
       "1   2008-08-09       4769    483130     9827       1     1   \n",
       "2   2008-08-09       4769    483131     9831      -1     0   \n",
       "3   2008-08-09       4769    483131     9746       1     1   \n",
       "16  2008-08-10       4769    483133     9941      -1     0   \n",
       "\n",
       "    avg_overall_rating  avg_potential  avg_crossing  avg_finishing  ...  \\\n",
       "0            69.454545      74.727273     56.272727      53.454545  ...   \n",
       "1            74.454545      83.090909     53.090909      47.636364  ...   \n",
       "2            68.818182      75.909091     50.818182      50.181818  ...   \n",
       "3            64.909091      72.454545     44.090909      43.636364  ...   \n",
       "16           70.363636      78.000000     55.727273      45.454545  ...   \n",
       "\n",
       "    top3avg_attacking_work_rate_imp  top3avg_defensive_work_rate_imp  \\\n",
       "0                          2.400000                         2.800000   \n",
       "1                          2.666667                         2.900000   \n",
       "2                          2.388889                         2.000000   \n",
       "3                          2.377778                         2.411111   \n",
       "16                         2.000000                         3.000000   \n",
       "\n",
       "    top3avg_volleys_imp  top3avg_curve_imp  top3avg_agility_imp  \\\n",
       "0             67.900000          70.666667            76.111111   \n",
       "1             76.333333          80.333333            83.666667   \n",
       "2             71.333333          68.666667            74.666667   \n",
       "3             68.000000          61.333333            71.333333   \n",
       "16            68.666667          77.666667            77.666667   \n",
       "\n",
       "    top3avg_balance_imp  top3avg_jumping_imp  top3avg_vision_imp  \\\n",
       "0             75.400000            76.811111           76.333333   \n",
       "1             81.333333            88.000000           85.333333   \n",
       "2             78.000000            75.888889           77.000000   \n",
       "3             71.666667            80.333333           71.000000   \n",
       "16            82.000000            77.666667           79.333333   \n",
       "\n",
       "    top3avg_sliding_tackle_imp     season  \n",
       "0                    69.666667  2008/2009  \n",
       "1                    83.000000  2008/2009  \n",
       "2                    72.333333  2008/2009  \n",
       "3                    66.666667  2008/2009  \n",
       "16                   75.000000  2008/2009  \n",
       "\n",
       "[5 rows x 76 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in feat_df.columns[6:-1]:\n",
    "    feat_df[col] = feat_df[col].diff()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df = feat_df.loc[feat_df[\"home\"]==1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df = feat_df.drop([\"home\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">avg_overall_rating</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>league_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.035820</td>\n",
       "      <td>3.939701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1729</th>\n",
       "      <td>0.079553</td>\n",
       "      <td>5.339994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4769</th>\n",
       "      <td>0.154711</td>\n",
       "      <td>5.202056</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7809</th>\n",
       "      <td>0.081841</td>\n",
       "      <td>4.876844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10257</th>\n",
       "      <td>0.096370</td>\n",
       "      <td>5.228207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13274</th>\n",
       "      <td>-0.033281</td>\n",
       "      <td>4.783394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15722</th>\n",
       "      <td>-0.184144</td>\n",
       "      <td>3.421616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17642</th>\n",
       "      <td>-0.052912</td>\n",
       "      <td>6.151427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19694</th>\n",
       "      <td>0.071913</td>\n",
       "      <td>5.068134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21518</th>\n",
       "      <td>0.044497</td>\n",
       "      <td>5.630449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24558</th>\n",
       "      <td>0.010573</td>\n",
       "      <td>4.853690</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          avg_overall_rating          \n",
       "                        mean       std\n",
       "league_id                             \n",
       "1                   0.035820  3.939701\n",
       "1729                0.079553  5.339994\n",
       "4769                0.154711  5.202056\n",
       "7809                0.081841  4.876844\n",
       "10257               0.096370  5.228207\n",
       "13274              -0.033281  4.783394\n",
       "15722              -0.184144  3.421616\n",
       "17642              -0.052912  6.151427\n",
       "19694               0.071913  5.068134\n",
       "21518               0.044497  5.630449\n",
       "24558               0.010573  4.853690"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(feat_df.groupby([\"league_id\"]).agg({\"avg_overall_rating\" : [\"mean\", \"std\"]}))\n",
    "# .groupby(\"league_id\")[\"avg_overall_rating\"].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for l in feat_df[\"league_id\"].unique():\n",
    "    for s in feat_df[\"season\"].unique():\n",
    "        for col in feat_df.columns[5:-1]:\n",
    "            \n",
    "            mu = feat_df.loc[(feat_df[\"season\"]==s) & (feat_df[\"league_id\"]==l), col].mean()\n",
    "            sd = feat_df.loc[(feat_df[\"season\"]==s) & (feat_df[\"league_id\"]==l), col].std()\n",
    "            \n",
    "            feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                        (feat_df[\"league_id\"]==l), col] = feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                                                                      (feat_df[\"league_id\"]==l), col] - mu\n",
    "            \n",
    "            feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                        (feat_df[\"league_id\"]==l), col] = feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                                                                      (feat_df[\"league_id\"]==l), col] / sd\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df.groupby(\"season\").size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df.groupby(\"season\")[\"match_id\"].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seasons = feat_df[\"season\"].unique()\n",
    "\n",
    "ctree_df = feat_df.loc[feat_df[\"season\"].isin(seasons[0:4])]\n",
    "ctree_df[\"fold\"] = 1\n",
    "ctree_df[\"test_set\"] = 1\n",
    "ctree_df.loc[ctree_df[\"season\"].isin(seasons[0:3]), \"test_set\"] = 0\n",
    "\n",
    "for i in range(1, len(seasons)-4):\n",
    "    c_df = feat_df.loc[feat_df[\"season\"].isin(seasons[i:i+4])]\n",
    "    c_df[\"fold\"] = i+1\n",
    "    c_df[\"test_set\"] = 1\n",
    "    c_df.loc[c_df[\"season\"].isin(seasons[i:i+3]), \"test_set\"] = 0\n",
    "    \n",
    "    ctree_df = pd.concat([ctree_df,c_df])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctree_df.groupby([\"fold\", \"test_set\"]).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctree_df = ctree_df[ctree_df.columns[4:]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctree_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctree_df.to_csv(dir+\"ctree_player_diff_df.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Team attributes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'league_id', 'match_id', 'team_id', 'result', 'home',\n",
       "       'avg_overall_rating', 'avg_potential', 'avg_crossing', 'avg_finishing',\n",
       "       'avg_heading_accuracy', 'avg_short_passing', 'avg_dribbling',\n",
       "       'avg_free_kick_accuracy', 'avg_long_passing', 'avg_ball_control',\n",
       "       'avg_acceleration', 'avg_sprint_speed', 'avg_reactions',\n",
       "       'avg_shot_power', 'avg_stamina', 'avg_strength', 'avg_long_shots',\n",
       "       'avg_aggression', 'avg_interceptions', 'avg_positioning',\n",
       "       'avg_penalties', 'avg_marking', 'avg_standing_tackle', 'max_gk_diving',\n",
       "       'max_gk_handling', 'max_gk_kicking', 'max_gk_positioning',\n",
       "       'max_gk_reflexes', 'avg_attacking_work_rate_imp',\n",
       "       'avg_defensive_work_rate_imp', 'avg_volleys_imp', 'avg_curve_imp',\n",
       "       'avg_agility_imp', 'avg_balance_imp', 'avg_jumping_imp',\n",
       "       'avg_vision_imp', 'avg_sliding_tackle_imp', 'top3avg_overall_rating',\n",
       "       'top3avg_potential', 'top3avg_crossing', 'top3avg_finishing',\n",
       "       'top3avg_heading_accuracy', 'top3avg_short_passing',\n",
       "       'top3avg_dribbling', 'top3avg_free_kick_accuracy',\n",
       "       'top3avg_long_passing', 'top3avg_ball_control', 'top3avg_acceleration',\n",
       "       'top3avg_sprint_speed', 'top3avg_reactions', 'top3avg_shot_power',\n",
       "       'top3avg_stamina', 'top3avg_strength', 'top3avg_long_shots',\n",
       "       'top3avg_aggression', 'top3avg_interceptions', 'top3avg_positioning',\n",
       "       'top3avg_penalties', 'top3avg_marking', 'top3avg_standing_tackle',\n",
       "       'top3avg_attacking_work_rate_imp', 'top3avg_defensive_work_rate_imp',\n",
       "       'top3avg_volleys_imp', 'top3avg_curve_imp', 'top3avg_agility_imp',\n",
       "       'top3avg_balance_imp', 'top3avg_jumping_imp', 'top3avg_vision_imp',\n",
       "       'top3avg_sliding_tackle_imp', 'season'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df = pd.read_csv(dir+\"feat_df.csv\")\n",
    "\n",
    "feat_df = feat_df.drop(\"Unnamed: 0\", axis=1)\n",
    "\n",
    "feat_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['team_id', 'date', 'defenceDefenderLineClass',\n",
       "       'buildUpPlayPositioningClass', 'chanceCreationPositioningClass',\n",
       "       'buildUpPlaySpeed', 'buildUpPlayPassing', 'chanceCreationPassing',\n",
       "       'chanceCreationCrossing', 'chanceCreationShooting', 'defencePressure',\n",
       "       'defenceAggression', 'defenceTeamWidth', 'buildUpPlayDribbling_imp',\n",
       "       'missing_buildUpPlayDribbling'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team_att_df = pd.read_csv(dir+\"team_att_df_imp.csv\")\n",
    "\n",
    "team_att_df = team_att_df.drop(\"Unnamed: 0\", axis=1)\n",
    "\n",
    "team_att_df.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(42724, 76)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(35057, 91)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team_att_df[\"date_team_att\"] = pd.to_datetime(team_att_df[\"date\"])\n",
    "feat_df[\"date\"] = pd.to_datetime(feat_df[\"date\"])\n",
    "\n",
    "print(feat_df.shape)\n",
    "\n",
    "feat_df = feat_df.merge(team_att_df.drop(\"date\",axis=1), on=\"team_id\", how=\"left\")\n",
    "\n",
    "feat_df[\"day_diff\"] = (feat_df[\"date\"] - feat_df[\"date_team_att\"]).dt.days\n",
    "\n",
    "feat_df = feat_df.loc[feat_df[\"day_diff\"] >= 0]\n",
    "\n",
    "feat_df = feat_df.sort_values([\"team_id\", \"date\", \"day_diff\"])\n",
    "\n",
    "feat_df = feat_df.sort_values(\"day_diff\").groupby([\"team_id\", \"date\"]).head(1)\n",
    "feat_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "buildUpPlaySpeed\n",
      "8.914053051775703\n",
      "--------------------------------------------------\n",
      "buildUpPlayPassing\n",
      "8.242744671840322\n",
      "--------------------------------------------------\n",
      "chanceCreationPassing\n",
      "7.876966433328479\n",
      "--------------------------------------------------\n",
      "chanceCreationCrossing\n",
      "8.136517765410979\n",
      "--------------------------------------------------\n",
      "chanceCreationShooting\n",
      "8.076980654681046\n",
      "--------------------------------------------------\n",
      "defencePressure\n",
      "7.66169519162797\n",
      "--------------------------------------------------\n",
      "defenceAggression\n",
      "7.995128660669684\n",
      "--------------------------------------------------\n",
      "defenceTeamWidth\n",
      "7.2979359159771295\n",
      "--------------------------------------------------\n",
      "buildUpPlayDribbling_imp\n",
      "8.958549761478745\n",
      "--------------------------------------------------\n",
      "missing_buildUpPlayDribbling\n",
      "0.46832218922872937\n",
      "--------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "for col in team_att_df.columns[2:-1]:\n",
    "    if \"Class\" not in col:\n",
    "        print(col)\n",
    "        print(team_att_df.groupby(\"team_id\")[col].std().mean())\n",
    "        print(\"--------------------------------------------------\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'league_id', 'match_id', 'team_id', 'result', 'home',\n",
       "       'avg_overall_rating', 'avg_potential', 'avg_crossing', 'avg_finishing',\n",
       "       'avg_heading_accuracy', 'avg_short_passing', 'avg_dribbling',\n",
       "       'avg_free_kick_accuracy', 'avg_long_passing', 'avg_ball_control',\n",
       "       'avg_acceleration', 'avg_sprint_speed', 'avg_reactions',\n",
       "       'avg_shot_power', 'avg_stamina', 'avg_strength', 'avg_long_shots',\n",
       "       'avg_aggression', 'avg_interceptions', 'avg_positioning',\n",
       "       'avg_penalties', 'avg_marking', 'avg_standing_tackle', 'max_gk_diving',\n",
       "       'max_gk_handling', 'max_gk_kicking', 'max_gk_positioning',\n",
       "       'max_gk_reflexes', 'avg_attacking_work_rate_imp',\n",
       "       'avg_defensive_work_rate_imp', 'avg_volleys_imp', 'avg_curve_imp',\n",
       "       'avg_agility_imp', 'avg_balance_imp', 'avg_jumping_imp',\n",
       "       'avg_vision_imp', 'avg_sliding_tackle_imp', 'top3avg_overall_rating',\n",
       "       'top3avg_potential', 'top3avg_crossing', 'top3avg_finishing',\n",
       "       'top3avg_heading_accuracy', 'top3avg_short_passing',\n",
       "       'top3avg_dribbling', 'top3avg_free_kick_accuracy',\n",
       "       'top3avg_long_passing', 'top3avg_ball_control', 'top3avg_acceleration',\n",
       "       'top3avg_sprint_speed', 'top3avg_reactions', 'top3avg_shot_power',\n",
       "       'top3avg_stamina', 'top3avg_strength', 'top3avg_long_shots',\n",
       "       'top3avg_aggression', 'top3avg_interceptions', 'top3avg_positioning',\n",
       "       'top3avg_penalties', 'top3avg_marking', 'top3avg_standing_tackle',\n",
       "       'top3avg_attacking_work_rate_imp', 'top3avg_defensive_work_rate_imp',\n",
       "       'top3avg_volleys_imp', 'top3avg_curve_imp', 'top3avg_agility_imp',\n",
       "       'top3avg_balance_imp', 'top3avg_jumping_imp', 'top3avg_vision_imp',\n",
       "       'top3avg_sliding_tackle_imp', 'season', 'defenceDefenderLineClass',\n",
       "       'buildUpPlayPositioningClass', 'chanceCreationPositioningClass',\n",
       "       'buildUpPlaySpeed', 'buildUpPlayPassing', 'chanceCreationPassing',\n",
       "       'chanceCreationCrossing', 'chanceCreationShooting', 'defencePressure',\n",
       "       'defenceAggression', 'defenceTeamWidth', 'buildUpPlayDribbling_imp',\n",
       "       'missing_buildUpPlayDribbling', 'date_team_att', 'day_diff'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp=feat_df.groupby(\"match_id\")[\"team_id\"].nunique().reset_index()\n",
    "\n",
    "feat_df = feat_df.loc[feat_df[\"match_id\"].isin(set(temp[\"match_id\"]))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df = feat_df.drop([\"day_diff\", \"date_team_att\", \"missing_buildUpPlayDribbling\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df = feat_df.drop([c for c in feat_df.columns if \"top3avg_gk\" in c], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'league_id', 'match_id', 'team_id', 'result', 'home',\n",
       "       'avg_overall_rating', 'avg_potential', 'avg_crossing', 'avg_finishing',\n",
       "       'avg_heading_accuracy', 'avg_short_passing', 'avg_dribbling',\n",
       "       'avg_free_kick_accuracy', 'avg_long_passing', 'avg_ball_control',\n",
       "       'avg_acceleration', 'avg_sprint_speed', 'avg_reactions',\n",
       "       'avg_shot_power', 'avg_stamina', 'avg_strength', 'avg_long_shots',\n",
       "       'avg_aggression', 'avg_interceptions', 'avg_positioning',\n",
       "       'avg_penalties', 'avg_marking', 'avg_standing_tackle', 'max_gk_diving',\n",
       "       'max_gk_handling', 'max_gk_kicking', 'max_gk_positioning',\n",
       "       'max_gk_reflexes', 'avg_attacking_work_rate_imp',\n",
       "       'avg_defensive_work_rate_imp', 'avg_volleys_imp', 'avg_curve_imp',\n",
       "       'avg_agility_imp', 'avg_balance_imp', 'avg_jumping_imp',\n",
       "       'avg_vision_imp', 'avg_sliding_tackle_imp', 'top3avg_overall_rating',\n",
       "       'top3avg_potential', 'top3avg_crossing', 'top3avg_finishing',\n",
       "       'top3avg_heading_accuracy', 'top3avg_short_passing',\n",
       "       'top3avg_dribbling'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.columns[:50]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['top3avg_free_kick_accuracy', 'top3avg_long_passing',\n",
       "       'top3avg_ball_control', 'top3avg_acceleration', 'top3avg_sprint_speed',\n",
       "       'top3avg_reactions', 'top3avg_shot_power', 'top3avg_stamina',\n",
       "       'top3avg_strength', 'top3avg_long_shots', 'top3avg_aggression',\n",
       "       'top3avg_interceptions', 'top3avg_positioning', 'top3avg_penalties',\n",
       "       'top3avg_marking', 'top3avg_standing_tackle',\n",
       "       'top3avg_attacking_work_rate_imp', 'top3avg_defensive_work_rate_imp',\n",
       "       'top3avg_volleys_imp', 'top3avg_curve_imp', 'top3avg_agility_imp',\n",
       "       'top3avg_balance_imp', 'top3avg_jumping_imp', 'top3avg_vision_imp',\n",
       "       'top3avg_sliding_tackle_imp', 'season', 'defenceDefenderLineClass',\n",
       "       'buildUpPlayPositioningClass', 'chanceCreationPositioningClass',\n",
       "       'buildUpPlaySpeed', 'buildUpPlayPassing', 'chanceCreationPassing',\n",
       "       'chanceCreationCrossing', 'chanceCreationShooting', 'defencePressure',\n",
       "       'defenceAggression', 'defenceTeamWidth', 'buildUpPlayDribbling_imp'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.columns[50:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#for col in [c for c in feat_df.columns if \"Class\" in c]:\n",
    "    \n",
    "#    team_season = feat_df.groupby([\"team_id\", \"season\", col]).agg({\"result\": [\"size\", \"sum\"]}).reset_index()\n",
    "#    team_season.columns = [\"team_id\", \"season\", col, \"count01\", \"sum01\"]\n",
    "\n",
    "#    league_season = feat_df.groupby([\"league_id\", \"season\", col])[\"result\"].mean().reset_index()\n",
    "#    league_season.columns = [\"league_id\", \"season\", col, \"avg\"]\n",
    "\n",
    "#    team_season_og = feat_df[[\"team_id\", \"league_id\", \"season\", col]].drop_duplicates()\n",
    "\n",
    "#    team_season_og = team_season_og.merge(team_season, on=[\"team_id\", \"season\", col], how=\"left\")\n",
    "\n",
    "#    team_season_og = team_season_og.merge(league_season, on=[\"league_id\", \"season\", col], how=\"left\")\n",
    "\n",
    "#    team_season_og[\"{}_smoothed_te\".format(col)] = ((team_season_og[\"sum01\"] + (4*team_season_og[\"avg\"]) )/\n",
    "#                                                    (team_season_og[\"count01\"] + 4))\n",
    "    \n",
    "#    feat_df = feat_df.merge(team_season_og[[\"team_id\", \"season\", \"{}_smoothed_te\".format(col), col]],\n",
    "#                        on=[\"team_id\", \"season\", col])\n",
    "    \n",
    "#    feat_df = feat_df.drop(col, axis=1)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(35057, 88)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df = feat_df.sort_values([\"match_id\", \"home\"], ascending=[True, True])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['avg_overall_rating',\n",
       " 'avg_potential',\n",
       " 'avg_crossing',\n",
       " 'avg_finishing',\n",
       " 'avg_heading_accuracy',\n",
       " 'avg_short_passing',\n",
       " 'avg_dribbling',\n",
       " 'avg_free_kick_accuracy',\n",
       " 'avg_long_passing',\n",
       " 'avg_ball_control',\n",
       " 'avg_acceleration',\n",
       " 'avg_sprint_speed',\n",
       " 'avg_reactions',\n",
       " 'avg_shot_power',\n",
       " 'avg_stamina',\n",
       " 'avg_strength',\n",
       " 'avg_long_shots',\n",
       " 'avg_aggression',\n",
       " 'avg_interceptions',\n",
       " 'avg_positioning',\n",
       " 'avg_penalties',\n",
       " 'avg_marking',\n",
       " 'avg_standing_tackle',\n",
       " 'max_gk_diving',\n",
       " 'max_gk_handling',\n",
       " 'max_gk_kicking',\n",
       " 'max_gk_positioning',\n",
       " 'max_gk_reflexes',\n",
       " 'avg_attacking_work_rate_imp',\n",
       " 'avg_defensive_work_rate_imp',\n",
       " 'avg_volleys_imp',\n",
       " 'avg_curve_imp',\n",
       " 'avg_agility_imp',\n",
       " 'avg_balance_imp',\n",
       " 'avg_jumping_imp',\n",
       " 'avg_vision_imp',\n",
       " 'avg_sliding_tackle_imp',\n",
       " 'top3avg_overall_rating',\n",
       " 'top3avg_potential',\n",
       " 'top3avg_crossing',\n",
       " 'top3avg_finishing',\n",
       " 'top3avg_heading_accuracy',\n",
       " 'top3avg_short_passing',\n",
       " 'top3avg_dribbling',\n",
       " 'top3avg_free_kick_accuracy',\n",
       " 'top3avg_long_passing',\n",
       " 'top3avg_ball_control',\n",
       " 'top3avg_acceleration',\n",
       " 'top3avg_sprint_speed',\n",
       " 'top3avg_reactions',\n",
       " 'top3avg_shot_power',\n",
       " 'top3avg_stamina',\n",
       " 'top3avg_strength',\n",
       " 'top3avg_long_shots',\n",
       " 'top3avg_aggression',\n",
       " 'top3avg_interceptions',\n",
       " 'top3avg_positioning',\n",
       " 'top3avg_penalties',\n",
       " 'top3avg_marking',\n",
       " 'top3avg_standing_tackle',\n",
       " 'top3avg_attacking_work_rate_imp',\n",
       " 'top3avg_defensive_work_rate_imp',\n",
       " 'top3avg_volleys_imp',\n",
       " 'top3avg_curve_imp',\n",
       " 'top3avg_agility_imp',\n",
       " 'top3avg_balance_imp',\n",
       " 'top3avg_jumping_imp',\n",
       " 'top3avg_vision_imp',\n",
       " 'top3avg_sliding_tackle_imp',\n",
       " 'buildUpPlaySpeed',\n",
       " 'buildUpPlayPassing',\n",
       " 'chanceCreationPassing',\n",
       " 'chanceCreationCrossing',\n",
       " 'chanceCreationShooting',\n",
       " 'defencePressure',\n",
       " 'defenceAggression',\n",
       " 'defenceTeamWidth',\n",
       " 'buildUpPlayDribbling_imp']"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "numeric_cols = feat_df.select_dtypes([np.number]).columns\n",
    "numeric_cols = [col for col in numeric_cols if col not in [\"date\", \"team_id\", 'result', 'league_id',\n",
    "                                                           'match_id', \"home\"]]\n",
    "numeric_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in [c for c in feat_df.columns if \"Class\" in c]:\n",
    "    \n",
    "    feat_df[col] = np.where(feat_df[col]==feat_df[col].value_counts().head(1).index[0],\n",
    "                            2, 1)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>defenceDefenderLineClass</th>\n",
       "      <th>buildUpPlayPositioningClass</th>\n",
       "      <th>chanceCreationPositioningClass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>43852</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43858</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40408</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40414</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44634</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44640</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39317</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39323</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38945</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38951</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       defenceDefenderLineClass  buildUpPlayPositioningClass  \\\n",
       "43852                         2                            2   \n",
       "43858                         1                            2   \n",
       "40408                         2                            2   \n",
       "40414                         1                            2   \n",
       "44634                         1                            2   \n",
       "44640                         2                            2   \n",
       "39317                         2                            2   \n",
       "39323                         2                            2   \n",
       "38945                         2                            2   \n",
       "38951                         1                            2   \n",
       "\n",
       "       chanceCreationPositioningClass  \n",
       "43852                               2  \n",
       "43858                               1  \n",
       "40408                               2  \n",
       "40414                               2  \n",
       "44634                               2  \n",
       "44640                               2  \n",
       "39317                               2  \n",
       "39323                               2  \n",
       "38945                               2  \n",
       "38951                               2  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df[[c for c in feat_df.columns if \"Class\" in c]].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df[\"buildUpPlayDribbling_imp\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df[\"buildUpPlayDribbling_imp\"] = feat_df[\"buildUpPlayDribbling_imp\"].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in numeric_cols + [c for c in feat_df.columns if \"Class\" in c]:\n",
    "    feat_df[col] = feat_df[col].diff()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in [c for c in feat_df.columns if \"Class\" in c]:\n",
    "    feat_df[col] = np.where(feat_df[col]==0, 0, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df = feat_df.loc[feat_df[\"home\"]==1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df = feat_df.drop([\"home\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "chanceCreationShooting      0\n",
       "defencePressure             0\n",
       "defenceAggression           0\n",
       "defenceTeamWidth            0\n",
       "buildUpPlayDribbling_imp    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.isnull().sum().tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['avg_overall_rating',\n",
       " 'avg_potential',\n",
       " 'avg_crossing',\n",
       " 'avg_finishing',\n",
       " 'avg_heading_accuracy',\n",
       " 'avg_short_passing',\n",
       " 'avg_dribbling',\n",
       " 'avg_free_kick_accuracy',\n",
       " 'avg_long_passing',\n",
       " 'avg_ball_control',\n",
       " 'avg_acceleration',\n",
       " 'avg_sprint_speed',\n",
       " 'avg_reactions',\n",
       " 'avg_shot_power',\n",
       " 'avg_stamina',\n",
       " 'avg_strength',\n",
       " 'avg_long_shots',\n",
       " 'avg_aggression',\n",
       " 'avg_interceptions',\n",
       " 'avg_positioning',\n",
       " 'avg_penalties',\n",
       " 'avg_marking',\n",
       " 'avg_standing_tackle',\n",
       " 'max_gk_diving',\n",
       " 'max_gk_handling',\n",
       " 'max_gk_kicking',\n",
       " 'max_gk_positioning',\n",
       " 'max_gk_reflexes',\n",
       " 'avg_attacking_work_rate_imp',\n",
       " 'avg_defensive_work_rate_imp',\n",
       " 'avg_volleys_imp',\n",
       " 'avg_curve_imp',\n",
       " 'avg_agility_imp',\n",
       " 'avg_balance_imp',\n",
       " 'avg_jumping_imp',\n",
       " 'avg_vision_imp',\n",
       " 'avg_sliding_tackle_imp',\n",
       " 'top3avg_overall_rating',\n",
       " 'top3avg_potential',\n",
       " 'top3avg_crossing',\n",
       " 'top3avg_finishing',\n",
       " 'top3avg_heading_accuracy',\n",
       " 'top3avg_short_passing',\n",
       " 'top3avg_dribbling',\n",
       " 'top3avg_free_kick_accuracy',\n",
       " 'top3avg_long_passing',\n",
       " 'top3avg_ball_control',\n",
       " 'top3avg_acceleration',\n",
       " 'top3avg_sprint_speed',\n",
       " 'top3avg_reactions',\n",
       " 'top3avg_shot_power',\n",
       " 'top3avg_stamina',\n",
       " 'top3avg_strength',\n",
       " 'top3avg_long_shots',\n",
       " 'top3avg_aggression',\n",
       " 'top3avg_interceptions',\n",
       " 'top3avg_positioning',\n",
       " 'top3avg_penalties',\n",
       " 'top3avg_marking',\n",
       " 'top3avg_standing_tackle',\n",
       " 'top3avg_attacking_work_rate_imp',\n",
       " 'top3avg_defensive_work_rate_imp',\n",
       " 'top3avg_volleys_imp',\n",
       " 'top3avg_curve_imp',\n",
       " 'top3avg_agility_imp',\n",
       " 'top3avg_balance_imp',\n",
       " 'top3avg_jumping_imp',\n",
       " 'top3avg_vision_imp',\n",
       " 'top3avg_sliding_tackle_imp',\n",
       " 'buildUpPlaySpeed',\n",
       " 'buildUpPlayPassing',\n",
       " 'chanceCreationPassing',\n",
       " 'chanceCreationCrossing',\n",
       " 'chanceCreationShooting',\n",
       " 'defencePressure',\n",
       " 'defenceAggression',\n",
       " 'defenceTeamWidth',\n",
       " 'buildUpPlayDribbling_imp']"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "numeric_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "for l in feat_df[\"league_id\"].unique():\n",
    "    for s in feat_df[\"season\"].unique():\n",
    "        for col in numeric_cols:\n",
    "            \n",
    "            if col != \"buildUpPlayDribbling_imp\":\n",
    "            \n",
    "                mu = feat_df.loc[(feat_df[\"season\"]==s) & (feat_df[\"league_id\"]==l), col].mean()\n",
    "                sd = feat_df.loc[(feat_df[\"season\"]==s) & (feat_df[\"league_id\"]==l), col].std()\n",
    "\n",
    "                feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                            (feat_df[\"league_id\"]==l), col] = feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                                                                          (feat_df[\"league_id\"]==l), col] - mu\n",
    "\n",
    "                feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                            (feat_df[\"league_id\"]==l), col] = feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                                                                          (feat_df[\"league_id\"]==l), col] / sd\n",
    "            \n",
    "            elif col == \"buildUpPlayDribbling_imp\":\n",
    "                \n",
    "                mu = feat_df.loc[(feat_df[\"season\"]==s) & (feat_df[\"league_id\"]==l), col].mean()\n",
    "                feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                        (feat_df[\"league_id\"]==l), col] = feat_df.loc[(feat_df[\"season\"]==s) &\n",
    "                                                                      (feat_df[\"league_id\"]==l), col] - mu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df[\"buildUpPlayDribbling_imp\"].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "season\n",
       "2009/2010     820\n",
       "2010/2011    2613\n",
       "2011/2012    2743\n",
       "2012/2013    2796\n",
       "2013/2014    2685\n",
       "2014/2015    2899\n",
       "2015/2016    2964\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.groupby(\"season\").size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "ctree_df = feat_df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import random\n",
    "\n",
    "#test_size = int(len(feat_df)*0.2)\n",
    "#for i in range(10):\n",
    "#    test_matches = random.sample(feat_df[\"match_id\"].tolist(), test_size)\n",
    "#    feat_df[\"test_set_{}\".format(i)] = 0\n",
    "#    feat_df.loc[feat_df[\"match_id\"].isin(test_matches), \"test_set_{}\".format(i)] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "season\n",
       "2009/2010     820\n",
       "2010/2011    2613\n",
       "2011/2012    2743\n",
       "2012/2013    2796\n",
       "2013/2014    2685\n",
       "2014/2015    2899\n",
       "2015/2016    2964\n",
       "dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.groupby(\"season\").size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2010/2011 2011/2012\n",
      "2011/2012 2012/2013\n",
      "2012/2013 2013/2014\n",
      "2013/2014 2014/2015\n",
      "2014/2015 2015/2016\n"
     ]
    }
   ],
   "source": [
    "seasons = sorted(feat_df[\"season\"].unique())\n",
    "\n",
    "for i in range(1, len(seasons)-1):\n",
    "    print(seasons[i], seasons[i+1])    \n",
    "    feat_df[\"fold_1t_{}_test\".format(i)] = \"other\"\n",
    "    feat_df.loc[feat_df[\"season\"]==seasons[i+1], \"fold_1t_{}_test\".format(i)] = \"test\"\n",
    "    feat_df.loc[feat_df[\"season\"]==seasons[i], \"fold_1t_{}_test\".format(i)] = \"train\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fold_1t_5_test</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>2010-02-22</td>\n",
       "      <td>2014-05-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test</th>\n",
       "      <td>2015-07-17</td>\n",
       "      <td>2016-05-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>train</th>\n",
       "      <td>2014-07-19</td>\n",
       "      <td>2015-05-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     date           \n",
       "                      min        max\n",
       "fold_1t_5_test                      \n",
       "other          2010-02-22 2014-05-18\n",
       "test           2015-07-17 2016-05-25\n",
       "train          2014-07-19 2015-05-31"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "i=5\n",
    "feat_df.groupby(\"fold_1t_{}_test\".format(i)).agg({\"date\": [\"min\", \"max\"]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2010/2011', '2011/2012'] 2012/2013\n",
      "['2011/2012', '2012/2013'] 2013/2014\n",
      "['2012/2013', '2013/2014'] 2014/2015\n",
      "['2013/2014', '2014/2015'] 2015/2016\n"
     ]
    }
   ],
   "source": [
    "for i in range(1, len(seasons)-2):\n",
    "\n",
    "    print(seasons[i:i+3][:-1], seasons[i:i+3][-1])    \n",
    "    feat_df[\"fold_2t_{}_test\".format(i)] = \"other\"\n",
    "    feat_df.loc[feat_df[\"season\"]==seasons[i:i+3][-1], \"fold_2t_{}_test\".format(i)] = \"test\"\n",
    "    feat_df.loc[feat_df[\"season\"].isin(seasons[i:i+3][:-1]), \"fold_2t_{}_test\".format(i)] = \"train\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fold_2t_4_test</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>2010-02-22</td>\n",
       "      <td>2013-06-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test</th>\n",
       "      <td>2015-07-17</td>\n",
       "      <td>2016-05-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>train</th>\n",
       "      <td>2013-07-13</td>\n",
       "      <td>2015-05-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     date           \n",
       "                      min        max\n",
       "fold_2t_4_test                      \n",
       "other          2010-02-22 2013-06-01\n",
       "test           2015-07-17 2016-05-25\n",
       "train          2013-07-13 2015-05-31"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.groupby(\"fold_2t_{}_test\".format(i)).agg({\"date\": [\"min\", \"max\"]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['2010/2011', '2011/2012', '2012/2013'] 2013/2014\n",
      "['2011/2012', '2012/2013', '2013/2014'] 2014/2015\n",
      "['2012/2013', '2013/2014', '2014/2015'] 2015/2016\n"
     ]
    }
   ],
   "source": [
    "for i in range(1, len(seasons)-3):\n",
    "    print(seasons[i:i+4][:-1], seasons[i:i+4][-1])    \n",
    "    feat_df[\"fold_3t_{}_test\".format(i)] = \"other\"\n",
    "    feat_df.loc[feat_df[\"season\"]==seasons[i:i+4][-1], \"fold_3t_{}_test\".format(i)] = \"test\"\n",
    "    feat_df.loc[feat_df[\"season\"].isin(seasons[i:i+4][:-1]), \"fold_3t_{}_test\".format(i)] = \"train\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fold_3t_3_test</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>2010-02-22</td>\n",
       "      <td>2012-05-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test</th>\n",
       "      <td>2015-07-17</td>\n",
       "      <td>2016-05-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>train</th>\n",
       "      <td>2012-07-13</td>\n",
       "      <td>2015-05-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     date           \n",
       "                      min        max\n",
       "fold_3t_3_test                      \n",
       "other          2010-02-22 2012-05-23\n",
       "test           2015-07-17 2016-05-25\n",
       "train          2012-07-13 2015-05-31"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.groupby(\"fold_3t_{}_test\".format(i)).agg({\"date\": [\"min\", \"max\"]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2010/2011 2011/2012\n",
      "2010/2011 2011/2012\n",
      "2011/2012 2012/2013\n",
      "2011/2012 2012/2013\n",
      "2012/2013 2013/2014\n",
      "2012/2013 2013/2014\n",
      "2013/2014 2014/2015\n",
      "2013/2014 2014/2015\n",
      "2014/2015 2015/2016\n",
      "2014/2015 2015/2016\n"
     ]
    }
   ],
   "source": [
    "seasons = sorted(feat_df[\"season\"].unique())\n",
    "\n",
    "for i in range(1, len(seasons)-1):\n",
    "    \n",
    "    print(seasons[i], seasons[i+1])    \n",
    "    \n",
    "    current_set_dates = sorted(feat_df.loc[feat_df[\"season\"]==seasons[i+1], \"date\"].unique())\n",
    "    \n",
    "    print(seasons[i], seasons[i+1])    \n",
    "    feat_df[\"fold_1t_c_{}_test\".format(i)] = \"other\"\n",
    "    feat_df.loc[feat_df[\"season\"]==seasons[i+1], \"fold_1t_c_{}_test\".format(i)] = \"test\"\n",
    "    feat_df.loc[feat_df[\"season\"]==seasons[i], \"fold_1t_c_{}_test\".format(i)] = \"train\"\n",
    "    \n",
    "    feat_df.loc[feat_df[\"date\"].isin(current_set_dates[:int(len(current_set_dates)//2)]),\n",
    "                \"fold_1t_c_{}_test\".format(i)] = \"train\"\n",
    "    \n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fold_1t_c_5_test</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>other</th>\n",
       "      <td>2010-02-22</td>\n",
       "      <td>2014-05-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>test</th>\n",
       "      <td>2016-01-08</td>\n",
       "      <td>2016-05-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>train</th>\n",
       "      <td>2014-07-19</td>\n",
       "      <td>2016-01-06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       date           \n",
       "                        min        max\n",
       "fold_1t_c_5_test                      \n",
       "other            2010-02-22 2014-05-18\n",
       "test             2016-01-08 2016-05-25\n",
       "train            2014-07-19 2016-01-06"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.groupby(\"fold_1t_c_{}_test\".format(i)).agg({\"date\": [\"min\", \"max\"]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['date', 'league_id', 'match_id', 'team_id', 'result',\n",
       "       'avg_overall_rating', 'avg_potential', 'avg_crossing', 'avg_finishing',\n",
       "       'avg_heading_accuracy',\n",
       "       ...\n",
       "       'fold_2t_3_test', 'fold_2t_4_test', 'fold_3t_1_test', 'fold_3t_2_test',\n",
       "       'fold_3t_3_test', 'fold_1t_c_1_test', 'fold_1t_c_2_test',\n",
       "       'fold_1t_c_3_test', 'fold_1t_c_4_test', 'fold_1t_c_5_test'],\n",
       "      dtype='object', length=104)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "#seasons = feat_df[\"season\"].unique()\n",
    "\n",
    "#ctree_df = feat_df.loc[feat_df[\"season\"].isin(seasons[0:4])]\n",
    "#ctree_df[\"fold\"] = 1\n",
    "#ctree_df[\"test_set\"] = 1\n",
    "#ctree_df.loc[ctree_df[\"season\"].isin(seasons[0:3]), \"test_set\"] = 0\n",
    "\n",
    "#for i in range(1, len(seasons)-4):\n",
    "#    c_df = feat_df.loc[feat_df[\"season\"].isin(seasons[i:i+4])]\n",
    "#    c_df[\"fold\"] = i+1\n",
    "#    c_df[\"test_set\"] = 1\n",
    "#    c_df.loc[c_df[\"season\"].isin(seasons[i:i+3]), \"test_set\"] = 0\n",
    "    \n",
    "#    ctree_df = pd.concat([ctree_df,c_df])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df = feat_df[feat_df.columns[4:].tolist() + [\"league_id\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "result                0\n",
       "avg_overall_rating    0\n",
       "avg_potential         0\n",
       "avg_crossing          0\n",
       "avg_finishing         0\n",
       "                     ..\n",
       "fold_1t_c_2_test      0\n",
       "fold_1t_c_3_test      0\n",
       "fold_1t_c_4_test      0\n",
       "fold_1t_c_5_test      0\n",
       "league_id             0\n",
       "Length: 101, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "feat_df.to_csv(dir+\"ctree_teamplayer_diff_df.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(17520, 101)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feat_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}