6_Position Classification model.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Position fitness analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "%config InlineBackend.figure_formats = {'png', 'retina'}\n",
    "\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib as mpl\n",
    "import pandas as pd\n",
    "import MySQLdb\n",
    "\n",
    "from sklearn.tree import export_graphviz\n",
    "from sklearn.datasets import load_iris\n",
    "from sklearn.cross_validation import train_test_split\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.metrics import confusion_matrix\n",
    "from sklearn.metrics import classification_report"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Connect DB & Make QUERY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "db = MySQLdb.connect(\n",
    "    \"db.fastcamp.us\",\n",
    "    \"root\",\n",
    "    \"dkstncks\",\n",
    "    \"football\",\n",
    "    charset='utf8',\n",
    ")\n",
    "\n",
    "# forword feature\n",
    "# apps_sub, mins, goals, assists, yel, spg, motm, aw, tackles, inter, drb, blocks, keyp_x, fouled, unstch, avgp          \n",
    "\n",
    "# defenser feature\n",
    "# goals, assists, spg, ps_x, motm, aw, tackles, inter, fouls, clear, owng, keyp_x, fouled, unstch, avgp          \n",
    "\n",
    "# midfilder feature\n",
    "# age, apps_sub, mins, goals, assists, spg, ps_x, motm, aw, tackles, inter, keyp_x, fouled, avgp\n",
    "\n",
    "def make_query(position):\n",
    "    \"\"\"\n",
    "    \n",
    "    parameter------------\n",
    "    position : M, D, F, G\n",
    "    \n",
    "    return---------------\n",
    "    SQL_QUERY String\n",
    "    \n",
    "    \"\"\"\n",
    "    SQL_QUERY = \"\"\"\n",
    "        SELECT \n",
    "            tall, weight, apps_sub, mins, goals, assists\n",
    "            , spg, ps_x, motm, aw, tackles, inter, fouls, clear, drb\n",
    "            , owng, keyp_x, fouled, off, disp, unstch, avgp\n",
    "        FROM player\n",
    "        WHERE position not like \"%,%\" and position like \"%{position}%\" and mins > 270\n",
    "        \n",
    "        ;\n",
    "    \"\"\".format(position=position)\n",
    "    \n",
    "    return SQL_QUERY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Each Position Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(356, 997, 971, 213)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# forword\n",
    "SQL_QUERY = make_query(\"F\")\n",
    "forward_df = pd.read_sql(SQL_QUERY, db)\n",
    "\n",
    "# midfilder\n",
    "SQL_QUERY = make_query(\"M\")\n",
    "midfielder_df = pd.read_sql(SQL_QUERY, db)\n",
    "\n",
    "# defencer\n",
    "SQL_QUERY = make_query(\"D\")\n",
    "defender_df = pd.read_sql(SQL_QUERY, db)\n",
    "\n",
    "# goalkeeper\n",
    "SQL_QUERY = make_query(\"G\")\n",
    "goalkeeper_df = pd.read_sql(SQL_QUERY, db)\n",
    "\n",
    "len(forward_df), len(midfielder_df), len(defender_df), len(goalkeeper_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set Class per Position & Concat Dataframes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>tall</th>\n",
       "      <th>weight</th>\n",
       "      <th>apps_sub</th>\n",
       "      <th>mins</th>\n",
       "      <th>goals</th>\n",
       "      <th>assists</th>\n",
       "      <th>spg</th>\n",
       "      <th>ps_x</th>\n",
       "      <th>motm</th>\n",
       "      <th>aw</th>\n",
       "      <th>...</th>\n",
       "      <th>clear</th>\n",
       "      <th>drb</th>\n",
       "      <th>owng</th>\n",
       "      <th>keyp_x</th>\n",
       "      <th>fouled</th>\n",
       "      <th>off</th>\n",
       "      <th>disp</th>\n",
       "      <th>unstch</th>\n",
       "      <th>avgp</th>\n",
       "      <th>position</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>187</td>\n",
       "      <td>82</td>\n",
       "      <td>0</td>\n",
       "      <td>2430</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>59.7</td>\n",
       "      <td>0</td>\n",
       "      <td>0.5</td>\n",
       "      <td>...</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1</td>\n",
       "      <td>25.5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>198</td>\n",
       "      <td>83</td>\n",
       "      <td>2</td>\n",
       "      <td>1397</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>...</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19.8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>199</td>\n",
       "      <td>91</td>\n",
       "      <td>0</td>\n",
       "      <td>2020</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>48.2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>...</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22.7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>211</th>\n",
       "      <td>188</td>\n",
       "      <td>78</td>\n",
       "      <td>0</td>\n",
       "      <td>2970</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>54.2</td>\n",
       "      <td>0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.1</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>28.9</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>185</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>450</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>62.8</td>\n",
       "      <td>0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>27.4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     tall  weight  apps_sub  mins  goals  assists  spg  ps_x  motm   aw  \\\n",
       "208   187      82         0  2430      0        0  0.0  59.7     0  0.5   \n",
       "209   198      83         2  1397      0        0  0.0  47.0     0  0.4   \n",
       "210   199      91         0  2020      0        0  0.0  48.2     0  0.3   \n",
       "211   188      78         0  2970      0        0  0.0  54.2     0  0.2   \n",
       "212   185      81         0   450      0        0  0.0  62.8     0  0.2   \n",
       "\n",
       "       ...     clear  drb  owng  keyp_x  fouled  off  disp  unstch  avgp  \\\n",
       "208    ...       1.2  0.0     0     0.0     0.3  0.0   0.0     0.1  25.5   \n",
       "209    ...       1.1  0.1     0     0.0     0.2  0.0   0.0     0.0  19.8   \n",
       "210    ...       1.1  0.0     0     0.0     0.0  0.0   0.0     0.0  22.7   \n",
       "211    ...       0.8  0.1     0     0.0     0.2  0.0   0.0     0.0  28.9   \n",
       "212    ...       0.8  0.0     0     0.2     0.0  0.0   0.0     0.0  27.4   \n",
       "\n",
       "     position  \n",
       "208         3  \n",
       "209         3  \n",
       "210         3  \n",
       "211         3  \n",
       "212         3  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "forward_df[\"position\"] = 0\n",
    "forward_df\n",
    "\n",
    "midfielder_df[\"position\"] = 1\n",
    "midfielder_df\n",
    "\n",
    "defender_df[\"position\"] = 2\n",
    "defender_df\n",
    "\n",
    "goalkeeper_df[\"position\"] = 3\n",
    "goalkeeper_df\n",
    "\n",
    "merged_df = pd.concat([forward_df, midfielder_df, defender_df, goalkeeper_df])\n",
    "merged_df.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Classification Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Split train data and test data\n",
    "X_train, X_test, y_train, y_test = train_test_split(merged_df.ix[:,:-1], merged_df.ix[:,-1], test_size=0.2, random_state=1)\n",
    "\n",
    "# Make DecisionTreeClassifier\n",
    "model = DecisionTreeClassifier(criterion='entropy', max_depth=3).fit(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 28,  28,   2,   0],\n",
       "       [  3, 189,  14,   0],\n",
       "       [  1,  48, 154,   0],\n",
       "       [  0,   0,   0,  41]])"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check Confusion_matrix\n",
    "confusion_matrix(y_test, model.predict(X_test))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             precision    recall  f1-score   support\n",
      "\n",
      "          0       0.88      0.48      0.62        58\n",
      "          1       0.71      0.92      0.80       206\n",
      "          2       0.91      0.76      0.83       203\n",
      "          3       1.00      1.00      1.00        41\n",
      "\n",
      "avg / total       0.83      0.81      0.81       508\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Check Classification Report\n",
    "print(classification_report(y_test, model.predict(X_test)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Recommend Position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "586"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SQL_QUERY = \"\"\"\n",
    "    SELECT \n",
    "        tall, weight, apps_sub, mins, goals, assists\n",
    "        , spg, ps_x, motm, aw, tackles, inter, fouls, clear, drb\n",
    "        , owng, keyp_x, fouled, off, disp, unstch, avgp, position\n",
    "    FROM player\n",
    "    WHERE position like \"%,%\" and mins > 270\n",
    "    ;\n",
    "\"\"\"\n",
    "\n",
    "multi_position_player_df = pd.read_sql(SQL_QUERY, db)\n",
    "len(multi_position_player_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test_data = model.predict(multi_position_player_df.ix[:,:-1])\n",
    "multi_position_player_df[\"recommend_position\"] = test_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>position</th>\n",
       "      <th>recommend_position</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>M(CLR),FW</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>D(C),DMC</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D(LR),M(CR)</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>D(L),M(L)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>D(C),M(C)</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>D(C),M(C)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AM(L),FW</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AM(CLR),FW</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>D(C),M(CLR)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AM(CR),FW</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AM(CLR),FW</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        position  recommend_position\n",
       "0      M(CLR),FW                   1\n",
       "1       D(C),DMC                   1\n",
       "2    D(LR),M(CR)                   2\n",
       "3      D(L),M(L)                   1\n",
       "4      D(C),M(C)                   2\n",
       "5      D(C),M(C)                   1\n",
       "6       AM(L),FW                   0\n",
       "7     AM(CLR),FW                   1\n",
       "8    D(C),M(CLR)                   1\n",
       "9      AM(CR),FW                   1\n",
       "10    AM(CLR),FW                   1"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Recomend Result\n",
    "# 0 : Forword, 1 : Midfilder, 2 : Defencer, 3 : Goalkeeper\n",
    "multi_position_player_df.ix[:10,-2:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 결론\n",
    "- 포지션 분류 모델의 정확도는 약 82%로 결과가 나왔으나, 공격수와 미드필더를 잘 분류하지 못하는 한계가 있었다. 그러나 포지션 별이 아닌 Role 별로 선수를 나눈다면 더 나은 모델이 될것이라고 확신한다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.1+"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}