{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4d2a8b6c",
   "metadata": {},
   "source": [
    "#### Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7be9eeff",
   "metadata": {},
   "outputs": [],
   "source": [
    "PROJECT_PATH = '/home/md/Work/ligalytics/leagues_stable/'\n",
    "import os, sys\n",
    "sys.path.insert(0, PROJECT_PATH)\n",
    "os.environ.setdefault(\"DJANGO_SETTINGS_MODULE\", \"leagues.settings\")\n",
    "\n",
    "from leagues import settings\n",
    "settings.DATABASES['default']['NAME'] = PROJECT_PATH+'/db.sqlite3'\n",
    "\n",
    "import django\n",
    "django.setup()\n",
    "\n",
    "from scheduler.models import *\n",
    "from common.functions import distanceInKmByGPS\n",
    "season = Season.objects.filter(nicename=\"Imported: Benchmark Season\").first()\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from django.db.models import F\n",
    "games = Game.objects.filter(season=season)\n",
    "df = pd.DataFrame.from_records(games.values())\n",
    "games = Game.objects.filter(season=season).annotate(\n",
    "    home=F('homeTeam__shortname'),\n",
    "    away=F('awayTeam__shortname'),\n",
    "    home_lat=F('homeTeam__latitude'),\n",
    "    home_lon=F('homeTeam__longitude'),\n",
    "    home_attr=F('homeTeam__attractivity'),\n",
    "    away_lat=F('awayTeam__latitude'),\n",
    "    away_lon=F('awayTeam__longitude'),\n",
    "    away_attr=F('awayTeam__attractivity'),\n",
    "    home_country=F('homeTeam__country'),\n",
    "    away_country=F('awayTeam__country'),\n",
    ").values()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc191792",
   "metadata": {},
   "source": [
    "#### Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1e404cf8",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import OneHotEncoder\n",
    "\n",
    "# create dataset\n",
    "df = pd.DataFrame.from_records(games.values())\n",
    "\n",
    "# data cleaning\n",
    "df['time'] = df['time'].replace('','0')\n",
    "df = df[df['attendance'] != 0]\n",
    "\n",
    "\n",
    "# pivots\n",
    "pivot_homeTeam_mean = df.pivot_table('attendance','homeTeam_id',aggfunc='mean')\n",
    "pivot_homeTeam_max = df.pivot_table('attendance','homeTeam_id',aggfunc='max')\n",
    "\n",
    "# add more features\n",
    "df['weekday'] = df.apply(lambda r: r['date'].weekday(), axis=1)\n",
    "df['day'] = df.apply(lambda r: r['date'].day, axis=1)\n",
    "df['month'] = df.apply(lambda r: r['date'].month, axis=1)\n",
    "df['year'] = df.apply(lambda r: r['date'].year, axis=1)\n",
    "df['distance'] = df.apply(lambda r: distanceInKmByGPS(r['home_lon'],r['home_lat'],r['away_lon'],r['away_lat']), axis=1)\n",
    "df['weekend'] = df.apply(lambda r: int(r['weekday'] in [6,7]), axis=1)\n",
    "df['winter_season'] = df.apply(lambda r: int(r['month'] in [1,2,3,10,11,12]), axis=1)\n",
    "df['home_base'] = df.apply(lambda r: pivot_homeTeam_mean.loc[r['homeTeam_id'],'attendance'], axis=1)\n",
    "df['stadium_size'] = df.apply(lambda r: pivot_homeTeam_max.loc[r['homeTeam_id'],'attendance'], axis=1)\n",
    "df['early'] = df.apply(lambda r: r['time'].replace(':','') < \"1800\", axis=1)\n",
    "df['before2010'] = df.apply(lambda r: r['historic_season'].split('-')[0] < \"2010\", axis=1)\n",
    "\n",
    "\n",
    "# one hot encoding\n",
    "ohe_fields = ['home_country']\n",
    "\n",
    "for field in ohe_fields:\n",
    "    ohe = OneHotEncoder()\n",
    "    transformed = ohe.fit_transform(df[[field]])\n",
    "    df[ohe.categories_[0]] = transformed.toarray()\n",
    "\n",
    "# sort label to last index\n",
    "cols = list(df.columns)\n",
    "cols.append(cols.pop(cols.index('attendance')))\n",
    "df = df[cols]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2ea08e5",
   "metadata": {},
   "source": [
    "#### Train/Test Data - Normalization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "74e12f87",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np \n",
    "import pandas as pd \n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from sklearn.model_selection import train_test_split, cross_val_predict\n",
    "from sklearn import metrics\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "\n",
    "\n",
    "remove_columns = ['season_id', 'resultEntered', 'reversible', 'reschedule', 'homeGoals', 'awayGoals',\n",
    "                  'homeGoals2', 'awayGoals2', 'homeGoals3', 'awayGoals3', 'home', 'away', 'date', 'time',\n",
    "                  'id', 'homeTeam_id', 'awayTeam_id', 'historic_season',\n",
    "                  'home_country','home_lat','home_lon','away_lat','away_lon','away_country']\n",
    "feature_cols = list(set(df.columns[:-1]) - set(remove_columns))\n",
    "# feature_cols = ['weekday','weekend','home_base','distance','winter_season']\n",
    "label = 'attendance'\n",
    "\n",
    "\n",
    "X = df[feature_cols]  # Features\n",
    "y = df[label]  # Target variable\n",
    "\n",
    "X_train, X_test, y_train, y_test = train_test_split(\n",
    "    X, y, test_size=0.3, random_state=1)  # 70% training and 30% test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3a05ac61",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "from pandas import DataFrame,Series\n",
    "from sklearn import tree\n",
    "import matplotlib\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn import svm\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from mpl_toolkits.mplot3d import Axes3D\n",
    "import seaborn as sns\n",
    "from sklearn import neighbors\n",
    "from sklearn import linear_model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "76e774e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_Train=X_train.values\n",
    "X_Train=np.asarray(X_Train)\n",
    "\n",
    "# Finding normalised array of X_Train\n",
    "X_std=StandardScaler().fit_transform(X_Train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "45e08026",
   "metadata": {},
   "outputs": [],
   "source": [
    "number_of_samples = len(y_train)\n",
    "np.random.seed(0)\n",
    "random_indices = np.random.permutation(number_of_samples)\n",
    "num_training_samples = int(number_of_samples*0.75)\n",
    "x_train = X_Train[random_indices[:num_training_samples]]\n",
    "y_train=y[random_indices[:num_training_samples]]\n",
    "x_test=X_Train[random_indices[num_training_samples:]]\n",
    "y_test=y[random_indices[num_training_samples:]]\n",
    "y_Train=list(y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "470425b6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train error = 98.11885891315514 percent in Ridge Regression\n",
      "Test error = 100.86230814227264 percent in Ridge Regression\n"
     ]
    }
   ],
   "source": [
    "model=linear_model.Ridge()\n",
    "model.fit(x_train,y_train)\n",
    "y_predict=model.predict(x_train)\n",
    "\n",
    "error=0\n",
    "for i in range(len(y_Train)):\n",
    "    error+=(abs(y_Train[i]-y_predict[i])/y_Train[i])\n",
    "train_error_ridge=error/len(y_Train)*100\n",
    "print(\"Train error = \"'{}'.format(train_error_ridge)+\" percent in Ridge Regression\")\n",
    "\n",
    "Y_test=model.predict(x_test)\n",
    "y_Predict=list(y_test)\n",
    "\n",
    "error=0\n",
    "for i in range(len(y_test)):\n",
    "    error+=(abs(y_Predict[i]-Y_test[i])/y_Predict[i])\n",
    "test_error_ridge=error/len(Y_test)*100\n",
    "print(\"Test error = \"'{}'.format(test_error_ridge)+\" percent in Ridge Regression\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "88b4de2f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5, 1.0, 'Residual plot in Ridge Regression')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 29824 characters]",
      "text/plain": [
       "<Figure size 432x432 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "matplotlib.rcParams['figure.figsize'] = (6.0, 6.0)\n",
    "\n",
    "preds = pd.DataFrame({\"preds\":model.predict(x_train), \"true\":y_train})\n",
    "preds[\"residuals\"] = preds[\"true\"] - preds[\"preds\"]\n",
    "preds.plot(x = \"preds\", y = \"residuals\",kind = \"scatter\")\n",
    "plt.title(\"Residual plot in Ridge Regression\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f2f52314",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train error = 86.04732435905834 percent in Knn algorithm\n",
      "Test error = 29.305494469835775 percent in knn algorithm\n"
     ]
    }
   ],
   "source": [
    "n_neighbors=5\n",
    "knn=neighbors.KNeighborsRegressor(n_neighbors,weights='uniform')\n",
    "knn.fit(x_train,y_train)\n",
    "y1_knn=knn.predict(x_train)\n",
    "y1_knn=list(y1_knn)\n",
    "\n",
    "error=0\n",
    "for i in range(len(y_train)):\n",
    "    error+=(abs(y1_knn[i]-y_Train[i])/y_Train[i])\n",
    "train_error_knn=error/len(y_Train)*100\n",
    "print(\"Train error = \"+'{}'.format(train_error_knn)+\" percent\"+\" in Knn algorithm\")\n",
    "\n",
    "y2_knn=knn.predict(x_test)\n",
    "y2_knn=list(y2_knn)\n",
    "error=0\n",
    "for i in range(len(y_test)):\n",
    "    error+=(abs(y2_knn[i]-Y_test[i])/Y_test[i])\n",
    "test_error_knn=error/len(Y_test)*100\n",
    "print(\"Test error = \"'{}'.format(test_error_knn)+\" percent\"+\" in knn algorithm\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "06ed0796",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5, 1.0, 'Residual plot in Knn')"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 29216 characters]",
      "text/plain": [
       "<Figure size 432x432 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "matplotlib.rcParams['figure.figsize'] = (6.0, 6.0)\n",
    "preds = pd.DataFrame({\"preds\":knn.predict(x_train), \"true\":y_train})\n",
    "preds[\"residuals\"] = preds[\"true\"] - preds[\"preds\"]\n",
    "preds.plot(x = \"preds\", y = \"residuals\",kind = \"scatter\")\n",
    "plt.title(\"Residual plot in Knn\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "27215d99",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train error = 98.41770536088067 percent in Bayesian Regression\n",
      "Test error = 6.2704745296132876 percent in Bayesian Regression\n"
     ]
    }
   ],
   "source": [
    "reg = linear_model.BayesianRidge()\n",
    "reg.fit(x_train,y_train)\n",
    "y1_reg=reg.predict(x_train)\n",
    "y1_reg=list(y1_reg)\n",
    "y2_reg=reg.predict(x_test)\n",
    "y2_reg=list(y2_reg)\n",
    "\n",
    "error=0\n",
    "for i in range(len(y_train)):\n",
    "    error+=(abs(y1_reg[i]-y_Train[i])/y_Train[i])\n",
    "train_error_bay=error/len(y_Train)*100\n",
    "print(\"Train error = \"+'{}'.format(train_error_bay)+\" percent\"+\" in Bayesian Regression\")\n",
    "\n",
    "error=0\n",
    "for i in range(len(y_test)):\n",
    "    error+=(abs(y2_reg[i]-Y_test[i])/Y_test[i])\n",
    "test_error_bay=(error/len(Y_test))*100\n",
    "print(\"Test error = \"+'{}'.format(test_error_bay)+\" percent\"+\" in Bayesian Regression\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "c68c7026",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5, 1.0, 'Residual plot in Bayesian Regression')"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 26712 characters]",
      "text/plain": [
       "<Figure size 432x432 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "matplotlib.rcParams['figure.figsize'] = (6.0, 6.0)\n",
    "preds = pd.DataFrame({\"preds\":reg.predict(x_train), \"true\":y_train})\n",
    "preds[\"residuals\"] = preds[\"true\"] - preds[\"preds\"]\n",
    "preds.plot(x = \"preds\", y = \"residuals\",kind = \"scatter\")\n",
    "plt.title(\"Residual plot in Bayesian Regression\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a3c76976",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train error = 98.15574267963966 percent in Decision Tree Regressor\n",
      "Test error = 7.1677452486549855 percent in Decision Tree Regressor\n"
     ]
    }
   ],
   "source": [
    "dec = tree.DecisionTreeRegressor(max_depth=1)\n",
    "dec.fit(x_train,y_train)\n",
    "y1_dec=dec.predict(x_train)\n",
    "y1_dec=list(y1_dec)\n",
    "y2_dec=dec.predict(x_test)\n",
    "y2_dec=list(y2_dec)\n",
    "\n",
    "error=0\n",
    "for i in range(len(y_train)):\n",
    "    error+=(abs(y1_dec[i]-y_Train[i])/y_Train[i])\n",
    "train_error_tree=error/len(y_Train)*100\n",
    "print(\"Train error = \"+'{}'.format(train_error_tree)+\" percent\"+\" in Decision Tree Regressor\")\n",
    "\n",
    "error=0\n",
    "for i in range(len(y_test)):\n",
    "    error+=(abs(y1_dec[i]-Y_test[i])/Y_test[i])\n",
    "test_error_tree=error/len(Y_test)*100\n",
    "print(\"Test error = \"'{}'.format(test_error_tree)+\" percent in Decision Tree Regressor\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "52ec5460",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5, 1.0, 'Residual plot in Decision Tree')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 15424 characters]",
      "text/plain": [
       "<Figure size 432x432 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "matplotlib.rcParams['figure.figsize'] = (6.0, 6.0)\n",
    "preds = pd.DataFrame({\"preds\":dec.predict(x_train), \"true\":y_train})\n",
    "preds[\"residuals\"] = preds[\"true\"] - preds[\"preds\"]\n",
    "preds.plot(x = \"preds\", y = \"residuals\",kind = \"scatter\")\n",
    "plt.title(\"Residual plot in Decision Tree\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f2b28bd7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train error = 73.1224560206315 percent in SVM Regressor\n",
      "Test error = 21.522381238558573 percent in SVM Regressor\n"
     ]
    }
   ],
   "source": [
    "svm_reg=svm.SVR()\n",
    "svm_reg.fit(x_train,y_train)\n",
    "y1_svm=svm_reg.predict(x_train)\n",
    "y1_svm=list(y1_svm)\n",
    "y2_svm=svm_reg.predict(x_test)\n",
    "y2_svm=list(y2_svm)\n",
    "\n",
    "error=0\n",
    "for i in range(len(y_train)):\n",
    "    error+=(abs(y1_svm[i]-y_Train[i])/y_Train[i])\n",
    "train_error_svm=error/len(y_Train)*100\n",
    "print(\"Train error = \"+'{}'.format(train_error_svm)+\" percent\"+\" in SVM Regressor\")\n",
    "\n",
    "error=0\n",
    "for i in range(len(y_test)):\n",
    "    error+=(abs(y2_svm[i]-Y_test[i])/Y_test[i])\n",
    "test_error_svm=error/len(Y_test)*100\n",
    "print(\"Test error = \"'{}'.format(test_error_svm)+\" percent in SVM Regressor\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "71819737",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5, 1.0, 'Residual plot in SVM')"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 29400 characters]",
      "text/plain": [
       "<Figure size 432x432 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "matplotlib.rcParams['figure.figsize'] = (6.0, 6.0)\n",
    "preds = pd.DataFrame({\"preds\":knn.predict(x_train), \"true\":y_train})\n",
    "preds[\"residuals\"] = preds[\"true\"] - preds[\"preds\"]\n",
    "preds.plot(x = \"preds\", y = \"residuals\",kind = \"scatter\")\n",
    "plt.title(\"Residual plot in SVM\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "ef357c92",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Train Error</th>\n",
       "      <th>Test Error</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Ridge Regression</th>\n",
       "      <td>98.118859</td>\n",
       "      <td>100.862308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Knn</th>\n",
       "      <td>86.047324</td>\n",
       "      <td>29.305494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bayesian Regression</th>\n",
       "      <td>98.417705</td>\n",
       "      <td>6.270475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Decision Tree</th>\n",
       "      <td>98.155743</td>\n",
       "      <td>7.167745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SVM</th>\n",
       "      <td>73.122456</td>\n",
       "      <td>21.522381</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Train Error  Test Error\n",
       "Ridge Regression       98.118859  100.862308\n",
       "Knn                    86.047324   29.305494\n",
       "Bayesian Regression    98.417705    6.270475\n",
       "Decision Tree          98.155743    7.167745\n",
       "SVM                    73.122456   21.522381"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_error=[train_error_ridge,train_error_knn,train_error_bay,train_error_tree,train_error_svm]\n",
    "test_error=[test_error_ridge,test_error_knn,test_error_bay,test_error_tree,test_error_svm]\n",
    "\n",
    "col={'Train Error':train_error,'Test Error':test_error}\n",
    "models=['Ridge Regression','Knn','Bayesian Regression','Decision Tree','SVM']\n",
    "df=DataFrame(data=col,index=models)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fbe3ec2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.13 ('leagues')",
   "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.9"
  },
  "vscode": {
   "interpreter": {
    "hash": "a07b7f3079ca8c056705d3c757c4f3f92f9509f33eeab9ad5420dacec37bc01a"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
