Alternative Cap Compliance.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from datetime import datetime, timedelta\n",
    "import os\n",
    "\n",
    "TZ = \"America/Los_Angeles\"\n",
    "START_TIME = datetime(2019, 1, 1, 0, 0, 0)\n",
    "NUM_DAYS = 20\n",
    "DAYS = [START_TIME + timedelta(days=i) for i in range(0, NUM_DAYS)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## db connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "user, password = os.environ[\"MDS_USER\"], os.environ[\"MDS_PASSWORD\"]\n",
    "db = os.environ[\"MDS_DB\"]\n",
    "host = os.environ[\"POSTGRES_HOSTNAME\"]\n",
    "\n",
    "con = f\"postgresql://{user}:{password}@{host}:5432/{db}\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## fetch data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_sql(\"select * from csm_availability\", con=con)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## null end times should be treated as if the end time is in the future"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"end_time_local\"] = df.end_time_local.fillna(df.end_time_local.max())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## function to compute average vehicles per day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def average_vehicles_per_day(df, day_start_time):\n",
    "    # get the end of the day\n",
    "    day_end_time = day_start_time + timedelta(days=1)\n",
    "\n",
    "    # cap start times to the start of today and end times to the end of today\n",
    "    df[\"capped_start_time_local\"] = df.start_time_local.clip(lower=day_start_time)\n",
    "    df[\"capped_end_time_local\"] = df.end_time_local.clip(upper=day_end_time)\n",
    "    \n",
    "    # compute the duration in seconds of this event today\n",
    "    df[\"duration_in_seconds\"] = (df.capped_end_time_local - df.capped_start_time_local).dt.total_seconds() \n",
    "    # negative values can occur when the event doesn't overlap with today\n",
    "    df_today = df[df.duration_in_seconds > 0].copy()\n",
    "\n",
    "    seconds_in_day = 60 * 60 * 24\n",
    "    # each event contributes this value to average vehicle count\n",
    "    df_today[\"fractional_day_equivalents\"] = df_today.duration_in_seconds / seconds_in_day\n",
    "    \n",
    "    # add it up and round\n",
    "    average_vehicles = df_today.fractional_day_equivalents.sum()\n",
    "    return round(average_vehicles, 1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## run it"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for day in DAYS:\n",
    "    average_vehicles = average_vehicles_per_day(df, day)\n",
    "    print(f\"Average vehicles for {day} is {average_vehicles}\")"
   ]
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}