availability.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bike and Dock Availability"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "from datetime import timedelta\n",
    "from matplotlib.dates import DateFormatter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "ename": "IndentationError",
     "evalue": "unexpected indent (<ipython-input-2-3982a3d18f72>, line 2)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;36m  File \u001b[0;32m\"<ipython-input-2-3982a3d18f72>\"\u001b[0;36m, line \u001b[0;32m2\u001b[0m\n\u001b[0;31m    columns=['starttime','start station id','stoptime','end station id'])\u001b[0m\n\u001b[0m    ^\u001b[0m\n\u001b[0;31mIndentationError\u001b[0m\u001b[0;31m:\u001b[0m unexpected indent\n"
     ]
    }
   ],
   "source": [
    "# df = pd.read_parquet('~/bikeshare/tripdata/202009-citibike-tripdata.parquet',\\\n",
    "#                     columns=['starttime','start station id','stoptime','end station id'])\n",
    "# df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Read Trip Data\n",
    "Read September 2020 tripdata file into dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('~/bikeshare/202009-citibike-tripdata.csv',\\\n",
    "                 usecols=['starttime','start station id','stoptime','end station id'],\\\n",
    "                 parse_dates=['starttime','stoptime'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Reblancing \n",
    "Read file with rebalancing movements.   \n",
    "Note: requires pyarrow, from command line `conda install -c conda-forge pyarrow`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfrebal=pd.read_parquet('../bikeshare/202009-citibike-reblance.parquet')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfrebal.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Combine\n",
    "Concatenate actual trips with rebalancing movements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df = pd.concat([df,dfrebal])\n",
    "df.reset_index(drop=True, inplace=True)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Restructure "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create `dfs` with `startime`, `start station id` and `act` as -1 to indicate a bike pick-up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs=df[['starttime','start station id']].assign(act=-1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create `dfe` with `stoptime`, `end station id` and `act` as +1 to indicate a bike drop-off"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfe=df[['stoptime','end station id']].assign(act=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rename the columns to be the same in both dataframes.\n",
    "Then create new dataframe `dfse` by concatentating start and end rows.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs.columns=['docktime','stationid','act']\n",
    "dfe.columns=['docktime','stationid','act']\n",
    "dfse=pd.concat([dfs,dfe])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sort by Time\n",
    "Finally sort combined start/end rows all by `docktime`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfse.sort_values(by=['docktime'], inplace=True) \n",
    "dfse.reset_index(drop=True, inplace=True) \n",
    "dfse.head(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get Station Names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfstations = \\\n",
    "  pd.read_csv('~/bikeshare/202009-citibike-tripdata.csv',\\\n",
    "  usecols=['start station id','start station name']).\\\n",
    "  drop_duplicates()                \n",
    "dfstations.columns=['stationid','station name']\n",
    "dfstations.set_index('stationid' ,  inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dfstations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### One Station \n",
    "Create datafame for all bikes in/out to one station"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get the station id for one station"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "station = 'W 31 St & 7 Ave'\n",
    "sid = dfstations.loc[dfstations['station name']==station].index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create new dataframe `dfstation` with rows for one station"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfstation = dfse.loc[(dfse.stationid==sid) ]\n",
    "dfstation.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate running sum of `act` as `cnt`.  Note that it starts counting at zero, so eventually there will be a negative count. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "dfstation = dfstation.assign(cnt = dfstation.act.cumsum())\n",
    "dfstation.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Determine likely starting number of bikes for this station \n",
    "Calculate lowest value for a running sum of `act` which will be a negative number.  \n",
    "Add its absolute value to first value for `act` to get starting bike count "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfstation.at[0, 'act'] =+ abs(dfstation.act.cumsum().min()) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "dfstation = dfstation.assign(cnt = dfstation.act.cumsum())\n",
    "dfstation.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Plot availabilty for the month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sns.set(font_scale = 1)\n",
    "plt.figure(figsize=(20,8))\n",
    "ax=sns.lineplot(data=dfstation , x='docktime', y='cnt' ) \n",
    "ax.set_xlabel('Day') \n",
    "ax.set_ylabel('Available Bikes') ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Plot availabilty for one day\n",
    "Using Sept 8 (day after labor day)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "sns.set(font_scale = 2)\n",
    "plt.figure(figsize=(20,8))\n",
    "ax=sns.lineplot(data=dfstation.loc[dfstation.docktime.dt.day == 8] , x='docktime', y='cnt' ) \n",
    "ax.set_ylabel('Available Bikes')\n",
    "ax.set_xlabel('Hour of Day') \n",
    "ax.set_title('Citi Bike Station - ' + station +' - September 8, 2020')\n",
    "ax.xaxis.set_major_formatter(DateFormatter(\"%H\")) ;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Create function to plot one station "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def availability (station,day):\n",
    "    # inputs: station name, day \n",
    "    # requires: dfstations, dfse \n",
    "    sid = dfstations.loc[dfstations['station name']==station].index[0] # lookup station id \n",
    "    dfstation = dfse.loc[(dfse.stationid==sid) ] # create dataframe for a specified station \n",
    "    dfstation.reset_index(drop=True, inplace=True)\n",
    "    dfstation = dfstation.assign(cnt = dfstation.act.cumsum()) # get running total \n",
    "    dfstation.at[0, 'act'] =+ abs(dfstation.act.cumsum().min()) # find sub-zero bike count\n",
    "    dfstation = dfstation.assign(cnt = dfstation.act.cumsum()) # recalculate running total \n",
    "    \n",
    "    # Create chart \n",
    "    sns.set(font_scale = 2)\n",
    "    plt.figure(figsize=(20,8))\n",
    "    ax=sns.lineplot(data=dfstation.loc[dfstation.docktime.dt.day == day] , x='docktime', y='cnt' ) \n",
    "    ax.set_ylabel('Available Bikes')\n",
    "    ax.set_xlabel('Hour of Day') \n",
    "    ax.set_title('Citi Bike Station - ' + station +' - September ' + str(day))\n",
    "    ax.xaxis.set_major_formatter(DateFormatter(\"%H\")) ;\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get list of all station names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list(dfstations['station name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "availability ('W 52 St & 6 Ave',8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "availability ('1 Ave & E 18 St', 9)"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}