CVM_HouseholdData.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import Data\n",
    "# input_dir = os.path.join(r\"C:\\Users\\kdannemiller\\Cambridge Systematics\\PROJ SANDAG Commercial Vehicle & Heavy Truck Model Update - _Shared_CSTeam\\Task03_DataID_Review\")\n",
    "root_dir = os.path.join(r\"C:\\Users\\jgliebe\\OneDrive - Cambridge Systematics\\Documents - PROJ SANDAG Commercial Vehicle & Heavy Truck Model Update\\_Shared_CSTeam\\Task03_DataID_Review\")\n",
    "\n",
    "land_use = pd.read_csv(os.path.join(root_dir,\"Land_Use\", \"mgra15_based_input2022.csv\"))\n",
    "\n",
    "day = pd.read_csv(os.path.join(root_dir,\"HHTS\", \"export_day_weights.csv\"))\n",
    "hh = pd.read_csv(os.path.join(root_dir,\"HHTS\", \"Model Estimation\", \"export_hh_weights_TAZ.csv\"))\n",
    "person = pd.read_csv(os.path.join(root_dir,\"HHTS\", \"export_person_weights.csv\"))\n",
    "tour = pd.read_csv(os.path.join(root_dir,\"HHTS\", \"2022HHTS_tours.csv\"))\n",
    "\n",
    "#Export\n",
    "outfile = \"HHAttr_dataset_v5.csv\"\n",
    "export_dir = os.path.join(root_dir, \"HHTS\", outfile)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Household"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hh_id</th>\n",
       "      <th>home_taz</th>\n",
       "      <th>num_people</th>\n",
       "      <th>num_people_nl</th>\n",
       "      <th>hh_size_1</th>\n",
       "      <th>hh_size_2</th>\n",
       "      <th>num_adults</th>\n",
       "      <th>num_kids</th>\n",
       "      <th>num_vehicles</th>\n",
       "      <th>num_veh_per_worker</th>\n",
       "      <th>hh_0_veh</th>\n",
       "      <th>car_insufficiency</th>\n",
       "      <th>car_insufficiency_0_veh</th>\n",
       "      <th>car_sufficiency</th>\n",
       "      <th>income_50_99</th>\n",
       "      <th>income_100_199</th>\n",
       "      <th>income_200_plus</th>\n",
       "      <th>income_100_plus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22083219</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>1.386294</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</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>22057262</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>1.791759</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>22075920</td>\n",
       "      <td>553</td>\n",
       "      <td>1</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>22117761</td>\n",
       "      <td>553</td>\n",
       "      <td>2</td>\n",
       "      <td>1.098612</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>22085865</td>\n",
       "      <td>557</td>\n",
       "      <td>1</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2773</td>\n",
       "      <td>22070306</td>\n",
       "      <td>4719</td>\n",
       "      <td>1</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>inf</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2774</td>\n",
       "      <td>22083618</td>\n",
       "      <td>4740</td>\n",
       "      <td>2</td>\n",
       "      <td>1.098612</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2775</td>\n",
       "      <td>22061489</td>\n",
       "      <td>4776</td>\n",
       "      <td>2</td>\n",
       "      <td>1.098612</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2778</td>\n",
       "      <td>22056665</td>\n",
       "      <td>4801</td>\n",
       "      <td>1</td>\n",
       "      <td>0.693147</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>inf</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2780</td>\n",
       "      <td>22070335</td>\n",
       "      <td>4805</td>\n",
       "      <td>2</td>\n",
       "      <td>1.098612</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>inf</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2355 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         hh_id  home_taz  num_people  num_people_nl  hh_size_1  hh_size_2  \\\n",
       "0     22083219        13           3       1.386294          0          0   \n",
       "1     22057262        13           5       1.791759          0          0   \n",
       "3     22075920       553           1       0.693147          1          0   \n",
       "4     22117761       553           2       1.098612          0          1   \n",
       "5     22085865       557           1       0.693147          1          0   \n",
       "...        ...       ...         ...            ...        ...        ...   \n",
       "2773  22070306      4719           1       0.693147          1          0   \n",
       "2774  22083618      4740           2       1.098612          0          1   \n",
       "2775  22061489      4776           2       1.098612          0          1   \n",
       "2778  22056665      4801           1       0.693147          1          0   \n",
       "2780  22070335      4805           2       1.098612          0          1   \n",
       "\n",
       "      num_adults  num_kids  num_vehicles  num_veh_per_worker  hh_0_veh  \\\n",
       "0              2         1             2                 2.0         0   \n",
       "1              2         3             3                 3.0         0   \n",
       "3              1         0             1                 1.0         0   \n",
       "4              2         0             0                 0.0         1   \n",
       "5              1         0             1                 1.0         0   \n",
       "...          ...       ...           ...                 ...       ...   \n",
       "2773           1         0             2                 inf         0   \n",
       "2774           2         0             1                 0.5         0   \n",
       "2775           2         0             2                 2.0         0   \n",
       "2778           1         0             2                 inf         0   \n",
       "2780           2         0             1                 inf         0   \n",
       "\n",
       "      car_insufficiency  car_insufficiency_0_veh  car_sufficiency  \\\n",
       "0                     0                        0                1   \n",
       "1                     0                        0                1   \n",
       "3                     0                        0                1   \n",
       "4                     0                        1                0   \n",
       "5                     0                        0                1   \n",
       "...                 ...                      ...              ...   \n",
       "2773                  0                        0                1   \n",
       "2774                  1                        1                0   \n",
       "2775                  0                        0                1   \n",
       "2778                  0                        0                1   \n",
       "2780                  1                        1                0   \n",
       "\n",
       "      income_50_99  income_100_199  income_200_plus  income_100_plus  \n",
       "0                0               0                0                0  \n",
       "1                0               1                0                1  \n",
       "3                0               0                0                0  \n",
       "4                0               0                0                0  \n",
       "5                0               1                0                1  \n",
       "...            ...             ...              ...              ...  \n",
       "2773             1               0                0                0  \n",
       "2774             0               1                0                1  \n",
       "2775             0               0                0                0  \n",
       "2778             0               0                0                0  \n",
       "2780             1               0                0                0  \n",
       "\n",
       "[2355 rows x 18 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Filter Out Missing Responses \n",
    "#num_people\n",
    "hh = hh[hh['num_people'] != 995]\n",
    "\n",
    "#num_adults\n",
    "hh = hh[hh['num_adults'] != 995]\n",
    "\n",
    "#num_kids\n",
    "hh = hh[hh['num_kids'] != 995]\n",
    "\n",
    "#num_students\n",
    "hh = hh[hh['num_students'] != 995]\n",
    "\n",
    "#num_workers\n",
    "hh = hh[hh['num_workers'] != 995]\n",
    "\n",
    "#num_vehicles\n",
    "hh = hh[hh['num_vehicles'] != 995]\n",
    "\n",
    "#income_detailed\n",
    "hh = hh[hh['income_detailed'] != 999]\n",
    "\n",
    "#HH type\n",
    "hh = hh[hh['res_type'] != 995]\n",
    "\n",
    "#Create new variables\n",
    "#num_people_nl\n",
    "hh['num_people_nl'] = np.log(hh['num_people'] + 1)\n",
    "\n",
    "#num_adults_worker\n",
    "hh['num_adults_worker'] = hh['num_workers']\n",
    "\n",
    "#num_adults_nonworker\n",
    "hh['num_adults_nonworker'] = hh['num_adults'] - hh['num_adults_worker']\n",
    "hh['num_adults_nonworker'] = hh['num_adults_nonworker'].apply(lambda x: max(0, x))\n",
    "\n",
    "#hh_size_1\n",
    "is_1_person = lambda x: 1 if x in [1] else 0\n",
    "hh['hh_size_1'] = hh['num_people'].apply(is_1_person)\n",
    "\n",
    "#hh_size_2\n",
    "is_2_person = lambda x: 1 if x in [2] else 0\n",
    "hh['hh_size_2'] = hh['num_people'].apply(is_2_person)\n",
    "\n",
    "#num_veh_per_worker\n",
    "hh['num_veh_per_worker'] = hh['num_vehicles']/hh['num_workers']\n",
    "\n",
    "#hh_0_veh\n",
    "is_0_veh = lambda x: 1 if x in [0] else 0\n",
    "hh['hh_0_veh'] = hh['num_vehicles'].apply(is_0_veh)\n",
    "\n",
    "#car_insufficiency\n",
    "hh['num_veh_per_adult'] = hh['num_vehicles']/hh['num_adults']\n",
    "hh['car_insufficiency'] = hh['num_veh_per_adult'].apply(lambda x: 1 if 0 < x < 1 else 0)\n",
    "hh['car_insufficiency_0_veh'] = hh['num_veh_per_adult'].apply(lambda x: 1 if x < 1 else 0)\n",
    "\n",
    "#car_sufficiency\n",
    "hh['car_sufficiency'] = hh['num_veh_per_adult'].apply(lambda x: 1 if x >= 1 else 0)\n",
    "\n",
    "#income: $15,000 to $49,999\n",
    "is_15_49 = lambda x: 1 if x in [2, 3, 4] else 0\n",
    "hh['income_15_49'] = hh['income_detailed'].apply(is_15_49)\n",
    "\n",
    "#income: $50,000 to $99,999\n",
    "is_50_99 = lambda x: 1 if x in [5, 6] else 0\n",
    "hh['income_50_99'] = hh['income_detailed'].apply(is_50_99)\n",
    "\n",
    "#income: $100,000 to $199,999\n",
    "is_100_199 = lambda x: 1 if x in [7, 8] else 0\n",
    "hh['income_100_199'] = hh['income_detailed'].apply(is_100_199)\n",
    "\n",
    "#income: $200,000 plus\n",
    "is_200_plus = lambda x: 1 if x in [9, 10] else 0\n",
    "hh['income_200_plus'] = hh['income_detailed'].apply(is_200_plus)\n",
    "\n",
    "#(TEMPORARY?) income: $100,000 plus\n",
    "is_100_plus = lambda x: 1 if x in [7, 8, 9, 10] else 0\n",
    "hh['income_100_plus'] = hh['income_detailed'].apply(is_100_plus)\n",
    "\n",
    "#Condense Household Data\n",
    "hh_cond = hh[[\n",
    "    'hh_id',\n",
    "    'home_taz',\n",
    "    'num_people',\n",
    "    'num_people_nl',\n",
    "    'hh_size_1',\n",
    "    'hh_size_2',\n",
    "    'num_adults',\n",
    "    'num_kids',\n",
    "    'num_vehicles',\n",
    "    'num_veh_per_worker',\n",
    "    'hh_0_veh',\n",
    "    'car_insufficiency',\n",
    "    'car_insufficiency_0_veh',\n",
    "    'car_sufficiency',\n",
    "    'income_50_99',\n",
    "    'income_100_199',\n",
    "    'income_200_plus',\n",
    "    'income_100_plus'\n",
    "]]\n",
    "\n",
    "hh_cond"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>hh_id</th>\n",
       "      <th>day_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>weekday</th>\n",
       "      <th>friday</th>\n",
       "      <th>delivery</th>\n",
       "      <th>food</th>\n",
       "      <th>service</th>\n",
       "      <th>package</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22000077</td>\n",
       "      <td>220000770101</td>\n",
       "      <td>2200007701</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>22000077</td>\n",
       "      <td>220000770102</td>\n",
       "      <td>2200007701</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>22000077</td>\n",
       "      <td>220000770103</td>\n",
       "      <td>2200007701</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>22000077</td>\n",
       "      <td>220000770104</td>\n",
       "      <td>2200007701</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>22000077</td>\n",
       "      <td>220000770105</td>\n",
       "      <td>2200007701</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>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14849</td>\n",
       "      <td>22118054</td>\n",
       "      <td>221180540107</td>\n",
       "      <td>2211805401</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14850</td>\n",
       "      <td>22118058</td>\n",
       "      <td>221180580101</td>\n",
       "      <td>2211805801</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14851</td>\n",
       "      <td>22118059</td>\n",
       "      <td>221180590101</td>\n",
       "      <td>2211805901</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14852</td>\n",
       "      <td>22118063</td>\n",
       "      <td>221180630101</td>\n",
       "      <td>2211806301</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>14853</td>\n",
       "      <td>22118069</td>\n",
       "      <td>221180690101</td>\n",
       "      <td>2211806901</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14854 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          hh_id        day_id   person_id  weekday  friday  delivery  food  \\\n",
       "0      22000077  220000770101  2200007701        1       0         1     0   \n",
       "1      22000077  220000770102  2200007701        1       0         0     0   \n",
       "2      22000077  220000770103  2200007701        1       0         0     0   \n",
       "3      22000077  220000770104  2200007701        0       1         0     0   \n",
       "4      22000077  220000770105  2200007701        0       0         0     0   \n",
       "...         ...           ...         ...      ...     ...       ...   ...   \n",
       "14849  22118054  221180540107  2211805401        1       0         0     0   \n",
       "14850  22118058  221180580101  2211805801        1       0         0     0   \n",
       "14851  22118059  221180590101  2211805901        1       0         1     0   \n",
       "14852  22118063  221180630101  2211806301        1       0         1     0   \n",
       "14853  22118069  221180690101  2211806901        1       0         0     0   \n",
       "\n",
       "       service  package  \n",
       "0            0        1  \n",
       "1            0        0  \n",
       "2            0        0  \n",
       "3            0        0  \n",
       "4            0        0  \n",
       "...        ...      ...  \n",
       "14849        0        0  \n",
       "14850        0        0  \n",
       "14851        0        1  \n",
       "14852        0        1  \n",
       "14853        0        0  \n",
       "\n",
       "[14854 rows x 9 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Filter Out Missing Responses\n",
    "#survey_complete\n",
    "day = day[day['survey_complete'] == 1]\n",
    "\n",
    "#delivery\n",
    "day = day[(day['delivery_2'] != 995) &\n",
    "          (day['delivery_3'] != 995) &\n",
    "          (day['delivery_5'] != 995) &\n",
    "          (day['delivery_8'] != 995)]\n",
    "\n",
    "#Create Dummy Variables\n",
    "#weekday\n",
    "is_weekday = lambda x: 1 if x in [1, 2, 3, 4] else 0\n",
    "day['weekday'] = day['travel_dow'].apply(is_weekday)\n",
    "\n",
    "#friday\n",
    "is_friday = lambda x: 1 if x in [5] else 0\n",
    "day['friday'] = day['travel_dow'].apply(is_friday)\n",
    "\n",
    "#delivery\n",
    "day['delivery'] = (day['delivery_2'] | day['delivery_3'] | day['delivery_5'] | day['delivery_8']).astype(int)\n",
    "\n",
    "#food\n",
    "day['food'] = (day['delivery_2']).astype(int)\n",
    "\n",
    "#service\n",
    "day['service'] = (day['delivery_3']).astype(int)\n",
    "\n",
    "#package\n",
    "day['package'] = (day['delivery_5'] | day['delivery_8']).astype(int)\n",
    "\n",
    "#Condense Day Data\n",
    "day_cond = day.groupby(['hh_id', 'day_id']).agg({\n",
    "    'person_id': 'first',\n",
    "    #'num_trips': 'first',\n",
    "    'weekday': 'first',\n",
    "    'friday': 'first',\n",
    "    'delivery': 'first',\n",
    "    'food': 'first',\n",
    "    'service': 'first',\n",
    "    'package': 'first'\n",
    "    }).reset_index()\n",
    "\n",
    "day_cond"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Person"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>person_id</th>\n",
       "      <th>age_18_34</th>\n",
       "      <th>age_35_54</th>\n",
       "      <th>age_55_74</th>\n",
       "      <th>age_75_plus</th>\n",
       "      <th>education_higher</th>\n",
       "      <th>student_status</th>\n",
       "      <th>driver</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2200007701</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2200007702</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2200012501</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2200015401</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2200018201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5612</td>\n",
       "      <td>2211805801</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5613</td>\n",
       "      <td>2211805802</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5614</td>\n",
       "      <td>2211805901</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5615</td>\n",
       "      <td>2211806301</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5616</td>\n",
       "      <td>2211806901</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4446 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       person_id  age_18_34  age_35_54  age_55_74  age_75_plus  \\\n",
       "0     2200007701          1          0          0            0   \n",
       "1     2200007702          1          0          0            0   \n",
       "2     2200012501          0          1          0            0   \n",
       "5     2200015401          0          0          1            0   \n",
       "6     2200018201          0          0          1            0   \n",
       "...          ...        ...        ...        ...          ...   \n",
       "5612  2211805801          0          1          0            0   \n",
       "5613  2211805802          0          0          1            0   \n",
       "5614  2211805901          1          0          0            0   \n",
       "5615  2211806301          0          0          1            0   \n",
       "5616  2211806901          1          0          0            0   \n",
       "\n",
       "      education_higher  student_status  driver  \n",
       "0                    1               0       1  \n",
       "1                    1               0       1  \n",
       "2                    1               0       1  \n",
       "5                    1               0       1  \n",
       "6                    1               0       0  \n",
       "...                ...             ...     ...  \n",
       "5612                 1               1       0  \n",
       "5613                 1               0       1  \n",
       "5614                 1               0       1  \n",
       "5615                 0               0       1  \n",
       "5616                 1               1       1  \n",
       "\n",
       "[4446 rows x 8 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Filter Out Missing Responses\n",
    "#employment\n",
    "#person = person[person['employment'] != 995]\n",
    "\n",
    "#num_jobs\n",
    "#person = person[person['num_jobs'] != 995]\n",
    "\n",
    "#education\n",
    "person = person[person['education'] != 995]\n",
    "person = person[person['education'] != 999]\n",
    "\n",
    "#student\n",
    "person = person[person['student'] != 995]\n",
    "\n",
    "#can_drive\n",
    "person = person[person['can_drive'] != 995]\n",
    "\n",
    "#telework_freq\n",
    "#person = person[person['telework_freq'] != 995]\n",
    "\n",
    "#commute_freq\n",
    "#person = person[person['commute_freq'] != 995]\n",
    "\n",
    "#Create Dummy Variables\n",
    "#age: 18-34\n",
    "is_18_34 = lambda x: 1 if x in [4, 5] else 0\n",
    "person['age_18_34'] = person['age'].apply(is_18_34)\n",
    "\n",
    "#age: 35-54\n",
    "is_35_54 = lambda x: 1 if x in [6, 7] else 0\n",
    "person['age_35_54'] = person['age'].apply(is_35_54)\n",
    "\n",
    "#age: 55-74\n",
    "is_55_74 = lambda x: 1 if x in [8, 9] else 0\n",
    "person['age_55_74'] = person['age'].apply(is_55_74)\n",
    "\n",
    "#age: 75+\n",
    "is_75_plus = lambda x: 1 if x in [10, 11] else 0\n",
    "person['age_75_plus'] = person['age'].apply(is_75_plus)\n",
    "\n",
    "#employed\n",
    "#is_employed = lambda x: 1 if x in [1, 2, 3, 7, 8] else 0\n",
    "#person['employed'] = person['employment'].apply(is_employed)\n",
    "\n",
    "#education_higher\n",
    "is_educated = lambda x: 1 if x in [3, 4, 5, 6, 7] else 0\n",
    "person['education_higher'] = person['education'].apply(is_educated)\n",
    "\n",
    "#student_status\n",
    "is_student = lambda x: 1 if x in [0, 1, 3, 4] else 0\n",
    "person['student_status'] = person['student'].apply(is_student)\n",
    "\n",
    "#driver\n",
    "is_driver = lambda x: 1 if x in [1] else 0\n",
    "person['driver'] = person['can_drive'].apply(is_driver)\n",
    "\n",
    "#commute\n",
    "#is_commute = lambda x: 1 if x in [1, 2, 3, 4] else 0\n",
    "#person['commute'] = person['commute_freq'].apply(is_commute)\n",
    "\n",
    "#telework\n",
    "#is_telework = lambda x: 1 if x in [1, 2, 3, 4] else 0\n",
    "#person['telework'] = person['telework_freq'].apply(is_telework)\n",
    "\n",
    "#Condense person data\n",
    "person_cond = person[[\n",
    "    'person_id',\n",
    "    'age_18_34',\n",
    "    'age_35_54',\n",
    "    'age_55_74',\n",
    "    'age_75_plus',\n",
    "    'education_higher', \n",
    "    'student_status', \n",
    "    'driver'\n",
    "]]\n",
    "\n",
    "#Group person dataset by houshold ID, maintaining the maximum age group category\n",
    "\n",
    "person_cond"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>day_id</th>\n",
       "      <th>home_based_tours</th>\n",
       "      <th>shop_tours</th>\n",
       "      <th>dine_tours</th>\n",
       "      <th>shop_dine_tours</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>220000770101</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>220000770102</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>220000770103</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>220000770104</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>220000770201</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9514</td>\n",
       "      <td>221180580103</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9515</td>\n",
       "      <td>221180580203</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9516</td>\n",
       "      <td>221180590103</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9517</td>\n",
       "      <td>221180630104</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9518</td>\n",
       "      <td>221180690102</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9519 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            day_id  home_based_tours  shop_tours  dine_tours  shop_dine_tours\n",
       "0     220000770101                 1           0           0                0\n",
       "1     220000770102                 3           0           0                0\n",
       "2     220000770103                 3           0           1                1\n",
       "3     220000770104                 2           0           0                0\n",
       "4     220000770201                 1           0           0                0\n",
       "...            ...               ...         ...         ...              ...\n",
       "9514  221180580103                 0           0           0                0\n",
       "9515  221180580203                 0           0           0                0\n",
       "9516  221180590103                 2           0           1                1\n",
       "9517  221180630104                 1           1           0                1\n",
       "9518  221180690102                 1           0           0                0\n",
       "\n",
       "[9519 rows x 5 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Rename tour columns\n",
    "column_names_tour = {\n",
    "    'HH_ID': 'hh_id',\n",
    "    'PER_ID': 'person_id',\n",
    "    'TOURPURP': 'tour_purp'}\n",
    "tour.rename(columns=column_names_tour, inplace=True)\n",
    "\n",
    "#Create Dummy Variables\n",
    "#shop\n",
    "is_shop = lambda x: 1 if x in [5] else 0\n",
    "tour['shop_tours'] = tour['tour_purp'].apply(is_shop)\n",
    "\n",
    "#dine\n",
    "is_dine = lambda x: 1 if x in [7] else 0\n",
    "tour['dine_tours'] = tour['tour_purp'].apply(is_dine)\n",
    "\n",
    "#work-based\n",
    "is_work = lambda x: 1 if x in [1] else 0\n",
    "tour['work_tours'] = tour['tour_purp'].apply(is_work)\n",
    "\n",
    "#tours\n",
    "tour['tours_total'] = 1\n",
    "\n",
    "#Convert day column\n",
    "def extract_day_number(day_string):\n",
    "    return int(day_string.replace('day', ''))\n",
    "tour['day'] = tour['day'].apply(extract_day_number)\n",
    "\n",
    "#Create day_id column\n",
    "tour['hh_id'] = tour['hh_id'].astype(str)\n",
    "tour['person_id'] = tour['person_id'].astype(str)\n",
    "tour['day'] = tour['day'].astype(str)\n",
    "\n",
    "tour['day_id'] = tour['hh_id'] + tour['person_id'].astype(str).str.zfill(2) + tour['day'].astype(str).str.zfill(2)\n",
    "\n",
    "#Create person_id column\n",
    "tour['person_id'] = tour['hh_id'] + tour['person_id'].astype(str).str.zfill(2) \n",
    "\n",
    "#Group by day_id\n",
    "tour_grouped = tour.groupby(['day_id']).agg({\n",
    "    'hh_id': 'first',  \n",
    "    'person_id': 'first',\n",
    "    'tours_total': 'sum',\n",
    "    'shop_tours': 'sum',\n",
    "    'dine_tours': 'sum',\n",
    "    'work_tours': 'sum'}).reset_index() \n",
    "\n",
    "#tours_home_based\n",
    "tour_grouped['home_based_tours'] = tour_grouped['tours_total'] - tour_grouped['work_tours'] \n",
    "\n",
    "#shop_dine\n",
    "tour_grouped['shop_dine_tours'] = tour_grouped['shop_tours'] + tour_grouped['dine_tours']\n",
    "\n",
    "#Condense tour dataframe\n",
    "tour_cond = tour_grouped[[\n",
    "#     'hh_id',\n",
    "#     'person_id',\n",
    "    'day_id',\n",
    "    'home_based_tours',\n",
    "    'shop_tours',\n",
    "    'dine_tours',\n",
    "    'shop_dine_tours'\n",
    "    \n",
    "]]\n",
    "\n",
    "tour_cond"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Land Use"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>home_taz</th>\n",
       "      <th>home_taz_pop</th>\n",
       "      <th>home_taz_pop_100</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>4646</td>\n",
       "      <td>46.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>14</td>\n",
       "      <td>2079</td>\n",
       "      <td>20.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>15</td>\n",
       "      <td>4417</td>\n",
       "      <td>44.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>16</td>\n",
       "      <td>38</td>\n",
       "      <td>0.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>11361</td>\n",
       "      <td>113.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4930</td>\n",
       "      <td>4943</td>\n",
       "      <td>49</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4931</td>\n",
       "      <td>4944</td>\n",
       "      <td>367</td>\n",
       "      <td>3.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4932</td>\n",
       "      <td>4945</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4933</td>\n",
       "      <td>4946</td>\n",
       "      <td>0</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4934</td>\n",
       "      <td>4947</td>\n",
       "      <td>16</td>\n",
       "      <td>0.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4935 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      home_taz  home_taz_pop  home_taz_pop_100\n",
       "0           13          4646             46.46\n",
       "1           14          2079             20.79\n",
       "2           15          4417             44.17\n",
       "3           16            38              0.38\n",
       "4           17         11361            113.61\n",
       "...        ...           ...               ...\n",
       "4930      4943            49              0.49\n",
       "4931      4944           367              3.67\n",
       "4932      4945             0              0.00\n",
       "4933      4946             0              0.00\n",
       "4934      4947            16              0.16\n",
       "\n",
       "[4935 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Group by TAZ\n",
    "land_use_grouped = land_use.groupby(['taz']).agg({\n",
    "    'pop': 'sum'}).reset_index() \n",
    "\n",
    "column_names_land_use = {\n",
    "    'taz': 'home_taz',\n",
    "    'pop': 'home_taz_pop'}\n",
    "land_use_grouped.rename(columns=column_names_land_use, inplace=True)\n",
    "\n",
    "land_use_grouped['home_taz_pop_100'] = land_use_grouped['home_taz_pop']/100\n",
    "\n",
    "land_use_grouped"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jgliebe\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "C:\\Users\\jgliebe\\AppData\\Local\\Continuum\\anaconda3\\lib\\site-packages\\ipykernel_launcher.py:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"\n"
     ]
    },
    {
     "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>hh_id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>day_id</th>\n",
       "      <th>weekday</th>\n",
       "      <th>friday</th>\n",
       "      <th>delivery</th>\n",
       "      <th>food</th>\n",
       "      <th>service</th>\n",
       "      <th>package</th>\n",
       "      <th>home_taz</th>\n",
       "      <th>...</th>\n",
       "      <th>car_insufficiency_0_veh</th>\n",
       "      <th>car_sufficiency</th>\n",
       "      <th>age_18_34</th>\n",
       "      <th>age_35_54</th>\n",
       "      <th>age_55_74</th>\n",
       "      <th>age_75_plus</th>\n",
       "      <th>income_50_99</th>\n",
       "      <th>income_100_199</th>\n",
       "      <th>income_200_plus</th>\n",
       "      <th>income_100_plus</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>22000077</td>\n",
       "      <td>2200007701</td>\n",
       "      <td>220000770101</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1915.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>22000077</td>\n",
       "      <td>2200007701</td>\n",
       "      <td>220000770102</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>1915.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>22000077</td>\n",
       "      <td>2200007701</td>\n",
       "      <td>220000770103</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>1915.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>22000077</td>\n",
       "      <td>2200007701</td>\n",
       "      <td>220000770104</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1915.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>22000077</td>\n",
       "      <td>2200007702</td>\n",
       "      <td>220000770201</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>1915.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 36 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      hh_id   person_id        day_id  weekday  friday  delivery  food  \\\n",
       "0  22000077  2200007701  220000770101        1       0         1     0   \n",
       "1  22000077  2200007701  220000770102        1       0         0     0   \n",
       "2  22000077  2200007701  220000770103        1       0         0     0   \n",
       "3  22000077  2200007701  220000770104        0       1         0     0   \n",
       "7  22000077  2200007702  220000770201        1       0         0     0   \n",
       "\n",
       "   service  package  home_taz  ...  car_insufficiency_0_veh  car_sufficiency  \\\n",
       "0        0        1    1915.0  ...                      0.0              1.0   \n",
       "1        0        0    1915.0  ...                      0.0              1.0   \n",
       "2        0        0    1915.0  ...                      0.0              1.0   \n",
       "3        0        0    1915.0  ...                      0.0              1.0   \n",
       "7        0        0    1915.0  ...                      0.0              1.0   \n",
       "\n",
       "   age_18_34  age_35_54  age_55_74  age_75_plus  income_50_99  income_100_199  \\\n",
       "0        1.0        0.0        0.0          0.0           0.0             1.0   \n",
       "1        1.0        0.0        0.0          0.0           0.0             1.0   \n",
       "2        1.0        0.0        0.0          0.0           0.0             1.0   \n",
       "3        1.0        0.0        0.0          0.0           0.0             1.0   \n",
       "7        1.0        0.0        0.0          0.0           0.0             1.0   \n",
       "\n",
       "   income_200_plus  income_100_plus  \n",
       "0              0.0              1.0  \n",
       "1              0.0              1.0  \n",
       "2              0.0              1.0  \n",
       "3              0.0              1.0  \n",
       "7              0.0              1.0  \n",
       "\n",
       "[5 rows x 36 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Change column type\n",
    "day_cond['person_id'] = pd.to_numeric(day_cond['person_id'])\n",
    "person_cond['person_id'] = pd.to_numeric(person_cond['person_id'])\n",
    "day_cond['day_id'] = pd.to_numeric(day_cond['day_id'])\n",
    "tour_cond['day_id'] = pd.to_numeric(tour_cond['day_id'])\n",
    "\n",
    "#Merge\n",
    "df = pd.merge(day_cond, tour_cond, on=['day_id'], how='left')\n",
    "df = pd.merge(df, hh_cond, on=['hh_id'], how='left')\n",
    "df = pd.merge(df, land_use_grouped, on=['home_taz'], how='left')\n",
    "df = pd.merge(df, person_cond, on=['person_id'], how='left')\n",
    "\n",
    "#Drop na columns\n",
    "df = df.dropna()\n",
    "\n",
    "df = df[[\n",
    "    'hh_id',\n",
    "    'person_id',\n",
    "    'day_id',\n",
    "    'weekday',\n",
    "    'friday',\n",
    "    'delivery',\n",
    "    'food',\n",
    "    'service',\n",
    "    'package',\n",
    "    'home_taz',\n",
    "    'home_taz_pop',\n",
    "    'home_taz_pop_100',\n",
    "    'home_based_tours',\n",
    "    'shop_tours',\n",
    "    'dine_tours',\n",
    "    'shop_dine_tours',\n",
    "    'num_people',\n",
    "    'num_people_nl',\n",
    "    'hh_size_1',\n",
    "    'hh_size_2',\n",
    "    'num_adults',\n",
    "    'num_kids',\n",
    "    'num_vehicles',\n",
    "    'num_veh_per_worker',\n",
    "    'hh_0_veh',\n",
    "    'car_insufficiency',\n",
    "    'car_insufficiency_0_veh',\n",
    "    'car_sufficiency',\n",
    "    'age_18_34',\n",
    "    'age_35_54',\n",
    "    'age_55_74',\n",
    "    'age_75_plus',\n",
    "    'income_50_99',\n",
    "    'income_100_199',\n",
    "    'income_200_plus',\n",
    "    'income_100_plus'\n",
    "]]\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['hh_id',\n",
       " 'person_id',\n",
       " 'day_id',\n",
       " 'weekday',\n",
       " 'friday',\n",
       " 'delivery',\n",
       " 'food',\n",
       " 'service',\n",
       " 'package',\n",
       " 'home_taz',\n",
       " 'home_taz_pop',\n",
       " 'home_taz_pop_100',\n",
       " 'home_based_tours',\n",
       " 'shop_tours',\n",
       " 'dine_tours',\n",
       " 'shop_dine_tours',\n",
       " 'num_people',\n",
       " 'num_people_nl',\n",
       " 'hh_size_1',\n",
       " 'hh_size_2',\n",
       " 'num_adults',\n",
       " 'num_kids',\n",
       " 'num_vehicles',\n",
       " 'num_veh_per_worker',\n",
       " 'hh_0_veh',\n",
       " 'car_insufficiency',\n",
       " 'car_insufficiency_0_veh',\n",
       " 'car_sufficiency',\n",
       " 'age_18_34',\n",
       " 'age_35_54',\n",
       " 'age_55_74',\n",
       " 'age_75_plus',\n",
       " 'income_50_99',\n",
       " 'income_100_199',\n",
       " 'income_200_plus',\n",
       " 'income_100_plus']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Checks\n",
    "#df['delivery'].value_counts()\n",
    "#pd.crosstab(delivery_package['package'], delivery_package['income_200_plus'])\n",
    "df.columns.tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#delivery_food\n",
    "# delivery_food = df[df['food'] == 1]\n",
    "# delivery_food"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#delivery_package\n",
    "# delivery_package = df[(df['package'] == 1)]\n",
    "# delivery_package"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#delivery_service\n",
    "# delivery_service = df[df['service'] == 1]\n",
    "# delivery_service"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(export_dir, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\jgliebe\\\\OneDrive - Cambridge Systematics\\\\Documents - PROJ SANDAG Commercial Vehicle & Heavy Truck Model Update\\\\_Shared_CSTeam\\\\Task03_DataID_Review\\\\HHTS\\\\HHAttr_dataset_v5.csv'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "export_dir"
   ]
  },
  {
   "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": 5
}