connect_datasets.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connecting datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sklearn import preprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Premier League table data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "table_data = pd.read_csv('data/table_data.csv')\n",
    "table_data.columns = ['FINAL_POSITION','TEAM','FINAL_POINTS','SEASON']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>80</td>\n",
       "      <td>2000-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>70</td>\n",
       "      <td>2000-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>69</td>\n",
       "      <td>2000-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Leeds United</td>\n",
       "      <td>68</td>\n",
       "      <td>2000-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Ipswich Town</td>\n",
       "      <td>66</td>\n",
       "      <td>2000-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FINAL_POSITION               TEAM  FINAL_POINTS   SEASON\n",
       "0               1  Manchester United            80  2000-01\n",
       "1               2            Arsenal            70  2000-01\n",
       "2               3          Liverpool            69  2000-01\n",
       "3               4       Leeds United            68  2000-01\n",
       "4               5       Ipswich Town            66  2000-01"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>16</td>\n",
       "      <td>Burnley</td>\n",
       "      <td>40</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>17</td>\n",
       "      <td>Watford</td>\n",
       "      <td>40</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>337</th>\n",
       "      <td>18</td>\n",
       "      <td>Hull City</td>\n",
       "      <td>34</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>338</th>\n",
       "      <td>19</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>28</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>20</td>\n",
       "      <td>Sunderland</td>\n",
       "      <td>24</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FINAL_POSITION           TEAM  FINAL_POINTS   SEASON\n",
       "335              16        Burnley            40  2016-17\n",
       "336              17        Watford            40  2016-17\n",
       "337              18      Hull City            34  2016-17\n",
       "338              19  Middlesbrough            28  2016-17\n",
       "339              20     Sunderland            24  2016-17"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_data.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Wages data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "wages_data = pd.read_csv('data/combined_wages_scaled.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TEAM</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>SEASON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chelsea</td>\n",
       "      <td>50000000.0</td>\n",
       "      <td>1.83277</td>\n",
       "      <td>2000-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Manchester United</td>\n",
       "      <td>50000000.0</td>\n",
       "      <td>1.83277</td>\n",
       "      <td>2000-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Liverpool</td>\n",
       "      <td>49000000.0</td>\n",
       "      <td>1.74886</td>\n",
       "      <td>2000-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Leeds United</td>\n",
       "      <td>43000000.0</td>\n",
       "      <td>1.24540</td>\n",
       "      <td>2000-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arsenal</td>\n",
       "      <td>41000000.0</td>\n",
       "      <td>1.07758</td>\n",
       "      <td>2000-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                TEAM        WAGE  WAGE_SCALED   SEASON\n",
       "0            Chelsea  50000000.0      1.83277  2000-01\n",
       "1  Manchester United  50000000.0      1.83277  2000-01\n",
       "2          Liverpool  49000000.0      1.74886  2000-01\n",
       "3       Leeds United  43000000.0      1.24540  2000-01\n",
       "4            Arsenal  41000000.0      1.07758  2000-01"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wages_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TEAM</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>SEASON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>343</th>\n",
       "      <td>Watford</td>\n",
       "      <td>41000000.0</td>\n",
       "      <td>-0.826545</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>344</th>\n",
       "      <td>Bournemouth</td>\n",
       "      <td>34000000.0</td>\n",
       "      <td>-0.930447</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>345</th>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>34000000.0</td>\n",
       "      <td>-0.930447</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>346</th>\n",
       "      <td>Burnley</td>\n",
       "      <td>33000000.0</td>\n",
       "      <td>-0.945290</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347</th>\n",
       "      <td>Hull City</td>\n",
       "      <td>25000000.0</td>\n",
       "      <td>-1.064036</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              TEAM        WAGE  WAGE_SCALED   SEASON\n",
       "343        Watford  41000000.0    -0.826545  2016-17\n",
       "344    Bournemouth  34000000.0    -0.930447  2016-17\n",
       "345  Middlesbrough  34000000.0    -0.930447  2016-17\n",
       "346        Burnley  33000000.0    -0.945290  2016-17\n",
       "347      Hull City  25000000.0    -1.064036  2016-17"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wages_data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 348 entries, 0 to 347\n",
      "Data columns (total 4 columns):\n",
      "TEAM           348 non-null object\n",
      "WAGE           348 non-null float64\n",
      "WAGE_SCALED    348 non-null float64\n",
      "SEASON         348 non-null object\n",
      "dtypes: float64(2), object(2)\n",
      "memory usage: 11.0+ KB\n"
     ]
    }
   ],
   "source": [
    "wages_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 340 entries, 0 to 339\n",
      "Data columns (total 4 columns):\n",
      "FINAL_POSITION    340 non-null int64\n",
      "TEAM              340 non-null object\n",
      "FINAL_POINTS      340 non-null int64\n",
      "SEASON            340 non-null object\n",
      "dtypes: int64(2), object(2)\n",
      "memory usage: 10.7+ KB\n"
     ]
    }
   ],
   "source": [
    "table_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_data = table_data.merge(wages_data, how='left', on=['TEAM','SEASON'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 340 entries, 0 to 339\n",
      "Data columns (total 6 columns):\n",
      "FINAL_POSITION    340 non-null int64\n",
      "TEAM              340 non-null object\n",
      "FINAL_POINTS      340 non-null int64\n",
      "SEASON            340 non-null object\n",
      "WAGE              333 non-null float64\n",
      "WAGE_SCALED       333 non-null float64\n",
      "dtypes: float64(2), int64(2), object(2)\n",
      "memory usage: 18.6+ KB\n"
     ]
    }
   ],
   "source": [
    "total_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>80</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>50000000.0</td>\n",
       "      <td>1.832770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>70</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>41000000.0</td>\n",
       "      <td>1.077580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>69</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>49000000.0</td>\n",
       "      <td>1.748860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Leeds United</td>\n",
       "      <td>68</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>43000000.0</td>\n",
       "      <td>1.245400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Ipswich Town</td>\n",
       "      <td>66</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>18000000.0</td>\n",
       "      <td>-0.852348</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FINAL_POSITION               TEAM  FINAL_POINTS   SEASON        WAGE  \\\n",
       "0               1  Manchester United            80  2000-01  50000000.0   \n",
       "1               2            Arsenal            70  2000-01  41000000.0   \n",
       "2               3          Liverpool            69  2000-01  49000000.0   \n",
       "3               4       Leeds United            68  2000-01  43000000.0   \n",
       "4               5       Ipswich Town            66  2000-01  18000000.0   \n",
       "\n",
       "   WAGE_SCALED  \n",
       "0     1.832770  \n",
       "1     1.077580  \n",
       "2     1.748860  \n",
       "3     1.245400  \n",
       "4    -0.852348  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>16</td>\n",
       "      <td>Burnley</td>\n",
       "      <td>40</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>33000000.0</td>\n",
       "      <td>-0.945290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>17</td>\n",
       "      <td>Watford</td>\n",
       "      <td>40</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>41000000.0</td>\n",
       "      <td>-0.826545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>337</th>\n",
       "      <td>18</td>\n",
       "      <td>Hull City</td>\n",
       "      <td>34</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>25000000.0</td>\n",
       "      <td>-1.064036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>338</th>\n",
       "      <td>19</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>28</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>34000000.0</td>\n",
       "      <td>-0.930447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>20</td>\n",
       "      <td>Sunderland</td>\n",
       "      <td>24</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>68300000.0</td>\n",
       "      <td>-0.421325</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FINAL_POSITION           TEAM  FINAL_POINTS   SEASON        WAGE  \\\n",
       "335              16        Burnley            40  2016-17  33000000.0   \n",
       "336              17        Watford            40  2016-17  41000000.0   \n",
       "337              18      Hull City            34  2016-17  25000000.0   \n",
       "338              19  Middlesbrough            28  2016-17  34000000.0   \n",
       "339              20     Sunderland            24  2016-17  68300000.0   \n",
       "\n",
       "     WAGE_SCALED  \n",
       "335    -0.945290  \n",
       "336    -0.826545  \n",
       "337    -1.064036  \n",
       "338    -0.930447  \n",
       "339    -0.421325  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transfer spend data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "transfer_data = pd.read_csv('data/combined_transfer_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TEAM</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "      <th>SEASON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Blackburn Rovers</td>\n",
       "      <td>9813000.0</td>\n",
       "      <td>3.405195</td>\n",
       "      <td>1992-93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sheffield Wednesday</td>\n",
       "      <td>3060000.0</td>\n",
       "      <td>1.061846</td>\n",
       "      <td>1992-93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Oldham Athletic</td>\n",
       "      <td>2384000.0</td>\n",
       "      <td>0.827268</td>\n",
       "      <td>1992-93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wimbledon</td>\n",
       "      <td>2143000.0</td>\n",
       "      <td>0.743639</td>\n",
       "      <td>1992-93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Aston Villa</td>\n",
       "      <td>1956000.0</td>\n",
       "      <td>0.678749</td>\n",
       "      <td>1992-93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  TEAM  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED   SEASON\n",
       "0     Blackburn Rovers           9813000.0                   3.405195  1992-93\n",
       "1  Sheffield Wednesday           3060000.0                   1.061846  1992-93\n",
       "2      Oldham Athletic           2384000.0                   0.827268  1992-93\n",
       "3            Wimbledon           2143000.0                   0.743639  1992-93\n",
       "4          Aston Villa           1956000.0                   0.678749  1992-93"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transfer_data.columns = ['TEAM','NET_TRANSFER_SPEND','NET_TRANSFER_SPEND_SCALED','SEASON']\n",
    "transfer_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TEAM</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "      <th>SEASON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>Watford</td>\n",
       "      <td>11100000.0</td>\n",
       "      <td>0.236934</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>Swansea City</td>\n",
       "      <td>9600000.0</td>\n",
       "      <td>0.204916</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>Hull City</td>\n",
       "      <td>8800000.0</td>\n",
       "      <td>0.187839</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>504</th>\n",
       "      <td>Liverpool</td>\n",
       "      <td>-2700000.0</td>\n",
       "      <td>-0.057633</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>505</th>\n",
       "      <td>Southampton</td>\n",
       "      <td>-16150000.0</td>\n",
       "      <td>-0.344728</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             TEAM  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED   SEASON\n",
       "501       Watford          11100000.0                   0.236934  2016-17\n",
       "502  Swansea City           9600000.0                   0.204916  2016-17\n",
       "503     Hull City           8800000.0                   0.187839  2016-17\n",
       "504     Liverpool          -2700000.0                  -0.057633  2016-17\n",
       "505   Southampton         -16150000.0                  -0.344728  2016-17"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transfer_data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 506 entries, 0 to 505\n",
      "Data columns (total 4 columns):\n",
      "TEAM                         506 non-null object\n",
      "NET_TRANSFER_SPEND           506 non-null float64\n",
      "NET_TRANSFER_SPEND_SCALED    506 non-null float64\n",
      "SEASON                       506 non-null object\n",
      "dtypes: float64(2), object(2)\n",
      "memory usage: 15.9+ KB\n"
     ]
    }
   ],
   "source": [
    "transfer_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "total_data = total_data.merge(transfer_data, how='left', on=['TEAM','SEASON'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 340 entries, 0 to 339\n",
      "Data columns (total 8 columns):\n",
      "FINAL_POSITION               340 non-null int64\n",
      "TEAM                         340 non-null object\n",
      "FINAL_POINTS                 340 non-null int64\n",
      "SEASON                       340 non-null object\n",
      "WAGE                         333 non-null float64\n",
      "WAGE_SCALED                  333 non-null float64\n",
      "NET_TRANSFER_SPEND           340 non-null float64\n",
      "NET_TRANSFER_SPEND_SCALED    340 non-null float64\n",
      "dtypes: float64(4), int64(2), object(2)\n",
      "memory usage: 23.9+ KB\n"
     ]
    }
   ],
   "source": [
    "total_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>80</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>50000000.0</td>\n",
       "      <td>1.832770</td>\n",
       "      <td>1820000.0</td>\n",
       "      <td>0.130414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>70</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>41000000.0</td>\n",
       "      <td>1.077580</td>\n",
       "      <td>-5175000.0</td>\n",
       "      <td>-0.370820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>69</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>49000000.0</td>\n",
       "      <td>1.748860</td>\n",
       "      <td>17870000.0</td>\n",
       "      <td>1.280492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Leeds United</td>\n",
       "      <td>68</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>43000000.0</td>\n",
       "      <td>1.245400</td>\n",
       "      <td>41025000.0</td>\n",
       "      <td>2.939685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Ipswich Town</td>\n",
       "      <td>66</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>18000000.0</td>\n",
       "      <td>-0.852348</td>\n",
       "      <td>5100000.0</td>\n",
       "      <td>0.365445</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FINAL_POSITION               TEAM  FINAL_POINTS   SEASON        WAGE  \\\n",
       "0               1  Manchester United            80  2000-01  50000000.0   \n",
       "1               2            Arsenal            70  2000-01  41000000.0   \n",
       "2               3          Liverpool            69  2000-01  49000000.0   \n",
       "3               4       Leeds United            68  2000-01  43000000.0   \n",
       "4               5       Ipswich Town            66  2000-01  18000000.0   \n",
       "\n",
       "   WAGE_SCALED  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED  \n",
       "0     1.832770           1820000.0                   0.130414  \n",
       "1     1.077580          -5175000.0                  -0.370820  \n",
       "2     1.748860          17870000.0                   1.280492  \n",
       "3     1.245400          41025000.0                   2.939685  \n",
       "4    -0.852348           5100000.0                   0.365445  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>16</td>\n",
       "      <td>Burnley</td>\n",
       "      <td>40</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>33000000.0</td>\n",
       "      <td>-0.945290</td>\n",
       "      <td>44400000.0</td>\n",
       "      <td>0.947735</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>17</td>\n",
       "      <td>Watford</td>\n",
       "      <td>40</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>41000000.0</td>\n",
       "      <td>-0.826545</td>\n",
       "      <td>11100000.0</td>\n",
       "      <td>0.236934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>337</th>\n",
       "      <td>18</td>\n",
       "      <td>Hull City</td>\n",
       "      <td>34</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>25000000.0</td>\n",
       "      <td>-1.064036</td>\n",
       "      <td>8800000.0</td>\n",
       "      <td>0.187839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>338</th>\n",
       "      <td>19</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>28</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>34000000.0</td>\n",
       "      <td>-0.930447</td>\n",
       "      <td>39150000.0</td>\n",
       "      <td>0.835671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>20</td>\n",
       "      <td>Sunderland</td>\n",
       "      <td>24</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>68300000.0</td>\n",
       "      <td>-0.421325</td>\n",
       "      <td>15830000.0</td>\n",
       "      <td>0.337897</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FINAL_POSITION           TEAM  FINAL_POINTS   SEASON        WAGE  \\\n",
       "335              16        Burnley            40  2016-17  33000000.0   \n",
       "336              17        Watford            40  2016-17  41000000.0   \n",
       "337              18      Hull City            34  2016-17  25000000.0   \n",
       "338              19  Middlesbrough            28  2016-17  34000000.0   \n",
       "339              20     Sunderland            24  2016-17  68300000.0   \n",
       "\n",
       "     WAGE_SCALED  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED  \n",
       "335    -0.945290          44400000.0                   0.947735  \n",
       "336    -0.826545          11100000.0                   0.236934  \n",
       "337    -1.064036           8800000.0                   0.187839  \n",
       "338    -0.930447          39150000.0                   0.835671  \n",
       "339    -0.421325          15830000.0                   0.337897  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Squad info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "team_info = pd.read_csv('data/team_info.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TEAM</th>\n",
       "      <th>SQUAD_SIZE</th>\n",
       "      <th>AVG_AGE</th>\n",
       "      <th>NUM_FOREIGN_PLAYERS</th>\n",
       "      <th>TEAM_MARKET_VALUE</th>\n",
       "      <th>SEASON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Arsenal</td>\n",
       "      <td>33</td>\n",
       "      <td>25.0</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1999-00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Derby County</td>\n",
       "      <td>35</td>\n",
       "      <td>24.7</td>\n",
       "      <td>19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1999-00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Everton</td>\n",
       "      <td>38</td>\n",
       "      <td>24.4</td>\n",
       "      <td>17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1999-00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Liverpool</td>\n",
       "      <td>33</td>\n",
       "      <td>24.2</td>\n",
       "      <td>23</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1999-00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Tottenham Hotspur</td>\n",
       "      <td>34</td>\n",
       "      <td>24.9</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1999-00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                TEAM  SQUAD_SIZE  AVG_AGE  NUM_FOREIGN_PLAYERS  \\\n",
       "0            Arsenal          33     25.0                   18   \n",
       "1       Derby County          35     24.7                   19   \n",
       "2            Everton          38     24.4                   17   \n",
       "3          Liverpool          33     24.2                   23   \n",
       "4  Tottenham Hotspur          34     24.9                   16   \n",
       "\n",
       "   TEAM_MARKET_VALUE   SEASON  \n",
       "0                NaN  1999-00  \n",
       "1                NaN  1999-00  \n",
       "2                NaN  1999-00  \n",
       "3                NaN  1999-00  \n",
       "4                NaN  1999-00  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team_info.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TEAM</th>\n",
       "      <th>SQUAD_SIZE</th>\n",
       "      <th>AVG_AGE</th>\n",
       "      <th>NUM_FOREIGN_PLAYERS</th>\n",
       "      <th>TEAM_MARKET_VALUE</th>\n",
       "      <th>SEASON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>355</th>\n",
       "      <td>Bournemouth</td>\n",
       "      <td>26</td>\n",
       "      <td>26.3</td>\n",
       "      <td>10</td>\n",
       "      <td>106000000.0</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>356</th>\n",
       "      <td>Sunderland</td>\n",
       "      <td>28</td>\n",
       "      <td>27.1</td>\n",
       "      <td>20</td>\n",
       "      <td>102000000.0</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>25</td>\n",
       "      <td>27.9</td>\n",
       "      <td>15</td>\n",
       "      <td>99500000.0</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>West Bromwich Albion</td>\n",
       "      <td>20</td>\n",
       "      <td>28.6</td>\n",
       "      <td>14</td>\n",
       "      <td>95600000.0</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>Burnley</td>\n",
       "      <td>23</td>\n",
       "      <td>28.2</td>\n",
       "      <td>9</td>\n",
       "      <td>85000000.0</td>\n",
       "      <td>2016-17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     TEAM  SQUAD_SIZE  AVG_AGE  NUM_FOREIGN_PLAYERS  \\\n",
       "355           Bournemouth          26     26.3                   10   \n",
       "356            Sunderland          28     27.1                   20   \n",
       "357         Middlesbrough          25     27.9                   15   \n",
       "358  West Bromwich Albion          20     28.6                   14   \n",
       "359               Burnley          23     28.2                    9   \n",
       "\n",
       "     TEAM_MARKET_VALUE   SEASON  \n",
       "355        106000000.0  2016-17  \n",
       "356        102000000.0  2016-17  \n",
       "357         99500000.0  2016-17  \n",
       "358         95600000.0  2016-17  \n",
       "359         85000000.0  2016-17  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team_info.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 340 entries, 0 to 339\n",
      "Data columns (total 8 columns):\n",
      "FINAL_POSITION               340 non-null int64\n",
      "TEAM                         340 non-null object\n",
      "FINAL_POINTS                 340 non-null int64\n",
      "SEASON                       340 non-null object\n",
      "WAGE                         333 non-null float64\n",
      "WAGE_SCALED                  333 non-null float64\n",
      "NET_TRANSFER_SPEND           340 non-null float64\n",
      "NET_TRANSFER_SPEND_SCALED    340 non-null float64\n",
      "dtypes: float64(4), int64(2), object(2)\n",
      "memory usage: 23.9+ KB\n"
     ]
    }
   ],
   "source": [
    "total_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "total_data = total_data.merge(team_info, how='left', on=['TEAM','SEASON'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 340 entries, 0 to 339\n",
      "Data columns (total 12 columns):\n",
      "FINAL_POSITION               340 non-null int64\n",
      "TEAM                         340 non-null object\n",
      "FINAL_POINTS                 340 non-null int64\n",
      "SEASON                       340 non-null object\n",
      "WAGE                         333 non-null float64\n",
      "WAGE_SCALED                  333 non-null float64\n",
      "NET_TRANSFER_SPEND           340 non-null float64\n",
      "NET_TRANSFER_SPEND_SCALED    340 non-null float64\n",
      "SQUAD_SIZE                   340 non-null int64\n",
      "AVG_AGE                      340 non-null float64\n",
      "NUM_FOREIGN_PLAYERS          340 non-null int64\n",
      "TEAM_MARKET_VALUE            241 non-null float64\n",
      "dtypes: float64(6), int64(4), object(2)\n",
      "memory usage: 34.5+ KB\n"
     ]
    }
   ],
   "source": [
    "total_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Estimated market values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TEAM</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>EST_TEAM_MARKET_VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>Bournemouth</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>336</th>\n",
       "      <td>Hull City</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>337</th>\n",
       "      <td>Swansea City</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>338</th>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>Sunderland</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              TEAM   SEASON  EST_TEAM_MARKET_VALUE\n",
       "335    Bournemouth  2016-17                    NaN\n",
       "336      Hull City  2016-17                    NaN\n",
       "337   Swansea City  2016-17                    NaN\n",
       "338  Middlesbrough  2016-17                    NaN\n",
       "339     Sunderland  2016-17                    NaN"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "estimated_team_values = pd.read_csv('data/estimated_team_values.csv', index_col=0)\n",
    "estimated_team_values.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The NaN's are there because I actually have the transfermarkt data for these observations.  \n",
    "I'm also going to create an 'is estimated' column so that I know which are the values I generated."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TEAM</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>EST_TEAM_MARKET_VALUE</th>\n",
       "      <th>IS_ESTIMATED_MARKET_VAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ipswich Town</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>19354000.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Charlton Athletic</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>9876000.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Norwich City</td>\n",
       "      <td>2004-05</td>\n",
       "      <td>8493000.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Leicester City</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>-5679000.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>West Bromwich Albion</td>\n",
       "      <td>2002-03</td>\n",
       "      <td>-22552000.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    TEAM   SEASON  EST_TEAM_MARKET_VALUE  \\\n",
       "4           Ipswich Town  2000-01             19354000.0   \n",
       "8      Charlton Athletic  2000-01              9876000.0   \n",
       "98          Norwich City  2004-05              8493000.0   \n",
       "12        Leicester City  2000-01             -5679000.0   \n",
       "58  West Bromwich Albion  2002-03            -22552000.0   \n",
       "\n",
       "    IS_ESTIMATED_MARKET_VAL  \n",
       "4                         1  \n",
       "8                         1  \n",
       "98                        1  \n",
       "12                        1  \n",
       "58                        1  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "estimated_team_values.dropna(axis=0, how='any', inplace=True)\n",
    "estimated_team_values['IS_ESTIMATED_MARKET_VAL'] = 1\n",
    "estimated_team_values.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "def negative_to_zero(input_val):\n",
    "    if input_val < 0:\n",
    "        return 0\n",
    "    else:\n",
    "        return input_val\n",
    "\n",
    "estimated_team_values['EST_TEAM_MARKET_VALUE'] = estimated_team_values['EST_TEAM_MARKET_VALUE'].map(negative_to_zero)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TEAM</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>EST_TEAM_MARKET_VALUE</th>\n",
       "      <th>IS_ESTIMATED_MARKET_VAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Chelsea</td>\n",
       "      <td>2004-05</td>\n",
       "      <td>360542000.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Bolton Wanderers</td>\n",
       "      <td>2001-02</td>\n",
       "      <td>49386000.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Arsenal</td>\n",
       "      <td>2004-05</td>\n",
       "      <td>181973000.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Birmingham City</td>\n",
       "      <td>2004-05</td>\n",
       "      <td>77784000.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Newcastle United</td>\n",
       "      <td>2003-04</td>\n",
       "      <td>65333000.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                TEAM   SEASON  EST_TEAM_MARKET_VALUE  IS_ESTIMATED_MARKET_VAL\n",
       "80           Chelsea  2004-05            360542000.0                        1\n",
       "35  Bolton Wanderers  2001-02             49386000.0                        1\n",
       "81           Arsenal  2004-05            181973000.0                        1\n",
       "91   Birmingham City  2004-05             77784000.0                        1\n",
       "64  Newcastle United  2003-04             65333000.0                        1"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "estimated_team_values.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "total_data = total_data.merge(estimated_team_values, how='left', on=['TEAM','SEASON'])\n",
    "# total_data2 = total_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "      <th>SQUAD_SIZE</th>\n",
       "      <th>AVG_AGE</th>\n",
       "      <th>NUM_FOREIGN_PLAYERS</th>\n",
       "      <th>TEAM_MARKET_VALUE</th>\n",
       "      <th>EST_TEAM_MARKET_VALUE</th>\n",
       "      <th>IS_ESTIMATED_MARKET_VAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>80</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>50000000.0</td>\n",
       "      <td>1.832770</td>\n",
       "      <td>1820000.0</td>\n",
       "      <td>0.130414</td>\n",
       "      <td>34</td>\n",
       "      <td>24.9</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>170072000.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>70</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>41000000.0</td>\n",
       "      <td>1.077580</td>\n",
       "      <td>-5175000.0</td>\n",
       "      <td>-0.370820</td>\n",
       "      <td>33</td>\n",
       "      <td>25.3</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>109338000.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>69</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>49000000.0</td>\n",
       "      <td>1.748860</td>\n",
       "      <td>17870000.0</td>\n",
       "      <td>1.280492</td>\n",
       "      <td>30</td>\n",
       "      <td>24.5</td>\n",
       "      <td>21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>139748000.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Leeds United</td>\n",
       "      <td>68</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>43000000.0</td>\n",
       "      <td>1.245400</td>\n",
       "      <td>41025000.0</td>\n",
       "      <td>2.939685</td>\n",
       "      <td>36</td>\n",
       "      <td>22.5</td>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>104249000.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Ipswich Town</td>\n",
       "      <td>66</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>18000000.0</td>\n",
       "      <td>-0.852348</td>\n",
       "      <td>5100000.0</td>\n",
       "      <td>0.365445</td>\n",
       "      <td>27</td>\n",
       "      <td>24.8</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19354000.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FINAL_POSITION               TEAM  FINAL_POINTS   SEASON        WAGE  \\\n",
       "0               1  Manchester United            80  2000-01  50000000.0   \n",
       "1               2            Arsenal            70  2000-01  41000000.0   \n",
       "2               3          Liverpool            69  2000-01  49000000.0   \n",
       "3               4       Leeds United            68  2000-01  43000000.0   \n",
       "4               5       Ipswich Town            66  2000-01  18000000.0   \n",
       "\n",
       "   WAGE_SCALED  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED  SQUAD_SIZE  \\\n",
       "0     1.832770           1820000.0                   0.130414          34   \n",
       "1     1.077580          -5175000.0                  -0.370820          33   \n",
       "2     1.748860          17870000.0                   1.280492          30   \n",
       "3     1.245400          41025000.0                   2.939685          36   \n",
       "4    -0.852348           5100000.0                   0.365445          27   \n",
       "\n",
       "   AVG_AGE  NUM_FOREIGN_PLAYERS  TEAM_MARKET_VALUE  EST_TEAM_MARKET_VALUE  \\\n",
       "0     24.9                   18                NaN            170072000.0   \n",
       "1     25.3                   22                NaN            109338000.0   \n",
       "2     24.5                   21                NaN            139748000.0   \n",
       "3     22.5                   22                NaN            104249000.0   \n",
       "4     24.8                   11                NaN             19354000.0   \n",
       "\n",
       "   IS_ESTIMATED_MARKET_VAL  \n",
       "0                      1.0  \n",
       "1                      1.0  \n",
       "2                      1.0  \n",
       "3                      1.0  \n",
       "4                      1.0  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_data['TEAM_MARKET_VALUE'] = total_data['TEAM_MARKET_VALUE'].fillna(value=total_data['EST_TEAM_MARKET_VALUE'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "      <th>SQUAD_SIZE</th>\n",
       "      <th>AVG_AGE</th>\n",
       "      <th>NUM_FOREIGN_PLAYERS</th>\n",
       "      <th>TEAM_MARKET_VALUE</th>\n",
       "      <th>EST_TEAM_MARKET_VALUE</th>\n",
       "      <th>IS_ESTIMATED_MARKET_VAL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>15</td>\n",
       "      <td>Aston Villa</td>\n",
       "      <td>41</td>\n",
       "      <td>2012-13</td>\n",
       "      <td>72000000.0</td>\n",
       "      <td>-0.162781</td>\n",
       "      <td>24630000.0</td>\n",
       "      <td>0.928159</td>\n",
       "      <td>31</td>\n",
       "      <td>24.1</td>\n",
       "      <td>19</td>\n",
       "      <td>101030000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>5</td>\n",
       "      <td>Leeds United</td>\n",
       "      <td>66</td>\n",
       "      <td>2001-02</td>\n",
       "      <td>54000000.0</td>\n",
       "      <td>1.139156</td>\n",
       "      <td>41725000.0</td>\n",
       "      <td>2.799861</td>\n",
       "      <td>26</td>\n",
       "      <td>24.1</td>\n",
       "      <td>13</td>\n",
       "      <td>92629000.0</td>\n",
       "      <td>92629000.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>10</td>\n",
       "      <td>Blackburn Rovers</td>\n",
       "      <td>50</td>\n",
       "      <td>2009-10</td>\n",
       "      <td>47000000.0</td>\n",
       "      <td>-0.417002</td>\n",
       "      <td>-23370000.0</td>\n",
       "      <td>-0.635856</td>\n",
       "      <td>35</td>\n",
       "      <td>25.3</td>\n",
       "      <td>29</td>\n",
       "      <td>90300000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>15</td>\n",
       "      <td>Everton</td>\n",
       "      <td>43</td>\n",
       "      <td>2001-02</td>\n",
       "      <td>29000000.0</td>\n",
       "      <td>-0.530617</td>\n",
       "      <td>5575000.0</td>\n",
       "      <td>0.374098</td>\n",
       "      <td>31</td>\n",
       "      <td>26.3</td>\n",
       "      <td>19</td>\n",
       "      <td>110502000.0</td>\n",
       "      <td>110502000.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320</th>\n",
       "      <td>1</td>\n",
       "      <td>Chelsea</td>\n",
       "      <td>93</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>218000000.0</td>\n",
       "      <td>1.800705</td>\n",
       "      <td>33900000.0</td>\n",
       "      <td>0.723608</td>\n",
       "      <td>25</td>\n",
       "      <td>26.6</td>\n",
       "      <td>20</td>\n",
       "      <td>515000000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FINAL_POSITION              TEAM  FINAL_POINTS   SEASON         WAGE  \\\n",
       "254              15       Aston Villa            41  2012-13   72000000.0   \n",
       "24                5      Leeds United            66  2001-02   54000000.0   \n",
       "189              10  Blackburn Rovers            50  2009-10   47000000.0   \n",
       "34               15           Everton            43  2001-02   29000000.0   \n",
       "320               1           Chelsea            93  2016-17  218000000.0   \n",
       "\n",
       "     WAGE_SCALED  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED  SQUAD_SIZE  \\\n",
       "254    -0.162781          24630000.0                   0.928159          31   \n",
       "24      1.139156          41725000.0                   2.799861          26   \n",
       "189    -0.417002         -23370000.0                  -0.635856          35   \n",
       "34     -0.530617           5575000.0                   0.374098          31   \n",
       "320     1.800705          33900000.0                   0.723608          25   \n",
       "\n",
       "     AVG_AGE  NUM_FOREIGN_PLAYERS  TEAM_MARKET_VALUE  EST_TEAM_MARKET_VALUE  \\\n",
       "254     24.1                   19        101030000.0                    NaN   \n",
       "24      24.1                   13         92629000.0             92629000.0   \n",
       "189     25.3                   29         90300000.0                    NaN   \n",
       "34      26.3                   19        110502000.0            110502000.0   \n",
       "320     26.6                   20        515000000.0                    NaN   \n",
       "\n",
       "     IS_ESTIMATED_MARKET_VAL  \n",
       "254                      NaN  \n",
       "24                       1.0  \n",
       "189                      NaN  \n",
       "34                       1.0  \n",
       "320                      NaN  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_data['IS_ESTIMATED_MARKET_VAL'].fillna(value=0, axis=0, inplace=True)\n",
    "total_data.drop('EST_TEAM_MARKET_VALUE', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_data['TEAM_MARKET_VALUE'] = total_data['TEAM_MARKET_VALUE'].fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Scale data..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_data['TEAM_MARKET_VALUE_SCALED'] = preprocessing.scale(total_data['TEAM_MARKET_VALUE'], with_mean=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "      <th>SQUAD_SIZE</th>\n",
       "      <th>AVG_AGE</th>\n",
       "      <th>NUM_FOREIGN_PLAYERS</th>\n",
       "      <th>TEAM_MARKET_VALUE</th>\n",
       "      <th>IS_ESTIMATED_MARKET_VAL</th>\n",
       "      <th>TEAM_MARKET_VALUE_SCALED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>16</td>\n",
       "      <td>Manchester City</td>\n",
       "      <td>41</td>\n",
       "      <td>2003-04</td>\n",
       "      <td>38000000.0</td>\n",
       "      <td>-0.114428</td>\n",
       "      <td>8937000.0</td>\n",
       "      <td>0.236941</td>\n",
       "      <td>33</td>\n",
       "      <td>25.7</td>\n",
       "      <td>24</td>\n",
       "      <td>135209000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.098447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>20</td>\n",
       "      <td>Wolverhampton Wanderers</td>\n",
       "      <td>33</td>\n",
       "      <td>2003-04</td>\n",
       "      <td>19000000.0</td>\n",
       "      <td>-0.934852</td>\n",
       "      <td>8725000.0</td>\n",
       "      <td>0.231321</td>\n",
       "      <td>34</td>\n",
       "      <td>26.2</td>\n",
       "      <td>22</td>\n",
       "      <td>44591000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.362260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>331</th>\n",
       "      <td>12</td>\n",
       "      <td>Leicester City</td>\n",
       "      <td>44</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>66000000.0</td>\n",
       "      <td>-0.455464</td>\n",
       "      <td>26050000.0</td>\n",
       "      <td>0.556047</td>\n",
       "      <td>25</td>\n",
       "      <td>27.7</td>\n",
       "      <td>18</td>\n",
       "      <td>205300000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.667871</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FINAL_POSITION                     TEAM  FINAL_POINTS   SEASON  \\\n",
       "75               16          Manchester City            41  2003-04   \n",
       "79               20  Wolverhampton Wanderers            33  2003-04   \n",
       "331              12           Leicester City            44  2016-17   \n",
       "\n",
       "           WAGE  WAGE_SCALED  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED  \\\n",
       "75   38000000.0    -0.114428           8937000.0                   0.236941   \n",
       "79   19000000.0    -0.934852           8725000.0                   0.231321   \n",
       "331  66000000.0    -0.455464          26050000.0                   0.556047   \n",
       "\n",
       "     SQUAD_SIZE  AVG_AGE  NUM_FOREIGN_PLAYERS  TEAM_MARKET_VALUE  \\\n",
       "75           33     25.7                   24        135209000.0   \n",
       "79           34     26.2                   22         44591000.0   \n",
       "331          25     27.7                   18        205300000.0   \n",
       "\n",
       "     IS_ESTIMATED_MARKET_VAL  TEAM_MARKET_VALUE_SCALED  \n",
       "75                       1.0                  1.098447  \n",
       "79                       1.0                  0.362260  \n",
       "331                      0.0                  1.667871  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Team continuity data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TEAM</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>AVG_TIME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Birmingham City</td>\n",
       "      <td>2005-06</td>\n",
       "      <td>2.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>Swansea City</td>\n",
       "      <td>2015-16</td>\n",
       "      <td>2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>Blackburn Rovers</td>\n",
       "      <td>2010-11</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>QPR</td>\n",
       "      <td>2011-12</td>\n",
       "      <td>2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>Arsenal</td>\n",
       "      <td>2013-14</td>\n",
       "      <td>3.7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 TEAM   SEASON  AVG_TIME\n",
       "122   Birmingham City  2005-06       2.2\n",
       "335      Swansea City  2015-16       2.9\n",
       "223  Blackburn Rovers  2010-11       2.5\n",
       "252               QPR  2011-12       2.1\n",
       "280           Arsenal  2013-14       3.7"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "continuity = pd.read_csv('data/team_continuity.csv')\n",
    "continuity.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_data = total_data.merge(continuity, how='left', on=['TEAM','SEASON'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 340 entries, 0 to 339\n",
      "Data columns (total 15 columns):\n",
      "FINAL_POSITION               340 non-null int64\n",
      "TEAM                         340 non-null object\n",
      "FINAL_POINTS                 340 non-null int64\n",
      "SEASON                       340 non-null object\n",
      "WAGE                         333 non-null float64\n",
      "WAGE_SCALED                  333 non-null float64\n",
      "NET_TRANSFER_SPEND           340 non-null float64\n",
      "NET_TRANSFER_SPEND_SCALED    340 non-null float64\n",
      "SQUAD_SIZE                   340 non-null int64\n",
      "AVG_AGE                      340 non-null float64\n",
      "NUM_FOREIGN_PLAYERS          340 non-null int64\n",
      "TEAM_MARKET_VALUE            340 non-null float64\n",
      "IS_ESTIMATED_MARKET_VAL      340 non-null float64\n",
      "TEAM_MARKET_VALUE_SCALED     340 non-null float64\n",
      "AVG_TIME                     340 non-null float64\n",
      "dtypes: float64(9), int64(4), object(2)\n",
      "memory usage: 42.5+ KB\n"
     ]
    }
   ],
   "source": [
    "total_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of transfers in/out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_transfers = pd.read_csv('data/num_transfers.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_data = total_data.merge(num_transfers, how='left', on=['TEAM','SEASON'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "      <th>SQUAD_SIZE</th>\n",
       "      <th>AVG_AGE</th>\n",
       "      <th>NUM_FOREIGN_PLAYERS</th>\n",
       "      <th>TEAM_MARKET_VALUE</th>\n",
       "      <th>IS_ESTIMATED_MARKET_VAL</th>\n",
       "      <th>TEAM_MARKET_VALUE_SCALED</th>\n",
       "      <th>AVG_TIME</th>\n",
       "      <th>NUM_TRANSFERS_IN</th>\n",
       "      <th>NUM_TRANSFERS_OUT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>304</th>\n",
       "      <td>5</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>66</td>\n",
       "      <td>2015-16</td>\n",
       "      <td>203000000.0</td>\n",
       "      <td>1.819297</td>\n",
       "      <td>44650000.0</td>\n",
       "      <td>1.237479</td>\n",
       "      <td>44</td>\n",
       "      <td>22.5</td>\n",
       "      <td>23</td>\n",
       "      <td>374150000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.039620</td>\n",
       "      <td>2.9</td>\n",
       "      <td>6</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>11</td>\n",
       "      <td>Middlesbrough</td>\n",
       "      <td>49</td>\n",
       "      <td>2002-03</td>\n",
       "      <td>29000000.0</td>\n",
       "      <td>-0.618545</td>\n",
       "      <td>37410000.0</td>\n",
       "      <td>1.506136</td>\n",
       "      <td>32</td>\n",
       "      <td>24.0</td>\n",
       "      <td>15</td>\n",
       "      <td>90525000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.735431</td>\n",
       "      <td>2.2</td>\n",
       "      <td>8</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>1</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>80</td>\n",
       "      <td>2010-11</td>\n",
       "      <td>153000000.0</td>\n",
       "      <td>1.677800</td>\n",
       "      <td>12000000.0</td>\n",
       "      <td>0.307158</td>\n",
       "      <td>43</td>\n",
       "      <td>24.1</td>\n",
       "      <td>28</td>\n",
       "      <td>384150000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.120860</td>\n",
       "      <td>5.1</td>\n",
       "      <td>4</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FINAL_POSITION               TEAM  FINAL_POINTS   SEASON         WAGE  \\\n",
       "304               5  Manchester United            66  2015-16  203000000.0   \n",
       "50               11      Middlesbrough            49  2002-03   29000000.0   \n",
       "200               1  Manchester United            80  2010-11  153000000.0   \n",
       "\n",
       "     WAGE_SCALED  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED  SQUAD_SIZE  \\\n",
       "304     1.819297          44650000.0                   1.237479          44   \n",
       "50     -0.618545          37410000.0                   1.506136          32   \n",
       "200     1.677800          12000000.0                   0.307158          43   \n",
       "\n",
       "     AVG_AGE  NUM_FOREIGN_PLAYERS  TEAM_MARKET_VALUE  IS_ESTIMATED_MARKET_VAL  \\\n",
       "304     22.5                   23        374150000.0                      0.0   \n",
       "50      24.0                   15         90525000.0                      1.0   \n",
       "200     24.1                   28        384150000.0                      0.0   \n",
       "\n",
       "     TEAM_MARKET_VALUE_SCALED  AVG_TIME  NUM_TRANSFERS_IN  NUM_TRANSFERS_OUT  \n",
       "304                  3.039620       2.9                 6                 14  \n",
       "50                   0.735431       2.2                 8                 14  \n",
       "200                  3.120860       5.1                 4                 11  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Club size via stadium capacity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "def change_to_float(input_val):\n",
    "    val = str(input_val)\n",
    "    val = val.replace(',','')\n",
    "    val = float(val)\n",
    "    return val\n",
    "    \n",
    "stadium = pd.read_csv('data/stadium_capacity.csv')\n",
    "stadium = stadium.dropna(how='any', axis=1)\n",
    "stadium['STADIUM_CAPACITY'] = stadium['STADIUM_CAPACITY'].map(change_to_float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "stadium['STADIUM_CAPACITY_SCALED'] = preprocessing.scale(stadium['STADIUM_CAPACITY'], with_mean=False)\n",
    "stadium.drop('STADIUM_CAPACITY', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "      <th>SQUAD_SIZE</th>\n",
       "      <th>AVG_AGE</th>\n",
       "      <th>NUM_FOREIGN_PLAYERS</th>\n",
       "      <th>TEAM_MARKET_VALUE</th>\n",
       "      <th>IS_ESTIMATED_MARKET_VAL</th>\n",
       "      <th>TEAM_MARKET_VALUE_SCALED</th>\n",
       "      <th>AVG_TIME</th>\n",
       "      <th>NUM_TRANSFERS_IN</th>\n",
       "      <th>NUM_TRANSFERS_OUT</th>\n",
       "      <th>STADIUM_CAPACITY_SCALED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>80</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>50000000.0</td>\n",
       "      <td>1.832770</td>\n",
       "      <td>1820000.0</td>\n",
       "      <td>0.130414</td>\n",
       "      <td>34</td>\n",
       "      <td>24.9</td>\n",
       "      <td>18</td>\n",
       "      <td>170072000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.381676</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>6.350441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>70</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>41000000.0</td>\n",
       "      <td>1.077580</td>\n",
       "      <td>-5175000.0</td>\n",
       "      <td>-0.370820</td>\n",
       "      <td>33</td>\n",
       "      <td>25.3</td>\n",
       "      <td>22</td>\n",
       "      <td>109338000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.888269</td>\n",
       "      <td>4.1</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>3.175220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>69</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>49000000.0</td>\n",
       "      <td>1.748860</td>\n",
       "      <td>17870000.0</td>\n",
       "      <td>1.280492</td>\n",
       "      <td>30</td>\n",
       "      <td>24.5</td>\n",
       "      <td>21</td>\n",
       "      <td>139748000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.135322</td>\n",
       "      <td>2.4</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>4.595714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Leeds United</td>\n",
       "      <td>68</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>43000000.0</td>\n",
       "      <td>1.245400</td>\n",
       "      <td>41025000.0</td>\n",
       "      <td>2.939685</td>\n",
       "      <td>36</td>\n",
       "      <td>22.5</td>\n",
       "      <td>22</td>\n",
       "      <td>104249000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.846926</td>\n",
       "      <td>2.9</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>3.091662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Ipswich Town</td>\n",
       "      <td>66</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>18000000.0</td>\n",
       "      <td>-0.852348</td>\n",
       "      <td>5100000.0</td>\n",
       "      <td>0.365445</td>\n",
       "      <td>27</td>\n",
       "      <td>24.8</td>\n",
       "      <td>11</td>\n",
       "      <td>19354000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.157233</td>\n",
       "      <td>2.3</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>2.506753</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FINAL_POSITION               TEAM  FINAL_POINTS   SEASON        WAGE  \\\n",
       "0               1  Manchester United            80  2000-01  50000000.0   \n",
       "1               2            Arsenal            70  2000-01  41000000.0   \n",
       "2               3          Liverpool            69  2000-01  49000000.0   \n",
       "3               4       Leeds United            68  2000-01  43000000.0   \n",
       "4               5       Ipswich Town            66  2000-01  18000000.0   \n",
       "\n",
       "   WAGE_SCALED  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED  SQUAD_SIZE  \\\n",
       "0     1.832770           1820000.0                   0.130414          34   \n",
       "1     1.077580          -5175000.0                  -0.370820          33   \n",
       "2     1.748860          17870000.0                   1.280492          30   \n",
       "3     1.245400          41025000.0                   2.939685          36   \n",
       "4    -0.852348           5100000.0                   0.365445          27   \n",
       "\n",
       "   AVG_AGE  NUM_FOREIGN_PLAYERS  TEAM_MARKET_VALUE  IS_ESTIMATED_MARKET_VAL  \\\n",
       "0     24.9                   18        170072000.0                      1.0   \n",
       "1     25.3                   22        109338000.0                      1.0   \n",
       "2     24.5                   21        139748000.0                      1.0   \n",
       "3     22.5                   22        104249000.0                      1.0   \n",
       "4     24.8                   11         19354000.0                      1.0   \n",
       "\n",
       "   TEAM_MARKET_VALUE_SCALED  AVG_TIME  NUM_TRANSFERS_IN  NUM_TRANSFERS_OUT  \\\n",
       "0                  1.381676       4.5                 1                  8   \n",
       "1                  0.888269       4.1                 8                 12   \n",
       "2                  1.135322       2.4                10                 10   \n",
       "3                  0.846926       2.9                 8                  8   \n",
       "4                  0.157233       2.3                 6                  7   \n",
       "\n",
       "   STADIUM_CAPACITY_SCALED  \n",
       "0                 6.350441  \n",
       "1                 3.175220  \n",
       "2                 4.595714  \n",
       "3                 3.091662  \n",
       "4                 2.506753  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data = total_data.merge(stadium, how='left', on=['TEAM','SEASON'])\n",
    "total_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Number of years in league"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "years_in_league = pd.read_csv('data/years_in_league.csv')\n",
    "years_in_league = pd.melt(years_in_league, id_vars=[\"TEAM\"], var_name=\"SEASON\", value_name=\"YEARS_IN_LEAGUE\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "      <th>SQUAD_SIZE</th>\n",
       "      <th>AVG_AGE</th>\n",
       "      <th>NUM_FOREIGN_PLAYERS</th>\n",
       "      <th>TEAM_MARKET_VALUE</th>\n",
       "      <th>IS_ESTIMATED_MARKET_VAL</th>\n",
       "      <th>TEAM_MARKET_VALUE_SCALED</th>\n",
       "      <th>AVG_TIME</th>\n",
       "      <th>NUM_TRANSFERS_IN</th>\n",
       "      <th>NUM_TRANSFERS_OUT</th>\n",
       "      <th>STADIUM_CAPACITY_SCALED</th>\n",
       "      <th>YEARS_IN_LEAGUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Manchester United</td>\n",
       "      <td>80</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>50000000.0</td>\n",
       "      <td>1.832770</td>\n",
       "      <td>1820000.0</td>\n",
       "      <td>0.130414</td>\n",
       "      <td>34</td>\n",
       "      <td>24.9</td>\n",
       "      <td>18</td>\n",
       "      <td>170072000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.381676</td>\n",
       "      <td>4.5</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>6.350441</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Arsenal</td>\n",
       "      <td>70</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>41000000.0</td>\n",
       "      <td>1.077580</td>\n",
       "      <td>-5175000.0</td>\n",
       "      <td>-0.370820</td>\n",
       "      <td>33</td>\n",
       "      <td>25.3</td>\n",
       "      <td>22</td>\n",
       "      <td>109338000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.888269</td>\n",
       "      <td>4.1</td>\n",
       "      <td>8</td>\n",
       "      <td>12</td>\n",
       "      <td>3.175220</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Liverpool</td>\n",
       "      <td>69</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>49000000.0</td>\n",
       "      <td>1.748860</td>\n",
       "      <td>17870000.0</td>\n",
       "      <td>1.280492</td>\n",
       "      <td>30</td>\n",
       "      <td>24.5</td>\n",
       "      <td>21</td>\n",
       "      <td>139748000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.135322</td>\n",
       "      <td>2.4</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>4.595714</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Leeds United</td>\n",
       "      <td>68</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>43000000.0</td>\n",
       "      <td>1.245400</td>\n",
       "      <td>41025000.0</td>\n",
       "      <td>2.939685</td>\n",
       "      <td>36</td>\n",
       "      <td>22.5</td>\n",
       "      <td>22</td>\n",
       "      <td>104249000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.846926</td>\n",
       "      <td>2.9</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>3.091662</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Ipswich Town</td>\n",
       "      <td>66</td>\n",
       "      <td>2000-01</td>\n",
       "      <td>18000000.0</td>\n",
       "      <td>-0.852348</td>\n",
       "      <td>5100000.0</td>\n",
       "      <td>0.365445</td>\n",
       "      <td>27</td>\n",
       "      <td>24.8</td>\n",
       "      <td>11</td>\n",
       "      <td>19354000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.157233</td>\n",
       "      <td>2.3</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>2.506753</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   FINAL_POSITION               TEAM  FINAL_POINTS   SEASON        WAGE  \\\n",
       "0               1  Manchester United            80  2000-01  50000000.0   \n",
       "1               2            Arsenal            70  2000-01  41000000.0   \n",
       "2               3          Liverpool            69  2000-01  49000000.0   \n",
       "3               4       Leeds United            68  2000-01  43000000.0   \n",
       "4               5       Ipswich Town            66  2000-01  18000000.0   \n",
       "\n",
       "   WAGE_SCALED  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED  SQUAD_SIZE  \\\n",
       "0     1.832770           1820000.0                   0.130414          34   \n",
       "1     1.077580          -5175000.0                  -0.370820          33   \n",
       "2     1.748860          17870000.0                   1.280492          30   \n",
       "3     1.245400          41025000.0                   2.939685          36   \n",
       "4    -0.852348           5100000.0                   0.365445          27   \n",
       "\n",
       "   AVG_AGE  NUM_FOREIGN_PLAYERS  TEAM_MARKET_VALUE  IS_ESTIMATED_MARKET_VAL  \\\n",
       "0     24.9                   18        170072000.0                      1.0   \n",
       "1     25.3                   22        109338000.0                      1.0   \n",
       "2     24.5                   21        139748000.0                      1.0   \n",
       "3     22.5                   22        104249000.0                      1.0   \n",
       "4     24.8                   11         19354000.0                      1.0   \n",
       "\n",
       "   TEAM_MARKET_VALUE_SCALED  AVG_TIME  NUM_TRANSFERS_IN  NUM_TRANSFERS_OUT  \\\n",
       "0                  1.381676       4.5                 1                  8   \n",
       "1                  0.888269       4.1                 8                 12   \n",
       "2                  1.135322       2.4                10                 10   \n",
       "3                  0.846926       2.9                 8                  8   \n",
       "4                  0.157233       2.3                 6                  7   \n",
       "\n",
       "   STADIUM_CAPACITY_SCALED  YEARS_IN_LEAGUE  \n",
       "0                 6.350441                9  \n",
       "1                 3.175220                9  \n",
       "2                 4.595714                9  \n",
       "3                 3.091662                9  \n",
       "4                 2.506753                1  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data = total_data.merge(years_in_league, how='left', on=['TEAM','SEASON'])\n",
    "total_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The big six"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "big_six_list = ['Arsenal', 'Chelsea', 'Liverpool', 'Manchester United', 'Manchester City', 'Tottenham Hotspur']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_data['BIG_SIX'] = total_data['TEAM'].isin(big_six_list).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FINAL_POSITION</th>\n",
       "      <th>TEAM</th>\n",
       "      <th>FINAL_POINTS</th>\n",
       "      <th>SEASON</th>\n",
       "      <th>WAGE</th>\n",
       "      <th>WAGE_SCALED</th>\n",
       "      <th>NET_TRANSFER_SPEND</th>\n",
       "      <th>NET_TRANSFER_SPEND_SCALED</th>\n",
       "      <th>SQUAD_SIZE</th>\n",
       "      <th>AVG_AGE</th>\n",
       "      <th>NUM_FOREIGN_PLAYERS</th>\n",
       "      <th>TEAM_MARKET_VALUE</th>\n",
       "      <th>IS_ESTIMATED_MARKET_VAL</th>\n",
       "      <th>TEAM_MARKET_VALUE_SCALED</th>\n",
       "      <th>AVG_TIME</th>\n",
       "      <th>NUM_TRANSFERS_IN</th>\n",
       "      <th>NUM_TRANSFERS_OUT</th>\n",
       "      <th>STADIUM_CAPACITY_SCALED</th>\n",
       "      <th>YEARS_IN_LEAGUE</th>\n",
       "      <th>BIG_SIX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>9</td>\n",
       "      <td>Tottenham Hotspur</td>\n",
       "      <td>52</td>\n",
       "      <td>2004-05</td>\n",
       "      <td>33000000.0</td>\n",
       "      <td>-0.326542</td>\n",
       "      <td>34225000.0</td>\n",
       "      <td>0.922463</td>\n",
       "      <td>36</td>\n",
       "      <td>23.3</td>\n",
       "      <td>20</td>\n",
       "      <td>167770000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.362975</td>\n",
       "      <td>2.0</td>\n",
       "      <td>17</td>\n",
       "      <td>20</td>\n",
       "      <td>3.008104</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>15</td>\n",
       "      <td>Manchester City</td>\n",
       "      <td>43</td>\n",
       "      <td>2005-06</td>\n",
       "      <td>34000000.0</td>\n",
       "      <td>-0.360422</td>\n",
       "      <td>-20025000.0</td>\n",
       "      <td>-1.309864</td>\n",
       "      <td>28</td>\n",
       "      <td>24.7</td>\n",
       "      <td>14</td>\n",
       "      <td>70400000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.571934</td>\n",
       "      <td>2.4</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>3.927246</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>333</th>\n",
       "      <td>14</td>\n",
       "      <td>Crystal Palace</td>\n",
       "      <td>41</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>55000000.0</td>\n",
       "      <td>-0.618739</td>\n",
       "      <td>46800000.0</td>\n",
       "      <td>0.998964</td>\n",
       "      <td>27</td>\n",
       "      <td>28.8</td>\n",
       "      <td>18</td>\n",
       "      <td>186500000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.515139</td>\n",
       "      <td>2.4</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>2.172519</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>14</td>\n",
       "      <td>Charlton Athletic</td>\n",
       "      <td>44</td>\n",
       "      <td>2001-02</td>\n",
       "      <td>22000000.0</td>\n",
       "      <td>-0.998153</td>\n",
       "      <td>12125000.0</td>\n",
       "      <td>0.813621</td>\n",
       "      <td>29</td>\n",
       "      <td>26.3</td>\n",
       "      <td>14</td>\n",
       "      <td>21426000.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.174066</td>\n",
       "      <td>3.3</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "      <td>2.256078</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>3</td>\n",
       "      <td>Manchester City</td>\n",
       "      <td>78</td>\n",
       "      <td>2016-17</td>\n",
       "      <td>225000000.0</td>\n",
       "      <td>1.904607</td>\n",
       "      <td>180000000.0</td>\n",
       "      <td>3.842168</td>\n",
       "      <td>25</td>\n",
       "      <td>28.2</td>\n",
       "      <td>21</td>\n",
       "      <td>525250000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.267166</td>\n",
       "      <td>3.2</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>4.595714</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FINAL_POSITION               TEAM  FINAL_POINTS   SEASON         WAGE  \\\n",
       "88                9  Tottenham Hotspur            52  2004-05   33000000.0   \n",
       "114              15    Manchester City            43  2005-06   34000000.0   \n",
       "333              14     Crystal Palace            41  2016-17   55000000.0   \n",
       "33               14  Charlton Athletic            44  2001-02   22000000.0   \n",
       "322               3    Manchester City            78  2016-17  225000000.0   \n",
       "\n",
       "     WAGE_SCALED  NET_TRANSFER_SPEND  NET_TRANSFER_SPEND_SCALED  SQUAD_SIZE  \\\n",
       "88     -0.326542          34225000.0                   0.922463          36   \n",
       "114    -0.360422         -20025000.0                  -1.309864          28   \n",
       "333    -0.618739          46800000.0                   0.998964          27   \n",
       "33     -0.998153          12125000.0                   0.813621          29   \n",
       "322     1.904607         180000000.0                   3.842168          25   \n",
       "\n",
       "     AVG_AGE  NUM_FOREIGN_PLAYERS  TEAM_MARKET_VALUE  IS_ESTIMATED_MARKET_VAL  \\\n",
       "88      23.3                   20        167770000.0                      1.0   \n",
       "114     24.7                   14         70400000.0                      0.0   \n",
       "333     28.8                   18        186500000.0                      0.0   \n",
       "33      26.3                   14         21426000.0                      1.0   \n",
       "322     28.2                   21        525250000.0                      0.0   \n",
       "\n",
       "     TEAM_MARKET_VALUE_SCALED  AVG_TIME  NUM_TRANSFERS_IN  NUM_TRANSFERS_OUT  \\\n",
       "88                   1.362975       2.0                17                 20   \n",
       "114                  0.571934       2.4                 5                  7   \n",
       "333                  1.515139       2.4                 9                 10   \n",
       "33                   0.174066       3.3                 5                  7   \n",
       "322                  4.267166       3.2                12                  6   \n",
       "\n",
       "     STADIUM_CAPACITY_SCALED  YEARS_IN_LEAGUE  BIG_SIX  \n",
       "88                  3.008104               13        1  \n",
       "114                 3.927246                4        1  \n",
       "333                 2.172519                4        0  \n",
       "33                  2.256078                2        0  \n",
       "322                 4.595714               15        1  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_data.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Export data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 340 entries, 0 to 339\n",
      "Data columns (total 20 columns):\n",
      "FINAL_POSITION               340 non-null int64\n",
      "TEAM                         340 non-null object\n",
      "FINAL_POINTS                 340 non-null int64\n",
      "SEASON                       340 non-null object\n",
      "WAGE                         333 non-null float64\n",
      "WAGE_SCALED                  333 non-null float64\n",
      "NET_TRANSFER_SPEND           340 non-null float64\n",
      "NET_TRANSFER_SPEND_SCALED    340 non-null float64\n",
      "SQUAD_SIZE                   340 non-null int64\n",
      "AVG_AGE                      340 non-null float64\n",
      "NUM_FOREIGN_PLAYERS          340 non-null int64\n",
      "TEAM_MARKET_VALUE            340 non-null float64\n",
      "IS_ESTIMATED_MARKET_VAL      340 non-null float64\n",
      "TEAM_MARKET_VALUE_SCALED     340 non-null float64\n",
      "AVG_TIME                     340 non-null float64\n",
      "NUM_TRANSFERS_IN             340 non-null int64\n",
      "NUM_TRANSFERS_OUT            340 non-null int64\n",
      "STADIUM_CAPACITY_SCALED      340 non-null float64\n",
      "YEARS_IN_LEAGUE              340 non-null int64\n",
      "BIG_SIX                      340 non-null int64\n",
      "dtypes: float64(10), int64(8), object(2)\n",
      "memory usage: 55.8+ KB\n"
     ]
    }
   ],
   "source": [
    "total_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "total_data.to_csv('data/complete_data.csv', index=None)"
   ]
  }
 ],
 "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}