Get-common-times-places-v6-cleaned2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intro\n",
    "\n",
    "This project takes 2 location history files and outputs 2 things:\n",
    "\n",
    "1. For each day how many time the people spent together in seconds\n",
    "2. The observations with timestamp and location for which there is a *match* between two people.\n",
    "    * A match is defined as having a corresponding timestamp between two people, with two locations which are not too far from each other."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import geopy.distance\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option(\"display.precision\", 9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create example to run everything on"
   ]
  },
  {
   "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>timestampMs</th>\n",
       "      <th>latitudeE7</th>\n",
       "      <th>longitudeE7</th>\n",
       "      <th>accuracy</th>\n",
       "      <th>altitude</th>\n",
       "      <th>verticalAccuracy</th>\n",
       "      <th>velocity</th>\n",
       "      <th>heading</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1564621610402</td>\n",
       "      <td>508619179</td>\n",
       "      <td>43359986</td>\n",
       "      <td>14</td>\n",
       "      <td>79.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1564623771349</td>\n",
       "      <td>508619471</td>\n",
       "      <td>43359965</td>\n",
       "      <td>15</td>\n",
       "      <td>75.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1564624761191</td>\n",
       "      <td>508619535</td>\n",
       "      <td>43359980</td>\n",
       "      <td>16</td>\n",
       "      <td>79.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1564625362345</td>\n",
       "      <td>508619469</td>\n",
       "      <td>43360118</td>\n",
       "      <td>14</td>\n",
       "      <td>79.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1564627523336</td>\n",
       "      <td>508619358</td>\n",
       "      <td>43359998</td>\n",
       "      <td>15</td>\n",
       "      <td>79.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38307</th>\n",
       "      <td>1572649082673</td>\n",
       "      <td>46005019</td>\n",
       "      <td>-740691845</td>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38308</th>\n",
       "      <td>1572649202734</td>\n",
       "      <td>46005019</td>\n",
       "      <td>-740691845</td>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38309</th>\n",
       "      <td>1572649322751</td>\n",
       "      <td>46005019</td>\n",
       "      <td>-740691845</td>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38310</th>\n",
       "      <td>1572649478731</td>\n",
       "      <td>46005019</td>\n",
       "      <td>-740691845</td>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38311</th>\n",
       "      <td>1572649605066</td>\n",
       "      <td>46005019</td>\n",
       "      <td>-740691845</td>\n",
       "      <td>50</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>38312 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         timestampMs  latitudeE7  longitudeE7  accuracy  altitude  \\\n",
       "0      1564621610402   508619179     43359986        14      79.0   \n",
       "1      1564623771349   508619471     43359965        15      75.0   \n",
       "2      1564624761191   508619535     43359980        16      79.0   \n",
       "3      1564625362345   508619469     43360118        14      79.0   \n",
       "4      1564627523336   508619358     43359998        15      79.0   \n",
       "...              ...         ...          ...       ...       ...   \n",
       "38307  1572649082673    46005019   -740691845        50       NaN   \n",
       "38308  1572649202734    46005019   -740691845        50       NaN   \n",
       "38309  1572649322751    46005019   -740691845        50       NaN   \n",
       "38310  1572649478731    46005019   -740691845        50       NaN   \n",
       "38311  1572649605066    46005019   -740691845        50       NaN   \n",
       "\n",
       "       verticalAccuracy  velocity  heading  \n",
       "0                   3.0       NaN      NaN  \n",
       "1                   3.0       NaN      NaN  \n",
       "2                   3.0       NaN      NaN  \n",
       "3                   3.0       NaN      NaN  \n",
       "4                   3.0       NaN      NaN  \n",
       "...                 ...       ...      ...  \n",
       "38307               NaN       NaN      NaN  \n",
       "38308               NaN       NaN      NaN  \n",
       "38309               NaN       NaN      NaN  \n",
       "38310               NaN       NaN      NaN  \n",
       "38311               NaN       NaN      NaN  \n",
       "\n",
       "[38312 rows x 8 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('Klaas-filtered.json') as json_file:\n",
    "    df1 = json.load(json_file)\n",
    "df1 = pd.DataFrame(df1)\n",
    "df1 = df1.drop(\"activity\", 1)\n",
    "df1 = df1[df1['timestampMs'] < '1572650160000']\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></th>\n",
       "      <th>timestampMs</th>\n",
       "      <th>latitudeE7</th>\n",
       "      <th>longitudeE7</th>\n",
       "      <th>accuracy</th>\n",
       "      <th>altitude</th>\n",
       "      <th>verticalAccuracy</th>\n",
       "      <th>velocity</th>\n",
       "      <th>heading</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1564637041886</td>\n",
       "      <td>510350641</td>\n",
       "      <td>37339686</td>\n",
       "      <td>65</td>\n",
       "      <td>2.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1564640697015</td>\n",
       "      <td>510341940</td>\n",
       "      <td>37362204</td>\n",
       "      <td>24</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1564640875904</td>\n",
       "      <td>510322913</td>\n",
       "      <td>37339917</td>\n",
       "      <td>16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1564641018721</td>\n",
       "      <td>510325417</td>\n",
       "      <td>37342550</td>\n",
       "      <td>65</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1564641267355</td>\n",
       "      <td>510300225</td>\n",
       "      <td>37378699</td>\n",
       "      <td>65</td>\n",
       "      <td>4.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3362</th>\n",
       "      <td>1571585366999</td>\n",
       "      <td>489340639</td>\n",
       "      <td>23920436</td>\n",
       "      <td>32</td>\n",
       "      <td>20.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3363</th>\n",
       "      <td>1571585446999</td>\n",
       "      <td>489346679</td>\n",
       "      <td>23989600</td>\n",
       "      <td>32</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3364</th>\n",
       "      <td>1571585526998</td>\n",
       "      <td>489370248</td>\n",
       "      <td>24083527</td>\n",
       "      <td>32</td>\n",
       "      <td>24.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3365</th>\n",
       "      <td>1571585606998</td>\n",
       "      <td>489390858</td>\n",
       "      <td>24167024</td>\n",
       "      <td>32</td>\n",
       "      <td>38.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3366</th>\n",
       "      <td>1571585676998</td>\n",
       "      <td>489415984</td>\n",
       "      <td>24297702</td>\n",
       "      <td>32</td>\n",
       "      <td>34.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>74.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3367 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        timestampMs  latitudeE7  longitudeE7  accuracy  altitude  \\\n",
       "0     1564637041886   510350641     37339686        65       2.0   \n",
       "1     1564640697015   510341940     37362204        24       2.0   \n",
       "2     1564640875904   510322913     37339917        16       0.0   \n",
       "3     1564641018721   510325417     37342550        65       3.0   \n",
       "4     1564641267355   510300225     37378699        65       4.0   \n",
       "...             ...         ...          ...       ...       ...   \n",
       "3362  1571585366999   489340639     23920436        32      20.0   \n",
       "3363  1571585446999   489346679     23989600        32      -5.0   \n",
       "3364  1571585526998   489370248     24083527        32      24.0   \n",
       "3365  1571585606998   489390858     24167024        32      38.0   \n",
       "3366  1571585676998   489415984     24297702        32      34.0   \n",
       "\n",
       "      verticalAccuracy  velocity  heading  \n",
       "0                 10.0       NaN      NaN  \n",
       "1                  3.0       1.0      NaN  \n",
       "2                  3.0       1.0      NaN  \n",
       "3                 10.0       NaN      NaN  \n",
       "4                 10.0       NaN      NaN  \n",
       "...                ...       ...      ...  \n",
       "3362              12.0      11.0     89.0  \n",
       "3363              16.0       7.0     76.0  \n",
       "3364              15.0       9.0     71.0  \n",
       "3365              36.0       8.0     68.0  \n",
       "3366              19.0      21.0     74.0  \n",
       "\n",
       "[3367 rows x 8 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open('Thomas-filtered.json') as json_file:\n",
    "    df2 = json.load(json_file)\n",
    "df2 = pd.DataFrame(df2)\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Convert ts col to readable format**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1['timestampMs'] = pd.to_datetime(df1['timestampMs'], unit = 'ms')\n",
    "df1.rename(columns = {'timestampMs':'timestamp'}, inplace = True)\n",
    "\n",
    "df2['timestampMs'] = pd.to_datetime(df2['timestampMs'], unit = 'ms')\n",
    "df2.rename(columns = {'timestampMs':'timestamp'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# lat en long\n",
    "df1[\"latitudeE7\"] = df1[\"latitudeE7\"].div(10**7).round(9)\n",
    "df1[\"longitudeE7\"] = df1[\"longitudeE7\"].div(10**7).round(9)\n",
    "\n",
    "df1.rename(columns = {'latitudeE7':'latitude',\n",
    "                       'longitudeE7':'longitude'}, inplace = True)\n",
    "\n",
    "df2[\"latitudeE7\"] = df2[\"latitudeE7\"].div(10**7).round(9)\n",
    "df2[\"longitudeE7\"] = df2[\"longitudeE7\"].div(10**7).round(9)\n",
    "\n",
    "df2.rename(columns = {'latitudeE7':'latitude',\n",
    "                       'longitudeE7':'longitude'}, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop other columns\n",
    "df1 = df1.drop(columns = ['accuracy', 'altitude', 'verticalAccuracy', 'velocity', 'heading'])\n",
    "df2 = df2.drop(columns = ['accuracy', 'altitude', 'verticalAccuracy', 'velocity', 'heading'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run the code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>timestamp</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-08-01 01:06:50.402</td>\n",
       "      <td>50.8619179</td>\n",
       "      <td>4.3359986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08-01 01:42:51.349</td>\n",
       "      <td>50.8619471</td>\n",
       "      <td>4.3359965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-08-01 01:59:21.191</td>\n",
       "      <td>50.8619535</td>\n",
       "      <td>4.3359980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-08-01 02:09:22.345</td>\n",
       "      <td>50.8619469</td>\n",
       "      <td>4.3360118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-08-01 02:45:23.336</td>\n",
       "      <td>50.8619358</td>\n",
       "      <td>4.3359998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38307</th>\n",
       "      <td>2019-11-01 22:58:02.673</td>\n",
       "      <td>4.6005019</td>\n",
       "      <td>-74.0691845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38308</th>\n",
       "      <td>2019-11-01 23:00:02.734</td>\n",
       "      <td>4.6005019</td>\n",
       "      <td>-74.0691845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38309</th>\n",
       "      <td>2019-11-01 23:02:02.751</td>\n",
       "      <td>4.6005019</td>\n",
       "      <td>-74.0691845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38310</th>\n",
       "      <td>2019-11-01 23:04:38.731</td>\n",
       "      <td>4.6005019</td>\n",
       "      <td>-74.0691845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38311</th>\n",
       "      <td>2019-11-01 23:06:45.066</td>\n",
       "      <td>4.6005019</td>\n",
       "      <td>-74.0691845</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>38312 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    timestamp    latitude   longitude\n",
       "0     2019-08-01 01:06:50.402  50.8619179   4.3359986\n",
       "1     2019-08-01 01:42:51.349  50.8619471   4.3359965\n",
       "2     2019-08-01 01:59:21.191  50.8619535   4.3359980\n",
       "3     2019-08-01 02:09:22.345  50.8619469   4.3360118\n",
       "4     2019-08-01 02:45:23.336  50.8619358   4.3359998\n",
       "...                       ...         ...         ...\n",
       "38307 2019-11-01 22:58:02.673   4.6005019 -74.0691845\n",
       "38308 2019-11-01 23:00:02.734   4.6005019 -74.0691845\n",
       "38309 2019-11-01 23:02:02.751   4.6005019 -74.0691845\n",
       "38310 2019-11-01 23:04:38.731   4.6005019 -74.0691845\n",
       "38311 2019-11-01 23:06:45.066   4.6005019 -74.0691845\n",
       "\n",
       "[38312 rows x 3 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "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>timestamp</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08-01 06:24:57.015</td>\n",
       "      <td>51.0341940</td>\n",
       "      <td>3.7362204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-08-01 06:27:55.904</td>\n",
       "      <td>51.0322913</td>\n",
       "      <td>3.7339917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-08-01 06:30:18.721</td>\n",
       "      <td>51.0325417</td>\n",
       "      <td>3.7342550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-08-01 06:34:27.355</td>\n",
       "      <td>51.0300225</td>\n",
       "      <td>3.7378699</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3362</th>\n",
       "      <td>2019-10-20 15:29:26.999</td>\n",
       "      <td>48.9340639</td>\n",
       "      <td>2.3920436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3363</th>\n",
       "      <td>2019-10-20 15:30:46.999</td>\n",
       "      <td>48.9346679</td>\n",
       "      <td>2.3989600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3364</th>\n",
       "      <td>2019-10-20 15:32:06.998</td>\n",
       "      <td>48.9370248</td>\n",
       "      <td>2.4083527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3365</th>\n",
       "      <td>2019-10-20 15:33:26.998</td>\n",
       "      <td>48.9390858</td>\n",
       "      <td>2.4167024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3366</th>\n",
       "      <td>2019-10-20 15:34:36.998</td>\n",
       "      <td>48.9415984</td>\n",
       "      <td>2.4297702</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3367 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   timestamp    latitude  longitude\n",
       "0    2019-08-01 05:24:01.886  51.0350641  3.7339686\n",
       "1    2019-08-01 06:24:57.015  51.0341940  3.7362204\n",
       "2    2019-08-01 06:27:55.904  51.0322913  3.7339917\n",
       "3    2019-08-01 06:30:18.721  51.0325417  3.7342550\n",
       "4    2019-08-01 06:34:27.355  51.0300225  3.7378699\n",
       "...                      ...         ...        ...\n",
       "3362 2019-10-20 15:29:26.999  48.9340639  2.3920436\n",
       "3363 2019-10-20 15:30:46.999  48.9346679  2.3989600\n",
       "3364 2019-10-20 15:32:06.998  48.9370248  2.4083527\n",
       "3365 2019-10-20 15:33:26.998  48.9390858  2.4167024\n",
       "3366 2019-10-20 15:34:36.998  48.9415984  2.4297702\n",
       "\n",
       "[3367 rows x 3 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# do full merge of two df's\n",
    "# every ts of df1 should be as many times duplicated for every row of df2\n",
    "\n",
    "# merging on new key to get full cartesian product\n",
    "# https://stackoverflow.com/questions/13269890/cartesian-product-in-pandas\n",
    "# create new key column in each df\n",
    "\n",
    "df1['key'] = 1\n",
    "df2['key'] = 1\n",
    "\n",
    "result = pd.merge(df1, df2, on = 'key')\n",
    "# result = pd.merge(df1[['key', 'timestamp']], df2[['key', 'timestamp']], on = 'key')\n",
    "\n",
    "result = result.drop(columns='key')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>timestamp_x</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>timestamp_y</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-08-01 01:06:50.402</td>\n",
       "      <td>50.8619179</td>\n",
       "      <td>4.3359986</td>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08-01 01:06:50.402</td>\n",
       "      <td>50.8619179</td>\n",
       "      <td>4.3359986</td>\n",
       "      <td>2019-08-01 06:24:57.015</td>\n",
       "      <td>51.0341940</td>\n",
       "      <td>3.7362204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-08-01 01:06:50.402</td>\n",
       "      <td>50.8619179</td>\n",
       "      <td>4.3359986</td>\n",
       "      <td>2019-08-01 06:27:55.904</td>\n",
       "      <td>51.0322913</td>\n",
       "      <td>3.7339917</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-08-01 01:06:50.402</td>\n",
       "      <td>50.8619179</td>\n",
       "      <td>4.3359986</td>\n",
       "      <td>2019-08-01 06:30:18.721</td>\n",
       "      <td>51.0325417</td>\n",
       "      <td>3.7342550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-08-01 01:06:50.402</td>\n",
       "      <td>50.8619179</td>\n",
       "      <td>4.3359986</td>\n",
       "      <td>2019-08-01 06:34:27.355</td>\n",
       "      <td>51.0300225</td>\n",
       "      <td>3.7378699</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>128996499</th>\n",
       "      <td>2019-11-01 23:06:45.066</td>\n",
       "      <td>4.6005019</td>\n",
       "      <td>-74.0691845</td>\n",
       "      <td>2019-10-20 15:29:26.999</td>\n",
       "      <td>48.9340639</td>\n",
       "      <td>2.3920436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128996500</th>\n",
       "      <td>2019-11-01 23:06:45.066</td>\n",
       "      <td>4.6005019</td>\n",
       "      <td>-74.0691845</td>\n",
       "      <td>2019-10-20 15:30:46.999</td>\n",
       "      <td>48.9346679</td>\n",
       "      <td>2.3989600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128996501</th>\n",
       "      <td>2019-11-01 23:06:45.066</td>\n",
       "      <td>4.6005019</td>\n",
       "      <td>-74.0691845</td>\n",
       "      <td>2019-10-20 15:32:06.998</td>\n",
       "      <td>48.9370248</td>\n",
       "      <td>2.4083527</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128996502</th>\n",
       "      <td>2019-11-01 23:06:45.066</td>\n",
       "      <td>4.6005019</td>\n",
       "      <td>-74.0691845</td>\n",
       "      <td>2019-10-20 15:33:26.998</td>\n",
       "      <td>48.9390858</td>\n",
       "      <td>2.4167024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128996503</th>\n",
       "      <td>2019-11-01 23:06:45.066</td>\n",
       "      <td>4.6005019</td>\n",
       "      <td>-74.0691845</td>\n",
       "      <td>2019-10-20 15:34:36.998</td>\n",
       "      <td>48.9415984</td>\n",
       "      <td>2.4297702</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>128996504 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      timestamp_x  latitude_x  longitude_x  \\\n",
       "0         2019-08-01 01:06:50.402  50.8619179    4.3359986   \n",
       "1         2019-08-01 01:06:50.402  50.8619179    4.3359986   \n",
       "2         2019-08-01 01:06:50.402  50.8619179    4.3359986   \n",
       "3         2019-08-01 01:06:50.402  50.8619179    4.3359986   \n",
       "4         2019-08-01 01:06:50.402  50.8619179    4.3359986   \n",
       "...                           ...         ...          ...   \n",
       "128996499 2019-11-01 23:06:45.066   4.6005019  -74.0691845   \n",
       "128996500 2019-11-01 23:06:45.066   4.6005019  -74.0691845   \n",
       "128996501 2019-11-01 23:06:45.066   4.6005019  -74.0691845   \n",
       "128996502 2019-11-01 23:06:45.066   4.6005019  -74.0691845   \n",
       "128996503 2019-11-01 23:06:45.066   4.6005019  -74.0691845   \n",
       "\n",
       "                      timestamp_y  latitude_y  longitude_y  \n",
       "0         2019-08-01 05:24:01.886  51.0350641    3.7339686  \n",
       "1         2019-08-01 06:24:57.015  51.0341940    3.7362204  \n",
       "2         2019-08-01 06:27:55.904  51.0322913    3.7339917  \n",
       "3         2019-08-01 06:30:18.721  51.0325417    3.7342550  \n",
       "4         2019-08-01 06:34:27.355  51.0300225    3.7378699  \n",
       "...                           ...         ...          ...  \n",
       "128996499 2019-10-20 15:29:26.999  48.9340639    2.3920436  \n",
       "128996500 2019-10-20 15:30:46.999  48.9346679    2.3989600  \n",
       "128996501 2019-10-20 15:32:06.998  48.9370248    2.4083527  \n",
       "128996502 2019-10-20 15:33:26.998  48.9390858    2.4167024  \n",
       "128996503 2019-10-20 15:34:36.998  48.9415984    2.4297702  \n",
       "\n",
       "[128996504 rows x 6 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_time = 10 \n",
    "max_time = pd.to_timedelta(max_time, unit = 'minutes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# filter combined df on upper and lower bound\n",
    "result = result.loc[(result['timestamp_x'] <= result['timestamp_y'] + max_time) & (result['timestamp_x'] >= result['timestamp_y'] - max_time)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'result' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-1-3655ae69460e>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[1;32mif\u001b[0m \u001b[0mresult\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mempty\u001b[0m \u001b[1;33m==\u001b[0m \u001b[1;32mTrue\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      2\u001b[0m     \u001b[1;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'There are no overlapping matches based on GPS traces. \\nTHIS IS SAD'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'result' is not defined"
     ]
    }
   ],
   "source": [
    "if result.empty == True:\n",
    "    raise ValueError('There are no overlapping matches based on GPS traces. \\nTHIS IS SAD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>timestamp_x</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>timestamp_y</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>60606</th>\n",
       "      <td>2019-08-01 05:20:28.518</td>\n",
       "      <td>50.8619474</td>\n",
       "      <td>4.3359985</td>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63973</th>\n",
       "      <td>2019-08-01 05:26:31.305</td>\n",
       "      <td>50.8619292</td>\n",
       "      <td>4.3359912</td>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67340</th>\n",
       "      <td>2019-08-01 05:32:32.325</td>\n",
       "      <td>50.8619361</td>\n",
       "      <td>4.3359995</td>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111112</th>\n",
       "      <td>2019-08-01 06:15:50.628</td>\n",
       "      <td>50.8618882</td>\n",
       "      <td>4.3359122</td>\n",
       "      <td>2019-08-01 06:24:57.015</td>\n",
       "      <td>51.0341940</td>\n",
       "      <td>3.7362204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114479</th>\n",
       "      <td>2019-08-01 06:20:50.721</td>\n",
       "      <td>50.8618793</td>\n",
       "      <td>4.3359543</td>\n",
       "      <td>2019-08-01 06:24:57.015</td>\n",
       "      <td>51.0341940</td>\n",
       "      <td>3.7362204</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>106060204</th>\n",
       "      <td>2019-10-16 16:28:38.010</td>\n",
       "      <td>51.0173066</td>\n",
       "      <td>4.4776110</td>\n",
       "      <td>2019-10-16 16:21:48.999</td>\n",
       "      <td>51.0351943</td>\n",
       "      <td>3.7357462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106060205</th>\n",
       "      <td>2019-10-16 16:28:38.010</td>\n",
       "      <td>51.0173066</td>\n",
       "      <td>4.4776110</td>\n",
       "      <td>2019-10-16 16:23:57.803</td>\n",
       "      <td>51.0351859</td>\n",
       "      <td>3.7342722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106063571</th>\n",
       "      <td>2019-10-16 16:30:39.111</td>\n",
       "      <td>51.0173070</td>\n",
       "      <td>4.4776119</td>\n",
       "      <td>2019-10-16 16:21:48.999</td>\n",
       "      <td>51.0351943</td>\n",
       "      <td>3.7357462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106063572</th>\n",
       "      <td>2019-10-16 16:30:39.111</td>\n",
       "      <td>51.0173070</td>\n",
       "      <td>4.4776119</td>\n",
       "      <td>2019-10-16 16:23:57.803</td>\n",
       "      <td>51.0351859</td>\n",
       "      <td>3.7342722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106066939</th>\n",
       "      <td>2019-10-16 16:32:41.053</td>\n",
       "      <td>51.0173161</td>\n",
       "      <td>4.4776206</td>\n",
       "      <td>2019-10-16 16:23:57.803</td>\n",
       "      <td>51.0351859</td>\n",
       "      <td>3.7342722</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25671 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      timestamp_x  latitude_x  longitude_x  \\\n",
       "60606     2019-08-01 05:20:28.518  50.8619474    4.3359985   \n",
       "63973     2019-08-01 05:26:31.305  50.8619292    4.3359912   \n",
       "67340     2019-08-01 05:32:32.325  50.8619361    4.3359995   \n",
       "111112    2019-08-01 06:15:50.628  50.8618882    4.3359122   \n",
       "114479    2019-08-01 06:20:50.721  50.8618793    4.3359543   \n",
       "...                           ...         ...          ...   \n",
       "106060204 2019-10-16 16:28:38.010  51.0173066    4.4776110   \n",
       "106060205 2019-10-16 16:28:38.010  51.0173066    4.4776110   \n",
       "106063571 2019-10-16 16:30:39.111  51.0173070    4.4776119   \n",
       "106063572 2019-10-16 16:30:39.111  51.0173070    4.4776119   \n",
       "106066939 2019-10-16 16:32:41.053  51.0173161    4.4776206   \n",
       "\n",
       "                      timestamp_y  latitude_y  longitude_y  \n",
       "60606     2019-08-01 05:24:01.886  51.0350641    3.7339686  \n",
       "63973     2019-08-01 05:24:01.886  51.0350641    3.7339686  \n",
       "67340     2019-08-01 05:24:01.886  51.0350641    3.7339686  \n",
       "111112    2019-08-01 06:24:57.015  51.0341940    3.7362204  \n",
       "114479    2019-08-01 06:24:57.015  51.0341940    3.7362204  \n",
       "...                           ...         ...          ...  \n",
       "106060204 2019-10-16 16:21:48.999  51.0351943    3.7357462  \n",
       "106060205 2019-10-16 16:23:57.803  51.0351859    3.7342722  \n",
       "106063571 2019-10-16 16:21:48.999  51.0351943    3.7357462  \n",
       "106063572 2019-10-16 16:23:57.803  51.0351859    3.7342722  \n",
       "106066939 2019-10-16 16:23:57.803  51.0351859    3.7342722  \n",
       "\n",
       "[25671 rows x 6 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-26-a135545f0540>:5: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  result[new_colname] = result.groupby('timestamp_x')['timestamp_x'].transform('count')\n"
     ]
    }
   ],
   "source": [
    "# count how many matches in interval between df1 and df2 (groupbyby ts of df1)\n",
    "# for each timestamp of df1, how many rows are there\n",
    "new_colname = 'count_overlaps_in_int'\n",
    "\n",
    "result[new_colname] = result.groupby('timestamp_x')['timestamp_x'].transform('count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>timestamp_x</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>timestamp_y</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "      <th>count_overlaps_in_int</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>60606</th>\n",
       "      <td>2019-08-01 05:20:28.518</td>\n",
       "      <td>50.8619474</td>\n",
       "      <td>4.3359985</td>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63973</th>\n",
       "      <td>2019-08-01 05:26:31.305</td>\n",
       "      <td>50.8619292</td>\n",
       "      <td>4.3359912</td>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67340</th>\n",
       "      <td>2019-08-01 05:32:32.325</td>\n",
       "      <td>50.8619361</td>\n",
       "      <td>4.3359995</td>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111112</th>\n",
       "      <td>2019-08-01 06:15:50.628</td>\n",
       "      <td>50.8618882</td>\n",
       "      <td>4.3359122</td>\n",
       "      <td>2019-08-01 06:24:57.015</td>\n",
       "      <td>51.0341940</td>\n",
       "      <td>3.7362204</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114479</th>\n",
       "      <td>2019-08-01 06:20:50.721</td>\n",
       "      <td>50.8618793</td>\n",
       "      <td>4.3359543</td>\n",
       "      <td>2019-08-01 06:24:57.015</td>\n",
       "      <td>51.0341940</td>\n",
       "      <td>3.7362204</td>\n",
       "      <td>3</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106060204</th>\n",
       "      <td>2019-10-16 16:28:38.010</td>\n",
       "      <td>51.0173066</td>\n",
       "      <td>4.4776110</td>\n",
       "      <td>2019-10-16 16:21:48.999</td>\n",
       "      <td>51.0351943</td>\n",
       "      <td>3.7357462</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106060205</th>\n",
       "      <td>2019-10-16 16:28:38.010</td>\n",
       "      <td>51.0173066</td>\n",
       "      <td>4.4776110</td>\n",
       "      <td>2019-10-16 16:23:57.803</td>\n",
       "      <td>51.0351859</td>\n",
       "      <td>3.7342722</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106063571</th>\n",
       "      <td>2019-10-16 16:30:39.111</td>\n",
       "      <td>51.0173070</td>\n",
       "      <td>4.4776119</td>\n",
       "      <td>2019-10-16 16:21:48.999</td>\n",
       "      <td>51.0351943</td>\n",
       "      <td>3.7357462</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106063572</th>\n",
       "      <td>2019-10-16 16:30:39.111</td>\n",
       "      <td>51.0173070</td>\n",
       "      <td>4.4776119</td>\n",
       "      <td>2019-10-16 16:23:57.803</td>\n",
       "      <td>51.0351859</td>\n",
       "      <td>3.7342722</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106066939</th>\n",
       "      <td>2019-10-16 16:32:41.053</td>\n",
       "      <td>51.0173161</td>\n",
       "      <td>4.4776206</td>\n",
       "      <td>2019-10-16 16:23:57.803</td>\n",
       "      <td>51.0351859</td>\n",
       "      <td>3.7342722</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25671 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      timestamp_x  latitude_x  longitude_x  \\\n",
       "60606     2019-08-01 05:20:28.518  50.8619474    4.3359985   \n",
       "63973     2019-08-01 05:26:31.305  50.8619292    4.3359912   \n",
       "67340     2019-08-01 05:32:32.325  50.8619361    4.3359995   \n",
       "111112    2019-08-01 06:15:50.628  50.8618882    4.3359122   \n",
       "114479    2019-08-01 06:20:50.721  50.8618793    4.3359543   \n",
       "...                           ...         ...          ...   \n",
       "106060204 2019-10-16 16:28:38.010  51.0173066    4.4776110   \n",
       "106060205 2019-10-16 16:28:38.010  51.0173066    4.4776110   \n",
       "106063571 2019-10-16 16:30:39.111  51.0173070    4.4776119   \n",
       "106063572 2019-10-16 16:30:39.111  51.0173070    4.4776119   \n",
       "106066939 2019-10-16 16:32:41.053  51.0173161    4.4776206   \n",
       "\n",
       "                      timestamp_y  latitude_y  longitude_y  \\\n",
       "60606     2019-08-01 05:24:01.886  51.0350641    3.7339686   \n",
       "63973     2019-08-01 05:24:01.886  51.0350641    3.7339686   \n",
       "67340     2019-08-01 05:24:01.886  51.0350641    3.7339686   \n",
       "111112    2019-08-01 06:24:57.015  51.0341940    3.7362204   \n",
       "114479    2019-08-01 06:24:57.015  51.0341940    3.7362204   \n",
       "...                           ...         ...          ...   \n",
       "106060204 2019-10-16 16:21:48.999  51.0351943    3.7357462   \n",
       "106060205 2019-10-16 16:23:57.803  51.0351859    3.7342722   \n",
       "106063571 2019-10-16 16:21:48.999  51.0351943    3.7357462   \n",
       "106063572 2019-10-16 16:23:57.803  51.0351859    3.7342722   \n",
       "106066939 2019-10-16 16:23:57.803  51.0351859    3.7342722   \n",
       "\n",
       "           count_overlaps_in_int  \n",
       "60606                          1  \n",
       "63973                          1  \n",
       "67340                          1  \n",
       "111112                         1  \n",
       "114479                         3  \n",
       "...                          ...  \n",
       "106060204                      2  \n",
       "106060205                      2  \n",
       "106063571                      2  \n",
       "106063572                      2  \n",
       "106066939                      1  \n",
       "\n",
       "[25671 rows x 7 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 25671 entries, 60606 to 106066939\n",
      "Data columns (total 7 columns):\n",
      " #   Column                 Non-Null Count  Dtype         \n",
      "---  ------                 --------------  -----         \n",
      " 0   timestamp_x            25671 non-null  datetime64[ns]\n",
      " 1   latitude_x             25671 non-null  float64       \n",
      " 2   longitude_x            25671 non-null  float64       \n",
      " 3   timestamp_y            25671 non-null  datetime64[ns]\n",
      " 4   latitude_y             25671 non-null  float64       \n",
      " 5   longitude_y            25671 non-null  float64       \n",
      " 6   count_overlaps_in_int  25671 non-null  int64         \n",
      "dtypes: datetime64[ns](2), float64(4), int64(1)\n",
      "memory usage: 1.6 MB\n"
     ]
    }
   ],
   "source": [
    "result.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\u0100050\\appdata\\local\\programs\\python\\python38\\lib\\site-packages\\pandas\\core\\indexing.py:845: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self.obj[key] = _infer_fill_value(value)\n",
      "c:\\users\\u0100050\\appdata\\local\\programs\\python\\python38\\lib\\site-packages\\pandas\\core\\indexing.py:966: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  self.obj[item] = s\n"
     ]
    }
   ],
   "source": [
    "# calc dist and filter on that\n",
    "for lab, row in result.iterrows():\n",
    "    result.loc[lab, 'dist'] = geopy.distance.distance((row['latitude_x'], row['longitude_x']),(row['latitude_y'], row['longitude_y'])).m"
   ]
  },
  {
   "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>timestamp_x</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>timestamp_y</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "      <th>count_overlaps_in_int</th>\n",
       "      <th>dist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>60606</th>\n",
       "      <td>2019-08-01 05:20:28.518</td>\n",
       "      <td>50.8619474</td>\n",
       "      <td>4.3359985</td>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "      <td>1</td>\n",
       "      <td>46484.811531111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63973</th>\n",
       "      <td>2019-08-01 05:26:31.305</td>\n",
       "      <td>50.8619292</td>\n",
       "      <td>4.3359912</td>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "      <td>1</td>\n",
       "      <td>46485.190997363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67340</th>\n",
       "      <td>2019-08-01 05:32:32.325</td>\n",
       "      <td>50.8619361</td>\n",
       "      <td>4.3359995</td>\n",
       "      <td>2019-08-01 05:24:01.886</td>\n",
       "      <td>51.0350641</td>\n",
       "      <td>3.7339686</td>\n",
       "      <td>1</td>\n",
       "      <td>46485.400970788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111112</th>\n",
       "      <td>2019-08-01 06:15:50.628</td>\n",
       "      <td>50.8618882</td>\n",
       "      <td>4.3359122</td>\n",
       "      <td>2019-08-01 06:24:57.015</td>\n",
       "      <td>51.0341940</td>\n",
       "      <td>3.7362204</td>\n",
       "      <td>1</td>\n",
       "      <td>46298.277681280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114479</th>\n",
       "      <td>2019-08-01 06:20:50.721</td>\n",
       "      <td>50.8618793</td>\n",
       "      <td>4.3359543</td>\n",
       "      <td>2019-08-01 06:24:57.015</td>\n",
       "      <td>51.0341940</td>\n",
       "      <td>3.7362204</td>\n",
       "      <td>3</td>\n",
       "      <td>46301.384361709</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106060204</th>\n",
       "      <td>2019-10-16 16:28:38.010</td>\n",
       "      <td>51.0173066</td>\n",
       "      <td>4.4776110</td>\n",
       "      <td>2019-10-16 16:21:48.999</td>\n",
       "      <td>51.0351943</td>\n",
       "      <td>3.7357462</td>\n",
       "      <td>2</td>\n",
       "      <td>52085.602649285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106060205</th>\n",
       "      <td>2019-10-16 16:28:38.010</td>\n",
       "      <td>51.0173066</td>\n",
       "      <td>4.4776110</td>\n",
       "      <td>2019-10-16 16:23:57.803</td>\n",
       "      <td>51.0351859</td>\n",
       "      <td>3.7342722</td>\n",
       "      <td>2</td>\n",
       "      <td>52188.908047730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106063571</th>\n",
       "      <td>2019-10-16 16:30:39.111</td>\n",
       "      <td>51.0173070</td>\n",
       "      <td>4.4776119</td>\n",
       "      <td>2019-10-16 16:21:48.999</td>\n",
       "      <td>51.0351943</td>\n",
       "      <td>3.7357462</td>\n",
       "      <td>2</td>\n",
       "      <td>52085.663820756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106063572</th>\n",
       "      <td>2019-10-16 16:30:39.111</td>\n",
       "      <td>51.0173070</td>\n",
       "      <td>4.4776119</td>\n",
       "      <td>2019-10-16 16:23:57.803</td>\n",
       "      <td>51.0351859</td>\n",
       "      <td>3.7342722</td>\n",
       "      <td>2</td>\n",
       "      <td>52188.969223145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106066939</th>\n",
       "      <td>2019-10-16 16:32:41.053</td>\n",
       "      <td>51.0173161</td>\n",
       "      <td>4.4776206</td>\n",
       "      <td>2019-10-16 16:23:57.803</td>\n",
       "      <td>51.0351859</td>\n",
       "      <td>3.7342722</td>\n",
       "      <td>1</td>\n",
       "      <td>52189.535473983</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25671 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                      timestamp_x  latitude_x  longitude_x  \\\n",
       "60606     2019-08-01 05:20:28.518  50.8619474    4.3359985   \n",
       "63973     2019-08-01 05:26:31.305  50.8619292    4.3359912   \n",
       "67340     2019-08-01 05:32:32.325  50.8619361    4.3359995   \n",
       "111112    2019-08-01 06:15:50.628  50.8618882    4.3359122   \n",
       "114479    2019-08-01 06:20:50.721  50.8618793    4.3359543   \n",
       "...                           ...         ...          ...   \n",
       "106060204 2019-10-16 16:28:38.010  51.0173066    4.4776110   \n",
       "106060205 2019-10-16 16:28:38.010  51.0173066    4.4776110   \n",
       "106063571 2019-10-16 16:30:39.111  51.0173070    4.4776119   \n",
       "106063572 2019-10-16 16:30:39.111  51.0173070    4.4776119   \n",
       "106066939 2019-10-16 16:32:41.053  51.0173161    4.4776206   \n",
       "\n",
       "                      timestamp_y  latitude_y  longitude_y  \\\n",
       "60606     2019-08-01 05:24:01.886  51.0350641    3.7339686   \n",
       "63973     2019-08-01 05:24:01.886  51.0350641    3.7339686   \n",
       "67340     2019-08-01 05:24:01.886  51.0350641    3.7339686   \n",
       "111112    2019-08-01 06:24:57.015  51.0341940    3.7362204   \n",
       "114479    2019-08-01 06:24:57.015  51.0341940    3.7362204   \n",
       "...                           ...         ...          ...   \n",
       "106060204 2019-10-16 16:21:48.999  51.0351943    3.7357462   \n",
       "106060205 2019-10-16 16:23:57.803  51.0351859    3.7342722   \n",
       "106063571 2019-10-16 16:21:48.999  51.0351943    3.7357462   \n",
       "106063572 2019-10-16 16:23:57.803  51.0351859    3.7342722   \n",
       "106066939 2019-10-16 16:23:57.803  51.0351859    3.7342722   \n",
       "\n",
       "           count_overlaps_in_int             dist  \n",
       "60606                          1  46484.811531111  \n",
       "63973                          1  46485.190997363  \n",
       "67340                          1  46485.400970788  \n",
       "111112                         1  46298.277681280  \n",
       "114479                         3  46301.384361709  \n",
       "...                          ...              ...  \n",
       "106060204                      2  52085.602649285  \n",
       "106060205                      2  52188.908047730  \n",
       "106063571                      2  52085.663820756  \n",
       "106063572                      2  52188.969223145  \n",
       "106066939                      1  52189.535473983  \n",
       "\n",
       "[25671 rows x 8 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>timestamp_x</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>timestamp_y</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "      <th>count_overlaps_in_int</th>\n",
       "      <th>dist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52442900</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>4.374792876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52442901</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>7.547835973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52446267</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52446268</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>10.818282131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52449634</th>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58638480</th>\n",
       "      <td>2019-09-15 08:46:13.779</td>\n",
       "      <td>51.4897612</td>\n",
       "      <td>-0.1157898</td>\n",
       "      <td>2019-09-15 08:51:56.791</td>\n",
       "      <td>51.4898393</td>\n",
       "      <td>-0.1155317</td>\n",
       "      <td>1</td>\n",
       "      <td>19.921482811</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58827036</th>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>51.5309645</td>\n",
       "      <td>-0.1257586</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>11.207023835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58833770</th>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>51.5308573</td>\n",
       "      <td>-0.1258008</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>17.180610921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58840504</th>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>51.5309513</td>\n",
       "      <td>-0.1257283</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>8.949512239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58843871</th>\n",
       "      <td>2019-09-15 11:11:54.643</td>\n",
       "      <td>51.5310514</td>\n",
       "      <td>-0.1256708</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>12.604794188</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>152 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     timestamp_x  latitude_x  longitude_x  \\\n",
       "52442900 2019-09-09 06:39:14.097  52.2525362    0.7138794   \n",
       "52442901 2019-09-09 06:39:14.097  52.2525362    0.7138794   \n",
       "52446267 2019-09-09 06:40:55.850  52.2525082    0.7138945   \n",
       "52446268 2019-09-09 06:40:55.850  52.2525082    0.7138945   \n",
       "52449634 2019-09-09 06:45:08.096  52.2525082    0.7138945   \n",
       "...                          ...         ...          ...   \n",
       "58638480 2019-09-15 08:46:13.779  51.4897612   -0.1157898   \n",
       "58827036 2019-09-15 11:04:09.436  51.5309645   -0.1257586   \n",
       "58833770 2019-09-15 11:08:13.895  51.5308573   -0.1258008   \n",
       "58840504 2019-09-15 11:11:33.024  51.5309513   -0.1257283   \n",
       "58843871 2019-09-15 11:11:54.643  51.5310514   -0.1256708   \n",
       "\n",
       "                     timestamp_y  latitude_y  longitude_y  \\\n",
       "52442900 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "52442901 2019-09-09 06:48:59.484  52.2526024    0.7138553   \n",
       "52446267 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "52446268 2019-09-09 06:48:59.484  52.2526024    0.7138553   \n",
       "52449634 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "...                          ...         ...          ...   \n",
       "58638480 2019-09-15 08:51:56.791  51.4898393   -0.1155317   \n",
       "58827036 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58833770 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58840504 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58843871 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "\n",
       "          count_overlaps_in_int          dist  \n",
       "52442900                      2   4.374792876  \n",
       "52442901                      2   7.547835973  \n",
       "52446267                      2   7.620557842  \n",
       "52446268                      2  10.818282131  \n",
       "52449634                      2   7.620557842  \n",
       "...                         ...           ...  \n",
       "58638480                      1  19.921482811  \n",
       "58827036                      4  11.207023835  \n",
       "58833770                      4  17.180610921  \n",
       "58840504                      4   8.949512239  \n",
       "58843871                      4  12.604794188  \n",
       "\n",
       "[152 rows x 8 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filter on maximum distance\n",
    "max_dist = 20\n",
    "\n",
    "result = result.loc[result['dist'] < max_dist]\n",
    "\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "if result.empty == True:\n",
    "    raise ValueError('There are no overlapping matches based on GPS traces. \\nTHIS IS SAD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-33-f52b5caedb65>:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  result.sort_values(\"timestamp_x\", inplace = True)\n",
      "<ipython-input-33-f52b5caedb65>:9: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  result['ts_latest'] = result.loc[:, ['timestamp_x', 'timestamp_y']].max(axis=1)\n",
      "<ipython-input-33-f52b5caedb65>:10: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  result['ts_earliest'] = result.loc[:, ['timestamp_x', 'timestamp_y']].min(axis=1)\n",
      "<ipython-input-33-f52b5caedb65>:16: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  result['ts_earliest_moved_up'] = result['ts_earliest'].shift(periods = -1)\n",
      "<ipython-input-33-f52b5caedb65>:22: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  result['ts_smallest_delta'] = result.loc[:, ['ts_latest', 'ts_earliest_moved_up']].max(axis=1) - result.loc[:, ['ts_latest', 'ts_earliest_moved_up']].min(axis=1)\n"
     ]
    },
    {
     "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>timestamp_x</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>timestamp_y</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "      <th>count_overlaps_in_int</th>\n",
       "      <th>dist</th>\n",
       "      <th>ts_latest</th>\n",
       "      <th>ts_earliest</th>\n",
       "      <th>ts_earliest_moved_up</th>\n",
       "      <th>ts_smallest_delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52442900</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>4.374792876</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>00:07:37.102000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52442901</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>7.547835973</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>00:08:03.634000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52446267</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>00:05:55.349000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52446268</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>10.818282131</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>00:03:51.388000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52449634</th>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>00:01:43.103000</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>58638480</th>\n",
       "      <td>2019-09-15 08:46:13.779</td>\n",
       "      <td>51.4897612</td>\n",
       "      <td>-0.1157898</td>\n",
       "      <td>2019-09-15 08:51:56.791</td>\n",
       "      <td>51.4898393</td>\n",
       "      <td>-0.1155317</td>\n",
       "      <td>1</td>\n",
       "      <td>19.921482811</td>\n",
       "      <td>2019-09-15 08:51:56.791</td>\n",
       "      <td>2019-09-15 08:46:13.779</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>02:11:45.357000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58827036</th>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>51.5309645</td>\n",
       "      <td>-0.1257586</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>11.207023835</td>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>00:00:27.288000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58833770</th>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>51.5308573</td>\n",
       "      <td>-0.1258008</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>17.180610921</td>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>00:04:31.747000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58840504</th>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>51.5309513</td>\n",
       "      <td>-0.1257283</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>8.949512239</td>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>00:07:50.876000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58843871</th>\n",
       "      <td>2019-09-15 11:11:54.643</td>\n",
       "      <td>51.5310514</td>\n",
       "      <td>-0.1256708</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>12.604794188</td>\n",
       "      <td>2019-09-15 11:11:54.643</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>NaT</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>152 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     timestamp_x  latitude_x  longitude_x  \\\n",
       "52442900 2019-09-09 06:39:14.097  52.2525362    0.7138794   \n",
       "52442901 2019-09-09 06:39:14.097  52.2525362    0.7138794   \n",
       "52446267 2019-09-09 06:40:55.850  52.2525082    0.7138945   \n",
       "52446268 2019-09-09 06:40:55.850  52.2525082    0.7138945   \n",
       "52449634 2019-09-09 06:45:08.096  52.2525082    0.7138945   \n",
       "...                          ...         ...          ...   \n",
       "58638480 2019-09-15 08:46:13.779  51.4897612   -0.1157898   \n",
       "58827036 2019-09-15 11:04:09.436  51.5309645   -0.1257586   \n",
       "58833770 2019-09-15 11:08:13.895  51.5308573   -0.1258008   \n",
       "58840504 2019-09-15 11:11:33.024  51.5309513   -0.1257283   \n",
       "58843871 2019-09-15 11:11:54.643  51.5310514   -0.1256708   \n",
       "\n",
       "                     timestamp_y  latitude_y  longitude_y  \\\n",
       "52442900 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "52442901 2019-09-09 06:48:59.484  52.2526024    0.7138553   \n",
       "52446267 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "52446268 2019-09-09 06:48:59.484  52.2526024    0.7138553   \n",
       "52449634 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "...                          ...         ...          ...   \n",
       "58638480 2019-09-15 08:51:56.791  51.4898393   -0.1155317   \n",
       "58827036 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58833770 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58840504 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58843871 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "\n",
       "          count_overlaps_in_int          dist               ts_latest  \\\n",
       "52442900                      2   4.374792876 2019-09-09 06:46:51.199   \n",
       "52442901                      2   7.547835973 2019-09-09 06:48:59.484   \n",
       "52446267                      2   7.620557842 2019-09-09 06:46:51.199   \n",
       "52446268                      2  10.818282131 2019-09-09 06:48:59.484   \n",
       "52449634                      2   7.620557842 2019-09-09 06:46:51.199   \n",
       "...                         ...           ...                     ...   \n",
       "58638480                      1  19.921482811 2019-09-15 08:51:56.791   \n",
       "58827036                      4  11.207023835 2019-09-15 11:04:09.436   \n",
       "58833770                      4  17.180610921 2019-09-15 11:08:13.895   \n",
       "58840504                      4   8.949512239 2019-09-15 11:11:33.024   \n",
       "58843871                      4  12.604794188 2019-09-15 11:11:54.643   \n",
       "\n",
       "                     ts_earliest    ts_earliest_moved_up ts_smallest_delta  \n",
       "52442900 2019-09-09 06:39:14.097 2019-09-09 06:39:14.097   00:07:37.102000  \n",
       "52442901 2019-09-09 06:39:14.097 2019-09-09 06:40:55.850   00:08:03.634000  \n",
       "52446267 2019-09-09 06:40:55.850 2019-09-09 06:40:55.850   00:05:55.349000  \n",
       "52446268 2019-09-09 06:40:55.850 2019-09-09 06:45:08.096   00:03:51.388000  \n",
       "52449634 2019-09-09 06:45:08.096 2019-09-09 06:45:08.096   00:01:43.103000  \n",
       "...                          ...                     ...               ...  \n",
       "58638480 2019-09-15 08:46:13.779 2019-09-15 11:03:42.148   02:11:45.357000  \n",
       "58827036 2019-09-15 11:03:42.148 2019-09-15 11:03:42.148   00:00:27.288000  \n",
       "58833770 2019-09-15 11:03:42.148 2019-09-15 11:03:42.148   00:04:31.747000  \n",
       "58840504 2019-09-15 11:03:42.148 2019-09-15 11:03:42.148   00:07:50.876000  \n",
       "58843871 2019-09-15 11:03:42.148                     NaT          00:00:00  \n",
       "\n",
       "[152 rows x 12 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Now filter on time between two observations\n",
    "result.sort_values(\"timestamp_x\", inplace = True)\n",
    "\n",
    "# For each two rows, check if the smallest difference between the latest ts of the first \n",
    "# row and the earliest ts of the second row is smaller than the max_delta_ts\n",
    "\n",
    "# We allow this difference to pass over different days for simplicity\n",
    "# So do not calculate this by day yet\n",
    "result['ts_latest'] = result.loc[:, ['timestamp_x', 'timestamp_y']].max(axis=1)\n",
    "result['ts_earliest'] = result.loc[:, ['timestamp_x', 'timestamp_y']].min(axis=1)\n",
    "\n",
    "# Calculate the difference between each two rows: the ts_latest of the 1st row, the \n",
    "# earliest of the 2nd row\n",
    "\n",
    "# move ts_earliest up one row\n",
    "result['ts_earliest_moved_up'] = result['ts_earliest'].shift(periods = -1)\n",
    "\n",
    "# calculate diff\n",
    "# result['ts_smallest_delta'] = result['ts_latest'] - result['ts_earliest_moved_up']\n",
    "# Weird issue with day difference?\n",
    "# Just take the difference of the latest value \n",
    "result['ts_smallest_delta'] = result.loc[:, ['ts_latest', 'ts_earliest_moved_up']].max(axis=1) - result.loc[:, ['ts_latest', 'ts_earliest_moved_up']].min(axis=1)\n",
    "\n",
    "result\n",
    "# result['ts_earliest_moved_up'][5]\n",
    "# print(str(result['ts_earliest_moved_up'][5]))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(152, 12)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_delta_ts = 60\n",
    "max_delta_ts = pd.to_timedelta(max_delta_ts, unit = 'minutes')\n",
    "\n",
    "# filter on max_delta_ts\n",
    "# Note that the last row also gets deleted (NaT)\n",
    "result = result.loc[result['ts_smallest_delta'] < max_delta_ts]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(135, 12)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "if result.empty == True:\n",
    "    raise ValueError('There are no overlapping matches based on GPS traces. \\nTHIS IS SAD')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 135 entries, 52442900 to 58843871\n",
      "Data columns (total 12 columns):\n",
      " #   Column                 Non-Null Count  Dtype          \n",
      "---  ------                 --------------  -----          \n",
      " 0   timestamp_x            135 non-null    datetime64[ns] \n",
      " 1   latitude_x             135 non-null    float64        \n",
      " 2   longitude_x            135 non-null    float64        \n",
      " 3   timestamp_y            135 non-null    datetime64[ns] \n",
      " 4   latitude_y             135 non-null    float64        \n",
      " 5   longitude_y            135 non-null    float64        \n",
      " 6   count_overlaps_in_int  135 non-null    int64          \n",
      " 7   dist                   135 non-null    float64        \n",
      " 8   ts_latest              135 non-null    datetime64[ns] \n",
      " 9   ts_earliest            135 non-null    datetime64[ns] \n",
      " 10  ts_earliest_moved_up   134 non-null    datetime64[ns] \n",
      " 11  ts_smallest_delta      135 non-null    timedelta64[ns]\n",
      "dtypes: datetime64[ns](5), float64(5), int64(1), timedelta64[ns](1)\n",
      "memory usage: 13.7 KB\n"
     ]
    }
   ],
   "source": [
    "result.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>timestamp_x</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>timestamp_y</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "      <th>count_overlaps_in_int</th>\n",
       "      <th>dist</th>\n",
       "      <th>ts_latest</th>\n",
       "      <th>ts_earliest</th>\n",
       "      <th>ts_earliest_moved_up</th>\n",
       "      <th>ts_smallest_delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52442900</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>4.374792876</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>00:07:37.102000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52442901</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>7.547835973</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>00:08:03.634000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52446267</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>00:05:55.349000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52446268</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>10.818282131</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>00:03:51.388000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52449634</th>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>00:01:43.103000</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>58635113</th>\n",
       "      <td>2019-09-15 08:43:11.727</td>\n",
       "      <td>51.4897612</td>\n",
       "      <td>-0.1157898</td>\n",
       "      <td>2019-09-15 08:51:56.791</td>\n",
       "      <td>51.4898393</td>\n",
       "      <td>-0.1155317</td>\n",
       "      <td>1</td>\n",
       "      <td>19.921482811</td>\n",
       "      <td>2019-09-15 08:51:56.791</td>\n",
       "      <td>2019-09-15 08:43:11.727</td>\n",
       "      <td>2019-09-15 08:46:13.779</td>\n",
       "      <td>00:05:43.012000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58827036</th>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>51.5309645</td>\n",
       "      <td>-0.1257586</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>11.207023835</td>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>00:00:27.288000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58833770</th>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>51.5308573</td>\n",
       "      <td>-0.1258008</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>17.180610921</td>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>00:04:31.747000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58840504</th>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>51.5309513</td>\n",
       "      <td>-0.1257283</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>8.949512239</td>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>00:07:50.876000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58843871</th>\n",
       "      <td>2019-09-15 11:11:54.643</td>\n",
       "      <td>51.5310514</td>\n",
       "      <td>-0.1256708</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>12.604794188</td>\n",
       "      <td>2019-09-15 11:11:54.643</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>NaT</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>135 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     timestamp_x  latitude_x  longitude_x  \\\n",
       "52442900 2019-09-09 06:39:14.097  52.2525362    0.7138794   \n",
       "52442901 2019-09-09 06:39:14.097  52.2525362    0.7138794   \n",
       "52446267 2019-09-09 06:40:55.850  52.2525082    0.7138945   \n",
       "52446268 2019-09-09 06:40:55.850  52.2525082    0.7138945   \n",
       "52449634 2019-09-09 06:45:08.096  52.2525082    0.7138945   \n",
       "...                          ...         ...          ...   \n",
       "58635113 2019-09-15 08:43:11.727  51.4897612   -0.1157898   \n",
       "58827036 2019-09-15 11:04:09.436  51.5309645   -0.1257586   \n",
       "58833770 2019-09-15 11:08:13.895  51.5308573   -0.1258008   \n",
       "58840504 2019-09-15 11:11:33.024  51.5309513   -0.1257283   \n",
       "58843871 2019-09-15 11:11:54.643  51.5310514   -0.1256708   \n",
       "\n",
       "                     timestamp_y  latitude_y  longitude_y  \\\n",
       "52442900 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "52442901 2019-09-09 06:48:59.484  52.2526024    0.7138553   \n",
       "52446267 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "52446268 2019-09-09 06:48:59.484  52.2526024    0.7138553   \n",
       "52449634 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "...                          ...         ...          ...   \n",
       "58635113 2019-09-15 08:51:56.791  51.4898393   -0.1155317   \n",
       "58827036 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58833770 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58840504 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58843871 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "\n",
       "          count_overlaps_in_int          dist               ts_latest  \\\n",
       "52442900                      2   4.374792876 2019-09-09 06:46:51.199   \n",
       "52442901                      2   7.547835973 2019-09-09 06:48:59.484   \n",
       "52446267                      2   7.620557842 2019-09-09 06:46:51.199   \n",
       "52446268                      2  10.818282131 2019-09-09 06:48:59.484   \n",
       "52449634                      2   7.620557842 2019-09-09 06:46:51.199   \n",
       "...                         ...           ...                     ...   \n",
       "58635113                      1  19.921482811 2019-09-15 08:51:56.791   \n",
       "58827036                      4  11.207023835 2019-09-15 11:04:09.436   \n",
       "58833770                      4  17.180610921 2019-09-15 11:08:13.895   \n",
       "58840504                      4   8.949512239 2019-09-15 11:11:33.024   \n",
       "58843871                      4  12.604794188 2019-09-15 11:11:54.643   \n",
       "\n",
       "                     ts_earliest    ts_earliest_moved_up ts_smallest_delta  \n",
       "52442900 2019-09-09 06:39:14.097 2019-09-09 06:39:14.097   00:07:37.102000  \n",
       "52442901 2019-09-09 06:39:14.097 2019-09-09 06:40:55.850   00:08:03.634000  \n",
       "52446267 2019-09-09 06:40:55.850 2019-09-09 06:40:55.850   00:05:55.349000  \n",
       "52446268 2019-09-09 06:40:55.850 2019-09-09 06:45:08.096   00:03:51.388000  \n",
       "52449634 2019-09-09 06:45:08.096 2019-09-09 06:45:08.096   00:01:43.103000  \n",
       "...                          ...                     ...               ...  \n",
       "58635113 2019-09-15 08:43:11.727 2019-09-15 08:46:13.779   00:05:43.012000  \n",
       "58827036 2019-09-15 11:03:42.148 2019-09-15 11:03:42.148   00:00:27.288000  \n",
       "58833770 2019-09-15 11:03:42.148 2019-09-15 11:03:42.148   00:04:31.747000  \n",
       "58840504 2019-09-15 11:03:42.148 2019-09-15 11:03:42.148   00:07:50.876000  \n",
       "58843871 2019-09-15 11:03:42.148                     NaT          00:00:00  \n",
       "\n",
       "[135 rows x 12 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-40-c601c6e36882>:13: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  result['ts_latest_moved_up'] = result['ts_latest'].shift(periods = -1)\n",
      "<ipython-input-40-c601c6e36882>:16: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  result['ts_delta_since_previous_match'] = result.loc[:, ['ts_latest_moved_up', 'ts_earliest']].max(axis=1) - result.loc[:, ['ts_latest_moved_up', 'ts_earliest']].min(axis=1)\n"
     ]
    },
    {
     "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>timestamp_x</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>timestamp_y</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "      <th>count_overlaps_in_int</th>\n",
       "      <th>dist</th>\n",
       "      <th>ts_latest</th>\n",
       "      <th>ts_earliest</th>\n",
       "      <th>ts_earliest_moved_up</th>\n",
       "      <th>ts_smallest_delta</th>\n",
       "      <th>ts_latest_moved_up</th>\n",
       "      <th>ts_delta_since_previous_match</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52442900</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>4.374792876</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>00:07:37.102000</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>00:09:45.387000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52442901</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>7.547835973</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>00:08:03.634000</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>00:07:37.102000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52446267</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>00:05:55.349000</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>00:08:03.634000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52446268</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>10.818282131</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>00:03:51.388000</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>00:05:55.349000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52449634</th>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>00:01:43.103000</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>00:03:51.388000</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58635113</th>\n",
       "      <td>2019-09-15 08:43:11.727</td>\n",
       "      <td>51.4897612</td>\n",
       "      <td>-0.1157898</td>\n",
       "      <td>2019-09-15 08:51:56.791</td>\n",
       "      <td>51.4898393</td>\n",
       "      <td>-0.1155317</td>\n",
       "      <td>1</td>\n",
       "      <td>19.921482811</td>\n",
       "      <td>2019-09-15 08:51:56.791</td>\n",
       "      <td>2019-09-15 08:43:11.727</td>\n",
       "      <td>2019-09-15 08:46:13.779</td>\n",
       "      <td>00:05:43.012000</td>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>02:20:57.709000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58827036</th>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>51.5309645</td>\n",
       "      <td>-0.1257586</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>11.207023835</td>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>00:00:27.288000</td>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>00:04:31.747000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58833770</th>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>51.5308573</td>\n",
       "      <td>-0.1258008</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>17.180610921</td>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>00:04:31.747000</td>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>00:07:50.876000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58840504</th>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>51.5309513</td>\n",
       "      <td>-0.1257283</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>8.949512239</td>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>00:07:50.876000</td>\n",
       "      <td>2019-09-15 11:11:54.643</td>\n",
       "      <td>00:08:12.495000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58843871</th>\n",
       "      <td>2019-09-15 11:11:54.643</td>\n",
       "      <td>51.5310514</td>\n",
       "      <td>-0.1256708</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>12.604794188</td>\n",
       "      <td>2019-09-15 11:11:54.643</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>NaT</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>135 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     timestamp_x  latitude_x  longitude_x  \\\n",
       "52442900 2019-09-09 06:39:14.097  52.2525362    0.7138794   \n",
       "52442901 2019-09-09 06:39:14.097  52.2525362    0.7138794   \n",
       "52446267 2019-09-09 06:40:55.850  52.2525082    0.7138945   \n",
       "52446268 2019-09-09 06:40:55.850  52.2525082    0.7138945   \n",
       "52449634 2019-09-09 06:45:08.096  52.2525082    0.7138945   \n",
       "...                          ...         ...          ...   \n",
       "58635113 2019-09-15 08:43:11.727  51.4897612   -0.1157898   \n",
       "58827036 2019-09-15 11:04:09.436  51.5309645   -0.1257586   \n",
       "58833770 2019-09-15 11:08:13.895  51.5308573   -0.1258008   \n",
       "58840504 2019-09-15 11:11:33.024  51.5309513   -0.1257283   \n",
       "58843871 2019-09-15 11:11:54.643  51.5310514   -0.1256708   \n",
       "\n",
       "                     timestamp_y  latitude_y  longitude_y  \\\n",
       "52442900 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "52442901 2019-09-09 06:48:59.484  52.2526024    0.7138553   \n",
       "52446267 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "52446268 2019-09-09 06:48:59.484  52.2526024    0.7138553   \n",
       "52449634 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "...                          ...         ...          ...   \n",
       "58635113 2019-09-15 08:51:56.791  51.4898393   -0.1155317   \n",
       "58827036 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58833770 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58840504 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58843871 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "\n",
       "          count_overlaps_in_int          dist               ts_latest  \\\n",
       "52442900                      2   4.374792876 2019-09-09 06:46:51.199   \n",
       "52442901                      2   7.547835973 2019-09-09 06:48:59.484   \n",
       "52446267                      2   7.620557842 2019-09-09 06:46:51.199   \n",
       "52446268                      2  10.818282131 2019-09-09 06:48:59.484   \n",
       "52449634                      2   7.620557842 2019-09-09 06:46:51.199   \n",
       "...                         ...           ...                     ...   \n",
       "58635113                      1  19.921482811 2019-09-15 08:51:56.791   \n",
       "58827036                      4  11.207023835 2019-09-15 11:04:09.436   \n",
       "58833770                      4  17.180610921 2019-09-15 11:08:13.895   \n",
       "58840504                      4   8.949512239 2019-09-15 11:11:33.024   \n",
       "58843871                      4  12.604794188 2019-09-15 11:11:54.643   \n",
       "\n",
       "                     ts_earliest    ts_earliest_moved_up ts_smallest_delta  \\\n",
       "52442900 2019-09-09 06:39:14.097 2019-09-09 06:39:14.097   00:07:37.102000   \n",
       "52442901 2019-09-09 06:39:14.097 2019-09-09 06:40:55.850   00:08:03.634000   \n",
       "52446267 2019-09-09 06:40:55.850 2019-09-09 06:40:55.850   00:05:55.349000   \n",
       "52446268 2019-09-09 06:40:55.850 2019-09-09 06:45:08.096   00:03:51.388000   \n",
       "52449634 2019-09-09 06:45:08.096 2019-09-09 06:45:08.096   00:01:43.103000   \n",
       "...                          ...                     ...               ...   \n",
       "58635113 2019-09-15 08:43:11.727 2019-09-15 08:46:13.779   00:05:43.012000   \n",
       "58827036 2019-09-15 11:03:42.148 2019-09-15 11:03:42.148   00:00:27.288000   \n",
       "58833770 2019-09-15 11:03:42.148 2019-09-15 11:03:42.148   00:04:31.747000   \n",
       "58840504 2019-09-15 11:03:42.148 2019-09-15 11:03:42.148   00:07:50.876000   \n",
       "58843871 2019-09-15 11:03:42.148                     NaT          00:00:00   \n",
       "\n",
       "              ts_latest_moved_up ts_delta_since_previous_match  \n",
       "52442900 2019-09-09 06:48:59.484               00:09:45.387000  \n",
       "52442901 2019-09-09 06:46:51.199               00:07:37.102000  \n",
       "52446267 2019-09-09 06:48:59.484               00:08:03.634000  \n",
       "52446268 2019-09-09 06:46:51.199               00:05:55.349000  \n",
       "52449634 2019-09-09 06:48:59.484               00:03:51.388000  \n",
       "...                          ...                           ...  \n",
       "58635113 2019-09-15 11:04:09.436               02:20:57.709000  \n",
       "58827036 2019-09-15 11:08:13.895               00:04:31.747000  \n",
       "58833770 2019-09-15 11:11:33.024               00:07:50.876000  \n",
       "58840504 2019-09-15 11:11:54.643               00:08:12.495000  \n",
       "58843871                     NaT                      00:00:00  \n",
       "\n",
       "[135 rows x 14 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For two 'hits' of observations who are in the allowed max_delta_ts\n",
    "# For each day\n",
    "# Calculate the time difference between each two subsequent hits\n",
    "# For the first row take the earliest ts\n",
    "# For the second row take the latest ts\n",
    "# Calculate the difference between those\n",
    "# And add to total_time_together by day\n",
    "\n",
    "# Calculate the difference between each two rows: the ts_latest of the 1st row, the \n",
    "# earliest of the 2nd row\n",
    "\n",
    "# move ts_earliest up one row\n",
    "result['ts_latest_moved_up'] = result['ts_latest'].shift(periods = -1)\n",
    "\n",
    "# calculate diff\n",
    "result['ts_delta_since_previous_match'] = result.loc[:, ['ts_latest_moved_up', 'ts_earliest']].max(axis=1) - result.loc[:, ['ts_latest_moved_up', 'ts_earliest']].min(axis=1)\n",
    "\n",
    "result\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(135, 14)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\u0100050\\appdata\\local\\programs\\python\\python38\\lib\\site-packages\\pandas\\core\\frame.py:3990: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  return super().drop(\n"
     ]
    }
   ],
   "source": [
    "# Delete last match because there is no 'next' match that we can use to calculate the total time spent together\n",
    "result.drop(result.tail(1).index,inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(134, 14)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-44-731412db5e2b>:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  result['current_day'] = result['timestamp_x'].dt.date\n"
     ]
    },
    {
     "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>date</th>\n",
       "      <th>time_together</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-09-12</td>\n",
       "      <td>00:25:37.599000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-09-13</td>\n",
       "      <td>05:17:51.378000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-09-14</td>\n",
       "      <td>04:40:11.522000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-09-15</td>\n",
       "      <td>00:18:32.923000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date   time_together\n",
       "0  2019-09-09 03:41:23.498000\n",
       "1  2019-09-12 00:25:37.599000\n",
       "2  2019-09-13 05:17:51.378000\n",
       "3  2019-09-14 04:40:11.522000\n",
       "4  2019-09-15 00:18:32.923000"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add column which sums time spent together for each day\n",
    "result['current_day'] = result['timestamp_x'].dt.date\n",
    "\n",
    "#result['time_together_today'] = \n",
    "output = result.groupby('current_day')['ts_smallest_delta'].sum()\n",
    "\n",
    "output = pd.DataFrame({'date': output.index, 'time_together': output.values})\n",
    "\n",
    "output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['timestamp_x', 'latitude_x', 'longitude_x', 'timestamp_y', 'latitude_y',\n",
       "       'longitude_y', 'count_overlaps_in_int', 'dist', 'ts_latest',\n",
       "       'ts_earliest', 'ts_earliest_moved_up', 'ts_smallest_delta',\n",
       "       'ts_latest_moved_up', 'ts_delta_since_previous_match', 'current_day'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Merge with result to get second type of output and clean up**\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>timestamp_x</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>timestamp_y</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "      <th>count_overlaps_in_int</th>\n",
       "      <th>dist</th>\n",
       "      <th>current_day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52442900</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>4.374792876</td>\n",
       "      <td>2019-09-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52442901</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>7.547835973</td>\n",
       "      <td>2019-09-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52446267</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52446268</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>10.818282131</td>\n",
       "      <td>2019-09-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52449634</th>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57742856</th>\n",
       "      <td>2019-09-14 23:06:03.000</td>\n",
       "      <td>51.4897918</td>\n",
       "      <td>-0.1158281</td>\n",
       "      <td>2019-09-14 22:56:46.626</td>\n",
       "      <td>51.4897181</td>\n",
       "      <td>-0.1156491</td>\n",
       "      <td>1</td>\n",
       "      <td>14.893132608</td>\n",
       "      <td>2019-09-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58635113</th>\n",
       "      <td>2019-09-15 08:43:11.727</td>\n",
       "      <td>51.4897612</td>\n",
       "      <td>-0.1157898</td>\n",
       "      <td>2019-09-15 08:51:56.791</td>\n",
       "      <td>51.4898393</td>\n",
       "      <td>-0.1155317</td>\n",
       "      <td>1</td>\n",
       "      <td>19.921482811</td>\n",
       "      <td>2019-09-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58827036</th>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>51.5309645</td>\n",
       "      <td>-0.1257586</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>11.207023835</td>\n",
       "      <td>2019-09-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58833770</th>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>51.5308573</td>\n",
       "      <td>-0.1258008</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>17.180610921</td>\n",
       "      <td>2019-09-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58840504</th>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>51.5309513</td>\n",
       "      <td>-0.1257283</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>8.949512239</td>\n",
       "      <td>2019-09-15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     timestamp_x  latitude_x  longitude_x  \\\n",
       "52442900 2019-09-09 06:39:14.097  52.2525362    0.7138794   \n",
       "52442901 2019-09-09 06:39:14.097  52.2525362    0.7138794   \n",
       "52446267 2019-09-09 06:40:55.850  52.2525082    0.7138945   \n",
       "52446268 2019-09-09 06:40:55.850  52.2525082    0.7138945   \n",
       "52449634 2019-09-09 06:45:08.096  52.2525082    0.7138945   \n",
       "...                          ...         ...          ...   \n",
       "57742856 2019-09-14 23:06:03.000  51.4897918   -0.1158281   \n",
       "58635113 2019-09-15 08:43:11.727  51.4897612   -0.1157898   \n",
       "58827036 2019-09-15 11:04:09.436  51.5309645   -0.1257586   \n",
       "58833770 2019-09-15 11:08:13.895  51.5308573   -0.1258008   \n",
       "58840504 2019-09-15 11:11:33.024  51.5309513   -0.1257283   \n",
       "\n",
       "                     timestamp_y  latitude_y  longitude_y  \\\n",
       "52442900 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "52442901 2019-09-09 06:48:59.484  52.2526024    0.7138553   \n",
       "52446267 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "52446268 2019-09-09 06:48:59.484  52.2526024    0.7138553   \n",
       "52449634 2019-09-09 06:46:51.199  52.2525704    0.7138478   \n",
       "...                          ...         ...          ...   \n",
       "57742856 2019-09-14 22:56:46.626  51.4897181   -0.1156491   \n",
       "58635113 2019-09-15 08:51:56.791  51.4898393   -0.1155317   \n",
       "58827036 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58833770 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "58840504 2019-09-15 11:03:42.148  51.5309472   -0.1255995   \n",
       "\n",
       "          count_overlaps_in_int          dist current_day  \n",
       "52442900                      2   4.374792876  2019-09-09  \n",
       "52442901                      2   7.547835973  2019-09-09  \n",
       "52446267                      2   7.620557842  2019-09-09  \n",
       "52446268                      2  10.818282131  2019-09-09  \n",
       "52449634                      2   7.620557842  2019-09-09  \n",
       "...                         ...           ...         ...  \n",
       "57742856                      1  14.893132608  2019-09-14  \n",
       "58635113                      1  19.921482811  2019-09-15  \n",
       "58827036                      4  11.207023835  2019-09-15  \n",
       "58833770                      4  17.180610921  2019-09-15  \n",
       "58840504                      4   8.949512239  2019-09-15  \n",
       "\n",
       "[134 rows x 9 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge with result to get second type of output and clean up\n",
    "result = result[['timestamp_x', 'latitude_x', 'longitude_x', 'timestamp_y', 'latitude_y', 'longitude_y', 'count_overlaps_in_int', 'dist', 'current_day']]\n",
    "\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "output2 = pd.merge(result, output, left_on = 'current_day' , right_on = 'date')\n",
    "output2 = output2.drop(columns='current_day')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>timestamp_x</th>\n",
       "      <th>latitude_x</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>timestamp_y</th>\n",
       "      <th>latitude_y</th>\n",
       "      <th>longitude_y</th>\n",
       "      <th>count_overlaps_in_int</th>\n",
       "      <th>dist</th>\n",
       "      <th>date</th>\n",
       "      <th>time_together</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>4.374792876</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>7.547835973</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>10.818282131</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</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>129</th>\n",
       "      <td>2019-09-14 23:06:03.000</td>\n",
       "      <td>51.4897918</td>\n",
       "      <td>-0.1158281</td>\n",
       "      <td>2019-09-14 22:56:46.626</td>\n",
       "      <td>51.4897181</td>\n",
       "      <td>-0.1156491</td>\n",
       "      <td>1</td>\n",
       "      <td>14.893132608</td>\n",
       "      <td>2019-09-14</td>\n",
       "      <td>04:40:11.522000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>2019-09-15 08:43:11.727</td>\n",
       "      <td>51.4897612</td>\n",
       "      <td>-0.1157898</td>\n",
       "      <td>2019-09-15 08:51:56.791</td>\n",
       "      <td>51.4898393</td>\n",
       "      <td>-0.1155317</td>\n",
       "      <td>1</td>\n",
       "      <td>19.921482811</td>\n",
       "      <td>2019-09-15</td>\n",
       "      <td>00:18:32.923000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>51.5309645</td>\n",
       "      <td>-0.1257586</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>11.207023835</td>\n",
       "      <td>2019-09-15</td>\n",
       "      <td>00:18:32.923000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>51.5308573</td>\n",
       "      <td>-0.1258008</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>17.180610921</td>\n",
       "      <td>2019-09-15</td>\n",
       "      <td>00:18:32.923000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>51.5309513</td>\n",
       "      <td>-0.1257283</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>8.949512239</td>\n",
       "      <td>2019-09-15</td>\n",
       "      <td>00:18:32.923000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                timestamp_x  latitude_x  longitude_x             timestamp_y  \\\n",
       "0   2019-09-09 06:39:14.097  52.2525362    0.7138794 2019-09-09 06:46:51.199   \n",
       "1   2019-09-09 06:39:14.097  52.2525362    0.7138794 2019-09-09 06:48:59.484   \n",
       "2   2019-09-09 06:40:55.850  52.2525082    0.7138945 2019-09-09 06:46:51.199   \n",
       "3   2019-09-09 06:40:55.850  52.2525082    0.7138945 2019-09-09 06:48:59.484   \n",
       "4   2019-09-09 06:45:08.096  52.2525082    0.7138945 2019-09-09 06:46:51.199   \n",
       "..                      ...         ...          ...                     ...   \n",
       "129 2019-09-14 23:06:03.000  51.4897918   -0.1158281 2019-09-14 22:56:46.626   \n",
       "130 2019-09-15 08:43:11.727  51.4897612   -0.1157898 2019-09-15 08:51:56.791   \n",
       "131 2019-09-15 11:04:09.436  51.5309645   -0.1257586 2019-09-15 11:03:42.148   \n",
       "132 2019-09-15 11:08:13.895  51.5308573   -0.1258008 2019-09-15 11:03:42.148   \n",
       "133 2019-09-15 11:11:33.024  51.5309513   -0.1257283 2019-09-15 11:03:42.148   \n",
       "\n",
       "     latitude_y  longitude_y  count_overlaps_in_int          dist        date  \\\n",
       "0    52.2525704    0.7138478                      2   4.374792876  2019-09-09   \n",
       "1    52.2526024    0.7138553                      2   7.547835973  2019-09-09   \n",
       "2    52.2525704    0.7138478                      2   7.620557842  2019-09-09   \n",
       "3    52.2526024    0.7138553                      2  10.818282131  2019-09-09   \n",
       "4    52.2525704    0.7138478                      2   7.620557842  2019-09-09   \n",
       "..          ...          ...                    ...           ...         ...   \n",
       "129  51.4897181   -0.1156491                      1  14.893132608  2019-09-14   \n",
       "130  51.4898393   -0.1155317                      1  19.921482811  2019-09-15   \n",
       "131  51.5309472   -0.1255995                      4  11.207023835  2019-09-15   \n",
       "132  51.5309472   -0.1255995                      4  17.180610921  2019-09-15   \n",
       "133  51.5309472   -0.1255995                      4   8.949512239  2019-09-15   \n",
       "\n",
       "      time_together  \n",
       "0   03:41:23.498000  \n",
       "1   03:41:23.498000  \n",
       "2   03:41:23.498000  \n",
       "3   03:41:23.498000  \n",
       "4   03:41:23.498000  \n",
       "..              ...  \n",
       "129 04:40:11.522000  \n",
       "130 00:18:32.923000  \n",
       "131 00:18:32.923000  \n",
       "132 00:18:32.923000  \n",
       "133 00:18:32.923000  \n",
       "\n",
       "[134 rows x 10 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "scrolled": false
   },
   "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>timestamp_1</th>\n",
       "      <th>latitude_1</th>\n",
       "      <th>longitude_1</th>\n",
       "      <th>timestamp_2</th>\n",
       "      <th>latitude_2</th>\n",
       "      <th>longitude_2</th>\n",
       "      <th>count_overlapping_hits_by_max_time</th>\n",
       "      <th>distance_in_m</th>\n",
       "      <th>date</th>\n",
       "      <th>time_together</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>4.374792876</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-09-09 06:39:14.097</td>\n",
       "      <td>52.2525362</td>\n",
       "      <td>0.7138794</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>7.547835973</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-09-09 06:40:55.850</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:48:59.484</td>\n",
       "      <td>52.2526024</td>\n",
       "      <td>0.7138553</td>\n",
       "      <td>2</td>\n",
       "      <td>10.818282131</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-09-09 06:45:08.096</td>\n",
       "      <td>52.2525082</td>\n",
       "      <td>0.7138945</td>\n",
       "      <td>2019-09-09 06:46:51.199</td>\n",
       "      <td>52.2525704</td>\n",
       "      <td>0.7138478</td>\n",
       "      <td>2</td>\n",
       "      <td>7.620557842</td>\n",
       "      <td>2019-09-09</td>\n",
       "      <td>03:41:23.498000</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>129</th>\n",
       "      <td>2019-09-14 23:06:03.000</td>\n",
       "      <td>51.4897918</td>\n",
       "      <td>-0.1158281</td>\n",
       "      <td>2019-09-14 22:56:46.626</td>\n",
       "      <td>51.4897181</td>\n",
       "      <td>-0.1156491</td>\n",
       "      <td>1</td>\n",
       "      <td>14.893132608</td>\n",
       "      <td>2019-09-14</td>\n",
       "      <td>04:40:11.522000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>2019-09-15 08:43:11.727</td>\n",
       "      <td>51.4897612</td>\n",
       "      <td>-0.1157898</td>\n",
       "      <td>2019-09-15 08:51:56.791</td>\n",
       "      <td>51.4898393</td>\n",
       "      <td>-0.1155317</td>\n",
       "      <td>1</td>\n",
       "      <td>19.921482811</td>\n",
       "      <td>2019-09-15</td>\n",
       "      <td>00:18:32.923000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>2019-09-15 11:04:09.436</td>\n",
       "      <td>51.5309645</td>\n",
       "      <td>-0.1257586</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>11.207023835</td>\n",
       "      <td>2019-09-15</td>\n",
       "      <td>00:18:32.923000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>2019-09-15 11:08:13.895</td>\n",
       "      <td>51.5308573</td>\n",
       "      <td>-0.1258008</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>17.180610921</td>\n",
       "      <td>2019-09-15</td>\n",
       "      <td>00:18:32.923000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>2019-09-15 11:11:33.024</td>\n",
       "      <td>51.5309513</td>\n",
       "      <td>-0.1257283</td>\n",
       "      <td>2019-09-15 11:03:42.148</td>\n",
       "      <td>51.5309472</td>\n",
       "      <td>-0.1255995</td>\n",
       "      <td>4</td>\n",
       "      <td>8.949512239</td>\n",
       "      <td>2019-09-15</td>\n",
       "      <td>00:18:32.923000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                timestamp_1  latitude_1  longitude_1             timestamp_2  \\\n",
       "0   2019-09-09 06:39:14.097  52.2525362    0.7138794 2019-09-09 06:46:51.199   \n",
       "1   2019-09-09 06:39:14.097  52.2525362    0.7138794 2019-09-09 06:48:59.484   \n",
       "2   2019-09-09 06:40:55.850  52.2525082    0.7138945 2019-09-09 06:46:51.199   \n",
       "3   2019-09-09 06:40:55.850  52.2525082    0.7138945 2019-09-09 06:48:59.484   \n",
       "4   2019-09-09 06:45:08.096  52.2525082    0.7138945 2019-09-09 06:46:51.199   \n",
       "..                      ...         ...          ...                     ...   \n",
       "129 2019-09-14 23:06:03.000  51.4897918   -0.1158281 2019-09-14 22:56:46.626   \n",
       "130 2019-09-15 08:43:11.727  51.4897612   -0.1157898 2019-09-15 08:51:56.791   \n",
       "131 2019-09-15 11:04:09.436  51.5309645   -0.1257586 2019-09-15 11:03:42.148   \n",
       "132 2019-09-15 11:08:13.895  51.5308573   -0.1258008 2019-09-15 11:03:42.148   \n",
       "133 2019-09-15 11:11:33.024  51.5309513   -0.1257283 2019-09-15 11:03:42.148   \n",
       "\n",
       "     latitude_2  longitude_2  count_overlapping_hits_by_max_time  \\\n",
       "0    52.2525704    0.7138478                                   2   \n",
       "1    52.2526024    0.7138553                                   2   \n",
       "2    52.2525704    0.7138478                                   2   \n",
       "3    52.2526024    0.7138553                                   2   \n",
       "4    52.2525704    0.7138478                                   2   \n",
       "..          ...          ...                                 ...   \n",
       "129  51.4897181   -0.1156491                                   1   \n",
       "130  51.4898393   -0.1155317                                   1   \n",
       "131  51.5309472   -0.1255995                                   4   \n",
       "132  51.5309472   -0.1255995                                   4   \n",
       "133  51.5309472   -0.1255995                                   4   \n",
       "\n",
       "     distance_in_m        date   time_together  \n",
       "0      4.374792876  2019-09-09 03:41:23.498000  \n",
       "1      7.547835973  2019-09-09 03:41:23.498000  \n",
       "2      7.620557842  2019-09-09 03:41:23.498000  \n",
       "3     10.818282131  2019-09-09 03:41:23.498000  \n",
       "4      7.620557842  2019-09-09 03:41:23.498000  \n",
       "..             ...         ...             ...  \n",
       "129   14.893132608  2019-09-14 04:40:11.522000  \n",
       "130   19.921482811  2019-09-15 00:18:32.923000  \n",
       "131   11.207023835  2019-09-15 00:18:32.923000  \n",
       "132   17.180610921  2019-09-15 00:18:32.923000  \n",
       "133    8.949512239  2019-09-15 00:18:32.923000  \n",
       "\n",
       "[134 rows x 10 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "output2.rename(columns = {'timestamp_x':'timestamp_1',\n",
    "                       'latitude_x':'latitude_1',\n",
    "                         'longitude_x': 'longitude_1',\n",
    "                         'timestamp_y':'timestamp_2',\n",
    "                       'latitude_y':'latitude_2',\n",
    "                         'longitude_y': 'longitude_2',\n",
    "                         'count_overlaps_in_int': 'count_overlapping_hits_by_max_time',\n",
    "                         'dist': 'distance_in_m',\n",
    "                         'date': 'date',\n",
    "                         'time_together': 'time_together'}, inplace = True)\n",
    "output2"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}