01_processing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transportation Forecasting\n",
    "For TRBAM 2019 TRANSFOR19 Forecasting competition\n",
    "\n",
    "notes:\n",
    "\n",
    "- total data size = `(17856 rows × 1024 columns)`\n",
    "\n",
    "- available date range = `2016-10-01 to 2016-12-01`\n",
    "\n",
    "- speed forecasting range = `2016-12-01 00:00:00+08:00 to 2016-12-01 23:55:00+08:00` `6am - 10:55am, 4pm to 8:55pm`\n",
    "\n",
    "- prediction boundaries: \n",
    "  * `108.94615156073496 < longitude < 108.94765628015638`\n",
    "  * `34.2324012260476 < Latitude < 34.23940650580562`\n",
    "\n",
    "- Dec 01, 2016 is a Thursday\n",
    "\n",
    "### This is Part 1 of 3 part series:\n",
    "\n",
    "Use the following to navigate\n",
    "\n",
    "- [Part 1: data processing](01_processing.ipynb)\n",
    "- [Part 2: data preparation](02_preparation.ipynb)\n",
    "- [Part 3: model training](03_training.ipynb)\n",
    "\n",
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Setup and library installation\n",
    "\n",
    "Requirements: \n",
    "- Python >= 3.5 (3.7 recommended)\n",
    "- BLAS libraries (OpenBLAS, ATLAS, Intel MKL etc.) \n",
    "- gcc >= 4.9\n",
    "- Git\n",
    "- Python pip\n",
    "\n",
    "example install on Debian Linux:\n",
    "\n",
    "`apt install python3.7 python3.7-dev python3-pip g++ libblas-dev git`\n",
    "\n",
    "to install basic required Python libraries run:\n",
    "\n",
    "`pip3 install --user -r requirements.txt`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Data Analysis\n",
    "\n",
    "Python script"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# import Python libraries\n",
    "\n",
    "import sys\n",
    "import os\n",
    "import io\n",
    "import tarfile \n",
    "import time\n",
    "import pytz\n",
    "import datetime as dt\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Longitude range: 0.08742100000000619\n",
      "Latitude range: 0.07492099999999624\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# setup the default parameters\n",
    "\n",
    "lon_min = 108.911189\n",
    "lon_max = 108.99861\n",
    "lon_range = lon_max - lon_min\n",
    "\n",
    "lat_min = 34.2053\n",
    "lat_max = 34.280221\n",
    "lat_range = lat_max - lat_min\n",
    "\n",
    "n_segments = 32\n",
    "\n",
    "print(\n",
    "    'Longitude range: {}\\n'\n",
    "    'Latitude range: {}\\n'.format(lon_range, lat_range)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>speed_north</th>\n",
       "      <th>speed_south</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2016-10-01 00:00:00+08:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-01 00:05:00+08:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-01 00:10:00+08:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-01 00:15:00+08:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-01 00:20:00+08:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          speed_north speed_south\n",
       "Timestamp                                        \n",
       "2016-10-01 00:00:00+08:00         NaN         NaN\n",
       "2016-10-01 00:05:00+08:00         NaN         NaN\n",
       "2016-10-01 00:10:00+08:00         NaN         NaN\n",
       "2016-10-01 00:15:00+08:00         NaN         NaN\n",
       "2016-10-01 00:20:00+08:00         NaN         NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>1014</th>\n",
       "      <th>1015</th>\n",
       "      <th>1016</th>\n",
       "      <th>1017</th>\n",
       "      <th>1018</th>\n",
       "      <th>1019</th>\n",
       "      <th>1020</th>\n",
       "      <th>1021</th>\n",
       "      <th>1022</th>\n",
       "      <th>1023</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Timestamp</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>2016-10-01 00:00:00+08:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-01 00:05:00+08:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-01 00:10:00+08:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-01 00:15:00+08:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-10-01 00:20:00+08:00</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 1024 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           0     1     2     3     4     5     6     7     \\\n",
       "Timestamp                                                                   \n",
       "2016-10-01 00:00:00+08:00   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   \n",
       "2016-10-01 00:05:00+08:00   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   \n",
       "2016-10-01 00:10:00+08:00   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   \n",
       "2016-10-01 00:15:00+08:00   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   \n",
       "2016-10-01 00:20:00+08:00   0.0   0.0   0.0   0.0   0.0   0.0   0.0   0.0   \n",
       "\n",
       "                           8     9     ...   1014  1015  1016  1017  1018  \\\n",
       "Timestamp                              ...                                  \n",
       "2016-10-01 00:00:00+08:00   0.0   0.0  ...    0.0   0.0   0.0   0.0   0.0   \n",
       "2016-10-01 00:05:00+08:00   0.0   0.0  ...    0.0   0.0   0.0   0.0   0.0   \n",
       "2016-10-01 00:10:00+08:00   0.0   0.0  ...    0.0   0.0   0.0   0.0   0.0   \n",
       "2016-10-01 00:15:00+08:00   0.0   0.0  ...    0.0   0.0   0.0   0.0   0.0   \n",
       "2016-10-01 00:20:00+08:00   0.0   0.0  ...    0.0   0.0   0.0   0.0   0.0   \n",
       "\n",
       "                           1019  1020  1021  1022  1023  \n",
       "Timestamp                                                \n",
       "2016-10-01 00:00:00+08:00   0.0   0.0   0.0   0.0   0.0  \n",
       "2016-10-01 00:05:00+08:00   0.0   0.0   0.0   0.0   0.0  \n",
       "2016-10-01 00:10:00+08:00   0.0   0.0   0.0   0.0   0.0  \n",
       "2016-10-01 00:15:00+08:00   0.0   0.0   0.0   0.0   0.0  \n",
       "2016-10-01 00:20:00+08:00   0.0   0.0   0.0   0.0   0.0  \n",
       "\n",
       "[5 rows x 1024 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# create the empty base table that we are inputing values from the datasets\n",
    "time_range = np.arange('2016-10-01 00:00:00', '2016-12-02 00:00:00', np.timedelta64(5, 'm'), dtype='datetime64')\n",
    "col_range = np.arange(0, n_segments**2)\n",
    "\n",
    "# construct the pandas DataFrame object\n",
    "datatable = pd.DataFrame(index=time_range, columns=col_range).fillna(0.)\n",
    "datatable.index.name = 'Timestamp'\n",
    "datatable.index = datatable.index.tz_localize(pytz.timezone('Asia/Shanghai'))\n",
    "\n",
    "speedtable = pd.DataFrame(index=time_range, columns=['speed_north', 'speed_south'])\n",
    "speedtable.index.name = 'Timestamp'\n",
    "speedtable.index = speedtable.index.tz_localize(pytz.timezone('Asia/Shanghai'))\n",
    "\n",
    "# check and display\n",
    "assert datatable.index.dtype == 'datetime64[ns, Asia/Shanghai]'\n",
    "assert speedtable.index.dtype == 'datetime64[ns, Asia/Shanghai]'\n",
    "\n",
    "display(speedtable.head())\n",
    "display(datatable.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# save the empty table as a template\n",
    "datatable.to_csv('datatable_empty.csv')\n",
    "speedtable.to_csv('speedtable_empty.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----\n",
    "\n",
    "## 3. Import and extract data from the GAIA dataset\n",
    "\n",
    "- enumerate over all the \\*.tar.gz files \n",
    "- process using io.BytesIO() (reads directly from *.tar.gz)\n",
    "- create a list of files `tarfile_ls[]`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1: october/gps_20161026.tar.gz <TarInfo 'xian/gps_20161026' at 0x7f02155b89a8>\n",
      "    in <tarfile.TarFile object at 0x7f024752f710>\n",
      "\n",
      "2: october/gps_20161027.tar.gz <TarInfo 'xian/gps_20161027' at 0x7f02155b85c0>\n",
      "    in <tarfile.TarFile object at 0x7f024752f6a0>\n",
      "\n",
      "3: october/gps_20161028.tar.gz <TarInfo 'xian/gps_20161028' at 0x7f02155b8b38>\n",
      "    in <tarfile.TarFile object at 0x7f024752fa58>\n",
      "\n",
      "4: october/gps_20161029.tar.gz <TarInfo 'xian/gps_20161029' at 0x7f02155b8c00>\n",
      "    in <tarfile.TarFile object at 0x7f024752f860>\n",
      "\n",
      "5: october/gps_20161001.tar.gz <TarInfo 'xian/gps_20161001' at 0x7f02155b8cc8>\n",
      "    in <tarfile.TarFile object at 0x7f024752f828>\n",
      "\n",
      "6: october/gps_20161002.tar.gz <TarInfo 'xian/gps_20161002' at 0x7f02155b8d90>\n",
      "    in <tarfile.TarFile object at 0x7f024752fef0>\n",
      "\n",
      "7: october/gps_20161003.tar.gz <TarInfo 'xian/gps_20161003' at 0x7f02155b8e58>\n",
      "    in <tarfile.TarFile object at 0x7f024752fba8>\n",
      "\n",
      "8: october/gps_20161004.tar.gz <TarInfo 'xian/gps_20161004' at 0x7f02155b8f20>\n",
      "    in <tarfile.TarFile object at 0x7f024752fc88>\n",
      "\n",
      "9: october/gps_20161005.tar.gz <TarInfo 'xian/gps_20161005' at 0x7f02149e5048>\n",
      "    in <tarfile.TarFile object at 0x7f024752fd68>\n",
      "\n",
      "10: october/gps_20161006.tar.gz <TarInfo 'xian/gps_20161006' at 0x7f02149e51d8>\n",
      "    in <tarfile.TarFile object at 0x7f024752ff98>\n",
      "\n",
      "11: october/gps_20161007.tar.gz <TarInfo 'xian/gps_20161007' at 0x7f02149e52a0>\n",
      "    in <tarfile.TarFile object at 0x7f024752fac8>\n",
      "\n",
      "12: october/gps_20161008.tar.gz <TarInfo 'xian/gps_20161008' at 0x7f02149e5368>\n",
      "    in <tarfile.TarFile object at 0x7f02149e30b8>\n",
      "\n",
      "13: october/gps_20161009.tar.gz <TarInfo 'xian/gps_20161009' at 0x7f02149e5430>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3198>\n",
      "\n",
      "14: october/gps_20161010.tar.gz <TarInfo 'xian/gps_20161010' at 0x7f02149e5110>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3278>\n",
      "\n",
      "15: october/gps_20161011.tar.gz <TarInfo 'xian/gps_20161011' at 0x7f02149e54f8>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3358>\n",
      "\n",
      "16: october/gps_20161012.tar.gz <TarInfo 'xian/gps_20161012' at 0x7f02149e55c0>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3438>\n",
      "\n",
      "17: october/gps_20161013.tar.gz <TarInfo 'xian/gps_20161013' at 0x7f02149e5688>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3518>\n",
      "\n",
      "18: october/gps_20161014.tar.gz <TarInfo 'xian/gps_20161014' at 0x7f02149e5818>\n",
      "    in <tarfile.TarFile object at 0x7f02149e35f8>\n",
      "\n",
      "19: october/gps_20161015.tar.gz <TarInfo 'xian/gps_20161015' at 0x7f02149e58e0>\n",
      "    in <tarfile.TarFile object at 0x7f02149e36d8>\n",
      "\n",
      "20: october/gps_20161016.tar.gz <TarInfo 'xian/gps_20161016' at 0x7f02149e59a8>\n",
      "    in <tarfile.TarFile object at 0x7f02149e37b8>\n",
      "\n",
      "21: october/gps_20161017.tar.gz <TarInfo 'xian/gps_20161017' at 0x7f02149e5cc8>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3898>\n",
      "\n",
      "22: october/gps_20161018.tar.gz <TarInfo 'xian/gps_20161018' at 0x7f02149fe110>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3978>\n",
      "\n",
      "23: october/gps_20161019.tar.gz <TarInfo 'xian/gps_20161019' at 0x7f02149fe1d8>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3a58>\n",
      "\n",
      "24: october/gps_20161020.tar.gz <TarInfo 'xian/gps_20161020' at 0x7f02149fe2a0>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3b38>\n",
      "\n",
      "25: october/gps_20161021.tar.gz <TarInfo 'xian/gps_20161021' at 0x7f02149fe368>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3b70>\n",
      "\n",
      "26: october/gps_20161022.tar.gz <TarInfo 'xian/gps_20161022' at 0x7f02149fe430>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3d30>\n",
      "\n",
      "27: october/gps_20161023.tar.gz <TarInfo 'xian/gps_20161023' at 0x7f02149e5750>\n",
      "    in <tarfile.TarFile object at 0x7f02149e3cf8>\n",
      "\n",
      "28: october/gps_20161024.tar.gz <TarInfo 'xian/gps_20161024' at 0x7f02149fe4f8>\n",
      "    in <tarfile.TarFile object at 0x7f0206761898>\n",
      "\n",
      "29: october/gps_20161025.tar.gz <TarInfo 'xian/gps_20161025' at 0x7f02149fe5c0>\n",
      "    in <tarfile.TarFile object at 0x7f0206761780>\n",
      "\n",
      "30: october/gps_20161030.tar.gz <TarInfo 'xian/gps_20161030' at 0x7f02149fe688>\n",
      "    in <tarfile.TarFile object at 0x7f0206761cf8>\n",
      "\n",
      "31: october/gps_20161031.tar.gz <TarInfo 'xian/gps_20161031' at 0x7f02149fe750>\n",
      "    in <tarfile.TarFile object at 0x7f0206760828>\n",
      "\n",
      "1: november/gps_20161101.tar.gz <TarInfo 'xian/gps_20161101' at 0x7f02149fe818>\n",
      "    in <tarfile.TarFile object at 0x7f02067606a0>\n",
      "\n",
      "2: november/gps_20161102.tar.gz <TarInfo 'xian/gps_20161102' at 0x7f02149fe8e0>\n",
      "    in <tarfile.TarFile object at 0x7f0206760278>\n",
      "\n",
      "3: november/gps_20161103.tar.gz <TarInfo 'xian/gps_20161103' at 0x7f02149fe9a8>\n",
      "    in <tarfile.TarFile object at 0x7f0206760d30>\n",
      "\n",
      "4: november/gps_20161104.tar.gz <TarInfo 'xian/gps_20161104' at 0x7f02149fea70>\n",
      "    in <tarfile.TarFile object at 0x7f020675c748>\n",
      "\n",
      "5: november/gps_20161105.tar.gz <TarInfo 'xian/gps_20161105' at 0x7f02149feb38>\n",
      "    in <tarfile.TarFile object at 0x7f020675c6a0>\n",
      "\n",
      "6: november/gps_20161106.tar.gz <TarInfo 'xian/gps_20161106' at 0x7f02149fec00>\n",
      "    in <tarfile.TarFile object at 0x7f020675ab00>\n",
      "\n",
      "7: november/gps_20161107.tar.gz <TarInfo 'xian/gps_20161107' at 0x7f02149fecc8>\n",
      "    in <tarfile.TarFile object at 0x7f020675a7b8>\n",
      "\n",
      "8: november/gps_20161108.tar.gz <TarInfo 'xian/gps_20161108' at 0x7f02149fed90>\n",
      "    in <tarfile.TarFile object at 0x7f020675a048>\n",
      "\n",
      "9: november/gps_20161109.tar.gz <TarInfo 'xian/gps_20161109' at 0x7f02149fee58>\n",
      "    in <tarfile.TarFile object at 0x7f020675ec50>\n",
      "\n",
      "10: november/gps_20161123.tar.gz <TarInfo 'xian/gps_20161123' at 0x7f02149fef20>\n",
      "    in <tarfile.TarFile object at 0x7f020675e828>\n",
      "\n",
      "11: november/gps_20161110.tar.gz <TarInfo 'xian/gps_20161110' at 0x7f024755b048>\n",
      "    in <tarfile.TarFile object at 0x7f020675ef98>\n",
      "\n",
      "12: november/gps_20161111.tar.gz <TarInfo 'xian/gps_20161111' at 0x7f024755b110>\n",
      "    in <tarfile.TarFile object at 0x7f0206767e10>\n",
      "\n",
      "13: november/gps_20161112.tar.gz <TarInfo 'xian/gps_20161112' at 0x7f024755b1d8>\n",
      "    in <tarfile.TarFile object at 0x7f02067677b8>\n",
      "\n",
      "14: november/gps_20161113.tar.gz <TarInfo 'xian/gps_20161113' at 0x7f024755b2a0>\n",
      "    in <tarfile.TarFile object at 0x7f0206767320>\n",
      "\n",
      "15: november/gps_20161114.tar.gz <TarInfo 'xian/gps_20161114' at 0x7f024755b368>\n",
      "    in <tarfile.TarFile object at 0x7f0206767fd0>\n",
      "\n",
      "16: november/gps_20161115.tar.gz <TarInfo 'xian/gps_20161115' at 0x7f024755b430>\n",
      "    in <tarfile.TarFile object at 0x7f0206762da0>\n",
      "\n",
      "17: november/gps_20161116.tar.gz <TarInfo 'xian/gps_20161116' at 0x7f024755b4f8>\n",
      "    in <tarfile.TarFile object at 0x7f0206762710>\n",
      "\n",
      "18: november/gps_20161117.tar.gz <TarInfo 'xian/gps_20161117' at 0x7f024755b5c0>\n",
      "    in <tarfile.TarFile object at 0x7f02067621d0>\n",
      "\n",
      "19: november/gps_20161118.tar.gz <TarInfo 'xian/gps_20161118' at 0x7f024755b688>\n",
      "    in <tarfile.TarFile object at 0x7f02067620f0>\n",
      "\n",
      "20: november/gps_20161119.tar.gz <TarInfo 'xian/gps_20161119' at 0x7f024755b750>\n",
      "    in <tarfile.TarFile object at 0x7f020676cc88>\n",
      "\n",
      "21: november/gps_20161120.tar.gz <TarInfo 'xian/gps_20161120' at 0x7f024755b818>\n",
      "    in <tarfile.TarFile object at 0x7f020676c828>\n",
      "\n",
      "22: november/gps_20161121.tar.gz <TarInfo 'xian/gps_20161121' at 0x7f024755b8e0>\n",
      "    in <tarfile.TarFile object at 0x7f020676c588>\n",
      "\n",
      "23: november/gps_20161122.tar.gz <TarInfo 'xian/gps_20161122' at 0x7f024755b9a8>\n",
      "    in <tarfile.TarFile object at 0x7f020676cd30>\n",
      "\n",
      "24: november/gps_20161124.tar.gz <TarInfo 'xian/gps_20161124' at 0x7f024755ba70>\n",
      "    in <tarfile.TarFile object at 0x7f020676b390>\n",
      "\n",
      "25: november/gps_20161125.tar.gz <TarInfo 'xian/gps_20161125' at 0x7f024755bb38>\n",
      "    in <tarfile.TarFile object at 0x7f020676b400>\n",
      "\n",
      "26: november/gps_20161126.tar.gz <TarInfo 'xian/gps_20161126' at 0x7f024755bc00>\n",
      "    in <tarfile.TarFile object at 0x7f0206765d30>\n",
      "\n",
      "27: november/gps_20161127.tar.gz <TarInfo 'xian/gps_20161127' at 0x7f024755bcc8>\n",
      "    in <tarfile.TarFile object at 0x7f0206765dd8>\n",
      "\n",
      "28: november/gps_20161128.tar.gz <TarInfo 'xian/gps_20161128' at 0x7f024755bd90>\n",
      "    in <tarfile.TarFile object at 0x7f0206763860>\n",
      "\n",
      "29: november/gps_20161129.tar.gz <TarInfo 'xian/gps_20161129' at 0x7f024755be58>\n",
      "    in <tarfile.TarFile object at 0x7f020676a630>\n",
      "\n",
      "30: november/gps_20161130.tar.gz <TarInfo 'xian/gps_20161130' at 0x7f024755bf20>\n",
      "    in <tarfile.TarFile object at 0x7f020676a1d0>\n",
      "\n",
      "1: december/gps_20161201.tar.gz <TarInfo 'gps_20161201' at 0x7f02149dd048>\n",
      "    in <tarfile.TarFile object at 0x7f020676a0f0>\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# run through the directories and generate a list of filenames\n",
    "month_folder = ['october', 'november', 'december']\n",
    "tarfile_ls = []\n",
    "\n",
    "# iterate through each folder and generate a list of files\n",
    "for month_name in month_folder:\n",
    "    for root, dirs, files in os.walk(os.path.join(month_name)):\n",
    "        for n, f in enumerate(files, 1):\n",
    "            tf = tarfile.open(os.path.join(month_name, f))\n",
    "            assert len(tf.members) == 1  # check if there is only one file\n",
    "            \n",
    "            # add file member to list\n",
    "            tarfile_ls.append(tf)\n",
    "            \n",
    "            print(\n",
    "                '{0:d}: {1} {2}\\n'\n",
    "                '    in {3}\\n'.format(n, os.path.join(month_name, f), tf.members[0], tf))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- For each \\*.tar.gz data file, compute the number of users (drivers) on the road \n",
    "- Input the number of observations e.g. `groupby` into a 32 x 32 cell matrix.\n",
    "- Each cell is calculated over every 5 minute interval\n",
    "- Cell matrix is transformed into 1024 columns\n",
    "- Haversine distance formula:\n",
    "\n",
    "\\begin{equation}\n",
    "2r\\arcsin\\Big(\\sqrt{\\sin^2(\\frac{\\phi_2-\\phi_1}{2})+\\cos(\\phi_1)\\cos(\\phi_2)\\sin^2(\\frac{\\theta_2-\\theta_1}{2})}\\Big)\n",
    "\\end{equation}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# functions for speed and distance calculations\n",
    "\n",
    "def calculate_distance(lat1, lon1, lat2, lon2):\n",
    "    # haversine distance\n",
    "    earth_radius = 6371  # km\n",
    "    dlat = np.radians(lat2-lat1)\n",
    "    dlon = np.radians(lon2-lon1)\n",
    "    a = np.sin(dlat/2) ** 2 + np.cos(np.radians(lat1)) * np.cos(np.radians(lat2)) * np.sin(dlon/2) ** 2\n",
    "    distance = 2 * earth_radius * np.arcsin(np.sqrt(a))\n",
    "    return distance * 1000  # meters\n",
    "\n",
    "def calculate_speed(x):\n",
    "    d = {}\n",
    "    d['Timestamp_begin'] = pd.to_datetime(x['Timestamp'].astype(int).min())\n",
    "    d['Speed_mean'] = x['Speed'].mean()\n",
    "    return pd.Series(d, index=['Timestamp_begin', 'Speed_mean'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Algorithm to process each datefile in the GAIA dataset from October 1st to December 1st\n",
    "- Speed data is calculated using the haversine distance and interval between each observation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date range processed: 2016-10-26 00:00:00+08:00 to 2016-10-27 00:00:00+08:00\n",
      "1: Extraction of file <TarInfo 'xian/gps_20161026' at 0x7f02155b89a8> time elapsed: 1.17 minutes\n",
      "Date range processed: 2016-10-27 00:00:00+08:00 to 2016-10-28 00:00:00+08:00\n",
      "2: Extraction of file <TarInfo 'xian/gps_20161027' at 0x7f02155b85c0> time elapsed: 2.39 minutes\n",
      "Date range processed: 2016-10-28 00:00:00+08:00 to 2016-10-29 00:00:00+08:00\n",
      "3: Extraction of file <TarInfo 'xian/gps_20161028' at 0x7f02155b8b38> time elapsed: 3.76 minutes\n",
      "Date range processed: 2016-10-29 00:00:00+08:00 to 2016-10-30 00:00:00+08:00\n",
      "4: Extraction of file <TarInfo 'xian/gps_20161029' at 0x7f02155b8c00> time elapsed: 5.04 minutes\n",
      "Date range processed: 2016-10-01 00:00:00+08:00 to 2016-10-02 00:00:00+08:00\n",
      "5: Extraction of file <TarInfo 'xian/gps_20161001' at 0x7f02155b8cc8> time elapsed: 6.29 minutes\n",
      "Date range processed: 2016-10-02 00:00:00+08:00 to 2016-10-03 00:00:00+08:00\n",
      "6: Extraction of file <TarInfo 'xian/gps_20161002' at 0x7f02155b8d90> time elapsed: 7.62 minutes\n",
      "Date range processed: 2016-10-03 00:00:00+08:00 to 2016-10-04 00:00:00+08:00\n",
      "7: Extraction of file <TarInfo 'xian/gps_20161003' at 0x7f02155b8e58> time elapsed: 8.91 minutes\n",
      "Date range processed: 2016-10-04 00:00:00+08:00 to 2016-10-05 00:00:00+08:00\n",
      "8: Extraction of file <TarInfo 'xian/gps_20161004' at 0x7f02155b8f20> time elapsed: 10.25 minutes\n",
      "Date range processed: 2016-10-05 00:00:00+08:00 to 2016-10-06 00:00:00+08:00\n",
      "9: Extraction of file <TarInfo 'xian/gps_20161005' at 0x7f02149e5048> time elapsed: 11.55 minutes\n",
      "Date range processed: 2016-10-06 00:00:00+08:00 to 2016-10-07 00:00:00+08:00\n",
      "10: Extraction of file <TarInfo 'xian/gps_20161006' at 0x7f02149e51d8> time elapsed: 12.82 minutes\n",
      "Date range processed: 2016-10-07 00:00:00+08:00 to 2016-10-08 00:00:00+08:00\n",
      "11: Extraction of file <TarInfo 'xian/gps_20161007' at 0x7f02149e52a0> time elapsed: 13.91 minutes\n",
      "Date range processed: 2016-10-08 00:00:00+08:00 to 2016-10-09 00:00:00+08:00\n",
      "12: Extraction of file <TarInfo 'xian/gps_20161008' at 0x7f02149e5368> time elapsed: 14.89 minutes\n",
      "Date range processed: 2016-10-09 00:00:00+08:00 to 2016-10-10 00:00:00+08:00\n",
      "13: Extraction of file <TarInfo 'xian/gps_20161009' at 0x7f02149e5430> time elapsed: 15.92 minutes\n",
      "Date range processed: 2016-10-10 00:00:00+08:00 to 2016-10-11 00:00:00+08:00\n",
      "14: Extraction of file <TarInfo 'xian/gps_20161010' at 0x7f02149e5110> time elapsed: 16.95 minutes\n",
      "Date range processed: 2016-10-11 00:00:00+08:00 to 2016-10-12 00:00:00+08:00\n",
      "15: Extraction of file <TarInfo 'xian/gps_20161011' at 0x7f02149e54f8> time elapsed: 17.94 minutes\n",
      "Date range processed: 2016-10-12 00:00:00+08:00 to 2016-10-13 00:00:00+08:00\n",
      "16: Extraction of file <TarInfo 'xian/gps_20161012' at 0x7f02149e55c0> time elapsed: 18.99 minutes\n",
      "Date range processed: 2016-10-13 00:00:00+08:00 to 2016-10-14 00:00:00+08:00\n",
      "17: Extraction of file <TarInfo 'xian/gps_20161013' at 0x7f02149e5688> time elapsed: 19.79 minutes\n",
      "Date range processed: 2016-10-14 00:00:00+08:00 to 2016-10-15 00:00:00+08:00\n",
      "18: Extraction of file <TarInfo 'xian/gps_20161014' at 0x7f02149e5818> time elapsed: 20.73 minutes\n",
      "Date range processed: 2016-10-15 00:00:00+08:00 to 2016-10-16 00:00:00+08:00\n",
      "19: Extraction of file <TarInfo 'xian/gps_20161015' at 0x7f02149e58e0> time elapsed: 21.80 minutes\n",
      "Date range processed: 2016-10-16 00:00:00+08:00 to 2016-10-17 00:00:00+08:00\n",
      "20: Extraction of file <TarInfo 'xian/gps_20161016' at 0x7f02149e59a8> time elapsed: 22.64 minutes\n",
      "Date range processed: 2016-10-17 00:00:00+08:00 to 2016-10-17 23:55:00+08:00\n",
      "21: Extraction of file <TarInfo 'xian/gps_20161017' at 0x7f02149e5cc8> time elapsed: 23.37 minutes\n",
      "Date range processed: 2016-10-18 00:00:00+08:00 to 2016-10-19 00:00:00+08:00\n",
      "22: Extraction of file <TarInfo 'xian/gps_20161018' at 0x7f02149fe110> time elapsed: 24.40 minutes\n",
      "Date range processed: 2016-10-19 00:00:00+08:00 to 2016-10-20 00:00:00+08:00\n",
      "23: Extraction of file <TarInfo 'xian/gps_20161019' at 0x7f02149fe1d8> time elapsed: 25.41 minutes\n",
      "Date range processed: 2016-10-20 00:00:00+08:00 to 2016-10-21 00:00:00+08:00\n",
      "24: Extraction of file <TarInfo 'xian/gps_20161020' at 0x7f02149fe2a0> time elapsed: 26.48 minutes\n",
      "Date range processed: 2016-10-21 00:00:00+08:00 to 2016-10-22 00:00:00+08:00\n",
      "25: Extraction of file <TarInfo 'xian/gps_20161021' at 0x7f02149fe368> time elapsed: 27.77 minutes\n",
      "Date range processed: 2016-10-22 00:00:00+08:00 to 2016-10-23 00:00:00+08:00\n",
      "26: Extraction of file <TarInfo 'xian/gps_20161022' at 0x7f02149fe430> time elapsed: 29.30 minutes\n",
      "Date range processed: 2016-10-23 00:00:00+08:00 to 2016-10-24 00:00:00+08:00\n",
      "27: Extraction of file <TarInfo 'xian/gps_20161023' at 0x7f02149e5750> time elapsed: 30.48 minutes\n",
      "Date range processed: 2016-10-24 00:00:00+08:00 to 2016-10-25 00:00:00+08:00\n",
      "28: Extraction of file <TarInfo 'xian/gps_20161024' at 0x7f02149fe4f8> time elapsed: 31.64 minutes\n",
      "Date range processed: 2016-10-25 00:00:00+08:00 to 2016-10-26 00:00:00+08:00\n",
      "29: Extraction of file <TarInfo 'xian/gps_20161025' at 0x7f02149fe5c0> time elapsed: 32.87 minutes\n",
      "Date range processed: 2016-10-30 00:00:00+08:00 to 2016-10-31 00:00:00+08:00\n",
      "30: Extraction of file <TarInfo 'xian/gps_20161030' at 0x7f02149fe688> time elapsed: 33.99 minutes\n",
      "Date range processed: 2016-10-31 00:00:00+08:00 to 2016-11-01 00:00:00+08:00\n",
      "31: Extraction of file <TarInfo 'xian/gps_20161031' at 0x7f02149fe750> time elapsed: 35.04 minutes\n",
      "Date range processed: 2016-11-01 00:00:00+08:00 to 2016-11-02 00:00:00+08:00\n",
      "32: Extraction of file <TarInfo 'xian/gps_20161101' at 0x7f02149fe818> time elapsed: 36.07 minutes\n",
      "Date range processed: 2016-11-02 00:00:00+08:00 to 2016-11-03 00:00:00+08:00\n",
      "33: Extraction of file <TarInfo 'xian/gps_20161102' at 0x7f02149fe8e0> time elapsed: 37.13 minutes\n",
      "Date range processed: 2016-11-03 00:00:00+08:00 to 2016-11-04 00:00:00+08:00\n",
      "34: Extraction of file <TarInfo 'xian/gps_20161103' at 0x7f02149fe9a8> time elapsed: 38.22 minutes\n",
      "Date range processed: 2016-11-04 00:00:00+08:00 to 2016-11-05 00:00:00+08:00\n",
      "35: Extraction of file <TarInfo 'xian/gps_20161104' at 0x7f02149fea70> time elapsed: 39.48 minutes\n",
      "Date range processed: 2016-11-05 00:00:00+08:00 to 2016-11-06 00:00:00+08:00\n",
      "36: Extraction of file <TarInfo 'xian/gps_20161105' at 0x7f02149feb38> time elapsed: 40.88 minutes\n",
      "Date range processed: 2016-11-06 00:00:00+08:00 to 2016-11-07 00:00:00+08:00\n",
      "37: Extraction of file <TarInfo 'xian/gps_20161106' at 0x7f02149fec00> time elapsed: 42.04 minutes\n",
      "Date range processed: 2016-11-07 00:00:00+08:00 to 2016-11-08 00:00:00+08:00\n",
      "38: Extraction of file <TarInfo 'xian/gps_20161107' at 0x7f02149fecc8> time elapsed: 43.09 minutes\n",
      "Date range processed: 2016-11-08 00:00:00+08:00 to 2016-11-09 00:00:00+08:00\n",
      "39: Extraction of file <TarInfo 'xian/gps_20161108' at 0x7f02149fed90> time elapsed: 44.14 minutes\n",
      "Date range processed: 2016-11-09 00:00:00+08:00 to 2016-11-10 00:00:00+08:00\n",
      "40: Extraction of file <TarInfo 'xian/gps_20161109' at 0x7f02149fee58> time elapsed: 45.18 minutes\n",
      "Date range processed: 2016-11-23 00:00:00+08:00 to 2016-11-24 00:00:00+08:00\n",
      "41: Extraction of file <TarInfo 'xian/gps_20161123' at 0x7f02149fef20> time elapsed: 46.11 minutes\n",
      "Date range processed: 2016-11-10 00:00:00+08:00 to 2016-11-11 00:00:00+08:00\n",
      "42: Extraction of file <TarInfo 'xian/gps_20161110' at 0x7f024755b048> time elapsed: 47.16 minutes\n",
      "Date range processed: 2016-11-11 00:00:00+08:00 to 2016-11-12 00:00:00+08:00\n",
      "43: Extraction of file <TarInfo 'xian/gps_20161111' at 0x7f024755b110> time elapsed: 48.44 minutes\n",
      "Date range processed: 2016-11-12 00:00:00+08:00 to 2016-11-13 00:00:00+08:00\n",
      "44: Extraction of file <TarInfo 'xian/gps_20161112' at 0x7f024755b1d8> time elapsed: 49.69 minutes\n",
      "Date range processed: 2016-11-13 00:00:00+08:00 to 2016-11-14 00:00:00+08:00\n",
      "45: Extraction of file <TarInfo 'xian/gps_20161113' at 0x7f024755b2a0> time elapsed: 50.71 minutes\n",
      "Date range processed: 2016-11-14 00:00:00+08:00 to 2016-11-15 00:00:00+08:00\n",
      "46: Extraction of file <TarInfo 'xian/gps_20161114' at 0x7f024755b368> time elapsed: 51.61 minutes\n",
      "Date range processed: 2016-11-15 00:00:00+08:00 to 2016-11-16 00:00:00+08:00\n",
      "47: Extraction of file <TarInfo 'xian/gps_20161115' at 0x7f024755b430> time elapsed: 52.50 minutes\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Date range processed: 2016-11-16 00:00:00+08:00 to 2016-11-17 00:00:00+08:00\n",
      "48: Extraction of file <TarInfo 'xian/gps_20161116' at 0x7f024755b4f8> time elapsed: 53.47 minutes\n",
      "Date range processed: 2016-11-17 00:00:00+08:00 to 2016-11-18 00:00:00+08:00\n",
      "49: Extraction of file <TarInfo 'xian/gps_20161117' at 0x7f024755b5c0> time elapsed: 54.48 minutes\n",
      "Date range processed: 2016-11-18 00:00:00+08:00 to 2016-11-19 00:00:00+08:00\n",
      "50: Extraction of file <TarInfo 'xian/gps_20161118' at 0x7f024755b688> time elapsed: 55.61 minutes\n",
      "Date range processed: 2016-11-19 00:00:00+08:00 to 2016-11-20 00:00:00+08:00\n",
      "51: Extraction of file <TarInfo 'xian/gps_20161119' at 0x7f024755b750> time elapsed: 56.89 minutes\n",
      "Date range processed: 2016-11-20 00:00:00+08:00 to 2016-11-21 00:00:00+08:00\n",
      "52: Extraction of file <TarInfo 'xian/gps_20161120' at 0x7f024755b818> time elapsed: 57.97 minutes\n",
      "Date range processed: 2016-11-21 00:00:00+08:00 to 2016-11-22 00:00:00+08:00\n",
      "53: Extraction of file <TarInfo 'xian/gps_20161121' at 0x7f024755b8e0> time elapsed: 59.02 minutes\n",
      "Date range processed: 2016-11-22 00:00:00+08:00 to 2016-11-23 00:00:00+08:00\n",
      "54: Extraction of file <TarInfo 'xian/gps_20161122' at 0x7f024755b9a8> time elapsed: 59.87 minutes\n",
      "Date range processed: 2016-11-24 00:00:00+08:00 to 2016-11-25 00:00:00+08:00\n",
      "55: Extraction of file <TarInfo 'xian/gps_20161124' at 0x7f024755ba70> time elapsed: 60.95 minutes\n",
      "Date range processed: 2016-11-25 00:00:00+08:00 to 2016-11-26 00:00:00+08:00\n",
      "56: Extraction of file <TarInfo 'xian/gps_20161125' at 0x7f024755bb38> time elapsed: 62.25 minutes\n",
      "Date range processed: 2016-11-26 00:00:00+08:00 to 2016-11-27 00:00:00+08:00\n",
      "57: Extraction of file <TarInfo 'xian/gps_20161126' at 0x7f024755bc00> time elapsed: 63.62 minutes\n",
      "Date range processed: 2016-11-27 00:00:00+08:00 to 2016-11-28 00:00:00+08:00\n",
      "58: Extraction of file <TarInfo 'xian/gps_20161127' at 0x7f024755bcc8> time elapsed: 64.72 minutes\n",
      "Date range processed: 2016-11-28 00:00:00+08:00 to 2016-11-29 00:00:00+08:00\n",
      "59: Extraction of file <TarInfo 'xian/gps_20161128' at 0x7f024755bd90> time elapsed: 65.77 minutes\n",
      "Date range processed: 2016-11-29 00:00:00+08:00 to 2016-11-30 00:00:00+08:00\n",
      "60: Extraction of file <TarInfo 'xian/gps_20161129' at 0x7f024755be58> time elapsed: 66.85 minutes\n",
      "Date range processed: 2016-11-30 00:00:00+08:00 to 2016-12-01 00:00:00+08:00\n",
      "61: Extraction of file <TarInfo 'xian/gps_20161130' at 0x7f024755bf20> time elapsed: 67.93 minutes\n",
      "Date range processed: 2016-12-01 00:00:00+08:00 to 2016-12-02 00:00:00+08:00\n",
      "62: Extraction of file <TarInfo 'gps_20161201' at 0x7f02149dd048> time elapsed: 68.92 minutes\n"
     ]
    }
   ],
   "source": [
    "assert datatable.sum().sum() == 0  # check is datatable is empty\n",
    "\n",
    "start_time = time.time()\n",
    "for n, tf in enumerate(tarfile_ls, 1):\n",
    "\n",
    "    # read data directly from inside the tar file\n",
    "    with io.BytesIO(tf.extractfile(tf.members[0]).read()) as xian_gps_info:\n",
    "        df = pd.read_csv(\n",
    "            filepath_or_buffer=xian_gps_info, \n",
    "            sep=',', \n",
    "            header=None, \n",
    "            names=['Driver_ID', 'Order_ID', 'Timestamp', 'Longitude', 'Latitude']\n",
    "        )\n",
    "        \n",
    "        # convert timestamp to datetime dtype\n",
    "        df['Timestamp'] = pd.to_datetime(df['Timestamp'].values, unit='s')\n",
    "        \n",
    "        # assign segment numbers to each observation\n",
    "        df['Longitude_seg'] = np.floor((df['Longitude'].values-lon_min)/lon_range*n_segments).astype('int')\n",
    "        df['Latitude_seg'] = np.floor((df['Latitude'].values-lat_min)/lat_range*n_segments).astype('int')\n",
    "        df['segment_num'] = df['Longitude_seg'].values*n_segments + df['Latitude_seg'].values\n",
    "        df.loc[\n",
    "            (df['Longitude']>108.94615156073496) & (df['Longitude']<108.94765628015638) & \n",
    "            (df['Latitude']>34.2324012260476) & (df['Latitude']<34.23940650580562), 'zone_interest'\n",
    "        ] = 1.\n",
    "        df['zone_interest'] = df['zone_interest'].fillna(0.)\n",
    "        \n",
    "        #################\n",
    "        # Process speed #\n",
    "        #################\n",
    "        \n",
    "        # compute speed per observation in road segment\n",
    "        df_roadsection = df.loc[df['zone_interest']==1.]\n",
    "        df_roadsection = df_roadsection.sort_values(['Driver_ID', 'Order_ID', 'Timestamp'])\n",
    "        df_roadsection[['Time_delta', 'Longitude_delta', 'Latitude_delta']] = df_roadsection.sort_values(\n",
    "            ['Driver_ID', 'Order_ID', 'Timestamp']).loc[:, ['Timestamp', 'Longitude', 'Latitude']].diff()\n",
    "        \n",
    "        # locate and ignore points where [Driver_ID, Order_ID] changes or between trajectories\n",
    "        df_roadsection.loc[\n",
    "            (\n",
    "                (df_roadsection.loc[:, ['Time_delta']] > pd.Timedelta('9 sec')) | \n",
    "                (df_roadsection.loc[:, ['Time_delta']] < pd.Timedelta('1 sec'))  | \n",
    "                (pd.isnull(df_roadsection.loc[:, ['Time_delta']]))\n",
    "            ).values.flatten(), ['Time_delta']] = np.nan\n",
    "        \n",
    "        df_roadsection['Time_delta'] = df_roadsection['Time_delta'].dt.total_seconds()\n",
    "        df_roadsection['Speed'] = calculate_distance(\n",
    "            0, 0, \n",
    "            df_roadsection['Latitude_delta'], df_roadsection['Longitude_delta']\n",
    "        ) / df_roadsection['Time_delta'] * 3.6\n",
    "\n",
    "        # infer direction from Latitude change: -ve == traveling south, +ve == traveling north\n",
    "        conditions = [\n",
    "            ((df_roadsection['Latitude_delta'] == 0.) & \n",
    "             (df_roadsection['Speed'] == 0.) & \n",
    "             (df_roadsection['Time_delta'] > 0.)), \n",
    "            ((df_roadsection['Latitude_delta'] < 0.) & \n",
    "             (df_roadsection['Time_delta'] > 0.)), \n",
    "            ((df_roadsection['Latitude_delta'] > 0.) & \n",
    "             (df_roadsection['Time_delta'] > 0.))\n",
    "        ]\n",
    "        choices = ['NA', 'south', 'north']\n",
    "        df_roadsection['Direction'] = np.select(conditions, choices, default='NULL')\n",
    "\n",
    "        def nashift(x):\n",
    "        #     print( pd.Series(x['Direction'].value_counts().index[0]) )\n",
    "            return x['Direction'].value_counts().index[0]\n",
    "\n",
    "        a = df_roadsection.groupby(['Driver_ID', 'Order_ID']).apply(nashift)\n",
    "        df_roadsection = df_roadsection.set_index(['Driver_ID', 'Order_ID']).join(\n",
    "            pd.DataFrame(a, columns=['Dir'])).reset_index().set_index(df_roadsection.index)\n",
    "        df_roadsection.loc[df_roadsection['Direction']=='NA', 'Direction'] = df_roadsection.loc[\n",
    "            df_roadsection['Direction']=='NA', 'Dir']\n",
    "        df_roadsection = df_roadsection.drop(['Dir'], axis=1)\n",
    "        \n",
    "        # aggregate mean speed for each direction\n",
    "        df_speed = df_roadsection[\n",
    "            (df_roadsection['Direction']!='NULL') & (df_roadsection['Direction'] != 'NA')\n",
    "        ].groupby(['Driver_ID', 'Order_ID', 'Direction']).apply(calculate_speed)\n",
    "        df_speed = df_speed.groupby([\n",
    "            pd.Grouper(key='Timestamp_begin', freq='5min'), 'Direction'])[['Speed_mean']].mean().unstack(\n",
    "            )\n",
    "        \n",
    "        # localize to system timezone and convert to Asia/Shanghai timezone\n",
    "        df_speed.index = df_speed.index.tz_localize('UTC').tz_convert(pytz.timezone('Asia/Shanghai'))\n",
    "        \n",
    "        # reset column label to ['Speed_north', 'Speed_south']\n",
    "        df_speed.columns = ['Speed_' + df_speed.columns.levels[1][0], 'Speed_' + df_speed.columns.levels[1][1]]\n",
    "        \n",
    "        # write to speedtable\n",
    "        speedtable_concat = pd.concat([speedtable, df_speed], sort=False)\n",
    "        speedtable = speedtable_concat.groupby(speedtable_concat.index).mean()\n",
    "        \n",
    "        ###################\n",
    "        # process density #\n",
    "        ###################\n",
    "        \n",
    "        # compute the total count of drivers in each zone 'segment_num'\n",
    "        count_info = df.loc[df['zone_interest']==0.].groupby([\n",
    "            pd.Grouper(key='Timestamp', freq='5min'), 'segment_num'])['Driver_ID'].count().unstack(fill_value=0)\n",
    "        \n",
    "        # localize to system timezone and convert to Asia/Shanghai timezone\n",
    "        count_info.index = count_info.index.tz_localize('UTC').tz_convert(pytz.timezone('Asia/Shanghai'))\n",
    "        \n",
    "        print('Date range processed: {} to {}'.format(count_info.index.min(), count_info.index.max()))\n",
    "        count_info.columns.name = None\n",
    "        datatable = datatable.add(count_info, fill_value=0)\n",
    "    \n",
    "    print('{}: Extraction of file {} '\n",
    "          'time elapsed: {:.2f} minutes'.format(n, tf.members[0], (time.time()-start_time)/60.))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# slice speedtable to only contain entries for 2016-10-01 00:00:00 to 2016-12-01 23:55:00\n",
    "speedtable = speedtable.fillna(0.).loc['2016-10-01 00:00:00+08:00':'2016-12-01 23:55:00+08:00']\n",
    "\n",
    "# slice datatable to only contain entries for 2016-10-01 00:00:00 to 2016-12-01 23:55:00\n",
    "datatable = datatable.fillna(0.).loc['2016-10-01 00:00:00+08:00':'2016-12-01 23:55:00+08:00', '0':]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# finally, save datatable to csv\n",
    "datatable.to_csv('datatable_full.csv')\n",
    "speedtable.to_csv('speedtable_full.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Continue](02_preparation.ipynb) to next step to prepare datasets for model training"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}