5 Data cleaning - new data obtained 20190728.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data cleaning - new data obtained at 20190728"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext autoreload\n",
    "%autoreload 2\n",
    "%matplotlib inline\n",
    "# %qtconsole\n",
    "\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\"\n",
    "\n",
    "from IPython.display import display\n",
    "def display_all(df):\n",
    "    with pd.option_context(\"display.max_rows\", 1000): \n",
    "        with pd.option_context(\"display.max_columns\", 1000): \n",
    "            display(df)\n",
    "\t\t\t\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from pathlib import Path\t\t\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "# warnings.filterwarnings(action='once')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ref: https://gis.stackexchange.com/questions/225065/converting-nztm-new-zealand-transverse-mercator-to-lat-long\n",
    "import math\n",
    "def nztm_to_lat_long(nztm_e, nztm_n):\n",
    "    \"\"\" converting nztm to latitude and longitude\n",
    "        #input Northing(Y); Easting(X) variables\n",
    "    \"\"\"\n",
    "    #Common variables for NZTM2000\n",
    "    a = 6378137;\n",
    "    f = 1 / 298.257222101;\n",
    "    phizero = 0;\n",
    "    lambdazero = 173;\n",
    "    Nzero = 10000000;\n",
    "    Ezero = 1600000;\n",
    "    kzero = 0.9996;   \n",
    "\n",
    "    #input Northing(Y); Easting(X) variables\n",
    "    N  = int(nztm_n);\n",
    "    E  = int(nztm_e);\n",
    "\n",
    "    #Calculation: From NZTM to lat/Long\n",
    "    b = a * (1 - f);\n",
    "    esq = 2 * f - f ** 2;\n",
    "    Z0 = 1 - esq / 4 - 3 * (esq ** 2) / 64 - 5 * (esq ** 3) / 256;\n",
    "    A2 = 0.375 * (esq + esq ** 2 / 4 + 15 * (esq ** 3) / 128);\n",
    "    A4 = 15 * ((esq ** 2) + 3 * (esq ** 3) / 4) / 256;\n",
    "    A6 = 35 * (esq ** 3) / 3072;\n",
    "\n",
    "    Nprime = N - Nzero;\n",
    "    mprime = Nprime / kzero;\n",
    "    smn = (a - b) / (a + b);\n",
    "    G = a * (1 - smn) * (1 - (smn ** 2)) * (1 + 9 * (smn ** 2) / 4 + 225 * (smn ** 4) / 64) * math.pi/ 180.0;\n",
    "    sigma = mprime * math.pi / (180 * G);\n",
    "    phiprime = sigma + (3 * smn / 2 - 27 * (smn ** 3) / 32) * math.sin(2 * sigma) + (21 * (smn ** 2) / 16 - 55 * (smn ** 4) / 32) * math.sin(4 * sigma) + (151 * (smn ** 3) / 96) * math.sin(6 * sigma) + (1097 * (smn ** 4) / 512) *math.sin(8 * sigma);\n",
    "    rhoprime = a * (1 - esq) / ((1 - esq * ((math.sin(phiprime)) ** 2)) ** 1.5);\n",
    "    upsilonprime = a / math.sqrt(1 - esq * ((math.sin(phiprime)) ** 2));\n",
    "\n",
    "    psiprime = upsilonprime / rhoprime;\n",
    "    tprime = math.tan(phiprime);\n",
    "    Eprime = E - Ezero;\n",
    "    chi = Eprime / (kzero * upsilonprime);\n",
    "    term_1 = tprime * Eprime * chi / (kzero * rhoprime * 2);\n",
    "    term_2 = term_1 * (chi ** 2) / 12 * (-4 * (psiprime ** 2) + 9 * psiprime * (1 - (tprime ** 2)) + 12 * (tprime ** 2));\n",
    "    term_3 = tprime * Eprime * (chi ** 5) / (kzero * rhoprime * 720) * (8 * (psiprime ** 4) * (11 - 24 * (tprime ** 2)) - 12 * (psiprime ** 3) * (21 - 71 * (tprime ** 2)) + 15 * (psiprime ** 2) * (15 - 98 * (tprime ** 2) + 15 * (tprime ** 4)) + 180 * psiprime * (5 * (tprime ** 2) - 3 * (tprime ** 4)) + 360 * (tprime ** 4));\n",
    "    term_4 = tprime * Eprime * (chi ** 7) / (kzero * rhoprime * 40320) * (1385 + 3633 * (tprime ** 2) + 4095 * (tprime ** 4) + 1575 * (tprime ** 6));\n",
    "    term1 = chi * (1 / math.cos(phiprime));\n",
    "    term2 = (chi ** 3) * (1 / math.cos(phiprime)) / 6 * (psiprime + 2 * (tprime ** 2));\n",
    "    term3 = (chi ** 5) * (1 / math.cos(phiprime)) / 120 * (-4 * (psiprime ** 3) * (1 - 6 * (tprime ** 2)) + (psiprime ** 2) * (9 - 68 * (tprime ** 2)) + 72 * psiprime * (tprime ** 2) + 24 * (tprime ** 4));\n",
    "    term4 = (chi ** 7) * (1 / math.cos(phiprime)) / 5040 * (61 + 662 * (tprime ** 2) + 1320 * (tprime ** 4) + 720 * (tprime ** 6));\n",
    "\n",
    "    latitude = (phiprime - term_1 + term_2 - term_3 + term_4) * 180 / math.pi;\n",
    "    longitude = lambdazero + 180 / math.pi * (term1 - term2 + term3 - term4);  \n",
    "\n",
    "#         outfile.write(\"{}, {}, {}\\n\".format(site_id, latitude, longitude))\n",
    "    return latitude, longitude"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>X</th>\n",
       "      <th>Y</th>\n",
       "      <th>OBJECTID</th>\n",
       "      <th>carr_way_no</th>\n",
       "      <th>road_id</th>\n",
       "      <th>road_name</th>\n",
       "      <th>start_name</th>\n",
       "      <th>end_name</th>\n",
       "      <th>location</th>\n",
       "      <th>latest</th>\n",
       "      <th>count_date</th>\n",
       "      <th>peak_hour</th>\n",
       "      <th>count_duration</th>\n",
       "      <th>adt</th>\n",
       "      <th>peaktraffic</th>\n",
       "      <th>pccar</th>\n",
       "      <th>pclcv</th>\n",
       "      <th>pcmcv</th>\n",
       "      <th>pchcvi</th>\n",
       "      <th>pchcvii</th>\n",
       "      <th>pcbus</th>\n",
       "      <th>pcheavy</th>\n",
       "      <th>NZTMX</th>\n",
       "      <th>NZTMY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.753117e+06</td>\n",
       "      <td>5.915068e+06</td>\n",
       "      <td>4001</td>\n",
       "      <td>14721.0</td>\n",
       "      <td>31125</td>\n",
       "      <td>RICHARDSON RD (OWAIRAKA)</td>\n",
       "      <td>HENDON AVE</td>\n",
       "      <td>O'DONNELL AVE</td>\n",
       "      <td>1218.0</td>\n",
       "      <td>No</td>\n",
       "      <td>1986-06-25T00:00:00.000Z</td>\n",
       "      <td>16:15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.753117e+06</td>\n",
       "      <td>5.915068e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.753117e+06</td>\n",
       "      <td>5.915068e+06</td>\n",
       "      <td>4002</td>\n",
       "      <td>14721.0</td>\n",
       "      <td>31125</td>\n",
       "      <td>RICHARDSON RD (OWAIRAKA)</td>\n",
       "      <td>HENDON AVE</td>\n",
       "      <td>O'DONNELL AVE</td>\n",
       "      <td>1218.0</td>\n",
       "      <td>No</td>\n",
       "      <td>1984-06-20T00:00:00.000Z</td>\n",
       "      <td>16:15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.753117e+06</td>\n",
       "      <td>5.915068e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1.753117e+06</td>\n",
       "      <td>5.915068e+06</td>\n",
       "      <td>4003</td>\n",
       "      <td>14721.0</td>\n",
       "      <td>31125</td>\n",
       "      <td>RICHARDSON RD (OWAIRAKA)</td>\n",
       "      <td>HENDON AVE</td>\n",
       "      <td>O'DONNELL AVE</td>\n",
       "      <td>1218.0</td>\n",
       "      <td>No</td>\n",
       "      <td>1984-06-15T00:00:00.000Z</td>\n",
       "      <td>16:15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.753117e+06</td>\n",
       "      <td>5.915068e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.753117e+06</td>\n",
       "      <td>5.915068e+06</td>\n",
       "      <td>4004</td>\n",
       "      <td>14721.0</td>\n",
       "      <td>31125</td>\n",
       "      <td>RICHARDSON RD (OWAIRAKA)</td>\n",
       "      <td>HENDON AVE</td>\n",
       "      <td>O'DONNELL AVE</td>\n",
       "      <td>1218.0</td>\n",
       "      <td>No</td>\n",
       "      <td>1993-10-27T00:00:00.000Z</td>\n",
       "      <td>17:45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.753117e+06</td>\n",
       "      <td>5.915068e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.753117e+06</td>\n",
       "      <td>5.915068e+06</td>\n",
       "      <td>4005</td>\n",
       "      <td>14721.0</td>\n",
       "      <td>31125</td>\n",
       "      <td>RICHARDSON RD (OWAIRAKA)</td>\n",
       "      <td>HENDON AVE</td>\n",
       "      <td>O'DONNELL AVE</td>\n",
       "      <td>1218.0</td>\n",
       "      <td>No</td>\n",
       "      <td>1993-10-27T00:00:00.000Z</td>\n",
       "      <td>17:45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.753117e+06</td>\n",
       "      <td>5.915068e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              X             Y  OBJECTID  carr_way_no  road_id  \\\n",
       "0  1.753117e+06  5.915068e+06      4001      14721.0    31125   \n",
       "1  1.753117e+06  5.915068e+06      4002      14721.0    31125   \n",
       "2  1.753117e+06  5.915068e+06      4003      14721.0    31125   \n",
       "3  1.753117e+06  5.915068e+06      4004      14721.0    31125   \n",
       "4  1.753117e+06  5.915068e+06      4005      14721.0    31125   \n",
       "\n",
       "                  road_name  start_name       end_name  location latest  \\\n",
       "0  RICHARDSON RD (OWAIRAKA)  HENDON AVE  O'DONNELL AVE    1218.0     No   \n",
       "1  RICHARDSON RD (OWAIRAKA)  HENDON AVE  O'DONNELL AVE    1218.0     No   \n",
       "2  RICHARDSON RD (OWAIRAKA)  HENDON AVE  O'DONNELL AVE    1218.0     No   \n",
       "3  RICHARDSON RD (OWAIRAKA)  HENDON AVE  O'DONNELL AVE    1218.0     No   \n",
       "4  RICHARDSON RD (OWAIRAKA)  HENDON AVE  O'DONNELL AVE    1218.0     No   \n",
       "\n",
       "                 count_date peak_hour count_duration  adt  peaktraffic  pccar  \\\n",
       "0  1986-06-25T00:00:00.000Z     16:15            NaN  NaN          NaN    NaN   \n",
       "1  1984-06-20T00:00:00.000Z     16:15            NaN  NaN          NaN    NaN   \n",
       "2  1984-06-15T00:00:00.000Z     16:15            NaN  NaN          NaN    NaN   \n",
       "3  1993-10-27T00:00:00.000Z     17:45            NaN  NaN          NaN    NaN   \n",
       "4  1993-10-27T00:00:00.000Z     17:45            NaN  NaN          NaN    NaN   \n",
       "\n",
       "   pclcv  pcmcv  pchcvi  pchcvii  pcbus  pcheavy         NZTMX         NZTMY  \n",
       "0    NaN    NaN     NaN      NaN    NaN      NaN  1.753117e+06  5.915068e+06  \n",
       "1    NaN    NaN     NaN      NaN    NaN      NaN  1.753117e+06  5.915068e+06  \n",
       "2    NaN    NaN     NaN      NaN    NaN      NaN  1.753117e+06  5.915068e+06  \n",
       "3    NaN    NaN     NaN      NaN    NaN      NaN  1.753117e+06  5.915068e+06  \n",
       "4    NaN    NaN     NaN      NaN    NaN      NaN  1.753117e+06  5.915068e+06  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_orig = pd.read_csv(r'data/Average_Daily_Traffic_Counts_20190728.csv')\n",
    "display_all(df_orig.head())\n",
    "df = df_orig.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = df_orig.apply(lambda row: nztm_to_lat_long(row['NZTMX'], row['NZTMY']), axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp2 = temp.apply(pd.Series)\n",
    "temp2.columns = ['latitude', 'longitude']\n",
    "df['latitude'] = temp2['latitude']\n",
    "df['longitude'] = temp2['longitude']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['count_date'] = df['count_date'].str[:10]\n",
    "df['count_date'] = pd.to_datetime(df['count_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "X                     0\n",
       "Y                     0\n",
       "OBJECTID              0\n",
       "carr_way_no         252\n",
       "road_id               0\n",
       "road_name             0\n",
       "start_name          515\n",
       "end_name            586\n",
       "location             81\n",
       "latest                0\n",
       "count_date            0\n",
       "peak_hour         26452\n",
       "count_duration    24116\n",
       "adt                8151\n",
       "peaktraffic       32121\n",
       "pccar             23699\n",
       "pclcv             23699\n",
       "pcmcv             23703\n",
       "pchcvi            23699\n",
       "pchcvii           24633\n",
       "pcbus             24237\n",
       "pcheavy           22462\n",
       "NZTMX                 0\n",
       "NZTMY                 0\n",
       "latitude              0\n",
       "longitude             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# only consider 7-day traffic count measure\n",
    "df2 = df[df.count_duration == '7D']\n",
    "# consider only adt traffic measure (other measures have too many missing values)\n",
    "# consider only longitude/latitude coordinate (drop X, Y, NZTMX, NZTMY)\n",
    "cols_wanted = ['carr_way_no', 'road_id', 'road_name',\n",
    "       'start_name', 'end_name', 'location', 'latest', 'count_date',\n",
    "       'adt', 'latitude', 'longitude']\n",
    "df2 = df2[cols_wanted]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "carr_way_no     148\n",
       "road_id           0\n",
       "road_name         0\n",
       "start_name      409\n",
       "end_name        440\n",
       "location         81\n",
       "latest            0\n",
       "count_date        0\n",
       "adt            1825\n",
       "latitude          0\n",
       "longitude         0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "carr_way_no    0\n",
       "road_id        0\n",
       "road_name      0\n",
       "start_name     0\n",
       "end_name       0\n",
       "location       0\n",
       "latest         0\n",
       "count_date     0\n",
       "adt            0\n",
       "latitude       0\n",
       "longitude      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.isna().sum()\n",
    "# drop rows with missing values for now since we seem to have enough data\n",
    "df2.dropna(inplace = True)\n",
    "df2.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>carr_way_no</th>\n",
       "      <th>road_id</th>\n",
       "      <th>road_name</th>\n",
       "      <th>start_name</th>\n",
       "      <th>end_name</th>\n",
       "      <th>cway_width</th>\n",
       "      <th>traff_manage_level</th>\n",
       "      <th>Shape__Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2001</td>\n",
       "      <td>41976</td>\n",
       "      <td>41096</td>\n",
       "      <td>BABICH RD</td>\n",
       "      <td>WIDTH CHANGE (END K&amp;C RHS)</td>\n",
       "      <td>END</td>\n",
       "      <td>5.0</td>\n",
       "      <td>&lt; 10,000 vpd Rural/&lt;15,000 vpd Urban</td>\n",
       "      <td>636.519615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2002</td>\n",
       "      <td>32589</td>\n",
       "      <td>41433</td>\n",
       "      <td>BABICH RD NORTH</td>\n",
       "      <td>METCALFE RD</td>\n",
       "      <td>PLATINUM RISE</td>\n",
       "      <td>10.0</td>\n",
       "      <td>&lt;500 vpd (Low Volume)</td>\n",
       "      <td>72.580121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2003</td>\n",
       "      <td>32605</td>\n",
       "      <td>41433</td>\n",
       "      <td>BABICH RD NORTH</td>\n",
       "      <td>PLATINUM RISE</td>\n",
       "      <td>SAUVIGNON AVE</td>\n",
       "      <td>10.0</td>\n",
       "      <td>&lt;500 vpd (Low Volume)</td>\n",
       "      <td>122.037043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>32606</td>\n",
       "      <td>41433</td>\n",
       "      <td>BABICH RD NORTH</td>\n",
       "      <td>SAUVIGNON AVE</td>\n",
       "      <td>END OF STAGE 1</td>\n",
       "      <td>10.0</td>\n",
       "      <td>&lt;500 vpd (Low Volume)</td>\n",
       "      <td>76.091004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005</td>\n",
       "      <td>34909</td>\n",
       "      <td>41433</td>\n",
       "      <td>BABICH RD NORTH</td>\n",
       "      <td>END OF STAGE 1</td>\n",
       "      <td>END OF STAGE 2</td>\n",
       "      <td>10.0</td>\n",
       "      <td>&lt;500 vpd (Low Volume)</td>\n",
       "      <td>163.637715</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   OBJECTID  carr_way_no  road_id        road_name  \\\n",
       "0      2001        41976    41096        BABICH RD   \n",
       "1      2002        32589    41433  BABICH RD NORTH   \n",
       "2      2003        32605    41433  BABICH RD NORTH   \n",
       "3      2004        32606    41433  BABICH RD NORTH   \n",
       "4      2005        34909    41433  BABICH RD NORTH   \n",
       "\n",
       "                   start_name        end_name  cway_width  \\\n",
       "0  WIDTH CHANGE (END K&C RHS)             END         5.0   \n",
       "1                 METCALFE RD   PLATINUM RISE        10.0   \n",
       "2               PLATINUM RISE   SAUVIGNON AVE        10.0   \n",
       "3               SAUVIGNON AVE  END OF STAGE 1        10.0   \n",
       "4              END OF STAGE 1  END OF STAGE 2        10.0   \n",
       "\n",
       "                     traff_manage_level  Shape__Length  \n",
       "0  < 10,000 vpd Rural/<15,000 vpd Urban     636.519615  \n",
       "1                 <500 vpd (Low Volume)      72.580121  \n",
       "2                 <500 vpd (Low Volume)     122.037043  \n",
       "3                 <500 vpd (Low Volume)      76.091004  \n",
       "4                 <500 vpd (Low Volume)     163.637715  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_road = pd.read_csv(r'data/Traffic_Management_Levels_20190728.csv')\n",
    "df_road.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['carr_way_no', 'road_id', 'road_name', 'start_name', 'end_name',\n",
       "       'location', 'latest', 'count_date', 'adt', 'latitude', 'longitude'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "Index(['OBJECTID', 'carr_way_no', 'road_id', 'road_name', 'start_name',\n",
       "       'end_name', 'cway_width', 'traff_manage_level', 'Shape__Length'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "['carr_way_no', 'road_id', 'road_name', 'start_name', 'end_name']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.columns\n",
    "df_road.columns\n",
    "[col for col in df2.columns if col in df_road.columns]\n",
    "# OBJECTID is the index, \n",
    "# carr_way_no is the id for a part of the road e.g. a raod can have many carriage way\n",
    "# road id "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(25755, 11)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(37474, 9)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "OBJECTID                0\n",
       "carr_way_no             0\n",
       "road_id                 0\n",
       "road_name               0\n",
       "start_name            264\n",
       "end_name              297\n",
       "cway_width              0\n",
       "traff_manage_level    179\n",
       "Shape__Length           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape\n",
    "# df2.isna().sum()\n",
    "df_road.shape\n",
    "df_road.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "carr_way_no            0\n",
       "road_id                0\n",
       "road_name              0\n",
       "start_name             0\n",
       "end_name               0\n",
       "location               0\n",
       "latest                 0\n",
       "count_date             0\n",
       "adt                    0\n",
       "latitude               0\n",
       "longitude              0\n",
       "OBJECTID              14\n",
       "cway_width            14\n",
       "traff_manage_level    14\n",
       "Shape__Length         14\n",
       "dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cols = [col for col in df2.columns if col in df_road.columns]\n",
    "df3 = df2.merge(df_road, how = 'left', on = cols)\n",
    "df3.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# a few roads don't have road info, drop these records\n",
    "df3.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.drop(columns=['OBJECTID'], inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['carr_way_no', 'road_id', 'road_name', 'start_name', 'end_name',\n",
       "       'location', 'latest', 'count_date', 'adt', 'latitude', 'longitude',\n",
       "       'cway_width', 'traff_manage_level', 'Shape__Length'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(25741, 14)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.to_csv(r'data\\merged_data_20190728.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ISLAND NOSE MOTORWAY    634\n",
       "END OF SEAL             319\n",
       "URBAN/RURAL             173\n",
       "GREAT NORTH RD          147\n",
       "WHANGAPARAOA RD         115\n",
       "TE ATATU RD              97\n",
       "S HWAY 1                 95\n",
       "SPEED DERESTRICTION      93\n",
       "S HWAY 16                80\n",
       "START OF SEAL            73\n",
       "Name: start_name, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.start_name.value_counts().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "END OF SEAL            808\n",
       "SPEED RESTRICTION      766\n",
       "END                    427\n",
       "CUL-DE-SAC             381\n",
       "URBAN/RURAL            359\n",
       "WIDTH CHANGE           311\n",
       "START OF 2 LANES       303\n",
       "GATE                   280\n",
       "BEAUMONT ST            267\n",
       "SPEED DERESTRICTION    258\n",
       "Name: end_name, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.end_name.value_counts().head(10)"
   ]
  },
  {
   "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.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}