SEN-Transport_flooding20-pre-project-scenario_2022-01-07.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Network analysis in Senegal\n",
    "\n",
    "### Objectives\n",
    "    1)\tUse measures of road-based accessibility to identify road segments that, if rehabilitated, would improve agricultural market activities in Senegal, including during flood conditions.\n",
    "    2)\tGain a better understanding of the accessibility, connectivity, and criticality of roads in Senegal in relationship to agricultural origins, processing & transfer sites, and markets.\n",
    "\n",
    "To this end, the team will develop an accessibility model which measures the travel time from sites of agricultural production to their nearest populated areas, processing centers, and markets. \n",
    "\n",
    "### Datasets for analysis\n",
    "#### ORIGIN\n",
    "    1) agriculture: MapSPAM 2017. Measuring value in international dollars.\n",
    "    2) agriculture: UMD Land Cover 2019 30m. Assign MapSPAM value onto land cover cropland class for more precise origin information.\n",
    "    3) population: WorldPop 2020, UN-adjusted.\n",
    "    4) settlement extent: GRID3 2020.\n",
    "#### DESTINATION\n",
    "    4) markets: derived from WorldPop 2020 and GRID3 2020 urban clusters.\n",
    "    5) agricultural processing hubs: to be acquired.\n",
    "#### TRAVEL ROUTE\n",
    "    6) roads: OpenStreetMap, July 2021.\n",
    "    7) elevation: \n",
    "#### OBSTACLE\n",
    "    8) flood: FATHOM. 1-in-10, 20, and 50 year flood return periods. These are combined pluvial and fluvial (undefended) flood layers whereby whichever flood level was higher was retained.\n",
    "#### INTERVENTION\n",
    "    9) upcoming road projects: AGEROUTE interventions separate from the World Bank-financed project\n",
    "    10) targeted road projects: critical road segments identified by this accessibility model's baseline outputs\n",
    "\n",
    "\n",
    "### Model design\n",
    "#### Basic formula: \n",
    "    (a) Off-road driving time from origin to closest road node\n",
    "    +\n",
    "    (b) Driving time from road node in (a) to a destination (closeness measured by road segments speeds)\n",
    "\n",
    "#### Model origin & destination (OD) sets:\n",
    "    A)\tTravel time from an area that has agricultural value/potential to the nearest processing hub (if provided).\n",
    "    B)\tTravel time from an area that has agricultural value/potential to the nearest larger settlement, (“larger” settlement identified using a case-appropriate population metric to be determined).\n",
    "    C)\tTravel time from an area that has agricultural value/potential to the nearest market.\n",
    "    D)\tTravel time from all settlements to the nearest market.\n",
    "    E)\tTravel time from larger settlements to the nearest market.\n",
    "\n",
    "#### Before/after scenarios for each OD set:\n",
    "    1)\tPre-project, baseline weather: No inclement weather. Road network status as of November 2021.\n",
    "    2)\tPre-project, flood: 1-in-10, 1-in-20 and 1-in-50 year flood return period. Road network status as of November 2021.\n",
    "    3)\tPost-project, baseline weather: No inclement weather. Road network status if X number of critical road segments to high-value areas are protected (i.e., their travel times reduced).\n",
    "    4)\tPost-project, flood: 1-in-10 year flood return period. Road network status if X number of critical road segments to high-value areas are protected (i.e., their travel times reduced).\n",
    "\n",
    "#### Notes:\n",
    "    --Destinations are expected to be proximal to the road network, so no measure is taken between road and destination.\n",
    "    --All travel times will be assigned to each model variation’s point of origin; the aggregation up to admin areas is possible if desired.\n",
    "    --Obstacles & interventions modify the road segment speeds. Basic formula is then applied to the modified road network.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prep workspace"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\wb527163\\\\GEO-Cdrive-Grace'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os, sys\n",
    "GISFolder = os.getcwd()\n",
    "GISFolder"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Note: needed to reinstall rtree due to geopandas import error. Did so in the console. \n",
    "# conda install -c conda-forge rtree=0.9.3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# load and filter osm network (step 1)\n",
    "import geopandas as gpd\n",
    "from geopandas import GeoDataFrame\n",
    "import pandas as pd\n",
    "import time\n",
    "sys.path.append(r\"C:\\Users\\wb527163\\.conda\\envs\\geo\\GOSTnets-master\")\n",
    "import GOSTnets as gn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import networkx as nx\n",
    "import osmnx as ox\n",
    "import numpy as np\n",
    "import rasterio as rt\n",
    "import shapely\n",
    "from shapely.geometry import Point, box, Polygon\n",
    "from shapely.ops import unary_union, linemerge, transform\n",
    "from shapely.wkt import loads\n",
    "from shapely import wkt\n",
    "from shapely.geometry import LineString, MultiLineString, Point\n",
    "import peartree"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#### Might not use these\n",
    "import fiona\n",
    "from osgeo import gdal\n",
    "import importlib\n",
    "import matplotlib.pyplot as plt\n",
    "import subprocess, glob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from GOSTnets import load_osm as losm\n",
    "import importlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\wb527163\\\\GEO-Cdrive-Grace\\\\SEN-Cdrive'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pth = os.path.join(GISFolder, \"SEN-Cdrive\") # Personal folder system for running model.\n",
    "pth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\wb527163\\\\GEO-Cdrive-Grace\\\\SEN-Cdrive\\\\outputs'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out_pth = os.path.join(GISFolder, \"SEN-Cdrive\\outputs\") # For storing intermediate outputs from the model.\n",
    "out_pth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'R:\\\\SEN\\\\GEO'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "team_pth = 'R:\\\\SEN\\\\GEO' # This is where the unmodified input data is stored. Finalized outputs also housed here.\n",
    "team_pth"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare OSM driving network.\n",
    "Travel measured in length (meters)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Ensure all targeted roads are changed to tertiary. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'geopandas.geodataframe.GeoDataFrame'>\n",
      "RangeIndex: 2459356 entries, 0 to 2459355\n",
      "Data columns (total 4 columns):\n",
      " #   Column        Dtype   \n",
      "---  ------        -----   \n",
      " 0   PFU_1in50     float64 \n",
      " 1   Shape_Length  float64 \n",
      " 2   Shape_Area    float64 \n",
      " 3   geometry      geometry\n",
      "dtypes: float64(3), geometry(1)\n",
      "memory usage: 75.1 MB\n"
     ]
    }
   ],
   "source": [
    "flood50 = gpd.read_file(\"C:/Users/wb527163/GEO-Cdrive-Grace/SEN-Cdrive/scratch.gdb\", layer=\"PFU_1in50\")\n",
    "flood50.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "gTime = nx.read_gpickle(\"SEN-Cdrive/outputs/gTime_pre-project.pickle\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "edges = gn.edge_gdf_from_graph(gTime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\wb527163\\.conda\\envs\\geo\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3444: DtypeWarning: Columns (3) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2871182 entries, 0 to 2871181\n",
      "Data columns (total 7 columns):\n",
      " #   Column      Dtype  \n",
      "---  ------      -----  \n",
      " 0   Unnamed: 0  int64  \n",
      " 1   node_ID     int64  \n",
      " 2   highway     object \n",
      " 3   ref         object \n",
      " 4   y           float64\n",
      " 5   x           float64\n",
      " 6   geometry    object \n",
      "dtypes: float64(2), int64(2), object(3)\n",
      "memory usage: 153.3+ MB\n",
      "None\n",
      "<class 'geopandas.geodataframe.GeoDataFrame'>\n",
      "RangeIndex: 6203383 entries, 0 to 6203382\n",
      "Data columns (total 22 columns):\n",
      " #   Column      Dtype   \n",
      "---  ------      -----   \n",
      " 0   stnode      int64   \n",
      " 1   endnode     int64   \n",
      " 2   name        object  \n",
      " 3   access      object  \n",
      " 4   maxspeed    object  \n",
      " 5   length      float64 \n",
      " 6   bridge      object  \n",
      " 7   time        float64 \n",
      " 8   oneway      bool    \n",
      " 9   lanes       float64 \n",
      " 10  tunnel      object  \n",
      " 11  area        object  \n",
      " 12  landuse     object  \n",
      " 13  service     object  \n",
      " 14  ref         object  \n",
      " 15  mode        object  \n",
      " 16  Unnamed: 0  int64   \n",
      " 17  junction    object  \n",
      " 18  highway     object  \n",
      " 19  width       object  \n",
      " 20  osmid       int64   \n",
      " 21  geometry    geometry\n",
      "dtypes: bool(1), float64(3), geometry(1), int64(4), object(13)\n",
      "memory usage: 999.8+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "nodes = os.path.join(out_pth, \"gTime_node_pre-project.csv\")\n",
    "nodes = pd.read_csv(nodes)\n",
    "print(nodes.info())\n",
    "print(edges.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Spatial join should be on projected GDFs.\n",
    "edges = edges.to_crs(\"EPSG:31028\")\n",
    "flood50 = flood50.to_crs(\"EPSG:31028\")\n",
    "edges.crs == flood50.crs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'geopandas.geodataframe.GeoDataFrame'>\n",
      "Int64Index: 6508966 entries, 0 to 6203382\n",
      "Data columns (total 26 columns):\n",
      " #   Column        Dtype   \n",
      "---  ------        -----   \n",
      " 0   stnode        int64   \n",
      " 1   endnode       int64   \n",
      " 2   name          object  \n",
      " 3   access        object  \n",
      " 4   maxspeed      object  \n",
      " 5   length        float64 \n",
      " 6   bridge        object  \n",
      " 7   time          float64 \n",
      " 8   oneway        bool    \n",
      " 9   lanes         float64 \n",
      " 10  tunnel        object  \n",
      " 11  area          object  \n",
      " 12  landuse       object  \n",
      " 13  service       object  \n",
      " 14  ref           object  \n",
      " 15  mode          object  \n",
      " 16  Unnamed: 0    int64   \n",
      " 17  junction      object  \n",
      " 18  highway       object  \n",
      " 19  width         object  \n",
      " 20  osmid         int64   \n",
      " 21  geometry      geometry\n",
      " 22  index_right   float64 \n",
      " 23  PFU_1in50     float64 \n",
      " 24  Shape_Length  float64 \n",
      " 25  Shape_Area    float64 \n",
      "dtypes: bool(1), float64(7), geometry(1), int64(4), object(13)\n",
      "memory usage: 1.3+ GB\n",
      "None\n",
      "             stnode     endnode   name access maxspeed  length bridge  \\\n",
      "0         358284990  5217543379  D 523    NaN      NaN  33.127    NaN   \n",
      "1         358284990  1888282175    NaN    NaN      NaN  12.832    NaN   \n",
      "2         358284990  5329792467    NaN    NaN      NaN  48.781    NaN   \n",
      "3        5217543379   358284990  D 523    NaN      NaN  33.127    NaN   \n",
      "4        5217543379  5329928981  D 523    NaN      NaN  17.124    NaN   \n",
      "...             ...         ...    ...    ...      ...     ...    ...   \n",
      "6203378  9165665162  9165665161    NaN    NaN      NaN   5.476    NaN   \n",
      "6203379  9201905615  9201918817    NaN    NaN      NaN   3.418    NaN   \n",
      "6203380  9201905615  9201918818    NaN    NaN      NaN   7.045    NaN   \n",
      "6203381  9201918818  9201905615    NaN    NaN      NaN   7.045    NaN   \n",
      "6203382  9201918818  9201905616    NaN    NaN      NaN  14.255    yes   \n",
      "\n",
      "             time  oneway  lanes  ... Unnamed: 0 junction       highway width  \\\n",
      "0        2.385144   False    NaN  ...          0      NaN  unclassified   NaN   \n",
      "1        0.769920   False    NaN  ...          1      NaN      tertiary   NaN   \n",
      "2        2.926860   False    NaN  ...          2      NaN      tertiary   NaN   \n",
      "3        2.385144   False    NaN  ...      31397      NaN  unclassified   NaN   \n",
      "4        1.232928   False    NaN  ...      31398      NaN  unclassified   NaN   \n",
      "...           ...     ...    ...  ...        ...      ...           ...   ...   \n",
      "6203378  4.380800   False    NaN  ...    6203362      NaN          path   NaN   \n",
      "6203379  0.307620   False    NaN  ...    6203363      NaN   residential   NaN   \n",
      "6203380  0.634050   False    NaN  ...    6203364      NaN   residential   NaN   \n",
      "6203381  0.634050   False    NaN  ...    6203369      NaN   residential   NaN   \n",
      "6203382  1.282950   False    NaN  ...    6203370      NaN   residential   NaN   \n",
      "\n",
      "             osmid                                           geometry  \\\n",
      "0         59618174  LINESTRING (790871.607 1370083.257, 790848.290...   \n",
      "1        178482063  LINESTRING (790871.607 1370083.257, 790867.287...   \n",
      "2        178482063  LINESTRING (790871.607 1370083.257, 790904.086...   \n",
      "3         59618174  LINESTRING (790848.290 1370106.724, 790871.607...   \n",
      "4         59618174  LINESTRING (790848.290 1370106.724, 790838.054...   \n",
      "...            ...                                                ...   \n",
      "6203378  992024621  LINESTRING (232312.244 1631600.542, 232307.060...   \n",
      "6203379  996565464  LINESTRING (231437.078 1630744.554, 231433.991...   \n",
      "6203380  996565464  LINESTRING (231437.078 1630744.554, 231442.229...   \n",
      "6203381  996565464  LINESTRING (231442.229 1630739.759, 231437.078...   \n",
      "6203382  996565465  LINESTRING (231442.229 1630739.759, 231444.822...   \n",
      "\n",
      "         index_right PFU_1in50 Shape_Length Shape_Area  \n",
      "0                NaN       NaN          NaN        NaN  \n",
      "1                NaN       NaN          NaN        NaN  \n",
      "2                NaN       NaN          NaN        NaN  \n",
      "3                NaN       NaN          NaN        NaN  \n",
      "4                NaN       NaN          NaN        NaN  \n",
      "...              ...       ...          ...        ...  \n",
      "6203378          NaN       NaN          NaN        NaN  \n",
      "6203379          NaN       NaN          NaN        NaN  \n",
      "6203380          NaN       NaN          NaN        NaN  \n",
      "6203381          NaN       NaN          NaN        NaN  \n",
      "6203382          NaN       NaN          NaN        NaN  \n",
      "\n",
      "[6508966 rows x 26 columns]\n"
     ]
    }
   ],
   "source": [
    "floodjoin = gpd.sjoin_nearest(edges, flood50, how=\"left\", max_distance=3) \n",
    "print(floodjoin.info())\n",
    "print(floodjoin)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "No flood crossing at node: 5745481 locations\n",
      "\n",
      "Flood crossing at node: 763485 locations\n",
      "\n",
      "\n",
      "Percent flooded: 11.729743249542247 percent out of 6508966 possible locations\n"
     ]
    }
   ],
   "source": [
    "# How many nodes experienced flooding?\n",
    "pc_flooded = floodjoin[\"PFU_1in50\"].count() / len(floodjoin) * 100\n",
    "\n",
    "print(\"No flood crossing at node:\", floodjoin[\"PFU_1in50\"].isnull().sum(), \"locations\", end=\"\\n\\n\")\n",
    "print(\"Flood crossing at node:\", floodjoin[\"PFU_1in50\"].count(), \"locations\", end=\"\\n\\n\")\n",
    "print(\"\\nPercent flooded:\", pc_flooded, \"percent\", \"out of\", len(floodjoin), \"possible locations\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'geopandas.geodataframe.GeoDataFrame'>\n",
      "Int64Index: 6508966 entries, 0 to 6203382\n",
      "Data columns (total 8 columns):\n",
      " #   Column     Dtype   \n",
      "---  ------     -----   \n",
      " 0   stnode     int64   \n",
      " 1   endnode    int64   \n",
      " 2   time       float64 \n",
      " 3   length     float64 \n",
      " 4   highway    object  \n",
      " 5   osmid      int64   \n",
      " 6   geometry   geometry\n",
      " 7   PFU_1in50  float64 \n",
      "dtypes: float64(3), geometry(1), int64(3), object(1)\n",
      "memory usage: 446.9+ MB\n"
     ]
    }
   ],
   "source": [
    "floodjoin = floodjoin[['stnode', 'endnode', 'time', 'length', 'highway', 'osmid', 'geometry', 'PFU_1in50']]\n",
    "floodjoin = floodjoin.to_crs(\"EPSG:4326\")\n",
    "floodjoin.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fewer errors farther down when using dataframe instead of gdf\n",
    "floodjoin = pd.DataFrame(floodjoin)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save progress.\n",
    "floodjoin.to_csv(os.path.join(out_pth, 'gTime_edge_pre-project_flood50.csv'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create speed penalties.\n",
    "Note: Flood depths are in centimeters. FATHOM uses meters, but conversion process to vector required some finessing. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GISFolder', 'GeoDataFrame', 'In', 'LineString', 'MultiLineString', 'Out', 'Point', 'Polygon', '_', '_1', '_14', '_7', '_8', '_9', '__', '___', '__builtin__', '__builtins__', '__doc__', '__loader__', '__name__', '__package__', '__spec__', '_dh', '_i', '_i1', '_i10', '_i11', '_i12', '_i13', '_i14', '_i15', '_i16', '_i17', '_i18', '_i19', '_i2', '_i20', '_i3', '_i4', '_i5', '_i6', '_i7', '_i8', '_i9', '_ih', '_ii', '_iii', '_oh', 'box', 'edges', 'exit', 'fiona', 'flood50', 'floodjoin', 'gTime', 'gdal', 'get_ipython', 'glob', 'gn', 'gpd', 'importlib', 'linemerge', 'loads', 'losm', 'nodes', 'np', 'nx', 'os', 'out_pth', 'ox', 'pc_flooded', 'pd', 'peartree', 'plt', 'pth', 'quit', 'rt', 'shapely', 'subprocess', 'sys', 'team_pth', 'time', 'transform', 'unary_union', 'wkt']\n"
     ]
    }
   ],
   "source": [
    "print(dir())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GISFolder', 'GeoDataFrame', 'In', 'LineString', 'MultiLineString', 'Out', 'Point', 'Polygon', '_', '_1', '_14', '_7', '_8', '_9', '__', '___', '__builtin__', '__builtins__', '__doc__', '__loader__', '__name__', '__package__', '__spec__', '_dh', '_i', '_i1', '_i10', '_i11', '_i12', '_i13', '_i14', '_i15', '_i16', '_i17', '_i18', '_i19', '_i2', '_i20', '_i21', '_i3', '_i4', '_i5', '_i6', '_i7', '_i8', '_i9', '_ih', '_ii', '_iii', '_oh', 'box', 'exit', 'fiona', 'gdal', 'get_ipython', 'glob', 'gn', 'gpd', 'importlib', 'linemerge', 'loads', 'losm', 'nodes', 'np', 'nx', 'os', 'out_pth', 'ox', 'pc_flooded', 'pd', 'peartree', 'plt', 'pth', 'quit', 'rt', 'shapely', 'subprocess', 'sys', 'team_pth', 'time', 'transform', 'unary_union', 'wkt']\n"
     ]
    }
   ],
   "source": [
    "del flood50, gTime, edges, floodjoin\n",
    "print(dir())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Alternatively, restart the kernel and reload.\n",
    "floodjoin = os.path.join(out_pth, \"gTime_edge_pre-project_flood50.csv\")\n",
    "floodjoin = pd.read_csv(floodjoin)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>stnode</th>\n",
       "      <th>endnode</th>\n",
       "      <th>time</th>\n",
       "      <th>length</th>\n",
       "      <th>highway</th>\n",
       "      <th>osmid</th>\n",
       "      <th>geometry</th>\n",
       "      <th>PFU_1in50</th>\n",
       "      <th>t50</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>358284990</td>\n",
       "      <td>5217543379</td>\n",
       "      <td>2.385144</td>\n",
       "      <td>33.127</td>\n",
       "      <td>unclassified</td>\n",
       "      <td>59618174</td>\n",
       "      <td>LINESTRING (-12.323470812149301 12.38118950295...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>358284990</td>\n",
       "      <td>1888282175</td>\n",
       "      <td>0.769920</td>\n",
       "      <td>12.832</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>178482063</td>\n",
       "      <td>LINESTRING (-12.323470812149301 12.38118950295...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>358284990</td>\n",
       "      <td>5329792467</td>\n",
       "      <td>2.926860</td>\n",
       "      <td>48.781</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>178482063</td>\n",
       "      <td>LINESTRING (-12.323470812149301 12.38118950295...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5217543379</td>\n",
       "      <td>358284990</td>\n",
       "      <td>2.385144</td>\n",
       "      <td>33.127</td>\n",
       "      <td>unclassified</td>\n",
       "      <td>59618174</td>\n",
       "      <td>LINESTRING (-12.323682912149085 12.38140360295...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5217543379</td>\n",
       "      <td>5329928981</td>\n",
       "      <td>1.232928</td>\n",
       "      <td>17.124</td>\n",
       "      <td>unclassified</td>\n",
       "      <td>59618174</td>\n",
       "      <td>LINESTRING (-12.323682912149085 12.38140360295...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</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>6508961</th>\n",
       "      <td>6203378</td>\n",
       "      <td>9165665162</td>\n",
       "      <td>9165665161</td>\n",
       "      <td>4.380800</td>\n",
       "      <td>5.476</td>\n",
       "      <td>path</td>\n",
       "      <td>992024621</td>\n",
       "      <td>LINESTRING (-17.484701707370633 14.74572550266...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508962</th>\n",
       "      <td>6203379</td>\n",
       "      <td>9201905615</td>\n",
       "      <td>9201918817</td>\n",
       "      <td>0.307620</td>\n",
       "      <td>3.418</td>\n",
       "      <td>residential</td>\n",
       "      <td>996565464</td>\n",
       "      <td>LINESTRING (-17.492736907366183 14.73790610266...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508963</th>\n",
       "      <td>6203380</td>\n",
       "      <td>9201905615</td>\n",
       "      <td>9201918818</td>\n",
       "      <td>0.634050</td>\n",
       "      <td>7.045</td>\n",
       "      <td>residential</td>\n",
       "      <td>996565464</td>\n",
       "      <td>LINESTRING (-17.492736907366183 14.73790610266...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508964</th>\n",
       "      <td>6203381</td>\n",
       "      <td>9201918818</td>\n",
       "      <td>9201905615</td>\n",
       "      <td>0.634050</td>\n",
       "      <td>7.045</td>\n",
       "      <td>residential</td>\n",
       "      <td>996565464</td>\n",
       "      <td>LINESTRING (-17.492688607366233 14.73786330266...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508965</th>\n",
       "      <td>6203382</td>\n",
       "      <td>9201918818</td>\n",
       "      <td>9201905616</td>\n",
       "      <td>1.282950</td>\n",
       "      <td>14.255</td>\n",
       "      <td>residential</td>\n",
       "      <td>996565465</td>\n",
       "      <td>LINESTRING (-17.492688607366233 14.73786330266...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6508966 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Unnamed: 0      stnode     endnode      time  length       highway  \\\n",
       "0                 0   358284990  5217543379  2.385144  33.127  unclassified   \n",
       "1                 1   358284990  1888282175  0.769920  12.832      tertiary   \n",
       "2                 2   358284990  5329792467  2.926860  48.781      tertiary   \n",
       "3                 3  5217543379   358284990  2.385144  33.127  unclassified   \n",
       "4                 4  5217543379  5329928981  1.232928  17.124  unclassified   \n",
       "...             ...         ...         ...       ...     ...           ...   \n",
       "6508961     6203378  9165665162  9165665161  4.380800   5.476          path   \n",
       "6508962     6203379  9201905615  9201918817  0.307620   3.418   residential   \n",
       "6508963     6203380  9201905615  9201918818  0.634050   7.045   residential   \n",
       "6508964     6203381  9201918818  9201905615  0.634050   7.045   residential   \n",
       "6508965     6203382  9201918818  9201905616  1.282950  14.255   residential   \n",
       "\n",
       "             osmid                                           geometry  \\\n",
       "0         59618174  LINESTRING (-12.323470812149301 12.38118950295...   \n",
       "1        178482063  LINESTRING (-12.323470812149301 12.38118950295...   \n",
       "2        178482063  LINESTRING (-12.323470812149301 12.38118950295...   \n",
       "3         59618174  LINESTRING (-12.323682912149085 12.38140360295...   \n",
       "4         59618174  LINESTRING (-12.323682912149085 12.38140360295...   \n",
       "...            ...                                                ...   \n",
       "6508961  992024621  LINESTRING (-17.484701707370633 14.74572550266...   \n",
       "6508962  996565464  LINESTRING (-17.492736907366183 14.73790610266...   \n",
       "6508963  996565464  LINESTRING (-17.492736907366183 14.73790610266...   \n",
       "6508964  996565464  LINESTRING (-17.492688607366233 14.73786330266...   \n",
       "6508965  996565465  LINESTRING (-17.492688607366233 14.73786330266...   \n",
       "\n",
       "         PFU_1in50  t50  \n",
       "0             -1.0  1.0  \n",
       "1             -1.0  1.0  \n",
       "2             -1.0  1.0  \n",
       "3             -1.0  1.0  \n",
       "4             -1.0  1.0  \n",
       "...            ...  ...  \n",
       "6508961       -1.0  1.0  \n",
       "6508962       -1.0  1.0  \n",
       "6508963       -1.0  1.0  \n",
       "6508964       -1.0  1.0  \n",
       "6508965       -1.0  1.0  \n",
       "\n",
       "[6508966 rows x 10 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Give a depth to the nodes that don't cross a flood point. \n",
    "floodjoin.loc[floodjoin['PFU_1in50'].isnull(), 'PFU_1in50'] = -1\n",
    "floodjoin[\"t50\"] = 1 # This is the penalty column.\n",
    "floodjoin.loc[floodjoin['PFU_1in50'] <= 5, 't50'] = 1 # Where no flood crosses, keep the default value (no penalty).\n",
    "floodjoin.loc[(floodjoin['PFU_1in50'] > 5) & (floodjoin['PFU_1in50'] <= 10), 't50'] = 1.25 # Between 10-30cm flooding, increase travel time by 1.25.\n",
    "floodjoin.loc[(floodjoin['PFU_1in50'] > 10) & (floodjoin['PFU_1in50'] <= 15), 't50'] = 2\n",
    "floodjoin.loc[(floodjoin['PFU_1in50'] > 15) & (floodjoin['PFU_1in50'] <= 40), 't50'] = 5\n",
    "floodjoin.loc[(floodjoin['PFU_1in50'] > 40), 't50'] = 9999\n",
    "floodjoin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>stnode</th>\n",
       "      <th>endnode</th>\n",
       "      <th>time</th>\n",
       "      <th>length</th>\n",
       "      <th>highway</th>\n",
       "      <th>osmid</th>\n",
       "      <th>geometry</th>\n",
       "      <th>PFU_1in50</th>\n",
       "      <th>t50</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>358284990</td>\n",
       "      <td>5217543379</td>\n",
       "      <td>2.385144</td>\n",
       "      <td>33.127</td>\n",
       "      <td>unclassified</td>\n",
       "      <td>59618174</td>\n",
       "      <td>LINESTRING (-12.323470812149301 12.38118950295...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>2.385144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>358284990</td>\n",
       "      <td>1888282175</td>\n",
       "      <td>0.769920</td>\n",
       "      <td>12.832</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>178482063</td>\n",
       "      <td>LINESTRING (-12.323470812149301 12.38118950295...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.769920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>358284990</td>\n",
       "      <td>5329792467</td>\n",
       "      <td>2.926860</td>\n",
       "      <td>48.781</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>178482063</td>\n",
       "      <td>LINESTRING (-12.323470812149301 12.38118950295...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>2.926860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>5217543379</td>\n",
       "      <td>358284990</td>\n",
       "      <td>2.385144</td>\n",
       "      <td>33.127</td>\n",
       "      <td>unclassified</td>\n",
       "      <td>59618174</td>\n",
       "      <td>LINESTRING (-12.323682912149085 12.38140360295...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>2.385144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>5217543379</td>\n",
       "      <td>5329928981</td>\n",
       "      <td>1.232928</td>\n",
       "      <td>17.124</td>\n",
       "      <td>unclassified</td>\n",
       "      <td>59618174</td>\n",
       "      <td>LINESTRING (-12.323682912149085 12.38140360295...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.232928</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>6508961</th>\n",
       "      <td>6203378</td>\n",
       "      <td>9165665162</td>\n",
       "      <td>9165665161</td>\n",
       "      <td>4.380800</td>\n",
       "      <td>5.476</td>\n",
       "      <td>path</td>\n",
       "      <td>992024621</td>\n",
       "      <td>LINESTRING (-17.484701707370633 14.74572550266...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>4.380800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508962</th>\n",
       "      <td>6203379</td>\n",
       "      <td>9201905615</td>\n",
       "      <td>9201918817</td>\n",
       "      <td>0.307620</td>\n",
       "      <td>3.418</td>\n",
       "      <td>residential</td>\n",
       "      <td>996565464</td>\n",
       "      <td>LINESTRING (-17.492736907366183 14.73790610266...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.307620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508963</th>\n",
       "      <td>6203380</td>\n",
       "      <td>9201905615</td>\n",
       "      <td>9201918818</td>\n",
       "      <td>0.634050</td>\n",
       "      <td>7.045</td>\n",
       "      <td>residential</td>\n",
       "      <td>996565464</td>\n",
       "      <td>LINESTRING (-17.492736907366183 14.73790610266...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.634050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508964</th>\n",
       "      <td>6203381</td>\n",
       "      <td>9201918818</td>\n",
       "      <td>9201905615</td>\n",
       "      <td>0.634050</td>\n",
       "      <td>7.045</td>\n",
       "      <td>residential</td>\n",
       "      <td>996565464</td>\n",
       "      <td>LINESTRING (-17.492688607366233 14.73786330266...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0.634050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508965</th>\n",
       "      <td>6203382</td>\n",
       "      <td>9201918818</td>\n",
       "      <td>9201905616</td>\n",
       "      <td>1.282950</td>\n",
       "      <td>14.255</td>\n",
       "      <td>residential</td>\n",
       "      <td>996565465</td>\n",
       "      <td>LINESTRING (-17.492688607366233 14.73786330266...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>1.282950</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6508966 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Unnamed: 0      stnode     endnode      time  length       highway  \\\n",
       "0                 0   358284990  5217543379  2.385144  33.127  unclassified   \n",
       "1                 1   358284990  1888282175  0.769920  12.832      tertiary   \n",
       "2                 2   358284990  5329792467  2.926860  48.781      tertiary   \n",
       "3                 3  5217543379   358284990  2.385144  33.127  unclassified   \n",
       "4                 4  5217543379  5329928981  1.232928  17.124  unclassified   \n",
       "...             ...         ...         ...       ...     ...           ...   \n",
       "6508961     6203378  9165665162  9165665161  4.380800   5.476          path   \n",
       "6508962     6203379  9201905615  9201918817  0.307620   3.418   residential   \n",
       "6508963     6203380  9201905615  9201918818  0.634050   7.045   residential   \n",
       "6508964     6203381  9201918818  9201905615  0.634050   7.045   residential   \n",
       "6508965     6203382  9201918818  9201905616  1.282950  14.255   residential   \n",
       "\n",
       "             osmid                                           geometry  \\\n",
       "0         59618174  LINESTRING (-12.323470812149301 12.38118950295...   \n",
       "1        178482063  LINESTRING (-12.323470812149301 12.38118950295...   \n",
       "2        178482063  LINESTRING (-12.323470812149301 12.38118950295...   \n",
       "3         59618174  LINESTRING (-12.323682912149085 12.38140360295...   \n",
       "4         59618174  LINESTRING (-12.323682912149085 12.38140360295...   \n",
       "...            ...                                                ...   \n",
       "6508961  992024621  LINESTRING (-17.484701707370633 14.74572550266...   \n",
       "6508962  996565464  LINESTRING (-17.492736907366183 14.73790610266...   \n",
       "6508963  996565464  LINESTRING (-17.492736907366183 14.73790610266...   \n",
       "6508964  996565464  LINESTRING (-17.492688607366233 14.73786330266...   \n",
       "6508965  996565465  LINESTRING (-17.492688607366233 14.73786330266...   \n",
       "\n",
       "         PFU_1in50       t50  \n",
       "0             -1.0  2.385144  \n",
       "1             -1.0  0.769920  \n",
       "2             -1.0  2.926860  \n",
       "3             -1.0  2.385144  \n",
       "4             -1.0  1.232928  \n",
       "...            ...       ...  \n",
       "6508961       -1.0  4.380800  \n",
       "6508962       -1.0  0.307620  \n",
       "6508963       -1.0  0.634050  \n",
       "6508964       -1.0  0.634050  \n",
       "6508965       -1.0  1.282950  \n",
       "\n",
       "[6508966 rows x 10 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Turn the penalty column into a flood-affected time column.\n",
    "floodjoin['t50'] = floodjoin['t50'] * floodjoin['time']\n",
    "floodjoin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save progress.\n",
    "floodjoin.to_csv(os.path.join(out_pth, 'gTime_edge_pre-project_flood50.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Travel time without inclement weather\n",
      "\n",
      "Mean: 7.464243950039652\n",
      "Median: 3.2853600000000003\n",
      "\n",
      "\n",
      "\n",
      "During 1 in 50-year flood conditions\n",
      "\n",
      "Mean: 8461.404240362535\n",
      "Median: 3.5988685714285715\n"
     ]
    }
   ],
   "source": [
    "print(\"Travel time without inclement weather\\n\")\n",
    "print(\"Mean:\", floodjoin['time'].mean(), end=\"\\n\")\n",
    "print(\"Median:\", floodjoin['time'].median(), end='\\n\\n')\n",
    "print(\"\\n\\nDuring 1 in 50-year flood conditions\\n\")\n",
    "print(\"Mean:\", floodjoin['t50'].mean(), end=\"\\n\") \n",
    "print(\"Median:\", floodjoin['t50'].median()) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Convert back to graph object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GISFolder', 'GeoDataFrame', 'In', 'LineString', 'MultiLineString', 'Out', 'Point', 'Polygon', '_', '_1', '_14', '_23', '_24', '_7', '_8', '_9', '__', '___', '__builtin__', '__builtins__', '__doc__', '__loader__', '__name__', '__package__', '__spec__', '_dh', '_i', '_i1', '_i10', '_i11', '_i12', '_i13', '_i14', '_i15', '_i16', '_i17', '_i18', '_i19', '_i2', '_i20', '_i21', '_i22', '_i23', '_i24', '_i25', '_i26', '_i27', '_i3', '_i4', '_i5', '_i6', '_i7', '_i8', '_i9', '_ih', '_ii', '_iii', '_oh', 'box', 'exit', 'fiona', 'gdal', 'get_ipython', 'glob', 'gn', 'gpd', 'importlib', 'linemerge', 'loads', 'losm', 'np', 'nx', 'os', 'out_pth', 'ox', 'pc_flooded', 'pd', 'peartree', 'plt', 'pth', 'quit', 'rt', 'shapely', 'subprocess', 'sys', 'team_pth', 'time', 'transform', 'unary_union', 'wkt']\n"
     ]
    }
   ],
   "source": [
    "# Converting back to graph can cause memory errors. Suggested to restart the kernel and reload the nodes and revised edges at this point.\n",
    "del floodjoin, nodes\n",
    "print(dir())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\wb527163\\.conda\\envs\\geo\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3444: DtypeWarning: Columns (3) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2871182 entries, 0 to 2871181\n",
      "Data columns (total 7 columns):\n",
      " #   Column      Dtype  \n",
      "---  ------      -----  \n",
      " 0   Unnamed: 0  int64  \n",
      " 1   node_ID     int64  \n",
      " 2   highway     object \n",
      " 3   ref         object \n",
      " 4   y           float64\n",
      " 5   x           float64\n",
      " 6   geometry    object \n",
      "dtypes: float64(2), int64(2), object(3)\n",
      "memory usage: 153.3+ MB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6508966 entries, 0 to 6508965\n",
      "Data columns (total 11 columns):\n",
      " #   Column        Dtype  \n",
      "---  ------        -----  \n",
      " 0   Unnamed: 0    int64  \n",
      " 1   Unnamed: 0.1  int64  \n",
      " 2   stnode        int64  \n",
      " 3   endnode       int64  \n",
      " 4   time          float64\n",
      " 5   length        float64\n",
      " 6   highway       object \n",
      " 7   osmid         int64  \n",
      " 8   geometry      object \n",
      " 9   PFU_1in50     float64\n",
      " 10  t50           float64\n",
      "dtypes: float64(4), int64(5), object(2)\n",
      "memory usage: 546.3+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "nodes = os.path.join(out_pth, \"gTime_node_pre-project.csv\")\n",
    "nodes = pd.read_csv(nodes)\n",
    "floodjoin = os.path.join(out_pth, \"gTime_edge_pre-project_flood50.csv\")\n",
    "floodjoin = pd.read_csv(floodjoin)\n",
    "print(nodes.info())\n",
    "print(floodjoin.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "start: Fri Jan  7 23:17:29 2022\n",
      "\n",
      "(358284990, 5217543379, {'geometry': <shapely.geometry.linestring.LineString object at 0x0000021417A4E820>, 'Unnamed: 0': 0, 'Unnamed: 0.1': 0, 'time': 2.3851440000000004, 'length': 33.127, 'highway': 'unclassified', 'osmid': 59618174, 'PFU_1in50': -1.0, 't50': 2.3851440000000004})\n",
      "(358284990, 1888282175, {'geometry': <shapely.geometry.linestring.LineString object at 0x0000021417A4EBE0>, 'Unnamed: 0': 1, 'Unnamed: 0.1': 1, 'time': 0.76992, 'length': 12.832, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 0.76992})\n",
      "(358284990, 5329792467, {'geometry': <shapely.geometry.linestring.LineString object at 0x0000021417A4E6A0>, 'Unnamed: 0': 2, 'Unnamed: 0.1': 2, 'time': 2.92686, 'length': 48.781, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 2.92686})\n",
      "(5217543379, 358284990, {'geometry': <shapely.geometry.linestring.LineString object at 0x0000021417A4E310>, 'Unnamed: 0': 3, 'Unnamed: 0.1': 3, 'time': 2.3851440000000004, 'length': 33.127, 'highway': 'unclassified', 'osmid': 59618174, 'PFU_1in50': -1.0, 't50': 2.3851440000000004})\n",
      "(5217543379, 5329928981, {'geometry': <shapely.geometry.linestring.LineString object at 0x0000021417A4E2E0>, 'Unnamed: 0': 4, 'Unnamed: 0.1': 4, 'time': 1.232928, 'length': 17.124, 'highway': 'unclassified', 'osmid': 59618174, 'PFU_1in50': -1.0, 't50': 1.232928})\n",
      "(1888282175, 1888282174, {'geometry': <shapely.geometry.linestring.LineString object at 0x0000021417A4E2B0>, 'Unnamed: 0': 5, 'Unnamed: 0.1': 5, 'time': 0.9322200000000002, 'length': 15.537, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 0.9322200000000002})\n",
      "(1888282175, 358284990, {'geometry': <shapely.geometry.linestring.LineString object at 0x0000021417A4E280>, 'Unnamed: 0': 6, 'Unnamed: 0.1': 6, 'time': 0.76992, 'length': 12.832, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 0.76992})\n",
      "(5329792467, 358284990, {'geometry': <shapely.geometry.linestring.LineString object at 0x0000021417A4E250>, 'Unnamed: 0': 7, 'Unnamed: 0.1': 7, 'time': 2.92686, 'length': 48.781, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 2.92686})\n",
      "(5329792467, 1888282177, {'geometry': <shapely.geometry.linestring.LineString object at 0x0000021417A4EB80>, 'Unnamed: 0': 8, 'Unnamed: 0.1': 8, 'time': 2.7629999999999995, 'length': 46.05, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 2.7629999999999995})\n",
      "(5329928981, 5217543379, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000214CD94B640>, 'Unnamed: 0': 32624, 'Unnamed: 0.1': 32553, 'time': 1.232928, 'length': 17.124, 'highway': 'unclassified', 'osmid': 59618174, 'PFU_1in50': -1.0, 't50': 1.232928})\n",
      "\n",
      "end: Fri Jan  7 23:32:31 2022\n",
      "\n",
      "--- processing complete\n"
     ]
    }
   ],
   "source": [
    "print('start: %s\\n' % time.ctime())\n",
    "gTime = gn.edges_and_nodes_gdf_to_graph(nodes, floodjoin, node_tag='node_ID', u_tag='stnode', v_tag='endnode', geometry_tag='geometry')\n",
    "gn.example_edge(gTime, 10)\n",
    "print('\\nend: %s' % time.ctime())\n",
    "print('\\n--- processing complete')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "start: Fri Jan  7 23:32:31 2022\n",
      "\n",
      "\n",
      "end: Fri Jan  7 23:35:32 2022\n",
      "\n",
      "--- processing complete\n"
     ]
    }
   ],
   "source": [
    "print('start: %s\\n' % time.ctime())\n",
    "gn.save(gTime, 'gTime_pre-project_flood50', out_pth, edges = False, nodes = False)\n",
    "print('\\nend: %s' % time.ctime())\n",
    "print('\\n--- processing complete')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create travel time values for the road nodes nearest to each service.\n",
    "\n",
    "Using calculate_OD.\n",
    "\n",
    "Already measured distance from origin/destination to nearest node in pre-project script."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GISFolder', 'GeoDataFrame', 'In', 'LineString', 'MultiLineString', 'Out', 'Point', 'Polygon', '_', '_1', '_14', '_23', '_24', '_7', '_8', '_9', '__', '___', '__builtin__', '__builtins__', '__doc__', '__loader__', '__name__', '__package__', '__spec__', '_dh', '_i', '_i1', '_i10', '_i11', '_i12', '_i13', '_i14', '_i15', '_i16', '_i17', '_i18', '_i19', '_i2', '_i20', '_i21', '_i22', '_i23', '_i24', '_i25', '_i26', '_i27', '_i28', '_i29', '_i3', '_i30', '_i31', '_i4', '_i5', '_i6', '_i7', '_i8', '_i9', '_ih', '_ii', '_iii', '_oh', 'box', 'exit', 'fiona', 'gdal', 'get_ipython', 'glob', 'gn', 'gpd', 'importlib', 'linemerge', 'loads', 'losm', 'np', 'nx', 'os', 'out_pth', 'ox', 'pc_flooded', 'pd', 'peartree', 'plt', 'pth', 'quit', 'rt', 'shapely', 'subprocess', 'sys', 'team_pth', 'time', 'transform', 'unary_union', 'wkt']\n",
      "['GISFolder', 'GeoDataFrame', 'In', 'LineString', 'MultiLineString', 'Out', 'Point', 'Polygon', '_', '_1', '_14', '_23', '_24', '_7', '_8', '_9', '__', '___', '__builtin__', '__builtins__', '__doc__', '__loader__', '__name__', '__package__', '__spec__', '_dh', '_i', '_i1', '_i10', '_i11', '_i12', '_i13', '_i14', '_i15', '_i16', '_i17', '_i18', '_i19', '_i2', '_i20', '_i21', '_i22', '_i23', '_i24', '_i25', '_i26', '_i27', '_i28', '_i29', '_i3', '_i30', '_i31', '_i4', '_i5', '_i6', '_i7', '_i8', '_i9', '_ih', '_ii', '_iii', '_oh', 'box', 'exit', 'fiona', 'gTime', 'gdal', 'get_ipython', 'glob', 'gn', 'gpd', 'importlib', 'linemerge', 'loads', 'losm', 'np', 'nx', 'os', 'out_pth', 'ox', 'pc_flooded', 'pd', 'peartree', 'plt', 'pth', 'quit', 'rt', 'shapely', 'subprocess', 'sys', 'team_pth', 'time', 'transform', 'unary_union', 'wkt']\n"
     ]
    }
   ],
   "source": [
    "#%% If starting new session, reload graph from file\n",
    "del gTime, floodjoin, nodes\n",
    "print(dir())\n",
    "gTime = nx.read_gpickle(\"SEN-Cdrive/outputs/gTime_pre-project_flood50.pickle\")\n",
    "print(dir())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If starting a new session, load from file.\n",
    "HDurban_snap = os.path.join(out_pth, \"HDurban_snap.csv\")\n",
    "HDurban_snap = pd.read_csv(HDurban_snap)\n",
    "hamlet_snap = os.path.join(out_pth, \"hamlet_snap.csv\")\n",
    "hamlet_snap = pd.read_csv(hamlet_snap)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "ag_snap = os.path.join(out_pth, \"ag_snap.csv\")\n",
    "ag_snap = pd.read_csv(ag_snap)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed_ 0</th>\n",
       "      <th>mgrs_code</th>\n",
       "      <th>type</th>\n",
       "      <th>AREA_GEO</th>\n",
       "      <th>SUM</th>\n",
       "      <th>Total_Driv</th>\n",
       "      <th>WP_dens</th>\n",
       "      <th>urb</th>\n",
       "      <th>hd_urb</th>\n",
       "      <th>Urb_class</th>\n",
       "      <th>Shape_Leng</th>\n",
       "      <th>Shape_Area</th>\n",
       "      <th>geometry</th>\n",
       "      <th>NN</th>\n",
       "      <th>NN_dist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>28PCU6189_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>27.151434</td>\n",
       "      <td>1.860466e+05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6852.183502</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.671746</td>\n",
       "      <td>2.258828e-03</td>\n",
       "      <td>POINT (-16.27318626010657 12.562367084144856)</td>\n",
       "      <td>6058226279</td>\n",
       "      <td>2.216606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>28PEV0625_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>25.799549</td>\n",
       "      <td>1.064198e+05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4124.872441</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.718941</td>\n",
       "      <td>2.149259e-03</td>\n",
       "      <td>POINT (-14.936187613172205 12.892160925397892)</td>\n",
       "      <td>6029307183</td>\n",
       "      <td>45.594167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>37</td>\n",
       "      <td>28PBA8597_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>81.242354</td>\n",
       "      <td>3.382677e+05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4163.686114</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2.303639</td>\n",
       "      <td>6.813610e-03</td>\n",
       "      <td>POINT (-16.987028969206293 14.445279143289168)</td>\n",
       "      <td>4998093094</td>\n",
       "      <td>55.383696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>47</td>\n",
       "      <td>28PCA8365_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>42.134069</td>\n",
       "      <td>2.755403e+05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6539.609258</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.070058</td>\n",
       "      <td>3.528274e-03</td>\n",
       "      <td>POINT (-16.076128475299285 14.160382012387636)</td>\n",
       "      <td>2201506815</td>\n",
       "      <td>55.754910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>56</td>\n",
       "      <td>28PDA4058_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>11.123881</td>\n",
       "      <td>5.186398e+04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4662.399756</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.298223</td>\n",
       "      <td>9.311211e-04</td>\n",
       "      <td>POINT (-15.547596052646174 14.100419699131665)</td>\n",
       "      <td>4321656809</td>\n",
       "      <td>61.727308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>75</td>\n",
       "      <td>28PBB9234_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>53.453391</td>\n",
       "      <td>2.911360e+05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5446.539082</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.468618</td>\n",
       "      <td>4.489114e-03</td>\n",
       "      <td>POINT (-16.926149059908756 14.780621967102865)</td>\n",
       "      <td>1697006012</td>\n",
       "      <td>23.047883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>76</td>\n",
       "      <td>28PBB4632_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>226.850348</td>\n",
       "      <td>3.637718e+06</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4.771359</td>\n",
       "      <td>1.905124e-02</td>\n",
       "      <td>POINT (-17.35638706164378 14.751962825615575)</td>\n",
       "      <td>1901689169</td>\n",
       "      <td>5.826006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>80</td>\n",
       "      <td>28PCB0453_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>19.580942</td>\n",
       "      <td>8.712281e+04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4449.367491</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.611345</td>\n",
       "      <td>1.645460e-03</td>\n",
       "      <td>POINT (-16.81720508223022 14.94580992504745)</td>\n",
       "      <td>6032060028</td>\n",
       "      <td>88.156089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>84</td>\n",
       "      <td>28PCB6720_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>23.634834</td>\n",
       "      <td>1.342721e+05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5681.109779</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.689064</td>\n",
       "      <td>1.983306e-03</td>\n",
       "      <td>POINT (-16.227786747221682 14.653718853747844)</td>\n",
       "      <td>6040927878</td>\n",
       "      <td>41.930884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>98</td>\n",
       "      <td>28PDB0443_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>196.802191</td>\n",
       "      <td>9.490037e+05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2575.818720</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.094788</td>\n",
       "      <td>1.653195e-02</td>\n",
       "      <td>POINT (-15.887758561636817 14.860301673230607)</td>\n",
       "      <td>3449495495</td>\n",
       "      <td>28.431974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>123</td>\n",
       "      <td>28QCC4070_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>9.174147</td>\n",
       "      <td>1.077744e+05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>11747.618988</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.212800</td>\n",
       "      <td>7.746230e-04</td>\n",
       "      <td>POINT (-16.490434722674546 16.009724520231487)</td>\n",
       "      <td>3990543961</td>\n",
       "      <td>30.501908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>125</td>\n",
       "      <td>28QCC4573_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>13.701781</td>\n",
       "      <td>7.519136e+04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5487.706734</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.612294</td>\n",
       "      <td>1.157396e-03</td>\n",
       "      <td>POINT (-16.44247212109952 16.048232088709874)</td>\n",
       "      <td>8972391475</td>\n",
       "      <td>76.235638</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>151</td>\n",
       "      <td>28PFA4322_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>26.974183</td>\n",
       "      <td>1.207991e+05</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4478.321578</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.686130</td>\n",
       "      <td>2.255116e-03</td>\n",
       "      <td>POINT (-13.670259928888687 13.770692647360004)</td>\n",
       "      <td>3418418812</td>\n",
       "      <td>13.729478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>157</td>\n",
       "      <td>28PCC6827_01</td>\n",
       "      <td>bua</td>\n",
       "      <td>20.814212</td>\n",
       "      <td>9.576882e+04</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4601.126349</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.494002</td>\n",
       "      <td>1.754369e-03</td>\n",
       "      <td>POINT (-16.22513510721168 15.620904283746231)</td>\n",
       "      <td>1983641803</td>\n",
       "      <td>29.090625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>160</td>\n",
       "      <td>28PCU6286_04</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.105280</td>\n",
       "      <td>2.093665e+02</td>\n",
       "      <td>5.479664</td>\n",
       "      <td>6852.183502</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.021230</td>\n",
       "      <td>8.758142e-06</td>\n",
       "      <td>POINT (-16.264484462734323 12.537366413261045)</td>\n",
       "      <td>6014451367</td>\n",
       "      <td>61.273348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>168</td>\n",
       "      <td>28PEV0425_03</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.164601</td>\n",
       "      <td>2.503226e+02</td>\n",
       "      <td>5.371455</td>\n",
       "      <td>4124.872441</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.019495</td>\n",
       "      <td>1.371013e-05</td>\n",
       "      <td>POINT (-14.961042651677891 12.895762457094666)</td>\n",
       "      <td>6027163276</td>\n",
       "      <td>23.270139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>173</td>\n",
       "      <td>28PFA4321_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.036757</td>\n",
       "      <td>5.002239e+01</td>\n",
       "      <td>3.131679</td>\n",
       "      <td>4478.321578</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.006776</td>\n",
       "      <td>3.072012e-06</td>\n",
       "      <td>POINT (-13.668587614564068 13.755222178122034)</td>\n",
       "      <td>2833577858</td>\n",
       "      <td>216.111092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>174</td>\n",
       "      <td>28PBA9292_06</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.023177</td>\n",
       "      <td>1.846325e+01</td>\n",
       "      <td>13.239334</td>\n",
       "      <td>4163.686114</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.005371</td>\n",
       "      <td>1.942589e-06</td>\n",
       "      <td>POINT (-16.927480646349732 14.39849858186184)</td>\n",
       "      <td>7321088861</td>\n",
       "      <td>126.948072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>175</td>\n",
       "      <td>28PBA8299_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.013923</td>\n",
       "      <td>1.655101e+01</td>\n",
       "      <td>4.715519</td>\n",
       "      <td>4163.686114</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.004799</td>\n",
       "      <td>1.167113e-06</td>\n",
       "      <td>POINT (-17.016066265874734 14.460756350154803)</td>\n",
       "      <td>6045659373</td>\n",
       "      <td>27.871739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>176</td>\n",
       "      <td>28PBB7801_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.017578</td>\n",
       "      <td>3.044826e+01</td>\n",
       "      <td>11.659853</td>\n",
       "      <td>4163.686114</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.004813</td>\n",
       "      <td>1.473665e-06</td>\n",
       "      <td>POINT (-17.055862944965526 14.477635627799748)</td>\n",
       "      <td>5528866190</td>\n",
       "      <td>64.826632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>177</td>\n",
       "      <td>28PBB8202_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.033487</td>\n",
       "      <td>3.486522e+01</td>\n",
       "      <td>7.892879</td>\n",
       "      <td>4163.686114</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.008101</td>\n",
       "      <td>2.807464e-06</td>\n",
       "      <td>POINT (-17.018846969475057 14.483053711062894)</td>\n",
       "      <td>6027939517</td>\n",
       "      <td>24.581971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>178</td>\n",
       "      <td>28PBB7902_04</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.188962</td>\n",
       "      <td>2.698936e+02</td>\n",
       "      <td>9.563365</td>\n",
       "      <td>4163.686114</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.030043</td>\n",
       "      <td>1.584495e-05</td>\n",
       "      <td>POINT (-17.042492216832404 14.48472898603413)</td>\n",
       "      <td>6031093257</td>\n",
       "      <td>273.489784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22</td>\n",
       "      <td>179</td>\n",
       "      <td>28PBB8103_02</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.152780</td>\n",
       "      <td>2.366904e+02</td>\n",
       "      <td>9.538024</td>\n",
       "      <td>4163.686114</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.029995</td>\n",
       "      <td>1.281388e-05</td>\n",
       "      <td>POINT (-17.026406509846662 14.49188592573409)</td>\n",
       "      <td>1883155712</td>\n",
       "      <td>129.540762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>182</td>\n",
       "      <td>28PCA7866_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.009282</td>\n",
       "      <td>1.921189e+01</td>\n",
       "      <td>8.378629</td>\n",
       "      <td>6539.609258</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.003274</td>\n",
       "      <td>7.770706e-07</td>\n",
       "      <td>POINT (-16.12870301954371 14.163238160099032)</td>\n",
       "      <td>6026417971</td>\n",
       "      <td>287.396023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>24</td>\n",
       "      <td>184</td>\n",
       "      <td>28PCA8268_03</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.026723</td>\n",
       "      <td>3.108315e+01</td>\n",
       "      <td>5.292360</td>\n",
       "      <td>6539.609258</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.006861</td>\n",
       "      <td>2.237697e-06</td>\n",
       "      <td>POINT (-16.090276652783288 14.185562094447294)</td>\n",
       "      <td>6031245697</td>\n",
       "      <td>49.284838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>25</td>\n",
       "      <td>197</td>\n",
       "      <td>28PBB3031_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.036793</td>\n",
       "      <td>2.190752e+02</td>\n",
       "      <td>15.925017</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.007562</td>\n",
       "      <td>3.088678e-06</td>\n",
       "      <td>POINT (-17.503912092775586 14.746318221560271)</td>\n",
       "      <td>7493947593</td>\n",
       "      <td>15.922254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>26</td>\n",
       "      <td>199</td>\n",
       "      <td>28PBB9431_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.017802</td>\n",
       "      <td>3.757302e+01</td>\n",
       "      <td>6.049956</td>\n",
       "      <td>5446.539082</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.004718</td>\n",
       "      <td>1.494350e-06</td>\n",
       "      <td>POINT (-16.909081994152583 14.746958369151947)</td>\n",
       "      <td>1859359090</td>\n",
       "      <td>107.065710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>201</td>\n",
       "      <td>28PBB5833_07</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.044133</td>\n",
       "      <td>6.815012e+01</td>\n",
       "      <td>15.771149</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.008892</td>\n",
       "      <td>3.704665e-06</td>\n",
       "      <td>POINT (-17.23995849720513 14.767547644151895)</td>\n",
       "      <td>6053816462</td>\n",
       "      <td>52.284317</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>28</td>\n",
       "      <td>202</td>\n",
       "      <td>28PBB5934_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.067703</td>\n",
       "      <td>6.689919e+01</td>\n",
       "      <td>16.463440</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.012549</td>\n",
       "      <td>5.684528e-06</td>\n",
       "      <td>POINT (-17.23391993155635 14.77602519637424)</td>\n",
       "      <td>6027530676</td>\n",
       "      <td>16.433933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29</td>\n",
       "      <td>203</td>\n",
       "      <td>28PBB7434_03</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.024692</td>\n",
       "      <td>6.194532e+01</td>\n",
       "      <td>8.691752</td>\n",
       "      <td>2999.449116</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.005714</td>\n",
       "      <td>2.072792e-06</td>\n",
       "      <td>POINT (-17.098633451710782 14.77747413510043)</td>\n",
       "      <td>2988435647</td>\n",
       "      <td>89.468532</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>30</td>\n",
       "      <td>204</td>\n",
       "      <td>28PBB7034_07</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.112259</td>\n",
       "      <td>1.692285e+02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2999.449116</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.016761</td>\n",
       "      <td>9.425205e-06</td>\n",
       "      <td>POINT (-17.129431186301037 14.778506781574904)</td>\n",
       "      <td>7838754747</td>\n",
       "      <td>18.701502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>31</td>\n",
       "      <td>206</td>\n",
       "      <td>28PBB5835_03</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.022898</td>\n",
       "      <td>9.636268e+00</td>\n",
       "      <td>18.883526</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.005762</td>\n",
       "      <td>1.922510e-06</td>\n",
       "      <td>POINT (-17.242305601017154 14.783375368548443)</td>\n",
       "      <td>6053825179</td>\n",
       "      <td>85.053967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>32</td>\n",
       "      <td>208</td>\n",
       "      <td>28PBB7435_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.045895</td>\n",
       "      <td>7.314733e+01</td>\n",
       "      <td>9.383175</td>\n",
       "      <td>2999.449116</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.010610</td>\n",
       "      <td>3.853366e-06</td>\n",
       "      <td>POINT (-17.094173826957192 14.782872880683293)</td>\n",
       "      <td>2988435792</td>\n",
       "      <td>448.688952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>33</td>\n",
       "      <td>209</td>\n",
       "      <td>28PBB5736_02</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.024862</td>\n",
       "      <td>2.599556e+01</td>\n",
       "      <td>18.950196</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.005707</td>\n",
       "      <td>2.087379e-06</td>\n",
       "      <td>POINT (-17.249054064042234 14.789944283803916)</td>\n",
       "      <td>3036877534</td>\n",
       "      <td>83.358619</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>34</td>\n",
       "      <td>210</td>\n",
       "      <td>28PBB6036_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.127726</td>\n",
       "      <td>2.177392e+02</td>\n",
       "      <td>18.371827</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.020240</td>\n",
       "      <td>1.072401e-05</td>\n",
       "      <td>POINT (-17.224509034146617 14.78865536663635)</td>\n",
       "      <td>6056472223</td>\n",
       "      <td>33.845239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>35</td>\n",
       "      <td>211</td>\n",
       "      <td>28PBB4937_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.064379</td>\n",
       "      <td>5.965873e+02</td>\n",
       "      <td>7.559833</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.010636</td>\n",
       "      <td>5.405016e-06</td>\n",
       "      <td>POINT (-17.33197897874605 14.801673118686626)</td>\n",
       "      <td>7460241128</td>\n",
       "      <td>101.985612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>36</td>\n",
       "      <td>213</td>\n",
       "      <td>28PBB9439_02</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.031203</td>\n",
       "      <td>6.360090e+01</td>\n",
       "      <td>6.593517</td>\n",
       "      <td>5446.539082</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.006431</td>\n",
       "      <td>2.620111e-06</td>\n",
       "      <td>POINT (-16.906716646509395 14.821388297685347)</td>\n",
       "      <td>6028595154</td>\n",
       "      <td>16.068266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>37</td>\n",
       "      <td>214</td>\n",
       "      <td>28PBB9140_02</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.016236</td>\n",
       "      <td>3.369405e+01</td>\n",
       "      <td>7.070007</td>\n",
       "      <td>5446.539082</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.005212</td>\n",
       "      <td>1.363254e-06</td>\n",
       "      <td>POINT (-16.93440097090104 14.827449770877049)</td>\n",
       "      <td>4053739078</td>\n",
       "      <td>53.575602</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>38</td>\n",
       "      <td>216</td>\n",
       "      <td>28PCB0753_03</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.038454</td>\n",
       "      <td>2.791577e+01</td>\n",
       "      <td>4.990843</td>\n",
       "      <td>4449.367491</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.007613</td>\n",
       "      <td>3.230593e-06</td>\n",
       "      <td>POINT (-16.793775813770107 14.948188044266814)</td>\n",
       "      <td>4071261250</td>\n",
       "      <td>101.308205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>39</td>\n",
       "      <td>218</td>\n",
       "      <td>28PCB6420_04</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.036734</td>\n",
       "      <td>5.628131e+01</td>\n",
       "      <td>5.323107</td>\n",
       "      <td>5681.109779</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.008031</td>\n",
       "      <td>3.082793e-06</td>\n",
       "      <td>POINT (-16.256116818111135 14.656461506543518)</td>\n",
       "      <td>6014815775</td>\n",
       "      <td>69.797088</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>40</td>\n",
       "      <td>219</td>\n",
       "      <td>28PCB6622_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.042953</td>\n",
       "      <td>7.583911e+01</td>\n",
       "      <td>7.330625</td>\n",
       "      <td>5681.109779</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.008189</td>\n",
       "      <td>3.604109e-06</td>\n",
       "      <td>POINT (-16.241253248843044 14.674992153284423)</td>\n",
       "      <td>6024430122</td>\n",
       "      <td>65.929413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>41</td>\n",
       "      <td>223</td>\n",
       "      <td>28PDB0035_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.007997</td>\n",
       "      <td>1.721998e+01</td>\n",
       "      <td>12.119414</td>\n",
       "      <td>4822.119467</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.003240</td>\n",
       "      <td>6.713225e-07</td>\n",
       "      <td>POINT (-15.924404585230247 14.79008802267149)</td>\n",
       "      <td>8229317581</td>\n",
       "      <td>46.096276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>42</td>\n",
       "      <td>224</td>\n",
       "      <td>28PCB9937_03</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.066384</td>\n",
       "      <td>1.701381e+02</td>\n",
       "      <td>10.443704</td>\n",
       "      <td>4822.119467</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.011110</td>\n",
       "      <td>5.573913e-06</td>\n",
       "      <td>POINT (-15.933785288853453 14.809426960267158)</td>\n",
       "      <td>6027834438</td>\n",
       "      <td>208.336925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>43</td>\n",
       "      <td>226</td>\n",
       "      <td>28PDB1139_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.020778</td>\n",
       "      <td>2.617514e+01</td>\n",
       "      <td>10.677935</td>\n",
       "      <td>4822.119467</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.005601</td>\n",
       "      <td>1.744649e-06</td>\n",
       "      <td>POINT (-15.82161911516783 14.826628245382166)</td>\n",
       "      <td>6024894515</td>\n",
       "      <td>194.366522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>44</td>\n",
       "      <td>227</td>\n",
       "      <td>28PDB1139_02</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.056959</td>\n",
       "      <td>8.547818e+01</td>\n",
       "      <td>10.874058</td>\n",
       "      <td>4822.119467</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.011433</td>\n",
       "      <td>4.783164e-06</td>\n",
       "      <td>POINT (-15.824145139116334 14.826896103769105)</td>\n",
       "      <td>6024894530</td>\n",
       "      <td>44.896345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>45</td>\n",
       "      <td>228</td>\n",
       "      <td>28PDB1139_03</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.020389</td>\n",
       "      <td>1.425154e+01</td>\n",
       "      <td>10.553416</td>\n",
       "      <td>4822.119467</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.005011</td>\n",
       "      <td>1.712171e-06</td>\n",
       "      <td>POINT (-15.819773868082187 14.82763603774496)</td>\n",
       "      <td>6024894513</td>\n",
       "      <td>214.531196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>46</td>\n",
       "      <td>229</td>\n",
       "      <td>28PCB9741_04</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.028295</td>\n",
       "      <td>2.806345e+01</td>\n",
       "      <td>9.374555</td>\n",
       "      <td>4822.119467</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.006360</td>\n",
       "      <td>2.376282e-06</td>\n",
       "      <td>POINT (-15.956248013051251 14.84813630510084)</td>\n",
       "      <td>8169321141</td>\n",
       "      <td>23.619127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>47</td>\n",
       "      <td>230</td>\n",
       "      <td>28PDB0149_01</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.040534</td>\n",
       "      <td>4.546143e+01</td>\n",
       "      <td>6.408090</td>\n",
       "      <td>4822.119467</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.007648</td>\n",
       "      <td>3.404769e-06</td>\n",
       "      <td>POINT (-15.920443499962804 14.914499169364376)</td>\n",
       "      <td>8200304626</td>\n",
       "      <td>6.919737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>48</td>\n",
       "      <td>237</td>\n",
       "      <td>28PCC6924_02</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.013923</td>\n",
       "      <td>9.612921e+00</td>\n",
       "      <td>5.051494</td>\n",
       "      <td>4601.126349</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.004811</td>\n",
       "      <td>1.173189e-06</td>\n",
       "      <td>POINT (-16.217456240770417 15.597199756480478)</td>\n",
       "      <td>1968458114</td>\n",
       "      <td>56.750510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>49</td>\n",
       "      <td>244</td>\n",
       "      <td>28QCC4877_02</td>\n",
       "      <td>hamlet</td>\n",
       "      <td>0.020283</td>\n",
       "      <td>1.279428e+01</td>\n",
       "      <td>4.109954</td>\n",
       "      <td>5487.706734</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.005630</td>\n",
       "      <td>1.713057e-06</td>\n",
       "      <td>POINT (-16.416588195364064 16.071912118977373)</td>\n",
       "      <td>9359670710</td>\n",
       "      <td>34.348832</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>50</td>\n",
       "      <td>274</td>\n",
       "      <td>28PFA3822_04</td>\n",
       "      <td>ssa</td>\n",
       "      <td>0.295219</td>\n",
       "      <td>5.353027e+02</td>\n",
       "      <td>4.677464</td>\n",
       "      <td>4478.321578</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.026402</td>\n",
       "      <td>2.467579e-05</td>\n",
       "      <td>POINT (-13.714783248440478 13.771220759716533)</td>\n",
       "      <td>6021177135</td>\n",
       "      <td>239.631441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>51</td>\n",
       "      <td>275</td>\n",
       "      <td>28PFA4124_01</td>\n",
       "      <td>ssa</td>\n",
       "      <td>0.101893</td>\n",
       "      <td>1.886798e+02</td>\n",
       "      <td>4.089369</td>\n",
       "      <td>4478.321578</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.013817</td>\n",
       "      <td>8.517618e-06</td>\n",
       "      <td>POINT (-13.69532688091379 13.783265672958317)</td>\n",
       "      <td>6027615161</td>\n",
       "      <td>29.882395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>52</td>\n",
       "      <td>278</td>\n",
       "      <td>28PBA8299_02</td>\n",
       "      <td>ssa</td>\n",
       "      <td>0.343841</td>\n",
       "      <td>6.259307e+02</td>\n",
       "      <td>5.307602</td>\n",
       "      <td>4163.686114</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.051295</td>\n",
       "      <td>2.883058e-05</td>\n",
       "      <td>POINT (-17.020742566008337 14.46138641364398)</td>\n",
       "      <td>6027276892</td>\n",
       "      <td>23.453107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>53</td>\n",
       "      <td>287</td>\n",
       "      <td>28PBB5835_05</td>\n",
       "      <td>ssa</td>\n",
       "      <td>0.308017</td>\n",
       "      <td>3.190239e+02</td>\n",
       "      <td>17.747726</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.043032</td>\n",
       "      <td>2.586476e-05</td>\n",
       "      <td>POINT (-17.245528396017207 14.782219244270532)</td>\n",
       "      <td>6041228287</td>\n",
       "      <td>40.406566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>54</td>\n",
       "      <td>288</td>\n",
       "      <td>28PBB5936_02</td>\n",
       "      <td>ssa</td>\n",
       "      <td>0.154444</td>\n",
       "      <td>2.000646e+02</td>\n",
       "      <td>20.407153</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.026399</td>\n",
       "      <td>1.296732e-05</td>\n",
       "      <td>POINT (-17.23454728899848 14.795909517565944)</td>\n",
       "      <td>5536661253</td>\n",
       "      <td>24.430550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>55</td>\n",
       "      <td>289</td>\n",
       "      <td>28PBB6040_03</td>\n",
       "      <td>ssa</td>\n",
       "      <td>0.160911</td>\n",
       "      <td>2.663758e+02</td>\n",
       "      <td>21.657425</td>\n",
       "      <td>16035.762110</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.027111</td>\n",
       "      <td>1.351374e-05</td>\n",
       "      <td>POINT (-17.225385101570456 14.830374516240026)</td>\n",
       "      <td>7357630367</td>\n",
       "      <td>149.183632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>56</td>\n",
       "      <td>297</td>\n",
       "      <td>28PCB9642_02</td>\n",
       "      <td>ssa</td>\n",
       "      <td>2.337009</td>\n",
       "      <td>3.504073e+03</td>\n",
       "      <td>11.801961</td>\n",
       "      <td>4822.119467</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.172410</td>\n",
       "      <td>1.963038e-04</td>\n",
       "      <td>POINT (-15.9651353582649 14.85568825099391)</td>\n",
       "      <td>8178147277</td>\n",
       "      <td>13.505685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>57</td>\n",
       "      <td>298</td>\n",
       "      <td>28PDB0750_04</td>\n",
       "      <td>ssa</td>\n",
       "      <td>0.454997</td>\n",
       "      <td>5.968164e+02</td>\n",
       "      <td>11.240426</td>\n",
       "      <td>4822.119467</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0.038288</td>\n",
       "      <td>3.822519e-05</td>\n",
       "      <td>POINT (-15.85780585273177 14.926926620823643)</td>\n",
       "      <td>6026834850</td>\n",
       "      <td>10.829250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0  Unnamed_ 0     mgrs_code    type    AREA_GEO           SUM  \\\n",
       "0            0           1  28PCU6189_01     bua   27.151434  1.860466e+05   \n",
       "1            1           8  28PEV0625_01     bua   25.799549  1.064198e+05   \n",
       "2            2          37  28PBA8597_01     bua   81.242354  3.382677e+05   \n",
       "3            3          47  28PCA8365_01     bua   42.134069  2.755403e+05   \n",
       "4            4          56  28PDA4058_01     bua   11.123881  5.186398e+04   \n",
       "5            5          75  28PBB9234_01     bua   53.453391  2.911360e+05   \n",
       "6            6          76  28PBB4632_01     bua  226.850348  3.637718e+06   \n",
       "7            7          80  28PCB0453_01     bua   19.580942  8.712281e+04   \n",
       "8            8          84  28PCB6720_01     bua   23.634834  1.342721e+05   \n",
       "9            9          98  28PDB0443_01     bua  196.802191  9.490037e+05   \n",
       "10          10         123  28QCC4070_01     bua    9.174147  1.077744e+05   \n",
       "11          11         125  28QCC4573_01     bua   13.701781  7.519136e+04   \n",
       "12          12         151  28PFA4322_01     bua   26.974183  1.207991e+05   \n",
       "13          13         157  28PCC6827_01     bua   20.814212  9.576882e+04   \n",
       "14          14         160  28PCU6286_04  hamlet    0.105280  2.093665e+02   \n",
       "15          15         168  28PEV0425_03  hamlet    0.164601  2.503226e+02   \n",
       "16          16         173  28PFA4321_01  hamlet    0.036757  5.002239e+01   \n",
       "17          17         174  28PBA9292_06  hamlet    0.023177  1.846325e+01   \n",
       "18          18         175  28PBA8299_01  hamlet    0.013923  1.655101e+01   \n",
       "19          19         176  28PBB7801_01  hamlet    0.017578  3.044826e+01   \n",
       "20          20         177  28PBB8202_01  hamlet    0.033487  3.486522e+01   \n",
       "21          21         178  28PBB7902_04  hamlet    0.188962  2.698936e+02   \n",
       "22          22         179  28PBB8103_02  hamlet    0.152780  2.366904e+02   \n",
       "23          23         182  28PCA7866_01  hamlet    0.009282  1.921189e+01   \n",
       "24          24         184  28PCA8268_03  hamlet    0.026723  3.108315e+01   \n",
       "25          25         197  28PBB3031_01  hamlet    0.036793  2.190752e+02   \n",
       "26          26         199  28PBB9431_01  hamlet    0.017802  3.757302e+01   \n",
       "27          27         201  28PBB5833_07  hamlet    0.044133  6.815012e+01   \n",
       "28          28         202  28PBB5934_01  hamlet    0.067703  6.689919e+01   \n",
       "29          29         203  28PBB7434_03  hamlet    0.024692  6.194532e+01   \n",
       "30          30         204  28PBB7034_07  hamlet    0.112259  1.692285e+02   \n",
       "31          31         206  28PBB5835_03  hamlet    0.022898  9.636268e+00   \n",
       "32          32         208  28PBB7435_01  hamlet    0.045895  7.314733e+01   \n",
       "33          33         209  28PBB5736_02  hamlet    0.024862  2.599556e+01   \n",
       "34          34         210  28PBB6036_01  hamlet    0.127726  2.177392e+02   \n",
       "35          35         211  28PBB4937_01  hamlet    0.064379  5.965873e+02   \n",
       "36          36         213  28PBB9439_02  hamlet    0.031203  6.360090e+01   \n",
       "37          37         214  28PBB9140_02  hamlet    0.016236  3.369405e+01   \n",
       "38          38         216  28PCB0753_03  hamlet    0.038454  2.791577e+01   \n",
       "39          39         218  28PCB6420_04  hamlet    0.036734  5.628131e+01   \n",
       "40          40         219  28PCB6622_01  hamlet    0.042953  7.583911e+01   \n",
       "41          41         223  28PDB0035_01  hamlet    0.007997  1.721998e+01   \n",
       "42          42         224  28PCB9937_03  hamlet    0.066384  1.701381e+02   \n",
       "43          43         226  28PDB1139_01  hamlet    0.020778  2.617514e+01   \n",
       "44          44         227  28PDB1139_02  hamlet    0.056959  8.547818e+01   \n",
       "45          45         228  28PDB1139_03  hamlet    0.020389  1.425154e+01   \n",
       "46          46         229  28PCB9741_04  hamlet    0.028295  2.806345e+01   \n",
       "47          47         230  28PDB0149_01  hamlet    0.040534  4.546143e+01   \n",
       "48          48         237  28PCC6924_02  hamlet    0.013923  9.612921e+00   \n",
       "49          49         244  28QCC4877_02  hamlet    0.020283  1.279428e+01   \n",
       "50          50         274  28PFA3822_04     ssa    0.295219  5.353027e+02   \n",
       "51          51         275  28PFA4124_01     ssa    0.101893  1.886798e+02   \n",
       "52          52         278  28PBA8299_02     ssa    0.343841  6.259307e+02   \n",
       "53          53         287  28PBB5835_05     ssa    0.308017  3.190239e+02   \n",
       "54          54         288  28PBB5936_02     ssa    0.154444  2.000646e+02   \n",
       "55          55         289  28PBB6040_03     ssa    0.160911  2.663758e+02   \n",
       "56          56         297  28PCB9642_02     ssa    2.337009  3.504073e+03   \n",
       "57          57         298  28PDB0750_04     ssa    0.454997  5.968164e+02   \n",
       "\n",
       "    Total_Driv       WP_dens  urb  hd_urb  Urb_class  Shape_Leng  \\\n",
       "0     0.000000   6852.183502    1       1          2    0.671746   \n",
       "1     0.000000   4124.872441    1       1          2    0.718941   \n",
       "2     0.000000   4163.686114    1       1          2    2.303639   \n",
       "3     0.000000   6539.609258    1       1          2    1.070058   \n",
       "4     0.000000   4662.399756    1       1          2    0.298223   \n",
       "5     0.000000   5446.539082    1       1          2    1.468618   \n",
       "6     0.000000  16035.762110    1       1          2    4.771359   \n",
       "7     0.000000   4449.367491    1       1          2    0.611345   \n",
       "8     0.000000   5681.109779    1       1          2    0.689064   \n",
       "9     0.000000   2575.818720    1       1          2    3.094788   \n",
       "10    0.000000  11747.618988    1       1          2    0.212800   \n",
       "11    0.000000   5487.706734    1       1          2    0.612294   \n",
       "12    0.000000   4478.321578    1       1          2    0.686130   \n",
       "13    0.000000   4601.126349    1       1          2    0.494002   \n",
       "14    5.479664   6852.183502    1       1          2    0.021230   \n",
       "15    5.371455   4124.872441    1       1          2    0.019495   \n",
       "16    3.131679   4478.321578    1       1          2    0.006776   \n",
       "17   13.239334   4163.686114    1       1          2    0.005371   \n",
       "18    4.715519   4163.686114    1       1          2    0.004799   \n",
       "19   11.659853   4163.686114    1       1          2    0.004813   \n",
       "20    7.892879   4163.686114    1       1          2    0.008101   \n",
       "21    9.563365   4163.686114    1       1          2    0.030043   \n",
       "22    9.538024   4163.686114    1       1          2    0.029995   \n",
       "23    8.378629   6539.609258    1       1          2    0.003274   \n",
       "24    5.292360   6539.609258    1       1          2    0.006861   \n",
       "25   15.925017  16035.762110    1       1          2    0.007562   \n",
       "26    6.049956   5446.539082    1       1          2    0.004718   \n",
       "27   15.771149  16035.762110    1       1          2    0.008892   \n",
       "28   16.463440  16035.762110    1       1          2    0.012549   \n",
       "29    8.691752   2999.449116    1       1          2    0.005714   \n",
       "30         NaN   2999.449116    1       1          2    0.016761   \n",
       "31   18.883526  16035.762110    1       1          2    0.005762   \n",
       "32    9.383175   2999.449116    1       1          2    0.010610   \n",
       "33   18.950196  16035.762110    1       1          2    0.005707   \n",
       "34   18.371827  16035.762110    1       1          2    0.020240   \n",
       "35    7.559833  16035.762110    1       1          2    0.010636   \n",
       "36    6.593517   5446.539082    1       1          2    0.006431   \n",
       "37    7.070007   5446.539082    1       1          2    0.005212   \n",
       "38    4.990843   4449.367491    1       1          2    0.007613   \n",
       "39    5.323107   5681.109779    1       1          2    0.008031   \n",
       "40    7.330625   5681.109779    1       1          2    0.008189   \n",
       "41   12.119414   4822.119467    1       1          2    0.003240   \n",
       "42   10.443704   4822.119467    1       1          2    0.011110   \n",
       "43   10.677935   4822.119467    1       1          2    0.005601   \n",
       "44   10.874058   4822.119467    1       1          2    0.011433   \n",
       "45   10.553416   4822.119467    1       1          2    0.005011   \n",
       "46    9.374555   4822.119467    1       1          2    0.006360   \n",
       "47    6.408090   4822.119467    1       1          2    0.007648   \n",
       "48    5.051494   4601.126349    1       1          2    0.004811   \n",
       "49    4.109954   5487.706734    1       1          2    0.005630   \n",
       "50    4.677464   4478.321578    1       1          2    0.026402   \n",
       "51    4.089369   4478.321578    1       1          2    0.013817   \n",
       "52    5.307602   4163.686114    1       1          2    0.051295   \n",
       "53   17.747726  16035.762110    1       1          2    0.043032   \n",
       "54   20.407153  16035.762110    1       1          2    0.026399   \n",
       "55   21.657425  16035.762110    1       1          2    0.027111   \n",
       "56   11.801961   4822.119467    1       1          2    0.172410   \n",
       "57   11.240426   4822.119467    1       1          2    0.038288   \n",
       "\n",
       "      Shape_Area                                        geometry          NN  \\\n",
       "0   2.258828e-03   POINT (-16.27318626010657 12.562367084144856)  6058226279   \n",
       "1   2.149259e-03  POINT (-14.936187613172205 12.892160925397892)  6029307183   \n",
       "2   6.813610e-03  POINT (-16.987028969206293 14.445279143289168)  4998093094   \n",
       "3   3.528274e-03  POINT (-16.076128475299285 14.160382012387636)  2201506815   \n",
       "4   9.311211e-04  POINT (-15.547596052646174 14.100419699131665)  4321656809   \n",
       "5   4.489114e-03  POINT (-16.926149059908756 14.780621967102865)  1697006012   \n",
       "6   1.905124e-02   POINT (-17.35638706164378 14.751962825615575)  1901689169   \n",
       "7   1.645460e-03    POINT (-16.81720508223022 14.94580992504745)  6032060028   \n",
       "8   1.983306e-03  POINT (-16.227786747221682 14.653718853747844)  6040927878   \n",
       "9   1.653195e-02  POINT (-15.887758561636817 14.860301673230607)  3449495495   \n",
       "10  7.746230e-04  POINT (-16.490434722674546 16.009724520231487)  3990543961   \n",
       "11  1.157396e-03   POINT (-16.44247212109952 16.048232088709874)  8972391475   \n",
       "12  2.255116e-03  POINT (-13.670259928888687 13.770692647360004)  3418418812   \n",
       "13  1.754369e-03   POINT (-16.22513510721168 15.620904283746231)  1983641803   \n",
       "14  8.758142e-06  POINT (-16.264484462734323 12.537366413261045)  6014451367   \n",
       "15  1.371013e-05  POINT (-14.961042651677891 12.895762457094666)  6027163276   \n",
       "16  3.072012e-06  POINT (-13.668587614564068 13.755222178122034)  2833577858   \n",
       "17  1.942589e-06   POINT (-16.927480646349732 14.39849858186184)  7321088861   \n",
       "18  1.167113e-06  POINT (-17.016066265874734 14.460756350154803)  6045659373   \n",
       "19  1.473665e-06  POINT (-17.055862944965526 14.477635627799748)  5528866190   \n",
       "20  2.807464e-06  POINT (-17.018846969475057 14.483053711062894)  6027939517   \n",
       "21  1.584495e-05   POINT (-17.042492216832404 14.48472898603413)  6031093257   \n",
       "22  1.281388e-05   POINT (-17.026406509846662 14.49188592573409)  1883155712   \n",
       "23  7.770706e-07   POINT (-16.12870301954371 14.163238160099032)  6026417971   \n",
       "24  2.237697e-06  POINT (-16.090276652783288 14.185562094447294)  6031245697   \n",
       "25  3.088678e-06  POINT (-17.503912092775586 14.746318221560271)  7493947593   \n",
       "26  1.494350e-06  POINT (-16.909081994152583 14.746958369151947)  1859359090   \n",
       "27  3.704665e-06   POINT (-17.23995849720513 14.767547644151895)  6053816462   \n",
       "28  5.684528e-06    POINT (-17.23391993155635 14.77602519637424)  6027530676   \n",
       "29  2.072792e-06   POINT (-17.098633451710782 14.77747413510043)  2988435647   \n",
       "30  9.425205e-06  POINT (-17.129431186301037 14.778506781574904)  7838754747   \n",
       "31  1.922510e-06  POINT (-17.242305601017154 14.783375368548443)  6053825179   \n",
       "32  3.853366e-06  POINT (-17.094173826957192 14.782872880683293)  2988435792   \n",
       "33  2.087379e-06  POINT (-17.249054064042234 14.789944283803916)  3036877534   \n",
       "34  1.072401e-05   POINT (-17.224509034146617 14.78865536663635)  6056472223   \n",
       "35  5.405016e-06   POINT (-17.33197897874605 14.801673118686626)  7460241128   \n",
       "36  2.620111e-06  POINT (-16.906716646509395 14.821388297685347)  6028595154   \n",
       "37  1.363254e-06   POINT (-16.93440097090104 14.827449770877049)  4053739078   \n",
       "38  3.230593e-06  POINT (-16.793775813770107 14.948188044266814)  4071261250   \n",
       "39  3.082793e-06  POINT (-16.256116818111135 14.656461506543518)  6014815775   \n",
       "40  3.604109e-06  POINT (-16.241253248843044 14.674992153284423)  6024430122   \n",
       "41  6.713225e-07   POINT (-15.924404585230247 14.79008802267149)  8229317581   \n",
       "42  5.573913e-06  POINT (-15.933785288853453 14.809426960267158)  6027834438   \n",
       "43  1.744649e-06   POINT (-15.82161911516783 14.826628245382166)  6024894515   \n",
       "44  4.783164e-06  POINT (-15.824145139116334 14.826896103769105)  6024894530   \n",
       "45  1.712171e-06   POINT (-15.819773868082187 14.82763603774496)  6024894513   \n",
       "46  2.376282e-06   POINT (-15.956248013051251 14.84813630510084)  8169321141   \n",
       "47  3.404769e-06  POINT (-15.920443499962804 14.914499169364376)  8200304626   \n",
       "48  1.173189e-06  POINT (-16.217456240770417 15.597199756480478)  1968458114   \n",
       "49  1.713057e-06  POINT (-16.416588195364064 16.071912118977373)  9359670710   \n",
       "50  2.467579e-05  POINT (-13.714783248440478 13.771220759716533)  6021177135   \n",
       "51  8.517618e-06   POINT (-13.69532688091379 13.783265672958317)  6027615161   \n",
       "52  2.883058e-05   POINT (-17.020742566008337 14.46138641364398)  6027276892   \n",
       "53  2.586476e-05  POINT (-17.245528396017207 14.782219244270532)  6041228287   \n",
       "54  1.296732e-05   POINT (-17.23454728899848 14.795909517565944)  5536661253   \n",
       "55  1.351374e-05  POINT (-17.225385101570456 14.830374516240026)  7357630367   \n",
       "56  1.963038e-04     POINT (-15.9651353582649 14.85568825099391)  8178147277   \n",
       "57  3.822519e-05   POINT (-15.85780585273177 14.926926620823643)  6026834850   \n",
       "\n",
       "       NN_dist  \n",
       "0     2.216606  \n",
       "1    45.594167  \n",
       "2    55.383696  \n",
       "3    55.754910  \n",
       "4    61.727308  \n",
       "5    23.047883  \n",
       "6     5.826006  \n",
       "7    88.156089  \n",
       "8    41.930884  \n",
       "9    28.431974  \n",
       "10   30.501908  \n",
       "11   76.235638  \n",
       "12   13.729478  \n",
       "13   29.090625  \n",
       "14   61.273348  \n",
       "15   23.270139  \n",
       "16  216.111092  \n",
       "17  126.948072  \n",
       "18   27.871739  \n",
       "19   64.826632  \n",
       "20   24.581971  \n",
       "21  273.489784  \n",
       "22  129.540762  \n",
       "23  287.396023  \n",
       "24   49.284838  \n",
       "25   15.922254  \n",
       "26  107.065710  \n",
       "27   52.284317  \n",
       "28   16.433933  \n",
       "29   89.468532  \n",
       "30   18.701502  \n",
       "31   85.053967  \n",
       "32  448.688952  \n",
       "33   83.358619  \n",
       "34   33.845239  \n",
       "35  101.985612  \n",
       "36   16.068266  \n",
       "37   53.575602  \n",
       "38  101.308205  \n",
       "39   69.797088  \n",
       "40   65.929413  \n",
       "41   46.096276  \n",
       "42  208.336925  \n",
       "43  194.366522  \n",
       "44   44.896345  \n",
       "45  214.531196  \n",
       "46   23.619127  \n",
       "47    6.919737  \n",
       "48   56.750510  \n",
       "49   34.348832  \n",
       "50  239.631441  \n",
       "51   29.882395  \n",
       "52   23.453107  \n",
       "53   40.406566  \n",
       "54   24.430550  \n",
       "55  149.183632  \n",
       "56   13.505685  \n",
       "57   10.829250  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HDurban_snap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We only need to find the origin-destination pairs for nodes closest to the origins and services,\n",
    "# and some nodes will be the nearest for more than one service (and definitely for multiple origins).\n",
    "list_hamlet = list(hamlet_snap.NN.unique())\n",
    "origins = list_hamlet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_ag = list(ag_snap.NN.unique())\n",
    "originslist = list_hamlet + list_ag\n",
    "origins = list(set(originslist))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "dests = list(HDurban_snap.NN.unique()) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "985720"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(origins) # 985720 unique nearest nodes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "58"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dests) # 58 unique nearest nodes. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(358284990, 5217543379, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000213863B8C70>, 'Unnamed: 0': 0, 'Unnamed: 0.1': 0, 'time': 2.3851440000000004, 'length': 33.127, 'highway': 'unclassified', 'osmid': 59618174, 'PFU_1in50': -1.0, 't50': 2.3851440000000004})\n",
      "(358284990, 1888282175, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000213FE5BD1C0>, 'Unnamed: 0': 1, 'Unnamed: 0.1': 1, 'time': 0.76992, 'length': 12.832, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 0.76992})\n",
      "(358284990, 5329792467, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000213FE5BD7F0>, 'Unnamed: 0': 2, 'Unnamed: 0.1': 2, 'time': 2.92686, 'length': 48.781, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 2.92686})\n",
      "(5217543379, 358284990, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000215DF33F8E0>, 'Unnamed: 0': 3, 'Unnamed: 0.1': 3, 'time': 2.3851440000000004, 'length': 33.127, 'highway': 'unclassified', 'osmid': 59618174, 'PFU_1in50': -1.0, 't50': 2.3851440000000004})\n",
      "(5217543379, 5329928981, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000215DF33F040>, 'Unnamed: 0': 4, 'Unnamed: 0.1': 4, 'time': 1.232928, 'length': 17.124, 'highway': 'unclassified', 'osmid': 59618174, 'PFU_1in50': -1.0, 't50': 1.232928})\n",
      "(1888282175, 1888282174, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000215DF33F910>, 'Unnamed: 0': 5, 'Unnamed: 0.1': 5, 'time': 0.9322200000000002, 'length': 15.537, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 0.9322200000000002})\n",
      "(1888282175, 358284990, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000215DF33FD00>, 'Unnamed: 0': 6, 'Unnamed: 0.1': 6, 'time': 0.76992, 'length': 12.832, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 0.76992})\n",
      "(5329792467, 358284990, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000215DF33FB80>, 'Unnamed: 0': 7, 'Unnamed: 0.1': 7, 'time': 2.92686, 'length': 48.781, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 2.92686})\n",
      "(5329792467, 1888282177, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000215DF33FDF0>, 'Unnamed: 0': 8, 'Unnamed: 0.1': 8, 'time': 2.7629999999999995, 'length': 46.05, 'highway': 'tertiary', 'osmid': 178482063, 'PFU_1in50': -1.0, 't50': 2.7629999999999995})\n",
      "(5329928981, 5217543379, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000215DF33FE80>, 'Unnamed: 0': 32624, 'Unnamed: 0.1': 32553, 'time': 1.232928, 'length': 17.124, 'highway': 'unclassified', 'osmid': 59618174, 'PFU_1in50': -1.0, 't50': 1.232928})\n"
     ]
    }
   ],
   "source": [
    "gn.example_edge(gTime,10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "calculate_OD won't run if any of the edge times are null or zero. If there are any hits, reassign time values for those edges to a very small time. A more efficient way to do this would be to simplify junctions with the clean_network() tool, but that was throwing errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "fail_value = 999999999 # If there is no shortest path, the OD pair will be assigned the fail value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "start: Fri Jan  7 23:40:20 2022\n",
      "\n",
      "\n",
      "end: Sat Jan  8 00:14:07 2022\n",
      "\n",
      "--- processing complete\n"
     ]
    }
   ],
   "source": [
    "print('start: %s\\n' % time.ctime())\n",
    "OD = gn.calculate_OD(gTime, origins, dests, fail_value, weight = 't50')\n",
    "# Takes a few minutes.\n",
    "print('\\nend: %s' % time.ctime())\n",
    "print('\\n--- processing complete')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "OD_df = pd.DataFrame(OD, index = origins, columns = dests)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 985720 entries, 3331684166 to 3706826877\n",
      "Data columns (total 58 columns):\n",
      " #   Column      Non-Null Count   Dtype  \n",
      "---  ------      --------------   -----  \n",
      " 0   6058226279  985720 non-null  float64\n",
      " 1   6029307183  985720 non-null  float64\n",
      " 2   4998093094  985720 non-null  float64\n",
      " 3   2201506815  985720 non-null  float64\n",
      " 4   4321656809  985720 non-null  float64\n",
      " 5   1697006012  985720 non-null  float64\n",
      " 6   1901689169  985720 non-null  float64\n",
      " 7   6032060028  985720 non-null  float64\n",
      " 8   6040927878  985720 non-null  float64\n",
      " 9   3449495495  985720 non-null  float64\n",
      " 10  3990543961  985720 non-null  float64\n",
      " 11  8972391475  985720 non-null  float64\n",
      " 12  3418418812  985720 non-null  float64\n",
      " 13  1983641803  985720 non-null  float64\n",
      " 14  6014451367  985720 non-null  float64\n",
      " 15  6027163276  985720 non-null  float64\n",
      " 16  2833577858  985720 non-null  float64\n",
      " 17  7321088861  985720 non-null  float64\n",
      " 18  6045659373  985720 non-null  float64\n",
      " 19  5528866190  985720 non-null  float64\n",
      " 20  6027939517  985720 non-null  float64\n",
      " 21  6031093257  985720 non-null  float64\n",
      " 22  1883155712  985720 non-null  float64\n",
      " 23  6026417971  985720 non-null  float64\n",
      " 24  6031245697  985720 non-null  float64\n",
      " 25  7493947593  985720 non-null  float64\n",
      " 26  1859359090  985720 non-null  float64\n",
      " 27  6053816462  985720 non-null  float64\n",
      " 28  6027530676  985720 non-null  float64\n",
      " 29  2988435647  985720 non-null  float64\n",
      " 30  7838754747  985720 non-null  float64\n",
      " 31  6053825179  985720 non-null  float64\n",
      " 32  2988435792  985720 non-null  float64\n",
      " 33  3036877534  985720 non-null  float64\n",
      " 34  6056472223  985720 non-null  float64\n",
      " 35  7460241128  985720 non-null  float64\n",
      " 36  6028595154  985720 non-null  float64\n",
      " 37  4053739078  985720 non-null  float64\n",
      " 38  4071261250  985720 non-null  float64\n",
      " 39  6014815775  985720 non-null  float64\n",
      " 40  6024430122  985720 non-null  float64\n",
      " 41  8229317581  985720 non-null  float64\n",
      " 42  6027834438  985720 non-null  float64\n",
      " 43  6024894515  985720 non-null  float64\n",
      " 44  6024894530  985720 non-null  float64\n",
      " 45  6024894513  985720 non-null  float64\n",
      " 46  8169321141  985720 non-null  float64\n",
      " 47  8200304626  985720 non-null  float64\n",
      " 48  1968458114  985720 non-null  float64\n",
      " 49  9359670710  985720 non-null  float64\n",
      " 50  6021177135  985720 non-null  float64\n",
      " 51  6027615161  985720 non-null  float64\n",
      " 52  6027276892  985720 non-null  float64\n",
      " 53  6041228287  985720 non-null  float64\n",
      " 54  5536661253  985720 non-null  float64\n",
      " 55  7357630367  985720 non-null  float64\n",
      " 56  8178147277  985720 non-null  float64\n",
      " 57  6026834850  985720 non-null  float64\n",
      "dtypes: float64(58)\n",
      "memory usage: 443.7 MB\n"
     ]
    }
   ],
   "source": [
    "OD_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>6058226279</th>\n",
       "      <th>6029307183</th>\n",
       "      <th>4998093094</th>\n",
       "      <th>2201506815</th>\n",
       "      <th>4321656809</th>\n",
       "      <th>1697006012</th>\n",
       "      <th>1901689169</th>\n",
       "      <th>6032060028</th>\n",
       "      <th>6040927878</th>\n",
       "      <th>3449495495</th>\n",
       "      <th>...</th>\n",
       "      <th>1968458114</th>\n",
       "      <th>9359670710</th>\n",
       "      <th>6021177135</th>\n",
       "      <th>6027615161</th>\n",
       "      <th>6027276892</th>\n",
       "      <th>6041228287</th>\n",
       "      <th>5536661253</th>\n",
       "      <th>7357630367</th>\n",
       "      <th>8178147277</th>\n",
       "      <th>6026834850</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8925478824</th>\n",
       "      <td>374622.993017</td>\n",
       "      <td>534714.687089</td>\n",
       "      <td>6294.513822</td>\n",
       "      <td>10817.165367</td>\n",
       "      <td>344976.431423</td>\n",
       "      <td>4119.039466</td>\n",
       "      <td>6514.333964</td>\n",
       "      <td>2932.849334</td>\n",
       "      <td>118766.313972</td>\n",
       "      <td>5300.161107</td>\n",
       "      <td>...</td>\n",
       "      <td>9043.084400</td>\n",
       "      <td>10281.605269</td>\n",
       "      <td>454892.011369</td>\n",
       "      <td>454874.412607</td>\n",
       "      <td>6547.461643</td>\n",
       "      <td>6106.681600</td>\n",
       "      <td>6074.985768</td>\n",
       "      <td>6069.733378</td>\n",
       "      <td>5435.576714</td>\n",
       "      <td>5485.793684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826868</th>\n",
       "      <td>302145.220413</td>\n",
       "      <td>462236.914485</td>\n",
       "      <td>615052.559622</td>\n",
       "      <td>606753.422529</td>\n",
       "      <td>264314.007033</td>\n",
       "      <td>612986.329720</td>\n",
       "      <td>615466.766349</td>\n",
       "      <td>611888.914646</td>\n",
       "      <td>714702.571134</td>\n",
       "      <td>604023.032339</td>\n",
       "      <td>...</td>\n",
       "      <td>616979.309359</td>\n",
       "      <td>618217.830228</td>\n",
       "      <td>382414.238764</td>\n",
       "      <td>382396.640002</td>\n",
       "      <td>615305.507444</td>\n",
       "      <td>615059.113985</td>\n",
       "      <td>615027.418152</td>\n",
       "      <td>615022.165763</td>\n",
       "      <td>604231.839749</td>\n",
       "      <td>604643.615459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826871</th>\n",
       "      <td>232760.161467</td>\n",
       "      <td>392851.855539</td>\n",
       "      <td>545667.500676</td>\n",
       "      <td>537368.363583</td>\n",
       "      <td>194928.948087</td>\n",
       "      <td>543601.270774</td>\n",
       "      <td>546081.707403</td>\n",
       "      <td>542503.855700</td>\n",
       "      <td>645317.512188</td>\n",
       "      <td>534637.973393</td>\n",
       "      <td>...</td>\n",
       "      <td>547594.250413</td>\n",
       "      <td>548832.771282</td>\n",
       "      <td>313029.179818</td>\n",
       "      <td>313011.581056</td>\n",
       "      <td>545920.448498</td>\n",
       "      <td>545674.055039</td>\n",
       "      <td>545642.359206</td>\n",
       "      <td>545637.106817</td>\n",
       "      <td>534846.780803</td>\n",
       "      <td>535258.556513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826876</th>\n",
       "      <td>232424.189685</td>\n",
       "      <td>392515.883757</td>\n",
       "      <td>545331.528894</td>\n",
       "      <td>537032.391801</td>\n",
       "      <td>194592.976305</td>\n",
       "      <td>543265.298992</td>\n",
       "      <td>545745.735621</td>\n",
       "      <td>542167.883918</td>\n",
       "      <td>644981.540406</td>\n",
       "      <td>534302.001611</td>\n",
       "      <td>...</td>\n",
       "      <td>547258.278631</td>\n",
       "      <td>548496.799500</td>\n",
       "      <td>312693.208036</td>\n",
       "      <td>312675.609274</td>\n",
       "      <td>545584.476716</td>\n",
       "      <td>545338.083257</td>\n",
       "      <td>545306.387424</td>\n",
       "      <td>545301.135035</td>\n",
       "      <td>534510.809021</td>\n",
       "      <td>534922.584731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826877</th>\n",
       "      <td>232775.276715</td>\n",
       "      <td>392866.970787</td>\n",
       "      <td>545682.615924</td>\n",
       "      <td>537383.478831</td>\n",
       "      <td>194944.063335</td>\n",
       "      <td>543616.386022</td>\n",
       "      <td>546096.822651</td>\n",
       "      <td>542518.970948</td>\n",
       "      <td>645332.627436</td>\n",
       "      <td>534653.088641</td>\n",
       "      <td>...</td>\n",
       "      <td>547609.365661</td>\n",
       "      <td>548847.886530</td>\n",
       "      <td>313044.295066</td>\n",
       "      <td>313026.696304</td>\n",
       "      <td>545935.563746</td>\n",
       "      <td>545689.170287</td>\n",
       "      <td>545657.474454</td>\n",
       "      <td>545652.222065</td>\n",
       "      <td>534861.896051</td>\n",
       "      <td>535273.671761</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 58 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               6058226279     6029307183     4998093094     2201506815  \\\n",
       "8925478824  374622.993017  534714.687089    6294.513822   10817.165367   \n",
       "3706826868  302145.220413  462236.914485  615052.559622  606753.422529   \n",
       "3706826871  232760.161467  392851.855539  545667.500676  537368.363583   \n",
       "3706826876  232424.189685  392515.883757  545331.528894  537032.391801   \n",
       "3706826877  232775.276715  392866.970787  545682.615924  537383.478831   \n",
       "\n",
       "               4321656809     1697006012     1901689169     6032060028  \\\n",
       "8925478824  344976.431423    4119.039466    6514.333964    2932.849334   \n",
       "3706826868  264314.007033  612986.329720  615466.766349  611888.914646   \n",
       "3706826871  194928.948087  543601.270774  546081.707403  542503.855700   \n",
       "3706826876  194592.976305  543265.298992  545745.735621  542167.883918   \n",
       "3706826877  194944.063335  543616.386022  546096.822651  542518.970948   \n",
       "\n",
       "               6040927878     3449495495  ...     1968458114     9359670710  \\\n",
       "8925478824  118766.313972    5300.161107  ...    9043.084400   10281.605269   \n",
       "3706826868  714702.571134  604023.032339  ...  616979.309359  618217.830228   \n",
       "3706826871  645317.512188  534637.973393  ...  547594.250413  548832.771282   \n",
       "3706826876  644981.540406  534302.001611  ...  547258.278631  548496.799500   \n",
       "3706826877  645332.627436  534653.088641  ...  547609.365661  548847.886530   \n",
       "\n",
       "               6021177135     6027615161     6027276892     6041228287  \\\n",
       "8925478824  454892.011369  454874.412607    6547.461643    6106.681600   \n",
       "3706826868  382414.238764  382396.640002  615305.507444  615059.113985   \n",
       "3706826871  313029.179818  313011.581056  545920.448498  545674.055039   \n",
       "3706826876  312693.208036  312675.609274  545584.476716  545338.083257   \n",
       "3706826877  313044.295066  313026.696304  545935.563746  545689.170287   \n",
       "\n",
       "               5536661253     7357630367     8178147277     6026834850  \n",
       "8925478824    6074.985768    6069.733378    5435.576714    5485.793684  \n",
       "3706826868  615027.418152  615022.165763  604231.839749  604643.615459  \n",
       "3706826871  545642.359206  545637.106817  534846.780803  535258.556513  \n",
       "3706826876  545306.387424  545301.135035  534510.809021  534922.584731  \n",
       "3706826877  545657.474454  545652.222065  534861.896051  535273.671761  \n",
       "\n",
       "[5 rows x 58 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "OD_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>6058226279</th>\n",
       "      <th>6029307183</th>\n",
       "      <th>4998093094</th>\n",
       "      <th>2201506815</th>\n",
       "      <th>4321656809</th>\n",
       "      <th>1697006012</th>\n",
       "      <th>1901689169</th>\n",
       "      <th>6032060028</th>\n",
       "      <th>6040927878</th>\n",
       "      <th>3449495495</th>\n",
       "      <th>...</th>\n",
       "      <th>1968458114</th>\n",
       "      <th>9359670710</th>\n",
       "      <th>6021177135</th>\n",
       "      <th>6027615161</th>\n",
       "      <th>6027276892</th>\n",
       "      <th>6041228287</th>\n",
       "      <th>5536661253</th>\n",
       "      <th>7357630367</th>\n",
       "      <th>8178147277</th>\n",
       "      <th>6026834850</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3331684166</th>\n",
       "      <td>6213.530255</td>\n",
       "      <td>8881.725156</td>\n",
       "      <td>288.418746</td>\n",
       "      <td>11.536460</td>\n",
       "      <td>5719.420895</td>\n",
       "      <td>253.981581</td>\n",
       "      <td>295.322191</td>\n",
       "      <td>235.691330</td>\n",
       "      <td>1869.250269</td>\n",
       "      <td>104.593291</td>\n",
       "      <td>...</td>\n",
       "      <td>320.531242</td>\n",
       "      <td>341.173256</td>\n",
       "      <td>7551.347228</td>\n",
       "      <td>7551.053915</td>\n",
       "      <td>292.634543</td>\n",
       "      <td>288.527985</td>\n",
       "      <td>287.999722</td>\n",
       "      <td>287.912182</td>\n",
       "      <td>108.073415</td>\n",
       "      <td>114.936343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3571449893</th>\n",
       "      <td>520.662115</td>\n",
       "      <td>3042.737410</td>\n",
       "      <td>5966.540964</td>\n",
       "      <td>5828.222012</td>\n",
       "      <td>257.309284</td>\n",
       "      <td>5932.103799</td>\n",
       "      <td>5973.444409</td>\n",
       "      <td>5913.813547</td>\n",
       "      <td>7627.374489</td>\n",
       "      <td>5782.715509</td>\n",
       "      <td>...</td>\n",
       "      <td>5998.653459</td>\n",
       "      <td>6019.295474</td>\n",
       "      <td>1712.359481</td>\n",
       "      <td>1712.066169</td>\n",
       "      <td>5970.756761</td>\n",
       "      <td>5966.650203</td>\n",
       "      <td>5966.121939</td>\n",
       "      <td>5966.034399</td>\n",
       "      <td>5786.195632</td>\n",
       "      <td>5793.058561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3571449966</th>\n",
       "      <td>521.049027</td>\n",
       "      <td>3043.124322</td>\n",
       "      <td>5966.927875</td>\n",
       "      <td>5828.608924</td>\n",
       "      <td>257.696195</td>\n",
       "      <td>5932.490710</td>\n",
       "      <td>5973.831321</td>\n",
       "      <td>5914.200459</td>\n",
       "      <td>7627.761400</td>\n",
       "      <td>5783.102420</td>\n",
       "      <td>...</td>\n",
       "      <td>5999.040371</td>\n",
       "      <td>6019.682385</td>\n",
       "      <td>1712.746393</td>\n",
       "      <td>1712.453080</td>\n",
       "      <td>5971.143672</td>\n",
       "      <td>5967.037115</td>\n",
       "      <td>5966.508851</td>\n",
       "      <td>5966.421311</td>\n",
       "      <td>5786.582544</td>\n",
       "      <td>5793.445472</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3405774993</th>\n",
       "      <td>396.351017</td>\n",
       "      <td>3178.487893</td>\n",
       "      <td>5956.171840</td>\n",
       "      <td>5817.852888</td>\n",
       "      <td>246.940160</td>\n",
       "      <td>5921.734675</td>\n",
       "      <td>5963.075285</td>\n",
       "      <td>5903.444424</td>\n",
       "      <td>7617.005365</td>\n",
       "      <td>5772.346385</td>\n",
       "      <td>...</td>\n",
       "      <td>5988.284336</td>\n",
       "      <td>6008.926350</td>\n",
       "      <td>1848.109964</td>\n",
       "      <td>1847.816651</td>\n",
       "      <td>5960.387637</td>\n",
       "      <td>5956.281079</td>\n",
       "      <td>5955.752815</td>\n",
       "      <td>5955.665276</td>\n",
       "      <td>5775.826509</td>\n",
       "      <td>5782.689437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3405774994</th>\n",
       "      <td>395.950268</td>\n",
       "      <td>3178.087144</td>\n",
       "      <td>5955.771091</td>\n",
       "      <td>5817.452139</td>\n",
       "      <td>246.539411</td>\n",
       "      <td>5921.333926</td>\n",
       "      <td>5962.674536</td>\n",
       "      <td>5903.043675</td>\n",
       "      <td>7616.604616</td>\n",
       "      <td>5771.945636</td>\n",
       "      <td>...</td>\n",
       "      <td>5987.883587</td>\n",
       "      <td>6008.525601</td>\n",
       "      <td>1847.709215</td>\n",
       "      <td>1847.415902</td>\n",
       "      <td>5959.986888</td>\n",
       "      <td>5955.880330</td>\n",
       "      <td>5955.352066</td>\n",
       "      <td>5955.264527</td>\n",
       "      <td>5775.425760</td>\n",
       "      <td>5782.288688</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8925478824</th>\n",
       "      <td>6243.716550</td>\n",
       "      <td>8911.911451</td>\n",
       "      <td>104.908564</td>\n",
       "      <td>180.286089</td>\n",
       "      <td>5749.607190</td>\n",
       "      <td>68.650658</td>\n",
       "      <td>108.572233</td>\n",
       "      <td>48.880822</td>\n",
       "      <td>1979.438566</td>\n",
       "      <td>88.336018</td>\n",
       "      <td>...</td>\n",
       "      <td>150.718073</td>\n",
       "      <td>171.360088</td>\n",
       "      <td>7581.533523</td>\n",
       "      <td>7581.240210</td>\n",
       "      <td>109.124361</td>\n",
       "      <td>101.778027</td>\n",
       "      <td>101.249763</td>\n",
       "      <td>101.162223</td>\n",
       "      <td>90.592945</td>\n",
       "      <td>91.429895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826868</th>\n",
       "      <td>5035.753674</td>\n",
       "      <td>7703.948575</td>\n",
       "      <td>10250.875994</td>\n",
       "      <td>10112.557042</td>\n",
       "      <td>4405.233451</td>\n",
       "      <td>10216.438829</td>\n",
       "      <td>10257.779439</td>\n",
       "      <td>10198.148577</td>\n",
       "      <td>11911.709519</td>\n",
       "      <td>10067.050539</td>\n",
       "      <td>...</td>\n",
       "      <td>10282.988489</td>\n",
       "      <td>10303.630504</td>\n",
       "      <td>6373.570646</td>\n",
       "      <td>6373.277333</td>\n",
       "      <td>10255.091791</td>\n",
       "      <td>10250.985233</td>\n",
       "      <td>10250.456969</td>\n",
       "      <td>10250.369429</td>\n",
       "      <td>10070.530662</td>\n",
       "      <td>10077.393591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826871</th>\n",
       "      <td>3879.336024</td>\n",
       "      <td>6547.530926</td>\n",
       "      <td>9094.458345</td>\n",
       "      <td>8956.139393</td>\n",
       "      <td>3248.815801</td>\n",
       "      <td>9060.021180</td>\n",
       "      <td>9101.361790</td>\n",
       "      <td>9041.730928</td>\n",
       "      <td>10755.291870</td>\n",
       "      <td>8910.632890</td>\n",
       "      <td>...</td>\n",
       "      <td>9126.570840</td>\n",
       "      <td>9147.212855</td>\n",
       "      <td>5217.152997</td>\n",
       "      <td>5216.859684</td>\n",
       "      <td>9098.674142</td>\n",
       "      <td>9094.567584</td>\n",
       "      <td>9094.039320</td>\n",
       "      <td>9093.951780</td>\n",
       "      <td>8914.113013</td>\n",
       "      <td>8920.975942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826876</th>\n",
       "      <td>3873.736495</td>\n",
       "      <td>6541.931396</td>\n",
       "      <td>9088.858815</td>\n",
       "      <td>8950.539863</td>\n",
       "      <td>3243.216272</td>\n",
       "      <td>9054.421650</td>\n",
       "      <td>9095.762260</td>\n",
       "      <td>9036.131399</td>\n",
       "      <td>10749.692340</td>\n",
       "      <td>8905.033360</td>\n",
       "      <td>...</td>\n",
       "      <td>9120.971311</td>\n",
       "      <td>9141.613325</td>\n",
       "      <td>5211.553467</td>\n",
       "      <td>5211.260155</td>\n",
       "      <td>9093.074612</td>\n",
       "      <td>9088.968054</td>\n",
       "      <td>9088.439790</td>\n",
       "      <td>9088.352251</td>\n",
       "      <td>8908.513484</td>\n",
       "      <td>8915.376412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826877</th>\n",
       "      <td>3879.587945</td>\n",
       "      <td>6547.782846</td>\n",
       "      <td>9094.710265</td>\n",
       "      <td>8956.391314</td>\n",
       "      <td>3249.067722</td>\n",
       "      <td>9060.273100</td>\n",
       "      <td>9101.613711</td>\n",
       "      <td>9041.982849</td>\n",
       "      <td>10755.543791</td>\n",
       "      <td>8910.884811</td>\n",
       "      <td>...</td>\n",
       "      <td>9126.822761</td>\n",
       "      <td>9147.464776</td>\n",
       "      <td>5217.404918</td>\n",
       "      <td>5217.111605</td>\n",
       "      <td>9098.926062</td>\n",
       "      <td>9094.819505</td>\n",
       "      <td>9094.291241</td>\n",
       "      <td>9094.203701</td>\n",
       "      <td>8914.364934</td>\n",
       "      <td>8921.227863</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>985720 rows × 58 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             6058226279   6029307183    4998093094    2201506815   4321656809  \\\n",
       "3331684166  6213.530255  8881.725156    288.418746     11.536460  5719.420895   \n",
       "3571449893   520.662115  3042.737410   5966.540964   5828.222012   257.309284   \n",
       "3571449966   521.049027  3043.124322   5966.927875   5828.608924   257.696195   \n",
       "3405774993   396.351017  3178.487893   5956.171840   5817.852888   246.940160   \n",
       "3405774994   395.950268  3178.087144   5955.771091   5817.452139   246.539411   \n",
       "...                 ...          ...           ...           ...          ...   \n",
       "8925478824  6243.716550  8911.911451    104.908564    180.286089  5749.607190   \n",
       "3706826868  5035.753674  7703.948575  10250.875994  10112.557042  4405.233451   \n",
       "3706826871  3879.336024  6547.530926   9094.458345   8956.139393  3248.815801   \n",
       "3706826876  3873.736495  6541.931396   9088.858815   8950.539863  3243.216272   \n",
       "3706826877  3879.587945  6547.782846   9094.710265   8956.391314  3249.067722   \n",
       "\n",
       "              1697006012    1901689169    6032060028    6040927878  \\\n",
       "3331684166    253.981581    295.322191    235.691330   1869.250269   \n",
       "3571449893   5932.103799   5973.444409   5913.813547   7627.374489   \n",
       "3571449966   5932.490710   5973.831321   5914.200459   7627.761400   \n",
       "3405774993   5921.734675   5963.075285   5903.444424   7617.005365   \n",
       "3405774994   5921.333926   5962.674536   5903.043675   7616.604616   \n",
       "...                  ...           ...           ...           ...   \n",
       "8925478824     68.650658    108.572233     48.880822   1979.438566   \n",
       "3706826868  10216.438829  10257.779439  10198.148577  11911.709519   \n",
       "3706826871   9060.021180   9101.361790   9041.730928  10755.291870   \n",
       "3706826876   9054.421650   9095.762260   9036.131399  10749.692340   \n",
       "3706826877   9060.273100   9101.613711   9041.982849  10755.543791   \n",
       "\n",
       "              3449495495  ...    1968458114    9359670710   6021177135  \\\n",
       "3331684166    104.593291  ...    320.531242    341.173256  7551.347228   \n",
       "3571449893   5782.715509  ...   5998.653459   6019.295474  1712.359481   \n",
       "3571449966   5783.102420  ...   5999.040371   6019.682385  1712.746393   \n",
       "3405774993   5772.346385  ...   5988.284336   6008.926350  1848.109964   \n",
       "3405774994   5771.945636  ...   5987.883587   6008.525601  1847.709215   \n",
       "...                  ...  ...           ...           ...          ...   \n",
       "8925478824     88.336018  ...    150.718073    171.360088  7581.533523   \n",
       "3706826868  10067.050539  ...  10282.988489  10303.630504  6373.570646   \n",
       "3706826871   8910.632890  ...   9126.570840   9147.212855  5217.152997   \n",
       "3706826876   8905.033360  ...   9120.971311   9141.613325  5211.553467   \n",
       "3706826877   8910.884811  ...   9126.822761   9147.464776  5217.404918   \n",
       "\n",
       "             6027615161    6027276892    6041228287    5536661253  \\\n",
       "3331684166  7551.053915    292.634543    288.527985    287.999722   \n",
       "3571449893  1712.066169   5970.756761   5966.650203   5966.121939   \n",
       "3571449966  1712.453080   5971.143672   5967.037115   5966.508851   \n",
       "3405774993  1847.816651   5960.387637   5956.281079   5955.752815   \n",
       "3405774994  1847.415902   5959.986888   5955.880330   5955.352066   \n",
       "...                 ...           ...           ...           ...   \n",
       "8925478824  7581.240210    109.124361    101.778027    101.249763   \n",
       "3706826868  6373.277333  10255.091791  10250.985233  10250.456969   \n",
       "3706826871  5216.859684   9098.674142   9094.567584   9094.039320   \n",
       "3706826876  5211.260155   9093.074612   9088.968054   9088.439790   \n",
       "3706826877  5217.111605   9098.926062   9094.819505   9094.291241   \n",
       "\n",
       "              7357630367    8178147277    6026834850  \n",
       "3331684166    287.912182    108.073415    114.936343  \n",
       "3571449893   5966.034399   5786.195632   5793.058561  \n",
       "3571449966   5966.421311   5786.582544   5793.445472  \n",
       "3405774993   5955.665276   5775.826509   5782.689437  \n",
       "3405774994   5955.264527   5775.425760   5782.288688  \n",
       "...                  ...           ...           ...  \n",
       "8925478824    101.162223     90.592945     91.429895  \n",
       "3706826868  10250.369429  10070.530662  10077.393591  \n",
       "3706826871   9093.951780   8914.113013   8920.975942  \n",
       "3706826876   9088.352251   8908.513484   8915.376412  \n",
       "3706826877   9094.203701   8914.364934   8921.227863  \n",
       "\n",
       "[985720 rows x 58 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert to minutes and save to file.\n",
    "OD_min = OD_df[OD_df <fail_value] / 60\n",
    "OD_min.to_csv(os.path.join(out_pth, 'OD_allorigins_pre-project_flood50.csv'))\n",
    "OD_min"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6058226279    3\n",
      "6029307183    3\n",
      "4998093094    3\n",
      "2201506815    3\n",
      "4321656809    3\n",
      "1697006012    3\n",
      "1901689169    3\n",
      "6032060028    3\n",
      "6040927878    3\n",
      "3449495495    3\n",
      "3990543961    3\n",
      "8972391475    3\n",
      "3418418812    3\n",
      "1983641803    3\n",
      "6014451367    3\n",
      "6027163276    3\n",
      "2833577858    3\n",
      "7321088861    3\n",
      "6045659373    3\n",
      "5528866190    3\n",
      "6027939517    3\n",
      "6031093257    3\n",
      "1883155712    3\n",
      "6026417971    3\n",
      "6031245697    3\n",
      "7493947593    3\n",
      "1859359090    3\n",
      "6053816462    3\n",
      "6027530676    3\n",
      "2988435647    3\n",
      "7838754747    3\n",
      "6053825179    3\n",
      "2988435792    3\n",
      "3036877534    3\n",
      "6056472223    3\n",
      "7460241128    3\n",
      "6028595154    3\n",
      "4053739078    3\n",
      "4071261250    3\n",
      "6014815775    3\n",
      "6024430122    3\n",
      "8229317581    3\n",
      "6027834438    3\n",
      "6024894515    3\n",
      "6024894530    3\n",
      "6024894513    3\n",
      "8169321141    3\n",
      "8200304626    3\n",
      "1968458114    3\n",
      "9359670710    3\n",
      "6021177135    3\n",
      "6027615161    3\n",
      "6027276892    3\n",
      "6041228287    3\n",
      "5536661253    3\n",
      "7357630367    3\n",
      "8178147277    3\n",
      "6026834850    3\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(OD_min.isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>6058226279</th>\n",
       "      <th>6029307183</th>\n",
       "      <th>4998093094</th>\n",
       "      <th>2201506815</th>\n",
       "      <th>4321656809</th>\n",
       "      <th>1697006012</th>\n",
       "      <th>1901689169</th>\n",
       "      <th>6032060028</th>\n",
       "      <th>6040927878</th>\n",
       "      <th>3449495495</th>\n",
       "      <th>...</th>\n",
       "      <th>1968458114</th>\n",
       "      <th>9359670710</th>\n",
       "      <th>6021177135</th>\n",
       "      <th>6027615161</th>\n",
       "      <th>6027276892</th>\n",
       "      <th>6041228287</th>\n",
       "      <th>5536661253</th>\n",
       "      <th>7357630367</th>\n",
       "      <th>8178147277</th>\n",
       "      <th>6026834850</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7761872870</th>\n",
       "      <td>7555.041976</td>\n",
       "      <td>11114.143037</td>\n",
       "      <td>13891.826984</td>\n",
       "      <td>13753.508032</td>\n",
       "      <td>8182.595304</td>\n",
       "      <td>13857.389819</td>\n",
       "      <td>13898.730429</td>\n",
       "      <td>13839.099568</td>\n",
       "      <td>15552.660509</td>\n",
       "      <td>13708.001529</td>\n",
       "      <td>...</td>\n",
       "      <td>13923.939479</td>\n",
       "      <td>13944.581494</td>\n",
       "      <td>9783.765108</td>\n",
       "      <td>9783.471795</td>\n",
       "      <td>13896.042781</td>\n",
       "      <td>13891.936223</td>\n",
       "      <td>13891.407959</td>\n",
       "      <td>13891.320420</td>\n",
       "      <td>13711.481653</td>\n",
       "      <td>13718.344581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7761872869</th>\n",
       "      <td>7555.023417</td>\n",
       "      <td>11114.124477</td>\n",
       "      <td>13891.808424</td>\n",
       "      <td>13753.489473</td>\n",
       "      <td>8182.576744</td>\n",
       "      <td>13857.371259</td>\n",
       "      <td>13898.711870</td>\n",
       "      <td>13839.081008</td>\n",
       "      <td>15552.641950</td>\n",
       "      <td>13707.982970</td>\n",
       "      <td>...</td>\n",
       "      <td>13923.920920</td>\n",
       "      <td>13944.562934</td>\n",
       "      <td>9783.746548</td>\n",
       "      <td>9783.453236</td>\n",
       "      <td>13896.024221</td>\n",
       "      <td>13891.917664</td>\n",
       "      <td>13891.389400</td>\n",
       "      <td>13891.301860</td>\n",
       "      <td>13711.463093</td>\n",
       "      <td>13718.326022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6442044321</th>\n",
       "      <td>7554.013585</td>\n",
       "      <td>11113.114645</td>\n",
       "      <td>13890.798592</td>\n",
       "      <td>13752.479641</td>\n",
       "      <td>8181.566912</td>\n",
       "      <td>13856.361427</td>\n",
       "      <td>13897.702038</td>\n",
       "      <td>13838.071176</td>\n",
       "      <td>15551.632118</td>\n",
       "      <td>13706.973138</td>\n",
       "      <td>...</td>\n",
       "      <td>13922.911088</td>\n",
       "      <td>13943.553102</td>\n",
       "      <td>9782.736716</td>\n",
       "      <td>9782.443404</td>\n",
       "      <td>13895.014389</td>\n",
       "      <td>13890.907832</td>\n",
       "      <td>13890.379568</td>\n",
       "      <td>13890.292028</td>\n",
       "      <td>13710.453261</td>\n",
       "      <td>13717.316190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2142496418</th>\n",
       "      <td>7555.467107</td>\n",
       "      <td>11114.568167</td>\n",
       "      <td>13892.252114</td>\n",
       "      <td>13753.933163</td>\n",
       "      <td>8183.020434</td>\n",
       "      <td>13857.814949</td>\n",
       "      <td>13899.155560</td>\n",
       "      <td>13839.524698</td>\n",
       "      <td>15553.085640</td>\n",
       "      <td>13708.426660</td>\n",
       "      <td>...</td>\n",
       "      <td>13924.364610</td>\n",
       "      <td>13945.006624</td>\n",
       "      <td>9784.190238</td>\n",
       "      <td>9783.896926</td>\n",
       "      <td>13896.467911</td>\n",
       "      <td>13892.361354</td>\n",
       "      <td>13891.833090</td>\n",
       "      <td>13891.745550</td>\n",
       "      <td>13711.906783</td>\n",
       "      <td>13718.769712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2142496429</th>\n",
       "      <td>7555.849481</td>\n",
       "      <td>11114.950541</td>\n",
       "      <td>13892.634488</td>\n",
       "      <td>13754.315537</td>\n",
       "      <td>8183.402808</td>\n",
       "      <td>13858.197323</td>\n",
       "      <td>13899.537934</td>\n",
       "      <td>13839.907072</td>\n",
       "      <td>15553.468014</td>\n",
       "      <td>13708.809034</td>\n",
       "      <td>...</td>\n",
       "      <td>13924.746984</td>\n",
       "      <td>13945.388998</td>\n",
       "      <td>9784.572612</td>\n",
       "      <td>9784.279300</td>\n",
       "      <td>13896.850285</td>\n",
       "      <td>13892.743728</td>\n",
       "      <td>13892.215464</td>\n",
       "      <td>13892.127924</td>\n",
       "      <td>13712.289157</td>\n",
       "      <td>13719.152086</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9208004175</th>\n",
       "      <td>407808.542291</td>\n",
       "      <td>410476.737192</td>\n",
       "      <td>401657.732281</td>\n",
       "      <td>401745.111830</td>\n",
       "      <td>407314.432931</td>\n",
       "      <td>401602.316288</td>\n",
       "      <td>401642.237863</td>\n",
       "      <td>401583.581214</td>\n",
       "      <td>403544.264307</td>\n",
       "      <td>401653.161759</td>\n",
       "      <td>...</td>\n",
       "      <td>401498.200522</td>\n",
       "      <td>401426.662175</td>\n",
       "      <td>409146.359264</td>\n",
       "      <td>409146.065951</td>\n",
       "      <td>401661.948078</td>\n",
       "      <td>401635.443657</td>\n",
       "      <td>401634.915393</td>\n",
       "      <td>401634.827853</td>\n",
       "      <td>401655.418686</td>\n",
       "      <td>401656.255636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3884280043</th>\n",
       "      <td>7782.266869</td>\n",
       "      <td>10121.321138</td>\n",
       "      <td>3678.828061</td>\n",
       "      <td>3580.973513</td>\n",
       "      <td>7518.914037</td>\n",
       "      <td>3641.568467</td>\n",
       "      <td>3681.490042</td>\n",
       "      <td>3622.800319</td>\n",
       "      <td>5380.125990</td>\n",
       "      <td>3489.544372</td>\n",
       "      <td>...</td>\n",
       "      <td>3486.750734</td>\n",
       "      <td>3560.877505</td>\n",
       "      <td>5560.727839</td>\n",
       "      <td>5560.434526</td>\n",
       "      <td>3683.043858</td>\n",
       "      <td>3674.695836</td>\n",
       "      <td>3674.167572</td>\n",
       "      <td>3674.080032</td>\n",
       "      <td>3498.737171</td>\n",
       "      <td>3491.253225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3884280001</th>\n",
       "      <td>7775.404642</td>\n",
       "      <td>10114.458911</td>\n",
       "      <td>3671.965834</td>\n",
       "      <td>3574.111286</td>\n",
       "      <td>7512.051810</td>\n",
       "      <td>3634.706240</td>\n",
       "      <td>3674.627815</td>\n",
       "      <td>3615.938092</td>\n",
       "      <td>5373.263763</td>\n",
       "      <td>3482.682145</td>\n",
       "      <td>...</td>\n",
       "      <td>3479.888507</td>\n",
       "      <td>3554.015278</td>\n",
       "      <td>5553.865612</td>\n",
       "      <td>5553.572299</td>\n",
       "      <td>3676.181631</td>\n",
       "      <td>3667.833609</td>\n",
       "      <td>3667.305345</td>\n",
       "      <td>3667.217805</td>\n",
       "      <td>3491.874944</td>\n",
       "      <td>3484.390998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8592243457</th>\n",
       "      <td>7768.998180</td>\n",
       "      <td>10108.052450</td>\n",
       "      <td>3647.759412</td>\n",
       "      <td>3549.904864</td>\n",
       "      <td>7505.645348</td>\n",
       "      <td>3610.499818</td>\n",
       "      <td>3650.421393</td>\n",
       "      <td>3591.731670</td>\n",
       "      <td>5349.057341</td>\n",
       "      <td>3458.475722</td>\n",
       "      <td>...</td>\n",
       "      <td>3455.682085</td>\n",
       "      <td>3529.808856</td>\n",
       "      <td>5547.459150</td>\n",
       "      <td>5547.165838</td>\n",
       "      <td>3651.975209</td>\n",
       "      <td>3643.627187</td>\n",
       "      <td>3643.098923</td>\n",
       "      <td>3643.011383</td>\n",
       "      <td>3467.668522</td>\n",
       "      <td>3460.184575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3649149740</th>\n",
       "      <td>107430.606399</td>\n",
       "      <td>109769.660669</td>\n",
       "      <td>103269.209051</td>\n",
       "      <td>103171.354503</td>\n",
       "      <td>107167.253568</td>\n",
       "      <td>103231.949457</td>\n",
       "      <td>103271.871032</td>\n",
       "      <td>103213.181309</td>\n",
       "      <td>104970.506980</td>\n",
       "      <td>103079.925361</td>\n",
       "      <td>...</td>\n",
       "      <td>103077.131724</td>\n",
       "      <td>103151.258495</td>\n",
       "      <td>105209.067370</td>\n",
       "      <td>105208.774057</td>\n",
       "      <td>103273.424848</td>\n",
       "      <td>103265.076826</td>\n",
       "      <td>103264.548562</td>\n",
       "      <td>103264.461022</td>\n",
       "      <td>103089.118161</td>\n",
       "      <td>103081.634214</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74662 rows × 58 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               6058226279     6029307183     4998093094     2201506815  \\\n",
       "7761872870    7555.041976   11114.143037   13891.826984   13753.508032   \n",
       "7761872869    7555.023417   11114.124477   13891.808424   13753.489473   \n",
       "6442044321    7554.013585   11113.114645   13890.798592   13752.479641   \n",
       "2142496418    7555.467107   11114.568167   13892.252114   13753.933163   \n",
       "2142496429    7555.849481   11114.950541   13892.634488   13754.315537   \n",
       "...                   ...            ...            ...            ...   \n",
       "9208004175  407808.542291  410476.737192  401657.732281  401745.111830   \n",
       "3884280043    7782.266869   10121.321138    3678.828061    3580.973513   \n",
       "3884280001    7775.404642   10114.458911    3671.965834    3574.111286   \n",
       "8592243457    7768.998180   10108.052450    3647.759412    3549.904864   \n",
       "3649149740  107430.606399  109769.660669  103269.209051  103171.354503   \n",
       "\n",
       "               4321656809     1697006012     1901689169     6032060028  \\\n",
       "7761872870    8182.595304   13857.389819   13898.730429   13839.099568   \n",
       "7761872869    8182.576744   13857.371259   13898.711870   13839.081008   \n",
       "6442044321    8181.566912   13856.361427   13897.702038   13838.071176   \n",
       "2142496418    8183.020434   13857.814949   13899.155560   13839.524698   \n",
       "2142496429    8183.402808   13858.197323   13899.537934   13839.907072   \n",
       "...                   ...            ...            ...            ...   \n",
       "9208004175  407314.432931  401602.316288  401642.237863  401583.581214   \n",
       "3884280043    7518.914037    3641.568467    3681.490042    3622.800319   \n",
       "3884280001    7512.051810    3634.706240    3674.627815    3615.938092   \n",
       "8592243457    7505.645348    3610.499818    3650.421393    3591.731670   \n",
       "3649149740  107167.253568  103231.949457  103271.871032  103213.181309   \n",
       "\n",
       "               6040927878     3449495495  ...     1968458114     9359670710  \\\n",
       "7761872870   15552.660509   13708.001529  ...   13923.939479   13944.581494   \n",
       "7761872869   15552.641950   13707.982970  ...   13923.920920   13944.562934   \n",
       "6442044321   15551.632118   13706.973138  ...   13922.911088   13943.553102   \n",
       "2142496418   15553.085640   13708.426660  ...   13924.364610   13945.006624   \n",
       "2142496429   15553.468014   13708.809034  ...   13924.746984   13945.388998   \n",
       "...                   ...            ...  ...            ...            ...   \n",
       "9208004175  403544.264307  401653.161759  ...  401498.200522  401426.662175   \n",
       "3884280043    5380.125990    3489.544372  ...    3486.750734    3560.877505   \n",
       "3884280001    5373.263763    3482.682145  ...    3479.888507    3554.015278   \n",
       "8592243457    5349.057341    3458.475722  ...    3455.682085    3529.808856   \n",
       "3649149740  104970.506980  103079.925361  ...  103077.131724  103151.258495   \n",
       "\n",
       "               6021177135     6027615161     6027276892     6041228287  \\\n",
       "7761872870    9783.765108    9783.471795   13896.042781   13891.936223   \n",
       "7761872869    9783.746548    9783.453236   13896.024221   13891.917664   \n",
       "6442044321    9782.736716    9782.443404   13895.014389   13890.907832   \n",
       "2142496418    9784.190238    9783.896926   13896.467911   13892.361354   \n",
       "2142496429    9784.572612    9784.279300   13896.850285   13892.743728   \n",
       "...                   ...            ...            ...            ...   \n",
       "9208004175  409146.359264  409146.065951  401661.948078  401635.443657   \n",
       "3884280043    5560.727839    5560.434526    3683.043858    3674.695836   \n",
       "3884280001    5553.865612    5553.572299    3676.181631    3667.833609   \n",
       "8592243457    5547.459150    5547.165838    3651.975209    3643.627187   \n",
       "3649149740  105209.067370  105208.774057  103273.424848  103265.076826   \n",
       "\n",
       "               5536661253     7357630367     8178147277     6026834850  \n",
       "7761872870   13891.407959   13891.320420   13711.481653   13718.344581  \n",
       "7761872869   13891.389400   13891.301860   13711.463093   13718.326022  \n",
       "6442044321   13890.379568   13890.292028   13710.453261   13717.316190  \n",
       "2142496418   13891.833090   13891.745550   13711.906783   13718.769712  \n",
       "2142496429   13892.215464   13892.127924   13712.289157   13719.152086  \n",
       "...                   ...            ...            ...            ...  \n",
       "9208004175  401634.915393  401634.827853  401655.418686  401656.255636  \n",
       "3884280043    3674.167572    3674.080032    3498.737171    3491.253225  \n",
       "3884280001    3667.305345    3667.217805    3491.874944    3484.390998  \n",
       "8592243457    3643.098923    3643.011383    3467.668522    3460.184575  \n",
       "3649149740  103264.548562  103264.461022  103089.118161  103081.634214  \n",
       "\n",
       "[74662 rows x 58 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create origin-specific matrix and save to file.\n",
    "OD_hamlet = OD_df.loc[list_hamlet,:]\n",
    "OD_hamlet = OD_hamlet[OD_hamlet < fail_value] / 60 \n",
    "OD_hamlet.to_csv(os.path.join(out_pth, 'OD_hamlet_pre-project_flood50.csv'))\n",
    "OD_hamlet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6058226279    1\n",
      "6029307183    1\n",
      "4998093094    1\n",
      "2201506815    1\n",
      "4321656809    1\n",
      "1697006012    1\n",
      "1901689169    1\n",
      "6032060028    1\n",
      "6040927878    1\n",
      "3449495495    1\n",
      "3990543961    1\n",
      "8972391475    1\n",
      "3418418812    1\n",
      "1983641803    1\n",
      "6014451367    1\n",
      "6027163276    1\n",
      "2833577858    1\n",
      "7321088861    1\n",
      "6045659373    1\n",
      "5528866190    1\n",
      "6027939517    1\n",
      "6031093257    1\n",
      "1883155712    1\n",
      "6026417971    1\n",
      "6031245697    1\n",
      "7493947593    1\n",
      "1859359090    1\n",
      "6053816462    1\n",
      "6027530676    1\n",
      "2988435647    1\n",
      "7838754747    1\n",
      "6053825179    1\n",
      "2988435792    1\n",
      "3036877534    1\n",
      "6056472223    1\n",
      "7460241128    1\n",
      "6028595154    1\n",
      "4053739078    1\n",
      "4071261250    1\n",
      "6014815775    1\n",
      "6024430122    1\n",
      "8229317581    1\n",
      "6027834438    1\n",
      "6024894515    1\n",
      "6024894530    1\n",
      "6024894513    1\n",
      "8169321141    1\n",
      "8200304626    1\n",
      "1968458114    1\n",
      "9359670710    1\n",
      "6021177135    1\n",
      "6027615161    1\n",
      "6027276892    1\n",
      "6041228287    1\n",
      "5536661253    1\n",
      "7357630367    1\n",
      "8178147277    1\n",
      "6026834850    1\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(OD_hamlet.isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>6058226279</th>\n",
       "      <th>6029307183</th>\n",
       "      <th>4998093094</th>\n",
       "      <th>2201506815</th>\n",
       "      <th>4321656809</th>\n",
       "      <th>1697006012</th>\n",
       "      <th>1901689169</th>\n",
       "      <th>6032060028</th>\n",
       "      <th>6040927878</th>\n",
       "      <th>3449495495</th>\n",
       "      <th>...</th>\n",
       "      <th>1968458114</th>\n",
       "      <th>9359670710</th>\n",
       "      <th>6021177135</th>\n",
       "      <th>6027615161</th>\n",
       "      <th>6027276892</th>\n",
       "      <th>6041228287</th>\n",
       "      <th>5536661253</th>\n",
       "      <th>7357630367</th>\n",
       "      <th>8178147277</th>\n",
       "      <th>6026834850</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3507831609</th>\n",
       "      <td>84.063858</td>\n",
       "      <td>3644.738929</td>\n",
       "      <td>6422.422876</td>\n",
       "      <td>6284.103925</td>\n",
       "      <td>713.191196</td>\n",
       "      <td>6387.985711</td>\n",
       "      <td>6429.326322</td>\n",
       "      <td>6369.695460</td>\n",
       "      <td>8083.256401</td>\n",
       "      <td>6238.597421</td>\n",
       "      <td>...</td>\n",
       "      <td>6454.535372</td>\n",
       "      <td>6475.177386</td>\n",
       "      <td>2314.361000</td>\n",
       "      <td>2314.067687</td>\n",
       "      <td>6426.638673</td>\n",
       "      <td>6422.532115</td>\n",
       "      <td>6422.003852</td>\n",
       "      <td>6421.916312</td>\n",
       "      <td>6242.077545</td>\n",
       "      <td>6248.940473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3507831510</th>\n",
       "      <td>90.422354</td>\n",
       "      <td>3651.097425</td>\n",
       "      <td>6428.781373</td>\n",
       "      <td>6290.462421</td>\n",
       "      <td>719.549692</td>\n",
       "      <td>6394.344207</td>\n",
       "      <td>6435.684818</td>\n",
       "      <td>6376.053956</td>\n",
       "      <td>8089.614898</td>\n",
       "      <td>6244.955918</td>\n",
       "      <td>...</td>\n",
       "      <td>6460.893868</td>\n",
       "      <td>6481.535883</td>\n",
       "      <td>2320.719497</td>\n",
       "      <td>2320.426184</td>\n",
       "      <td>6432.997170</td>\n",
       "      <td>6428.890612</td>\n",
       "      <td>6428.362348</td>\n",
       "      <td>6428.274808</td>\n",
       "      <td>6248.436041</td>\n",
       "      <td>6255.298970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6188134127</th>\n",
       "      <td>25.237636</td>\n",
       "      <td>3585.912707</td>\n",
       "      <td>6363.596654</td>\n",
       "      <td>6225.277703</td>\n",
       "      <td>654.364974</td>\n",
       "      <td>6329.159489</td>\n",
       "      <td>6370.500100</td>\n",
       "      <td>6310.869238</td>\n",
       "      <td>8024.430179</td>\n",
       "      <td>6179.771199</td>\n",
       "      <td>...</td>\n",
       "      <td>6395.709150</td>\n",
       "      <td>6416.351164</td>\n",
       "      <td>2255.534778</td>\n",
       "      <td>2255.241465</td>\n",
       "      <td>6367.812451</td>\n",
       "      <td>6363.705893</td>\n",
       "      <td>6363.177630</td>\n",
       "      <td>6363.090090</td>\n",
       "      <td>6183.251323</td>\n",
       "      <td>6190.114251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8631201421</th>\n",
       "      <td>1070.956229</td>\n",
       "      <td>2782.475931</td>\n",
       "      <td>6516.835077</td>\n",
       "      <td>6378.516125</td>\n",
       "      <td>807.603397</td>\n",
       "      <td>6482.397912</td>\n",
       "      <td>6523.738522</td>\n",
       "      <td>6464.107661</td>\n",
       "      <td>8177.668602</td>\n",
       "      <td>6333.009622</td>\n",
       "      <td>...</td>\n",
       "      <td>6548.947573</td>\n",
       "      <td>6569.589587</td>\n",
       "      <td>2079.632570</td>\n",
       "      <td>2079.339257</td>\n",
       "      <td>6521.050874</td>\n",
       "      <td>6516.944316</td>\n",
       "      <td>6516.416053</td>\n",
       "      <td>6516.328513</td>\n",
       "      <td>6336.489746</td>\n",
       "      <td>6343.352674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8598305977</th>\n",
       "      <td>1085.282837</td>\n",
       "      <td>2796.802539</td>\n",
       "      <td>6531.161686</td>\n",
       "      <td>6392.842734</td>\n",
       "      <td>821.930005</td>\n",
       "      <td>6496.724521</td>\n",
       "      <td>6538.065131</td>\n",
       "      <td>6478.434269</td>\n",
       "      <td>8191.995211</td>\n",
       "      <td>6347.336231</td>\n",
       "      <td>...</td>\n",
       "      <td>6563.274181</td>\n",
       "      <td>6583.916196</td>\n",
       "      <td>2093.959178</td>\n",
       "      <td>2093.665866</td>\n",
       "      <td>6535.377483</td>\n",
       "      <td>6531.270925</td>\n",
       "      <td>6530.742661</td>\n",
       "      <td>6530.655121</td>\n",
       "      <td>6350.816354</td>\n",
       "      <td>6357.679283</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3651042474</th>\n",
       "      <td>297700.252222</td>\n",
       "      <td>300368.447123</td>\n",
       "      <td>291549.442212</td>\n",
       "      <td>291636.821761</td>\n",
       "      <td>297206.142862</td>\n",
       "      <td>291494.026219</td>\n",
       "      <td>291533.947794</td>\n",
       "      <td>291475.291145</td>\n",
       "      <td>293435.974238</td>\n",
       "      <td>291544.871690</td>\n",
       "      <td>...</td>\n",
       "      <td>291389.910453</td>\n",
       "      <td>291318.372106</td>\n",
       "      <td>299038.069195</td>\n",
       "      <td>299037.775882</td>\n",
       "      <td>291553.658009</td>\n",
       "      <td>291527.153588</td>\n",
       "      <td>291526.625324</td>\n",
       "      <td>291526.537784</td>\n",
       "      <td>291547.128617</td>\n",
       "      <td>291547.965567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3651042508</th>\n",
       "      <td>297692.101967</td>\n",
       "      <td>300360.296868</td>\n",
       "      <td>291541.291958</td>\n",
       "      <td>291628.671506</td>\n",
       "      <td>297197.992607</td>\n",
       "      <td>291485.875964</td>\n",
       "      <td>291525.797539</td>\n",
       "      <td>291467.140890</td>\n",
       "      <td>293427.823983</td>\n",
       "      <td>291536.721435</td>\n",
       "      <td>...</td>\n",
       "      <td>291381.760198</td>\n",
       "      <td>291310.221851</td>\n",
       "      <td>299029.918940</td>\n",
       "      <td>299029.625627</td>\n",
       "      <td>291545.507755</td>\n",
       "      <td>291519.003333</td>\n",
       "      <td>291518.475069</td>\n",
       "      <td>291518.387529</td>\n",
       "      <td>291538.978362</td>\n",
       "      <td>291539.815312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3651042501</th>\n",
       "      <td>297697.597083</td>\n",
       "      <td>300365.791984</td>\n",
       "      <td>291546.787073</td>\n",
       "      <td>291634.166622</td>\n",
       "      <td>297203.487723</td>\n",
       "      <td>291491.371079</td>\n",
       "      <td>291531.292654</td>\n",
       "      <td>291472.636005</td>\n",
       "      <td>293433.319099</td>\n",
       "      <td>291542.216551</td>\n",
       "      <td>...</td>\n",
       "      <td>291387.255313</td>\n",
       "      <td>291315.716967</td>\n",
       "      <td>299035.414055</td>\n",
       "      <td>299035.120743</td>\n",
       "      <td>291551.002870</td>\n",
       "      <td>291524.498448</td>\n",
       "      <td>291523.970184</td>\n",
       "      <td>291523.882645</td>\n",
       "      <td>291544.473478</td>\n",
       "      <td>291545.310427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3651042393</th>\n",
       "      <td>297684.066780</td>\n",
       "      <td>300352.261682</td>\n",
       "      <td>291533.256771</td>\n",
       "      <td>291620.636320</td>\n",
       "      <td>297189.957421</td>\n",
       "      <td>291477.840777</td>\n",
       "      <td>291517.762352</td>\n",
       "      <td>291459.105703</td>\n",
       "      <td>293419.788796</td>\n",
       "      <td>291528.686249</td>\n",
       "      <td>...</td>\n",
       "      <td>291373.725011</td>\n",
       "      <td>291302.186665</td>\n",
       "      <td>299021.883753</td>\n",
       "      <td>299021.590440</td>\n",
       "      <td>291537.472568</td>\n",
       "      <td>291510.968146</td>\n",
       "      <td>291510.439882</td>\n",
       "      <td>291510.352342</td>\n",
       "      <td>291530.943175</td>\n",
       "      <td>291531.780125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6323434997</th>\n",
       "      <td>298369.612336</td>\n",
       "      <td>301037.807237</td>\n",
       "      <td>292218.802326</td>\n",
       "      <td>292306.181875</td>\n",
       "      <td>297875.502976</td>\n",
       "      <td>292163.386332</td>\n",
       "      <td>292203.307907</td>\n",
       "      <td>292144.651258</td>\n",
       "      <td>294105.334352</td>\n",
       "      <td>292214.231804</td>\n",
       "      <td>...</td>\n",
       "      <td>292059.270566</td>\n",
       "      <td>291987.732220</td>\n",
       "      <td>299707.429308</td>\n",
       "      <td>299707.135996</td>\n",
       "      <td>292223.018123</td>\n",
       "      <td>292196.513701</td>\n",
       "      <td>292195.985437</td>\n",
       "      <td>292195.897897</td>\n",
       "      <td>292216.488731</td>\n",
       "      <td>292217.325680</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>967552 rows × 58 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               6058226279     6029307183     4998093094     2201506815  \\\n",
       "3507831609      84.063858    3644.738929    6422.422876    6284.103925   \n",
       "3507831510      90.422354    3651.097425    6428.781373    6290.462421   \n",
       "6188134127      25.237636    3585.912707    6363.596654    6225.277703   \n",
       "8631201421    1070.956229    2782.475931    6516.835077    6378.516125   \n",
       "8598305977    1085.282837    2796.802539    6531.161686    6392.842734   \n",
       "...                   ...            ...            ...            ...   \n",
       "3651042474  297700.252222  300368.447123  291549.442212  291636.821761   \n",
       "3651042508  297692.101967  300360.296868  291541.291958  291628.671506   \n",
       "3651042501  297697.597083  300365.791984  291546.787073  291634.166622   \n",
       "3651042393  297684.066780  300352.261682  291533.256771  291620.636320   \n",
       "6323434997  298369.612336  301037.807237  292218.802326  292306.181875   \n",
       "\n",
       "               4321656809     1697006012     1901689169     6032060028  \\\n",
       "3507831609     713.191196    6387.985711    6429.326322    6369.695460   \n",
       "3507831510     719.549692    6394.344207    6435.684818    6376.053956   \n",
       "6188134127     654.364974    6329.159489    6370.500100    6310.869238   \n",
       "8631201421     807.603397    6482.397912    6523.738522    6464.107661   \n",
       "8598305977     821.930005    6496.724521    6538.065131    6478.434269   \n",
       "...                   ...            ...            ...            ...   \n",
       "3651042474  297206.142862  291494.026219  291533.947794  291475.291145   \n",
       "3651042508  297197.992607  291485.875964  291525.797539  291467.140890   \n",
       "3651042501  297203.487723  291491.371079  291531.292654  291472.636005   \n",
       "3651042393  297189.957421  291477.840777  291517.762352  291459.105703   \n",
       "6323434997  297875.502976  292163.386332  292203.307907  292144.651258   \n",
       "\n",
       "               6040927878     3449495495  ...     1968458114     9359670710  \\\n",
       "3507831609    8083.256401    6238.597421  ...    6454.535372    6475.177386   \n",
       "3507831510    8089.614898    6244.955918  ...    6460.893868    6481.535883   \n",
       "6188134127    8024.430179    6179.771199  ...    6395.709150    6416.351164   \n",
       "8631201421    8177.668602    6333.009622  ...    6548.947573    6569.589587   \n",
       "8598305977    8191.995211    6347.336231  ...    6563.274181    6583.916196   \n",
       "...                   ...            ...  ...            ...            ...   \n",
       "3651042474  293435.974238  291544.871690  ...  291389.910453  291318.372106   \n",
       "3651042508  293427.823983  291536.721435  ...  291381.760198  291310.221851   \n",
       "3651042501  293433.319099  291542.216551  ...  291387.255313  291315.716967   \n",
       "3651042393  293419.788796  291528.686249  ...  291373.725011  291302.186665   \n",
       "6323434997  294105.334352  292214.231804  ...  292059.270566  291987.732220   \n",
       "\n",
       "               6021177135     6027615161     6027276892     6041228287  \\\n",
       "3507831609    2314.361000    2314.067687    6426.638673    6422.532115   \n",
       "3507831510    2320.719497    2320.426184    6432.997170    6428.890612   \n",
       "6188134127    2255.534778    2255.241465    6367.812451    6363.705893   \n",
       "8631201421    2079.632570    2079.339257    6521.050874    6516.944316   \n",
       "8598305977    2093.959178    2093.665866    6535.377483    6531.270925   \n",
       "...                   ...            ...            ...            ...   \n",
       "3651042474  299038.069195  299037.775882  291553.658009  291527.153588   \n",
       "3651042508  299029.918940  299029.625627  291545.507755  291519.003333   \n",
       "3651042501  299035.414055  299035.120743  291551.002870  291524.498448   \n",
       "3651042393  299021.883753  299021.590440  291537.472568  291510.968146   \n",
       "6323434997  299707.429308  299707.135996  292223.018123  292196.513701   \n",
       "\n",
       "               5536661253     7357630367     8178147277     6026834850  \n",
       "3507831609    6422.003852    6421.916312    6242.077545    6248.940473  \n",
       "3507831510    6428.362348    6428.274808    6248.436041    6255.298970  \n",
       "6188134127    6363.177630    6363.090090    6183.251323    6190.114251  \n",
       "8631201421    6516.416053    6516.328513    6336.489746    6343.352674  \n",
       "8598305977    6530.742661    6530.655121    6350.816354    6357.679283  \n",
       "...                   ...            ...            ...            ...  \n",
       "3651042474  291526.625324  291526.537784  291547.128617  291547.965567  \n",
       "3651042508  291518.475069  291518.387529  291538.978362  291539.815312  \n",
       "3651042501  291523.970184  291523.882645  291544.473478  291545.310427  \n",
       "3651042393  291510.439882  291510.352342  291530.943175  291531.780125  \n",
       "6323434997  292195.985437  292195.897897  292216.488731  292217.325680  \n",
       "\n",
       "[967552 rows x 58 columns]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create origin-specific matrix and save to file.\n",
    "OD_ag = OD_df.loc[list_ag,:]\n",
    "OD_ag = OD_ag[OD_ag < fail_value] / 60 \n",
    "OD_ag.to_csv(os.path.join(out_pth, 'OD_ag_pre-project_flood50.csv'))\n",
    "OD_ag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6058226279    2\n",
      "6029307183    2\n",
      "4998093094    2\n",
      "2201506815    2\n",
      "4321656809    2\n",
      "1697006012    2\n",
      "1901689169    2\n",
      "6032060028    2\n",
      "6040927878    2\n",
      "3449495495    2\n",
      "3990543961    2\n",
      "8972391475    2\n",
      "3418418812    2\n",
      "1983641803    2\n",
      "6014451367    2\n",
      "6027163276    2\n",
      "2833577858    2\n",
      "7321088861    2\n",
      "6045659373    2\n",
      "5528866190    2\n",
      "6027939517    2\n",
      "6031093257    2\n",
      "1883155712    2\n",
      "6026417971    2\n",
      "6031245697    2\n",
      "7493947593    2\n",
      "1859359090    2\n",
      "6053816462    2\n",
      "6027530676    2\n",
      "2988435647    2\n",
      "7838754747    2\n",
      "6053825179    2\n",
      "2988435792    2\n",
      "3036877534    2\n",
      "6056472223    2\n",
      "7460241128    2\n",
      "6028595154    2\n",
      "4053739078    2\n",
      "4071261250    2\n",
      "6014815775    2\n",
      "6024430122    2\n",
      "8229317581    2\n",
      "6027834438    2\n",
      "6024894515    2\n",
      "6024894530    2\n",
      "6024894513    2\n",
      "8169321141    2\n",
      "8200304626    2\n",
      "1968458114    2\n",
      "9359670710    2\n",
      "6021177135    2\n",
      "6027615161    2\n",
      "6027276892    2\n",
      "6041228287    2\n",
      "5536661253    2\n",
      "7357630367    2\n",
      "8178147277    2\n",
      "6026834850    2\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(OD_ag.isna().sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Filter 1st nearest"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Check each file to make sure nearest neighbor column is named correctly. If not, rename."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GISFolder', 'GeoDataFrame', 'In', 'LineString', 'MultiLineString', 'OD_ag', 'OD_hamlet', 'Out', 'Point', 'Polygon', '_', '_1', '_14', '_23', '_24', '_34', '_38', '_39', '_45', '_46', '_48', '_50', '_7', '_8', '_9', '__', '___', '__builtin__', '__builtins__', '__doc__', '__loader__', '__name__', '__package__', '__spec__', '_dh', '_i', '_i1', '_i10', '_i11', '_i12', '_i13', '_i14', '_i15', '_i16', '_i17', '_i18', '_i19', '_i2', '_i20', '_i21', '_i22', '_i23', '_i24', '_i25', '_i26', '_i27', '_i28', '_i29', '_i3', '_i30', '_i31', '_i32', '_i33', '_i34', '_i35', '_i36', '_i37', '_i38', '_i39', '_i4', '_i40', '_i41', '_i42', '_i43', '_i44', '_i45', '_i46', '_i47', '_i48', '_i49', '_i5', '_i50', '_i51', '_i52', '_i6', '_i7', '_i8', '_i9', '_ih', '_ii', '_iii', '_oh', 'box', 'dests', 'exit', 'fail_value', 'fiona', 'gdal', 'get_ipython', 'glob', 'gn', 'gpd', 'importlib', 'linemerge', 'list_ag', 'list_hamlet', 'loads', 'losm', 'np', 'nx', 'origins', 'originslist', 'os', 'out_pth', 'ox', 'pc_flooded', 'pd', 'peartree', 'plt', 'pth', 'quit', 'rt', 'shapely', 'subprocess', 'sys', 'team_pth', 'time', 'transform', 'unary_union', 'wkt']\n"
     ]
    }
   ],
   "source": [
    "del gTime, ag_snap, hamlet_snap, HDurban_snap, OD, OD_df, OD_min\n",
    "print(dir())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reload from file even if already loaded. Quickest way to ensure NN is a column rather than only the index.\n",
    "OD_hamlet = os.path.join(out_pth, \"OD_hamlet_pre-project_flood50.csv\")\n",
    "OD_hamlet = pd.read_csv(OD_hamlet)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "OD_ag = os.path.join(out_pth, \"OD_ag_pre-project_flood50.csv\")\n",
    "OD_ag = pd.read_csv(OD_ag)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "OD_ag.rename(columns={'Unnamed: 0': 'NN'}, inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "OD_hamlet.rename(columns={'Unnamed: 0': 'NN'}, inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NN</th>\n",
       "      <th>6058226279</th>\n",
       "      <th>6029307183</th>\n",
       "      <th>4998093094</th>\n",
       "      <th>2201506815</th>\n",
       "      <th>4321656809</th>\n",
       "      <th>1697006012</th>\n",
       "      <th>1901689169</th>\n",
       "      <th>6032060028</th>\n",
       "      <th>6040927878</th>\n",
       "      <th>...</th>\n",
       "      <th>1968458114</th>\n",
       "      <th>9359670710</th>\n",
       "      <th>6021177135</th>\n",
       "      <th>6027615161</th>\n",
       "      <th>6027276892</th>\n",
       "      <th>6041228287</th>\n",
       "      <th>5536661253</th>\n",
       "      <th>7357630367</th>\n",
       "      <th>8178147277</th>\n",
       "      <th>6026834850</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3507831609</td>\n",
       "      <td>84.063858</td>\n",
       "      <td>3644.738929</td>\n",
       "      <td>6422.422876</td>\n",
       "      <td>6284.103925</td>\n",
       "      <td>713.191196</td>\n",
       "      <td>6387.985711</td>\n",
       "      <td>6429.326322</td>\n",
       "      <td>6369.695460</td>\n",
       "      <td>8083.256401</td>\n",
       "      <td>...</td>\n",
       "      <td>6454.535372</td>\n",
       "      <td>6475.177386</td>\n",
       "      <td>2314.361000</td>\n",
       "      <td>2314.067687</td>\n",
       "      <td>6426.638673</td>\n",
       "      <td>6422.532115</td>\n",
       "      <td>6422.003852</td>\n",
       "      <td>6421.916312</td>\n",
       "      <td>6242.077545</td>\n",
       "      <td>6248.940473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3507831510</td>\n",
       "      <td>90.422354</td>\n",
       "      <td>3651.097425</td>\n",
       "      <td>6428.781373</td>\n",
       "      <td>6290.462421</td>\n",
       "      <td>719.549692</td>\n",
       "      <td>6394.344207</td>\n",
       "      <td>6435.684818</td>\n",
       "      <td>6376.053956</td>\n",
       "      <td>8089.614898</td>\n",
       "      <td>...</td>\n",
       "      <td>6460.893868</td>\n",
       "      <td>6481.535883</td>\n",
       "      <td>2320.719497</td>\n",
       "      <td>2320.426184</td>\n",
       "      <td>6432.997170</td>\n",
       "      <td>6428.890612</td>\n",
       "      <td>6428.362348</td>\n",
       "      <td>6428.274808</td>\n",
       "      <td>6248.436041</td>\n",
       "      <td>6255.298970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6188134127</td>\n",
       "      <td>25.237636</td>\n",
       "      <td>3585.912707</td>\n",
       "      <td>6363.596654</td>\n",
       "      <td>6225.277703</td>\n",
       "      <td>654.364974</td>\n",
       "      <td>6329.159489</td>\n",
       "      <td>6370.500100</td>\n",
       "      <td>6310.869238</td>\n",
       "      <td>8024.430179</td>\n",
       "      <td>...</td>\n",
       "      <td>6395.709150</td>\n",
       "      <td>6416.351164</td>\n",
       "      <td>2255.534778</td>\n",
       "      <td>2255.241465</td>\n",
       "      <td>6367.812451</td>\n",
       "      <td>6363.705893</td>\n",
       "      <td>6363.177630</td>\n",
       "      <td>6363.090090</td>\n",
       "      <td>6183.251323</td>\n",
       "      <td>6190.114251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8631201421</td>\n",
       "      <td>1070.956229</td>\n",
       "      <td>2782.475931</td>\n",
       "      <td>6516.835077</td>\n",
       "      <td>6378.516125</td>\n",
       "      <td>807.603397</td>\n",
       "      <td>6482.397912</td>\n",
       "      <td>6523.738522</td>\n",
       "      <td>6464.107661</td>\n",
       "      <td>8177.668602</td>\n",
       "      <td>...</td>\n",
       "      <td>6548.947573</td>\n",
       "      <td>6569.589587</td>\n",
       "      <td>2079.632570</td>\n",
       "      <td>2079.339257</td>\n",
       "      <td>6521.050874</td>\n",
       "      <td>6516.944316</td>\n",
       "      <td>6516.416053</td>\n",
       "      <td>6516.328513</td>\n",
       "      <td>6336.489746</td>\n",
       "      <td>6343.352674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8598305977</td>\n",
       "      <td>1085.282837</td>\n",
       "      <td>2796.802539</td>\n",
       "      <td>6531.161686</td>\n",
       "      <td>6392.842734</td>\n",
       "      <td>821.930005</td>\n",
       "      <td>6496.724521</td>\n",
       "      <td>6538.065131</td>\n",
       "      <td>6478.434269</td>\n",
       "      <td>8191.995211</td>\n",
       "      <td>...</td>\n",
       "      <td>6563.274181</td>\n",
       "      <td>6583.916196</td>\n",
       "      <td>2093.959178</td>\n",
       "      <td>2093.665866</td>\n",
       "      <td>6535.377483</td>\n",
       "      <td>6531.270925</td>\n",
       "      <td>6530.742661</td>\n",
       "      <td>6530.655121</td>\n",
       "      <td>6350.816354</td>\n",
       "      <td>6357.679283</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>967547</th>\n",
       "      <td>3651042474</td>\n",
       "      <td>297700.252222</td>\n",
       "      <td>300368.447123</td>\n",
       "      <td>291549.442212</td>\n",
       "      <td>291636.821761</td>\n",
       "      <td>297206.142862</td>\n",
       "      <td>291494.026219</td>\n",
       "      <td>291533.947794</td>\n",
       "      <td>291475.291145</td>\n",
       "      <td>293435.974238</td>\n",
       "      <td>...</td>\n",
       "      <td>291389.910453</td>\n",
       "      <td>291318.372106</td>\n",
       "      <td>299038.069195</td>\n",
       "      <td>299037.775882</td>\n",
       "      <td>291553.658009</td>\n",
       "      <td>291527.153588</td>\n",
       "      <td>291526.625324</td>\n",
       "      <td>291526.537784</td>\n",
       "      <td>291547.128617</td>\n",
       "      <td>291547.965567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>967548</th>\n",
       "      <td>3651042508</td>\n",
       "      <td>297692.101967</td>\n",
       "      <td>300360.296868</td>\n",
       "      <td>291541.291958</td>\n",
       "      <td>291628.671506</td>\n",
       "      <td>297197.992607</td>\n",
       "      <td>291485.875964</td>\n",
       "      <td>291525.797539</td>\n",
       "      <td>291467.140890</td>\n",
       "      <td>293427.823983</td>\n",
       "      <td>...</td>\n",
       "      <td>291381.760198</td>\n",
       "      <td>291310.221851</td>\n",
       "      <td>299029.918940</td>\n",
       "      <td>299029.625627</td>\n",
       "      <td>291545.507755</td>\n",
       "      <td>291519.003333</td>\n",
       "      <td>291518.475069</td>\n",
       "      <td>291518.387529</td>\n",
       "      <td>291538.978362</td>\n",
       "      <td>291539.815312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>967549</th>\n",
       "      <td>3651042501</td>\n",
       "      <td>297697.597083</td>\n",
       "      <td>300365.791984</td>\n",
       "      <td>291546.787073</td>\n",
       "      <td>291634.166622</td>\n",
       "      <td>297203.487723</td>\n",
       "      <td>291491.371079</td>\n",
       "      <td>291531.292654</td>\n",
       "      <td>291472.636005</td>\n",
       "      <td>293433.319099</td>\n",
       "      <td>...</td>\n",
       "      <td>291387.255313</td>\n",
       "      <td>291315.716967</td>\n",
       "      <td>299035.414055</td>\n",
       "      <td>299035.120743</td>\n",
       "      <td>291551.002870</td>\n",
       "      <td>291524.498448</td>\n",
       "      <td>291523.970184</td>\n",
       "      <td>291523.882645</td>\n",
       "      <td>291544.473478</td>\n",
       "      <td>291545.310427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>967550</th>\n",
       "      <td>3651042393</td>\n",
       "      <td>297684.066780</td>\n",
       "      <td>300352.261682</td>\n",
       "      <td>291533.256771</td>\n",
       "      <td>291620.636320</td>\n",
       "      <td>297189.957421</td>\n",
       "      <td>291477.840777</td>\n",
       "      <td>291517.762352</td>\n",
       "      <td>291459.105703</td>\n",
       "      <td>293419.788796</td>\n",
       "      <td>...</td>\n",
       "      <td>291373.725011</td>\n",
       "      <td>291302.186665</td>\n",
       "      <td>299021.883753</td>\n",
       "      <td>299021.590440</td>\n",
       "      <td>291537.472568</td>\n",
       "      <td>291510.968146</td>\n",
       "      <td>291510.439882</td>\n",
       "      <td>291510.352342</td>\n",
       "      <td>291530.943175</td>\n",
       "      <td>291531.780125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>967551</th>\n",
       "      <td>6323434997</td>\n",
       "      <td>298369.612336</td>\n",
       "      <td>301037.807237</td>\n",
       "      <td>292218.802326</td>\n",
       "      <td>292306.181875</td>\n",
       "      <td>297875.502976</td>\n",
       "      <td>292163.386332</td>\n",
       "      <td>292203.307907</td>\n",
       "      <td>292144.651258</td>\n",
       "      <td>294105.334352</td>\n",
       "      <td>...</td>\n",
       "      <td>292059.270566</td>\n",
       "      <td>291987.732220</td>\n",
       "      <td>299707.429308</td>\n",
       "      <td>299707.135996</td>\n",
       "      <td>292223.018123</td>\n",
       "      <td>292196.513701</td>\n",
       "      <td>292195.985437</td>\n",
       "      <td>292195.897897</td>\n",
       "      <td>292216.488731</td>\n",
       "      <td>292217.325680</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>967552 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                NN     6058226279     6029307183     4998093094  \\\n",
       "0       3507831609      84.063858    3644.738929    6422.422876   \n",
       "1       3507831510      90.422354    3651.097425    6428.781373   \n",
       "2       6188134127      25.237636    3585.912707    6363.596654   \n",
       "3       8631201421    1070.956229    2782.475931    6516.835077   \n",
       "4       8598305977    1085.282837    2796.802539    6531.161686   \n",
       "...            ...            ...            ...            ...   \n",
       "967547  3651042474  297700.252222  300368.447123  291549.442212   \n",
       "967548  3651042508  297692.101967  300360.296868  291541.291958   \n",
       "967549  3651042501  297697.597083  300365.791984  291546.787073   \n",
       "967550  3651042393  297684.066780  300352.261682  291533.256771   \n",
       "967551  6323434997  298369.612336  301037.807237  292218.802326   \n",
       "\n",
       "           2201506815     4321656809     1697006012     1901689169  \\\n",
       "0         6284.103925     713.191196    6387.985711    6429.326322   \n",
       "1         6290.462421     719.549692    6394.344207    6435.684818   \n",
       "2         6225.277703     654.364974    6329.159489    6370.500100   \n",
       "3         6378.516125     807.603397    6482.397912    6523.738522   \n",
       "4         6392.842734     821.930005    6496.724521    6538.065131   \n",
       "...               ...            ...            ...            ...   \n",
       "967547  291636.821761  297206.142862  291494.026219  291533.947794   \n",
       "967548  291628.671506  297197.992607  291485.875964  291525.797539   \n",
       "967549  291634.166622  297203.487723  291491.371079  291531.292654   \n",
       "967550  291620.636320  297189.957421  291477.840777  291517.762352   \n",
       "967551  292306.181875  297875.502976  292163.386332  292203.307907   \n",
       "\n",
       "           6032060028     6040927878  ...     1968458114     9359670710  \\\n",
       "0         6369.695460    8083.256401  ...    6454.535372    6475.177386   \n",
       "1         6376.053956    8089.614898  ...    6460.893868    6481.535883   \n",
       "2         6310.869238    8024.430179  ...    6395.709150    6416.351164   \n",
       "3         6464.107661    8177.668602  ...    6548.947573    6569.589587   \n",
       "4         6478.434269    8191.995211  ...    6563.274181    6583.916196   \n",
       "...               ...            ...  ...            ...            ...   \n",
       "967547  291475.291145  293435.974238  ...  291389.910453  291318.372106   \n",
       "967548  291467.140890  293427.823983  ...  291381.760198  291310.221851   \n",
       "967549  291472.636005  293433.319099  ...  291387.255313  291315.716967   \n",
       "967550  291459.105703  293419.788796  ...  291373.725011  291302.186665   \n",
       "967551  292144.651258  294105.334352  ...  292059.270566  291987.732220   \n",
       "\n",
       "           6021177135     6027615161     6027276892     6041228287  \\\n",
       "0         2314.361000    2314.067687    6426.638673    6422.532115   \n",
       "1         2320.719497    2320.426184    6432.997170    6428.890612   \n",
       "2         2255.534778    2255.241465    6367.812451    6363.705893   \n",
       "3         2079.632570    2079.339257    6521.050874    6516.944316   \n",
       "4         2093.959178    2093.665866    6535.377483    6531.270925   \n",
       "...               ...            ...            ...            ...   \n",
       "967547  299038.069195  299037.775882  291553.658009  291527.153588   \n",
       "967548  299029.918940  299029.625627  291545.507755  291519.003333   \n",
       "967549  299035.414055  299035.120743  291551.002870  291524.498448   \n",
       "967550  299021.883753  299021.590440  291537.472568  291510.968146   \n",
       "967551  299707.429308  299707.135996  292223.018123  292196.513701   \n",
       "\n",
       "           5536661253     7357630367     8178147277     6026834850  \n",
       "0         6422.003852    6421.916312    6242.077545    6248.940473  \n",
       "1         6428.362348    6428.274808    6248.436041    6255.298970  \n",
       "2         6363.177630    6363.090090    6183.251323    6190.114251  \n",
       "3         6516.416053    6516.328513    6336.489746    6343.352674  \n",
       "4         6530.742661    6530.655121    6350.816354    6357.679283  \n",
       "...               ...            ...            ...            ...  \n",
       "967547  291526.625324  291526.537784  291547.128617  291547.965567  \n",
       "967548  291518.475069  291518.387529  291538.978362  291539.815312  \n",
       "967549  291523.970184  291523.882645  291544.473478  291545.310427  \n",
       "967550  291510.439882  291510.352342  291530.943175  291531.780125  \n",
       "967551  292195.985437  292195.897897  292216.488731  292217.325680  \n",
       "\n",
       "[967552 rows x 59 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "OD_ag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NN</th>\n",
       "      <th>6058226279</th>\n",
       "      <th>6029307183</th>\n",
       "      <th>4998093094</th>\n",
       "      <th>2201506815</th>\n",
       "      <th>4321656809</th>\n",
       "      <th>1697006012</th>\n",
       "      <th>1901689169</th>\n",
       "      <th>6032060028</th>\n",
       "      <th>6040927878</th>\n",
       "      <th>...</th>\n",
       "      <th>1968458114</th>\n",
       "      <th>9359670710</th>\n",
       "      <th>6021177135</th>\n",
       "      <th>6027615161</th>\n",
       "      <th>6027276892</th>\n",
       "      <th>6041228287</th>\n",
       "      <th>5536661253</th>\n",
       "      <th>7357630367</th>\n",
       "      <th>8178147277</th>\n",
       "      <th>6026834850</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7761872870</td>\n",
       "      <td>7555.041976</td>\n",
       "      <td>11114.143037</td>\n",
       "      <td>13891.826984</td>\n",
       "      <td>13753.508032</td>\n",
       "      <td>8182.595304</td>\n",
       "      <td>13857.389819</td>\n",
       "      <td>13898.730429</td>\n",
       "      <td>13839.099568</td>\n",
       "      <td>15552.660509</td>\n",
       "      <td>...</td>\n",
       "      <td>13923.939479</td>\n",
       "      <td>13944.581494</td>\n",
       "      <td>9783.765108</td>\n",
       "      <td>9783.471795</td>\n",
       "      <td>13896.042781</td>\n",
       "      <td>13891.936223</td>\n",
       "      <td>13891.407959</td>\n",
       "      <td>13891.320420</td>\n",
       "      <td>13711.481653</td>\n",
       "      <td>13718.344581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7761872869</td>\n",
       "      <td>7555.023417</td>\n",
       "      <td>11114.124477</td>\n",
       "      <td>13891.808424</td>\n",
       "      <td>13753.489473</td>\n",
       "      <td>8182.576744</td>\n",
       "      <td>13857.371259</td>\n",
       "      <td>13898.711870</td>\n",
       "      <td>13839.081008</td>\n",
       "      <td>15552.641950</td>\n",
       "      <td>...</td>\n",
       "      <td>13923.920920</td>\n",
       "      <td>13944.562934</td>\n",
       "      <td>9783.746548</td>\n",
       "      <td>9783.453236</td>\n",
       "      <td>13896.024221</td>\n",
       "      <td>13891.917664</td>\n",
       "      <td>13891.389400</td>\n",
       "      <td>13891.301860</td>\n",
       "      <td>13711.463093</td>\n",
       "      <td>13718.326022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6442044321</td>\n",
       "      <td>7554.013585</td>\n",
       "      <td>11113.114645</td>\n",
       "      <td>13890.798592</td>\n",
       "      <td>13752.479641</td>\n",
       "      <td>8181.566912</td>\n",
       "      <td>13856.361427</td>\n",
       "      <td>13897.702038</td>\n",
       "      <td>13838.071176</td>\n",
       "      <td>15551.632118</td>\n",
       "      <td>...</td>\n",
       "      <td>13922.911088</td>\n",
       "      <td>13943.553102</td>\n",
       "      <td>9782.736716</td>\n",
       "      <td>9782.443404</td>\n",
       "      <td>13895.014389</td>\n",
       "      <td>13890.907832</td>\n",
       "      <td>13890.379568</td>\n",
       "      <td>13890.292028</td>\n",
       "      <td>13710.453261</td>\n",
       "      <td>13717.316190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2142496418</td>\n",
       "      <td>7555.467107</td>\n",
       "      <td>11114.568167</td>\n",
       "      <td>13892.252114</td>\n",
       "      <td>13753.933163</td>\n",
       "      <td>8183.020434</td>\n",
       "      <td>13857.814949</td>\n",
       "      <td>13899.155560</td>\n",
       "      <td>13839.524698</td>\n",
       "      <td>15553.085640</td>\n",
       "      <td>...</td>\n",
       "      <td>13924.364610</td>\n",
       "      <td>13945.006624</td>\n",
       "      <td>9784.190238</td>\n",
       "      <td>9783.896926</td>\n",
       "      <td>13896.467911</td>\n",
       "      <td>13892.361354</td>\n",
       "      <td>13891.833090</td>\n",
       "      <td>13891.745550</td>\n",
       "      <td>13711.906783</td>\n",
       "      <td>13718.769712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2142496429</td>\n",
       "      <td>7555.849481</td>\n",
       "      <td>11114.950541</td>\n",
       "      <td>13892.634488</td>\n",
       "      <td>13754.315537</td>\n",
       "      <td>8183.402808</td>\n",
       "      <td>13858.197323</td>\n",
       "      <td>13899.537934</td>\n",
       "      <td>13839.907072</td>\n",
       "      <td>15553.468014</td>\n",
       "      <td>...</td>\n",
       "      <td>13924.746984</td>\n",
       "      <td>13945.388998</td>\n",
       "      <td>9784.572612</td>\n",
       "      <td>9784.279300</td>\n",
       "      <td>13896.850285</td>\n",
       "      <td>13892.743728</td>\n",
       "      <td>13892.215464</td>\n",
       "      <td>13892.127924</td>\n",
       "      <td>13712.289157</td>\n",
       "      <td>13719.152086</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74657</th>\n",
       "      <td>9208004175</td>\n",
       "      <td>407808.542291</td>\n",
       "      <td>410476.737192</td>\n",
       "      <td>401657.732281</td>\n",
       "      <td>401745.111830</td>\n",
       "      <td>407314.432931</td>\n",
       "      <td>401602.316288</td>\n",
       "      <td>401642.237863</td>\n",
       "      <td>401583.581214</td>\n",
       "      <td>403544.264307</td>\n",
       "      <td>...</td>\n",
       "      <td>401498.200522</td>\n",
       "      <td>401426.662175</td>\n",
       "      <td>409146.359264</td>\n",
       "      <td>409146.065951</td>\n",
       "      <td>401661.948078</td>\n",
       "      <td>401635.443657</td>\n",
       "      <td>401634.915393</td>\n",
       "      <td>401634.827853</td>\n",
       "      <td>401655.418686</td>\n",
       "      <td>401656.255636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74658</th>\n",
       "      <td>3884280043</td>\n",
       "      <td>7782.266869</td>\n",
       "      <td>10121.321138</td>\n",
       "      <td>3678.828061</td>\n",
       "      <td>3580.973513</td>\n",
       "      <td>7518.914037</td>\n",
       "      <td>3641.568467</td>\n",
       "      <td>3681.490042</td>\n",
       "      <td>3622.800319</td>\n",
       "      <td>5380.125990</td>\n",
       "      <td>...</td>\n",
       "      <td>3486.750734</td>\n",
       "      <td>3560.877505</td>\n",
       "      <td>5560.727839</td>\n",
       "      <td>5560.434526</td>\n",
       "      <td>3683.043858</td>\n",
       "      <td>3674.695836</td>\n",
       "      <td>3674.167572</td>\n",
       "      <td>3674.080032</td>\n",
       "      <td>3498.737171</td>\n",
       "      <td>3491.253225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74659</th>\n",
       "      <td>3884280001</td>\n",
       "      <td>7775.404642</td>\n",
       "      <td>10114.458911</td>\n",
       "      <td>3671.965834</td>\n",
       "      <td>3574.111286</td>\n",
       "      <td>7512.051810</td>\n",
       "      <td>3634.706240</td>\n",
       "      <td>3674.627815</td>\n",
       "      <td>3615.938092</td>\n",
       "      <td>5373.263763</td>\n",
       "      <td>...</td>\n",
       "      <td>3479.888507</td>\n",
       "      <td>3554.015278</td>\n",
       "      <td>5553.865612</td>\n",
       "      <td>5553.572299</td>\n",
       "      <td>3676.181631</td>\n",
       "      <td>3667.833609</td>\n",
       "      <td>3667.305345</td>\n",
       "      <td>3667.217805</td>\n",
       "      <td>3491.874944</td>\n",
       "      <td>3484.390998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74660</th>\n",
       "      <td>8592243457</td>\n",
       "      <td>7768.998180</td>\n",
       "      <td>10108.052450</td>\n",
       "      <td>3647.759412</td>\n",
       "      <td>3549.904864</td>\n",
       "      <td>7505.645348</td>\n",
       "      <td>3610.499818</td>\n",
       "      <td>3650.421393</td>\n",
       "      <td>3591.731670</td>\n",
       "      <td>5349.057341</td>\n",
       "      <td>...</td>\n",
       "      <td>3455.682085</td>\n",
       "      <td>3529.808856</td>\n",
       "      <td>5547.459150</td>\n",
       "      <td>5547.165838</td>\n",
       "      <td>3651.975209</td>\n",
       "      <td>3643.627187</td>\n",
       "      <td>3643.098923</td>\n",
       "      <td>3643.011383</td>\n",
       "      <td>3467.668522</td>\n",
       "      <td>3460.184575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74661</th>\n",
       "      <td>3649149740</td>\n",
       "      <td>107430.606399</td>\n",
       "      <td>109769.660669</td>\n",
       "      <td>103269.209051</td>\n",
       "      <td>103171.354503</td>\n",
       "      <td>107167.253568</td>\n",
       "      <td>103231.949457</td>\n",
       "      <td>103271.871032</td>\n",
       "      <td>103213.181309</td>\n",
       "      <td>104970.506980</td>\n",
       "      <td>...</td>\n",
       "      <td>103077.131724</td>\n",
       "      <td>103151.258495</td>\n",
       "      <td>105209.067370</td>\n",
       "      <td>105208.774057</td>\n",
       "      <td>103273.424848</td>\n",
       "      <td>103265.076826</td>\n",
       "      <td>103264.548562</td>\n",
       "      <td>103264.461022</td>\n",
       "      <td>103089.118161</td>\n",
       "      <td>103081.634214</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74662 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               NN     6058226279     6029307183     4998093094     2201506815  \\\n",
       "0      7761872870    7555.041976   11114.143037   13891.826984   13753.508032   \n",
       "1      7761872869    7555.023417   11114.124477   13891.808424   13753.489473   \n",
       "2      6442044321    7554.013585   11113.114645   13890.798592   13752.479641   \n",
       "3      2142496418    7555.467107   11114.568167   13892.252114   13753.933163   \n",
       "4      2142496429    7555.849481   11114.950541   13892.634488   13754.315537   \n",
       "...           ...            ...            ...            ...            ...   \n",
       "74657  9208004175  407808.542291  410476.737192  401657.732281  401745.111830   \n",
       "74658  3884280043    7782.266869   10121.321138    3678.828061    3580.973513   \n",
       "74659  3884280001    7775.404642   10114.458911    3671.965834    3574.111286   \n",
       "74660  8592243457    7768.998180   10108.052450    3647.759412    3549.904864   \n",
       "74661  3649149740  107430.606399  109769.660669  103269.209051  103171.354503   \n",
       "\n",
       "          4321656809     1697006012     1901689169     6032060028  \\\n",
       "0        8182.595304   13857.389819   13898.730429   13839.099568   \n",
       "1        8182.576744   13857.371259   13898.711870   13839.081008   \n",
       "2        8181.566912   13856.361427   13897.702038   13838.071176   \n",
       "3        8183.020434   13857.814949   13899.155560   13839.524698   \n",
       "4        8183.402808   13858.197323   13899.537934   13839.907072   \n",
       "...              ...            ...            ...            ...   \n",
       "74657  407314.432931  401602.316288  401642.237863  401583.581214   \n",
       "74658    7518.914037    3641.568467    3681.490042    3622.800319   \n",
       "74659    7512.051810    3634.706240    3674.627815    3615.938092   \n",
       "74660    7505.645348    3610.499818    3650.421393    3591.731670   \n",
       "74661  107167.253568  103231.949457  103271.871032  103213.181309   \n",
       "\n",
       "          6040927878  ...     1968458114     9359670710     6021177135  \\\n",
       "0       15552.660509  ...   13923.939479   13944.581494    9783.765108   \n",
       "1       15552.641950  ...   13923.920920   13944.562934    9783.746548   \n",
       "2       15551.632118  ...   13922.911088   13943.553102    9782.736716   \n",
       "3       15553.085640  ...   13924.364610   13945.006624    9784.190238   \n",
       "4       15553.468014  ...   13924.746984   13945.388998    9784.572612   \n",
       "...              ...  ...            ...            ...            ...   \n",
       "74657  403544.264307  ...  401498.200522  401426.662175  409146.359264   \n",
       "74658    5380.125990  ...    3486.750734    3560.877505    5560.727839   \n",
       "74659    5373.263763  ...    3479.888507    3554.015278    5553.865612   \n",
       "74660    5349.057341  ...    3455.682085    3529.808856    5547.459150   \n",
       "74661  104970.506980  ...  103077.131724  103151.258495  105209.067370   \n",
       "\n",
       "          6027615161     6027276892     6041228287     5536661253  \\\n",
       "0        9783.471795   13896.042781   13891.936223   13891.407959   \n",
       "1        9783.453236   13896.024221   13891.917664   13891.389400   \n",
       "2        9782.443404   13895.014389   13890.907832   13890.379568   \n",
       "3        9783.896926   13896.467911   13892.361354   13891.833090   \n",
       "4        9784.279300   13896.850285   13892.743728   13892.215464   \n",
       "...              ...            ...            ...            ...   \n",
       "74657  409146.065951  401661.948078  401635.443657  401634.915393   \n",
       "74658    5560.434526    3683.043858    3674.695836    3674.167572   \n",
       "74659    5553.572299    3676.181631    3667.833609    3667.305345   \n",
       "74660    5547.165838    3651.975209    3643.627187    3643.098923   \n",
       "74661  105208.774057  103273.424848  103265.076826  103264.548562   \n",
       "\n",
       "          7357630367     8178147277     6026834850  \n",
       "0       13891.320420   13711.481653   13718.344581  \n",
       "1       13891.301860   13711.463093   13718.326022  \n",
       "2       13890.292028   13710.453261   13717.316190  \n",
       "3       13891.745550   13711.906783   13718.769712  \n",
       "4       13892.127924   13712.289157   13719.152086  \n",
       "...              ...            ...            ...  \n",
       "74657  401634.827853  401655.418686  401656.255636  \n",
       "74658    3674.080032    3498.737171    3491.253225  \n",
       "74659    3667.217805    3491.874944    3484.390998  \n",
       "74660    3643.011383    3467.668522    3460.184575  \n",
       "74661  103264.461022  103089.118161  103081.634214  \n",
       "\n",
       "[74662 rows x 59 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "OD_hamlet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Find first, second, and third nearest destination for each origin node. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "fail_value = 999999999"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NN</th>\n",
       "      <th>ag_HD1</th>\n",
       "      <th>ag_HD2</th>\n",
       "      <th>ag_HD3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3507831609</td>\n",
       "      <td>82.363044</td>\n",
       "      <td>84.063858</td>\n",
       "      <td>687.136217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3507831510</td>\n",
       "      <td>88.721541</td>\n",
       "      <td>90.422354</td>\n",
       "      <td>693.494714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6188134127</td>\n",
       "      <td>23.536822</td>\n",
       "      <td>25.237636</td>\n",
       "      <td>628.309995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8631201421</td>\n",
       "      <td>615.678582</td>\n",
       "      <td>807.603397</td>\n",
       "      <td>1070.956229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8598305977</td>\n",
       "      <td>630.005190</td>\n",
       "      <td>821.930005</td>\n",
       "      <td>1085.282837</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           NN      ag_HD1      ag_HD2       ag_HD3\n",
       "0  3507831609   82.363044   84.063858   687.136217\n",
       "1  3507831510   88.721541   90.422354   693.494714\n",
       "2  6188134127   23.536822   25.237636   628.309995\n",
       "3  8631201421  615.678582  807.603397  1070.956229\n",
       "4  8598305977  630.005190  821.930005  1085.282837"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Nearest\n",
    "OD_ag[\"ag_HD1\"] = 0\n",
    "sub = OD_ag.iloc[:,1:-1] # Filtering out the newly created field and the node ID column. (\"include everything between column 0 and the last column\")\n",
    "OD_ag[\"ag_HD1\"] = sub.min(axis=1) # Default is axis=0, meaning min value of each column selected. We want min of each row.\n",
    "ag1 = OD_ag[['NN', 'ag_HD1']] # Remove unnecessary OD values.\n",
    "\n",
    "\n",
    "# Second nearest\n",
    "dupes = OD_ag.apply(pd.Series.duplicated, axis = 1, keep=False) # If a number is repeated within a row, value is True. If not, False.\n",
    "# The first time this is done, there should be two True values per row, unless any POIs are equidistant.\n",
    "dupes = OD_ag.where(~dupes, fail_value) # For any value that appears more than once in its row, it is replaced with the fail_value.\n",
    "OD_ag[\"ag_HD2\"] = 0\n",
    "Dsub = dupes.iloc[:,1:] # Filtering out the node ID column. No need to filter 1st nearest as its new \"dupes\" value is too high to be caught.\n",
    "OD_ag[\"ag_HD2\"] = Dsub.min(axis=1) \n",
    "ag2 = OD_ag.loc[:,['NN', 'ag_HD2']] \n",
    "\n",
    "\n",
    "# Third nearest\n",
    "dupes = OD_ag.apply(pd.Series.duplicated, axis = 1, keep=False)\n",
    "# Since this includes both first and second nearest columns, there should be four True values per row, unless POIs are equidistant.\n",
    "dupes = OD_ag.where(~dupes, fail_value)\n",
    "\n",
    "OD_ag[\"ag_HD3\"] = 0\n",
    "Dsub = dupes.iloc[:,1:] # Filtering out the node ID column.\n",
    "OD_ag[\"ag_HD3\"] = Dsub.min(axis=1)\n",
    "ag3 = OD_ag.loc[:,['NN', 'ag_HD3']]\n",
    "\n",
    "# Combine and write to file\n",
    "ag_all = OD_ag.loc[:,['NN', 'ag_HD1', 'ag_HD2', 'ag_HD3']]\n",
    "ag_all.to_csv(os.path.join(out_pth, 'ag_to_HDurban_drive_pre-project_flood50.csv'))\n",
    "ag_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NN</th>\n",
       "      <th>ha_HD1</th>\n",
       "      <th>ha_HD2</th>\n",
       "      <th>ha_HD3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7761872870</td>\n",
       "      <td>7555.041976</td>\n",
       "      <td>7558.410156</td>\n",
       "      <td>8156.540325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7761872869</td>\n",
       "      <td>7555.023417</td>\n",
       "      <td>7558.391596</td>\n",
       "      <td>8156.521766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6442044321</td>\n",
       "      <td>7554.013585</td>\n",
       "      <td>7557.381764</td>\n",
       "      <td>8155.511934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2142496418</td>\n",
       "      <td>7555.467107</td>\n",
       "      <td>7558.835286</td>\n",
       "      <td>8156.965456</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2142496429</td>\n",
       "      <td>7555.849481</td>\n",
       "      <td>7559.217660</td>\n",
       "      <td>8157.347830</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           NN       ha_HD1       ha_HD2       ha_HD3\n",
       "0  7761872870  7555.041976  7558.410156  8156.540325\n",
       "1  7761872869  7555.023417  7558.391596  8156.521766\n",
       "2  6442044321  7554.013585  7557.381764  8155.511934\n",
       "3  2142496418  7555.467107  7558.835286  8156.965456\n",
       "4  2142496429  7555.849481  7559.217660  8157.347830"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Nearest\n",
    "OD_hamlet[\"ha_HD1\"] = 0\n",
    "sub = OD_hamlet.iloc[:,1:-1] # Filtering out the newly created field and the node ID column. (\"include everything between column 0 and the last column\")\n",
    "OD_hamlet[\"ha_HD1\"] = sub.min(axis=1) # Default is axis=0, meaning min value of each column selected. We want min of each row.\n",
    "hamlet1 = OD_hamlet[['NN', 'ha_HD1']] # Remove unnecessary OD values.\n",
    "\n",
    "\n",
    "# Second nearest\n",
    "dupes = OD_hamlet.apply(pd.Series.duplicated, axis = 1, keep=False) # If a number is repeated within a row, value is True. If not, False.\n",
    "# The first time this is done, there should be two True values per row, unless any POIs are equidistant.\n",
    "dupes = OD_hamlet.where(~dupes, fail_value) # For any value that appears more than once in its row, it is replaced with the fail_value.\n",
    "OD_hamlet[\"ha_HD2\"] = 0\n",
    "Dsub = dupes.iloc[:,1:] # Filtering out the node ID column. No need to filter 1st nearest as its new \"dupes\" value is too high to be caught.\n",
    "OD_hamlet[\"ha_HD2\"] = Dsub.min(axis=1) \n",
    "hamlet2 = OD_hamlet.loc[:,['NN', 'ha_HD2']] \n",
    "\n",
    "\n",
    "# Third nearest\n",
    "dupes = OD_hamlet.apply(pd.Series.duplicated, axis = 1, keep=False)\n",
    "# Since this includes both first and second nearest columns, there should be four True values per row, unless POIs are equidistant.\n",
    "dupes = OD_hamlet.where(~dupes, fail_value)\n",
    "OD_hamlet[\"ha_HD3\"] = 0\n",
    "Dsub = dupes.iloc[:,1:] # Filtering out the node ID column.\n",
    "OD_hamlet[\"ha_HD3\"] = Dsub.min(axis=1)\n",
    "hamlet3 = OD_hamlet.loc[:,['NN', 'ha_HD3']]\n",
    "\n",
    "\n",
    "# Combine and write to file\n",
    "hamlet_all = OD_hamlet.loc[:,['NN', 'ha_HD1', 'ha_HD2', 'ha_HD3']]\n",
    "hamlet_all.to_csv(os.path.join(out_pth, 'hamlet_to_HDurban_drive_pre-project_flood50.csv'))\n",
    "hamlet_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### End of script. Load into QGIS or Arc and visualize at 10 min intervals. \n",
    "QML file for symbology in QGIS:\n",
    "R:\\GEOGlobal\\Design\\symb_traveltimes_10min.qml"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}