WPP.Distant-helper_PIPE-001_v2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Copyright (c) Microsoft Corporation.\n",
    "# Licensed under the MIT license.\n",
    "\n",
    "# BUILDING A DISTANCE HELPER EXCEL VERSION FOR PYTHON\n",
    "\n",
    "from IPython.display import display, Markdown, clear_output, HTML, Javascript, display_html\n",
    "import subprocess\n",
    "import sys\n",
    "display(Markdown(\"\"\" ## Checking required packages:\n",
    "#### if any are missing, installing them now..... \"\"\"))\n",
    "!{sys.executable} -m pip install --no-cache-dir -r requirements_SP.txt\n",
    "clear_output()\n",
    "\n",
    "\n",
    "import ipysheet\n",
    "from ipysheet import column,sheet,to_dataframe,row\n",
    "import ipywidgets as widgets\n",
    "import pandas as pd\n",
    "import geopy\n",
    "import collections\n",
    "import ipywidgets as widgets\n",
    "from geopy.geocoders import Nominatim\n",
    "import numpy as np\n",
    "from itertools import combinations, permutations, product\n",
    "from math import radians, cos, sin, asin, sqrt\n",
    "from ast import literal_eval as make_tuple\n",
    "\n",
    "import os\n",
    "!jupyter nbextension enable --py widgetsnbextension --sys-prefix\n",
    "clear_output()\n",
    "#display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index(), IPython.notebook.get_selected_index()+1)'))\n",
    "\n",
    "\n",
    "HTML('''<script>{\n",
    "    $('div.input').hide();\n",
    "    }''')\n",
    "\n",
    "display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index(), IPython.notebook.get_selected_index()+1)'))\n",
    "HTML('''<script>\n",
    "    code_show=true; \n",
    "    function code_toggle() {\n",
    "     if (code_show){\n",
    "     $('div.input').hide();\n",
    "     } else {\n",
    "     $('div.input').show();\n",
    "     }\n",
    "     code_show = !code_show\n",
    "    } \n",
    "    $( document ).ready(code_toggle);\n",
    "    </script>\n",
    "    The raw code for this IPython notebook is by default hidden for easier reading toggle on/off the raw code by clicking <a href=\"javascript:code_toggle()\">here</a>.''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(Markdown(\"# Workspace Collaboration Optimizer\"))\n",
    "display(Markdown(\"If your team or company is moving to a new worksite or you need to reorganize an existing workspace, this open-source tool can help. The Workspace Collaboration Optimizer can help you identify and seat teams together in a workspace that maximizes and fosters cross-team productivity and collaboration. You can use this tool to generate floor plans quickly and objectively, in a data-driven way that optimizes employee collaboration by seating teams together. This tool works best in Chrome. \"))\n",
    "\n",
    "display(Markdown(\"##  Distance Helper\"))\n",
    "display(Markdown(\"This notebook produces a distance.csv file, one of the required input files to generate a seating plan. Before running this notebook, make sure you have your zone coordinates to reference and the list of workspaces for your campus. \"))\n",
    "display(Markdown(\"You can skip this step and move to the Validations notebook if you have already prepared a distance.csv file. You can also skip this step if you only have one building containing all of your workspaces and want to manually create your distance file - See documentation for more details.\"))\n",
    "display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "style = {'description_width': '185px'}\n",
    "layout = {'width': '365px'}\n",
    "\n",
    "get_to_next = widgets.Button(description='Go',button_style='primary')\n",
    "howmanyBLDG = widgets.Text(description='Number of buildings',style=style)\n",
    "def go_next_line(click):\n",
    "    display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n",
    "\n",
    "clear_output()\n",
    "display(Markdown(\"#### <font color='Green'> 1. Enter the total number of buildings you would like to include in your seating plan. </font>\"))\n",
    "display(howmanyBLDG)\n",
    "display(get_to_next)\n",
    "get_to_next.on_click(go_next_line)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "styletext = {'description_width': '200px'}\n",
    "howmanyFLRS = widgets.Text(description='Max number of floors',style=styletext)\n",
    "# style_sheet = {'description_width': '1200px'}\n",
    "# sheetnameBLDGS = ipysheet.sheet(rows=int(howmanyBLDG.value), columns=3, column_headers=True, row_headers=False,layout=layout,style=style_sheet,row_resizing=True,column_resizing=True)\n",
    "\n",
    "# sheetnameBLDGS.column_headers=['Campus Name','Building','Address']\n",
    "# exec(\"for i in range(\"+str(howmanyBLDG.value)+\"):\\n\\trow(i, ['', '', ''])\")\n",
    "# # column(0, ['', '', ''])\n",
    "# create an Empty DataFrame \n",
    "# object With column names only \n",
    "dfskeleton_BLDG = pd.DataFrame(np.zeros([int(howmanyBLDG.value), 3])*np.nan)\n",
    "dfskeleton_BLDG1 = dfskeleton_BLDG.replace(np.nan, '', regex=True)\n",
    "dfskeleton_BLDG1.columns = ['Campus Name','Building','Address']\n",
    "dfskeleton_BLDG1.reset_index(drop=True, inplace=True)\n",
    "\n",
    "sheetnameBLDGS = ipysheet.from_dataframe(dfskeleton_BLDG1)\n",
    "\n",
    "clear_output()\n",
    "display(Markdown(\"#### <font color='Green'> 2. Enter your campus/building information below. </font>\"))\n",
    "display(Markdown(\"##### <font color='Gray'> Address is needed to fetch latitude and longitude coordinates. </font>\"))\n",
    "maxflrs_mkdwn = widgets.Output()\n",
    "with maxflrs_mkdwn:\n",
    "    display(Markdown(\"#### <font color='Green'> 3. Enter the maximum number of floors across your buildings.</font>\"))\n",
    "    display(Markdown(\"##### <font color='Gray'> Ex. If Building A has 4 floors and Building B has 2 floors, enter '4'</font>\"))\n",
    "display(widgets.VBox([sheetnameBLDGS,widgets.VBox([maxflrs_mkdwn,howmanyFLRS]),get_to_next]))\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "\n",
    "display(Markdown(\"\"\" #### Fetching Latitude and Longitude coordinates... \"\"\"))\n",
    "\n",
    "dfbuildingADDRESS = to_dataframe(sheetnameBLDGS) \n",
    "\n",
    "addresscol = 'Address'\n",
    "global maxfloors\n",
    "maxfloors = int(howmanyFLRS.value)\n",
    "\n",
    "# declare an empty list to store \n",
    "# latitude and longitude of values  \n",
    "# of column \n",
    "longitude = [] \n",
    "latitude = [] \n",
    "\n",
    "\n",
    "\n",
    "def findlatlong_1(addresscol): \n",
    "# try and catch is used to overcome \n",
    "    # the exception thrown by geolocator \n",
    "    # using geocodertimedout   \n",
    "    try: \n",
    "        time.sleep(1) #to add delay in case of large DFs  \n",
    "        # Specify the user_agent as your \n",
    "        # app name it should not be none \n",
    "        geolocator = Nominatim(user_agent=\"get_lat_long\") \n",
    "#         location = geolocator.geocode(col)\n",
    "#         coordinates = location.latitude, location.longitude  \n",
    "        return geolocator.geocode(addresscol)\n",
    "      \n",
    "    except GeocoderTimedOut: \n",
    "          \n",
    "        return findlatlong_1(addresscol) \n",
    "    \n",
    "    \n",
    "# each value from column \n",
    "# will be fetched and sent to \n",
    "# function find_geocode \n",
    "for i in (dfbuildingADDRESS[addresscol]): \n",
    "\n",
    "    if findlatlong_1(i) != None: \n",
    "\n",
    "        loc = findlatlong_1(i) \n",
    "\n",
    "        # coordinates returned from  \n",
    "        # function is stored into \n",
    "        # two separate list \n",
    "        latitude.append(loc.latitude) \n",
    "        longitude.append(loc.longitude) \n",
    "\n",
    "    # if coordinate for a city not \n",
    "    # found, insert \"NaN\" indicating  \n",
    "    # missing value  \n",
    "    else: \n",
    "        latitude.append(np.nan) \n",
    "        longitude.append(np.nan) \n",
    "clear_output()\n",
    "display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    " #now add these columns to dataframe \n",
    "dfbuildingSTG = dfbuildingADDRESS.copy()\n",
    "dfbuildingSTG[\"Latitude\"] = latitude \n",
    "dfbuildingSTG[\"Longitude\"] = longitude \n",
    "#create coordinates column from both\n",
    "dfbuildingSTG['Coordinates'] = dfbuildingSTG['Latitude'].map('{:,.6f}'.format) +','+ dfbuildingSTG['Longitude'].map('{:,.6f}'.format)\n",
    "\n",
    "#grab final output df\n",
    "dfbuildingSTG.drop(['Latitude','Longitude','Address'],axis=1,inplace=True)\n",
    "display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "columnsFLR = ['Number of Floors']\n",
    "columnsFLR = columnsFLR + ['Floor Number '+str(i) for i in range(1,maxfloors+1)]\n",
    "\n",
    "dfbuildingSTG2 = dfbuildingSTG.copy()\n",
    "dfbuildingSTG3 = pd.concat([dfbuildingSTG2,pd.DataFrame(None,columns=columnsFLR)],sort=False)\n",
    "dfbuildingSTG4 = dfbuildingSTG3.replace(np.nan, '', regex=True)\n",
    "dfbuildingSTG4['Building ID'] = dfbuildingSTG4['Campus Name']+'-'+dfbuildingSTG4['Building']\n",
    "\n",
    "bid_col_name=\"Building ID\"\n",
    "first_col_bldg = dfbuildingSTG4.pop(bid_col_name)\n",
    "\n",
    "dfbuildingSTG4.insert(0, bid_col_name, first_col_bldg)\n",
    "sheetnameBLDGFLRS = ipysheet.from_dataframe(dfbuildingSTG4)\n",
    "display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clear_output()\n",
    "display(Markdown(\"#### <font color='Green'> 4. Enter the total number of floors and the corresponding floor names/numbers for each building below. </font>\"))\n",
    "display(Markdown(\"#### <font color='Gray'>_If your organization’s first floor is on the actual first floor of the building, fill out “1” underneath Floor number 1. If your organization’s floors start on the 5th floor of a building, fill out “5” underneath Floor number 1._ </font>\"))\n",
    "display(widgets.VBox([sheetnameBLDGFLRS,get_to_next]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "maxzones_mkdwn = widgets.Output()\n",
    "with maxzones_mkdwn:\n",
    "    display(Markdown(\"#### <font color='Green'> 5. Enter the maximum number of zones you have across all floors </font>\"))\n",
    "\n",
    "howmanyQDRTS = widgets.Text(description='Max number of zones',style=styletext)\n",
    "display(widgets.VBox([maxzones_mkdwn,howmanyQDRTS,get_to_next]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfbuildingsFINAL = to_dataframe(sheetnameBLDGFLRS)\n",
    "global maxquadrants\n",
    "maxquadrants = int(howmanyQDRTS.value)\n",
    "\n",
    "flrqdrt_style = {'width:50px'}\n",
    "\n",
    "columnsQDRT = ['Floor Name','Number of Zones']\n",
    "columnsQDRT = columnsQDRT + ['Zone Coordinates '+str(i) for i in range(1,maxquadrants+1)]\n",
    "df_selectneededCOLS = dfbuildingsFINAL[['Campus Name','Building', 'Number of Floors']].copy()\n",
    "\n",
    "dfrepeatedbyFLRS = pd.DataFrame(np.repeat(df_selectneededCOLS.values, \n",
    "                                           df_selectneededCOLS['Number of Floors'].replace(0,1).tolist(), \n",
    "                                           axis=0),\n",
    "                                 columns=df_selectneededCOLS.columns)\n",
    "\n",
    "dffloorSTG1 = pd.concat([dfrepeatedbyFLRS,pd.DataFrame(None,columns=columnsQDRT)],sort=False)\n",
    "dffloorSTG2 = dffloorSTG1.replace(np.nan, '', regex=True)\n",
    "\n",
    "sheetnameFLRS = ipysheet.from_dataframe(dffloorSTG2)\n",
    "display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#### clear_output()\n",
    "display(Markdown(\"#### <font color='Green'> 6. Enter the floor names from Step 4, total number of zones for each floor, and zone coordinates below. </font>\"))\n",
    "display(Markdown(\"#### <font color='black'> <b> Please review the documentation on how to create the zone coordinates and use the following syntax for zone coordinates. E.G. (1,1)</b> </font>\"))\n",
    "display(widgets.VBox([sheetnameFLRS,get_to_next]))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_quadrants_selected = to_dataframe(sheetnameFLRS)\n",
    "display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n",
    "clear_output()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#dffloorsFINAL = to_dataframe(sheetnameFLRS)\n",
    "dffloorsFINAL = df_quadrants_selected.copy()\n",
    "# dffloorsFINAL= dffloorsFINAL.fillna(value=np.nan)\n",
    "dffloorsFINAL = dffloorsFINAL.replace([None], [''], regex=True)\n",
    "dffloorsFINAL['Floor ID'] = dffloorsFINAL['Campus Name']+'-'+dffloorsFINAL['Building']+'-'+dffloorsFINAL['Floor Name']\n",
    "\n",
    "fid_col_name=\"Floor ID\"\n",
    "first_col_flr = dffloorsFINAL.pop(fid_col_name)\n",
    "\n",
    "dffloorsFINAL.insert(0, fid_col_name, first_col_flr)\n",
    "dffloorsFINAL = dffloorsFINAL.rename(columns={'Campus Name': 'Campus'})\n",
    "\n",
    "\n",
    "dffloorsFINAL_melted = pd.melt(dffloorsFINAL, id_vars=['Floor ID','Campus','Building','Number of Floors','Floor Name','Number of Zones'], var_name='Zone Id', value_name='Zone Coordinates')\n",
    "dffloorsFINAL_melted['Space Name'] = \"\"\n",
    "\n",
    "df_rename_ZONEQDRNTS = dffloorsFINAL_melted[['Floor ID','Number of Zones','Zone Coordinates','Space Name']]\n",
    "\n",
    "df_rename_ZONEQDRNTS = df_rename_ZONEQDRNTS.sort_values('Floor ID')\n",
    "df_rename_ZONEQDRNTS['Zone Coordinates'].replace('', np.nan, inplace=True)\n",
    "df_rename_ZONEQDRNTS.dropna(subset=['Zone Coordinates'], inplace=True)\n",
    "\n",
    "sheetnameZONES_NAMES = ipysheet.from_dataframe(df_rename_ZONEQDRNTS)\n",
    "\n",
    "display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "display(Markdown(\"#### <font color='Green'> 7. Enter a unique workspace name for each zone. </font>\"))\n",
    "display(Markdown(\"#### <font color='black'> <b> These 'friendly' names should match the workspace/floor names used in your space_capacity.csv file. </b> </font>\"))\n",
    "\n",
    "display(widgets.VBox([sheetnameZONES_NAMES,get_to_next]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_zones_STG = to_dataframe(sheetnameZONES_NAMES)\n",
    "\n",
    "# check if space names \n",
    "display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "https://stackoverflow.com/questions/4913349/haversine-formula-in-python-bearing-and-distance-between-two-gps-points\n",
    "\n",
    "\"\"\"\n",
    "def haversine(lon1, lat1, lon2, lat2):\n",
    "    \"\"\"\n",
    "    Calculate the great circle distance between two points \n",
    "    on the earth (specified in decimal degrees)\n",
    "    \"\"\"\n",
    "    # convert decimal degrees to radians \n",
    "    lon1, lat1, lon2, lat2 = map(radians, [lon1, lat1, lon2, lat2])\n",
    "\n",
    "    # haversine formula \n",
    "    dlon = lon2 - lon1 \n",
    "    dlat = lat2 - lat1 \n",
    "    a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2\n",
    "    c = 2 * asin(sqrt(a)) \n",
    "    #r = 6371 # Radius of earth in kilometers. Use 3956 for miles\n",
    "    r = 3956\n",
    "    return c * r\n",
    "\n",
    "\n",
    "##############################\n",
    "#Load Files ** Start Here **\n",
    "############################\n",
    "\n",
    "cwd = os.getcwd()\n",
    "# tab_nest_001 = widgets.Accordion()\n",
    "# style = {'description_width': '185px'}\n",
    "# layout = {'width': '365px'}\n",
    "\n",
    "# go_run_full = widgets.Button(description='Go',button_style = 'primary')\n",
    "\n",
    "# def go_to_nextblock(btn):\n",
    "#         display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+3)'))\n",
    "\n",
    "\n",
    "# input_path_001 = FileChooser(cwd)\n",
    "# tab_nest_001.children = [input_path_001]\n",
    "\n",
    "# tab_nest_001.set_title(0,'Load file and name output file')\n",
    "# go_run_full.on_click(go_to_nextblock) \n",
    "\n",
    "\n",
    "# display(widgets.VBox([tab_nest_001,go_run_full]))\n",
    "\n",
    "\n",
    "dfbuildingsDIST = dfbuildingsFINAL.copy()\n",
    "dfbuildingsDIST.index = dfbuildingsDIST.index.astype(int) #use astype to convert to int\n",
    "dfbuildingsDIST.columns = [s.strip() for s in dfbuildingsDIST.columns]\n",
    "num_bldgs = len(dfbuildingsDIST)\n",
    "dfbuildingsDIST[['lat','long']] = dfbuildingsDIST[\"Coordinates\"].str.split(\",\",expand=True) \n",
    "dfbuildingsDIST['lat'] = dfbuildingsDIST['lat'].astype(float)\n",
    "dfbuildingsDIST['long'] = dfbuildingsDIST['long'].astype(float)\n",
    "\n",
    "for i in range(num_bldgs):\n",
    "    bldg_id = dfbuildingsDIST.loc[i,\"Building ID\"]\n",
    "    bldg_bmcRaw = dfbuildingsDIST.loc[i,\"Coordinates\"]\n",
    "    blat = dfbuildingsDIST.loc[i,\"lat\"]\n",
    "    blong = dfbuildingsDIST.loc[i,\"long\"]\n",
    "    #print(bldg_id, \"coordinates found...\", blat, blong)\n",
    "\n",
    "#print(\"calculating building distance...\")\n",
    "\n",
    "bld_ids = list(range(len(dfbuildingsDIST)))\n",
    "bldg_edges = list(product(bld_ids, bld_ids))\n",
    "#bldg_edges = list(permutations(bld_ids, 2))\n",
    "bldg_distDF = pd.DataFrame(bldg_edges, columns = [\"from_idx\", \"to_idx\"])\n",
    "\n",
    "dfbuildingsDIST2 = dfbuildingsDIST[[\"Building ID\", \"lat\", \"long\"]].copy()\n",
    "\n",
    "bldg_distDF = pd.merge(bldg_distDF, dfbuildingsDIST2, left_on = \"from_idx\", right_index=True, how = 'left')\n",
    "bldg_distDF = pd.merge(bldg_distDF, dfbuildingsDIST2, left_on = \"to_idx\", right_index=True, how = 'left', suffixes= ['_From', '_To'])\n",
    "for i in range(len(bldg_distDF)):\n",
    "    lat_from = bldg_distDF.loc[i,\"lat_From\"]\n",
    "    long_from = bldg_distDF.loc[i,\"long_From\"]\n",
    "    lat_to = bldg_distDF.loc[i,\"lat_To\"]\n",
    "    long_to = bldg_distDF.loc[i,\"long_To\"]\n",
    "    hav_dist = haversine(lat_from, long_from, lat_to, long_to)\n",
    "    bldg_distDF.loc[i,\"dist\"] = hav_dist\n",
    "    \n",
    "    \n",
    "#print(\"Everything good so far... \")\n",
    "#print(\"Calculated \" + str(len(bldg_distDF))+ \" distances across building and campus using Bing Map coordinates and Haversine formula\")\n",
    "\n",
    "\n",
    "################################\n",
    "## Floor Distances\n",
    "#################################\n",
    "\n",
    "#print(\"calculating floor to floor distances...\")\n",
    "\n",
    "#create floors\n",
    "def create_floor_distances(floor_rel_dist, avg_dist):\n",
    "    rel_floor_idxs = list(range(len(floor_rel_dist)))\n",
    "    res = []\n",
    "    floor_rel_dist_avg = [x * avg_dist for x in floor_rel_dist]\n",
    "    for origin in floor_rel_dist_avg:\n",
    "        calc_dist_row = [abs(x - origin) for x in floor_rel_dist_avg]\n",
    "        res.append(calc_dist_row.copy())\n",
    "    return res, rel_floor_idxs  \n",
    "\n",
    "def create_floor_adj_list(K, key_start = 0):\n",
    "    if K == 0: return [], None\n",
    "    if K == 1: return [key_start], {key_start: []}\n",
    "    ids = [key_start+i for i in list(range(K))]\n",
    "    ids_set = set(ids)\n",
    "    d = {}\n",
    "    for current in ids:\n",
    "        d[current] = sorted(list(ids_set - set([current])))\n",
    "    return ids, d\n",
    "\n",
    "bldg_input = dfbuildingsFINAL.copy()\n",
    "bldg_input.index = bldg_input.index.astype(int) #use astype to convert to int\n",
    "num_bldgs = len(bldg_input)\n",
    "floors_per_bldg = {}\n",
    "dist_per_floors = {}\n",
    "floor_labels_per_bldg = {}\n",
    "cols_b_tab = list(bldg_input.columns)\n",
    "f_start_idx = cols_b_tab.index(\"Floor Number 1\")\n",
    "#print(\"loading floor relative distances....\")\n",
    "for i in range(num_bldgs):\n",
    "    bldg_id = dfbuildingsDIST.loc[i,\"Building ID\"]\n",
    "    num_floors = int(dfbuildingsDIST.loc[i,\"Number of Floors\"])\n",
    "    #removing optional column to input average distance as per @july 20, 2020 excel input file validation review meeting\n",
    "    #avg_dist = df1.loc[i,\"Average Distance per Floor\"] \n",
    "    avg_dist = 2\n",
    "    floor_rel_dist = list(dfbuildingsDIST.iloc[i,f_start_idx: f_start_idx + num_floors])\n",
    "    floor_rel_dist = [int(i) for i in floor_rel_dist] \n",
    "    #print(bldg_id, \"found\", num_floors, \"floors rel dist: \", floor_rel_dist)\n",
    "    floor_distances, floors_idx = create_floor_distances(floor_rel_dist, avg_dist)\n",
    "    key_start = 0\n",
    "    floor_labels = [key_start+i for i in floors_idx]  \n",
    "    dist_per_floors[bldg_id]= floor_distances.copy()\n",
    "    floor_labels_per_bldg[bldg_id] = floor_labels.copy()\n",
    "    \n",
    "    fi, fn = create_floor_adj_list(num_floors)\n",
    "    if fn:\n",
    "        floors_per_bldg[bldg_id] = fn.copy()\n",
    "    else:\n",
    "        print(\"wait....something went wrong....Buildings dataframe (dfbuildingsFINAL)... row:\", i)\n",
    "\n",
    "#print(\"number of buildings with floor dist:\", len(dist_per_floors), \"of\", len(floors_per_bldg) )\n",
    "\n",
    "\n",
    "##########################\n",
    "## Quadrant Coordinates\n",
    "##########################\n",
    "\n",
    "#print(\"loading quadrant coordinates... from floors tab... \")\n",
    "df100 = dffloorsFINAL.copy()\n",
    "#df100 = dffloorsFINAL.copy()\n",
    "df100.index = df100.index.astype(int) #use astype to convert to int\n",
    "df100.columns = [s.strip() for s in df100.columns]\n",
    "num_floors = len(df100)\n",
    "quad_coord_per_floor = {}\n",
    "\n",
    "cols = list(df100.columns)\n",
    "start_idx = cols.index('Zone Coordinates 1')\n",
    "#print(\"start of coordinates columns detected... column idx:\", start_idx)\n",
    "#print(\"validating coordinate columns vs number of quadrants... \")\n",
    "\n",
    "for i in range(num_floors):\n",
    "    floor_id = df100.loc[i,\"Floor ID\"]\n",
    "    num_quads = int(df100.loc[i,\"Number of Zones\"])\n",
    "    coord_row = []\n",
    "    for j in range(num_quads):\n",
    "        temp = df100.iloc[i, start_idx+j]\n",
    "        coord_row.append(temp)\n",
    "    if num_quads != len(coord_row):\n",
    "        print(floor_id, \"number of zones do not match\")\n",
    "        print(\"Excel Floor tab ERROR: Please enter one coordinate per each zone\")\n",
    "    else:\n",
    "        #print(floor_id,\"found quadrant coordinates...\", coord_row)\n",
    "        quad_coord_per_floor[floor_id] = coord_row.copy() \n",
    "\n",
    "        \n",
    "    ##########################\n",
    "## Quadrant Distances\n",
    "##########################\n",
    "#print(\"calculating quadrant to quadrant distances...\")\n",
    "\n",
    "#create quadrants\n",
    "def create_quad_distances(coords, avg_dist):\n",
    "    coord_idxs = list(range(len(coords)))\n",
    "    coords_arr = [ np.array(x) for x in coords] \n",
    "    res = []\n",
    "    for origin in coords_arr:\n",
    "        coord_dist_row = [np.linalg.norm(coord2-origin) for coord2 in coords_arr]\n",
    "        res.append(coord_dist_row.copy())\n",
    "    return res, coord_idxs  \n",
    "\n",
    "def create_quad_adj_list(K, key_start = \"a\"):\n",
    "    if K == 0: return [], None\n",
    "    if K == 1: return [key_start], {key_start: \"\"}\n",
    "    ids = [chr(ord(key_start)+i) for i in list(range(K))]\n",
    "    ids_set = set(ids)\n",
    "    d = {}\n",
    "    for current_quad in ids:\n",
    "        d[current_quad] = \"\".join(sorted(list(ids_set - set(current_quad))))\n",
    "    return ids, d\n",
    "\n",
    "\n",
    "quads_dist_per_floor = {}\n",
    "quad_idxs_map_per_floor = {}\n",
    "quad_labels_per_floor = {} \n",
    "for i in range(num_floors):\n",
    "    floor_id = df100.loc[i,\"Floor ID\"]\n",
    "    #TO DO. Average distance per quadrant small/medium/large\n",
    "    #avg_dist_q = df100.loc[i,\"Average Distance Per Quadrant\"] \n",
    "    avg_dist_q = 1\n",
    "    coords = quad_coord_per_floor[floor_id]\n",
    "    coords = [make_tuple(s) for s in coords]\n",
    "    coords = [(float(x),float(y)) for x,y in coords]\n",
    "    quad_distances, quads_idx = create_quad_distances(coords, avg_dist_q)\n",
    "    key_start_q = 'a'\n",
    "    quad_labels = [chr(ord(key_start_q)+i) for i in quads_idx]\n",
    "    quad_idxs_map = {q: i for q,i in zip(quad_labels, quads_idx)}\n",
    "    quads_dist_per_floor[floor_id]= quad_distances.copy()\n",
    "    quad_labels_per_floor[floor_id] = quad_labels.copy()\n",
    "    quad_idxs_map_per_floor[floor_id] = quad_idxs_map.copy() \n",
    "\n",
    "#print(\"number of floors with quads dist\", len(quads_dist_per_floor))\n",
    "\n",
    "#############################\n",
    "#  Quadrant IDs\n",
    "#############################\n",
    "\n",
    "quadrant_ids = []\n",
    "for floor_id, quad_labels in quad_labels_per_floor.items():\n",
    "    campus_name, bldg_name, floor_name = floor_id.split(\"-\")  \n",
    "    bldg_id = campus_name + \"-\" + bldg_name \n",
    "    for q in quad_labels:\n",
    "        space_row = {}\n",
    "        qid = floor_id+\"-\"+q\n",
    "        space_row[\"campus\"] = campus_name\n",
    "        space_row[\"building\"] = bldg_name\n",
    "        space_row[\"floor\"] = floor_name\n",
    "        space_row[\"quad\"] = q\n",
    "        quad_idx_map = quad_idxs_map_per_floor[floor_id]\n",
    "        space_row[\"quad_idx\"] = quad_idx_map[q]\n",
    "        space_row[\"building_id\"] = bldg_id\n",
    "        space_row[\"floor_id\"] = floor_id\n",
    "        space_row[\"quadrant_id\"] = qid\n",
    "        quadrant_ids.append(space_row.copy())        \n",
    "\n",
    "#print(\"number of spaces loaded... \", len(quadrant_ids))\n",
    "####################################\n",
    "#  Create Campus and Building Matrix\n",
    "####################################\n",
    "\n",
    "#sort idxs\n",
    "quadrant_idsDF = pd.DataFrame(quadrant_ids)\n",
    "quadrant_idsDF = quadrant_idsDF.sort_values(by = [\"quadrant_id\"]).reset_index(drop = True)\n",
    "quadrant_idsDF = quadrant_idsDF.reset_index()\n",
    "\n",
    "#new campus and buildings matrix (filled with zeros)\n",
    "N = len(quadrant_ids)\n",
    "new_building_expanded_matrix = np.zeros((N,N))\n",
    "\n",
    "#print(\"New building matrix created... expanding to quad size... shape:\", new_building_expanded_matrix.shape )\n",
    "#print(\"adding building distances as per Bing Map coordintes...\")\n",
    "\n",
    "proxyDF = quadrant_idsDF\n",
    "bldg_T = new_building_expanded_matrix\n",
    "for i in range(len(bldg_distDF)):\n",
    "    from_bldg = bldg_distDF.loc[i,\"Building ID_From\"]\n",
    "    to_bldg = bldg_distDF.loc[i,\"Building ID_To\"]\n",
    "    bldg_dist = bldg_distDF.loc[i,\"dist\"]\n",
    "    from_idxs = list(proxyDF.loc[proxyDF[\"building_id\"]==from_bldg,\"index\"]) \n",
    "    to_idxs = list(proxyDF.loc[proxyDF[\"building_id\"]==to_bldg,\"index\"]) \n",
    "    for i in from_idxs:\n",
    "        for j in to_idxs:\n",
    "            bldg_T[i, j] = bldg_dist\n",
    "\n",
    "#print(\"building matrix filled...\")\n",
    "\n",
    "#output for excel\n",
    "campus_bldg_DF = pd.DataFrame(bldg_T)\n",
    "campus_bldg_DF.index = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "campus_bldg_DF = campus_bldg_DF.reset_index()\n",
    "quad_ids = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "new_cols = [\"quadrant_id\"] + quad_ids\n",
    "campus_bldg_DF.columns = new_cols\n",
    "\n",
    "####################################\n",
    "#  Create Floors Distance Matrix\n",
    "####################################\n",
    "\n",
    "#new floors matrix (filled with zeros)\n",
    "new_floors_expanded_matrix = np.zeros((N,N))\n",
    "\n",
    "#print(\"New floors matrix created... expanding to quad size... shape:\", new_floors_expanded_matrix.shape )\n",
    "#print(\"adding floors distances as per buildings tab...\")\n",
    "\n",
    "proxyDF = quadrant_idsDF\n",
    "floor_T = new_floors_expanded_matrix\n",
    "for bldg_id, floor_distances in dist_per_floors.items():\n",
    "    floor_labels = sorted(set(list(proxyDF.loc[proxyDF['building_id'] == bldg_id, \"floor\"])))\n",
    "    floor_labels_map = { i:s for i,s in enumerate(floor_labels)}\n",
    "    for rel_floor_idx1, floor_dist_row in enumerate(floor_distances):                        \n",
    "        from_floor = floor_labels_map[rel_floor_idx1]\n",
    "        for rel_floor_idx2, floor_dist in enumerate(floor_dist_row): \n",
    "            to_floor = floor_labels_map[rel_floor_idx2]\n",
    "            from_idxs = list(proxyDF.loc[(proxyDF[\"building_id\"] == bldg_id) & (proxyDF[\"floor\"]==from_floor),\"index\"]) \n",
    "            to_idxs = list(proxyDF.loc[(proxyDF[\"building_id\"] == bldg_id) & (proxyDF[\"floor\"]==to_floor),\"index\"]) \n",
    "            for i in from_idxs:\n",
    "                for j in to_idxs:\n",
    "                    floor_T[i, j] = floor_dist\n",
    "\n",
    "#print(\"floor matrix filled...\")\n",
    "\n",
    "#output for excel\n",
    "floors_DF = pd.DataFrame(floor_T)\n",
    "floors_DF.index = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "floors_DF = floors_DF.reset_index()\n",
    "quad_ids = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "new_cols = [\"quadrant_id\"] + quad_ids\n",
    "floors_DF.columns = new_cols\n",
    "\n",
    "####################################\n",
    "# \"REPMAT\" Floor Vectors - Horizontally\n",
    "####################################\n",
    "#print(\"translating floor distances horizontally... \")\n",
    "\n",
    "proxyDF = quadrant_idsDF\n",
    "floor_T2 = np.zeros((N,N))\n",
    "for bldg_id, floor_distances in dist_per_floors.items():\n",
    "    row_idxs = list(proxyDF.loc[(proxyDF[\"building_id\"] == bldg_id),\"index\"]) \n",
    "    col_idxs = row_idxs.copy()\n",
    "    cv, rv = np.meshgrid(row_idxs, col_idxs)\n",
    "    temp = floor_T[rv, cv]\n",
    "    floor_dist = temp[:,[0]]  #first column\n",
    "    floor_vector = floor_dist\n",
    "    #print(\"REPMAT horizontally\", bldg_id, \"num rows:\", len(floor_vector))\n",
    "    min_idx, max_idx = min(col_idxs), max(col_idxs)\n",
    "    #load left \n",
    "    floor_T2[row_idxs, 0:min_idx] = floor_vector\n",
    "    #load right\n",
    "    floor_T2[row_idxs, max_idx+1:] = floor_vector\n",
    "\n",
    "#output for excel\n",
    "floors_DF2 = pd.DataFrame(floor_T2)\n",
    "floors_DF2.index = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "floors_DF2 = floors_DF2.reset_index()\n",
    "quad_ids = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "new_cols = [\"quadrant_id\"] + quad_ids\n",
    "floors_DF2.columns = new_cols\n",
    "\n",
    "#print(\"Floor matrix HORIZONTAL translation matrix filled...\")\n",
    "\n",
    "####################################\n",
    "# \"REPMAT\" Floor Vectors - Vertically\n",
    "####################################\n",
    "#print(\"translating floor distances VERTICALLY... \")\n",
    "\n",
    "proxyDF = quadrant_idsDF\n",
    "floor_T3 = np.zeros((N,N))\n",
    "for bldg_id, floor_distances in dist_per_floors.items():\n",
    "    row_idxs = list(proxyDF.loc[(proxyDF[\"building_id\"] == bldg_id),\"index\"]) \n",
    "    col_idxs = row_idxs.copy()\n",
    "    cv, rv = np.meshgrid(row_idxs, col_idxs)\n",
    "    temp = floor_T[rv, cv]\n",
    "    floor_dist = temp[[0],:]  #first row\n",
    "    floor_vector = floor_dist\n",
    "    #print(\"REPMAT vertically\", bldg_id, \"num rows:\", len(floor_vector))\n",
    "   \n",
    "    min_idx, max_idx = min(col_idxs), max(col_idxs)\n",
    "   \n",
    "    #load left \n",
    "    floor_T3[0:min_idx, col_idxs] = floor_vector\n",
    "    #load right\n",
    "    floor_T3[max_idx+1:, col_idxs] = floor_vector\n",
    "   \n",
    "   \n",
    "\n",
    "#output for excel\n",
    "floors_DF3 = pd.DataFrame(floor_T3)\n",
    "floors_DF3.index = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "floors_DF3 = floors_DF3.reset_index()\n",
    "quad_ids = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "new_cols = [\"quadrant_id\"] + quad_ids\n",
    "floors_DF3.columns = new_cols\n",
    "\n",
    "#print(\"Floor matrix VERTICAL translation matrix filled...\")\n",
    "\n",
    "###################################################################\n",
    "# Add \"Translate\" Floor Vectors - Vertical + Horizontal + Diagonal\n",
    "###################################################################\n",
    "\n",
    "floor_T4 = floor_T + floor_T2 + floor_T3\n",
    "\n",
    "#output for excel\n",
    "floors_DF4 = pd.DataFrame(floor_T4)\n",
    "floors_DF4.index = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "floors_DF4 = floors_DF4.reset_index()\n",
    "quad_ids = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "new_cols = [\"quadrant_id\"] + quad_ids\n",
    "floors_DF4.columns = new_cols\n",
    "\n",
    "\n",
    "####################################\n",
    "#  Create Quadrants Distance Matrix\n",
    "####################################\n",
    "\n",
    "#new quadrants matrix (filled with zeros)\n",
    "new_quad_expanded_matrix = np.zeros((N,N))\n",
    "\n",
    "#print(\"New quad matrix created... expanding to quad size... shape:\", new_quad_expanded_matrix.shape )\n",
    "#print(\"adding quad distances as per coordinates in floors tab...\")\n",
    "\n",
    "proxyDF = quadrant_idsDF\n",
    "quad_T = new_quad_expanded_matrix\n",
    "for floor_id, quad_distances in quads_dist_per_floor.items():\n",
    "    quad_labels = quad_labels_per_floor[floor_id]\n",
    "    quad_labels_map = { i:s for i,s in enumerate(quad_labels)}\n",
    "    for rel_quad_idx1, quad_dist_row in enumerate(quad_distances):                        \n",
    "        from_quad = quad_labels_map[rel_quad_idx1]\n",
    "        for rel_quad_idx2, quad_dist in enumerate(quad_dist_row): \n",
    "            to_quad = quad_labels_map[rel_quad_idx2]\n",
    "            from_idxs = list(proxyDF.loc[(proxyDF[\"floor_id\"] == floor_id) & (proxyDF[\"quad\"]==from_quad),\"index\"]) \n",
    "            to_idxs = list(proxyDF.loc[(proxyDF[\"floor_id\"] == floor_id) & (proxyDF[\"quad\"]==to_quad),\"index\"]) \n",
    "            for i in from_idxs:\n",
    "                for j in to_idxs:\n",
    "                    quad_T[i, j] = quad_dist\n",
    "\n",
    "#print(\"quad matrix filled...\")\n",
    "\n",
    "#output for excel\n",
    "\n",
    "quads_DF = pd.DataFrame(quad_T)\n",
    "quads_DF.index = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "quads_DF = quads_DF.reset_index()\n",
    "quad_ids = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "new_cols = [\"quadrant_id\"] + quad_ids\n",
    "quads_DF.columns = new_cols\n",
    "\n",
    "####################################\n",
    "# \"REPMAT\" Quad Vectors - Horizontally\n",
    "####################################\n",
    "#print(\"translating quad distances horizontally... \")\n",
    "\n",
    "proxyDF = quadrant_idsDF\n",
    "quad_T2 = np.zeros((N,N))\n",
    "for floor_id, quad_distances in quads_dist_per_floor.items():\n",
    "    quad_dist = quad_distances[0]\n",
    "    quad_vector = np.array(quad_dist).reshape(len(quad_dist),1)\n",
    "    #print(\"REPMAT horizontally\", floor_id, \"num rows:\", len(quad_vector))\n",
    "    row_idxs = list(proxyDF.loc[(proxyDF[\"floor_id\"] == floor_id),\"index\"]) \n",
    "    col_idxs = row_idxs.copy()\n",
    "    min_idx, max_idx = min(col_idxs), max(col_idxs)\n",
    "    #load left \n",
    "    quad_T2[row_idxs, 0:min_idx] = quad_vector\n",
    "    #load right\n",
    "    quad_T2[row_idxs, max_idx+1:] = quad_vector\n",
    "\n",
    "#output for excel\n",
    "quads_DF2 = pd.DataFrame(quad_T2)\n",
    "quads_DF2.index = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "quads_DF2 = quads_DF2.reset_index()\n",
    "quad_ids = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "new_cols = [\"quadrant_id\"] + quad_ids\n",
    "quads_DF2.columns = new_cols\n",
    "\n",
    "#print(\"quad matrix HORIZONTAL translation matrix filled...\")\n",
    "\n",
    "####################################\n",
    "# \"REPMAT\" Quad Vectors - Vertical\n",
    "####################################\n",
    "#print(\"translating quads distances vertically... \")\n",
    "proxyDF = quadrant_idsDF\n",
    "quad_T3 = np.zeros((N,N))\n",
    "for floor_id, quad_distances in quads_dist_per_floor.items():\n",
    "    quad_dist = quad_distances[0]\n",
    "    quad_vector = np.array(quad_dist).reshape(1, len(quad_dist))\n",
    "    #print(\"REPMAT vertically\", floor_id, \"num rows:\", len(quad_vector))\n",
    "    row_idxs = list(proxyDF.loc[(proxyDF[\"floor_id\"] == floor_id),\"index\"]) \n",
    "    col_idxs = row_idxs.copy()\n",
    "    min_idx, max_idx = min(row_idxs), max(row_idxs)\n",
    "    #load up\n",
    "    quad_T3[0:min_idx, col_idxs] = quad_vector\n",
    "    #load right\n",
    "    quad_T3[max_idx+1:, col_idxs] = quad_vector\n",
    "\n",
    "#output for excel\n",
    "quads_DF3 = pd.DataFrame(quad_T3)\n",
    "quads_DF3.index = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "quads_DF3 = quads_DF3.reset_index()\n",
    "quad_ids = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "new_cols = [\"quadrant_id\"] + quad_ids\n",
    "quads_DF3.columns = new_cols\n",
    "\n",
    "#print(\"quad matrix VERTICAL translation matrix filled...\")\n",
    "\n",
    "##############################################################\n",
    "# Add \"Translate\" Quad Vectors - Vertical + Horizontal + Diagonal\n",
    "##############################################################\n",
    "\n",
    "quad_T4 = quad_T + quad_T2 + quad_T3\n",
    "\n",
    "#output for excel\n",
    "quads_DF4 = pd.DataFrame(quad_T4)\n",
    "quads_DF4.index = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "quads_DF4 = quads_DF4.reset_index()\n",
    "quad_ids = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "new_cols = [\"quadrant_id\"] + quad_ids\n",
    "quads_DF4.columns = new_cols\n",
    "\n",
    "\n",
    "##########>  TO DOs. \n",
    "# \n",
    "#               1) expand floor and quads vector (and/or affine transformation for final distance across floors quads)\n",
    "#\n",
    "#               2) calibrate quads coordinate (e.g. sqrt(2))\n",
    "#\n",
    "#               3) calibrate buildings, floors quads order of magnitude - bing map vs quads coordinates vs floor rel dist proportions according to CS needs (e.g. \"zoom in\" into quad vs \"zoom in\" floors, etc.) \n",
    "# \n",
    "###############################>>>\n",
    "\n",
    "bldg_boost = 1000\n",
    "floor_boost = 20\n",
    "quad_boost = 1\n",
    "\n",
    "final_T = bldg_T*bldg_boost + floor_T4*floor_boost + quad_T4*quad_boost + bldg_T* 5280\n",
    "\n",
    "#output for excel\n",
    "final_DF = pd.DataFrame(final_T)\n",
    "final_DF.index = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "final_DF = final_DF.reset_index()\n",
    "quad_ids = list(quadrant_idsDF[\"quadrant_id\"])\n",
    "new_cols = [\"quadrant_id\"] + quad_ids\n",
    "final_DF.columns = new_cols\n",
    "\n",
    "quad_ids_keys = quad_ids\n",
    "quad_ids_values = list(df_zones_STG['Space Name'])\n",
    "\n",
    "# using zip() \n",
    "# to convert lists to dictionary \n",
    "quad_rename_dict  = dict(zip(quad_ids_keys, quad_ids_values)) \n",
    "\n",
    "final_DF2 = final_DF.copy()\n",
    "# Remap the values of the dataframe \n",
    "final_DF2['quadrant_id']= final_DF2['quadrant_id'].map(quad_rename_dict) \n",
    "final_DF2 = final_DF2.rename(columns=quad_rename_dict)\n",
    "\n",
    "\n",
    "final_DF2.to_csv('distance.csv',index = False)\n",
    "\n",
    "\n",
    "\n",
    "#w2f.save()\n",
    "#clear_output()\n",
    "\n",
    "showdshp = widgets.Button(description ='Show details')\n",
    "hidedshp = widgets.Button(description ='Hide details')\n",
    "dshp_mkdn = widgets.Output()\n",
    "with dshp_mkdn:\n",
    "    display(Markdown('<b>Distance logging</b>'))\n",
    "display(dshp_mkdn)\n",
    "display(showdshp)\n",
    "\n",
    "def click_on_hide_showdshp_details(click):\n",
    "    clear_output()\n",
    "    display(dshp_mkdn)\n",
    "    display(showdshp)\n",
    "\n",
    "def click_on_show_showdshp_details(click):\n",
    "    clear_output()\n",
    "    display(dshp_mkdn)\n",
    "    display(hidedshp)\n",
    "#     print(\"Loading space catalog from... \")\n",
    "#     print(\"folder:\", input_path)\n",
    "#     print(\"file:\", input_file)\n",
    "\n",
    "#     print(\"--------------------------------------------------------\")\n",
    "#     for tab_name in xl1.sheet_names:\n",
    "#         temp = xl1.parse(tab_name).fillna(0)\n",
    "#         print(\"reading excel tab...\", tab_name,\" shape... \", temp.shape)\n",
    "#         tabs.append(temp.copy())\n",
    "\n",
    "    print(\"loading buildings...\")\n",
    "\n",
    "    for i in range(num_bldgs):\n",
    "        bldg_id = dfbuildingsDIST.loc[i,\"Building ID\"]\n",
    "#         bldg_id.index = bldg_id.index.astype(int) #use astype to convert to int\n",
    "        bldg_bmcRaw = dfbuildingsDIST.loc[i,\"Coordinates\"]\n",
    "        blat = dfbuildingsDIST.loc[i,\"lat\"]\n",
    "        blong = dfbuildingsDIST.loc[i,\"long\"]\n",
    "        print(bldg_id, \"coordinates found...\", blat, blong)\n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "\n",
    "    print(\"calculating building distance...\")\n",
    "\n",
    "    print(\"Everything good so far... \")\n",
    "    print(\"Calculated \" + str(len(bldg_distDF))+ \" distances across building and campus using Coordinates and Haversine formula\")\n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "\n",
    "    print(\"calculating floor to floor distances...\")\n",
    "    print(\"loading floor relative distances....\")\n",
    "\n",
    "\n",
    "    for i in range(num_bldgs):\n",
    "        bldg_id = dfbuildingsDIST.loc[i,\"Building ID\"]\n",
    "        num_floors = int(dfbuildingsDIST.loc[i,\"Number of Floors\"])\n",
    "        #removing optional column to input average distance as per @july 20, 2020 excel input file validation review meeting\n",
    "        #avg_dist = df1.loc[i,\"Average Distance per Floor\"] \n",
    "        avg_dist = 2\n",
    "        floor_rel_dist = list(dfbuildingsDIST.iloc[i,f_start_idx: f_start_idx + num_floors])\n",
    "        floor_rel_dist = [int(i) for i in floor_rel_dist] \n",
    "        print(bldg_id, \"found\", num_floors, \"floors rel dist: \", floor_rel_dist)\n",
    "\n",
    "\n",
    "\n",
    "    if fn:\n",
    "            floors_per_bldg[bldg_id] = fn.copy()\n",
    "    else:\n",
    "        print(\"wait....something went wrong....first tab... row:\", i)\n",
    "\n",
    "\n",
    "    print(\"number of buildings with floor dist:\", len(dist_per_floors), \"of\", len(floors_per_bldg) )\n",
    "\n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "\n",
    "    print(\"loading quadrant coordinates... from floors tab... \")\n",
    "\n",
    "    print(\"start of coordinates columns detected... column idx:\", start_idx)\n",
    "    print(\"validating coordinate columns vs number of zones... \")\n",
    "\n",
    "    for i in range(num_floors):\n",
    "        floor_id = df100.loc[i,\"Floor ID\"]\n",
    "        num_quads = int(df100.loc[i,\"Number of Zones\"])\n",
    "        coord_row = []\n",
    "        for j in range(int(num_quads)):\n",
    "            temp = df100.iloc[i, start_idx+j]\n",
    "            coord_row.append(temp)\n",
    "        if num_quads != len(coord_row):\n",
    "            print(floor_id, \"number of zones do not match\")\n",
    "            print(\"Excel Floor tab ERROR: Please enter one coordinate per each zone\")\n",
    "        else:\n",
    "            print(floor_id,\"found zone coordinates...\", coord_row)\n",
    "            quad_coord_per_floor[floor_id] = coord_row.copy() \n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "\n",
    "    print(\"calculating zone to zone distances...\")\n",
    "    print(\"number of floors with quads dist\", len(quads_dist_per_floor))\n",
    "    print(\"number of spaces loaded... \", len(quadrant_ids))\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "\n",
    "    print(\"New building matrix created... expanding to quad size... shape:\", new_building_expanded_matrix.shape )\n",
    "    print(\"adding building distances as per Coordinates...\")\n",
    "\n",
    "\n",
    "    print(\"building matrix filled...\")\n",
    "\n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "    print(\"New floors matrix created... expanding to quad size... shape:\", new_floors_expanded_matrix.shape )\n",
    "    print(\"adding floors distances as per buildings tab...\")\n",
    "    print(\"floor matrix filled...\")\n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "    print(\"translating floor distances horizontally... \")\n",
    "    print(\"Floor matrix HORIZONTAL translation matrix filled...\")\n",
    "    print(\"--------------------------------------------------------\")\n",
    "    for bldg_id, floor_distances in dist_per_floors.items():\n",
    "        row_idxs = list(proxyDF.loc[(proxyDF[\"building_id\"] == bldg_id),\"index\"]) \n",
    "        col_idxs = row_idxs.copy()\n",
    "        cv, rv = np.meshgrid(row_idxs, col_idxs)\n",
    "        temp = floor_T[rv, cv]\n",
    "        floor_dist = temp[[0],:]  #first row\n",
    "        floor_vector = floor_dist\n",
    "        print(\"REPMAT vertically\", bldg_id, \"num rows:\", len(floor_vector))\n",
    "\n",
    "\n",
    "    print(\"Floor matrix VERTICAL translation matrix filled...\")\n",
    "\n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "\n",
    "    print(\"New quad matrix created... expanding to quad size... shape:\", new_quad_expanded_matrix.shape )\n",
    "    print(\"adding quad distances as per coordinates in floors tab...\")\n",
    "\n",
    "    print(\"quad matrix filled...\")\n",
    "\n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "    print(\"translating quad distances horizontally... \")\n",
    "\n",
    "    for floor_id, quad_distances in quads_dist_per_floor.items():\n",
    "        quad_dist = quad_distances[0]\n",
    "        quad_vector = np.array(quad_dist).reshape(len(quad_dist),1)\n",
    "        print(\"REPMAT horizontally\", floor_id, \"num rows:\", len(quad_vector))\n",
    "\n",
    "\n",
    "    print(\"quad matrix HORIZONTAL translation matrix filled...\")\n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "\n",
    "    print(\"translating quads distances vertically... \")\n",
    "    for floor_id, quad_distances in quads_dist_per_floor.items():\n",
    "        quad_dist = quad_distances[0]\n",
    "        quad_vector = np.array(quad_dist).reshape(1, len(quad_dist))\n",
    "        print(\"REPMAT vertically\", floor_id, \"num rows:\", len(quad_vector))\n",
    "\n",
    "    print(\"quad matrix VERTICAL translation matrix filled...\")\n",
    "\n",
    "\n",
    "    print(\"--------------------------------------------------------\")\n",
    "\n",
    "\n",
    "\n",
    "showdshp.on_click(click_on_show_showdshp_details)\n",
    "hidedshp.on_click(click_on_hide_showdshp_details)\n",
    "\n",
    "# print(os.getcwd())\n",
    "# print(base_path)\n",
    "# print(base_path + out_filename)\n",
    "\n",
    "display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "savedshpfinsh = '<div class=\"alert alert-block alert-success\"><b> Done: </b> Your distant output file <b>(distance.csv)</b> has successfully saved back to location of this notebook.('+ os.getcwd() +')</div>'\n",
    "display(Markdown(savedshpfinsh))\n",
    "style = {'description_width': '300px'}\n",
    "layout = {'width': '225px'}\n",
    "startValidationsNB = widgets.Button(description ='Open Validations UI Notebook',style=style,layout=layout,button_style='info')\n",
    "display(startValidationsNB)\n",
    "\n",
    "def go_to_nextblock(btn):\n",
    "        display(Javascript('IPython.notebook.execute_cell_range(IPython.notebook.get_selected_index()+1, IPython.notebook.get_selected_index()+2)'))\n",
    "\n",
    "startValidationsNB.on_click(go_to_nextblock)           \n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%javascript\n",
    "var name_of_the_notebook = 'WPP.Input-File-Validations_PIPE-002_v2.ipynb'\n",
    "var url = window.location.href.split('/')\n",
    "var newurl = url[0] + '//'\n",
    "for (var i = 1; i < url.length - 1; i++) {\n",
    "    console.log(url[i], newurl)\n",
    "    newurl += url[i] + '/'\n",
    "}\n",
    "newurl += name_of_the_notebook\n",
    "window.open(newurl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}