Toll prices 2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How much do drivers pay for tolls across the country?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data source\n",
    "\n",
    "FHWA - https://www.fhwa.dot.gov/policyinformation/tollpage/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State</th>\n",
       "      <th>Name of Facility</th>\n",
       "      <th>HPMS Toll ID</th>\n",
       "      <th>Financing or Operating Authority</th>\n",
       "      <th>From</th>\n",
       "      <th>To</th>\n",
       "      <th>Length 1/ Miles</th>\n",
       "      <th>Kilometers</th>\n",
       "      <th>Rural/Urban</th>\n",
       "      <th>Interstate Route</th>\n",
       "      <th>...</th>\n",
       "      <th>Maximum Truck Fee</th>\n",
       "      <th>Minimum Truck Fee</th>\n",
       "      <th>Average Truck Cost per Vehicle-Mile</th>\n",
       "      <th>num_Maximum Passenger Vehicle Fee</th>\n",
       "      <th>num_Minimum Passenger Vehicle Fee</th>\n",
       "      <th>num_Average Passenger Vehicle Cost per Vehicle-Mile</th>\n",
       "      <th>num_Maximum Truck Fee</th>\n",
       "      <th>num_Minimum Truck Fee</th>\n",
       "      <th>num_Average Truck Cost per Vehicle-Mile</th>\n",
       "      <th>spreadsheet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>California</td>\n",
       "      <td>I-15 Value Pricing Project</td>\n",
       "      <td>16</td>\n",
       "      <td>San Diego Assoc of Gov; CA Dept of Trans</td>\n",
       "      <td>SR 56/ Ted Williams Pkwy</td>\n",
       "      <td>SR 52</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12.87</td>\n",
       "      <td>Urban</td>\n",
       "      <td>15</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>interstate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>I-680 SMART Carpool Lanes</td>\n",
       "      <td>297</td>\n",
       "      <td>Alameda County Congestion Management Agency</td>\n",
       "      <td>SR 84/ Pleasanton</td>\n",
       "      <td>SR 237 Milpitas</td>\n",
       "      <td>14.0</td>\n",
       "      <td>22.53</td>\n",
       "      <td>Urban</td>\n",
       "      <td>680</td>\n",
       "      <td>...</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>$0.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>interstate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>California</td>\n",
       "      <td>I-880 / SR 237 Express Connector</td>\n",
       "      <td>298</td>\n",
       "      <td>Santa Clara Valley Transportation Authority</td>\n",
       "      <td>I-880</td>\n",
       "      <td>SR 237</td>\n",
       "      <td>4.0</td>\n",
       "      <td>6.44</td>\n",
       "      <td>Urban</td>\n",
       "      <td>880</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>interstate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>California</td>\n",
       "      <td>I-110 Express Lanes</td>\n",
       "      <td>313</td>\n",
       "      <td>Los Angeles County Metropolitan Transportation...</td>\n",
       "      <td>Harbor Gateway Transit Center</td>\n",
       "      <td>Adams Blvd</td>\n",
       "      <td>11.0</td>\n",
       "      <td>17.70</td>\n",
       "      <td>Urban</td>\n",
       "      <td>110</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.4</td>\n",
       "      <td>0.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>interstate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>California</td>\n",
       "      <td>I-10 Express Lanes</td>\n",
       "      <td>338</td>\n",
       "      <td>Los Angeles County Metropolitan Transportation...</td>\n",
       "      <td>Alameda St/Union Station</td>\n",
       "      <td>I-605</td>\n",
       "      <td>14.0</td>\n",
       "      <td>22.53</td>\n",
       "      <td>Urban</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>19.6</td>\n",
       "      <td>0.25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>interstate</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        State                  Name of Facility HPMS Toll ID  \\\n",
       "3  California        I-15 Value Pricing Project           16   \n",
       "4  California         I-680 SMART Carpool Lanes          297   \n",
       "5  California  I-880 / SR 237 Express Connector          298   \n",
       "6  California               I-110 Express Lanes          313   \n",
       "7  California                I-10 Express Lanes          338   \n",
       "\n",
       "                    Financing or Operating Authority  \\\n",
       "3           San Diego Assoc of Gov; CA Dept of Trans   \n",
       "4        Alameda County Congestion Management Agency   \n",
       "5        Santa Clara Valley Transportation Authority   \n",
       "6  Los Angeles County Metropolitan Transportation...   \n",
       "7  Los Angeles County Metropolitan Transportation...   \n",
       "\n",
       "                            From               To  Length 1/ Miles Kilometers  \\\n",
       "3       SR 56/ Ted Williams Pkwy            SR 52              8.0      12.87   \n",
       "4              SR 84/ Pleasanton  SR 237 Milpitas             14.0      22.53   \n",
       "5                          I-880           SR 237              4.0       6.44   \n",
       "6  Harbor Gateway Transit Center       Adams Blvd             11.0      17.70   \n",
       "7       Alameda St/Union Station            I-605             14.0      22.53   \n",
       "\n",
       "  Rural/Urban Interstate Route     ...     Maximum Truck Fee  \\\n",
       "3       Urban               15     ...                   NaN   \n",
       "4       Urban              680     ...                 $0.00   \n",
       "5       Urban              880     ...                   NaN   \n",
       "6       Urban              110     ...                   NaN   \n",
       "7       Urban               10     ...                   NaN   \n",
       "\n",
       "  Minimum Truck Fee Average Truck Cost per Vehicle-Mile  \\\n",
       "3               NaN                                 NaN   \n",
       "4             $0.00                                 NaN   \n",
       "5               NaN                                 NaN   \n",
       "6               NaN                                 NaN   \n",
       "7               NaN                                 NaN   \n",
       "\n",
       "  num_Maximum Passenger Vehicle Fee num_Minimum Passenger Vehicle Fee  \\\n",
       "3                               8.0                              0.50   \n",
       "4                               8.0                              0.30   \n",
       "5                               NaN                              0.30   \n",
       "6                              15.4                              0.25   \n",
       "7                              19.6                              0.25   \n",
       "\n",
       "  num_Average Passenger Vehicle Cost per Vehicle-Mile num_Maximum Truck Fee  \\\n",
       "3                                                NaN                    NaN   \n",
       "4                                                NaN                    0.0   \n",
       "5                                                NaN                    NaN   \n",
       "6                                                NaN                    NaN   \n",
       "7                                                NaN                    NaN   \n",
       "\n",
       "  num_Minimum Truck Fee num_Average Truck Cost per Vehicle-Mile spreadsheet  \n",
       "3                   NaN                                     NaN  interstate  \n",
       "4                   0.0                                     NaN  interstate  \n",
       "5                   NaN                                     NaN  interstate  \n",
       "6                   NaN                                     NaN  interstate  \n",
       "7                   NaN                                     NaN  interstate  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get a requested spreadsheet\n",
    "# Note that the interstate, non-interstate, and bandt\n",
    "# spreadsheets in the data folder are modified via \n",
    "# spreadsheet application to truncate some \n",
    "# non-data lines before and after the content\n",
    "\n",
    "def get_df(which=\"non-interstate\"):\n",
    "    \n",
    "    ret = pd.read_csv(\"data/\" + which + \".csv\", header=None)\n",
    "    cols = []\n",
    "    \n",
    "    row_0 = ret.iloc[0].fillna(\"\")\n",
    "    row_1 = ret.iloc[1].fillna(\"\")\n",
    "    row_2 = ret.iloc[2].fillna(\"\")\n",
    "    \n",
    "    for i in ret.columns:\n",
    "        c = \"\"\n",
    "        if row_0[i] is not None:\n",
    "            c += str(row_0[i]) + \" \"\n",
    "        if row_1[i] is not None:\n",
    "            c += str(row_1[i]) + \" \"\n",
    "        if row_2[i] is not None:\n",
    "            c += str(row_2[i]) + \" \"\n",
    "        \n",
    "        cols.append(c.strip())\n",
    "        \n",
    "    # print cols\n",
    "    \n",
    "    ret = ret.drop(0).drop(1).drop(2)#.reset_index()\n",
    "    \n",
    "    ret.columns = cols\n",
    "    \n",
    "    # cut out out blank-named cols\n",
    "    keepers = filter(lambda x: x.strip() != \"\", cols)\n",
    "    \n",
    "    ret = ret[keepers]\n",
    "\n",
    "    \n",
    "    def money_num(n):\n",
    "        if type(n) != str:\n",
    "            return\n",
    "        return n.replace(\"$\",\"\")\n",
    "    \n",
    "    def money_col(c):\n",
    "        ret[\"num_\" + c] = pd.to_numeric(ret[c].apply(money_num), errors=\"coerce\")\n",
    "\n",
    "    for mc in [\"Maximum Passenger Vehicle Fee\",\"Minimum Passenger Vehicle Fee\",\n",
    "    \"Average Passenger Vehicle Cost per Vehicle-Mile\",\n",
    "    \"Maximum Truck Fee\",\n",
    "    \"Minimum Truck Fee\",\n",
    "    \"Average Truck Cost per Vehicle-Mile\"]:\n",
    "        money_col(mc)\n",
    "    \n",
    "    ret[\"State\"] = ret[\"State\"].str.replace(\" 3/\",\"\") # Would be better to use regex\n",
    "    ret[\"State\"] = ret[\"State\"].str.replace(\" 5/\",\"\") # but... working fast\n",
    "    ret[\"State\"] = ret[\"State\"].str.replace(\" 7/\",\"\")\n",
    "\n",
    "    ret[\"State\"] = ret[\"State\"].str.strip()\n",
    "    ret[\"Length 1/ Miles\"] = pd.to_numeric(ret[\"Length 1/ Miles\"],errors=\"coerce\")\n",
    "    \n",
    "    \n",
    "    ret[\"spreadsheet\"] = which\n",
    "    return ret\n",
    "\n",
    "get_df(which=\"interstate\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "interstate 106\n",
      "non interstate 138\n",
      "bandt 112\n"
     ]
    }
   ],
   "source": [
    "# get frame for each of the three types of tolls\n",
    "\n",
    "interstate = get_df(which=\"interstate\")\n",
    "print \"interstate\", len(interstate)\n",
    "non_interstate = get_df(which=\"non-interstate\")\n",
    "print \"non interstate\",len(non_interstate)\n",
    "bandt = get_df(\"bandt\")\n",
    "print \"bandt\", len(bandt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# combine all into one big \"all_df\"\n",
    "frames = [bandt,interstate,non_interstate]\n",
    "all_df = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# combine just the highway frames \n",
    "highway_df = pd.concat([interstate,non_interstate])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Look at the average price per mile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    164.000000\n",
       "mean       0.149756\n",
       "std        0.227294\n",
       "min        0.020000\n",
       "25%        0.050000\n",
       "50%        0.100000\n",
       "75%        0.170000\n",
       "max        2.200000\n",
       "Name: num_Average Passenger Vehicle Cost per Vehicle-Mile, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "highway_df[\"num_Average Passenger Vehicle Cost per Vehicle-Mile\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    79.000000\n",
       "mean      0.100253\n",
       "std       0.089743\n",
       "min       0.020000\n",
       "25%       0.050000\n",
       "50%       0.060000\n",
       "75%       0.130000\n",
       "max       0.510000\n",
       "Name: num_Average Passenger Vehicle Cost per Vehicle-Mile, dtype: float64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "interstate[\"num_Average Passenger Vehicle Cost per Vehicle-Mile\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    85.000000\n",
       "mean      0.195765\n",
       "std       0.297195\n",
       "min       0.030000\n",
       "25%       0.080000\n",
       "50%       0.130000\n",
       "75%       0.190000\n",
       "max       2.200000\n",
       "Name: num_Average Passenger Vehicle Cost per Vehicle-Mile, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "non_interstate[\"num_Average Passenger Vehicle Cost per Vehicle-Mile\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    206.000000\n",
       "mean       5.478689\n",
       "std        7.005890\n",
       "min        0.350000\n",
       "25%        1.420000\n",
       "50%        3.000000\n",
       "75%        6.995000\n",
       "max       40.000000\n",
       "Name: num_Maximum Passenger Vehicle Fee, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "highway_df[\"num_Maximum Passenger Vehicle Fee\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    208.000000\n",
       "mean       1.968942\n",
       "std        3.307094\n",
       "min        0.000000\n",
       "25%        0.500000\n",
       "50%        0.790000\n",
       "75%        1.962500\n",
       "max       28.000000\n",
       "Name: num_Minimum Passenger Vehicle Fee, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "highway_df[\"num_Minimum Passenger Vehicle Fee\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Average Passenger Vehicle Cost per Vehicle-Mile</th>\n",
       "      <th>Average Truck Cost per Vehicle-Mile</th>\n",
       "      <th>Both Ways</th>\n",
       "      <th>Electronic Toll Collection System? 2/ No</th>\n",
       "      <th>Electronic Toll Collection System? 4/ No</th>\n",
       "      <th>Federal Authority Source</th>\n",
       "      <th>Fee Type</th>\n",
       "      <th>Financing or Operating Authority</th>\n",
       "      <th>From</th>\n",
       "      <th>Functional  Classification 2/</th>\n",
       "      <th>...</th>\n",
       "      <th>To</th>\n",
       "      <th>Toll Collection One-Way (N,S,E,W)</th>\n",
       "      <th>Yes/Kind</th>\n",
       "      <th>num_Average Passenger Vehicle Cost per Vehicle-Mile</th>\n",
       "      <th>num_Average Truck Cost per Vehicle-Mile</th>\n",
       "      <th>num_Maximum Passenger Vehicle Fee</th>\n",
       "      <th>num_Maximum Truck Fee</th>\n",
       "      <th>num_Minimum Passenger Vehicle Fee</th>\n",
       "      <th>num_Minimum Truck Fee</th>\n",
       "      <th>spreadsheet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>$0.39</td>\n",
       "      <td>$0.39</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>X</td>\n",
       "      <td>None (not a Federal-aid toll facility)</td>\n",
       "      <td>Fixed: Rate does not vary by time of day or tr...</td>\n",
       "      <td>City of Colorado Springs operates under a Term...</td>\n",
       "      <td>Town of Cascade, US 24 west of Colorado Springs</td>\n",
       "      <td>local</td>\n",
       "      <td>...</td>\n",
       "      <td>Top of Pikes Peak Mountain -14, 110' elevation</td>\n",
       "      <td>W or UP :-)</td>\n",
       "      <td>Booth</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.39</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>10.00</td>\n",
       "      <td>10.0</td>\n",
       "      <td>non-interstate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>$0.05</td>\n",
       "      <td>$0.24</td>\n",
       "      <td>X</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None (not a Federal-aid toll facility)</td>\n",
       "      <td>Fixed: Rate does not vary by time of day or tr...</td>\n",
       "      <td>NY State Thruway Authority</td>\n",
       "      <td>Pennsylvania Line</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Albany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E-ZPass</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.24</td>\n",
       "      <td>33.7</td>\n",
       "      <td>137.9</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.4</td>\n",
       "      <td>interstate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>$0.05</td>\n",
       "      <td>$0.24</td>\n",
       "      <td>X</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None (not a Federal-aid toll facility)</td>\n",
       "      <td>Fixed: Rate does not vary by time of day or tr...</td>\n",
       "      <td>NY State Thruway Authority</td>\n",
       "      <td>Pennsylvania Line</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Albany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E-ZPass</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.24</td>\n",
       "      <td>33.7</td>\n",
       "      <td>137.9</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.4</td>\n",
       "      <td>interstate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>$0.05</td>\n",
       "      <td>$0.24</td>\n",
       "      <td>X</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None (not a Federal-aid toll facility)</td>\n",
       "      <td>Fixed: Rate does not vary by time of day or tr...</td>\n",
       "      <td>NY State Thruway Authority</td>\n",
       "      <td>Exit B1 (US 9)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Massachusetts Line</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E-ZPass</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.24</td>\n",
       "      <td>33.7</td>\n",
       "      <td>137.9</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.4</td>\n",
       "      <td>interstate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>$0.05</td>\n",
       "      <td>$0.24</td>\n",
       "      <td>X</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None (not a Federal-aid toll facility)</td>\n",
       "      <td>Fixed: Rate does not vary by time of day or tr...</td>\n",
       "      <td>NY State Thruway Authority</td>\n",
       "      <td>New York City</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Albany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>E-ZPass</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.24</td>\n",
       "      <td>33.7</td>\n",
       "      <td>137.9</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.4</td>\n",
       "      <td>interstate</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Average Passenger Vehicle Cost per Vehicle-Mile  \\\n",
       "12                                           $0.39   \n",
       "48                                           $0.05   \n",
       "47                                           $0.05   \n",
       "51                                           $0.05   \n",
       "49                                           $0.05   \n",
       "\n",
       "   Average Truck Cost per Vehicle-Mile Both Ways  \\\n",
       "12                               $0.39       NaN   \n",
       "48                               $0.24         X   \n",
       "47                               $0.24         X   \n",
       "51                               $0.24         X   \n",
       "49                               $0.24         X   \n",
       "\n",
       "   Electronic Toll Collection System? 2/ No  \\\n",
       "12                                      NaN   \n",
       "48                                      NaN   \n",
       "47                                      NaN   \n",
       "51                                      NaN   \n",
       "49                                      NaN   \n",
       "\n",
       "   Electronic Toll Collection System? 4/ No  \\\n",
       "12                                        X   \n",
       "48                                      NaN   \n",
       "47                                      NaN   \n",
       "51                                      NaN   \n",
       "49                                      NaN   \n",
       "\n",
       "                  Federal Authority Source  \\\n",
       "12  None (not a Federal-aid toll facility)   \n",
       "48  None (not a Federal-aid toll facility)   \n",
       "47  None (not a Federal-aid toll facility)   \n",
       "51  None (not a Federal-aid toll facility)   \n",
       "49  None (not a Federal-aid toll facility)   \n",
       "\n",
       "                                             Fee Type  \\\n",
       "12  Fixed: Rate does not vary by time of day or tr...   \n",
       "48  Fixed: Rate does not vary by time of day or tr...   \n",
       "47  Fixed: Rate does not vary by time of day or tr...   \n",
       "51  Fixed: Rate does not vary by time of day or tr...   \n",
       "49  Fixed: Rate does not vary by time of day or tr...   \n",
       "\n",
       "                     Financing or Operating Authority  \\\n",
       "12  City of Colorado Springs operates under a Term...   \n",
       "48                         NY State Thruway Authority   \n",
       "47                         NY State Thruway Authority   \n",
       "51                         NY State Thruway Authority   \n",
       "49                         NY State Thruway Authority   \n",
       "\n",
       "                                               From  \\\n",
       "12  Town of Cascade, US 24 west of Colorado Springs   \n",
       "48                                Pennsylvania Line   \n",
       "47                                Pennsylvania Line   \n",
       "51                                   Exit B1 (US 9)   \n",
       "49                                    New York City   \n",
       "\n",
       "   Functional  Classification 2/       ...        \\\n",
       "12                         local       ...         \n",
       "48                           NaN       ...         \n",
       "47                           NaN       ...         \n",
       "51                           NaN       ...         \n",
       "49                           NaN       ...         \n",
       "\n",
       "                                                To  \\\n",
       "12  Top of Pikes Peak Mountain -14, 110' elevation   \n",
       "48                                          Albany   \n",
       "47                                          Albany   \n",
       "51                              Massachusetts Line   \n",
       "49                                          Albany   \n",
       "\n",
       "   Toll Collection One-Way (N,S,E,W) Yes/Kind  \\\n",
       "12                       W or UP :-)    Booth   \n",
       "48                               NaN  E-ZPass   \n",
       "47                               NaN  E-ZPass   \n",
       "51                               NaN  E-ZPass   \n",
       "49                               NaN  E-ZPass   \n",
       "\n",
       "    num_Average Passenger Vehicle Cost per Vehicle-Mile  \\\n",
       "12                                               0.39     \n",
       "48                                               0.05     \n",
       "47                                               0.05     \n",
       "51                                               0.05     \n",
       "49                                               0.05     \n",
       "\n",
       "   num_Average Truck Cost per Vehicle-Mile num_Maximum Passenger Vehicle Fee  \\\n",
       "12                                    0.39                              40.0   \n",
       "48                                    0.24                              33.7   \n",
       "47                                    0.24                              33.7   \n",
       "51                                    0.24                              33.7   \n",
       "49                                    0.24                              33.7   \n",
       "\n",
       "   num_Maximum Truck Fee num_Minimum Passenger Vehicle Fee  \\\n",
       "12                  40.0                             10.00   \n",
       "48                 137.9                              0.15   \n",
       "47                 137.9                              0.15   \n",
       "51                 137.9                              0.15   \n",
       "49                 137.9                              0.15   \n",
       "\n",
       "   num_Minimum Truck Fee     spreadsheet  \n",
       "12                  10.0  non-interstate  \n",
       "48                   0.4      interstate  \n",
       "47                   0.4      interstate  \n",
       "51                   0.4      interstate  \n",
       "49                   0.4      interstate  \n",
       "\n",
       "[5 rows x 34 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "highway_df.sort_values(by=\"num_Maximum Passenger Vehicle Fee\",ascending=False).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Types of fees\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Fixed: Rate does not vary by time of day or traffic conditions (May vary by vehicle/weight class or distance traveled).    156\n",
       "Fixed Variable: Rate varies by time of day  based on pre-set schedule                                                       38\n",
       "Dynamic Variable: Rate varies based on current traffic conditions                                                           15\n",
       "Fixed Rate does not vary by time of day or traffic conditions (May vary by vehicle/weight class or distance traveled).       1\n",
       "Name: Fee Type, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "highway_df[\"Fee Type\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Fixed: Rate does not vary by time of day or traffic conditions (May vary by vehicle/weight class or distance traveled).    66\n",
       "Fixed Variable: Rate varies by time of day  based on pre-set schedule                                                      17\n",
       "Dynamic Variable: Rate varies based on current traffic conditions                                                          14\n",
       "Fixed Rate does not vary by time of day or traffic conditions (May vary by vehicle/weight class or distance traveled).      1\n",
       "Name: Fee Type, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "interstate[\"Fee Type\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "244"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(highway_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "210"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(highway_df[highway_df[\"Fee Type\"].notnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Fixed: Rate does not vary by time of day or traffic conditions (May vary by vehicle/weight class or distance traveled).    0.742857\n",
       "Fixed Variable: Rate varies by time of day  based on pre-set schedule                                                      0.180952\n",
       "Dynamic Variable: Rate varies based on current traffic conditions                                                          0.071429\n",
       "Fixed Rate does not vary by time of day or traffic conditions (May vary by vehicle/weight class or distance traveled).     0.004762\n",
       "Name: Fee Type, dtype: float64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Types of highway toll fees as a percentage\n",
    "\n",
    "highway_df[\"Fee Type\"].value_counts() / 210\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How much data is missing?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "27"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(interstate[interstate[\"num_Average Passenger Vehicle Cost per Vehicle-Mile\"].isnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "79"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(interstate[interstate[\"num_Average Passenger Vehicle Cost per Vehicle-Mile\"].notnull()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}