Download_and_Process_PEMS_traffic_volume_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import shutil, os, csv, itertools, glob\n",
    "import math\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pickle as pk\n",
    "import time\n",
    "from os import listdir\n",
    "from os.path import isfile, join\n",
    "import collections\n",
    "import matplotlib.pyplot as plt\n",
    "import random\n",
    "import argparse\n",
    "import gzip\n",
    "from geopy.distance import distance\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Raw data downloading:\n",
    "## 1. visit PEMS website http://pems.dot.ca.gov\n",
    "## 2. Select District 7 LA/Ventura, click \"Data Clearinghouse'\n",
    "## 3. Select Type: Station Hour, District 7, download hourly traffic volume data, e.g., d07_text_station_hour_2018_01.txt.gz\n",
    "## 4. Select Tpye: Station Metadata, District 7, download station meta data, e.g., d07_text_meta_2017_12_23.txt\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process raw traffic volume data, only 2 hourly traffic volumes are missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mypath = \"../../data/Pems_LA_volume/volume\"\n",
    "onlyfiles = [f for f in listdir(mypath) if isfile(join(mypath, f))]\n",
    "\n",
    "res = []\n",
    "for filename in onlyfiles:\n",
    "    with gzip.open(\"../../data/Pems_LA_volume/volume/\"+filename, 'rb') as f:\n",
    "        for line in f:\n",
    "            line = str(line)\n",
    "            line = line[2:].split(',') \n",
    "            res.append(line[0:10])\n",
    "            \n",
    "res = pd.DataFrame(res)\n",
    "res.columns = ['time', 'stationid', 'district', 'route', 'travel_dict', 'lanetype', 'station_length', 'samples', 'observed', 'total_flow']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# lane type\n",
    "#CD (Coll/Dist)\n",
    "#CH (Conventional Highway)\n",
    "#FF (Fwy-Fwy connector)\n",
    "#FR (Off Ramp)\n",
    "#HV (HOV)\n",
    "#ML (Mainline)\n",
    "#OR (On Ramp)\n",
    "\n",
    "# Sum of 5-minute flows over the hour. \n",
    "#Note that the basic 5-minute rollup normalizes flow by the number of good samples received from the controller.\n",
    "#Veh/Hour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# delete empty flow\n",
    "res = res[res['total_flow'] != '']\n",
    "\n",
    "res_ML = res[res['lanetype'] == 'ML']\n",
    "\n",
    "res_ML_group = res_ML.groupby(['stationid']).size().reset_index()\n",
    "res_ML_group.columns = ['stationid', 'count']\n",
    "\n",
    "res_ML_13102 = res_ML_group[res_ML_group['count'] == 13102] # most of sensors miss 2 hourly traffic volume, should be 13104 / 24\n",
    "print (res_ML_13102.shape) # 1858 sensors have 13102 records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_res = []\n",
    "\n",
    "def hourly_it(start, finish):\n",
    "     while finish > start:\n",
    "             yield start\n",
    "             start = start + timedelta(hours=1)\n",
    "             \n",
    "start = datetime(2018, 1, 1, 0)\n",
    "finish = datetime(2019, 7, 1, 0)\n",
    "for hour in hourly_it(start, finish):\n",
    "     final_res.append(hour)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_res = pd.DataFrame(final_res)\n",
    "final_res.columns = ['new_time']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "column_names = []\n",
    "for stid in res_ML_13102['stationid']:\n",
    "    res_ML_tem = res_ML[res_ML['stationid'] == stid]\n",
    "    res_ML_tem.loc[:, 'new_time'] = pd.to_datetime(res_ML_tem['time'])\n",
    "    final_res = pd.merge(final_res, res_ML_tem[['new_time', 'total_flow']], on='new_time', how='left')\n",
    "    column_names.append(stid)\n",
    "    print (stid)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_res.columns = ['new_time']+ column_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# whether it is NaN or not\n",
    "final_res[final_res.iloc[:, 1] != final_res.iloc[:, 1]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# there are two time points the data of which are all missing for all sensors\n",
    "final_res.iloc[1658, 1:] = (final_res.iloc[1657, 1:].astype(int) + final_res.iloc[1659, 1:].astype(int)) / 2\n",
    "final_res.iloc[10394, 1:] = (final_res.iloc[10393, 1:].astype(int) + final_res.iloc[10395, 1:].astype(int)) / 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_res.to_csv('traffic_volume_district7.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extract sensor information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensor = []\n",
    "f=open(\"../../data/Pems_LA_volume/sensor/d07_text_meta_2017_12_23.txt\", \"r\")\n",
    "lines = f.readlines()\n",
    "\n",
    "for x in lines:\n",
    "    sensor.append(x.split('\\t'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>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>10</th>\n",
       "      <th>11</th>\n",
       "      <th>12</th>\n",
       "      <th>13</th>\n",
       "      <th>14</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>715898</td>\n",
       "      <td>5</td>\n",
       "      <td>S</td>\n",
       "      <td>7</td>\n",
       "      <td>37</td>\n",
       "      <td>40032</td>\n",
       "      <td>.71</td>\n",
       "      <td>117.28</td>\n",
       "      <td>33.880183</td>\n",
       "      <td>-118.021787</td>\n",
       "      <td>.43</td>\n",
       "      <td>ML</td>\n",
       "      <td>3</td>\n",
       "      <td>PHOEBE</td>\n",
       "      <td>2029</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>715900</td>\n",
       "      <td>5</td>\n",
       "      <td>S</td>\n",
       "      <td>7</td>\n",
       "      <td>37</td>\n",
       "      <td>40032</td>\n",
       "      <td>1.06</td>\n",
       "      <td>117.63</td>\n",
       "      <td>33.882892</td>\n",
       "      <td>-118.026822</td>\n",
       "      <td></td>\n",
       "      <td>OR</td>\n",
       "      <td>1</td>\n",
       "      <td>VALLEY VIEW</td>\n",
       "      <td>3255</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>715901</td>\n",
       "      <td>5</td>\n",
       "      <td>N</td>\n",
       "      <td>7</td>\n",
       "      <td>37</td>\n",
       "      <td>40032</td>\n",
       "      <td>1.11</td>\n",
       "      <td>117.743</td>\n",
       "      <td>33.8834</td>\n",
       "      <td>-118.027451</td>\n",
       "      <td></td>\n",
       "      <td>OR</td>\n",
       "      <td>1</td>\n",
       "      <td>VALLEY VIEW</td>\n",
       "      <td>3268</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>715903</td>\n",
       "      <td>5</td>\n",
       "      <td>N</td>\n",
       "      <td>7</td>\n",
       "      <td>37</td>\n",
       "      <td>69154</td>\n",
       "      <td>1.56</td>\n",
       "      <td>118.193</td>\n",
       "      <td>33.886992</td>\n",
       "      <td>-118.034125</td>\n",
       "      <td></td>\n",
       "      <td>OR</td>\n",
       "      <td>1</td>\n",
       "      <td>ALONDRA</td>\n",
       "      <td>3269</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>\\n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>715906</td>\n",
       "      <td>5</td>\n",
       "      <td>S</td>\n",
       "      <td>7</td>\n",
       "      <td>37</td>\n",
       "      <td>52526</td>\n",
       "      <td>3.28</td>\n",
       "      <td>119.85</td>\n",
       "      <td>33.900669</td>\n",
       "      <td>-118.059422</td>\n",
       "      <td></td>\n",
       "      <td>OR</td>\n",
       "      <td>1</td>\n",
       "      <td>ROSECRANS</td>\n",
       "      <td>3252</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>\\n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       0  1  2  3   4      5     6        7          8            9    10  11  \\\n",
       "0  715898  5  S  7  37  40032   .71   117.28  33.880183  -118.021787  .43  ML   \n",
       "1  715900  5  S  7  37  40032  1.06   117.63  33.882892  -118.026822       OR   \n",
       "2  715901  5  N  7  37  40032  1.11  117.743    33.8834  -118.027451       OR   \n",
       "3  715903  5  N  7  37  69154  1.56  118.193  33.886992  -118.034125       OR   \n",
       "4  715906  5  S  7  37  52526  3.28   119.85  33.900669  -118.059422       OR   \n",
       "\n",
       "  12           13    14 15 16  17  \n",
       "0  3       PHOEBE  2029        \\n  \n",
       "1  1  VALLEY VIEW  3255        \\n  \n",
       "2  1  VALLEY VIEW  3268        \\n  \n",
       "3  1      ALONDRA  3269        \\n  \n",
       "4  1    ROSECRANS  3252        \\n  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sensor = pd.DataFrame(sensor)\n",
    "sensor.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensor.columns = ['ID', 'Fwy', 'Dir', 'District', 'County', 'City', 'State_PM', 'Abs_PM', 'Latitude', 'Longitude', 'Length', 'Type', 'Lanes', 'Name', 'User_ID_1', 'User_ID_2', 'User_ID_3', 'User_ID_4']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensor_select = sensor[['ID', 'Latitude', 'Longitude']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Unnamed: 0</th>\n",
       "      <th>new_time</th>\n",
       "      <th>715898</th>\n",
       "      <th>715918</th>\n",
       "      <th>715920</th>\n",
       "      <th>715929</th>\n",
       "      <th>715930</th>\n",
       "      <th>715933</th>\n",
       "      <th>715938</th>\n",
       "      <th>715944</th>\n",
       "      <th>...</th>\n",
       "      <th>775961</th>\n",
       "      <th>775975</th>\n",
       "      <th>775990</th>\n",
       "      <th>776019</th>\n",
       "      <th>776020</th>\n",
       "      <th>776052</th>\n",
       "      <th>776060</th>\n",
       "      <th>776066</th>\n",
       "      <th>776073</th>\n",
       "      <th>776081</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2018-01-01 00:00:00</td>\n",
       "      <td>887.0</td>\n",
       "      <td>1045.0</td>\n",
       "      <td>2931.0</td>\n",
       "      <td>3303.0</td>\n",
       "      <td>3067.0</td>\n",
       "      <td>2763.0</td>\n",
       "      <td>2994.0</td>\n",
       "      <td>2622.0</td>\n",
       "      <td>...</td>\n",
       "      <td>91.0</td>\n",
       "      <td>216.0</td>\n",
       "      <td>3808.0</td>\n",
       "      <td>2405.0</td>\n",
       "      <td>3231.0</td>\n",
       "      <td>2461.0</td>\n",
       "      <td>3730.0</td>\n",
       "      <td>3752.0</td>\n",
       "      <td>2637.0</td>\n",
       "      <td>4122.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-01-01 01:00:00</td>\n",
       "      <td>561.0</td>\n",
       "      <td>687.0</td>\n",
       "      <td>4558.0</td>\n",
       "      <td>5058.0</td>\n",
       "      <td>4559.0</td>\n",
       "      <td>3944.0</td>\n",
       "      <td>4417.0</td>\n",
       "      <td>3952.0</td>\n",
       "      <td>...</td>\n",
       "      <td>156.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>4294.0</td>\n",
       "      <td>3139.0</td>\n",
       "      <td>4047.0</td>\n",
       "      <td>3310.0</td>\n",
       "      <td>4808.0</td>\n",
       "      <td>5574.0</td>\n",
       "      <td>3641.0</td>\n",
       "      <td>6020.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2018-01-01 02:00:00</td>\n",
       "      <td>483.0</td>\n",
       "      <td>543.0</td>\n",
       "      <td>3167.0</td>\n",
       "      <td>3527.0</td>\n",
       "      <td>3470.0</td>\n",
       "      <td>3158.0</td>\n",
       "      <td>3452.0</td>\n",
       "      <td>3119.0</td>\n",
       "      <td>...</td>\n",
       "      <td>68.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>3348.0</td>\n",
       "      <td>2311.0</td>\n",
       "      <td>3057.0</td>\n",
       "      <td>1998.0</td>\n",
       "      <td>3387.0</td>\n",
       "      <td>3340.0</td>\n",
       "      <td>2079.0</td>\n",
       "      <td>3593.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2018-01-01 03:00:00</td>\n",
       "      <td>531.0</td>\n",
       "      <td>589.0</td>\n",
       "      <td>2126.0</td>\n",
       "      <td>2255.0</td>\n",
       "      <td>1808.0</td>\n",
       "      <td>1642.0</td>\n",
       "      <td>1834.0</td>\n",
       "      <td>1697.0</td>\n",
       "      <td>...</td>\n",
       "      <td>52.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>2457.0</td>\n",
       "      <td>1726.0</td>\n",
       "      <td>2400.0</td>\n",
       "      <td>1530.0</td>\n",
       "      <td>2884.0</td>\n",
       "      <td>2773.0</td>\n",
       "      <td>1595.0</td>\n",
       "      <td>3024.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2018-01-01 04:00:00</td>\n",
       "      <td>1088.0</td>\n",
       "      <td>1263.0</td>\n",
       "      <td>1452.0</td>\n",
       "      <td>1284.0</td>\n",
       "      <td>1336.0</td>\n",
       "      <td>1270.0</td>\n",
       "      <td>1428.0</td>\n",
       "      <td>1307.0</td>\n",
       "      <td>...</td>\n",
       "      <td>48.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>1440.0</td>\n",
       "      <td>1417.0</td>\n",
       "      <td>1982.0</td>\n",
       "      <td>959.0</td>\n",
       "      <td>2048.0</td>\n",
       "      <td>1380.0</td>\n",
       "      <td>823.0</td>\n",
       "      <td>1523.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 1860 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0             new_time  715898  715918  715920  715929  715930  \\\n",
       "0           0  2018-01-01 00:00:00   887.0  1045.0  2931.0  3303.0  3067.0   \n",
       "1           1  2018-01-01 01:00:00   561.0   687.0  4558.0  5058.0  4559.0   \n",
       "2           2  2018-01-01 02:00:00   483.0   543.0  3167.0  3527.0  3470.0   \n",
       "3           3  2018-01-01 03:00:00   531.0   589.0  2126.0  2255.0  1808.0   \n",
       "4           4  2018-01-01 04:00:00  1088.0  1263.0  1452.0  1284.0  1336.0   \n",
       "\n",
       "   715933  715938  715944  ...  775961  775975  775990  776019  776020  \\\n",
       "0  2763.0  2994.0  2622.0  ...    91.0   216.0  3808.0  2405.0  3231.0   \n",
       "1  3944.0  4417.0  3952.0  ...   156.0   260.0  4294.0  3139.0  4047.0   \n",
       "2  3158.0  3452.0  3119.0  ...    68.0   177.0  3348.0  2311.0  3057.0   \n",
       "3  1642.0  1834.0  1697.0  ...    52.0   157.0  2457.0  1726.0  2400.0   \n",
       "4  1270.0  1428.0  1307.0  ...    48.0   148.0  1440.0  1417.0  1982.0   \n",
       "\n",
       "   776052  776060  776066  776073  776081  \n",
       "0  2461.0  3730.0  3752.0  2637.0  4122.0  \n",
       "1  3310.0  4808.0  5574.0  3641.0  6020.0  \n",
       "2  1998.0  3387.0  3340.0  2079.0  3593.0  \n",
       "3  1530.0  2884.0  2773.0  1595.0  3024.0  \n",
       "4   959.0  2048.0  1380.0   823.0  1523.0  \n",
       "\n",
       "[5 rows x 1860 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "traffic_volume = pd.read_csv('../../data/Pems_LA_volume/traffic_volume_district7.csv')\n",
    "traffic_volume.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensor_res = []\n",
    "volume_columns = []\n",
    "i = 2\n",
    "while i < len(traffic_volume.columns):\n",
    "    has_loc_or_not = sensor_select[sensor_select['ID'] == traffic_volume.columns[i]].values[0]\n",
    "    if has_loc_or_not[1] != '' and has_loc_or_not[2] != '': # lat and long exist\n",
    "        sensor_res.append(has_loc_or_not)\n",
    "        volume_columns.append(traffic_volume.columns[i])\n",
    "    i = i + 1\n",
    "    \n",
    "# 1856 sensors left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensor_res = pd.DataFrame(sensor_res)\n",
    "sensor_res.columns = ['ID', 'Latitude', 'Longitude']\n",
    "sensor_res.to_csv('sensor_location.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "volume_res = traffic_volume[volume_columns]\n",
    "volume_res.to_csv('sensor_volume.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# only pick the first 150 sensors as a small network, because of the computational limitation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensor_volume = pd.read_csv(\"../../data/Pems_LA_volume/sensor_volume.csv\")\n",
    "sensor_volume.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensor_volume.iloc[:, 0:150].to_csv(\"../../data/Pems_LA_volume/sensor_volume_150.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>ID</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>715898</td>\n",
       "      <td>33.880183</td>\n",
       "      <td>-118.021787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>715918</td>\n",
       "      <td>33.933110</td>\n",
       "      <td>-118.091005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>715920</td>\n",
       "      <td>33.938544</td>\n",
       "      <td>-118.094941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>715929</td>\n",
       "      <td>33.971707</td>\n",
       "      <td>-118.123095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>715930</td>\n",
       "      <td>33.971763</td>\n",
       "      <td>-118.122905</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID   Latitude   Longitude\n",
       "0  715898  33.880183 -118.021787\n",
       "1  715918  33.933110 -118.091005\n",
       "2  715920  33.938544 -118.094941\n",
       "3  715929  33.971707 -118.123095\n",
       "4  715930  33.971763 -118.122905"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sensor_location = pd.read_csv(\"../../data/Pems_LA_volume/sensor_location.csv\")\n",
    "sensor_location.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sensor_location.iloc[0:150, :].to_csv(\"../../data/Pems_LA_volume/sensor_location_150.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# calculate spatial distances as the adjacency matrix for DCRNN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = [] # for benchmakr model DCRNN\n",
    "\n",
    "for i in range(150):\n",
    "    row = []\n",
    "    for j in range(150):\n",
    "        dist = distance(sensor_loc.iloc[i, 1:], sensor_loc.iloc[j, 1:]).m\n",
    "        row.append(dist)\n",
    "        res.append([sensor_loc.iloc[i, 0], sensor_loc.iloc[j, 0], dist])\n",
    "    \n",
    "#print (res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = pd.DataFrame(res)\n",
    "res.columns = ['from', 'to', 'cost']\n",
    "res.to_csv('distance_DCRNN.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (tf_gpu)",
   "language": "python",
   "name": "tf_gpu"
  },
  "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
}