prepare_training_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import time\n",
    "import numpy as np\n",
    "import arcgis\n",
    "from astral import Astral\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1. Load/Condition Data from GDB"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "With the prepwork done in Pro or Arcpy, we can use the ArcGIS Python API to easily load this data into a SpatialDataFrame, which will allow us to do any geospatial manipulations we need to do. In this example, we'll mostly use the ``pandas`` functionality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "project_gdb = r'utah.gdb'\n",
    "collisions_path = os.path.join(project_gdb,'collisions_joined')\n",
    "road_features_path = os.path.join(project_gdb,'static_features')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.1 Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = arcgis.features.SpatialDataFrame().from_featureclass(collisions_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "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>OBJECTID</th>\n",
       "      <th>Join_Count</th>\n",
       "      <th>TARGET_FID</th>\n",
       "      <th>CRASHID</th>\n",
       "      <th>CRASHDATE</th>\n",
       "      <th>CRASHTIME</th>\n",
       "      <th>RouteName</th>\n",
       "      <th>BeginMilePost</th>\n",
       "      <th>RoadView</th>\n",
       "      <th>CrashSeverity</th>\n",
       "      <th>...</th>\n",
       "      <th>NUMBERVEHICLESINVOLVED</th>\n",
       "      <th>FIRSTHARMFULEVENTID</th>\n",
       "      <th>ROADWAYTYPECD</th>\n",
       "      <th>ROUTEDIRECTION</th>\n",
       "      <th>EXITNUMBER</th>\n",
       "      <th>RAMPID</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>segment_id</th>\n",
       "      <th>SHAPE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10401151</td>\n",
       "      <td>2010-10-15</td>\n",
       "      <td>19:56:00</td>\n",
       "      <td>0009P</td>\n",
       "      <td>11.64</td>\n",
       "      <td>http://168.178.125.111/virtualnavigator/udot/V...</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>Other Motor Vehicle in Transport</td>\n",
       "      <td>Mainline</td>\n",
       "      <td>P</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4119389.0</td>\n",
       "      <td>298229.0</td>\n",
       "      <td>28425</td>\n",
       "      <td>{'x': -12609547.622000001, 'y': 4466906.681199...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>10394697</td>\n",
       "      <td>2010-08-20</td>\n",
       "      <td>19:33:00</td>\n",
       "      <td>0009P</td>\n",
       "      <td>11.85</td>\n",
       "      <td>http://168.178.125.111/virtualnavigator/udot/V...</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>2</td>\n",
       "      <td>Other Motor Vehicle in Transport</td>\n",
       "      <td>Mainline</td>\n",
       "      <td>P</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4119704.0</td>\n",
       "      <td>298349.0</td>\n",
       "      <td>28431</td>\n",
       "      <td>{'x': -12609406.6849, 'y': 4467306.825099997, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>10346333</td>\n",
       "      <td>2010-03-27</td>\n",
       "      <td>21:30:00</td>\n",
       "      <td>0009P</td>\n",
       "      <td>14.00</td>\n",
       "      <td>http://168.178.125.111/virtualnavigator/udot/V...</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Delineator Post</td>\n",
       "      <td>Mainline</td>\n",
       "      <td>P</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4122320.0</td>\n",
       "      <td>299671.0</td>\n",
       "      <td>24677</td>\n",
       "      <td>{'x': -12607827.8035, 'y': 4470640.231600001, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>10352753</td>\n",
       "      <td>2010-04-16</td>\n",
       "      <td>13:45:00</td>\n",
       "      <td>0009P</td>\n",
       "      <td>14.00</td>\n",
       "      <td>http://168.178.125.111/virtualnavigator/udot/V...</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Overturn/Rollover</td>\n",
       "      <td>Mainline</td>\n",
       "      <td>P</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4122320.0</td>\n",
       "      <td>299671.0</td>\n",
       "      <td>24677</td>\n",
       "      <td>{'x': -12607827.8035, 'y': 4470640.231600001, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>10357506</td>\n",
       "      <td>2010-03-05</td>\n",
       "      <td>9:48:00</td>\n",
       "      <td>0009P</td>\n",
       "      <td>21.00</td>\n",
       "      <td>http://168.178.125.111/virtualnavigator/udot/V...</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Animal - Wild</td>\n",
       "      <td>Mainline</td>\n",
       "      <td>P</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4118637.0</td>\n",
       "      <td>309403.0</td>\n",
       "      <td>28601</td>\n",
       "      <td>{'x': -12595519.862399999, 'y': 4466288.306199...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   OBJECTID  Join_Count  TARGET_FID   CRASHID  CRASHDATE CRASHTIME RouteName  \\\n",
       "0         1           1           1  10401151 2010-10-15  19:56:00     0009P   \n",
       "1         2           1           2  10394697 2010-08-20  19:33:00     0009P   \n",
       "2         3           1           3  10346333 2010-03-27  21:30:00     0009P   \n",
       "3         4           1           4  10352753 2010-04-16  13:45:00     0009P   \n",
       "4         5           1           5  10357506 2010-03-05   9:48:00     0009P   \n",
       "\n",
       "   BeginMilePost                                           RoadView  \\\n",
       "0          11.64  http://168.178.125.111/virtualnavigator/udot/V...   \n",
       "1          11.85  http://168.178.125.111/virtualnavigator/udot/V...   \n",
       "2          14.00  http://168.178.125.111/virtualnavigator/udot/V...   \n",
       "3          14.00  http://168.178.125.111/virtualnavigator/udot/V...   \n",
       "4          21.00  http://168.178.125.111/virtualnavigator/udot/V...   \n",
       "\n",
       "   CrashSeverity                        ...                          \\\n",
       "0              2                        ...                           \n",
       "1              1                        ...                           \n",
       "2              1                        ...                           \n",
       "3              1                        ...                           \n",
       "4              1                        ...                           \n",
       "\n",
       "  NUMBERVEHICLESINVOLVED               FIRSTHARMFULEVENTID  ROADWAYTYPECD  \\\n",
       "0                      2  Other Motor Vehicle in Transport       Mainline   \n",
       "1                      2  Other Motor Vehicle in Transport       Mainline   \n",
       "2                      1                   Delineator Post       Mainline   \n",
       "3                      1                 Overturn/Rollover       Mainline   \n",
       "4                      1                     Animal - Wild       Mainline   \n",
       "\n",
       "  ROUTEDIRECTION EXITNUMBER RAMPID   Latitude Longitude  segment_id  \\\n",
       "0              P       None   None  4119389.0  298229.0       28425   \n",
       "1              P       None   None  4119704.0  298349.0       28431   \n",
       "2              P       None   None  4122320.0  299671.0       24677   \n",
       "3              P       None   None  4122320.0  299671.0       24677   \n",
       "4              P       None   None  4118637.0  309403.0       28601   \n",
       "\n",
       "                                               SHAPE  \n",
       "0  {'x': -12609547.622000001, 'y': 4466906.681199...  \n",
       "1  {'x': -12609406.6849, 'y': 4467306.825099997, ...  \n",
       "2  {'x': -12607827.8035, 'y': 4470640.231600001, ...  \n",
       "3  {'x': -12607827.8035, 'y': 4470640.231600001, ...  \n",
       "4  {'x': -12595519.862399999, 'y': 4466288.306199...  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rdf = arcgis.SpatialDataFrame().from_featureclass(road_features_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2 Remove some bad data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# We don't care about accidents that happened far from known major/minor roads\n",
    "df = df.dropna(how='any',subset=['segment_id'])\n",
    "\n",
    "# Convert some types\n",
    "df['segment_id'] = df.segment_id.astype('int64')\n",
    "rdf['segment_id'] = rdf.segment_id.astype('int64') \n",
    "rdf['station_id'] = rdf.station_id.astype('int64') # Weather statiion ID"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.3 Parse Dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df['CRASHDATE'] = df['CRASHDATE'].astype('str')\n",
    "df['CRASHTIME'] = df['CRASHTIME'].astype('str')\n",
    "\n",
    "df['timestamp'] = pd.to_datetime(df['CRASHDATE']+' '+df['CRASHTIME'])\n",
    "\n",
    "time_index = pd.DatetimeIndex(df.timestamp).floor('1h')\n",
    "df['hour'] = time_index.hour\n",
    "df['weekday'] = time_index.weekday\n",
    "df['month'] = time_index.month\n",
    "### Make sure the ouput directory is created \n",
    "!mkdir training_data\n",
    "df.to_csv('training_data/collisions_new.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.4 Remove/Replace missing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rdf.loc[pd.isna(rdf.speed_limit),'speed_limit'] = np.median(rdf.speed_limit)\n",
    "rdf.loc[pd.isna(rdf.aadt),'aadt'] = 0.0 # AKA, unknown, zero, etc. This will help differentiate major/minor roadways"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Expand Feature Set\n",
    "Once the data is in a DataFrame, it's very easy to manipulate to add certain features. Here we will add a accident count field by doing some joins and aggregations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "accident_counts_per_segment = df.groupby('segment_id').size().reset_index(name='accident_counts').set_index('segment_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rdf = rdf.set_index('segment_id').join(accident_counts_per_segment)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# If there was no accident, set value to 0, not NaN\n",
    "rdf.loc[pd.isna(rdf.accident_counts),'accident_counts'] = 0.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3. Select Features\n",
    "\n",
    "## 3.1 Feature Names\n",
    "We can do most of our exploratory data analysis in ArcGIS Pro to determine a set of candidate features. Once we build our training set, we can do further EDA to determine the best feature set. Here we select the candidate features that we found in Pro "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['OBJECTID_1', 'Join_Count', 'TARGET_FID', 'Join_Count_1', 'TARGET_FID_1', 'OBJECTID', 'pre_dir', 'street_type', 'suf_dir', 'one_way', 'speed_limit', 'surface_type', 'surface_width', 'aadt', 'sinuosity', 'euclidean_length', 'segment_length', 'at_intersection', 'near_billboard', 'road_orient_approx', 'near_major_road', 'station_id', 'SHAPE', 'accident_counts']\n"
     ]
    }
   ],
   "source": [
    "print(rdf.columns.tolist())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "static_feature_names = [\n",
    "    'pre_dir', \n",
    "    'street_type',\n",
    "    'suf_dir', \n",
    "    'one_way', \n",
    "    'speed_limit', \n",
    "    'surface_type', \n",
    "    'surface_width', \n",
    "    'aadt', \n",
    "    'sinuosity',          # \\\n",
    "    'euclidean_length',   # |  These together define road curvature\n",
    "    'segment_length',     # /\n",
    "    'near_major_road', \n",
    "    'road_orient_approx', \n",
    "    'at_intersection', \n",
    "    'near_billboard',\n",
    "    'accident_counts'\n",
    "]\n",
    "temporal_feature_names = [\n",
    "    'hour',\n",
    "    'weekday',\n",
    "    'month'\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.2 Select static features, join to accidents records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "features = df\\\n",
    "    .xs(['timestamp','segment_id']+temporal_feature_names,axis=1)\\\n",
    "    .set_index('segment_id')\\\n",
    "    .join(\n",
    "        rdf.xs(static_feature_names+['station_id'],axis=1),\n",
    "        how='left'\n",
    "    ).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "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>segment_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>hour</th>\n",
       "      <th>weekday</th>\n",
       "      <th>month</th>\n",
       "      <th>pre_dir</th>\n",
       "      <th>street_type</th>\n",
       "      <th>suf_dir</th>\n",
       "      <th>one_way</th>\n",
       "      <th>speed_limit</th>\n",
       "      <th>...</th>\n",
       "      <th>aadt</th>\n",
       "      <th>sinuosity</th>\n",
       "      <th>euclidean_length</th>\n",
       "      <th>segment_length</th>\n",
       "      <th>near_major_road</th>\n",
       "      <th>road_orient_approx</th>\n",
       "      <th>at_intersection</th>\n",
       "      <th>near_billboard</th>\n",
       "      <th>accident_counts</th>\n",
       "      <th>station_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2016-11-26 10:13:00</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>16689.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>72572024127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2017-10-16 16:03:00</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>16689.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>72572024127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2010-01-16 15:26:00</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>16689.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>72572024127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2013-12-16 15:48:00</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>16689.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>72572024127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2015-09-04 21:10:00</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>16689.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>0</td>\n",
       "      <td>1.187714</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>72572024127</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   segment_id           timestamp  hour  weekday  month pre_dir street_type  \\\n",
       "0           1 2016-11-26 10:13:00    10        5     11       N               \n",
       "1           1 2017-10-16 16:03:00    16        0     10       N               \n",
       "2           1 2010-01-16 15:26:00    15        5      1       N               \n",
       "3           1 2013-12-16 15:48:00    15        0     12       N               \n",
       "4           2 2015-09-04 21:10:00    21        4      9       N               \n",
       "\n",
       "  suf_dir one_way  speed_limit     ...          aadt  sinuosity  \\\n",
       "0               0         40.0     ...       16689.0        1.0   \n",
       "1               0         40.0     ...       16689.0        1.0   \n",
       "2               0         40.0     ...       16689.0        1.0   \n",
       "3               0         40.0     ...       16689.0        1.0   \n",
       "4               0         40.0     ...       16689.0        1.0   \n",
       "\n",
       "   euclidean_length  segment_length  near_major_road  road_orient_approx  \\\n",
       "0        110.198977      110.198977                0            1.022803   \n",
       "1        110.198977      110.198977                0            1.022803   \n",
       "2        110.198977      110.198977                0            1.022803   \n",
       "3        110.198977      110.198977                0            1.022803   \n",
       "4         87.835598       87.835598                0            1.187714   \n",
       "\n",
       "   at_intersection  near_billboard  accident_counts   station_id  \n",
       "0                1               0              4.0  72572024127  \n",
       "1                1               0              4.0  72572024127  \n",
       "2                1               0              4.0  72572024127  \n",
       "3                1               0              4.0  72572024127  \n",
       "4                1               0              7.0  72572024127  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.3 Index by timestamp/road"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "time_index = pd.DatetimeIndex(features.timestamp).floor('1h')\n",
    "feature_timestamp_series = time_index.to_series()\n",
    "segment_id = features.segment_id.astype('str')\n",
    "\n",
    "# Index for fast lookup\n",
    "features['fid'] = feature_timestamp_series.values.astype('str') + segment_id.values\n",
    "features.set_index('fid',inplace=True)\n",
    "\n",
    "# Floor to nearest hour\n",
    "features['timestamp'] = feature_timestamp_series.values\n",
    "\n",
    "\n",
    "# Set target value\n",
    "features['target'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "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>segment_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>hour</th>\n",
       "      <th>weekday</th>\n",
       "      <th>month</th>\n",
       "      <th>pre_dir</th>\n",
       "      <th>street_type</th>\n",
       "      <th>suf_dir</th>\n",
       "      <th>one_way</th>\n",
       "      <th>speed_limit</th>\n",
       "      <th>...</th>\n",
       "      <th>sinuosity</th>\n",
       "      <th>euclidean_length</th>\n",
       "      <th>segment_length</th>\n",
       "      <th>near_major_road</th>\n",
       "      <th>road_orient_approx</th>\n",
       "      <th>at_intersection</th>\n",
       "      <th>near_billboard</th>\n",
       "      <th>accident_counts</th>\n",
       "      <th>station_id</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fid</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-11-26T10:00:00.0000000001</th>\n",
       "      <td>1</td>\n",
       "      <td>2016-11-26 10:00:00</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-10-16T16:00:00.0000000001</th>\n",
       "      <td>1</td>\n",
       "      <td>2017-10-16 16:00:00</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-16T15:00:00.0000000001</th>\n",
       "      <td>1</td>\n",
       "      <td>2010-01-16 15:00:00</td>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-12-16T15:00:00.0000000001</th>\n",
       "      <td>1</td>\n",
       "      <td>2013-12-16 15:00:00</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-09-04T21:00:00.0000000002</th>\n",
       "      <td>2</td>\n",
       "      <td>2015-09-04 21:00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>0</td>\n",
       "      <td>1.187714</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-20T06:00:00.0000000002</th>\n",
       "      <td>2</td>\n",
       "      <td>2011-10-20 06:00:00</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>0</td>\n",
       "      <td>1.187714</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-09-14T07:00:00.0000000002</th>\n",
       "      <td>2</td>\n",
       "      <td>2016-09-14 07:00:00</td>\n",
       "      <td>7</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>0</td>\n",
       "      <td>1.187714</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-03-07T20:00:00.0000000002</th>\n",
       "      <td>2</td>\n",
       "      <td>2013-03-07 20:00:00</td>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>0</td>\n",
       "      <td>1.187714</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-04-16T14:00:00.0000000002</th>\n",
       "      <td>2</td>\n",
       "      <td>2015-04-16 14:00:00</td>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>0</td>\n",
       "      <td>1.187714</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-05-08T21:00:00.0000000002</th>\n",
       "      <td>2</td>\n",
       "      <td>2010-05-08 21:00:00</td>\n",
       "      <td>21</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>0</td>\n",
       "      <td>1.187714</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-08-12T13:00:00.0000000002</th>\n",
       "      <td>2</td>\n",
       "      <td>2016-08-12 13:00:00</td>\n",
       "      <td>13</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>0</td>\n",
       "      <td>1.187714</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>72572024127</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-06-02T12:00:00.0000000004</th>\n",
       "      <td>4</td>\n",
       "      <td>2016-06-02 12:00:00</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>S</td>\n",
       "      <td>ST</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>256.607449</td>\n",
       "      <td>256.607449</td>\n",
       "      <td>0</td>\n",
       "      <td>1.565687</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>72470093141</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-07-06T16:00:00.0000000004</th>\n",
       "      <td>4</td>\n",
       "      <td>2017-07-06 16:00:00</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "      <td>S</td>\n",
       "      <td>ST</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>256.607449</td>\n",
       "      <td>256.607449</td>\n",
       "      <td>0</td>\n",
       "      <td>1.565687</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>72470093141</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-08-17T11:00:00.0000000004</th>\n",
       "      <td>4</td>\n",
       "      <td>2017-08-17 11:00:00</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>S</td>\n",
       "      <td>ST</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>256.607449</td>\n",
       "      <td>256.607449</td>\n",
       "      <td>0</td>\n",
       "      <td>1.565687</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>72470093141</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-09-01T12:00:00.0000000004</th>\n",
       "      <td>4</td>\n",
       "      <td>2011-09-01 12:00:00</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>S</td>\n",
       "      <td>ST</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>256.607449</td>\n",
       "      <td>256.607449</td>\n",
       "      <td>0</td>\n",
       "      <td>1.565687</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>72470093141</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                segment_id           timestamp  hour  weekday  \\\n",
       "fid                                                                             \n",
       "2016-11-26T10:00:00.0000000001           1 2016-11-26 10:00:00    10        5   \n",
       "2017-10-16T16:00:00.0000000001           1 2017-10-16 16:00:00    16        0   \n",
       "2010-01-16T15:00:00.0000000001           1 2010-01-16 15:00:00    15        5   \n",
       "2013-12-16T15:00:00.0000000001           1 2013-12-16 15:00:00    15        0   \n",
       "2015-09-04T21:00:00.0000000002           2 2015-09-04 21:00:00    21        4   \n",
       "2011-10-20T06:00:00.0000000002           2 2011-10-20 06:00:00     6        3   \n",
       "2016-09-14T07:00:00.0000000002           2 2016-09-14 07:00:00     7        2   \n",
       "2013-03-07T20:00:00.0000000002           2 2013-03-07 20:00:00    20        3   \n",
       "2015-04-16T14:00:00.0000000002           2 2015-04-16 14:00:00    14        3   \n",
       "2010-05-08T21:00:00.0000000002           2 2010-05-08 21:00:00    21        5   \n",
       "2016-08-12T13:00:00.0000000002           2 2016-08-12 13:00:00    13        4   \n",
       "2016-06-02T12:00:00.0000000004           4 2016-06-02 12:00:00    12        3   \n",
       "2017-07-06T16:00:00.0000000004           4 2017-07-06 16:00:00    16        3   \n",
       "2017-08-17T11:00:00.0000000004           4 2017-08-17 11:00:00    11        3   \n",
       "2011-09-01T12:00:00.0000000004           4 2011-09-01 12:00:00    12        3   \n",
       "\n",
       "                                month pre_dir street_type suf_dir one_way  \\\n",
       "fid                                                                         \n",
       "2016-11-26T10:00:00.0000000001     11       N                           0   \n",
       "2017-10-16T16:00:00.0000000001     10       N                           0   \n",
       "2010-01-16T15:00:00.0000000001      1       N                           0   \n",
       "2013-12-16T15:00:00.0000000001     12       N                           0   \n",
       "2015-09-04T21:00:00.0000000002      9       N                           0   \n",
       "2011-10-20T06:00:00.0000000002     10       N                           0   \n",
       "2016-09-14T07:00:00.0000000002      9       N                           0   \n",
       "2013-03-07T20:00:00.0000000002      3       N                           0   \n",
       "2015-04-16T14:00:00.0000000002      4       N                           0   \n",
       "2010-05-08T21:00:00.0000000002      5       N                           0   \n",
       "2016-08-12T13:00:00.0000000002      8       N                           0   \n",
       "2016-06-02T12:00:00.0000000004      6       S          ST               0   \n",
       "2017-07-06T16:00:00.0000000004      7       S          ST               0   \n",
       "2017-08-17T11:00:00.0000000004      8       S          ST               0   \n",
       "2011-09-01T12:00:00.0000000004      9       S          ST               0   \n",
       "\n",
       "                                speed_limit   ...   sinuosity  \\\n",
       "fid                                           ...               \n",
       "2016-11-26T10:00:00.0000000001         40.0   ...         1.0   \n",
       "2017-10-16T16:00:00.0000000001         40.0   ...         1.0   \n",
       "2010-01-16T15:00:00.0000000001         40.0   ...         1.0   \n",
       "2013-12-16T15:00:00.0000000001         40.0   ...         1.0   \n",
       "2015-09-04T21:00:00.0000000002         40.0   ...         1.0   \n",
       "2011-10-20T06:00:00.0000000002         40.0   ...         1.0   \n",
       "2016-09-14T07:00:00.0000000002         40.0   ...         1.0   \n",
       "2013-03-07T20:00:00.0000000002         40.0   ...         1.0   \n",
       "2015-04-16T14:00:00.0000000002         40.0   ...         1.0   \n",
       "2010-05-08T21:00:00.0000000002         40.0   ...         1.0   \n",
       "2016-08-12T13:00:00.0000000002         40.0   ...         1.0   \n",
       "2016-06-02T12:00:00.0000000004         40.0   ...         1.0   \n",
       "2017-07-06T16:00:00.0000000004         40.0   ...         1.0   \n",
       "2017-08-17T11:00:00.0000000004         40.0   ...         1.0   \n",
       "2011-09-01T12:00:00.0000000004         40.0   ...         1.0   \n",
       "\n",
       "                                euclidean_length  segment_length  \\\n",
       "fid                                                                \n",
       "2016-11-26T10:00:00.0000000001        110.198977      110.198977   \n",
       "2017-10-16T16:00:00.0000000001        110.198977      110.198977   \n",
       "2010-01-16T15:00:00.0000000001        110.198977      110.198977   \n",
       "2013-12-16T15:00:00.0000000001        110.198977      110.198977   \n",
       "2015-09-04T21:00:00.0000000002         87.835598       87.835598   \n",
       "2011-10-20T06:00:00.0000000002         87.835598       87.835598   \n",
       "2016-09-14T07:00:00.0000000002         87.835598       87.835598   \n",
       "2013-03-07T20:00:00.0000000002         87.835598       87.835598   \n",
       "2015-04-16T14:00:00.0000000002         87.835598       87.835598   \n",
       "2010-05-08T21:00:00.0000000002         87.835598       87.835598   \n",
       "2016-08-12T13:00:00.0000000002         87.835598       87.835598   \n",
       "2016-06-02T12:00:00.0000000004        256.607449      256.607449   \n",
       "2017-07-06T16:00:00.0000000004        256.607449      256.607449   \n",
       "2017-08-17T11:00:00.0000000004        256.607449      256.607449   \n",
       "2011-09-01T12:00:00.0000000004        256.607449      256.607449   \n",
       "\n",
       "                                near_major_road  road_orient_approx  \\\n",
       "fid                                                                   \n",
       "2016-11-26T10:00:00.0000000001                0            1.022803   \n",
       "2017-10-16T16:00:00.0000000001                0            1.022803   \n",
       "2010-01-16T15:00:00.0000000001                0            1.022803   \n",
       "2013-12-16T15:00:00.0000000001                0            1.022803   \n",
       "2015-09-04T21:00:00.0000000002                0            1.187714   \n",
       "2011-10-20T06:00:00.0000000002                0            1.187714   \n",
       "2016-09-14T07:00:00.0000000002                0            1.187714   \n",
       "2013-03-07T20:00:00.0000000002                0            1.187714   \n",
       "2015-04-16T14:00:00.0000000002                0            1.187714   \n",
       "2010-05-08T21:00:00.0000000002                0            1.187714   \n",
       "2016-08-12T13:00:00.0000000002                0            1.187714   \n",
       "2016-06-02T12:00:00.0000000004                0            1.565687   \n",
       "2017-07-06T16:00:00.0000000004                0            1.565687   \n",
       "2017-08-17T11:00:00.0000000004                0            1.565687   \n",
       "2011-09-01T12:00:00.0000000004                0            1.565687   \n",
       "\n",
       "                                at_intersection  near_billboard  \\\n",
       "fid                                                               \n",
       "2016-11-26T10:00:00.0000000001                1               0   \n",
       "2017-10-16T16:00:00.0000000001                1               0   \n",
       "2010-01-16T15:00:00.0000000001                1               0   \n",
       "2013-12-16T15:00:00.0000000001                1               0   \n",
       "2015-09-04T21:00:00.0000000002                1               0   \n",
       "2011-10-20T06:00:00.0000000002                1               0   \n",
       "2016-09-14T07:00:00.0000000002                1               0   \n",
       "2013-03-07T20:00:00.0000000002                1               0   \n",
       "2015-04-16T14:00:00.0000000002                1               0   \n",
       "2010-05-08T21:00:00.0000000002                1               0   \n",
       "2016-08-12T13:00:00.0000000002                1               0   \n",
       "2016-06-02T12:00:00.0000000004                1               1   \n",
       "2017-07-06T16:00:00.0000000004                1               1   \n",
       "2017-08-17T11:00:00.0000000004                1               1   \n",
       "2011-09-01T12:00:00.0000000004                1               1   \n",
       "\n",
       "                                accident_counts   station_id  target  \n",
       "fid                                                                   \n",
       "2016-11-26T10:00:00.0000000001              4.0  72572024127       1  \n",
       "2017-10-16T16:00:00.0000000001              4.0  72572024127       1  \n",
       "2010-01-16T15:00:00.0000000001              4.0  72572024127       1  \n",
       "2013-12-16T15:00:00.0000000001              4.0  72572024127       1  \n",
       "2015-09-04T21:00:00.0000000002              7.0  72572024127       1  \n",
       "2011-10-20T06:00:00.0000000002              7.0  72572024127       1  \n",
       "2016-09-14T07:00:00.0000000002              7.0  72572024127       1  \n",
       "2013-03-07T20:00:00.0000000002              7.0  72572024127       1  \n",
       "2015-04-16T14:00:00.0000000002              7.0  72572024127       1  \n",
       "2010-05-08T21:00:00.0000000002              7.0  72572024127       1  \n",
       "2016-08-12T13:00:00.0000000002              7.0  72572024127       1  \n",
       "2016-06-02T12:00:00.0000000004              9.0  72470093141       1  \n",
       "2017-07-06T16:00:00.0000000004              9.0  72470093141       1  \n",
       "2017-08-17T11:00:00.0000000004              9.0  72470093141       1  \n",
       "2011-09-01T12:00:00.0000000004              9.0  72470093141       1  \n",
       "\n",
       "[15 rows x 23 columns]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "features.head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4 Negative Sampling\n",
    "We have a large set of positive examples, that is, when accidents occured. Any time/segment when an accident didn't occur is a valid negative sample. We sample possible negtive examples by sampling from and mutating the positive examples until they aren't positive. We do this by changing either the hour, day or segment ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "class NegativeSampler:\n",
    "    \"\"\"\n",
    "    Given features (positive examples), roads, times and the number of samples to draw, draws a sample\n",
    "    \"\"\"\n",
    "    def __init__(self,num_samples,\n",
    "                 accidents,roads,\n",
    "                 static_feature_names,\n",
    "                 seed=None):\n",
    "\n",
    "        self.roads = roads\n",
    "        self.accidents = accidents\n",
    "        self.num_samples = num_samples\n",
    "        \n",
    "        self.static_feature_names = static_feature_names\n",
    "        \n",
    "        if seed:\n",
    "            np.random.seed(seed)\n",
    "    \n",
    "    def sample(self):\n",
    "        segment_ids = self.roads.index.to_series()\n",
    "        \n",
    "        altered = pd.DataFrame()\n",
    "        num_to_sample = self.num_samples\n",
    "        \n",
    "        while num_to_sample > 0:\n",
    "            samples = self.accidents.sample(n=num_to_sample,replace=True).reset_index()\n",
    "            alt = self._mod(samples.copy(),segment_ids)\n",
    "            # Create an index\n",
    "         \n",
    "            ats = pd.DatetimeIndex(alt.timestamp).floor('1h').to_series().values.astype('str')\n",
    "            asegid = alt.segment_id.astype('str')\n",
    "            \n",
    "            # Set index on altered samples\n",
    "            alt['fid'] = ats + asegid.values\n",
    "            alt.set_index('fid',inplace=True)\n",
    "            \n",
    "            altered = altered.append(alt)\n",
    "\n",
    "            # Which happened before? They shouldn't get negative samples\n",
    "            intersection = altered.index.intersection(self.accidents.index)\n",
    "            idxer = intersection.get_values()\n",
    "            \n",
    "            # Drop samples where accidents occurred.\n",
    "            altered = altered.drop(idxer)\n",
    "            \n",
    "            num_to_sample = self.num_samples - altered.shape[0]\n",
    "            \n",
    "        \n",
    "        altered['target'] = 0\n",
    "        \n",
    "        ts = pd.DatetimeIndex(altered.timestamp)\n",
    "        segment_ids = altered.segment_id\n",
    "        \n",
    "        altered = altered.reset_index()\n",
    "        \n",
    "        station_ids = self.roads.loc[segment_ids].station_id\n",
    "        static_features = self.roads.loc[segment_ids][self.static_feature_names]\n",
    "        \n",
    "        altered[self.static_feature_names] = static_features.values\n",
    "        altered['station_id'] = station_ids.values\n",
    "        altered['weekday'] = ts.weekday\n",
    "        altered['month'] = ts.month\n",
    "        \n",
    "        altered = altered.drop(columns=['fid'])\n",
    "        return altered\n",
    "    \n",
    "    def _mod(self,samples,segment_ids):\n",
    "        # Get the current timestamps\n",
    "        ts = pd.DatetimeIndex(samples.timestamp)\n",
    "        \n",
    "        # Hour, Day, Year\n",
    "        hour = ts.hour.to_series()\n",
    "        day = ts.dayofyear.to_series()\n",
    "        year = ts.year.to_series()\n",
    "\n",
    "        # Road ID\n",
    "        segment_id = samples.segment_id.copy()\n",
    "        \n",
    "        # Index of samples to mutate\n",
    "        feat_i = np.random.randint(0,3,size=samples.shape[0])\n",
    "\n",
    "        ##########################\n",
    "        # i == 0\n",
    "        # Change hour of day\n",
    "        idx = feat_i == 0\n",
    "        samp_i = samples.loc[idx]\n",
    "        N = samp_i.shape[0]\n",
    "\n",
    "        # Sample until we have all different hours of the day\n",
    "        num_same = N\n",
    "        new_hours = hour[idx].values.copy()\n",
    "        dif_idx = np.ones(N,dtype='bool')\n",
    "        while num_same != 0:\n",
    "            new_hours[dif_idx] = np.random.choice(24,size=num_same)\n",
    "            dif_idx = new_hours == hour[idx]\n",
    "            num_same = dif_idx.sum()\n",
    "\n",
    "        # Create new timestamps\n",
    "        new_timestamps = year[idx].apply(pd.Timestamp,args=(1,1))\n",
    "        new_timestamps = pd.DatetimeIndex(new_timestamps) \n",
    "        new_timestamps += pd.TimedeltaIndex(day[idx]-1,unit='D') # same day\n",
    "        new_timestamps += pd.TimedeltaIndex(new_hours,unit='H') # new hour\n",
    "        \n",
    "        \n",
    "        samples.loc[idx,'hour'] = new_hours\n",
    "        samples.loc[idx,'segment_id'] = segment_id[idx]\n",
    "        samples.loc[idx,'timestamp'] = new_timestamps.copy()\n",
    "        ##########################\n",
    "\n",
    "        ##########################\n",
    "        # i == 1\n",
    "        # Change day of year\n",
    "        idx = feat_i == 1\n",
    "        samp_i = samples.loc[idx]\n",
    "        N = samp_i.shape[0]\n",
    "\n",
    "        is_leap_yr = ts[idx].is_leap_year\n",
    "\n",
    "        # Sample until we have all different days of the year.\n",
    "        num_same = N\n",
    "        new_days = day[idx].values.copy()\n",
    "        dif_idx = np.ones(N,dtype='bool')\n",
    "        while num_same != 0:\n",
    "            # Pay attention to leap years\n",
    "            dif_leap_yr = (dif_idx&is_leap_yr)\n",
    "            dif_no_leap_yr = dif_idx&(~is_leap_yr)\n",
    "            \n",
    "            new_days[dif_leap_yr] = np.random.choice(np.arange(1,367,dtype='int'),size=dif_leap_yr.sum())\n",
    "            new_days[dif_no_leap_yr] = np.random.choice(np.arange(1,366,dtype='int'),size=dif_no_leap_yr.sum())\n",
    "\n",
    "            dif_idx = new_days == day[idx]\n",
    "            num_same = dif_idx.sum()\n",
    "        \n",
    "        # Create new timestamps\n",
    "        timestamps = year[idx].apply(pd.Timestamp,args=(1,1))\n",
    "        new_timestamps = pd.DatetimeIndex(timestamps) \n",
    "        new_timestamps += pd.TimedeltaIndex(new_days,unit='D')  # new day\n",
    "        new_timestamps += pd.TimedeltaIndex(hour[idx],unit='H') # same hour\n",
    "\n",
    "        samples.loc[idx,'hour'] = new_timestamps.hour\n",
    "        samples.loc[idx,'segment_id'] = segment_id[idx]\n",
    "        samples.loc[idx,'timestamp'] = new_timestamps.copy()\n",
    "        ##########################\n",
    "\n",
    "        ##########################\n",
    "        # i == 2\n",
    "        # Change road\n",
    "        idx = feat_i == 2\n",
    "        samp_i = samples.loc[idx]\n",
    "        N = samp_i.shape[0]\n",
    "\n",
    "        num_same = N\n",
    "        new_roads = segment_id[idx].values.copy()\n",
    "        dif_idx = np.ones(N,dtype='bool')\n",
    "\n",
    "        while num_same != 0:\n",
    "            new_roads[dif_idx] = segment_ids.sample(n=num_same,replace=True).values\n",
    "            dif_idx = new_roads == segment_id[idx]\n",
    "            num_same = dif_idx.sum()\n",
    "\n",
    "        samples.loc[idx,'segment_id'] = new_roads\n",
    "        samples.loc[idx,'hour'] = hour[idx].values\n",
    "        samples.loc[idx,'timestamp'] = ts[idx].copy()\n",
    "        \n",
    "        samples['segment_id'] = samples['segment_id'].astype('int64')     \n",
    "        return samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "39.565378189086914 seconds\n"
     ]
    }
   ],
   "source": [
    "t = time.time()\n",
    "N = features.shape[0] * 4\n",
    "ns = NegativeSampler(N,features,rdf,static_feature_names,seed=12345)\n",
    "negative_examples = ns.sample().set_index(['timestamp','segment_id','station_id'])\n",
    "\n",
    "print((time.time() - t),'seconds')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "if not os.path.exists('training_data'):\n",
    "    os.mkdir('training_data')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "negative_examples.to_csv('training_data/utah_negative_examples.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>hour</th>\n",
       "      <th>weekday</th>\n",
       "      <th>month</th>\n",
       "      <th>pre_dir</th>\n",
       "      <th>street_type</th>\n",
       "      <th>suf_dir</th>\n",
       "      <th>one_way</th>\n",
       "      <th>speed_limit</th>\n",
       "      <th>surface_type</th>\n",
       "      <th>surface_width</th>\n",
       "      <th>aadt</th>\n",
       "      <th>sinuosity</th>\n",
       "      <th>euclidean_length</th>\n",
       "      <th>segment_length</th>\n",
       "      <th>near_major_road</th>\n",
       "      <th>road_orient_approx</th>\n",
       "      <th>at_intersection</th>\n",
       "      <th>near_billboard</th>\n",
       "      <th>accident_counts</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th>segment_id</th>\n",
       "      <th>station_id</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>2014-01-11 17:00:00</th>\n",
       "      <th>23524</th>\n",
       "      <th>72575524101</th>\n",
       "      <td>17</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>25.0</td>\n",
       "      <td>PAVED</td>\n",
       "      <td>0</td>\n",
       "      <td>3183.0</td>\n",
       "      <td>1.010711</td>\n",
       "      <td>721.107956</td>\n",
       "      <td>728.831980</td>\n",
       "      <td>0</td>\n",
       "      <td>2.124443</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-10-09 20:00:00</th>\n",
       "      <th>5753</th>\n",
       "      <th>72572024127</th>\n",
       "      <td>20</td>\n",
       "      <td>3</td>\n",
       "      <td>10</td>\n",
       "      <td>S</td>\n",
       "      <td></td>\n",
       "      <td>E</td>\n",
       "      <td>0</td>\n",
       "      <td>35.0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>11763.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>74.051639</td>\n",
       "      <td>74.051639</td>\n",
       "      <td>0</td>\n",
       "      <td>1.562353</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-08-19 08:00:00</th>\n",
       "      <th>141302</th>\n",
       "      <th>72570594030</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td></td>\n",
       "      <td>RD</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>25.0</td>\n",
       "      <td></td>\n",
       "      <td>18</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000042</td>\n",
       "      <td>418.596688</td>\n",
       "      <td>418.614378</td>\n",
       "      <td>0</td>\n",
       "      <td>1.240349</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-11-12 13:00:00</th>\n",
       "      <th>153026</th>\n",
       "      <th>72572424174</th>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>W</td>\n",
       "      <td></td>\n",
       "      <td>S</td>\n",
       "      <td>0</td>\n",
       "      <td>25.0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>197.361685</td>\n",
       "      <td>197.361685</td>\n",
       "      <td>0</td>\n",
       "      <td>0.018032</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-27 16:00:00</th>\n",
       "      <th>44435</th>\n",
       "      <th>72572424174</th>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>E</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>65.0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>20191.0</td>\n",
       "      <td>1.076068</td>\n",
       "      <td>2629.327473</td>\n",
       "      <td>2829.333868</td>\n",
       "      <td>0</td>\n",
       "      <td>0.488510</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>140.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            hour  weekday  month pre_dir  \\\n",
       "timestamp           segment_id station_id                                  \n",
       "2014-01-11 17:00:00 23524      72575524101    17        5      1           \n",
       "2014-10-09 20:00:00 5753       72572024127    20        3     10       S   \n",
       "2015-08-19 08:00:00 141302     72570594030     8        2      8           \n",
       "2014-11-12 13:00:00 153026     72572424174    13        2     11       W   \n",
       "2017-01-27 16:00:00 44435      72572424174    16        4      1       E   \n",
       "\n",
       "                                           street_type suf_dir one_way  \\\n",
       "timestamp           segment_id station_id                                \n",
       "2014-01-11 17:00:00 23524      72575524101                           1   \n",
       "2014-10-09 20:00:00 5753       72572024127                   E       0   \n",
       "2015-08-19 08:00:00 141302     72570594030          RD               0   \n",
       "2014-11-12 13:00:00 153026     72572424174                   S       0   \n",
       "2017-01-27 16:00:00 44435      72572424174                           0   \n",
       "\n",
       "                                            speed_limit surface_type  \\\n",
       "timestamp           segment_id station_id                              \n",
       "2014-01-11 17:00:00 23524      72575524101         25.0        PAVED   \n",
       "2014-10-09 20:00:00 5753       72572024127         35.0                \n",
       "2015-08-19 08:00:00 141302     72570594030         25.0                \n",
       "2014-11-12 13:00:00 153026     72572424174         25.0                \n",
       "2017-01-27 16:00:00 44435      72572424174         65.0                \n",
       "\n",
       "                                            surface_width     aadt  sinuosity  \\\n",
       "timestamp           segment_id station_id                                       \n",
       "2014-01-11 17:00:00 23524      72575524101              0   3183.0   1.010711   \n",
       "2014-10-09 20:00:00 5753       72572024127              0  11763.0   1.000000   \n",
       "2015-08-19 08:00:00 141302     72570594030             18      0.0   1.000042   \n",
       "2014-11-12 13:00:00 153026     72572424174              0      0.0   1.000000   \n",
       "2017-01-27 16:00:00 44435      72572424174              0  20191.0   1.076068   \n",
       "\n",
       "                                            euclidean_length  segment_length  \\\n",
       "timestamp           segment_id station_id                                      \n",
       "2014-01-11 17:00:00 23524      72575524101        721.107956      728.831980   \n",
       "2014-10-09 20:00:00 5753       72572024127         74.051639       74.051639   \n",
       "2015-08-19 08:00:00 141302     72570594030        418.596688      418.614378   \n",
       "2014-11-12 13:00:00 153026     72572424174        197.361685      197.361685   \n",
       "2017-01-27 16:00:00 44435      72572424174       2629.327473     2829.333868   \n",
       "\n",
       "                                            near_major_road  \\\n",
       "timestamp           segment_id station_id                     \n",
       "2014-01-11 17:00:00 23524      72575524101                0   \n",
       "2014-10-09 20:00:00 5753       72572024127                0   \n",
       "2015-08-19 08:00:00 141302     72570594030                0   \n",
       "2014-11-12 13:00:00 153026     72572424174                0   \n",
       "2017-01-27 16:00:00 44435      72572424174                0   \n",
       "\n",
       "                                            road_orient_approx  \\\n",
       "timestamp           segment_id station_id                        \n",
       "2014-01-11 17:00:00 23524      72575524101            2.124443   \n",
       "2014-10-09 20:00:00 5753       72572024127            1.562353   \n",
       "2015-08-19 08:00:00 141302     72570594030            1.240349   \n",
       "2014-11-12 13:00:00 153026     72572424174            0.018032   \n",
       "2017-01-27 16:00:00 44435      72572424174            0.488510   \n",
       "\n",
       "                                            at_intersection  near_billboard  \\\n",
       "timestamp           segment_id station_id                                     \n",
       "2014-01-11 17:00:00 23524      72575524101                0               0   \n",
       "2014-10-09 20:00:00 5753       72572024127                0               0   \n",
       "2015-08-19 08:00:00 141302     72570594030                0               0   \n",
       "2014-11-12 13:00:00 153026     72572424174                1               0   \n",
       "2017-01-27 16:00:00 44435      72572424174                1               0   \n",
       "\n",
       "                                            accident_counts  target  \n",
       "timestamp           segment_id station_id                            \n",
       "2014-01-11 17:00:00 23524      72575524101             35.0       0  \n",
       "2014-10-09 20:00:00 5753       72572024127              3.0       0  \n",
       "2015-08-19 08:00:00 141302     72570594030              0.0       0  \n",
       "2014-11-12 13:00:00 153026     72572424174             28.0       0  \n",
       "2017-01-27 16:00:00 44435      72572424174            140.0       0  "
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "negative_examples.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "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></th>\n",
       "      <th></th>\n",
       "      <th>hour</th>\n",
       "      <th>weekday</th>\n",
       "      <th>month</th>\n",
       "      <th>pre_dir</th>\n",
       "      <th>street_type</th>\n",
       "      <th>suf_dir</th>\n",
       "      <th>one_way</th>\n",
       "      <th>speed_limit</th>\n",
       "      <th>surface_type</th>\n",
       "      <th>surface_width</th>\n",
       "      <th>aadt</th>\n",
       "      <th>sinuosity</th>\n",
       "      <th>euclidean_length</th>\n",
       "      <th>segment_length</th>\n",
       "      <th>near_major_road</th>\n",
       "      <th>road_orient_approx</th>\n",
       "      <th>at_intersection</th>\n",
       "      <th>near_billboard</th>\n",
       "      <th>accident_counts</th>\n",
       "      <th>target</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</th>\n",
       "      <th>segment_id</th>\n",
       "      <th>station_id</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-11-26 10:00:00</th>\n",
       "      <th>1</th>\n",
       "      <th>72572024127</th>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>16689.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-10-16 16:00:00</th>\n",
       "      <th>1</th>\n",
       "      <th>72572024127</th>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>16689.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-16 15:00:00</th>\n",
       "      <th>1</th>\n",
       "      <th>72572024127</th>\n",
       "      <td>15</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>16689.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-12-16 15:00:00</th>\n",
       "      <th>1</th>\n",
       "      <th>72572024127</th>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>16689.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>110.198977</td>\n",
       "      <td>0</td>\n",
       "      <td>1.022803</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-09-04 21:00:00</th>\n",
       "      <th>2</th>\n",
       "      <th>72572024127</th>\n",
       "      <td>21</td>\n",
       "      <td>4</td>\n",
       "      <td>9</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>40.0</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>16689.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>87.835598</td>\n",
       "      <td>0</td>\n",
       "      <td>1.187714</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            hour  weekday  month pre_dir  \\\n",
       "timestamp           segment_id station_id                                  \n",
       "2016-11-26 10:00:00 1          72572024127    10        5     11       N   \n",
       "2017-10-16 16:00:00 1          72572024127    16        0     10       N   \n",
       "2010-01-16 15:00:00 1          72572024127    15        5      1       N   \n",
       "2013-12-16 15:00:00 1          72572024127    15        0     12       N   \n",
       "2015-09-04 21:00:00 2          72572024127    21        4      9       N   \n",
       "\n",
       "                                           street_type suf_dir one_way  \\\n",
       "timestamp           segment_id station_id                                \n",
       "2016-11-26 10:00:00 1          72572024127                           0   \n",
       "2017-10-16 16:00:00 1          72572024127                           0   \n",
       "2010-01-16 15:00:00 1          72572024127                           0   \n",
       "2013-12-16 15:00:00 1          72572024127                           0   \n",
       "2015-09-04 21:00:00 2          72572024127                           0   \n",
       "\n",
       "                                            speed_limit surface_type  \\\n",
       "timestamp           segment_id station_id                              \n",
       "2016-11-26 10:00:00 1          72572024127         40.0                \n",
       "2017-10-16 16:00:00 1          72572024127         40.0                \n",
       "2010-01-16 15:00:00 1          72572024127         40.0                \n",
       "2013-12-16 15:00:00 1          72572024127         40.0                \n",
       "2015-09-04 21:00:00 2          72572024127         40.0                \n",
       "\n",
       "                                            surface_width     aadt  sinuosity  \\\n",
       "timestamp           segment_id station_id                                       \n",
       "2016-11-26 10:00:00 1          72572024127              0  16689.0        1.0   \n",
       "2017-10-16 16:00:00 1          72572024127              0  16689.0        1.0   \n",
       "2010-01-16 15:00:00 1          72572024127              0  16689.0        1.0   \n",
       "2013-12-16 15:00:00 1          72572024127              0  16689.0        1.0   \n",
       "2015-09-04 21:00:00 2          72572024127              0  16689.0        1.0   \n",
       "\n",
       "                                            euclidean_length  segment_length  \\\n",
       "timestamp           segment_id station_id                                      \n",
       "2016-11-26 10:00:00 1          72572024127        110.198977      110.198977   \n",
       "2017-10-16 16:00:00 1          72572024127        110.198977      110.198977   \n",
       "2010-01-16 15:00:00 1          72572024127        110.198977      110.198977   \n",
       "2013-12-16 15:00:00 1          72572024127        110.198977      110.198977   \n",
       "2015-09-04 21:00:00 2          72572024127         87.835598       87.835598   \n",
       "\n",
       "                                            near_major_road  \\\n",
       "timestamp           segment_id station_id                     \n",
       "2016-11-26 10:00:00 1          72572024127                0   \n",
       "2017-10-16 16:00:00 1          72572024127                0   \n",
       "2010-01-16 15:00:00 1          72572024127                0   \n",
       "2013-12-16 15:00:00 1          72572024127                0   \n",
       "2015-09-04 21:00:00 2          72572024127                0   \n",
       "\n",
       "                                            road_orient_approx  \\\n",
       "timestamp           segment_id station_id                        \n",
       "2016-11-26 10:00:00 1          72572024127            1.022803   \n",
       "2017-10-16 16:00:00 1          72572024127            1.022803   \n",
       "2010-01-16 15:00:00 1          72572024127            1.022803   \n",
       "2013-12-16 15:00:00 1          72572024127            1.022803   \n",
       "2015-09-04 21:00:00 2          72572024127            1.187714   \n",
       "\n",
       "                                            at_intersection  near_billboard  \\\n",
       "timestamp           segment_id station_id                                     \n",
       "2016-11-26 10:00:00 1          72572024127                1               0   \n",
       "2017-10-16 16:00:00 1          72572024127                1               0   \n",
       "2010-01-16 15:00:00 1          72572024127                1               0   \n",
       "2013-12-16 15:00:00 1          72572024127                1               0   \n",
       "2015-09-04 21:00:00 2          72572024127                1               0   \n",
       "\n",
       "                                            accident_counts  target  \n",
       "timestamp           segment_id station_id                            \n",
       "2016-11-26 10:00:00 1          72572024127              4.0       1  \n",
       "2017-10-16 16:00:00 1          72572024127              4.0       1  \n",
       "2010-01-16 15:00:00 1          72572024127              4.0       1  \n",
       "2013-12-16 15:00:00 1          72572024127              4.0       1  \n",
       "2015-09-04 21:00:00 2          72572024127              7.0       1  "
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reselect Features\n",
    "positive_examples = df\\\n",
    "    .xs(['segment_id']+temporal_feature_names,axis=1)\\\n",
    "    .set_index('segment_id')\\\n",
    "    .join(\n",
    "        rdf.xs(static_feature_names+['station_id'],axis=1),\n",
    "        how='left'\n",
    "    ).reset_index()\n",
    "    \n",
    "positive_examples = positive_examples.set_index([time_index.floor('1h'),'segment_id','station_id'])\n",
    "positive_examples['target'] = 1\n",
    "positive_examples.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "positive_examples.to_csv('training_data/utah_positive_examples.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rdf.to_csv('training_data/road_features.csv')\n",
    "df.to_csv('training_data/collisions_new.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Join Hourly Weather Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pdf = pd.read_csv('training_data/utah_positive_examples.csv')\n",
    "ndf = pd.read_csv('training_data/utah_negative_examples.csv')\n",
    "###Make sure you have run get_weather.ipynb to pull this csv below\n",
    "wdf = pd.read_csv('utah_weather_2010-2018_grouped.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pdf['timestamp'] = pd.DatetimeIndex(pd.to_datetime(pdf.timestamp))\\\n",
    "    .tz_localize('US/Mountain',ambiguous='NaT',errors='coerce').tz_convert('utc')\n",
    "ndf['timestamp'] = pd.DatetimeIndex(pd.to_datetime(ndf.timestamp))\\\n",
    "    .tz_localize('US/Mountain',ambiguous='NaT',errors='coerce').tz_convert('utc')\n",
    "wdf['timestamp'] = pd.DatetimeIndex(pd.to_datetime(wdf.timestamp))\\\n",
    "    .tz_localize('utc')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def add_join_key(df):\n",
    "    df['join_key'] = df.station_id.map(int).map(str)+df.timestamp.map(datetime.isoformat)\n",
    "    df = df.set_index('join_key')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pdf = add_join_key(pdf)\n",
    "ndf = add_join_key(ndf)\n",
    "wdf = add_join_key(wdf)\n",
    "pdf = pdf.join(wdf.drop(columns=['timestamp', 'station_id']))\n",
    "ndf = ndf.join(wdf.drop(columns=['timestamp', 'station_id']))\n",
    "\n",
    "# Append negative samples to positive ones\n",
    "tset = pdf.append(ndf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "tset.to_csv('training_data/utah_training_set.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>timestamp</th>\n",
       "      <th>segment_id</th>\n",
       "      <th>station_id</th>\n",
       "      <th>hour</th>\n",
       "      <th>weekday</th>\n",
       "      <th>month</th>\n",
       "      <th>pre_dir</th>\n",
       "      <th>street_type</th>\n",
       "      <th>suf_dir</th>\n",
       "      <th>one_way</th>\n",
       "      <th>...</th>\n",
       "      <th>precip_depth</th>\n",
       "      <th>snow_depth</th>\n",
       "      <th>snowing</th>\n",
       "      <th>raining</th>\n",
       "      <th>foggy</th>\n",
       "      <th>icy</th>\n",
       "      <th>hailing</th>\n",
       "      <th>thunderstorm</th>\n",
       "      <th>solar_azimuth</th>\n",
       "      <th>solar_elevation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>join_key</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>724700931412010-01-01T09:00:00+00:00</th>\n",
       "      <td>2010-01-01 09:00:00+00:00</td>\n",
       "      <td>21821</td>\n",
       "      <td>72470093141</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>S</td>\n",
       "      <td>ST</td>\n",
       "      <td></td>\n",
       "      <td>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>53.133310</td>\n",
       "      <td>-64.221430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>724700931412010-01-01T18:00:00+00:00</th>\n",
       "      <td>2010-01-01 18:00:00+00:00</td>\n",
       "      <td>32221</td>\n",
       "      <td>72470093141</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>S</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>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>157.114449</td>\n",
       "      <td>22.926000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>724700931412010-01-02T09:00:00+00:00</th>\n",
       "      <td>2010-01-02 09:00:00+00:00</td>\n",
       "      <td>42886</td>\n",
       "      <td>72470093141</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>E</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>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>52.812066</td>\n",
       "      <td>-64.224402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>724700931412010-01-03T01:00:00+00:00</th>\n",
       "      <td>2010-01-03 01:00:00+00:00</td>\n",
       "      <td>28854</td>\n",
       "      <td>72470093141</td>\n",
       "      <td>18</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>W</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>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>247.537151</td>\n",
       "      <td>-8.984745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>724700931412010-01-03T15:00:00+00:00</th>\n",
       "      <td>2010-01-03 15:00:00+00:00</td>\n",
       "      <td>11320</td>\n",
       "      <td>72470093141</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>E</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>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>121.156632</td>\n",
       "      <td>0.908545</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     timestamp  segment_id  \\\n",
       "join_key                                                                     \n",
       "724700931412010-01-01T09:00:00+00:00 2010-01-01 09:00:00+00:00       21821   \n",
       "724700931412010-01-01T18:00:00+00:00 2010-01-01 18:00:00+00:00       32221   \n",
       "724700931412010-01-02T09:00:00+00:00 2010-01-02 09:00:00+00:00       42886   \n",
       "724700931412010-01-03T01:00:00+00:00 2010-01-03 01:00:00+00:00       28854   \n",
       "724700931412010-01-03T15:00:00+00:00 2010-01-03 15:00:00+00:00       11320   \n",
       "\n",
       "                                       station_id  hour  weekday  month  \\\n",
       "join_key                                                                  \n",
       "724700931412010-01-01T09:00:00+00:00  72470093141     2        4      1   \n",
       "724700931412010-01-01T18:00:00+00:00  72470093141    11        4      1   \n",
       "724700931412010-01-02T09:00:00+00:00  72470093141     2        5      1   \n",
       "724700931412010-01-03T01:00:00+00:00  72470093141    18        5      1   \n",
       "724700931412010-01-03T15:00:00+00:00  72470093141     8        6      1   \n",
       "\n",
       "                                     pre_dir street_type suf_dir  one_way  \\\n",
       "join_key                                                                    \n",
       "724700931412010-01-01T09:00:00+00:00       S          ST                0   \n",
       "724700931412010-01-01T18:00:00+00:00       S                            0   \n",
       "724700931412010-01-02T09:00:00+00:00       E                            0   \n",
       "724700931412010-01-03T01:00:00+00:00       W                            0   \n",
       "724700931412010-01-03T15:00:00+00:00       E                            0   \n",
       "\n",
       "                                           ...         precip_depth  \\\n",
       "join_key                                   ...                        \n",
       "724700931412010-01-01T09:00:00+00:00       ...                  0.0   \n",
       "724700931412010-01-01T18:00:00+00:00       ...                  0.0   \n",
       "724700931412010-01-02T09:00:00+00:00       ...                  0.0   \n",
       "724700931412010-01-03T01:00:00+00:00       ...                  0.0   \n",
       "724700931412010-01-03T15:00:00+00:00       ...                  0.0   \n",
       "\n",
       "                                     snow_depth  snowing  raining  foggy  icy  \\\n",
       "join_key                                                                        \n",
       "724700931412010-01-01T09:00:00+00:00        0.0      0.0      0.0    0.0  0.0   \n",
       "724700931412010-01-01T18:00:00+00:00        0.0      0.0      0.0    0.0  0.0   \n",
       "724700931412010-01-02T09:00:00+00:00        0.0      0.0      0.0    0.0  0.0   \n",
       "724700931412010-01-03T01:00:00+00:00        0.0      0.0      0.0    0.0  0.0   \n",
       "724700931412010-01-03T15:00:00+00:00        0.0      0.0      0.0    0.0  0.0   \n",
       "\n",
       "                                      hailing  thunderstorm  solar_azimuth  \\\n",
       "join_key                                                                     \n",
       "724700931412010-01-01T09:00:00+00:00      0.0           0.0      53.133310   \n",
       "724700931412010-01-01T18:00:00+00:00      0.0           0.0     157.114449   \n",
       "724700931412010-01-02T09:00:00+00:00      0.0           0.0      52.812066   \n",
       "724700931412010-01-03T01:00:00+00:00      0.0           0.0     247.537151   \n",
       "724700931412010-01-03T15:00:00+00:00      0.0           0.0     121.156632   \n",
       "\n",
       "                                      solar_elevation  \n",
       "join_key                                               \n",
       "724700931412010-01-01T09:00:00+00:00       -64.221430  \n",
       "724700931412010-01-01T18:00:00+00:00        22.926000  \n",
       "724700931412010-01-02T09:00:00+00:00       -64.224402  \n",
       "724700931412010-01-03T01:00:00+00:00        -8.984745  \n",
       "724700931412010-01-03T15:00:00+00:00         0.908545  \n",
       "\n",
       "[5 rows x 36 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}