OD-matrix stops-Supermarkets1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PyTables is not installed. No support for HDF output.\n",
      "SQLalchemy is not installed. No support for SQL output.\n"
     ]
    }
   ],
   "source": [
    "import geopandas as gpd\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from simpledbf import Dbf5\n",
    "from sklearn.cluster import KMeans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbf = Dbf5(r\"C:\\Users\\bruno\\Bureau RMC Dropbox\\2. RMC Intern\\1. Bureau\\3. Stagiairs\\Bruno\\BHermans\\qgis\\od-matrix-stops.dbf\")\n",
    "df = dbf.to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>origin_id</th>\n",
       "      <th>destinatio</th>\n",
       "      <th>entry_cost</th>\n",
       "      <th>network_co</th>\n",
       "      <th>exit_cost</th>\n",
       "      <th>total_cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1013285</td>\n",
       "      <td>1013304</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>2202.493737</td>\n",
       "      <td>2.300000e-06</td>\n",
       "      <td>2202.493741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1013285</td>\n",
       "      <td>1013306</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>3470.083187</td>\n",
       "      <td>2.200000e-06</td>\n",
       "      <td>3470.083191</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1013285</td>\n",
       "      <td>103939</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>10772.932400</td>\n",
       "      <td>7.000000e-07</td>\n",
       "      <td>10772.932402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1013285</td>\n",
       "      <td>103942</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>8183.500344</td>\n",
       "      <td>5.000000e-06</td>\n",
       "      <td>8183.500350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1013285</td>\n",
       "      <td>103943</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>10423.020565</td>\n",
       "      <td>3.200000e-06</td>\n",
       "      <td>10423.020570</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813599</th>\n",
       "      <td>980476</td>\n",
       "      <td>96491</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>11483.741035</td>\n",
       "      <td>6.000000e-07</td>\n",
       "      <td>11483.741038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813600</th>\n",
       "      <td>980476</td>\n",
       "      <td>979780</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>11892.055173</td>\n",
       "      <td>7.000000e-07</td>\n",
       "      <td>11892.055175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813601</th>\n",
       "      <td>980476</td>\n",
       "      <td>980432</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>6947.787457</td>\n",
       "      <td>3.600000e-06</td>\n",
       "      <td>6947.787462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813602</th>\n",
       "      <td>980476</td>\n",
       "      <td>980448</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>7241.957070</td>\n",
       "      <td>1.000000e-06</td>\n",
       "      <td>7241.957072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813603</th>\n",
       "      <td>980476</td>\n",
       "      <td>980476</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>7241.957070</td>\n",
       "      <td>1.000000e-06</td>\n",
       "      <td>7241.957072</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>813603 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        origin_id  destinatio  entry_cost    network_co     exit_cost  \\\n",
       "1         1013285     1013304    0.000001   2202.493737  2.300000e-06   \n",
       "2         1013285     1013306    0.000001   3470.083187  2.200000e-06   \n",
       "3         1013285      103939    0.000001  10772.932400  7.000000e-07   \n",
       "4         1013285      103942    0.000001   8183.500344  5.000000e-06   \n",
       "5         1013285      103943    0.000001  10423.020565  3.200000e-06   \n",
       "...           ...         ...         ...           ...           ...   \n",
       "813599     980476       96491    0.000002  11483.741035  6.000000e-07   \n",
       "813600     980476      979780    0.000002  11892.055173  7.000000e-07   \n",
       "813601     980476      980432    0.000002   6947.787457  3.600000e-06   \n",
       "813602     980476      980448    0.000002   7241.957070  1.000000e-06   \n",
       "813603     980476      980476    0.000002   7241.957070  1.000000e-06   \n",
       "\n",
       "          total_cost  \n",
       "1        2202.493741  \n",
       "2        3470.083191  \n",
       "3       10772.932402  \n",
       "4        8183.500350  \n",
       "5       10423.020570  \n",
       "...              ...  \n",
       "813599  11483.741038  \n",
       "813600  11892.055175  \n",
       "813601   6947.787462  \n",
       "813602   7241.957072  \n",
       "813603   7241.957072  \n",
       "\n",
       "[813603 rows x 6 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.dropna()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# supermarkets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>osm_id</th>\n",
       "      <th>code</th>\n",
       "      <th>fclass</th>\n",
       "      <th>name</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>34044390</td>\n",
       "      <td>2501</td>\n",
       "      <td>supermarket</td>\n",
       "      <td>Albert Heijn</td>\n",
       "      <td>POINT (4.26519 52.03464)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>34044395</td>\n",
       "      <td>2501</td>\n",
       "      <td>supermarket</td>\n",
       "      <td>Albert Heijn</td>\n",
       "      <td>POINT (4.26109 52.04296)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>34044402</td>\n",
       "      <td>2501</td>\n",
       "      <td>supermarket</td>\n",
       "      <td>Albert Heijn</td>\n",
       "      <td>POINT (4.30589 52.07775)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34044404</td>\n",
       "      <td>2501</td>\n",
       "      <td>supermarket</td>\n",
       "      <td>Coop</td>\n",
       "      <td>POINT (4.29346 52.08180)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>34044410</td>\n",
       "      <td>2501</td>\n",
       "      <td>supermarket</td>\n",
       "      <td>Albert Heijn</td>\n",
       "      <td>POINT (4.35675 52.09177)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>6551707251</td>\n",
       "      <td>2501</td>\n",
       "      <td>supermarket</td>\n",
       "      <td>BE &amp; CE Supermarkt</td>\n",
       "      <td>POINT (4.30532 52.06956)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>6800057159</td>\n",
       "      <td>2501</td>\n",
       "      <td>supermarket</td>\n",
       "      <td>Zaki</td>\n",
       "      <td>POINT (4.30967 52.06647)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>6901077377</td>\n",
       "      <td>2501</td>\n",
       "      <td>supermarket</td>\n",
       "      <td>Houtwijk</td>\n",
       "      <td>POINT (4.25072 52.05444)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>6964926432</td>\n",
       "      <td>2501</td>\n",
       "      <td>supermarket</td>\n",
       "      <td>Jumbo</td>\n",
       "      <td>POINT (4.35810 52.07554)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>7036733525</td>\n",
       "      <td>2501</td>\n",
       "      <td>supermarket</td>\n",
       "      <td>Jumbo City</td>\n",
       "      <td>POINT (4.31073 52.07621)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>219 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         osm_id  code       fclass                name  \\\n",
       "0      34044390  2501  supermarket        Albert Heijn   \n",
       "1      34044395  2501  supermarket        Albert Heijn   \n",
       "2      34044402  2501  supermarket        Albert Heijn   \n",
       "3      34044404  2501  supermarket                Coop   \n",
       "4      34044410  2501  supermarket        Albert Heijn   \n",
       "..          ...   ...          ...                 ...   \n",
       "214  6551707251  2501  supermarket  BE & CE Supermarkt   \n",
       "215  6800057159  2501  supermarket                Zaki   \n",
       "216  6901077377  2501  supermarket            Houtwijk   \n",
       "217  6964926432  2501  supermarket               Jumbo   \n",
       "218  7036733525  2501  supermarket          Jumbo City   \n",
       "\n",
       "                     geometry  \n",
       "0    POINT (4.26519 52.03464)  \n",
       "1    POINT (4.26109 52.04296)  \n",
       "2    POINT (4.30589 52.07775)  \n",
       "3    POINT (4.29346 52.08180)  \n",
       "4    POINT (4.35675 52.09177)  \n",
       "..                        ...  \n",
       "214  POINT (4.30532 52.06956)  \n",
       "215  POINT (4.30967 52.06647)  \n",
       "216  POINT (4.25072 52.05444)  \n",
       "217  POINT (4.35810 52.07554)  \n",
       "218  POINT (4.31073 52.07621)  \n",
       "\n",
       "[219 rows x 5 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4 = gpd.read_file(r\"C:\\Users\\bruno\\Documents\\J3Q3-4 BEP\\repositories\\supermarketsinregio.shp\")\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>origin_id</th>\n",
       "      <th>destinatio</th>\n",
       "      <th>entry_cost</th>\n",
       "      <th>network_co</th>\n",
       "      <th>exit_cost</th>\n",
       "      <th>total_cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1013285</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000039</td>\n",
       "      <td>9345.020277</td>\n",
       "      <td>0.000148</td>\n",
       "      <td>9345.020464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1013285</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000039</td>\n",
       "      <td>8218.907083</td>\n",
       "      <td>0.000133</td>\n",
       "      <td>8218.907254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1013285</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000039</td>\n",
       "      <td>3009.910563</td>\n",
       "      <td>0.000104</td>\n",
       "      <td>3009.910706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1013285</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000039</td>\n",
       "      <td>2511.395431</td>\n",
       "      <td>0.000129</td>\n",
       "      <td>2511.395599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1013285</td>\n",
       "      <td>5</td>\n",
       "      <td>0.000039</td>\n",
       "      <td>5247.287009</td>\n",
       "      <td>0.000044</td>\n",
       "      <td>5247.287091</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197533</th>\n",
       "      <td>980476</td>\n",
       "      <td>215</td>\n",
       "      <td>0.000048</td>\n",
       "      <td>8304.388512</td>\n",
       "      <td>0.000107</td>\n",
       "      <td>8304.388666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197534</th>\n",
       "      <td>980476</td>\n",
       "      <td>216</td>\n",
       "      <td>0.000048</td>\n",
       "      <td>7769.124798</td>\n",
       "      <td>0.000102</td>\n",
       "      <td>7769.124948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197535</th>\n",
       "      <td>980476</td>\n",
       "      <td>217</td>\n",
       "      <td>0.000048</td>\n",
       "      <td>11912.545373</td>\n",
       "      <td>0.000131</td>\n",
       "      <td>11912.545552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197536</th>\n",
       "      <td>980476</td>\n",
       "      <td>218</td>\n",
       "      <td>0.000048</td>\n",
       "      <td>5020.239591</td>\n",
       "      <td>0.000024</td>\n",
       "      <td>5020.239662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197537</th>\n",
       "      <td>980476</td>\n",
       "      <td>219</td>\n",
       "      <td>0.000048</td>\n",
       "      <td>8113.148756</td>\n",
       "      <td>0.000096</td>\n",
       "      <td>8113.148900</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>197538 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        origin_id  destinatio  entry_cost    network_co  exit_cost  \\\n",
       "0         1013285           1    0.000039   9345.020277   0.000148   \n",
       "1         1013285           2    0.000039   8218.907083   0.000133   \n",
       "2         1013285           3    0.000039   3009.910563   0.000104   \n",
       "3         1013285           4    0.000039   2511.395431   0.000129   \n",
       "4         1013285           5    0.000039   5247.287009   0.000044   \n",
       "...           ...         ...         ...           ...        ...   \n",
       "197533     980476         215    0.000048   8304.388512   0.000107   \n",
       "197534     980476         216    0.000048   7769.124798   0.000102   \n",
       "197535     980476         217    0.000048  11912.545373   0.000131   \n",
       "197536     980476         218    0.000048   5020.239591   0.000024   \n",
       "197537     980476         219    0.000048   8113.148756   0.000096   \n",
       "\n",
       "          total_cost  \n",
       "0        9345.020464  \n",
       "1        8218.907254  \n",
       "2        3009.910706  \n",
       "3        2511.395599  \n",
       "4        5247.287091  \n",
       "...              ...  \n",
       "197533   8304.388666  \n",
       "197534   7769.124948  \n",
       "197535  11912.545552  \n",
       "197536   5020.239662  \n",
       "197537   8113.148900  \n",
       "\n",
       "[197538 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dbf1 = Dbf5(r\"C:\\Users\\bruno\\Bureau RMC Dropbox\\2. RMC Intern\\1. Bureau\\3. Stagiairs\\Bruno\\BHermans\\qgis\\odmatrixsupermarketmetdestination.dbf\")\n",
    "df1 = dbf1.to_dataframe()\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>destinatio</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>210</th>\n",
       "      <th>211</th>\n",
       "      <th>212</th>\n",
       "      <th>213</th>\n",
       "      <th>214</th>\n",
       "      <th>215</th>\n",
       "      <th>216</th>\n",
       "      <th>217</th>\n",
       "      <th>218</th>\n",
       "      <th>219</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15941</th>\n",
       "      <td>20353.772159</td>\n",
       "      <td>20462.476879</td>\n",
       "      <td>16593.150339</td>\n",
       "      <td>17695.078283</td>\n",
       "      <td>13481.140522</td>\n",
       "      <td>14848.071709</td>\n",
       "      <td>16038.470149</td>\n",
       "      <td>12670.420412</td>\n",
       "      <td>11328.785312</td>\n",
       "      <td>2017.915813</td>\n",
       "      <td>...</td>\n",
       "      <td>11093.950336</td>\n",
       "      <td>22594.559508</td>\n",
       "      <td>16213.733216</td>\n",
       "      <td>14901.904906</td>\n",
       "      <td>18773.139530</td>\n",
       "      <td>16569.932853</td>\n",
       "      <td>16286.081227</td>\n",
       "      <td>20928.548593</td>\n",
       "      <td>12793.327874</td>\n",
       "      <td>16177.214329</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15943</th>\n",
       "      <td>10087.125843</td>\n",
       "      <td>10195.830563</td>\n",
       "      <td>6136.400516</td>\n",
       "      <td>7238.328459</td>\n",
       "      <td>2785.741330</td>\n",
       "      <td>4100.205208</td>\n",
       "      <td>5290.603648</td>\n",
       "      <td>1922.553911</td>\n",
       "      <td>1767.000102</td>\n",
       "      <td>9626.732885</td>\n",
       "      <td>...</td>\n",
       "      <td>1964.596074</td>\n",
       "      <td>12319.838014</td>\n",
       "      <td>11078.886561</td>\n",
       "      <td>4529.876226</td>\n",
       "      <td>8316.389707</td>\n",
       "      <td>6207.935520</td>\n",
       "      <td>5924.083893</td>\n",
       "      <td>10661.902277</td>\n",
       "      <td>2045.461373</td>\n",
       "      <td>5754.890598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15950</th>\n",
       "      <td>22567.226732</td>\n",
       "      <td>22604.030358</td>\n",
       "      <td>18482.743375</td>\n",
       "      <td>19584.671318</td>\n",
       "      <td>15907.538974</td>\n",
       "      <td>17079.760827</td>\n",
       "      <td>18020.003737</td>\n",
       "      <td>15096.818864</td>\n",
       "      <td>13755.183764</td>\n",
       "      <td>4161.764364</td>\n",
       "      <td>...</td>\n",
       "      <td>13520.348789</td>\n",
       "      <td>24578.170187</td>\n",
       "      <td>17826.258224</td>\n",
       "      <td>16791.497942</td>\n",
       "      <td>20662.732565</td>\n",
       "      <td>18459.525888</td>\n",
       "      <td>18175.674262</td>\n",
       "      <td>22918.243806</td>\n",
       "      <td>15219.726326</td>\n",
       "      <td>18066.807364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15951</th>\n",
       "      <td>3791.996695</td>\n",
       "      <td>2673.022409</td>\n",
       "      <td>4196.643162</td>\n",
       "      <td>3869.002094</td>\n",
       "      <td>8319.046690</td>\n",
       "      <td>6253.778186</td>\n",
       "      <td>6102.132548</td>\n",
       "      <td>8381.612777</td>\n",
       "      <td>10622.738060</td>\n",
       "      <td>19349.955527</td>\n",
       "      <td>...</td>\n",
       "      <td>11017.752168</td>\n",
       "      <td>2458.010992</td>\n",
       "      <td>11306.942374</td>\n",
       "      <td>5887.629053</td>\n",
       "      <td>2638.477141</td>\n",
       "      <td>3981.635851</td>\n",
       "      <td>4291.302831</td>\n",
       "      <td>851.263584</td>\n",
       "      <td>8319.641473</td>\n",
       "      <td>4488.705148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15952</th>\n",
       "      <td>22559.428759</td>\n",
       "      <td>22596.232385</td>\n",
       "      <td>18474.945402</td>\n",
       "      <td>19576.873346</td>\n",
       "      <td>15899.741002</td>\n",
       "      <td>17071.962855</td>\n",
       "      <td>18012.205764</td>\n",
       "      <td>15089.020892</td>\n",
       "      <td>13747.385792</td>\n",
       "      <td>4153.966392</td>\n",
       "      <td>...</td>\n",
       "      <td>13512.550816</td>\n",
       "      <td>24570.372215</td>\n",
       "      <td>17818.460252</td>\n",
       "      <td>16783.699969</td>\n",
       "      <td>20654.934593</td>\n",
       "      <td>18451.727916</td>\n",
       "      <td>18167.876289</td>\n",
       "      <td>22910.445834</td>\n",
       "      <td>15211.928354</td>\n",
       "      <td>18059.009392</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>2031678</th>\n",
       "      <td>9453.358834</td>\n",
       "      <td>8442.253805</td>\n",
       "      <td>2962.991887</td>\n",
       "      <td>2723.943619</td>\n",
       "      <td>4080.889505</td>\n",
       "      <td>2380.092224</td>\n",
       "      <td>1119.908881</td>\n",
       "      <td>4603.593854</td>\n",
       "      <td>6690.852582</td>\n",
       "      <td>15884.835208</td>\n",
       "      <td>...</td>\n",
       "      <td>7052.637054</td>\n",
       "      <td>7649.599830</td>\n",
       "      <td>13243.483224</td>\n",
       "      <td>2991.449601</td>\n",
       "      <td>3904.185720</td>\n",
       "      <td>4089.283712</td>\n",
       "      <td>4430.378081</td>\n",
       "      <td>7350.827341</td>\n",
       "      <td>4541.622549</td>\n",
       "      <td>3068.657752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2031679</th>\n",
       "      <td>9685.732730</td>\n",
       "      <td>8674.627700</td>\n",
       "      <td>3195.365782</td>\n",
       "      <td>2956.317515</td>\n",
       "      <td>3865.993202</td>\n",
       "      <td>2425.113960</td>\n",
       "      <td>1164.930617</td>\n",
       "      <td>4409.219029</td>\n",
       "      <td>6496.477757</td>\n",
       "      <td>15690.460384</td>\n",
       "      <td>...</td>\n",
       "      <td>6837.740751</td>\n",
       "      <td>7881.973726</td>\n",
       "      <td>13463.707339</td>\n",
       "      <td>3171.115646</td>\n",
       "      <td>4136.559616</td>\n",
       "      <td>4321.657608</td>\n",
       "      <td>4662.751977</td>\n",
       "      <td>7583.201236</td>\n",
       "      <td>4347.247725</td>\n",
       "      <td>3301.031648</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2031732</th>\n",
       "      <td>10418.641586</td>\n",
       "      <td>9372.226807</td>\n",
       "      <td>3953.482800</td>\n",
       "      <td>3664.715152</td>\n",
       "      <td>4066.091498</td>\n",
       "      <td>2893.233512</td>\n",
       "      <td>2091.832733</td>\n",
       "      <td>4625.541990</td>\n",
       "      <td>6704.086137</td>\n",
       "      <td>15906.783344</td>\n",
       "      <td>...</td>\n",
       "      <td>7037.839047</td>\n",
       "      <td>8502.362115</td>\n",
       "      <td>14234.801894</td>\n",
       "      <td>3984.209218</td>\n",
       "      <td>4831.383375</td>\n",
       "      <td>5079.774625</td>\n",
       "      <td>5420.868994</td>\n",
       "      <td>8266.072675</td>\n",
       "      <td>4563.570685</td>\n",
       "      <td>4059.148665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2031795</th>\n",
       "      <td>10729.622456</td>\n",
       "      <td>9683.207677</td>\n",
       "      <td>4236.131622</td>\n",
       "      <td>3975.696022</td>\n",
       "      <td>4136.705069</td>\n",
       "      <td>2963.847083</td>\n",
       "      <td>2174.150564</td>\n",
       "      <td>4696.155561</td>\n",
       "      <td>6774.699708</td>\n",
       "      <td>15977.396915</td>\n",
       "      <td>...</td>\n",
       "      <td>7108.452618</td>\n",
       "      <td>8813.342986</td>\n",
       "      <td>14368.202640</td>\n",
       "      <td>4055.947626</td>\n",
       "      <td>5142.364246</td>\n",
       "      <td>5297.476159</td>\n",
       "      <td>5626.530357</td>\n",
       "      <td>8577.053546</td>\n",
       "      <td>4634.184256</td>\n",
       "      <td>4341.797487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2031800</th>\n",
       "      <td>10311.177103</td>\n",
       "      <td>9264.762324</td>\n",
       "      <td>3846.018317</td>\n",
       "      <td>3557.250669</td>\n",
       "      <td>3959.006466</td>\n",
       "      <td>2786.148480</td>\n",
       "      <td>1984.368250</td>\n",
       "      <td>4518.456957</td>\n",
       "      <td>6597.001104</td>\n",
       "      <td>15799.698312</td>\n",
       "      <td>...</td>\n",
       "      <td>6930.754015</td>\n",
       "      <td>8394.897632</td>\n",
       "      <td>14127.337411</td>\n",
       "      <td>3876.744736</td>\n",
       "      <td>4723.918892</td>\n",
       "      <td>4972.310143</td>\n",
       "      <td>5313.404511</td>\n",
       "      <td>8158.608192</td>\n",
       "      <td>4456.485653</td>\n",
       "      <td>3951.684182</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>902 rows × 219 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "destinatio           1             2             3             4    \\\n",
       "origin_id                                                            \n",
       "15941       20353.772159  20462.476879  16593.150339  17695.078283   \n",
       "15943       10087.125843  10195.830563   6136.400516   7238.328459   \n",
       "15950       22567.226732  22604.030358  18482.743375  19584.671318   \n",
       "15951        3791.996695   2673.022409   4196.643162   3869.002094   \n",
       "15952       22559.428759  22596.232385  18474.945402  19576.873346   \n",
       "...                  ...           ...           ...           ...   \n",
       "2031678      9453.358834   8442.253805   2962.991887   2723.943619   \n",
       "2031679      9685.732730   8674.627700   3195.365782   2956.317515   \n",
       "2031732     10418.641586   9372.226807   3953.482800   3664.715152   \n",
       "2031795     10729.622456   9683.207677   4236.131622   3975.696022   \n",
       "2031800     10311.177103   9264.762324   3846.018317   3557.250669   \n",
       "\n",
       "destinatio           5             6             7             8    \\\n",
       "origin_id                                                            \n",
       "15941       13481.140522  14848.071709  16038.470149  12670.420412   \n",
       "15943        2785.741330   4100.205208   5290.603648   1922.553911   \n",
       "15950       15907.538974  17079.760827  18020.003737  15096.818864   \n",
       "15951        8319.046690   6253.778186   6102.132548   8381.612777   \n",
       "15952       15899.741002  17071.962855  18012.205764  15089.020892   \n",
       "...                  ...           ...           ...           ...   \n",
       "2031678      4080.889505   2380.092224   1119.908881   4603.593854   \n",
       "2031679      3865.993202   2425.113960   1164.930617   4409.219029   \n",
       "2031732      4066.091498   2893.233512   2091.832733   4625.541990   \n",
       "2031795      4136.705069   2963.847083   2174.150564   4696.155561   \n",
       "2031800      3959.006466   2786.148480   1984.368250   4518.456957   \n",
       "\n",
       "destinatio           9             10   ...           210           211  \\\n",
       "origin_id                               ...                               \n",
       "15941       11328.785312   2017.915813  ...  11093.950336  22594.559508   \n",
       "15943        1767.000102   9626.732885  ...   1964.596074  12319.838014   \n",
       "15950       13755.183764   4161.764364  ...  13520.348789  24578.170187   \n",
       "15951       10622.738060  19349.955527  ...  11017.752168   2458.010992   \n",
       "15952       13747.385792   4153.966392  ...  13512.550816  24570.372215   \n",
       "...                  ...           ...  ...           ...           ...   \n",
       "2031678      6690.852582  15884.835208  ...   7052.637054   7649.599830   \n",
       "2031679      6496.477757  15690.460384  ...   6837.740751   7881.973726   \n",
       "2031732      6704.086137  15906.783344  ...   7037.839047   8502.362115   \n",
       "2031795      6774.699708  15977.396915  ...   7108.452618   8813.342986   \n",
       "2031800      6597.001104  15799.698312  ...   6930.754015   8394.897632   \n",
       "\n",
       "destinatio           212           213           214           215  \\\n",
       "origin_id                                                            \n",
       "15941       16213.733216  14901.904906  18773.139530  16569.932853   \n",
       "15943       11078.886561   4529.876226   8316.389707   6207.935520   \n",
       "15950       17826.258224  16791.497942  20662.732565  18459.525888   \n",
       "15951       11306.942374   5887.629053   2638.477141   3981.635851   \n",
       "15952       17818.460252  16783.699969  20654.934593  18451.727916   \n",
       "...                  ...           ...           ...           ...   \n",
       "2031678     13243.483224   2991.449601   3904.185720   4089.283712   \n",
       "2031679     13463.707339   3171.115646   4136.559616   4321.657608   \n",
       "2031732     14234.801894   3984.209218   4831.383375   5079.774625   \n",
       "2031795     14368.202640   4055.947626   5142.364246   5297.476159   \n",
       "2031800     14127.337411   3876.744736   4723.918892   4972.310143   \n",
       "\n",
       "destinatio           216           217           218           219  \n",
       "origin_id                                                           \n",
       "15941       16286.081227  20928.548593  12793.327874  16177.214329  \n",
       "15943        5924.083893  10661.902277   2045.461373   5754.890598  \n",
       "15950       18175.674262  22918.243806  15219.726326  18066.807364  \n",
       "15951        4291.302831    851.263584   8319.641473   4488.705148  \n",
       "15952       18167.876289  22910.445834  15211.928354  18059.009392  \n",
       "...                  ...           ...           ...           ...  \n",
       "2031678      4430.378081   7350.827341   4541.622549   3068.657752  \n",
       "2031679      4662.751977   7583.201236   4347.247725   3301.031648  \n",
       "2031732      5420.868994   8266.072675   4563.570685   4059.148665  \n",
       "2031795      5626.530357   8577.053546   4634.184256   4341.797487  \n",
       "2031800      5313.404511   8158.608192   4456.485653   3951.684182  \n",
       "\n",
       "[902 rows x 219 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = df1.pivot(index='origin_id', columns='destinatio', values = 'total_cost')\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>destinatio</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>211</th>\n",
       "      <th>212</th>\n",
       "      <th>213</th>\n",
       "      <th>214</th>\n",
       "      <th>215</th>\n",
       "      <th>216</th>\n",
       "      <th>217</th>\n",
       "      <th>218</th>\n",
       "      <th>219</th>\n",
       "      <th>closest_market</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>origin_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15941</th>\n",
       "      <td>20353.772159</td>\n",
       "      <td>20462.476879</td>\n",
       "      <td>16593.150339</td>\n",
       "      <td>17695.078283</td>\n",
       "      <td>13481.140522</td>\n",
       "      <td>14848.071709</td>\n",
       "      <td>16038.470149</td>\n",
       "      <td>12670.420412</td>\n",
       "      <td>11328.785312</td>\n",
       "      <td>2017.915813</td>\n",
       "      <td>...</td>\n",
       "      <td>22594.559508</td>\n",
       "      <td>16213.733216</td>\n",
       "      <td>14901.904906</td>\n",
       "      <td>18773.139530</td>\n",
       "      <td>16569.932853</td>\n",
       "      <td>16286.081227</td>\n",
       "      <td>20928.548593</td>\n",
       "      <td>12793.327874</td>\n",
       "      <td>16177.214329</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15943</th>\n",
       "      <td>10087.125843</td>\n",
       "      <td>10195.830563</td>\n",
       "      <td>6136.400516</td>\n",
       "      <td>7238.328459</td>\n",
       "      <td>2785.741330</td>\n",
       "      <td>4100.205208</td>\n",
       "      <td>5290.603648</td>\n",
       "      <td>1922.553911</td>\n",
       "      <td>1767.000102</td>\n",
       "      <td>9626.732885</td>\n",
       "      <td>...</td>\n",
       "      <td>12319.838014</td>\n",
       "      <td>11078.886561</td>\n",
       "      <td>4529.876226</td>\n",
       "      <td>8316.389707</td>\n",
       "      <td>6207.935520</td>\n",
       "      <td>5924.083893</td>\n",
       "      <td>10661.902277</td>\n",
       "      <td>2045.461373</td>\n",
       "      <td>5754.890598</td>\n",
       "      <td>126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15950</th>\n",
       "      <td>22567.226732</td>\n",
       "      <td>22604.030358</td>\n",
       "      <td>18482.743375</td>\n",
       "      <td>19584.671318</td>\n",
       "      <td>15907.538974</td>\n",
       "      <td>17079.760827</td>\n",
       "      <td>18020.003737</td>\n",
       "      <td>15096.818864</td>\n",
       "      <td>13755.183764</td>\n",
       "      <td>4161.764364</td>\n",
       "      <td>...</td>\n",
       "      <td>24578.170187</td>\n",
       "      <td>17826.258224</td>\n",
       "      <td>16791.497942</td>\n",
       "      <td>20662.732565</td>\n",
       "      <td>18459.525888</td>\n",
       "      <td>18175.674262</td>\n",
       "      <td>22918.243806</td>\n",
       "      <td>15219.726326</td>\n",
       "      <td>18066.807364</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15951</th>\n",
       "      <td>3791.996695</td>\n",
       "      <td>2673.022409</td>\n",
       "      <td>4196.643162</td>\n",
       "      <td>3869.002094</td>\n",
       "      <td>8319.046690</td>\n",
       "      <td>6253.778186</td>\n",
       "      <td>6102.132548</td>\n",
       "      <td>8381.612777</td>\n",
       "      <td>10622.738060</td>\n",
       "      <td>19349.955527</td>\n",
       "      <td>...</td>\n",
       "      <td>2458.010992</td>\n",
       "      <td>11306.942374</td>\n",
       "      <td>5887.629053</td>\n",
       "      <td>2638.477141</td>\n",
       "      <td>3981.635851</td>\n",
       "      <td>4291.302831</td>\n",
       "      <td>851.263584</td>\n",
       "      <td>8319.641473</td>\n",
       "      <td>4488.705148</td>\n",
       "      <td>155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15952</th>\n",
       "      <td>22559.428759</td>\n",
       "      <td>22596.232385</td>\n",
       "      <td>18474.945402</td>\n",
       "      <td>19576.873346</td>\n",
       "      <td>15899.741002</td>\n",
       "      <td>17071.962855</td>\n",
       "      <td>18012.205764</td>\n",
       "      <td>15089.020892</td>\n",
       "      <td>13747.385792</td>\n",
       "      <td>4153.966392</td>\n",
       "      <td>...</td>\n",
       "      <td>24570.372215</td>\n",
       "      <td>17818.460252</td>\n",
       "      <td>16783.699969</td>\n",
       "      <td>20654.934593</td>\n",
       "      <td>18451.727916</td>\n",
       "      <td>18167.876289</td>\n",
       "      <td>22910.445834</td>\n",
       "      <td>15211.928354</td>\n",
       "      <td>18059.009392</td>\n",
       "      <td>65</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>2031678</th>\n",
       "      <td>9453.358834</td>\n",
       "      <td>8442.253805</td>\n",
       "      <td>2962.991887</td>\n",
       "      <td>2723.943619</td>\n",
       "      <td>4080.889505</td>\n",
       "      <td>2380.092224</td>\n",
       "      <td>1119.908881</td>\n",
       "      <td>4603.593854</td>\n",
       "      <td>6690.852582</td>\n",
       "      <td>15884.835208</td>\n",
       "      <td>...</td>\n",
       "      <td>7649.599830</td>\n",
       "      <td>13243.483224</td>\n",
       "      <td>2991.449601</td>\n",
       "      <td>3904.185720</td>\n",
       "      <td>4089.283712</td>\n",
       "      <td>4430.378081</td>\n",
       "      <td>7350.827341</td>\n",
       "      <td>4541.622549</td>\n",
       "      <td>3068.657752</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2031679</th>\n",
       "      <td>9685.732730</td>\n",
       "      <td>8674.627700</td>\n",
       "      <td>3195.365782</td>\n",
       "      <td>2956.317515</td>\n",
       "      <td>3865.993202</td>\n",
       "      <td>2425.113960</td>\n",
       "      <td>1164.930617</td>\n",
       "      <td>4409.219029</td>\n",
       "      <td>6496.477757</td>\n",
       "      <td>15690.460384</td>\n",
       "      <td>...</td>\n",
       "      <td>7881.973726</td>\n",
       "      <td>13463.707339</td>\n",
       "      <td>3171.115646</td>\n",
       "      <td>4136.559616</td>\n",
       "      <td>4321.657608</td>\n",
       "      <td>4662.751977</td>\n",
       "      <td>7583.201236</td>\n",
       "      <td>4347.247725</td>\n",
       "      <td>3301.031648</td>\n",
       "      <td>181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2031732</th>\n",
       "      <td>10418.641586</td>\n",
       "      <td>9372.226807</td>\n",
       "      <td>3953.482800</td>\n",
       "      <td>3664.715152</td>\n",
       "      <td>4066.091498</td>\n",
       "      <td>2893.233512</td>\n",
       "      <td>2091.832733</td>\n",
       "      <td>4625.541990</td>\n",
       "      <td>6704.086137</td>\n",
       "      <td>15906.783344</td>\n",
       "      <td>...</td>\n",
       "      <td>8502.362115</td>\n",
       "      <td>14234.801894</td>\n",
       "      <td>3984.209218</td>\n",
       "      <td>4831.383375</td>\n",
       "      <td>5079.774625</td>\n",
       "      <td>5420.868994</td>\n",
       "      <td>8266.072675</td>\n",
       "      <td>4563.570685</td>\n",
       "      <td>4059.148665</td>\n",
       "      <td>181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2031795</th>\n",
       "      <td>10729.622456</td>\n",
       "      <td>9683.207677</td>\n",
       "      <td>4236.131622</td>\n",
       "      <td>3975.696022</td>\n",
       "      <td>4136.705069</td>\n",
       "      <td>2963.847083</td>\n",
       "      <td>2174.150564</td>\n",
       "      <td>4696.155561</td>\n",
       "      <td>6774.699708</td>\n",
       "      <td>15977.396915</td>\n",
       "      <td>...</td>\n",
       "      <td>8813.342986</td>\n",
       "      <td>14368.202640</td>\n",
       "      <td>4055.947626</td>\n",
       "      <td>5142.364246</td>\n",
       "      <td>5297.476159</td>\n",
       "      <td>5626.530357</td>\n",
       "      <td>8577.053546</td>\n",
       "      <td>4634.184256</td>\n",
       "      <td>4341.797487</td>\n",
       "      <td>181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2031800</th>\n",
       "      <td>10311.177103</td>\n",
       "      <td>9264.762324</td>\n",
       "      <td>3846.018317</td>\n",
       "      <td>3557.250669</td>\n",
       "      <td>3959.006466</td>\n",
       "      <td>2786.148480</td>\n",
       "      <td>1984.368250</td>\n",
       "      <td>4518.456957</td>\n",
       "      <td>6597.001104</td>\n",
       "      <td>15799.698312</td>\n",
       "      <td>...</td>\n",
       "      <td>8394.897632</td>\n",
       "      <td>14127.337411</td>\n",
       "      <td>3876.744736</td>\n",
       "      <td>4723.918892</td>\n",
       "      <td>4972.310143</td>\n",
       "      <td>5313.404511</td>\n",
       "      <td>8158.608192</td>\n",
       "      <td>4456.485653</td>\n",
       "      <td>3951.684182</td>\n",
       "      <td>181</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>902 rows × 220 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "destinatio             1             2             3             4  \\\n",
       "origin_id                                                            \n",
       "15941       20353.772159  20462.476879  16593.150339  17695.078283   \n",
       "15943       10087.125843  10195.830563   6136.400516   7238.328459   \n",
       "15950       22567.226732  22604.030358  18482.743375  19584.671318   \n",
       "15951        3791.996695   2673.022409   4196.643162   3869.002094   \n",
       "15952       22559.428759  22596.232385  18474.945402  19576.873346   \n",
       "...                  ...           ...           ...           ...   \n",
       "2031678      9453.358834   8442.253805   2962.991887   2723.943619   \n",
       "2031679      9685.732730   8674.627700   3195.365782   2956.317515   \n",
       "2031732     10418.641586   9372.226807   3953.482800   3664.715152   \n",
       "2031795     10729.622456   9683.207677   4236.131622   3975.696022   \n",
       "2031800     10311.177103   9264.762324   3846.018317   3557.250669   \n",
       "\n",
       "destinatio             5             6             7             8  \\\n",
       "origin_id                                                            \n",
       "15941       13481.140522  14848.071709  16038.470149  12670.420412   \n",
       "15943        2785.741330   4100.205208   5290.603648   1922.553911   \n",
       "15950       15907.538974  17079.760827  18020.003737  15096.818864   \n",
       "15951        8319.046690   6253.778186   6102.132548   8381.612777   \n",
       "15952       15899.741002  17071.962855  18012.205764  15089.020892   \n",
       "...                  ...           ...           ...           ...   \n",
       "2031678      4080.889505   2380.092224   1119.908881   4603.593854   \n",
       "2031679      3865.993202   2425.113960   1164.930617   4409.219029   \n",
       "2031732      4066.091498   2893.233512   2091.832733   4625.541990   \n",
       "2031795      4136.705069   2963.847083   2174.150564   4696.155561   \n",
       "2031800      3959.006466   2786.148480   1984.368250   4518.456957   \n",
       "\n",
       "destinatio             9            10  ...           211           212  \\\n",
       "origin_id                               ...                               \n",
       "15941       11328.785312   2017.915813  ...  22594.559508  16213.733216   \n",
       "15943        1767.000102   9626.732885  ...  12319.838014  11078.886561   \n",
       "15950       13755.183764   4161.764364  ...  24578.170187  17826.258224   \n",
       "15951       10622.738060  19349.955527  ...   2458.010992  11306.942374   \n",
       "15952       13747.385792   4153.966392  ...  24570.372215  17818.460252   \n",
       "...                  ...           ...  ...           ...           ...   \n",
       "2031678      6690.852582  15884.835208  ...   7649.599830  13243.483224   \n",
       "2031679      6496.477757  15690.460384  ...   7881.973726  13463.707339   \n",
       "2031732      6704.086137  15906.783344  ...   8502.362115  14234.801894   \n",
       "2031795      6774.699708  15977.396915  ...   8813.342986  14368.202640   \n",
       "2031800      6597.001104  15799.698312  ...   8394.897632  14127.337411   \n",
       "\n",
       "destinatio           213           214           215           216  \\\n",
       "origin_id                                                            \n",
       "15941       14901.904906  18773.139530  16569.932853  16286.081227   \n",
       "15943        4529.876226   8316.389707   6207.935520   5924.083893   \n",
       "15950       16791.497942  20662.732565  18459.525888  18175.674262   \n",
       "15951        5887.629053   2638.477141   3981.635851   4291.302831   \n",
       "15952       16783.699969  20654.934593  18451.727916  18167.876289   \n",
       "...                  ...           ...           ...           ...   \n",
       "2031678      2991.449601   3904.185720   4089.283712   4430.378081   \n",
       "2031679      3171.115646   4136.559616   4321.657608   4662.751977   \n",
       "2031732      3984.209218   4831.383375   5079.774625   5420.868994   \n",
       "2031795      4055.947626   5142.364246   5297.476159   5626.530357   \n",
       "2031800      3876.744736   4723.918892   4972.310143   5313.404511   \n",
       "\n",
       "destinatio           217           218           219  closest_market  \n",
       "origin_id                                                             \n",
       "15941       20928.548593  12793.327874  16177.214329              37  \n",
       "15943       10661.902277   2045.461373   5754.890598             126  \n",
       "15950       22918.243806  15219.726326  18066.807364              65  \n",
       "15951         851.263584   8319.641473   4488.705148             155  \n",
       "15952       22910.445834  15211.928354  18059.009392              65  \n",
       "...                  ...           ...           ...             ...  \n",
       "2031678      7350.827341   4541.622549   3068.657752               7  \n",
       "2031679      7583.201236   4347.247725   3301.031648             181  \n",
       "2031732      8266.072675   4563.570685   4059.148665             181  \n",
       "2031795      8577.053546   4634.184256   4341.797487             181  \n",
       "2031800      8158.608192   4456.485653   3951.684182             181  \n",
       "\n",
       "[902 rows x 220 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1['closest_market'] = df1.idxmin(axis=1)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Distance_to_SuperMarket</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15941</td>\n",
       "      <td>37</td>\n",
       "      <td>29.379494</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>15943</td>\n",
       "      <td>126</td>\n",
       "      <td>1267.857307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15950</td>\n",
       "      <td>65</td>\n",
       "      <td>168.212369</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>15951</td>\n",
       "      <td>155</td>\n",
       "      <td>736.187807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>15952</td>\n",
       "      <td>65</td>\n",
       "      <td>160.414396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>897</th>\n",
       "      <td>2031678</td>\n",
       "      <td>7</td>\n",
       "      <td>1119.908881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>898</th>\n",
       "      <td>2031679</td>\n",
       "      <td>181</td>\n",
       "      <td>1073.289482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>899</th>\n",
       "      <td>2031732</td>\n",
       "      <td>181</td>\n",
       "      <td>553.032186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>900</th>\n",
       "      <td>2031795</td>\n",
       "      <td>181</td>\n",
       "      <td>91.225451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>901</th>\n",
       "      <td>2031800</td>\n",
       "      <td>181</td>\n",
       "      <td>509.670806</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>902 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Origin  Destination  Distance_to_SuperMarket\n",
       "0      15941           37                29.379494\n",
       "1      15943          126              1267.857307\n",
       "2      15950           65               168.212369\n",
       "3      15951          155               736.187807\n",
       "4      15952           65               160.414396\n",
       "..       ...          ...                      ...\n",
       "897  2031678            7              1119.908881\n",
       "898  2031679          181              1073.289482\n",
       "899  2031732          181               553.032186\n",
       "900  2031795          181                91.225451\n",
       "901  2031800          181               509.670806\n",
       "\n",
       "[902 rows x 3 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Origins = df1.index.tolist()\n",
    "Closeststops = df1['closest_market'].tolist()\n",
    "Distance = []\n",
    "for i in range(902):\n",
    "    Origin = Origins[i]\n",
    "    Destination = Closeststops[i]\n",
    "    Distance.append(df1.loc[Origin, Destination])\n",
    "df2 = pd.DataFrame({'Origin': Origins, 'Destination': Closeststops, 'Distance_to_SuperMarket':Distance})\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>origin_id</th>\n",
       "      <th>destinatio</th>\n",
       "      <th>entry_cost</th>\n",
       "      <th>network_co</th>\n",
       "      <th>exit_cost</th>\n",
       "      <th>total_cost</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destination</th>\n",
       "      <th>Distance_to_SuperMarket</th>\n",
       "      <th>total_to_supermarket</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1013285</td>\n",
       "      <td>1013304</td>\n",
       "      <td>1.300000e-06</td>\n",
       "      <td>2202.493737</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>2202.493741</td>\n",
       "      <td>1013304</td>\n",
       "      <td>12</td>\n",
       "      <td>79.205328</td>\n",
       "      <td>2281.699068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1013304</td>\n",
       "      <td>1013304</td>\n",
       "      <td>2.300000e-06</td>\n",
       "      <td>2202.493737</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>2202.493741</td>\n",
       "      <td>1013304</td>\n",
       "      <td>12</td>\n",
       "      <td>79.205328</td>\n",
       "      <td>2281.699068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1013306</td>\n",
       "      <td>1013304</td>\n",
       "      <td>2.200000e-06</td>\n",
       "      <td>1267.589451</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>1267.589455</td>\n",
       "      <td>1013304</td>\n",
       "      <td>12</td>\n",
       "      <td>79.205328</td>\n",
       "      <td>1346.794783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103939</td>\n",
       "      <td>1013304</td>\n",
       "      <td>7.000000e-07</td>\n",
       "      <td>12758.216341</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>12758.216344</td>\n",
       "      <td>1013304</td>\n",
       "      <td>12</td>\n",
       "      <td>79.205328</td>\n",
       "      <td>12837.421672</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>103942</td>\n",
       "      <td>1013304</td>\n",
       "      <td>5.000000e-06</td>\n",
       "      <td>9527.723067</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>9527.723075</td>\n",
       "      <td>1013304</td>\n",
       "      <td>12</td>\n",
       "      <td>79.205328</td>\n",
       "      <td>9606.928402</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>813598</th>\n",
       "      <td>96491</td>\n",
       "      <td>1013285</td>\n",
       "      <td>6.000000e-07</td>\n",
       "      <td>8342.019998</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>8342.020000</td>\n",
       "      <td>1013285</td>\n",
       "      <td>13</td>\n",
       "      <td>1111.066006</td>\n",
       "      <td>9453.086006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813599</th>\n",
       "      <td>979780</td>\n",
       "      <td>1013285</td>\n",
       "      <td>7.000000e-07</td>\n",
       "      <td>9829.745292</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>9829.745294</td>\n",
       "      <td>1013285</td>\n",
       "      <td>13</td>\n",
       "      <td>1111.066006</td>\n",
       "      <td>10940.811300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813600</th>\n",
       "      <td>980432</td>\n",
       "      <td>1013285</td>\n",
       "      <td>3.600000e-06</td>\n",
       "      <td>12473.646507</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>12473.646512</td>\n",
       "      <td>1013285</td>\n",
       "      <td>13</td>\n",
       "      <td>1111.066006</td>\n",
       "      <td>13584.712518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813601</th>\n",
       "      <td>980448</td>\n",
       "      <td>1013285</td>\n",
       "      <td>1.000000e-06</td>\n",
       "      <td>12767.816120</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>12767.816123</td>\n",
       "      <td>1013285</td>\n",
       "      <td>13</td>\n",
       "      <td>1111.066006</td>\n",
       "      <td>13878.882129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813602</th>\n",
       "      <td>980476</td>\n",
       "      <td>1013285</td>\n",
       "      <td>1.500000e-06</td>\n",
       "      <td>11170.170077</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>11170.170079</td>\n",
       "      <td>1013285</td>\n",
       "      <td>13</td>\n",
       "      <td>1111.066006</td>\n",
       "      <td>12281.236086</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>813603 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        origin_id  destinatio    entry_cost    network_co  exit_cost  \\\n",
       "0         1013285     1013304  1.300000e-06   2202.493737   0.000002   \n",
       "1         1013304     1013304  2.300000e-06   2202.493737   0.000001   \n",
       "2         1013306     1013304  2.200000e-06   1267.589451   0.000002   \n",
       "3          103939     1013304  7.000000e-07  12758.216341   0.000002   \n",
       "4          103942     1013304  5.000000e-06   9527.723067   0.000002   \n",
       "...           ...         ...           ...           ...        ...   \n",
       "813598      96491     1013285  6.000000e-07   8342.019998   0.000001   \n",
       "813599     979780     1013285  7.000000e-07   9829.745292   0.000001   \n",
       "813600     980432     1013285  3.600000e-06  12473.646507   0.000001   \n",
       "813601     980448     1013285  1.000000e-06  12767.816120   0.000001   \n",
       "813602     980476     1013285  1.500000e-06  11170.170077   0.000001   \n",
       "\n",
       "          total_cost   Origin  Destination  Distance_to_SuperMarket  \\\n",
       "0        2202.493741  1013304           12                79.205328   \n",
       "1        2202.493741  1013304           12                79.205328   \n",
       "2        1267.589455  1013304           12                79.205328   \n",
       "3       12758.216344  1013304           12                79.205328   \n",
       "4        9527.723075  1013304           12                79.205328   \n",
       "...              ...      ...          ...                      ...   \n",
       "813598   8342.020000  1013285           13              1111.066006   \n",
       "813599   9829.745294  1013285           13              1111.066006   \n",
       "813600  12473.646512  1013285           13              1111.066006   \n",
       "813601  12767.816123  1013285           13              1111.066006   \n",
       "813602  11170.170079  1013285           13              1111.066006   \n",
       "\n",
       "        total_to_supermarket  \n",
       "0                2281.699068  \n",
       "1                2281.699068  \n",
       "2                1346.794783  \n",
       "3               12837.421672  \n",
       "4                9606.928402  \n",
       "...                      ...  \n",
       "813598           9453.086006  \n",
       "813599          10940.811300  \n",
       "813600          13584.712518  \n",
       "813601          13878.882129  \n",
       "813602          12281.236086  \n",
       "\n",
       "[813603 rows x 10 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = df.merge(df2, left_on = 'destinatio', right_on = 'Origin')\n",
    "merged['total_to_supermarket'] = merged['Distance_to_SuperMarket'] + merged['total_cost']\n",
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>origin</th>\n",
       "      <th>distance_to_supermarket</th>\n",
       "      <th>supermarketid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1013285</td>\n",
       "      <td>1111.364795</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1013304</td>\n",
       "      <td>537.711815</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1013306</td>\n",
       "      <td>509.556531</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103939</td>\n",
       "      <td>691.216536</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>103942</td>\n",
       "      <td>535.886214</td>\n",
       "      <td>209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>897</th>\n",
       "      <td>96491</td>\n",
       "      <td>532.500212</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>898</th>\n",
       "      <td>979780</td>\n",
       "      <td>465.495800</td>\n",
       "      <td>129</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>899</th>\n",
       "      <td>980432</td>\n",
       "      <td>917.790210</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>900</th>\n",
       "      <td>980448</td>\n",
       "      <td>602.550866</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>901</th>\n",
       "      <td>980476</td>\n",
       "      <td>1670.329933</td>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>902 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      origin  distance_to_supermarket  supermarketid\n",
       "0    1013285              1111.364795             13\n",
       "1    1013304               537.711815             12\n",
       "2    1013306               509.556531             12\n",
       "3     103939               691.216536             87\n",
       "4     103942               535.886214            209\n",
       "..       ...                      ...            ...\n",
       "897    96491               532.500212             56\n",
       "898   979780               465.495800            129\n",
       "899   980432               917.790210             15\n",
       "900   980448               602.550866            203\n",
       "901   980476              1670.329933            116\n",
       "\n",
       "[902 rows x 3 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "L = merged.origin_id.unique().tolist()\n",
    "distance = []\n",
    "for i in L:\n",
    "    distance.append({'origin': str(i), 'distance_to_supermarket': merged[merged['origin_id'] == i].total_to_supermarket.min(),\n",
    "                    'supermarketid':\n",
    "                    merged[(merged['origin_id'] == i) &\n",
    "                   (merged['total_to_supermarket'] == merged[merged['origin_id'] == i]\n",
    "                   .total_to_supermarket.min())]['Destination'].to_list()[0]\n",
    "                    })\n",
    "distances = pd.DataFrame(distance)\n",
    "distances"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>name</th>\n",
       "      <th>fclass</th>\n",
       "      <th>osm_id</th>\n",
       "      <th>long</th>\n",
       "      <th>latt</th>\n",
       "      <th>cluster</th>\n",
       "      <th>longdiff</th>\n",
       "      <th>Lattdiff</th>\n",
       "      <th>Euclidian</th>\n",
       "      <th>Origin</th>\n",
       "      <th>Destinatio</th>\n",
       "      <th>Distance_t</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267999488</td>\n",
       "      <td>4.259434</td>\n",
       "      <td>52.049581</td>\n",
       "      <td>0</td>\n",
       "      <td>0.000143</td>\n",
       "      <td>0.000003</td>\n",
       "      <td>0.000143</td>\n",
       "      <td>267999488</td>\n",
       "      <td>652095</td>\n",
       "      <td>238.997482</td>\n",
       "      <td>POINT (4.25943 52.04958)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>265070349</td>\n",
       "      <td>4.343525</td>\n",
       "      <td>52.054926</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000123</td>\n",
       "      <td>0.000084</td>\n",
       "      <td>0.000149</td>\n",
       "      <td>265070349</td>\n",
       "      <td>480300</td>\n",
       "      <td>348.614352</td>\n",
       "      <td>POINT (4.34353 52.05493)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267555805</td>\n",
       "      <td>4.280483</td>\n",
       "      <td>52.084788</td>\n",
       "      <td>2</td>\n",
       "      <td>0.000014</td>\n",
       "      <td>0.000053</td>\n",
       "      <td>0.000055</td>\n",
       "      <td>267555805</td>\n",
       "      <td>16647</td>\n",
       "      <td>101.205184</td>\n",
       "      <td>POINT (4.28048 52.08479)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>255149925</td>\n",
       "      <td>4.415170</td>\n",
       "      <td>52.061477</td>\n",
       "      <td>3</td>\n",
       "      <td>0.000115</td>\n",
       "      <td>0.000116</td>\n",
       "      <td>0.000163</td>\n",
       "      <td>255149925</td>\n",
       "      <td>651858</td>\n",
       "      <td>1125.415327</td>\n",
       "      <td>POINT (4.41517 52.06148)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>265477156</td>\n",
       "      <td>4.301079</td>\n",
       "      <td>52.030911</td>\n",
       "      <td>4</td>\n",
       "      <td>0.000096</td>\n",
       "      <td>0.000038</td>\n",
       "      <td>0.000103</td>\n",
       "      <td>265477156</td>\n",
       "      <td>1400191</td>\n",
       "      <td>331.874966</td>\n",
       "      <td>POINT (4.30108 52.03091)</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6009</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>265705659</td>\n",
       "      <td>4.323908</td>\n",
       "      <td>52.041390</td>\n",
       "      <td>5995</td>\n",
       "      <td>0.000077</td>\n",
       "      <td>0.000029</td>\n",
       "      <td>0.000082</td>\n",
       "      <td>265705659</td>\n",
       "      <td>16732</td>\n",
       "      <td>128.737307</td>\n",
       "      <td>POINT (4.32391 52.04139)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6010</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267790175</td>\n",
       "      <td>4.261039</td>\n",
       "      <td>52.078407</td>\n",
       "      <td>5996</td>\n",
       "      <td>0.000115</td>\n",
       "      <td>0.000047</td>\n",
       "      <td>0.000124</td>\n",
       "      <td>267790175</td>\n",
       "      <td>1541931</td>\n",
       "      <td>395.267447</td>\n",
       "      <td>POINT (4.26104 52.07841)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6011</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>265071405</td>\n",
       "      <td>4.344107</td>\n",
       "      <td>52.059060</td>\n",
       "      <td>5997</td>\n",
       "      <td>0.000030</td>\n",
       "      <td>0.000086</td>\n",
       "      <td>0.000091</td>\n",
       "      <td>265071405</td>\n",
       "      <td>16482</td>\n",
       "      <td>34.468616</td>\n",
       "      <td>POINT (4.34411 52.05906)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6012</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267533186</td>\n",
       "      <td>4.278420</td>\n",
       "      <td>52.064253</td>\n",
       "      <td>5998</td>\n",
       "      <td>0.000019</td>\n",
       "      <td>0.000104</td>\n",
       "      <td>0.000105</td>\n",
       "      <td>267533186</td>\n",
       "      <td>16025</td>\n",
       "      <td>184.221788</td>\n",
       "      <td>POINT (4.27842 52.06425)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6013</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>264826291</td>\n",
       "      <td>4.338082</td>\n",
       "      <td>52.082371</td>\n",
       "      <td>5999</td>\n",
       "      <td>0.000084</td>\n",
       "      <td>0.000140</td>\n",
       "      <td>0.000163</td>\n",
       "      <td>264826291</td>\n",
       "      <td>16253</td>\n",
       "      <td>238.083857</td>\n",
       "      <td>POINT (4.33808 52.08237)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6014 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      name    fclass     osm_id      long       latt  cluster  longdiff  \\\n",
       "0     None  building  267999488  4.259434  52.049581        0  0.000143   \n",
       "1     None  building  265070349  4.343525  52.054926        1  0.000123   \n",
       "2     None  building  267555805  4.280483  52.084788        2  0.000014   \n",
       "3     None  building  255149925  4.415170  52.061477        3  0.000115   \n",
       "4     None  building  265477156  4.301079  52.030911        4  0.000096   \n",
       "...    ...       ...        ...       ...        ...      ...       ...   \n",
       "6009  None  building  265705659  4.323908  52.041390     5995  0.000077   \n",
       "6010  None  building  267790175  4.261039  52.078407     5996  0.000115   \n",
       "6011  None  building  265071405  4.344107  52.059060     5997  0.000030   \n",
       "6012  None  building  267533186  4.278420  52.064253     5998  0.000019   \n",
       "6013  None  building  264826291  4.338082  52.082371     5999  0.000084   \n",
       "\n",
       "      Lattdiff  Euclidian     Origin Destinatio   Distance_t  \\\n",
       "0     0.000003   0.000143  267999488     652095   238.997482   \n",
       "1     0.000084   0.000149  265070349     480300   348.614352   \n",
       "2     0.000053   0.000055  267555805      16647   101.205184   \n",
       "3     0.000116   0.000163  255149925     651858  1125.415327   \n",
       "4     0.000038   0.000103  265477156    1400191   331.874966   \n",
       "...        ...        ...        ...        ...          ...   \n",
       "6009  0.000029   0.000082  265705659      16732   128.737307   \n",
       "6010  0.000047   0.000124  267790175    1541931   395.267447   \n",
       "6011  0.000086   0.000091  265071405      16482    34.468616   \n",
       "6012  0.000104   0.000105  267533186      16025   184.221788   \n",
       "6013  0.000140   0.000163  264826291      16253   238.083857   \n",
       "\n",
       "                      geometry  \n",
       "0     POINT (4.25943 52.04958)  \n",
       "1     POINT (4.34353 52.05493)  \n",
       "2     POINT (4.28048 52.08479)  \n",
       "3     POINT (4.41517 52.06148)  \n",
       "4     POINT (4.30108 52.03091)  \n",
       "...                        ...  \n",
       "6009  POINT (4.32391 52.04139)  \n",
       "6010  POINT (4.26104 52.07841)  \n",
       "6011  POINT (4.34411 52.05906)  \n",
       "6012  POINT (4.27842 52.06425)  \n",
       "6013  POINT (4.33808 52.08237)  \n",
       "\n",
       "[6014 rows x 13 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf = gpd.read_file(r\"C:\\Users\\bruno\\Documents\\J3Q3-4 BEP\\repositories\\housesincludingdistancecloseststop.shp\")\n",
    "gdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>name</th>\n",
       "      <th>fclass</th>\n",
       "      <th>osm_id</th>\n",
       "      <th>cluster</th>\n",
       "      <th>long</th>\n",
       "      <th>latt</th>\n",
       "      <th>Closest_stop</th>\n",
       "      <th>closest_supermarket</th>\n",
       "      <th>to_closest_PT_stop</th>\n",
       "      <th>to_closest_supermarket_PT</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267999488</td>\n",
       "      <td>0</td>\n",
       "      <td>4.259434</td>\n",
       "      <td>52.049581</td>\n",
       "      <td>652095</td>\n",
       "      <td>159</td>\n",
       "      <td>238.997482</td>\n",
       "      <td>1296.024755</td>\n",
       "      <td>POINT (4.25943 52.04958)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267996968</td>\n",
       "      <td>2649</td>\n",
       "      <td>4.261053</td>\n",
       "      <td>52.050188</td>\n",
       "      <td>652095</td>\n",
       "      <td>159</td>\n",
       "      <td>146.379395</td>\n",
       "      <td>1203.406668</td>\n",
       "      <td>POINT (4.26105 52.05019)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267997481</td>\n",
       "      <td>4284</td>\n",
       "      <td>4.260152</td>\n",
       "      <td>52.050155</td>\n",
       "      <td>652095</td>\n",
       "      <td>159</td>\n",
       "      <td>166.724627</td>\n",
       "      <td>1223.751900</td>\n",
       "      <td>POINT (4.26015 52.05015)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>265070349</td>\n",
       "      <td>1</td>\n",
       "      <td>4.343525</td>\n",
       "      <td>52.054926</td>\n",
       "      <td>480300</td>\n",
       "      <td>14</td>\n",
       "      <td>348.614352</td>\n",
       "      <td>868.088193</td>\n",
       "      <td>POINT (4.34353 52.05493)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>265670500</td>\n",
       "      <td>594</td>\n",
       "      <td>4.341636</td>\n",
       "      <td>52.052332</td>\n",
       "      <td>480300</td>\n",
       "      <td>14</td>\n",
       "      <td>155.825721</td>\n",
       "      <td>675.299562</td>\n",
       "      <td>POINT (4.34164 52.05233)</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6009</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267843259</td>\n",
       "      <td>5384</td>\n",
       "      <td>4.282780</td>\n",
       "      <td>52.048521</td>\n",
       "      <td>15996</td>\n",
       "      <td>29</td>\n",
       "      <td>140.761093</td>\n",
       "      <td>675.200116</td>\n",
       "      <td>POINT (4.28278 52.04852)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6010</th>\n",
       "      <td>Boshoek</td>\n",
       "      <td>building</td>\n",
       "      <td>229070095</td>\n",
       "      <td>5395</td>\n",
       "      <td>4.310450</td>\n",
       "      <td>52.041714</td>\n",
       "      <td>16669</td>\n",
       "      <td>129</td>\n",
       "      <td>542.478848</td>\n",
       "      <td>958.790792</td>\n",
       "      <td>POINT (4.31045 52.04171)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6011</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>265197726</td>\n",
       "      <td>5876</td>\n",
       "      <td>4.308207</td>\n",
       "      <td>52.042528</td>\n",
       "      <td>16669</td>\n",
       "      <td>129</td>\n",
       "      <td>619.134787</td>\n",
       "      <td>1035.446731</td>\n",
       "      <td>POINT (4.30821 52.04253)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6012</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267709178</td>\n",
       "      <td>5754</td>\n",
       "      <td>4.277444</td>\n",
       "      <td>52.078013</td>\n",
       "      <td>229179</td>\n",
       "      <td>194</td>\n",
       "      <td>104.716581</td>\n",
       "      <td>573.933202</td>\n",
       "      <td>POINT (4.27744 52.07801)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6013</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>254189893</td>\n",
       "      <td>5824</td>\n",
       "      <td>4.366376</td>\n",
       "      <td>52.039889</td>\n",
       "      <td>16266</td>\n",
       "      <td>98</td>\n",
       "      <td>183.934210</td>\n",
       "      <td>1465.175170</td>\n",
       "      <td>POINT (4.36638 52.03989)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6014 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         name    fclass     osm_id  cluster      long       latt Closest_stop  \\\n",
       "0        None  building  267999488        0  4.259434  52.049581       652095   \n",
       "1        None  building  267996968     2649  4.261053  52.050188       652095   \n",
       "2        None  building  267997481     4284  4.260152  52.050155       652095   \n",
       "3        None  building  265070349        1  4.343525  52.054926       480300   \n",
       "4        None  building  265670500      594  4.341636  52.052332       480300   \n",
       "...       ...       ...        ...      ...       ...        ...          ...   \n",
       "6009     None  building  267843259     5384  4.282780  52.048521        15996   \n",
       "6010  Boshoek  building  229070095     5395  4.310450  52.041714        16669   \n",
       "6011     None  building  265197726     5876  4.308207  52.042528        16669   \n",
       "6012     None  building  267709178     5754  4.277444  52.078013       229179   \n",
       "6013     None  building  254189893     5824  4.366376  52.039889        16266   \n",
       "\n",
       "      closest_supermarket  to_closest_PT_stop  to_closest_supermarket_PT  \\\n",
       "0                     159          238.997482                1296.024755   \n",
       "1                     159          146.379395                1203.406668   \n",
       "2                     159          166.724627                1223.751900   \n",
       "3                      14          348.614352                 868.088193   \n",
       "4                      14          155.825721                 675.299562   \n",
       "...                   ...                 ...                        ...   \n",
       "6009                   29          140.761093                 675.200116   \n",
       "6010                  129          542.478848                 958.790792   \n",
       "6011                  129          619.134787                1035.446731   \n",
       "6012                  194          104.716581                 573.933202   \n",
       "6013                   98          183.934210                1465.175170   \n",
       "\n",
       "                      geometry  \n",
       "0     POINT (4.25943 52.04958)  \n",
       "1     POINT (4.26105 52.05019)  \n",
       "2     POINT (4.26015 52.05015)  \n",
       "3     POINT (4.34353 52.05493)  \n",
       "4     POINT (4.34164 52.05233)  \n",
       "...                        ...  \n",
       "6009  POINT (4.28278 52.04852)  \n",
       "6010  POINT (4.31045 52.04171)  \n",
       "6011  POINT (4.30821 52.04253)  \n",
       "6012  POINT (4.27744 52.07801)  \n",
       "6013  POINT (4.36638 52.03989)  \n",
       "\n",
       "[6014 rows x 11 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged1 = gdf.merge(distances, left_on = 'Destinatio', right_on = 'origin')\n",
    "merged1['to_closest_supermarket_PT'] = merged1['distance_to_supermarket'] + merged1['Distance_t']\n",
    "merged1 = merged1.drop(['longdiff', 'Lattdiff', 'Euclidian', 'origin', 'Origin', 'distance_to_supermarket'], axis = 1)\n",
    "merged1 = merged1.rename(columns = {'Distance_t':'to_closest_PT_stop','Destinatio': 'Closest_stop', 'supermarketid': 'closest_supermarket'})\n",
    "merged1 = merged1[['name', 'fclass', 'osm_id', 'cluster', 'long', 'latt', 'Closest_stop', 'closest_supermarket', 'to_closest_PT_stop', 'to_closest_supermarket_PT','geometry']]\n",
    "merged1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged1.to_file('Houses_13-05-2020.shp')"
   ]
  },
  {
   "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>name</th>\n",
       "      <th>fclass</th>\n",
       "      <th>osm_id</th>\n",
       "      <th>cluster</th>\n",
       "      <th>long</th>\n",
       "      <th>latt</th>\n",
       "      <th>Closest_stop</th>\n",
       "      <th>closest_supermarket</th>\n",
       "      <th>to_closest_PT_stop</th>\n",
       "      <th>to_closest_supermarket_PT</th>\n",
       "      <th>geometry</th>\n",
       "      <th>cluster_test_accessibility</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267999488</td>\n",
       "      <td>0</td>\n",
       "      <td>4.259434</td>\n",
       "      <td>52.049581</td>\n",
       "      <td>652095</td>\n",
       "      <td>159</td>\n",
       "      <td>238.997482</td>\n",
       "      <td>1296.024755</td>\n",
       "      <td>POINT (4.25943 52.04958)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267996968</td>\n",
       "      <td>2649</td>\n",
       "      <td>4.261053</td>\n",
       "      <td>52.050188</td>\n",
       "      <td>652095</td>\n",
       "      <td>159</td>\n",
       "      <td>146.379395</td>\n",
       "      <td>1203.406668</td>\n",
       "      <td>POINT (4.26105 52.05019)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267997481</td>\n",
       "      <td>4284</td>\n",
       "      <td>4.260152</td>\n",
       "      <td>52.050155</td>\n",
       "      <td>652095</td>\n",
       "      <td>159</td>\n",
       "      <td>166.724627</td>\n",
       "      <td>1223.751900</td>\n",
       "      <td>POINT (4.26015 52.05015)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>265070349</td>\n",
       "      <td>1</td>\n",
       "      <td>4.343525</td>\n",
       "      <td>52.054926</td>\n",
       "      <td>480300</td>\n",
       "      <td>14</td>\n",
       "      <td>348.614352</td>\n",
       "      <td>868.088193</td>\n",
       "      <td>POINT (4.34353 52.05493)</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>265670500</td>\n",
       "      <td>594</td>\n",
       "      <td>4.341636</td>\n",
       "      <td>52.052332</td>\n",
       "      <td>480300</td>\n",
       "      <td>14</td>\n",
       "      <td>155.825721</td>\n",
       "      <td>675.299562</td>\n",
       "      <td>POINT (4.34164 52.05233)</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6009</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267843259</td>\n",
       "      <td>5384</td>\n",
       "      <td>4.282780</td>\n",
       "      <td>52.048521</td>\n",
       "      <td>15996</td>\n",
       "      <td>29</td>\n",
       "      <td>140.761093</td>\n",
       "      <td>675.200116</td>\n",
       "      <td>POINT (4.28278 52.04852)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6010</th>\n",
       "      <td>Boshoek</td>\n",
       "      <td>building</td>\n",
       "      <td>229070095</td>\n",
       "      <td>5395</td>\n",
       "      <td>4.310450</td>\n",
       "      <td>52.041714</td>\n",
       "      <td>16669</td>\n",
       "      <td>129</td>\n",
       "      <td>542.478848</td>\n",
       "      <td>958.790792</td>\n",
       "      <td>POINT (4.31045 52.04171)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6011</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>265197726</td>\n",
       "      <td>5876</td>\n",
       "      <td>4.308207</td>\n",
       "      <td>52.042528</td>\n",
       "      <td>16669</td>\n",
       "      <td>129</td>\n",
       "      <td>619.134787</td>\n",
       "      <td>1035.446731</td>\n",
       "      <td>POINT (4.30821 52.04253)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6012</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>267709178</td>\n",
       "      <td>5754</td>\n",
       "      <td>4.277444</td>\n",
       "      <td>52.078013</td>\n",
       "      <td>229179</td>\n",
       "      <td>194</td>\n",
       "      <td>104.716581</td>\n",
       "      <td>573.933202</td>\n",
       "      <td>POINT (4.27744 52.07801)</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6013</th>\n",
       "      <td>None</td>\n",
       "      <td>building</td>\n",
       "      <td>254189893</td>\n",
       "      <td>5824</td>\n",
       "      <td>4.366376</td>\n",
       "      <td>52.039889</td>\n",
       "      <td>16266</td>\n",
       "      <td>98</td>\n",
       "      <td>183.934210</td>\n",
       "      <td>1465.175170</td>\n",
       "      <td>POINT (4.36638 52.03989)</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6014 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         name    fclass     osm_id  cluster      long       latt Closest_stop  \\\n",
       "0        None  building  267999488        0  4.259434  52.049581       652095   \n",
       "1        None  building  267996968     2649  4.261053  52.050188       652095   \n",
       "2        None  building  267997481     4284  4.260152  52.050155       652095   \n",
       "3        None  building  265070349        1  4.343525  52.054926       480300   \n",
       "4        None  building  265670500      594  4.341636  52.052332       480300   \n",
       "...       ...       ...        ...      ...       ...        ...          ...   \n",
       "6009     None  building  267843259     5384  4.282780  52.048521        15996   \n",
       "6010  Boshoek  building  229070095     5395  4.310450  52.041714        16669   \n",
       "6011     None  building  265197726     5876  4.308207  52.042528        16669   \n",
       "6012     None  building  267709178     5754  4.277444  52.078013       229179   \n",
       "6013     None  building  254189893     5824  4.366376  52.039889        16266   \n",
       "\n",
       "      closest_supermarket  to_closest_PT_stop  to_closest_supermarket_PT  \\\n",
       "0                     159          238.997482                1296.024755   \n",
       "1                     159          146.379395                1203.406668   \n",
       "2                     159          166.724627                1223.751900   \n",
       "3                      14          348.614352                 868.088193   \n",
       "4                      14          155.825721                 675.299562   \n",
       "...                   ...                 ...                        ...   \n",
       "6009                   29          140.761093                 675.200116   \n",
       "6010                  129          542.478848                 958.790792   \n",
       "6011                  129          619.134787                1035.446731   \n",
       "6012                  194          104.716581                 573.933202   \n",
       "6013                   98          183.934210                1465.175170   \n",
       "\n",
       "                      geometry  cluster_test_accessibility  \n",
       "0     POINT (4.25943 52.04958)                           4  \n",
       "1     POINT (4.26105 52.05019)                           4  \n",
       "2     POINT (4.26015 52.05015)                           4  \n",
       "3     POINT (4.34353 52.05493)                           6  \n",
       "4     POINT (4.34164 52.05233)                           1  \n",
       "...                        ...                         ...  \n",
       "6009  POINT (4.28278 52.04852)                           1  \n",
       "6010  POINT (4.31045 52.04171)                           4  \n",
       "6011  POINT (4.30821 52.04253)                           4  \n",
       "6012  POINT (4.27744 52.07801)                           1  \n",
       "6013  POINT (4.36638 52.03989)                           0  \n",
       "\n",
       "[6014 rows x 12 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X = merged1[['to_closest_PT_stop','to_closest_supermarket_PT']]\n",
    "kmeans = KMeans(n_clusters=10, random_state=0).fit(X)\n",
    "a = kmeans.labels_\n",
    "merged1['cluster_test_accessibility'] = a\n",
    "merged1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "merged1.to_file('test.shp')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[  398.5514997 ,  1430.18139553],\n",
       "       [  163.9689434 ,   616.73702307],\n",
       "       [ 1194.44499612,  2418.69431464],\n",
       "       [ 9365.75216418, 10477.11695938],\n",
       "       [  319.85631806,  1119.02210427],\n",
       "       [  715.88710478,  1761.11820333],\n",
       "       [  229.67999416,   870.07179821],\n",
       "       [ 5538.7034242 ,  6650.0682194 ],\n",
       "       [12264.89260515, 13376.25740035],\n",
       "       [ 1705.89519784,  3004.81959961]])"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kmeans.cluster_centers_"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}