Traffix Parser.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Traffix Parser\n",
    "\n",
    "This notebook ingests the CMP historical data and parses two separate traffix xml files then joins them.\n",
    "\n",
    "As the separate files are put together, relevant fields are pulled from the traffix files.\n",
    "\n",
    "\n",
    "## Sourcefiles\n",
    "The CMP historical shapefile data is good since it has geocoordinates and historical LOS scores\n",
    "\n",
    "2016CMP80_PM.xml Appears to be a traffix forecasted output file which has pretty good detail about the intersections\n",
    "\n",
    "CMP_80_traffix  is more of a traffix input file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import geopandas as gpd\n",
    "%matplotlib inline\n",
    "import pandas as pd\n",
    "\n",
    "from json import dumps\n",
    "from xmljson import badgerfish as bf\n",
    "from xml.etree.ElementTree import Element, tostring\n",
    "import xml.etree.ElementTree as ET\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "CMP_Int = gpd.read_file('data/CMP_Int.shp')\n",
    "CMP_Int = CMP_Int.to_crs({'init': 'epsg:4326'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['OBJECTID', 'STATUS', 'SCORE', 'SIDE', 'ARC_STREET', 'ID', 'CMP_SYSTEM',\n",
       "       'CROSS_STRE', 'INTERSECTI', 'LOCATION', 'JURISDICTI', 'YR_1991',\n",
       "       'YR_1992', 'YR_1993', 'YR_1994', 'YR_1996', 'YR_1997', 'YR_1998',\n",
       "       'YR_2000', 'YR_2001', 'YR_2002', 'YR_2004', 'YR_2006', 'YR_2008',\n",
       "       'YR_2010', 'LOS_RATING', 'LOS_2008', 'LOS_2006', 'LOS_2004', 'YR_2012',\n",
       "       'ID_1', 'YR_2014', 'YR_2016', 'geometry'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CMP_Int.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x1130de908>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAVUAAAD8CAYAAADHaDe8AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4yLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvNQv5yAAAIABJREFUeJzt3W2QXNWZH/D/Mz0t0SOzjJCHBA0IYewIG2Rp1hPQRiELii05wbCDbV60ptbrKoe4knyAuCYllbUr4VXC7I5xXKnNfqBcu+UkhBVvHoMEljGSN1hrXkaeGWStpWVZDLjlLHLEYEsapNbMkw99b3P7zj33ntt9uvtO9/9XNaXpe/t2nxlNP/ec57yJqoKIiNzoanUBiIjaCYMqEZFDDKpERA4xqBIROcSgSkTkEIMqEZFDDKpERA4xqBIROcSgSkTkUHerCxD2/ve/X1euXNnqYhARVRw8ePCXqtpn89zMBdWVK1difHy81cUgIqoQkddtn8vmPxGRQwyqREQOMagSETnEoEpE5BCDKhGRQwyqREQOMagSETnEoEpE5BCDKhGRQwyqREQOMagSETmUubn/9RqbKGJ071Ecm57B8t4ChjetwtBAf6uLRUQdoq2C6thEEcOPTqE0qwCA4vQMhh+dAgAGViJqirZq/t/75OFKQPWVZhX3PDyJsYlii0pFRJ2krWqqb58uRR5XBbY+fgiAucbKtAERudBWNdU4M6VZjO49GnlubKKIrY8fQnF6Bopy2mDr44dYuyWi1NoqqPYW8rHnj03PRB4f3XsUM6XZqmNxQZiaZ2yiiPUj+3D5lj1YP7KPNzrKvLZq/u+4+SoMPzKF0pxGnl/eW4g8bgq2puPUOME0zAWFPE6dPVfV8ZiUxiFqtbaqqQ4N9GP01jVY2jO/xlrI5zC8aVXkdaZgazpOjRFOw0zPlOZ1PLIFQVnXVkEVKAfWiT/ciG/cvhb9vQUIgP7eAu779Gpj7WZ40yoU8rmqY3FBmBojKg0ThS0IyrK2av4HDQ30xzYRw739n/lYP/YfOZ6695+jBszS/m5sgyVbEJRlbRtU4/jNTL9WVJyewWMHi7G1WdvX6ZScX1LAHJsoVuW3i9MzGH4kfiLG8t4CigmBlS0Iyrq2a/4nGZso4ssPTznp7e/UUQNRQ9Du2TWJbWOHKs/Z8cTheR2GpTnFjicOG183Kg2T7xIs7clbpXGIsqCjaqp+MJjV6NEBaXN1plpVO+T8to0dwkMvvIlZVeREsPnaS7FzaDWA6JuJAnjw+TcweNmFGBrox/RM9EQM03HgvRos0ym0kHVUUE3qCEmTqxubKEJQDib1vI4LrvO628YO4X89/0bl8axq5fHOodXGm4ai/Duu572TcuE+5rIpqzoqqMbVIMO5uqQP7ejeo5EBVYCm5vwakdd96IU3jcd3Dq2OzX36v+OlPfnIacNRw91s+f8nxemZqhtaJ+WyKfs6KqdqqkHmRKpydTbTVuNqa838YDcir2tKj/jHhzetghiu9X/H22+6Cvlc9bPyOcH2m66qqUzB/xNgfguhE3LZtDC0fVANTnM8ffYc8l3VH/RCPof7b1szrxaaFKhMAbq/yU3/RswGy0l0yPSPDw3043PrVswLrMHa/tBAP0Y/u6ZqrPDoZ9fUfMOxGcPaDrlsWvjauvkfbhq/fbqEfE7QW8jjnZmSMRdnE6iGN62qem2g3PQvTs9g/ci+puX4TE3xpLxuXHpj87WXVuVUfZuvvbTy/c6h1Ri87MLYFIltfjSpjEnDrHwcv0pZkBhUReQ8AP8HwGLv+Y+q6nYReQ7A+d7TLgLwoqoOGV7jNwD8FMC3VfU/OCm5hajaTWlWsWRxNya3bzReZwpUXSK4fMueSgC579OrW57jiwruSWM5k/Kwg5ddiIdefBOzgSFRuS7B4GUXVr1OvUEzTriMSTh+lbLCpvl/BsAGVV0DYC2AT4rIOlW9TlXXqupaAD8C8HjMa/wRgL+qv7jp2DaNwysh3XBl37zxkkA5pxjMsQLAgS0b0N9baFmOb2igH/d9erX1lFwgOb0xuvdoVUAFgNk5revnSbvalE1z308/cPwqZUliTVVVFcBJ72He+6p84kTkfAAbAHwh6noR+RiAfwTguwAG6yxvKjZNY9PsquC01S6ReZ03fhAaGuiPDd5jE0Xc++ThSk94byGPHTdf5TQApK0xJt1sXOZpxyaK2PHE4arxqaaafDAlEd1VVu2/3r6WgZQyx6qjSkRyIjIJ4C0Az6jqC4HTtwB4VlV/FXFdF4D7AQy7KGxaNgulmGpt+48cx4EtG/DayI2Yi5ksMDZRRJehY+eCQh7Dj05VDS2aninh7tDso2ZLWpXL1apd/g0rasB/uCYfHnGRpL+3wIBKmWQVVFV11mvmXwLgGhG5OnB6M4CHDJf+OwBPqWr0wEePiNwlIuMiMn78+HGbIlmxaRrb1MpMwaS3J2+coSUASrNz85au8z34/BstW3A56WYTdV4A3HBlX+JrB5v5UdOBg/yb0vqRfbh71yTzp9QWRA21MOMFItsBnFLVr4nIMgB/C6BfVd+NeO6DAK4DMAfgfQAWAfgzVd1iev3BwUEdHx9PVaZ6rB/ZF5ki6O8t4MCWDQCiO00K+RwWd3fFTrtM4r9HrbOD6plVlHTttrFDePD5N6pqjYV8LjZ3mbZzaWlPHu+W5qyen/NSMP2cPUUtICIHVdUqfZkYVEWkD0BJVadFpADgewD+WFV3i8iXAPyWqn7eolC/D2Awqfe/2UHVFDDDwSMqCN2za9KqqWoiKOcFowLR0p48tt9kzr3alrtWNjcb22ui5HOCc7Oa+PszvR+nqVIzpQmqNuNULwbwLRHJoZwueFhVd3vn7gAwEnrzQQBfUtUvpihzy9gu4hHVGZRmDGWU5b0FYy/326dLlc6cqPLF9eC7CC5JaZGooGbbkVVOjSTfjkzN/E5ecpGyL3Xzv9GaXVOtR9rmbpBfq0yq7fYW8jhzbm5ejdT0ngLgtZEbU5cn7MN/8DRmSnOR5dlx81WRteTz8l2R8/1zIphTnbfnVJxwMz8YxKNGY/jXmGrRRPVwXVMlg2AtNzwBIIlfq+w1LDziM/Wc5wyBxbaHPq75PDZRjAyoACBiHjGxuLtrXsAPpiTWj+xLzEH7zweAe588jLt3Tc57jqulG4kagTVVh9JOqwTKizBD7JrDYXEBLKmccfnYga9+LzbQm/g54uBuqCLA9OmS1ar+ORHcf9saAMDwo1OpfyesqVKjOO2oarasBdVaOkTSpgV6C+Xl8MK1uLgmdX8gt5q2s8bUoeQ302v9i0gaMRFXk8/nBEsWdeOdmZKxeR/HZScdURib/47U2iHinwvOpIrzzkwJr43cGBnAARjn9tc6997UTE4byIK6AJw+e66yNsKpM+cidweICqxLFuVw9txc5aZiWw7/JhC8oXBUALUag2oMU+7wyw/Hb2Dnnwt/yE01MD8PGg6S/rXBHKqLcZo2TfG05oDKDSTutRXlGq0f9FYuK+DAqydSv59p2BtHBVCrtf16qvWIq9HdvWsSA1/9XuKsqKGB/sp01/tvW5M4bdYXXpR5VhWFfA43XNmH0b1HrRcmiRI1Y6pZ/BTBayM34oYr+2oKqKYFVDp1I0bKFtZUYyTV6IJjSW1qQmk2tjMFiOAsp1prYkMD/Rh//URlYz9baUY3RAnfQEzbtvi6BPAXy7JZiKYRC3YTpcWgGiNqrdKwtAPubfOgcdu11PP+QLkW/NjBYqqAWsjnKit31Zo6CNcu496/lo6nWhfsjsMcLaXFoBrD//B8+eGp2ADQiJpQmrxn2vc3zeIKdvzccGVfZenDYKfZ/iPlBW/S1lqjVpUyjbUF5gdgX9zW2bUs2B2FGwxSPRhUE/gfnrga6/LegvMajWm7lqgQ1NuTx/qRfdbvbQrCc6rG2VjhTqBgT35/IAhHTYIwBTbTti13rlthDKhxW2enSa+Yfsbw2q8uWgbUWRhULfgfoPAHDkCl88h1r3NUgLjhyj48drA4L7i/fbpU1fOe9N61NJOjard+QA0PuLe9wfg1zHDNc/CyCyNvEklbZ/s/c9rf+dhEEV/59iGcOms3rpg5WorDoGopaohUoxc3iQoQg5ddmDj+Nem9a2kmp+kEShPYdg6trgREoBzghh+ZQsnroSpOz2D4kfIQtqSts2sxNlFMPXuLGwxSHAbVlKICxj0R89OBxtRohgb6Mbr3aOKkAn9X16jaYi3N5KTabTAPWc+Y2h1PHK4EVF9pTrHjicPGHKxpS20bo3uPpgqo+S7hAtkUi0HVgUb0OsexCdb+dtlAbemIcI185bLCvL2j/Pe4fOseBGOdH/hqeV/TgivTMyXcuW5F4tbZScI/V9qRDIu6u5hPpVgMqg646nW2lbSyVVSHVjAlkDTzKOp8VPDx3yOu9Z2UiggHuTimHGwwfRAlOGIgKO3KYgBw6uwsxiaKDKxkxKDqQL29zmnFBbH+mNqXX8NNygHbbA+dhqlmHRW8TXry5cl/4RxskvCIgbBasrHs/ac4DKqOJHXOuBxy9Y6hiSwADmzZYFyFyq8J1rpFda1MQ87SBO/SnNZUQ0yateULz9761JqLjcGYvf8Uh3P/myC8/bLf3K51N9WkLaSTdkutdYvqWgSHnAV//rt3TabKZ5ZmFV9+eCr178x2ZMCclm9Kd65bgcntG7FzaHVlScYw9v5THAbVJnC90EdS0EzamruWLarT8Dvj/ffdf+S4k3TCrGrqm1GakQGK6q3Dd9x8lfUCOEQ+Nv+bwNRcLE7PVNYfTZMOCC+IkhPBZz5WnX6IS0ckXR/OEV9QyOPXZ85hNjDUKdclOH9xN6ZnSolDqExDzmphu/SizzRry0TxXs602blyag8Mqk0QN3QnmA4A7ALF2EQRu156rzd7VhW7XnoTg5ddaH19cEGVWVU8drBYdX0wqETtLTU7p1iyuBuT2zcmvlctK/nH8WusfjnjhEcM+OLWHQjeBGtdCJw6F5v/TWDTnE6TDrj3ycPzBqyXZhX3Pnm48nhsooj1I/si111Nm46odUk9P5ecNqAu7cnjG7evjW26p/l97RxaPW8t27gyMWdK9WBQbYJwjtPEtlfZNEbVP57UMZY2SCZ1bJnUOjTr7dMljO49mjio3581ZpNjtS2Lbc407qZFnY3N/yYJN6cbOQMraRxq2hlgtU5uqGfoUXF6Bv/bIhcalTr5xNd/gFfeOlV5zocuWhJbluD2LrVs7MglASmINdUWSOp9r5cpf+sfT/v+fk07OMTovHzyn07STcI0ZMk3l/gOZcFUQDigAsArb51Cl6GJENze5cCWDYljjdeP7MPduya5bQsZsabaAvX2KvcW8pFz5P0glbTwSK3vf+bce2EuvJVMeHD/DVf24fTZc8bX8oNZcCGWevg10XBA9c1q+cZR61Rim23Hi9MznMJKDKqtYjMDK7h+65JFOeRzXXhnpoTenvk1vC4pj6sE4pfIiwpi//eddzH++ol55QkGSgnMOPLNlGYrnWPh5nDcMKbwmNqhgX5jSsSWTerkvk+vrulGNjZRTNz9wcc0ADGoZlB4TVEA3gLK5aAV1VEV7Ck31WQL+a7I2lZ4BX2/DFUr/RviydunS9jxxGHrDinTWFab/cBMBMDKZQVcsfWp2OfVuoB1mhEM3BmAmFPNoNG9R+etKZqkNKeVnJ5pJNKZc3OxQevBQO0yTc+9abm+MH9tgqiAEzULzPY1P3jREhx49URs4PvQRUusXi+slhEMXBugs7GmmkG1fij966YNQ66S4nTwdL05zihdIrEzyMI1yaSUgF/r9WdYmXzooiV45j9eb1XGNOutmpYN5DjXzsaaagbV+qFMWhAlzTz4NIvpL8rZPXlWNdWCMnGdSDmRSq03qWl+7QeWWZUvanyv6SfLieBz61ZwbQCah0E1g4Y3rULeNAYoxg1X9lWuj/qwb7720tiZXUsWvXcuzSQoU6oiJwJBdDC3GYI0NNCP9VdcGHkuODEg6WZhu/yfaXPD8KsX8jncf9sa7Bxajfs+vRpLAx2Hi7v5kep0/AvIoKGBfozeuqZqHOeSRTn0FvLGIAUA+48cr1wftUqVHwQKhjGmt/xmbZ0rpgDsb3ltqknapBge/De/hTvXraj8zDkR3LluRdVC1Ukzr0zvH54VFbc+g2nFLwB4t/TeULPpmVJdyzrSwsecakbF9VRfvmVP5HGbhUD8lf2jAogflIHy/PukzQV9pnGxfhqi3g37klb798+ZhnFFvU/UrChTjjRqG27/NaKGWnEEQGdjTXUBqnUuvs9m7v/2m65CPiJXmgulJUxphWBusRFbS4ftHFqNO9etiDy37gNL583TT9PUj8qRJg214giAzpUYVEXkPBF5UUSmROSwiNzrHX9ORCa9r2MiMhZx7WUictB7zmER+VIjfohOM7xp1byAl8/Zb51sE5SHBvox+tk1Vc3eb9y+FvffusaYVjA1kU3Do2yHTdnyA2swVbD+igvx4zfesd51IKmp70saasURAJ3Lpvl/BsAGVT0pInkAPxSRp1X1Ov8JIvIYgO9EXPsLAP9MVc+IyPsA/EREnlDVY05K38FmQ51D4cdxbBdIiUshRB0zNXdvuLIvsmnud6y5FE4VrB/Zl2qcqampD1QPt4r7bXMEQGdLrKlq2UnvYd77qvxNicj5ADYAmFdTVdWzqnrGe7jY5v0o2b1PHp435nROUbWeapyk7VZcC+ZqbY67lKYZHhcMw8OtTHIiDf1dUvZZdVSJSA7AQQAfBPDfVfWFwOlbADyrqr8yXHspgD3etcOspdYvaT1VG/WuaB+1gMr+I8er5tUDiF0spRl5x6QB/EA5j5q0FoDtzKp1H1jKgNrhrIKqqs4CWCsivQC+LSJXq+pPvNObAXwz5to3AXxURJYDGBORR1X1H4LPEZG7ANwFACtWRHc2kDvBgNjbk4dqedvretYTDTbvi9MzGH5kChDM26EgKGphGNeS1hSI69m3nVkVdODVE9g2dih2tAK1t1TNcVWdBvADAJ8EABFZBuAalGuiSdceA3AYwHUR5x5Q1UFVHezrc59nazemgUg2A5TCzdi3T5cwPVOqdOIMP5q8DbRNra00p7EBFUg3waBWUWvB+pJ69m1mVkWxnWxA7cmm97/Pq6FCRAoAPg7giHf6VgC7VfVdw7WXeNdARJYCWA+AK/nWyRSLbGJUUkAM73UVxVWz/R3LhVjqNTTQj8ntG/GN29fW3LMfNdzKxOVQMVp4bJr/FwP4lpdX7QLwsKru9s7dAWAk+GQRGQTwJVX9IoAPA7hfRPy/ya+p6iFnpe9Q/YbmqM0QJZuAmJSbvcCwtGBazWj+B9nmkU2/I3+4lZ8SMI0CSLPGArWfxKCqqi8DGDCcuz7i2DiAL3rfPwPgo/UVkcJq3TMKsOu4SWITM/JdkphTdVGhC+c+0+ygYGL6HYXzr9vGDkUOFUuaNkvtjUOcFqB6hkTZbJedtHeUaWlBAJXyjN66pjJ5wKTe5n/SrrG1vuapM/O3gYm6aUVNNgivS0Cdh3P/F6hah0T51wS3agnqwnvbspjY1uT892vU7rFJu8amFbXjAgD05LvwXww3raR1CajzsKbagYIdN8Fl63oLeXz99rWJASntbqyN2j3WZg0DW2MTRdzz8GTkMoanS7b7uhKxptrR6q3tpsllnpfvqtQqewt57Lj5qoblPnt78lg/sg/HpmdwQSEPkXLnm79aVnifLD+NEJfj5apTZItBlWoSFZCjOo0AYPjRqaoOq1MxW1enEdVhl88JTr57rjKCIZji8Ic6+blX/+ewGXfL7afJFpv/5ERUp9E9uyYx/MjkvBEANmNhbUR12C1Z1G21aWJw5wHbdAEXnyYbrKmSE6YB86Z0ZNJYWNuhUuEas2kB7yh+MLUdZjZTmsWXH57CPbsmnQ3fovbDmio54XJxlHqGSqUZUeA/12aYmS/t5oXUeRhUyYm0w6NM+2QB8UOlTPz9pmzn6QdHH0SlEZZazPay2byQOg+b/+TE8KZVuGfXpNX6AwBwdlbxkT94ujJcKTgiIO1QqfCqWf6caPVeN6n3H5ifRgi/pgm3TaEwBtUFqhHTM+sxNNCP8ddP4MHn37AKrLNzitOBDqXpmVJ5uUCYc5ym2rApnxu3in+S8LCxroTNDYl8DKoLUNR6psEhQq0q0/4jx61rqlFKc4rRvUdTr21gqi0GA/O2sUN46IU3MauKnAg2X3tp4kyoYO01quYaLpN/oytOz1RqxXG1Y2pPDKoLkOvpmfUyLSxSi2PTM6knF8T13o9NFDH++omq8s2qVh7bTjFNKlM46Pq1WtPYWGpfohlb+3FwcFDHx8dbXYxMu3zLnsgaoQB4beTGppZlbKKIu3dNRp4TL7F5QSGPU2fPJS5aDQBLe/LoWdSdKq0RV4beQh6/fvdcZNM9J4JX7/vXVa9Ta03TtL5BWD0pCWodETmoqoM2z2Xv/wJks8V0s8T1fquWg/zk9o1V210v7cmjK6KLvkuAd06XqoZSDT+SvBNBXNCdnikZF40OHg8O4wqeC9c0TWWx7bBix1b7Y1BdgBq1QElaYxNF67VZhwb6cWDLBrw2ciMm/nAjvn7b/MVcFnd3ITxXoDSn2PFEfbOvbLafsZmqGjeEyvaGxo6t9sec6gJUy4Imrvk1uzhxYz2j1g5YaZgNVe8uAz2Lcjh1dn7A7Fn03o2p3prm8KZVxhSErxU3Pmo+BtUFqt4tpuuVVLPrEmD7TfHrsrrUa9jipbeQNy6GfToQaG2nqppqmuOvn4i9zpSTzdrQOKofg2qHqvfDHFezq3Vpv6U9+cg1AWxmN31qzcWRIxA+teZi7J76RWTAvSCww0HSVtZA/O6rDxpGP/QW8pjcvjHyXHjUhL+bLcARAgsZc6odyMU2JKYaW39vAZPbN9YUFLbfdBXyueoMaD4nVjXe/UeOG4+b9tQKHg9OVQXmb96XE8FnPhbdOhjde9Q4PtdUSx6bKEbeBFyt4EWtw6DagWqZWx/WiM6yoYH+qlEC/b0FjH52TV07oB6bnjHuqRU+7nem/WzkRtx/25qqn29WFY8dLEbeeOLSBnGzwEySVvCibGPzvwO52IakUZ1lteaKk6a2pt0jK80Ei5xhCiuA1LPAaOFjUO1AaefWm7S6sywoaWpr2i2909x4TAEVMOdGXWwVTtnE5n8HSmq6bxs7hCu2PoWVW/bgiq1PYdtY/NCpLIjbtruWLb3TTLAwbcMdtz13XECPmhhBCwdrqh0oruke7pGuZZ58q8TVnNPWqtMs6pJ2ARjf4u4unDk3f2uE3712hXU5KXs495+qXLH1Kat58p0gzbCztM+NGr4lAD63bkXmb16dKM3cf9ZUqYrNPPmFptYxuWlqt2mea5o4sby3wIDaBhhUqYqpJzs8bnOhyOLasy5GX1B2saOKqmy+9tJUx7POxZhc17K0yhi5x6BKVXYOrcad61ZUaqY5Edy5gPN8WawVZmWVMWoMNv9pnp1DqxdsEA1zNSbXpSysMkaNw6BKba3W4U6NlqWJE+QWgyq1NdYKqdkYVKntsVZIzcSOKiIihxKDqoicJyIvisiUiBwWkXu948+JyKT3dUxExiKuXSsiP/Kue1lEbm/ED0FElBU2zf8zADao6kkRyQP4oYg8rarX+U8QkccAfCfi2tMAfk9VXxGR5QAOisheVZ12UnoiooxJDKpaXhzgpPcw731VptyIyPkANgD4QsS1fxv4/piIvAWgDwCDKhG1JaucqojkRGQSwFsAnlHVFwKnbwHwrKr+KuE1rgGwCMCrtRaWiCjrrIKqqs6q6loAlwC4RkSuDpzeDOChuOtF5GIA/xPAF1R13lpnInKXiIyLyPjx49F7DRERLQSpev+9XOgPAHwSAERkGYBrAERv2F5+zm9457ep6vOG131AVQdVdbCvry9NkYiIMsWm979PRHq97wsAPg7giHf6VgC7VfVdw7WLAHwbwP9Q1UfcFJmIKLtsaqoXA9gvIi8DeAnlnOpu79wdCDX9RWRQRL7pPbwNwL8A8PuB4VdrHZWdiChzuPI/EVGCNCv/c0YVEZFDDKpERA4xqBIROcSgSkTkEIMqEZFDDKpERA4xqBIROcSgSkTkEIMqEZFDDKpERA4xqBIROcSgSkTkEIMqEZFDDKpERA4xqBIROcSgSkTkEIMqEZFDDKpERA4xqBIROcSgSkTkEIMqEZFDDKpERA4xqBIROcSgSkTkEIMqEZFDDKpERA4xqBIROcSgSkTkEIMqEZFDDKpERA4xqBIROcSgSkTkEIMqEZFDDKpERA4xqBIROZQYVEXkPBF5UUSmROSwiNzrHX9ORCa9r2MiMma4/rsiMi0iu10Xnogoa7otnnMGwAZVPSkieQA/FJGnVfU6/wki8hiA7xiuHwXQA+Df1l1aIqKMS6ypatlJ72He+1L/vIicD2ADgMiaqqo+C+DX9ReViCj7rHKqIpITkUkAbwF4RlVfCJy+BcCzqvqrRhSQiGghsQqqqjqrqmsBXALgGhG5OnB6M4CH6imEiNwlIuMiMn78+PF6XoqIqKVS9f6r6jSAHwD4JACIyDIA1wDYU08hVPUBVR1U1cG+vr56XoqIqKVsev/7RKTX+74A4OMAjninbwWwW1XfbVwRiYgWDpua6sUA9ovIywBeQjmn6g+PugOhpr+IDIrINwOPnwPwCIB/KSI/F5FNbopORJQ9iUOqVPVlAAOGc9dHHBsH8MXA4+vCzyEialecUUVE5BCDKhGRQwyqREQOMagSETnEoEpE5BCDKhGRQwyqREQOMagSETnEoEpE5BCDKhGRQwyqREQOMagSETnEoEpE5BCDKhGRQwyqREQOMagSETnEoEpE5BCDKhGRQwyqREQOMagSETnEoEpE5BCDKhGRQwyqREQOMagSETnEoEpE5BCDKhGRQwyqREQOdbe6AEREjTA2UcTo3qM4Nj2D5b0FDG9ahaGB/oa/L4MqEbWdsYkitj5+CDOlWQBAcXoGWx8/BAAND6xs/hNR2xnde7QSUH0zpVmM7j3a8PdmUCWitnNseibVcZcYVImo7SzvLaQ67hKDKhE5bciLAAAGKUlEQVS1neFNq1DI56qOFfI5DG9a1fD3ZkcVEbUdvzOKvf9ERI4MDfQ3JYiGJTb/ReQ8EXlRRKZE5LCI3Osdf05EJr2vYyIyZrj+8yLyivf1edc/ABFRltjUVM8A2KCqJ0UkD+CHIvK0ql7nP0FEHgPwnfCFInIhgO0ABgEogIMi8oSqvu2m+ERE2ZJYU9Wyk97DvPel/nkROR/ABgBRNdVNAJ5R1RNeIH0GwCfrLjURUUZZ9f6LSE5EJgG8hXKQfCFw+hYAz6rqryIu7QfwZuDxz71j4de/S0TGRWT8+PHj9qUnIsoYq6CqqrOquhbAJQCuEZGrA6c3A3jIcKlEvVzE6z+gqoOqOtjX12dTJCKiTEo1TlVVpwH8AF4TXkSWAbgGwB7DJT8HcGng8SUAjqUuJRHRAmHT+98nIr3e9wUAHwdwxDt9K4Ddqvqu4fK9ADaKyFIRWQpgo3eMiKgt2dRULwawX0ReBvASyjnV3d65OxBq+ovIoIh8EwBU9QSAP/KuewnAV71jRERtSVTnpThbanBwUMfHx1tdDCKiChE5qKqDNs/l3H8iIocYVImIHMpc819EjgN43Xv4fgC/bGFx0lhIZQVY3kZjeRur2eW9TFWtxntmLqgGici4bR6j1RZSWQGWt9FY3sbKcnnZ/CcicohBlYjIoawH1QdaXYAUFlJZAZa30VjexspseTOdUyUiWmiyXlMlIlpQmh5UReRWbweBOREZDBz/hIgcFJFD3r8bvOM9IrJHRI54140YXjfy+gyXd5mI7BeRkyLypy7K2sjyes/dKiJ/JyJHRWRTK8rrnfvPIvKmiJyMflVARBaJyF9410+JyPUZL29eRL7lXf9TEdma8fJ+Tt7b+WPSe/21WS2v97yPisiPvNc/JCLn1VveSKra1C8AHwawCuXVrgYDxwcALPe+vxpA0fu+B8AN3veLADwH4F9FvG7k9Rku7xIA/xzAlwD86QL4/X4EwBSAxQAuB/AqgFyzy+s9XofymhQnY1733wP4C+/7iwAcBNCV4fL+LoC/DPyf/AzAyqyWN/QeqwH8fSv+flP8frsBvAxgjfd4mYu/36ivpm/8p6o/BQARCR+fCDw8DOA8EVmsqqcB7Peec1ZEfozyEoK215/JaHlPobw1zQfrKV+zygvgd1D+0J8B8JqI/B3Kyz7+qMnlPaOqz0ddE/IRAM96r/WWiEyjvK3PixktrwJYIiLdAAoAzgKIWvg9K+UNiltTOZUGlncjgJdVdcp7vf/norxRsppT/QyAiXBAlPIShDfB+7Ckvb6B6i1vs9VSXqtdHBqklv/PKQC/IyLdInI5gI+hem3fRqqlvI8COAXgFwDeAPA1bd6KbvV+Xm6Ho6BqqZby/hMAKiJ7ReTHIvKfGlS2xtRUReT7AP5xxKmvqOq8DQJD114F4I9RvrMEj3ej/B/331T179Nen9Xy1qJF5bXaxaFZ5bXw5yg3JcdRnvb81wDO2VzYovJeA2AWwHIASwE8JyLft/nbaVF5/euvBXBaVX+S4ppWlLcb5XTbPwVwGsCzUl55ynmFpyFBVVU/Xst1InIJgG8D+D1VfTV0+gEAr6jqN2q8PnPlrVWLylvzLg4NKm/Se54DcE/gtf4awCuW1za9vCjnVL+rqiUAb4nIAZTTFYlBtUXl9c1bUzlJi8r7cwB/paq/9F7rKQC/iQa0IjPT/PeannsAbFXVA6FzOwFcAODuWq5vhHrL22wOyvsEgDtEZLHXnP4Q6sxP1lpey+t7RGSJ9/0nAJxT1b9xXMzg+9X79/cGgA1StgTlzpcjCdfUzMXnRUS6UN794y9dls3wXvWWdy+Aj3p/F90AfhtAY/4eGtH7FfeF8u6rPwdwBsA/ANjrHd+Gck5pMvB1Eco1IgXw08DxL3rX3IzybgLG67NaXu/xzwCcAHDSe4+PZLy8X0G51/8oIkYINKO83rk/8a6Z8/7dEfH3sNIr508BfB/lVYayXN73AXgE5U6YvwEwnOXyeo+vB/B8K+NDyvLe6f1+fwLgT1yWO/jFGVVERA5lpvlPRNQOGFSJiBxiUCUicohBlYjIIQZVIiKHGFSJiBxiUCUicohBlYjIof8PJVRWpuX85s4AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x10679ca58>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "CMP_Int.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "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>STATUS</th>\n",
       "      <th>SCORE</th>\n",
       "      <th>SIDE</th>\n",
       "      <th>ARC_STREET</th>\n",
       "      <th>ID</th>\n",
       "      <th>CMP_SYSTEM</th>\n",
       "      <th>CROSS_STRE</th>\n",
       "      <th>INTERSECTI</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>...</th>\n",
       "      <th>YR_2010</th>\n",
       "      <th>LOS_RATING</th>\n",
       "      <th>LOS_2008</th>\n",
       "      <th>LOS_2006</th>\n",
       "      <th>LOS_2004</th>\n",
       "      <th>YR_2012</th>\n",
       "      <th>ID_1</th>\n",
       "      <th>YR_2014</th>\n",
       "      <th>YR_2016</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>54</td>\n",
       "      <td>T</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "      <td>Lawrence Expy &amp; State Hwy 82</td>\n",
       "      <td>1214.0</td>\n",
       "      <td>Lawrence Exp.</td>\n",
       "      <td>El Camino Real (Rte 82)</td>\n",
       "      <td>Lawrence Expy &amp; State Hwy 82</td>\n",
       "      <td>Santa Clara</td>\n",
       "      <td>...</td>\n",
       "      <td>C</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>C</td>\n",
       "      <td>1214.0</td>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "      <td>POINT (-121.9965930082738 37.35223899061431)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>55</td>\n",
       "      <td>T</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "      <td>Saratoga Sunnyvale Rd &amp; State Hwy 9</td>\n",
       "      <td>1301.0</td>\n",
       "      <td>Big Basin Way (Hwy 9)</td>\n",
       "      <td>Saratoga-Los Gatos Rd.</td>\n",
       "      <td>Saratoga Sunnyvale Rd &amp; State Hwy 9</td>\n",
       "      <td>Saratoga</td>\n",
       "      <td>...</td>\n",
       "      <td>D+</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "      <td>D+</td>\n",
       "      <td>1301.0</td>\n",
       "      <td>D+</td>\n",
       "      <td>D+</td>\n",
       "      <td>POINT (-122.0319839991837 37.25921699567839)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>56</td>\n",
       "      <td>T</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "      <td>Sunnyvale Saratoga Rd &amp; W Fremont Ave</td>\n",
       "      <td>1401.0</td>\n",
       "      <td>Saratoga-Sunnyvale Rd.</td>\n",
       "      <td>Fremont Av.</td>\n",
       "      <td>Sunnyvale Saratoga Rd &amp; W Fremont Ave</td>\n",
       "      <td>Sunnyvale</td>\n",
       "      <td>...</td>\n",
       "      <td>D</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>D</td>\n",
       "      <td>1401.0</td>\n",
       "      <td>D</td>\n",
       "      <td>D</td>\n",
       "      <td>POINT (-122.0324419959961 37.35204999538769)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>57</td>\n",
       "      <td>M</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "      <td>Sunnyvale Saratoga Rd &amp; E Remington Dr</td>\n",
       "      <td>1402.0</td>\n",
       "      <td>Saratoga-Sunnyvale Rd.</td>\n",
       "      <td>Remington Dr.</td>\n",
       "      <td>Sunnyvale Saratoga Rd &amp; E Remington Dr</td>\n",
       "      <td>Sunnyvale</td>\n",
       "      <td>...</td>\n",
       "      <td>C-</td>\n",
       "      <td>7</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>9</td>\n",
       "      <td>D</td>\n",
       "      <td>1402.0</td>\n",
       "      <td>D</td>\n",
       "      <td>D</td>\n",
       "      <td>POINT (-122.0321700036354 37.35902599706698)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>58</td>\n",
       "      <td>M</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "      <td>E Remington Dr &amp; State Hwy 82</td>\n",
       "      <td>1404.0</td>\n",
       "      <td>El Camino Real (Rte. 82)</td>\n",
       "      <td>Fair Oaks Av.</td>\n",
       "      <td>E Remington Dr &amp; State Hwy 82</td>\n",
       "      <td>Sunnyvale</td>\n",
       "      <td>...</td>\n",
       "      <td>D</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>D</td>\n",
       "      <td>1404.0</td>\n",
       "      <td>D</td>\n",
       "      <td>D</td>\n",
       "      <td>POINT (-122.0250620091708 37.36169999372341)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   OBJECTID STATUS  SCORE  SIDE                              ARC_STREET  \\\n",
       "0        54      T    100  None            Lawrence Expy & State Hwy 82   \n",
       "1        55      T    100  None     Saratoga Sunnyvale Rd & State Hwy 9   \n",
       "2        56      T    100  None   Sunnyvale Saratoga Rd & W Fremont Ave   \n",
       "3        57      M    100  None  Sunnyvale Saratoga Rd & E Remington Dr   \n",
       "4        58      M    100  None           E Remington Dr & State Hwy 82   \n",
       "\n",
       "       ID                CMP_SYSTEM               CROSS_STRE  \\\n",
       "0  1214.0             Lawrence Exp.  El Camino Real (Rte 82)   \n",
       "1  1301.0     Big Basin Way (Hwy 9)   Saratoga-Los Gatos Rd.   \n",
       "2  1401.0    Saratoga-Sunnyvale Rd.              Fremont Av.   \n",
       "3  1402.0    Saratoga-Sunnyvale Rd.            Remington Dr.   \n",
       "4  1404.0  El Camino Real (Rte. 82)            Fair Oaks Av.   \n",
       "\n",
       "                               INTERSECTI     LOCATION  \\\n",
       "0            Lawrence Expy & State Hwy 82  Santa Clara   \n",
       "1     Saratoga Sunnyvale Rd & State Hwy 9     Saratoga   \n",
       "2   Sunnyvale Saratoga Rd & W Fremont Ave    Sunnyvale   \n",
       "3  Sunnyvale Saratoga Rd & E Remington Dr    Sunnyvale   \n",
       "4           E Remington Dr & State Hwy 82    Sunnyvale   \n",
       "\n",
       "                       ...                      YR_2010 LOS_RATING LOS_2008  \\\n",
       "0                      ...                            C          6        6   \n",
       "1                      ...                           D+          8        8   \n",
       "2                      ...                            D          9        9   \n",
       "3                      ...                           C-          7        9   \n",
       "4                      ...                            D          9        8   \n",
       "\n",
       "  LOS_2006 LOS_2004 YR_2012    ID_1 YR_2014 YR_2016  \\\n",
       "0        6        7       C  1214.0       C       C   \n",
       "1        9        7      D+  1301.0      D+      D+   \n",
       "2        0        9       D  1401.0       D       D   \n",
       "3        0        9       D  1402.0       D       D   \n",
       "4        9        9       D  1404.0       D       D   \n",
       "\n",
       "                                       geometry  \n",
       "0  POINT (-121.9965930082738 37.35223899061431)  \n",
       "1  POINT (-122.0319839991837 37.25921699567839)  \n",
       "2  POINT (-122.0324419959961 37.35204999538769)  \n",
       "3  POINT (-122.0321700036354 37.35902599706698)  \n",
       "4  POINT (-122.0250620091708 37.36169999372341)  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CMP_Int.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "tree = ET.parse('data/2016CMP80_PM.xml')\n",
    "# tree = ET.parse('data/CMP_80_traffix_write_file_single_xml.in.xml')\n",
    "root = tree.getroot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_intersection(elements):\n",
    "    return dumps(bf.data(elements))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def node_parser(node):\n",
    "    node_dict = {}\n",
    "    node_dict['ID'] = node.attrib.get('index')\n",
    "    node_dict['name'] = node.find('name').text\n",
    "    node_dict['volcount_date_year'] = node.find('volumedata').find('volcountdate').get('year')\n",
    "    node_dict['volcount_date_month'] = node.find('volumedata').find('volcountdate').get('month')\n",
    "    node_dict['volcount_date_day'] = node.find('volumedata').find('volcountdate').get('day')\n",
    "    node_dict['voltimeperiod'] = node.find('volumedata').find('voltimeperiod').text\n",
    "    node_dict['los'] = node.find('resultdata').find('los').text\n",
    "    node_dict['averagedelay'] = node.find('resultdata').find('averagedelay').get('val')\n",
    "    node_dict['averagecriticaldelay'] = node.find('resultdata').find('averagecriticaldelay').get('val')\n",
    "    node_dict['optimalcycle'] = node.find('resultdata').find('optimalcycle').get('val')\n",
    "    node_dict['criticalvoltocap'] = node.find('resultdata').find('criticalvoltocap').get('val')\n",
    "    node_dict['criticalvoltocap'] = node.find('resultdata').find('criticalvoltocap').get('val')\n",
    "\n",
    "    \n",
    "    node_dict['nscontrol'] = node.find('geometrydata').find('control').get('nscontrol')\n",
    "    node_dict['ewcontrol'] = node.find('geometrydata').find('control').get('ewcontrol')\n",
    "    node_dict['cycletime'] = node.find('geometrydata').find('control').get('cycletime')\n",
    "    node_dict['losstime'] = node.find('geometrydata').find('control').get('losstime')\n",
    "    node_dict['y_r'] = process_intersection(node.find('geometrydata').find('yellowplusallred'))\n",
    "    node_dict['lanes'] = process_intersection(node.find('geometrydata').find('lanes'))\n",
    "    node_dict['min_green'] = process_intersection(node.find('geometrydata').find('mingreen'))\n",
    "    node_dict['rights_nb'] = node.find('geometrydata').find('rtor').get('nb')\n",
    "    node_dict['rights_sb'] = node.find('geometrydata').find('rtor').get('sb')\n",
    "    node_dict['rights_eb'] = node.find('geometrydata').find('rtor').get('eb')\n",
    "    node_dict['rights_wb'] = node.find('geometrydata').find('rtor').get('wb')\n",
    "    \n",
    "    node_dict['base_vol'] = process_intersection(node.find('resultdata').find('volumemodule').find('basevolume'))\n",
    "    node_dict['user_adj_vol'] = process_intersection(node.find('resultdata').find('volumemodule').find('useradj'))\n",
    "    node_dict['final_vol'] = process_intersection(node.find('resultdata').find('volumemodule').find('finalvolume'))\n",
    "    node_dict['bike_vol'] = process_intersection(node.find('volumedata').find('bikevol'))\n",
    "    node_dict['ped_vol'] = process_intersection(node.find('volumedata').find('pedvol'))\n",
    "    \n",
    "    node_dict['final_sat'] = process_intersection(node.find('resultdata').find('satmodule').find('finalsat'))\n",
    "\n",
    "    node_dict['cap_vol_sat'] = process_intersection(node.find('resultdata').find('capacitymodule').find('voltosat'))\n",
    "    node_dict['cap_vol_cap'] = process_intersection(node.find('resultdata').find('capacitymodule').find('voltocap'))\n",
    "    node_dict['los_by_move'] = process_intersection(node.find('resultdata').find('losmodule').find('approachlosbymove'))\n",
    "    node_dict['cap_delay_veh'] = process_intersection(node.find('resultdata').find('losmodule').find('delaycalc').find('finaldelay'))\n",
    "    \n",
    "    #     node.find('geometrydata').find('yellowplusallred').find('nb').items()\n",
    "    return node_dict\n",
    "#     print(node.find('volumedata').find('voltimeperiod').text)\n",
    "# node_parser(node)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>averagecriticaldelay</th>\n",
       "      <th>averagedelay</th>\n",
       "      <th>base_vol</th>\n",
       "      <th>bike_vol</th>\n",
       "      <th>cap_delay_veh</th>\n",
       "      <th>cap_vol_cap</th>\n",
       "      <th>cap_vol_sat</th>\n",
       "      <th>criticalvoltocap</th>\n",
       "      <th>cycletime</th>\n",
       "      <th>...</th>\n",
       "      <th>rights_eb</th>\n",
       "      <th>rights_nb</th>\n",
       "      <th>rights_sb</th>\n",
       "      <th>rights_wb</th>\n",
       "      <th>user_adj_vol</th>\n",
       "      <th>volcount_date_day</th>\n",
       "      <th>volcount_date_month</th>\n",
       "      <th>volcount_date_year</th>\n",
       "      <th>voltimeperiod</th>\n",
       "      <th>y_r</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>28.51241</td>\n",
       "      <td>30.69518</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 190.0, \"@thru\"...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 54.7613, \"@thr...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.59839, \"@thru\"...</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.06032, \"@thru\"...</td>\n",
       "      <td>0.57155</td>\n",
       "      <td>120</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>5:00 - 6:00 pm</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>56.93715</td>\n",
       "      <td>47.77116</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 140.0, \"@thru\"...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 67.29492, \"@th...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.53322, \"@thru\"...</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.08, \"@thru\": 0...</td>\n",
       "      <td>0.60126</td>\n",
       "      <td>166</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>4:30 PM to 5:30 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>30.43571</td>\n",
       "      <td>25.11601</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 791.0, \"@thru\"...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 48.07382, \"@th...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.55739, \"@thru\"...</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.17381, \"@thru\"...</td>\n",
       "      <td>0.66155</td>\n",
       "      <td>166</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>4:45 - 5:45 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>49.06350</td>\n",
       "      <td>52.22595</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 70.0, \"@thru\":...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 138.48647, \"@t...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.87234, \"@thru\"...</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.04, \"@thru\": 0...</td>\n",
       "      <td>0.87234</td>\n",
       "      <td>166</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Free</td>\n",
       "      <td>No</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>13</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>4:45 - 5:45 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>52.74055</td>\n",
       "      <td>51.41155</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 444.0, \"@thru\"...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 64.13012, \"@th...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.6868, \"@thru\":...</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.14095, \"@thru\"...</td>\n",
       "      <td>0.71093</td>\n",
       "      <td>166</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>5:15 - 6:15 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID averagecriticaldelay averagedelay  \\\n",
       "0  101             28.51241     30.69518   \n",
       "1  102             56.93715     47.77116   \n",
       "2  103             30.43571     25.11601   \n",
       "3  104             49.06350     52.22595   \n",
       "4  105             52.74055     51.41155   \n",
       "\n",
       "                                            base_vol  \\\n",
       "0  {\"basevolume\": {\"nb\": {\"@left\": 190.0, \"@thru\"...   \n",
       "1  {\"basevolume\": {\"nb\": {\"@left\": 140.0, \"@thru\"...   \n",
       "2  {\"basevolume\": {\"nb\": {\"@left\": 791.0, \"@thru\"...   \n",
       "3  {\"basevolume\": {\"nb\": {\"@left\": 70.0, \"@thru\":...   \n",
       "4  {\"basevolume\": {\"nb\": {\"@left\": 444.0, \"@thru\"...   \n",
       "\n",
       "                                            bike_vol  \\\n",
       "0  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "1  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "2  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "3  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "4  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "\n",
       "                                       cap_delay_veh  \\\n",
       "0  {\"finaldelay\": {\"nb\": {\"@left\": 54.7613, \"@thr...   \n",
       "1  {\"finaldelay\": {\"nb\": {\"@left\": 67.29492, \"@th...   \n",
       "2  {\"finaldelay\": {\"nb\": {\"@left\": 48.07382, \"@th...   \n",
       "3  {\"finaldelay\": {\"nb\": {\"@left\": 138.48647, \"@t...   \n",
       "4  {\"finaldelay\": {\"nb\": {\"@left\": 64.13012, \"@th...   \n",
       "\n",
       "                                         cap_vol_cap  \\\n",
       "0  {\"voltocap\": {\"nb\": {\"@left\": 0.59839, \"@thru\"...   \n",
       "1  {\"voltocap\": {\"nb\": {\"@left\": 0.53322, \"@thru\"...   \n",
       "2  {\"voltocap\": {\"nb\": {\"@left\": 0.55739, \"@thru\"...   \n",
       "3  {\"voltocap\": {\"nb\": {\"@left\": 0.87234, \"@thru\"...   \n",
       "4  {\"voltocap\": {\"nb\": {\"@left\": 0.6868, \"@thru\":...   \n",
       "\n",
       "                                         cap_vol_sat criticalvoltocap  \\\n",
       "0  {\"voltosat\": {\"nb\": {\"@left\": 0.06032, \"@thru\"...          0.57155   \n",
       "1  {\"voltosat\": {\"nb\": {\"@left\": 0.08, \"@thru\": 0...          0.60126   \n",
       "2  {\"voltosat\": {\"nb\": {\"@left\": 0.17381, \"@thru\"...          0.66155   \n",
       "3  {\"voltosat\": {\"nb\": {\"@left\": 0.04, \"@thru\": 0...          0.87234   \n",
       "4  {\"voltosat\": {\"nb\": {\"@left\": 0.14095, \"@thru\"...          0.71093   \n",
       "\n",
       "  cycletime                        ...                         rights_eb  \\\n",
       "0       120                        ...                               Ovl   \n",
       "1       166                        ...                               Ovl   \n",
       "2       166                        ...                               Ovl   \n",
       "3       166                        ...                                No   \n",
       "4       166                        ...                               Ovl   \n",
       "\n",
       "  rights_nb rights_sb rights_wb  \\\n",
       "0        No        No       Ovl   \n",
       "1       Ovl       Ovl       Ovl   \n",
       "2       Ovl        No        No   \n",
       "3       Ovl      Free        No   \n",
       "4       Ovl       Ovl       Ovl   \n",
       "\n",
       "                                        user_adj_vol volcount_date_day  \\\n",
       "0  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                 6   \n",
       "1  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                 1   \n",
       "2  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                 1   \n",
       "3  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                13   \n",
       "4  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                 2   \n",
       "\n",
       "  volcount_date_month volcount_date_year       voltimeperiod  \\\n",
       "0                  12               2016      5:00 - 6:00 pm   \n",
       "1                  12               2016  4:30 PM to 5:30 PM   \n",
       "2                  12               2016      4:45 - 5:45 PM   \n",
       "3                  12               2016      4:45 - 5:45 PM   \n",
       "4                   1               2017      5:15 - 6:15 PM   \n",
       "\n",
       "                                                 y_r  \n",
       "0  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "1  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "2  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "3  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "4  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node_list = []\n",
    "nodes = root.findall(\"node\")\n",
    "for node in nodes:\n",
    "    node_list.append(node_parser(node))\n",
    "#     break\n",
    "df = pd.DataFrame(node_list)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>averagecriticaldelay</th>\n",
       "      <th>averagedelay</th>\n",
       "      <th>base_vol</th>\n",
       "      <th>bike_vol</th>\n",
       "      <th>cap_delay_veh</th>\n",
       "      <th>cap_vol_cap</th>\n",
       "      <th>cap_vol_sat</th>\n",
       "      <th>criticalvoltocap</th>\n",
       "      <th>cycletime</th>\n",
       "      <th>...</th>\n",
       "      <th>rights_eb</th>\n",
       "      <th>rights_nb</th>\n",
       "      <th>rights_sb</th>\n",
       "      <th>rights_wb</th>\n",
       "      <th>user_adj_vol</th>\n",
       "      <th>volcount_date_day</th>\n",
       "      <th>volcount_date_month</th>\n",
       "      <th>volcount_date_year</th>\n",
       "      <th>voltimeperiod</th>\n",
       "      <th>y_r</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>5214</td>\n",
       "      <td>24.66300</td>\n",
       "      <td>49.40820</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 0.0, \"@thru\": ...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 0.0, \"@thru\": ...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0....</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0....</td>\n",
       "      <td>1.15307</td>\n",
       "      <td>104</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>No</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>4:30-5:30 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       ID averagecriticaldelay averagedelay  \\\n",
       "187  5214             24.66300     49.40820   \n",
       "\n",
       "                                              base_vol  \\\n",
       "187  {\"basevolume\": {\"nb\": {\"@left\": 0.0, \"@thru\": ...   \n",
       "\n",
       "                                              bike_vol  \\\n",
       "187  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "\n",
       "                                         cap_delay_veh  \\\n",
       "187  {\"finaldelay\": {\"nb\": {\"@left\": 0.0, \"@thru\": ...   \n",
       "\n",
       "                                           cap_vol_cap  \\\n",
       "187  {\"voltocap\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0....   \n",
       "\n",
       "                                           cap_vol_sat criticalvoltocap  \\\n",
       "187  {\"voltosat\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0....          1.15307   \n",
       "\n",
       "    cycletime                        ...                         rights_eb  \\\n",
       "187       104                        ...                                No   \n",
       "\n",
       "    rights_nb rights_sb rights_wb  \\\n",
       "187       Ovl        No       Ovl   \n",
       "\n",
       "                                          user_adj_vol volcount_date_day  \\\n",
       "187  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                31   \n",
       "\n",
       "    volcount_date_month volcount_date_year voltimeperiod  \\\n",
       "187                   1               2017  4:30-5:30 PM   \n",
       "\n",
       "                                                   y_r  \n",
       "187  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "\n",
       "[1 rows x 32 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.query(\"ID=='5214'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['{\"voltocap\": {\"nb\": {\"@left\": 0.20305, \"@thru\": 0.35528, \"@rite\": 0.34078}, \"eb\": {\"@left\": 0.49352, \"@thru\": 0.49352, \"@rite\": 0.22648}, \"sb\": {\"@left\": 0.75362, \"@thru\": 0.61875, \"@rite\": 0.01712}, \"wb\": {\"@left\": 0.56015, \"@thru\": 0.56015, \"@rite\": 0.16634}}}']\n"
     ]
    }
   ],
   "source": [
    "t = df['cap_vol_cap'][191:192].values\n",
    "print(t)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# [print(x.tag,x.attrib) for x in list(node.find('resultdata').find('capacitymodule').find('voltosat'))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# [str(x.tag) + ' : ' + str(x.attrib) for x in list(node.find('resultdata').find('capacitymodule').find('voltosat'))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "tree = ET.parse('data/CMP_80_traffix_write_file_single_xml.in.xml')\n",
    "root = tree.getroot()\n",
    "node_list = []\n",
    "nodes = root.findall(\"node\")\n",
    "# for node in nodes:\n",
    "#     print(node)\n",
    "#     node_list.append(node_parser(node))\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#103 - <links ne=\"11708\" e=\"105\" se=\"11709\" w=\"11704\"/>\n",
    "#104  <links nw=\"11708\" e=\"11704\" sw=\"11709\" w=\"102\"/>\n",
    "def full_node_parser(node):\n",
    "    node_dict = {}\n",
    "    node_dict['ID'] = node.attrib.get('index')\n",
    "    node_dict['name'] = node.find('name').text\n",
    "    node_dict['xpos'] = node.find('position').get('xpos')\n",
    "    node_dict['ypos'] = node.find('position').get('ypos')\n",
    "#     node_dict['base_volume_data'] = [x.items() for x in list(node.find('volumedata').find('volume'))]\n",
    "    if node.find('links') is not None:\n",
    "        links = []\n",
    "        for each in node.find('links').keys():\n",
    "            links.append(node.find('links').get(each))\n",
    "        node_dict['links'] = ', '.join(links)\n",
    "\n",
    "#     node_dict['nlink'] = node.find('links').get('n')\n",
    "#     node_dict['slink'] = node.find('links').get('s') \n",
    "#     node_dict['wlink'] = node.find('links').get('w')\n",
    "    return node_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "node_list = []\n",
    "for node in nodes:\n",
    "    node_list.append(full_node_parser(node))\n",
    "#     break\n",
    "\n",
    "link_df = pd.DataFrame(node_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>links</th>\n",
       "      <th>name</th>\n",
       "      <th>xpos</th>\n",
       "      <th>ypos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>105, 3071, 5430</td>\n",
       "      <td>Bascom/Campbell</td>\n",
       "      <td>2017</td>\n",
       "      <td>-1492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>7251, 104, 5429</td>\n",
       "      <td>Winchester/Hamilton</td>\n",
       "      <td>1877</td>\n",
       "      <td>-1422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>11708, 105, 11709, 11704</td>\n",
       "      <td>Hwy 17 NB/Hamilton</td>\n",
       "      <td>1975</td>\n",
       "      <td>-1424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>11708, 11704, 11709, 102</td>\n",
       "      <td>Hwy 17 SB/Hamilton</td>\n",
       "      <td>1937</td>\n",
       "      <td>-1424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>3070, 101, 103</td>\n",
       "      <td>Bascom/Hamilton</td>\n",
       "      <td>2017</td>\n",
       "      <td>-1424</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID                     links                 name  xpos   ypos\n",
       "0  101           105, 3071, 5430      Bascom/Campbell  2017  -1492\n",
       "1  102           7251, 104, 5429  Winchester/Hamilton  1877  -1422\n",
       "2  103  11708, 105, 11709, 11704   Hwy 17 NB/Hamilton  1975  -1424\n",
       "3  104  11708, 11704, 11709, 102   Hwy 17 SB/Hamilton  1937  -1424\n",
       "4  105            3070, 101, 103      Bascom/Hamilton  2017  -1424"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "link_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for node in nodes:\n",
    "#     print(node.get('index'))\n",
    "#     print(node.find('name').text)\n",
    "# #     if node.find('links'):\n",
    "# #         print('yay')\n",
    "#     if node.find('links') is not None:\n",
    "#         links = []\n",
    "#         for each in node.find('links').keys():\n",
    "            \n",
    "#             print(each)\n",
    "#             links.append(node.find('links').get(each))\n",
    "#     print(links)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'101'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "link_df['ID'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "link_df['ID'] = link_df['ID'].astype(int)\n",
    "CMP_Int['ID'] = CMP_Int['ID'].astype(int)\n",
    "df['ID'] = df['ID'].astype(int)"
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>averagecriticaldelay</th>\n",
       "      <th>averagedelay</th>\n",
       "      <th>base_vol</th>\n",
       "      <th>bike_vol</th>\n",
       "      <th>cap_delay_veh</th>\n",
       "      <th>cap_vol_cap</th>\n",
       "      <th>cap_vol_sat</th>\n",
       "      <th>criticalvoltocap</th>\n",
       "      <th>cycletime</th>\n",
       "      <th>...</th>\n",
       "      <th>rights_eb</th>\n",
       "      <th>rights_nb</th>\n",
       "      <th>rights_sb</th>\n",
       "      <th>rights_wb</th>\n",
       "      <th>user_adj_vol</th>\n",
       "      <th>volcount_date_day</th>\n",
       "      <th>volcount_date_month</th>\n",
       "      <th>volcount_date_year</th>\n",
       "      <th>voltimeperiod</th>\n",
       "      <th>y_r</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>28.51241</td>\n",
       "      <td>30.69518</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 190.0, \"@thru\"...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 54.7613, \"@thr...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.59839, \"@thru\"...</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.06032, \"@thru\"...</td>\n",
       "      <td>0.57155</td>\n",
       "      <td>120</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>5:00 - 6:00 pm</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>56.93715</td>\n",
       "      <td>47.77116</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 140.0, \"@thru\"...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 67.29492, \"@th...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.53322, \"@thru\"...</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.08, \"@thru\": 0...</td>\n",
       "      <td>0.60126</td>\n",
       "      <td>166</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>4:30 PM to 5:30 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>30.43571</td>\n",
       "      <td>25.11601</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 791.0, \"@thru\"...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 48.07382, \"@th...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.55739, \"@thru\"...</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.17381, \"@thru\"...</td>\n",
       "      <td>0.66155</td>\n",
       "      <td>166</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>4:45 - 5:45 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>49.06350</td>\n",
       "      <td>52.22595</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 70.0, \"@thru\":...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 138.48647, \"@t...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.87234, \"@thru\"...</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.04, \"@thru\": 0...</td>\n",
       "      <td>0.87234</td>\n",
       "      <td>166</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Free</td>\n",
       "      <td>No</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>13</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>4:45 - 5:45 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>52.74055</td>\n",
       "      <td>51.41155</td>\n",
       "      <td>{\"basevolume\": {\"nb\": {\"@left\": 444.0, \"@thru\"...</td>\n",
       "      <td>{\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...</td>\n",
       "      <td>{\"finaldelay\": {\"nb\": {\"@left\": 64.13012, \"@th...</td>\n",
       "      <td>{\"voltocap\": {\"nb\": {\"@left\": 0.6868, \"@thru\":...</td>\n",
       "      <td>{\"voltosat\": {\"nb\": {\"@left\": 0.14095, \"@thru\"...</td>\n",
       "      <td>0.71093</td>\n",
       "      <td>166</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>5:15 - 6:15 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID averagecriticaldelay averagedelay  \\\n",
       "0  101             28.51241     30.69518   \n",
       "1  102             56.93715     47.77116   \n",
       "2  103             30.43571     25.11601   \n",
       "3  104             49.06350     52.22595   \n",
       "4  105             52.74055     51.41155   \n",
       "\n",
       "                                            base_vol  \\\n",
       "0  {\"basevolume\": {\"nb\": {\"@left\": 190.0, \"@thru\"...   \n",
       "1  {\"basevolume\": {\"nb\": {\"@left\": 140.0, \"@thru\"...   \n",
       "2  {\"basevolume\": {\"nb\": {\"@left\": 791.0, \"@thru\"...   \n",
       "3  {\"basevolume\": {\"nb\": {\"@left\": 70.0, \"@thru\":...   \n",
       "4  {\"basevolume\": {\"nb\": {\"@left\": 444.0, \"@thru\"...   \n",
       "\n",
       "                                            bike_vol  \\\n",
       "0  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "1  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "2  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "3  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "4  {\"bikevol\": {\"nb\": {\"@left\": 0.0, \"@thru\": 0.0...   \n",
       "\n",
       "                                       cap_delay_veh  \\\n",
       "0  {\"finaldelay\": {\"nb\": {\"@left\": 54.7613, \"@thr...   \n",
       "1  {\"finaldelay\": {\"nb\": {\"@left\": 67.29492, \"@th...   \n",
       "2  {\"finaldelay\": {\"nb\": {\"@left\": 48.07382, \"@th...   \n",
       "3  {\"finaldelay\": {\"nb\": {\"@left\": 138.48647, \"@t...   \n",
       "4  {\"finaldelay\": {\"nb\": {\"@left\": 64.13012, \"@th...   \n",
       "\n",
       "                                         cap_vol_cap  \\\n",
       "0  {\"voltocap\": {\"nb\": {\"@left\": 0.59839, \"@thru\"...   \n",
       "1  {\"voltocap\": {\"nb\": {\"@left\": 0.53322, \"@thru\"...   \n",
       "2  {\"voltocap\": {\"nb\": {\"@left\": 0.55739, \"@thru\"...   \n",
       "3  {\"voltocap\": {\"nb\": {\"@left\": 0.87234, \"@thru\"...   \n",
       "4  {\"voltocap\": {\"nb\": {\"@left\": 0.6868, \"@thru\":...   \n",
       "\n",
       "                                         cap_vol_sat criticalvoltocap  \\\n",
       "0  {\"voltosat\": {\"nb\": {\"@left\": 0.06032, \"@thru\"...          0.57155   \n",
       "1  {\"voltosat\": {\"nb\": {\"@left\": 0.08, \"@thru\": 0...          0.60126   \n",
       "2  {\"voltosat\": {\"nb\": {\"@left\": 0.17381, \"@thru\"...          0.66155   \n",
       "3  {\"voltosat\": {\"nb\": {\"@left\": 0.04, \"@thru\": 0...          0.87234   \n",
       "4  {\"voltosat\": {\"nb\": {\"@left\": 0.14095, \"@thru\"...          0.71093   \n",
       "\n",
       "  cycletime                        ...                         rights_eb  \\\n",
       "0       120                        ...                               Ovl   \n",
       "1       166                        ...                               Ovl   \n",
       "2       166                        ...                               Ovl   \n",
       "3       166                        ...                                No   \n",
       "4       166                        ...                               Ovl   \n",
       "\n",
       "  rights_nb rights_sb rights_wb  \\\n",
       "0        No        No       Ovl   \n",
       "1       Ovl       Ovl       Ovl   \n",
       "2       Ovl        No        No   \n",
       "3       Ovl      Free        No   \n",
       "4       Ovl       Ovl       Ovl   \n",
       "\n",
       "                                        user_adj_vol volcount_date_day  \\\n",
       "0  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                 6   \n",
       "1  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                 1   \n",
       "2  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                 1   \n",
       "3  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                13   \n",
       "4  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...                 2   \n",
       "\n",
       "  volcount_date_month volcount_date_year       voltimeperiod  \\\n",
       "0                  12               2016      5:00 - 6:00 pm   \n",
       "1                  12               2016  4:30 PM to 5:30 PM   \n",
       "2                  12               2016      4:45 - 5:45 PM   \n",
       "3                  12               2016      4:45 - 5:45 PM   \n",
       "4                   1               2017      5:15 - 6:15 PM   \n",
       "\n",
       "                                                 y_r  \n",
       "0  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "1  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "2  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "3  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "4  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "580"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "link_df['ID'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>links</th>\n",
       "      <th>name</th>\n",
       "      <th>xpos</th>\n",
       "      <th>ypos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>105, 3071, 5430</td>\n",
       "      <td>Bascom/Campbell</td>\n",
       "      <td>2017</td>\n",
       "      <td>-1492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>7251, 104, 5429</td>\n",
       "      <td>Winchester/Hamilton</td>\n",
       "      <td>1877</td>\n",
       "      <td>-1422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>11708, 105, 11709, 11704</td>\n",
       "      <td>Hwy 17 NB/Hamilton</td>\n",
       "      <td>1975</td>\n",
       "      <td>-1424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>11708, 11704, 11709, 102</td>\n",
       "      <td>Hwy 17 SB/Hamilton</td>\n",
       "      <td>1937</td>\n",
       "      <td>-1424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>3070, 101, 103</td>\n",
       "      <td>Bascom/Hamilton</td>\n",
       "      <td>2017</td>\n",
       "      <td>-1424</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID                     links                 name  xpos   ypos\n",
       "0  101           105, 3071, 5430      Bascom/Campbell  2017  -1492\n",
       "1  102           7251, 104, 5429  Winchester/Hamilton  1877  -1422\n",
       "2  103  11708, 105, 11709, 11704   Hwy 17 NB/Hamilton  1975  -1424\n",
       "3  104  11708, 11704, 11709, 102   Hwy 17 SB/Hamilton  1937  -1424\n",
       "4  105            3070, 101, 103      Bascom/Hamilton  2017  -1424"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "link_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>links</th>\n",
       "      <th>name</th>\n",
       "      <th>xpos</th>\n",
       "      <th>ypos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>596</th>\n",
       "      <td>18818</td>\n",
       "      <td>18817</td>\n",
       "      <td>None</td>\n",
       "      <td>2474</td>\n",
       "      <td>711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>597</th>\n",
       "      <td>18819</td>\n",
       "      <td>5625</td>\n",
       "      <td>None</td>\n",
       "      <td>1515</td>\n",
       "      <td>-977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>598</th>\n",
       "      <td>18821</td>\n",
       "      <td>18809, 18823, 3056</td>\n",
       "      <td>None</td>\n",
       "      <td>1954</td>\n",
       "      <td>-1169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>599</th>\n",
       "      <td>18822</td>\n",
       "      <td>7226</td>\n",
       "      <td>None</td>\n",
       "      <td>-171</td>\n",
       "      <td>-437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>600</th>\n",
       "      <td>18823</td>\n",
       "      <td>18821, 7105, 12813, 3056</td>\n",
       "      <td>None</td>\n",
       "      <td>1955</td>\n",
       "      <td>-1202</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        ID                     links  name  xpos   ypos\n",
       "596  18818                     18817  None  2474    711\n",
       "597  18819                      5625  None  1515   -977\n",
       "598  18821        18809, 18823, 3056  None  1954  -1169\n",
       "599  18822                      7226  None  -171   -437\n",
       "600  18823  18821, 7105, 12813, 3056  None  1955  -1202"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "link_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "255"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CMP_Int['ID'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "249"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['ID'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{3012, 3073, 3100, 3101, 5803, 5804}"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(CMP_Int['ID'].unique()) - set(df['ID'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>STATUS</th>\n",
       "      <th>SCORE</th>\n",
       "      <th>SIDE</th>\n",
       "      <th>ARC_STREET</th>\n",
       "      <th>ID</th>\n",
       "      <th>CMP_SYSTEM</th>\n",
       "      <th>CROSS_STRE</th>\n",
       "      <th>INTERSECTI</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>...</th>\n",
       "      <th>YR_2010</th>\n",
       "      <th>LOS_RATING</th>\n",
       "      <th>LOS_2008</th>\n",
       "      <th>LOS_2006</th>\n",
       "      <th>LOS_2004</th>\n",
       "      <th>YR_2012</th>\n",
       "      <th>ID_1</th>\n",
       "      <th>YR_2014</th>\n",
       "      <th>YR_2016</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>76</td>\n",
       "      <td>T</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "      <td>State Hwy 87 &amp; Coleman Ave</td>\n",
       "      <td>3012</td>\n",
       "      <td>Hwy 87</td>\n",
       "      <td>Coleman Av.</td>\n",
       "      <td>State Hwy 87 &amp; Coleman Ave</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NM</td>\n",
       "      <td>3012.0</td>\n",
       "      <td>NM</td>\n",
       "      <td>NM</td>\n",
       "      <td>POINT (-121.9009090038399 37.34184400309833)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>248</td>\n",
       "      <td>T</td>\n",
       "      <td>86</td>\n",
       "      <td>None</td>\n",
       "      <td>Montague Exp. &amp; Capitol Av.</td>\n",
       "      <td>5803</td>\n",
       "      <td>Montague Exp.</td>\n",
       "      <td>Capitol Av.</td>\n",
       "      <td>Montague Exp. &amp; Capitol Av.</td>\n",
       "      <td>Milpitas</td>\n",
       "      <td>...</td>\n",
       "      <td>D-</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>12</td>\n",
       "      <td>D-</td>\n",
       "      <td>5803.0</td>\n",
       "      <td>E+</td>\n",
       "      <td>NM</td>\n",
       "      <td>POINT (-121.8942910084896 37.41018099551391)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>249</td>\n",
       "      <td>T</td>\n",
       "      <td>86</td>\n",
       "      <td>None</td>\n",
       "      <td>Montague Exp. &amp; Milpitas Blvd.</td>\n",
       "      <td>5804</td>\n",
       "      <td>Montague Exp.</td>\n",
       "      <td>Milpitas Blvd.</td>\n",
       "      <td>Montague Exp. &amp; Milpitas Blvd.</td>\n",
       "      <td>Milpitas</td>\n",
       "      <td>...</td>\n",
       "      <td>C-</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>D+</td>\n",
       "      <td>5804.0</td>\n",
       "      <td>C-</td>\n",
       "      <td>NM</td>\n",
       "      <td>POINT (-121.8867929927123 37.41252900308231)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>137</td>\n",
       "      <td>M</td>\n",
       "      <td>100</td>\n",
       "      <td>L</td>\n",
       "      <td>6050 State Hwy 82</td>\n",
       "      <td>3073</td>\n",
       "      <td>Monterey Hwy. N</td>\n",
       "      <td>Bernal Rd.</td>\n",
       "      <td>6050 State Hwy 82</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>...</td>\n",
       "      <td>C</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>C</td>\n",
       "      <td>3073.0</td>\n",
       "      <td>C</td>\n",
       "      <td>C</td>\n",
       "      <td>POINT (-121.7748347672189 37.2391614181243)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>164</td>\n",
       "      <td>T</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "      <td>W Hedding St &amp; State Hwy 87</td>\n",
       "      <td>3100</td>\n",
       "      <td>Guadalupe Parkway</td>\n",
       "      <td>Hedding St.</td>\n",
       "      <td>W Hedding St &amp; State Hwy 87</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NM</td>\n",
       "      <td>3100.0</td>\n",
       "      <td>NM</td>\n",
       "      <td>NM</td>\n",
       "      <td>POINT (-121.9093580129817 37.35036500525176)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>167</th>\n",
       "      <td>165</td>\n",
       "      <td>T</td>\n",
       "      <td>100</td>\n",
       "      <td>None</td>\n",
       "      <td>W Taylor St &amp; State Hwy 87</td>\n",
       "      <td>3101</td>\n",
       "      <td>Guadalupe Parkway</td>\n",
       "      <td>Taylor</td>\n",
       "      <td>W Taylor St &amp; State Hwy 87</td>\n",
       "      <td>San Jose</td>\n",
       "      <td>...</td>\n",
       "      <td>NM</td>\n",
       "      <td>14</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NM</td>\n",
       "      <td>3101.0</td>\n",
       "      <td>NM</td>\n",
       "      <td>NM</td>\n",
       "      <td>POINT (-121.9046459933454 37.34670599126902)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     OBJECTID STATUS  SCORE  SIDE                      ARC_STREET    ID  \\\n",
       "22         76      T    100  None      State Hwy 87 & Coleman Ave  3012   \n",
       "94        248      T     86  None     Montague Exp. & Capitol Av.  5803   \n",
       "95        249      T     86  None  Montague Exp. & Milpitas Blvd.  5804   \n",
       "139       137      M    100     L               6050 State Hwy 82  3073   \n",
       "166       164      T    100  None     W Hedding St & State Hwy 87  3100   \n",
       "167       165      T    100  None      W Taylor St & State Hwy 87  3101   \n",
       "\n",
       "            CMP_SYSTEM      CROSS_STRE                      INTERSECTI  \\\n",
       "22              Hwy 87     Coleman Av.      State Hwy 87 & Coleman Ave   \n",
       "94       Montague Exp.     Capitol Av.     Montague Exp. & Capitol Av.   \n",
       "95       Montague Exp.  Milpitas Blvd.  Montague Exp. & Milpitas Blvd.   \n",
       "139    Monterey Hwy. N      Bernal Rd.               6050 State Hwy 82   \n",
       "166  Guadalupe Parkway     Hedding St.     W Hedding St & State Hwy 87   \n",
       "167  Guadalupe Parkway          Taylor      W Taylor St & State Hwy 87   \n",
       "\n",
       "     LOCATION                      ...                      YR_2010  \\\n",
       "22   San Jose                      ...                           NM   \n",
       "94   Milpitas                      ...                           D-   \n",
       "95   Milpitas                      ...                           C-   \n",
       "139  San Jose                      ...                            C   \n",
       "166  San Jose                      ...                           NM   \n",
       "167  San Jose                      ...                           NM   \n",
       "\n",
       "    LOS_RATING LOS_2008 LOS_2006 LOS_2004 YR_2012    ID_1 YR_2014 YR_2016  \\\n",
       "22          14        0        0        0      NM  3012.0      NM      NM   \n",
       "94          10       11       11       12      D-  5803.0      E+      NM   \n",
       "95           7        8        9        8      D+  5804.0      C-      NM   \n",
       "139          6        6        6        6       C  3073.0       C       C   \n",
       "166         14        0        0        0      NM  3100.0      NM      NM   \n",
       "167         14        0        0        0      NM  3101.0      NM      NM   \n",
       "\n",
       "                                         geometry  \n",
       "22   POINT (-121.9009090038399 37.34184400309833)  \n",
       "94   POINT (-121.8942910084896 37.41018099551391)  \n",
       "95   POINT (-121.8867929927123 37.41252900308231)  \n",
       "139   POINT (-121.7748347672189 37.2391614181243)  \n",
       "166  POINT (-121.9093580129817 37.35036500525176)  \n",
       "167  POINT (-121.9046459933454 37.34670599126902)  \n",
       "\n",
       "[6 rows x 34 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Missing Intersections\n",
    "CMP_Int.loc[CMP_Int['ID'].isin(set(CMP_Int['ID'].unique()) - set(df['ID'].unique())),]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# CMP_Int"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'87/COLEMAN',\n",
       " 'BERNAL/MONTEREY (N)',\n",
       " 'Bollinger Rd/Miller Ave',\n",
       " 'De Anza Blvd/Rainbow Dr',\n",
       " 'GUADALUPE/HEDDING',\n",
       " 'GUADALUPE/TAYLOR',\n",
       " 'Homestead Rd/Stelling Rd',\n",
       " 'LAWRENCE EXPWY/SARATOGA ',\n",
       " 'MONTAGUE EXPWY/CAPITOL AVE',\n",
       " 'MONTAGUE EXPWY/MILPITAS BLVD',\n",
       " None,\n",
       " 'Stelling Rd/McClellan Rd',\n",
       " 'Stelling Rd/Rainbow Dr',\n",
       " 'Wolfe Rd/Homestead Rd'}"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(link_df['name'].unique()) - set(df['name'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(df['ID'].unique()) - set(CMP_Int['ID'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set(link_df['ID'].unique()) - set(df['ID'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ID</th>\n",
       "      <th>links</th>\n",
       "      <th>name</th>\n",
       "      <th>xpos</th>\n",
       "      <th>ypos</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>SCORE</th>\n",
       "      <th>SIDE</th>\n",
       "      <th>ARC_STREET</th>\n",
       "      <th>...</th>\n",
       "      <th>rights_eb</th>\n",
       "      <th>rights_nb</th>\n",
       "      <th>rights_sb</th>\n",
       "      <th>rights_wb</th>\n",
       "      <th>user_adj_vol</th>\n",
       "      <th>volcount_date_day</th>\n",
       "      <th>volcount_date_month</th>\n",
       "      <th>volcount_date_year</th>\n",
       "      <th>voltimeperiod</th>\n",
       "      <th>y_r</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>105, 3071, 5430</td>\n",
       "      <td>Bascom/Campbell</td>\n",
       "      <td>2017</td>\n",
       "      <td>-1492</td>\n",
       "      <td>1.0</td>\n",
       "      <td>T</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "      <td>S. Bascom Ave &amp; E. Campbell Ave</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>6</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>5:00 - 6:00 pm</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>7251, 104, 5429</td>\n",
       "      <td>Winchester/Hamilton</td>\n",
       "      <td>1877</td>\n",
       "      <td>-1422</td>\n",
       "      <td>2.0</td>\n",
       "      <td>T</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "      <td>E. Hamilton Ave &amp; Winchester Blvd</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>4:30 PM to 5:30 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>11708, 105, 11709, 11704</td>\n",
       "      <td>Hwy 17 NB/Hamilton</td>\n",
       "      <td>1975</td>\n",
       "      <td>-1424</td>\n",
       "      <td>3.0</td>\n",
       "      <td>T</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "      <td>E. Hamilton Ave &amp; Creekside Way</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>No</td>\n",
       "      <td>No</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>4:45 - 5:45 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>11708, 11704, 11709, 102</td>\n",
       "      <td>Hwy 17 SB/Hamilton</td>\n",
       "      <td>1937</td>\n",
       "      <td>-1424</td>\n",
       "      <td>4.0</td>\n",
       "      <td>T</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "      <td>E. Hamilton Ave &amp; Salmar Ave</td>\n",
       "      <td>...</td>\n",
       "      <td>No</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Free</td>\n",
       "      <td>No</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>13</td>\n",
       "      <td>12</td>\n",
       "      <td>2016</td>\n",
       "      <td>4:45 - 5:45 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>105</td>\n",
       "      <td>3070, 101, 103</td>\n",
       "      <td>Bascom/Hamilton</td>\n",
       "      <td>2017</td>\n",
       "      <td>-1424</td>\n",
       "      <td>5.0</td>\n",
       "      <td>T</td>\n",
       "      <td>100.0</td>\n",
       "      <td>None</td>\n",
       "      <td>E. Hamilton Ave &amp; S. Bascom Ave</td>\n",
       "      <td>...</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>Ovl</td>\n",
       "      <td>{\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2017</td>\n",
       "      <td>5:15 - 6:15 PM</td>\n",
       "      <td>{\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 68 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    ID                     links                 name  xpos   ypos  OBJECTID  \\\n",
       "0  101           105, 3071, 5430      Bascom/Campbell  2017  -1492       1.0   \n",
       "1  102           7251, 104, 5429  Winchester/Hamilton  1877  -1422       2.0   \n",
       "2  103  11708, 105, 11709, 11704   Hwy 17 NB/Hamilton  1975  -1424       3.0   \n",
       "3  104  11708, 11704, 11709, 102   Hwy 17 SB/Hamilton  1937  -1424       4.0   \n",
       "4  105            3070, 101, 103      Bascom/Hamilton  2017  -1424       5.0   \n",
       "\n",
       "  STATUS  SCORE  SIDE                         ARC_STREET  \\\n",
       "0      T  100.0  None    S. Bascom Ave & E. Campbell Ave   \n",
       "1      T  100.0  None  E. Hamilton Ave & Winchester Blvd   \n",
       "2      T  100.0  None    E. Hamilton Ave & Creekside Way   \n",
       "3      T  100.0  None       E. Hamilton Ave & Salmar Ave   \n",
       "4      T  100.0  None    E. Hamilton Ave & S. Bascom Ave   \n",
       "\n",
       "                         ...                         rights_eb rights_nb  \\\n",
       "0                        ...                               Ovl        No   \n",
       "1                        ...                               Ovl       Ovl   \n",
       "2                        ...                               Ovl       Ovl   \n",
       "3                        ...                                No       Ovl   \n",
       "4                        ...                               Ovl       Ovl   \n",
       "\n",
       "  rights_sb rights_wb                                       user_adj_vol  \\\n",
       "0        No       Ovl  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...   \n",
       "1       Ovl       Ovl  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...   \n",
       "2        No        No  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...   \n",
       "3      Free        No  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...   \n",
       "4       Ovl       Ovl  {\"useradj\": {\"nb\": {\"@left\": 1.0, \"@thru\": 1.0...   \n",
       "\n",
       "  volcount_date_day volcount_date_month volcount_date_year  \\\n",
       "0                 6                  12               2016   \n",
       "1                 1                  12               2016   \n",
       "2                 1                  12               2016   \n",
       "3                13                  12               2016   \n",
       "4                 2                   1               2017   \n",
       "\n",
       "        voltimeperiod                                                y_r  \n",
       "0      5:00 - 6:00 pm  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "1  4:30 PM to 5:30 PM  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "2      4:45 - 5:45 PM  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "3      4:45 - 5:45 PM  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "4      5:15 - 6:15 PM  {\"yellowplusallred\": {\"nb\": {\"@left\": 4.0, \"@t...  \n",
       "\n",
       "[5 rows x 68 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int_w_history = pd.merge(link_df, pd.merge(CMP_Int, df, how='left'), how='left')\n",
    "int_w_history.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "del int_w_history['ID_1']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "601"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(int_w_history)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "219"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(int_w_history['YR_2016'] == int_w_history['los'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "353"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(int_w_history['geometry'].isnull())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "int_w_history.to_csv('output/full_int_w_history.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Only output the intersections with geometrys attached.\n",
    "\n",
    "int_w_history_clean = int_w_history.loc[~int_w_history['geometry'].isnull(),]\n",
    "crs = {'init': 'epsg:4326'}\n",
    "filename = 'output/int_w_history.geojson'\n",
    "if os.path.exists(filename):\n",
    "    os.remove(filename)\n",
    "int_w_history_clean = gpd.GeoDataFrame(int_w_history_clean, crs=crs, geometry=int_w_history_clean['geometry'])\n",
    "int_w_history_clean.to_file(filename, driver='GeoJSON')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Wolfe Rd/Homestead Rd', 'Bollinger Rd/Miller Ave',\n",
       "       'De Anza Blvd/Rainbow Dr', 'Stelling Rd/Rainbow Dr',\n",
       "       'Stelling Rd/McClellan Rd', 'Homestead Rd/Stelling Rd',\n",
       "       '87/COLEMAN', 'BERNAL/MONTEREY (N)', 'GUADALUPE/HEDDING',\n",
       "       'GUADALUPE/TAYLOR', 'LAWRENCE EXPWY/SARATOGA ',\n",
       "       'MONTAGUE EXPWY/CAPITOL AVE', 'MONTAGUE EXPWY/MILPITAS BLVD', None],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "int_w_history.loc[int_w_history['geometry'].isnull(),]['name'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}