1 - Data Preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "RNFx81IgcP94"
   },
   "outputs": [],
   "source": [
    "import math\n",
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pygeohash as gh"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "-H6PkI9EcP-C"
   },
   "source": [
    "## The Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XhQ3na2ecP-E"
   },
   "source": [
    "### Load the raw data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 255
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 34287,
     "status": "ok",
     "timestamp": 1559399359605,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "Wp5GN5EncP-F",
    "outputId": "ba8c3162-b1a1-4115-d08a-e628e0a32567"
   },
   "outputs": [],
   "source": [
    "# !rm \"traffic-management.zip\" \"training.csv\"\n",
    "# !wget \"https://s3-ap-southeast-1.amazonaws.com/grab-aiforsea-dataset/traffic-management.zip\"\n",
    "# !unzip traffic-management.zip\n",
    "# !mv \"Traffic Management/training.csv\" .\n",
    "# !rm -r \"Traffic Management\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 5513,
     "status": "ok",
     "timestamp": 1559399391524,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "mKFoE7t7cP-K",
    "outputId": "773620c7-aa65-4709-cbda-e7e467acba40"
   },
   "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>geohash6</th>\n",
       "      <th>day</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>demand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>qp03wc</td>\n",
       "      <td>18</td>\n",
       "      <td>20:0</td>\n",
       "      <td>0.020072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>qp03pn</td>\n",
       "      <td>10</td>\n",
       "      <td>14:30</td>\n",
       "      <td>0.024721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>qp09sw</td>\n",
       "      <td>9</td>\n",
       "      <td>6:15</td>\n",
       "      <td>0.102821</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>qp0991</td>\n",
       "      <td>32</td>\n",
       "      <td>5:0</td>\n",
       "      <td>0.088755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>qp090q</td>\n",
       "      <td>15</td>\n",
       "      <td>4:0</td>\n",
       "      <td>0.074468</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  geohash6  day timestamp    demand\n",
       "0   qp03wc   18      20:0  0.020072\n",
       "1   qp03pn   10     14:30  0.024721\n",
       "2   qp09sw    9      6:15  0.102821\n",
       "3   qp0991   32       5:0  0.088755\n",
       "4   qp090q   15       4:0  0.074468"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('training.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Ca2sYv1vcP-T"
   },
   "source": [
    "### Feature Engineering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 7288,
     "status": "ok",
     "timestamp": 1559399407090,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "ZEkvD5VycP-V",
    "outputId": "ac31d6b0-5e6a-494a-d0d6-750668a0e97c"
   },
   "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>geohash6</th>\n",
       "      <th>day</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>demand</th>\n",
       "      <th>in_raw</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>qp03wc</td>\n",
       "      <td>18</td>\n",
       "      <td>20:0</td>\n",
       "      <td>0.020072</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>qp03pn</td>\n",
       "      <td>10</td>\n",
       "      <td>14:30</td>\n",
       "      <td>0.024721</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>qp09sw</td>\n",
       "      <td>9</td>\n",
       "      <td>6:15</td>\n",
       "      <td>0.102821</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>qp0991</td>\n",
       "      <td>32</td>\n",
       "      <td>5:0</td>\n",
       "      <td>0.088755</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>qp090q</td>\n",
       "      <td>15</td>\n",
       "      <td>4:0</td>\n",
       "      <td>0.074468</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  geohash6  day timestamp    demand  in_raw\n",
       "0   qp03wc   18      20:0  0.020072    True\n",
       "1   qp03pn   10     14:30  0.024721    True\n",
       "2   qp09sw    9      6:15  0.102821    True\n",
       "3   qp0991   32       5:0  0.088755    True\n",
       "4   qp090q   15       4:0  0.074468    True"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['in_raw'] = True\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XilHS4rtcP-d"
   },
   "source": [
    "#### Add datetime features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "wrRPLT-icP-e"
   },
   "outputs": [],
   "source": [
    "df[\"weekday\"] = df[\"day\"].apply(lambda x : x % 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "o1aMHVuScP-i"
   },
   "outputs": [],
   "source": [
    "timestamps = df['timestamp'].unique()\n",
    "timestamps_decoded = {}\n",
    "for timestamp in timestamps:\n",
    "    timestamps_decoded[timestamp] = list(map(int, timestamp.split(':')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "AoJC0q-JcP-m"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 6.59 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df['hour']            = df['timestamp'].apply(lambda x : timestamps_decoded[x][0])\n",
    "df['minutes']         = df['timestamp'].apply(lambda x : timestamps_decoded[x][1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 51
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 60238,
     "status": "ok",
     "timestamp": 1559400477720,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "sBr4GzeMcP-r",
    "outputId": "34b2bd95-fcba-46e5-b96a-4c6ee24ab787"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 2min 12s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df['ds'] = df['day'].astype(str) + \" \" + df['timestamp']\n",
    "df['ds'] = df['ds'].apply(lambda x : datetime.datetime.strptime(x, \"%j %H:%M\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 59046,
     "status": "ok",
     "timestamp": 1559400477728,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "LSNQYH5tcP-6",
    "outputId": "ce3e1023-3939-415c-ea1a-a14865762c7f"
   },
   "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>geohash6</th>\n",
       "      <th>day</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>demand</th>\n",
       "      <th>in_raw</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>minutes</th>\n",
       "      <th>ds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>qp03wc</td>\n",
       "      <td>18</td>\n",
       "      <td>20:0</td>\n",
       "      <td>0.020072</td>\n",
       "      <td>True</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-18 20:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>qp03pn</td>\n",
       "      <td>10</td>\n",
       "      <td>14:30</td>\n",
       "      <td>0.024721</td>\n",
       "      <td>True</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>30</td>\n",
       "      <td>1900-01-10 14:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>qp09sw</td>\n",
       "      <td>9</td>\n",
       "      <td>6:15</td>\n",
       "      <td>0.102821</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "      <td>1900-01-09 06:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>qp0991</td>\n",
       "      <td>32</td>\n",
       "      <td>5:0</td>\n",
       "      <td>0.088755</td>\n",
       "      <td>True</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-02-01 05:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>qp090q</td>\n",
       "      <td>15</td>\n",
       "      <td>4:0</td>\n",
       "      <td>0.074468</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-15 04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  geohash6  day timestamp    demand  in_raw  weekday  hour  minutes  \\\n",
       "0   qp03wc   18      20:0  0.020072    True        4    20        0   \n",
       "1   qp03pn   10     14:30  0.024721    True        3    14       30   \n",
       "2   qp09sw    9      6:15  0.102821    True        2     6       15   \n",
       "3   qp0991   32       5:0  0.088755    True        4     5        0   \n",
       "4   qp090q   15       4:0  0.074468    True        1     4        0   \n",
       "\n",
       "                   ds  \n",
       "0 1900-01-18 20:00:00  \n",
       "1 1900-01-10 14:30:00  \n",
       "2 1900-01-09 06:15:00  \n",
       "3 1900-02-01 05:00:00  \n",
       "4 1900-01-15 04:00:00  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "i7V2-mxMcP_B"
   },
   "source": [
    "#### Get Unique locations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 51
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 54301,
     "status": "ok",
     "timestamp": 1559400478073,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "KMAgRT4bcP_C",
    "outputId": "087e4eb9-3742-4b5a-b708-3a35bde6d276",
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The dataset has 1329 unique geohashes:\n",
      " ['qp03wc' 'qp03pn' 'qp09sw' ... 'qp03yn' 'qp09v9' 'qp0d45']\n"
     ]
    }
   ],
   "source": [
    "locations = df['geohash6'].unique()\n",
    "print(\"The dataset has\", len(locations), \"unique geohashes:\\n\", locations)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "gz3rM-DlcP_H"
   },
   "source": [
    "#### Fill in missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1900-01-01 00:00:00 --to--> 1900-03-02 23:45:00 with steps: 0:15:00\n"
     ]
    }
   ],
   "source": [
    "min_ds = df['ds'].min()\n",
    "max_ds = df['ds'].max()\n",
    "delta_ds = datetime.timedelta(minutes=15)\n",
    "print(min_ds, '--to-->', max_ds, 'with steps:', delta_ds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "bjuUX1-HcP_I"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 35.6 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "present_values = df[['geohash6', 'ds']].values\n",
    "present_values = set(tuple(x) for x in present_values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 394
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 70723,
     "status": "error",
     "timestamp": 1559400517220,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "AInSDbTxcP_O",
    "outputId": "4e35c486-1973-4b33-9598-56ed1ba0ca66"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 1min 54s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "missing_values = []\n",
    "for location in locations:\n",
    "    cur_time = min_ds\n",
    "    while cur_time <= max_ds:\n",
    "        if not (location, cur_time) in present_values:\n",
    "            day = cur_time.dayofyear\n",
    "            x = {'geohash6' : location,\n",
    "                 'day' : day,\n",
    "                 'timestamp' : str(int(cur_time.hour)) + \":\" + str(int(cur_time.minute)),\n",
    "                 'demand' : 0,\n",
    "                 'in_raw' : False,\n",
    "                 'weekday' : day % 7,\n",
    "                 'hour' : cur_time.hour,\n",
    "                 'minutes' : cur_time.minute,\n",
    "                 'ds' : cur_time}\n",
    "            missing_values.append(x)\n",
    "        cur_time += delta_ds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 51
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 90287,
     "status": "ok",
     "timestamp": 1559400025364,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "xTKjsvv8cP_T",
    "outputId": "a6107b96-e51a-4261-f2bc-3ffb6966e807"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 16.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_missing = pd.DataFrame(missing_values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 51
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 90802,
     "status": "ok",
     "timestamp": 1559400026233,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "WuYq8cxCcP_Z",
    "outputId": "51bb0075-56cf-470c-cb3a-9fd2fde93cb3"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 2.4 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_full = pd.concat([df, df_missing], sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 204
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 92912,
     "status": "ok",
     "timestamp": 1559400028609,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "dvXZqmU2cP_j",
    "outputId": "702ca0cc-8351-4de9-dab2-ca77aa71182b"
   },
   "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>geohash6</th>\n",
       "      <th>day</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>demand</th>\n",
       "      <th>in_raw</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>minutes</th>\n",
       "      <th>ds</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>qp03wc</td>\n",
       "      <td>18</td>\n",
       "      <td>20:0</td>\n",
       "      <td>0.020072</td>\n",
       "      <td>True</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-18 20:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>qp03pn</td>\n",
       "      <td>10</td>\n",
       "      <td>14:30</td>\n",
       "      <td>0.024721</td>\n",
       "      <td>True</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>30</td>\n",
       "      <td>1900-01-10 14:30:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>qp09sw</td>\n",
       "      <td>9</td>\n",
       "      <td>6:15</td>\n",
       "      <td>0.102821</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "      <td>1900-01-09 06:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>qp0991</td>\n",
       "      <td>32</td>\n",
       "      <td>5:0</td>\n",
       "      <td>0.088755</td>\n",
       "      <td>True</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-02-01 05:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>qp090q</td>\n",
       "      <td>15</td>\n",
       "      <td>4:0</td>\n",
       "      <td>0.074468</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-15 04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  geohash6  day timestamp    demand  in_raw  weekday  hour  minutes  \\\n",
       "0   qp03wc   18      20:0  0.020072    True        4    20        0   \n",
       "1   qp03pn   10     14:30  0.024721    True        3    14       30   \n",
       "2   qp09sw    9      6:15  0.102821    True        2     6       15   \n",
       "3   qp0991   32       5:0  0.088755    True        4     5        0   \n",
       "4   qp090q   15       4:0  0.074468    True        1     4        0   \n",
       "\n",
       "                   ds  \n",
       "0 1900-01-18 20:00:00  \n",
       "1 1900-01-10 14:30:00  \n",
       "2 1900-01-09 06:15:00  \n",
       "3 1900-02-01 05:00:00  \n",
       "4 1900-01-15 04:00:00  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_full = df_full.reset_index().drop('index', axis=1)\n",
    "df_full.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 34
    },
    "colab_type": "code",
    "executionInfo": {
     "elapsed": 91516,
     "status": "ok",
     "timestamp": 1559400028612,
     "user": {
      "displayName": "Franz Louis Cesista",
      "photoUrl": "https://lh3.googleusercontent.com/-NquKeLt1770/AAAAAAAAAAI/AAAAAAAAAMo/RHnx0T1cGIk/s64/photo.jpg",
      "userId": "08273419339046628946"
     },
     "user_tz": -480
    },
    "id": "aPgV843icP_q",
    "outputId": "1362e001-e7de-430b-e582-0e16a65440fb"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_full) == 4*24*61*len(locations)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "LuDw0VfGcP_y"
   },
   "source": [
    "#### Decode the geohashes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "9LYnzpt4cP_z"
   },
   "outputs": [],
   "source": [
    "locations_decoded = {}\n",
    "for location in locations:\n",
    "    locations_decoded[location] = gh.decode_exactly(location)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "8M4YcVBbcP_4",
    "outputId": "55fc2aa2-7f9b-44db-b554-c0de63825868"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 16.2 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_full['latitude']  = df_full['geohash6'].apply(lambda x : locations_decoded[x][0])\n",
    "df_full['longitude'] = df_full['geohash6'].apply(lambda x : locations_decoded[x][1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "wnW1WCPQcQAA",
    "outputId": "4ada4712-a144-47aa-cbcb-054569cadf84"
   },
   "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>geohash6</th>\n",
       "      <th>day</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>demand</th>\n",
       "      <th>in_raw</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>minutes</th>\n",
       "      <th>ds</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>qp03wc</td>\n",
       "      <td>18</td>\n",
       "      <td>20:0</td>\n",
       "      <td>0.020072</td>\n",
       "      <td>True</td>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-18 20:00:00</td>\n",
       "      <td>-5.353088</td>\n",
       "      <td>90.653687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>qp03pn</td>\n",
       "      <td>10</td>\n",
       "      <td>14:30</td>\n",
       "      <td>0.024721</td>\n",
       "      <td>True</td>\n",
       "      <td>3</td>\n",
       "      <td>14</td>\n",
       "      <td>30</td>\n",
       "      <td>1900-01-10 14:30:00</td>\n",
       "      <td>-5.413513</td>\n",
       "      <td>90.664673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>qp09sw</td>\n",
       "      <td>9</td>\n",
       "      <td>6:15</td>\n",
       "      <td>0.102821</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "      <td>1900-01-09 06:15:00</td>\n",
       "      <td>-5.325623</td>\n",
       "      <td>90.906372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>qp0991</td>\n",
       "      <td>32</td>\n",
       "      <td>5:0</td>\n",
       "      <td>0.088755</td>\n",
       "      <td>True</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-02-01 05:00:00</td>\n",
       "      <td>-5.353088</td>\n",
       "      <td>90.752563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>qp090q</td>\n",
       "      <td>15</td>\n",
       "      <td>4:0</td>\n",
       "      <td>0.074468</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-15 04:00:00</td>\n",
       "      <td>-5.413513</td>\n",
       "      <td>90.719604</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  geohash6  day timestamp    demand  in_raw  weekday  hour  minutes  \\\n",
       "0   qp03wc   18      20:0  0.020072    True        4    20        0   \n",
       "1   qp03pn   10     14:30  0.024721    True        3    14       30   \n",
       "2   qp09sw    9      6:15  0.102821    True        2     6       15   \n",
       "3   qp0991   32       5:0  0.088755    True        4     5        0   \n",
       "4   qp090q   15       4:0  0.074468    True        1     4        0   \n",
       "\n",
       "                   ds  latitude  longitude  \n",
       "0 1900-01-18 20:00:00 -5.353088  90.653687  \n",
       "1 1900-01-10 14:30:00 -5.413513  90.664673  \n",
       "2 1900-01-09 06:15:00 -5.325623  90.906372  \n",
       "3 1900-02-01 05:00:00 -5.353088  90.752563  \n",
       "4 1900-01-15 04:00:00 -5.413513  90.719604  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_full.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "_xM5HEENcQAF"
   },
   "source": [
    "#### Circular Embedding"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "hLKy7d3ccQAG",
    "outputId": "40992217-547d-445c-ba38-4a27acc9b11c"
   },
   "outputs": [],
   "source": [
    "# %%time\n",
    "# df_full['weekday_sin'] = np.sin(2*np.pi*df_full['weekday'].values/7)\n",
    "# df_full['weekday_cos'] = np.cos(2*np.pi*df_full['weekday'].values/7)\n",
    "# \n",
    "# df_full['hour_sin']    = np.sin(2*np.pi*df_full['hour'].values/24)\n",
    "# df_full['hour_cos']    = np.cos(2*np.pi*df_full['hour'].values/24)\n",
    "# \n",
    "# df_full['minutes_sin'] = np.sin(2*np.pi*df_full['minutes'].values/60)\n",
    "# df_full['minutes_cos'] = np.cos(2*np.pi*df_full['minutes'].values/60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "XQWZANBDcQAN",
    "outputId": "fb9713a3-4fde-4542-d5ee-f68db2ce817e"
   },
   "outputs": [],
   "source": [
    "# df_full.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "I7WTYIvecQAT"
   },
   "source": [
    "### Sort by time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "MUILipZ3cQAU",
    "outputId": "cce9a92e-50f5-46f8-85d7-d324ed988304"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 15.9 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_full = df_full.sort_values(by=['ds']).reset_index().drop('index', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "wqHnQ5iPcQAh",
    "outputId": "67ec4bc2-76c4-4180-c95e-6adea234d022"
   },
   "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>geohash6</th>\n",
       "      <th>day</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>demand</th>\n",
       "      <th>in_raw</th>\n",
       "      <th>weekday</th>\n",
       "      <th>hour</th>\n",
       "      <th>minutes</th>\n",
       "      <th>ds</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>qp09du</td>\n",
       "      <td>1</td>\n",
       "      <td>0:0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>-5.336609</td>\n",
       "      <td>90.829468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>qp09jm</td>\n",
       "      <td>1</td>\n",
       "      <td>0:0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>-5.419006</td>\n",
       "      <td>90.939331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>qp09sf</td>\n",
       "      <td>1</td>\n",
       "      <td>0:0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>-5.347595</td>\n",
       "      <td>90.917358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>qp09vd</td>\n",
       "      <td>1</td>\n",
       "      <td>0:0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>-5.303650</td>\n",
       "      <td>90.950317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>qp03y4</td>\n",
       "      <td>1</td>\n",
       "      <td>0:0</td>\n",
       "      <td>0.002732</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1900-01-01</td>\n",
       "      <td>-5.303650</td>\n",
       "      <td>90.620728</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  geohash6  day timestamp    demand  in_raw  weekday  hour  minutes  \\\n",
       "0   qp09du    1       0:0  0.000000   False        1     0        0   \n",
       "1   qp09jm    1       0:0  0.000000   False        1     0        0   \n",
       "2   qp09sf    1       0:0  0.000000   False        1     0        0   \n",
       "3   qp09vd    1       0:0  0.000000   False        1     0        0   \n",
       "4   qp03y4    1       0:0  0.002732    True        1     0        0   \n",
       "\n",
       "          ds  latitude  longitude  \n",
       "0 1900-01-01 -5.336609  90.829468  \n",
       "1 1900-01-01 -5.419006  90.939331  \n",
       "2 1900-01-01 -5.347595  90.917358  \n",
       "3 1900-01-01 -5.303650  90.950317  \n",
       "4 1900-01-01 -5.303650  90.620728  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_full.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "UnBmZy_gcQAp"
   },
   "source": [
    "### Save preprocessed data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "78mxSXjdcQAs"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 6min 53s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# to_drop = ['timestamp',\n",
    "#            'weekday_sin', 'weekday_cos',\n",
    "#            'hour_sin', 'hour_cos',\n",
    "#            'minutes_sin', 'minutes_cos']\n",
    "to_drop = ['timestamp']\n",
    "df_full.drop(to_drop, axis=1).to_csv(\"full_data.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "HcoWhD5ocQAz"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "name": "1 - Data Preprocessing.ipynb",
   "provenance": [],
   "version": "0.3.2"
  },
  "kernelspec": {
   "display_name": "Python [conda env:py36]",
   "language": "python",
   "name": "conda-env-py36-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}