1.4 - Summary_Sheet_5pct_fullplans.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "from pyproj import CRS\n",
    "import pathlib\n",
    "from pathlib import Path\n",
    "from shapely import wkt\n",
    "from tqdm import tqdm\n",
    "\n",
    "import math\n",
    "import codecs\n",
    "\n",
    "from shapely import wkt\n",
    "\n",
    "import folium\n",
    "from folium import features\n",
    "from folium import plugins\n",
    "\n",
    "import gzip\n",
    "from xml.etree.ElementTree import Element, SubElement, Comment, tostring\n",
    "import xml.etree.ElementTree as ET\n",
    "\n",
    "# to read the excel\n",
    "from openpyxl import load_workbook\n",
    "from openpyxl import Workbook\n",
    "\n",
    "# import folium\n",
    "from shapely.geometry import LineString, MultiLineString\n",
    "import branca.colormap as cmp\n",
    "from folium.plugins import Search\n",
    "\n",
    "from tqdm import tqdm\n",
    "import time\n",
    "\n",
    "import datetime\n",
    "from datetime import timedelta\n",
    "\n",
    "from collections import Counter\n",
    "\n",
    "import os\n",
    "import re\n",
    "\n",
    "# set the working directory\n",
    "BASE_DIR = Path.cwd()"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Sample information"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "#Sample information\n",
    "sample_pct = 0.05 # sample value in percentage\n",
    "scale_factor = 100/(100*sample_pct)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### Set the path to the simulation run folder; also get the date"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [],
   "source": [
    "# get the first and the last iteration name\n",
    "def get_iteration_count(simulation_run):\n",
    "    rootPath = BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\")\n",
    "    list_iter=[]\n",
    "    for name in [entry for entry in os.listdir(rootPath) if os.path.isdir(os.path.join(rootPath, entry))]:\n",
    "        list_iter.append(int(name.rsplit(\"it.\")[1]))\n",
    "    return max((list_iter)),min((list_iter))\n",
    "# setup the BEAM model output folder name here\n",
    "simulation_run = \"updated-mc-coefficient-new-tpcm-utility-calculation__eko\"\n",
    "last_iter,first_iter = get_iteration_count(simulation_run)\n",
    "\n",
    "# get the path of the simulation run and also get its creation date\n",
    "def get_foldercreation_inf(folder_name):\n",
    "    fname = pathlib.Path(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(last_iter)), (str(last_iter)+\".linkstats.csv.gz\")))\n",
    "    assert fname.exists(), f'No such file: {fname}'  # check that the file exists\n",
    "    ctime = datetime.datetime.fromtimestamp(fname.stat().st_ctime)\n",
    "    return ctime.strftime(\"%Y-%m-%d\")\n",
    "# get its creation date\n",
    "date_time = get_foldercreation_inf(simulation_run)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#### Perform analysis for Bay Area"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [],
   "source": [
    "# get VMT, VHT and ratio from the last iteration of the linkstats file\n",
    "class linkstats_to_df(object):\n",
    "    def __init__(self,df):\n",
    "        self.dataframe = df\n",
    "        self.dataframe = self.dataframe.reset_index(drop=True)\n",
    "        self.dataframe[\"volume\"] = self.dataframe[\"volume\"].fillna(0)\n",
    "        self.dataframe[\"length\"] = self.dataframe[\"length\"].fillna(0)\n",
    "        self.dataframe[\"vmt\"] = self.dataframe[\"length\"]*0.00062137*self.dataframe[\"volume\"]*scale_factor\n",
    "        self.VMT_sum = self.dataframe[\"vmt\"].sum()\n",
    "        # add congested speed (mph)\n",
    "        self.dataframe[\"congested_speed_mph\"] = (self.dataframe[\"length\"]/self.dataframe[\"traveltime\"])*2.23694\n",
    "        # add free-flow speed (mph)\n",
    "        self.dataframe[\"freeflow_speed_mph\"] = self.dataframe[\"freespeed\"]*2.23694\n",
    "        # add vehicle hours of delay (free flow speed - congested speed)* volume\n",
    "        self.dataframe[\"VHD\"] = ((self.dataframe[\"freeflow_speed_mph\"]-self.dataframe[\"congested_speed_mph\"])*(self.dataframe[\"volume\"])*20)/(60*60)\n",
    "        self.VHD_sum = self.dataframe[\"VHD\"].sum()\n",
    "        # calculate VHT\n",
    "        self.dataframe[\"VHT\"] = self.dataframe[\"volume\"]*(self.dataframe[\"traveltime\"]/(60*60))*scale_factor\n",
    "        self.VHT_sum = self.dataframe[\"VHT\"].sum()\n",
    "        # calculate VMT VHT Ratio\n",
    "        self.VMT_VHT_ratio = self.VMT_sum/self.VHT_sum\n",
    "\n",
    "dfBayAreaBeam = linkstats_to_df(pd.read_csv((BASE_DIR.parent.joinpath(\"runs\",simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".linkstats.csv.gz\")))))\n",
    "# print(\"LinkStatistics\")\n",
    "# print(\"VMT: {}, VHT: {}, ratio:{}\".format(dfBayAreaBeam.VMT_sum,dfBayAreaBeam.VHT_sum,dfBayAreaBeam.VMT_VHT_ratio))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "now read the link stat from the SF County"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [],
   "source": [
    "### now read the link stat from the SF County\n",
    "gdSFCountyNetwork = gpd.read_file((BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"Network\",\"SFCounty_EPSG4326.geojson\")),driver='GeoJSON')\n",
    "gdfSFBoundary = gpd.read_file((BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"Network\",\"SFCounty_boundary_EPSG4326.geojson\")),driver='GeoJSON')\n",
    "\n",
    "df = gdSFCountyNetwork.loc[~gdSFCountyNetwork[\"Information\"].isin(['San Mateo', 'Golden Gate Bridge', 'SFOak1', 'SFOak2'])]\n",
    "# # convert it to dictinary \"Length\" & \"Link\"\n",
    "network_links = dict(zip(df[\"ID\"], df[\"LENGTH\"]))\n",
    "\n",
    "dfBayArea = pd.read_csv(BASE_DIR.parent.joinpath((BASE_DIR.parent.joinpath(\"runs\",simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".linkstats.csv.gz\")))), compression=\"gzip\", low_memory=True)\n",
    "dfBayArea.reset_index(drop=True,inplace=True)\n",
    "dfBayArea[\"link\"]=dfBayArea[\"link\"].astype(str)\n",
    "dfSFCounty=dfBayArea[dfBayArea[\"link\"].isin(network_links.keys())]\n",
    "dfSFCountyBeam = linkstats_to_df(dfSFCounty)\n",
    "# print(\"LinkStatistics\")\n",
    "# print(\"VMT: {}, VHT: {}, ratio:{}\".format(dfSFCountyBeam.VMT_sum,dfSFCountyBeam.VHT_sum,dfSFCountyBeam.VMT_VHT_ratio))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "read the iter.plans.xml file to get % of trips by modes"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [],
   "source": [
    "class detailed_plans(object):\n",
    "    def __init__(self,folder_path):\n",
    "        # get the path of the output_network file\n",
    "        with gzip.open(folder_path, 'rb') as f:\n",
    "            file_content = f.read()\n",
    "            plans_root = ET.fromstring(file_content)\n",
    "        # internal-internal trips\n",
    "        self.ii_car_legs = 0\n",
    "        self.ii_walk_legs = 0\n",
    "        self.ii_drive_transit_legs = 0\n",
    "        self.ii_walk_transit_legs = 0\n",
    "        self.ii_bike_legs = 0\n",
    "        self.ii_bike_transit_legs = 0\n",
    "        self.ii_ridehail_legs = 0\n",
    "        self.ii_ridehail_transit_legs = 0\n",
    "        self.ii_teleportation_legs=0\n",
    "        self.ii_other_legs=0\n",
    "        self.ii_nonlabel_legs=0\n",
    "        self.ii_sr2_legs = 0\n",
    "        self.ii_sr3_legs = 0\n",
    "        self.ii_nonlabel_legs = 0\n",
    "        self.ii_total_legs = 0\n",
    "        self.ii_agent_count = 0\n",
    "        self.ii_activities = 0\n",
    "        self.ii_total_trips= 0\n",
    "        # external-external trips\n",
    "        self.ix_car_legs = 0\n",
    "        self.ix_walk_legs = 0\n",
    "        self.ix_drive_transit_legs = 0\n",
    "        self.ix_walk_transit_legs = 0\n",
    "        self.ix_bike_legs = 0\n",
    "        self.ix_bike_transit_legs = 0\n",
    "        self.ix_ridehail_legs = 0\n",
    "        self.ix_ridehail_transit_legs = 0\n",
    "        self.ix_teleportation_legs=0\n",
    "        self.ix_other_legs=0\n",
    "        self.ix_nonlabel_legs=0\n",
    "        self.ix_sr2_legs = 0\n",
    "        self.ix_sr3_legs = 0\n",
    "        self.ix_nonlabel_legs=0\n",
    "        self.ix_total_legs = 0\n",
    "        self.ix_agent_count = 0\n",
    "        self.ix_activities = 0\n",
    "        self.ix_total_trips= 0\n",
    "        for persons in (plans_root.iter('person')):\n",
    "            for child in persons.iter(\"plan\"):\n",
    "                if \"yes\" in child.get(\"selected\"):\n",
    "                    if \"ix\" not in persons.get(\"id\"):\n",
    "                        agent_activity = 0\n",
    "                        for activity in child.iter(\"activity\"):\n",
    "                            agent_activity += 1\n",
    "                        self.ii_activities += (agent_activity)\n",
    "                        self.ii_total_trips += (agent_activity - 1)\n",
    "                        self.ii_agent_count+=1\n",
    "                        for leg_mode in child.iter(\"leg\"):\n",
    "                            self.ii_total_legs+=1\n",
    "                            if \"car\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_car_legs+=1\n",
    "                            elif \"walk_transit\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_walk_transit_legs+=1\n",
    "                            elif \"walk\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_walk_legs+=1\n",
    "                            elif \"drive_transit\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_drive_transit_legs+=1\n",
    "                            elif \"bike_transit\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_bike_transit_legs+=1\n",
    "                            elif \"bike\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_bike_legs+=1\n",
    "                            elif \"ride_hail_transit\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_ridehail_transit_legs+=1\n",
    "                            elif \"ride_hail\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_ridehail_legs+=1\n",
    "                            elif \"teleportation\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_teleportation_legs+=1\n",
    "                            elif \"other\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_other_legs+=1\n",
    "                            elif \"sr2\" in leg_mode.get(\"mode\") or \"hov2\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_sr2_legs+=1\n",
    "                            elif \"sr3\" in leg_mode.get(\"mode\") or \"hov3\" in leg_mode.get(\"mode\"):\n",
    "                                self.ii_sr3_legs+=1\n",
    "                            else:\n",
    "                                # this are non-labeled modes\n",
    "                                self.ii_nonlabel_legs+=1\n",
    "                                # # do nothing\n",
    "                                # None\n",
    "                                # # get the count by trips_modes\n",
    "                    else:\n",
    "                        agent_activity = 0\n",
    "                        for activity in child.iter(\"activity\"):\n",
    "                            agent_activity += 1\n",
    "                        self.ix_activities += (agent_activity)\n",
    "                        self.ix_total_trips += (agent_activity - 1)\n",
    "                        self.ix_agent_count+=1\n",
    "                        for leg_mode in child.iter(\"leg\"):\n",
    "                            self.ix_total_legs+=1\n",
    "                            if \"car\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_car_legs+=1\n",
    "                            elif \"walk_transit\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_walk_transit_legs+=1\n",
    "                            elif \"walk\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_walk_legs+=1\n",
    "                            elif \"drive_transit\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_drive_transit_legs+=1\n",
    "                            elif \"bike_transit\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_bike_transit_legs+=1\n",
    "                            elif \"bike\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_bike_legs+=1\n",
    "                            elif \"ride_hail_transit\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_ridehail_transit_legs+=1\n",
    "                            elif \"ride_hail\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_ridehail_legs+=1\n",
    "                            elif \"teleportation\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_teleportation_legs+=1\n",
    "                            elif \"other\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_other_legs+=1\n",
    "                            elif \"sr2\" in leg_mode.get(\"mode\") or \"hov2\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_sr2_legs+=1\n",
    "                            elif \"sr3\" in leg_mode.get(\"mode\") or \"hov3\" in leg_mode.get(\"mode\"):\n",
    "                                self.ix_sr3_legs+=1\n",
    "                            else:\n",
    "                                # this are non-labeled modes\n",
    "                                self.ix_nonlabel_legs+=1\n",
    "                                # # do nothing\n",
    "                                # None\n",
    "                                # # get the count by trips_modes\n",
    "\n",
    "        self.TT_activity_type = []\n",
    "        self.ii_activity_type = []\n",
    "\n",
    "        for child in (plans_root.iter('person')):\n",
    "            for subchild in child.iter(\"activity\"):\n",
    "                act_type = subchild.get(\"type\")\n",
    "                self.TT_activity_type.append(act_type)\n",
    "\n",
    "        for child in (plans_root.iter('person')):\n",
    "            if \"ix\" not in child.get(\"id\"):\n",
    "                for subchild in child.iter(\"activity\"):\n",
    "                    act_type = subchild.get(\"type\")\n",
    "                    self.ii_activity_type.append(act_type)\n",
    "\n",
    "    def get_ii_modeshare(self):\n",
    "        # calculate the mode share\n",
    "        ii_modeshare = {}\n",
    "        ii_modeshare[\"car_share\"] = (self.ii_car_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"walk_share\"] = (self.ii_walk_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"walk_transit_share\"] = (self.ii_walk_transit_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"drive_transit_share\"] = (self.ii_drive_transit_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"ridehail_transit_share\"] = (self.ii_ridehail_transit_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"bike_transit_share\"] = (self.ii_bike_transit_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"transit_share\"] = ((self.ii_walk_transit_legs + self.ii_drive_transit_legs + self.ii_ridehail_transit_legs + self.ii_bike_transit_legs)/self.ii_total_legs)\n",
    "        ii_modeshare[\"bike_share\"] = (self.ii_bike_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"ridehail_share\"] = (self.ii_ridehail_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"teleportation_share\"] = (self.ii_teleportation_legs_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"other_share\"] = (self.ii_other_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"sr2_share\"] = (self.ii_sr2_legs/self.ii_total_legs)\n",
    "        ii_modeshare[\"sr3_share\"] = (self.ii_sr3_legs/self.ii_total_legs)\n",
    "        return ii_modeshare\n",
    "\n",
    "    def get_overall_modeshare(self):\n",
    "        # calculate the mode share\n",
    "        modeshare = {}\n",
    "        modeshare[\"car_share\"] = (self.ii_car_legs+self.ix_car_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"walk_share\"] = (self.ii_walk_legs+self.ix_walk_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"walk_transit_share\"] = (self.ii_walk_transit_legs+self.ix_walk_transit_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"drive_transit_share\"] = (self.ii_drive_transit_legs + self.ix_drive_transit_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"ridehail_transit_share\"] = (self.ii_ridehail_transit_legs+self.ix_ridehail_transit_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"bike_transit_share\"] = (self.ii_bike_transit_legs+self.ix_bike_transit_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"transit_share\"] = ((self.ii_walk_transit_legs + self.ii_drive_transit_legs + self.ii_ridehail_transit_legs + self.ii_bike_transit_legs)+(self.ix_walk_transit_legs + self.ix_drive_transit_legs + self.ix_ridehail_transit_legs + self.ix_bike_transit_legs))/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"bike_share\"] = (self.ii_bike_legs+self.ix_bike_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"ridehail_share\"] = (self.ii_ridehail_legs+self.ix_ridehail_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"teleportation_share\"] = (self.ii_teleportation_legs_legs+self.ix_teleportation_legs_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"other_share\"] = (self.ii_other_legs+self.ix_other_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"sr2_share\"] = (self.ii_sr2_legs+self.ix_sr2_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        modeshare[\"sr3_share\"] = (self.ii_sr3_legs+self.ix_sr3_legs)/(self.ii_total_legs+self.ix_total_legs)\n",
    "        return modeshare\n",
    "\n",
    "    def get_TT_unlinked_trips(self):\n",
    "        unl_trips ={}\n",
    "        unl_trips[\"car\"] = self.ix_car_legs + self.ii_car_legs\n",
    "        unl_trips[\"transit\"] = (self.ii_walk_transit_legs + self.ii_drive_transit_legs + self.ii_ridehail_transit_legs + self.ii_bike_transit_legs)+(self.ix_walk_transit_legs + self.ix_drive_transit_legs + self.ix_ridehail_transit_legs + self.ix_bike_transit_legs)\n",
    "        unl_trips[\"pedestrian\"] = (self.ii_walk_legs+self.ix_walk_legs)\n",
    "        unl_trips[\"ridehail\"] = (self.ii_ridehail_legs+self.ix_ridehail_legs)\n",
    "        unl_trips[\"bike\"] = (self.ii_bike_legs+self.ix_bike_legs)\n",
    "        unl_trips[\"teleportation\"] = (self.ii_teleportation_legs+self.ix_teleportation_legs)\n",
    "        unl_trips[\"other\"] = (self.ii_other_legs+self.ix_other_legs)\n",
    "        unl_trips[\"sr2\"] = (self.ii_sr2_legs+self.ix_sr2_legs)\n",
    "        unl_trips[\"sr3\"] = (self.ii_sr3_legs+self.ix_sr3_legs)\n",
    "        unl_trips[\"nonlabel\"] = (self.ii_nonlabel_legs+self.ix_nonlabel_legs)\n",
    "        unl_trips[\"trips\"] = self.ix_total_trips + self.ii_total_trips\n",
    "        return unl_trips\n",
    "\n",
    "    def get_ii_unlinked_trips(self):\n",
    "        unl_trips ={}\n",
    "        unl_trips[\"car\"] = self.ii_car_legs\n",
    "        unl_trips[\"transit\"] = (self.ii_walk_transit_legs + self.ii_drive_transit_legs + self.ii_ridehail_transit_legs + self.ii_bike_transit_legs)\n",
    "        unl_trips[\"pedestrian\"] = (self.ii_walk_legs)\n",
    "        unl_trips[\"ridehail\"] = (self.ii_ridehail_legs)\n",
    "        unl_trips[\"bike\"] = self.ii_bike_legs\n",
    "        unl_trips[\"teleportation\"] = self.ii_teleportation_legs\n",
    "        unl_trips[\"other\"] = self.ii_other_legs\n",
    "        unl_trips[\"sr2\"] = self.ii_sr2_legs\n",
    "        unl_trips[\"sr3\"] = self.ii_sr3_legs\n",
    "        unl_trips[\"nonlabel\"] = (self.ii_nonlabel_legs)\n",
    "        unl_trips[\"trips\"] = self.ii_total_trips\n",
    "        return unl_trips\n",
    "\n",
    "    def get_TT_activity_type(self):\n",
    "        return Counter(self.TT_activity_type)\n",
    "\n",
    "    def get_ii_activity_type(self):\n",
    "        return Counter(self.ii_activity_type)\n",
    "\n",
    "dfBayAreaBeamplans =  detailed_plans(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".plans.xml.gz\")))\n",
    "\n",
    "# modeshare = dfBayAreaBeamplans.get_overall_modeshare()\n",
    "# print(modeshare)\n",
    "# print(\"External agent: {}\".format(dfBayAreaBeamplans.ix_agent_count))\n",
    "# print(\"Internal agent: {}\".format(dfBayAreaBeamplans.ii_agent_count))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "auto_deficient     0.582677\nauto_sufficient    0.256124\nno_auto            0.161199\nName: autoWorkRatio, dtype: float64"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_agents_autoWorkRatio_status(folder_path):\n",
    "    with gzip.open(folder_path, 'rb') as f:\n",
    "        file_content = f.read()\n",
    "    plans_root = ET.fromstring(file_content)\n",
    "    attribute_name = {}\n",
    "    # get the dictionary with personID and autoWorkRatio\n",
    "    for child in (plans_root.iter('person')):\n",
    "        for subchild in child.iter(\"attributes\"):\n",
    "            for schild in subchild.iter(\"attribute\"):\n",
    "                if \"autoWorkRatio\" in schild.get(\"name\"):\n",
    "                    txt = schild.text\n",
    "                    attribute_name[child.get(\"id\")] = txt\n",
    "    return attribute_name\n",
    "\n",
    "def get_agentIDs(folder_path):\n",
    "    with gzip.open(folder_path, 'rb') as f:\n",
    "        file_content = f.read()\n",
    "    plans_root = ET.fromstring(file_content)\n",
    "    exp_personId = {}\n",
    "    counter=0\n",
    "    # get the dictionary with personID and autoWorkRatio\n",
    "    for persons in (plans_root.iter('person')):\n",
    "        for child in persons.iter(\"plan\"):\n",
    "            if \"yes\" in child.get(\"selected\"):\n",
    "                exp_personId[counter] =persons.get(\"id\")\n",
    "                counter+=1\n",
    "    return (pd.DataFrame(exp_personId.items(), columns=[\"item_no\",'personID']))\n",
    "\n",
    "attribute_name = get_agents_autoWorkRatio_status(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".plans.xml.gz\")))\n",
    "dfExpPersonsList = get_agentIDs(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".plans.xml.gz\")))\n",
    "dfExpPersonsList[\"autoWorkRatio\"] = dfExpPersonsList[\"personID\"].map(attribute_name)\n",
    "# get Total agents, autoWorkRatio status\n",
    "dfExpPersonsList[\"autoWorkRatio\"].value_counts(normalize=True)\n",
    "# get internal agents, autoWorkRatio status\n",
    "dfExpPersonsList[~dfExpPersonsList[\"personID\"].str.startswith(\"ix\",na=False)][\"autoWorkRatio\"].value_counts(normalize=True)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\goyal\\.virtualenvs\\t-score-0msqhlpo\\lib\\site-packages\\pandas\\core\\frame.py:3607: 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: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self._set_item(key, value)\n"
     ]
    }
   ],
   "source": [
    "class detailed_events(object):\n",
    "    def __init__(self,folder_path):\n",
    "        self.dfEvents = pd.read_csv(folder_path,low_memory=False)\n",
    "        self.dfEvents[\"Travel_Time\"] = self.dfEvents[\"arrivalTime\"] - self.dfEvents[\"departureTime\"]\n",
    "        self.TT_PTBoardings = {}\n",
    "        PersonEnterVeh = self.dfEvents.loc[(self.dfEvents[\"type\"]==\"PersonEntersVehicle\") &\n",
    "                                       (~self.dfEvents[\"vehicle\"].str.contains(\"rideHailVehicle\",na=False)) &\n",
    "                                       (~self.dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False))]\n",
    "\n",
    "        pt_ridreship = len(PersonEnterVeh[PersonEnterVeh[\"vehicle\"].str.contains(\"SF|BA\", na=False)])\n",
    "        # create dictionary: vehicle:mode\n",
    "        veh_mode_df = self.dfEvents.loc[(self.dfEvents[\"type\"]==\"PathTraversal\") &(self.dfEvents[\"vehicle\"].str.contains(\"SF|BA\", na=False))]\n",
    "        veh_mode = dict(zip(veh_mode_df.vehicle, veh_mode_df[\"mode\"]))\n",
    "        # map them to get number of boardings by PT mode\n",
    "        PersonEnterVeh[\"mode_veh\"] = PersonEnterVeh[\"vehicle\"].map(veh_mode,na_action='ignore')\n",
    "        # get counts\n",
    "        self.veh_counts = PersonEnterVeh[~PersonEnterVeh[\"mode_veh\"].isna()].mode_veh.value_counts()\n",
    "        self.bus_boarding = self.veh_counts.bus\n",
    "        self.TT_PTBoardings[\"bus_boarding\"] = self.veh_counts.bus\n",
    "        self.tram_boarding = self.veh_counts.tram\n",
    "        self.TT_PTBoardings[\"tram_boarding\"] = self.veh_counts.tram\n",
    "        self.rail_boarding = self.veh_counts.subway\n",
    "        self.TT_PTBoardings[\"rail_boarding\"] = self.veh_counts.subway\n",
    "        self.cable_boarding = self.veh_counts.cable_car\n",
    "        self.TT_PTBoardings[\"cable_boarding\"] = self.veh_counts.cable_car\n",
    "\n",
    "    def get_TT_modeshare(self):\n",
    "        TT_modeshare={}\n",
    "        # Calculate the mode share\n",
    "        mode_counts = self.dfEvents[self.dfEvents[\"type\"]==\"ModeChoice\"][\"mode\"].value_counts()\n",
    "        transit_share=0\n",
    "        for mode in mode_counts.keys():\n",
    "            if \"transit\" not in mode:\n",
    "                mode_share = str(mode) + \"_share\"  #skip\n",
    "                TT_modeshare[mode_share] = mode_counts[mode]/mode_counts.sum()\n",
    "            else:\n",
    "                transit_share+=mode_counts[mode]\n",
    "                # transit = {k: v for k, v in TT_modeshare.items() if \"_transit\" in k}\n",
    "        TT_modeshare[\"transit_share\"] = transit_share/mode_counts.sum()\n",
    "\n",
    "        return TT_modeshare\n",
    "\n",
    "    def get_ii_PTBoardings(self):\n",
    "        dfEvents_ii = self.dfEvents.copy()\n",
    "        PTBoardings = {}\n",
    "        dfEvents_ii = dfEvents_ii.loc[~dfEvents_ii[\"person\"].str.startswith(\"ix\", na=False)]\n",
    "\n",
    "        PersonEnterVeh = dfEvents_ii.loc[(dfEvents_ii[\"type\"]==\"PersonEntersVehicle\") &\n",
    "                                     (~dfEvents_ii[\"vehicle\"].str.contains(\"rideHailVehicle\",na=False)) &\n",
    "                                     (~dfEvents_ii[\"person\"].str.contains(\"TransitDriverAgent\",na=False))]\n",
    "        pt_ridreship = len(PersonEnterVeh[PersonEnterVeh[\"vehicle\"].str.contains(\"SF|BA\", na=False)])\n",
    "        # create dictionary: vehicle:mode\n",
    "        veh_mode_df = dfEvents_ii.loc[(dfEvents_ii[\"type\"]==\"PathTraversal\") &(dfEvents_ii[\"vehicle\"].str.contains(\"SF|BA\", na=False))]\n",
    "        veh_mode = dict(zip(veh_mode_df.vehicle, veh_mode_df[\"mode\"]))\n",
    "        # map them to get number of boardings by PT mode\n",
    "        PersonEnterVeh[\"mode_veh\"] = PersonEnterVeh[\"vehicle\"].map(veh_mode,na_action='ignore')\n",
    "        # get counts\n",
    "        veh_counts = PersonEnterVeh[~PersonEnterVeh[\"mode_veh\"].isna()].mode_veh.value_counts()\n",
    "        PTBoardings[\"bus_boarding\"] = veh_counts.bus\n",
    "        PTBoardings[\"tram_boarding\"] =veh_counts.tram\n",
    "        PTBoardings[\"rail_boarding\"] =veh_counts.subway\n",
    "        PTBoardings[\"cable_boarding\"] = veh_counts.cable_car\n",
    "\n",
    "        return PTBoardings\n",
    "\n",
    "    def get_ii_modeshare(self):\n",
    "        ii_modeshare={}\n",
    "        # Calculate the mode share\n",
    "        dfEvents_ii = self.dfEvents.loc[~self.dfEvents[\"person\"].str.startswith(\"ix\", na=False)].copy()\n",
    "        mode_counts = dfEvents_ii.loc[dfEvents_ii[\"type\"]==\"ModeChoice\"][\"mode\"].value_counts()\n",
    "        transit_share=0\n",
    "        for mode in mode_counts.keys():\n",
    "            if \"transit\" not in mode:\n",
    "                mode_share = str(mode) + \"_share\"  #skip\n",
    "                ii_modeshare[mode_share] = mode_counts[mode]/mode_counts.sum()\n",
    "            else:\n",
    "                transit_share+=mode_counts[mode]\n",
    "                # transit = {k: v for k, v in TT_modeshare.items() if \"_transit\" in k}\n",
    "        ii_modeshare[\"transit_share\"] = transit_share/mode_counts.sum()\n",
    "        return ii_modeshare\n",
    "\n",
    "    def get_agents_count(self):\n",
    "        agent_counts = {}\n",
    "        ix_agent = self.dfEvents.loc[self.dfEvents[\"person\"].str.startswith(\"ix\", na=False),\"person\"].nunique()\n",
    "        agent_counts[\"ix_agents\"] = ix_agent\n",
    "        ii_agent = self.dfEvents.loc[(~ self.dfEvents[\"person\"].str.startswith(\"ix\", na=False)) & (~self.dfEvents[\"person\"].str.startswith(\"rideHailAgent\",na=False)) & (~self.dfEvents[\"person\"].str.startswith(\"TransitDriverAgent\",na=False)),\"person\"].nunique()\n",
    "        agent_counts[\"ii_agents\"] = ii_agent\n",
    "        ridehail_agent = self.dfEvents.loc[self.dfEvents[\"person\"].str.startswith(\"ride\", na=False),\"person\"].nunique()\n",
    "        agent_counts[\"ridehail_agent\"] = ridehail_agent\n",
    "        total_agent = self.dfEvents[\"person\"].nunique()\n",
    "        agent_counts[\"total_agent\"] = total_agent\n",
    "        return agent_counts\n",
    "\n",
    "dfBayAreaBeamEvents = detailed_events(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".events.csv.gz\")))\n",
    "# dfBayAreaBeamEvents.get_TT_modeshare()\n",
    "# dfBayAreaBeamEvents.get_ii_modeshare()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [],
   "source": [
    "dfBayAreaZerothplans =  detailed_plans(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(first_iter)),(str(first_iter)+\".plans.xml.gz\")))\n",
    "dfBayAreaZerothEvents = detailed_events(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(first_iter)),(str(first_iter)+\".events.csv.gz\")))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [],
   "source": [
    "# Lets export these values in csv format\n",
    "from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment\n",
    "# read the excel template\n",
    "workbook = load_workbook(filename=BASE_DIR.parent.joinpath(\"template\", \"templatev9.0.xlsx\"))\n",
    "sheet = workbook[\"Summary\"]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [],
   "source": [
    "# Summary worksheet\n",
    "# general information\n",
    "# Model Run\n",
    "iteration_counter = 1\n",
    "cell_row=1\n",
    "cell_column=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column+1)).value = (\"BEAM Simulation iteration {}\".format(iteration_counter))\n",
    "# Folder\n",
    "sheet.cell(row=(cell_row+1),column=(cell_column+1)).value = simulation_run\n",
    "# Date\n",
    "sheet.cell(row=(cell_row+2),column=(cell_column+1)).value =\"{}\".format(date_time)\n",
    "# Notes\n",
    "sheet.cell(row=(cell_row+3),column=(cell_column+1)).value = \"\"\n",
    "#Sample information\n",
    "# sample_pct = 0.05\n",
    "sheet.cell(row=(cell_row+6),column=(cell_column+1)).value = sample_pct*100\n",
    "sheet.cell(row=(cell_row+7),column=(cell_column+1)).value = 100/(100*sample_pct)\n",
    "# scale_factor = 100/(100*sample_pct)\n",
    "\n",
    "# dump values for full Bay Area\n",
    "#change the heading of the simulation run\n",
    "\n",
    "#re-locate the fixed row and column = 11\n",
    "cell_row=11\n",
    "cell_column=6\n",
    "\n",
    "temp = \"Beam_Run_\"+date_time\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = temp\n",
    "# sheet.merge_cells('D2:E2')\n",
    "# sheet[\"F2\"].font = Font(b=True,size=10)\n",
    "\n",
    "# VMT & VHT Data\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeam.VMT_sum\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeam.VHT_sum\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeam.VMT_VHT_ratio\n",
    "# sheet[\"F9\"] = (VHD_sum)\n",
    "\n",
    "# Mode Share data\n",
    "cell_row=17\n",
    "cell_column=6\n",
    "\n",
    "# sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeamEvents.get_TT_modeshare()[\"car_share\"]\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeamEvents.get_TT_modeshare().get(\"car_share\",0)\n",
    "cell_row+=1\n",
    "# sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeamEvents.get_TT_modeshare()[\"transit_share\"]\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeamEvents.get_TT_modeshare().get(\"transit_share\",0)\n",
    "cell_row+=1\n",
    "# sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_TT_modeshare()[\"walk_share\"]\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_TT_modeshare().get(\"walk_share\",0)\n",
    "cell_row+=1\n",
    "# sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_TT_modeshare()[\"bike_share\"]\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_TT_modeshare().get(\"bike_share\",0)\n",
    "cell_row+=1\n",
    "# sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeamEvents.get_TT_modeshare()[\"ride_hail_share\"]\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_TT_modeshare().get(\"ride_hail_share\",0)\n",
    "\n",
    "# Boarding data\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=(dfBayAreaBeamEvents.TT_PTBoardings[\"bus_boarding\"]*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaBeamEvents.TT_PTBoardings[\"tram_boarding\"]+dfBayAreaBeamEvents.TT_PTBoardings[\"cable_boarding\"])*scale_factor\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaBeamEvents.TT_PTBoardings[\"rail_boarding\"]*scale_factor)\n",
    "\n",
    "# Agent stats\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaBeamEvents.get_agents_count()[\"ix_agents\"]*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaBeamEvents.get_agents_count()[\"ii_agents\"]*scale_factor)\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaBeamEvents.get_agents_count().get(\"ridehail_agent\",0)*scale_factor)\n",
    "\n",
    "# leg trip information\n",
    "cell_row=33\n",
    "cell_column=6\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  ((dfBayAreaBeamplans.get_TT_unlinked_trips().get(\"car_share\",0)+dfBayAreaBeamplans.get_TT_unlinked_trips().get(\"sr2\",0)+dfBayAreaBeamplans.get_TT_unlinked_trips().get(\"sr3\",0))*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamplans.get_TT_unlinked_trips().get(\"transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamplans.get_TT_unlinked_trips().get(\"pedestrian\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamplans.get_TT_unlinked_trips().get(\"ridehail\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamplans.get_TT_unlinked_trips().get(\"bike\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamplans.get_TT_unlinked_trips().get(\"teleportation\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamplans.get_TT_unlinked_trips().get(\"other\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamplans.get_TT_unlinked_trips().get(\"nonlabel\",0)*scale_factor)\n",
    "\n",
    "# \"Total Trip Information\"\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamplans.get_TT_unlinked_trips()[\"trips\"]*scale_factor)\n",
    "\n",
    "trip_purpose = dfBayAreaBeamplans.get_TT_activity_type()\n",
    "# activity_type --> trip purpose\n",
    "cell_row=49\n",
    "cell_column=6\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=   trip_purpose[\"home\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=   trip_purpose[\"othmaint\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=   trip_purpose[\"escort\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=   trip_purpose[\"shopping\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=   trip_purpose[\"social\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  trip_purpose[\"work\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=   trip_purpose[\"eatout\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=   trip_purpose[\"othdiscr\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  trip_purpose[\"univ\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=   trip_purpose[\"school\"]/sum(trip_purpose.values())\n",
    "\n",
    "\n",
    "# for SF County\n",
    "#change heading of the simulation run\n",
    "temp = \"Beam_Run_\"+date_time\n",
    "cell_row = 11\n",
    "cell_column =16\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = temp\n",
    "# sheet.merge_cells('D2:E2')\n",
    "# sheet[\"F2\"].font = Font(b=True,size=10)\n",
    "\n",
    "# VMT & VHT Data\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfSFCountyBeam.VMT_sum\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfSFCountyBeam.VHT_sum\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfSFCountyBeam.VMT_VHT_ratio\n",
    "# sheet[\"F9\"] = (VHD_sum)\n",
    "\n",
    "# Mode Share data\n",
    "cell_row = 17\n",
    "cell_column =16\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_ii_modeshare().get(\"car_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeamEvents.get_ii_modeshare().get(\"transit_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeamEvents.get_ii_modeshare().get(\"walk_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaBeamEvents.get_ii_modeshare().get(\"bike_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_ii_modeshare().get(\"ride_hail_share\",0)\n",
    "cell_row+=2\n",
    "\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaBeamEvents.get_ii_PTBoardings()[\"bus_boarding\"]*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamEvents.get_ii_PTBoardings()[\"tram_boarding\"]+dfBayAreaBeamEvents.get_ii_PTBoardings()[\"cable_boarding\"])*scale_factor\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamEvents.get_ii_PTBoardings()[\"rail_boarding\"]*scale_factor)\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamEvents.get_agents_count()[\"ii_agents\"]*scale_factor)\n",
    "cell_row+=2\n",
    "# sheet[\"P23\"] = (dfSFBeamEvents.get_agents_count()[\"ii_agents\"]*5)\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamEvents.get_agents_count().get(\"ridehail_agent\",0)*scale_factor)\n",
    "\n",
    "# leg trip information\n",
    "cell_row = 32\n",
    "cell_column =16\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = ((dfBayAreaBeamplans.get_ii_unlinked_trips().get(\"car\",0)+dfBayAreaBeamplans.get_ii_unlinked_trips().get(\"sr2\",0)+dfBayAreaBeamplans.get_ii_unlinked_trips().get(\"sr3\",0))*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamplans.get_ii_unlinked_trips().get(\"transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamplans.get_ii_unlinked_trips().get(\"pedestrian\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamplans.get_ii_unlinked_trips().get(\"ridehail\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamplans.get_ii_unlinked_trips().get(\"bike\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamplans.get_ii_unlinked_trips().get(\"teleportation\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamplans.get_ii_unlinked_trips().get(\"other\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamplans.get_ii_unlinked_trips().get(\"nonlabel\",0)*scale_factor)\n",
    "# \"Total Trip Information\"\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaBeamplans.get_ii_unlinked_trips()[\"trips\"]*scale_factor)\n",
    "\n",
    "\n",
    "trip_purpose = dfBayAreaBeamplans.get_ii_activity_type()\n",
    "# activity_type --> trip purpose\n",
    "cell_row = 48\n",
    "cell_column =16\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"home\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"othmaint\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"escort\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"shopping\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"social\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"work\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"eatout\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"othdiscr\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"univ\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"school\"]/sum(trip_purpose.values())"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [],
   "source": [
    "# Initial run details - this replicates SFChamp scenario to an extent (not VMT, VHT and VMT_VHT_ratio)\n",
    "# dump values for full SF Area\n",
    "#change the heading of the simulation run\n",
    "cell_row=11\n",
    "cell_column=9\n",
    "temp = \"SF_Champ_\"+date_time\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = temp\n",
    "# sheet.merge_cells('D2:E2')\n",
    "# sheet[\"F2\"].font = Font(b=True,size=10)\n",
    "\n",
    "# VMT & VHT Data\n",
    "# sheet[\"I6\"] = dfSFCountyRdNtwrk2016[\"VMT\"].sum()\n",
    "# sheet[\"I7\"] = dfSFCountyRdNtwrk2016[\"VHT\"].sum()\n",
    "# sheet[\"I8\"] = np.true_divide(dfSFCountyRdNtwrk2016[\"VMT\"].sum(),dfSFCountyRdNtwrk2016[\"VHT\"].sum())\n",
    "# sheet[\"F9\"] = (VHD_sum)\n",
    "\n",
    "# Mode Share data\n",
    "cell_row=17\n",
    "cell_column=9\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaZerothEvents.get_TT_modeshare().get(\"car_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaZerothEvents.get_TT_modeshare().get(\"transit_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaZerothEvents.get_TT_modeshare().get(\"walk_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaZerothEvents.get_TT_modeshare().get(\"bike_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaZerothEvents.get_TT_modeshare().get(\"ride_hail_share\",0)\n",
    "\n",
    "# Boarding data\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothEvents.TT_PTBoardings[\"bus_boarding\"]*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothEvents.TT_PTBoardings[\"tram_boarding\"]+dfBayAreaZerothEvents.TT_PTBoardings[\"cable_boarding\"])*scale_factor\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothEvents.TT_PTBoardings[\"rail_boarding\"]*scale_factor)\n",
    "\n",
    "# Agent stats\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothEvents.get_agents_count()[\"ix_agents\"]*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothEvents.get_agents_count()[\"ii_agents\"]*scale_factor)\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothEvents.get_agents_count().get(\"ridehail_agent\",0)*scale_factor)\n",
    "\n",
    "# leg trip information\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = ((dfBayAreaZerothplans.get_TT_unlinked_trips().get(\"car\",0)+dfBayAreaZerothplans.get_TT_unlinked_trips().get(\"sr2\",0)+dfBayAreaZerothplans.get_TT_unlinked_trips().get(\"sr3\",0))*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothplans.get_TT_unlinked_trips().get(\"transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothplans.get_TT_unlinked_trips().get(\"pedestrian\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothplans.get_TT_unlinked_trips().get(\"ridehail\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothplans.get_TT_unlinked_trips().get(\"bike\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothplans.get_TT_unlinked_trips().get(\"teleportation\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothplans.get_TT_unlinked_trips().get(\"other\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothplans.get_TT_unlinked_trips().get(\"nonlabel\",0)*scale_factor)\n",
    "\n",
    "# \"Total Trip Information\"\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothplans.get_TT_unlinked_trips()[\"trips\"]*scale_factor)\n",
    "\n",
    "trip_purpose = dfBayAreaZerothplans.get_TT_activity_type()\n",
    "# activity_type --> trip purpose\n",
    "cell_row=49\n",
    "cell_column=9\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"home\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= trip_purpose[\"othmaint\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"escort\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"shopping\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"social\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"work\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"eatout\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= trip_purpose[\"othdiscr\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"univ\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= trip_purpose[\"school\"]/sum(trip_purpose.values())\n",
    "\n",
    "\n",
    "# dump values\n",
    "# for Bay Area\n",
    "#change heading of the simulation run\n",
    "temp = \"SF_Champ_\"+date_time\n",
    "cell_row=11\n",
    "cell_column=19\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = temp\n",
    "# sheet.merge_cells('D2:E2')\n",
    "# sheet[\"F2\"].font = Font(b=True,size=10)\n",
    "\n",
    "# VMT & VHT Data\n",
    "# sheet[\"S6\"] = dfSFCountyRdNtwrk2016[\"VMT\"].sum()\n",
    "# sheet[\"S7\"] = dfSFCountyRdNtwrk2016[\"VHT\"].sum()\n",
    "# sheet[\"S8\"] = np.true_divide(dfSFCountyRdNtwrk2016[\"VMT\"].sum(),dfSFCountyRdNtwrk2016[\"VHT\"].sum())\n",
    "# sheet[\"F9\"] = (VHD_sum)\n",
    "\n",
    "# Mode Share data\n",
    "cell_row=17\n",
    "cell_column=19\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaZerothEvents.get_ii_modeshare().get(\"car_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaZerothEvents.get_ii_modeshare().get(\"transit_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaZerothEvents.get_ii_modeshare().get(\"walk_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaZerothEvents.get_ii_modeshare().get(\"bike_share\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = dfBayAreaZerothEvents.get_ii_modeshare().get(\"ride_hail_share\",0)\n",
    "\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothEvents.get_ii_PTBoardings()[\"bus_boarding\"]*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothEvents.get_ii_PTBoardings()[\"tram_boarding\"]+dfBayAreaZerothEvents.get_ii_PTBoardings()[\"cable_boarding\"])*scale_factor\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothEvents.get_ii_PTBoardings()[\"rail_boarding\"]*scale_factor)\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothEvents.get_agents_count()[\"ii_agents\"]*scale_factor)\n",
    "# sheet[\"P23\"] = (dfSFBeamEvents.get_agents_count()[\"ii_agents\"]*5)\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothEvents.get_agents_count().get(\"ridehail_agent\",0)*scale_factor)\n",
    "\n",
    "# leg trip information\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = ((dfBayAreaZerothplans.get_ii_unlinked_trips().get(\"car\",0)+dfBayAreaZerothplans.get_ii_unlinked_trips().get(\"sr2\",0)+dfBayAreaZerothplans.get_ii_unlinked_trips().get(\"sr3\",0))*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = ((dfBayAreaZerothplans.get_ii_unlinked_trips().get(\"transit\",0)*scale_factor))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothplans.get_ii_unlinked_trips().get(\"pedestrian\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothplans.get_ii_unlinked_trips().get(\"ridehail\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothplans.get_ii_unlinked_trips().get(\"bike\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothplans.get_ii_unlinked_trips().get(\"teleportation\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothplans.get_ii_unlinked_trips().get(\"other\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothplans.get_ii_unlinked_trips().get(\"nonlabel\",0)*scale_factor)\n",
    "\n",
    "\n",
    "# \"Total Trip Information\"\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfBayAreaZerothplans.get_ii_unlinked_trips()[\"trips\"]*scale_factor)\n",
    "\n",
    "\n",
    "trip_purpose = dfBayAreaZerothplans.get_ii_activity_type()\n",
    "# activity_type --> trip purpose\n",
    "cell_row=48\n",
    "cell_column=19\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"home\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= trip_purpose[\"othmaint\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"escort\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"shopping\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"social\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= trip_purpose[\"work\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"eatout\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"othdiscr\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"univ\"]/sum(trip_purpose.values())\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = trip_purpose[\"school\"]/sum(trip_purpose.values())\n",
    "cell_row+=1"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [],
   "source": [
    "def get_agents_autoWorkRatio_status(folder_path):\n",
    "    with gzip.open(folder_path, 'rb') as f:\n",
    "        file_content = f.read()\n",
    "    plans_root = ET.fromstring(file_content)\n",
    "    attribute_name = {}\n",
    "    # get the dictionary with personID and autoWorkRatio\n",
    "    for child in (plans_root.iter('person')):\n",
    "        for subchild in child.iter(\"attributes\"):\n",
    "            for schild in subchild.iter(\"attribute\"):\n",
    "                if \"autoWorkRatio\" in schild.get(\"name\"):\n",
    "                    txt = schild.text\n",
    "                    attribute_name[child.get(\"id\")] = txt\n",
    "    return attribute_name\n",
    "\n",
    "def get_agentIDs(folder_path):\n",
    "    with gzip.open(folder_path, 'rb') as f:\n",
    "        file_content = f.read()\n",
    "    plans_root = ET.fromstring(file_content)\n",
    "    exp_personId = {}\n",
    "    counter = 0\n",
    "    # get the dictionary with personID and autoWorkRatio\n",
    "    for child in (plans_root.iter('person')):\n",
    "        exp_personId[counter] = child.get(\"id\")\n",
    "        counter += 1\n",
    "    return (pd.DataFrame(exp_personId.items(), columns=[\"item_no\", 'personID']))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [],
   "source": [
    "attribute_name = get_agents_autoWorkRatio_status(\n",
    "    BASE_DIR.parent.joinpath(\"runs\", simulation_run, \"ITERS\", (\"it.\"+str(last_iter)),(str(last_iter)+\".plans.xml.gz\")))\n",
    "dfExpPersonsList = get_agentIDs(\n",
    "    BASE_DIR.parent.joinpath(\"runs\", simulation_run, \"ITERS\", (\"it.\"+str(last_iter)),(str(last_iter)+\".plans.xml.gz\")))\n",
    "dfExpPersonsList[\"autoWorkRatio\"] = dfExpPersonsList[\"personID\"].map(attribute_name)\n",
    "# get Total agents, autoWorkRatio status\n",
    "dfExpPersonsList[\"autoWorkRatio\"].value_counts(normalize=True)\n",
    "# get internal agents, autoWorkRatio status\n",
    "dfExpPersonsList[~dfExpPersonsList[\"personID\"].str.startswith(\"ix\", na=False)][\"autoWorkRatio\"].value_counts(\n",
    "    normalize=True)\n",
    "\n",
    "# autoWorkStatus\n",
    "cell_row=45\n",
    "cell_column=6\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList[\"autoWorkRatio\"].value_counts(normalize=True)[\"no_auto\"])\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList[\"autoWorkRatio\"].value_counts(normalize=True)[\"auto_sufficient\"])\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList[\"autoWorkRatio\"].value_counts(normalize=True)[\"auto_deficient\"])\n",
    "\n",
    "\n",
    "cell_row=44\n",
    "cell_column=16\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList[~dfExpPersonsList[\"personID\"].str.startswith(\"ix\", na=False)][\"autoWorkRatio\"].value_counts(normalize=True)[\"no_auto\"])\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList[~dfExpPersonsList[\"personID\"].str.startswith(\"ix\", na=False)][\"autoWorkRatio\"].value_counts(normalize=True)[\"auto_sufficient\"])\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList[~dfExpPersonsList[\"personID\"].str.startswith(\"ix\", na=False)][\"autoWorkRatio\"].value_counts(normalize=True)[\"auto_deficient\"])\n",
    "cell_row+=1"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [],
   "source": [
    "attribute_name_zerothplan = get_agents_autoWorkRatio_status(\n",
    "    BASE_DIR.parent.joinpath(\"runs\", simulation_run, \"ITERS\", (\"it.\"+str(first_iter)),(str(first_iter)+\".plans.xml.gz\")))\n",
    "dfExpPersonsList_zerothplan = get_agentIDs(\n",
    "    BASE_DIR.parent.joinpath(\"runs\", simulation_run, \"ITERS\", (\"it.\"+str(first_iter)),(str(first_iter)+\".plans.xml.gz\")))\n",
    "dfExpPersonsList_zerothplan[\"autoWorkRatio\"] = dfExpPersonsList_zerothplan[\"personID\"].map(attribute_name_zerothplan)\n",
    "\n",
    "# autoWorkStatus\n",
    "cell_row=45\n",
    "cell_column=9\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList_zerothplan[\"autoWorkRatio\"].value_counts(normalize=True)[\"no_auto\"])\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfExpPersonsList_zerothplan[\"autoWorkRatio\"].value_counts(normalize=True)[\"auto_sufficient\"])\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList_zerothplan[\"autoWorkRatio\"].value_counts(normalize=True)[\"auto_deficient\"])\n",
    "\n",
    "cell_row=44\n",
    "cell_column=19\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList_zerothplan[~dfExpPersonsList_zerothplan[\"personID\"].str.startswith(\"ix\", na=False)][\"autoWorkRatio\"].value_counts(normalize=True)[\"no_auto\"])\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList_zerothplan[~dfExpPersonsList_zerothplan[\"personID\"].str.startswith(\"ix\", na=False)][\"autoWorkRatio\"].value_counts(normalize=True)[\"auto_sufficient\"])\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value = (dfExpPersonsList_zerothplan[~dfExpPersonsList_zerothplan[\"personID\"].str.startswith(\"ix\", na=False)][\"autoWorkRatio\"].value_counts(normalize=True)[\"auto_deficient\"])\n",
    "\n",
    "# save the data\n",
    "# workbook.save(BASE_DIR.parent.joinpath(\"exported\", \"summary_report_v6.0.xlsx\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [],
   "source": [
    "#### check the missing agents\n",
    "dfBayAreaBeamEvents10 = detailed_events(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".events.csv.gz\")))\n",
    "dfExpEvents = dfBayAreaBeamEvents10.dfEvents\n",
    "s = dfExpEvents.loc[(~ dfExpEvents[\"person\"].str.startswith(\"ix\", na=False)) & (~ dfExpEvents[\"person\"].str.startswith(\"rideHailAgent\", na=False)) & (~ dfExpEvents[\"person\"].str.startswith(\"TransitDriverAgent\", na=False)) & (~ dfExpEvents[\"person\"].isna())][\"person\"].unique()\n",
    "pd.Series(s).to_csv(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(last_iter)), \"exp_event.csv\"))\n",
    "\n",
    "df0Event = dfBayAreaZerothEvents.dfEvents\n",
    "z = df0Event.loc[(~ df0Event[\"person\"].str.startswith(\"ix\", na=False)) & (~ df0Event[\"person\"].str.startswith(\"rideHailAgent\", na=False)) & (~ df0Event[\"person\"].str.startswith(\"TransitDriverAgent\", na=False)) & (~ df0Event[\"person\"].isna())][\"person\"].unique()\n",
    "pd.Series(z).to_csv(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(first_iter)), \"zero_event.csv\"))\n",
    "\n",
    "res =pd.Series(s)[~pd.Series(s).isin(pd.Series(z))]\n",
    "pd.Series(res).to_csv(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(last_iter)), \"agents_not_present.csv\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "Create the tour-purpose summary sheet\n"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [],
   "source": [
    "# get the agent list\n",
    "def get_agentIDs(folder_path):\n",
    "    with gzip.open(folder_path, 'rb') as f:\n",
    "        file_content = f.read()\n",
    "    plans_root = ET.fromstring(file_content)\n",
    "    exp_personId = []\n",
    "    # counter = 0\n",
    "    # get the dictionary with personID and autoWorkRatio\n",
    "    for child in (plans_root.iter('person')):\n",
    "        exp_personId.append(child.get(\"id\"))\n",
    "        # exp_personId[counter] = child.get(\"id\")\n",
    "        # counter += 1\n",
    "    return exp_personId\n",
    "    #(pd.DataFrame(exp_personId.items(), columns=[\"item_no\", 'personID']))\n",
    "\n",
    "def get_ii_agentIDs(folder_path):\n",
    "    with gzip.open(folder_path, 'rb') as f:\n",
    "        file_content = f.read()\n",
    "    plans_root = ET.fromstring(file_content)\n",
    "    exp_ii_personId = []\n",
    "    # counter = 0\n",
    "    # get the dictionary with personID and autoWorkRatio\n",
    "    for child in (plans_root.iter('person')):\n",
    "        if \"ix\" not in child.get(\"id\"):\n",
    "            exp_ii_personId.append(child.get(\"id\"))\n",
    "        # exp_personId[counter] = child.get(\"id\")\n",
    "        # counter += 1\n",
    "    return exp_ii_personId"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [],
   "source": [
    "# Lets export these values in csv format\n",
    "from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment\n",
    "\n",
    "# read the excel template\n",
    "# workbook = load_workbook(filename=BASE_DIR.parent.joinpath(\"template\", \"templatev7.0.xlsx\"))\n",
    "Mode_Choice_wrksheet = workbook[\"Mode_Choice\"]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "outputs": [],
   "source": [
    "# ExpPersonsList = get_agentIDs(BASE_DIR.parent.joinpath(\"runs\", simulation_run, \"ITERS\", \"it.10\", \"10.experienced_plans.xml.gz\"))\n",
    "# get list of agents for the last iteration (for SF County)\n",
    "iiExpPersonsList = get_ii_agentIDs(BASE_DIR.parent.joinpath(\"runs\", simulation_run, \"ITERS\", (\"it.\"+str(last_iter)),(str(last_iter)+\".plans.xml.gz\")))\n",
    "\n",
    "folder_path = BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".plans.xml.gz\"))\n",
    "with gzip.open(folder_path, 'rb') as f:\n",
    "    file_content = f.read()\n",
    "    plans_root = ET.fromstring(file_content)\n",
    "modechoice_summary = {\n",
    "    \"work\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"univ\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"school\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"shopping\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"othmaint\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"eatout\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"social\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"othdiscr\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"escort\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "}\n",
    "mode_list = []\n",
    "\n",
    "def get_auto_ownership_status(_root):\n",
    "    for child in _root.iter(\"attributes\"):\n",
    "        for schild in child.iter(\"attribute\"):\n",
    "            if \"autoWorkRatio\" in schild.get(\"name\"):\n",
    "                auto_ownship_status = schild.text\n",
    "    return auto_ownship_status\n",
    "\n",
    "# this is for the last iteration of the simulation run\n",
    "for child in (plans_root.iter('person')):\n",
    "    if child.get(\"id\") in iiExpPersonsList:\n",
    "        # print(child.get(\"id\"))\n",
    "        for subchild in child.iter(\"plan\"):\n",
    "            if \"yes\" in subchild.get(\"selected\"):\n",
    "                auto_ownship_status=get_auto_ownership_status(child)\n",
    "                for s in subchild.iter():\n",
    "                    if s.tag == 'activity':\n",
    "                        for s3 in s.iter('attribute'):\n",
    "                            if \"primary_purpose\" in s3.get(\"name\"):\n",
    "                                purpose = s3.text\n",
    "                                # print(purpose)\n",
    "                    if s.tag == 'leg':\n",
    "                        mode = s.get('mode')\n",
    "                        if \"hov2\" in mode:\n",
    "                            mode_list.append((purpose, \"sr2\"))\n",
    "                            modechoice_summary[purpose][auto_ownship_status][\"sr2\"]+=1\n",
    "                        elif \"hov3\" in mode:\n",
    "                            mode_list.append((purpose, \"sr3\"))\n",
    "                            modechoice_summary[purpose][auto_ownship_status][\"sr3\"]+=1\n",
    "                        else:\n",
    "                            mode_list.append((purpose, mode))\n",
    "                            modechoice_summary[purpose][auto_ownship_status][mode]+=1\n",
    "                    # print(mode)\n",
    "\n",
    "# populate records for SF County using last iteration records\n",
    "for tour_purpose in modechoice_summary.keys():\n",
    "    if \"work\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_column =11\n",
    "                cell_row=\"D\"\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_column =11\n",
    "                cell_row=\"E\"\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "                cell_column =11\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"school\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "                cell_column =26\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "                cell_column =26\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "                cell_column =26\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"univ\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "                cell_column =41\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "                cell_column =41\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "                cell_column =41\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"shopping\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "                cell_column =56\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "                cell_column =56\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "                cell_column =56\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"othmaint\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "                cell_column =71\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "                cell_column =71\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "                cell_column =71\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"eatout\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "                cell_column =86\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "                cell_column =86\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "                cell_column =86\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"social\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "                cell_column =101\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "                cell_column =101\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "                cell_column =101\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"othdiscr\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "                cell_column =116\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "                cell_column =116\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "                cell_column =116\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"escort\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "                cell_column =131\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "                cell_column =131\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "                cell_column =131\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [],
   "source": [
    "dflastIterationStats=pd.DataFrame.from_dict({(i,j): modechoice_summary[i][j]\n",
    "                                             for i in modechoice_summary.keys()\n",
    "                                             for j in modechoice_summary[i].keys()},\n",
    "                                            orient='index')\n",
    "dflastIterationStats.reset_index(drop=False).to_csv(BASE_DIR.parent.joinpath(\"exported\",\"lastIterationModeChoice_Count.csv\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [],
   "source": [
    "# ExpPersonsList = get_agentIDs(BASE_DIR.parent.joinpath(\"runs\", simulation_run, \"ITERS\", \"it.10\", \"10.experienced_plans.xml.gz\"))\n",
    "# get list of agents for the 0th iteration (for SF County)\n",
    "iiExpPersonsList = get_ii_agentIDs(BASE_DIR.parent.joinpath(\"runs\", simulation_run, \"ITERS\", (\"it.\"+str(first_iter)),(str(first_iter)+\".plans.xml.gz\")))\n",
    "\n",
    "folder_path = BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(first_iter)),(str(first_iter)+\".plans.xml.gz\"))\n",
    "with gzip.open(folder_path, 'rb') as f:\n",
    "    file_content = f.read()\n",
    "    plans_root = ET.fromstring(file_content)\n",
    "modechoice_summary = {\n",
    "    \"work\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"univ\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"school\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"shopping\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"othmaint\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"eatout\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"social\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"othdiscr\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "    \"escort\":\n",
    "        {\n",
    "            \"no_auto\": {\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_deficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "            \"auto_sufficient\":{\"car\":0,\"sr2\":0,\"sr3\":0,\"walk_transit\":0,\"bike_transit\":0,\"drive_transit\":0,\"ride_hail_transit\":0,\"walk\":0,\"bike\":0,\"ride_hail\":0,\"teleportation\":0,\"other\":0,\"\":0},\n",
    "        },\n",
    "}\n",
    "mode_list = []\n",
    "\n",
    "def get_auto_ownership_status(_root):\n",
    "    for child in _root.iter(\"attributes\"):\n",
    "        for schild in child.iter(\"attribute\"):\n",
    "            if \"autoWorkRatio\" in schild.get(\"name\"):\n",
    "                auto_ownship_status = schild.text\n",
    "    return auto_ownship_status\n",
    "\n",
    "for child in (plans_root.iter('person')):\n",
    "    if child.get(\"id\") in iiExpPersonsList:\n",
    "        # print(child.get(\"id\"))\n",
    "        for subchild in child.iter(\"plan\"):\n",
    "            if \"yes\" in subchild.get(\"selected\"):\n",
    "                auto_ownship_status=get_auto_ownership_status(child)\n",
    "                for s in subchild.iter():\n",
    "                    if s.tag == 'activity':\n",
    "                        for s3 in s.iter('attribute'):\n",
    "                            if \"primary_purpose\" in s3.get(\"name\"):\n",
    "                                purpose = s3.text\n",
    "                                # print(purpose)\n",
    "                    if s.tag == 'leg':\n",
    "                        mode = s.get('mode')\n",
    "                        if \"hov2\" in mode:\n",
    "                            mode_list.append((purpose, \"sr2\"))\n",
    "                            modechoice_summary[purpose][auto_ownship_status][\"sr2\"]+=1\n",
    "                        elif \"hov3\" in mode:\n",
    "                            mode_list.append((purpose, \"sr3\"))\n",
    "                            modechoice_summary[purpose][auto_ownship_status][\"sr3\"]+=1\n",
    "                        else:\n",
    "                            mode_list.append((purpose, mode))\n",
    "                            modechoice_summary[purpose][auto_ownship_status][mode]+=1\n",
    "                    # print(mode)\n",
    "\n",
    "# populate records for SF County using 0th iteration records\n",
    "for tour_purpose in modechoice_summary.keys():\n",
    "    if \"work\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_column =11\n",
    "                cell_row=\"L\"\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_column =11\n",
    "                cell_row=\"M\"\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "                cell_column =11\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"school\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "                cell_column =26\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "                cell_column =26\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "                cell_column =26\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"univ\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "                cell_column =41\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "                cell_column =41\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "                cell_column =41\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"shopping\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "                cell_column =56\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "                cell_column =56\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "                cell_column =56\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"othmaint\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "                cell_column =71\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "                cell_column =71\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "                cell_column =71\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"eatout\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "                cell_column =86\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "                cell_column =86\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "                cell_column =86\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"social\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "                cell_column =101\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "                cell_column =101\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "                cell_column =101\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"othdiscr\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "                cell_column =116\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "                cell_column =116\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "                cell_column =116\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "    elif \"escort\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "                cell_column =131\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "                cell_column =131\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "                cell_column =131\n",
    "                for mode, count in modechoice_summary[tour_purpose][auto_ownership].items():\n",
    "                    if \"car\" in mode:\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr2\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"sr3\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"drive_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail_transit\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"walk\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"bike\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    elif \"ride_hail\" in mode:\n",
    "                        cell_column+=1\n",
    "                        Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "                    else:\n",
    "                        continue"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "outputs": [],
   "source": [
    "# convert the dictionary count to a dataframe list\n",
    "dfZerothIterationStats=pd.DataFrame.from_dict({(i,j): modechoice_summary[i][j]\n",
    "                                               for i in modechoice_summary.keys()\n",
    "                                               for j in modechoice_summary[i].keys()},\n",
    "                                              orient='index')\n",
    "dfZerothIterationStats.reset_index(drop=False).to_csv(BASE_DIR.parent.joinpath(\"exported\",\"ZerothIterationModeChoice_Count.csv\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "outputs": [],
   "source": [
    "# save the data\n",
    "workbook.save(BASE_DIR.parent.joinpath(\"exported\", \"summary_report_v9.0_trial8.xlsx\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [],
   "source": [
    "# find the number of agents details are not in the events file\n",
    "# dflastevent = dfBayAreaBeamEvents.dfEvents\n",
    "# dfzeroevent = dfBayAreaZerothEvents.dfEvents\n",
    "#\n",
    "# dflastevent_clean = dflastevent.dropna(subset=['person'])\n",
    "# dfzerothevent_clean = dfzeroevent.dropna(subset=['person'])\n",
    "#\n",
    "# iiPersonDetailsNotinEventsFile_lastiter = []\n",
    "# iiPersonDetailsNotinEventsFile_zerothiter = []\n",
    "#\n",
    "# for person in tqdm(iiExpPersonsList):\n",
    "#     if dflastevent_clean.loc[dflastevent_clean[\"person\"].str.startswith(person,na=False)][\"person\"].count()==0:\n",
    "#         iiPersonDetailsNotinEventsFile_lastiter.append(person)\n",
    "#     if dfzerothevent_clean.loc[dfzerothevent_clean[\"person\"].str.startswith(person,na=False)][\"person\"].count()==0:\n",
    "#         iiPersonDetailsNotinEventsFile_zerothiter.append(person)\n",
    "#\n",
    "# pd.DataFrame(iiPersonDetailsNotinEventsFile_lastiter,columns=[\"agent_ids\"]).to_csv(BASE_DIR.parent.joinpath(\"exported\", \"iiPersonDetailsNotinEventsFile_lastiter.csv\"))\n",
    "# pd.DataFrame(iiPersonDetailsNotinEventsFile_zerothiter,columns=[\"agent_ids\"]).to_csv(BASE_DIR.parent.joinpath(\"exported\", \"iiPersonDetailsNotinEventsFile_zerothiter.csv\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}