trips_summaries.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creates yearly summary table for Citibike trips data\n",
    "* Also adds MTA subway and bus ridership data from\n",
    "    * https://new.mta.info/agency/new-york-city-transit/subway-bus-ridership-2019\n",
    "    * https://new.mta.info/agency/new-york-city-transit/subway-bus-ridership-2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "\n",
    "import pandas as pd\n",
    "import logging\n",
    "import os\n",
    "\n",
    "# import our helpers module\n",
    "import helpers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "DATA_DIR = \"data/\"\n",
    "SUMMARY_DIR = DATA_DIR + \"summaries/\"\n",
    "logging.basicConfig(level=logging.DEBUG)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "if not os.path.exists(SUMMARY_DIR):\n",
    "    os.makedirs(os.path.dirname(SUMMARY_DIR))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def summarise_years(years: range) -> pd.DataFrame:\n",
    "    \"\"\"\n",
    "    Summarizes yearly statistics for trip data\n",
    "    :param years: range of years to summarize\n",
    "    :return: summary dataframe\n",
    "    \"\"\"\n",
    "    summaries = []\n",
    "    for year in years:\n",
    "        print(f\"Summarizing year {year}...\")\n",
    "        trips = helpers.get_trips(year, DATA_DIR)\n",
    "\n",
    "        num_trips = trips.shape[0]\n",
    "        avg_trip_duration_s = round(trips.tripduration.mean())\n",
    "        num_bikes = trips.bikeid.nunique()\n",
    "\n",
    "        num_stations = trips.startstationid.nunique()\n",
    "        start_station_values = trips.startstationid.value_counts(ascending=False)\n",
    "        mode_start_station_id = start_station_values.index[0]\n",
    "        mode_start_station_trip_count = start_station_values.iloc[0]\n",
    "\n",
    "        end_station_values = trips.endstationid.value_counts(ascending=False)\n",
    "        mode_end_station_id = end_station_values.index[0]\n",
    "        mode_end_station_trip_count = end_station_values.iloc[0]\n",
    "\n",
    "        usertype_values = trips.usertype.value_counts()\n",
    "        subscribers = usertype_values[\"Subscriber\"]\n",
    "        # some year have no \"Customer\" usertype so set to zero if this value does not exist\n",
    "        customers = usertype_values[\"Customer\"] if len(usertype_values.index) > 1 else 0\n",
    "\n",
    "        gender_values = trips.gender.value_counts()\n",
    "        trips_by_males = gender_values[1]\n",
    "        trips_by_females = gender_values[2]\n",
    "\n",
    "        summary = pd.Series(\n",
    "            [\n",
    "                num_trips,\n",
    "                avg_trip_duration_s,\n",
    "                num_bikes,\n",
    "                num_stations,\n",
    "                mode_start_station_id,\n",
    "                mode_start_station_trip_count,\n",
    "                mode_end_station_id,\n",
    "                mode_end_station_trip_count,\n",
    "                subscribers,\n",
    "                customers,\n",
    "                trips_by_males,\n",
    "                trips_by_females,\n",
    "            ],\n",
    "            index=[\n",
    "                \"trip_count\",\n",
    "                \"avg_trip_duration\",\n",
    "                \"num_unique_bikes\",\n",
    "                \"num_unique_stations\",\n",
    "                \"mode_start_station_id\",\n",
    "                \"mode_start_station_count\",\n",
    "                \"mode_end_station_id\",\n",
    "                \"mode_end_station_trip_count\",\n",
    "                \"subscribers\",\n",
    "                \"customers\",\n",
    "                \"trips_by_males\",\n",
    "                \"trips_by_females\",\n",
    "            ],\n",
    "            name=year,\n",
    "        )\n",
    "        summaries.append(summary)\n",
    "\n",
    "    return pd.concat(summaries, axis=1).T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "%%time\n",
    "summaries = summarise_years(range(2014, 2021))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add MTA subway and bus data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "summaries.to_csv(SUMMARY_DIR + \"summaries\" + helpers.CSV_EXTENSION)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# TODO summaries should have mta info. This was run before, but needs to be run again to ensure we have it for all years\n",
    "\n",
    "mta = pd.DataFrame(index=[2014, 2015, 2016, 2017, 2018, 2019, 2020])\n",
    "# no data for 2013 and 2021\n",
    "mta[\"subway\"] = [\n",
    "    -1,\n",
    "    1751287621,\n",
    "    1762565419,\n",
    "    1756814800,\n",
    "    1727366607,\n",
    "    1680060402,\n",
    "    1697787002,\n",
    "    639541029,\n",
    "    -1,\n",
    "]\n",
    "mta[\"bus\"] = [\n",
    "    -1,\n",
    "    667051170 + 125581237,\n",
    "    776081306,\n",
    "    764030270,\n",
    "    724834684,\n",
    "    690809514,\n",
    "    677588084,\n",
    "    382424444,\n",
    "    -1,\n",
    "]  # MTA Bus Company + NYC Transit Buses\n",
    "summaries = pd.concat([summaries, mta], axis=1)\n",
    "summaries.to_csv(SUMMARY_DIR + \"summaries\" + helpers.CSV_EXTENSION)\n",
    "summaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}