Model _BQ.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "698FKRL_YSx7"
   },
   "source": [
    "# **Pamcrash baseline model**\n",
    "\n",
    "The goal of this Notebook is to provide a baseline solution for the Challenge to help beginners iterate from a working and ready-to-use solution.\n",
    "\n",
    "Made by Hamza Jebbar (DIRE) - November 2020\n",
    "\n",
    "Last Update: January 25, 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use this code if running on GCP's Labs for Renault"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery train_df\n",
    "SELECT\n",
    "*\n",
    "FROM `challenge.training_data`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "q81V-ESMa7Ph"
   },
   "outputs": [],
   "source": [
    "# Use this code if running on Google Colaboratory instead, or your own Jupyter Notebook\n",
    "#import pandas as pd\n",
    "#train_df = pd.read_csv(\"https://drive.google.com/u/0/uc?id=1nYfF1tsQtEo0YBAlu0iE5qohp7kAKe2c&export=download\", sep=\";\")\n",
    "#train_df = train_df.rename(columns={\"TZC FINAL\": \"TZC_FINAL\"}, errors=\"raise\")\n",
    "#train_df = train_df.rename(columns={\"MPLINK+NTNUL\": \"MPLINK_NTNU\"}, errors=\"raise\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "B_uCEESIY-N7"
   },
   "source": [
    "### **Loading the libraries required for the analysis and plots**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "qWNk6J6YwZaS"
   },
   "source": [
    "Install machine learning libraries if they're not already there on your system.\r\n",
    "Notice how you can run system commands by starting the line with an exclamation mark. In case the output is \"pip3 not found\", try and replace it with \"pip\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "RnbRhSO5fTOk",
    "outputId": "1c739bdb-c0ff-462e-ca46-2b7d00ba4e0d"
   },
   "outputs": [],
   "source": [
    "!pip3 install xgboost\n",
    "!pip3 install lightgbm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "0JKQMndIwSxD"
   },
   "outputs": [],
   "source": [
    "import numpy as np\r\n",
    "import pandas as pd\r\n",
    "from sklearn import preprocessing\r\n",
    "from sklearn.model_selection import train_test_split\r\n",
    "from sklearn.metrics import r2_score\r\n",
    "from sklearn.preprocessing import LabelEncoder\r\n",
    "from sklearn.preprocessing import MinMaxScaler\r\n",
    "from sklearn.metrics import mean_squared_error\r\n",
    "from sklearn.metrics import mean_absolute_error\r\n",
    "from sklearn.model_selection import GridSearchCV, KFold\r\n",
    "from xgboost import XGBRegressor\r\n",
    "from lightgbm import LGBMRegressor\r\n",
    "import matplotlib\r\n",
    "from matplotlib import pyplot\r\n",
    "import seaborn as sns\r\n",
    "\r\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "RS-byD4z3eHa"
   },
   "source": [
    "## **Data preprocessing**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YUqFR_rr2GYK"
   },
   "source": [
    "### **Load the data and take a quick look at the various columns**\n",
    "\n",
    "For a more thorough analysis, please refer to \"Analyze Dataset.ipynb\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 215
    },
    "id": "Rjn0_0rmfuCW",
    "outputId": "a3800147-a0eb-4447-977b-8095923ff2fe"
   },
   "outputs": [],
   "source": [
    "df = train_df.drop([\"JOBID\",\"DAY\",\"HOUR\",\"TZC_FINAL\"], axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "DP5WRn2u2QfU"
   },
   "source": [
    "### **Encoding the categorical columns**\n",
    "In order to be able to use the categorical columns, we must encode their values and transform them into numerical values.\n",
    "\n",
    "For example [YES, NO] would be [1, 0]\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 215
    },
    "id": "1h6WnYVwfxwL",
    "outputId": "2becfa87-4de6-42ec-fdf2-dee74ae0e39c"
   },
   "outputs": [],
   "source": [
    "numerical = list(df.describe().columns)\n",
    "categorical = [col for col in df.columns if col not in numerical]\n",
    "encoders = {}\n",
    "for column in categorical:\n",
    "  encoder = LabelEncoder().fit(df[column])\n",
    "  df[column] = encoder.transform(df[column])\n",
    "  encoders[column]=encoder\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "YyA23l6c3S8d"
   },
   "source": [
    "### **Preparing the data**\n",
    "We extract the output y \"ELAPSEDTIME\" from the input X.\n",
    "Due to the big difference in the columns scale, we choose to scale the data using MinMaxScaler so that all the columns values will be between 0 and 1. This results in all the columns having the same effect on the output result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 215
    },
    "id": "gu5Bnqsf3lIl",
    "outputId": "9234a058-359c-4af1-c70e-fd9e67d9ce0c"
   },
   "outputs": [],
   "source": [
    "y = df[\"ELAPSEDTIME\"]\n",
    "X = df.drop([\"ELAPSEDTIME\"], axis=1)\n",
    "scaler = MinMaxScaler().fit(X)\n",
    "X_scaled = pd.DataFrame(data = scaler.transform(X), columns = X.columns)\n",
    "# split the data into two categories, one for training and the other for testing\n",
    "X_train_sc, X_test_sc, _, _ = train_test_split(X_scaled, y, test_size=0.33, random_state=42)\n",
    "# We do the same split on the unscalled data, we'll use these ones later for the plots\n",
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.33, random_state=42)\n",
    "# Display the first samples of the dataset\n",
    "X_scaled.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2EA4hvey439v"
   },
   "source": [
    "## **Creating a prediction model**\n",
    "\n",
    "First of all let's define a function that computes three metrics that we'll use next to evaluate our models.\n",
    "\n",
    "**RMSE**: root-mean-square deviation is a metric that computes the quadratic mean of the difference between the predicted values and the observed ones.\n",
    "\n",
    "**MAE**: mean absolute error computes the absolute error between the predicted values and the observed ones.\n",
    "\n",
    "**R2_score**: a score between 0 and 1 that indicates to which degree tha input variables explain the output.\n",
    "\n",
    "The smaller RMSE and MAE and the bigger R2_score, the better the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ePfW8aF3dyFt"
   },
   "outputs": [],
   "source": [
    "def compute_metrics(y,y_pred):\n",
    "  RMSE = mean_squared_error(y, y_pred, squared=False)\n",
    "  MAE = mean_absolute_error(y, y_pred)\n",
    "  R2 = r2_score(y,y_pred)\n",
    "  return RMSE, MAE, R2, \"RMSE: \"+str(round(RMSE,4))+\"   |   MAE: \"+str(round(MAE,4))+\"   |   R2: \"+str(round(R2,4))+\"  |   size: \"+str(len(y))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "F4cs4QnHnlyo"
   },
   "source": [
    "### **First try : LightGBM**\n",
    "We split the data into two categories, a training set that we feed to the model, and a test set to evaluate it.\n",
    "\n",
    "We start by creating an instance of the model, train it, then use the predict function to make the prediction and the r2_score metric to evaluate the model's accuracy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "dezxVwN8nmgT",
    "outputId": "d0bdcf28-bb2a-4d56-8d60-4bdd3c33eadf"
   },
   "outputs": [],
   "source": [
    "lgbm = LGBMRegressor()\n",
    "# Example with tweaking parameters\n",
    "# lgbm = LGBMRegressor(n_estimators=300, learning_rate=0.3, reg_lambda=1)\n",
    "# Train the model\n",
    "lgbm.fit(X_train_sc, y_train)\n",
    "# Print the model results\n",
    "print(\"LGBM:\")\n",
    "RMSE, MAE, R2, ALL = compute_metrics(lgbm.predict(X_train_sc), y_train)\n",
    "print(\"Results on training data: \"+ALL)\n",
    "RMSE, MAE, R2, ALL = compute_metrics(lgbm.predict(X_test_sc), y_test)\n",
    "print(\"Results on test data: \" + ALL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "G0JVAHRgsZz6"
   },
   "source": [
    "### **Second Try: XGBoost**\n",
    "\n",
    "This time, we try to use the XGBoost model to compare its results with the previous one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "36izvlNNtCq4",
    "outputId": "ce73bc63-e359-4818-ed06-25d3ca72cf0e"
   },
   "outputs": [],
   "source": [
    "xgb1 = XGBRegressor()\n",
    "xgb1.fit(X_train_sc, y_train)\n",
    "print(\"XGBOOST:\")\n",
    "RMSE, MAE, R2, ALL = compute_metrics(xgb1.predict(X_train_sc), y_train)\n",
    "print(\"Results on training data: \" + ALL)\n",
    "RMSE, MAE, R2, ALL = compute_metrics(xgb1.predict(X_test_sc), y_test)\n",
    "print(\"Results on test data: \" + ALL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "t3VKcoMJtFGj"
   },
   "source": [
    "####**Comparing the metrics**\n",
    "\n",
    "At first sight and based on the evalutation metrics, we can easily conclude that the LightGBM model is doing much better than XGBoost and therefore should be selected.\n",
    "\n",
    "However, further analysis (that won't be detailed in this notebook) have shown that XGBoost can do better with some **Hyperparameter Tuning** (Next section) whilst LGBM's performance decreases, and thus we choose to proceed with XGBoost for the rest of the notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1mC7aicI6bXV"
   },
   "source": [
    "### **XGBoost Regressor and the Hyperparameter tuning**\n",
    "We choose to use the XGBoost Regressor model to solve this problem.\n",
    "Since XGBoost has many input parameters (called \"hyperparameters\"), we'll iterate on them to find the combination which yields the best results. This is called \"hyperparameter tuning\".\n",
    "\n",
    "We use the GridSearch method to tune these hyperpameters. For each one, we provide a number of values to be tested using the Kfold method.\n",
    "\n",
    "KFold is a cross-validation method that splits randomly the training data to a number of partitions, and each time a partition will be used for the test while the others serve as training data. For each combination of hyperparameters, the final score is the mean of all the scores computed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "CFsS5ckVgCZT",
    "outputId": "ac4f1197-6053-4b04-ba03-0633ef935da9"
   },
   "outputs": [],
   "source": [
    "model = XGBRegressor()\n",
    "# Among the possible hyperparameters, we chose to iterate over 2: n_estimators and learning_rate\n",
    "# n_estimators is the number of gradient boosting trees\n",
    "# learning_rate is the step taken by the optimization algorithm on each iteration\n",
    "# You can try to add more parameters or possible values and check if it gives better results\n",
    "n_estimators = [10, 200, 300]\n",
    "learning_rate = [0.01, 0.1, 0.3]\n",
    "param_grid = dict(learning_rate=learning_rate, n_estimators=n_estimators) ## hyperparameter's dictionary\n",
    "kfold = KFold(n_splits=4, shuffle=True, random_state=7) ## n_splits: number of partitions after the split\n",
    "grid_search = GridSearchCV(model, param_grid, n_jobs=-1, cv=kfold)\n",
    "grid_result = grid_search.fit(X_scaled, y)\n",
    "# summarize results\n",
    "print(\"Best: %f using %s\" % (grid_result.best_score_, grid_result.best_params_))\n",
    "means = grid_result.cv_results_['mean_test_score']\n",
    "stds = grid_result.cv_results_['std_test_score']\n",
    "params = grid_result.cv_results_['params']\n",
    "print(\"\\nAll tests:\")\n",
    "for mean, stdev, param in zip(means, stds, params):\n",
    "\tprint(\"%f (%f) with: %r\" % (mean, stdev, param))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "fnpLQt2KB4ed"
   },
   "source": [
    "**Interpreting the results**\n",
    "\n",
    "As we can see, the combination : n_estimators=300, learning_rate=0.3 gives the best results and thus will be chosen for the model."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "TZX_Oqa--CSA"
   },
   "source": [
    "**Plotting the curve**\n",
    "\n",
    "This curve shows the evolution of the model's precision with each change in the hyperparameter. On the x-axis we have the number of estimators, and the y-axis represents the R2_score. The best value is 1 (100% accuracy) and the worst is 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 498
    },
    "id": "nguWD75B-DYV",
    "outputId": "887b9eff-d42f-4256-9173-521fa20827e1"
   },
   "outputs": [],
   "source": [
    "# plot\n",
    "scores = np.array(means).reshape(len(learning_rate), len(n_estimators))\n",
    "pyplot.figure(figsize=(12,8))\n",
    "for i, value in enumerate(learning_rate):\n",
    "    pyplot.plot(n_estimators, scores[i], label='learning_rate: ' + str(value))\n",
    "ax = pyplot.gca()\n",
    "sns.set_style('whitegrid')\n",
    "pyplot.legend()\n",
    "pyplot.xlabel('n_estimators')\n",
    "pyplot.ylabel('R2_score (higher is better)')\n",
    "pyplot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "CFZHljUqE4Eh"
   },
   "source": [
    "## **Evalutating the model's results**\n",
    "\n",
    "After choosing our best hyperparameters and training the model, let's evaluate its results on the test set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 215
    },
    "id": "imBFv8xLE28T",
    "outputId": "9a27456f-4ec7-426a-dc80-75fb20e1b4c8"
   },
   "outputs": [],
   "source": [
    "# We create a new DataFrame that we'll use for the plots\n",
    "data = pd.DataFrame(data=X_test.copy(), columns=X.columns)\n",
    "data[\"ELAPSEDTIME\"] = y_test\n",
    "data[\"Y_PRED\"] = grid_result.predict(X_test_sc)\n",
    "data[\"residuals\"] = data[\"Y_PRED\"] - data[\"ELAPSEDTIME\"]\n",
    "data[\"delta_all\"] = 100 * (data[\"Y_PRED\"] - data[\"ELAPSEDTIME\"]) / data[\"ELAPSEDTIME\"]\n",
    "data[\"delta\"] = data[\"delta_all\"].apply(abs)\n",
    "# We transform the values of the \"PERFORMANCE\" column from numerical [0,1...] to categorical\n",
    "data[\"PERFORMANCE\"] = encoders[\"PERFORMANCE\"].inverse_transform(data[\"PERFORMANCE\"])\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jAtz7aeuKNyf"
   },
   "source": [
    "**Plotting the average error per servers' number**\n",
    "\n",
    "Plotting a curve of the average %error as a function of the servers' number can give us an idea about the model's predictions precision for each number of servers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "z9vGOv3yIl4D"
   },
   "outputs": [],
   "source": [
    "def plot_line(data, x=\"NBSERVERS\", y=\"delta\", y_pred=\"Y_PRED\", h=None, s=None):\n",
    "  # Plotting the curve of the average error in % per number of servers\n",
    "  sns.set_style('whitegrid')\n",
    "  g = sns.relplot(x=x, y=y, hue=h, style=s, ci=None, kind=\"line\", data=data)\n",
    "  g.fig.autofmt_xdate()\n",
    "  g.fig.set_size_inches(12, 8)\n",
    "  axes = g.axes.flatten()\n",
    "  RMSE, MAE, R2, ALL = compute_metrics(data[\"ELAPSEDTIME\"], data[y_pred])\n",
    "  print(ALL)\n",
    "  axes[0].set_title(ALL+\"\\n\"+\"Servers' number frequency:\"+str(data[x].value_counts().to_dict()))\n",
    "  axes[0].set(xlabel=\"Servers number\", ylabel=\"Error in % (lower is better)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 555
    },
    "id": "_i4pbU83koav",
    "outputId": "dd21da8a-b92e-4c58-deea-b93ecbf87593"
   },
   "outputs": [],
   "source": [
    "plot_line(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "mrBYprI8KmB4"
   },
   "source": [
    "**Error categories in a histogram**\n",
    "\n",
    "We transform the error column from a continuous variable to a discretized variable, the value of which will belong to a category (for exemple ]15%,30%[ error). This will help us determine the density of each category."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "s4arq56E-AzL"
   },
   "outputs": [],
   "source": [
    "def plot_hist(data,x=\"delta_disc\",color=\"blue\",size_x=12,size_y=8):\n",
    "  sns.set_style('whitegrid')\n",
    "  g = sns.displot(data=data, x=x, color=color, discrete=True)\n",
    "  axes = g.axes.flatten()\n",
    "  g.fig.set_size_inches(size_x, size_y)\n",
    "  axes[0].set_ylabel(\"Errors' Density\")\n",
    "  axes[0].set_xlabel(\"Errors' Categories\")\n",
    "  return axes[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "048AQ1tOv75F"
   },
   "outputs": [],
   "source": [
    "def discretize(data,bins):\n",
    "  data = np.asarray(data)\n",
    "  data = np.digitize(data,bins)\n",
    "  return data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 620
    },
    "id": "iIenHplx-GpO",
    "outputId": "ee747cc8-b8bb-496e-b52b-f00a39fe431a"
   },
   "outputs": [],
   "source": [
    "data[\"delta_disc\"] = discretize(data[\"delta_all\"],[-50,-30,-15,15,30,50])\n",
    "axes = plot_hist(data)\n",
    "RMSE,MAE,R2,ALL = compute_metrics(data[\"ELAPSEDTIME\"], data[\"Y_PRED\"])\n",
    "dic = {0:\"]-inf,-50%[\",1:\"]-50%,-30%[\",2:\"]-30%,-15%[\",3:\"]-15%,15%[\",4:\"]15%,30%[\",5:\"]30%,50%[\",6:\"]50%,+inf%[\"}\n",
    "axes.set_title(ALL+\"\\n\"+str(dic))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ZzkUWO-pLPLl"
   },
   "source": [
    "**Plotting the prediction as a function of the ground truth**\n",
    "\n",
    "This plot will help us see how far our predictions were from the ground truth."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 623
    },
    "id": "UZnNYiltgVnw",
    "outputId": "73e1dd8e-d55a-435c-a68e-83cbed4a0d9f"
   },
   "outputs": [],
   "source": [
    "fig, ax = pyplot.subplots()\n",
    "fig.set_size_inches(18, 10)\n",
    "sns.lineplot(x=\"ELAPSEDTIME\", y=\"ELAPSEDTIME\", data=data)\n",
    "sns.lineplot(x=\"ELAPSEDTIME\", y=\"Y_PRED\", data=data)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cWHp1jvkxgRW"
   },
   "source": [
    "As you can see, error is low when ELAPSEDTIME is lower than about 70.000 then things start to go wild.\n",
    "\n",
    "We can arguably attribute the increase in the prediction's error when ELAPSEDTIME is bigger than 70000 to an unbalance in ELAPSEDTIME's values distribution, as we can see in the histogram bellow, the density of ELAPSEDTIME values becomes smaller and smaller for the values >70000."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "n5XRWnGIMUVu"
   },
   "source": [
    "**Boxplot**\n",
    "\n",
    "The boxplot helps us see the errors' distribution for each type of simulation (column \"PERFORMANCE\").\n",
    "\n",
    "The first line of the box represents 25% of the population, the second 50% and the third 75%,  the closer they are to the 0% line, the better the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 637
    },
    "id": "Fn79DpMvo80L",
    "outputId": "9d5c9da3-9c15-44cb-a622-3fca4c73a2c3"
   },
   "outputs": [],
   "source": [
    "## This line's job is to delete the rows that have an error >500%, they're called the OUTLAYERS\n",
    "## We delete them only to have a better view at what the boxplot looks like\n",
    "## We do not recommend doing that in a real project\n",
    "data = data[~(data[\"delta\"]>500)]\n",
    "################\n",
    "sns.set_theme(style=\"ticks\", palette=\"pastel\")\n",
    "sns.set_style('whitegrid')\n",
    "# Draw a nested boxplot to show bills by day and time\n",
    "g = sns.boxplot(x=\"PERFORMANCE\", y=\"delta\", data=data)\n",
    "g.set(ylabel='Error in %')\n",
    "axes = g.axes\n",
    "axes.figure.set_size_inches(15, 10)\n",
    "axes.set_title(data[\"PERFORMANCE\"].value_counts().to_dict())\n",
    "sns.despine(offset=10, trim=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "uTiJXISS-GXE"
   },
   "source": [
    "**Joint Plot with KDE**\n",
    "\n",
    "The Kernel Density Estimate allows us to have a good idea about the density of our variables, we can see that we have a lot of data centered around the 20.000 value (ELAPSEDTIME axis) with 0 residuals/errors (residuals axis), meaning that our model's prediction is close to the ground truth."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 490
    },
    "id": "gtAcQbVmOjQY",
    "outputId": "3a04cf99-4658-4ccc-c89c-73b48a53091f"
   },
   "outputs": [],
   "source": [
    "sns.jointplot('residuals', 'ELAPSEDTIME', data=data,\n",
    "              kind=\"kde\", color=\"g\");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "QUhPC1lhAGNr"
   },
   "source": [
    "**Saving the model**\n",
    "\n",
    "We can save the model using pickle, but in order to be able to use it, we need to save the encoder and scaler that we used on the data as well. That will allow us to apply the same preprocessing on the data that we will predict in the future."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5gfyDr7PPLyx"
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "with open('label_encoder.pkl', 'wb') as output:\n",
    "    pickle.dump(encoders, output, pickle.HIGHEST_PROTOCOL)\n",
    "with open('scaler.pkl', 'wb') as output:\n",
    "    pickle.dump(encoders, output, pickle.HIGHEST_PROTOCOL)\n",
    "with open('model.pkl', 'wb') as output:\n",
    "    pickle.dump(grid_result, output, pickle.HIGHEST_PROTOCOL)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jD66Z5R2Bomg"
   },
   "source": [
    "## **Predicting the competition's data**\n",
    "\n",
    "This section is reserved for you, you can choose a model and train it using the methods that we used in the sections above, then make a prediction on the data that we will provide for you."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "g1x5ztZxHxQS"
   },
   "source": [
    "### Preprocessing function:\r\n",
    "\r\n",
    "This preprocessing method will help you prepare your data for prediction. Of course you're not obligated to use it and you can create your own function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "-EgnyrP9ImC8"
   },
   "outputs": [],
   "source": [
    "def prepare_data(df,encoders,scaler):\r\n",
    "  numerical = list(df.describe().columns)\r\n",
    "  categorical = [col for col in df.columns if col not in numerical]\r\n",
    "  for column in categorical:\r\n",
    "    df[column] = encoders[column].transform(df[column])\r\n",
    "  X_scaled = pd.DataFrame(data = scaler.transform(df), columns = df.columns)\r\n",
    "  return X_scaled"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ifeV6OR4rYeJ"
   },
   "outputs": [],
   "source": [
    "# In this cell you'll choose your model and make the prediciton, we loaded the data for you\n",
    "df = pd.read_csv(\"https://drive.google.com/u/0/uc?id=15QutxBe6nQxtcUN0crCzmyz43TpeGyCu&export=download\", sep=\";\")\n",
    "df = df.rename(columns={\"MPLINK+NTNU\": \"MPLINK_NTNU\"}, errors=\"raise\")\n",
    "competition = df.drop([\"JOBID\", \"DAY\", \"HOUR\"], axis=1)\n",
    "\n",
    "# Apply the same preprocessing methods you used on the training data on the competition data\n",
    "# TO GO FURTHER, try and apply your own preprocessing workflow\n",
    "X_competition = prepare_data(competition, encoders, scaler)\n",
    "\n",
    "# Choose your model, or uncomment the second line to use our pretrained model to make the predictions\n",
    "# TO GO FURTHER, try with a custom model built by yourself\n",
    "model = grid_result\n",
    "\n",
    "# Train your model here in case you created your own model\n",
    "# Remeber, use on the training data the same preprocessing method you used on the competition data above\n",
    "X_train = X\n",
    "y_train = y\n",
    "\n",
    "#Save your prediction here\n",
    "df[\"prediction\"] = model.predict(X_competition)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "PTCxfGkhCydx"
   },
   "source": [
    "### **Saving the prediction**\n",
    "\n",
    "This section is reserved to save your results in a file that you'll need to upload in the competition's application."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 17
    },
    "id": "Je5T5haxDM-k",
    "outputId": "71434d59-8f55-43dc-c5e5-ed823922d578"
   },
   "outputs": [],
   "source": [
    "data_comp = pd.DataFrame(data=df[\"JOBID\"], columns=[\"JOBID\"])\n",
    "data_comp[\"PREDICTION\"] = df[\"prediction\"]\n",
    "# Saving and downloading the prediction file\n",
    "path = \"prediction.csv\"\n",
    "data_comp.to_csv(path, index=False, sep=\";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Model _BQ.ipynb",
   "provenance": []
  },
  "environment": {
   "name": "tf2-cpu.2-3.mnightly-2021-01-20-debian-10-test",
   "type": "gcloud",
   "uri": "gcr.io/deeplearning-platform-release/tf2-cpu.2-3:mnightly-2021-01-20-debian-10-test"
  },
  "kernelspec": {
   "display_name": "Python [conda env:root] *",
   "language": "python",
   "name": "conda-root-py"
  },
  "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.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}