transloc.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "361e7004",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "from datetime import datetime\n",
    "from datetime import date\n",
    "import matplotlib.pyplot as plt\n",
    "from datetime import timezone, timedelta\n",
    "from scipy.signal import argrelextrema\n",
    "from itertools import product\n",
    "import pandas as pd\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "149e3e14",
   "metadata": {},
   "outputs": [],
   "source": [
    "log_fname = \"/home/sparrow/soft/transloc/log.tsv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e8b9b215",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['10:00am',\n",
       " '10:00pm',\n",
       " '10:10am',\n",
       " '10:15am',\n",
       " '10:20am',\n",
       " '10:25pm',\n",
       " '10:30am',\n",
       " '10:30pm',\n",
       " '10:45am',\n",
       " '10:50pm',\n",
       " '11:00am',\n",
       " '11:00pm',\n",
       " '11:10am',\n",
       " '11:15am',\n",
       " '11:15pm',\n",
       " '11:30am',\n",
       " '11:30pm',\n",
       " '11:35am',\n",
       " '11:40pm',\n",
       " '11:45am',\n",
       " '12:00am',\n",
       " '12:00pm',\n",
       " '12:05am',\n",
       " '12:15pm',\n",
       " '12:25pm',\n",
       " '12:30am',\n",
       " '12:30pm',\n",
       " '12:45pm',\n",
       " '12:50pm',\n",
       " '1:00pm',\n",
       " '1:15pm',\n",
       " '1:30pm',\n",
       " '1:40pm',\n",
       " '1:45pm',\n",
       " '2:00pm',\n",
       " '2:05pm',\n",
       " '2:15pm',\n",
       " '2:30pm',\n",
       " '2:45pm',\n",
       " '2:55pm',\n",
       " '3:00pm',\n",
       " '3:06pm',\n",
       " '3:12pm',\n",
       " '3:15pm',\n",
       " '3:18pm',\n",
       " '3:20pm',\n",
       " '3:24pm',\n",
       " '3:30pm',\n",
       " '3:36pm',\n",
       " '3:42pm',\n",
       " '3:45pm',\n",
       " '3:48pm',\n",
       " '3:54pm',\n",
       " '4:00pm',\n",
       " '4:06pm',\n",
       " '4:10pm',\n",
       " '4:12pm',\n",
       " '4:18pm',\n",
       " '4:24pm',\n",
       " '4:30pm',\n",
       " '4:35pm',\n",
       " '4:36pm',\n",
       " '4:42pm',\n",
       " '4:48pm',\n",
       " '4:54pm',\n",
       " '5:00pm',\n",
       " '5:06pm',\n",
       " '5:12pm',\n",
       " '5:18pm',\n",
       " '5:24pm',\n",
       " '5:25pm',\n",
       " '5:30pm',\n",
       " '5:36pm',\n",
       " '5:42pm',\n",
       " '5:48pm',\n",
       " '5:50pm',\n",
       " '5:54pm',\n",
       " '6:00am',\n",
       " '6:00pm',\n",
       " '6:10pm',\n",
       " '6:15am',\n",
       " '6:15pm',\n",
       " '6:20pm',\n",
       " '6:30am',\n",
       " '6:30pm',\n",
       " '6:40pm',\n",
       " '6:45am',\n",
       " '6:45pm',\n",
       " '6:50pm',\n",
       " '7:00am',\n",
       " '7:00pm',\n",
       " '7:05pm',\n",
       " '7:15am',\n",
       " '7:15pm',\n",
       " '7:25am',\n",
       " '7:30am',\n",
       " '7:30pm',\n",
       " '7:36am',\n",
       " '7:42am',\n",
       " '7:45pm',\n",
       " '7:48am',\n",
       " '7:50am',\n",
       " '7:54am',\n",
       " '7:55pm',\n",
       " '8:00am',\n",
       " '8:00pm',\n",
       " '8:06am',\n",
       " '8:12am',\n",
       " '8:15am',\n",
       " '8:15pm',\n",
       " '8:18am',\n",
       " '8:20pm',\n",
       " '8:24am',\n",
       " '8:30am',\n",
       " '8:30pm',\n",
       " '8:36am',\n",
       " '8:40am',\n",
       " '8:42am',\n",
       " '8:45pm',\n",
       " '8:48am',\n",
       " '8:54am',\n",
       " '9:00am',\n",
       " '9:00pm',\n",
       " '9:05am',\n",
       " '9:06am',\n",
       " '9:10am',\n",
       " '9:10pm',\n",
       " '9:12am',\n",
       " '9:18am',\n",
       " '9:20am',\n",
       " '9:24am',\n",
       " '9:30am',\n",
       " '9:30pm',\n",
       " '9:35pm',\n",
       " '9:40am',\n",
       " '9:50am',\n",
       " '9:55am']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "l = [\"6:00am\",\"6:15am\",\"6:30am\",\"6:45am\",\"7:00am\",\"7:15am\",\"7:30am\",\"7:36am\",\"7:42am\",\"7:48am\",\"7:54am\",\"8:00am\",\"8:06am\",\"8:12am\",\"8:18am\",\"8:24am\",\"8:30am\",\"8:36am\",\"8:42am\",\"8:48am\",\"8:54am\",\"9:00am\",\"9:10am\",\"9:20am\",\"9:30am\",\"9:40am\",\"9:50am\",\"10:00am\",\"10:15am\",\"10:30am\",\"10:45am\",\"11:00am\",\"11:15am\",\"11:30am\",\"11:45am\",\"12:00pm\",\"12:15pm\",\"12:30pm\",\"12:45pm\",\"1:00pm\",\"1:15pm\",\"1:30pm\",\"1:45pm\",\"2:00pm\",\"2:15pm\",\"2:30pm\",\"2:45pm\",\"3:00pm\",\"3:06pm\",\"3:12pm\",\"3:18pm\",\"3:24pm\",\"3:30pm\",\"3:36pm\",\"3:42pm\",\"3:48pm\",\"3:54pm\",\"4:00pm\",\"4:06pm\",\"4:12pm\",\"4:18pm\",\"4:24pm\",\"4:30pm\",\"4:36pm\",\"4:42pm\",\"4:48pm\",\"4:54pm\",\"5:00pm\",\"5:06pm\",\"5:12pm\",\"5:18pm\",\"5:24pm\",\"5:30pm\",\"5:36pm\",\"5:42pm\",\"5:48pm\",\"5:54pm\",\"6:00pm\",\"6:10pm\",\"6:20pm\",\"6:30pm\",\"6:45pm\",\"7:00pm\",\"7:15pm\",\"7:30pm\",\"7:45pm\",\"8:00pm\",\"8:15pm\",\"8:30pm\",\"9:00pm\",\"9:30pm\",\"10:00pm\",\"10:30pm\",\"11:00pm\",\"11:30pm\",\"12:00am\",\"12:30am\",\"6:00am\",\"6:15am\",\"6:30am\",\"6:45am\",\"7:00am\",\"7:15am\",\"7:30am\",\"7:36am\",\"7:42am\",\"7:48am\",\"7:54am\",\"8:00am\",\"8:06am\",\"8:12am\",\"8:18am\",\"8:24am\",\"8:30am\",\"8:36am\",\"8:42am\",\"8:48am\",\"8:54am\",\"9:00am\",\"9:06am\",\"9:12am\",\"9:18am\",\"9:24am\",\"9:30am\",\"9:40am\",\"9:50am\",\"10:00am\",\"10:10am\",\"10:20am\",\"10:30am\",\"10:45am\",\"11:00am\",\"11:15am\",\"11:30am\",\"11:45am\",\"12:00pm\",\"12:15pm\",\"12:30pm\",\"12:45pm\",\"1:00pm\",\"1:15pm\",\"1:30pm\",\"1:45pm\",\"2:00pm\",\"2:15pm\",\"2:30pm\",\"2:45pm\",\"3:00pm\",\"3:15pm\",\"3:30pm\",\"3:36pm\",\"3:42pm\",\"3:48pm\",\"3:54pm\",\"4:00pm\",\"4:06pm\",\"4:12pm\",\"4:18pm\",\"4:24pm\",\"4:30pm\",\"4:36pm\",\"4:42pm\",\"4:48pm\",\"4:54pm\",\"5:00pm\",\"5:06pm\",\"5:12pm\",\"5:18pm\",\"5:24pm\",\"5:30pm\",\"5:36pm\",\"5:42pm\",\"5:48pm\",\"5:54pm\",\"6:00pm\",\"6:10pm\",\"6:20pm\",\"6:30pm\",\"6:40pm\",\"6:50pm\",\"7:00pm\",\"7:15pm\",\"7:30pm\",\"7:45pm\",\"8:00pm\",\"8:15pm\",\"8:30pm\",\"8:45pm\",\"9:00pm\",\"9:30pm\",\"10:00pm\",\"10:30pm\",\"11:00pm\",\"11:30pm\",\"12:00am\",\"12:30am\",\"7:25am\",\"7:50am\",\"8:15am\",\"8:40am\",\"9:05am\",\"9:30am\",\"9:55am\",\"10:20am\",\"10:45am\",\"11:10am\",\"11:35am\",\"12:00pm\",\"12:25pm\",\"12:50pm\",\"1:15pm\",\"1:40pm\",\"2:05pm\",\"2:30pm\",\"2:55pm\",\"3:20pm\",\"3:45pm\",\"4:10pm\",\"4:35pm\",\"5:00pm\",\"5:25pm\",\"5:50pm\",\"6:15pm\",\"6:40pm\",\"7:05pm\",\"7:30pm\",\"7:55pm\",\"8:20pm\",\"8:45pm\",\"9:10pm\",\"9:35pm\",\"10:00pm\",\"10:25pm\",\"10:50pm\",\"11:15pm\",\"11:40pm\",\"12:05am\",\"12:30am\",\"11:10am\",\"11:35am\",\"12:00pm\",\"12:25pm\",\"12:50pm\",\"1:15pm\",\"1:40pm\",\"2:05pm\",\"2:30pm\",\"2:55pm\",\"3:20pm\",\"3:45pm\",\"4:10pm\",\"4:35pm\",\"5:00pm\",\"5:25pm\",\"5:50pm\",\"6:15pm\",\"6:40pm\",\"7:05pm\",\"7:30pm\",\"7:55pm\",\"8:20pm\",\"8:45pm\",\"9:10pm\",\"9:35pm\",\"10:00pm\",\"10:25pm\",\"10:50pm\",\"11:15pm\",\"11:40pm\"]\n",
    "sorted(list(set(l)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8d551407",
   "metadata": {},
   "outputs": [],
   "source": [
    "format = '%I:%M%p'\n",
    "\n",
    "broadway_times_weekday = [\"6:00am\",\"6:15am\",\"6:30am\",\"6:45am\",\"7:00am\",\"7:15am\",\"7:30am\",\"7:36am\",\"7:42am\",\n",
    "                          \"7:48am\",\"7:54am\",\"8:00am\",\"8:06am\",\"8:12am\",\"8:18am\",\"8:24am\",\"8:30am\",\"8:36am\",\n",
    "                          \"8:42am\",\"8:48am\",\"8:54am\",\"9:00am\",\"9:10am\",\"9:20am\",\"9:30am\",\"9:40am\",\"9:50am\",\n",
    "                          \"10:00am\",\"10:15am\",\"10:30am\",\"10:45am\",\"11:00am\",\"11:15am\",\"11:30am\",\"11:45am\",\n",
    "                          \"12:00pm\",\"12:15pm\",\"12:30pm\",\"12:45pm\",\"1:00pm\",\"1:15pm\",\"1:30pm\",\"1:45pm\",\"2:00pm\",\n",
    "                          \"2:15pm\",\"2:30pm\",\"2:45pm\",\"3:00pm\",\"3:06pm\",\"3:12pm\",\"3:18pm\",\"3:24pm\",\"3:30pm\",\n",
    "                          \"3:36pm\",\"3:42pm\",\"3:48pm\",\"3:54pm\",\"4:00pm\",\"4:06pm\",\"4:12pm\",\"4:18pm\",\"4:24pm\",\n",
    "                          \"4:30pm\",\"4:36pm\",\"4:42pm\",\"4:48pm\",\"4:54pm\",\"5:00pm\",\"5:06pm\",\"5:12pm\",\"5:18pm\",\n",
    "                          \"5:24pm\",\"5:30pm\",\"5:36pm\",\"5:42pm\",\"5:48pm\",\"5:54pm\",\"6:00pm\",\"6:10pm\",\"6:20pm\",\n",
    "                          \"6:30pm\",\"6:45pm\",\"7:00pm\",\"7:15pm\",\"7:30pm\",\"7:45pm\",\"8:00pm\",\"8:15pm\",\"8:30pm\",\n",
    "                          \"9:00pm\",\"9:30pm\",\"10:00pm\",\"10:30pm\",\"11:00pm\",\"11:30pm\",\"12:00am\",\"12:30am\"]\n",
    "interfaith_times_weekday = [\"6:00am\",\"6:15am\",\"6:30am\",\"6:45am\",\"7:00am\",\"7:15am\",\"7:30am\",\"7:36am\",\"7:42am\",\n",
    "                            \"7:48am\",\"7:54am\",\"8:00am\",\"8:06am\",\"8:12am\",\"8:18am\",\"8:24am\",\"8:30am\",\"8:36am\",\n",
    "                            \"8:42am\",\"8:48am\",\"8:54am\",\"9:00am\",\"9:06am\",\"9:12am\",\"9:18am\",\"9:24am\",\"9:30am\",\n",
    "                            \"9:40am\",\"9:50am\",\"10:00am\",\"10:10am\",\"10:20am\",\"10:30am\",\"10:45am\",\"11:00am\",\n",
    "                            \"11:15am\",\"11:30am\",\"11:45am\",\"12:00pm\",\"12:15pm\",\"12:30pm\",\"12:45pm\",\"1:00pm\",\n",
    "                            \"1:15pm\",\"1:30pm\",\"1:45pm\",\"2:00pm\",\"2:15pm\",\"2:30pm\",\"2:45pm\",\"3:00pm\",\"3:15pm\",\n",
    "                            \"3:30pm\",\"3:36pm\",\"3:42pm\",\"3:48pm\",\"3:54pm\",\"4:00pm\",\"4:06pm\",\"4:12pm\",\"4:18pm\",\n",
    "                            \"4:24pm\",\"4:30pm\",\"4:36pm\",\"4:42pm\",\"4:48pm\",\"4:54pm\",\"5:00pm\",\"5:06pm\",\"5:12pm\",\n",
    "                            \"5:18pm\",\"5:24pm\",\"5:30pm\",\"5:36pm\",\"5:42pm\",\"5:48pm\",\"5:54pm\",\"6:00pm\",\"6:10pm\",\n",
    "                            \"6:20pm\",\"6:30pm\",\"6:40pm\",\"6:50pm\",\"7:00pm\",\"7:15pm\",\"7:30pm\",\"7:45pm\",\"8:00pm\",\n",
    "                            \"8:15pm\",\"8:30pm\",\"8:45pm\",\"9:00pm\",\"9:30pm\",\"10:00pm\",\"10:30pm\",\"11:00pm\",\"11:30pm\",\n",
    "                            \"12:00am\",\"12:30am\"]\n",
    "times_sat = [\"7:25am\",\"7:50am\",\"8:15am\",\"8:40am\",\"9:05am\",\"9:30am\",\"9:55am\",\"10:20am\",\"10:45am\",\"11:10am\",\n",
    "             \"11:35am\",\"12:00pm\",\"12:25pm\",\"12:50pm\",\"1:15pm\",\"1:40pm\",\"2:05pm\",\"2:30pm\",\"2:55pm\",\"3:20pm\",\n",
    "             \"3:45pm\",\"4:10pm\",\"4:35pm\",\"5:00pm\",\"5:25pm\",\"5:50pm\",\"6:15pm\",\"6:40pm\",\"7:05pm\",\"7:30pm\",\n",
    "             \"7:55pm\",\"8:20pm\",\"8:45pm\",\"9:10pm\",\"9:35pm\",\"10:00pm\",\"10:25pm\",\"10:50pm\",\"11:15pm\",\"11:40pm\",\n",
    "             \"12:05am\",\"12:30am\"]\n",
    "times_sun = [\"11:10am\",\"11:35am\",\"12:00pm\",\"12:25pm\",\"12:50pm\",\"1:15pm\",\"1:40pm\",\"2:05pm\",\"2:30pm\",\"2:55pm\",\n",
    "             \"3:20pm\",\"3:45pm\",\"4:10pm\",\"4:35pm\",\"5:00pm\",\"5:25pm\",\"5:50pm\",\"6:15pm\",\"6:40pm\",\"7:05pm\",\n",
    "             \"7:30pm\",\"7:55pm\",\"8:20pm\",\"8:45pm\",\"9:10pm\",\"9:35pm\",\"10:00pm\",\"10:25pm\",\"10:50pm\",\"11:15pm\",\n",
    "             \"11:40pm\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "26d45735",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1037648\n"
     ]
    }
   ],
   "source": [
    "log = pd.read_csv(log_fname,sep=\"\\t\",names=[\"sid\",\"vid\",\"timestamp\",\"dist\"])\n",
    "log[\"time\"] = pd.to_datetime(log[\"timestamp\"],unit='ms',utc=True).dt.tz_convert(\"US/Eastern\")\n",
    "print(len(log))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "eeeeff91",
   "metadata": {},
   "outputs": [],
   "source": [
    "n=100\n",
    "max_time_diff = 1200000 # 20 minutes in unix time\n",
    "min_max_distance_between_stops = 3500 # distance between the two stops\n",
    "min_distance_to_stop = 250\n",
    "\n",
    "name2sid = {\"Broadway\":4177254,\n",
    "            \"Interfaith\":4265754}\n",
    "sid2name = {4177254:\"Broadway\",\n",
    "            4265754:\"Interfaith\"}\n",
    "\n",
    "stops = {4177254:{\"name\":\"Broadway\",\n",
    "                  \"times\":{\"weekday\":[datetime.time(datetime.strptime(x,format)) for x in broadway_times_weekday],\n",
    "                           \"sat\":[datetime.time(datetime.strptime(x,format)) for x in times_sat],\n",
    "                           \"sun\":[datetime.time(datetime.strptime(x,format)) for x in times_sun]}},\n",
    "         4265754:{\"name\":\"Interfaith\",\n",
    "                  \"times\":{\"weekday\":[datetime.time(datetime.strptime(x,format)) for x in interfaith_times_weekday],\n",
    "                           \"sat\":[datetime.time(datetime.strptime(x,format)) for x in times_sat],\n",
    "                           \"sun\":[datetime.time(datetime.strptime(x,format)) for x in times_sun]}}}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4b512a58",
   "metadata": {},
   "outputs": [],
   "source": [
    "days = dict()\n",
    "\n",
    "all_stops_lst = []\n",
    "\n",
    "glog = log.groupby(by=[\"sid\",\"vid\"])\n",
    "grp = \"\"\n",
    "for name,grp in glog:\n",
    "    # Find local peaks\n",
    "#     grp['min'] = grp.dist[(grp.dist.shift(1) > grp.dist) & (grp.dist.shift(-1) > grp.dist) & (grp.dist<=100)]\n",
    "    \n",
    "    grp['min'] = grp.iloc[argrelextrema(grp.dist.values, np.less_equal,\n",
    "                    order=n)[0]]['dist']\n",
    "    grp[\"min\"] = grp[~(grp[\"min\"].isna())&(grp[\"min\"]<min_distance_to_stop)][\"min\"]\n",
    "    \n",
    "    \n",
    "    # remove duplicates close in time\n",
    "    sub = grp[~(grp[\"min\"].isna())].reset_index(drop=True)\n",
    "\n",
    "    sub[\"group_diff\"] = sub.sort_values(\"timestamp\")[\"timestamp\"]\\\n",
    "                         .diff()\\\n",
    "                         .gt(max_time_diff)\\\n",
    "                         .cumsum()\n",
    "\n",
    "    sub_grp_diff = pd.DataFrame(sub.groupby(by=\"group_diff\").max()).reset_index()\n",
    "    grp[\"min\"] = grp[~(grp[\"min\"].isna())&(grp[\"timestamp\"].isin(sub_grp_diff[\"timestamp\"]))][\"min\"]\n",
    "    \n",
    "    \n",
    "    # we probably should also make sure that the bus actually went somewhere meaningful \n",
    "    # and not just circled around without passengers\n",
    "    \n",
    "    # can be done as:\n",
    "    # compute distance between the two endpoints\n",
    "    # get maximum distance between two inferred stops\n",
    "    # if not within n% of the total route distance\n",
    "    \n",
    "    idxs = list(grp[~(grp[\"min\"].isna())].index)\n",
    "    \n",
    "    good_idxs = []\n",
    "    \n",
    "    if len(idxs)>=1:\n",
    "        good_idxs.append(idxs[0])\n",
    "\n",
    "    if len(idxs)>1:\n",
    "        for i in idxs[1:]:\n",
    "            max_dist_between = grp.loc[good_idxs[-1]:i][\"dist\"].max()\n",
    "            if max_dist_between<=min_max_distance_between_stops:\n",
    "                good_idxs[-1] = i\n",
    "            else:\n",
    "                good_idxs.append(i)\n",
    "                \n",
    "    grp[\"min\"] = grp[~(grp[\"min\"].isna())&(grp.index.isin(good_idxs))][\"min\"]\n",
    "    min_dist = grp[~(grp[\"min\"].isna())&(grp.index.isin(good_idxs))][\"min\"].iloc[0]\n",
    "    grp[\"time\"] = pd.to_datetime(grp[~(grp[\"min\"].isna())][\"timestamp\"],unit='ms',utc=True).dt.tz_convert(\"US/Eastern\")\n",
    "    \n",
    "    # add times\n",
    "    for t in list(grp[~(grp[\"min\"].isna())][\"time\"]):\n",
    "        if t.date() not in days:\n",
    "            if t.weekday() <= 4: # weekday\n",
    "                days[t.date()] = {name2sid[\"Broadway\"]:{\"scheduled\":[x for x in stops[name2sid[\"Broadway\"]][\"times\"][\"weekday\"]],\n",
    "                                                        \"observed\":[]},\n",
    "                                  name2sid[\"Interfaith\"]:{\"scheduled\":[x for x in stops[name2sid[\"Interfaith\"]][\"times\"][\"weekday\"]],\n",
    "                                                          \"observed\":[]}}\n",
    "            else:\n",
    "                if t.weekday() == 5: # Saturday\n",
    "                    days[t.date()] = {name2sid[\"Broadway\"]:{\"scheduled\":[x for x in stops[name2sid[\"Broadway\"]][\"times\"][\"sat\"]],\n",
    "                                                            \"observed\":[]},\n",
    "                                      name2sid[\"Interfaith\"]:{\"scheduled\":[x for x in stops[name2sid[\"Interfaith\"]][\"times\"][\"sat\"]],\n",
    "                                                              \"observed\":[]}}\n",
    "                if t.weekday() == 6: # Sunday\n",
    "                    days[t.date()] = {name2sid[\"Broadway\"]:{\"scheduled\":[x for x in stops[name2sid[\"Broadway\"]][\"times\"][\"sun\"]],\n",
    "                                                            \"observed\":[]},\n",
    "                                      name2sid[\"Interfaith\"]:{\"scheduled\":[x for x in stops[name2sid[\"Interfaith\"]][\"times\"][\"sun\"]],\n",
    "                                                              \"observed\":[]}}\n",
    "\n",
    "        # add days\n",
    "        days[t.date()][name[0]][\"observed\"].append(t.time())\n",
    "        all_stops_lst.append([datetime.combine(t.date(),t.time()),min_dist,sid2name[name[0]],name[1]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fcd69ecc",
   "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>time</th>\n",
       "      <th>dist</th>\n",
       "      <th>stop</th>\n",
       "      <th>vid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-11-12 07:50:42</td>\n",
       "      <td>2.666306</td>\n",
       "      <td>Broadway</td>\n",
       "      <td>4019313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-11-12 08:42:06</td>\n",
       "      <td>2.666306</td>\n",
       "      <td>Broadway</td>\n",
       "      <td>4019313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-11-12 09:32:24</td>\n",
       "      <td>2.666306</td>\n",
       "      <td>Broadway</td>\n",
       "      <td>4019313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-11-12 10:22:00</td>\n",
       "      <td>2.666306</td>\n",
       "      <td>Broadway</td>\n",
       "      <td>4019313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-11-12 11:24:06</td>\n",
       "      <td>2.666306</td>\n",
       "      <td>Broadway</td>\n",
       "      <td>4019313</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 time      dist      stop      vid\n",
       "0 2022-11-12 07:50:42  2.666306  Broadway  4019313\n",
       "1 2022-11-12 08:42:06  2.666306  Broadway  4019313\n",
       "2 2022-11-12 09:32:24  2.666306  Broadway  4019313\n",
       "3 2022-11-12 10:22:00  2.666306  Broadway  4019313\n",
       "4 2022-11-12 11:24:06  2.666306  Broadway  4019313"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "observed_df = pd.DataFrame(all_stops_lst)\n",
    "observed_df.columns = [\"time\",\"dist\",\"stop\",\"vid\"]\n",
    "\n",
    "\n",
    "observed_df = observed_df[~(observed_df['time'].dt.date==min(observed_df['time'].dt.date))&\n",
    "                        ~(observed_df['time'].dt.date==max(observed_df['time'].dt.date))].reset_index(drop=True)\n",
    "\n",
    "observed_df.to_csv(log_fname+\".observed.tsv\",sep=\"\\t\",index=False)\n",
    "observed_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "9a77bcf9",
   "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>scheduled</th>\n",
       "      <th>observed</th>\n",
       "      <th>observed_all</th>\n",
       "      <th>stop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-11-12 18:40:00</td>\n",
       "      <td>2022-11-12 18:40:20</td>\n",
       "      <td>2022-11-12 18:40:20</td>\n",
       "      <td>Broadway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-11-12 20:20:00</td>\n",
       "      <td>2022-11-12 20:20:21</td>\n",
       "      <td>2022-11-12 20:20:21</td>\n",
       "      <td>Broadway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-11-12 07:25:00</td>\n",
       "      <td>2022-11-12 07:25:29</td>\n",
       "      <td>2022-11-12 07:25:29</td>\n",
       "      <td>Broadway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-11-12 08:15:00</td>\n",
       "      <td>2022-11-12 08:15:30</td>\n",
       "      <td>2022-11-12 08:15:30</td>\n",
       "      <td>Broadway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-11-12 00:30:00</td>\n",
       "      <td>2022-11-12 00:30:30</td>\n",
       "      <td>2022-11-12 00:30:30</td>\n",
       "      <td>Broadway</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            scheduled             observed        observed_all      stop\n",
       "0 2022-11-12 18:40:00  2022-11-12 18:40:20 2022-11-12 18:40:20  Broadway\n",
       "1 2022-11-12 20:20:00  2022-11-12 20:20:21 2022-11-12 20:20:21  Broadway\n",
       "2 2022-11-12 07:25:00  2022-11-12 07:25:29 2022-11-12 07:25:29  Broadway\n",
       "3 2022-11-12 08:15:00  2022-11-12 08:15:30 2022-11-12 08:15:30  Broadway\n",
       "4 2022-11-12 00:30:00  2022-11-12 00:30:30 2022-11-12 00:30:30  Broadway"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# recursive function to find closest times\n",
    "# 1. find the closest pair from the two lists that meets criteria\n",
    "# 2. return list without the two timepoints\n",
    "\n",
    "def get_delta(t1,t2):\n",
    "    ct1 = datetime.combine(date.today(),t1)\n",
    "    ct2 = datetime.combine(date.today(),t2)\n",
    "    td = (ct1-ct2).total_seconds()\n",
    "    return td\n",
    "    \n",
    "def _closest(l1,l2,res,max_delta,recycle=False,future_only=False):\n",
    "    if len(l1)==0 or len(l2)==0:\n",
    "        return res\n",
    "    p = None\n",
    "    if not future_only:\n",
    "        p = product(l1, l2)\n",
    "    else:\n",
    "        p = [x for x in list(product(l1,l2)) if x[0]<=x[1]]\n",
    "        if len(p)==0:\n",
    "            return res\n",
    "        \n",
    "    cl = min(p, key=lambda t: abs(get_delta(t[0],t[1])))\n",
    "    if abs(get_delta(cl[0],cl[1]))>max_delta:\n",
    "        for x in l1:\n",
    "            res.append((x,0))\n",
    "        return res\n",
    "    \n",
    "    else:\n",
    "        res.append(cl)\n",
    "        t1 = [x for x in l1]\n",
    "        t1.remove(cl[0])\n",
    "        t2 = [x for x in l2]\n",
    "        if not recycle:\n",
    "            t2.remove(cl[1])\n",
    "        _closest(t1,t2,res,max_delta,recycle,future_only)\n",
    "        \n",
    "    \n",
    "def print_res(res):\n",
    "    for r in res:\n",
    "        try:\n",
    "            print(r[0].strftime(format),r[1].strftime(format))\n",
    "        except:\n",
    "            print(r[0].strftime(format),None)\n",
    "\n",
    "# now for each day and stop we need to couple datapoints and get some data out:\n",
    "# 1. number missed\n",
    "# 2. total time off from the schedule\n",
    "\n",
    "closest_df = pd.DataFrame([])\n",
    "closest_all_df = pd.DataFrame([])\n",
    "\n",
    "for day,v in days.items():\n",
    "    for sid,v2 in v.items():\n",
    "        # couple times\n",
    "        res = []\n",
    "        _closest(v2[\"scheduled\"],v2[\"observed\"],res,600)\n",
    "        \n",
    "        res2 = []\n",
    "        for x0,x1 in res:\n",
    "            t1 = datetime.combine(day,x0)\n",
    "            t2 = \"1-1-1 01:01:01\"\n",
    "            t2a = \"1-1-1 01:01:01\"\n",
    "            if x1 != 0:\n",
    "                t2 = datetime.combine(day,x1)\n",
    "                t2a = datetime.combine(day,x1)\n",
    "            else:\n",
    "                # what is the closest time in future?\n",
    "                tr = []\n",
    "                _closest([x0],v2[\"observed\"],tr,100000000,True,True)\n",
    "                if len(tr)>0:\n",
    "                    t2a = datetime.combine(day,tr[0][1])\n",
    "                else:\n",
    "                    # nothing found add 1 hour\n",
    "                    t2a = t1 + timedelta(hours=1)\n",
    "            res2.append((t1,t2,t2a))\n",
    "        \n",
    "        tmp = pd.DataFrame(res2)\n",
    "        tmp.columns = [\"scheduled\",\"observed\",\"observed_all\"]\n",
    "        tmp[\"stop\"] = sid2name[sid]\n",
    "        closest_df = pd.concat([closest_df,tmp],axis=0)\n",
    "\n",
    "# remove first and last dates\n",
    "closest_df = closest_df[~(closest_df['scheduled'].dt.date==min(closest_df['scheduled'].dt.date))&\n",
    "                        ~(closest_df['scheduled'].dt.date==max(closest_df['scheduled'].dt.date))].reset_index(drop=True)\n",
    "closest_df.to_csv(log_fname+\".closest.tsv\",sep=\"\\t\",index=False)\n",
    "closest_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "6f3aecae",
   "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>sid</th>\n",
       "      <th>vid</th>\n",
       "      <th>time</th>\n",
       "      <th>dist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Broadway</td>\n",
       "      <td>4019315</td>\n",
       "      <td>2022-11-12 00:00:02-05:00</td>\n",
       "      <td>4277.045330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Interfaith</td>\n",
       "      <td>4019315</td>\n",
       "      <td>2022-11-12 00:00:02-05:00</td>\n",
       "      <td>5.893331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Broadway</td>\n",
       "      <td>4019321</td>\n",
       "      <td>2022-11-12 00:00:12-05:00</td>\n",
       "      <td>6367.668294</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Interfaith</td>\n",
       "      <td>4019321</td>\n",
       "      <td>2022-11-12 00:00:12-05:00</td>\n",
       "      <td>10090.864038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Broadway</td>\n",
       "      <td>4019315</td>\n",
       "      <td>2022-11-12 00:00:12-05:00</td>\n",
       "      <td>4270.014428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887205</th>\n",
       "      <td>Interfaith</td>\n",
       "      <td>4019317</td>\n",
       "      <td>2022-11-20 23:50:02-05:00</td>\n",
       "      <td>4020.437809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887206</th>\n",
       "      <td>Broadway</td>\n",
       "      <td>4019317</td>\n",
       "      <td>2022-11-20 23:50:08-05:00</td>\n",
       "      <td>1718.921788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887207</th>\n",
       "      <td>Interfaith</td>\n",
       "      <td>4019317</td>\n",
       "      <td>2022-11-20 23:50:08-05:00</td>\n",
       "      <td>4087.692680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887208</th>\n",
       "      <td>Broadway</td>\n",
       "      <td>4019317</td>\n",
       "      <td>2022-11-20 23:50:10-05:00</td>\n",
       "      <td>1722.864754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887209</th>\n",
       "      <td>Interfaith</td>\n",
       "      <td>4019317</td>\n",
       "      <td>2022-11-20 23:50:10-05:00</td>\n",
       "      <td>4111.450078</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>887210 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               sid      vid                      time          dist\n",
       "0         Broadway  4019315 2022-11-12 00:00:02-05:00   4277.045330\n",
       "1       Interfaith  4019315 2022-11-12 00:00:02-05:00      5.893331\n",
       "2         Broadway  4019321 2022-11-12 00:00:12-05:00   6367.668294\n",
       "3       Interfaith  4019321 2022-11-12 00:00:12-05:00  10090.864038\n",
       "4         Broadway  4019315 2022-11-12 00:00:12-05:00   4270.014428\n",
       "...            ...      ...                       ...           ...\n",
       "887205  Interfaith  4019317 2022-11-20 23:50:02-05:00   4020.437809\n",
       "887206    Broadway  4019317 2022-11-20 23:50:08-05:00   1718.921788\n",
       "887207  Interfaith  4019317 2022-11-20 23:50:08-05:00   4087.692680\n",
       "887208    Broadway  4019317 2022-11-20 23:50:10-05:00   1722.864754\n",
       "887209  Interfaith  4019317 2022-11-20 23:50:10-05:00   4111.450078\n",
       "\n",
       "[887210 rows x 4 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rlog = log.copy(deep=True)\n",
    "rlog.replace({\"sid\":sid2name},inplace=True)\n",
    "rlog = rlog[[\"sid\",\"vid\",\"time\",\"dist\"]]\n",
    "\n",
    "rlog = rlog[~(rlog['time'].dt.date==min(rlog['time'].dt.date))&\n",
    "            ~(rlog['time'].dt.date==max(rlog['time'].dt.date))].reset_index(drop=True)\n",
    "rlog.to_csv(log_fname+\".edited.tsv\",sep=\"\\t\",index=False)\n",
    "rlog"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "918e281e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c2c53a3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfa52220",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}