clean_player_att.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "from sklearn.impute import KNNImputer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "dir = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "team_att_df = pd.read_csv(dir + \"team_attributes.csv\")\n",
    "\n",
    "player_df = pd.read_csv(dir + \"player.csv\")\n",
    "\n",
    "match_df = pd.read_csv(dir + \"match_clean.csv\")\n",
    "\n",
    "league_df = pd.read_csv(dir + \"league.csv\")\n",
    "\n",
    "country_df = pd.read_csv(dir + \"country.csv\")\n",
    "\n",
    "player_att_df = pd.read_csv(dir + \"player_attributes.csv\")\n",
    "\n",
    "team_df = pd.read_csv(dir + \"team.csv\", encoding = \"ISO-8859-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(183978, 40)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'country_id', 'league_id', 'season', 'stage', 'date',\n",
       "       'match_id', 'home_team_id', 'away_team_id', 'home_team_goal',\n",
       "       'away_team_goal', 'home_player_1', 'home_player_2', 'home_player_3',\n",
       "       'home_player_4', 'home_player_5', 'home_player_6', 'home_player_7',\n",
       "       'home_player_8', 'home_player_9', 'home_player_10', 'home_player_11',\n",
       "       'away_player_1', 'away_player_2', 'away_player_3', 'away_player_4',\n",
       "       'away_player_5', 'away_player_6', 'away_player_7', 'away_player_8',\n",
       "       'away_player_9', 'away_player_10', 'away_player_11', 'B365H', 'B365D',\n",
       "       'B365A', 'BWH', 'BWD', 'BWA', 'IWH', 'IWD', 'IWA', 'LBH', 'LBD', 'LBA',\n",
       "       'PSH', 'PSD', 'PSA', 'WHH', 'WHD', 'WHA', 'SJH', 'SJD', 'SJA', 'VCH',\n",
       "       'VCD', 'VCA', 'GBH', 'GBD', 'GBA', 'BSH', 'BSD', 'BSA'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Match team/player"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    299\n",
       "Name: league_id, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_df.groupby(\"away_team_id\")[\"league_id\"].nunique().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    299\n",
       "Name: league_id, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "match_df.groupby(\"home_team_id\")[\"league_id\"].nunique().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = [h for h in match_df.columns if \"home_player\" in h][0]\n",
    "\n",
    "home_df = match_df[[\"season\", \"league_id\", \"match_id\", \"home_team_id\", col]]\n",
    "\n",
    "home_df.columns = [\"season\", \"league_id\", \"match_id\", \"team_id\", \"player_id\"]\n",
    "\n",
    "for col in [h for h in match_df.columns if \"home_player\" in h][1:]:\n",
    "    \n",
    "    home = match_df[[\"season\", \"league_id\", \"match_id\", \"home_team_id\", col]]\n",
    "    \n",
    "    home.columns = [\"season\", \"league_id\", \"match_id\", \"team_id\", \"player_id\"]\n",
    "    \n",
    "    home_df = pd.concat([home_df, home])\n",
    "\n",
    "home_df = home_df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = [h for h in match_df.columns if \"away_player\" in h][0]\n",
    "\n",
    "away_df = match_df[[\"season\", \"league_id\", \"match_id\", \"away_team_id\", col]]\n",
    "\n",
    "away_df.columns = [\"season\", \"league_id\", \"match_id\", \"team_id\", \"player_id\"]\n",
    "\n",
    "for col in [h for h in match_df.columns if \"away_player\" in h][1:]:\n",
    "    \n",
    "    away = match_df[[\"season\", \"league_id\", \"match_id\", \"away_team_id\", col]]\n",
    "    \n",
    "    away.columns = [\"season\", \"league_id\", \"match_id\", \"team_id\", \"player_id\"]\n",
    "\n",
    "    away_df = pd.concat([away_df, away])\n",
    "\n",
    "away_df = away_df.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "season_team_player_df = pd.concat([home_df, away_df]).drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "season          0\n",
       "league_id       0\n",
       "match_id        0\n",
       "team_id         0\n",
       "player_id    7125\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "season_team_player_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>season</th>\n",
       "      <th>league_id</th>\n",
       "      <th>match_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>player_id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>season</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2008/2009</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.045089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009/2010</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.015590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010/2011</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.011764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011/2012</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.009923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012/2013</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.007831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013/2014</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.011215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014/2015</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.004991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015/2016</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.004334</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           season  league_id  match_id  team_id  player_id\n",
       "season                                                    \n",
       "2008/2009     0.0        0.0       0.0      0.0   0.045089\n",
       "2009/2010     0.0        0.0       0.0      0.0   0.015590\n",
       "2010/2011     0.0        0.0       0.0      0.0   0.011764\n",
       "2011/2012     0.0        0.0       0.0      0.0   0.009923\n",
       "2012/2013     0.0        0.0       0.0      0.0   0.007831\n",
       "2013/2014     0.0        0.0       0.0      0.0   0.011215\n",
       "2014/2015     0.0        0.0       0.0      0.0   0.004991\n",
       "2015/2016     0.0        0.0       0.0      0.0   0.004334"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "season_team_player_df.isnull().groupby(season_team_player_df[\"season\"]).mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = (season_team_player_df\n",
    "        .player_id\n",
    "        .isnull()\n",
    "        .groupby([season_team_player_df[\"season\"], season_team_player_df[\"league_id\"]]).mean()\n",
    "        .reset_index())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       season  league_id  player_id\n",
      "0   2008/2009          1   0.211138\n",
      "11  2009/2010          1   0.025937\n",
      "22  2010/2011          1   0.009110\n",
      "33  2011/2012          1   0.004924\n",
      "44  2012/2013          1   0.002841\n",
      "55  2013/2014          1   1.000000\n",
      "66  2014/2015          1   0.004167\n",
      "77  2015/2016          1   0.002841\n",
      "----------------------------------------\n",
      "       season  league_id  player_id\n",
      "1   2008/2009       1729   0.004067\n",
      "12  2009/2010       1729   0.000837\n",
      "23  2010/2011       1729   0.001555\n",
      "34  2011/2012       1729   0.001555\n",
      "45  2012/2013       1729   0.000000\n",
      "56  2013/2014       1729   0.000120\n",
      "67  2014/2015       1729   0.001077\n",
      "78  2015/2016       1729   0.000359\n",
      "----------------------------------------\n",
      "       season  league_id  player_id\n",
      "2   2008/2009       4769   0.007190\n",
      "13  2009/2010       4769   0.002034\n",
      "24  2010/2011       4769   0.002512\n",
      "35  2011/2012       4769   0.000598\n",
      "46  2012/2013       4769   0.003110\n",
      "57  2013/2014       4769   0.000478\n",
      "68  2014/2015       4769   0.001794\n",
      "79  2015/2016       4769   0.003708\n",
      "----------------------------------------\n",
      "       season  league_id  player_id\n",
      "3   2008/2009       7809   0.009678\n",
      "14  2009/2010       7809   0.000743\n",
      "25  2010/2011       7809   0.000594\n",
      "36  2011/2012       7809   0.000000\n",
      "47  2012/2013       7809   0.000000\n",
      "58  2013/2014       7809   0.000000\n",
      "69  2014/2015       7809   0.000000\n",
      "80  2015/2016       7809   0.000000\n",
      "----------------------------------------\n",
      "       season  league_id  player_id\n",
      "4   2008/2009      10257   0.007299\n",
      "15  2009/2010      10257   0.003828\n",
      "26  2010/2011      10257   0.003589\n",
      "37  2011/2012      10257   0.010098\n",
      "48  2012/2013      10257   0.000478\n",
      "59  2013/2014      10257   0.003828\n",
      "70  2014/2015      10257   0.004677\n",
      "81  2015/2016      10257   0.005263\n",
      "----------------------------------------\n",
      "       season  league_id  player_id\n",
      "5   2008/2009      13274   0.183327\n",
      "16  2009/2010      13274   0.006685\n",
      "27  2010/2011      13274   0.005496\n",
      "38  2011/2012      13274   0.003119\n",
      "49  2012/2013      13274   0.001337\n",
      "60  2013/2014      13274   0.000446\n",
      "71  2014/2015      13274   0.001337\n",
      "82  2015/2016      13274   0.005051\n",
      "----------------------------------------\n",
      "       season  league_id  player_id\n",
      "6   2008/2009      15722   0.330351\n",
      "17  2009/2010      15722   0.158625\n",
      "28  2010/2011      15722   0.133615\n",
      "39  2011/2012      15722   0.114165\n",
      "50  2012/2013      15722   0.086957\n",
      "61  2013/2014      15722   0.022910\n",
      "72  2014/2015      15722   0.022024\n",
      "83  2015/2016      15722   0.009291\n",
      "----------------------------------------\n",
      "       season  league_id  player_id\n",
      "7   2008/2009      17642   0.120367\n",
      "18  2009/2010      17642   0.032079\n",
      "29  2010/2011      17642   0.022057\n",
      "40  2011/2012      17642   0.012514\n",
      "51  2012/2013      17642   0.009670\n",
      "62  2013/2014      17642   0.010804\n",
      "73  2014/2015      17642   0.013238\n",
      "84  2015/2016      17642   0.008919\n",
      "----------------------------------------\n",
      "       season  league_id  player_id\n",
      "8   2008/2009      19694   0.015360\n",
      "19  2009/2010      19694   0.014803\n",
      "30  2010/2011      19694   0.004788\n",
      "41  2011/2012      19694   0.002392\n",
      "52  2012/2013      19694   0.004984\n",
      "63  2013/2014      19694   0.005784\n",
      "74  2014/2015      19694   0.005587\n",
      "85  2015/2016      19694   0.006781\n",
      "----------------------------------------\n",
      "       season  league_id  player_id\n",
      "9   2008/2009      21518   0.017512\n",
      "20  2009/2010      21518   0.013177\n",
      "31  2010/2011      21518   0.004547\n",
      "42  2011/2012      21518   0.000239\n",
      "53  2012/2013      21518   0.001316\n",
      "64  2013/2014      21518   0.000957\n",
      "75  2014/2015      21518   0.001914\n",
      "86  2015/2016      21518   0.003469\n",
      "----------------------------------------\n",
      "       season  league_id  player_id\n",
      "10  2008/2009      24558   0.139247\n",
      "21  2009/2010      24558   0.003535\n",
      "32  2010/2011      24558   0.004293\n",
      "43  2011/2012      24558   0.001403\n",
      "54  2012/2013      24558   0.002020\n",
      "65  2013/2014      24558   0.004040\n",
      "76  2014/2015      24558   0.005556\n",
      "87  2015/2016      24558   0.004555\n",
      "----------------------------------------\n"
     ]
    }
   ],
   "source": [
    "for l in temp[\"league_id\"].unique():\n",
    "    print(temp.loc[temp[\"league_id\"]==l])\n",
    "    print(\"----------------------------------------\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(set(season_team_player_df.loc[~season_team_player_df[\"player_id\"].isnull(), \"player_id\"]) - \n",
    " set(player_att_df[\"player_id\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(542017, 5)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "season_team_player_df = season_team_player_df.loc[~season_team_player_df[\"player_id\"].isnull()]\n",
    "season_team_player_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    541991\n",
       "2        13\n",
       "dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "season_team_player_df.groupby([\"match_id\", \"player_id\"]).size().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "match_id  player_id\n",
       "888198    39487.0      1\n",
       "          39447.0      1\n",
       "530209    96623.0      2\n",
       "857362    27488.0      2\n",
       "483176    26523.0      2\n",
       "653921    34003.0      2\n",
       "658982    43230.0      2\n",
       "493077    37900.0      2\n",
       "1287019   41478.0      2\n",
       "499619    27255.0      2\n",
       "1043052   177126.0     2\n",
       "509190    43283.0      2\n",
       "654015    26117.0      2\n",
       "875979    28955.0      2\n",
       "1043052   307227.0     2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "season_team_player_df.groupby([\"match_id\", \"player_id\"]).size().sort_values().tail(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>season</th>\n",
       "      <th>league_id</th>\n",
       "      <th>match_id</th>\n",
       "      <th>team_id</th>\n",
       "      <th>player_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12828</th>\n",
       "      <td>2011/2012</td>\n",
       "      <td>17642</td>\n",
       "      <td>1043052</td>\n",
       "      <td>9771</td>\n",
       "      <td>307227.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12828</th>\n",
       "      <td>2011/2012</td>\n",
       "      <td>17642</td>\n",
       "      <td>1043052</td>\n",
       "      <td>4064</td>\n",
       "      <td>307227.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          season  league_id  match_id  team_id  player_id\n",
       "12828  2011/2012      17642   1043052     9771   307227.0\n",
       "12828  2011/2012      17642   1043052     4064   307227.0"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "season_team_player_df.loc[(season_team_player_df[\"match_id\"]==1043052) & \n",
    "                          (season_team_player_df[\"player_id\"]==307227)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "96623.0    2\n",
       "38471.0    1\n",
       "37423.0    1\n",
       "2790.0     1\n",
       "37440.0    1\n",
       "18628.0    1\n",
       "33586.0    1\n",
       "42310.0    1\n",
       "39142.0    1\n",
       "74747.0    1\n",
       "37597.0    1\n",
       "43374.0    1\n",
       "37448.0    1\n",
       "33820.0    1\n",
       "33729.0    1\n",
       "37434.0    1\n",
       "38035.0    1\n",
       "75192.0    1\n",
       "33958.0    1\n",
       "33022.0    1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd.Series(match_df.loc[match_df[\"match_id\"]==530209,\n",
    "                        [h for h in match_df.columns if \"player\" in h]].values[0])\n",
    " .value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "177126.0    2\n",
       "307227.0    2\n",
       "98374.0     1\n",
       "164105.0    1\n",
       "163665.0    1\n",
       "164062.0    1\n",
       "98209.0     1\n",
       "164098.0    1\n",
       "164099.0    1\n",
       "72782.0     1\n",
       "45359.0     1\n",
       "91473.0     1\n",
       "29036.0     1\n",
       "281338.0    1\n",
       "22125.0     1\n",
       "185918.0    1\n",
       "26861.0     1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(pd.Series(match_df.loc[match_df[\"match_id\"]==1043052,\n",
    "                        [h for h in match_df.columns if \"player\" in h]].values[0])\n",
    " .value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clean player attributes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_att_df[\"date\"] = pd.to_datetime(player_att_df[\"date\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['player_id', 'date', 'overall_rating', 'potential', 'preferred_foot',\n",
       "       'attacking_work_rate', 'defensive_work_rate', 'crossing', 'finishing',\n",
       "       'heading_accuracy', 'short_passing', 'volleys', 'dribbling', 'curve',\n",
       "       'free_kick_accuracy', 'long_passing', 'ball_control', 'acceleration',\n",
       "       'sprint_speed', 'agility', 'reactions', 'balance', 'shot_power',\n",
       "       'jumping', 'stamina', 'strength', 'long_shots', 'aggression',\n",
       "       'interceptions', 'positioning', 'vision', 'penalties', 'marking',\n",
       "       'standing_tackle', 'sliding_tackle', 'gk_diving', 'gk_handling',\n",
       "       'gk_kicking', 'gk_positioning', 'gk_reflexes'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "player_id                 0\n",
       "date                      0\n",
       "overall_rating          836\n",
       "potential               836\n",
       "preferred_foot          836\n",
       "attacking_work_rate    3230\n",
       "defensive_work_rate     836\n",
       "crossing                836\n",
       "finishing               836\n",
       "heading_accuracy        836\n",
       "short_passing           836\n",
       "volleys                2713\n",
       "dribbling               836\n",
       "curve                  2713\n",
       "free_kick_accuracy      836\n",
       "long_passing            836\n",
       "ball_control            836\n",
       "acceleration            836\n",
       "sprint_speed            836\n",
       "agility                2713\n",
       "reactions               836\n",
       "balance                2713\n",
       "shot_power              836\n",
       "jumping                2713\n",
       "stamina                 836\n",
       "strength                836\n",
       "long_shots              836\n",
       "aggression              836\n",
       "interceptions           836\n",
       "positioning             836\n",
       "vision                 2713\n",
       "penalties               836\n",
       "marking                 836\n",
       "standing_tackle         836\n",
       "sliding_tackle         2713\n",
       "gk_diving               836\n",
       "gk_handling             836\n",
       "gk_kicking              836\n",
       "gk_positioning          836\n",
       "gk_reflexes             836\n",
       "dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "overall_rating\n",
      "2.97\n",
      "--------------------------------------------------------\n",
      "potential\n",
      "2.85\n",
      "--------------------------------------------------------\n",
      "crossing\n",
      "4.14\n",
      "--------------------------------------------------------\n",
      "finishing\n",
      "3.99\n",
      "--------------------------------------------------------\n",
      "heading_accuracy\n",
      "3.69\n",
      "--------------------------------------------------------\n",
      "short_passing\n",
      "3.62\n",
      "--------------------------------------------------------\n",
      "volleys\n",
      "2.61\n",
      "--------------------------------------------------------\n",
      "dribbling\n",
      "3.91\n",
      "--------------------------------------------------------\n",
      "curve\n",
      "2.75\n",
      "--------------------------------------------------------\n",
      "free_kick_accuracy\n",
      "4.32\n",
      "--------------------------------------------------------\n",
      "long_passing\n",
      "4.54\n",
      "--------------------------------------------------------\n",
      "ball_control\n",
      "3.42\n",
      "--------------------------------------------------------\n",
      "acceleration\n",
      "4.12\n",
      "--------------------------------------------------------\n",
      "sprint_speed\n",
      "4.2\n",
      "--------------------------------------------------------\n",
      "agility\n",
      "3.03\n",
      "--------------------------------------------------------\n",
      "reactions\n",
      "3.62\n",
      "--------------------------------------------------------\n",
      "balance\n",
      "4.29\n",
      "--------------------------------------------------------\n",
      "shot_power\n",
      "3.94\n",
      "--------------------------------------------------------\n",
      "jumping\n",
      "3.49\n",
      "--------------------------------------------------------\n",
      "stamina\n",
      "5.09\n",
      "--------------------------------------------------------\n",
      "strength\n",
      "4.0\n",
      "--------------------------------------------------------\n",
      "long_shots\n",
      "4.15\n",
      "--------------------------------------------------------\n",
      "aggression\n",
      "4.57\n",
      "--------------------------------------------------------\n",
      "interceptions\n",
      "5.94\n",
      "--------------------------------------------------------\n",
      "positioning\n",
      "5.76\n",
      "--------------------------------------------------------\n",
      "vision\n",
      "3.77\n",
      "--------------------------------------------------------\n",
      "penalties\n",
      "4.75\n",
      "--------------------------------------------------------\n",
      "marking\n",
      "4.34\n",
      "--------------------------------------------------------\n",
      "standing_tackle\n",
      "4.31\n",
      "--------------------------------------------------------\n",
      "sliding_tackle\n",
      "3.27\n",
      "--------------------------------------------------------\n",
      "gk_diving\n",
      "1.34\n",
      "--------------------------------------------------------\n",
      "gk_handling\n",
      "2.81\n",
      "--------------------------------------------------------\n",
      "gk_kicking\n",
      "9.98\n",
      "--------------------------------------------------------\n",
      "gk_positioning\n",
      "2.83\n",
      "--------------------------------------------------------\n",
      "gk_reflexes\n",
      "2.85\n",
      "--------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "num_cols = [c for c in player_att_df.columns if c not in \n",
    "            [\"player_id\", \"date\", \"preferred_foot\", \"attacking_work_rate\", \"defensive_work_rate\"]]\n",
    "for col in num_cols:\n",
    "    print(col)\n",
    "    print(round(player_att_df.groupby(\"player_id\")[col].agg(lambda x: x.var()**0.5).mean(),2))\n",
    "    print(\"--------------------------------------------------------\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8     498\n",
       "7     491\n",
       "6     484\n",
       "9     442\n",
       "5     433\n",
       "18    404\n",
       "20    397\n",
       "4     393\n",
       "19    392\n",
       "10    391\n",
       "21    389\n",
       "22    385\n",
       "23    359\n",
       "15    358\n",
       "16    357\n",
       "17    354\n",
       "11    346\n",
       "13    343\n",
       "12    340\n",
       "14    339\n",
       "3     331\n",
       "25    327\n",
       "24    317\n",
       "26    287\n",
       "27    247\n",
       "29    214\n",
       "28    213\n",
       "30    182\n",
       "2     158\n",
       "31    140\n",
       "33    124\n",
       "32    117\n",
       "34     96\n",
       "36     74\n",
       "35     72\n",
       "37     54\n",
       "38     52\n",
       "39     33\n",
       "41     25\n",
       "40     24\n",
       "42     15\n",
       "43     12\n",
       "46     10\n",
       "45     10\n",
       "44      9\n",
       "48      6\n",
       "50      4\n",
       "47      4\n",
       "56      2\n",
       "53      2\n",
       "51      1\n",
       "52      1\n",
       "54      1\n",
       "55      1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.groupby(\"player_id\").size().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_att_df = player_att_df.sort_values([\"player_id\", \"date\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "player_id                       int64\n",
       "date                   datetime64[ns]\n",
       "overall_rating                float64\n",
       "potential                     float64\n",
       "preferred_foot                 object\n",
       "attacking_work_rate            object\n",
       "defensive_work_rate            object\n",
       "crossing                      float64\n",
       "finishing                     float64\n",
       "heading_accuracy              float64\n",
       "short_passing                 float64\n",
       "volleys                       float64\n",
       "dribbling                     float64\n",
       "curve                         float64\n",
       "free_kick_accuracy            float64\n",
       "long_passing                  float64\n",
       "ball_control                  float64\n",
       "acceleration                  float64\n",
       "sprint_speed                  float64\n",
       "agility                       float64\n",
       "reactions                     float64\n",
       "balance                       float64\n",
       "shot_power                    float64\n",
       "jumping                       float64\n",
       "stamina                       float64\n",
       "strength                      float64\n",
       "long_shots                    float64\n",
       "aggression                    float64\n",
       "interceptions                 float64\n",
       "positioning                   float64\n",
       "vision                        float64\n",
       "penalties                     float64\n",
       "marking                       float64\n",
       "standing_tackle               float64\n",
       "sliding_tackle                float64\n",
       "gk_diving                     float64\n",
       "gk_handling                   float64\n",
       "gk_kicking                    float64\n",
       "gk_positioning                float64\n",
       "gk_reflexes                   float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "player_id                 0\n",
       "date                      0\n",
       "overall_rating          836\n",
       "potential               836\n",
       "preferred_foot          836\n",
       "attacking_work_rate    3230\n",
       "defensive_work_rate     836\n",
       "crossing                836\n",
       "finishing               836\n",
       "heading_accuracy        836\n",
       "short_passing           836\n",
       "volleys                2713\n",
       "dribbling               836\n",
       "curve                  2713\n",
       "free_kick_accuracy      836\n",
       "long_passing            836\n",
       "ball_control            836\n",
       "acceleration            836\n",
       "sprint_speed            836\n",
       "agility                2713\n",
       "reactions               836\n",
       "balance                2713\n",
       "shot_power              836\n",
       "jumping                2713\n",
       "stamina                 836\n",
       "strength                836\n",
       "long_shots              836\n",
       "aggression              836\n",
       "interceptions           836\n",
       "positioning             836\n",
       "vision                 2713\n",
       "penalties               836\n",
       "marking                 836\n",
       "standing_tackle         836\n",
       "sliding_tackle         2713\n",
       "gk_diving               836\n",
       "gk_handling             836\n",
       "gk_kicking              836\n",
       "gk_positioning          836\n",
       "gk_reflexes             836\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "right    138409\n",
       "left      44733\n",
       "Name: preferred_foot, dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df[\"preferred_foot\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_att_df[\"preferred_foot\"] = np.where(player_att_df[\"preferred_foot\"]==\"left\", 0, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "medium    125070\n",
       "high       42823\n",
       "low         8569\n",
       "None        3639\n",
       "norm         348\n",
       "y            106\n",
       "le           104\n",
       "stoc          89\n",
       "Name: attacking_work_rate, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df[\"attacking_work_rate\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in player_att_df.columns[2:]:\n",
    "    player_att_df[col] = player_att_df.groupby(\"player_id\")[col].ffill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "player_id                 0\n",
       "date                      0\n",
       "overall_rating            0\n",
       "potential                 0\n",
       "preferred_foot            0\n",
       "attacking_work_rate    2445\n",
       "defensive_work_rate       0\n",
       "crossing                  0\n",
       "finishing                 0\n",
       "heading_accuracy          0\n",
       "short_passing             0\n",
       "volleys                1926\n",
       "dribbling                 0\n",
       "curve                  1926\n",
       "free_kick_accuracy        0\n",
       "long_passing              0\n",
       "ball_control              0\n",
       "acceleration              0\n",
       "sprint_speed              0\n",
       "agility                1926\n",
       "reactions                 0\n",
       "balance                1926\n",
       "shot_power                0\n",
       "jumping                1926\n",
       "stamina                   0\n",
       "strength                  0\n",
       "long_shots                0\n",
       "aggression                0\n",
       "interceptions             0\n",
       "positioning               0\n",
       "vision                 1926\n",
       "penalties                 0\n",
       "marking                   0\n",
       "standing_tackle           0\n",
       "sliding_tackle         1926\n",
       "gk_diving                 0\n",
       "gk_handling               0\n",
       "gk_kicking                0\n",
       "gk_positioning            0\n",
       "gk_reflexes               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "overall_rating\n",
      "6     1417\n",
      "7     1385\n",
      "5     1310\n",
      "4     1215\n",
      "3     1142\n",
      "8     1084\n",
      "2     1019\n",
      "9      815\n",
      "1      636\n",
      "10     512\n",
      "11     260\n",
      "12     149\n",
      "13      70\n",
      "14      29\n",
      "15      10\n",
      "16       5\n",
      "18       1\n",
      "17       1\n",
      "Name: overall_rating, dtype: int64\n",
      "-------------------------------------------------------\n",
      "potential\n",
      "5     1487\n",
      "6     1457\n",
      "3     1375\n",
      "4     1358\n",
      "7     1246\n",
      "2     1214\n",
      "8      952\n",
      "1      784\n",
      "9      597\n",
      "10     344\n",
      "11     140\n",
      "12      77\n",
      "13      17\n",
      "14       7\n",
      "15       3\n",
      "17       1\n",
      "16       1\n",
      "Name: potential, dtype: int64\n",
      "-------------------------------------------------------\n",
      "preferred_foot\n",
      "1    9812\n",
      "2    1248\n",
      "Name: preferred_foot, dtype: int64\n",
      "-------------------------------------------------------\n",
      "crossing\n",
      "2     2107\n",
      "3     1851\n",
      "1     1635\n",
      "4     1628\n",
      "5     1326\n",
      "6      952\n",
      "7      724\n",
      "8      401\n",
      "9      240\n",
      "10     106\n",
      "11      55\n",
      "12      25\n",
      "13       9\n",
      "18       1\n",
      "Name: crossing, dtype: int64\n",
      "-------------------------------------------------------\n",
      "finishing\n",
      "2     2180\n",
      "3     1830\n",
      "4     1600\n",
      "1     1573\n",
      "5     1257\n",
      "6      885\n",
      "7      590\n",
      "8      451\n",
      "9      321\n",
      "10     179\n",
      "11     100\n",
      "12      52\n",
      "13      25\n",
      "14      13\n",
      "15       3\n",
      "16       1\n",
      "Name: finishing, dtype: int64\n",
      "-------------------------------------------------------\n",
      "heading_accuracy\n",
      "2     2131\n",
      "3     1987\n",
      "4     1731\n",
      "1     1618\n",
      "5     1354\n",
      "6      934\n",
      "7      591\n",
      "8      360\n",
      "9      184\n",
      "10     107\n",
      "11      38\n",
      "12      17\n",
      "13       7\n",
      "14       1\n",
      "Name: heading_accuracy, dtype: int64\n",
      "-------------------------------------------------------\n",
      "short_passing\n",
      "2     1763\n",
      "3     1638\n",
      "4     1541\n",
      "1     1418\n",
      "5     1360\n",
      "6     1133\n",
      "7      934\n",
      "8      622\n",
      "9      357\n",
      "10     173\n",
      "11      85\n",
      "12      27\n",
      "13       6\n",
      "14       3\n",
      "Name: short_passing, dtype: int64\n",
      "-------------------------------------------------------\n",
      "volleys\n",
      "2     3238\n",
      "1     2734\n",
      "3     2086\n",
      "4     1306\n",
      "5      671\n",
      "0      478\n",
      "6      292\n",
      "7      144\n",
      "8       69\n",
      "9       27\n",
      "10       8\n",
      "11       5\n",
      "13       2\n",
      "Name: volleys, dtype: int64\n",
      "-------------------------------------------------------\n",
      "dribbling\n",
      "2     1753\n",
      "3     1719\n",
      "4     1581\n",
      "5     1428\n",
      "1     1330\n",
      "6     1173\n",
      "7      861\n",
      "8      569\n",
      "9      348\n",
      "10     178\n",
      "11      77\n",
      "12      30\n",
      "13       9\n",
      "14       3\n",
      "15       1\n",
      "Name: dribbling, dtype: int64\n",
      "-------------------------------------------------------\n",
      "curve\n",
      "2     3227\n",
      "1     2900\n",
      "3     2033\n",
      "4     1278\n",
      "5      648\n",
      "0      478\n",
      "6      298\n",
      "7      127\n",
      "8       45\n",
      "9       17\n",
      "10       5\n",
      "11       2\n",
      "15       1\n",
      "12       1\n",
      "Name: curve, dtype: int64\n",
      "-------------------------------------------------------\n",
      "free_kick_accuracy\n",
      "2     3103\n",
      "3     2245\n",
      "1     1918\n",
      "4     1651\n",
      "5     1010\n",
      "6      566\n",
      "7      296\n",
      "8      154\n",
      "9       69\n",
      "10      27\n",
      "11      14\n",
      "13       3\n",
      "14       2\n",
      "12       1\n",
      "18       1\n",
      "Name: free_kick_accuracy, dtype: int64\n",
      "-------------------------------------------------------\n",
      "long_passing\n",
      "2     1967\n",
      "3     1781\n",
      "4     1663\n",
      "1     1514\n",
      "5     1371\n",
      "6     1068\n",
      "7      731\n",
      "8      470\n",
      "9      270\n",
      "10     122\n",
      "11      63\n",
      "12      22\n",
      "13      13\n",
      "14       3\n",
      "15       1\n",
      "16       1\n",
      "Name: long_passing, dtype: int64\n",
      "-------------------------------------------------------\n",
      "ball_control\n",
      "2     1729\n",
      "3     1639\n",
      "4     1477\n",
      "5     1433\n",
      "6     1263\n",
      "1     1260\n",
      "7      986\n",
      "8      599\n",
      "9      356\n",
      "10     193\n",
      "11      78\n",
      "12      31\n",
      "13      15\n",
      "15       1\n",
      "Name: ball_control, dtype: int64\n",
      "-------------------------------------------------------\n",
      "acceleration\n",
      "2     1722\n",
      "1     1665\n",
      "3     1648\n",
      "4     1469\n",
      "5     1319\n",
      "6     1152\n",
      "7      865\n",
      "8      551\n",
      "9      330\n",
      "10     181\n",
      "11      98\n",
      "12      34\n",
      "13      10\n",
      "14       7\n",
      "15       5\n",
      "16       2\n",
      "18       1\n",
      "17       1\n",
      "Name: acceleration, dtype: int64\n",
      "-------------------------------------------------------\n",
      "sprint_speed\n",
      "2     1703\n",
      "3     1624\n",
      "1     1574\n",
      "4     1412\n",
      "5     1356\n",
      "6     1174\n",
      "7      882\n",
      "8      605\n",
      "9      385\n",
      "10     183\n",
      "11      86\n",
      "12      47\n",
      "13      19\n",
      "14       4\n",
      "15       4\n",
      "17       1\n",
      "16       1\n",
      "Name: sprint_speed, dtype: int64\n",
      "-------------------------------------------------------\n",
      "agility\n",
      "1     2663\n",
      "2     2330\n",
      "3     2039\n",
      "4     1586\n",
      "5     1010\n",
      "6      565\n",
      "0      478\n",
      "7      243\n",
      "8       95\n",
      "9       38\n",
      "11       9\n",
      "10       4\n",
      "Name: agility, dtype: int64\n",
      "-------------------------------------------------------\n",
      "reactions\n",
      "2     1827\n",
      "3     1620\n",
      "4     1613\n",
      "1     1431\n",
      "5     1410\n",
      "6     1140\n",
      "7      847\n",
      "8      537\n",
      "9      329\n",
      "10     160\n",
      "11      84\n",
      "12      34\n",
      "13      19\n",
      "14       6\n",
      "16       2\n",
      "15       1\n",
      "Name: reactions, dtype: int64\n",
      "-------------------------------------------------------\n",
      "balance\n",
      "2     2865\n",
      "1     2669\n",
      "3     2221\n",
      "4     1466\n",
      "5      801\n",
      "0      478\n",
      "6      341\n",
      "7      147\n",
      "8       50\n",
      "9       15\n",
      "10       6\n",
      "11       1\n",
      "Name: balance, dtype: int64\n",
      "-------------------------------------------------------\n",
      "shot_power\n",
      "2     2365\n",
      "3     1948\n",
      "1     1784\n",
      "4     1595\n",
      "5     1281\n",
      "6      900\n",
      "7      604\n",
      "8      312\n",
      "9      149\n",
      "10      72\n",
      "11      32\n",
      "12      12\n",
      "14       4\n",
      "13       2\n",
      "Name: shot_power, dtype: int64\n",
      "-------------------------------------------------------\n",
      "jumping\n",
      "1     2797\n",
      "2     2407\n",
      "3     2070\n",
      "4     1515\n",
      "5      952\n",
      "6      493\n",
      "0      478\n",
      "7      234\n",
      "8       76\n",
      "9       20\n",
      "10      16\n",
      "11       2\n",
      "Name: jumping, dtype: int64\n",
      "-------------------------------------------------------\n",
      "stamina\n",
      "2     1721\n",
      "1     1676\n",
      "3     1628\n",
      "4     1478\n",
      "5     1359\n",
      "6     1122\n",
      "7      829\n",
      "8      573\n",
      "9      321\n",
      "10     181\n",
      "11      95\n",
      "12      45\n",
      "13      19\n",
      "14       4\n",
      "16       4\n",
      "15       3\n",
      "18       1\n",
      "17       1\n",
      "Name: stamina, dtype: int64\n",
      "-------------------------------------------------------\n",
      "strength\n",
      "2     1831\n",
      "3     1738\n",
      "1     1673\n",
      "4     1668\n",
      "5     1430\n",
      "6     1086\n",
      "7      779\n",
      "8      468\n",
      "9      230\n",
      "10      92\n",
      "11      45\n",
      "12      14\n",
      "13       6\n",
      "Name: strength, dtype: int64\n",
      "-------------------------------------------------------\n",
      "long_shots\n",
      "2     2313\n",
      "3     1919\n",
      "1     1734\n",
      "4     1574\n",
      "5     1296\n",
      "6      885\n",
      "7      592\n",
      "8      381\n",
      "9      187\n",
      "10      95\n",
      "11      56\n",
      "12      19\n",
      "13       8\n",
      "14       1\n",
      "Name: long_shots, dtype: int64\n",
      "-------------------------------------------------------\n",
      "aggression\n",
      "2     2263\n",
      "3     1959\n",
      "1     1696\n",
      "4     1645\n",
      "5     1299\n",
      "6      863\n",
      "7      622\n",
      "8      379\n",
      "9      191\n",
      "10      81\n",
      "11      42\n",
      "12      12\n",
      "13       7\n",
      "14       1\n",
      "Name: aggression, dtype: int64\n",
      "-------------------------------------------------------\n",
      "interceptions\n",
      "2     2046\n",
      "3     1862\n",
      "4     1618\n",
      "1     1476\n",
      "5     1258\n",
      "6      979\n",
      "7      712\n",
      "8      495\n",
      "9      328\n",
      "10     150\n",
      "11      78\n",
      "12      33\n",
      "13      14\n",
      "14       6\n",
      "15       3\n",
      "16       2\n",
      "Name: interceptions, dtype: int64\n",
      "-------------------------------------------------------\n",
      "positioning\n",
      "2     1861\n",
      "3     1789\n",
      "4     1650\n",
      "5     1370\n",
      "1     1348\n",
      "6     1044\n",
      "7      789\n",
      "8      561\n",
      "9      327\n",
      "10     189\n",
      "11      80\n",
      "12      33\n",
      "13      12\n",
      "14       4\n",
      "15       2\n",
      "17       1\n",
      "Name: positioning, dtype: int64\n",
      "-------------------------------------------------------\n",
      "vision\n",
      "2     2240\n",
      "1     1997\n",
      "3     1898\n",
      "4     1542\n",
      "5     1230\n",
      "6      786\n",
      "0      478\n",
      "7      447\n",
      "8      249\n",
      "9      112\n",
      "10      50\n",
      "11      18\n",
      "12       7\n",
      "13       6\n",
      "Name: vision, dtype: int64\n",
      "-------------------------------------------------------\n",
      "penalties\n",
      "2     2567\n",
      "3     2037\n",
      "1     1812\n",
      "4     1748\n",
      "5     1326\n",
      "6      784\n",
      "7      416\n",
      "8      217\n",
      "9      110\n",
      "10      31\n",
      "11       7\n",
      "13       3\n",
      "15       1\n",
      "12       1\n",
      "Name: penalties, dtype: int64\n",
      "-------------------------------------------------------\n",
      "marking\n",
      "2     2085\n",
      "3     1825\n",
      "4     1616\n",
      "1     1516\n",
      "5     1297\n",
      "6      913\n",
      "7      676\n",
      "8      502\n",
      "9      300\n",
      "10     173\n",
      "11      94\n",
      "12      39\n",
      "13      18\n",
      "15       3\n",
      "16       2\n",
      "14       1\n",
      "Name: marking, dtype: int64\n",
      "-------------------------------------------------------\n",
      "standing_tackle\n",
      "2     2069\n",
      "3     1731\n",
      "4     1615\n",
      "1     1467\n",
      "5     1299\n",
      "6      951\n",
      "7      719\n",
      "8      527\n",
      "9      328\n",
      "10     197\n",
      "11      87\n",
      "12      47\n",
      "13      16\n",
      "14       5\n",
      "19       1\n",
      "16       1\n",
      "Name: standing_tackle, dtype: int64\n",
      "-------------------------------------------------------\n",
      "sliding_tackle\n",
      "2     2475\n",
      "1     2198\n",
      "3     1986\n",
      "4     1414\n",
      "5      975\n",
      "6      647\n",
      "0      478\n",
      "7      412\n",
      "8      241\n",
      "9      127\n",
      "10      67\n",
      "11      20\n",
      "12      14\n",
      "14       4\n",
      "15       1\n",
      "13       1\n",
      "Name: sliding_tackle, dtype: int64\n",
      "-------------------------------------------------------\n",
      "gk_diving\n",
      "2     4996\n",
      "1     2699\n",
      "3     2410\n",
      "4      353\n",
      "5      161\n",
      "7      122\n",
      "6      122\n",
      "8       75\n",
      "9       68\n",
      "10      32\n",
      "11      14\n",
      "13       4\n",
      "12       3\n",
      "15       1\n",
      "Name: gk_diving, dtype: int64\n",
      "-------------------------------------------------------\n",
      "gk_handling\n",
      "2     4140\n",
      "3     3170\n",
      "1     2205\n",
      "4      979\n",
      "5      148\n",
      "6      133\n",
      "7       96\n",
      "8       80\n",
      "9       49\n",
      "10      32\n",
      "11      15\n",
      "13       6\n",
      "12       6\n",
      "14       1\n",
      "Name: gk_handling, dtype: int64\n",
      "-------------------------------------------------------\n",
      "gk_kicking\n",
      "2     3526\n",
      "1     2162\n",
      "3     1903\n",
      "4     1615\n",
      "5     1056\n",
      "6      491\n",
      "7      163\n",
      "8       66\n",
      "9       29\n",
      "10      23\n",
      "11      20\n",
      "12       6\n",
      "Name: gk_kicking, dtype: int64\n",
      "-------------------------------------------------------\n",
      "gk_positioning\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2     4159\n",
      "3     3163\n",
      "1     2195\n",
      "4      961\n",
      "5      164\n",
      "7      108\n",
      "6      108\n",
      "8       94\n",
      "9       53\n",
      "10      27\n",
      "11      15\n",
      "12       8\n",
      "13       2\n",
      "15       1\n",
      "14       1\n",
      "18       1\n",
      "Name: gk_positioning, dtype: int64\n",
      "-------------------------------------------------------\n",
      "gk_reflexes\n",
      "2     4134\n",
      "3     3129\n",
      "1     2210\n",
      "4      956\n",
      "5      163\n",
      "6      132\n",
      "8      101\n",
      "7       96\n",
      "9       66\n",
      "10      39\n",
      "11      18\n",
      "12      11\n",
      "13       3\n",
      "15       2\n",
      "Name: gk_reflexes, dtype: int64\n",
      "-------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "for col in [c for c in player_att_df.columns[2:] if c not in [\"attacking_work_rate\", \"defensive_work_rate\"]]: \n",
    "    print(col)\n",
    "    print(player_att_df.groupby(\"player_id\")[col].nunique().value_counts())\n",
    "    print(\"-------------------------------------------------------\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "player_id                 0\n",
       "date                      0\n",
       "overall_rating            0\n",
       "potential                 0\n",
       "preferred_foot            0\n",
       "attacking_work_rate    2445\n",
       "defensive_work_rate       0\n",
       "crossing                  0\n",
       "finishing                 0\n",
       "heading_accuracy          0\n",
       "short_passing             0\n",
       "volleys                1926\n",
       "dribbling                 0\n",
       "curve                  1926\n",
       "free_kick_accuracy        0\n",
       "long_passing              0\n",
       "ball_control              0\n",
       "acceleration              0\n",
       "sprint_speed              0\n",
       "agility                1926\n",
       "reactions                 0\n",
       "balance                1926\n",
       "shot_power                0\n",
       "jumping                1926\n",
       "stamina                   0\n",
       "strength                  0\n",
       "long_shots                0\n",
       "aggression                0\n",
       "interceptions             0\n",
       "positioning               0\n",
       "vision                 1926\n",
       "penalties                 0\n",
       "marking                   0\n",
       "standing_tackle           0\n",
       "sliding_tackle         1926\n",
       "gk_diving                 0\n",
       "gk_handling               0\n",
       "gk_kicking                0\n",
       "gk_positioning            0\n",
       "gk_reflexes               0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "attack_defense_rate_mapping = {\"high\": 3, \"medium\": 2, \"norm\": 2, \"ormal\": 2, \"low\": 1}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_att_df[\"attacking_work_rate_imp\"] = player_att_df[\"attacking_work_rate\"].map(attack_defense_rate_mapping)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.0    125961\n",
       "3.0     42949\n",
       "1.0      8614\n",
       "Name: attacking_work_rate_imp, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df[\"attacking_work_rate_imp\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6454"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.attacking_work_rate_imp.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "attack_cols = [\"attacking_work_rate_imp\"] + player_att_df.columns[7:-9].tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "attack_cols = (player_att_df[attack_cols].corr()[\"attacking_work_rate_imp\"]\n",
    "               .sort_values().tail(11).head(10).index.values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "long_shots         0\n",
       "volleys         1926\n",
       "crossing           0\n",
       "curve           1926\n",
       "finishing          0\n",
       "positioning        0\n",
       "dribbling          0\n",
       "agility         1926\n",
       "sprint_speed       0\n",
       "acceleration       0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df[attack_cols].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "long_shots      18.365093\n",
       "volleys         18.263053\n",
       "crossing        17.245154\n",
       "curve           18.260479\n",
       "finishing       19.040732\n",
       "positioning     18.440471\n",
       "dribbling       17.751109\n",
       "agility         12.949663\n",
       "sprint_speed    12.567452\n",
       "acceleration    12.978621\n",
       "dtype: float64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df[attack_cols].var()**0.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "attack_imp_df = (player_att_df[attack_cols]-player_att_df[attack_cols].mean())/player_att_df[attack_cols].std()\n",
    "\n",
    "attack_imp_df[\"attacking_work_rate_imp\"] = player_att_df[\"attacking_work_rate_imp\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "att_imp = KNNImputer(n_neighbors=30)\n",
    "\n",
    "att_work_rate_imp = att_imp.fit_transform(attack_imp_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_att_df[\"attacking_work_rate_imp\"] = att_work_rate_imp[:,-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(183978, 41)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['player_id', 'date', 'overall_rating', 'potential', 'preferred_foot',\n",
       "       'attacking_work_rate', 'defensive_work_rate', 'crossing', 'finishing',\n",
       "       'heading_accuracy', 'short_passing', 'volleys', 'dribbling', 'curve',\n",
       "       'free_kick_accuracy', 'long_passing', 'ball_control', 'acceleration',\n",
       "       'sprint_speed', 'agility', 'reactions', 'balance', 'shot_power',\n",
       "       'jumping', 'stamina', 'strength', 'long_shots', 'aggression',\n",
       "       'interceptions', 'positioning', 'vision', 'penalties', 'marking',\n",
       "       'standing_tackle', 'sliding_tackle', 'gk_diving', 'gk_handling',\n",
       "       'gk_kicking', 'gk_positioning', 'gk_reflexes',\n",
       "       'attacking_work_rate_imp'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_att_df[\"defensive_work_rate_imp\"] = player_att_df[\"defensive_work_rate\"].map(attack_defense_rate_mapping)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "def_cols = [\"defensive_work_rate_imp\"] + player_att_df.columns[-25:-6].tolist()\n",
    "\n",
    "def_cols = player_att_df[def_cols].corr()[\"defensive_work_rate_imp\"].sort_values().tail(11).head(10).index.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "def_imp_df = (player_att_df[def_cols]-player_att_df[def_cols].mean())/player_att_df[def_cols].std()\n",
    "\n",
    "def_imp_df[\"defensive_work_rate_imp\"] = player_att_df[\"defensive_work_rate_imp\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "def_imp = KNNImputer(n_neighbors=30)\n",
    "\n",
    "def_work_rate_imp = def_imp.fit_transform(def_imp_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_att_df[\"defensive_work_rate_imp\"] = def_work_rate_imp[:,-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "defensive_work_rate_imp\n",
       "1.000000    29.982507\n",
       "1.100000    24.000000\n",
       "1.200000    20.000000\n",
       "1.233333    22.250000\n",
       "1.266667    22.428571\n",
       "1.300000    21.181818\n",
       "1.333333    21.416667\n",
       "1.366667    17.266667\n",
       "1.400000    22.238095\n",
       "1.433333    24.600000\n",
       "1.466667    23.409091\n",
       "1.500000    21.800000\n",
       "1.533333    22.633333\n",
       "1.566667    25.956522\n",
       "1.600000    25.840580\n",
       "1.633333    26.470588\n",
       "1.666667    28.392857\n",
       "1.700000    26.601504\n",
       "1.733333    27.152941\n",
       "1.766667    29.210526\n",
       "1.800000    27.461538\n",
       "1.833333    28.310881\n",
       "1.866667    30.820106\n",
       "1.900000    31.035176\n",
       "1.933333    34.763333\n",
       "1.966667    36.676829\n",
       "2.000000    46.031425\n",
       "2.033333    55.488235\n",
       "2.066667    56.761273\n",
       "2.100000    58.839506\n",
       "2.133333    61.616477\n",
       "2.166667    60.216418\n",
       "2.200000    63.479705\n",
       "2.233333    63.589212\n",
       "2.266667    63.223301\n",
       "2.300000    61.354497\n",
       "2.333333    66.650000\n",
       "2.366667    67.910569\n",
       "2.400000    65.935484\n",
       "2.433333    67.917808\n",
       "2.466667    70.151515\n",
       "2.500000    69.968254\n",
       "2.533333    69.152174\n",
       "2.566667    71.243243\n",
       "2.600000    75.205882\n",
       "2.633333    73.558824\n",
       "2.666667    78.473684\n",
       "2.700000    71.380952\n",
       "2.733333    71.454545\n",
       "2.766667    76.875000\n",
       "2.800000    76.625000\n",
       "2.833333    72.500000\n",
       "2.866667    75.500000\n",
       "2.900000    74.555556\n",
       "2.933333    66.750000\n",
       "3.000000    61.350287\n",
       "Name: marking, dtype: float64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.groupby(\"defensive_work_rate_imp\")[\"marking\"].mean().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols_to_imp = player_att_df.isnull().sum().reset_index()\n",
    "cols_to_imp = [c for c in cols_to_imp.loc[cols_to_imp[0] > 1900, \"index\"].values if c != \"attacking_work_rate\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['volleys',\n",
       " 'curve',\n",
       " 'agility',\n",
       " 'balance',\n",
       " 'jumping',\n",
       " 'vision',\n",
       " 'sliding_tackle']"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols_to_imp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['overall_rating',\n",
       " 'potential',\n",
       " 'crossing',\n",
       " 'finishing',\n",
       " 'heading_accuracy',\n",
       " 'short_passing',\n",
       " 'volleys',\n",
       " 'dribbling',\n",
       " 'curve',\n",
       " 'free_kick_accuracy',\n",
       " 'long_passing',\n",
       " 'ball_control',\n",
       " 'acceleration',\n",
       " 'sprint_speed',\n",
       " 'agility',\n",
       " 'reactions',\n",
       " 'balance',\n",
       " 'shot_power',\n",
       " 'jumping',\n",
       " 'stamina',\n",
       " 'strength',\n",
       " 'long_shots',\n",
       " 'aggression',\n",
       " 'interceptions',\n",
       " 'positioning',\n",
       " 'vision',\n",
       " 'penalties',\n",
       " 'marking',\n",
       " 'standing_tackle',\n",
       " 'sliding_tackle',\n",
       " 'gk_diving',\n",
       " 'gk_handling',\n",
       " 'gk_kicking',\n",
       " 'gk_positioning',\n",
       " 'gk_reflexes',\n",
       " 'attacking_work_rate_imp',\n",
       " 'defensive_work_rate_imp']"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "numeric_cols = player_att_df.select_dtypes([np.number]).columns\n",
    "numeric_cols = [col for col in numeric_cols if col not in [\"player_id\", \"preferred_foot\"]]\n",
    "numeric_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "for col in cols_to_imp:\n",
    "    \n",
    "    imp_col = (player_att_df[numeric_cols].corr()[col].sort_values().tail(11).head(10).index.values)\n",
    "    \n",
    "    imp_df = (player_att_df[imp_col]-player_att_df[imp_col].mean())/player_att_df[imp_col].std()\n",
    "\n",
    "    imp_df[col] = player_att_df[col]\n",
    "    \n",
    "    imp = KNNImputer(n_neighbors=30)\n",
    "\n",
    "    col_imp_df = imp.fit_transform(imp_df)\n",
    "    \n",
    "    player_att_df[\"{}_imp\".format(col)] = col_imp_df[:,-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(183978, 49)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_att_df.to_csv(dir+\"player_att_imp.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "#player_att_df = pd.read_csv(dir+\"player_att_imp.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tie attributes to match"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "col = [c for c in match_df.columns if \"player\" in c][0]\n",
    "\n",
    "player_by_match_df = match_df[[\"date\", \"match_id\", col]]\n",
    "\n",
    "player_by_match_df.columns = [\"date\", \"match_id\", \"player_id\"]\n",
    "\n",
    "for col in [c for c in match_df.columns if \"player\" in c][1:]:\n",
    "    \n",
    "    pbm_df = match_df[[\"date\", \"match_id\", col]]\n",
    "    \n",
    "    pbm_df.columns = [\"date\", \"match_id\", \"player_id\"]\n",
    "    \n",
    "    player_by_match_df = pd.concat([player_by_match_df, pbm_df])\n",
    "    \n",
    "player_by_match_df = player_by_match_df.drop_duplicates()\n",
    "\n",
    "player_by_match_df[\"date\"] = pd.to_datetime(player_by_match_df[\"date\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date            0\n",
       "match_id        0\n",
       "player_id    4868\n",
       "dtype: int64"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_df = player_by_match_df.loc[~player_by_match_df[\"player_id\"].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['player_id',\n",
       " 'attribute_date',\n",
       " 'overall_rating',\n",
       " 'potential',\n",
       " 'preferred_foot',\n",
       " 'attacking_work_rate',\n",
       " 'defensive_work_rate',\n",
       " 'crossing',\n",
       " 'finishing',\n",
       " 'heading_accuracy',\n",
       " 'short_passing',\n",
       " 'volleys',\n",
       " 'dribbling',\n",
       " 'curve',\n",
       " 'free_kick_accuracy',\n",
       " 'long_passing',\n",
       " 'ball_control',\n",
       " 'acceleration',\n",
       " 'sprint_speed',\n",
       " 'agility',\n",
       " 'reactions',\n",
       " 'balance',\n",
       " 'shot_power',\n",
       " 'jumping',\n",
       " 'stamina',\n",
       " 'strength',\n",
       " 'long_shots',\n",
       " 'aggression',\n",
       " 'interceptions',\n",
       " 'positioning',\n",
       " 'vision',\n",
       " 'penalties',\n",
       " 'marking',\n",
       " 'standing_tackle',\n",
       " 'sliding_tackle',\n",
       " 'gk_diving',\n",
       " 'gk_handling',\n",
       " 'gk_kicking',\n",
       " 'gk_positioning',\n",
       " 'gk_reflexes',\n",
       " 'attacking_work_rate_imp',\n",
       " 'defensive_work_rate_imp',\n",
       " 'volleys_imp',\n",
       " 'curve_imp',\n",
       " 'agility_imp',\n",
       " 'balance_imp',\n",
       " 'jumping_imp',\n",
       " 'vision_imp',\n",
       " 'sliding_tackle_imp']"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_att_df.rename(columns={'date':'attribute_date'}, inplace=True)\n",
    "\n",
    "final_att_cols = [c for c in player_att_df.columns if col not in [\"preferred_foot\",\n",
    "                                                                  \"attacking_work_rate\",\n",
    "                                                                  \"defensive_working_rate\"]]\n",
    "\n",
    "final_att_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_merged = player_by_match_df.merge(player_att_df[final_att_cols], on=\"player_id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(11868330, 51)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_merged.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_merged[\"day_diff\"] = (player_by_match_merged[\"date\"] - \n",
    "                                      player_by_match_merged[\"attribute_date\"]).dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.05      32.0\n",
       "0.25     410.0\n",
       "0.50    1125.0\n",
       "0.75    2463.5\n",
       "0.95    7412.7\n",
       "Name: day_diff, dtype: float64"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_merged[\"day_diff\"].value_counts().quantile([0.05, 0.25, 0.5, 0.75, 0.95])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7fd496393280>"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX0AAAD4CAYAAAAAczaOAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAXvUlEQVR4nO3dcYxV533m8e8TcAj1xAavk1kCaCEtrYqNQsqIJZv1aiZ2a+pYwZEaaSI3xoorIq8jJS2rNTTSNlGERHdDsrId006WrHFNM6VJXJAT2rrUoyiSCYGsnQFj1tMydcdQaIuNmayFAvntH+cluRnfmbncOXPnHt7nI13dc3/nvOc8Z2B+c+97z9xRRGBmZnl4y0wHMDOz1nHTNzPLiJu+mVlG3PTNzDLipm9mlpHZMx1gMjfeeGMsWbKkqbE/+tGPuPbaa8sNVLIqZATnLFMVMkI1clYhI8xMzsOHD/9LRLzjTSsioq1vq1atimY988wzTY9tlSpkjHDOMlUhY0Q1clYhY8TM5AQORZ2e6ukdM7OMuOmbmWXETd/MLCNu+mZmGXHTNzPLiJu+mVlG3PTNzDLipm9mlhE3fTOzjLT9xzBMxeAr57h307daftzhrR9s+THNzBox6TN9SW+TdFDS85KOSvpcqn9W0iuSnku3O2rGbJY0JOm4pNtr6qskDaZ1D0nS9JyWmZnV08gz/QvAByJiVNI1wHcl7UvrvhQRX6jdWNJyoBe4CXgX8DeSfjkiLgHbgQ3AAeDbwFpgH2Zm1hKTPtNPn90zmh5ek24T/WHddUB/RFyIiBPAELBa0gLguoh4Nn0Y0OPAXVOLb2ZmV0LRwB9GlzQLOAz8EvDliHhQ0meBe4HXgUPAxoh4VdIjwIGIeCKN3UHxbH4Y2BoRt6X6LcCDEXFnneNtoHhFQGdn56r+/v6mTu7M2XOcfqOpoVOyYuH1DW87OjpKR0fHNKYph3OWpwoZoRo5q5ARZiZnT0/P4YjoGltv6I3cNDWzUtI84ElJN1NM1Xye4ln/54FtwMeBevP0MUG93vH6gD6Arq6u6O7ubiTmmzy8aw/bBlv/XvXw3d0NbzswMECz59dKzlmeKmSEauSsQkZor5xXdMlmRLwGDABrI+J0RFyKiJ8AXwFWp81GgMU1wxYBJ1N9UZ26mZm1SCNX77wjPcNH0lzgNuDFNEd/2YeBI2l5L9AraY6kpcAy4GBEnALOS1qTrtq5B9hT4rmYmdkkGpn7WADsTPP6bwF2R8RTkv5E0kqKKZph4BMAEXFU0m7gBeAi8ECaHgK4H3gMmEsxz+8rd8zMWmjSph8RPwTeW6f+sQnGbAG21KkfAm6+woxmZlYSfwyDmVlG3PTNzDLipm9mlhE3fTOzjLjpm5llxE3fzCwjbvpmZhlx0zczy4ibvplZRtz0zcwy4qZvZpYRN30zs4y46ZuZZcRN38wsI276ZmYZcdM3M8uIm76ZWUbc9M3MMuKmb2aWETd9M7OMTNr0Jb1N0kFJz0s6KulzqX6DpKclvZTu59eM2SxpSNJxSbfX1FdJGkzrHpKk6TktMzOrp5Fn+heAD0TEe4CVwFpJa4BNwP6IWAbsT4+RtBzoBW4C1gKPSpqV9rUd2AAsS7e1JZ6LmZlNYtKmH4XR9PCadAtgHbAz1XcCd6XldUB/RFyIiBPAELBa0gLguoh4NiICeLxmjJmZtYCK/jvJRsUz9cPALwFfjogHJb0WEfNqtnk1IuZLegQ4EBFPpPoOYB8wDGyNiNtS/RbgwYi4s87xNlC8IqCzs3NVf39/Uyd35uw5Tr/R1NApWbHw+oa3HR0dpaOjYxrTlMM5y1OFjFCNnFXICDOTs6en53BEdI2tz25kcERcAlZKmgc8KenmCTavN08fE9TrHa8P6APo6uqK7u7uRmK+ycO79rBtsKFTLNXw3d0NbzswMECz59dKzlmeKmSEauSsQkZor5xXdPVORLwGDFDMxZ9OUzak+zNpsxFgcc2wRcDJVF9Up25mZi3SyNU770jP8JE0F7gNeBHYC6xPm60H9qTlvUCvpDmSllK8YXswIk4B5yWtSVft3FMzxszMWqCRuY8FwM40r/8WYHdEPCXpWWC3pPuAl4GPAETEUUm7gReAi8ADaXoI4H7gMWAuxTz/vjJPxszMJjZp04+IHwLvrVP/V+DWccZsAbbUqR8CJno/wMzMppF/I9fMLCNu+mZmGXHTNzPLiJu+mVlG3PTNzDLipm9mlhE3fTOzjLjpm5llxE3fzCwjbvpmZhlx0zczy4ibvplZRtz0zcwy4qZvZpYRN30zs4y46ZuZZcRN38wsI276ZmYZcdM3M8uIm76ZWUYmbfqSFkt6RtIxSUclfSrVPyvpFUnPpdsdNWM2SxqSdFzS7TX1VZIG07qHJGl6TsvMzOqZ3cA2F4GNEfEDSW8HDkt6Oq37UkR8oXZjScuBXuAm4F3A30j65Yi4BGwHNgAHgG8Da4F95ZyKmZlNZtJn+hFxKiJ+kJbPA8eAhRMMWQf0R8SFiDgBDAGrJS0ArouIZyMigMeBu6Z8BmZm1jAV/bfBjaUlwHeAm4HfA+4FXgcOUbwaeFXSI8CBiHgijdlB8Wx+GNgaEbel+i3AgxFxZ53jbKB4RUBnZ+eq/v7+pk7uzNlznH6jqaFTsmLh9Q1vOzo6SkdHxzSmKYdzlqcKGaEaOauQEWYmZ09Pz+GI6Bpbb2R6BwBJHcA3gE9HxOuStgOfByLdbwM+DtSbp48J6m8uRvQBfQBdXV3R3d3daMyf8/CuPWwbbPgUSzN8d3fD2w4MDNDs+bWSc5anChmhGjmrkBHaK2dDV+9Iuoai4e+KiG8CRMTpiLgUET8BvgKsTpuPAItrhi8CTqb6ojp1MzNrkUau3hGwAzgWEV+sqS+o2ezDwJG0vBfolTRH0lJgGXAwIk4B5yWtSfu8B9hT0nmYmVkDGpn7eD/wMWBQ0nOp9vvARyWtpJiiGQY+ARARRyXtBl6guPLngXTlDsD9wGPAXIp5fl+5Y2bWQpM2/Yj4LvXn4789wZgtwJY69UMUbwKbmdkM8G/kmpllxE3fzCwjbvpmZhlx0zczy4ibvplZRtz0zcwy4qZvZpYRN30zs4y46ZuZZcRN38wsI276ZmYZcdM3M8uIm76ZWUbc9M3MMuKmb2aWETd9M7OMuOmbmWXETd/MLCNu+mZmGXHTNzPLyKRNX9JiSc9IOibpqKRPpfoNkp6W9FK6n18zZrOkIUnHJd1eU18laTCte0hSvT+4bmZm06SRZ/oXgY0R8avAGuABScuBTcD+iFgG7E+PSet6gZuAtcCjkmalfW0HNgDL0m1tiediZmaTmLTpR8SpiPhBWj4PHAMWAuuAnWmzncBdaXkd0B8RFyLiBDAErJa0ALguIp6NiAAerxljZmYtoKL/NrixtAT4DnAz8HJEzKtZ92pEzJf0CHAgIp5I9R3APmAY2BoRt6X6LcCDEXFnneNsoHhFQGdn56r+/v6mTu7M2XOcfqOpoVOyYuH1DW87OjpKR0fHNKYph3OWpwoZoRo5q5ARZiZnT0/P4YjoGluf3egOJHUA3wA+HRGvTzAdX29FTFB/czGiD+gD6Orqiu7u7kZj/pyHd+1h22DDp1ia4bu7G952YGCAZs+vlZyzPFXICNXIWYWM0F45G7p6R9I1FA1/V0R8M5VPpykb0v2ZVB8BFtcMXwScTPVFdepmZtYijVy9I2AHcCwivlizai+wPi2vB/bU1HslzZG0lOIN24MRcQo4L2lN2uc9NWPMzKwFGpn7eD/wMWBQ0nOp9vvAVmC3pPuAl4GPAETEUUm7gRcorvx5ICIupXH3A48Bcynm+feVdB5mZtaASZt+RHyX+vPxALeOM2YLsKVO/RDFm8BmZjYD/Bu5ZmYZcdM3M8uIm76ZWUbc9M3MMuKmb2aWETd9M7OMuOmbmWXETd/MLCNu+mZmGXHTNzPLiJu+mVlG3PTNzDLipm9mlhE3fTOzjLjpm5llxE3fzCwjbvpmZhlx0zczy4ibvplZRtz0zcwyMmnTl/RVSWckHampfVbSK5KeS7c7atZtljQk6bik22vqqyQNpnUPSRrvj62bmdk0aeSZ/mPA2jr1L0XEynT7NoCk5UAvcFMa86ikWWn77cAGYFm61dunmZlNo0mbfkR8Bzjb4P7WAf0RcSEiTgBDwGpJC4DrIuLZiAjgceCuZkObmVlzZk9h7Ccl3QMcAjZGxKvAQuBAzTYjqfbjtDy2XpekDRSvCujs7GRgYKCpgJ1zYeOKi02NnYoryTs6Otr0+bWSc5anChmhGjmrkBHaK2ezTX878Hkg0v024ONAvXn6mKBeV0T0AX0AXV1d0d3d3VTIh3ftYdvgVH6uNWf47u6Gtx0YGKDZ82sl5yxPFTJCNXJWISO0V86mrt6JiNMRcSkifgJ8BVidVo0Ai2s2XQScTPVFdepmZtZCTTX9NEd/2YeBy1f27AV6Jc2RtJTiDduDEXEKOC9pTbpq5x5gzxRym5lZEyad+5D0NaAbuFHSCPAHQLeklRRTNMPAJwAi4qik3cALwEXggYi4lHZ1P8WVQHOBfelmZmYtNGnTj4iP1invmGD7LcCWOvVDwM1XlM7MzErl38g1M8uIm76ZWUbc9M3MMuKmb2aWETd9M7OMuOmbmWXETd/MLCOt/2CaDCzZ9K2Gt9244iL3XsH2kxne+sHS9mVmVx8/0zczy4ibvplZRtz0zcwy4qZvZpYRN30zs4y46ZuZZcRN38wsI276ZmYZcdM3M8uIm76ZWUbc9M3MMuKmb2aWkUmbvqSvSjoj6UhN7QZJT0t6Kd3Pr1m3WdKQpOOSbq+pr5I0mNY9JEnln46ZmU2kkWf6jwFrx9Q2AfsjYhmwPz1G0nKgF7gpjXlU0qw0ZjuwAViWbmP3aWZm02zSph8R3wHOjimvA3am5Z3AXTX1/oi4EBEngCFgtaQFwHUR8WxEBPB4zRgzM2sRFT14ko2kJcBTEXFzevxaRMyrWf9qRMyX9AhwICKeSPUdwD5gGNgaEbel+i3AgxFx5zjH20DxqoDOzs5V/f39TZ3cmbPnOP1GU0NbpnMupWZcsfD68nZWY3R0lI6OjmnZd5mqkLMKGaEaOauQEWYmZ09Pz+GI6BpbL/uPqNSbp48J6nVFRB/QB9DV1RXd3d1NhXl41x62Dbb334nZuOJiqRmH7+4ubV+1BgYGaPbfoZWqkLMKGaEaOauQEdorZ7NX75xOUzak+zOpPgIsrtluEXAy1RfVqZuZWQs12/T3AuvT8npgT029V9IcSUsp3rA9GBGngPOS1qSrdu6pGWNmZi0y6byCpK8B3cCNkkaAPwC2Arsl3Qe8DHwEICKOStoNvABcBB6IiEtpV/dTXAk0l2Kef1+pZ2JmZpOatOlHxEfHWXXrONtvAbbUqR8Cbr6idGZmVir/Rq6ZWUbc9M3MMuKmb2aWETd9M7OMuOmbmWXETd/MLCNu+mZmGXHTNzPLiJu+mVlG3PTNzDLipm9mlhE3fTOzjLjpm5llxE3fzCwjbvpmZhlx0zczy4ibvplZRtz0zcwy4qZvZpYRN30zs4xMqelLGpY0KOk5SYdS7QZJT0t6Kd3Pr9l+s6QhSccl3T7V8GZmdmXKeKbfExErI6IrPd4E7I+IZcD+9BhJy4Fe4CZgLfCopFklHN/MzBo0HdM764CdaXkncFdNvT8iLkTECWAIWD0Nxzczs3EoIpofLJ0AXgUC+OOI6JP0WkTMq9nm1YiYL+kR4EBEPJHqO4B9EfH1OvvdAGwA6OzsXNXf399UvjNnz3H6jaaGtkznXErNuGLh9eXtrMbo6CgdHR3Tsu8yVSFnFTJCNXJWISPMTM6enp7DNTMwPzV7ivt9f0SclPRO4GlJL06wrerU6v7EiYg+oA+gq6sruru7mwr38K49bBuc6ilOr40rLpaacfju7tL2VWtgYIBm/x1aqQo5q5ARqpGzChmhvXJOqdtExMl0f0bSkxTTNaclLYiIU5IWAGfS5iPA4prhi4CTUzm+vdmSTd+alv1uXHGReyfY9/DWD07Lcc2sXE3P6Uu6VtLbLy8DvwEcAfYC69Nm64E9aXkv0CtpjqSlwDLgYLPHNzOzKzeVZ/qdwJOSLu/nTyPiLyV9H9gt6T7gZeAjABFxVNJu4AXgIvBARFyaUnozM7siTTf9iPh74D116v8K3DrOmC3AlmaPaWZmU+PfyDUzy4ibvplZRtz0zcwy4qZvZpYRN30zs4y46ZuZZcRN38wsI276ZmYZcdM3M8tIe38EpVkDaj9kbrIPhiuTP2TOqshN30oxXZ/uaWbl8vSOmVlG/EzfrEnNvropYwrKU0vWLD/TNzPLiJu+mVlG3PTNzDLipm9mlhG/kWtWQa24RLbeG85+A7n6/EzfzCwjbvpmZhlpedOXtFbScUlDkja1+vhmZjlr6Zy+pFnAl4FfB0aA70vaGxEvtDKHmTVnpj5uw+8llKfVb+SuBoYi4u8BJPUD6wA3fTMb13g/bFrxAXtX2w8cRUTrDib9FrA2In4nPf4Y8O8j4pNjttsAbEgPfwU43uQhbwT+pcmxrVKFjOCcZapCRqhGzipkhJnJ+e8i4h1ji61+pq86tTf91ImIPqBvygeTDkVE11T3M52qkBGcs0xVyAjVyFmFjNBeOVv9Ru4IsLjm8SLgZIszmJllq9VN//vAMklLJb0V6AX2tjiDmVm2Wjq9ExEXJX0S+CtgFvDViDg6jYec8hRRC1QhIzhnmaqQEaqRswoZoY1ytvSNXDMzm1n+jVwzs4y46ZuZZeSqbPoz+VEPkhZLekbSMUlHJX0q1W+Q9LSkl9L9/Joxm1PW45Jur6mvkjSY1j0kqd4lr1PNO0vS/5H0VLvmlDRP0tclvZi+ru9rt5ySfjf9ex+R9DVJb2uHjJK+KumMpCM1tdJySZoj6c9S/XuSlpSY83+kf/MfSnpS0ryZzFkvY826/yIpJN04kxkbEhFX1Y3iDeK/A94NvBV4HljewuMvAH4tLb8d+L/AcuC/A5tSfRPwh2l5eco4B1iass9K6w4C76P4/YZ9wG9OQ97fA/4UeCo9brucwE7gd9LyW4F57ZQTWAicAOamx7uBe9shI/CfgF8DjtTUSssF/Gfgj9JyL/BnJeb8DWB2Wv7Dmc5ZL2OqL6a4OOUfgBtn+ms56XlMx05n8pa+mH9V83gzsHkG8+yh+Kyh48CCVFsAHK+XL/3neV/a5sWa+keBPy452yJgP/ABftb02yoncB1FQ9WYetvkpGj6/wjcQHFF3FOpYbVFRmAJP99MS8t1eZu0PJvit05VRs4x6z4M7JrpnPUyAl8H3gMM87OmP6Nfy4luV+P0zuVvwMtGUq3l0suz9wLfAzoj4hRAun9n2my8vAvT8th6mf4n8F+Bn9TU2i3nu4F/Bv53mob6X5KubaecEfEK8AXgZeAUcC4i/rqdMo5RZq6fjomIi8A54N9MQ+aPUzwrbquckj4EvBIRz49Z1TYZx7oam35DH/Uw7SGkDuAbwKcj4vWJNq1TiwnqpZB0J3AmIg43OmScPNP99Z5N8ZJ6e0S8F/gRxZTEeFqeM82Jr6N4Gf8u4FpJvz3RkHGyzPT/3WZyTXtmSZ8BLgK7JjlmS3NK+gXgM8B/q7d6nOPN6NcSrs6mP+Mf9SDpGoqGvysivpnKpyUtSOsXAGdSfby8I2l5bL0s7wc+JGkY6Ac+IOmJNsw5AoxExPfS469T/BBop5y3ASci4p8j4sfAN4H/0GYZa5WZ66djJM0GrgfOlhVU0nrgTuDuSPMebZTzFyl+0D+fvo8WAT+Q9G/bKOObXI1Nf0Y/6iG9E78DOBYRX6xZtRdYn5bXU8z1X673pnfulwLLgIPpZfd5SWvSPu+pGTNlEbE5IhZFxBKKr9HfRsRvt2HOfwL+UdKvpNKtFB/F3U45XwbWSPqFtO9bgWNtlrFWmblq9/VbFP+PynoFtRZ4EPhQRPy/MflnPGdEDEbEOyNiSfo+GqG4iOOf2iXjeMGvuhtwB8VVM38HfKbFx/6PFC/Jfgg8l253UMzN7QdeSvc31Iz5TMp6nJqrNYAu4Eha9wjT8KZOOk43P3sjt+1yAiuBQ+lr+hfA/HbLCXwOeDHt/08ortqY8YzA1yjeZ/gxRVO6r8xcwNuAPweGKK5KeXeJOYco5rgvfx/90UzmrJdxzPph0hu5M/m1nOzmj2EwM8vI1Ti9Y2Zm43DTNzPLiJu+mVlG3PTNzDLipm9mlhE3fTOzjLjpm5ll5P8De6MwMDU+LcAAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "player_by_match_merged[\"day_diff\"].value_counts().hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_merged = player_by_match_merged.loc[player_by_match_merged[\"day_diff\"] >= 0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_merged = player_by_match_merged.sort_values([\"player_id\", \"date\", \"day_diff\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>player_id</th>\n",
       "      <th>date</th>\n",
       "      <th>attribute_date</th>\n",
       "      <th>day_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8807078</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-01</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807077</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-01</td>\n",
       "      <td>2007-08-30</td>\n",
       "      <td>549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807076</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-01</td>\n",
       "      <td>2007-02-22</td>\n",
       "      <td>738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807400</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-07</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807399</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-07</td>\n",
       "      <td>2007-08-30</td>\n",
       "      <td>555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807398</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-07</td>\n",
       "      <td>2007-02-22</td>\n",
       "      <td>744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8806770</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-14</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8806769</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-14</td>\n",
       "      <td>2007-08-30</td>\n",
       "      <td>562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8806768</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-14</td>\n",
       "      <td>2007-02-22</td>\n",
       "      <td>751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807414</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-21</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         player_id       date attribute_date  day_diff\n",
       "8807078     2625.0 2009-03-01     2008-08-30       183\n",
       "8807077     2625.0 2009-03-01     2007-08-30       549\n",
       "8807076     2625.0 2009-03-01     2007-02-22       738\n",
       "8807400     2625.0 2009-03-07     2008-08-30       189\n",
       "8807399     2625.0 2009-03-07     2007-08-30       555\n",
       "8807398     2625.0 2009-03-07     2007-02-22       744\n",
       "8806770     2625.0 2009-03-14     2008-08-30       196\n",
       "8806769     2625.0 2009-03-14     2007-08-30       562\n",
       "8806768     2625.0 2009-03-14     2007-02-22       751\n",
       "8807414     2625.0 2009-03-21     2008-08-30       203"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_merged.loc[player_by_match_merged[\"player_id\"]==2625.0,\n",
    "                           [\"player_id\", \"date\", \"attribute_date\", \"day_diff\"]].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_merged = (player_by_match_merged\n",
    "                          .sort_values([\"player_id\", \"date\", \"day_diff\"])\n",
    "                          .groupby([\"player_id\", \"date\"]).head(1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>player_id</th>\n",
       "      <th>date</th>\n",
       "      <th>attribute_date</th>\n",
       "      <th>day_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8807078</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-01</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807400</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-07</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8806770</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-14</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807414</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-03-21</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807092</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-04-03</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>216</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807428</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-04-11</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8806784</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-04-18</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807442</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-04-25</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807274</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-05-09</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8807288</th>\n",
       "      <td>2625.0</td>\n",
       "      <td>2009-05-16</td>\n",
       "      <td>2008-08-30</td>\n",
       "      <td>259</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         player_id       date attribute_date  day_diff\n",
       "8807078     2625.0 2009-03-01     2008-08-30       183\n",
       "8807400     2625.0 2009-03-07     2008-08-30       189\n",
       "8806770     2625.0 2009-03-14     2008-08-30       196\n",
       "8807414     2625.0 2009-03-21     2008-08-30       203\n",
       "8807092     2625.0 2009-04-03     2008-08-30       216\n",
       "8807428     2625.0 2009-04-11     2008-08-30       224\n",
       "8806784     2625.0 2009-04-18     2008-08-30       231\n",
       "8807442     2625.0 2009-04-25     2008-08-30       238\n",
       "8807274     2625.0 2009-05-09     2008-08-30       252\n",
       "8807288     2625.0 2009-05-16     2008-08-30       259"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_merged.loc[player_by_match_merged[\"player_id\"]==2625.0,\n",
    "                           [\"player_id\", \"date\", \"attribute_date\", \"day_diff\"]].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7fd72b834a90>"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAD4CAYAAADrRI2NAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAXy0lEQVR4nO3df4xV933m8fezUBPbUxu8NLMsgxZS0eyC3R9hliXNtrqEtNDEMq5US2Phmm5djdZ1s243UQNraa39A637w92t7bW7o+CG1NQTSt2CvKKNRX1lVTKmJr8wxtSTQskEh0kW2/W4Xmrcz/5xvjS30zsM99w7d+7h+7yk0Zz7Ped7z3NH8Nw755w7VxGBmZnl45/NdQAzM+suF7+ZWWZc/GZmmXHxm5llxsVvZpaZ+XMdYCaLFy+O5cuXl5r79ttvc+2113Y20CypUlaoVt4qZYVq5a1SVqhW3nazHjly5DsR8X1NV0ZET3+tWbMmynr22WdLz+22KmWNqFbeKmWNqFbeKmWNqFbedrMCL8Y0vepDPWZmmXHxm5llxsVvZpYZF7+ZWWZc/GZmmZmx+CU9LmlC0ktTxj8p6YSkY5J+vWF8u6SxtG5jw/gaSUfTuockqbMPxczMLsflvOL/HLCpcUDSemAz8IMRsRr4zTS+ChgCVqc5j0qal6Y9BgwDK9PXP7pPMzPrjhmLPyKeA85NGb4beCAizqdtJtL4ZmA0Is5HxElgDFgraQlwXUQ8n64v/Txwa6cehJmZXb6y79z9AeDHJO0A/h/w6Yj4C2ApcKhhu/E09m5anjrelKRhit8O6O/vp16vlwo5OTlZem63VSkrVCtvlbJCtfJWKStUK+9sZi1b/POBRcA64N8CeyR9AGh23D4uMd5URIwAIwCDg4NRq9VKhXx49z4e/PO3S81tx6kHPtHynHq9TtnHOReqlLdKWaFaeauUFaqVdzazlr2qZxx4Kr0z+DDw98DiNL6sYbsB4EwaH2gybmZmXVa2+P8Y+CiApB8ArgK+A+wHhiQtkLSC4iTu4Yh4DXhL0rp0Nc+dwL6205uZWctmPNQj6UmgBiyWNA7cDzwOPJ4u8fw7YGs6aXtM0h7gZeACcE9EvJfu6m6KK4SuBg6kLzMz67IZiz8ibp9m1R3TbL8D2NFk/EXgxpbSmZlZx/mdu2ZmmXHxm5llxsVvZpYZF7+ZWWZc/GZmmXHxm5llxsVvZpYZF7+ZWWZc/GZmmXHxm5llxsVvZpYZF7+ZWWZc/GZmmXHxm5llxsVvZpYZF7+ZWWZc/GZmmZmx+CU9Lmkifczi1HWflhSSFjeMbZc0JumEpI0N42skHU3rHkqfvWtmZl12Oa/4PwdsmjooaRnwE8DphrFVwBCwOs15VNK8tPoxYJjiA9hXNrtPMzObfTMWf0Q8B5xrsup/AL8KRMPYZmA0Is5HxElgDFgraQlwXUQ8nz6U/fPArW2nNzOzls34YevNSLoF+GZEfHXKEZulwKGG2+Np7N20PHV8uvsfpvjtgP7+fur1epmY9F8Nn7rpQqm57SiTd3JysvTjnAtVylulrFCtvFXKCtXKO5tZWy5+SdcA9wE/2Wx1k7G4xHhTETECjAAMDg5GrVZrNSYAD+/ex4NHSz23teXUllrLc+r1OmUf51yoUt4qZYVq5a1SVqhW3tnMWqYVvx9YAVx8tT8AfEnSWopX8ssath0AzqTxgSbjZmbWZS1fzhkRRyPi/RGxPCKWU5T6hyLiW8B+YEjSAkkrKE7iHo6I14C3JK1LV/PcCezr3MMwM7PLdTmXcz4JPA98UNK4pLum2zYijgF7gJeBPwHuiYj30uq7gc9SnPD9OnCgzexmZlbCjId6IuL2GdYvn3J7B7CjyXYvAje2mM/MzDrM79w1M8uMi9/MLDMufjOzzLj4zcwy4+I3M8uMi9/MLDMufjOzzLj4zcwy4+I3M8uMi9/MLDMufjOzzLj4zcwy4+I3M8uMi9/MLDMufjOzzLj4zcwy4+I3M8vM5Xz04uOSJiS91DD2G5JekfQ1SX8kaWHDuu2SxiSdkLSxYXyNpKNp3UPps3fNzKzLLucV/+eATVPGngFujIgfBP4S2A4gaRUwBKxOcx6VNC/NeQwYpvgA9pVN7tPMzLpgxuKPiOeAc1PGvhgRF9LNQ8BAWt4MjEbE+Yg4SfHB6mslLQGui4jnIyKAzwO3dupBmJnZ5Zvxw9Yvw88DX0jLSymeCC4aT2PvpuWp401JGqb47YD+/n7q9XqpYP1Xw6duujDzhh1WJu/k5GTpxzkXqpS3SlmhWnmrlBWqlXc2s7ZV/JLuAy4Auy8ONdksLjHeVESMACMAg4ODUavVSuV7ePc+Hjzaiee21pzaUmt5Tr1ep+zjnAtVylulrFCtvFXKCtXKO5tZS7eipK3AzcCGdPgGilfyyxo2GwDOpPGBJuNmZtZlpS7nlLQJ+AxwS0T8bcOq/cCQpAWSVlCcxD0cEa8Bb0lal67muRPY12Z2MzMrYcZX/JKeBGrAYknjwP0UV/EsAJ5JV2Ueioj/GBHHJO0BXqY4BHRPRLyX7upuiiuErgYOpC8zM+uyGYs/Im5vMrzzEtvvAHY0GX8RuLGldGZm1nF+566ZWWZc/GZmmXHxm5llxsVvZpYZF7+ZWWZc/GZmmXHxm5llxsVvZpYZF7+ZWWZc/GZmmXHxm5llxsVvZpYZF7+ZWWZc/GZmmXHxm5llxsVvZpYZF7+ZWWZmLH5Jj0uakPRSw9gNkp6R9Gr6vqhh3XZJY5JOSNrYML5G0tG07qH02btmZtZll/OK/3PApilj24CDEbESOJhuI2kVMASsTnMelTQvzXkMGKb4APaVTe7TzMy6YMbij4jngHNThjcDu9LyLuDWhvHRiDgfESeBMWCtpCXAdRHxfEQE8PmGOWZm1kUqeniGjaTlwNMRcWO6/UZELGxY/3pELJL0CHAoIp5I4zuBA8Ap4IGI+Fga/zHgMxFx8zT7G6b47YD+/v41o6OjpR7cxLk3OftOqaltuWnp9S3PmZycpK+vbxbSzI4q5a1SVqhW3iplhWrlbTfr+vXrj0TEYLN180vfa3PNjtvHJcabiogRYARgcHAwarVaqTAP797Hg0c7/RBndmpLreU59Xqdso9zLlQpb5WyQrXyVikrVCvvbGYte1XP2XT4hvR9Io2PA8sathsAzqTxgSbjZmbWZWWLfz+wNS1vBfY1jA9JWiBpBcVJ3MMR8RrwlqR16WqeOxvmmJlZF814HETSk0ANWCxpHLgfeADYI+ku4DRwG0BEHJO0B3gZuADcExHvpbu6m+IKoaspjvsf6OgjMTOzyzJj8UfE7dOs2jDN9juAHU3GXwRubCmdmZl1nN+5a2aWGRe/mVlmXPxmZplx8ZuZZcbFb2aWGRe/mVlmXPxmZplx8ZuZZcbFb2aWGRe/mVlmXPxmZplx8ZuZZcbFb2aWGRe/mVlmXPxmZplx8ZuZZcbFb2aWmbaKX9KvSDom6SVJT0p6n6QbJD0j6dX0fVHD9tsljUk6IWlj+/HNzKxVpYtf0lLgPwGDEXEjMA8YArYBByNiJXAw3UbSqrR+NbAJeFTSvPbim5lZq9o91DMfuFrSfOAa4AywGdiV1u8Cbk3Lm4HRiDgfESeBMWBtm/s3M7MWKSLKT5bupfhg9XeAL0bEFklvRMTChm1ej4hFkh4BDkXEE2l8J3AgIvY2ud9hYBigv79/zejoaKl8E+fe5Ow7paa25aal17c8Z3Jykr6+vllIMzuqlLdKWaFaeauUFaqVt92s69evPxIRg83WzS97p+nY/WZgBfAG8AeS7rjUlCZjTZ91ImIEGAEYHByMWq1WKuPDu/fx4NHSD7G0U1tqLc+p1+uUfZxzoUp5q5QVqpW3SlmhWnlnM2s7h3o+BpyMiG9HxLvAU8CPAmclLQFI3yfS9uPAsob5AxSHhszMrIvaKf7TwDpJ10gSsAE4DuwHtqZttgL70vJ+YEjSAkkrgJXA4Tb2b2ZmJZQ+DhIRL0jaC3wJuAB8meLwTB+wR9JdFE8Ot6Xtj0naA7yctr8nIt5rM7+ZmbWorQPgEXE/cP+U4fMUr/6bbb+D4mSwmZnNEb9z18wsMy5+M7PMuPjNzDLj4jczy4yL38wsMy5+M7PMuPjNzDLj4jczy4yL38wsMy5+M7PMuPjNzDLj4jczy4yL38wsMy5+M7PMuPjNzDLj4jczy4yL38wsM20Vv6SFkvZKekXScUkflnSDpGckvZq+L2rYfrukMUknJG1sP76ZmbWq3Vf8vw38SUT8a+CHKD5sfRtwMCJWAgfTbSStAoaA1cAm4FFJ89rcv5mZtah08Uu6DvhxYCdARPxdRLwBbAZ2pc12Abem5c3AaEScj4iTwBiwtuz+zcysHEVEuYnSDwMjwMsUr/aPAPcC34yIhQ3bvR4RiyQ9AhyKiCfS+E7gQETsbXLfw8AwQH9//5rR0dFSGSfOvcnZd0pNbctNS69vec7k5CR9fX2zkGZ2VClvlbJCtfJWKStUK2+7WdevX38kIgabrZtf+l6LuR8CPhkRL0j6bdJhnWmoyVjTZ52IGKF4UmFwcDBqtVqpgA/v3seDR9t5iOWc2lJreU69Xqfs45wLVcpbpaxQrbxVygrVyjubWds5xj8OjEfEC+n2XoongrOSlgCk7xMN2y9rmD8AnGlj/2ZmVkLp4o+IbwHfkPTBNLSB4rDPfmBrGtsK7EvL+4EhSQskrQBWAofL7t/MzMpp9zjIJ4Hdkq4C/gr4DxRPJnsk3QWcBm4DiIhjkvZQPDlcAO6JiPfa3L+ZmbWoreKPiK8AzU4ebJhm+x3Ajnb2aWZm7fE7d83MMuPiNzPLjIvfzCwzLn4zs8y4+M3MMuPiNzPLjIvfzCwzLn4zs8y4+M3MMuPiNzPLjIvfzCwzLn4zs8y4+M3MMuPiNzPLjIvfzCwzLn4zs8y4+M3MMtN28UuaJ+nLkp5Ot2+Q9IykV9P3RQ3bbpc0JumEpI3t7tvMzFrXiVf89wLHG25vAw5GxErgYLqNpFXAELAa2AQ8KmleB/ZvZmYtaKv4JQ0AnwA+2zC8GdiVlncBtzaMj0bE+Yg4CYwBa9vZv5mZtU4RUX6ytBf478D3Ap+OiJslvRERCxu2eT0iFkl6BDgUEU+k8Z3AgYjY2+R+h4FhgP7+/jWjo6Ol8k2ce5Oz75Sa2pabll7f8pzJyUn6+vpmIc3sqFLeKmWFauWtUlaoVt52s65fv/5IRAw2Wze/7J1KuhmYiIgjkmqXM6XJWNNnnYgYAUYABgcHo1a7nLv/px7evY8Hj5Z+iKWd2lJreU69Xqfs45wLVcpbpaxQrbxVygrVyjubWdtpxY8At0j6OPA+4DpJTwBnJS2JiNckLQEm0vbjwLKG+QPAmTb2b2ZmJZQ+xh8R2yNiICKWU5y0/bOIuAPYD2xNm20F9qXl/cCQpAWSVgArgcOlk5uZWSmzcRzkAWCPpLuA08BtABFxTNIe4GXgAnBPRLw3C/s3M7NL6EjxR0QdqKfl/wtsmGa7HcCOTuzTzMzK8Tt3zcwy4+I3M8uMi9/MLDMufjOzzLj4zcwy4+I3M8uMi9/MLDMufjOzzLj4zcwy4+I3M8uMi9/MLDMufjOzzLj4zcwy4+I3M8uMi9/MLDMufjOzzLj4zcwyU7r4JS2T9Kyk45KOSbo3jd8g6RlJr6bvixrmbJc0JumEpI2deABmZtaadl7xXwA+FRH/BlgH3CNpFbANOBgRK4GD6TZp3RCwGtgEPCppXjvhzcysdaWLPyJei4gvpeW3gOPAUmAzsCtttgu4NS1vBkYj4nxEnATGgLVl929mZuUoItq/E2k58BxwI3A6IhY2rHs9IhZJegQ4FBFPpPGdwIGI2Nvk/oaBYYD+/v41o6OjpXJNnHuTs++UmtqWm5Ze3/KcyclJ+vr6ZiHN7KhS3iplhWrlrVJWqFbedrOuX7/+SEQMNls3v/S9JpL6gD8Efjki/kbStJs2GWv6rBMRI8AIwODgYNRqtVLZHt69jwePtv0QW3ZqS63lOfV6nbKPcy5UKW+VskK18lYpK1Qr72xmbeuqHknfQ1H6uyPiqTR8VtKStH4JMJHGx4FlDdMHgDPt7N/MzFrXzlU9AnYCxyPitxpW7Qe2puWtwL6G8SFJCyStAFYCh8vu38zMymnnOMhHgJ8Fjkr6Shr7L8ADwB5JdwGngdsAIuKYpD3AyxRXBN0TEe+1sX8zMyuhdPFHxJ/T/Lg9wIZp5uwAdpTdp5mZtc/v3DUzy4yL38wsMy5+M7PMuPjNzDLj4jczy4yL38wsMy5+M7PMuPjNzDLj4jczy4yL38wsM93/m8UZWL7t/7Q851M3XeDnSsyb6tQDn2j7PszsyuZX/GZmmXHxm5llxsVvZpYZH+O/wpQ5v1DG1HMSPrdgVh1+xW9mlhkXv5lZZrpe/JI2STohaUzStm7v38wsd10tfknzgP8F/BSwCrhd0qpuZjAzy123T+6uBcYi4q8AJI0Cmyk+gN0qrFsnlcvo1JvjuqWX8/ok/pVBEdG9nUk/A2yKiF9It38W+HcR8UtTthsGhtPNDwInSu5yMfCdknO7rUpZoVp5q5QVqpW3SlmhWnnbzfqvIuL7mq3o9it+NRn7J888ETECjLS9M+nFiBhs9366oUpZoVp5q5QVqpW3SlmhWnlnM2u3T+6OA8sabg8AZ7qcwcwsa90u/r8AVkpaIekqYAjY3+UMZmZZ6+qhnoi4IOmXgD8F5gGPR8SxWdxl24eLuqhKWaFaeauUFaqVt0pZoVp5Zy1rV0/umpnZ3PM7d83MMuPiNzPLzBVZ/L3wZyEkLZP0rKTjko5JujeN3yDpGUmvpu+LGuZsT5lPSNrYML5G0tG07iFJzS6L7VTueZK+LOnpXs4raaGkvZJeST/jD/dq1rSfX0n/Dl6S9KSk9/VSXkmPS5qQ9FLDWMfySVog6Qtp/AVJyzuc9TfSv4WvSfojSQt7Iet0eRvWfVpSSFrc1bwRcUV9UZw0/jrwAeAq4KvAqjnIsQT4UFr+XuAvKf5Mxa8D29L4NuDX0vKqlHUBsCI9hnlp3WHgwxTvgzgA/NQs5v7PwO8DT6fbPZkX2AX8Qlq+CljYw1mXAieBq9PtPcDP9VJe4MeBDwEvNYx1LB/wi8DvpOUh4AsdzvqTwPy0/Gu9knW6vGl8GcWFLn8NLO5m3lkpj7n8Sj+YP224vR3Y3gO59gE/QfEu5CVpbAlwolnO9A/iw2mbVxrGbwf+9yxlHAAOAh/lu8Xfc3mB6yiKVFPGey5rut+lwDeAGyiupHs6FVVP5QWW84/LtGP5Lm6TludTvCNVnco6Zd1PA7t7Jet0eYG9wA8Bp/hu8Xcl75V4qOfif7KLxtPYnEm/ev0I8ALQHxGvAaTv70+bTZd7aVqeOj4b/ifwq8DfN4z1Yt4PAN8GfjcdlvqspGt7NCsR8U3gN4HTwGvAmxHxxV7N26CT+f5hTkRcAN4E/vks5f55ilfEPZtV0i3ANyPiq1NWdSXvlVj8l/VnIbpFUh/wh8AvR8TfXGrTJmNxifGOknQzMBERRy53SpOxbuWdT/Gr82MR8SPA2xSHIqYz1z/bRRR/jHAF8C+BayXdcakp0+TqlX/bZfJ162d9H3AB2D3Dfucsq6RrgPuA/9ps9TT77mjeK7H4e+bPQkj6HorS3x0RT6Xhs5KWpPVLgIk0Pl3u8bQ8dbzTPgLcIukUMAp8VNITPZp3HBiPiBfS7b0UTwS9mBXgY8DJiPh2RLwLPAX8aA/nvaiT+f5hjqT5wPXAuU6GlbQVuBnYEum4R49m/X6KFwFfTf/fBoAvSfoX3cp7JRZ/T/xZiHTGfSdwPCJ+q2HVfmBrWt5Kcez/4vhQOkO/AlgJHE6/Yr8laV26zzsb5nRMRGyPiIGIWE7xM/uziLijF/NGxLeAb0j6YBraQPGnvXsua3IaWCfpmrSfDcDxHs57USfzNd7Xz1D8++rkq+hNwGeAWyLib6c8hp7KGhFHI+L9EbE8/X8bp7gQ5Ftdy9vOCYte/QI+TnEVzdeB++Yow7+n+HXra8BX0tfHKY69HQReTd9vaJhzX8p8goarNYBB4KW07hHaPNF0GdlrfPfkbk/mBX4YeDH9fP8YWNSrWdN+/hvwStrX71FctdEzeYEnKc4/vEtRRHd1Mh/wPuAPgDGKq1M+0OGsYxTHuS/+X/udXsg6Xd4p60+RTu52K6//ZIOZWWauxEM9ZmZ2CS5+M7PMuPjNzDLj4jczy4yL38wsMy5+M7PMuPjNzDLz/wFzXjHSiqFFwwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "player_by_match_merged[\"day_diff\"].value_counts().hist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.00        1.0\n",
       "0.05        1.0\n",
       "0.25        2.0\n",
       "0.50        5.0\n",
       "0.75       25.0\n",
       "0.95     2315.1\n",
       "1.00    13996.0\n",
       "Name: day_diff, dtype: float64"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_merged[\"day_diff\"].value_counts().quantile([0, 0.05, 0.25, 0.5, 0.75, 0.95, 1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_merged = player_by_match_merged.drop([\"attribute_date\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    542004\n",
       "dtype: int64"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_merged.groupby([\"date\", \"match_id\", \"player_id\"]).size().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    542004\n",
       "dtype: int64"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_df.groupby([\"date\", \"match_id\", \"player_id\"]).size().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(542004, 3)\n",
      "(542004, 51)\n"
     ]
    }
   ],
   "source": [
    "print(player_by_match_df.shape)\n",
    "player_by_match_df = player_by_match_df.merge(player_by_match_merged, on=[\"date\", \"match_id\", \"player_id\"])\n",
    "print(player_by_match_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date                          0\n",
       "match_id                      0\n",
       "player_id                     0\n",
       "overall_rating                0\n",
       "potential                     0\n",
       "preferred_foot                0\n",
       "attacking_work_rate        8448\n",
       "defensive_work_rate           0\n",
       "crossing                      0\n",
       "finishing                     0\n",
       "heading_accuracy              0\n",
       "short_passing                 0\n",
       "volleys                    6722\n",
       "dribbling                     0\n",
       "curve                      6722\n",
       "free_kick_accuracy            0\n",
       "long_passing                  0\n",
       "ball_control                  0\n",
       "acceleration                  0\n",
       "sprint_speed                  0\n",
       "agility                    6722\n",
       "reactions                     0\n",
       "balance                    6722\n",
       "shot_power                    0\n",
       "jumping                    6722\n",
       "stamina                       0\n",
       "strength                      0\n",
       "long_shots                    0\n",
       "aggression                    0\n",
       "interceptions                 0\n",
       "positioning                   0\n",
       "vision                     6722\n",
       "penalties                     0\n",
       "marking                       0\n",
       "standing_tackle               0\n",
       "sliding_tackle             6722\n",
       "gk_diving                     0\n",
       "gk_handling                   0\n",
       "gk_kicking                    0\n",
       "gk_positioning                0\n",
       "gk_reflexes                   0\n",
       "attacking_work_rate_imp       0\n",
       "defensive_work_rate_imp       0\n",
       "volleys_imp                   0\n",
       "curve_imp                     0\n",
       "agility_imp                   0\n",
       "balance_imp                   0\n",
       "jumping_imp                   0\n",
       "vision_imp                    0\n",
       "sliding_tackle_imp            0\n",
       "day_diff                      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_df = player_by_match_df.drop(cols_to_imp + [\"attacking_work_rate\", \"defensive_work_rate\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "match_id  player_id\n",
       "888198    41023.0      1\n",
       "          39699.0      1\n",
       "          39487.0      1\n",
       "          39447.0      1\n",
       "          39401.0      1\n",
       "          39376.0      1\n",
       "          39345.0      1\n",
       "          39323.0      1\n",
       "          38717.0      1\n",
       "          38118.0      1\n",
       "          37545.0      1\n",
       "          33782.0      1\n",
       "          31938.0      1\n",
       "          41475.0      1\n",
       "2216672   480778.0     1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_df.groupby([\"match_id\", \"player_id\"]).size().sort_values().tail(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    541991\n",
       "2        13\n",
       "dtype: int64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "season_team_player_df.groupby([\"match_id\", \"player_id\"]).size().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    542004\n",
       "dtype: int64"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "player_by_match_df.groupby([\"match_id\", \"player_id\"]).size().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(542004, 42)\n",
      "(542017, 43)\n"
     ]
    }
   ],
   "source": [
    "print(player_by_match_df.shape)\n",
    "player_by_match_df = player_by_match_df.merge(season_team_player_df[[\"match_id\", \"team_id\", \"player_id\"]],\n",
    "                                              on=[\"match_id\", \"player_id\"])\n",
    "print(player_by_match_df.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "player_by_match_df.to_csv(dir+\"player_attributes_by_match.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv(dir+\"player_attributes_by_match.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'date', 'match_id', 'player_id', 'overall_rating',\n",
       "       'potential', 'preferred_foot', '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",
       "       'day_diff', 'team_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "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
}