Team_Attributes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Team Attributes\n",
    "**Goal: process \"Team Attributes\" data to prepare it for machine learning model**   \n",
    "There are two versions of the processing for categorical variables:  \n",
    "1. One hot encoding for all categorical variables  \n",
    "2. Numerical ranking for ordinal data, one hot encoding for the rest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#import packages\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['index', 'id', 'team_fifa_api_id', 'team_api_id', 'date',\n",
       "       'buildUpPlaySpeed', 'buildUpPlaySpeedClass', 'buildUpPlayDribbling',\n",
       "       'buildUpPlayDribblingClass', 'buildUpPlayPassing',\n",
       "       'buildUpPlayPassingClass', 'buildUpPlayPositioningClass',\n",
       "       'chanceCreationPassing', 'chanceCreationPassingClass',\n",
       "       'chanceCreationCrossing', 'chanceCreationCrossingClass',\n",
       "       'chanceCreationShooting', 'chanceCreationShootingClass',\n",
       "       'chanceCreationPositioningClass', 'defencePressure',\n",
       "       'defencePressureClass', 'defenceAggression', 'defenceAggressionClass',\n",
       "       'defenceTeamWidth', 'defenceTeamWidthClass',\n",
       "       'defenceDefenderLineClass'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#import the database\n",
    "data = pd.read_csv('~/Documents/NIH/BIOF509/Project/Team_Attributes.csv')\n",
    "\n",
    "#view the data columns\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>id</th>\n",
       "      <th>team_fifa_api_id</th>\n",
       "      <th>team_api_id</th>\n",
       "      <th>date</th>\n",
       "      <th>buildUpPlaySpeed</th>\n",
       "      <th>buildUpPlaySpeedClass</th>\n",
       "      <th>buildUpPlayDribbling</th>\n",
       "      <th>buildUpPlayDribblingClass</th>\n",
       "      <th>buildUpPlayPassing</th>\n",
       "      <th>...</th>\n",
       "      <th>chanceCreationShooting</th>\n",
       "      <th>chanceCreationShootingClass</th>\n",
       "      <th>chanceCreationPositioningClass</th>\n",
       "      <th>defencePressure</th>\n",
       "      <th>defencePressureClass</th>\n",
       "      <th>defenceAggression</th>\n",
       "      <th>defenceAggressionClass</th>\n",
       "      <th>defenceTeamWidth</th>\n",
       "      <th>defenceTeamWidthClass</th>\n",
       "      <th>defenceDefenderLineClass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>434</td>\n",
       "      <td>9930</td>\n",
       "      <td>2010-02-22 00:00:00</td>\n",
       "      <td>60</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Little</td>\n",
       "      <td>50</td>\n",
       "      <td>...</td>\n",
       "      <td>55</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>50</td>\n",
       "      <td>Medium</td>\n",
       "      <td>55</td>\n",
       "      <td>Press</td>\n",
       "      <td>45</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>434</td>\n",
       "      <td>9930</td>\n",
       "      <td>2014-09-19 00:00:00</td>\n",
       "      <td>52</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>48.0</td>\n",
       "      <td>Normal</td>\n",
       "      <td>56</td>\n",
       "      <td>...</td>\n",
       "      <td>64</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>47</td>\n",
       "      <td>Medium</td>\n",
       "      <td>44</td>\n",
       "      <td>Press</td>\n",
       "      <td>54</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>434</td>\n",
       "      <td>9930</td>\n",
       "      <td>2015-09-10 00:00:00</td>\n",
       "      <td>47</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>41.0</td>\n",
       "      <td>Normal</td>\n",
       "      <td>54</td>\n",
       "      <td>...</td>\n",
       "      <td>64</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>47</td>\n",
       "      <td>Medium</td>\n",
       "      <td>44</td>\n",
       "      <td>Press</td>\n",
       "      <td>54</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>77</td>\n",
       "      <td>8485</td>\n",
       "      <td>2010-02-22 00:00:00</td>\n",
       "      <td>70</td>\n",
       "      <td>Fast</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Little</td>\n",
       "      <td>70</td>\n",
       "      <td>...</td>\n",
       "      <td>70</td>\n",
       "      <td>Lots</td>\n",
       "      <td>Organised</td>\n",
       "      <td>60</td>\n",
       "      <td>Medium</td>\n",
       "      <td>70</td>\n",
       "      <td>Double</td>\n",
       "      <td>70</td>\n",
       "      <td>Wide</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>77</td>\n",
       "      <td>8485</td>\n",
       "      <td>2011-02-22 00:00:00</td>\n",
       "      <td>47</td>\n",
       "      <td>Balanced</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Little</td>\n",
       "      <td>52</td>\n",
       "      <td>...</td>\n",
       "      <td>52</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Organised</td>\n",
       "      <td>47</td>\n",
       "      <td>Medium</td>\n",
       "      <td>47</td>\n",
       "      <td>Press</td>\n",
       "      <td>52</td>\n",
       "      <td>Normal</td>\n",
       "      <td>Cover</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  id  team_fifa_api_id  team_api_id                 date  \\\n",
       "0      0   1               434         9930  2010-02-22 00:00:00   \n",
       "1      1   2               434         9930  2014-09-19 00:00:00   \n",
       "2      2   3               434         9930  2015-09-10 00:00:00   \n",
       "3      3   4                77         8485  2010-02-22 00:00:00   \n",
       "4      4   5                77         8485  2011-02-22 00:00:00   \n",
       "\n",
       "   buildUpPlaySpeed buildUpPlaySpeedClass  buildUpPlayDribbling  \\\n",
       "0                60              Balanced                   NaN   \n",
       "1                52              Balanced                  48.0   \n",
       "2                47              Balanced                  41.0   \n",
       "3                70                  Fast                   NaN   \n",
       "4                47              Balanced                   NaN   \n",
       "\n",
       "  buildUpPlayDribblingClass  buildUpPlayPassing            ...             \\\n",
       "0                    Little                  50            ...              \n",
       "1                    Normal                  56            ...              \n",
       "2                    Normal                  54            ...              \n",
       "3                    Little                  70            ...              \n",
       "4                    Little                  52            ...              \n",
       "\n",
       "  chanceCreationShooting chanceCreationShootingClass  \\\n",
       "0                     55                      Normal   \n",
       "1                     64                      Normal   \n",
       "2                     64                      Normal   \n",
       "3                     70                        Lots   \n",
       "4                     52                      Normal   \n",
       "\n",
       "   chanceCreationPositioningClass defencePressure  defencePressureClass  \\\n",
       "0                       Organised              50                Medium   \n",
       "1                       Organised              47                Medium   \n",
       "2                       Organised              47                Medium   \n",
       "3                       Organised              60                Medium   \n",
       "4                       Organised              47                Medium   \n",
       "\n",
       "  defenceAggression  defenceAggressionClass defenceTeamWidth  \\\n",
       "0                55                   Press               45   \n",
       "1                44                   Press               54   \n",
       "2                44                   Press               54   \n",
       "3                70                  Double               70   \n",
       "4                47                   Press               52   \n",
       "\n",
       "  defenceTeamWidthClass  defenceDefenderLineClass  \n",
       "0                Normal                     Cover  \n",
       "1                Normal                     Cover  \n",
       "2                Normal                     Cover  \n",
       "3                  Wide                     Cover  \n",
       "4                Normal                     Cover  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#preview the table\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Numerical columns: \n",
      "['index', 'id', 'team_fifa_api_id', 'team_api_id', 'buildUpPlaySpeed', 'buildUpPlayDribbling', 'buildUpPlayPassing', 'chanceCreationPassing', 'chanceCreationCrossing', 'chanceCreationShooting', 'defencePressure', 'defenceAggression', 'defenceTeamWidth']\n",
      "\n",
      "String columns:\n",
      "['date', 'buildUpPlaySpeedClass', 'buildUpPlayDribblingClass', 'buildUpPlayPassingClass', 'buildUpPlayPositioningClass', 'chanceCreationPassingClass', 'chanceCreationCrossingClass', 'chanceCreationShootingClass', 'chanceCreationPositioningClass', 'defencePressureClass', 'defenceAggressionClass', 'defenceTeamWidthClass', 'defenceDefenderLineClass']\n",
      "\n",
      "26 out of 26 columns account for\n"
     ]
    }
   ],
   "source": [
    "#let's figure out the data type for each column\n",
    "#we'll store the column names that correspond to numerical data in the list num_columns\n",
    "#column names that correspond to str data will be stored in the list str_columns\n",
    "\n",
    "#initialize numerical list\n",
    "num_columns = []\n",
    "\n",
    "#loop through column names\n",
    "for i in data.columns:\n",
    "    \n",
    "    #grab column of interest\n",
    "    col = data.loc[data[i].notnull(), i]\n",
    "    \n",
    "    #check to see if is an instance of a numpy data (the numerical data are all np.int64 data types)\n",
    "    if isinstance(col.iloc[0], (np.int64, np.float64)):\n",
    "        #append column name if condition is met\n",
    "        num_columns.append(i)\n",
    "\n",
    "#initialize str list\n",
    "str_columns = []\n",
    "\n",
    "#loop through column names\n",
    "for i in data.columns:\n",
    "    \n",
    "    #grab column of interest\n",
    "    col = data.loc[data[i].notnull(), i]\n",
    "    \n",
    "    #check to see if is an instance of a string\n",
    "    if isinstance(col.iloc[0], str):\n",
    "        #append column name if condition is met\n",
    "        str_columns.append(i)\n",
    "        \n",
    "print('Numerical columns: \\n{}'.format(num_columns))\n",
    "print('\\nString columns:\\n{}'.format(str_columns))\n",
    "print('\\n{} out of {} columns account for'.format(len(num_columns) + len(str_columns), data.shape[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#our new dataframe is going to be called data_new\n",
    "data_new = data.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 969 NAs in the numerical columns before processing\n",
      "Now there are 0 NAs in the numerical columns after processing\n"
     ]
    }
   ],
   "source": [
    "#work with numerical data first\n",
    "#determine total number of NAs in dataframe\n",
    "num_NAs = 0\n",
    "for i in num_columns:\n",
    "    num_NAs += data_new[i].isnull().sum()\n",
    "print('There are {} NAs in the numerical columns before processing'.format(num_NAs))    \n",
    "\n",
    "#replace NA values with the median value for column\n",
    "for i in num_columns:\n",
    "    data_new.loc[data_new[i].isnull(), i] = data_new[i].median()\n",
    "    \n",
    "\n",
    "#perform sanity check to make sure that worked\n",
    "num_NAs = 0\n",
    "for i in num_columns:\n",
    "    num_NAs += data_new[i].isnull().sum()\n",
    "print('Now there are {} NAs in the numerical columns after processing'.format(num_NAs)) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. One hot encoding for all categorical variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date ['2010-02-22 00:00:00' '2014-09-19 00:00:00' '2015-09-10 00:00:00'\n",
      " '2011-02-22 00:00:00' '2012-02-22 00:00:00' '2013-09-20 00:00:00']\n",
      "buildUpPlaySpeedClass ['Balanced' 'Fast' 'Slow']\n",
      "buildUpPlayDribblingClass ['Little' 'Normal' 'Lots']\n",
      "buildUpPlayPassingClass ['Mixed' 'Long' 'Short']\n",
      "buildUpPlayPositioningClass ['Organised' 'Free Form']\n",
      "chanceCreationPassingClass ['Normal' 'Risky' 'Safe']\n",
      "chanceCreationCrossingClass ['Normal' 'Lots' 'Little']\n",
      "chanceCreationShootingClass ['Normal' 'Lots' 'Little']\n",
      "chanceCreationPositioningClass ['Organised' 'Free Form']\n",
      "defencePressureClass ['Medium' 'Deep' 'High']\n",
      "defenceAggressionClass ['Press' 'Double' 'Contain']\n",
      "defenceTeamWidthClass ['Normal' 'Wide' 'Narrow']\n",
      "defenceDefenderLineClass ['Cover' 'Offside Trap']\n"
     ]
    }
   ],
   "source": [
    "#work with string data now\n",
    "\n",
    "#look at the categorical values for each attribute\n",
    "for i in str_columns:\n",
    "    print(i,data_new[i].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#create a new data frame with just the data column (the only column that we're not going to edit)\n",
    "str_data = data_new['date']\n",
    "\n",
    "#we are going to loop through all of the categorical variables in the original data set \n",
    "#and for each value in a given variable we create a new column of binary values\n",
    "for i in str_columns[1:]:\n",
    "    labels = data_new[i].unique()\n",
    "    for j in labels:\n",
    "        col = pd.DataFrame({i+'_'+j:(data_new[i] == j)*1})\n",
    "        str_data = pd.concat((str_data, col), axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>buildUpPlaySpeedClass_Balanced</th>\n",
       "      <th>buildUpPlaySpeedClass_Fast</th>\n",
       "      <th>buildUpPlaySpeedClass_Slow</th>\n",
       "      <th>buildUpPlayDribblingClass_Little</th>\n",
       "      <th>buildUpPlayDribblingClass_Normal</th>\n",
       "      <th>buildUpPlayDribblingClass_Lots</th>\n",
       "      <th>buildUpPlayPassingClass_Mixed</th>\n",
       "      <th>buildUpPlayPassingClass_Long</th>\n",
       "      <th>buildUpPlayPassingClass_Short</th>\n",
       "      <th>...</th>\n",
       "      <th>defencePressureClass_Deep</th>\n",
       "      <th>defencePressureClass_High</th>\n",
       "      <th>defenceAggressionClass_Press</th>\n",
       "      <th>defenceAggressionClass_Double</th>\n",
       "      <th>defenceAggressionClass_Contain</th>\n",
       "      <th>defenceTeamWidthClass_Normal</th>\n",
       "      <th>defenceTeamWidthClass_Wide</th>\n",
       "      <th>defenceTeamWidthClass_Narrow</th>\n",
       "      <th>defenceDefenderLineClass_Cover</th>\n",
       "      <th>defenceDefenderLineClass_Offside Trap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-02-22 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-09-19 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-09-10 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010-02-22 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-02-22 00:00:00</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date  buildUpPlaySpeedClass_Balanced  \\\n",
       "0  2010-02-22 00:00:00                               1   \n",
       "1  2014-09-19 00:00:00                               1   \n",
       "2  2015-09-10 00:00:00                               1   \n",
       "3  2010-02-22 00:00:00                               0   \n",
       "4  2011-02-22 00:00:00                               1   \n",
       "\n",
       "   buildUpPlaySpeedClass_Fast  buildUpPlaySpeedClass_Slow  \\\n",
       "0                           0                           0   \n",
       "1                           0                           0   \n",
       "2                           0                           0   \n",
       "3                           1                           0   \n",
       "4                           0                           0   \n",
       "\n",
       "   buildUpPlayDribblingClass_Little  buildUpPlayDribblingClass_Normal  \\\n",
       "0                                 1                                 0   \n",
       "1                                 0                                 1   \n",
       "2                                 0                                 1   \n",
       "3                                 1                                 0   \n",
       "4                                 1                                 0   \n",
       "\n",
       "   buildUpPlayDribblingClass_Lots  buildUpPlayPassingClass_Mixed  \\\n",
       "0                               0                              1   \n",
       "1                               0                              1   \n",
       "2                               0                              1   \n",
       "3                               0                              0   \n",
       "4                               0                              1   \n",
       "\n",
       "   buildUpPlayPassingClass_Long  buildUpPlayPassingClass_Short  \\\n",
       "0                             0                              0   \n",
       "1                             0                              0   \n",
       "2                             0                              0   \n",
       "3                             1                              0   \n",
       "4                             0                              0   \n",
       "\n",
       "                   ...                    defencePressureClass_Deep  \\\n",
       "0                  ...                                            0   \n",
       "1                  ...                                            0   \n",
       "2                  ...                                            0   \n",
       "3                  ...                                            0   \n",
       "4                  ...                                            0   \n",
       "\n",
       "   defencePressureClass_High  defenceAggressionClass_Press  \\\n",
       "0                          0                             1   \n",
       "1                          0                             1   \n",
       "2                          0                             1   \n",
       "3                          0                             0   \n",
       "4                          0                             1   \n",
       "\n",
       "   defenceAggressionClass_Double  defenceAggressionClass_Contain  \\\n",
       "0                              0                               0   \n",
       "1                              0                               0   \n",
       "2                              0                               0   \n",
       "3                              1                               0   \n",
       "4                              0                               0   \n",
       "\n",
       "   defenceTeamWidthClass_Normal  defenceTeamWidthClass_Wide  \\\n",
       "0                             1                           0   \n",
       "1                             1                           0   \n",
       "2                             1                           0   \n",
       "3                             0                           1   \n",
       "4                             1                           0   \n",
       "\n",
       "   defenceTeamWidthClass_Narrow  defenceDefenderLineClass_Cover  \\\n",
       "0                             0                               1   \n",
       "1                             0                               1   \n",
       "2                             0                               1   \n",
       "3                             0                               1   \n",
       "4                             0                               1   \n",
       "\n",
       "   defenceDefenderLineClass_Offside Trap  \n",
       "0                                      0  \n",
       "1                                      0  \n",
       "2                                      0  \n",
       "3                                      0  \n",
       "4                                      0  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#display the result\n",
    "str_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#now we concatenate our numerical and categorical variables into the final dataset\n",
    "final_data = pd.concat((data_new[num_columns], str_data), axis =1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#save this as a new .csv file\n",
    "final_data.to_csv('Team_Attributes_processed.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Numerical ranking for ordinal data, one hot encoding for the rest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "date ['2010-02-22 00:00:00' '2014-09-19 00:00:00' '2015-09-10 00:00:00'\n",
      " '2011-02-22 00:00:00' '2012-02-22 00:00:00' '2013-09-20 00:00:00']\n",
      "buildUpPlaySpeedClass ['Balanced' 'Fast' 'Slow']\n",
      "buildUpPlayDribblingClass ['Little' 'Normal' 'Lots']\n",
      "buildUpPlayPassingClass ['Mixed' 'Long' 'Short']\n",
      "buildUpPlayPositioningClass ['Organised' 'Free Form']\n",
      "chanceCreationPassingClass ['Normal' 'Risky' 'Safe']\n",
      "chanceCreationCrossingClass ['Normal' 'Lots' 'Little']\n",
      "chanceCreationShootingClass ['Normal' 'Lots' 'Little']\n",
      "chanceCreationPositioningClass ['Organised' 'Free Form']\n",
      "defencePressureClass ['Medium' 'Deep' 'High']\n",
      "defenceAggressionClass ['Press' 'Double' 'Contain']\n",
      "defenceTeamWidthClass ['Normal' 'Wide' 'Narrow']\n",
      "defenceDefenderLineClass ['Cover' 'Offside Trap']\n"
     ]
    }
   ],
   "source": [
    "#work with string data now\n",
    "\n",
    "#look at the categorical values for each attribute\n",
    "for i in str_columns:\n",
    "    print(i,data_new[i].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#the only columns we're going to remove are date, defenceAggressionClass, and defenceDefenderLineClass\n",
    "col_names = str_columns[:]\n",
    "col_names.remove('date'); col_names.remove('defenceAggressionClass'); col_names.remove('defenceDefenderLineClass')\n",
    "\n",
    "#create dictionaries for each ordinal variable\n",
    "dict_list = [{'Slow': 1, 'Balanced': 2, 'Fast': 3},\n",
    "                {'Little': 1, 'Normal': 2, 'Lots': 3},\n",
    "                {'Short': 1, 'Mixed': 2, 'Long': 3},\n",
    "                {'Free Form':0, 'Organised': 1},\n",
    "                {'Safe': 1, 'Normal': 2, 'Risky': 3},\n",
    "                {'Little': 1, 'Normal': 2, 'Lots': 3},\n",
    "                {'Little': 1, 'Normal': 2, 'Lots': 3},\n",
    "                {'Free Form':0, 'Organised': 1},\n",
    "                {'Deep': 1, 'Medium': 2, 'High': 3},\n",
    "                {'Narrow': 1, 'Normal': 2, 'Wide': 3}]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'buildUpPlayDribblingClass': {'Little': 1, 'Lots': 3, 'Normal': 2},\n",
       " 'buildUpPlayPassingClass': {'Long': 3, 'Mixed': 2, 'Short': 1},\n",
       " 'buildUpPlayPositioningClass': {'Free Form': 0, 'Organised': 1},\n",
       " 'buildUpPlaySpeedClass': {'Balanced': 2, 'Fast': 3, 'Slow': 1},\n",
       " 'chanceCreationCrossingClass': {'Little': 1, 'Lots': 3, 'Normal': 2},\n",
       " 'chanceCreationPassingClass': {'Normal': 2, 'Risky': 3, 'Safe': 1},\n",
       " 'chanceCreationPositioningClass': {'Free Form': 0, 'Organised': 1},\n",
       " 'chanceCreationShootingClass': {'Little': 1, 'Lots': 3, 'Normal': 2},\n",
       " 'defencePressureClass': {'Deep': 1, 'High': 3, 'Medium': 2},\n",
       " 'defenceTeamWidthClass': {'Narrow': 1, 'Normal': 2, 'Wide': 3}}"
      ]
     },
     "execution_count": 160,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#now we'll create another dictionary to match our column names with the appropriate dictionary\n",
    "ordinal_dict = {}\n",
    "#this is our indexer\n",
    "j = 0\n",
    "#loop through the column names that we're going to use\n",
    "for i in col_names:\n",
    "    #create a new key/value pair (i/j)\n",
    "    ordinal_dict[i]= dict_list[j]\n",
    "    j+=1\n",
    "    \n",
    "#view the result\n",
    "ordinal_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "True\n",
      "True\n",
      "True\n",
      "True\n",
      "True\n",
      "True\n",
      "True\n",
      "True\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "#now sanity check to make sure that we did our work correctly\n",
    "\n",
    "#make sure that dictionary keys are spelled correctly\n",
    "#get the keys from our new dictionary\n",
    "keys = list(ordinal_dict.keys())\n",
    "#make sure that these keys are in the column names\n",
    "for i in keys:\n",
    "    print(i in col_names)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('buildUpPlaySpeedClass', {'Balanced': 2, 'Fast': 3, 'Slow': 1}),\n",
       " ('buildUpPlayDribblingClass', {'Little': 1, 'Lots': 3, 'Normal': 2}),\n",
       " ('buildUpPlayPassingClass', {'Long': 3, 'Mixed': 2, 'Short': 1}),\n",
       " ('buildUpPlayPositioningClass', {'Free Form': 0, 'Organised': 1}),\n",
       " ('chanceCreationPassingClass', {'Normal': 2, 'Risky': 3, 'Safe': 1}),\n",
       " ('chanceCreationCrossingClass', {'Little': 1, 'Lots': 3, 'Normal': 2}),\n",
       " ('chanceCreationShootingClass', {'Little': 1, 'Lots': 3, 'Normal': 2}),\n",
       " ('chanceCreationPositioningClass', {'Free Form': 0, 'Organised': 1}),\n",
       " ('defencePressureClass', {'Deep': 1, 'High': 3, 'Medium': 2}),\n",
       " ('defenceTeamWidthClass', {'Narrow': 1, 'Normal': 2, 'Wide': 3})]"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(ordinal_dict.items())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#create a new data frame with just the data column (the only column that we're not going to edit)\n",
    "test_data = data_new['date'].copy()\n",
    "\n",
    "#we are going to loop through all of the ordinal variables in the original data set \n",
    "#and for each label we substitute the value\n",
    "for i in col_names:\n",
    "    col = data_new[i].copy()\n",
    "    for label in list(ordinal_dict[i].keys()):\n",
    "        col.loc[col == label] = ordinal_dict[i][label]\n",
    "    test_data = pd.concat((test_data, col), axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#we are going to loop through the categorical variables that we want to one-hot-encode\n",
    "#and for each value in a given variable we create a new column of binary values\n",
    "\n",
    "one_hot_cols = ['defenceAggressionClass', 'defenceDefenderLineClass']\n",
    "for i in one_hot_cols:\n",
    "    labels = data_new[i].unique()\n",
    "    for j in labels:\n",
    "        col = pd.DataFrame({i+'_'+j:(data_new[i] == j)*1})\n",
    "        test_data = pd.concat((test_data, col), axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>buildUpPlaySpeedClass</th>\n",
       "      <th>buildUpPlayDribblingClass</th>\n",
       "      <th>buildUpPlayPassingClass</th>\n",
       "      <th>buildUpPlayPositioningClass</th>\n",
       "      <th>chanceCreationPassingClass</th>\n",
       "      <th>chanceCreationCrossingClass</th>\n",
       "      <th>chanceCreationShootingClass</th>\n",
       "      <th>chanceCreationPositioningClass</th>\n",
       "      <th>defencePressureClass</th>\n",
       "      <th>defenceTeamWidthClass</th>\n",
       "      <th>defenceAggressionClass_Press</th>\n",
       "      <th>defenceAggressionClass_Double</th>\n",
       "      <th>defenceAggressionClass_Contain</th>\n",
       "      <th>defenceDefenderLineClass_Cover</th>\n",
       "      <th>defenceDefenderLineClass_Offside Trap</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2010-02-22 00:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-09-19 00:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015-09-10 00:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2010-02-22 00:00:00</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-02-22 00:00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  date buildUpPlaySpeedClass buildUpPlayDribblingClass  \\\n",
       "0  2010-02-22 00:00:00                     2                         1   \n",
       "1  2014-09-19 00:00:00                     2                         2   \n",
       "2  2015-09-10 00:00:00                     2                         2   \n",
       "3  2010-02-22 00:00:00                     3                         1   \n",
       "4  2011-02-22 00:00:00                     2                         1   \n",
       "\n",
       "  buildUpPlayPassingClass buildUpPlayPositioningClass  \\\n",
       "0                       2                           1   \n",
       "1                       2                           1   \n",
       "2                       2                           1   \n",
       "3                       3                           1   \n",
       "4                       2                           1   \n",
       "\n",
       "  chanceCreationPassingClass chanceCreationCrossingClass  \\\n",
       "0                          2                           2   \n",
       "1                          2                           2   \n",
       "2                          2                           2   \n",
       "3                          3                           3   \n",
       "4                          2                           2   \n",
       "\n",
       "  chanceCreationShootingClass chanceCreationPositioningClass  \\\n",
       "0                           2                              1   \n",
       "1                           2                              1   \n",
       "2                           2                              1   \n",
       "3                           3                              1   \n",
       "4                           2                              1   \n",
       "\n",
       "  defencePressureClass defenceTeamWidthClass  defenceAggressionClass_Press  \\\n",
       "0                    2                     2                             1   \n",
       "1                    2                     2                             1   \n",
       "2                    2                     2                             1   \n",
       "3                    2                     3                             0   \n",
       "4                    2                     2                             1   \n",
       "\n",
       "   defenceAggressionClass_Double  defenceAggressionClass_Contain  \\\n",
       "0                              0                               0   \n",
       "1                              0                               0   \n",
       "2                              0                               0   \n",
       "3                              1                               0   \n",
       "4                              0                               0   \n",
       "\n",
       "   defenceDefenderLineClass_Cover  defenceDefenderLineClass_Offside Trap  \n",
       "0                               1                                      0  \n",
       "1                               1                                      0  \n",
       "2                               1                                      0  \n",
       "3                               1                                      0  \n",
       "4                               1                                      0  "
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#now we concatenate our numerical and categorical variables into the final dataset\n",
    "final_data = pd.concat((data_new[num_columns], test_data), axis =1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#save this as a new .csv file\n",
    "final_data.to_csv('Team_Attributes_processed_ordinal.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}