Task3_DataAnalysis_EstabTNC_use.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SANDAG Commercial Vehicle Model\n",
    "#### Task 3 -- Data Exploration for Design Decisions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Establishment TNC Use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os\n",
    "# from matplotlib import pyplot as plt\n",
    "# from matplotlib import ticker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option(\"display.max_rows\", None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root_dir = \n",
      " C:\\Users\\jgliebe\\OneDrive - Cambridge Systematics\\Documents - PROJ SANDAG Commercial Vehicle & Heavy Truck Model Update\\_Shared_CSTeam \n",
      "\n",
      "raw_data_dir = \n",
      " Task03_DataID_Review \n",
      "\n",
      "proc_data_dir = \n",
      " Task04_DataProcessing\\Data\\proc data \n",
      "\n",
      "lookup_dir = \n",
      " Task04_DataProcessing\\Data\\Lookups \n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Import file paths from script\n",
    "from FilePaths import *\n",
    "print(\"root_dir = \\n\", root_dir, \"\\n\")\n",
    "print(\"raw_data_dir = \\n\", raw_data_dir,\"\\n\")\n",
    "print(\"proc_data_dir = \\n\", proc_data_dir,\"\\n\")\n",
    "print(\"lookup_dir = \\n\", lookup_dir,\"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Read in establishment file and create expansion weights"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>company_id</th>\n",
       "      <th>Industry Group_Size Code (Group from Column D; Size 1=0-9 emp, 2=10+)</th>\n",
       "      <th>company_name</th>\n",
       "      <th>base_location_Industry Group</th>\n",
       "      <th>company_location_address</th>\n",
       "      <th>company_location_city</th>\n",
       "      <th>company_location_state</th>\n",
       "      <th>company_location_zipcode</th>\n",
       "      <th>company_location_latitude</th>\n",
       "      <th>company_location_longitude</th>\n",
       "      <th>...</th>\n",
       "      <th>LCV Owned or Leased</th>\n",
       "      <th>LCV other</th>\n",
       "      <th>LCV Total</th>\n",
       "      <th>SUT Owned or Leased</th>\n",
       "      <th>SUT other</th>\n",
       "      <th>SUT Total</th>\n",
       "      <th>MUT Owned or Leased</th>\n",
       "      <th>MUT other</th>\n",
       "      <th>MUT Total</th>\n",
       "      <th>TOTAL CVs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>100002</td>\n",
       "      <td>1_2</td>\n",
       "      <td>ALTAR</td>\n",
       "      <td>1</td>\n",
       "      <td>4370 LA JOLLA VILLAGE DR # 655</td>\n",
       "      <td>SAN DIEGO</td>\n",
       "      <td>CA</td>\n",
       "      <td>92122</td>\n",
       "      <td>32.873754</td>\n",
       "      <td>-117.210198</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>100003</td>\n",
       "      <td>1_1</td>\n",
       "      <td>John Baker Property Mgmt</td>\n",
       "      <td>1</td>\n",
       "      <td>405 W 9TH AVE</td>\n",
       "      <td>ESCONDIDO</td>\n",
       "      <td>CA</td>\n",
       "      <td>92025</td>\n",
       "      <td>33.112208</td>\n",
       "      <td>-117.080743</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>100004</td>\n",
       "      <td>1_1</td>\n",
       "      <td>OWB RANCHES LLC</td>\n",
       "      <td>1</td>\n",
       "      <td>512 VIA DE LA VALLE # 310</td>\n",
       "      <td>SOLANA BEACH</td>\n",
       "      <td>CA</td>\n",
       "      <td>92075</td>\n",
       "      <td>32.980370</td>\n",
       "      <td>-117.260820</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>100005</td>\n",
       "      <td>1_2</td>\n",
       "      <td>United Sportfishers of San Diego, Inc.</td>\n",
       "      <td>1</td>\n",
       "      <td>2803 Emerson St</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>CA</td>\n",
       "      <td>92106</td>\n",
       "      <td>32.723522</td>\n",
       "      <td>-117.227602</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>100007</td>\n",
       "      <td>1_1</td>\n",
       "      <td>SCRIPPS COASTAL RESERVE</td>\n",
       "      <td>1</td>\n",
       "      <td>9500 GILMAN DR</td>\n",
       "      <td>LA JOLLA</td>\n",
       "      <td>CA</td>\n",
       "      <td>92093</td>\n",
       "      <td>32.877189</td>\n",
       "      <td>-117.237422</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 68 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id  \\\n",
       "0      100002   \n",
       "1      100003   \n",
       "2      100004   \n",
       "3      100005   \n",
       "4      100007   \n",
       "\n",
       "  Industry Group_Size Code (Group from Column D; Size 1=0-9 emp, 2=10+)  \\\n",
       "0                                                1_2                      \n",
       "1                                                1_1                      \n",
       "2                                                1_1                      \n",
       "3                                                1_2                      \n",
       "4                                                1_1                      \n",
       "\n",
       "                             company_name  base_location_Industry Group  \\\n",
       "0                                   ALTAR                             1   \n",
       "1                John Baker Property Mgmt                             1   \n",
       "2                         OWB RANCHES LLC                             1   \n",
       "3  United Sportfishers of San Diego, Inc.                             1   \n",
       "4                 SCRIPPS COASTAL RESERVE                             1   \n",
       "\n",
       "         company_location_address company_location_city  \\\n",
       "0  4370 LA JOLLA VILLAGE DR # 655             SAN DIEGO   \n",
       "1                   405 W 9TH AVE             ESCONDIDO   \n",
       "2       512 VIA DE LA VALLE # 310          SOLANA BEACH   \n",
       "3                 2803 Emerson St             San Diego   \n",
       "4                  9500 GILMAN DR              LA JOLLA   \n",
       "\n",
       "  company_location_state  company_location_zipcode  company_location_latitude  \\\n",
       "0                     CA                     92122                  32.873754   \n",
       "1                     CA                     92025                  33.112208   \n",
       "2                     CA                     92075                  32.980370   \n",
       "3                     CA                     92106                  32.723522   \n",
       "4                     CA                     92093                  32.877189   \n",
       "\n",
       "   company_location_longitude  ...  LCV Owned or Leased  LCV other  LCV Total  \\\n",
       "0                 -117.210198  ...                    0          0          0   \n",
       "1                 -117.080743  ...                    0          1          1   \n",
       "2                 -117.260820  ...                    0          0          0   \n",
       "3                 -117.227602  ...                    0          0          0   \n",
       "4                 -117.237422  ...                    0          1          1   \n",
       "\n",
       "   SUT Owned or Leased  SUT other  SUT Total  MUT Owned or Leased  MUT other  \\\n",
       "0                    0          0          0                    0          0   \n",
       "1                    0          0          0                    0          0   \n",
       "2                    0          0          0                    0          0   \n",
       "3                    0          1          1                    1          0   \n",
       "4                    0          0          0                    0          0   \n",
       "\n",
       "   MUT Total  TOTAL CVs  \n",
       "0          0          0  \n",
       "1          0          1  \n",
       "2          0          0  \n",
       "3          1          2  \n",
       "4          0          1  \n",
       "\n",
       "[5 rows x 68 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read input data -- establishment file\n",
    "df_estab = pd.read_excel(os.path.join(root_dir, \n",
    "                                      raw_data_dir, \n",
    "                                      r\"CVS\\SANDAG 2022 CV DataBase & Dictionaires_03_03_2023.xlsx\"),\n",
    "                                     sheet_name=r\"Establishment Data\")\n",
    "\n",
    "df_estab.set_index('company_id')\n",
    "df_estab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_estab.loc[:, 'emp_total'] = df_estab['employees_fulltime_count'] + df_estab['employees_parttime_count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Read in file from CVS survey weighting report -- Most likely number of establishments by Industry"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 12 entries, 0 to 11\n",
      "Data columns (total 5 columns):\n",
      "SectorID    11 non-null float64\n",
      "Sector      12 non-null object\n",
      "Emp_0_9     12 non-null int64\n",
      "Emp_10p     12 non-null int64\n",
      "All         12 non-null int64\n",
      "dtypes: float64(1), int64(3), object(1)\n",
      "memory usage: 608.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df_expand = pd.read_excel(os.path.join(root_dir, \n",
    "                                      raw_data_dir, \n",
    "                                      r\"CVS\\CVS_EstabExpansion.xlsx\"),\n",
    "                                     sheet_name=r\"CVS_Expansion_Totals\")\n",
    "df_expand.info()\n",
    "df_expand.SectorID.fillna(0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>SectorID</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Emp_0_9</th>\n",
       "      <th>Emp_10p</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Agriculture/Mining</td>\n",
       "      <td>334</td>\n",
       "      <td>88</td>\n",
       "      <td>422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>2723</td>\n",
       "      <td>1664</td>\n",
       "      <td>4387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Industrial/Utilties</td>\n",
       "      <td>115</td>\n",
       "      <td>77</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Retail</td>\n",
       "      <td>7124</td>\n",
       "      <td>3272</td>\n",
       "      <td>10396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Wholesale</td>\n",
       "      <td>3355</td>\n",
       "      <td>990</td>\n",
       "      <td>4345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Construction</td>\n",
       "      <td>6162</td>\n",
       "      <td>1586</td>\n",
       "      <td>7748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Transportation</td>\n",
       "      <td>1371</td>\n",
       "      <td>496</td>\n",
       "      <td>1867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Info FlRE/Professional services</td>\n",
       "      <td>24539</td>\n",
       "      <td>4310</td>\n",
       "      <td>28849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>9.0</td>\n",
       "      <td>Education/Public/Other services</td>\n",
       "      <td>11416</td>\n",
       "      <td>3233</td>\n",
       "      <td>14649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>10.0</td>\n",
       "      <td>Medical/Health Services</td>\n",
       "      <td>19634</td>\n",
       "      <td>2404</td>\n",
       "      <td>22038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>11.0</td>\n",
       "      <td>Leisure/Accommodations and Food</td>\n",
       "      <td>4742</td>\n",
       "      <td>4492</td>\n",
       "      <td>9234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>TOTAL</td>\n",
       "      <td>81515</td>\n",
       "      <td>22612</td>\n",
       "      <td>104127</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    SectorID                           Sector  Emp_0_9  Emp_10p     All\n",
       "0        1.0               Agriculture/Mining      334       88     422\n",
       "1        2.0                    Manufacturing     2723     1664    4387\n",
       "2        3.0              Industrial/Utilties      115       77     192\n",
       "3        4.0                           Retail     7124     3272   10396\n",
       "4        5.0                        Wholesale     3355      990    4345\n",
       "5        6.0                     Construction     6162     1586    7748\n",
       "6        7.0                   Transportation     1371      496    1867\n",
       "7        8.0  Info FlRE/Professional services    24539     4310   28849\n",
       "8        9.0  Education/Public/Other services    11416     3233   14649\n",
       "9       10.0          Medical/Health Services    19634     2404   22038\n",
       "10      11.0  Leisure/Accommodations and Food     4742     4492    9234\n",
       "11       0.0                            TOTAL    81515    22612  104127"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_expand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Emp_0_9': {1.0: 334,\n",
       "  2.0: 2723,\n",
       "  3.0: 115,\n",
       "  4.0: 7124,\n",
       "  5.0: 3355,\n",
       "  6.0: 6162,\n",
       "  7.0: 1371,\n",
       "  8.0: 24539,\n",
       "  9.0: 11416,\n",
       "  10.0: 19634,\n",
       "  11.0: 4742,\n",
       "  0.0: 81515},\n",
       " 'Emp_10p': {1.0: 88,\n",
       "  2.0: 1664,\n",
       "  3.0: 77,\n",
       "  4.0: 3272,\n",
       "  5.0: 990,\n",
       "  6.0: 1586,\n",
       "  7.0: 496,\n",
       "  8.0: 4310,\n",
       "  9.0: 3233,\n",
       "  10.0: 2404,\n",
       "  11.0: 4492,\n",
       "  0.0: 22612}}"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create dictionary for establishment expansion targets\n",
    "expandEstab = dict()\n",
    "expandEstab['Emp_0_9'] = dict(zip(df_expand['SectorID'],df_expand['Emp_0_9']))\n",
    "expandEstab['Emp_10p'] = dict(zip(df_expand['SectorID'],df_expand['Emp_10p']))\n",
    "expandEstab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add establishment population-level estimates (targets)\n",
    "df_estab.loc[df_estab['emp_total']<=9, 'emp_lt10'] = 1\n",
    "df_estab.loc[df_estab['emp_total']>9, 'emp_lt10'] = 0\n",
    "df_estab['emp_lt10'] = df_estab['emp_lt10'].astype(int)\n",
    "             \n",
    "df_estab.loc[df_estab['emp_total']<=9, \\\n",
    "             'region_estab_wght'] = df_estab['base_location_Industry Group'].map(expandEstab['Emp_0_9'])\n",
    "\n",
    "df_estab.loc[df_estab['emp_total']>9, \\\n",
    "             'region_estab_wght'] = df_estab['base_location_Industry Group'].map(expandEstab['Emp_10p'])\n",
    "\n",
    "df_estab['region_estab_wght'] = df_estab['region_estab_wght'].astype('int64')\n",
    "#df_estab[['emp_total','base_location_Industry Group','emp_lt10','region_estab_wght']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate Establishment Weights\n",
    "estab_weights = df_estab.groupby(['region_estab_wght']).size().reset_index(name='num_estabs')\n",
    "estab_weights['estab_exp_weight'] = estab_weights['region_estab_wght'] / estab_weights['num_estabs']\n",
    "estab_weights = dict(zip(estab_weights['region_estab_wght'],estab_weights['estab_exp_weight']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_estab.loc[:,'estab_exp_weight'] = df_estab['region_estab_wght'].map(estab_weights)\n",
    "df_estab[['emp_total','base_location_Industry Group','emp_lt10','region_estab_wght','estab_exp_weight']].head(10)\n",
    "df_estab = df_estab.drop(['region_estab_wght'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " base_location_Industry Group  emp_lt10  estab_exp_weight\n",
      "                            1         0             2.514\n",
      "                            1         1             5.475\n",
      "                            2         0            13.206\n",
      "                            2         1            22.320\n",
      "                            3         0             2.962\n",
      "                            3         1             7.188\n",
      "                            4         0            25.562\n",
      "                            4         1            48.463\n",
      "                            5         0            10.879\n",
      "                            5         1            24.312\n",
      "                            6         0            14.685\n",
      "                            6         1            42.205\n",
      "                            7         0             9.725\n",
      "                            7         1            16.926\n",
      "                            8         0            34.480\n",
      "                            8         1            81.255\n",
      "                            9         0            26.942\n",
      "                            9         1            50.964\n",
      "                           10         0            14.570\n",
      "                           10         1            88.045\n",
      "                           11         0            35.094\n",
      "                           11         1            35.388\n"
     ]
    }
   ],
   "source": [
    "# Finalize Establishment Weights in Table\n",
    "estab_wts = round(df_estab.groupby(['base_location_Industry Group','emp_lt10'])['estab_exp_weight'].mean().reset_index(),3)\n",
    "print(estab_wts.to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find TNC Usage Rates by Establishment Industries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "indus_lookup = {\n",
    "    1: \"Agriculture/Mining\",\n",
    "    2: \"Manufacturing\",\n",
    "    3: \"Industrial/Utilities\",\n",
    "    4: \"Retail\",\n",
    "    5: \"Wholesale\",\n",
    "    6: \"Construction\",\n",
    "    7: \"Transportation\",\n",
    "    8: \"Info/Finance/Insurance/Real Estate/Professional services\",\n",
    "    9: \"Education/Public/Other services\",\n",
    "    10: \"Medical/Health Services\",\n",
    "    11: \"Leisure/Accommodations and Food\",\n",
    "    96: \"Other/Non-Classified\"\n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create short names for industries\n",
    "ind_abrv = {\n",
    "    1: \"AGM\",\n",
    "    6: \"CON\",\n",
    "    9: \"EPO\",\n",
    "    3: \"IUT\",\n",
    "    8: \"IFR\",\n",
    "    11: \"LAF\",\n",
    "    2: \"MFG\",\n",
    "    10: \"MHS\",\n",
    "    4: \"RET\",\n",
    "    7: \"TRN\",\n",
    "    5: \"WHL\",\n",
    "    96: \"ONC\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>base_location_Industry Group</th>\n",
       "      <th>delivTNC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>233.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    base_location_Industry Group  delivTNC\n",
       "0                              1       1.0\n",
       "1                              2       7.0\n",
       "2                              3       0.0\n",
       "3                              4     133.0\n",
       "4                              5       2.0\n",
       "5                              6       4.0\n",
       "6                              7       3.0\n",
       "7                              8       2.0\n",
       "8                              9       6.0\n",
       "9                             10       1.0\n",
       "10                            11     233.0"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delivTNC = df_estab[df_estab['is_use_tnc']==1].groupby(['base_location_Industry Group']).sum()['no_of_from_deliveries_pcsuvpu'].reset_index(name='delivTNC')\n",
    "delivTNC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unweighted Establishment Observations\n",
      "\n",
      "Note: 'rateDelTNC' = delivery trips per day per establishment in industry\n",
      "\n",
      "                                     IndustryGroup  useTNC  delivTNC  totEstab pctUseTNC rateDelTNC\n",
      "                                Agriculture/Mining       1         1        96     1.04%      0.010\n",
      "                                     Manufacturing       6         7       248     2.42%      0.028\n",
      "                              Industrial/Utilities       1         0        42     2.38%      0.000\n",
      "                                            Retail      26       133       275     9.45%      0.484\n",
      "                                         Wholesale       4         2       229     1.75%      0.009\n",
      "                                      Construction       8         4       254     3.15%      0.016\n",
      "                                    Transportation       7         3       132     5.30%      0.023\n",
      " Info/Finance/Insurance/Real Estate/Professiona...       8         2       427     1.87%      0.005\n",
      "                   Education/Public/Other services       5         6       344     1.45%      0.017\n",
      "                           Medical/Health Services       6         1       388     1.55%      0.003\n",
      "                   Leisure/Accommodations and Food      89       233       262    33.97%      0.889\n"
     ]
    }
   ],
   "source": [
    "# TNC Usage Rates (raw)\n",
    "useTNC = df_estab[df_estab['is_use_tnc']==1].groupby(['base_location_Industry Group']).size().reset_index(name='useTNC')\n",
    "delivTNC = df_estab[df_estab['is_use_tnc']==1].groupby(['base_location_Industry Group']).sum()['no_of_from_deliveries_pcsuvpu'].reset_index(name='delivTNC').astype('int64')\n",
    "\n",
    "allEstab = df_estab.groupby(['base_location_Industry Group']).size().reset_index(name='totEstab')\n",
    "allEstab[\"IndustryGroup\"] = allEstab['base_location_Industry Group'].map(indus_lookup)\n",
    "TNC_Industry = allEstab.set_index('base_location_Industry Group').join(useTNC.set_index('base_location_Industry Group'))\n",
    "TNC_Industry = TNC_Industry.join(delivTNC.set_index('base_location_Industry Group'))\n",
    "TNC_Industry.loc[:,'IndusAbbrv'] = TNC_Industry.index.map(ind_abrv)\n",
    "\n",
    "# Reorder columns\n",
    "TNC_Industry = TNC_Industry[['IndustryGroup', 'useTNC', 'delivTNC', 'totEstab']]\n",
    "\n",
    "# Calculate rates\n",
    "TNC_Industry['pctUseTNC'] = TNC_Industry['useTNC'] / TNC_Industry['totEstab']\n",
    "TNC_Industry['pctUseTNC'] = TNC_Industry['pctUseTNC'].map('{:.2%}'.format)\n",
    "TNC_Industry['rateDelTNC'] = TNC_Industry['delivTNC'] / TNC_Industry['totEstab']\n",
    "TNC_Industry['rateDelTNC'] = TNC_Industry['rateDelTNC'].map('{:5.3f}'.format)\n",
    "\n",
    "print(\"Unweighted Establishment Observations\")\n",
    "print(\"\\nNote: 'rateDelTNC' = delivery trips per day per establishment in industry\\n\")\n",
    "print(TNC_Industry.to_string(index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Weighted Establishment Observations\n",
      "\n",
      "Note: 'rateDelTNC' = delivery trips per day per establishment in industry\n",
      "\n",
      "IndusAbbrv useTNC delivTNC empIfUseTNC  empAll totEstab pctUseTNC rateDelTNC delivEmpIfTNC\n",
      "       AGM      3        2          25    3378      422   0.00596    0.00474       0.08000\n",
      "       MFG     88      101        3998   92184     4387   0.02014    0.02302       0.02526\n",
      "       IUT      3        0          35    3327      192   0.01542    0.00000       0.00000\n",
      "       RET    825     3766       26808  168206    10396   0.07935    0.36225       0.14048\n",
      "       WHL     70       48         594   38724     4345   0.01620    0.01105       0.08081\n",
      "       CON    173       58        3250   71600     7748   0.02227    0.00749       0.01785\n",
      "       TRN     90       29        2301   22314     1867   0.04803    0.01553       0.01260\n",
      "       IFR    556      162       10946  271083    28849   0.01929    0.00562       0.01480\n",
      "       EPO    159      161        3883  241807    14649   0.01084    0.01099       0.04146\n",
      "       MHS    308       14        2500  230367    22038   0.01397    0.00064       0.00560\n",
      "       LAF   3132     8184       78791  374445     9234   0.33914    0.88629       0.10387\n",
      "\n",
      "All Establishment Totals:\n",
      "useTNC delivTNC empIfUseTNC   empAll totEstab\n",
      "  5406    12525      133131  1517435   104127\n"
     ]
    }
   ],
   "source": [
    "# TNC Usage Rates (weighted)\n",
    "useTNC = df_estab[df_estab['is_use_tnc']==1].groupby(['base_location_Industry Group']).sum()['estab_exp_weight'].reset_index(name='useTNC')\n",
    "df_estab.loc[df_estab['is_use_tnc']==1, 'wghtd_from_deliveries_pcsuvpu'] = df_estab['estab_exp_weight'] * df_estab['no_of_from_deliveries_pcsuvpu']\n",
    "df_estab['wghtd_employment'] = df_estab['estab_exp_weight'] * df_estab['emp_total']\n",
    "delivTNC = df_estab[df_estab['is_use_tnc']==1].groupby(['base_location_Industry Group']).sum()['wghtd_from_deliveries_pcsuvpu'].reset_index(name='delivTNC').astype('int64')\n",
    "\n",
    "empIfUseTNC = df_estab[df_estab['is_use_tnc']==1].groupby(['base_location_Industry Group']).sum()['wghtd_employment'].reset_index(name='empIfUseTNC').astype('int64')\n",
    "empAll = df_estab.groupby(['base_location_Industry Group']).sum()['wghtd_employment'].reset_index(name='empAll').astype('int64')\n",
    "#empAll[\"IndustryGroup\"] = empAll['base_location_Industry Group'].map(indus_lookup)\n",
    "\n",
    "allEstab = df_estab.groupby(['base_location_Industry Group']).sum()['estab_exp_weight'].reset_index(name='totEstab')\n",
    "allEstab[\"IndustryGroup\"] = allEstab['base_location_Industry Group'].map(indus_lookup)\n",
    "TNC_Industry = allEstab.set_index('base_location_Industry Group').join(useTNC.set_index('base_location_Industry Group'))\n",
    "TNC_Industry = TNC_Industry.join(delivTNC.set_index('base_location_Industry Group'))\n",
    "TNC_Industry = TNC_Industry.join(empIfUseTNC.set_index('base_location_Industry Group'))\n",
    "TNC_Industry = TNC_Industry.join(empAll.set_index('base_location_Industry Group'))\n",
    "TNC_Industry.loc[:,'IndusAbbrv'] = TNC_Industry.index.map(ind_abrv)\n",
    "\n",
    "# Reorder columns\n",
    "TNC_Industry = TNC_Industry[['IndusAbbrv', 'useTNC', 'delivTNC', 'empIfUseTNC', 'empAll', 'totEstab']]\n",
    "\n",
    "TNC_Industry['pctUseTNC'] = TNC_Industry['useTNC'] / TNC_Industry['totEstab']\n",
    "TNC_Totals = pd.DataFrame(TNC_Industry[['useTNC', 'delivTNC', 'empIfUseTNC', 'empAll', 'totEstab']].sum().map('{:.0f}'.format)).T\n",
    "TNC_Industry['rateDelTNC'] = TNC_Industry['delivTNC'] / TNC_Industry['totEstab']\n",
    "TNC_Industry['delivEmpIfTNC'] = TNC_Industry['delivTNC'] / TNC_Industry['empIfUseTNC']\n",
    "\n",
    "NonRestRetl = pd.DataFrame(TNC_Industry[(TNC_Industry.index!=4) & (TNC_Industry.index!=11)]\\\n",
    "                             [['useTNC','delivTNC','empIfUseTNC','empAll','totEstab']].copy().sum()).T\n",
    "NonRestRetl['pctUseTNC'] = NonRestRetl['useTNC'] / NonRestRetl['totEstab']\n",
    "NonRestRetl['rateDelTNC'] = NonRestRetl['delivTNC'] / NonRestRetl['totEstab']\n",
    "NonRestRetl['delivEmpIfTNC'] = NonRestRetl['delivTNC'] / NonRestRetl['empIfUseTNC']\n",
    "\n",
    "TNC_Industry['useTNC'] = TNC_Industry['useTNC'].map('{:.0f}'.format)\n",
    "TNC_Industry['delivTNC'] = TNC_Industry['delivTNC'].map('{:.0f}'.format)\n",
    "TNC_Industry['totEstab'] = TNC_Industry['totEstab'].map('{:.0f}'.format)\n",
    "TNC_Industry['empIfUseTNC'] = TNC_Industry['empIfUseTNC'].map('{:.0f}'.format)\n",
    "TNC_Industry['empAll'] = TNC_Industry['empAll'].map('{:.0f}'.format)\n",
    "TNC_Industry['pctUseTNC'] = TNC_Industry['pctUseTNC'].map('{:.5f}'.format)\n",
    "TNC_Industry['rateDelTNC'] = TNC_Industry['rateDelTNC'].map('{:.5f}'.format)\n",
    "TNC_Industry['delivEmpIfTNC'] = TNC_Industry['delivEmpIfTNC'].map('{:.5f}'.format)\n",
    "\n",
    "NonRestRetl['useTNC'] = NonRestRetl['useTNC'].map('{:.0f}'.format)\n",
    "NonRestRetl['delivTNC'] = NonRestRetl['delivTNC'].map('{:.0f}'.format) \n",
    "NonRestRetl['totEstab'] = NonRestRetl['totEstab'].map('{:.0f}'.format)\n",
    "NonRestRetl['empIfUseTNC'] = NonRestRetl['empIfUseTNC'].map('{:.0f}'.format)\n",
    "NonRestRetl['empAll'] = NonRestRetl['empAll'].map('{:.0f}'.format)\n",
    "NonRestRetl['pctUseTNC'] = NonRestRetl['pctUseTNC'].map('{:.5f}'.format)\n",
    "NonRestRetl['rateDelTNC'] = NonRestRetl['rateDelTNC'].map('{:.5f}'.format)\n",
    "NonRestRetl['delivEmpIfTNC'] = NonRestRetl['delivEmpIfTNC'].map('{:.5f}'.format)\n",
    "NonRestRetl['type'] = 'NonRestRetl'\n",
    "\n",
    "print(\"Weighted Establishment Observations\")\n",
    "print(\"\\nNote: 'rateDelTNC' = delivery trips per day per establishment in industry\\n\")\n",
    "print(TNC_Industry.to_string(index=False))\n",
    "print(\"\\nAll Establishment Totals:\")\n",
    "print(TNC_Totals.to_string(index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>base_location_Industry Group</th>\n",
       "      <th>empAll</th>\n",
       "      <th>IndustryGroup</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3378</td>\n",
       "      <td>Agriculture/Mining</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>92184</td>\n",
       "      <td>Manufacturing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3327</td>\n",
       "      <td>Industrial/Utilities</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>168206</td>\n",
       "      <td>Retail</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>38724</td>\n",
       "      <td>Wholesale</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>71600</td>\n",
       "      <td>Construction</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>22314</td>\n",
       "      <td>Transportation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>271083</td>\n",
       "      <td>Info/Finance/Insurance/Real Estate/Professiona...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>241807</td>\n",
       "      <td>Education/Public/Other services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>230367</td>\n",
       "      <td>Medical/Health Services</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>374445</td>\n",
       "      <td>Leisure/Accommodations and Food</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    base_location_Industry Group  empAll  \\\n",
       "0                              1    3378   \n",
       "1                              2   92184   \n",
       "2                              3    3327   \n",
       "3                              4  168206   \n",
       "4                              5   38724   \n",
       "5                              6   71600   \n",
       "6                              7   22314   \n",
       "7                              8  271083   \n",
       "8                              9  241807   \n",
       "9                             10  230367   \n",
       "10                            11  374445   \n",
       "\n",
       "                                        IndustryGroup  \n",
       "0                                  Agriculture/Mining  \n",
       "1                                       Manufacturing  \n",
       "2                                Industrial/Utilities  \n",
       "3                                              Retail  \n",
       "4                                           Wholesale  \n",
       "5                                        Construction  \n",
       "6                                      Transportation  \n",
       "7   Info/Finance/Insurance/Real Estate/Professiona...  \n",
       "8                     Education/Public/Other services  \n",
       "9                             Medical/Health Services  \n",
       "10                    Leisure/Accommodations and Food  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "empAll[\"IndustryGroup\"] = empAll['base_location_Industry Group'].map(indus_lookup)\n",
    "empAll"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create table for Restaurant and Non-Restaurant Types\n",
    "usecols = ['useTNC', 'delivTNC', 'empIfUseTNC', 'empAll', 'totEstab', 'pctUseTNC', 'rateDelTNC', 'delivEmpIfTNC']\n",
    "\n",
    "Retail = TNC_Industry[TNC_Industry.index==4].reset_index()\n",
    "Retail = Retail[usecols]\n",
    "Retail['type'] = 'Retail'\n",
    "\n",
    "Restaurant = TNC_Industry[TNC_Industry.index==11].reset_index()\n",
    "Restaurant = Restaurant[usecols]\n",
    "Restaurant['type'] = 'Restaurant'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TNC Usage Rates by Three Primary Groups\n",
      "\n",
      "            useTNC delivTNC empIfUseTNC  empAll totEstab pctUseTNC rateDelTNC delivEmpIfTNC\n",
      "type                                                                                       \n",
      "NonRestRetl   1449      575       27532  974784    84497   0.01715    0.00680       0.02088\n",
      "Restaurant    3132     8184       78791  374445     9234   0.33914    0.88629       0.10387\n",
      "Retail         825     3766       26808  168206    10396   0.07935    0.36225       0.14048\n"
     ]
    }
   ],
   "source": [
    "# Create combined table of usage rates by three industry groups\n",
    "usecols = ['type', 'useTNC', 'delivTNC', 'empIfUseTNC','empAll','totEstab', 'pctUseTNC', 'rateDelTNC', 'delivEmpIfTNC'] #re-order\n",
    "TNC_Industry3 = pd.concat([NonRestRetl, Restaurant, Retail], axis=0, sort=False).reset_index().drop(['index'], axis=1)\n",
    "TNC_Industry3 = TNC_Industry3[usecols].set_index('type')\n",
    "\n",
    "print(\"TNC Usage Rates by Three Primary Groups\\n\")\n",
    "print(TNC_Industry3.to_string(index=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "639.4837313795"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    ".44129 * 84497 * 0.01715"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Read in TNC establishment-level records (driver summaries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>company_id</td>\n",
       "      <td>600026</td>\n",
       "      <td>600027</td>\n",
       "      <td>600028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Industry Group_Size Code (Group from Column D; Size 1=0-9 emp, 2=10+)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>company_name</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>Amazon Flex</td>\n",
       "      <td>Senpex</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_purpose</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>number_of_trips</td>\n",
       "      <td>14</td>\n",
       "      <td>26</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>base_location_Industry Group</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>company_location_address</td>\n",
       "      <td>1052 Woodlawn Ave</td>\n",
       "      <td>3980 Hatton St</td>\n",
       "      <td>6881 Alvarado Rd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>company_location_city</td>\n",
       "      <td>Chula Vista</td>\n",
       "      <td>San Diego</td>\n",
       "      <td>San Diego</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>company_location_state</td>\n",
       "      <td>California</td>\n",
       "      <td>California</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>company_location_zipcode</td>\n",
       "      <td>91911</td>\n",
       "      <td>92111</td>\n",
       "      <td>92120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>company_location_latitude</td>\n",
       "      <td>32.6106</td>\n",
       "      <td>32.8166</td>\n",
       "      <td>32.7755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>company_location_longitude</td>\n",
       "      <td>-117.086</td>\n",
       "      <td>-117.166</td>\n",
       "      <td>-117.051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>employees_fulltime_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>employees_parttime_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>no_of_emp_work</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Number of Employess (1=&lt;10, 2=10+)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>total_telecommute_from_home</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>is_use_tnc</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>no_of_deliveries</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>no_of_deliveries_pcsuvpu</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>no_of_deliveries_su</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>no_of_deliveries_cu</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_deliveries_warehouse</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_deliveries_airport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_deliveries_manufacture</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_deliveries_retail</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_deliveries_service</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_deliveries_other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_deliveries_dontknow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_deliveries_text_other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>no_of_from_deliveries</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>no_of_from_deliveries_pcsuvpu</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>no_of_from_deliveries_su</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>no_of_from_deliveries_cu</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_from_deliveries_warehouse</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_from_deliveries_airport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_from_deliveries_manufacture</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_from_deliveries_retail</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_from_deliveries_service</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_from_deliveries_other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_from_deliveries_dontknow</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>per_of_from_deliveries_text_other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>has_vehicles</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_count_total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_axle_cargotruck</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_semi_cargo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_cars_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_pickuptrucks_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_cargovans_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_servicevans_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>use_personal_vehicle</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_other_count_total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_other_axle_cargotruck</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_other_semi_cargo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_other_cars_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_other_pickuptrucks_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_other_cargovans_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_other_servicevans_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>vehicle_toll_trans_count</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Participated in Travel Survey?</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>LCV Owned or Leased</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>LCV other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>LCV Total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>SUT Owned or Leased</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>SUT other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>SUT Total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>MUT Owned or Leased</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>MUT other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>MUT Total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>TOTAL CVs</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>number_of_trips.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                    0  \\\n",
       "company_id                                                     600026   \n",
       "Industry Group_Size Code (Group from Column D; ...                NaN   \n",
       "company_name                                               Uber Eats    \n",
       "vehicle_purpose                                                   NaN   \n",
       "number_of_trips                                                    14   \n",
       "base_location_Industry Group                                      NaN   \n",
       "company_location_address                            1052 Woodlawn Ave   \n",
       "company_location_city                                     Chula Vista   \n",
       "company_location_state                                     California   \n",
       "company_location_zipcode                                        91911   \n",
       "company_location_latitude                                     32.6106   \n",
       "company_location_longitude                                   -117.086   \n",
       "employees_fulltime_count                                          NaN   \n",
       "employees_parttime_count                                          NaN   \n",
       "no_of_emp_work                                                    NaN   \n",
       "Number of Employess (1=<10, 2=10+)                                NaN   \n",
       "total_telecommute_from_home                                       NaN   \n",
       "is_use_tnc                                                        NaN   \n",
       "no_of_deliveries                                                  NaN   \n",
       "no_of_deliveries_pcsuvpu                                          NaN   \n",
       "no_of_deliveries_su                                               NaN   \n",
       "no_of_deliveries_cu                                               NaN   \n",
       "per_of_deliveries_warehouse                                       NaN   \n",
       "per_of_deliveries_airport                                         NaN   \n",
       "per_of_deliveries_manufacture                                     NaN   \n",
       "per_of_deliveries_retail                                          NaN   \n",
       "per_of_deliveries_service                                         NaN   \n",
       "per_of_deliveries_other                                           NaN   \n",
       "per_of_deliveries_dontknow                                        NaN   \n",
       "per_of_deliveries_text_other                                      NaN   \n",
       "no_of_from_deliveries                                             NaN   \n",
       "no_of_from_deliveries_pcsuvpu                                     NaN   \n",
       "no_of_from_deliveries_su                                          NaN   \n",
       "no_of_from_deliveries_cu                                          NaN   \n",
       "per_of_from_deliveries_warehouse                                  NaN   \n",
       "per_of_from_deliveries_airport                                    NaN   \n",
       "per_of_from_deliveries_manufacture                                NaN   \n",
       "per_of_from_deliveries_retail                                     NaN   \n",
       "per_of_from_deliveries_service                                    NaN   \n",
       "per_of_from_deliveries_other                                      NaN   \n",
       "per_of_from_deliveries_dontknow                                   NaN   \n",
       "per_of_from_deliveries_text_other                                 NaN   \n",
       "has_vehicles                                                      NaN   \n",
       "vehicle_count_total                                               NaN   \n",
       "vehicle_axle_cargotruck                                           NaN   \n",
       "vehicle_semi_cargo                                                NaN   \n",
       "vehicle_cars_count                                                NaN   \n",
       "vehicle_pickuptrucks_count                                        NaN   \n",
       "vehicle_cargovans_count                                           NaN   \n",
       "vehicle_servicevans_count                                         NaN   \n",
       "use_personal_vehicle                                              NaN   \n",
       "vehicle_other_count_total                                         NaN   \n",
       "vehicle_other_axle_cargotruck                                     NaN   \n",
       "vehicle_other_semi_cargo                                          NaN   \n",
       "vehicle_other_cars_count                                          NaN   \n",
       "vehicle_other_pickuptrucks_count                                  NaN   \n",
       "vehicle_other_cargovans_count                                     NaN   \n",
       "vehicle_other_servicevans_count                                   NaN   \n",
       "vehicle_toll_trans_count                                          NaN   \n",
       "Participated in Travel Survey?                                    NaN   \n",
       "LCV Owned or Leased                                               NaN   \n",
       "LCV other                                                         NaN   \n",
       "LCV Total                                                         NaN   \n",
       "SUT Owned or Leased                                               NaN   \n",
       "SUT other                                                         NaN   \n",
       "SUT Total                                                         NaN   \n",
       "MUT Owned or Leased                                               NaN   \n",
       "MUT other                                                         NaN   \n",
       "MUT Total                                                         NaN   \n",
       "TOTAL CVs                                                         NaN   \n",
       "number_of_trips.1                                                 NaN   \n",
       "\n",
       "                                                                 1  \\\n",
       "company_id                                                  600027   \n",
       "Industry Group_Size Code (Group from Column D; ...             NaN   \n",
       "company_name                                           Amazon Flex   \n",
       "vehicle_purpose                                                NaN   \n",
       "number_of_trips                                                 26   \n",
       "base_location_Industry Group                                   NaN   \n",
       "company_location_address                            3980 Hatton St   \n",
       "company_location_city                                    San Diego   \n",
       "company_location_state                                  California   \n",
       "company_location_zipcode                                     92111   \n",
       "company_location_latitude                                  32.8166   \n",
       "company_location_longitude                                -117.166   \n",
       "employees_fulltime_count                                       NaN   \n",
       "employees_parttime_count                                       NaN   \n",
       "no_of_emp_work                                                 NaN   \n",
       "Number of Employess (1=<10, 2=10+)                             NaN   \n",
       "total_telecommute_from_home                                    NaN   \n",
       "is_use_tnc                                                     NaN   \n",
       "no_of_deliveries                                               NaN   \n",
       "no_of_deliveries_pcsuvpu                                       NaN   \n",
       "no_of_deliveries_su                                            NaN   \n",
       "no_of_deliveries_cu                                            NaN   \n",
       "per_of_deliveries_warehouse                                    NaN   \n",
       "per_of_deliveries_airport                                      NaN   \n",
       "per_of_deliveries_manufacture                                  NaN   \n",
       "per_of_deliveries_retail                                       NaN   \n",
       "per_of_deliveries_service                                      NaN   \n",
       "per_of_deliveries_other                                        NaN   \n",
       "per_of_deliveries_dontknow                                     NaN   \n",
       "per_of_deliveries_text_other                                   NaN   \n",
       "no_of_from_deliveries                                          NaN   \n",
       "no_of_from_deliveries_pcsuvpu                                  NaN   \n",
       "no_of_from_deliveries_su                                       NaN   \n",
       "no_of_from_deliveries_cu                                       NaN   \n",
       "per_of_from_deliveries_warehouse                               NaN   \n",
       "per_of_from_deliveries_airport                                 NaN   \n",
       "per_of_from_deliveries_manufacture                             NaN   \n",
       "per_of_from_deliveries_retail                                  NaN   \n",
       "per_of_from_deliveries_service                                 NaN   \n",
       "per_of_from_deliveries_other                                   NaN   \n",
       "per_of_from_deliveries_dontknow                                NaN   \n",
       "per_of_from_deliveries_text_other                              NaN   \n",
       "has_vehicles                                                   NaN   \n",
       "vehicle_count_total                                            NaN   \n",
       "vehicle_axle_cargotruck                                        NaN   \n",
       "vehicle_semi_cargo                                             NaN   \n",
       "vehicle_cars_count                                             NaN   \n",
       "vehicle_pickuptrucks_count                                     NaN   \n",
       "vehicle_cargovans_count                                        NaN   \n",
       "vehicle_servicevans_count                                      NaN   \n",
       "use_personal_vehicle                                           NaN   \n",
       "vehicle_other_count_total                                      NaN   \n",
       "vehicle_other_axle_cargotruck                                  NaN   \n",
       "vehicle_other_semi_cargo                                       NaN   \n",
       "vehicle_other_cars_count                                       NaN   \n",
       "vehicle_other_pickuptrucks_count                               NaN   \n",
       "vehicle_other_cargovans_count                                  NaN   \n",
       "vehicle_other_servicevans_count                                NaN   \n",
       "vehicle_toll_trans_count                                       NaN   \n",
       "Participated in Travel Survey?                                 NaN   \n",
       "LCV Owned or Leased                                            NaN   \n",
       "LCV other                                                      NaN   \n",
       "LCV Total                                                      NaN   \n",
       "SUT Owned or Leased                                            NaN   \n",
       "SUT other                                                      NaN   \n",
       "SUT Total                                                      NaN   \n",
       "MUT Owned or Leased                                            NaN   \n",
       "MUT other                                                      NaN   \n",
       "MUT Total                                                      NaN   \n",
       "TOTAL CVs                                                      NaN   \n",
       "number_of_trips.1                                              NaN   \n",
       "\n",
       "                                                                   2  \n",
       "company_id                                                    600028  \n",
       "Industry Group_Size Code (Group from Column D; ...               NaN  \n",
       "company_name                                                 Senpex   \n",
       "vehicle_purpose                                                  NaN  \n",
       "number_of_trips                                                    4  \n",
       "base_location_Industry Group                                     NaN  \n",
       "company_location_address                            6881 Alvarado Rd  \n",
       "company_location_city                                      San Diego  \n",
       "company_location_state                                    California  \n",
       "company_location_zipcode                                       92120  \n",
       "company_location_latitude                                    32.7755  \n",
       "company_location_longitude                                  -117.051  \n",
       "employees_fulltime_count                                         NaN  \n",
       "employees_parttime_count                                         NaN  \n",
       "no_of_emp_work                                                   NaN  \n",
       "Number of Employess (1=<10, 2=10+)                               NaN  \n",
       "total_telecommute_from_home                                      NaN  \n",
       "is_use_tnc                                                       NaN  \n",
       "no_of_deliveries                                                 NaN  \n",
       "no_of_deliveries_pcsuvpu                                         NaN  \n",
       "no_of_deliveries_su                                              NaN  \n",
       "no_of_deliveries_cu                                              NaN  \n",
       "per_of_deliveries_warehouse                                      NaN  \n",
       "per_of_deliveries_airport                                        NaN  \n",
       "per_of_deliveries_manufacture                                    NaN  \n",
       "per_of_deliveries_retail                                         NaN  \n",
       "per_of_deliveries_service                                        NaN  \n",
       "per_of_deliveries_other                                          NaN  \n",
       "per_of_deliveries_dontknow                                       NaN  \n",
       "per_of_deliveries_text_other                                     NaN  \n",
       "no_of_from_deliveries                                            NaN  \n",
       "no_of_from_deliveries_pcsuvpu                                    NaN  \n",
       "no_of_from_deliveries_su                                         NaN  \n",
       "no_of_from_deliveries_cu                                         NaN  \n",
       "per_of_from_deliveries_warehouse                                 NaN  \n",
       "per_of_from_deliveries_airport                                   NaN  \n",
       "per_of_from_deliveries_manufacture                               NaN  \n",
       "per_of_from_deliveries_retail                                    NaN  \n",
       "per_of_from_deliveries_service                                   NaN  \n",
       "per_of_from_deliveries_other                                     NaN  \n",
       "per_of_from_deliveries_dontknow                                  NaN  \n",
       "per_of_from_deliveries_text_other                                NaN  \n",
       "has_vehicles                                                     NaN  \n",
       "vehicle_count_total                                              NaN  \n",
       "vehicle_axle_cargotruck                                          NaN  \n",
       "vehicle_semi_cargo                                               NaN  \n",
       "vehicle_cars_count                                               NaN  \n",
       "vehicle_pickuptrucks_count                                       NaN  \n",
       "vehicle_cargovans_count                                          NaN  \n",
       "vehicle_servicevans_count                                        NaN  \n",
       "use_personal_vehicle                                             NaN  \n",
       "vehicle_other_count_total                                        NaN  \n",
       "vehicle_other_axle_cargotruck                                    NaN  \n",
       "vehicle_other_semi_cargo                                         NaN  \n",
       "vehicle_other_cars_count                                         NaN  \n",
       "vehicle_other_pickuptrucks_count                                 NaN  \n",
       "vehicle_other_cargovans_count                                    NaN  \n",
       "vehicle_other_servicevans_count                                  NaN  \n",
       "vehicle_toll_trans_count                                         NaN  \n",
       "Participated in Travel Survey?                                   NaN  \n",
       "LCV Owned or Leased                                              NaN  \n",
       "LCV other                                                        NaN  \n",
       "LCV Total                                                        NaN  \n",
       "SUT Owned or Leased                                              NaN  \n",
       "SUT other                                                        NaN  \n",
       "SUT Total                                                        NaN  \n",
       "MUT Owned or Leased                                              NaN  \n",
       "MUT other                                                        NaN  \n",
       "MUT Total                                                        NaN  \n",
       "TOTAL CVs                                                        NaN  \n",
       "number_of_trips.1                                                NaN  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read input data -- TNC establishment file\n",
    "df_tnc_driver = pd.read_excel(os.path.join(root_dir, raw_data_dir, \n",
    "                                      r\"TNC\\TNC Travel Survey_Data Submittal_1-19-23.xlsx\"),\n",
    "                                     sheet_name=r\"Establishment Data\")\n",
    "\n",
    "df_tnc_driver.set_index('company_id')\n",
    "df_tnc_driver.head(3).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>company_id</th>\n",
       "      <th>client_app</th>\n",
       "      <th>number_of_trips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>600026</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>600027</td>\n",
       "      <td>Amazon Flex</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>600028</td>\n",
       "      <td>Senpex</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>600029</td>\n",
       "      <td>Grub Hub</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>600031</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id   client_app  number_of_trips\n",
       "0      600026   Uber Eats                14\n",
       "1      600027  Amazon Flex               26\n",
       "2      600028      Senpex                 4\n",
       "3      600029    Grub Hub                 9\n",
       "4      600031   Uber Eats                30"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tnc_driver = df_tnc_driver[['company_id','company_name','number_of_trips']]\n",
    "df_tnc_driver = df_tnc_driver.rename(columns = {'company_name': 'client_app'})\n",
    "df_tnc_driver.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>company_name</th>\n",
       "      <th>company_name_alt</th>\n",
       "      <th>Estb (ie veh)</th>\n",
       "      <th>Trips</th>\n",
       "      <th>Trips/Veh</th>\n",
       "      <th>Tnc_Cat</th>\n",
       "      <th>TNC_IndCat3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>amazon</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>34.0</td>\n",
       "      <td>748.0</td>\n",
       "      <td>22.000000</td>\n",
       "      <td>Package/Other</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>amazonflex</td>\n",
       "      <td>Amazon Flex</td>\n",
       "      <td>15.0</td>\n",
       "      <td>259.0</td>\n",
       "      <td>17.266667</td>\n",
       "      <td>Package/Other</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>amazonfresh</td>\n",
       "      <td>Amazon Fresh</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>Grocery</td>\n",
       "      <td>Retail</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>axhire</td>\n",
       "      <td>Axle Hire</td>\n",
       "      <td>4.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>15.250000</td>\n",
       "      <td>Package/Other</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>axle</td>\n",
       "      <td>Axle Hire</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.250000</td>\n",
       "      <td>Package/Other</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  company_name company_name_alt  Estb (ie veh)  Trips  Trips/Veh  \\\n",
       "0       amazon           Amazon           34.0  748.0  22.000000   \n",
       "1   amazonflex      Amazon Flex           15.0  259.0  17.266667   \n",
       "2  amazonfresh     Amazon Fresh            1.0   12.0  12.000000   \n",
       "3       axhire        Axle Hire            4.0   61.0  15.250000   \n",
       "4         axle        Axle Hire            NaN    NaN  15.250000   \n",
       "\n",
       "         Tnc_Cat  TNC_IndCat3  \n",
       "0  Package/Other  NonRestRetl  \n",
       "1  Package/Other  NonRestRetl  \n",
       "2        Grocery       Retail  \n",
       "3  Package/Other  NonRestRetl  \n",
       "4  Package/Other  NonRestRetl  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read input data -- lookup table for TNC client categories\n",
    "df_lookup = pd.read_excel(os.path.join(root_dir, lookup_dir, \n",
    "                                      r\"Lookups_v8.xlsx\"),\n",
    "                                     sheet_name=r\"TNC Categories\")\n",
    "df_lookup.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_lookup[\"company_name2\"] = df_lookup[\"company_name_alt\"].str.strip().str.lower().str.replace(\" \",\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'amazon': 'NonRestRetl',\n",
       " 'amazonflex': 'NonRestRetl',\n",
       " 'amazonfresh': 'Retail',\n",
       " 'axlehire': 'NonRestRetl',\n",
       " 'doordash': 'Restaurant',\n",
       " 'fantuan': 'Restaurant',\n",
       " 'gopuff': 'Retail',\n",
       " 'grubhub': 'Restaurant',\n",
       " 'instacart': 'Retail',\n",
       " 'pointpickup': 'Retail',\n",
       " 'postmates': 'Restaurant',\n",
       " 'returnmates': 'NonRestRetl',\n",
       " 'returnme': 'NonRestRetl',\n",
       " 'returntorunway': 'NonRestRetl',\n",
       " 'roadie': 'NonRestRetl',\n",
       " 'senpex': 'NonRestRetl',\n",
       " 'shipt': 'Retail',\n",
       " 'spark': 'Retail',\n",
       " 'ubereats': 'Restaurant'}"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TNC_IndCat = dict(zip(df_lookup['company_name2'],df_lookup['TNC_IndCat3']))\n",
    "TNC_IndCat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "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>company_id</th>\n",
       "      <th>client_app</th>\n",
       "      <th>number_of_trips</th>\n",
       "      <th>client_app2</th>\n",
       "      <th>IndCat3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>600026</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>14</td>\n",
       "      <td>ubereats</td>\n",
       "      <td>Restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>600027</td>\n",
       "      <td>Amazon Flex</td>\n",
       "      <td>26</td>\n",
       "      <td>amazonflex</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>600028</td>\n",
       "      <td>Senpex</td>\n",
       "      <td>4</td>\n",
       "      <td>senpex</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>600029</td>\n",
       "      <td>Grub Hub</td>\n",
       "      <td>9</td>\n",
       "      <td>grubhub</td>\n",
       "      <td>Restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>600031</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>30</td>\n",
       "      <td>ubereats</td>\n",
       "      <td>Restaurant</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id   client_app  number_of_trips client_app2      IndCat3\n",
       "0      600026   Uber Eats                14    ubereats   Restaurant\n",
       "1      600027  Amazon Flex               26  amazonflex  NonRestRetl\n",
       "2      600028      Senpex                 4      senpex  NonRestRetl\n",
       "3      600029    Grub Hub                 9     grubhub   Restaurant\n",
       "4      600031   Uber Eats                30    ubereats   Restaurant"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tnc_driver['client_app2'] = df_tnc_driver['client_app'].str.strip().str.lower().str.replace(\" \",\"\")\n",
    "df_tnc_driver.loc[:,'IndCat3'] = df_tnc_driver['client_app2'].map(TNC_IndCat)\n",
    "df_tnc_driver.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "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>company_id</th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>driver_id</th>\n",
       "      <th>trip_number</th>\n",
       "      <th>trip_load_status</th>\n",
       "      <th>trip_load_weight</th>\n",
       "      <th>activity_type</th>\n",
       "      <th>activity_type_other</th>\n",
       "      <th>placetype</th>\n",
       "      <th>placetype_other</th>\n",
       "      <th>...</th>\n",
       "      <th>cargo_do_weight</th>\n",
       "      <th>travel_date</th>\n",
       "      <th>arrival_time</th>\n",
       "      <th>departure_time</th>\n",
       "      <th>used_other_vehicle</th>\n",
       "      <th>participation_type</th>\n",
       "      <th>vehicle_id.1</th>\n",
       "      <th>Lower Estimate Weight Factor</th>\n",
       "      <th>Most Likely Estimate Weight Factor</th>\n",
       "      <th>Upper Estimate Weight Factor</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>600026</td>\n",
       "      <td>1371</td>\n",
       "      <td>1744</td>\n",
       "      <td>0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13:39:00</td>\n",
       "      <td>2</td>\n",
       "      <td>Smartphone</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>600026</td>\n",
       "      <td>1371</td>\n",
       "      <td>1744</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>14:03:00</td>\n",
       "      <td>14:33:00</td>\n",
       "      <td>2</td>\n",
       "      <td>Smartphone</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>600026</td>\n",
       "      <td>1371</td>\n",
       "      <td>1744</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>14:39:00</td>\n",
       "      <td>14:42:00</td>\n",
       "      <td>2</td>\n",
       "      <td>Smartphone</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>600026</td>\n",
       "      <td>1371</td>\n",
       "      <td>1744</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>89.0</td>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>14:54:03</td>\n",
       "      <td>15:01:21</td>\n",
       "      <td>2</td>\n",
       "      <td>Smartphone</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>600026</td>\n",
       "      <td>1371</td>\n",
       "      <td>1744</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-08-29</td>\n",
       "      <td>15:09:00</td>\n",
       "      <td>15:12:00</td>\n",
       "      <td>2</td>\n",
       "      <td>Smartphone</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id  vehicle_id  driver_id  trip_number  trip_load_status  \\\n",
       "0      600026        1371       1744            0               3.0   \n",
       "1      600026        1371       1744            1               NaN   \n",
       "2      600026        1371       1744            2               NaN   \n",
       "3      600026        1371       1744            3               NaN   \n",
       "4      600026        1371       1744            4               NaN   \n",
       "\n",
       "   trip_load_weight  activity_type activity_type_other  placetype  \\\n",
       "0               NaN             14                 NaN         11   \n",
       "1               NaN              6                 NaN          6   \n",
       "2               NaN              5                 NaN         11   \n",
       "3               NaN              5                 NaN         11   \n",
       "4               NaN              6                 NaN          5   \n",
       "\n",
       "  placetype_other  ... cargo_do_weight travel_date arrival_time  \\\n",
       "0             NaN  ...             NaN  2022-08-29          NaN   \n",
       "1             NaN  ...             NaN  2022-08-29     14:03:00   \n",
       "2             NaN  ...            11.0  2022-08-29     14:39:00   \n",
       "3             NaN  ...            89.0  2022-08-29     14:54:03   \n",
       "4             NaN  ...             NaN  2022-08-29     15:09:00   \n",
       "\n",
       "  departure_time  used_other_vehicle  participation_type  vehicle_id.1  \\\n",
       "0       13:39:00                   2          Smartphone           NaN   \n",
       "1       14:33:00                   2          Smartphone           NaN   \n",
       "2       14:42:00                   2          Smartphone           NaN   \n",
       "3       15:01:21                   2          Smartphone           NaN   \n",
       "4       15:12:00                   2          Smartphone           NaN   \n",
       "\n",
       "   Lower Estimate Weight Factor Most Likely Estimate Weight Factor  \\\n",
       "0                           NaN                                NaN   \n",
       "1                           NaN                                NaN   \n",
       "2                           NaN                                NaN   \n",
       "3                           NaN                                NaN   \n",
       "4                           NaN                                NaN   \n",
       "\n",
       "  Upper Estimate Weight Factor  \n",
       "0                          NaN  \n",
       "1                          NaN  \n",
       "2                          NaN  \n",
       "3                          NaN  \n",
       "4                          NaN  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read input data -- TNC trips\n",
    "df_tnc_trips = pd.read_excel(os.path.join(root_dir, raw_data_dir, \n",
    "                                      r\"TNC\\TNC Travel Survey_Data Submittal_1-19-23.xlsx\"),\n",
    "                                     sheet_name=r\"Trips\")\n",
    "\n",
    "df_tnc_trips.set_index('company_id')\n",
    "df_tnc_trips.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>company_id</th>\n",
       "      <th>clientPickups</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>600026</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>600027</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>600028</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>600029</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>600031</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id  clientPickups\n",
       "0      600026              3\n",
       "1      600027              4\n",
       "2      600028              1\n",
       "3      600029              3\n",
       "4      600031              4"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sum number of client pickup stops for each driver (total unique clients)\n",
    "temp = df_tnc_trips[df_tnc_trips['activity_type']==6].groupby(['company_id',\\\n",
    "                                                                        'location_placename',\\\n",
    "                                                                        'location_address']).size().reset_index(name='clientPickups')\n",
    "clientPickups = temp.groupby(['company_id']).count()\n",
    "clientPickups = clientPickups['clientPickups'].reset_index()\n",
    "clientPickups.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>company_id</th>\n",
       "      <th>homeDropoffs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>600026</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>600027</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>600028</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>600031</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>600035</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id  homeDropoffs\n",
       "0      600026             7\n",
       "1      600027            12\n",
       "2      600028             1\n",
       "3      600031            10\n",
       "4      600035            71"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sum number of home dropoffs for each driver\n",
    "homeDropoffs = df_tnc_trips[(df_tnc_trips['activity_type']==5) &\\\n",
    "                           (df_tnc_trips['placetype']==11)].groupby(['company_id']).size().reset_index(name='homeDropoffs')\n",
    "homeDropoffs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>company_id</th>\n",
       "      <th>businessDropoffs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>600026</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>600027</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>600029</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>600031</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>600032</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id  businessDropoffs\n",
       "0      600026                 3\n",
       "1      600027                 2\n",
       "2      600029                 2\n",
       "3      600031                 3\n",
       "4      600032                 2"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sum number of business dropoffs for each driver\n",
    "bizDropoffs = df_tnc_trips[(df_tnc_trips['activity_type']==5) &\\\n",
    "                           (df_tnc_trips['placetype']!=11)].groupby(['company_id']).size().reset_index(name='businessDropoffs')\n",
    "bizDropoffs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "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>company_id</th>\n",
       "      <th>client_app</th>\n",
       "      <th>number_of_trips</th>\n",
       "      <th>client_app2</th>\n",
       "      <th>IndCat3</th>\n",
       "      <th>clientPickups</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>600026</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>14</td>\n",
       "      <td>ubereats</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>600027</td>\n",
       "      <td>Amazon Flex</td>\n",
       "      <td>26</td>\n",
       "      <td>amazonflex</td>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>600028</td>\n",
       "      <td>Senpex</td>\n",
       "      <td>4</td>\n",
       "      <td>senpex</td>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>600029</td>\n",
       "      <td>Grub Hub</td>\n",
       "      <td>9</td>\n",
       "      <td>grubhub</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>600031</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>30</td>\n",
       "      <td>ubereats</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id   client_app  number_of_trips client_app2      IndCat3  \\\n",
       "0      600026   Uber Eats                14    ubereats   Restaurant   \n",
       "1      600027  Amazon Flex               26  amazonflex  NonRestRetl   \n",
       "2      600028      Senpex                 4      senpex  NonRestRetl   \n",
       "3      600029    Grub Hub                 9     grubhub   Restaurant   \n",
       "4      600031   Uber Eats                30    ubereats   Restaurant   \n",
       "\n",
       "   clientPickups  \n",
       "0            3.0  \n",
       "1            4.0  \n",
       "2            1.0  \n",
       "3            3.0  \n",
       "4            4.0  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tnc_driver = df_tnc_driver.merge(clientPickups, how='left', on='company_id')\n",
    "df_tnc_driver.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>company_id</th>\n",
       "      <th>client_app</th>\n",
       "      <th>number_of_trips</th>\n",
       "      <th>client_app2</th>\n",
       "      <th>IndCat3</th>\n",
       "      <th>clientPickups</th>\n",
       "      <th>homeDropoffs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>600026</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>14</td>\n",
       "      <td>ubereats</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>600027</td>\n",
       "      <td>Amazon Flex</td>\n",
       "      <td>26</td>\n",
       "      <td>amazonflex</td>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>4.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>600028</td>\n",
       "      <td>Senpex</td>\n",
       "      <td>4</td>\n",
       "      <td>senpex</td>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>600029</td>\n",
       "      <td>Grub Hub</td>\n",
       "      <td>9</td>\n",
       "      <td>grubhub</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>600031</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>30</td>\n",
       "      <td>ubereats</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id   client_app  number_of_trips client_app2      IndCat3  \\\n",
       "0      600026   Uber Eats                14    ubereats   Restaurant   \n",
       "1      600027  Amazon Flex               26  amazonflex  NonRestRetl   \n",
       "2      600028      Senpex                 4      senpex  NonRestRetl   \n",
       "3      600029    Grub Hub                 9     grubhub   Restaurant   \n",
       "4      600031   Uber Eats                30    ubereats   Restaurant   \n",
       "\n",
       "   clientPickups  homeDropoffs  \n",
       "0            3.0           7.0  \n",
       "1            4.0          12.0  \n",
       "2            1.0           1.0  \n",
       "3            3.0           NaN  \n",
       "4            4.0          10.0  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tnc_driver = df_tnc_driver.merge(homeDropoffs, how='left', on='company_id')\n",
    "df_tnc_driver.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>company_id</th>\n",
       "      <th>client_app</th>\n",
       "      <th>number_of_trips</th>\n",
       "      <th>client_app2</th>\n",
       "      <th>IndCat3</th>\n",
       "      <th>clientPickups</th>\n",
       "      <th>homeDropoffs</th>\n",
       "      <th>businessDropoffs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>600026</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>14</td>\n",
       "      <td>ubereats</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>600027</td>\n",
       "      <td>Amazon Flex</td>\n",
       "      <td>26</td>\n",
       "      <td>amazonflex</td>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>4.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>600028</td>\n",
       "      <td>Senpex</td>\n",
       "      <td>4</td>\n",
       "      <td>senpex</td>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>600029</td>\n",
       "      <td>Grub Hub</td>\n",
       "      <td>9</td>\n",
       "      <td>grubhub</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>600031</td>\n",
       "      <td>Uber Eats</td>\n",
       "      <td>30</td>\n",
       "      <td>ubereats</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   company_id   client_app  number_of_trips client_app2      IndCat3  \\\n",
       "0      600026   Uber Eats                14    ubereats   Restaurant   \n",
       "1      600027  Amazon Flex               26  amazonflex  NonRestRetl   \n",
       "2      600028      Senpex                 4      senpex  NonRestRetl   \n",
       "3      600029    Grub Hub                 9     grubhub   Restaurant   \n",
       "4      600031   Uber Eats                30    ubereats   Restaurant   \n",
       "\n",
       "   clientPickups  homeDropoffs  businessDropoffs  \n",
       "0            3.0           7.0               3.0  \n",
       "1            4.0          12.0               2.0  \n",
       "2            1.0           1.0               NaN  \n",
       "3            3.0           NaN               2.0  \n",
       "4            4.0          10.0               3.0  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tnc_driver = df_tnc_driver.merge(bizDropoffs, how='left', on='company_id')\n",
    "df_tnc_driver.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>clientPickups</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IndCat3</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Restaurant</td>\n",
       "      <td>636.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Retail</td>\n",
       "      <td>197.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             clientPickups\n",
       "IndCat3                   \n",
       "NonRestRetl          152.0\n",
       "Restaurant           636.0\n",
       "Retail               197.0"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate TNC Driver Pickups by Industry Category\n",
    "tnc_by_client = df_tnc_driver.groupby('IndCat3').sum()['clientPickups'].reset_index(name='clientPickups')\n",
    "tnc_by_client.set_index('IndCat3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>homeDropoffs</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IndCat3</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>719.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Restaurant</td>\n",
       "      <td>709.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Retail</td>\n",
       "      <td>508.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             homeDropoffs\n",
       "IndCat3                  \n",
       "NonRestRetl         719.0\n",
       "Restaurant          709.0\n",
       "Retail              508.0"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate TNC Home Dropoffs by Industry Category\n",
    "tnc_by_homedel = df_tnc_driver.groupby('IndCat3').sum()['homeDropoffs'].reset_index(name='homeDropoffs')\n",
    "tnc_by_homedel.set_index('IndCat3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>businessDropoffs</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IndCat3</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>292.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Restaurant</td>\n",
       "      <td>313.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Retail</td>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             businessDropoffs\n",
       "IndCat3                      \n",
       "NonRestRetl             292.0\n",
       "Restaurant              313.0\n",
       "Retail                   90.0"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Calculate TNC Business Dropoffs by Industry Category\n",
    "tnc_by_bizdel = df_tnc_driver.groupby('IndCat3').sum()['businessDropoffs'].reset_index(name='businessDropoffs')\n",
    "tnc_by_bizdel.set_index('IndCat3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     IndCat3 clientPickups delivTNC  tnc_expwght\n",
      " NonRestRetl           152      575      3.78289\n",
      "  Restaurant           636     8184     12.86792\n",
      "      Retail           197     3766     19.11675\n"
     ]
    }
   ],
   "source": [
    "# Calculate TNC Weights\n",
    "tnc_weights = tnc_by_client.merge(TNC_Industry3, left_on='IndCat3', right_on='type')\n",
    "\n",
    "tnc_weights['tnc_expwght'] = tnc_weights['delivTNC'].str.replace(\",\",\"\").astype(float) \\\n",
    "/ tnc_weights['clientPickups'].astype(float) \n",
    "\n",
    "tnc_weights['clientPickups'] = tnc_weights['clientPickups'].map('{:,.0f}'.format)\n",
    "\n",
    "tnc_weights['tnc_expwght'] = round(tnc_weights['tnc_expwght'],5)\n",
    "print(tnc_weights[['IndCat3','clientPickups','delivTNC','tnc_expwght']].to_string(index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create TNC expansion weights dictionary\n",
    "tnc_expwght = dict(zip(tnc_weights['IndCat3'],tnc_weights['tnc_expwght']))\n",
    "df_tnc_driver.loc[:, 'tnc_expwght'] = df_tnc_driver['IndCat3'].map(tnc_expwght)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'NonRestRetl': 3.78289, 'Restaurant': 12.86792, 'Retail': 19.11675}"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tnc_expwght"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Impact of TNC weights expansion on Total TNC trips\n",
      "\n",
      "raw_trips wghtd_trips\n",
      "    5,253      60,640\n"
     ]
    }
   ],
   "source": [
    "# Calculate impact on total trips\n",
    "df_tnc_driver['wghtd_trips'] = df_tnc_driver['number_of_trips'] * df_tnc_driver['tnc_expwght']\n",
    "\n",
    "tnc_summary = pd.DataFrame(df_tnc_driver[['number_of_trips','wghtd_trips']].sum()).T\n",
    "tnc_summary['number_of_trips'] = tnc_summary['number_of_trips'].map('{:,.0f}'.format)\n",
    "tnc_summary['wghtd_trips'] = tnc_summary['wghtd_trips'].map('{:,.0f}'.format)\n",
    "tnc_summary.rename(columns={'number_of_trips': 'raw_trips'}, inplace=True)\n",
    "\n",
    "print(\"Impact of TNC weights expansion on Total TNC trips\\n\")\n",
    "print(tnc_summary.to_string(index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tnc_driver.to_csv(os.path.join(root_dir, \n",
    "                                      raw_data_dir, \n",
    "                                      r\"TNC\\TNC_weighted.csv\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get weighted number of trips and average per driver-route\n",
    "df_tnc_driver['wghtd_client_picks'] = df_tnc_driver['clientPickups'] * df_tnc_driver['tnc_expwght']\n",
    "df_tnc_driver['wghtd_home_delivs'] = df_tnc_driver['homeDropoffs'] * df_tnc_driver['tnc_expwght']\n",
    "df_tnc_driver['wghtd_biz_delivs'] = df_tnc_driver['businessDropoffs'] * df_tnc_driver['tnc_expwght']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NonRestRetl Restaurant Retail\n",
      "        575      8,184  3,766\n"
     ]
    }
   ],
   "source": [
    "# Client pickups by TNCs\n",
    "clientPickups = df_tnc_driver.groupby(['IndCat3']).sum()['wghtd_client_picks'] \n",
    "clientPickups = pd.DataFrame(clientPickups.map('{:,.0f}'.format))\n",
    "print(clientPickups.T.to_string(index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NonRestRetl Restaurant Retail\n",
      "      2,720      9,123  9,711\n",
      "\n",
      "Food Deliveries = Restaurant + Retail\n",
      "\t18,835\n"
     ]
    }
   ],
   "source": [
    "# Calculate deliveries to households by TNCs -- food (retaurant+groceries), non-food parcels\n",
    "homeDeliveries = df_tnc_driver.groupby(['IndCat3']).sum()['wghtd_home_delivs']\n",
    "food = homeDeliveries['Restaurant']+homeDeliveries['Retail']\n",
    "homeDeliveries = pd.DataFrame(homeDeliveries.map('{:,.0f}'.format))\n",
    "\n",
    "print(homeDeliveries.T.to_string(index=False))\n",
    "print(\"\\nFood Deliveries = Restaurant + Retail\")\n",
    "print(f\"\\t{food:,.0f}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NonRestRetl Restaurant Retail\n",
      "      1,105      4,028  1,721\n"
     ]
    }
   ],
   "source": [
    "# Calculate deliveries to businesses by TNCs\n",
    "clientPickups = df_tnc_driver.groupby(['IndCat3']).sum()['wghtd_biz_delivs']\n",
    "clientPickups = pd.DataFrame(clientPickups.map('{:,.0f}'.format))\n",
    "print(clientPickups.T.to_string(index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Clients per driver route:\n",
      "NonRestRetl Restaurant  Retail\n",
      "     1.6344     2.8778  2.0957\n",
      "\n",
      "Driver routes (weighted)\n",
      "NonRestRetl Restaurant Retail Total\n",
      "        352       2844   1797  4993\n"
     ]
    }
   ],
   "source": [
    "# Clients per Driver Route\n",
    "clientPickups = df_tnc_driver.groupby(['IndCat3']).sum()['wghtd_client_picks'] \\\n",
    "/ df_tnc_driver.groupby(['IndCat3']).sum()['tnc_expwght']\n",
    "clientPickups = pd.DataFrame(clientPickups.map('{:,.4f}'.format))\n",
    "print(\"Clients per driver route:\")\n",
    "print(clientPickups.T.to_string(index=False))\n",
    "\n",
    "# Driver routes\n",
    "driverRoutes = df_tnc_driver.groupby(['IndCat3']).sum()['tnc_expwght']\n",
    "driverRoutes['Total'] = driverRoutes.sum()\n",
    "driverRoutes = pd.DataFrame(driverRoutes.map('{:.0f}'.format))\n",
    "print(\"\\nDriver routes (weighted)\")\n",
    "print(driverRoutes.T.to_string(index=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get TNC Route Generation (routes = drivers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Driver Routes per Client Establishment using TNCs:\n",
      "NonRestRetl Restaurant   Retail\n",
      "    0.61184    0.34748  0.47716\n"
     ]
    }
   ],
   "source": [
    "# Driver Routes Per Client Establishment using TNCs\n",
    "driversPerClient = df_tnc_driver.groupby(['IndCat3']).sum()['tnc_expwght'] \\\n",
    "/ df_tnc_driver.groupby(['IndCat3']).sum()['wghtd_client_picks']\n",
    "driversPerClient = pd.DataFrame(driversPerClient.map('{:.5f}'.format))\n",
    "driversPerClient.rename(columns={0:'drivers_per_client'}, inplace=True)\n",
    "\n",
    "print(\"Driver Routes per Client Establishment using TNCs:\")\n",
    "print(driversPerClient.T.to_string(index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge with establishment data\n",
    "tncRouteGen = pd.merge(driversPerClient.astype('float').reset_index(), \n",
    "                       TNC_Industry3.astype('float').reset_index(), \n",
    "                       how='left', left_on='IndCat3', right_on='type')\n",
    "\n",
    "tncRouteGen = tncRouteGen[['IndCat3','pctUseTNC','rateDelTNC', 'delivEmpIfTNC','drivers_per_client']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>IndCat3</th>\n",
       "      <th>pctUseTNC</th>\n",
       "      <th>rateDelTNC</th>\n",
       "      <th>delivEmpIfTNC</th>\n",
       "      <th>drivers_per_client</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>0.01715</td>\n",
       "      <td>0.00680</td>\n",
       "      <td>0.02088</td>\n",
       "      <td>0.61184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>0.33914</td>\n",
       "      <td>0.88629</td>\n",
       "      <td>0.10387</td>\n",
       "      <td>0.34748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Retail</td>\n",
       "      <td>0.07935</td>\n",
       "      <td>0.36225</td>\n",
       "      <td>0.14048</td>\n",
       "      <td>0.47716</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       IndCat3  pctUseTNC  rateDelTNC  delivEmpIfTNC  drivers_per_client\n",
       "0  NonRestRetl    0.01715     0.00680        0.02088             0.61184\n",
       "1   Restaurant    0.33914     0.88629        0.10387             0.34748\n",
       "2       Retail    0.07935     0.36225        0.14048             0.47716"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tncRouteGen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>useTNC</th>\n",
       "      <th>delivTNC</th>\n",
       "      <th>empIfUseTNC</th>\n",
       "      <th>empAll</th>\n",
       "      <th>totEstab</th>\n",
       "      <th>pctUseTNC</th>\n",
       "      <th>rateDelTNC</th>\n",
       "      <th>delivEmpIfTNC</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>1449</td>\n",
       "      <td>575</td>\n",
       "      <td>27532</td>\n",
       "      <td>974784</td>\n",
       "      <td>84497</td>\n",
       "      <td>0.01715</td>\n",
       "      <td>0.00680</td>\n",
       "      <td>0.02088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Restaurant</td>\n",
       "      <td>3132</td>\n",
       "      <td>8184</td>\n",
       "      <td>78791</td>\n",
       "      <td>374445</td>\n",
       "      <td>9234</td>\n",
       "      <td>0.33914</td>\n",
       "      <td>0.88629</td>\n",
       "      <td>0.10387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Retail</td>\n",
       "      <td>825</td>\n",
       "      <td>3766</td>\n",
       "      <td>26808</td>\n",
       "      <td>168206</td>\n",
       "      <td>10396</td>\n",
       "      <td>0.07935</td>\n",
       "      <td>0.36225</td>\n",
       "      <td>0.14048</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            useTNC delivTNC empIfUseTNC  empAll totEstab pctUseTNC rateDelTNC  \\\n",
       "type                                                                            \n",
       "NonRestRetl   1449      575       27532  974784    84497   0.01715    0.00680   \n",
       "Restaurant    3132     8184       78791  374445     9234   0.33914    0.88629   \n",
       "Retail         825     3766       26808  168206    10396   0.07935    0.36225   \n",
       "\n",
       "            delivEmpIfTNC  \n",
       "type                       \n",
       "NonRestRetl       0.02088  \n",
       "Restaurant        0.10387  \n",
       "Retail            0.14048  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "TNC_Industry3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "351.7273350144"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "27532 * 0.02088 * 0.61184 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Driver routes (weighted)\n",
      "NonRestRetl Restaurant Retail Total\n",
      "        352       2844   1797  4993\n"
     ]
    }
   ],
   "source": [
    "print(\"\\nDriver routes (weighted)\")\n",
    "print(driverRoutes.T.to_string(index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>IndCat3</th>\n",
       "      <th>routes_wtd</th>\n",
       "      <th>employment_wtd</th>\n",
       "      <th>routes_per_emp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>352.0</td>\n",
       "      <td>974784.0</td>\n",
       "      <td>0.000361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2844.0</td>\n",
       "      <td>374445.0</td>\n",
       "      <td>0.007595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Retail</td>\n",
       "      <td>1797.0</td>\n",
       "      <td>168206.0</td>\n",
       "      <td>0.010683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>Totals</td>\n",
       "      <td>4993.0</td>\n",
       "      <td>1517435.0</td>\n",
       "      <td>0.003290</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       IndCat3  routes_wtd  employment_wtd  routes_per_emp\n",
       "0  NonRestRetl       352.0        974784.0        0.000361\n",
       "1   Restaurant      2844.0        374445.0        0.007595\n",
       "2       Retail      1797.0        168206.0        0.010683\n",
       "4       Totals      4993.0       1517435.0        0.003290"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create employment based rates\n",
    "routeGenRates = pd.merge(driverRoutes.astype('float').reset_index(), \n",
    "                         TNC_Industry3[['empAll']].astype('float').reset_index(), \n",
    "                         how='inner', left_on='IndCat3', right_on='type').drop(columns=['type'])\n",
    "\n",
    "routeGenRates.rename(columns={'tnc_expwght': 'routes_wtd', 'empAll': 'employment_wtd'}, inplace=True)\n",
    "routeGenRates.loc[4] = routeGenRates.sum()\n",
    "routeGenRates.loc[routeGenRates.index[-1], 'IndCat3'] = \"Totals\"\n",
    "routeGenRates['routes_per_emp'] = routeGenRates['routes_wtd'] / routeGenRates['employment_wtd']\n",
    "routeGenRates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Calibrate Route Gen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "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>mgra</th>\n",
       "      <th>taz</th>\n",
       "      <th>LUZ</th>\n",
       "      <th>pop</th>\n",
       "      <th>hhp</th>\n",
       "      <th>hs</th>\n",
       "      <th>hs_sf</th>\n",
       "      <th>hs_mf</th>\n",
       "      <th>hs_mh</th>\n",
       "      <th>hh</th>\n",
       "      <th>...</th>\n",
       "      <th>hotelroomtotal</th>\n",
       "      <th>parkactive</th>\n",
       "      <th>openspaceparkpreserve</th>\n",
       "      <th>beachactive</th>\n",
       "      <th>district27</th>\n",
       "      <th>milestocoast</th>\n",
       "      <th>acre</th>\n",
       "      <th>landacre</th>\n",
       "      <th>effective_acres</th>\n",
       "      <th>truckregiontype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3010</td>\n",
       "      <td>10</td>\n",
       "      <td>440</td>\n",
       "      <td>440</td>\n",
       "      <td>176</td>\n",
       "      <td>84</td>\n",
       "      <td>92</td>\n",
       "      <td>0</td>\n",
       "      <td>174</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>4.35</td>\n",
       "      <td>18.837621</td>\n",
       "      <td>18.837621</td>\n",
       "      <td>18.837621</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1797</td>\n",
       "      <td>28</td>\n",
       "      <td>130</td>\n",
       "      <td>68</td>\n",
       "      <td>56</td>\n",
       "      <td>0</td>\n",
       "      <td>56</td>\n",
       "      <td>0</td>\n",
       "      <td>48</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>15</td>\n",
       "      <td>0.64</td>\n",
       "      <td>2.872330</td>\n",
       "      <td>2.872330</td>\n",
       "      <td>2.872330</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4361</td>\n",
       "      <td>239</td>\n",
       "      <td>549</td>\n",
       "      <td>549</td>\n",
       "      <td>200</td>\n",
       "      <td>23</td>\n",
       "      <td>177</td>\n",
       "      <td>0</td>\n",
       "      <td>192</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>13</td>\n",
       "      <td>12.22</td>\n",
       "      <td>25.713898</td>\n",
       "      <td>25.713898</td>\n",
       "      <td>25.713898</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>340</td>\n",
       "      <td>151</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.17</td>\n",
       "      <td>2.678374</td>\n",
       "      <td>2.678374</td>\n",
       "      <td>2.678374</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>388</td>\n",
       "      <td>151</td>\n",
       "      <td>90</td>\n",
       "      <td>90</td>\n",
       "      <td>43</td>\n",
       "      <td>43</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>36</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>0.47</td>\n",
       "      <td>4.057765</td>\n",
       "      <td>4.057765</td>\n",
       "      <td>4.057765</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 62 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   mgra   taz  LUZ  pop  hhp   hs  hs_sf  hs_mf  hs_mh   hh  ...  \\\n",
       "0     1  3010   10  440  440  176     84     92      0  174  ...   \n",
       "1     2  1797   28  130   68   56      0     56      0   48  ...   \n",
       "2     3  4361  239  549  549  200     23    177      0  192  ...   \n",
       "3     4   340  151    5    5    3      3      0      0    2  ...   \n",
       "4     5   388  151   90   90   43     43      0      0   36  ...   \n",
       "\n",
       "   hotelroomtotal  parkactive  openspaceparkpreserve  beachactive  district27  \\\n",
       "0               0         0.0                    0.0          0.0           9   \n",
       "1               0         0.0                    0.0          0.0          15   \n",
       "2               0         0.0                    0.0          0.0          13   \n",
       "3               0         0.0                    0.0          0.0           2   \n",
       "4               0         0.0                    0.0          0.0           2   \n",
       "\n",
       "   milestocoast       acre   landacre  effective_acres  truckregiontype  \n",
       "0          4.35  18.837621  18.837621        18.837621                1  \n",
       "1          0.64   2.872330   2.872330         2.872330                1  \n",
       "2         12.22  25.713898  25.713898        25.713898                1  \n",
       "3          0.17   2.678374   2.678374         2.678374                1  \n",
       "4          0.47   4.057765   4.057765         4.057765                1  \n",
       "\n",
       "[5 rows x 62 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read land use data \n",
    "df_MGRA = pd.read_csv(os.path.join(root_dir, \n",
    "                                      raw_data_dir, \n",
    "                                      r\"Land_Use\\mgra15_based_input_2022_02_cvm.csv\"))\n",
    "df_MGRA.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create short names for industries\n",
    "indus_abrv = {\n",
    "    1: \"AGM\",\n",
    "    2: \"MFG\",\n",
    "    3: \"IUT\",\n",
    "    4: \"RET\",\n",
    "    5: \"WHL\",\n",
    "    6: \"CON\",\n",
    "    7: \"TRN\",\n",
    "    8: \"IFR\",\n",
    "    9: \"EPO\",\n",
    "    10: \"MHS\",\n",
    "    11: \"LAF\",\n",
    "    12: \"MIL\"\n",
    "}\n",
    "\n",
    "# Reverse lookup\n",
    "abrv_indus = dict(zip(list(indus_abrv.values()), list(indus_abrv.keys())))\n",
    "\n",
    "# Cross walk between MGRA employment and model employment categories\n",
    "emp_mgra_to_model = {\n",
    "    1:[\"emp_ag_min\"],\n",
    "    2:[\"emp_mnf\"],\n",
    "    3:[\"emp_utl\"],\n",
    "    4:[\"emp_ret\"],\n",
    "    5:[\"emp_whl\"],\n",
    "    6:[\"emp_con\"],\n",
    "    7:[\"emp_trn_wrh\"],\n",
    "    8:[\"emp_fin_res_mgm\",\"emp_bus_svcs\"],\n",
    "    9:[\"emp_educ\",\"emp_gov\",\"emp_oth\",\"emp_ent\",\"emp_accm\"],\n",
    "    10:[\"emp_hlth\"],\n",
    "    11:[\"emp_food\"],\n",
    "    12:[\"emp_mil\"]\n",
    "}\n",
    "\n",
    "# Reverse lookup\n",
    "emp_survey_to_mgra = dict()\n",
    "for key, values in emp_mgra_to_model.items():\n",
    "    for v in values:\n",
    "        emp_survey_to_mgra[v] = key\n",
    "#emp_survey_to_mgra"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bucket round function\n",
    "def bucketRound(arr, thr=0.5):\n",
    "    if isinstance(arr, list) or isinstance(arr, np.ndarray):\n",
    "        arr = np.asarray(arr).astype('float')\n",
    "        out = np.zeros(len(arr)).astype('int64')\n",
    "        bucket = float(0)\n",
    "        for i in range(len(arr)):\n",
    "            out[i] = int(arr[i])\n",
    "            bucket += arr[i]%1\n",
    "            if bucket > thr:\n",
    "                out[i] += 1\n",
    "                bucket = 0\n",
    "        return out\n",
    "    else:\n",
    "        print(arr)\n",
    "        print(\"Error: Function requires inputs as an array or list of values.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>mgra_jobs</th>\n",
       "      <th>emp_group_no</th>\n",
       "      <th>emp_mod_sector</th>\n",
       "      <th>IndCat3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>emp_gov</td>\n",
       "      <td>121320</td>\n",
       "      <td>9.0</td>\n",
       "      <td>EPO</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_mil</td>\n",
       "      <td>110000</td>\n",
       "      <td>12.0</td>\n",
       "      <td>MIL</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_ag_min</td>\n",
       "      <td>3305</td>\n",
       "      <td>1.0</td>\n",
       "      <td>AGM</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_bus_svcs</td>\n",
       "      <td>267270</td>\n",
       "      <td>8.0</td>\n",
       "      <td>IFR</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_fin_res_mgm</td>\n",
       "      <td>105702</td>\n",
       "      <td>8.0</td>\n",
       "      <td>IFR</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_educ</td>\n",
       "      <td>130918</td>\n",
       "      <td>9.0</td>\n",
       "      <td>EPO</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_hlth</td>\n",
       "      <td>199896</td>\n",
       "      <td>10.0</td>\n",
       "      <td>MHS</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_ret</td>\n",
       "      <td>144185</td>\n",
       "      <td>4.0</td>\n",
       "      <td>RET</td>\n",
       "      <td>Retail</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_trn_wrh</td>\n",
       "      <td>32237</td>\n",
       "      <td>7.0</td>\n",
       "      <td>TRN</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_con</td>\n",
       "      <td>77331</td>\n",
       "      <td>6.0</td>\n",
       "      <td>CON</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_utl</td>\n",
       "      <td>4800</td>\n",
       "      <td>3.0</td>\n",
       "      <td>IUT</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_mnf</td>\n",
       "      <td>126720</td>\n",
       "      <td>2.0</td>\n",
       "      <td>MFG</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_whl</td>\n",
       "      <td>43668</td>\n",
       "      <td>5.0</td>\n",
       "      <td>WHL</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_ent</td>\n",
       "      <td>31417</td>\n",
       "      <td>9.0</td>\n",
       "      <td>EPO</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_accm</td>\n",
       "      <td>18875</td>\n",
       "      <td>9.0</td>\n",
       "      <td>EPO</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_food</td>\n",
       "      <td>140151</td>\n",
       "      <td>11.0</td>\n",
       "      <td>LAF</td>\n",
       "      <td>Restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_oth</td>\n",
       "      <td>50794</td>\n",
       "      <td>9.0</td>\n",
       "      <td>EPO</td>\n",
       "      <td>NonRestRetl</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_non_ws_wfh</td>\n",
       "      <td>128000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_non_ws_oth</td>\n",
       "      <td>407792</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>emp_tot</td>\n",
       "      <td>2144381</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 mgra_jobs  emp_group_no emp_mod_sector      IndCat3\n",
       "emp_gov             121320           9.0            EPO  NonRestRetl\n",
       "emp_mil             110000          12.0            MIL  NonRestRetl\n",
       "emp_ag_min            3305           1.0            AGM  NonRestRetl\n",
       "emp_bus_svcs        267270           8.0            IFR  NonRestRetl\n",
       "emp_fin_res_mgm     105702           8.0            IFR  NonRestRetl\n",
       "emp_educ            130918           9.0            EPO  NonRestRetl\n",
       "emp_hlth            199896          10.0            MHS  NonRestRetl\n",
       "emp_ret             144185           4.0            RET       Retail\n",
       "emp_trn_wrh          32237           7.0            TRN  NonRestRetl\n",
       "emp_con              77331           6.0            CON  NonRestRetl\n",
       "emp_utl               4800           3.0            IUT  NonRestRetl\n",
       "emp_mnf             126720           2.0            MFG  NonRestRetl\n",
       "emp_whl              43668           5.0            WHL  NonRestRetl\n",
       "emp_ent              31417           9.0            EPO  NonRestRetl\n",
       "emp_accm             18875           9.0            EPO  NonRestRetl\n",
       "emp_food            140151          11.0            LAF   Restaurant\n",
       "emp_oth              50794           9.0            EPO  NonRestRetl\n",
       "emp_non_ws_wfh      128000           0.0              0         None\n",
       "emp_non_ws_oth      407792           0.0              0         None\n",
       "emp_tot            2144381           0.0              0         None"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Select only relevant employment fields\n",
    "use_columns = [col for col in df_MGRA.columns if 'emp_' in col]\n",
    "#[use_columns.remove(x) for x in ['emp_non_ws_wfh','emp_non_ws_oth','emp_tot']]\n",
    "\n",
    "# Create new dataframe and identify model sectors\n",
    "df_MGRA_emp = pd.DataFrame(df_MGRA[use_columns].sum()).rename(columns={0:'mgra_jobs'})\n",
    "df_MGRA_emp.loc[:, 'emp_group_no'] = df_MGRA_emp.index.map(emp_survey_to_mgra)\n",
    "df_MGRA_emp.loc[:, 'emp_mod_sector'] = df_MGRA_emp.emp_group_no.map(indus_abrv)\n",
    "df_MGRA_emp.fillna(0, inplace=True)\n",
    "\n",
    "# Identify TNC3 sectors\n",
    "df_MGRA_emp['IndCat3'] = 'NonRestRetl'\n",
    "df_MGRA_emp.loc[df_MGRA_emp['emp_group_no'] == 11, 'IndCat3'] = 'Restaurant'\n",
    "df_MGRA_emp.loc[df_MGRA_emp['emp_group_no'] == 4, 'IndCat3'] = 'Retail'\n",
    "df_MGRA_emp.loc[df_MGRA_emp['emp_group_no'] == 0, 'IndCat3'] = 'None'\n",
    "df_MGRA_emp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>IndCat3</th>\n",
       "      <th>mgra_jobs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>1324253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>140151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Retail</td>\n",
       "      <td>144185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>Totals</td>\n",
       "      <td>1608589</td>\n",
       "      <td>1608589</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            IndCat3  mgra_jobs\n",
       "0       NonRestRetl    1324253\n",
       "1        Restaurant     140151\n",
       "2            Retail     144185\n",
       "Totals      1608589    1608589"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Collect jobs summaries by model sectors\n",
    "mgraEmpCat3 = pd.DataFrame(df_MGRA_emp[df_MGRA_emp['emp_group_no']>0].groupby(['IndCat3'])['mgra_jobs'].sum()).reset_index()\n",
    "mgraEmpCat3.loc['Totals'] = mgraEmpCat3.sum()['mgra_jobs']\n",
    "mgraEmpCat3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>IndCat3</th>\n",
       "      <th>routes_wtd</th>\n",
       "      <th>employment_wtd</th>\n",
       "      <th>routes_per_emp</th>\n",
       "      <th>mgra_jobs</th>\n",
       "      <th>routes_per_emp_adj1</th>\n",
       "      <th>routes_calibrated</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>NonRestRetl</td>\n",
       "      <td>352.0</td>\n",
       "      <td>974784.0</td>\n",
       "      <td>0.000361</td>\n",
       "      <td>1324253</td>\n",
       "      <td>0.000266</td>\n",
       "      <td>352.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Restaurant</td>\n",
       "      <td>2844.0</td>\n",
       "      <td>374445.0</td>\n",
       "      <td>0.007595</td>\n",
       "      <td>140151</td>\n",
       "      <td>0.020292</td>\n",
       "      <td>2844.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Retail</td>\n",
       "      <td>1797.0</td>\n",
       "      <td>168206.0</td>\n",
       "      <td>0.010683</td>\n",
       "      <td>144185</td>\n",
       "      <td>0.012463</td>\n",
       "      <td>1797.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       IndCat3  routes_wtd  employment_wtd  routes_per_emp  mgra_jobs  \\\n",
       "0  NonRestRetl       352.0        974784.0        0.000361    1324253   \n",
       "1   Restaurant      2844.0        374445.0        0.007595     140151   \n",
       "2       Retail      1797.0        168206.0        0.010683     144185   \n",
       "\n",
       "   routes_per_emp_adj1  routes_calibrated  \n",
       "0             0.000266              352.0  \n",
       "1             0.020292             2844.0  \n",
       "2             0.012463             1797.0  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adjust Rates to match MGRA mix of employment, which is different from CVS x TNC survey\n",
    "routeGenRates = routeGenRates.merge(mgraEmpCat3, how='inner', on='IndCat3')\n",
    "routeGenRates.loc[:, 'routes_per_emp_adj1'] = routeGenRates['routes_per_emp'] * routeGenRates['employment_wtd'] / routeGenRates['mgra_jobs']\n",
    "routeGenRates.loc[:, 'routes_calibrated'] = routeGenRates['routes_per_emp_adj1'] * routeGenRates['mgra_jobs']\n",
    "routeGenRates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1794.0"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Apply to MGRAs and adjust for rounding\n",
    "test_retail = pd.DataFrame(df_MGRA.groupby(['LUZ'])['emp_ret','emp_non_ws_wfh','emp_non_ws_oth'].sum()).reset_index()\n",
    "test_retail.loc[:, 'tnc_routes'] = routeGenRates.iloc[2][\"routes_per_emp_adj1\"] * test_retail['emp_ret']\n",
    "test_retail.loc[:, 'tnc_routes'] = np.round(test_retail['tnc_routes'].values)\n",
    "\n",
    "out_retail = test_retail['tnc_routes'].sum()\n",
    "factor = routeGenRates.iloc[2][\"routes_wtd\"] / out_retail\n",
    "routeGenRates[\"routes_per_emp_calib\"] = 0\n",
    "routeGenRates.iloc[2, routeGenRates.columns.get_loc(\"routes_per_emp_calib\")] = routeGenRates.iloc[2][\"routes_per_emp_adj1\"] * factor\n",
    "test_retail.loc[:, 'tnc_routes'] = routeGenRates.iloc[2][\"routes_per_emp_calib\"] * test_retail['emp_ret']\n",
    "test_retail.loc[:, 'tnc_routes'] = np.round(test_retail['tnc_routes'].values)\n",
    "\n",
    "routeGenRates.iloc[2, routeGenRates.columns.get_loc(\"routes_calibrated\")] = test_retail['tnc_routes'].sum()\n",
    "test_retail['tnc_routes'].sum()\n",
    "# test_retail[test_retail['tnc_routes']>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2841.0"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_MGRA.loc[:, 'emp_laf'] = df_MGRA['emp_food']# + df_MGRA['emp_ent'] + df_MGRA['emp_accm']\n",
    "test_restaurant = pd.DataFrame(df_MGRA.groupby(['LUZ'])['emp_laf','emp_non_ws_wfh','emp_non_ws_oth'].sum()).reset_index()\n",
    "test_restaurant.loc[:, 'tnc_routes'] = routeGenRates.iloc[1][\"routes_per_emp_adj1\"] * test_restaurant['emp_laf']\n",
    "test_restaurant.loc[:, 'tnc_routes'] = np.round(test_restaurant['tnc_routes'].values)\n",
    "\n",
    "out_restaurant = test_restaurant['tnc_routes'].sum()\n",
    "factor = routeGenRates.iloc[1][\"routes_wtd\"] / out_restaurant\n",
    "routeGenRates.iloc[1, routeGenRates.columns.get_loc(\"routes_per_emp_calib\")] = routeGenRates.iloc[1][\"routes_per_emp_adj1\"] * factor\n",
    "test_restaurant.loc[:, 'tnc_routes'] = routeGenRates.iloc[1][\"routes_per_emp_calib\"] * test_restaurant['emp_laf']\n",
    "test_restaurant.loc[:, 'tnc_routes'] = np.round(test_restaurant['tnc_routes'].values)\n",
    "\n",
    "routeGenRates.iloc[1, routeGenRates.columns.get_loc(\"routes_calibrated\")] = test_restaurant['tnc_routes'].sum()\n",
    "test_restaurant['tnc_routes'].sum()\n",
    "# test_restaurant[test_restaurant['tnc_routes']>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "357.0"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_MGRA.loc[:, 'emp_NonRR'] = df_MGRA['emp_tot'] - df_MGRA['emp_laf'] - df_MGRA['emp_ret'] - df_MGRA['emp_non_ws_wfh'] - df_MGRA['emp_non_ws_oth']\n",
    "test_nonrr = pd.DataFrame(df_MGRA.groupby(['LUZ'])['emp_NonRR','emp_non_ws_wfh','emp_non_ws_oth'].sum()).reset_index()\n",
    "test_nonrr.loc[:, 'tnc_routes'] = routeGenRates.iloc[0][\"routes_per_emp_adj1\"] * test_nonrr['emp_NonRR']\n",
    "test_nonrr.loc[:, 'tnc_routes'] = np.round(test_nonrr['tnc_routes'].values)\n",
    "\n",
    "out_nonrr = test_nonrr['tnc_routes'].sum()\n",
    "factor = routeGenRates.iloc[0][\"routes_wtd\"] / out_nonrr\n",
    "routeGenRates.iloc[0, routeGenRates.columns.get_loc(\"routes_per_emp_calib\")] = routeGenRates.iloc[0][\"routes_per_emp_adj1\"] * factor\n",
    "test_nonrr.loc[:, 'tnc_routes'] = routeGenRates.iloc[0][\"routes_per_emp_calib\"] * test_nonrr['emp_NonRR']\n",
    "test_nonrr.loc[:, 'tnc_routes'] = np.round(test_nonrr['tnc_routes'].values)\n",
    "\n",
    "routeGenRates.iloc[0, routeGenRates.columns.get_loc(\"routes_calibrated\")] = test_nonrr['tnc_routes'].sum()\n",
    "test_nonrr['tnc_routes'].sum()\n",
    "# test_nonrr[test_nonrr['tnc_routes']>0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Send to CSV\n",
    "model_path = \"Task05_Estimation_Calibration/Estimation/Route_Gen\"\n",
    "routeGenRates['routes_per_emp_calib'] = np.round(routeGenRates['routes_per_emp_calib'], 7) \n",
    "routeGenRates[['IndCat3','routes_wtd','routes_per_emp_calib']].to_csv(os.path.join(root_dir, model_path, \"TNC_RouteGenRates.csv\"), index=False)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# Format for printing\n",
    "routeGenRates['routes_wtd'] = routeGenRates['routes_wtd'].map('{:,.0f}'.format)\n",
    "routeGenRates['employment_wtd'] = routeGenRates['employment_wtd'].map('{:,.0f}'.format)\n",
    "routeGenRates['mgra_jobs'] = routeGenRates['mgra_jobs'].map('{:,.0f}'.format)\n",
    "routeGenRates['routes_calibrated'] = routeGenRates['routes_calibrated'].map('{:,.0f}'.format)\n",
    "routeGenRates"
   ]
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}