01. Download TVV Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b0a62a9c-8eb5-40e1-b1e2-60bd7ab3bc39",
   "metadata": {},
   "source": [
    "**NSW Traffic Volume Viewer Analytics**\n",
    "\n",
    "***Download Traffic Volume Viewer Data***\n",
    "\n",
    "This notebook has been developed to download traffic volume data from the TfNSW Traffic Volume Viewer website - see: https://roads-waterways.transport.nsw.gov.au/about/corporate-publications/statistics/traffic-volumes/aadt-map/index.html#/?z=6\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "afa2b975-102e-4c60-9709-b12f984119bf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2021-08-15 15:05:26.366534\n"
     ]
    }
   ],
   "source": [
    "# Import dependency packages\n",
    "import os\n",
    "import pandas as pd\n",
    "import glob\n",
    "import zipfile\n",
    "import datetime\n",
    "import urllib.request\n",
    "from pathlib import Path\n",
    "\n",
    "time_start = datetime.datetime.now()\n",
    "print(time_start)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f8e1e184-b3ab-4fed-be21-53e16eb2d3f3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C:\\Users\\peter\\Documents\\GitHub\\NSW_Traffic_Volume_Viewer_Analytics_dev\n"
     ]
    }
   ],
   "source": [
    "# Specify the directory on your machine where the CSV files are to be saved.\n",
    "base_dir = os.getcwd() \n",
    "data_dir = base_dir\n",
    "inputs_dir = os.path.join(data_dir, 'Inputs')\n",
    "traffic_data_dir = os.path.join(inputs_dir,'Traffic_Data')\n",
    "outputs_dir = os.path.join(data_dir, 'Outputs') # Note: not used in this notebook\n",
    "print(base_dir)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "89daa285-e216-4893-b8a9-1f13f0c49d41",
   "metadata": {},
   "source": [
    "***Download Data***\n",
    "\n",
    "The following cells download data from the NSW Traffic Volume Viewer website in CSV format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b28900bc",
   "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>the_geom</th>\n",
       "      <th>station_id</th>\n",
       "      <th>full_name</th>\n",
       "      <th>name</th>\n",
       "      <th>road_name</th>\n",
       "      <th>common_road_name</th>\n",
       "      <th>secondary_name</th>\n",
       "      <th>suburb</th>\n",
       "      <th>post_code</th>\n",
       "      <th>lga</th>\n",
       "      <th>...</th>\n",
       "      <th>link_number</th>\n",
       "      <th>mab_identifier</th>\n",
       "      <th>device_type</th>\n",
       "      <th>permanent_station</th>\n",
       "      <th>vehicle_classifier</th>\n",
       "      <th>road_on_type</th>\n",
       "      <th>road_classification_type</th>\n",
       "      <th>road_classification_admin</th>\n",
       "      <th>wgs84_latitude</th>\n",
       "      <th>wgs84_longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0101000020E61000004B1FBAA0BEE66240FD32182312ED...</td>\n",
       "      <td>01001</td>\n",
       "      <td>Sydney Harbour Bridge, North of Hickson Road</td>\n",
       "      <td>Sydney Harbour Bridge</td>\n",
       "      <td>Bradfield Highway</td>\n",
       "      <td>Bradfield Highway</td>\n",
       "      <td>North of Hickson Road</td>\n",
       "      <td>Milsons Point</td>\n",
       "      <td>2061.0</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>...</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trafficorder Dual Tube Classifier</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>OnBridge</td>\n",
       "      <td>Highway</td>\n",
       "      <td>State</td>\n",
       "      <td>-33.852116</td>\n",
       "      <td>151.210770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0101000020E6100000BB253960D7E66240179CC1DF2FEE...</td>\n",
       "      <td>01003</td>\n",
       "      <td>Sydney Harbour Tunnel, North of Cahill Expressway</td>\n",
       "      <td>Sydney Harbour Tunnel</td>\n",
       "      <td>Sydney Harbour Tunnel</td>\n",
       "      <td>Sydney Harbour Tunnel.sydney Harbour Tunnel, S...</td>\n",
       "      <td>North of Cahill Expressway</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>M1</td>\n",
       "      <td>Trafficorder Loop Counter</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>InTunnel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>State</td>\n",
       "      <td>-33.860836</td>\n",
       "      <td>151.213791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0101000020E61000007730629F80E6624082397AFCDEEE...</td>\n",
       "      <td>01004</td>\n",
       "      <td>Western Distributor, West of Sussex Street</td>\n",
       "      <td>Western Distributor</td>\n",
       "      <td>Western Distributor</td>\n",
       "      <td>Western.western Distributor, Sydney</td>\n",
       "      <td>West of Sussex Street</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>...</td>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trafficorder Loop Counter</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>OnBridge</td>\n",
       "      <td>Distributor</td>\n",
       "      <td>State</td>\n",
       "      <td>-33.866180</td>\n",
       "      <td>151.203201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0101000020E6100000EA5DBC1FB7E66240982F2FC03EEE...</td>\n",
       "      <td>01011</td>\n",
       "      <td>Cahill Expressway, East of Pitt Street</td>\n",
       "      <td>Cahill Expressway</td>\n",
       "      <td>Cahill Expressway</td>\n",
       "      <td>Cahill.cahill Expressway, Sydney</td>\n",
       "      <td>East of Pitt Street</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>...</td>\n",
       "      <td>180</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trafficorder Loop Counter</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>OnBridge</td>\n",
       "      <td>Expressway</td>\n",
       "      <td>State</td>\n",
       "      <td>-33.861290</td>\n",
       "      <td>151.209854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0101000020E61000000F7EE2007AE66240AE62F19BC2F0...</td>\n",
       "      <td>01323</td>\n",
       "      <td>Ultimo Road, East of Darling Drive</td>\n",
       "      <td>Ultimo Road</td>\n",
       "      <td>Ultimo Road</td>\n",
       "      <td>Ultimo Road</td>\n",
       "      <td>East of Darling Drive</td>\n",
       "      <td>Haymarket</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>Sydney</td>\n",
       "      <td>...</td>\n",
       "      <td>-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Trafficorder Tube Axlepair Counter</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>OnGround</td>\n",
       "      <td>Road</td>\n",
       "      <td>Local</td>\n",
       "      <td>-33.880939</td>\n",
       "      <td>151.202393</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            the_geom station_id  \\\n",
       "0  0101000020E61000004B1FBAA0BEE66240FD32182312ED...      01001   \n",
       "1  0101000020E6100000BB253960D7E66240179CC1DF2FEE...      01003   \n",
       "2  0101000020E61000007730629F80E6624082397AFCDEEE...      01004   \n",
       "3  0101000020E6100000EA5DBC1FB7E66240982F2FC03EEE...      01011   \n",
       "4  0101000020E61000000F7EE2007AE66240AE62F19BC2F0...      01323   \n",
       "\n",
       "                                           full_name                   name  \\\n",
       "0       Sydney Harbour Bridge, North of Hickson Road  Sydney Harbour Bridge   \n",
       "1  Sydney Harbour Tunnel, North of Cahill Expressway  Sydney Harbour Tunnel   \n",
       "2         Western Distributor, West of Sussex Street    Western Distributor   \n",
       "3             Cahill Expressway, East of Pitt Street      Cahill Expressway   \n",
       "4                 Ultimo Road, East of Darling Drive            Ultimo Road   \n",
       "\n",
       "               road_name                                   common_road_name  \\\n",
       "0      Bradfield Highway                                  Bradfield Highway   \n",
       "1  Sydney Harbour Tunnel  Sydney Harbour Tunnel.sydney Harbour Tunnel, S...   \n",
       "2    Western Distributor                Western.western Distributor, Sydney   \n",
       "3      Cahill Expressway                   Cahill.cahill Expressway, Sydney   \n",
       "4            Ultimo Road                                        Ultimo Road   \n",
       "\n",
       "               secondary_name         suburb  post_code     lga  ...  \\\n",
       "0       North of Hickson Road  Milsons Point     2061.0  Sydney  ...   \n",
       "1  North of Cahill Expressway         Sydney     2000.0  Sydney  ...   \n",
       "2       West of Sussex Street         Sydney     2000.0  Sydney  ...   \n",
       "3         East of Pitt Street         Sydney     2000.0  Sydney  ...   \n",
       "4       East of Darling Drive      Haymarket     2000.0  Sydney  ...   \n",
       "\n",
       "  link_number  mab_identifier                         device_type  \\\n",
       "0          20             NaN   Trafficorder Dual Tube Classifier   \n",
       "1          10              M1           Trafficorder Loop Counter   \n",
       "2          18             NaN           Trafficorder Loop Counter   \n",
       "3         180             NaN           Trafficorder Loop Counter   \n",
       "4         -10             NaN  Trafficorder Tube Axlepair Counter   \n",
       "\n",
       "   permanent_station  vehicle_classifier road_on_type  \\\n",
       "0                  0                   1     OnBridge   \n",
       "1                  1                   0     InTunnel   \n",
       "2                  1                   0     OnBridge   \n",
       "3                  1                   0     OnBridge   \n",
       "4                  0                   0     OnGround   \n",
       "\n",
       "  road_classification_type  road_classification_admin  wgs84_latitude  \\\n",
       "0                  Highway                      State      -33.852116   \n",
       "1                      NaN                      State      -33.860836   \n",
       "2              Distributor                      State      -33.866180   \n",
       "3               Expressway                      State      -33.861290   \n",
       "4                     Road                      Local      -33.880939   \n",
       "\n",
       "  wgs84_longitude  \n",
       "0      151.210770  \n",
       "1      151.213791  \n",
       "2      151.203201  \n",
       "3      151.209854  \n",
       "4      151.202393  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Download Station Information\n",
    "web_url = \"https://rms-uat.cartodb.com/api/v2/sql?format=CSV&filename=Traffic_Volume_Viewer_Station_Information&q=SELECT%20station_id,%20full_name,%20name,%20road_name,%20common_road_name,%20secondary_name,%20suburb,%20post_code,%20lga,%20rms_region,%20distance_to_intersection,%20intersection,%20road_number,%20link_number,%20mab_identifier,%20device_type,%20permanent_station,%20vehicle_classifier,%20road_on_type,%20road_classification_type,%20road_classification_admin,%20wgs84_latitude,%20wgs84_longitude,%20ST_SetSRID(ST_MakePoint(CAST(wgs84_longitude%20AS%20FLOAT),%20CAST(wgs84_latitude%20AS%20FLOAT)),4326)%20AS%20the_geom%20FROM%20ds_aadt_reference%20WHERE%20publish=%271%27%20ORDER%20BY%20station_id\"\n",
    "download_fp = os.path.join(traffic_data_dir, 'station_information.csv')\n",
    "try: \n",
    "    urllib.request.urlretrieve(web_url, download_fp)\n",
    "except:\n",
    "    print(\"No file available.\")\n",
    "\n",
    "# Read and view data\n",
    "station_information_df = pd.read_csv(download_fp)\n",
    "station_information_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8e3bfe9f",
   "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>the_geom</th>\n",
       "      <th>cartodb_id</th>\n",
       "      <th>the_geom_webmercator</th>\n",
       "      <th>record_id</th>\n",
       "      <th>station_key</th>\n",
       "      <th>station_id</th>\n",
       "      <th>name</th>\n",
       "      <th>road_name</th>\n",
       "      <th>full_name</th>\n",
       "      <th>common_road_name</th>\n",
       "      <th>...</th>\n",
       "      <th>vehicle_classifier</th>\n",
       "      <th>lambert_easting</th>\n",
       "      <th>lambert_northing</th>\n",
       "      <th>wgs84_latitude</th>\n",
       "      <th>wgs84_longitude</th>\n",
       "      <th>direction_seq</th>\n",
       "      <th>quality_rating</th>\n",
       "      <th>publish</th>\n",
       "      <th>md5</th>\n",
       "      <th>updated_on</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1791</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15828002</td>\n",
       "      <td>7211</td>\n",
       "      <td>Lily Lane</td>\n",
       "      <td>Lily Lane</td>\n",
       "      <td>Lily Lane, South of Fern Circuit</td>\n",
       "      <td>Lily Lane</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>9740191</td>\n",
       "      <td>4524353.5</td>\n",
       "      <td>-32.940571</td>\n",
       "      <td>151.713120</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>be6e76534a9cbe57941a6971d910a6eb</td>\n",
       "      <td>2018-06-12 02:48:41.978017+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1792</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58612</td>\n",
       "      <td>97340</td>\n",
       "      <td>Oxley Avenue</td>\n",
       "      <td>Oxley Avenue</td>\n",
       "      <td>Oxley Avenue, North of High Street</td>\n",
       "      <td>Oxley Avenue</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>9163953</td>\n",
       "      <td>4473475.5</td>\n",
       "      <td>-33.480766</td>\n",
       "      <td>145.534805</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>417b961c9a41b3a2f7ce41fa423d8977</td>\n",
       "      <td>2018-06-12 02:48:41.978017+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1793</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56692</td>\n",
       "      <td>19035</td>\n",
       "      <td>Sydenham Road</td>\n",
       "      <td>Sydenham Road</td>\n",
       "      <td>Sydenham Road, West of Victoria Road</td>\n",
       "      <td>Sydenham Road</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>9684475</td>\n",
       "      <td>4419245.0</td>\n",
       "      <td>-33.909630</td>\n",
       "      <td>151.162048</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>710efc78db32af23f3a6ed4870bc7aeb</td>\n",
       "      <td>2018-06-12 02:48:41.978017+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1794</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58856</td>\n",
       "      <td>99931</td>\n",
       "      <td>Castlereagh Highway</td>\n",
       "      <td>Castlereagh Highway</td>\n",
       "      <td>Castlereagh Highway, South of Abattoirs Road</td>\n",
       "      <td>Castlereagh Highway</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>9539223</td>\n",
       "      <td>4573615.5</td>\n",
       "      <td>-32.559254</td>\n",
       "      <td>149.549698</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>4dbb7f7bd091635de8b3a10399455245</td>\n",
       "      <td>2018-06-12 02:48:41.978017+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1795</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56098</td>\n",
       "      <td>05622</td>\n",
       "      <td>Hanbury Street</td>\n",
       "      <td>Hanbury Street</td>\n",
       "      <td>Hanbury Street, East of Waratah Street</td>\n",
       "      <td>Hanbury Street</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>9742526</td>\n",
       "      <td>4528977.0</td>\n",
       "      <td>-32.897934</td>\n",
       "      <td>151.735840</td>\n",
       "      <td>9</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>b5b01373fb1bc06495989748e45e918b</td>\n",
       "      <td>2018-06-12 02:48:41.978017+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 42 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   the_geom  cartodb_id  the_geom_webmercator  record_id  station_key  \\\n",
       "0       NaN        1791                   NaN        NaN     15828002   \n",
       "1       NaN        1792                   NaN        NaN        58612   \n",
       "2       NaN        1793                   NaN        NaN        56692   \n",
       "3       NaN        1794                   NaN        NaN        58856   \n",
       "4       NaN        1795                   NaN        NaN        56098   \n",
       "\n",
       "  station_id                 name            road_name  \\\n",
       "0       7211            Lily Lane            Lily Lane   \n",
       "1      97340         Oxley Avenue         Oxley Avenue   \n",
       "2      19035        Sydenham Road        Sydenham Road   \n",
       "3      99931  Castlereagh Highway  Castlereagh Highway   \n",
       "4      05622       Hanbury Street       Hanbury Street   \n",
       "\n",
       "                                      full_name     common_road_name  ...  \\\n",
       "0              Lily Lane, South of Fern Circuit            Lily Lane  ...   \n",
       "1            Oxley Avenue, North of High Street         Oxley Avenue  ...   \n",
       "2          Sydenham Road, West of Victoria Road        Sydenham Road  ...   \n",
       "3  Castlereagh Highway, South of Abattoirs Road  Castlereagh Highway  ...   \n",
       "4        Hanbury Street, East of Waratah Street       Hanbury Street  ...   \n",
       "\n",
       "  vehicle_classifier lambert_easting lambert_northing wgs84_latitude  \\\n",
       "0                  1         9740191        4524353.5     -32.940571   \n",
       "1                  0         9163953        4473475.5     -33.480766   \n",
       "2                  0         9684475        4419245.0     -33.909630   \n",
       "3                  1         9539223        4573615.5     -32.559254   \n",
       "4                  0         9742526        4528977.0     -32.897934   \n",
       "\n",
       "   wgs84_longitude  direction_seq  quality_rating publish  \\\n",
       "0       151.713120              7               5       1   \n",
       "1       145.534805              9               5       1   \n",
       "2       151.162048             10               5       1   \n",
       "3       149.549698              9               5       1   \n",
       "4       151.735840              9               5       1   \n",
       "\n",
       "                                md5                     updated_on  \n",
       "0  be6e76534a9cbe57941a6971d910a6eb  2018-06-12 02:48:41.978017+00  \n",
       "1  417b961c9a41b3a2f7ce41fa423d8977  2018-06-12 02:48:41.978017+00  \n",
       "2  710efc78db32af23f3a6ed4870bc7aeb  2018-06-12 02:48:41.978017+00  \n",
       "3  4dbb7f7bd091635de8b3a10399455245  2018-06-12 02:48:41.978017+00  \n",
       "4  b5b01373fb1bc06495989748e45e918b  2018-06-12 02:48:41.978017+00  \n",
       "\n",
       "[5 rows x 42 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ds_aadt_reference\n",
    "web_url = \"https://rms-uat.cartodb.com/api/v2/sql?format=CSV&filename=data%20&q=SELECT%20*%20FROM%20%20ds_aadt_reference\"\n",
    "download_fp = os.path.join(traffic_data_dir, 'ds_aadt_reference.csv')\n",
    "try: \n",
    "    urllib.request.urlretrieve(web_url, download_fp)\n",
    "except:\n",
    "    print(\"No file available.\")\n",
    "\n",
    "# Read and view data\n",
    "ds_aadt_reference_df = pd.read_csv(download_fp)\n",
    "ds_aadt_reference_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ab11a748",
   "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>the_geom</th>\n",
       "      <th>cartodb_id</th>\n",
       "      <th>the_geom_webmercator</th>\n",
       "      <th>record_id</th>\n",
       "      <th>station_key</th>\n",
       "      <th>station_id</th>\n",
       "      <th>traffic_direction_seq</th>\n",
       "      <th>traffic_direction_name</th>\n",
       "      <th>cardinal_direction_seq</th>\n",
       "      <th>cardinal_direction_name</th>\n",
       "      <th>...</th>\n",
       "      <th>traffic_count</th>\n",
       "      <th>data_start_date</th>\n",
       "      <th>data_end_date</th>\n",
       "      <th>data_duration</th>\n",
       "      <th>data_availability</th>\n",
       "      <th>data_reliability</th>\n",
       "      <th>data_quality_indicator</th>\n",
       "      <th>publish</th>\n",
       "      <th>updated_on</th>\n",
       "      <th>md5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>584072</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57103</td>\n",
       "      <td>53001</td>\n",
       "      <td>0</td>\n",
       "      <td>COUNTER</td>\n",
       "      <td>5</td>\n",
       "      <td>SOUTH</td>\n",
       "      <td>...</td>\n",
       "      <td>16801</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08-04 11:41:27.927875+00</td>\n",
       "      <td>ea52830029714d070f0b077937db2657</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>584073</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56950</td>\n",
       "      <td>42001</td>\n",
       "      <td>2</td>\n",
       "      <td>PRESCRIBED AND COUNTER</td>\n",
       "      <td>9</td>\n",
       "      <td>BOTH</td>\n",
       "      <td>...</td>\n",
       "      <td>139</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08-04 11:41:27.927875+00</td>\n",
       "      <td>19c71171ebe793c544eed4008a7fd2a8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>264076</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>55318</td>\n",
       "      <td>02015</td>\n",
       "      <td>2</td>\n",
       "      <td>PRESCRIBED AND COUNTER</td>\n",
       "      <td>9</td>\n",
       "      <td>BOTH</td>\n",
       "      <td>...</td>\n",
       "      <td>39273</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2018-12-14 05:33:24.64657+00</td>\n",
       "      <td>2c3a9b9bf222b4b98d5da5b20455e378</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>584074</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15334030</td>\n",
       "      <td>T0342</td>\n",
       "      <td>0</td>\n",
       "      <td>COUNTER</td>\n",
       "      <td>3</td>\n",
       "      <td>EAST</td>\n",
       "      <td>...</td>\n",
       "      <td>761</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>48</td>\n",
       "      <td>91</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08-04 11:41:27.927875+00</td>\n",
       "      <td>1fef957b2dc3ec4592da6be4639f82ec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>584075</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21606001</td>\n",
       "      <td>6118</td>\n",
       "      <td>2</td>\n",
       "      <td>PRESCRIBED AND COUNTER</td>\n",
       "      <td>9</td>\n",
       "      <td>BOTH</td>\n",
       "      <td>...</td>\n",
       "      <td>278</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-08-04 11:41:27.927875+00</td>\n",
       "      <td>be37fb416b55b02c7504684893fa1fc8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   the_geom  cartodb_id  the_geom_webmercator  record_id  station_key  \\\n",
       "0       NaN      584072                   NaN        NaN        57103   \n",
       "1       NaN      584073                   NaN        NaN        56950   \n",
       "2       NaN      264076                   NaN        NaN        55318   \n",
       "3       NaN      584074                   NaN        NaN     15334030   \n",
       "4       NaN      584075                   NaN        NaN     21606001   \n",
       "\n",
       "  station_id  traffic_direction_seq  traffic_direction_name  \\\n",
       "0      53001                      0                 COUNTER   \n",
       "1      42001                      2  PRESCRIBED AND COUNTER   \n",
       "2      02015                      2  PRESCRIBED AND COUNTER   \n",
       "3      T0342                      0                 COUNTER   \n",
       "4       6118                      2  PRESCRIBED AND COUNTER   \n",
       "\n",
       "   cardinal_direction_seq cardinal_direction_name  ...  traffic_count  \\\n",
       "0                       5                   SOUTH  ...          16801   \n",
       "1                       9                    BOTH  ...            139   \n",
       "2                       9                    BOTH  ...          39273   \n",
       "3                       3                    EAST  ...            761   \n",
       "4                       9                    BOTH  ...            278   \n",
       "\n",
       "  data_start_date data_end_date  data_duration data_availability  \\\n",
       "0             NaN           NaN            NaN                -1   \n",
       "1             NaN           NaN            NaN                -1   \n",
       "2             NaN           NaN            NaN                -1   \n",
       "3             NaN           NaN            NaN                48   \n",
       "4             NaN           NaN            NaN                -1   \n",
       "\n",
       "   data_reliability  data_quality_indicator  publish  \\\n",
       "0                -1                       0        1   \n",
       "1                -1                       0        1   \n",
       "2                -1                       0        1   \n",
       "3                91                       0        1   \n",
       "4                -1                       0        1   \n",
       "\n",
       "                      updated_on                               md5  \n",
       "0  2021-08-04 11:41:27.927875+00  ea52830029714d070f0b077937db2657  \n",
       "1  2021-08-04 11:41:27.927875+00  19c71171ebe793c544eed4008a7fd2a8  \n",
       "2   2018-12-14 05:33:24.64657+00  2c3a9b9bf222b4b98d5da5b20455e378  \n",
       "3  2021-08-04 11:41:27.927875+00  1fef957b2dc3ec4592da6be4639f82ec  \n",
       "4  2021-08-04 11:41:27.927875+00  be37fb416b55b02c7504684893fa1fc8  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ds_aadt_summary_data\n",
    "web_url = \"https://rms-uat.cartodb.com/api/v2/sql?format=CSV&filename=data%20&q=SELECT%20*%20FROM%20%20ds_aadt_summary_data\"\n",
    "download_fp = os.path.join(traffic_data_dir, 'ds_aadt_summary_data.csv')\n",
    "try: \n",
    "    urllib.request.urlretrieve(web_url, download_fp)\n",
    "except:\n",
    "    print(\"No file available.\")\n",
    "\n",
    "# Read and view data\n",
    "ds_aadt_summary_data_df = pd.read_csv(download_fp)\n",
    "ds_aadt_summary_data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "20120c8f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3165: DtypeWarning: Columns (39) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    },
    {
     "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>the_geom</th>\n",
       "      <th>cartodb_id</th>\n",
       "      <th>the_geom_webmercator</th>\n",
       "      <th>record_id</th>\n",
       "      <th>station_key</th>\n",
       "      <th>traffic_direction_seq</th>\n",
       "      <th>cardinal_direction_seq</th>\n",
       "      <th>classification_seq</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>...</th>\n",
       "      <th>hour_16</th>\n",
       "      <th>hour_17</th>\n",
       "      <th>hour_18</th>\n",
       "      <th>hour_19</th>\n",
       "      <th>hour_20</th>\n",
       "      <th>hour_21</th>\n",
       "      <th>hour_22</th>\n",
       "      <th>hour_23</th>\n",
       "      <th>updated_on</th>\n",
       "      <th>md5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>293075</td>\n",
       "      <td>NaN</td>\n",
       "      <td>293075.0</td>\n",
       "      <td>56422</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-07-07</td>\n",
       "      <td>2010</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>NaN</td>\n",
       "      <td>369287d360274f609eb1e9ec729df6e0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>1312878</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1312878.0</td>\n",
       "      <td>57105</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2009-04-04</td>\n",
       "      <td>2009</td>\n",
       "      <td>...</td>\n",
       "      <td>17.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56fb29cee4980eb3dfdd948514dba889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>102066</td>\n",
       "      <td>NaN</td>\n",
       "      <td>102066.0</td>\n",
       "      <td>55786</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2011-10-25</td>\n",
       "      <td>2011</td>\n",
       "      <td>...</td>\n",
       "      <td>38.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2c097340acd5dc43e3fc67a8aead7d4b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>100176</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100176.0</td>\n",
       "      <td>55747</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2009-06-17</td>\n",
       "      <td>2009</td>\n",
       "      <td>...</td>\n",
       "      <td>967.0</td>\n",
       "      <td>1114.0</td>\n",
       "      <td>1149.0</td>\n",
       "      <td>784.0</td>\n",
       "      <td>303.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>db283035674d8d0987861edea9adb45f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>102059</td>\n",
       "      <td>NaN</td>\n",
       "      <td>102059.0</td>\n",
       "      <td>55786</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2011-09-07</td>\n",
       "      <td>2011</td>\n",
       "      <td>...</td>\n",
       "      <td>30.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>724f407abe3de96210044ea01d2f9026</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   the_geom  cartodb_id  the_geom_webmercator  record_id  station_key  \\\n",
       "0       NaN      293075                   NaN   293075.0        56422   \n",
       "1       NaN     1312878                   NaN  1312878.0        57105   \n",
       "2       NaN      102066                   NaN   102066.0        55786   \n",
       "3       NaN      100176                   NaN   100176.0        55747   \n",
       "4       NaN      102059                   NaN   102059.0        55786   \n",
       "\n",
       "   traffic_direction_seq  cardinal_direction_seq  classification_seq  \\\n",
       "0                      1                       7                   0   \n",
       "1                      1                       1                   3   \n",
       "2                      1                       3                   0   \n",
       "3                      0                       5                   0   \n",
       "4                      1                       3                   0   \n",
       "\n",
       "         date  year  ...  hour_16  hour_17  hour_18  hour_19  hour_20  \\\n",
       "0  2010-07-07  2010  ...      0.0      0.0      0.0      0.0      0.0   \n",
       "1  2009-04-04  2009  ...     17.0     20.0     15.0     10.0      7.0   \n",
       "2  2011-10-25  2011  ...     38.0      6.0      3.0      3.0      1.0   \n",
       "3  2009-06-17  2009  ...    967.0   1114.0   1149.0    784.0    303.0   \n",
       "4  2011-09-07  2011  ...     30.0     13.0      0.0      1.0      2.0   \n",
       "\n",
       "   hour_21  hour_22  hour_23  updated_on                               md5  \n",
       "0      0.0      0.0      0.0         NaN  369287d360274f609eb1e9ec729df6e0  \n",
       "1      6.0      7.0      3.0         NaN  56fb29cee4980eb3dfdd948514dba889  \n",
       "2      2.0      2.0      2.0         NaN  2c097340acd5dc43e3fc67a8aead7d4b  \n",
       "3    190.0    109.0     58.0         NaN  db283035674d8d0987861edea9adb45f  \n",
       "4      0.0      1.0      0.0         NaN  724f407abe3de96210044ea01d2f9026  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ds_aadt_permanent_hourly_data\n",
    "# Note - results in ~600mb of data (may take a while to download)\n",
    "web_url = \"https://rms-uat.cartodb.com/api/v2/sql?format=CSV&filename=data%20&q=SELECT%20*%20FROM%20%20ds_aadt_permanent_hourly_data\"\n",
    "download_fp = os.path.join(traffic_data_dir, 'ds_aadt_permanent_hourly_data.csv')\n",
    "try: \n",
    "    urllib.request.urlretrieve(web_url, download_fp)\n",
    "except:\n",
    "    print(\"No file available.\")\n",
    "\n",
    "# Read and view data\n",
    "ds_aadt_permanent_hourly_data_df = pd.read_csv(download_fp)\n",
    "ds_aadt_permanent_hourly_data_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a6242778",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3165: DtypeWarning: Columns (40) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,\n"
     ]
    },
    {
     "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>the_geom</th>\n",
       "      <th>cartodb_id</th>\n",
       "      <th>the_geom_webmercator</th>\n",
       "      <th>record_id</th>\n",
       "      <th>station_key</th>\n",
       "      <th>traffic_direction_seq</th>\n",
       "      <th>cardinal_direction_seq</th>\n",
       "      <th>classification_seq</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>...</th>\n",
       "      <th>hour_16</th>\n",
       "      <th>hour_17</th>\n",
       "      <th>hour_18</th>\n",
       "      <th>hour_19</th>\n",
       "      <th>hour_20</th>\n",
       "      <th>hour_21</th>\n",
       "      <th>hour_22</th>\n",
       "      <th>hour_23</th>\n",
       "      <th>md5</th>\n",
       "      <th>updated_on</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>226714</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58308</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-05-24</td>\n",
       "      <td>2010</td>\n",
       "      <td>...</td>\n",
       "      <td>23.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>a0c8608b8d15dfd8b28c96bdff96ea6f</td>\n",
       "      <td>2018-06-12 05:16:50.885412+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>226715</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58260</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2006-05-10</td>\n",
       "      <td>2006</td>\n",
       "      <td>...</td>\n",
       "      <td>17.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>264a654a854aaf481c21b748d5ac18d3</td>\n",
       "      <td>2018-06-12 05:16:50.885412+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>226716</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58333</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2006-05-11</td>\n",
       "      <td>2006</td>\n",
       "      <td>...</td>\n",
       "      <td>17.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9bdadee8611e83b6caba5ac6507ba0a0</td>\n",
       "      <td>2018-06-12 05:16:50.885412+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>226717</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58308</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>2010-05-31</td>\n",
       "      <td>2010</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>70b9c041abfc5e27a8016c6ff1ce75a3</td>\n",
       "      <td>2018-06-12 05:16:50.885412+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>226718</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>58308</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2006-05-14</td>\n",
       "      <td>2006</td>\n",
       "      <td>...</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>99fd24bfc22ded71840ab214723c8b4d</td>\n",
       "      <td>2018-06-12 05:16:50.885412+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   the_geom  cartodb_id  the_geom_webmercator  record_id  station_key  \\\n",
       "0       NaN      226714                   NaN        NaN        58308   \n",
       "1       NaN      226715                   NaN        NaN        58260   \n",
       "2       NaN      226716                   NaN        NaN        58333   \n",
       "3       NaN      226717                   NaN        NaN        58308   \n",
       "4       NaN      226718                   NaN        NaN        58308   \n",
       "\n",
       "   traffic_direction_seq  cardinal_direction_seq  classification_seq  \\\n",
       "0                      0                       3                   0   \n",
       "1                      1                       7                   0   \n",
       "2                      0                       3                   0   \n",
       "3                      1                       7                   0   \n",
       "4                      0                       3                   0   \n",
       "\n",
       "         date  year  ...  hour_16  hour_17  hour_18  hour_19  hour_20  \\\n",
       "0  2010-05-24  2010  ...     23.0     20.0     17.0      3.0      5.0   \n",
       "1  2006-05-10  2006  ...     17.0     16.0      8.0      3.0      5.0   \n",
       "2  2006-05-11  2006  ...     17.0     18.0      8.0      3.0      3.0   \n",
       "3  2010-05-31  2010  ...      7.0     11.0      8.0      3.0      2.0   \n",
       "4  2006-05-14  2006  ...     11.0     10.0     12.0      8.0      2.0   \n",
       "\n",
       "   hour_21  hour_22  hour_23                               md5  \\\n",
       "0      5.0      1.0      0.0  a0c8608b8d15dfd8b28c96bdff96ea6f   \n",
       "1      6.0      4.0      2.0  264a654a854aaf481c21b748d5ac18d3   \n",
       "2      3.0      0.0      1.0  9bdadee8611e83b6caba5ac6507ba0a0   \n",
       "3      1.0      0.0      2.0  70b9c041abfc5e27a8016c6ff1ce75a3   \n",
       "4      2.0      0.0      0.0  99fd24bfc22ded71840ab214723c8b4d   \n",
       "\n",
       "                      updated_on  \n",
       "0  2018-06-12 05:16:50.885412+00  \n",
       "1  2018-06-12 05:16:50.885412+00  \n",
       "2  2018-06-12 05:16:50.885412+00  \n",
       "3  2018-06-12 05:16:50.885412+00  \n",
       "4  2018-06-12 05:16:50.885412+00  \n",
       "\n",
       "[5 rows x 41 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ds_aadt_sample_hourly_data\n",
    "web_url = \"https://rms-uat.cartodb.com/api/v2/sql?format=CSV&filename=data%20&q=SELECT%20*%20FROM%20%20ds_aadt_sample_hourly_data\"\n",
    "download_fp = os.path.join(traffic_data_dir, 'ds_aadt_sample_hourly_data.csv')\n",
    "try: \n",
    "    urllib.request.urlretrieve(web_url, download_fp)\n",
    "except:\n",
    "    print(\"No file available.\")\n",
    "\n",
    "# Read and view data\n",
    "ds_aadt_sample_hourly_data_df = pd.read_csv(download_fp)\n",
    "ds_aadt_sample_hourly_data_df.head()"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}