SEN-Transport_flooding-post-project-scenario_2022-01-08.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_post-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 (2,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 8 columns):\n",
      " #   Column        Dtype  \n",
      "---  ------        -----  \n",
      " 0   Unnamed: 0    int64  \n",
      " 1   node_ID       int64  \n",
      " 2   ref           object \n",
      " 3   highway       object \n",
      " 4   x             float64\n",
      " 5   Unnamed: 0.1  int64  \n",
      " 6   y             float64\n",
      " 7   geometry      object \n",
      "dtypes: float64(2), int64(3), object(3)\n",
      "memory usage: 175.2+ MB\n",
      "None\n",
      "<class 'geopandas.geodataframe.GeoDataFrame'>\n",
      "RangeIndex: 6203383 entries, 0 to 6203382\n",
      "Data columns (total 23 columns):\n",
      " #   Column        Dtype   \n",
      "---  ------        -----   \n",
      " 0   stnode        int64   \n",
      " 1   endnode       int64   \n",
      " 2   access        object  \n",
      " 3   mode          object  \n",
      " 4   length        float64 \n",
      " 5   Unnamed: 0    int64   \n",
      " 6   junction      object  \n",
      " 7   lanes         float64 \n",
      " 8   highway       object  \n",
      " 9   time          float64 \n",
      " 10  area          object  \n",
      " 11  landuse       object  \n",
      " 12  oneway        bool    \n",
      " 13  bridge        object  \n",
      " 14  ref           object  \n",
      " 15  service       object  \n",
      " 16  Unnamed: 0.1  int64   \n",
      " 17  tunnel        object  \n",
      " 18  name          object  \n",
      " 19  osmid         int64   \n",
      " 20  width         object  \n",
      " 21  maxspeed      object  \n",
      " 22  geometry      geometry\n",
      "dtypes: bool(1), float64(3), geometry(1), int64(5), object(13)\n",
      "memory usage: 1.0+ GB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "nodes = os.path.join(out_pth, \"gTime_node_post-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 27 columns):\n",
      " #   Column        Dtype   \n",
      "---  ------        -----   \n",
      " 0   stnode        int64   \n",
      " 1   endnode       int64   \n",
      " 2   access        object  \n",
      " 3   mode          object  \n",
      " 4   length        float64 \n",
      " 5   Unnamed: 0    int64   \n",
      " 6   junction      object  \n",
      " 7   lanes         float64 \n",
      " 8   highway       object  \n",
      " 9   time          float64 \n",
      " 10  area          object  \n",
      " 11  landuse       object  \n",
      " 12  oneway        bool    \n",
      " 13  bridge        object  \n",
      " 14  ref           object  \n",
      " 15  service       object  \n",
      " 16  Unnamed: 0.1  int64   \n",
      " 17  tunnel        object  \n",
      " 18  name          object  \n",
      " 19  osmid         int64   \n",
      " 20  width         object  \n",
      " 21  maxspeed      object  \n",
      " 22  geometry      geometry\n",
      " 23  index_right   float64 \n",
      " 24  PFU_1in50     float64 \n",
      " 25  Shape_Length  float64 \n",
      " 26  Shape_Area    float64 \n",
      "dtypes: bool(1), float64(7), geometry(1), int64(5), object(13)\n",
      "memory usage: 1.3+ GB\n",
      "None\n",
      "             stnode     endnode access   mode  length  Unnamed: 0 junction  \\\n",
      "0         358284990  5217543379    NaN  drive  33.127           0      NaN   \n",
      "1         358284990  1888282175    NaN  drive  12.832           1      NaN   \n",
      "2         358284990  5329792467    NaN  drive  48.781           2      NaN   \n",
      "3        5217543379   358284990    NaN  drive  33.127           3      NaN   \n",
      "4        5217543379  5329928981    NaN  drive  17.124           4      NaN   \n",
      "...             ...         ...    ...    ...     ...         ...      ...   \n",
      "6203378  9165665160  9165665161    NaN  drive  13.295     6203374      NaN   \n",
      "6203379  9165665160  9165665159    NaN  drive  12.982     6203375      NaN   \n",
      "6203380  9165665161  9165665162    NaN  drive   5.476     6203376      NaN   \n",
      "6203381  9165665161  9165665160    NaN  drive  13.295     6203377      NaN   \n",
      "6203382  9165665162  9165665161    NaN  drive   5.476     6203378      NaN   \n",
      "\n",
      "         lanes       highway       time  ... tunnel   name      osmid width  \\\n",
      "0          NaN  unclassified   2.385144  ...    NaN  D 523   59618174   NaN   \n",
      "1          NaN      tertiary   0.769920  ...    NaN    NaN  178482063   NaN   \n",
      "2          NaN      tertiary   2.926860  ...    NaN    NaN  178482063   NaN   \n",
      "3          NaN  unclassified   2.385144  ...    NaN  D 523   59618174   NaN   \n",
      "4          NaN  unclassified   1.232928  ...    NaN  D 523   59618174   NaN   \n",
      "...        ...           ...        ...  ...    ...    ...        ...   ...   \n",
      "6203378    NaN          path  10.636000  ...    NaN    NaN  992024621   NaN   \n",
      "6203379    NaN          path  10.385600  ...    NaN    NaN  992024621   NaN   \n",
      "6203380    NaN          path   4.380800  ...    NaN    NaN  992024621   NaN   \n",
      "6203381    NaN          path  10.636000  ...    NaN    NaN  992024621   NaN   \n",
      "6203382    NaN          path   4.380800  ...    NaN    NaN  992024621   NaN   \n",
      "\n",
      "        maxspeed                                           geometry  \\\n",
      "0            NaN  LINESTRING (790871.607 1370083.257, 790848.290...   \n",
      "1            NaN  LINESTRING (790871.607 1370083.257, 790867.287...   \n",
      "2            NaN  LINESTRING (790871.607 1370083.257, 790904.086...   \n",
      "3            NaN  LINESTRING (790848.290 1370106.724, 790871.607...   \n",
      "4            NaN  LINESTRING (790848.290 1370106.724, 790838.054...   \n",
      "...          ...                                                ...   \n",
      "6203378      NaN  LINESTRING (232294.089 1631605.337, 232307.060...   \n",
      "6203379      NaN  LINESTRING (232294.089 1631605.337, 232281.090...   \n",
      "6203380      NaN  LINESTRING (232307.060 1631602.326, 232312.244...   \n",
      "6203381      NaN  LINESTRING (232307.060 1631602.326, 232294.089...   \n",
      "6203382      NaN  LINESTRING (232312.244 1631600.542, 232307.060...   \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 27 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_post-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_post-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>9165665160</td>\n",
       "      <td>9165665161</td>\n",
       "      <td>10.636000</td>\n",
       "      <td>13.295</td>\n",
       "      <td>path</td>\n",
       "      <td>992024621</td>\n",
       "      <td>LINESTRING (-17.48487070737049 14.745767002669...</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>9165665160</td>\n",
       "      <td>9165665159</td>\n",
       "      <td>10.385600</td>\n",
       "      <td>12.982</td>\n",
       "      <td>path</td>\n",
       "      <td>992024621</td>\n",
       "      <td>LINESTRING (-17.48487070737049 14.745767002669...</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>9165665161</td>\n",
       "      <td>9165665162</td>\n",
       "      <td>4.380800</td>\n",
       "      <td>5.476</td>\n",
       "      <td>path</td>\n",
       "      <td>992024621</td>\n",
       "      <td>LINESTRING (-17.48475000737059 14.745741102669...</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>9165665161</td>\n",
       "      <td>9165665160</td>\n",
       "      <td>10.636000</td>\n",
       "      <td>13.295</td>\n",
       "      <td>path</td>\n",
       "      <td>992024621</td>\n",
       "      <td>LINESTRING (-17.48475000737059 14.745741102669...</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>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",
       "  </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  9165665160  9165665161  10.636000  13.295          path   \n",
       "6508962     6203379  9165665160  9165665159  10.385600  12.982          path   \n",
       "6508963     6203380  9165665161  9165665162   4.380800   5.476          path   \n",
       "6508964     6203381  9165665161  9165665160  10.636000  13.295          path   \n",
       "6508965     6203382  9165665162  9165665161   4.380800   5.476          path   \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.48487070737049 14.745767002669...   \n",
       "6508962  992024621  LINESTRING (-17.48487070737049 14.745767002669...   \n",
       "6508963  992024621  LINESTRING (-17.48475000737059 14.745741102669...   \n",
       "6508964  992024621  LINESTRING (-17.48475000737059 14.745741102669...   \n",
       "6508965  992024621  LINESTRING (-17.484701707370633 14.74572550266...   \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": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 59 entries, 0 to 58\n",
      "Data columns (total 35 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   id          59 non-null     object \n",
      " 1   @id         59 non-null     object \n",
      " 2   highway     59 non-null     object \n",
      " 3   name        5 non-null      object \n",
      " 4   oneway      26 non-null     object \n",
      " 5   smoothness  2 non-null      object \n",
      " 6   surface     48 non-null     object \n",
      " 7   access      0 non-null      float64\n",
      " 8   note        0 non-null      float64\n",
      " 9   source      0 non-null      float64\n",
      " 10  tracktype   17 non-null     object \n",
      " 11  bridge      0 non-null      float64\n",
      " 12  layer       0 non-null      float64\n",
      " 13  embankment  0 non-null      float64\n",
      " 14  ref         3 non-null      object \n",
      " 15  lanes       21 non-null     float64\n",
      " 16  maxspeed    7 non-null      float64\n",
      " 17  lit         0 non-null      float64\n",
      " 18  ford        0 non-null      float64\n",
      " 19  fixme       0 non-null      float64\n",
      " 20  junction    0 non-null      float64\n",
      " 21  intermitte  0 non-null      float64\n",
      " 22  seasonal    0 non-null      float64\n",
      " 23  route       0 non-null      float64\n",
      " 24  type        0 non-null      float64\n",
      " 25  old_ref     0 non-null      float64\n",
      " 26  waterway    0 non-null      float64\n",
      " 27  source_dat  0 non-null      float64\n",
      " 28  @relations  0 non-null      float64\n",
      " 29  foot        0 non-null      float64\n",
      " 30  horse       0 non-null      float64\n",
      " 31  int_ref     0 non-null      float64\n",
      " 32  nat_ref     0 non-null      float64\n",
      " 33  is_in_coun  0 non-null      float64\n",
      " 34  path        58 non-null     object \n",
      "dtypes: float64(25), object(10)\n",
      "memory usage: 16.3+ KB\n",
      "None\n",
      "\n",
      "[370283854, 363308324, 404254217, 574572341, 204942787, 370284428, 204942756, 370292472, 244533082, 262572836, 254170696, 907912593, 363308325, 370283898, 363308369, 253499835, 574572353, 254170693, 152277056, 370225228, 409556978, 370565621, 907912597, 248919216, 370228031, 320413364, 320413357, 370227790, 639434724, 370317360, 907912605, 891279918, 408531179, 370314089, 370314090, 367580491, 370320271, 376580111, 370319685, 907912636, 573703556, 562906754, 376580122, 573703557, 157254861, 573703554, 573703555, 409461642, 573703553, 199529598, 370320332, 370320366, 907912644, 376585119, 907912645, 573703560, 376585145, 370280771, 574572365]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\wb527163\\AppData\\Local\\Temp/ipykernel_22832/2148227697.py:5: FutureWarning: The default value of regex will change from True to False in a future version.\n",
      "  targeted['id'] = targeted['id'].str.replace(r'\\D', '').astype(int)\n"
     ]
    }
   ],
   "source": [
    "targeted = os.path.join(pth, \"targeted_roads_osmid.csv\")\n",
    "targeted = pd.read_csv(targeted)\n",
    "print(targeted.info(), end=\"\\n\\n\")\n",
    "targeted = targeted[['id']]\n",
    "targeted['id'] = targeted['id'].str.replace(r'\\D', '').astype(int)\n",
    "# Do NOT remove Mboro this time. It was not originally a track, but it will be rehabilitated. \n",
    "# targeted = targeted.drop([targeted.index[49]]) # Mboro-Diogo stays the same.\n",
    "target_list = list(targeted.id)\n",
    "print(target_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>9165665160</td>\n",
       "      <td>9165665161</td>\n",
       "      <td>10.636000</td>\n",
       "      <td>13.295</td>\n",
       "      <td>path</td>\n",
       "      <td>992024621</td>\n",
       "      <td>LINESTRING (-17.48487070737049 14.745767002669...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>10.636000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508962</th>\n",
       "      <td>6203379</td>\n",
       "      <td>9165665160</td>\n",
       "      <td>9165665159</td>\n",
       "      <td>10.385600</td>\n",
       "      <td>12.982</td>\n",
       "      <td>path</td>\n",
       "      <td>992024621</td>\n",
       "      <td>LINESTRING (-17.48487070737049 14.745767002669...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>10.385600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508963</th>\n",
       "      <td>6203380</td>\n",
       "      <td>9165665161</td>\n",
       "      <td>9165665162</td>\n",
       "      <td>4.380800</td>\n",
       "      <td>5.476</td>\n",
       "      <td>path</td>\n",
       "      <td>992024621</td>\n",
       "      <td>LINESTRING (-17.48475000737059 14.745741102669...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>4.380800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508964</th>\n",
       "      <td>6203381</td>\n",
       "      <td>9165665161</td>\n",
       "      <td>9165665160</td>\n",
       "      <td>10.636000</td>\n",
       "      <td>13.295</td>\n",
       "      <td>path</td>\n",
       "      <td>992024621</td>\n",
       "      <td>LINESTRING (-17.48475000737059 14.745741102669...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>10.636000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6508965</th>\n",
       "      <td>6203382</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",
       "  </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  9165665160  9165665161  10.636000  13.295          path   \n",
       "6508962     6203379  9165665160  9165665159  10.385600  12.982          path   \n",
       "6508963     6203380  9165665161  9165665162   4.380800   5.476          path   \n",
       "6508964     6203381  9165665161  9165665160  10.636000  13.295          path   \n",
       "6508965     6203382  9165665162  9165665161   4.380800   5.476          path   \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.48487070737049 14.745767002669...   \n",
       "6508962  992024621  LINESTRING (-17.48487070737049 14.745767002669...   \n",
       "6508963  992024621  LINESTRING (-17.48475000737059 14.745741102669...   \n",
       "6508964  992024621  LINESTRING (-17.48475000737059 14.745741102669...   \n",
       "6508965  992024621  LINESTRING (-17.484701707370633 14.74572550266...   \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  10.636000  \n",
       "6508962       -1.0  10.385600  \n",
       "6508963       -1.0   4.380800  \n",
       "6508964       -1.0  10.636000  \n",
       "6508965       -1.0   4.380800  \n",
       "\n",
       "[6508966 rows x 10 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Turn the penalty column into a flood-affected time column.\n",
    "floodjoin['t50'] = floodjoin['t50'] * floodjoin['time']\n",
    "# The project will implement flood protection that prevents isolation. Make sure that's reflected in this simulation.\n",
    "floodjoin.loc[floodjoin['osmid'].isin(target_list), \n",
    "          't50'] = floodjoin['time'] \n",
    "floodjoin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save progress.\n",
    "floodjoin.to_csv(os.path.join(out_pth, 'gTime_edge_post-project_flood50.csv'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Travel time without inclement weather\n",
      "\n",
      "Mean: 7.447111958077438\n",
      "Median: 3.282192\n",
      "\n",
      "\n",
      "\n",
      "During 1 in 50-year flood conditions\n",
      "\n",
      "Mean: 8436.681632176585\n",
      "Median: 3.5952\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": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GISFolder', 'GeoDataFrame', 'In', 'LineString', 'MultiLineString', 'Out', 'Point', 'Polygon', '_', '_1', '_14', '_23', '_25', '_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', '_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', 'target_list', '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, targeted\n",
    "print(dir())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\wb527163\\.conda\\envs\\geo\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3444: DtypeWarning: Columns (2,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 8 columns):\n",
      " #   Column        Dtype  \n",
      "---  ------        -----  \n",
      " 0   Unnamed: 0    int64  \n",
      " 1   node_ID       int64  \n",
      " 2   ref           object \n",
      " 3   highway       object \n",
      " 4   x             float64\n",
      " 5   Unnamed: 0.1  int64  \n",
      " 6   y             float64\n",
      " 7   geometry      object \n",
      "dtypes: float64(2), int64(3), object(3)\n",
      "memory usage: 175.2+ 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_post-project.csv\")\n",
    "nodes = pd.read_csv(nodes)\n",
    "floodjoin = os.path.join(out_pth, \"gTime_edge_post-project_flood50.csv\")\n",
    "floodjoin = pd.read_csv(floodjoin)\n",
    "print(nodes.info())\n",
    "print(floodjoin.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "start: Sun Jan  9 17:34:40 2022\n",
      "\n",
      "(358284990, 5217543379, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000194FF43A3A0>, '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 0x00000194FF43AA00>, '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 0x00000194FF43AB80>, '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 0x00000194FF43A940>, '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 0x00000194FF43A160>, '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 0x00000194FF43AAF0>, '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 0x00000194FF43AD60>, '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 0x00000194FF43AC70>, '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 0x00000194FF43A310>, '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 0x0000019472CC3400>, 'Unnamed: 0': 9, 'Unnamed: 0.1': 9, 'time': 1.232928, 'length': 17.124, 'highway': 'unclassified', 'osmid': 59618174, 'PFU_1in50': -1.0, 't50': 1.232928})\n",
      "\n",
      "end: Sun Jan  9 17:50:22 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": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "start: Sun Jan  9 17:50:22 2022\n",
      "\n",
      "\n",
      "end: Sun Jan  9 17:53:47 2022\n",
      "\n",
      "--- processing complete\n"
     ]
    }
   ],
   "source": [
    "print('start: %s\\n' % time.ctime())\n",
    "gn.save(gTime, 'gTime_post-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": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GISFolder', 'GeoDataFrame', 'In', 'LineString', 'MultiLineString', 'Out', 'Point', 'Polygon', '_', '_1', '_14', '_23', '_25', '_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', '_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', 'target_list', 'team_pth', 'time', 'transform', 'unary_union', 'wkt']\n",
      "['GISFolder', 'GeoDataFrame', 'In', 'LineString', 'MultiLineString', 'Out', 'Point', 'Polygon', '_', '_1', '_14', '_23', '_25', '_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', '_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', 'target_list', '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_post-project_flood50.pickle\")\n",
    "print(dir())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "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": 34,
   "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": 35,
   "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": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "HDurban_snap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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": 37,
   "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": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "dests = list(HDurban_snap.NN.unique()) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "985720"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(origins) # 985720 unique nearest nodes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "58"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dests) # 58 unique nearest nodes. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(358284990, 5217543379, {'geometry': <shapely.geometry.linestring.LineString object at 0x00000194977EDBB0>, '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 0x00000194977ED4F0>, '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 0x00000194977ED3A0>, '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 0x00000194977EDF40>, '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 0x00000194977ED520>, '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 0x00000194977ED460>, '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 0x00000194977ED1C0>, '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 0x00000194977EDB50>, '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 0x00000194977ED310>, '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 0x00000194977EDC40>, 'Unnamed: 0': 9, 'Unnamed: 0.1': 9, '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": 42,
   "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": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "start: Sun Jan  9 17:59:10 2022\n",
      "\n",
      "\n",
      "end: Sun Jan  9 18:36:11 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": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "OD_df = pd.DataFrame(OD, index = origins, columns = dests)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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": 46,
   "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>61632.315005</td>\n",
       "      <td>221724.009077</td>\n",
       "      <td>6294.513822</td>\n",
       "      <td>10817.165367</td>\n",
       "      <td>31985.753411</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>141901.333357</td>\n",
       "      <td>141883.734595</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>301627.683774</td>\n",
       "      <td>461719.377846</td>\n",
       "      <td>301544.344971</td>\n",
       "      <td>293245.207878</td>\n",
       "      <td>263796.470394</td>\n",
       "      <td>299478.115069</td>\n",
       "      <td>301958.551698</td>\n",
       "      <td>298380.699995</td>\n",
       "      <td>401194.356483</td>\n",
       "      <td>290514.817687</td>\n",
       "      <td>...</td>\n",
       "      <td>303471.094708</td>\n",
       "      <td>304709.615577</td>\n",
       "      <td>381896.702126</td>\n",
       "      <td>381879.103364</td>\n",
       "      <td>301797.292792</td>\n",
       "      <td>301550.899333</td>\n",
       "      <td>301519.203501</td>\n",
       "      <td>301513.951111</td>\n",
       "      <td>290723.625097</td>\n",
       "      <td>291135.400807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826871</th>\n",
       "      <td>232191.891657</td>\n",
       "      <td>392283.585729</td>\n",
       "      <td>232108.552854</td>\n",
       "      <td>223809.415761</td>\n",
       "      <td>194360.678277</td>\n",
       "      <td>230042.322952</td>\n",
       "      <td>232522.759581</td>\n",
       "      <td>228944.907878</td>\n",
       "      <td>331758.564366</td>\n",
       "      <td>221079.025571</td>\n",
       "      <td>...</td>\n",
       "      <td>234035.302591</td>\n",
       "      <td>235273.823460</td>\n",
       "      <td>312460.910008</td>\n",
       "      <td>312443.311247</td>\n",
       "      <td>232361.500676</td>\n",
       "      <td>232115.107217</td>\n",
       "      <td>232083.411384</td>\n",
       "      <td>232078.158995</td>\n",
       "      <td>221287.832981</td>\n",
       "      <td>221699.608691</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826876</th>\n",
       "      <td>231906.653046</td>\n",
       "      <td>391998.347118</td>\n",
       "      <td>231823.314243</td>\n",
       "      <td>223524.177150</td>\n",
       "      <td>194075.439666</td>\n",
       "      <td>229757.084341</td>\n",
       "      <td>232237.520970</td>\n",
       "      <td>228659.669267</td>\n",
       "      <td>331473.325755</td>\n",
       "      <td>220793.786959</td>\n",
       "      <td>...</td>\n",
       "      <td>233750.063980</td>\n",
       "      <td>234988.584849</td>\n",
       "      <td>312175.671398</td>\n",
       "      <td>312158.072636</td>\n",
       "      <td>232076.262064</td>\n",
       "      <td>231829.868605</td>\n",
       "      <td>231798.172773</td>\n",
       "      <td>231792.920383</td>\n",
       "      <td>221002.594369</td>\n",
       "      <td>221414.370079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826877</th>\n",
       "      <td>232207.006905</td>\n",
       "      <td>392298.700977</td>\n",
       "      <td>232123.668102</td>\n",
       "      <td>223824.531009</td>\n",
       "      <td>194375.793525</td>\n",
       "      <td>230057.438200</td>\n",
       "      <td>232537.874829</td>\n",
       "      <td>228960.023126</td>\n",
       "      <td>331773.679614</td>\n",
       "      <td>221094.140819</td>\n",
       "      <td>...</td>\n",
       "      <td>234050.417839</td>\n",
       "      <td>235288.938708</td>\n",
       "      <td>312476.025257</td>\n",
       "      <td>312458.426495</td>\n",
       "      <td>232376.615924</td>\n",
       "      <td>232130.222464</td>\n",
       "      <td>232098.526632</td>\n",
       "      <td>232093.274242</td>\n",
       "      <td>221302.948229</td>\n",
       "      <td>221714.723939</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 58 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               6058226279     6029307183     4998093094     2201506815  \\\n",
       "8925478824   61632.315005  221724.009077    6294.513822   10817.165367   \n",
       "3706826868  301627.683774  461719.377846  301544.344971  293245.207878   \n",
       "3706826871  232191.891657  392283.585729  232108.552854  223809.415761   \n",
       "3706826876  231906.653046  391998.347118  231823.314243  223524.177150   \n",
       "3706826877  232207.006905  392298.700977  232123.668102  223824.531009   \n",
       "\n",
       "               4321656809     1697006012     1901689169     6032060028  \\\n",
       "8925478824   31985.753411    4119.039466    6514.333964    2932.849334   \n",
       "3706826868  263796.470394  299478.115069  301958.551698  298380.699995   \n",
       "3706826871  194360.678277  230042.322952  232522.759581  228944.907878   \n",
       "3706826876  194075.439666  229757.084341  232237.520970  228659.669267   \n",
       "3706826877  194375.793525  230057.438200  232537.874829  228960.023126   \n",
       "\n",
       "               6040927878     3449495495  ...     1968458114     9359670710  \\\n",
       "8925478824  118766.313972    5300.161107  ...    9043.084400   10281.605269   \n",
       "3706826868  401194.356483  290514.817687  ...  303471.094708  304709.615577   \n",
       "3706826871  331758.564366  221079.025571  ...  234035.302591  235273.823460   \n",
       "3706826876  331473.325755  220793.786959  ...  233750.063980  234988.584849   \n",
       "3706826877  331773.679614  221094.140819  ...  234050.417839  235288.938708   \n",
       "\n",
       "               6021177135     6027615161     6027276892     6041228287  \\\n",
       "8925478824  141901.333357  141883.734595    6547.461643    6106.681600   \n",
       "3706826868  381896.702126  381879.103364  301797.292792  301550.899333   \n",
       "3706826871  312460.910008  312443.311247  232361.500676  232115.107217   \n",
       "3706826876  312175.671398  312158.072636  232076.262064  231829.868605   \n",
       "3706826877  312476.025257  312458.426495  232376.615924  232130.222464   \n",
       "\n",
       "               5536661253     7357630367     8178147277     6026834850  \n",
       "8925478824    6074.985768    6069.733378    5435.576714    5485.793684  \n",
       "3706826868  301519.203501  301513.951111  290723.625097  291135.400807  \n",
       "3706826871  232083.411384  232078.158995  221287.832981  221699.608691  \n",
       "3706826876  231798.172773  231792.920383  221002.594369  221414.370079  \n",
       "3706826877  232098.526632  232093.274242  221302.948229  221714.723939  \n",
       "\n",
       "[5 rows x 58 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "OD_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>997.018955</td>\n",
       "      <td>3665.213856</td>\n",
       "      <td>288.418746</td>\n",
       "      <td>11.536460</td>\n",
       "      <td>502.909595</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>2334.835928</td>\n",
       "      <td>2334.542615</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>750.029663</td>\n",
       "      <td>611.710712</td>\n",
       "      <td>257.309284</td>\n",
       "      <td>715.592498</td>\n",
       "      <td>756.933109</td>\n",
       "      <td>697.302247</td>\n",
       "      <td>2410.863189</td>\n",
       "      <td>566.204209</td>\n",
       "      <td>...</td>\n",
       "      <td>782.142159</td>\n",
       "      <td>802.784174</td>\n",
       "      <td>1712.359481</td>\n",
       "      <td>1712.066169</td>\n",
       "      <td>754.245460</td>\n",
       "      <td>750.138903</td>\n",
       "      <td>749.610639</td>\n",
       "      <td>749.523099</td>\n",
       "      <td>569.684332</td>\n",
       "      <td>576.547261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3571449966</th>\n",
       "      <td>521.049027</td>\n",
       "      <td>3043.124322</td>\n",
       "      <td>750.416575</td>\n",
       "      <td>612.097623</td>\n",
       "      <td>257.696195</td>\n",
       "      <td>715.979410</td>\n",
       "      <td>757.320020</td>\n",
       "      <td>697.689159</td>\n",
       "      <td>2411.250100</td>\n",
       "      <td>566.591120</td>\n",
       "      <td>...</td>\n",
       "      <td>782.529071</td>\n",
       "      <td>803.171085</td>\n",
       "      <td>1712.746393</td>\n",
       "      <td>1712.453080</td>\n",
       "      <td>754.632372</td>\n",
       "      <td>750.525814</td>\n",
       "      <td>749.997550</td>\n",
       "      <td>749.910011</td>\n",
       "      <td>570.071244</td>\n",
       "      <td>576.934172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3405774993</th>\n",
       "      <td>396.351017</td>\n",
       "      <td>3178.487893</td>\n",
       "      <td>739.660540</td>\n",
       "      <td>601.341588</td>\n",
       "      <td>246.940160</td>\n",
       "      <td>705.223375</td>\n",
       "      <td>746.563985</td>\n",
       "      <td>686.933123</td>\n",
       "      <td>2400.494065</td>\n",
       "      <td>555.835085</td>\n",
       "      <td>...</td>\n",
       "      <td>771.773035</td>\n",
       "      <td>792.415050</td>\n",
       "      <td>1848.109964</td>\n",
       "      <td>1847.816651</td>\n",
       "      <td>743.876337</td>\n",
       "      <td>739.769779</td>\n",
       "      <td>739.241515</td>\n",
       "      <td>739.153975</td>\n",
       "      <td>559.315208</td>\n",
       "      <td>566.178137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3405774994</th>\n",
       "      <td>395.950268</td>\n",
       "      <td>3178.087144</td>\n",
       "      <td>739.259791</td>\n",
       "      <td>600.940839</td>\n",
       "      <td>246.539411</td>\n",
       "      <td>704.822626</td>\n",
       "      <td>746.163236</td>\n",
       "      <td>686.532374</td>\n",
       "      <td>2400.093316</td>\n",
       "      <td>555.434336</td>\n",
       "      <td>...</td>\n",
       "      <td>771.372286</td>\n",
       "      <td>792.014301</td>\n",
       "      <td>1847.709215</td>\n",
       "      <td>1847.415902</td>\n",
       "      <td>743.475588</td>\n",
       "      <td>739.369030</td>\n",
       "      <td>738.840766</td>\n",
       "      <td>738.753226</td>\n",
       "      <td>558.914459</td>\n",
       "      <td>565.777388</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>1027.205250</td>\n",
       "      <td>3695.400151</td>\n",
       "      <td>104.908564</td>\n",
       "      <td>180.286089</td>\n",
       "      <td>533.095890</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>2365.022223</td>\n",
       "      <td>2364.728910</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>5027.128063</td>\n",
       "      <td>7695.322964</td>\n",
       "      <td>5025.739083</td>\n",
       "      <td>4887.420131</td>\n",
       "      <td>4396.607840</td>\n",
       "      <td>4991.301918</td>\n",
       "      <td>5032.642528</td>\n",
       "      <td>4973.011667</td>\n",
       "      <td>6686.572608</td>\n",
       "      <td>4841.913628</td>\n",
       "      <td>...</td>\n",
       "      <td>5057.851578</td>\n",
       "      <td>5078.493593</td>\n",
       "      <td>6364.945035</td>\n",
       "      <td>6364.651723</td>\n",
       "      <td>5029.954880</td>\n",
       "      <td>5025.848322</td>\n",
       "      <td>5025.320058</td>\n",
       "      <td>5025.232519</td>\n",
       "      <td>4845.393752</td>\n",
       "      <td>4852.256680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826871</th>\n",
       "      <td>3869.864861</td>\n",
       "      <td>6538.059762</td>\n",
       "      <td>3868.475881</td>\n",
       "      <td>3730.156929</td>\n",
       "      <td>3239.344638</td>\n",
       "      <td>3834.038716</td>\n",
       "      <td>3875.379326</td>\n",
       "      <td>3815.748465</td>\n",
       "      <td>5529.309406</td>\n",
       "      <td>3684.650426</td>\n",
       "      <td>...</td>\n",
       "      <td>3900.588377</td>\n",
       "      <td>3921.230391</td>\n",
       "      <td>5207.681833</td>\n",
       "      <td>5207.388521</td>\n",
       "      <td>3872.691678</td>\n",
       "      <td>3868.585120</td>\n",
       "      <td>3868.056856</td>\n",
       "      <td>3867.969317</td>\n",
       "      <td>3688.130550</td>\n",
       "      <td>3694.993478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826876</th>\n",
       "      <td>3865.110884</td>\n",
       "      <td>6533.305785</td>\n",
       "      <td>3863.721904</td>\n",
       "      <td>3725.402953</td>\n",
       "      <td>3234.590661</td>\n",
       "      <td>3829.284739</td>\n",
       "      <td>3870.625349</td>\n",
       "      <td>3810.994488</td>\n",
       "      <td>5524.555429</td>\n",
       "      <td>3679.896449</td>\n",
       "      <td>...</td>\n",
       "      <td>3895.834400</td>\n",
       "      <td>3916.476414</td>\n",
       "      <td>5202.927857</td>\n",
       "      <td>5202.634544</td>\n",
       "      <td>3867.937701</td>\n",
       "      <td>3863.831143</td>\n",
       "      <td>3863.302880</td>\n",
       "      <td>3863.215340</td>\n",
       "      <td>3683.376573</td>\n",
       "      <td>3690.239501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3706826877</th>\n",
       "      <td>3870.116782</td>\n",
       "      <td>6538.311683</td>\n",
       "      <td>3868.727802</td>\n",
       "      <td>3730.408850</td>\n",
       "      <td>3239.596559</td>\n",
       "      <td>3834.290637</td>\n",
       "      <td>3875.631247</td>\n",
       "      <td>3816.000385</td>\n",
       "      <td>5529.561327</td>\n",
       "      <td>3684.902347</td>\n",
       "      <td>...</td>\n",
       "      <td>3900.840297</td>\n",
       "      <td>3921.482312</td>\n",
       "      <td>5207.933754</td>\n",
       "      <td>5207.640442</td>\n",
       "      <td>3872.943599</td>\n",
       "      <td>3868.837041</td>\n",
       "      <td>3868.308777</td>\n",
       "      <td>3868.221237</td>\n",
       "      <td>3688.382470</td>\n",
       "      <td>3695.245399</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   997.018955  3665.213856   288.418746    11.536460   502.909595   \n",
       "3571449893   520.662115  3042.737410   750.029663   611.710712   257.309284   \n",
       "3571449966   521.049027  3043.124322   750.416575   612.097623   257.696195   \n",
       "3405774993   396.351017  3178.487893   739.660540   601.341588   246.940160   \n",
       "3405774994   395.950268  3178.087144   739.259791   600.940839   246.539411   \n",
       "...                 ...          ...          ...          ...          ...   \n",
       "8925478824  1027.205250  3695.400151   104.908564   180.286089   533.095890   \n",
       "3706826868  5027.128063  7695.322964  5025.739083  4887.420131  4396.607840   \n",
       "3706826871  3869.864861  6538.059762  3868.475881  3730.156929  3239.344638   \n",
       "3706826876  3865.110884  6533.305785  3863.721904  3725.402953  3234.590661   \n",
       "3706826877  3870.116782  6538.311683  3868.727802  3730.408850  3239.596559   \n",
       "\n",
       "             1697006012   1901689169   6032060028   6040927878   3449495495  \\\n",
       "3331684166   253.981581   295.322191   235.691330  1869.250269   104.593291   \n",
       "3571449893   715.592498   756.933109   697.302247  2410.863189   566.204209   \n",
       "3571449966   715.979410   757.320020   697.689159  2411.250100   566.591120   \n",
       "3405774993   705.223375   746.563985   686.933123  2400.494065   555.835085   \n",
       "3405774994   704.822626   746.163236   686.532374  2400.093316   555.434336   \n",
       "...                 ...          ...          ...          ...          ...   \n",
       "8925478824    68.650658   108.572233    48.880822  1979.438566    88.336018   \n",
       "3706826868  4991.301918  5032.642528  4973.011667  6686.572608  4841.913628   \n",
       "3706826871  3834.038716  3875.379326  3815.748465  5529.309406  3684.650426   \n",
       "3706826876  3829.284739  3870.625349  3810.994488  5524.555429  3679.896449   \n",
       "3706826877  3834.290637  3875.631247  3816.000385  5529.561327  3684.902347   \n",
       "\n",
       "            ...   1968458114   9359670710   6021177135   6027615161  \\\n",
       "3331684166  ...   320.531242   341.173256  2334.835928  2334.542615   \n",
       "3571449893  ...   782.142159   802.784174  1712.359481  1712.066169   \n",
       "3571449966  ...   782.529071   803.171085  1712.746393  1712.453080   \n",
       "3405774993  ...   771.773035   792.415050  1848.109964  1847.816651   \n",
       "3405774994  ...   771.372286   792.014301  1847.709215  1847.415902   \n",
       "...         ...          ...          ...          ...          ...   \n",
       "8925478824  ...   150.718073   171.360088  2365.022223  2364.728910   \n",
       "3706826868  ...  5057.851578  5078.493593  6364.945035  6364.651723   \n",
       "3706826871  ...  3900.588377  3921.230391  5207.681833  5207.388521   \n",
       "3706826876  ...  3895.834400  3916.476414  5202.927857  5202.634544   \n",
       "3706826877  ...  3900.840297  3921.482312  5207.933754  5207.640442   \n",
       "\n",
       "             6027276892   6041228287   5536661253   7357630367   8178147277  \\\n",
       "3331684166   292.634543   288.527985   287.999722   287.912182   108.073415   \n",
       "3571449893   754.245460   750.138903   749.610639   749.523099   569.684332   \n",
       "3571449966   754.632372   750.525814   749.997550   749.910011   570.071244   \n",
       "3405774993   743.876337   739.769779   739.241515   739.153975   559.315208   \n",
       "3405774994   743.475588   739.369030   738.840766   738.753226   558.914459   \n",
       "...                 ...          ...          ...          ...          ...   \n",
       "8925478824   109.124361   101.778027   101.249763   101.162223    90.592945   \n",
       "3706826868  5029.954880  5025.848322  5025.320058  5025.232519  4845.393752   \n",
       "3706826871  3872.691678  3868.585120  3868.056856  3867.969317  3688.130550   \n",
       "3706826876  3867.937701  3863.831143  3863.302880  3863.215340  3683.376573   \n",
       "3706826877  3872.943599  3868.837041  3868.308777  3868.221237  3688.382470   \n",
       "\n",
       "             6026834850  \n",
       "3331684166   114.936343  \n",
       "3571449893   576.547261  \n",
       "3571449966   576.934172  \n",
       "3405774993   566.178137  \n",
       "3405774994   565.777388  \n",
       "...                 ...  \n",
       "8925478824    91.429895  \n",
       "3706826868  4852.256680  \n",
       "3706826871  3694.993478  \n",
       "3706826876  3690.239501  \n",
       "3706826877  3695.245399  \n",
       "\n",
       "[985720 rows x 58 columns]"
      ]
     },
     "execution_count": 47,
     "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_post-project_flood50.csv'))\n",
    "OD_min"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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": 49,
   "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>8675.315684</td>\n",
       "      <td>8536.996732</td>\n",
       "      <td>8182.595304</td>\n",
       "      <td>8640.878519</td>\n",
       "      <td>8682.219129</td>\n",
       "      <td>8622.588267</td>\n",
       "      <td>10336.149209</td>\n",
       "      <td>8491.490229</td>\n",
       "      <td>...</td>\n",
       "      <td>8707.428179</td>\n",
       "      <td>8728.070194</td>\n",
       "      <td>9783.765108</td>\n",
       "      <td>9783.471795</td>\n",
       "      <td>8679.531481</td>\n",
       "      <td>8675.424923</td>\n",
       "      <td>8674.896659</td>\n",
       "      <td>8674.809119</td>\n",
       "      <td>8494.970352</td>\n",
       "      <td>8501.833281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7761872869</th>\n",
       "      <td>7555.023417</td>\n",
       "      <td>11114.124477</td>\n",
       "      <td>8675.297124</td>\n",
       "      <td>8536.978173</td>\n",
       "      <td>8182.576744</td>\n",
       "      <td>8640.859959</td>\n",
       "      <td>8682.200570</td>\n",
       "      <td>8622.569708</td>\n",
       "      <td>10336.130649</td>\n",
       "      <td>8491.471669</td>\n",
       "      <td>...</td>\n",
       "      <td>8707.409620</td>\n",
       "      <td>8728.051634</td>\n",
       "      <td>9783.746548</td>\n",
       "      <td>9783.453236</td>\n",
       "      <td>8679.512921</td>\n",
       "      <td>8675.406364</td>\n",
       "      <td>8674.878100</td>\n",
       "      <td>8674.790560</td>\n",
       "      <td>8494.951793</td>\n",
       "      <td>8501.814721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6442044321</th>\n",
       "      <td>7554.013585</td>\n",
       "      <td>11113.114645</td>\n",
       "      <td>8674.287292</td>\n",
       "      <td>8535.968341</td>\n",
       "      <td>8181.566912</td>\n",
       "      <td>8639.850127</td>\n",
       "      <td>8681.190738</td>\n",
       "      <td>8621.559876</td>\n",
       "      <td>10335.120817</td>\n",
       "      <td>8490.461837</td>\n",
       "      <td>...</td>\n",
       "      <td>8706.399788</td>\n",
       "      <td>8727.041802</td>\n",
       "      <td>9782.736716</td>\n",
       "      <td>9782.443404</td>\n",
       "      <td>8678.503089</td>\n",
       "      <td>8674.396532</td>\n",
       "      <td>8673.868268</td>\n",
       "      <td>8673.780728</td>\n",
       "      <td>8493.941961</td>\n",
       "      <td>8500.804889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2142496418</th>\n",
       "      <td>7555.467107</td>\n",
       "      <td>11114.568167</td>\n",
       "      <td>8675.740814</td>\n",
       "      <td>8537.421863</td>\n",
       "      <td>8183.020434</td>\n",
       "      <td>8641.303649</td>\n",
       "      <td>8682.644260</td>\n",
       "      <td>8623.013398</td>\n",
       "      <td>10336.574339</td>\n",
       "      <td>8491.915359</td>\n",
       "      <td>...</td>\n",
       "      <td>8707.853310</td>\n",
       "      <td>8728.495324</td>\n",
       "      <td>9784.190238</td>\n",
       "      <td>9783.896926</td>\n",
       "      <td>8679.956611</td>\n",
       "      <td>8675.850054</td>\n",
       "      <td>8675.321790</td>\n",
       "      <td>8675.234250</td>\n",
       "      <td>8495.395483</td>\n",
       "      <td>8502.258411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2142496429</th>\n",
       "      <td>7555.849481</td>\n",
       "      <td>11114.950541</td>\n",
       "      <td>8676.123188</td>\n",
       "      <td>8537.804237</td>\n",
       "      <td>8183.402808</td>\n",
       "      <td>8641.686023</td>\n",
       "      <td>8683.026634</td>\n",
       "      <td>8623.395772</td>\n",
       "      <td>10336.956713</td>\n",
       "      <td>8492.297733</td>\n",
       "      <td>...</td>\n",
       "      <td>8708.235684</td>\n",
       "      <td>8728.877698</td>\n",
       "      <td>9784.572612</td>\n",
       "      <td>9784.279300</td>\n",
       "      <td>8680.338985</td>\n",
       "      <td>8676.232428</td>\n",
       "      <td>8675.704164</td>\n",
       "      <td>8675.616624</td>\n",
       "      <td>8495.777857</td>\n",
       "      <td>8502.640785</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>402592.030991</td>\n",
       "      <td>405260.225892</td>\n",
       "      <td>401657.732281</td>\n",
       "      <td>401745.111830</td>\n",
       "      <td>402097.921631</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>403929.847963</td>\n",
       "      <td>403929.554651</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>1900.785584</td>\n",
       "      <td>4568.980485</td>\n",
       "      <td>1151.720971</td>\n",
       "      <td>1053.866424</td>\n",
       "      <td>1406.676224</td>\n",
       "      <td>1114.461377</td>\n",
       "      <td>1154.382952</td>\n",
       "      <td>1095.693230</td>\n",
       "      <td>2853.018900</td>\n",
       "      <td>962.437282</td>\n",
       "      <td>...</td>\n",
       "      <td>1001.608998</td>\n",
       "      <td>1075.735769</td>\n",
       "      <td>3238.602557</td>\n",
       "      <td>3238.309244</td>\n",
       "      <td>1155.936768</td>\n",
       "      <td>1147.588746</td>\n",
       "      <td>1147.060482</td>\n",
       "      <td>1146.972943</td>\n",
       "      <td>971.630082</td>\n",
       "      <td>964.146135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3884280001</th>\n",
       "      <td>1893.923357</td>\n",
       "      <td>4562.118258</td>\n",
       "      <td>1144.858744</td>\n",
       "      <td>1047.004197</td>\n",
       "      <td>1399.813997</td>\n",
       "      <td>1107.599150</td>\n",
       "      <td>1147.520725</td>\n",
       "      <td>1088.831003</td>\n",
       "      <td>2846.156673</td>\n",
       "      <td>955.575055</td>\n",
       "      <td>...</td>\n",
       "      <td>994.746771</td>\n",
       "      <td>1068.873542</td>\n",
       "      <td>3231.740330</td>\n",
       "      <td>3231.447017</td>\n",
       "      <td>1149.074541</td>\n",
       "      <td>1140.726519</td>\n",
       "      <td>1140.198255</td>\n",
       "      <td>1140.110716</td>\n",
       "      <td>964.767855</td>\n",
       "      <td>957.283908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8592243457</th>\n",
       "      <td>1869.716935</td>\n",
       "      <td>4537.911836</td>\n",
       "      <td>1120.652322</td>\n",
       "      <td>1022.797774</td>\n",
       "      <td>1375.607575</td>\n",
       "      <td>1083.392728</td>\n",
       "      <td>1123.314303</td>\n",
       "      <td>1064.624581</td>\n",
       "      <td>2821.950251</td>\n",
       "      <td>931.368633</td>\n",
       "      <td>...</td>\n",
       "      <td>970.540349</td>\n",
       "      <td>1044.667120</td>\n",
       "      <td>3207.533907</td>\n",
       "      <td>3207.240595</td>\n",
       "      <td>1124.868119</td>\n",
       "      <td>1116.520097</td>\n",
       "      <td>1115.991833</td>\n",
       "      <td>1115.904293</td>\n",
       "      <td>940.561432</td>\n",
       "      <td>933.077486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3649149740</th>\n",
       "      <td>101491.166574</td>\n",
       "      <td>104159.361475</td>\n",
       "      <td>100742.101961</td>\n",
       "      <td>100644.247413</td>\n",
       "      <td>100997.057214</td>\n",
       "      <td>100704.842367</td>\n",
       "      <td>100744.763942</td>\n",
       "      <td>100686.074219</td>\n",
       "      <td>102443.399890</td>\n",
       "      <td>100552.818272</td>\n",
       "      <td>...</td>\n",
       "      <td>100591.989988</td>\n",
       "      <td>100666.116759</td>\n",
       "      <td>102828.983546</td>\n",
       "      <td>102828.690234</td>\n",
       "      <td>100746.317758</td>\n",
       "      <td>100737.969736</td>\n",
       "      <td>100737.441472</td>\n",
       "      <td>100737.353932</td>\n",
       "      <td>100562.011071</td>\n",
       "      <td>100554.527125</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    8675.315684    8536.996732   \n",
       "7761872869    7555.023417   11114.124477    8675.297124    8536.978173   \n",
       "6442044321    7554.013585   11113.114645    8674.287292    8535.968341   \n",
       "2142496418    7555.467107   11114.568167    8675.740814    8537.421863   \n",
       "2142496429    7555.849481   11114.950541    8676.123188    8537.804237   \n",
       "...                   ...            ...            ...            ...   \n",
       "9208004175  402592.030991  405260.225892  401657.732281  401745.111830   \n",
       "3884280043    1900.785584    4568.980485    1151.720971    1053.866424   \n",
       "3884280001    1893.923357    4562.118258    1144.858744    1047.004197   \n",
       "8592243457    1869.716935    4537.911836    1120.652322    1022.797774   \n",
       "3649149740  101491.166574  104159.361475  100742.101961  100644.247413   \n",
       "\n",
       "               4321656809     1697006012     1901689169     6032060028  \\\n",
       "7761872870    8182.595304    8640.878519    8682.219129    8622.588267   \n",
       "7761872869    8182.576744    8640.859959    8682.200570    8622.569708   \n",
       "6442044321    8181.566912    8639.850127    8681.190738    8621.559876   \n",
       "2142496418    8183.020434    8641.303649    8682.644260    8623.013398   \n",
       "2142496429    8183.402808    8641.686023    8683.026634    8623.395772   \n",
       "...                   ...            ...            ...            ...   \n",
       "9208004175  402097.921631  401602.316288  401642.237863  401583.581214   \n",
       "3884280043    1406.676224    1114.461377    1154.382952    1095.693230   \n",
       "3884280001    1399.813997    1107.599150    1147.520725    1088.831003   \n",
       "8592243457    1375.607575    1083.392728    1123.314303    1064.624581   \n",
       "3649149740  100997.057214  100704.842367  100744.763942  100686.074219   \n",
       "\n",
       "               6040927878     3449495495  ...     1968458114     9359670710  \\\n",
       "7761872870   10336.149209    8491.490229  ...    8707.428179    8728.070194   \n",
       "7761872869   10336.130649    8491.471669  ...    8707.409620    8728.051634   \n",
       "6442044321   10335.120817    8490.461837  ...    8706.399788    8727.041802   \n",
       "2142496418   10336.574339    8491.915359  ...    8707.853310    8728.495324   \n",
       "2142496429   10336.956713    8492.297733  ...    8708.235684    8728.877698   \n",
       "...                   ...            ...  ...            ...            ...   \n",
       "9208004175  403544.264307  401653.161759  ...  401498.200522  401426.662175   \n",
       "3884280043    2853.018900     962.437282  ...    1001.608998    1075.735769   \n",
       "3884280001    2846.156673     955.575055  ...     994.746771    1068.873542   \n",
       "8592243457    2821.950251     931.368633  ...     970.540349    1044.667120   \n",
       "3649149740  102443.399890  100552.818272  ...  100591.989988  100666.116759   \n",
       "\n",
       "               6021177135     6027615161     6027276892     6041228287  \\\n",
       "7761872870    9783.765108    9783.471795    8679.531481    8675.424923   \n",
       "7761872869    9783.746548    9783.453236    8679.512921    8675.406364   \n",
       "6442044321    9782.736716    9782.443404    8678.503089    8674.396532   \n",
       "2142496418    9784.190238    9783.896926    8679.956611    8675.850054   \n",
       "2142496429    9784.572612    9784.279300    8680.338985    8676.232428   \n",
       "...                   ...            ...            ...            ...   \n",
       "9208004175  403929.847963  403929.554651  401661.948078  401635.443657   \n",
       "3884280043    3238.602557    3238.309244    1155.936768    1147.588746   \n",
       "3884280001    3231.740330    3231.447017    1149.074541    1140.726519   \n",
       "8592243457    3207.533907    3207.240595    1124.868119    1116.520097   \n",
       "3649149740  102828.983546  102828.690234  100746.317758  100737.969736   \n",
       "\n",
       "               5536661253     7357630367     8178147277     6026834850  \n",
       "7761872870    8674.896659    8674.809119    8494.970352    8501.833281  \n",
       "7761872869    8674.878100    8674.790560    8494.951793    8501.814721  \n",
       "6442044321    8673.868268    8673.780728    8493.941961    8500.804889  \n",
       "2142496418    8675.321790    8675.234250    8495.395483    8502.258411  \n",
       "2142496429    8675.704164    8675.616624    8495.777857    8502.640785  \n",
       "...                   ...            ...            ...            ...  \n",
       "9208004175  401634.915393  401634.827853  401655.418686  401656.255636  \n",
       "3884280043    1147.060482    1146.972943     971.630082     964.146135  \n",
       "3884280001    1140.198255    1140.110716     964.767855     957.283908  \n",
       "8592243457    1115.991833    1115.904293     940.561432     933.077486  \n",
       "3649149740  100737.441472  100737.353932  100562.011071  100554.527125  \n",
       "\n",
       "[74662 rows x 58 columns]"
      ]
     },
     "execution_count": 49,
     "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_post-project_flood50.csv'))\n",
    "OD_hamlet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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": 51,
   "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>1205.911576</td>\n",
       "      <td>1067.592624</td>\n",
       "      <td>713.191196</td>\n",
       "      <td>1171.474411</td>\n",
       "      <td>1212.815021</td>\n",
       "      <td>1153.184160</td>\n",
       "      <td>2866.745101</td>\n",
       "      <td>1022.086121</td>\n",
       "      <td>...</td>\n",
       "      <td>1238.024072</td>\n",
       "      <td>1258.666086</td>\n",
       "      <td>2314.361000</td>\n",
       "      <td>2314.067687</td>\n",
       "      <td>1210.127373</td>\n",
       "      <td>1206.020815</td>\n",
       "      <td>1205.492551</td>\n",
       "      <td>1205.405012</td>\n",
       "      <td>1025.566245</td>\n",
       "      <td>1032.429173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3507831510</th>\n",
       "      <td>90.422354</td>\n",
       "      <td>3651.097425</td>\n",
       "      <td>1212.270072</td>\n",
       "      <td>1073.951121</td>\n",
       "      <td>719.549692</td>\n",
       "      <td>1177.832907</td>\n",
       "      <td>1219.173518</td>\n",
       "      <td>1159.542656</td>\n",
       "      <td>2873.103598</td>\n",
       "      <td>1028.444618</td>\n",
       "      <td>...</td>\n",
       "      <td>1244.382568</td>\n",
       "      <td>1265.024582</td>\n",
       "      <td>2320.719497</td>\n",
       "      <td>2320.426184</td>\n",
       "      <td>1216.485869</td>\n",
       "      <td>1212.379312</td>\n",
       "      <td>1211.851048</td>\n",
       "      <td>1211.763508</td>\n",
       "      <td>1031.924741</td>\n",
       "      <td>1038.787670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6188134127</th>\n",
       "      <td>25.237636</td>\n",
       "      <td>3585.912707</td>\n",
       "      <td>1147.085354</td>\n",
       "      <td>1008.766402</td>\n",
       "      <td>654.364974</td>\n",
       "      <td>1112.648189</td>\n",
       "      <td>1153.988799</td>\n",
       "      <td>1094.357938</td>\n",
       "      <td>2807.918879</td>\n",
       "      <td>963.259899</td>\n",
       "      <td>...</td>\n",
       "      <td>1179.197850</td>\n",
       "      <td>1199.839864</td>\n",
       "      <td>2255.534778</td>\n",
       "      <td>2255.241465</td>\n",
       "      <td>1151.301151</td>\n",
       "      <td>1147.194593</td>\n",
       "      <td>1146.666329</td>\n",
       "      <td>1146.578790</td>\n",
       "      <td>966.740023</td>\n",
       "      <td>973.602951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8631201421</th>\n",
       "      <td>1070.956229</td>\n",
       "      <td>2782.475931</td>\n",
       "      <td>1300.323777</td>\n",
       "      <td>1162.004825</td>\n",
       "      <td>807.603397</td>\n",
       "      <td>1265.886612</td>\n",
       "      <td>1307.227222</td>\n",
       "      <td>1247.596361</td>\n",
       "      <td>2961.157302</td>\n",
       "      <td>1116.498322</td>\n",
       "      <td>...</td>\n",
       "      <td>1332.436272</td>\n",
       "      <td>1353.078287</td>\n",
       "      <td>2079.632570</td>\n",
       "      <td>2079.339257</td>\n",
       "      <td>1304.539574</td>\n",
       "      <td>1300.433016</td>\n",
       "      <td>1299.904752</td>\n",
       "      <td>1299.817212</td>\n",
       "      <td>1119.978446</td>\n",
       "      <td>1126.841374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8598305977</th>\n",
       "      <td>1085.282837</td>\n",
       "      <td>2796.802539</td>\n",
       "      <td>1314.650385</td>\n",
       "      <td>1176.331434</td>\n",
       "      <td>821.930005</td>\n",
       "      <td>1280.213220</td>\n",
       "      <td>1321.553831</td>\n",
       "      <td>1261.922969</td>\n",
       "      <td>2975.483911</td>\n",
       "      <td>1130.824931</td>\n",
       "      <td>...</td>\n",
       "      <td>1346.762881</td>\n",
       "      <td>1367.404895</td>\n",
       "      <td>2093.959178</td>\n",
       "      <td>2093.665866</td>\n",
       "      <td>1318.866182</td>\n",
       "      <td>1314.759625</td>\n",
       "      <td>1314.231361</td>\n",
       "      <td>1314.143821</td>\n",
       "      <td>1134.305054</td>\n",
       "      <td>1141.167983</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>292483.740922</td>\n",
       "      <td>295151.935823</td>\n",
       "      <td>291549.442212</td>\n",
       "      <td>291636.821761</td>\n",
       "      <td>291989.631562</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>293821.557894</td>\n",
       "      <td>293821.264582</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>292475.590667</td>\n",
       "      <td>295143.785568</td>\n",
       "      <td>291541.291958</td>\n",
       "      <td>291628.671506</td>\n",
       "      <td>291981.481307</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>293813.407640</td>\n",
       "      <td>293813.114327</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>292481.085783</td>\n",
       "      <td>295149.280684</td>\n",
       "      <td>291546.787073</td>\n",
       "      <td>291634.166622</td>\n",
       "      <td>291986.976423</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>293818.902755</td>\n",
       "      <td>293818.609443</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>292467.555480</td>\n",
       "      <td>295135.750381</td>\n",
       "      <td>291533.256771</td>\n",
       "      <td>291620.636320</td>\n",
       "      <td>291973.446120</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>293805.372453</td>\n",
       "      <td>293805.079140</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>293153.101035</td>\n",
       "      <td>295821.295937</td>\n",
       "      <td>292218.802326</td>\n",
       "      <td>292306.181875</td>\n",
       "      <td>292658.991676</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>294490.918008</td>\n",
       "      <td>294490.624695</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    1205.911576    1067.592624   \n",
       "3507831510      90.422354    3651.097425    1212.270072    1073.951121   \n",
       "6188134127      25.237636    3585.912707    1147.085354    1008.766402   \n",
       "8631201421    1070.956229    2782.475931    1300.323777    1162.004825   \n",
       "8598305977    1085.282837    2796.802539    1314.650385    1176.331434   \n",
       "...                   ...            ...            ...            ...   \n",
       "3651042474  292483.740922  295151.935823  291549.442212  291636.821761   \n",
       "3651042508  292475.590667  295143.785568  291541.291958  291628.671506   \n",
       "3651042501  292481.085783  295149.280684  291546.787073  291634.166622   \n",
       "3651042393  292467.555480  295135.750381  291533.256771  291620.636320   \n",
       "6323434997  293153.101035  295821.295937  292218.802326  292306.181875   \n",
       "\n",
       "               4321656809     1697006012     1901689169     6032060028  \\\n",
       "3507831609     713.191196    1171.474411    1212.815021    1153.184160   \n",
       "3507831510     719.549692    1177.832907    1219.173518    1159.542656   \n",
       "6188134127     654.364974    1112.648189    1153.988799    1094.357938   \n",
       "8631201421     807.603397    1265.886612    1307.227222    1247.596361   \n",
       "8598305977     821.930005    1280.213220    1321.553831    1261.922969   \n",
       "...                   ...            ...            ...            ...   \n",
       "3651042474  291989.631562  291494.026219  291533.947794  291475.291145   \n",
       "3651042508  291981.481307  291485.875964  291525.797539  291467.140890   \n",
       "3651042501  291986.976423  291491.371079  291531.292654  291472.636005   \n",
       "3651042393  291973.446120  291477.840777  291517.762352  291459.105703   \n",
       "6323434997  292658.991676  292163.386332  292203.307907  292144.651258   \n",
       "\n",
       "               6040927878     3449495495  ...     1968458114     9359670710  \\\n",
       "3507831609    2866.745101    1022.086121  ...    1238.024072    1258.666086   \n",
       "3507831510    2873.103598    1028.444618  ...    1244.382568    1265.024582   \n",
       "6188134127    2807.918879     963.259899  ...    1179.197850    1199.839864   \n",
       "8631201421    2961.157302    1116.498322  ...    1332.436272    1353.078287   \n",
       "8598305977    2975.483911    1130.824931  ...    1346.762881    1367.404895   \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    1210.127373    1206.020815   \n",
       "3507831510    2320.719497    2320.426184    1216.485869    1212.379312   \n",
       "6188134127    2255.534778    2255.241465    1151.301151    1147.194593   \n",
       "8631201421    2079.632570    2079.339257    1304.539574    1300.433016   \n",
       "8598305977    2093.959178    2093.665866    1318.866182    1314.759625   \n",
       "...                   ...            ...            ...            ...   \n",
       "3651042474  293821.557894  293821.264582  291553.658009  291527.153588   \n",
       "3651042508  293813.407640  293813.114327  291545.507755  291519.003333   \n",
       "3651042501  293818.902755  293818.609443  291551.002870  291524.498448   \n",
       "3651042393  293805.372453  293805.079140  291537.472568  291510.968146   \n",
       "6323434997  294490.918008  294490.624695  292223.018123  292196.513701   \n",
       "\n",
       "               5536661253     7357630367     8178147277     6026834850  \n",
       "3507831609    1205.492551    1205.405012    1025.566245    1032.429173  \n",
       "3507831510    1211.851048    1211.763508    1031.924741    1038.787670  \n",
       "6188134127    1146.666329    1146.578790     966.740023     973.602951  \n",
       "8631201421    1299.904752    1299.817212    1119.978446    1126.841374  \n",
       "8598305977    1314.231361    1314.143821    1134.305054    1141.167983  \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": 51,
     "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_post-project_flood50.csv'))\n",
    "OD_ag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GISFolder', 'GeoDataFrame', 'In', 'LineString', 'MultiLineString', 'OD_ag', 'OD_hamlet', 'Out', 'Point', 'Polygon', '_', '_1', '_14', '_23', '_25', '_35', '_39', '_40', '_46', '_47', '_49', '_51', '_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', '_i53', '_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', 'target_list', '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": 54,
   "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_post-project_flood50.csv\")\n",
    "OD_hamlet = pd.read_csv(OD_hamlet)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "OD_ag = os.path.join(out_pth, \"OD_ag_post-project_flood50.csv\")\n",
    "OD_ag = pd.read_csv(OD_ag)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "OD_ag.rename(columns={'Unnamed: 0': 'NN'}, inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "OD_hamlet.rename(columns={'Unnamed: 0': 'NN'}, inplace=True) "
   ]
  },
  {
   "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>3507831609</td>\n",
       "      <td>84.063858</td>\n",
       "      <td>3644.738929</td>\n",
       "      <td>1205.911576</td>\n",
       "      <td>1067.592624</td>\n",
       "      <td>713.191196</td>\n",
       "      <td>1171.474411</td>\n",
       "      <td>1212.815021</td>\n",
       "      <td>1153.184160</td>\n",
       "      <td>2866.745101</td>\n",
       "      <td>...</td>\n",
       "      <td>1238.024072</td>\n",
       "      <td>1258.666086</td>\n",
       "      <td>2314.361000</td>\n",
       "      <td>2314.067687</td>\n",
       "      <td>1210.127373</td>\n",
       "      <td>1206.020815</td>\n",
       "      <td>1205.492551</td>\n",
       "      <td>1205.405012</td>\n",
       "      <td>1025.566245</td>\n",
       "      <td>1032.429173</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>1212.270072</td>\n",
       "      <td>1073.951121</td>\n",
       "      <td>719.549692</td>\n",
       "      <td>1177.832907</td>\n",
       "      <td>1219.173518</td>\n",
       "      <td>1159.542656</td>\n",
       "      <td>2873.103598</td>\n",
       "      <td>...</td>\n",
       "      <td>1244.382568</td>\n",
       "      <td>1265.024582</td>\n",
       "      <td>2320.719497</td>\n",
       "      <td>2320.426184</td>\n",
       "      <td>1216.485869</td>\n",
       "      <td>1212.379312</td>\n",
       "      <td>1211.851048</td>\n",
       "      <td>1211.763508</td>\n",
       "      <td>1031.924741</td>\n",
       "      <td>1038.787670</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>1147.085354</td>\n",
       "      <td>1008.766402</td>\n",
       "      <td>654.364974</td>\n",
       "      <td>1112.648189</td>\n",
       "      <td>1153.988799</td>\n",
       "      <td>1094.357938</td>\n",
       "      <td>2807.918879</td>\n",
       "      <td>...</td>\n",
       "      <td>1179.197850</td>\n",
       "      <td>1199.839864</td>\n",
       "      <td>2255.534778</td>\n",
       "      <td>2255.241465</td>\n",
       "      <td>1151.301151</td>\n",
       "      <td>1147.194593</td>\n",
       "      <td>1146.666329</td>\n",
       "      <td>1146.578790</td>\n",
       "      <td>966.740023</td>\n",
       "      <td>973.602951</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>1300.323777</td>\n",
       "      <td>1162.004825</td>\n",
       "      <td>807.603397</td>\n",
       "      <td>1265.886612</td>\n",
       "      <td>1307.227222</td>\n",
       "      <td>1247.596361</td>\n",
       "      <td>2961.157302</td>\n",
       "      <td>...</td>\n",
       "      <td>1332.436272</td>\n",
       "      <td>1353.078287</td>\n",
       "      <td>2079.632570</td>\n",
       "      <td>2079.339257</td>\n",
       "      <td>1304.539574</td>\n",
       "      <td>1300.433016</td>\n",
       "      <td>1299.904752</td>\n",
       "      <td>1299.817212</td>\n",
       "      <td>1119.978446</td>\n",
       "      <td>1126.841374</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>1314.650385</td>\n",
       "      <td>1176.331434</td>\n",
       "      <td>821.930005</td>\n",
       "      <td>1280.213220</td>\n",
       "      <td>1321.553831</td>\n",
       "      <td>1261.922969</td>\n",
       "      <td>2975.483911</td>\n",
       "      <td>...</td>\n",
       "      <td>1346.762881</td>\n",
       "      <td>1367.404895</td>\n",
       "      <td>2093.959178</td>\n",
       "      <td>2093.665866</td>\n",
       "      <td>1318.866182</td>\n",
       "      <td>1314.759625</td>\n",
       "      <td>1314.231361</td>\n",
       "      <td>1314.143821</td>\n",
       "      <td>1134.305054</td>\n",
       "      <td>1141.167983</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>292483.740922</td>\n",
       "      <td>295151.935823</td>\n",
       "      <td>291549.442212</td>\n",
       "      <td>291636.821761</td>\n",
       "      <td>291989.631562</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>293821.557894</td>\n",
       "      <td>293821.264582</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>292475.590667</td>\n",
       "      <td>295143.785568</td>\n",
       "      <td>291541.291958</td>\n",
       "      <td>291628.671506</td>\n",
       "      <td>291981.481307</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>293813.407640</td>\n",
       "      <td>293813.114327</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>292481.085783</td>\n",
       "      <td>295149.280684</td>\n",
       "      <td>291546.787073</td>\n",
       "      <td>291634.166622</td>\n",
       "      <td>291986.976423</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>293818.902755</td>\n",
       "      <td>293818.609443</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>292467.555480</td>\n",
       "      <td>295135.750381</td>\n",
       "      <td>291533.256771</td>\n",
       "      <td>291620.636320</td>\n",
       "      <td>291973.446120</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>293805.372453</td>\n",
       "      <td>293805.079140</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>293153.101035</td>\n",
       "      <td>295821.295937</td>\n",
       "      <td>292218.802326</td>\n",
       "      <td>292306.181875</td>\n",
       "      <td>292658.991676</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>294490.918008</td>\n",
       "      <td>294490.624695</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    1205.911576   \n",
       "1       3507831510      90.422354    3651.097425    1212.270072   \n",
       "2       6188134127      25.237636    3585.912707    1147.085354   \n",
       "3       8631201421    1070.956229    2782.475931    1300.323777   \n",
       "4       8598305977    1085.282837    2796.802539    1314.650385   \n",
       "...            ...            ...            ...            ...   \n",
       "967547  3651042474  292483.740922  295151.935823  291549.442212   \n",
       "967548  3651042508  292475.590667  295143.785568  291541.291958   \n",
       "967549  3651042501  292481.085783  295149.280684  291546.787073   \n",
       "967550  3651042393  292467.555480  295135.750381  291533.256771   \n",
       "967551  6323434997  293153.101035  295821.295937  292218.802326   \n",
       "\n",
       "           2201506815     4321656809     1697006012     1901689169  \\\n",
       "0         1067.592624     713.191196    1171.474411    1212.815021   \n",
       "1         1073.951121     719.549692    1177.832907    1219.173518   \n",
       "2         1008.766402     654.364974    1112.648189    1153.988799   \n",
       "3         1162.004825     807.603397    1265.886612    1307.227222   \n",
       "4         1176.331434     821.930005    1280.213220    1321.553831   \n",
       "...               ...            ...            ...            ...   \n",
       "967547  291636.821761  291989.631562  291494.026219  291533.947794   \n",
       "967548  291628.671506  291981.481307  291485.875964  291525.797539   \n",
       "967549  291634.166622  291986.976423  291491.371079  291531.292654   \n",
       "967550  291620.636320  291973.446120  291477.840777  291517.762352   \n",
       "967551  292306.181875  292658.991676  292163.386332  292203.307907   \n",
       "\n",
       "           6032060028     6040927878  ...     1968458114     9359670710  \\\n",
       "0         1153.184160    2866.745101  ...    1238.024072    1258.666086   \n",
       "1         1159.542656    2873.103598  ...    1244.382568    1265.024582   \n",
       "2         1094.357938    2807.918879  ...    1179.197850    1199.839864   \n",
       "3         1247.596361    2961.157302  ...    1332.436272    1353.078287   \n",
       "4         1261.922969    2975.483911  ...    1346.762881    1367.404895   \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    1210.127373    1206.020815   \n",
       "1         2320.719497    2320.426184    1216.485869    1212.379312   \n",
       "2         2255.534778    2255.241465    1151.301151    1147.194593   \n",
       "3         2079.632570    2079.339257    1304.539574    1300.433016   \n",
       "4         2093.959178    2093.665866    1318.866182    1314.759625   \n",
       "...               ...            ...            ...            ...   \n",
       "967547  293821.557894  293821.264582  291553.658009  291527.153588   \n",
       "967548  293813.407640  293813.114327  291545.507755  291519.003333   \n",
       "967549  293818.902755  293818.609443  291551.002870  291524.498448   \n",
       "967550  293805.372453  293805.079140  291537.472568  291510.968146   \n",
       "967551  294490.918008  294490.624695  292223.018123  292196.513701   \n",
       "\n",
       "           5536661253     7357630367     8178147277     6026834850  \n",
       "0         1205.492551    1205.405012    1025.566245    1032.429173  \n",
       "1         1211.851048    1211.763508    1031.924741    1038.787670  \n",
       "2         1146.666329    1146.578790     966.740023     973.602951  \n",
       "3         1299.904752    1299.817212    1119.978446    1126.841374  \n",
       "4         1314.231361    1314.143821    1134.305054    1141.167983  \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": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "OD_ag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>8675.315684</td>\n",
       "      <td>8536.996732</td>\n",
       "      <td>8182.595304</td>\n",
       "      <td>8640.878519</td>\n",
       "      <td>8682.219129</td>\n",
       "      <td>8622.588267</td>\n",
       "      <td>10336.149209</td>\n",
       "      <td>...</td>\n",
       "      <td>8707.428179</td>\n",
       "      <td>8728.070194</td>\n",
       "      <td>9783.765108</td>\n",
       "      <td>9783.471795</td>\n",
       "      <td>8679.531481</td>\n",
       "      <td>8675.424923</td>\n",
       "      <td>8674.896659</td>\n",
       "      <td>8674.809119</td>\n",
       "      <td>8494.970352</td>\n",
       "      <td>8501.833281</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>8675.297124</td>\n",
       "      <td>8536.978173</td>\n",
       "      <td>8182.576744</td>\n",
       "      <td>8640.859959</td>\n",
       "      <td>8682.200570</td>\n",
       "      <td>8622.569708</td>\n",
       "      <td>10336.130649</td>\n",
       "      <td>...</td>\n",
       "      <td>8707.409620</td>\n",
       "      <td>8728.051634</td>\n",
       "      <td>9783.746548</td>\n",
       "      <td>9783.453236</td>\n",
       "      <td>8679.512921</td>\n",
       "      <td>8675.406364</td>\n",
       "      <td>8674.878100</td>\n",
       "      <td>8674.790560</td>\n",
       "      <td>8494.951793</td>\n",
       "      <td>8501.814721</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>8674.287292</td>\n",
       "      <td>8535.968341</td>\n",
       "      <td>8181.566912</td>\n",
       "      <td>8639.850127</td>\n",
       "      <td>8681.190738</td>\n",
       "      <td>8621.559876</td>\n",
       "      <td>10335.120817</td>\n",
       "      <td>...</td>\n",
       "      <td>8706.399788</td>\n",
       "      <td>8727.041802</td>\n",
       "      <td>9782.736716</td>\n",
       "      <td>9782.443404</td>\n",
       "      <td>8678.503089</td>\n",
       "      <td>8674.396532</td>\n",
       "      <td>8673.868268</td>\n",
       "      <td>8673.780728</td>\n",
       "      <td>8493.941961</td>\n",
       "      <td>8500.804889</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>8675.740814</td>\n",
       "      <td>8537.421863</td>\n",
       "      <td>8183.020434</td>\n",
       "      <td>8641.303649</td>\n",
       "      <td>8682.644260</td>\n",
       "      <td>8623.013398</td>\n",
       "      <td>10336.574339</td>\n",
       "      <td>...</td>\n",
       "      <td>8707.853310</td>\n",
       "      <td>8728.495324</td>\n",
       "      <td>9784.190238</td>\n",
       "      <td>9783.896926</td>\n",
       "      <td>8679.956611</td>\n",
       "      <td>8675.850054</td>\n",
       "      <td>8675.321790</td>\n",
       "      <td>8675.234250</td>\n",
       "      <td>8495.395483</td>\n",
       "      <td>8502.258411</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>8676.123188</td>\n",
       "      <td>8537.804237</td>\n",
       "      <td>8183.402808</td>\n",
       "      <td>8641.686023</td>\n",
       "      <td>8683.026634</td>\n",
       "      <td>8623.395772</td>\n",
       "      <td>10336.956713</td>\n",
       "      <td>...</td>\n",
       "      <td>8708.235684</td>\n",
       "      <td>8728.877698</td>\n",
       "      <td>9784.572612</td>\n",
       "      <td>9784.279300</td>\n",
       "      <td>8680.338985</td>\n",
       "      <td>8676.232428</td>\n",
       "      <td>8675.704164</td>\n",
       "      <td>8675.616624</td>\n",
       "      <td>8495.777857</td>\n",
       "      <td>8502.640785</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>402592.030991</td>\n",
       "      <td>405260.225892</td>\n",
       "      <td>401657.732281</td>\n",
       "      <td>401745.111830</td>\n",
       "      <td>402097.921631</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>403929.847963</td>\n",
       "      <td>403929.554651</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>1900.785584</td>\n",
       "      <td>4568.980485</td>\n",
       "      <td>1151.720971</td>\n",
       "      <td>1053.866424</td>\n",
       "      <td>1406.676224</td>\n",
       "      <td>1114.461377</td>\n",
       "      <td>1154.382952</td>\n",
       "      <td>1095.693230</td>\n",
       "      <td>2853.018900</td>\n",
       "      <td>...</td>\n",
       "      <td>1001.608998</td>\n",
       "      <td>1075.735769</td>\n",
       "      <td>3238.602557</td>\n",
       "      <td>3238.309244</td>\n",
       "      <td>1155.936768</td>\n",
       "      <td>1147.588746</td>\n",
       "      <td>1147.060482</td>\n",
       "      <td>1146.972943</td>\n",
       "      <td>971.630082</td>\n",
       "      <td>964.146135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74659</th>\n",
       "      <td>3884280001</td>\n",
       "      <td>1893.923357</td>\n",
       "      <td>4562.118258</td>\n",
       "      <td>1144.858744</td>\n",
       "      <td>1047.004197</td>\n",
       "      <td>1399.813997</td>\n",
       "      <td>1107.599150</td>\n",
       "      <td>1147.520725</td>\n",
       "      <td>1088.831003</td>\n",
       "      <td>2846.156673</td>\n",
       "      <td>...</td>\n",
       "      <td>994.746771</td>\n",
       "      <td>1068.873542</td>\n",
       "      <td>3231.740330</td>\n",
       "      <td>3231.447017</td>\n",
       "      <td>1149.074541</td>\n",
       "      <td>1140.726519</td>\n",
       "      <td>1140.198255</td>\n",
       "      <td>1140.110716</td>\n",
       "      <td>964.767855</td>\n",
       "      <td>957.283908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74660</th>\n",
       "      <td>8592243457</td>\n",
       "      <td>1869.716935</td>\n",
       "      <td>4537.911836</td>\n",
       "      <td>1120.652322</td>\n",
       "      <td>1022.797774</td>\n",
       "      <td>1375.607575</td>\n",
       "      <td>1083.392728</td>\n",
       "      <td>1123.314303</td>\n",
       "      <td>1064.624581</td>\n",
       "      <td>2821.950251</td>\n",
       "      <td>...</td>\n",
       "      <td>970.540349</td>\n",
       "      <td>1044.667120</td>\n",
       "      <td>3207.533907</td>\n",
       "      <td>3207.240595</td>\n",
       "      <td>1124.868119</td>\n",
       "      <td>1116.520097</td>\n",
       "      <td>1115.991833</td>\n",
       "      <td>1115.904293</td>\n",
       "      <td>940.561432</td>\n",
       "      <td>933.077486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74661</th>\n",
       "      <td>3649149740</td>\n",
       "      <td>101491.166574</td>\n",
       "      <td>104159.361475</td>\n",
       "      <td>100742.101961</td>\n",
       "      <td>100644.247413</td>\n",
       "      <td>100997.057214</td>\n",
       "      <td>100704.842367</td>\n",
       "      <td>100744.763942</td>\n",
       "      <td>100686.074219</td>\n",
       "      <td>102443.399890</td>\n",
       "      <td>...</td>\n",
       "      <td>100591.989988</td>\n",
       "      <td>100666.116759</td>\n",
       "      <td>102828.983546</td>\n",
       "      <td>102828.690234</td>\n",
       "      <td>100746.317758</td>\n",
       "      <td>100737.969736</td>\n",
       "      <td>100737.441472</td>\n",
       "      <td>100737.353932</td>\n",
       "      <td>100562.011071</td>\n",
       "      <td>100554.527125</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    8675.315684    8536.996732   \n",
       "1      7761872869    7555.023417   11114.124477    8675.297124    8536.978173   \n",
       "2      6442044321    7554.013585   11113.114645    8674.287292    8535.968341   \n",
       "3      2142496418    7555.467107   11114.568167    8675.740814    8537.421863   \n",
       "4      2142496429    7555.849481   11114.950541    8676.123188    8537.804237   \n",
       "...           ...            ...            ...            ...            ...   \n",
       "74657  9208004175  402592.030991  405260.225892  401657.732281  401745.111830   \n",
       "74658  3884280043    1900.785584    4568.980485    1151.720971    1053.866424   \n",
       "74659  3884280001    1893.923357    4562.118258    1144.858744    1047.004197   \n",
       "74660  8592243457    1869.716935    4537.911836    1120.652322    1022.797774   \n",
       "74661  3649149740  101491.166574  104159.361475  100742.101961  100644.247413   \n",
       "\n",
       "          4321656809     1697006012     1901689169     6032060028  \\\n",
       "0        8182.595304    8640.878519    8682.219129    8622.588267   \n",
       "1        8182.576744    8640.859959    8682.200570    8622.569708   \n",
       "2        8181.566912    8639.850127    8681.190738    8621.559876   \n",
       "3        8183.020434    8641.303649    8682.644260    8623.013398   \n",
       "4        8183.402808    8641.686023    8683.026634    8623.395772   \n",
       "...              ...            ...            ...            ...   \n",
       "74657  402097.921631  401602.316288  401642.237863  401583.581214   \n",
       "74658    1406.676224    1114.461377    1154.382952    1095.693230   \n",
       "74659    1399.813997    1107.599150    1147.520725    1088.831003   \n",
       "74660    1375.607575    1083.392728    1123.314303    1064.624581   \n",
       "74661  100997.057214  100704.842367  100744.763942  100686.074219   \n",
       "\n",
       "          6040927878  ...     1968458114     9359670710     6021177135  \\\n",
       "0       10336.149209  ...    8707.428179    8728.070194    9783.765108   \n",
       "1       10336.130649  ...    8707.409620    8728.051634    9783.746548   \n",
       "2       10335.120817  ...    8706.399788    8727.041802    9782.736716   \n",
       "3       10336.574339  ...    8707.853310    8728.495324    9784.190238   \n",
       "4       10336.956713  ...    8708.235684    8728.877698    9784.572612   \n",
       "...              ...  ...            ...            ...            ...   \n",
       "74657  403544.264307  ...  401498.200522  401426.662175  403929.847963   \n",
       "74658    2853.018900  ...    1001.608998    1075.735769    3238.602557   \n",
       "74659    2846.156673  ...     994.746771    1068.873542    3231.740330   \n",
       "74660    2821.950251  ...     970.540349    1044.667120    3207.533907   \n",
       "74661  102443.399890  ...  100591.989988  100666.116759  102828.983546   \n",
       "\n",
       "          6027615161     6027276892     6041228287     5536661253  \\\n",
       "0        9783.471795    8679.531481    8675.424923    8674.896659   \n",
       "1        9783.453236    8679.512921    8675.406364    8674.878100   \n",
       "2        9782.443404    8678.503089    8674.396532    8673.868268   \n",
       "3        9783.896926    8679.956611    8675.850054    8675.321790   \n",
       "4        9784.279300    8680.338985    8676.232428    8675.704164   \n",
       "...              ...            ...            ...            ...   \n",
       "74657  403929.554651  401661.948078  401635.443657  401634.915393   \n",
       "74658    3238.309244    1155.936768    1147.588746    1147.060482   \n",
       "74659    3231.447017    1149.074541    1140.726519    1140.198255   \n",
       "74660    3207.240595    1124.868119    1116.520097    1115.991833   \n",
       "74661  102828.690234  100746.317758  100737.969736  100737.441472   \n",
       "\n",
       "          7357630367     8178147277     6026834850  \n",
       "0        8674.809119    8494.970352    8501.833281  \n",
       "1        8674.790560    8494.951793    8501.814721  \n",
       "2        8673.780728    8493.941961    8500.804889  \n",
       "3        8675.234250    8495.395483    8502.258411  \n",
       "4        8675.616624    8495.777857    8502.640785  \n",
       "...              ...            ...            ...  \n",
       "74657  401634.827853  401655.418686  401656.255636  \n",
       "74658    1146.972943     971.630082     964.146135  \n",
       "74659    1140.110716     964.767855     957.283908  \n",
       "74660    1115.904293     940.561432     933.077486  \n",
       "74661  100737.353932  100562.011071  100554.527125  \n",
       "\n",
       "[74662 rows x 59 columns]"
      ]
     },
     "execution_count": 59,
     "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": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "fail_value = 999999999"
   ]
  },
  {
   "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>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": 61,
     "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_post-project_flood50.csv'))\n",
    "ag_all.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "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>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": 62,
     "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_post-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
}