Examples/Build service measures.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Build serice measures\n",
    "\n",
    "This notebook constructs three station-level measures of service from the raw data:\n",
    "- frequency the station had no bikes or no docks during morning or evening hours\n",
    "- median duation of instances with no bikes or no docks\n",
    "- portion of docks holding broken or out-of-service bikes\n",
    "\n",
    "Before running this notebook, you will need to record data and construct `dataset.parquet` and `stations_geo.geojson` with [`Build dataset`](../Build%20dataset.ipynb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = pd.read_parquet('../dataset.parquet') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_geo = gpd.read_file('../stations_geo.geojson').set_index('station_id')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Morning or evening frequency unavailable"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "subset to times of day/day of week."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "am_rush = (\n",
    "    dataset\n",
    "    .unstack(level='station_id')\n",
    "    .between_time('07:00','10:59')\n",
    "    .stack()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "evening = (\n",
    "    dataset\n",
    "    .unstack(level='station_id')\n",
    "    .between_time('16:00','21:59')\n",
    "    .stack()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "am_n_samples = am_rush.reset_index()['last_updated'].nunique()\n",
    "\n",
    "am_n_samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "evening_n_samples = evening.reset_index()['last_updated'].nunique()\n",
    "\n",
    "evening_n_samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_geo['freq_am_or_evening_no_bikes_or_no_docks'] = (\n",
    "    (\n",
    "        (\n",
    "            am_rush\n",
    "            [\n",
    "                (am_rush['num_bikes_available'] == 0) \n",
    "                | (am_rush['num_docks_available'] == 0) \n",
    "            ]\n",
    "            .index.get_level_values('station_id')\n",
    "            .value_counts()\n",
    "            .reindex(stations_geo.index)\n",
    "            .fillna(0)\n",
    "        ).add(\n",
    "            evening\n",
    "            [\n",
    "                (evening['num_bikes_available'] == 0)\n",
    "                | (evening['num_docks_available'] == 0) \n",
    "            ]\n",
    "            .index.get_level_values('station_id')\n",
    "            .value_counts()\n",
    "            .reindex(stations_geo.index)\n",
    "            .fillna(0)\n",
    "        )\n",
    "    ).div(\n",
    "        (am_n_samples + evening_n_samples)\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Docks with broken bikes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We found that there are a few instances (across many stations and many times) when the number of bikes exceeds station capacity. That should not be allowed. So dropping those instances for computation involving capacity. \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "dataset['pct_of_docks_w_disabled_bikes'] = (\n",
    "    (dataset['num_bikes_disabled'].div(dataset['capacity']))\n",
    "    .where(\n",
    "        dataset['capacity'] >= (dataset['num_bikes_available'] + dataset['num_bikes_disabled'])\n",
    "    )\n",
    "    .replace([-np.inf,np.inf],np.nan)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_geo = (\n",
    "    stations_geo\n",
    "    .join(\n",
    "        (\n",
    "            dataset\n",
    "            .groupby('station_id')\n",
    "            ['pct_of_docks_w_disabled_bikes']\n",
    "            .agg(['median','mean'])\n",
    "            .add_prefix('pct_of_docks_w_disabled_bikes_')\n",
    "        ),\n",
    "        how='left'   \n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Duration zero docks or zero bikes in 6:00am-midnight hours"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataset = dataset.reorder_levels(['last_updated','station_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "daytime_data = (\n",
    "    dataset\n",
    "    .unstack(level='station_id')\n",
    "    .between_time('06:00','23:59')\n",
    "    .stack()\n",
    "    .reorder_levels(['station_id','last_updated']).sort_index()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- loop through each sample at each station. \n",
    "- start a duration counter when there are zero bikes or zero docks, when there were not zero in the previous instance (separat ecounters for bikes and docks)\n",
    "- stop the counter if there is a gap in the samples > 40 minutes. \n",
    "- or, stop the counter when there is >0 docks or bikes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "zero_daytime_dock_durations = []\n",
    "zero_daytime_bike_durations = []\n",
    "ended_due_to_data_gap = []\n",
    "\n",
    "for station_id in tqdm(stations_geo.index):\n",
    "    \n",
    "    station_subset = (\n",
    "        (\n",
    "            daytime_data.loc[station_id]\n",
    "            [[\n",
    "                'num_bikes_available',\n",
    "                'num_docks_available',\n",
    "                'is_renting',\n",
    "                'is_returning'\n",
    "            ]]\n",
    "            .reset_index()\n",
    "            .assign(\n",
    "                previous_time = lambda row: row.shift(1)['last_updated'],\n",
    "                previous_docks = lambda row: row.shift(1)['num_docks_available'],\n",
    "                previous_bikes = lambda row: row.shift(1)['num_bikes_available']\n",
    "            )\n",
    "        )\n",
    "    )\n",
    "\n",
    "    zero_dock_start = None\n",
    "    zero_bike_start = None\n",
    "    zero_docks = False\n",
    "    zero_bikes = False\n",
    "\n",
    "    restarted_counter = False\n",
    "\n",
    "    for i,row in station_subset.iloc[1:].iterrows(): \n",
    "\n",
    "        if (\n",
    "            (row['num_docks_available'] == 0) and \n",
    "            (row['is_returning'] == 1) and\n",
    "            (not zero_docks)\n",
    "        ): \n",
    "            zero_dock_start = row['last_updated']\n",
    "            restarted_counter = True\n",
    "            zero_docks = True\n",
    "\n",
    "        if (\n",
    "            (row['num_bikes_available'] == 0) and\n",
    "            (row['is_renting'] == 1) and\n",
    "            (not zero_bikes)\n",
    "        ): \n",
    "            zero_bike_start = row['last_updated']\n",
    "            restarted_counter = True\n",
    "            zero_bikes = True\n",
    "\n",
    "        if restarted_counter:  # if the timer has just started, skip the rest\n",
    "            restarted_counter = False\n",
    "            continue\n",
    "\n",
    "        # on data gap > 40 minutes, end the timer and store the instance\n",
    "\n",
    "        if (row['last_updated'] - row['previous_time']) > pd.Timedelta('40min'):\n",
    "            \n",
    "            if zero_docks:\n",
    "                zero_dock_end = row['previous_time']\n",
    "                zero_daytime_dock_durations.append((\n",
    "                    station_id,\n",
    "                    zero_dock_start,\n",
    "                    zero_dock_end,\n",
    "                    (zero_dock_end - zero_dock_start)\n",
    "                ))\n",
    "                ended_due_to_data_gap.append((\n",
    "                    'dock',\n",
    "                    station_id,\n",
    "                    zero_dock_start,\n",
    "                    zero_dock_end,\n",
    "                    (zero_dock_end - zero_dock_start),\n",
    "                    row['last_updated']\n",
    "                ))\n",
    "                zero_dock_start = None\n",
    "                zero_dock_end = None\n",
    "                zero_docks = False\n",
    "\n",
    "            if zero_bikes:\n",
    "                zero_bike_end = row['previous_time']\n",
    "                zero_daytime_bike_durations.append((\n",
    "                    station_id,\n",
    "                    zero_bike_start,\n",
    "                    zero_bike_end,\n",
    "                    (zero_bike_end - zero_bike_start)\n",
    "                ))\n",
    "                ended_due_to_data_gap.append((\n",
    "                    'bike',\n",
    "                    station_id,\n",
    "                    zero_bike_start,\n",
    "                    zero_bike_end,\n",
    "                    (zero_bike_end - zero_bike_start),\n",
    "                    row['last_updated']\n",
    "                ))\n",
    "                zero_bike_start = None\n",
    "                zero_bike_end = None\n",
    "                zero_bikes = False\n",
    "\n",
    "            continue\n",
    "\n",
    "        # end counter when this row is no longer zero\n",
    "\n",
    "        assert ((row['last_updated'] - row['previous_time']) <= pd.Timedelta('40min'))\n",
    "\n",
    "        if (\n",
    "            zero_docks \n",
    "            and \n",
    "            (row['num_docks_available'] != 0)\n",
    "        ): \n",
    "            zero_dock_end = row['last_updated']\n",
    "            zero_daytime_dock_durations.append(\n",
    "                (station_id,\n",
    "                zero_dock_start,\n",
    "                zero_dock_end,\n",
    "                (zero_dock_end - zero_dock_start)\n",
    "            ))\n",
    "            zero_dock_start = None\n",
    "            zero_dock_end = None\n",
    "            zero_docks = False\n",
    "\n",
    "        if (\n",
    "            zero_bikes \n",
    "            and \n",
    "            (row['num_bikes_available'] != 0)\n",
    "        ): \n",
    "            zero_bike_end = row['last_updated']\n",
    "            zero_daytime_bike_durations.append((\n",
    "                station_id,\n",
    "                zero_bike_start,\n",
    "                zero_bike_end,\n",
    "                (zero_bike_end - zero_bike_start)\n",
    "            ))\n",
    "            zero_bike_start = None\n",
    "            zero_bike_end = None\n",
    "            zero_bikes = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "zero_daytime_dock_durations_data = pd.DataFrame.from_records(\n",
    "    zero_daytime_dock_durations, \n",
    "    columns=['station_id','zero_dock_start','zero_dock_end', 'zero_dock_duration']\n",
    ")\n",
    "\n",
    "zero_daytime_bike_durations_data = pd.DataFrame.from_records(\n",
    "    zero_daytime_bike_durations, \n",
    "    columns=['station_id','zero_bike_start','zero_bike_end', 'zero_bike_duration']\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "zero_daytime_durations = pd.concat([\n",
    "    (\n",
    "        zero_daytime_dock_durations_data\n",
    "        [['station_id','zero_dock_duration']]\n",
    "        .rename(columns={'zero_dock_duration':'zero_duration'})\n",
    "        .assign(duration_type = 'dock')\n",
    "    ),\n",
    "    (\n",
    "        zero_daytime_bike_durations_data\n",
    "        [['station_id','zero_bike_duration']]\n",
    "        .rename(columns={'zero_bike_duration':'zero_duration'})\n",
    "        .assign(duration_type = 'bike')\n",
    "    )\n",
    "])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_geo = (\n",
    "    stations_geo\n",
    "    # .join(\n",
    "    #     zero_daytime_dock_durations_data\n",
    "    #     .groupby('station_id')\n",
    "    #     ['zero_dock_duration']\n",
    "    #     .agg(['max','mean','median'])\n",
    "    #     .add_prefix('zero_dock_daytime_duration_')\n",
    "    #     .reindex(index=stations_geo.index)\n",
    "    #     .fillna(pd.Timedelta(0))\n",
    "    #     .div(pd.Timedelta('1hour'))\n",
    "    # )\n",
    "    # .join(\n",
    "    #     zero_daytime_bike_durations_data\n",
    "    #     .groupby('station_id')\n",
    "    #     ['zero_bike_duration']\n",
    "    #     .agg(['max','mean','median'])\n",
    "    #     .add_prefix('zero_bike_daytime_duration_')\n",
    "    #     .reindex(index=stations_geo.index)\n",
    "    #     .fillna(pd.Timedelta(0))\n",
    "    #     .div(pd.Timedelta('1hour'))\n",
    "    # )\n",
    "    .join(\n",
    "        zero_daytime_durations\n",
    "        .groupby('station_id')\n",
    "        ['zero_duration']\n",
    "        .agg(['max','mean','median'])\n",
    "        .add_prefix('zero_daytime_duration_')\n",
    "        .reindex(index=stations_geo.index)\n",
    "        .fillna(pd.Timedelta(0))\n",
    "        .div(pd.Timedelta('1hour'))\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "save out"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_geo.to_file('../stations_service_measures.geojson')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "stations_geo"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "citibike-analysis",
   "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}