feature_engineering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Engineering\n",
    "\n",
    "`Author: YUAN Yanzhe`\n",
    "\n",
    "- In this notebook, feature engineering is applied to the raw data and output feature.csv for the later modeling.\n",
    "\n",
    "- The codes are originally run in Google Colab\n",
    "    - use packages like re, datetime to extract features from \n",
    "    - use wwo-hist API to grab weather data \n",
    "        - [wwo-hist github](https://github.com/ekapope/WorldWeatherOnline)\n",
    "        - [wwo-hist tutorial](https://towardsdatascience.com/obtain-historical-weather-forecast-data-in-csv-format-using-python-5a6c090fc828)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8JIJmHs1yaRf"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 417
    },
    "executionInfo": {
     "elapsed": 4301,
     "status": "ok",
     "timestamp": 1607084465133,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "LdabBq2syp23",
    "outputId": "e0d36268-db4b-401a-f100-d6b51b66093b"
   },
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>speed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2017 0:00</td>\n",
       "      <td>43.002930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1/1/2017 1:00</td>\n",
       "      <td>46.118696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1/1/2017 2:00</td>\n",
       "      <td>44.294158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1/1/2017 3:00</td>\n",
       "      <td>41.067468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1/1/2017 4:00</td>\n",
       "      <td>46.448653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14001</th>\n",
       "      <td>14001</td>\n",
       "      <td>31/12/2018 12:00</td>\n",
       "      <td>19.865269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14002</th>\n",
       "      <td>14002</td>\n",
       "      <td>31/12/2018 15:00</td>\n",
       "      <td>17.820375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14003</th>\n",
       "      <td>14003</td>\n",
       "      <td>31/12/2018 16:00</td>\n",
       "      <td>12.501851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14004</th>\n",
       "      <td>14004</td>\n",
       "      <td>31/12/2018 18:00</td>\n",
       "      <td>15.979319</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14005</th>\n",
       "      <td>14005</td>\n",
       "      <td>31/12/2018 20:00</td>\n",
       "      <td>40.594183</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14006 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id              date      speed\n",
       "0          0     1/1/2017 0:00  43.002930\n",
       "1          1     1/1/2017 1:00  46.118696\n",
       "2          2     1/1/2017 2:00  44.294158\n",
       "3          3     1/1/2017 3:00  41.067468\n",
       "4          4     1/1/2017 4:00  46.448653\n",
       "...      ...               ...        ...\n",
       "14001  14001  31/12/2018 12:00  19.865269\n",
       "14002  14002  31/12/2018 15:00  17.820375\n",
       "14003  14003  31/12/2018 16:00  12.501851\n",
       "14004  14004  31/12/2018 18:00  15.979319\n",
       "14005  14005  31/12/2018 20:00  40.594183\n",
       "\n",
       "[14006 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load our original data\n",
    "train_data = pd.read_csv('/content/drive/My Drive/5001_kaggle/train.csv')\n",
    "test_data = pd.read_csv('/content/drive/My Drive/5001_kaggle/test.csv')\n",
    "sub_data = pd.read_csv('/content/drive/My Drive/5001_kaggle/sampleSubmission.csv')\n",
    "train_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Feature Engineering\n",
    "\n",
    "### Obtain Additional Features\n",
    "\n",
    "- weather conditions can effect traffic, so I obtain weather data from wwo-hist API\n",
    "- select the following features to build weather_features\n",
    "    - temperature\n",
    "    - visibility\n",
    "    - wind direction\n",
    "    - wind speed\n",
    "    - humidity\n",
    "    - cloudcover\n",
    "    - wind chill temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 5191,
     "status": "ok",
     "timestamp": 1607088822206,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "Ws3GbYIz9TLB",
    "outputId": "57482642-8dcd-44a6-a470-fd73c26f87b2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting wwo-hist\n",
      "  Downloading https://files.pythonhosted.org/packages/5a/b4/19a4d6a0d131567cf4b2ffa3758710d867f7d7d3f0c6f94bd63fadf1d02a/wwo_hist-0.0.5-py3-none-any.whl\n",
      "Installing collected packages: wwo-hist\n",
      "Successfully installed wwo-hist-0.0.5\n"
     ]
    }
   ],
   "source": [
    "!pip install wwo-hist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 24926,
     "status": "ok",
     "timestamp": 1607092502323,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "MRHBb1mEKBJT",
    "outputId": "20bd768d-9957-408b-b9dd-0d6b07a50609"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "Retrieving weather data for hongkong\n",
      "\n",
      "\n",
      "Currently retrieving data for hongkong: from 2017-01-01 to 2017-01-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:01.311524\n",
      "Currently retrieving data for hongkong: from 2017-02-01 to 2017-02-28\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:02.252394\n",
      "Currently retrieving data for hongkong: from 2017-03-01 to 2017-03-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:03.235293\n",
      "Currently retrieving data for hongkong: from 2017-04-01 to 2017-04-30\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:04.215726\n",
      "Currently retrieving data for hongkong: from 2017-05-01 to 2017-05-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:05.200638\n",
      "Currently retrieving data for hongkong: from 2017-06-01 to 2017-06-30\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:06.207318\n",
      "Currently retrieving data for hongkong: from 2017-07-01 to 2017-07-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:07.282019\n",
      "Currently retrieving data for hongkong: from 2017-08-01 to 2017-08-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:08.293757\n",
      "Currently retrieving data for hongkong: from 2017-09-01 to 2017-09-30\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:09.287504\n",
      "Currently retrieving data for hongkong: from 2017-10-01 to 2017-10-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:10.262587\n",
      "Currently retrieving data for hongkong: from 2017-11-01 to 2017-11-30\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:11.261223\n",
      "Currently retrieving data for hongkong: from 2017-12-01 to 2017-12-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:12.247742\n",
      "Currently retrieving data for hongkong: from 2018-01-01 to 2018-01-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:13.298002\n",
      "Currently retrieving data for hongkong: from 2018-02-01 to 2018-02-28\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:14.242171\n",
      "Currently retrieving data for hongkong: from 2018-03-01 to 2018-03-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:15.196873\n",
      "Currently retrieving data for hongkong: from 2018-04-01 to 2018-04-30\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:16.147031\n",
      "Currently retrieving data for hongkong: from 2018-05-01 to 2018-05-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:17.087197\n",
      "Currently retrieving data for hongkong: from 2018-06-01 to 2018-06-30\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:18.120888\n",
      "Currently retrieving data for hongkong: from 2018-07-01 to 2018-07-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:19.090285\n",
      "Currently retrieving data for hongkong: from 2018-08-01 to 2018-08-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:20.075850\n",
      "Currently retrieving data for hongkong: from 2018-09-01 to 2018-09-30\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:21.039997\n",
      "Currently retrieving data for hongkong: from 2018-10-01 to 2018-10-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:22.022038\n",
      "Currently retrieving data for hongkong: from 2018-11-01 to 2018-11-30\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:23.046258\n",
      "Currently retrieving data for hongkong: from 2018-12-01 to 2018-12-31\n",
      "Time elapsed (hh:mm:ss.ms) 0:00:24.006139\n",
      "\n",
      "\n",
      "export hongkong completed!\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from wwo_hist import retrieve_hist_data\n",
    "import urllib.request\n",
    "\n",
    "#downlaod extra weather information\n",
    "\n",
    "frequency = 24\n",
    "start_date = '01-Jan-2017'\n",
    "end_date = '31-DEC-2018'\n",
    "api_key = '8b9dd8eb5f174b05b0a143348200412'\n",
    "location_list = ['hongkong']\n",
    "\n",
    "hist_weather_data = retrieve_hist_data(api_key, location_list, start_date, end_date, frequency = 24, export_csv = True, store_df = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 448
    },
    "executionInfo": {
     "elapsed": 1033,
     "status": "ok",
     "timestamp": 1607092759101,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "uJ8L1cOaLaq1",
    "outputId": "317421a1-25c7-402d-a68b-ea956a0b0073"
   },
   "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>tempC</th>\n",
       "      <th>visibility</th>\n",
       "      <th>winddirDegree</th>\n",
       "      <th>windspeedKmph</th>\n",
       "      <th>humidity</th>\n",
       "      <th>cloudcover</th>\n",
       "      <th>WindChillC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date_time</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-01-01</th>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-02</th>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>77</td>\n",
       "      <td>10</td>\n",
       "      <td>80</td>\n",
       "      <td>11</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03</th>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>81</td>\n",
       "      <td>14</td>\n",
       "      <td>81</td>\n",
       "      <td>8</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-04</th>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>80</td>\n",
       "      <td>15</td>\n",
       "      <td>82</td>\n",
       "      <td>8</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-05</th>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>72</td>\n",
       "      <td>9</td>\n",
       "      <td>83</td>\n",
       "      <td>42</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-27</th>\n",
       "      <td>22</td>\n",
       "      <td>10</td>\n",
       "      <td>39</td>\n",
       "      <td>12</td>\n",
       "      <td>66</td>\n",
       "      <td>43</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-28</th>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "      <td>36</td>\n",
       "      <td>19</td>\n",
       "      <td>61</td>\n",
       "      <td>27</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-29</th>\n",
       "      <td>14</td>\n",
       "      <td>10</td>\n",
       "      <td>21</td>\n",
       "      <td>20</td>\n",
       "      <td>62</td>\n",
       "      <td>44</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-30</th>\n",
       "      <td>13</td>\n",
       "      <td>10</td>\n",
       "      <td>10</td>\n",
       "      <td>20</td>\n",
       "      <td>59</td>\n",
       "      <td>70</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31</th>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>730 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            tempC  visibility  winddirDegree  ...  humidity  cloudcover  WindChillC\n",
       "date_time                                     ...                                  \n",
       "2017-01-01     21          10             76  ...        80          30          19\n",
       "2017-01-02     22          10             77  ...        80          11          21\n",
       "2017-01-03     22          10             81  ...        81           8          21\n",
       "2017-01-04     22          10             80  ...        82           8          21\n",
       "2017-01-05     22          10             72  ...        83          42          21\n",
       "...           ...         ...            ...  ...       ...         ...         ...\n",
       "2018-12-27     22          10             39  ...        66          43          22\n",
       "2018-12-28     20          10             36  ...        61          27          18\n",
       "2018-12-29     14          10             21  ...        62          44          12\n",
       "2018-12-30     13          10             10  ...        59          70           9\n",
       "2018-12-31     12          10            138  ...        69          79          10\n",
       "\n",
       "[730 rows x 7 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add weather features\n",
    "dateparse = lambda dates: datetime.strptime(dates, '%Y-%m-%d')\n",
    "df_weather = pd.read_csv('/content/hongkong.csv', parse_dates=['date_time'], index_col='date_time', date_parser=dateparse)\n",
    "weather_features = df_weather[['tempC','visibility','winddirDegree','windspeedKmph','humidity','cloudcover', 'WindChillC']]\n",
    "weather_features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Obtain Features from Raw Data\n",
    "\n",
    "- There is only date data in the dataset, so I think it is necessary to mine more infromation (i.e. more features) extracted from the **date data**, so I extract the following features:\n",
    "    - hour, date, week, month, year: features from datetime.stiptime\n",
    "    - is_holiday: whether the date is a public holiday\n",
    "    - tried is_traffic_time(9 a.m., 10 a.m., 18 a.m., 19 a.m.), quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "executionInfo": {
     "elapsed": 760,
     "status": "ok",
     "timestamp": 1607084468609,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "R9gQMcOcxDbh",
    "outputId": "40e8c215-58f2-40d9-a0bc-d63c8b72cf6b"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        2017-01-01\n",
       "1        2017-01-01\n",
       "2        2017-01-01\n",
       "3        2017-01-01\n",
       "4        2017-01-01\n",
       "            ...    \n",
       "14001    2018-12-31\n",
       "14002    2018-12-31\n",
       "14003    2018-12-31\n",
       "14004    2018-12-31\n",
       "14005    2018-12-31\n",
       "Name: holiday, Length: 14006, dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this block just for test , ignore!\n",
    "def get_holiday(s):\n",
    "  s=str(s)\n",
    "  list = re.split(' ', s)\n",
    "  days = str(datetime.strptime(list[0],\"%d/%m/%Y\"))\n",
    "  day = re.split(' ', days)[0]\n",
    "  return day\n",
    "\n",
    "\n",
    "train_data[\"holiday\"] = train_data[\"date\"].apply(lambda x: get_holiday(x))\n",
    "train_data[\"holiday\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "xlQsxIc2WK98"
   },
   "outputs": [],
   "source": [
    "# define some functions to preprocess our data\n",
    "def get_hour(s):\n",
    "  s=str(s)\n",
    "  list = re.split(' |:',s)\n",
    "  return list[1]\n",
    "\n",
    "def get_month(s):\n",
    "  s=str(s)\n",
    "  list = re.split('/',s)\n",
    "  return list[1]\n",
    "\n",
    "def get_year(s):\n",
    "  s=str(s)\n",
    "  list = re.split('/| ',s)\n",
    "  return list[2]\n",
    "\n",
    "def get_day(s):\n",
    "  s=str(s)\n",
    "  list = re.split('/| ',s)\n",
    "  return list[0]\n",
    "\n",
    "def get_week(s):\n",
    "  s=str(s)\n",
    "  list = re.split(' ', s)\n",
    "  week = datetime.strptime(list[0],\"%d/%m/%Y\").weekday()\n",
    "  return str(week)\n",
    "\n",
    "\n",
    "holidays = ['2017-01-02','2017-01-28','2017-01-30','2017-01-31','2017-04-04','2017-04-14','2017-04-15','2017-04-17','2017-05-01','2017-05-03','2017-05-30','2017-07-01','2017-10-02','2017-10-05','2017-10-28','2017-12-25','2017-12-26','2018-01-01','2018-02-16','2018-02-17','2018-02-18','2018-03-30','2018-03-31','2018-04-02','5/4/2018','2018-05-01','2018-05-22','2018-06-18','2018-07-02','2018-09-25','2018-10-01','2018-10-17','2018-12-25','2018-12-26']\n",
    "\n",
    "def get_holiday(s):\n",
    "  s=str(s)\n",
    "  list = re.split(' ', s)\n",
    "  days = str(datetime.strptime(list[0],\"%d/%m/%Y\"))\n",
    "  day = re.split(' ', days)[0]\n",
    "  if day in holidays:\n",
    "    return 1\n",
    "  return 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 417
    },
    "executionInfo": {
     "elapsed": 1105,
     "status": "ok",
     "timestamp": 1607084478802,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "l4BLpjZwYznh",
    "outputId": "0c72b11c-b407-476b-ea0e-5b9cb8ea19d3"
   },
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>speed</th>\n",
       "      <th>holiday</th>\n",
       "      <th>hour</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>year</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2017 0:00</td>\n",
       "      <td>43.002930</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1/1/2017 1:00</td>\n",
       "      <td>46.118696</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1/1/2017 2:00</td>\n",
       "      <td>44.294158</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1/1/2017 3:00</td>\n",
       "      <td>41.067468</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1/1/2017 4:00</td>\n",
       "      <td>46.448653</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14001</th>\n",
       "      <td>14001</td>\n",
       "      <td>31/12/2018 12:00</td>\n",
       "      <td>19.865269</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14002</th>\n",
       "      <td>14002</td>\n",
       "      <td>31/12/2018 15:00</td>\n",
       "      <td>17.820375</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14003</th>\n",
       "      <td>14003</td>\n",
       "      <td>31/12/2018 16:00</td>\n",
       "      <td>12.501851</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14004</th>\n",
       "      <td>14004</td>\n",
       "      <td>31/12/2018 18:00</td>\n",
       "      <td>15.979319</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14005</th>\n",
       "      <td>14005</td>\n",
       "      <td>31/12/2018 20:00</td>\n",
       "      <td>40.594183</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14006 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id              date      speed  holiday hour month day  year weekday\n",
       "0          0     1/1/2017 0:00  43.002930        0    0     1   1  2017       6\n",
       "1          1     1/1/2017 1:00  46.118696        0    1     1   1  2017       6\n",
       "2          2     1/1/2017 2:00  44.294158        0    2     1   1  2017       6\n",
       "3          3     1/1/2017 3:00  41.067468        0    3     1   1  2017       6\n",
       "4          4     1/1/2017 4:00  46.448653        0    4     1   1  2017       6\n",
       "...      ...               ...        ...      ...  ...   ...  ..   ...     ...\n",
       "14001  14001  31/12/2018 12:00  19.865269        0   12    12  31  2018       0\n",
       "14002  14002  31/12/2018 15:00  17.820375        0   15    12  31  2018       0\n",
       "14003  14003  31/12/2018 16:00  12.501851        0   16    12  31  2018       0\n",
       "14004  14004  31/12/2018 18:00  15.979319        0   18    12  31  2018       0\n",
       "14005  14005  31/12/2018 20:00  40.594183        0   20    12  31  2018       0\n",
       "\n",
       "[14006 rows x 9 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_data[\"hour\"] = train_data[\"date\"].apply(lambda x : get_hour(x))\n",
    "train_data[\"month\"] = train_data[\"date\"].apply(lambda x : get_month(x))\n",
    "train_data[\"day\"] = train_data[\"date\"].apply(lambda x : get_day(x))\n",
    "train_data[\"year\"] = train_data[\"date\"].apply(lambda x : get_year(x))\n",
    "train_data[\"weekday\"] = train_data[\"date\"].apply(lambda x: get_week(x))\n",
    "train_data[\"holiday\"] = train_data[\"date\"].apply(lambda x: get_holiday(x))\n",
    "#train_fixed = train_data.drop([\"date\"], axis=1, inplace=False)\n",
    "#train_data[\"hour\"].describe()\n",
    "#train_data[\"weekday\"].describe()\n",
    "#train_data.head(100)\n",
    "train_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Concatenate All Features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 608
    },
    "executionInfo": {
     "elapsed": 2572,
     "status": "ok",
     "timestamp": 1607093058257,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "gR1RjWnuMgub",
    "outputId": "eeb006da-2a77-45f9-95db-619afd5fbc95"
   },
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>speed</th>\n",
       "      <th>holiday</th>\n",
       "      <th>hour</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>year</th>\n",
       "      <th>weekday</th>\n",
       "      <th>tempC</th>\n",
       "      <th>visibility</th>\n",
       "      <th>winddirDegree</th>\n",
       "      <th>windspeedKmph</th>\n",
       "      <th>humidity</th>\n",
       "      <th>cloudcover</th>\n",
       "      <th>WindChillC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2017 0:00</td>\n",
       "      <td>43.002930</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1/1/2017 1:00</td>\n",
       "      <td>46.118696</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1/1/2017 2:00</td>\n",
       "      <td>44.294158</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1/1/2017 3:00</td>\n",
       "      <td>41.067468</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1/1/2017 4:00</td>\n",
       "      <td>46.448653</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14001</th>\n",
       "      <td>14001</td>\n",
       "      <td>31/12/2018 12:00</td>\n",
       "      <td>19.865269</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14002</th>\n",
       "      <td>14002</td>\n",
       "      <td>31/12/2018 15:00</td>\n",
       "      <td>17.820375</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14003</th>\n",
       "      <td>14003</td>\n",
       "      <td>31/12/2018 16:00</td>\n",
       "      <td>12.501851</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14004</th>\n",
       "      <td>14004</td>\n",
       "      <td>31/12/2018 18:00</td>\n",
       "      <td>15.979319</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14005</th>\n",
       "      <td>14005</td>\n",
       "      <td>31/12/2018 20:00</td>\n",
       "      <td>40.594183</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14006 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id              date      speed  ...  humidity cloudcover WindChillC\n",
       "0          0     1/1/2017 0:00  43.002930  ...        80         30         19\n",
       "1          1     1/1/2017 1:00  46.118696  ...        80         30         19\n",
       "2          2     1/1/2017 2:00  44.294158  ...        80         30         19\n",
       "3          3     1/1/2017 3:00  41.067468  ...        80         30         19\n",
       "4          4     1/1/2017 4:00  46.448653  ...        80         30         19\n",
       "...      ...               ...        ...  ...       ...        ...        ...\n",
       "14001  14001  31/12/2018 12:00  19.865269  ...        69         79         10\n",
       "14002  14002  31/12/2018 15:00  17.820375  ...        69         79         10\n",
       "14003  14003  31/12/2018 16:00  12.501851  ...        69         79         10\n",
       "14004  14004  31/12/2018 18:00  15.979319  ...        69         79         10\n",
       "14005  14005  31/12/2018 20:00  40.594183  ...        69         79         10\n",
       "\n",
       "[14006 rows x 16 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# process weather data \n",
    "tempC = []\n",
    "visibility = []\n",
    "winddirDegree = []\n",
    "windspeedKmph = []\n",
    "humidity = []\n",
    "cloudcover = []\n",
    "WindChillC = []\n",
    "\n",
    "for i in train_data['date']:\n",
    "    # transform the type from date to datetime\n",
    "  string_date = str(i)\n",
    "  string_date=re.split(\" \",string_date)[0]\n",
    "  date = datetime.strptime(string_date, '%d/%m/%Y')\n",
    "  tempC.append(weather_features['tempC'][date])\n",
    "  visibility.append(weather_features['visibility'][date])\n",
    "  winddirDegree.append(weather_features['winddirDegree'][date])\n",
    "  windspeedKmph.append(weather_features['windspeedKmph'][date])\n",
    "  humidity.append(weather_features['humidity'][date])\n",
    "  cloudcover.append(weather_features['cloudcover'][date])\n",
    "  WindChillC.append(weather_features['WindChillC'][date])\n",
    "\n",
    "train_data['tempC'] = tempC\n",
    "train_data['visibility'] = visibility\n",
    "train_data['winddirDegree'] = winddirDegree\n",
    "train_data['windspeedKmph'] = windspeedKmph\n",
    "train_data['humidity'] = humidity\n",
    "train_data['cloudcover'] = cloudcover\n",
    "train_data['WindChillC'] = WindChillC\n",
    "train_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I tried to use one-hot form for those features like hour, date, etc. to add the dimensionality of features but the effect is worse than not using one-hot**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 437
    },
    "executionInfo": {
     "elapsed": 724,
     "status": "ok",
     "timestamp": 1604299075199,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "Vef563-OqdpX",
    "outputId": "e1f859ed-0b5b-44ea-ada0-c5ce2ff66b95"
   },
   "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>hour_0</th>\n",
       "      <th>hour_1</th>\n",
       "      <th>hour_10</th>\n",
       "      <th>hour_11</th>\n",
       "      <th>hour_12</th>\n",
       "      <th>hour_13</th>\n",
       "      <th>hour_14</th>\n",
       "      <th>hour_15</th>\n",
       "      <th>hour_16</th>\n",
       "      <th>hour_17</th>\n",
       "      <th>hour_18</th>\n",
       "      <th>hour_19</th>\n",
       "      <th>hour_2</th>\n",
       "      <th>hour_20</th>\n",
       "      <th>hour_21</th>\n",
       "      <th>hour_22</th>\n",
       "      <th>hour_23</th>\n",
       "      <th>hour_3</th>\n",
       "      <th>hour_4</th>\n",
       "      <th>hour_5</th>\n",
       "      <th>hour_6</th>\n",
       "      <th>hour_7</th>\n",
       "      <th>hour_8</th>\n",
       "      <th>hour_9</th>\n",
       "      <th>weekday_0</th>\n",
       "      <th>weekday_1</th>\n",
       "      <th>weekday_2</th>\n",
       "      <th>weekday_3</th>\n",
       "      <th>weekday_4</th>\n",
       "      <th>weekday_5</th>\n",
       "      <th>weekday_6</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14001</th>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14002</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14003</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14004</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14005</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14006 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       hour_0  hour_1  hour_10  ...  weekday_4  weekday_5  weekday_6\n",
       "0           1       0        0  ...          0          0          1\n",
       "1           0       1        0  ...          0          0          1\n",
       "2           0       0        0  ...          0          0          1\n",
       "3           0       0        0  ...          0          0          1\n",
       "4           0       0        0  ...          0          0          1\n",
       "...       ...     ...      ...  ...        ...        ...        ...\n",
       "14001       0       0        0  ...          0          0          0\n",
       "14002       0       0        0  ...          0          0          0\n",
       "14003       0       0        0  ...          0          0          0\n",
       "14004       0       0        0  ...          0          0          0\n",
       "14005       0       0        0  ...          0          0          0\n",
       "\n",
       "[14006 rows x 31 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pd.get_dummies(train_data[[\"hour\",\"weekday\"]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 437
    },
    "executionInfo": {
     "elapsed": 771,
     "status": "ok",
     "timestamp": 1607093163332,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "R7402AtMlmDp",
    "outputId": "70d01b7d-dd13-45c1-a402-6d30a56930c1"
   },
   "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>id</th>\n",
       "      <th>speed</th>\n",
       "      <th>holiday</th>\n",
       "      <th>hour</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>year</th>\n",
       "      <th>weekday</th>\n",
       "      <th>tempC</th>\n",
       "      <th>visibility</th>\n",
       "      <th>winddirDegree</th>\n",
       "      <th>windspeedKmph</th>\n",
       "      <th>humidity</th>\n",
       "      <th>cloudcover</th>\n",
       "      <th>WindChillC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>43.002930</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>46.118696</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>44.294158</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>41.067468</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>46.448653</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>6</td>\n",
       "      <td>21</td>\n",
       "      <td>10</td>\n",
       "      <td>76</td>\n",
       "      <td>16</td>\n",
       "      <td>80</td>\n",
       "      <td>30</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14001</th>\n",
       "      <td>14001</td>\n",
       "      <td>19.865269</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14002</th>\n",
       "      <td>14002</td>\n",
       "      <td>17.820375</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14003</th>\n",
       "      <td>14003</td>\n",
       "      <td>12.501851</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14004</th>\n",
       "      <td>14004</td>\n",
       "      <td>15.979319</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14005</th>\n",
       "      <td>14005</td>\n",
       "      <td>40.594183</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14006 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id      speed  holiday  ... humidity cloudcover WindChillC\n",
       "0          0  43.002930        0  ...       80         30         19\n",
       "1          1  46.118696        0  ...       80         30         19\n",
       "2          2  44.294158        0  ...       80         30         19\n",
       "3          3  41.067468        0  ...       80         30         19\n",
       "4          4  46.448653        0  ...       80         30         19\n",
       "...      ...        ...      ...  ...      ...        ...        ...\n",
       "14001  14001  19.865269        0  ...       69         79         10\n",
       "14002  14002  17.820375        0  ...       69         79         10\n",
       "14003  14003  12.501851        0  ...       69         79         10\n",
       "14004  14004  15.979319        0  ...       69         79         10\n",
       "14005  14005  40.594183        0  ...       69         79         10\n",
       "\n",
       "[14006 rows x 15 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pd.get_dummies(train_fixed[[\"hour\",\"month\",\"day\",\"year\"]])\n",
    "#train_clean = pd.concat([train_data, pd.get_dummies(train_data[[\"hour\",\"weekday\",\"month\",\"day\",\"year\"]])], axis=1)\n",
    "#train_clean = train_clean.drop([\"hour\",\"weekday\",\"month\",\"day\",\"date\",\"year\"], axis=1)\n",
    "#features = train_clean.loc[:, \"hour_0\":\"day_9\"]\n",
    "#train_clean.loc[:, \"hour_0\":\"day_9\"] = (features-features.mean())/features.std()\n",
    "train_clean = train_data.drop([\"date\"], axis=1)\n",
    "train_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Qo8hlA6Xbq6X"
   },
   "outputs": [],
   "source": [
    "#save train data with features\n",
    "train_clean.to_csv(\"/content/drive/My Drive/5001_kaggle/train_cleaned_data7.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "d23_Ufjrp4bI"
   },
   "source": [
    "### The Same For Test Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 417
    },
    "executionInfo": {
     "elapsed": 787,
     "status": "ok",
     "timestamp": 1607084675585,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "Q3tVg91tfPZj",
    "outputId": "690d9ca9-9753-4ee0-882e-96f3a98280fd"
   },
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>hour</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>year</th>\n",
       "      <th>weekday</th>\n",
       "      <th>holiday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2018 2:00</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1/1/2018 5:00</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1/1/2018 7:00</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1/1/2018 8:00</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1/1/2018 10:00</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3499</th>\n",
       "      <td>3499</td>\n",
       "      <td>31/12/2018 17:00</td>\n",
       "      <td>17</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3500</th>\n",
       "      <td>3500</td>\n",
       "      <td>31/12/2018 19:00</td>\n",
       "      <td>19</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3501</th>\n",
       "      <td>3501</td>\n",
       "      <td>31/12/2018 21:00</td>\n",
       "      <td>21</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3502</th>\n",
       "      <td>3502</td>\n",
       "      <td>31/12/2018 22:00</td>\n",
       "      <td>22</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3503</th>\n",
       "      <td>3503</td>\n",
       "      <td>31/12/2018 23:00</td>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3504 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id              date hour month day  year weekday  holiday\n",
       "0        0     1/1/2018 2:00    2     1   1  2018       0        1\n",
       "1        1     1/1/2018 5:00    5     1   1  2018       0        1\n",
       "2        2     1/1/2018 7:00    7     1   1  2018       0        1\n",
       "3        3     1/1/2018 8:00    8     1   1  2018       0        1\n",
       "4        4    1/1/2018 10:00   10     1   1  2018       0        1\n",
       "...    ...               ...  ...   ...  ..   ...     ...      ...\n",
       "3499  3499  31/12/2018 17:00   17    12  31  2018       0        0\n",
       "3500  3500  31/12/2018 19:00   19    12  31  2018       0        0\n",
       "3501  3501  31/12/2018 21:00   21    12  31  2018       0        0\n",
       "3502  3502  31/12/2018 22:00   22    12  31  2018       0        0\n",
       "3503  3503  31/12/2018 23:00   23    12  31  2018       0        0\n",
       "\n",
       "[3504 rows x 8 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data[\"hour\"] = test_data[\"date\"].apply(lambda x : get_hour(x))\n",
    "test_data[\"month\"] = test_data[\"date\"].apply(lambda x : get_month(x))\n",
    "test_data[\"day\"] = test_data[\"date\"].apply(lambda x : get_day(x))\n",
    "test_data[\"year\"] = test_data[\"date\"].apply(lambda x : get_year(x))\n",
    "test_data[\"weekday\"] = test_data[\"date\"].apply(lambda x: get_week(x))\n",
    "test_data[\"holiday\"] = test_data[\"date\"].apply(lambda x: get_holiday(x))\n",
    "\n",
    "\n",
    "#train_fixed = train_data.drop([\"date\"], axis=1, inplace=False)\n",
    "test_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 608
    },
    "executionInfo": {
     "elapsed": 1439,
     "status": "ok",
     "timestamp": 1607093236980,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "etz4fBvmOEeE",
    "outputId": "2e60860b-e77e-4552-decd-c13fe415e279"
   },
   "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>id</th>\n",
       "      <th>date</th>\n",
       "      <th>hour</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>year</th>\n",
       "      <th>weekday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>speed</th>\n",
       "      <th>tempC</th>\n",
       "      <th>visibility</th>\n",
       "      <th>winddirDegree</th>\n",
       "      <th>windspeedKmph</th>\n",
       "      <th>humidity</th>\n",
       "      <th>cloudcover</th>\n",
       "      <th>WindChillC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1/1/2018 2:00</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1/1/2018 5:00</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1/1/2018 7:00</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1/1/2018 8:00</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>1/1/2018 10:00</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3499</th>\n",
       "      <td>3499</td>\n",
       "      <td>31/12/2018 17:00</td>\n",
       "      <td>17</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3500</th>\n",
       "      <td>3500</td>\n",
       "      <td>31/12/2018 19:00</td>\n",
       "      <td>19</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3501</th>\n",
       "      <td>3501</td>\n",
       "      <td>31/12/2018 21:00</td>\n",
       "      <td>21</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3502</th>\n",
       "      <td>3502</td>\n",
       "      <td>31/12/2018 22:00</td>\n",
       "      <td>22</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3503</th>\n",
       "      <td>3503</td>\n",
       "      <td>31/12/2018 23:00</td>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3504 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id              date hour  ... humidity cloudcover WindChillC\n",
       "0        0     1/1/2018 2:00    2  ...       63         23         18\n",
       "1        1     1/1/2018 5:00    5  ...       63         23         18\n",
       "2        2     1/1/2018 7:00    7  ...       63         23         18\n",
       "3        3     1/1/2018 8:00    8  ...       63         23         18\n",
       "4        4    1/1/2018 10:00   10  ...       63         23         18\n",
       "...    ...               ...  ...  ...      ...        ...        ...\n",
       "3499  3499  31/12/2018 17:00   17  ...       69         79         10\n",
       "3500  3500  31/12/2018 19:00   19  ...       69         79         10\n",
       "3501  3501  31/12/2018 21:00   21  ...       69         79         10\n",
       "3502  3502  31/12/2018 22:00   22  ...       69         79         10\n",
       "3503  3503  31/12/2018 23:00   23  ...       69         79         10\n",
       "\n",
       "[3504 rows x 16 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tempC = []\n",
    "visibility = []\n",
    "winddirDegree = []\n",
    "windspeedKmph = []\n",
    "humidity = []\n",
    "cloudcover = []\n",
    "WindChillC = []\n",
    "\n",
    "for i in test_data['date']:\n",
    "    # transform the type from date to datetime\n",
    "  string_date = str(i)\n",
    "  string_date=re.split(\" \",string_date)[0]\n",
    "  date = datetime.strptime(string_date, '%d/%m/%Y')\n",
    "  tempC.append(weather_features['tempC'][date])\n",
    "  visibility.append(weather_features['visibility'][date])\n",
    "  winddirDegree.append(weather_features['winddirDegree'][date])\n",
    "  windspeedKmph.append(weather_features['windspeedKmph'][date])\n",
    "  humidity.append(weather_features['humidity'][date])\n",
    "  cloudcover.append(weather_features['cloudcover'][date])\n",
    "  WindChillC.append(weather_features['WindChillC'][date])\n",
    "\n",
    "test_data['tempC'] = tempC\n",
    "test_data['visibility'] = visibility\n",
    "test_data['winddirDegree'] = winddirDegree\n",
    "test_data['windspeedKmph'] = windspeedKmph\n",
    "test_data['humidity'] = humidity\n",
    "test_data['cloudcover'] = cloudcover\n",
    "test_data['WindChillC'] = WindChillC\n",
    "test_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 437
    },
    "executionInfo": {
     "elapsed": 841,
     "status": "ok",
     "timestamp": 1607093262428,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "iUyFmKiyUWiB",
    "outputId": "a68b40d8-45cf-4d88-b038-539e7ab8942f"
   },
   "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>id</th>\n",
       "      <th>hour</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>year</th>\n",
       "      <th>weekday</th>\n",
       "      <th>holiday</th>\n",
       "      <th>speed</th>\n",
       "      <th>tempC</th>\n",
       "      <th>visibility</th>\n",
       "      <th>winddirDegree</th>\n",
       "      <th>windspeedKmph</th>\n",
       "      <th>humidity</th>\n",
       "      <th>cloudcover</th>\n",
       "      <th>WindChillC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>10</td>\n",
       "      <td>65</td>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "      <td>23</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3499</th>\n",
       "      <td>3499</td>\n",
       "      <td>17</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3500</th>\n",
       "      <td>3500</td>\n",
       "      <td>19</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3501</th>\n",
       "      <td>3501</td>\n",
       "      <td>21</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3502</th>\n",
       "      <td>3502</td>\n",
       "      <td>22</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3503</th>\n",
       "      <td>3503</td>\n",
       "      <td>23</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>2018</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>10</td>\n",
       "      <td>138</td>\n",
       "      <td>18</td>\n",
       "      <td>69</td>\n",
       "      <td>79</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3504 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id hour month day  ... windspeedKmph humidity  cloudcover  WindChillC\n",
       "0        0    2     1   1  ...            12       63          23          18\n",
       "1        1    5     1   1  ...            12       63          23          18\n",
       "2        2    7     1   1  ...            12       63          23          18\n",
       "3        3    8     1   1  ...            12       63          23          18\n",
       "4        4   10     1   1  ...            12       63          23          18\n",
       "...    ...  ...   ...  ..  ...           ...      ...         ...         ...\n",
       "3499  3499   17    12  31  ...            18       69          79          10\n",
       "3500  3500   19    12  31  ...            18       69          79          10\n",
       "3501  3501   21    12  31  ...            18       69          79          10\n",
       "3502  3502   22    12  31  ...            18       69          79          10\n",
       "3503  3503   23    12  31  ...            18       69          79          10\n",
       "\n",
       "[3504 rows x 15 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data[\"speed\"] = 0\n",
    "#test_clean = pd.concat([test_data, pd.get_dummies(test_data[[\"hour\",\"weekday\",\"month\",\"day\",\"year\"]])], axis=1)\n",
    "#test_clean = test_clean.drop([\"hour\",\"weekday\",\"month\",\"day\",\"date\",\"year\"], axis=1)\n",
    "test_clean = test_data.drop([\"date\"], axis=1)\n",
    "#test_clean[\"year_2017\"] = 0\n",
    "\n",
    "#features = test_clean.loc[:, \"hour_0\":\"day_9\"]\n",
    "#test_clean.loc[:, \"hour_0\":\"day_9\"] = (features-features.mean())/features.std()\n",
    "test_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 437
    },
    "executionInfo": {
     "elapsed": 783,
     "status": "ok",
     "timestamp": 1604402285284,
     "user": {
      "displayName": "Xuxuxu Shh",
      "photoUrl": "",
      "userId": "06260925340065532759"
     },
     "user_tz": -480
    },
    "id": "0pRoInai9e1A",
    "outputId": "1e1419f7-a7ba-4289-e676-30f0a4437c2d"
   },
   "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>id</th>\n",
       "      <th>holiday</th>\n",
       "      <th>speed</th>\n",
       "      <th>hour_0</th>\n",
       "      <th>hour_1</th>\n",
       "      <th>hour_10</th>\n",
       "      <th>hour_11</th>\n",
       "      <th>hour_12</th>\n",
       "      <th>hour_13</th>\n",
       "      <th>hour_14</th>\n",
       "      <th>hour_15</th>\n",
       "      <th>hour_16</th>\n",
       "      <th>hour_17</th>\n",
       "      <th>hour_18</th>\n",
       "      <th>hour_19</th>\n",
       "      <th>hour_2</th>\n",
       "      <th>hour_20</th>\n",
       "      <th>hour_21</th>\n",
       "      <th>hour_22</th>\n",
       "      <th>hour_23</th>\n",
       "      <th>hour_3</th>\n",
       "      <th>hour_4</th>\n",
       "      <th>hour_5</th>\n",
       "      <th>hour_6</th>\n",
       "      <th>hour_7</th>\n",
       "      <th>hour_8</th>\n",
       "      <th>hour_9</th>\n",
       "      <th>weekday_0</th>\n",
       "      <th>weekday_1</th>\n",
       "      <th>weekday_2</th>\n",
       "      <th>weekday_3</th>\n",
       "      <th>weekday_4</th>\n",
       "      <th>weekday_5</th>\n",
       "      <th>weekday_6</th>\n",
       "      <th>month_1</th>\n",
       "      <th>month_10</th>\n",
       "      <th>month_11</th>\n",
       "      <th>month_12</th>\n",
       "      <th>month_2</th>\n",
       "      <th>month_3</th>\n",
       "      <th>month_4</th>\n",
       "      <th>month_5</th>\n",
       "      <th>month_6</th>\n",
       "      <th>month_7</th>\n",
       "      <th>month_8</th>\n",
       "      <th>month_9</th>\n",
       "      <th>day_1</th>\n",
       "      <th>day_10</th>\n",
       "      <th>day_11</th>\n",
       "      <th>day_12</th>\n",
       "      <th>day_13</th>\n",
       "      <th>day_14</th>\n",
       "      <th>day_15</th>\n",
       "      <th>day_16</th>\n",
       "      <th>day_17</th>\n",
       "      <th>day_18</th>\n",
       "      <th>day_19</th>\n",
       "      <th>day_2</th>\n",
       "      <th>day_20</th>\n",
       "      <th>day_21</th>\n",
       "      <th>day_22</th>\n",
       "      <th>day_23</th>\n",
       "      <th>day_24</th>\n",
       "      <th>day_25</th>\n",
       "      <th>day_26</th>\n",
       "      <th>day_27</th>\n",
       "      <th>day_28</th>\n",
       "      <th>day_29</th>\n",
       "      <th>day_3</th>\n",
       "      <th>day_30</th>\n",
       "      <th>day_31</th>\n",
       "      <th>day_4</th>\n",
       "      <th>day_5</th>\n",
       "      <th>day_6</th>\n",
       "      <th>day_7</th>\n",
       "      <th>day_8</th>\n",
       "      <th>day_9</th>\n",
       "      <th>year_2017</th>\n",
       "      <th>year_2018</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3499</th>\n",
       "      <td>3499</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3500</th>\n",
       "      <td>3500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3501</th>\n",
       "      <td>3501</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3502</th>\n",
       "      <td>3502</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3503</th>\n",
       "      <td>3503</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3504 rows × 79 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  holiday  speed  hour_0  ...  day_8  day_9  year_2017  year_2018\n",
       "0        0        1      0       0  ...      0      0          0          1\n",
       "1        1        1      0       0  ...      0      0          0          1\n",
       "2        2        1      0       0  ...      0      0          0          1\n",
       "3        3        1      0       0  ...      0      0          0          1\n",
       "4        4        1      0       0  ...      0      0          0          1\n",
       "...    ...      ...    ...     ...  ...    ...    ...        ...        ...\n",
       "3499  3499        0      0       0  ...      0      0          0          1\n",
       "3500  3500        0      0       0  ...      0      0          0          1\n",
       "3501  3501        0      0       0  ...      0      0          0          1\n",
       "3502  3502        0      0       0  ...      0      0          0          1\n",
       "3503  3503        0      0       0  ...      0      0          0          1\n",
       "\n",
       "[3504 rows x 79 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {
      "tags": []
     },
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#test_clean[['year_2018','year_2017']] = test_clean[['year_2017','year_2018']]\n",
    "#test_clean.rename(columns={'year_2018':'year_2017','year_2017':'year_2018'}, inplace=True)\n",
    "#test_clean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "-F2mUSZSVB-Q"
   },
   "outputs": [],
   "source": [
    "test_clean.to_csv(\"/content/drive/My Drive/5001_kaggle/test_cleaned_data7.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "authorship_tag": "ABX9TyPruY7R0RzQOPBknP5GZvwC",
   "mount_file_id": "1kwhFAvKCvDdZtSt1F_Hys_SwaYWHsK0n",
   "name": "traindata_clean.ipynb",
   "provenance": []
  },
  "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.7.9"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}