1.5 - Summary_Sheet_5pct_usingevents.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import pathlib\n",
    "from pathlib import Path\n",
    "from tqdm import tqdm\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 datetime\n",
    "\n",
    "\n",
    "import os\n",
    "import re\n",
    "\n",
    "import numpy as np\n",
    "\n",
    "from collections import OrderedDict\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": 11,
   "outputs": [],
   "source": [
    "#Sample information\n",
    "\n",
    "sample_pct = 0.0088 # 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 and get the date"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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": 13,
   "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": 14,
   "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": 15,
   "outputs": [],
   "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))].copy()\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_ii_modeshare(self):\n",
    "        dfEvents = self.dfEvents[~self.dfEvents[\"person\"].str.contains(\"ix\",na=False) & ~self.dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) & ~self.dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) & (~self.dfEvents[\"person\"].isna())]\n",
    "        return dfEvents.loc[(dfEvents[\"type\"]==\"TripArrivalEvent\"),\"mode\"].value_counts(normalize=True)\n",
    "\n",
    "    def get_TT_modeshare(self):\n",
    "        dfEvents = self.dfEvents[~self.dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) & ~self.dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) & (~self.dfEvents[\"person\"].isna())]\n",
    "        return dfEvents.loc[(dfEvents[\"type\"]==\"TripArrivalEvent\"),\"mode\"].value_counts(normalize=True)\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",
    "        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",
    "        return PTBoardings\n",
    "\n",
    "    def get_agents_count(self):\n",
    "        agent_counts = {}\n",
    "        agent_counts[\"ix_agents\"]  = self.dfEvents.loc[self.dfEvents[\"person\"].str.startswith(\"ix\", na=False),\"person\"].nunique()\n",
    "        agent_counts[\"ii_agents\"]  = 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[\"ridehail_agent\"] = self.dfEvents.loc[self.dfEvents[\"person\"].str.startswith(\"rideHailAgent\", na=False),\"person\"].nunique()\n",
    "        agent_counts[\"total_agent\"] = sum(agent_counts.values())\n",
    "        return agent_counts\n",
    "\n",
    "    def get_TT_unlinked_trips(self):\n",
    "        dfEvents = self.dfEvents[~self.dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) & ~self.dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) & (~self.dfEvents[\"person\"].isna())]\n",
    "        return dfEvents.loc[(dfEvents[\"type\"]==\"TripArrivalEvent\"),\"mode\"].value_counts()\n",
    "\n",
    "    def get_ii_unlinked_trips(self):\n",
    "        dfEvents = self.dfEvents[~self.dfEvents[\"person\"].str.contains(\"ix\",na=False) & ~self.dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) & ~self.dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) & (~self.dfEvents[\"person\"].isna())]\n",
    "        return dfEvents.loc[(dfEvents[\"type\"]==\"TripArrivalEvent\"),\"mode\"].value_counts()\n",
    "\n",
    "    def get_TT_tourpurpose_stats(self):\n",
    "        dfEvents = self.dfEvents.copy()\n",
    "        dfTour = dfEvents.loc[(~dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) &\n",
    "                               ~dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) &\n",
    "                               (~dfEvents[\"person\"].isna())),:]\\\n",
    "                         .dropna(subset = [\"vehicleOwnership\"])\n",
    "        return dfTour\\\n",
    "            .groupby([\"tourPurpose\",\"vehicleOwnership\",\"mode\"]).size().reset_index().rename(columns={0:\"count\"})\\\n",
    "            .groupby(\"tourPurpose\")\\\n",
    "            .apply(lambda vehOwntype: dict(vehOwntype.groupby(\"vehicleOwnership\")\n",
    "            .apply(lambda mode_used: dict(zip(mode_used[\"mode\"],mode_used[\"count\"])))))\n",
    "\n",
    "    def get_ii_tourpurpose_stats(self):\n",
    "        dfEvents = self.dfEvents.copy()\n",
    "        dfTour = dfEvents.loc[(~dfEvents[\"person\"].str.contains(\"ix\",na=False) &\n",
    "                               ~dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) &\n",
    "                               ~dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) &\n",
    "                               (~dfEvents[\"person\"].isna())),:]\\\n",
    "                            .dropna(subset = [\"vehicleOwnership\"])\n",
    "        dfTour=dfTour[dfTour[\"type\"]==\"TripArrivalEvent\"]\n",
    "        tour_purpose_dict = dfTour.groupby([\"tourPurpose\", \"vehicleOwnership\", \"mode\"]).size().reset_index().rename(columns={0: \"count\"})\\\n",
    "                                           .groupby(\"tourPurpose\")\\\n",
    "                                           .apply(lambda vehOwntype: dict(vehOwntype.groupby(\"vehicleOwnership\")\n",
    "                                                                                                 .apply(lambda mode_used: dict(zip(mode_used[\"mode\"], mode_used[\"count\"]))))).copy()\n",
    "        for key,value in tour_purpose_dict.iteritems():\n",
    "            if isinstance(tour_purpose_dict.get(key),dict):\n",
    "                for auto_ownership,modes in tour_purpose_dict.get(key).items():\n",
    "                    mode_keys = [\"car\", \"walk_transit\", \"drive_transit\", \"bike_transit\", \"ride_hail_transit\", \"walk\", \"bike\", \"ride_hail_pooled\", \"ride_hail\", \"hov2_teleportation\", \"hov2\",\"hov3_teleportation\", \"hov3\"]\n",
    "                    if isinstance(modes,dict):\n",
    "                        for mode,count in modes.items():\n",
    "                            if mode in mode_keys:\n",
    "                                mode_keys.remove(mode)\n",
    "                        for mode in mode_keys:\n",
    "                            tour_purpose_dict[key][auto_ownership][mode]=0\n",
    "\n",
    "        return tour_purpose_dict\n",
    "\n",
    "    def get_ii_transfers(self):\n",
    "        dfEvents = self.dfEvents.copy()\n",
    "        dfEvents = dfEvents[~dfEvents[\"person\"].str.contains(\"ix\",na=False) & ~dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) & ~dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) & (~dfEvents[\"person\"].isna())].copy()\n",
    "        # dfEvents = dfEvents.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:].copy()\n",
    "\n",
    "        dfEvents[\"numberofTransfers\"]=dfEvents.loc[(dfEvents[\"type\"]==\"ModeChoice\") & (dfEvents[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[-22:].str.split(\")\",expand=True)[0].str.split(\",\",expand=True)[1]\n",
    "        dfEvents[\"trip_cost_utility\"]=dfEvents.loc[(dfEvents[\"type\"]==\"ModeChoice\") & (dfEvents[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[-34:-25].str.split(\",\",expand=True)[1]\n",
    "        dfEvents[\"walkTransitDistances\"] = dfEvents.loc[(dfEvents[\"type\"]==\"ModeChoice\") & (dfEvents[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[1:-37].str.split(\",\",expand=True)[1].str.split(\":\",expand=True)[0]\n",
    "        # df[\"noofTransfers\"]=df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[-22:].str.split(\")\",expand=True)[0].str.split(\",\",expand=True)[1]\n",
    "        # df[\"trip_cost_utility\"]=df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[-34:-25].str.split(\",\",expand=True)[1]\n",
    "        # df[\"walkTransitDistance\"] = df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[1:-37].str.split(\",\",expand=True)[1].str.split(\":\",expand=True)[0]\n",
    "        # df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:][[\"tracingData\",\"walkTransitDistance\",\"trip_cost_utility\",\"noofTransfers\"]]\n",
    "\n",
    "        # dfEvents[\"walkTransitDistances\"]= dfEvents[\"tracingData\"].str.split(\":\",expand=True)[0].str.split(\",\",expand=True)[1].str.split(\")\",expand=True)[0]\n",
    "        # dfEvents[\"trip_cost_utility\"]= dfEvents[\"tracingData\"].str.split(\":\",expand=True)[1].str.split(\",\",expand=True)[1].str.split(\")\",expand=True)[0]\n",
    "        # dfEvents[\"numberofTransfers\"]= dfEvents[\"tracingData\"].str.split(\":\",expand=True)[2].str.split(\",\",expand=True)[1].str.split(\")\",expand=True)[0]\n",
    "\n",
    "        dfEvents[\"walkTransitDistances\"] = pd.to_numeric(dfEvents[\"walkTransitDistances\"], errors='coerce').replace(np.nan,0,regex=True)\n",
    "        dfEvents[\"trip_cost_utility\"] = pd.to_numeric(dfEvents[\"trip_cost_utility\"], errors='coerce').replace(np.nan,0,regex=True)\n",
    "        dfEvents[\"numberofTransfers\"] = pd.to_numeric(dfEvents[\"numberofTransfers\"], errors='coerce').replace(np.nan,0,regex=True).round(0).astype(int)\n",
    "\n",
    "        dData = dfEvents.loc[(dfEvents[\"type\"]==\"ModeChoice\") & (dfEvents[\"mode\"]==\"walk_transit\"),:]\n",
    "\n",
    "        dData[\"walkTransitDistances_mil\"] = dData[\"walkTransitDistances\"]*0.000621371\n",
    "\n",
    "        def categorized_distance(cell_value):\n",
    "            if cell_value>=1:\n",
    "                walkTransitDistances_cat=\">=1mil\"\n",
    "            elif ( cell_value<1.0) & (cell_value>0.5):\n",
    "                walkTransitDistances_cat=\">=0.5mil & <1mil\"\n",
    "            elif (cell_value<0.50) & (cell_value>0.25):\n",
    "                walkTransitDistances_cat=\">=0.25mil & <0.5mil\"\n",
    "            elif (cell_value<0.250):\n",
    "                walkTransitDistances_cat=\"<0.25mil\"\n",
    "            return walkTransitDistances_cat\n",
    "\n",
    "        dData[\"walkTransitDistances_cat\"] = dData[\"walkTransitDistances_mil\"].apply(lambda x:categorized_distance(x))\n",
    "\n",
    "        def categorized_transfer(cell_value):\n",
    "            if cell_value==0:\n",
    "                categorized_transfer_cat=\"0\"\n",
    "            elif ( cell_value<=1.0) & (cell_value>0.0):\n",
    "                categorized_transfer_cat=\"1\"\n",
    "            elif (cell_value<=2) & (cell_value>1):\n",
    "                categorized_transfer_cat=\"2\"\n",
    "            elif (cell_value>=3):\n",
    "                categorized_transfer_cat=\"3+\"\n",
    "            return categorized_transfer_cat\n",
    "\n",
    "        dData[\"numberofTransfers_cat\"] = dData[\"numberofTransfers\"].apply(lambda x:categorized_transfer(x))\n",
    "\n",
    "        transfer_dict = (dData.groupby([\"tourPurpose\", \"numberofTransfers_cat\",\"walkTransitDistances_cat\"]).size().reset_index().rename(columns={0: \"count\"}).groupby(\"tourPurpose\").apply(lambda vehOwntype: dict(vehOwntype.groupby(\"numberofTransfers_cat\").apply(lambda mode_used: dict(zip(mode_used[\"walkTransitDistances_cat\"], mode_used[\"count\"]))))).copy())\n",
    "\n",
    "        for key,values in transfer_dict.iteritems():\n",
    "            # print(key)\n",
    "            cat = [\"0\",\"1\",\"2\",\"3+\"]\n",
    "            for transfer_cat,dist in transfer_dict.get(key).items():\n",
    "                # print(\"\\t\",transfer_cat)\n",
    "                cat.remove(transfer_cat)\n",
    "            for c in cat:\n",
    "                # print(c)\n",
    "                transfer_dict[key][c]={\"<0.25mil\":0,\">=0.25mil & <0.5mil\":0,\">=0.5mil & <1mil\":0,\">=1mil\":0 }\n",
    "\n",
    "        for key,value in transfer_dict.iteritems():\n",
    "            if isinstance(transfer_dict.get(key),dict):\n",
    "                for transfer,walk_distance in transfer_dict.get(key).items():\n",
    "                    walk_distance_keys = [\"<0.25mil\", \">=0.25mil & <0.5mil\",\">=0.5mil & <1mil\",\">=1mil\"]\n",
    "                    if isinstance(walk_distance,dict):\n",
    "                        for cat,count in walk_distance.items():\n",
    "                            if cat in walk_distance_keys:\n",
    "                                walk_distance_keys.remove(cat)\n",
    "                        for cat in walk_distance_keys:\n",
    "                            transfer_dict[key][transfer][cat]=0\n",
    "\n",
    "        return transfer_dict\n",
    "\n",
    "    def get_ii_transfers_firstmile_summary(self):\n",
    "        dfEvents = self.dfEvents.copy()\n",
    "        dfEvents = dfEvents[~dfEvents[\"person\"].str.contains(\"ix\",na=False) & ~dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) & ~dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) & (~dfEvents[\"person\"].isna())].copy()\n",
    "        # dfEvents = dfEvents.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:].copy()\n",
    "\n",
    "        dfEvents[\"numberofTransfers\"]=dfEvents.loc[(dfEvents[\"type\"]==\"ModeChoice\") & (dfEvents[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[-22:].str.split(\")\",expand=True)[0].str.split(\",\",expand=True)[1]\n",
    "        dfEvents[\"trip_cost_utility\"]=dfEvents.loc[(dfEvents[\"type\"]==\"ModeChoice\") & (dfEvents[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[-34:-25].str.split(\",\",expand=True)[1]\n",
    "        dfEvents[\"walkTransitDistances\"] = dfEvents.loc[(dfEvents[\"type\"]==\"ModeChoice\") & (dfEvents[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[1:-37].str.split(\",\",expand=True)[1].str.split(\":\",expand=True)[0]\n",
    "\n",
    "        dfEvents[\"walkTransitDistances\"] = pd.to_numeric(dfEvents[\"walkTransitDistances\"], errors='coerce').replace(np.nan,0,regex=True)\n",
    "        dfEvents[\"trip_cost_utility\"] = pd.to_numeric(dfEvents[\"trip_cost_utility\"], errors='coerce').replace(np.nan,0,regex=True)\n",
    "        dfEvents[\"numberofTransfers\"] = pd.to_numeric(dfEvents[\"numberofTransfers\"], errors='coerce').replace(np.nan,0,regex=True).round(0).astype(int)\n",
    "\n",
    "        dData = dfEvents.loc[(dfEvents[\"type\"]==\"ModeChoice\") & (dfEvents[\"mode\"]==\"walk_transit\"),:]\n",
    "        dData[\"walkTransitDistances_mil\"] = dData[\"walkTransitDistances\"]*0.000621371\n",
    "\n",
    "        def categorized_distance(cell_value):\n",
    "            if cell_value>=1:\n",
    "                walkTransitDistances_cat=\">=1mil\"\n",
    "            elif ( cell_value<1.0) & (cell_value>0.5):\n",
    "                walkTransitDistances_cat=\">=0.5mil & <1mil\"\n",
    "            elif (cell_value<0.50) & (cell_value>0.25):\n",
    "                walkTransitDistances_cat=\">=0.25mil & <0.5mil\"\n",
    "            elif (cell_value<0.250):\n",
    "                walkTransitDistances_cat=\"<0.25mil\"\n",
    "            return walkTransitDistances_cat\n",
    "\n",
    "        dData[\"walkTransitDistances_cat\"] = dData[\"walkTransitDistances_mil\"].apply(lambda x:categorized_distance(x))\n",
    "\n",
    "        def categorized_transfer(cell_value):\n",
    "            if cell_value==0:\n",
    "                categorized_transfer_cat=\"0\"\n",
    "            elif ( cell_value<=1.0) & (cell_value>0.0):\n",
    "                categorized_transfer_cat=\"1\"\n",
    "            elif (cell_value<=2) & (cell_value>1):\n",
    "                categorized_transfer_cat=\"2\"\n",
    "            elif (cell_value>=3):\n",
    "                categorized_transfer_cat=\"3+\"\n",
    "            return categorized_transfer_cat\n",
    "\n",
    "        dData[\"numberofTransfers_cat\"] = dData[\"numberofTransfers\"].apply(lambda x:categorized_transfer(x))\n",
    "        transfer_dict = (dData.groupby([\"walkTransitDistances_cat\",\"numberofTransfers_cat\"]).size().reset_index().rename(columns={0: \"count\"}).groupby(\"walkTransitDistances_cat\").apply(lambda mode_used: dict(zip(mode_used[\"numberofTransfers_cat\"], mode_used[\"count\"])))).copy()\n",
    "        for key,values in transfer_dict.iteritems():\n",
    "            cat = [\"0\",\"1\",\"2\",\"3+\"]\n",
    "            if isinstance(values,dict):\n",
    "                for k,v in transfer_dict.get(key).items():\n",
    "                    if k in cat:\n",
    "                        cat.remove(k)\n",
    "                for c in cat:\n",
    "                    transfer_dict[key][c]=0\n",
    "\n",
    "        return transfer_dict\n",
    "\n",
    "    def get_ii_auto_ratio_status(self):\n",
    "        dfEvents = self.dfEvents.copy()\n",
    "        dfTour = dfEvents.loc[(~dfEvents[\"person\"].str.contains(\"ix\",na=False) &\n",
    "                               ~dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) &\n",
    "                               ~dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) &\n",
    "                               (~dfEvents[\"person\"].isna())),:]\\\n",
    "                          .dropna(subset = [\"vehicleOwnership\"])\n",
    "        return dfTour[\"vehicleOwnership\"].value_counts(normalize=True)\n",
    "\n",
    "    def get_TT_auto_ratio_status(self):\n",
    "        dfEvents = self.dfEvents.copy()\n",
    "        dfTour = dfEvents.loc[(~dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) &\n",
    "                               ~dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) &\n",
    "                               (~dfEvents[\"person\"].isna())),:]\\\n",
    "                        .dropna(subset = [\"vehicleOwnership\"])\n",
    "        return dfTour[\"vehicleOwnership\"].value_counts(normalize=True)\n",
    "\n",
    "    def get_TT_activity_type(self):\n",
    "        dfEvents = self.dfEvents.copy()\n",
    "        dfActivity = dfEvents.loc[(~dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) &\n",
    "                               ~dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) &\n",
    "                               (~dfEvents[\"person\"].isna())),:]\\\n",
    "                         .dropna(subset = [\"tourPurpose\"])\n",
    "        return dfActivity[\"tourPurpose\"].value_counts(normalize=True)\n",
    "\n",
    "    def get_ii_activity_type(self):\n",
    "        dfEvents = self.dfEvents.copy()\n",
    "        dfActivity = dfEvents.loc[(~dfEvents[\"person\"].str.contains(\"ix\",na=False) &\n",
    "                                   ~dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) &\n",
    "                                   ~dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) &\n",
    "                                   (~dfEvents[\"person\"].isna())),:]\\\n",
    "                             .dropna(subset = [\"tourPurpose\"])\n",
    "        return dfActivity[\"tourPurpose\"].value_counts(normalize=True)\n",
    "\n",
    "dfBayAreaBeamEvents = detailed_events(BASE_DIR.parent.joinpath(\"runs\", simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".events.csv.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": 16,
   "outputs": [],
   "source": [
    "# dfLast = dfBayAreaBeamEvents.dfEvents\n",
    "# dfFirst = dfBayAreaZerothEvents.dfEvents\n",
    "#\n",
    "# dfLast.loc[(~dfLast[\"person\"].str.contains(\"ix\",na=False)) & (~dfLast[\"person\"].str.contains(\"rideHailAgent\",na=False))\n",
    "#            & (~dfLast[\"person\"].str.contains(\"TransitDriverAgent\",na=False)) & (~dfLast[\"person\"].isna())\n",
    "#            & (dfLast[\"type\"]==\"TripArrivalEvent\"),\"mode\"].value_counts().sum()\n",
    "#\n",
    "# dfFirst.loc[(~dfFirst[\"person\"].str.contains(\"ix\",na=False)) & (~dfFirst[\"person\"].str.contains(\"rideHailAgent\",na=False))\n",
    "#             & (~dfFirst[\"person\"].str.contains(\"TransitDriverAgent\",na=False)) & (~dfFirst[\"person\"].isna())\n",
    "#             & (dfFirst[\"type\"]==\"TripArrivalEvent\"),\"mode\"].value_counts().sum()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [],
   "source": [
    "# dData = dfBayAreaBeamEvents.get_ii_transfers_firstmile_summary()\n",
    "# transfer_dict = (dData.groupby([\"walkTransitDistances_cat\",\"numberofTransfers_cat\"]).size().reset_index().rename(columns={0: \"count\"}).groupby(\"walkTransitDistances_cat\").apply(lambda mode_used: dict(zip(mode_used[\"numberofTransfers_cat\"], mode_used[\"count\"])))).copy()\n",
    "# for key,values in transfer_dict.iteritems():\n",
    "#     # print(key)\n",
    "#     cat = [\"0\",\"1\",\"2\",\"3+\"]\n",
    "#     if isinstance(values,dict):\n",
    "#         for k,v in transfer_dict.get(key).items():\n",
    "#             print(k,\"-->\",v)\n",
    "#             if k in cat:\n",
    "#                 cat.remove(k)\n",
    "#         for c in cat:\n",
    "#             transfer_dict[key][c]=0\n",
    "# # Populate noofTransfers by tourpurpose\n",
    "# transfer_wrksheet = workbook[\"noTransfer_Summary\"]\n",
    "# for distance_cat,values in transfer_dict.iteritems():\n",
    "#     if isinstance((values,dict)):\n",
    "#         cell_row=11\n",
    "#         cell_col=\"D\"\n",
    "#         for k,v in values.items():\n"
   ],
   "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",
    "# read the excel template\n",
    "workbook = load_workbook(filename=BASE_DIR.parent.joinpath(\"template\", \"templatev10.0.xlsx\"))\n",
    "sheet = workbook[\"Summary\"]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "outputs": [],
   "source": [
    "# Summary worksheet\n",
    "# general information\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)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [],
   "source": [
    "# Model Run - Last Iteration\n",
    "# dump values for full Bay Area\n",
    "#change the heading of the simulation run\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+=2\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\",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(\"walk_transit\",0) +\n",
    "                                                         dfBayAreaBeamEvents.get_TT_modeshare().get(\"bike_transit\",0) +\n",
    "                                                         dfBayAreaBeamEvents.get_TT_modeshare().get(\"drive_transit\",0) +\n",
    "                                                         dfBayAreaBeamEvents.get_TT_modeshare().get(\"rideh_hail_transit\",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\",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\",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\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_TT_modeshare().get(\"ride_hail_pool\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_TT_modeshare().get(\"hov2\",0) + \\\n",
    "                                                       dfBayAreaBeamEvents.get_TT_modeshare().get(\"hov2_teleportation\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_TT_modeshare().get(\"hov3\",0) +\\\n",
    "                                                       dfBayAreaBeamEvents.get_TT_modeshare().get(\"hov3_teleportation\",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",
    "# Unlinked trip information\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  ((dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"car\",0))*scale_factor)\n",
    "# Transit\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"walk_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"drive_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"bike_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"ridehail_transit\",0) *scale_factor)\n",
    "# Pedestrian\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"walk\",0)*scale_factor)\n",
    "# Bike\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"bike\",0)*scale_factor)\n",
    "# Ridehail\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"ride_hail\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"ride_hail_pooled\",0)*scale_factor)\n",
    "# HOV2 + HOV2_teleport\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"hov2\",0)+\n",
    "                                                         dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"hov2_teleportation\",0))*scale_factor\n",
    "# HOV3 + HOV3_teleport\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"hov3\",0)+\n",
    "                                                         dfBayAreaBeamEvents.get_TT_unlinked_trips().get(\"hov3_teleportation\",0))*scale_factor\n",
    "\n",
    "# \"Total Trip Information\"\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (sum(dfBayAreaBeamEvents.get_TT_unlinked_trips())*scale_factor)\n",
    "\n",
    "# Auto ownership status\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_auto_ratio_status().get(\"no_auto\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_auto_ratio_status().get(\"auto_sufficient\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_auto_ratio_status().get(\"auto_deficient\",0))\n",
    "\n",
    "# Activity Type\n",
    "cell_row+=2\n",
    "# sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"home\",0))\n",
    "# cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"othmaint\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"escort\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"shopping\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"social\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"work\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"eatout\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"othdiscr\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"univ\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"school\",0))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [],
   "source": [
    "# SFChamp Run - First Iteration\n",
    "# dump values for full Bay Area\n",
    "#change the heading of the simulation run\n",
    "#re-locate the fixed row and column = 11\n",
    "cell_row=11\n",
    "cell_column=9\n",
    "\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",
    "# Mode Share data\n",
    "cell_row+=5\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 = dfBayAreaZerothEvents.get_TT_modeshare().get(\"car\",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 = (dfBayAreaZerothEvents.get_TT_modeshare().get(\"walk_transit\",0) +\n",
    "                                                         dfBayAreaZerothEvents.get_TT_modeshare().get(\"bike_transit\",0) +\n",
    "                                                         dfBayAreaZerothEvents.get_TT_modeshare().get(\"drive_transit\",0) +\n",
    "                                                         dfBayAreaZerothEvents.get_TT_modeshare().get(\"rideh_hail_transit\",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= dfBayAreaZerothEvents.get_TT_modeshare().get(\"walk\",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= dfBayAreaZerothEvents.get_TT_modeshare().get(\"bike\",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= dfBayAreaZerothEvents.get_TT_modeshare().get(\"ride_hail\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaZerothEvents.get_TT_modeshare().get(\"ride_hail_pool\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaZerothEvents.get_TT_modeshare().get(\"hov2\",0) +\\\n",
    "                                                       dfBayAreaZerothEvents.get_TT_modeshare().get(\"hov2_teleportation\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaZerothEvents.get_TT_modeshare().get(\"hov3\",0) +\\\n",
    "                                                       dfBayAreaZerothEvents.get_TT_modeshare().get(\"hov3_teleportation\",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\"]+\n",
    "                                                        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",
    "# Unlinked trip information\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  ((dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"car\",0))*scale_factor)\n",
    "# Transit\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"walk_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"drive_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"bike_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"ridehail_transit\",0) *scale_factor)\n",
    "# Pedestrian\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"walk\",0)*scale_factor)\n",
    "# Bike\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"bike\",0)*scale_factor)\n",
    "# Ridehail\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"ride_hail\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"ride_hail_pooled\",0)*scale_factor)\n",
    "# HOV2 + HOV2_teleport\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"hov2\",0)+\n",
    "                                                         dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"hov2_teleportation\",0))*scale_factor\n",
    "# HOV3 + HOV3_teleport\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"hov3\",0)+\n",
    "                                                         dfBayAreaZerothEvents.get_TT_unlinked_trips().get(\"hov3_teleportation\",0))*scale_factor\n",
    "\n",
    "# \"Total Trip Information\"\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (sum(dfBayAreaZerothEvents.get_TT_unlinked_trips())*scale_factor)\n",
    "\n",
    "# Auto ownership status\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_auto_ratio_status().get(\"no_auto\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_auto_ratio_status().get(\"auto_sufficient\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_auto_ratio_status().get(\"auto_deficient\",0))\n",
    "\n",
    "# Activity Type\n",
    "cell_row+=2\n",
    "# sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_activity_type().get(\"home\",0))\n",
    "# cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_activity_type().get(\"othmaint\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_activity_type().get(\"escort\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_activity_type().get(\"shopping\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_activity_type().get(\"social\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_activity_type().get(\"work\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_activity_type().get(\"eatout\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_activity_type().get(\"othdiscr\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_activity_type().get(\"univ\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_TT_activity_type().get(\"school\",0))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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": [
    "# Model Run - Last Iteration\n",
    "# dump values for SF County\n",
    "#change the heading of the simulation run\n",
    "#re-locate the fixed row and column = 11\n",
    "cell_row=11\n",
    "cell_column=16\n",
    "\n",
    "temp = \"Beam_Run_County_\"+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 = 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+=2\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_ii_modeshare().get(\"car\",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_ii_modeshare().get(\"walk_transit\",0) +\n",
    "                                                         dfBayAreaBeamEvents.get_ii_modeshare().get(\"bike_transit\",0) +\n",
    "                                                         dfBayAreaBeamEvents.get_ii_modeshare().get(\"drive_transit\",0) +\n",
    "                                                         dfBayAreaBeamEvents.get_ii_modeshare().get(\"rideh_hail_transit\",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_ii_modeshare().get(\"walk\",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_ii_modeshare().get(\"bike\",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_ii_modeshare().get(\"ride_hail\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_ii_modeshare().get(\"ride_hail_pool\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_ii_modeshare().get(\"hov2\",0) +\\\n",
    "                                                       dfBayAreaBeamEvents.get_ii_modeshare().get(\"hov2_teleportation\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaBeamEvents.get_ii_modeshare().get(\"hov3\",0) +\\\n",
    "                                                       dfBayAreaBeamEvents.get_ii_modeshare().get(\"hov3_teleportation\",0)\n",
    "\n",
    "# Boarding data\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=(dfBayAreaBeamEvents.get_ii_PTBoardings().get(\"bus_boarding\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaBeamEvents.get_ii_PTBoardings().get(\"tram_boarding\",0)+\n",
    "                                                        dfBayAreaBeamEvents.get_ii_PTBoardings().get(\"cable_boarding\",0))*scale_factor\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaBeamEvents.get_ii_PTBoardings().get(\"rail_boarding\",0)*scale_factor)\n",
    "\n",
    "# Agent stats\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.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaBeamEvents.get_agents_count().get(\"ridehail_agent\",0)*scale_factor)\n",
    "\n",
    "# Unlinked trip information\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  ((dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"car\",0))*scale_factor)\n",
    "# Transit\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"walk_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"drive_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"bike_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"ridehail_transit\",0) *scale_factor)\n",
    "# Pedestrian\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"walk\",0)*scale_factor)\n",
    "# Bike\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"bike\",0)*scale_factor)\n",
    "# Ridehail\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"ride_hail\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"ride_hail_pooled\",0)*scale_factor)\n",
    "# HOV2 + HOV2_teleport\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"hov2\",0)+\n",
    "                                                         dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"hov2_teleportation\",0))*scale_factor\n",
    "# HOV3 + HOV3_teleport\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"hov3\",0)+\n",
    "                                                         dfBayAreaBeamEvents.get_ii_unlinked_trips().get(\"hov3_teleportation\",0))*scale_factor\n",
    "\n",
    "# \"Total Trip Information\"\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (sum(dfBayAreaBeamEvents.get_ii_unlinked_trips())*scale_factor)\n",
    "\n",
    "# Auto ownership status\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_auto_ratio_status().get(\"no_auto\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_auto_ratio_status().get(\"auto_sufficient\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_auto_ratio_status().get(\"auto_deficient\",0))\n",
    "\n",
    "# Activity Type\n",
    "cell_row+=2\n",
    "# sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"home\",0))\n",
    "# cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_activity_type().get(\"othmaint\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_activity_type().get(\"escort\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_activity_type().get(\"shopping\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_activity_type().get(\"social\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_activity_type().get(\"work\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_activity_type().get(\"eatout\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_activity_type().get(\"othdiscr\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_activity_type().get(\"univ\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_ii_activity_type().get(\"school\",0))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "outputs": [],
   "source": [
    "# SF Champ - First Iteration\n",
    "# dump values for SF County\n",
    "#change the heading of the simulation run\n",
    "#re-locate the fixed row and column = 11\n",
    "cell_row=11\n",
    "cell_column=19\n",
    "\n",
    "temp = \"SF_Champ_County_\"+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 = 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+=5\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 = dfBayAreaZerothEvents.get_ii_modeshare().get(\"car\",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 = (dfBayAreaZerothEvents.get_ii_modeshare().get(\"walk_transit\",0) +\n",
    "                                                         dfBayAreaZerothEvents.get_ii_modeshare().get(\"bike_transit\",0) +\n",
    "                                                         dfBayAreaZerothEvents.get_ii_modeshare().get(\"drive_transit\",0) +\n",
    "                                                         dfBayAreaZerothEvents.get_ii_modeshare().get(\"rideh_hail_transit\",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= dfBayAreaZerothEvents.get_ii_modeshare().get(\"walk\",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= dfBayAreaZerothEvents.get_ii_modeshare().get(\"bike\",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= dfBayAreaZerothEvents.get_ii_modeshare().get(\"ride_hail\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaZerothEvents.get_ii_modeshare().get(\"ride_hail_pool\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaZerothEvents.get_ii_modeshare().get(\"hov2\",0) +\\\n",
    "                                                       dfBayAreaZerothEvents.get_ii_modeshare().get(\"hov2_teleportation\",0)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= dfBayAreaZerothEvents.get_ii_modeshare().get(\"hov3\",0) +\\\n",
    "                                                       dfBayAreaZerothEvents.get_ii_modeshare().get(\"hov3_teleportation\",0)\n",
    "\n",
    "# Boarding data\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=(dfBayAreaZerothEvents.get_ii_PTBoardings().get(\"bus_boarding\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothEvents.get_ii_PTBoardings().get(\"tram_boarding\",0)+\n",
    "                                                        dfBayAreaZerothEvents.get_ii_PTBoardings().get(\"cable_boarding\",0))*scale_factor\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value= (dfBayAreaZerothEvents.get_ii_PTBoardings().get(\"rail_boarding\",0)*scale_factor)\n",
    "\n",
    "# Agent stats\n",
    "cell_row+=3\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",
    "# Unlinked trip information\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  ((dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"car\",0))*scale_factor)\n",
    "# Transit\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"walk_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"drive_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"bike_transit\",0) *scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"ridehail_transit\",0) *scale_factor)\n",
    "# Pedestrian\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"walk\",0)*scale_factor)\n",
    "# Bike\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"bike\",0)*scale_factor)\n",
    "# Ridehail\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"ride_hail\",0)*scale_factor)\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"ride_hail_pooled\",0)*scale_factor)\n",
    "# HOV2 + HOV2_teleport\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"hov2\",0)+\n",
    "                                                         dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"hov2_teleportation\",0))*scale_factor\n",
    "# HOV3 + HOV3_teleport\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"hov3\",0)+\n",
    "                                                         dfBayAreaZerothEvents.get_ii_unlinked_trips().get(\"hov3_teleportation\",0))*scale_factor\n",
    "\n",
    "# \"Total Trip Information\"\n",
    "cell_row+=3\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (sum(dfBayAreaZerothEvents.get_ii_unlinked_trips())*scale_factor)\n",
    "\n",
    "# Auto ownership status\n",
    "cell_row+=2\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_auto_ratio_status().get(\"no_auto\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_auto_ratio_status().get(\"auto_sufficient\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_auto_ratio_status().get(\"auto_deficient\",0))\n",
    "\n",
    "# Activity Type\n",
    "cell_row+=2\n",
    "# sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaBeamEvents.get_TT_activity_type().get(\"home\",0))\n",
    "# cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_activity_type().get(\"othmaint\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_activity_type().get(\"escort\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_activity_type().get(\"shopping\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_activity_type().get(\"social\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_activity_type().get(\"work\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_activity_type().get(\"eatout\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_activity_type().get(\"othdiscr\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_activity_type().get(\"univ\",0))\n",
    "cell_row+=1\n",
    "sheet.cell(row=(cell_row),column=(cell_column)).value=  (dfBayAreaZerothEvents.get_ii_activity_type().get(\"school\",0))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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\"]\n",
    "\n",
    "def nested_dict_pairs_populator(dict_obj,row_no,col_no):\n",
    "    # Mode_Choice_wrksheet = sorted(dict_obj.items(),key=lambda kv:kv[1])\n",
    "    modechoice_summary = OrderedDict(sorted(dict_obj.items())).copy()\n",
    "    cell_row=row_no\n",
    "    cell_column=col_no\n",
    "    for mode, count in modechoice_summary.items():\n",
    "        if \"bike\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"bike_transit\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"car\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"drive_transit\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"hov2\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"hov2_teleportation\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"hov3\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"hov3_teleportation\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"ride_hail\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"ride_hail_pooled\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"ride_hail_transit\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"walk\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \"walk_transit\" in mode:\n",
    "            Mode_Choice_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "            cell_column+=1\n",
    "        else:\n",
    "            continue\n",
    "modechoice_summary = dfBayAreaBeamEvents.get_ii_tourpurpose_stats()\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",
    "            cell_column =11\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"school\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =29\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"univ\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =47\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"shopping\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =65\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"othmaint\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =83\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"eatout\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =101\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"social\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =119\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"othdiscr\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =137\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"escort\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =155\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"D\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"E\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"F\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "\n",
    "modechoice_summary = dfBayAreaZerothEvents.get_ii_tourpurpose_stats()\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",
    "            cell_column =11\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"school\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =29\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"univ\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =47\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"shopping\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =65\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"othmaint\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =83\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"eatout\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =101\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"social\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =119\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"othdiscr\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =137\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)\n",
    "    elif \"escort\" in tour_purpose:\n",
    "        for auto_ownership in modechoice_summary[tour_purpose].keys():\n",
    "            cell_column =155\n",
    "            if \"no_auto\" in auto_ownership:\n",
    "                cell_row=\"L\"\n",
    "            elif \"auto_deficient\" in auto_ownership:\n",
    "                cell_row=\"M\"\n",
    "            elif \"auto_sufficient\" in auto_ownership:\n",
    "                cell_row=\"N\"\n",
    "            nested_dict_pairs_populator(modechoice_summary[tour_purpose][auto_ownership],cell_row,cell_column)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "outputs": [],
   "source": [
    "# Populate noofTransfers by tourpurpose\n",
    "transfer_wrksheet = workbook[\"noofTransfers_tourpurpose\"]\n",
    "\n",
    "def nested_dict_pairs_populator(dict_obj,row_no,col_no):\n",
    "    # Mode_Choice_wrksheet = sorted(dict_obj.items(),key=lambda kv:kv[1])\n",
    "    transfer_summary = OrderedDict(sorted(dict_obj.items())).copy()\n",
    "    cell_row=row_no\n",
    "    cell_column=col_no\n",
    "    for mode, count in transfer_summary.items():\n",
    "        if \"<0.25mil\" in mode:\n",
    "            transfer_wrksheet[\"{}{}\".format(cell_row,cell_column)].value=int(count)*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \">=0.25mil & <0.5mil\" in mode:\n",
    "            transfer_wrksheet[\"{}{}\".format(cell_row,cell_column)].value=int(count)*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \">=0.5mil & <1mil\" in mode:\n",
    "            transfer_wrksheet[\"{}{}\".format(cell_row,cell_column)].value=int(count)*scale_factor\n",
    "            cell_column+=1\n",
    "        elif \">=1mil\" in mode:\n",
    "            transfer_wrksheet[\"{}{}\".format(cell_row,cell_column)].value=int(count)*scale_factor\n",
    "            cell_column+=1\n",
    "        else:\n",
    "            continue\n",
    "# Last iteration - Event\n",
    "transfer_summary = dfBayAreaBeamEvents.get_ii_transfers()\n",
    "for tour_purpose in transfer_summary.keys():\n",
    "    if \"work\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =11\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"D\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"E\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"F\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"G\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"school\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =20\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"D\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"E\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"F\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"G\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"univ\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =29\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"D\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"E\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"F\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"G\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"shopping\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =38\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"D\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"E\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"F\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"G\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"othmaint\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =47\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"D\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"E\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"F\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"G\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"eatout\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =56\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"D\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"E\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"F\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"G\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"social\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =65\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"D\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"E\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"F\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"G\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"othdiscr\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =74\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"D\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"E\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"F\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"G\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"escort\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =83\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"D\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"E\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"F\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"G\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "\n",
    "# First iteration - Event\n",
    "transfer_summary = dfBayAreaZerothEvents.get_ii_transfers()\n",
    "for tour_purpose in transfer_summary.keys():\n",
    "    if \"work\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =11\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"N\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"O\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"P\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"Q\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"school\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =20\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"N\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"O\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"P\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"Q\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"univ\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =29\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"N\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"O\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"P\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"Q\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"shopping\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =38\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"N\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"O\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"P\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"Q\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"othmaint\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =47\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"N\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"O\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"P\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"Q\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"eatout\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =56\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"N\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"O\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"P\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"Q\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"social\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =65\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"N\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"O\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"P\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"Q\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"othdiscr\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =74\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"N\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"O\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"P\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"Q\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)\n",
    "    elif \"escort\" in tour_purpose:\n",
    "        for transfr in transfer_summary.get(tour_purpose).keys():\n",
    "            cell_column =83\n",
    "            if \"0\" in transfr:\n",
    "                cell_row=\"N\"\n",
    "            elif \"1\" in transfr:\n",
    "                cell_row=\"O\"\n",
    "            elif \"2\" in transfr:\n",
    "                cell_row=\"P\"\n",
    "            elif \"3+\" in transfr:\n",
    "                cell_row=\"Q\"\n",
    "            nested_dict_pairs_populator(transfer_summary[tour_purpose][transfr],cell_row,cell_column)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "outputs": [],
   "source": [
    "# Populate noofTransfers summary\n",
    "transfer_dict = dfBayAreaBeamEvents.get_ii_transfers_firstmile_summary()\n",
    "transfer_wrksheet = workbook[\"noTransfer_Summary\"]\n",
    "for distance_cat,values in transfer_dict.iteritems():\n",
    "    if isinstance(values,dict):\n",
    "        if distance_cat==\"<0.25mil\":\n",
    "            cell_row=11\n",
    "            cell_col=4\n",
    "        elif distance_cat==\">=0.25mil & <0.5mil\":\n",
    "            cell_row=12\n",
    "            cell_col=4\n",
    "        elif distance_cat==\">=0.5mil & <1mil\":\n",
    "            cell_row=13\n",
    "            cell_col=4\n",
    "        elif distance_cat==\">=1mil\":\n",
    "            cell_row=14\n",
    "            cell_col=4\n",
    "        for k,v in transfer_dict.get(distance_cat).items():\n",
    "            transfer_wrksheet.cell(row=(cell_row),column=(cell_col)).value=(v)*scale_factor\n",
    "            cell_col+=1\n",
    "\n",
    "transfer_dict = dfBayAreaZerothEvents.get_ii_transfers_firstmile_summary()\n",
    "for distance_cat,values in transfer_dict.iteritems():\n",
    "    if isinstance(values,dict):\n",
    "        if distance_cat==\"<0.25mil\":\n",
    "            cell_row=11\n",
    "            cell_col=14\n",
    "        elif distance_cat==\">=0.25mil & <0.5mil\":\n",
    "            cell_row=12\n",
    "            cell_col=14\n",
    "        elif distance_cat==\">=0.5mil & <1mil\":\n",
    "            cell_row=13\n",
    "            cell_col=14\n",
    "        elif distance_cat==\">=1mil\":\n",
    "            cell_row=14\n",
    "            cell_col=14\n",
    "        for k,v in transfer_dict.get(distance_cat).items():\n",
    "            transfer_wrksheet.cell(row=(cell_row),column=(cell_col)).value=(v)*scale_factor\n",
    "            cell_col+=1"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "outputs": [],
   "source": [
    "# save the data\n",
    "workbook.save(BASE_DIR.parent.joinpath(\"exported\", \"summary_report_v10.0.xlsx\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# save the data\n",
    "# workbook.save(BASE_DIR.parent.joinpath(\"exported\", \"summary_report_v10.0_trial.xlsx\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# def nested_dict_pairs_populator(dict_obj,row_no,col_no):\n",
    "#     # Mode_Choice_wrksheet = sorted(dict_obj.items(),key=lambda kv:kv[1])\n",
    "#     transfer_summary = OrderedDict(sorted(dict_obj.items())).copy()\n",
    "#     cell_row=row_no\n",
    "#     cell_column=col_no\n",
    "#     for mode, count in transfer_summary.items():\n",
    "#         if \"<0.25mil\" in mode:\n",
    "#             transfer_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "#         elif \">=0.25mil & <0.5mil\" in mode:\n",
    "#             cell_column+=1\n",
    "#             transfer_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "#         elif \">=0.5mil & <1mil\" in mode:\n",
    "#             cell_column+=1\n",
    "#             transfer_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "#         elif \">=1mil\" in mode:\n",
    "#             cell_column+=1\n",
    "#             transfer_wrksheet[\"{}{}\".format(cell_row,cell_column)]=count*scale_factor\n",
    "#         else:\n",
    "#             continue\n",
    "# Populate noofTransfers by tourpurpose\n",
    "# transfer_wrksheet = workbook[\"noTransfer_Summary\"]\n",
    "# #Last iteration - Event\n",
    "# transfer_summary = dfBayAreaBeamEvents.get_ii_transfers_firstmile_summary()\n",
    "# cell_column=11\n",
    "# cell_row=\"D\"\n",
    "# nested_dict_pairs_populator(transfer_summary,cell_row,cell_column)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# df = dfBayAreaBeamEvents.dfEvents\n",
    "# df = df[~df[\"person\"].str.contains(\"ix\",na=False) & ~df[\"person\"].str.contains(\"rideHailAgent\",na=False) & ~df[\"person\"].str.contains(\"TransitDriverAgent\",na=False) & (~df[\"person\"].isna())].copy()\n",
    "# df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:]\n",
    "# df = df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:]\n",
    "# df[\"walkTransitDistances\"]= df[\"tracingData\"].str.rsplit(\":\",3)\n",
    "# df[\"trip_cost_utility\"]= df[\"tracingData\"].str.rsplit(\":\",2)[1]\n",
    "# df[\"numberofTransfers\"]= df[\"tracingData\"].str[-22:].str.split(\")\",expand=True)[0]\n",
    "\n",
    "# df[\"walkTransitDistances\"]= df[\"tracingData\"].str.rsplit(\":\",expand=True)[3]\n",
    "# df[\"trip_cost_utility\"]= df[\"tracingData\"].str.rsplit(\":\",expand=True)[1]\n",
    "# df[\"numberofTransfers\"]= df[\"tracingData\"].str.rsplit(\":\",expand=True)[0]\n",
    "\n",
    "# df[\"walkTransitDistances\"]= df[\"tracingData\"].str.split(\":\",expand=True)[0].str.split(\",\",expand=True)[1].str.split(\")\",expand=True)[0]\n",
    "# df[\"trip_cost_utility\"]= df[\"tracingData\"].str.split(\":\",expand=True)[1].str.split(\",\",expand=True)[1].str.split(\")\",expand=True)[0]\n",
    "# df[\"numberofTransfers\"]= df[\"tracingData\"].str.split(\":\",expand=True)[2].str.split(\",\",expand=True)[1].str.split(\")\",expand=True)[0]\n",
    "#\n",
    "# df[\"walkTransitDistances\"] = pd.to_numeric(df[\"walkTransitDistances\"], errors='coerce').replace(np.nan,0,regex=True)\n",
    "# df[\"trip_cost_utility\"] = pd.to_numeric(df[\"trip_cost_utility\"], errors='coerce').replace(np.nan,0,regex=True)\n",
    "# df[\"numberofTransfers\"] = pd.to_numeric(df[\"numberofTransfers\"], errors='coerce').replace(np.nan,0,regex=True).round(0).astype(int)\n",
    "\n",
    "# dData = df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:][[\"tracingData\",\"walkTransitDistances\",\"trip_cost_utility\",\"numberofTransfers\"]]\n",
    "# df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:][[\"tracingData\",\"walkTransitDistances\",\"trip_cost_utility\",\"numberofTransfers\"]]\n",
    "\n",
    "# df[\"numberofTransfers\"]=df[\"tracingData\"].str[-22:].str.split(\")\",expand=True)[0]\n",
    "# df[\"travel_cost\"]= df[\"tracingData\"].str[-34:-25]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# df[\"noofTransfers\"]=df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[-22:].str.split(\")\",expand=True)[0].str.split(\",\",expand=True)[1]\n",
    "# df[\"travelCosts\"]=df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[-34:-25].str.split(\",\",expand=True)[1]\n",
    "# df[\"walkTransitDistance\"] = df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:][\"tracingData\"].str[1:-37].str.split(\",\",expand=True)[1].str.split(\":\",expand=True)[0]\n",
    "# df.loc[(df[\"type\"]==\"ModeChoice\") & (df[\"mode\"]==\"walk_transit\"),:][[\"tracingData\",\"walkTransitDistance\",\"travelCosts\",\"noofTransfers\"]]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# dfLast = dfBayAreaBeamEvents.dfEvents\n",
    "# dfFirst = dfBayAreaZerothEvents.dfEvents"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# dfLast.loc[(~dfLast[\"person\"].str.contains(\"ix\",na=False)) & (~dfLast[\"person\"].str.contains(\"rideHailAgent\",na=False))\n",
    "#            & (~dfLast[\"person\"].str.contains(\"TransitDriverAgent\",na=False)) & (~dfLast[\"person\"].isna())\n",
    "#            & (dfLast[\"type\"]==\"TripArrivalEvent\"),\"mode\"].value_counts().sum()*20"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# dfFirst.loc[(~dfFirst[\"person\"].str.contains(\"ix\",na=False)) & (~dfFirst[\"person\"].str.contains(\"rideHailAgent\",na=False))\n",
    "#             & (~dfFirst[\"person\"].str.contains(\"TransitDriverAgent\",na=False)) & (~dfFirst[\"person\"].isna())\n",
    "#             & (dfFirst[\"type\"]==\"TripArrivalEvent\"),\"mode\"].value_counts().sum()*20"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# df2 = dfLast.loc[(~dfLast[\"person\"].str.contains(\"ix\",na=False)) & (~dfLast[\"person\"].str.contains(\"rideHailAgent\",na=False))\n",
    "#            & (~dfLast[\"person\"].str.contains(\"TransitDriverAgent\",na=False)) & (~dfLast[\"person\"].isna())\n",
    "#            & (dfLast[\"type\"]==\"TripArrivalEvent\"),]\n",
    "#\n",
    "# df1 = dfFirst.loc[(~dfFirst[\"person\"].str.contains(\"ix\",na=False)) & (~dfFirst[\"person\"].str.contains(\"rideHailAgent\",na=False))\n",
    "#             & (~dfFirst[\"person\"].str.contains(\"TransitDriverAgent\",na=False)) & (~dfFirst[\"person\"].isna())\n",
    "#             & (dfFirst[\"type\"]==\"TripArrivalEvent\"),]\n",
    "#\n",
    "# df3 = pd.concat([df1,df2]).drop_duplicates(keep=False)\n",
    "#\n",
    "# df4 = pd.concat([df2,df1]).drop_duplicates(keep=False)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# df2.to_csv(BASE_DIR.parent.joinpath(\"exported\", \"Last_iteration.10_trial.csv\"))\n",
    "# df1.to_csv(BASE_DIR.parent.joinpath(\"exported\", \"First_iteration.10_trial.csv\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# tour_purpose_dict = dfTour.groupby([\"tourPurpose\", \"vehicleOwnership\", \"mode\"]).size().reset_index().rename(columns={0: \"count\"})\\\n",
    "#                           .groupby(\"tourPurpose\")\\\n",
    "#                           .apply(lambda vehOwntype: dict(vehOwntype.groupby(\"vehicleOwnership\")\n",
    "#                                                          .apply(lambda mode_used: dict(zip(mode_used[\"mode\"], mode_used[\"count\"]))))).copy()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# dfEvents = self.dfEvents.copy()\n",
    "# dfTour = dfEvents.loc[(~dfEvents[\"person\"].str.contains(\"ix\",na=False) &\n",
    "#                        ~dfEvents[\"person\"].str.contains(\"rideHailAgent\",na=False) &\n",
    "#                        ~dfEvents[\"person\"].str.contains(\"TransitDriverAgent\",na=False) &\n",
    "#                        (~dfEvents[\"person\"].isna())),:]\n",
    "#     .dropna(subset = [\"vehicleOwnership\"])\n",
    "# dfTour=dfTour[dfTour[\"type\"]==\"TripArrivalEvent\"]\n",
    "# tour_purpose_dict = dfTour.groupby([\"tourPurpose\", \"vehicleOwnership\", \"mode\"]).size().reset_index().rename(columns={0: \"count\"})\n",
    "#     .groupby(\"tourPurpose\")\n",
    "#     .apply(lambda vehOwntype: dict(vehOwntype.groupby(\"vehicleOwnership\")\n",
    "#                                    .apply(lambda mode_used: dict(zip(mode_used[\"mode\"], mode_used[\"count\"]))))).copy()\n",
    "# for key,value in tour_purpose_dict.iteritems():\n",
    "#     if isinstance(tour_purpose_dict.get(key),dict):\n",
    "#         for auto_ownership,modes in tour_purpose_dict.get(key).items():\n",
    "#             mode_keys = [\"car\", \"walk_transit\", \"drive_transit\", \"bike_transit\", \"ride_hail_transit\", \"walk\", \"bike\", \"ride_hail_pooled\", \"ride_hail\", \"hov2_teleportation\", \"hov2\",\"hov3_teleportation\", \"hov3\"]\n",
    "#             if isinstance(modes,dict):\n",
    "#                 for mode,count in modes.items():\n",
    "#                     if mode in mode_keys:\n",
    "#                         mode_keys.remove(mode)\n",
    "#                 for mode in mode_keys:\n",
    "#                     tour_purpose_dict[key][auto_ownership][mode]=0"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# (dfLast.loc[(~dfLast[\"person\"].str.contains(\"ix\",na=False)) & (~dfLast[\"person\"].str.contains(\"rideHailAgent\",na=False))\n",
    "#             & (~dfLast[\"person\"].str.contains(\"TransitDriverAgent\",na=False)) & (~dfLast[\"person\"].isna())\n",
    "#             & (dfLast[\"type\"]==\"TripArrivalEvent\"),].groupby([\"tourPurpose\", \"vehicleOwnership\", \"mode\"]).size())"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# df2.groupby([\"tourPurpose\", \"vehicleOwnership\", \"mode\"]).size().reset_index().rename(columns={0: \"count\"})\n",
    "# (dfLast.loc[(~dfLast[\"person\"].str.contains(\"ix\",na=False)) & (~dfLast[\"person\"].str.contains(\"rideHailAgent\",na=False))\n",
    "#            & (~dfLast[\"person\"].str.contains(\"TransitDriverAgent\",na=False)) & (~dfLast[\"person\"].isna())\n",
    "#            & (dfLast[\"type\"]==\"TripArrivalEvent\"),].groupby([\"tourPurpose\", \"vehicleOwnership\", \"mode\"]).size()*20).to_csv(BASE_DIR.parent.joinpath(\"exported\", \"Last_iteration_tour.10_trial.csv\"))\n",
    "# df2.groupby([\"tourPurpose\", \"vehicleOwnership\", \"mode\"]).size()*20"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# (dfFirst.loc[(~dfFirst[\"person\"].str.contains(\"ix\",na=False)) & (~dfFirst[\"person\"].str.contains(\"rideHailAgent\",na=False))\n",
    "#             & (~dfFirst[\"person\"].str.contains(\"TransitDriverAgent\",na=False)) & (~dfFirst[\"person\"].isna())\n",
    "#             & (dfFirst[\"type\"]==\"TripArrivalEvent\"),].groupby([\"tourPurpose\", \"vehicleOwnership\", \"mode\"]).size()*20).to_csv(BASE_DIR.parent.joinpath(\"exported\", \"First_iteration_tour.10_trial.csv\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# pd.DataFrame(dfBayAreaZerothEvents.get_ii_tourpurpose_stats()).to_csv(BASE_DIR.parent.joinpath(\"exported\", \"First_iteration_tour_aftr.10_trial.csv\"))\n",
    "# pd.DataFrame(dfBayAreaBeamEvents.get_ii_tourpurpose_stats()).to_csv(BASE_DIR.parent.joinpath(\"exported\", \"Last_iteration_tour_aftr.10_trial.csv\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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": [
    "# Read the linkstats file\n",
    "dfLink = pd.read_csv((BASE_DIR.parent.joinpath(\"runs\",simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".linkstats.csv.gz\"))))\n",
    "links = dfLink[[\"link\",\"length\"]]\n",
    "links = links.drop_duplicates()\n",
    "# create a dictionary linkId vs length_of_links\n",
    "ulinks = dict(zip(links[\"link\"],links[\"length\"]))\n",
    "# Read the physim events file\n",
    "dfphysim = pd.read_csv((BASE_DIR.parent.joinpath(\"runs\",simulation_run,\"ITERS\",(\"it.\"+str(last_iter)),(str(last_iter)+\".physSimEvents.csv.gz\"))))\n",
    "dfphysim_2 = dfphysim.loc[(dfphysim[\"type\"]==\"entered link\")]\n",
    "# dfphysim_2 = dfphysim_2.loc[~dfphysim_2[\"vehicle\"].str.contains(\"BA|SF\", na=False),:]\n",
    "# replace the length which is mostly \"0\" in the \"entered link\" with the actual link length\n",
    "dfphysim_2[\"length\"] = dfphysim_2[\"link\"].map(ulinks)\n",
    "dfphysim_2[\"volume\"]=1 #include a\n",
    "\n",
    "_df = dfphysim_2.groupby([\"link\"]).size().reset_index().rename(columns={0:\"count\"})\n",
    "_df[\"length\"] = _df[\"link\"].map(ulinks)\n",
    "_df.to_csv(BASE_DIR.parent.joinpath(\"exported\", \"Last_iteration_physim.csv\"))\n",
    "\n",
    "dfLink.groupby([\"link\"]).agg({\"length\":\"first\",\"volume\":\"sum\", \"capacity\":\"sum\"}).reset_index().to_csv(BASE_DIR.parent.joinpath(\"exported\", \"Last_iteration_linkstats.csv\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "253.0"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfLink.loc[dfLink[\"link\"]==93642,\"volume\"].sum()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "        link  count   length\n29388  93642    253  883.811",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>link</th>\n      <th>count</th>\n      <th>length</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>29388</th>\n      <td>93642</td>\n      <td>253</td>\n      <td>883.811</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_df[_df[\"link\"]==93642]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "outputs": [],
   "source": [
    "physim = dfphysim_2.groupby([\"link\"]).size().reset_index().rename(columns={0:\"count\"})\n",
    "linkstat = dfLink.groupby([\"link\"]).agg({\"length\":\"first\",\"volume\":\"sum\"})"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "outputs": [
    {
     "data": {
      "text/plain": "30229"
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfphysim.loc[(dfphysim[\"type\"]==\"entered link\")][\"link\"].nunique()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "outputs": [
    {
     "data": {
      "text/plain": "        length  volume\nlink                  \n0      244.148   324.0\n1      244.148     0.0\n2      167.183    12.0\n3      167.183     0.0\n4      186.697     0.0\n...        ...     ...\n97195    8.067     0.0\n97196   93.880     2.0\n97197   93.880     0.0\n97198   21.650     0.0\n97199   21.650     0.0\n\n[97200 rows x 2 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>length</th>\n      <th>volume</th>\n    </tr>\n    <tr>\n      <th>link</th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>244.148</td>\n      <td>324.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>244.148</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>167.183</td>\n      <td>12.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>167.183</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>186.697</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>97195</th>\n      <td>8.067</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>97196</th>\n      <td>93.880</td>\n      <td>2.0</td>\n    </tr>\n    <tr>\n      <th>97197</th>\n      <td>93.880</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>97198</th>\n      <td>21.650</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>97199</th>\n      <td>21.650</td>\n      <td>0.0</td>\n    </tr>\n  </tbody>\n</table>\n<p>97200 rows × 2 columns</p>\n</div>"
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "linkstat = dfLink.groupby([\"link\"]).agg({\"length\":\"first\",\"volume\":\"sum\"})"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "outputs": [],
   "source": [
    "dfjoin = pd.merge(linkstat,physim, on=\"link\")\n",
    "dfjoin.to_csv(BASE_DIR.parent.joinpath(\"exported\", \"Joined_Last_iteration.csv\"))"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "outputs": [],
   "source": [
    "dfjoin_2 = dfjoin[dfjoin[\"link\"].isin(network_links.keys())]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "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)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "outputs": [
    {
     "data": {
      "text/plain": "Empty DataFrame\nColumns: [link, length, volume, count]\nIndex: []",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>link</th>\n      <th>length</th>\n      <th>volume</th>\n      <th>count</th>\n    </tr>\n  </thead>\n  <tbody>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfjoin_2"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "outputs": [
    {
     "data": {
      "text/plain": "Empty DataFrame\nColumns: [link, count]\nIndex: []",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>link</th>\n      <th>count</th>\n    </tr>\n  </thead>\n  <tbody>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "physim[physim[\"link\"].isin(network_links.keys())]"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "outputs": [
    {
     "data": {
      "text/plain": "          ID   FROM     TO   LENGTH  CAPACITY  LANES          MODES  FRSPEED  \\\n0      24746   6090   6228  104.832     650.0    1.0  car,walk,bike     6.21   \n1      24846   6266   6264   30.571    2550.0    3.0  car,walk,bike     6.21   \n2      10446   2648   2656   74.760     300.0    1.0  car,walk,bike     7.45   \n3      91215  10995  21271   19.361     700.0    1.0      walk,bike     7.45   \n4      40839   9782   9787  208.259     300.0    1.0      walk,bike     7.45   \n...      ...    ...    ...      ...       ...    ...            ...      ...   \n66555  94351   8940   8943  138.244     300.0    1.0      walk,bike     6.21   \n66556  12405   2973   2969  199.006     350.0    1.0      walk,bike     7.45   \n66557  45656     53  11007  124.976     350.0    1.0  car,walk,bike     7.45   \n66558  20057   4922   4968  142.139     300.0    1.0      walk,bike     7.45   \n66559  42950  10595  10611   93.874     350.0    1.0  car,walk,bike     7.45   \n\n      Information                                           geometry  \n0            None  MULTILINESTRING ((-122.39897 37.79428, -122.40...  \n1            None  MULTILINESTRING ((-122.40272 37.79093, -122.40...  \n2            None  MULTILINESTRING ((-122.43462 37.74322, -122.43...  \n3            None  MULTILINESTRING ((-122.38312 37.74388, -122.38...  \n4            None  MULTILINESTRING ((-122.46977 37.78270, -122.46...  \n...           ...                                                ...  \n66555        None  MULTILINESTRING ((-122.43859 37.79574, -122.43...  \n66556        None  MULTILINESTRING ((-122.45116 37.73306, -122.45...  \n66557        None  MULTILINESTRING ((-122.37316 37.72864, -122.37...  \n66558        None  MULTILINESTRING ((-122.39918 37.75985, -122.39...  \n66559        None  MULTILINESTRING ((-122.50095 37.78128, -122.50...  \n\n[66548 rows x 10 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>ID</th>\n      <th>FROM</th>\n      <th>TO</th>\n      <th>LENGTH</th>\n      <th>CAPACITY</th>\n      <th>LANES</th>\n      <th>MODES</th>\n      <th>FRSPEED</th>\n      <th>Information</th>\n      <th>geometry</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>24746</td>\n      <td>6090</td>\n      <td>6228</td>\n      <td>104.832</td>\n      <td>650.0</td>\n      <td>1.0</td>\n      <td>car,walk,bike</td>\n      <td>6.21</td>\n      <td>None</td>\n      <td>MULTILINESTRING ((-122.39897 37.79428, -122.40...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>24846</td>\n      <td>6266</td>\n      <td>6264</td>\n      <td>30.571</td>\n      <td>2550.0</td>\n      <td>3.0</td>\n      <td>car,walk,bike</td>\n      <td>6.21</td>\n      <td>None</td>\n      <td>MULTILINESTRING ((-122.40272 37.79093, -122.40...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>10446</td>\n      <td>2648</td>\n      <td>2656</td>\n      <td>74.760</td>\n      <td>300.0</td>\n      <td>1.0</td>\n      <td>car,walk,bike</td>\n      <td>7.45</td>\n      <td>None</td>\n      <td>MULTILINESTRING ((-122.43462 37.74322, -122.43...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>91215</td>\n      <td>10995</td>\n      <td>21271</td>\n      <td>19.361</td>\n      <td>700.0</td>\n      <td>1.0</td>\n      <td>walk,bike</td>\n      <td>7.45</td>\n      <td>None</td>\n      <td>MULTILINESTRING ((-122.38312 37.74388, -122.38...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>40839</td>\n      <td>9782</td>\n      <td>9787</td>\n      <td>208.259</td>\n      <td>300.0</td>\n      <td>1.0</td>\n      <td>walk,bike</td>\n      <td>7.45</td>\n      <td>None</td>\n      <td>MULTILINESTRING ((-122.46977 37.78270, -122.46...</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>66555</th>\n      <td>94351</td>\n      <td>8940</td>\n      <td>8943</td>\n      <td>138.244</td>\n      <td>300.0</td>\n      <td>1.0</td>\n      <td>walk,bike</td>\n      <td>6.21</td>\n      <td>None</td>\n      <td>MULTILINESTRING ((-122.43859 37.79574, -122.43...</td>\n    </tr>\n    <tr>\n      <th>66556</th>\n      <td>12405</td>\n      <td>2973</td>\n      <td>2969</td>\n      <td>199.006</td>\n      <td>350.0</td>\n      <td>1.0</td>\n      <td>walk,bike</td>\n      <td>7.45</td>\n      <td>None</td>\n      <td>MULTILINESTRING ((-122.45116 37.73306, -122.45...</td>\n    </tr>\n    <tr>\n      <th>66557</th>\n      <td>45656</td>\n      <td>53</td>\n      <td>11007</td>\n      <td>124.976</td>\n      <td>350.0</td>\n      <td>1.0</td>\n      <td>car,walk,bike</td>\n      <td>7.45</td>\n      <td>None</td>\n      <td>MULTILINESTRING ((-122.37316 37.72864, -122.37...</td>\n    </tr>\n    <tr>\n      <th>66558</th>\n      <td>20057</td>\n      <td>4922</td>\n      <td>4968</td>\n      <td>142.139</td>\n      <td>300.0</td>\n      <td>1.0</td>\n      <td>walk,bike</td>\n      <td>7.45</td>\n      <td>None</td>\n      <td>MULTILINESTRING ((-122.39918 37.75985, -122.39...</td>\n    </tr>\n    <tr>\n      <th>66559</th>\n      <td>42950</td>\n      <td>10595</td>\n      <td>10611</td>\n      <td>93.874</td>\n      <td>350.0</td>\n      <td>1.0</td>\n      <td>car,walk,bike</td>\n      <td>7.45</td>\n      <td>None</td>\n      <td>MULTILINESTRING ((-122.50095 37.78128, -122.50...</td>\n    </tr>\n  </tbody>\n</table>\n<p>66548 rows × 10 columns</p>\n</div>"
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ],
   "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
}