vol-pdf-scrape.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scraping LADOT Volume Data from PDFs\n",
    "\n",
    "## Background\n",
    "At LADOT, we have a lot of historical (and relatively current) data on vehicle volume counts made at intersections throughout the city at various times. The problem is that the format the data are currently in - PDF - doesn't readily allow for the type of big data analyses that we would like to perform. So for this task I went about trying to develop a method for scraping these historical PDF counts and formatting them into usable data tables using any python package I could. I ended up settling on the pdfquery package, which is really just a lightweight wrapper around the much more well-known package PDFMiner.\n",
    "\n",
    "The roughly 1,000 PDFs typically (though not always) look like this (converted to images for display here):\n",
    "\n",
    "![Example Volume PDF](images/example.jpg?raw=TRUE)\n",
    "\n",
    "##### Format Advantages\n",
    "* One big advantage is that the (manual count) volume data sheets are generally in the same format.\n",
    "* With very few exceptions, the PDFs were generated from another program (rather than being scanned images).\n",
    "\n",
    "##### Format Challenges\n",
    "There are a few minor challenges:\n",
    "* There are multiple tables on each page, and each is formatted differently.\n",
    "* The tables / text do not always appear in the exact same location on each page, which meant I needed a range of parameters to test for the bounding boxes.\n",
    "\n",
    "## General Approach\n",
    "My approach can be broken down into the few key parts: (1) define bounding boxes, (2) search for text within the bounding boxes (3) reformat the resulting text into multiple data tables, and (4) join the resulting tables to the ID established by the Bureau of Engineering. The details of the PDF extraction process is covered in the next python notebook.\n",
    "\n",
    "##### Join Tables to BOE Count IDs\n",
    "The last step involves taking all the data generated by this process and relating it to both a GPS coordinate and the intersection ID of the BOE centerline.\n",
    "\n",
    "To do this exercise, I requested data from BOE that powers NavigateLA. There are actually two relevant tables that were provided by BOE. The first table, *dbo_dot_traffic_data_files* relates the name of the PDF to a TrafficID, so I can use this table to match the PDF names and get the resulting Traffic IDs. The second table, *dot_traffic_data* takes the TrafficID and relates it to the intersection ID (the same one that is usually on the front page of each traffic count summary) as well as the the lat / lon of the location and the intersection name. The structure of the two tables are shown below:\n",
    "\n",
    "*dbo_dot_traffic_data_files*\n",
    "* ID: Unique Identifier for the count\n",
    "* TrafficID: This seems to be an identifier for the location\n",
    "* TrafficType: Manual Count (manual_count) / Automatic Count (automatic_count) / Survey Data (survey_data)\n",
    "* DocName: Name of the PDF document\n",
    "* UniqueDocName: Not exactly sure the purpose of this one, perhaps at one time the DocNames were not unique?\n",
    "* UploadDT: Date the PDF was uploaded to NavLA\n",
    "\n",
    "*dot_traffic_data*\n",
    "* TrafficID: Identifier for the location\n",
    "* Intersection: The ID for the intersection, corresponding to the CL_NODE_ID in the BOE Centerline file\n",
    "* ext: ?\n",
    "* lat: Latitude\n",
    "* lon: Longitude\n",
    "* intersection: Intersection Name (eg ISLAND AVE at L ST)\n",
    "* Shape: geometry object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "### Setup\n",
    "import csv\n",
    "import glob\n",
    "from datetime import datetime, date, time\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import folium\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "### Getting Started\n",
    "I'm actually going to start by loading and cleaning the tables provided by BOE (mentioned just above). I went ahead ane excluded any rows where the TrafficID was NaN (there was one value) since we would not be able to associate that to a valid intersection or location."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 9034 records in the table.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>TrafficID</th>\n",
       "      <th>TrafficType</th>\n",
       "      <th>DocName</th>\n",
       "      <th>UniqueDocName</th>\n",
       "      <th>UploadDT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1435</td>\n",
       "      <td>manual_count</td>\n",
       "      <td>2_GRAVDM93.pdf</td>\n",
       "      <td>2_GRAVDM93.pdf</td>\n",
       "      <td>2007-04-02 08:38:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1436</td>\n",
       "      <td>manual_count</td>\n",
       "      <td>4_CULVIS95.pdf</td>\n",
       "      <td>4_CULVIS95.pdf</td>\n",
       "      <td>2008-02-20 09:15:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1436</td>\n",
       "      <td>manual_count</td>\n",
       "      <td>4_MONCUL100928.pdf</td>\n",
       "      <td>4_MONCUL100928.pdf</td>\n",
       "      <td>2011-08-09 13:58:55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>1437</td>\n",
       "      <td>manual_count</td>\n",
       "      <td>16_VISTA DEL MAR.WATERVIEW07.pdf</td>\n",
       "      <td>16_VISTA DEL MAR.WATERVIEW07.pdf</td>\n",
       "      <td>2007-11-28 13:01:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1437</td>\n",
       "      <td>manual_count</td>\n",
       "      <td>16_visvis01.pdf</td>\n",
       "      <td>16_visvis01.pdf</td>\n",
       "      <td>2007-12-03 16:30:42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   ID  TrafficID   TrafficType                           DocName  \\\n",
       "0   1       1435  manual_count                    2_GRAVDM93.pdf   \n",
       "1   2       1436  manual_count                    4_CULVIS95.pdf   \n",
       "2   3       1436  manual_count                4_MONCUL100928.pdf   \n",
       "3   4       1437  manual_count  16_VISTA DEL MAR.WATERVIEW07.pdf   \n",
       "4   5       1437  manual_count                   16_visvis01.pdf   \n",
       "\n",
       "                      UniqueDocName            UploadDT  \n",
       "0                    2_GRAVDM93.pdf 2007-04-02 08:38:30  \n",
       "1                    4_CULVIS95.pdf 2008-02-20 09:15:12  \n",
       "2                4_MONCUL100928.pdf 2011-08-09 13:58:55  \n",
       "3  16_VISTA DEL MAR.WATERVIEW07.pdf 2007-11-28 13:01:46  \n",
       "4                   16_visvis01.pdf 2007-12-03 16:30:42  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load traffic data files table\n",
    "traffic_data_files_path = 'data/TrafficCountFileStructure/boe_tables/dbo_dot_traffic_data_files.csv'\n",
    "dbo_dot_traffic_data_files = pd.read_csv(traffic_data_files_path, parse_dates=['UploadDT'], encoding=\"ISO-8859-1\")\n",
    "\n",
    "# Drop rows where TrafficID is NaN, convert TrafficID to int type\n",
    "dbo_dot_traffic_data_files = dbo_dot_traffic_data_files.dropna(axis=0, how='any',subset=['TrafficID'])\n",
    "dbo_dot_traffic_data_files['TrafficID'] = dbo_dot_traffic_data_files['TrafficID'].astype(int)\n",
    "\n",
    "# Subset out Survey Data and Automatic Counts\n",
    "dbo_dot_traffic_data_files = dbo_dot_traffic_data_files[(dbo_dot_traffic_data_files['TrafficType'] == 'manual_count')]\n",
    "\n",
    "# See traffic data files head\n",
    "print(\"There are \" + str(len(dbo_dot_traffic_data_files)) + \" records in the table.\")\n",
    "dbo_dot_traffic_data_files.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Step 1: Make sure I have all the Volume Data PDFs (prep) and Related TraffcIDs\n",
    "I knew we needed to be able to join all of the data we pull out of the PDF to the table provided by BOE. Since the BOE Table *dbo_dot_traffic_data_files* contains a field with the filename, this is an easy join with our filename. Once this join is complete we will have the TrafficID.\n",
    "\n",
    "My initial process for doing this involved just looping through *dbo_dot_traffic_data_files* (after subsetting for the manual counts as I did above), and then just grabbing a file with the same filename in my folder, but then I realized that there were many rows that did not have a PDF in the folder. By doing the join, I could easily identify which PDFs were missing.\n",
    "\n",
    "I went a bit further - to get a graphical sense of the differences, I went ahead and created a venn diagram showing the results of the join from the BOE table and my folder of manual PDF counts. As you can tell, there is quite a difference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAASEAAADuCAYAAAB21LHOAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4wLCBo\ndHRwOi8vbWF0cGxvdGxpYi5vcmcvFvnyVgAAHmxJREFUeJzt3Xl4XNWZ5/Hvq9XavcmrvGDANgZj\nm8RgbAgQoNkCOKSBTiCEnkm6MzwJzUzIkNAMxkmY9GTS3els02TpDlsnkGVM0yzBhrDEZl+8ATZY\n3mXLmyxb1lJS1dt/nJJTlkuyLFXdc2/V+3meeixXlXTekko/nXvuPeeIqmKMMb4U+C7AGJPfLISM\nMV5ZCBljvLIQMsZ4ZSFkjPHKQsgY45WFkDHGKwshY4xXFkLGGK8shIwxXlkIGWO8shAyxnhlIWSM\n8cpCyBjjlYWQMcYrCyFjjFcWQsYYryyEjDFeWQgZY7yyEDLGeGUhZIzxykLIGOOVhZAxxisLIWOM\nVxZCxhivinwXYI6TSClQkXIrx/0cC3vcClI+ViAGdKT82/1xDGgHmlHtDPKlGAMWQuElUgXUAiOB\nGqAaqAJKstjmIWB/8tZ8+GPVlqy1afKe2F70IeB6N7XAqOStFijzWtOROoHdwM7krdF6TSZTLIR8\nECkAxgKTgTpcTydKFBdK25O3nagm/JZkospCKCgixcAEXPBMJJuHVcHrArYAHwJbUY17rsdEiIVQ\nNrnDrBNxwTOO/DgbGQM24gKpAXuDmWOwEMoGkVpgBi6A8nnwvw3YAHyI6i7fxZhwshDKFJEiXOjM\nwA0smyPtBlYB9dY7MqkshAZLpAYXPFOBUs/VRMFBYDXwPqpdvosx/lkIDZTIUOAjwBRAPFcTRR3A\ne8AaVFt9F2P8sRA6Xq7n8xHcoZeFz+AlgHXAG6i2+S7GBM9CqL9EynHhM438OMsVtBjwDrDaTvHn\nFwuhY3EDzrOB08nvM11BOQi8imq970JMMCyE+iJSB5yLm7NlgrUDeBnVPb4LMdllIZSOu8jwbNwZ\nL+OPAuuBV1Dt8F2MyQ4LoZ5ETgTmE64JpPmuFXgR1S2+CzGZZyHUTaQCOAeY5LsU06t1uEO0mO9C\nTOZYCAGInIQLoFyaVJqrDgLP2jSQ3JHfIeSW1JgHnOa7FHNcEsBbwNs2BST68jeE3HU/FwFjfJdi\nBqwBWIZqu+9CzMDlZwiJjAUuxK3PbKLtAPA0qvt9F2IGJv9CSOR04EzsqudcEsP1iLb5LsQcv/wJ\nITf+cz5wkudKTHYkgBWovuu7EHN88iOE3NSLP8Ot52xy21pcGOXBGzs35H4IiZQAl2ID0PlkG7DU\ndgSJhtwOIZEy4HJghO9STOB2Ak/awmnhl7shJFIJXEH0ttMxmbMDeMqCKNxyM4TcwmNXAJW+SzHe\nNeBO4VsQhVTunaYWqQauxALIOOOAS5MnJ0wI5VYIuaugL8cuQjRHGgdcgkih70LM0XInhNxZsMuA\nat+lmFAajwui3HnP54jc+IG4N9Yl2Fkw07c63GoJJkRyI4TcldBjfRdhImE6IrZqQohEP4REPopN\nxTDH5+zk+uEmBKIdQm4xsjN8l2EiR4ALk5dyGM+iG0LuDXSu7zJMZJXiBqptNU3PohlC7lTrRUCx\n71JMpA0FLkLEdtL1KJoh5JZktTNhJhPqcDvrGk+iF0Iik4FTPVdhcsscREb7LiJfRSuE3KTU83yX\nYXKOABcgYof3HkQnhNwFiRfiBhSNybRq3KaXJmDRCSGYBViX2WTTNEQm+C4i30QjhESqgDm+yzB5\n4WN22j5Y0Qgh1022pRhMECpwZ19NQMIfQiKTsP3hTbCm29my4IQ7hNxCVDZYaHyw3lBAwh1CMBuo\n8l2EyUujEZniu4h8EN4QcnPDZvkuw+S1M20RtOwL8zf4bMCW4zQ+VQO29lCWhTOEREYCE32XYQxu\nSoddIJtF4QwhWyPIhEcpNsE1q8IXQiLDgcm+yzAmxYzkvEWTBeELIesFmfApwMaGsiZcISQyFDjB\ndxnGpDHdZtlnR7hCyM0Ps1XuTBiVANN8F5GLwhNCbvtm2zXDhNlpthRs5oUnhNxqifYDNmFWjZ00\nybhwhJBbuP5k32UY0w8zfReQa8IRQm4weojvIozphzGIjPJdRC4JSwhN912AMcfBBqgzyH8IuVUT\nx/kuw5jjcIJNbM2cMKxWaGNBJuM6htDVWkG8rZxErBSNF0FhFxTHkJIYUhxDyg5RVNoxoEnSQ3B/\nOLdluOy8FIYQstPyZlAUtHk4sV1j6dozmoLm4ZTEiymiH+/v4g66qprpHLGLRO0Oiobv6fduLidi\nIZQRoqoeW5da4JP+CjBR1jSCjo1TiTfWURovysyyL6VtdI7fRGzSB5RUHOpzm/EY8ACqiUy0m898\nh9BHsbli5jgo6NYptNVPp7ClJrt70FXvo33qGnTMdsp6ecrTqG7JZg35wHcILQTsdKfpl721tK88\nC2mtCnYDzKr9tM98HUlzqPYBqn8IspZc5C+ERIYAn8WukjbH0DGErlVziTXWUe6zjtHbaJ39CqXF\nnYcP/WLAg6jGfdYVdT5D6ETcts7G9Gr7RNpWnkVJIkNjPoNV2kbn3BdJDN13uFf0BKrbvRYVcT6v\ndbDtdk2vFHTVXA69vYCysAQQQEcZxcsvprh+Gq3Ju+wat0HyGUJ1Hts2IdZVSOLlj9O+5SQqfNeS\njhZQ8O4ZlL9xDq0qjPVdT9T5CSGREeD3+N6EU7yAxIqLiO0b3esZqdDYOYHyV8+jumGiLXY2GL56\nQtYLMkdR0FfPp+PA8OhMZt4zlvJbF3KW7zqizFcI2Wl5c5Q3F9AWhR5QT9VxJslisSAaIF8hNMJT\nuyak3pvFoZ0To3mIPqONAmCWLJapvmuJouBDyC0WXh14uya09tbSvuGUaAYQwOQOSpIfni2LJbKv\nwxcfPaHhHto0IdVVSOKtBRQi0b1odYhSOKGDTtxGief4ridqfISQHYqZw949g7aOsj4nikbC9DY6\nkx9OlsUyxWsxEWMhZLw5VEnnlinRPQxLNaaT1KkHC2SxROYMn28WQsab92bRSUF0D8NSje484nWU\n4fbQM/0QbAi5PZtsTMjQVkbXzrronY7vTW3nUWE6TRZLGBYNDL2ge0JlhGM1R+PZutOJ5UovCGBE\n11Hv6xLc6ovmGIIOoZw4/jeD01VEYvuk6FwV3R/D4mkn2Z4SeCER5KMnZPJcw0TatTAEO71kUIlS\nUBmn51Kvo2SxjPRSUIRYCJnAbZ+UO4dhqUZ30pXmbttT7xjscMwEKiFo08jDVxjnlDGdpFth0dYb\nOgbrCZlA7aulI0yLlGXSmBjplimtsbNkfbMQMoHaP+KocZOc0eOCxW6CXRvXJzscM4FqqfFdQfYM\njfc61lUbaCERE3Q3MadOy3a7Di58Fs4V0LGw/Q/wi7eg5mb4QhtUjIcty+FfaiD+MbjufZgG0Akl\nrVDVAbcBfByuWQUzAW6EJ74Hb3h8WVnRUp3ZQenvLOfj7+zkXEBmjeGlOxbw7D3Pc+XaXZxTWkQL\nwGUn8/9vmMmad3YyYtHzLK4qoRFgdCX1f/9nPJyJOhoOUvb/nuDzxJiAABdwP+s4jR3Mpot2uUfq\ngZtVtSET7eWSoEMop07LAiyHob+HCz+ARaOg81T4qzth7nKYeS0s+yd44yy44UtwzoPwwovwaPfn\nfgYuWAcTAe6GmRth4hb45n4oOgNu/xtYcwK0+3t1mddakbn33IubGffOTs790eV8u7yYrlue5G9e\n285qgDPGsuzr57K05+dUFLP7oWv4ZqZq6PZ//sj1dbWs2vdZfkY7hRyihJNpYBj/DuzhHuqAu4Ev\nZrrtqAs6FHLy1GwCCvZCcSsUxKCkDprrYdq34S2Am+Dl5TC75+e9AGdeA68BrIGxp8L6ckiMg1gd\nbPs+nBr0a8m2WGnmQmj9XsaOrqR+WBmx0iISE2tYv7T+6O9ztjW2MGRHC1OvO5cXABhCnBG0Mezw\nH5ASoALSjhnlPesJDdIC2H8FPHM6/F0RdJ4M734StvwDtJXjBmFnQtMBGJr6ec/B8P0w4ivwPsAZ\nsO2f4RM7YdluKPkApk2GHR5eUtYkBDK5btCMWrYvq2fhlmYqqkrorG/itFEVbK4o5tDbO7nght9x\ndm05m2+fz6/rqt0WPa2djLzxd9xVXEj7wmksuXo6Hw62jrW7GTmkiIM/fIwvEGMyNWzm0zxCFTEe\nYCFbOQv3s7xgsG3loqBDKOd6Quug/BWY/QbcOQXa5sJf/Th9D+aIv4I/gLmz4a0hyfvvgndfhUmn\nwx0VcHAS1BeR9rqTyFIhQQb/EM2fwM43Gnj6zme5rbiQjpHlbCsQEjeezvMTa/iPAoHFz3P1d1dw\n7fcu5f6ThtP8/cv42sQaDi3dwMSfvMUt8+q4Z3Tl4A55uxIU7m9n4lWzuP+xy2jg51zPEi7ls/w7\nN7EE+BX3MB74ErAoIy8+hwTdM8m57uh9cMpI2DMLWqogfj68/Tac2AFlrcnv72oYVg3NqZ+3HOb+\nRfJQrNvj8NQu+OZG+B7ANNgV2AsJgGjm/wjdehbLH7qGe//1ar5bVsShURU0njScgyWFaFEB+qkZ\nvLTrEJMBKkvomljDIYCLT2RLZQm7VzYyerA1nDCUpiFFNM2fwQYAZvAmTW6sLykB/BvwqcG2lYss\nhAZpKuzbCFN2QkkceBWmT4EdU2Dd1+EMgAfg7PnwTvfnLIHR7VB+C9R339cOstaNG/AwjG+Aujvg\n3cBfUBYVZCGENjRRBbC6keEbmphz/am8vm4Phy8E+P2HzB5WRgPAxiYqY8nT6G/tYGRLB6NOGcnu\nwdZw8ggOlBfT9M4mxriiOIVqdrDu8K4yXcBVJA+9zZGCPhzLuQvVvggbl8CbM+BvCyBRB1t/BC+9\nBqtvhi/cDwvHw5YfwPLuz7kPzpwLr6deNtwChR+DrwKUQvs/wM/Lc/D7VdxBvLM0c1dMf/MFvtgR\np6JAiF9zCr+sq6b11qf4L3tbqUOgsoS9/2MeDwH8YRMnL6vnahHiAomrpvHwhJrD2zkPyk2z+OV9\nL/PfeJUSytjNp7mfX3ETSxiN0gWsws6MpSWqAXZORP4cW9Qsr714Ce1R2tzweLxRQeviurQX5G7Q\nRfps4AVFRNCHYx0Bt2dCpuJg7vXuunX1frC5J8AyIifoEGoLuD0TMlUHfFeQPQcLeh3zHPS4Uy6z\nEDKBqtmXe5dpdGvsfeMi6wn1IegQysggoImuEY2USiI3D8l2lqT9fTqoizQWeDERYj0hE6iiOAU1\nTbk5NrizOO1Zv52BFxIx1hMygRu3OfeuFwPYkT6E1gVeSMRYT8gEbvwmSknkVhB1QuLA0StG7tdF\ntnTHsVgImcCVdlA4akduvReai9LO83sv8EIiyMfhWE5NyjQDM31lbm2Cua/oqJ024sB6H7VETbAh\npJoAmgJt04RSdTMlwxtzpze0u+iow8sNukhzcgA+03ys77PPQ5smhE5ZmTvrSzUWHxFCXSQXtDPH\n5uNNsNdDmyaEhu2ltLYhN86Y7iw+4iLM13WR5vC14ZllIWS8mv0KpYXpNw2MlPVlh8+MNQJrfNYS\nNRZCxqvSDgpPf51IX1HcCYn6Ukpwg9Ev6KIgl6aIvuBDSLUD3Op2xgCM30zZmC3RPSzbUkpM3drZ\nb+ki3e+7nqjxNTBovSFzhDNWUDZ0bzTPlq0rIw5sJGX1TNN/vkLIljYwRyhQZN5zlFY2R29e2Y5i\n9gDP2WHYwAS7suLhVmU0cHXwDZuw6ygl/tKlxNvLKfFdS39UNtOuTTxywQq7JmigfPWEdkG0ByNN\ndpR2UHjOMxSUHwx/j6i6ifYFS2mxABocPyHkul/bvLRtQm9IG0Ufe4riMF9RXVfPoXN+T2lxpy3V\nMVg+r1i1EDK9KopTMP85yk5ayyE0PDPuC7qIz1lB2+xXqUhuYbTdd01R53MS4VaPbZuImL6KijHb\n6HhnHrTUUOqzlspm2ue+SGFFC2XJu+Lk2FbdPvgZmD7culwLDPNXgImSTSfR+v4sSrpKgv3jWdxB\n1/SVxCZtOGo7n02oPhNkLbnIdwjNA073V4CJms5i4utm0r51CkPi6VcyzJiiGF1T3qfjxPcoK0yk\nHbp4DtUPs1lDPvAdQuOAT/grwERVvIDEthNo33wSBZncTFHiJEY20j75A2RUA0OEXncH6QIeRLUz\nU23nK98hJMBnSO7BbsxAtJXRtXsssd1joKmWouO9xqi0ldiwvXSN3AnjN1Na3NmvHtZGVJcOsGST\nwm8IgR2SmYzrGELXgRq6WitJtJejsVLoKkKKutDiGBTHkOIYlLdQMHQfJUVdAzpLvAzV+owXn4fC\nsMTmeiyETAaVtlNU204RjVlrogvYkrWvnmf8r2ynug+b0GqiZTOqPdeUNgPkP4Sc930XYMxxsL3E\nMigsIfQBHLVbgTFh1ISqXe2fQeEIIdUYYIN8JgpW+y4g14QjhBxbl9eEXTuu124yKDwhpLoHm09m\nwu1dVCO/KH/YhCeEHNuryYRVHFgbREMisklELurlsfNFJJAxKRFZKyLnJz++R0QeykY7YbhO6E9U\nGxFpAMb5LsXkpmr4361QLZAohth0WP0Y/GoCdEyErzTAlAIXODoMdp0Nb/4rLBsG61BtE5F7gL+F\nIxZd+4aqfqdnWyKyCRjNkVufT1XVhiy+xOMiIr/AzVpIXWTwv6rqI6p6ahA1hK0nBNYbMln2Dfhh\nJ9y6FL61BSbfDJd3P3YT/DIGt26Cr94Ov14Bc+fAlw8cOSD9iKpWptyOCqAUV/Z4rrcAEpHeOh3f\n6VHjI0HWFb4Qcj8kW63OZN0C2D8T1myB8T0fGwexr8L6R+FHW+GEGpifybZF5Krk4c5+EXleRE7p\n5XllIvILEWkSkXeBuT0eHycivxWR3SKyUURuTXnsHhH5jYg8JCIHgJuPs8a+DgvniciKZP0ruw/b\nko/dLCL1InIwWdMNfbUTvhByrDdksu5FGLYKZk7pYwrG+bBb4A3g3Ey1KyJTgV8CtwG1wJPA4yKS\nbuLtIuDE5O0S4HMpX6cAeBxYiQvSC4HbROSSlM+/GvgNMBR4OEP1jweeAL4FDAduB34rIrUiUgF8\nH7hMVatw4d3nVkjhDCF3Mdgu32WY3LQIbimF730C/udUWP8gPNXH09fE3VLEw1Puuy7ZA+i+9TWG\nuSTleUuS910PPKGqS9UtBfJdoIz0va3rgHtVdZ+qbsX9gnebC9Sq6jdUNaZuQu1Pgb9Iec7LqrpE\nVROq2tua3ben1Linj9fS7UbgSVV9Mvl1l+KCuvuwNgGcJiJlqrpDVfsc0A/XwPSRVgALfRdhcs9i\n+PGd/Zsq1A68jetlrEi5/1FVvbGfzS1U1WU97hsHbO7+j6omRGQraQ4Lk89NvXRlc8rHk4BxIpK6\n62sh8FLK//tz2ct3VfWufjwvtd1rReTKlPuKgT+o6iERuR7XO/q5iCwHvqKqvX6/w9kTAlDdhc0p\nM369Ke7s1kc48hd7sBpwv8gAiFtXawLpF83fkXys28SUj7cCG1V1aMqtSlUvT3lONtbq2Qo82KPd\nClX9OwBV/b2qXgyMxf0O/7SvLxbeEHJew/01MiZQ26C1xI3XPIZ7Hz6ZwS//KHCFiFwoIsXAV3Cn\n/Ff08tyvi8gwEakDvpzy2GvAARG5IzmAXSgip4nI3DRfJ5MeAq4UkUuSbQ5JXr9UJyKjk4PuFcnX\n1MKRlygcJdwhpNqO+0YbE4gH4NMl8P2J8M+d8I/Ab4FLVTWRqTZUdR1uXOUHwB7gStyp/HQbgi7G\nHYJtBJ4BHkz5OvHk585OPr4H+BlQk6lae6l/K27A+07clu5bga/i8qQAF6oNwD7gPOCWvr6e/5UV\n+0NkITDKdxkmb2xFta/BapNB4e4J/ckfyc6xrTE9xYAXfReRT6IRQm5y67u+yzB54RVUD/kuIp9E\nI4Sc14Bm30WYnLaNPk4lm+yITgi5i7qe5Rgj7cYMUDvwvO8i8lF0Qgi6D8te9V2GyUkvodrqu4h8\nFK0QAlBdA2zyXYbJKetQ3ei7iHwVvRByXsBdBGXMYO0DlvsuIp9FM4RUO3DjQxm7gMzkpXbgadtD\nzK9ohhC4VRjdzF1jBiIBPIOq9ag9i24IAai+g+1+YAbmJVRt8bwQiHYIOS/gZhob01+rcfO3TAhE\nP4TcxMJngP3HeqoxuAXKXvFdhPmT6IcQdA9UPwXYdR6mL03AMiIxazt/5EYIAagexK350nGsp5q8\n1AT8B+mXyzAe5U4IAajuA54G7JSrSbUfF0C9rbFsPMqtEILuU/dPAZ2+SzGhYAEUctFY1GwgRGqB\ny4Ahvksx3jQDj9ucsHDL3RACEBkGXAGU+y7FBM4CKCJyO4QARKpwQVTtuxQTmCbgSVucLBpyP4QA\nRMpxQTTMdykm67bhTsPbWbCIyI8QAhAZAlyKLZify9YAL9t1QNGSPyEEIFIILACm+y7FZFQCWIGq\nrUMeQfkVQt1EpgHn4LbMNdEWwx1+bfNdiBmY/AwhAJGRwMVAle9SzIAdwK0HZPMGIyx/QwhApBT4\nOEfu9W2i4UPgjzYAHX35HUIAIgJ8BJgDiOdqzLF14sLH1pHKERZC3URG4fbNttP44bUTeB7VA74L\nMZljIZRKpADXI5pDLs6ri6448DpuMTJ7w+YYC6F03HSP87BrisKgEXjBBp9zl4VQb9xY0anAmUCR\n52ryUQvwGqof+i7EZJeF0LGIVALzgCm+S8kTXcA7wCrbiic/WAj1lxu4PgsY67uUHLYe1/uxme95\nxELoeIlMBD4KjPRdSg7ZgZvztcd3ISZ4FkIDJTIJd32RhdHAKLAZWJlcDdPkKQuhwXI9o9OAOt+l\nREQX7rBrNarNvosx/lkIZYpINXAKMA1bUjaddmAtsBbVdt/FmPCwEMo0t1zIFGAGMNpzNb4psB03\nz6veznaZdCyEsklkBK5nNBmo9FtMoBqBDcAG2+XCHIuFUFBcIE3CBVIuDmbvw/V4NiQ3ojSmXyyE\nfHAXQHYH0liiOU+tA2jAHW5ts0mlZqAshHxzY0gjgVrcXLVRhHNnkFbcLPbu216bTGoywUIojNxi\na92BNBIXSlUEM4ftEG7X0ubkv/uBJts+x2SLhVCUuK2LqoCKlFs5LpwKe9wKUj5W3FrMHSn/dn8c\nw50+d6Gjattnm0BZCBljvIrigKgxJodYCBljvLIQMsZ4ZSFkjPHKQsgY45WFkDHGKwuhXojIEBFR\nEUm7TpCIfFFElmW4zVIRaRGRccn//0pE7spkG8aETaAhJCKbRKQt+YvWJCJPiMiEHs+ZLyLPichB\nEWkWkcdFZEbK4+eLSCL5NVJvZ6dpL/XxRErbLSJyQxCvOR0ReUVE2nvUN0dVO1S1UlUbfNVmTNB8\n9ISuVNVK3MTNRuAH3Q8kg+QZ4DFgHHACsBJYLiKpu100JH9ZU28v92wo9XFgS3fbydvD2XuJ/fL5\nHvW/7bkeY7zwdjimbnW93+AW/+r2HeABVf0nVT2oqvtU9S7gFeCeTNcgIgtE5NVkj6tBRP5RRHrO\nz1qY7MHtFpF7xe1Hlu5rnZbswTWJyHsisnAA9RzrEPCTIrJKRPaLyEs9eoj/S0R2iMiBZPvnHm/7\nxvjgLYTEzYO6Hhcw3f+fD/w6zdMfBS7OQhmdwJeA4cC5wJXA53s850pgNm4TxE8DRx3GiVvadSnw\nc9yE05uAfxGRkzJVqIjMA34M/CUwAngQWCIiRSIyK3n/bKAGuALYlqm2jckmHyG0RET2AwdwwfJ/\nk/cPT9azI83n7ODIhcDGJXsDqbeK4y1EVV9T1ddVNa6qG4Cf4bZ/TvVtVd2vqhuBH+KCqKdPAmtU\n9eHk13odeBz4VB/N35dS+4p+lPvXwA9V9c1kGz8BSnE7fnQBZbheZaGq1ifrNSb0fITQQlUdivsF\n+hLwgoiMAZqABOk3FxwLpO5J1aCqQ3vcjnupCRGZISJPiUijiBwA7uboVQ+3pny8GTdW1dMk4GOp\noYgLoL42SvzrlNrn96PcScCdPdqoBcar6lrga8C9wC4ReVhE8n19axMRPseE4qr6OyAOnJMMkZeB\na9M8/Trg2SyU8VPgLeBEVa0GvgH0HPNJPXs3EbeaYE9bgWd6hGKlqt6WwVq3Anf3aKM8+T1EVe9P\nhtkU3G4f38pg28Zkjc8xIRGRq4FhwHvJu78GfE5EbhWRKhEZJiLfAs4GFmehjCqgWVVbRORU4Atp\nnnOHiNSIyGRcz+2RNM9ZAswRketFpFhESkRknohMzWCtPwG+LCIfTX7vKkXkKhEpT/bozhO3GFpb\n8hbPYNvGZI2PEHpcRFpwY0L3Ap9LHk6gqn8ELgGuwY0DbQbm4HpKH6R8jXFprhPqa/ylN/8d+Hyy\nnh+RPmCewF0m8AZu0Pyhnk9Q1aZk3X+ZrLsB1xMpHkBNaanqcuBW4D7caofrgc/gFiwrA/4ed8i6\nA7ezx92ZatuYbLJFzYwxXtm0DWOMVxZCxhivLISMMV5ZCBljvLIQMsZ4ZSFkjPHKQsgY45WFkDHG\nKwshY4xX/wmPMoP05K4ERQAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Get Venn Diagram package\n",
    "from matplotlib import pyplot as plt\n",
    "from matplotlib_venn import venn2, venn2_circles\n",
    "\n",
    "# Grab all PDFs within the folder\n",
    "files = glob.glob('data\\TrafficCountData\\Manual\\Original\\*.pdf')\n",
    "file_names = [os.path.basename(file) for file in files]\n",
    "\n",
    "# Create df from filenames / paths\n",
    "pdf_df = pd.DataFrame(\n",
    "    {'path':files,\n",
    "     'filename':file_names\n",
    "    })\n",
    "\n",
    "# Join pdf_df to BOE tables\n",
    "traffic_data_files_leftjoin = dbo_dot_traffic_data_files.merge(pdf_df, how='left', left_on='DocName', right_on='filename')\n",
    "traffic_data_files_innerjoin = dbo_dot_traffic_data_files.merge(pdf_df, how='inner', left_on='DocName', right_on='filename')\n",
    "traffic_data_files_rightjoin = dbo_dot_traffic_data_files.merge(pdf_df, how='right', left_on='DocName', right_on='filename')\n",
    "\n",
    "### Create Venn Diagram showing differences\n",
    "\n",
    "# Subset sizes\n",
    "s = (\n",
    "    (len(traffic_data_files_leftjoin) - len(traffic_data_files_innerjoin)),  # BOE Table only count\n",
    "    (len(traffic_data_files_rightjoin)-len(traffic_data_files_innerjoin)),  # PDF Folder only count\n",
    "    len(traffic_data_files_innerjoin),  # Joined Files count\n",
    ")\n",
    "\n",
    "# Subset labels\n",
    "v = venn2(subsets=s, set_labels=('BOE Table Files', 'PDF Folder Files'))\n",
    "\n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As the Venn Diagram makes obvious, there is quite a difference. Of the 1,018 PDF files I started with, I was able to match 955 (~94%) of them to a row in the BOE table. Roughly 6% of the files (63) were unable to be matched in the BOE table.\n",
    "\n",
    "However, it looks like the folder of Manual PDF counts that I was initially pulling from contained only about 10% (1018 of the 9034 total) of the manual count files in BOE's NavigateLA system. We don't have to give up here. Since NavigateLA hosts the PDFs online (and they are public), we can go ahead and download them from the NavigateLA website to fill out our set of Manual Count PDFs. We will do this by (1) looping through all the names in the BOE table that do not have a match and (2) using the python urllib library to pull the PDFs to a new folder.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Step 2: Download the PDF documents (prep)\n",
    "This script loops through my table of document names and downloads the PDFs from the web. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Finished downloading files.\n"
     ]
    }
   ],
   "source": [
    "import urllib\n",
    "import time\n",
    "from pathlib2 import Path\n",
    "\n",
    "# Location to where I will be downloading the files\n",
    "folder = \"data/TrafficCountData/Manual/All/\"\n",
    "\n",
    "# Base URL \n",
    "base_url = \"http://boemaps.eng.ci.la.ca.us/dot/traffic_data/manual_counts/\"\n",
    "\n",
    "# Get the filenames from the BOE table that don't yet have a matching PDF\n",
    "traffic_data_files_missing = traffic_data_files_leftjoin[(~traffic_data_files_leftjoin.DocName.isin(traffic_data_files_innerjoin.DocName))&(~traffic_data_files_leftjoin.filename.isin(traffic_data_files_innerjoin.filename))]\n",
    "\n",
    "# Loop through resulting rows\n",
    "for index, row in traffic_data_files_missing.iterrows():\n",
    "    \n",
    "    # Filename, URL, Location\n",
    "    filename = row['DocName']\n",
    "    url = base_url + filename\n",
    "    download_location = folder + filename\n",
    "    \n",
    "    # If it is already in the folder from first round of NavLA downloads, skip\n",
    "    if Path(download_location).is_file():\n",
    "        pass\n",
    "    \n",
    "    # Otherwise, try to download\n",
    "    else:\n",
    "        \n",
    "        try:\n",
    "            # Download to folder\n",
    "            urllib.request.urlretrieve(url, download_location)\n",
    "\n",
    "            # To Not overwhelm the server\n",
    "            time.sleep(.2)\n",
    "        \n",
    "        except:\n",
    "            raise\n",
    "            #pass\n",
    "\n",
    "print(\"Finished downloading files.\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### [Update 1/7/18] Step 3: Scrape NavLA for missing document names\n",
    "One of the issues I realized only later was that the initial table of documents provided by BOE for the volume count data was incomplete, and there were more documents hosted on Navigate LA than revealed in that table. I wanted to fill out the difference. The first thing I did to get a more comprehensive listing of the documents on Navigate LA was run a small web crawler to loop through Traffic IDs (generated by BOE) and grab fileneames from each one.\n",
    "\n",
    "The script below scrapes the NavLA site, scraping the following pieces of information:\n",
    "\n",
    "* intersection: The name of the intersection / location\n",
    "* node_id: The Bureau of Engineering CL_NODE_ID that corresponds to that location\n",
    "* automatic: A list of filenames for automatic count PDF documents \n",
    "* manual: A list of filenames for manual count PDF documents\n",
    "\n",
    "For someone without the benefit of the BOE tables given ahead of time, that person could start at this step and continue to replicate the rest of the work. The only piece of this query that needs to be updated is the max value for the Traffic ID, which I found by just manually incrementing up from around 9,000 to max here, which is 9,434 (that number will probably continue to increase as time goes on) The final result is a json dump, which will will then import later and use for joining to the BOE intersection and centerline file later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "### Scrape NavLA for All Count Document Names\n",
    "### and save to JSON .txt file\n",
    "from lxml import html, etree\n",
    "import requests\n",
    "import time\n",
    "import json\n",
    "import urllib\n",
    "\n",
    "Count_data = []\n",
    "\n",
    "# Base URL to use for the web scraping\n",
    "base_url = \"http://boemaps.eng.ci.la.ca.us/reports/dot_traffic_data_report.cfm?trafficid=\"\n",
    "\n",
    "parser = etree.HTMLParser(remove_blank_text=True,encoding='utf-8')\n",
    "\n",
    "# Loop through integers, grab all data in range\n",
    "for traffic_id in range(1,9434):\n",
    "    \n",
    "    # Metering the requests\n",
    "    time.sleep(.1)\n",
    "    \n",
    "    # The dictionary to hold the final data\n",
    "    d = {}\n",
    "    \n",
    "    # Make the request\n",
    "    url = base_url + str(traffic_id)\n",
    "    page = requests.get(url)\n",
    "    tree = etree.HTML(page.content)\n",
    "    result = etree.tostring(tree,method='html')\n",
    "    \n",
    "    # Parse the data\n",
    "    table_info = tree.xpath('//td[@class=\"tablerecord\"]/text()')\n",
    "    if len(table_info) > 0:\n",
    "\n",
    "        # Parse the Node ID\n",
    "        node_id = tree.xpath('//td[@class=\"tablerecord\"]/text()')[0]\n",
    "        node_id = node_id.replace(u'\\xa0', u'')\n",
    "\n",
    "        # Parse the Intersection Name\n",
    "        intersection = tree.xpath('//td[@class=\"tablerecord\"]/text()')[1]\n",
    "        intersection = intersection.replace(u'\\r\\n\\t\\t\\t\\t\\r\\n\\t\\t\\t\\t\\t', u'')\n",
    "        intersection = intersection.replace(u'\\xa0\\r\\n\\t\\t\\t\\t\\r\\n\\t\\t\\t',u'')\n",
    "\n",
    "        ##### Automatic Counts\n",
    "        # Select the table that contains the automatic counts\n",
    "        automatic_count_table = tree.xpath('//b[text()=\"Automatic Count\"]/../../..')\n",
    "        \n",
    "        # If there is at least automatic count PDF, grab filenames\n",
    "        if len(automatic_count_table) > 0:\n",
    "            automatic1 = etree.tostring(automatic_count_table[0])\n",
    "            automatic_count_table = etree.XML(automatic1, parser=parser)\n",
    "            automatic_count_pdfs = automatic_count_table.xpath('//a/text()')\n",
    "            d['automatic'] = automatic_count_pdfs\n",
    "        else:\n",
    "            d['automatic'] = []\n",
    "\n",
    "        ##### Manual Counts\n",
    "        # Select the table that contains manual counts\n",
    "        manual_count_table = tree.xpath('//b[text()=\"Manual Count\"]/../../..')\n",
    "\n",
    "        # If there is at least manual count PDF, grab filenames\n",
    "        if len(manual_count_table) > 0:\n",
    "            manual1 = etree.tostring(manual_count_table[0])\n",
    "            manual_count_table = etree.XML(manual1, parser=parser)\n",
    "            manual_count_pdfs = manual_count_table.xpath('//a/text()')\n",
    "            d['manual'] = manual_count_pdfs\n",
    "        else:\n",
    "            d['manual'] = []\n",
    "\n",
    "        d['node_id'] = node_id\n",
    "        d['traffic_id'] = traffic_id\n",
    "        d['intersection'] = intersection\n",
    "        Count_data.append(d)\n",
    "\n",
    "with open('data/TrafficCountFileStructure/navLAdump.txt', 'w') as outfile:\n",
    "    json.dump(Count_data, outfile)\n",
    "\n",
    "print(\"Complete.\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### [Update 1/7/18] Step 4: Complete the Library of Manual Count PDFs\n",
    "For this exercise, I wanted to collect the rest of the PDF files from the internet. I'll start by looking at the documents that I was able to pull from the web scrape exercise, join it with a table listing everything I have, and get the difference. From there, I can query NavigateLA by the PDF name and pull it down to my list of flies.\n",
    "\n",
    "Now that we have the data pulled from Navigate LA stored in navLAdump.txt, let's load it. I'll make some slight modificatons to the json data structure before we can bring it into a pandas data frame and join to the rest of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 26840 files on NavigateLA. Of these, 9460 files are manual counts.\n"
     ]
    }
   ],
   "source": [
    "### Convert the JSON .txt file into a pandas dataframe for analysis\n",
    "import json\n",
    "import pandas as pd\n",
    "\n",
    "# Lcoation of the json file\n",
    "traffic_count_file = 'data/TrafficCountFileStructure/navLAdump.txt'\n",
    "\n",
    "# Load json\n",
    "with open(traffic_count_file) as f:\n",
    "    traffic_count_files = json.load(f)\n",
    "\n",
    "# New list data structure\n",
    "count_files = []\n",
    "count_files_labels = ['cl_node_id','location','traffic_id','type','file']\n",
    "\n",
    "for location in traffic_count_files:\n",
    "    \n",
    "    # New list for each count, add intersection infomration & node id\n",
    "    count = []\n",
    "    node_id = location['node_id']\n",
    "    loc = location['intersection']\n",
    "    traffic_id = location['traffic_id']\n",
    "    \n",
    "    # Add a row for each manual count\n",
    "    if len(location['manual']) > 0:\n",
    "        for man in location['manual']:\n",
    "            man2 = man + '.PDF'\n",
    "            count = [node_id, loc, traffic_id, 'manual', man2]\n",
    "            count_files.append(count)\n",
    "    \n",
    "    # Add a row for each automatic count\n",
    "    if len(location['automatic']) > 0:\n",
    "        for auto in location['automatic']:\n",
    "            auto2 = auto + '.PDF'\n",
    "            count = [node_id, loc, traffic_id, 'automatic', auto2]\n",
    "            count_files.append(count)\n",
    "\n",
    "# Create the new dataframe\n",
    "count_files_df = pd.DataFrame.from_records(count_files, columns=count_files_labels)\n",
    "count_files_df.insert(0,'count_id',range(1,1+len(count_files_df)))\n",
    "\n",
    "print(\"There are \" + str(len(count_files_df)) + \" files on NavigateLA. Of these, \" + str(len(count_files_df[(count_files_df['type'] == 'manual')])) + ' files are manual counts.')\n",
    "count_files_df.head()\n",
    "\n",
    "# Write out the csv to a file\n",
    "count_files_df.to_csv(path_or_buf='data/TrafficCountFileStructure/navLAfiles.csv',\n",
    "                      index=False,\n",
    "                      sep=',')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that I have this updated list of files from Navigate LA, I can get the difference between this list and what I have downloaded already (located in data/TrafficCountData/Manual/All/). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 9522 files in the Manual Count folder.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>filename</th>\n",
       "      <th>path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00000001.PDF</td>\n",
       "      <td>data/TrafficCountData/Manual/All\\00000001.pdf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1-2-3-CALRIV01.PDF</td>\n",
       "      <td>data/TrafficCountData/Manual/All\\1-2-3-calriv0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10018_GRA27S94.PDF</td>\n",
       "      <td>data/TrafficCountData/Manual/All\\10018_GRA27S9...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10041_GRA35S02.PDF</td>\n",
       "      <td>data/TrafficCountData/Manual/All\\10041_gra35s0...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1009_GAGGRA02.PDF</td>\n",
       "      <td>data/TrafficCountData/Manual/All\\1009_gaggra02...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             filename                                               path\n",
       "0        00000001.PDF      data/TrafficCountData/Manual/All\\00000001.pdf\n",
       "1  1-2-3-CALRIV01.PDF  data/TrafficCountData/Manual/All\\1-2-3-calriv0...\n",
       "2  10018_GRA27S94.PDF  data/TrafficCountData/Manual/All\\10018_GRA27S9...\n",
       "3  10041_GRA35S02.PDF  data/TrafficCountData/Manual/All\\10041_gra35s0...\n",
       "4   1009_GAGGRA02.PDF  data/TrafficCountData/Manual/All\\1009_gaggra02..."
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Inventory PDFs & Get difference with NavLA scrape\n",
    "# Grab all PDFs within the folder\n",
    "files = glob.glob('data/TrafficCountData/Manual/All/*.pdf')\n",
    "file_names = [os.path.basename(file) for file in files]\n",
    "\n",
    "# Create df from filenames / paths\n",
    "pdf_df = pd.DataFrame(\n",
    "    {'path':files,\n",
    "     'filename':file_names\n",
    "    })\n",
    "\n",
    "# Uppercase the filenames for the join in the next step\n",
    "pdf_df['filename'] = pdf_df['filename'].str.upper()\n",
    "\n",
    "print(\"There are \" + str(len(pdf_df)) + \" files in the Manual Count folder.\")\n",
    "pdf_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 3 files that are missing and need to be downloaded.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_id</th>\n",
       "      <th>cl_node_id</th>\n",
       "      <th>location</th>\n",
       "      <th>traffic_id</th>\n",
       "      <th>type</th>\n",
       "      <th>file</th>\n",
       "      <th>filename</th>\n",
       "      <th>path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>47</td>\n",
       "      <td>4279</td>\n",
       "      <td>95TH ST at HOOVER ST</td>\n",
       "      <td>17</td>\n",
       "      <td>manual</td>\n",
       "      <td>4279_Â­Â­HOO95S.PDF</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1801</th>\n",
       "      <td>3207</td>\n",
       "      <td>8622</td>\n",
       "      <td>HOOVER ST at PICO BLVD</td>\n",
       "      <td>812</td>\n",
       "      <td>manual</td>\n",
       "      <td>8622_Â­HOOPIC93.PDF</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2033</th>\n",
       "      <td>3643</td>\n",
       "      <td>8930</td>\n",
       "      <td>HOOVER ST AT VENICE BLVD</td>\n",
       "      <td>889</td>\n",
       "      <td>manual</td>\n",
       "      <td>8930_Â­HOOVEN99.PDF</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      count_id cl_node_id                  location  traffic_id    type  \\\n",
       "25          47       4279      95TH ST at HOOVER ST          17  manual   \n",
       "1801      3207       8622    HOOVER ST at PICO BLVD         812  manual   \n",
       "2033      3643       8930  HOOVER ST AT VENICE BLVD         889  manual   \n",
       "\n",
       "                     file filename path  \n",
       "25    4279_Â­Â­HOO95S.PDF      NaN  NaN  \n",
       "1801  8622_Â­HOOPIC93.PDF      NaN  NaN  \n",
       "2033  8930_Â­HOOVEN99.PDF      NaN  NaN  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Prepare to download remaining PDF Files from NavLA\n",
    "import urllib\n",
    "import time\n",
    "from pathlib2 import Path\n",
    "\n",
    "### Join list of PDFs to Count Files Manual Counts\n",
    "count_files_manual = count_files_df[(count_files_df['type'] == 'manual')]\n",
    "count_files_leftjoin = count_files_manual.merge(pdf_df, how='left', left_on='file', right_on='filename')\n",
    "count_files_innerjoin = count_files_manual.merge(pdf_df, how='inner', left_on='file', right_on='filename')\n",
    "\n",
    "# Location to where I will be downloading the files\n",
    "folder = \"data/TrafficCountData/Manual/All/\"\n",
    "\n",
    "# Base URL \n",
    "base_url = \"http://boemaps.eng.ci.la.ca.us/dot/traffic_data/manual_counts/\"\n",
    "\n",
    "# Get the filenames from the BOE table that don't yet have a matching PDF\n",
    "count_files_missing = count_files_leftjoin[(~count_files_leftjoin.file.isin(count_files_innerjoin.file))&(~count_files_leftjoin.filename.isin(count_files_innerjoin.filename))]\n",
    "print('There are ' + str(len(count_files_missing)) + ' files that are missing and need to be downloaded.')\n",
    "count_files_missing.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "downloading 4279_Â­Â­HOO95S.PDF\n",
      "   failed to download4279_Â­Â­HOO95S.PDF\n",
      "downloading 8622_Â­HOOPIC93.PDF\n",
      "   failed to download8622_Â­HOOPIC93.PDF\n",
      "downloading 8930_Â­HOOVEN99.PDF\n",
      "   failed to download8930_Â­HOOVEN99.PDF\n",
      "Finished downloading from NavigateLA\n"
     ]
    }
   ],
   "source": [
    "### Download Remaining Manual Count PDFs\n",
    "# Loop through resulting rows\n",
    "for index, row in count_files_missing.iterrows():\n",
    "    \n",
    "    # Filename, URL, Location\n",
    "    filename = row['file']\n",
    "    \n",
    "    # Encode string into ASCII set for url\n",
    "    #filename = urllib.parse.quote(filename)\n",
    "    url = base_url + urllib.parse.quote(filename)\n",
    "    download_location = folder + filename\n",
    "    \n",
    "    # If it is already in the folder from first round of NavLA downloads, skip\n",
    "    if Path(download_location).is_file():\n",
    "        pass\n",
    "    \n",
    "    # Otherwise, try to download\n",
    "    else:\n",
    "        \n",
    "        print('downloading ' + str(filename))\n",
    "        \n",
    "        try:\n",
    "            # Download to folder\n",
    "            urllib.request.urlretrieve(url, download_location)\n",
    "\n",
    "            # To Not overwhelm the server\n",
    "            time.sleep(1)\n",
    "        \n",
    "        except:\n",
    "            print('   failed to download' + str(filename))\n",
    "            pass\n",
    "\n",
    "print(\"Finished downloading from NavigateLA\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}