data_prep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c6e866fb",
   "metadata": {},
   "source": [
    "# Aim:\n",
    "Now that we have our initial prep done and have an initial understanding of the data, prepare the data for use by various algorithms.\n",
    "\n",
    "There is data available to use over a period of 3 and a bit years. Also there are a range of features available for people to use.\n",
    "\n",
    "The output of this notebook is csv files of the complete data sets that can then be read in by other analysis scripts that will filter down to the required data frame and the required feature set.\n",
    "\n",
    "Currently there are 3 different sets of data:\n",
    "1. Data on each bike station (at a bike station level)\n",
    "2. Bike transaction data (5 min intervals)\n",
    "3. Weather data (hourly)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cf7c7388",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime as dt\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9eccedf4",
   "metadata": {},
   "source": [
    "## Read in historical data\n",
    "Downloaded from https://data.smartdublin.ie/dataset/dublinbikes-api"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8682739a",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_files = [\n",
    "    \"dublinbikes_20190401_20190701.csv\",  # Apr - Jun 2019\n",
    "    \"dublinbikes_20190701_20191001.csv\",  # Jul - Sep 2019\n",
    "    \"dublinbikes_20191001_20200101.csv\",  # Oct - Dec 2019\n",
    "    \"dublinbikes_20200101_20200401.csv\",  # Jan - Mar 2020\n",
    "    \"dublinbikes_20200401_20200701.csv\",  # Apr - Jun 2020\n",
    "    \"dublinbikes_20201001_20210101.csv\",  # Oct - Dec 2020\n",
    "    \"dublinbikes_20210101_20210401.csv\",  # Jan - Mar 2021\n",
    "    \"dublinbikes_20210401_20210701.csv\",  # Apr - Jun 2021\n",
    "    \"dublinbikes_20210701_20211001.csv\",  # Jul - Oct 2021\n",
    "    \"dublinbikes_20211001_20220101.csv\",  # Oct - Dec 2021\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "dd9c9632",
   "metadata": {},
   "outputs": [],
   "source": [
    "station_level_data = ['STATION ID', 'TIME', 'LAST UPDATED', 'BIKE STANDS', 'AVAILABLE BIKE STANDS', \n",
    "                      'AVAILABLE BIKES', 'STATUS']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cb2c8078",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_data = []\n",
    "\n",
    "for each in data_files:\n",
    "    df_data.append(pd.read_csv(\"data/\" + str(each))[station_level_data])\n",
    "    \n",
    "df = pd.concat(df_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3d153ddc",
   "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>STATION ID</th>\n",
       "      <th>TIME</th>\n",
       "      <th>LAST UPDATED</th>\n",
       "      <th>BIKE STANDS</th>\n",
       "      <th>AVAILABLE BIKE STANDS</th>\n",
       "      <th>AVAILABLE BIKES</th>\n",
       "      <th>STATUS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-01 00:00:06</td>\n",
       "      <td>2019-03-31 23:52:05</td>\n",
       "      <td>20</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-01 00:05:04</td>\n",
       "      <td>2019-04-01 00:02:13</td>\n",
       "      <td>20</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-01 00:10:04</td>\n",
       "      <td>2019-04-01 00:02:13</td>\n",
       "      <td>20</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-01 00:15:03</td>\n",
       "      <td>2019-04-01 00:12:21</td>\n",
       "      <td>20</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-01 00:20:03</td>\n",
       "      <td>2019-04-01 00:12:21</td>\n",
       "      <td>20</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Open</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATION ID                 TIME         LAST UPDATED  BIKE STANDS  \\\n",
       "0           2  2019-04-01 00:00:06  2019-03-31 23:52:05           20   \n",
       "1           2  2019-04-01 00:05:04  2019-04-01 00:02:13           20   \n",
       "2           2  2019-04-01 00:10:04  2019-04-01 00:02:13           20   \n",
       "3           2  2019-04-01 00:15:03  2019-04-01 00:12:21           20   \n",
       "4           2  2019-04-01 00:20:03  2019-04-01 00:12:21           20   \n",
       "\n",
       "   AVAILABLE BIKE STANDS  AVAILABLE BIKES STATUS  \n",
       "0                     14                6   Open  \n",
       "1                     14                6   Open  \n",
       "2                     14                6   Open  \n",
       "3                     14                6   Open  \n",
       "4                     14                6   Open  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "ef8b3469",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 27192762 entries, 0 to 2776084\n",
      "Data columns (total 7 columns):\n",
      " #   Column                 Dtype \n",
      "---  ------                 ----- \n",
      " 0   STATION ID             int64 \n",
      " 1   TIME                   object\n",
      " 2   LAST UPDATED           object\n",
      " 3   BIKE STANDS            int64 \n",
      " 4   AVAILABLE BIKE STANDS  int64 \n",
      " 5   AVAILABLE BIKES        int64 \n",
      " 6   STATUS                 object\n",
      "dtypes: int64(4), object(3)\n",
      "memory usage: 1.6+ GB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb1f8676",
   "metadata": {},
   "source": [
    "### Adapting code from Rachael Breslin's blog post"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "76f78c2b",
   "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>STATION ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>920514</th>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>943220</th>\n",
       "      <td>114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1867181</th>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991340</th>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1991628</th>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342548</th>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>373359</th>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376307</th>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>376526</th>\n",
       "      <td>117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2693859</th>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>90 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         STATION ID\n",
       "920514           35\n",
       "943220          114\n",
       "1867181          46\n",
       "1991340          25\n",
       "1991628          26\n",
       "...             ...\n",
       "342548          108\n",
       "373359          106\n",
       "376307          116\n",
       "376526          117\n",
       "2693859          90\n",
       "\n",
       "[90 rows x 1 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.STATUS != 'Open'][['STATION ID']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8db55a2",
   "metadata": {},
   "source": [
    "90 stations were closed at some stage during the time line of interest. Dropping stations from the transactional data when they have a status of 'Close' will cause gaps in the timeline for some stations -> something to bear in mind when modelling."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9f088aca",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop data from closed stations\n",
    "df = df[df['STATUS'] == 'Open']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef422527",
   "metadata": {},
   "source": [
    "The blog uses the 'LAST UPDATED' for the date-time column, i.e. when a data entry was last updated. \n",
    "However, the 'TIME' column is more accurate as corresponds to the actual 5 min intervals of recording.Therefore, use 'TIME' going forward\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03876eed",
   "metadata": {},
   "source": [
    "## Deconstruct the timestamps into various components"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fa00aa84",
   "metadata": {},
   "outputs": [],
   "source": [
    "# First convert to a datetime object \n",
    "df['DATETIME'] = [dt.datetime.strptime(d, \"%Y-%m-%d %H:%M:%S\") for d in df[\"TIME\"]]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c2239093",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Deconstruct the timestamp further\n",
    "df['DATE'] = df.DATETIME.dt.date\n",
    "df['YEAR'] = df.DATETIME.dt.year\n",
    "df['MONTH'] = df.DATETIME.dt.month\n",
    "\n",
    "# Get the day of the week as a number. 0 = Sunday, Saturday = 6.\n",
    "df['DAY_NUMBER'] = df.DATETIME.dt.dayofweek\n",
    "df['HOUR'] = df.DATETIME.dt.hour\n",
    "df['MINUTE'] = df.DATETIME.dt.minute"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d41c27b1",
   "metadata": {},
   "source": [
    "### Create important features\n",
    "The intial analysis by Rachael Breslin calculated 'OCCUPANCY_PCT' as 'AVAILABLE BIKES' / 'BIKE STANDS'. \n",
    "However from the initial exploration, there are situations where \n",
    "BIKE STANDS' != 'AVAILABLE BIKES' + 'AVAILABLE BIKE STANDS',"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "6e5ec554",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of occurrences where the stated level of bike stands is greater than the sum of available bike stands and available bikes: 1236304\n"
     ]
    },
    {
     "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>BIKE STANDS</th>\n",
       "      <th>AVAILABLE BIKE STANDS</th>\n",
       "      <th>AVAILABLE BIKES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>382</th>\n",
       "      <td>20</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658</th>\n",
       "      <td>20</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>673</th>\n",
       "      <td>20</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>20</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1050</th>\n",
       "      <td>40</td>\n",
       "      <td>27</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      BIKE STANDS  AVAILABLE BIKE STANDS  AVAILABLE BIKES\n",
       "382            20                     19                0\n",
       "658            20                     19                0\n",
       "673            20                     19                0\n",
       "674            20                     19                0\n",
       "1050           40                     27               12"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Situations where the stated level of bike stands is greater than the sum of available bike stands and \n",
    "# available bikes\n",
    "tmp = df[df['BIKE STANDS'] > df['AVAILABLE BIKES'] + df['AVAILABLE BIKE STANDS']]\\\n",
    "    [['BIKE STANDS', 'AVAILABLE BIKE STANDS', 'AVAILABLE BIKES']]\n",
    "print(\"Number of occurrences where the stated level of bike stands is greater than the sum of available bike stands and available bikes:\", len(tmp))\n",
    "tmp.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2e02658c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of occurrences where the stated level of bike stands is less than the sum of available bike stands and  available bikes: 10091\n"
     ]
    },
    {
     "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>BIKE STANDS</th>\n",
       "      <th>AVAILABLE BIKE STANDS</th>\n",
       "      <th>AVAILABLE BIKES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>35407</th>\n",
       "      <td>20</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35408</th>\n",
       "      <td>20</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35409</th>\n",
       "      <td>20</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101795</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101796</th>\n",
       "      <td>20</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        BIKE STANDS  AVAILABLE BIKE STANDS  AVAILABLE BIKES\n",
       "35407            20                     22                0\n",
       "35408            20                     22                0\n",
       "35409            20                     22                0\n",
       "101795           20                     20                1\n",
       "101796           20                     20                1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Situations where the stated level of bike stands is less than the sum of available bike stands and \n",
    "# available bikes\n",
    "tmp = df[df['BIKE STANDS'] < df['AVAILABLE BIKES'] + df['AVAILABLE BIKE STANDS']]\\\n",
    "    [['BIKE STANDS', 'AVAILABLE BIKE STANDS', 'AVAILABLE BIKES']]\n",
    "print(\"Number of occurrences where the stated level of bike stands is less than the sum of available bike stands and  available bikes:\", len(tmp))\n",
    "tmp.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f09d7b86",
   "metadata": {},
   "source": [
    "From above, assume that bike stands is not always an accurate representation of the actual station capacity at\n",
    "a given time. Use 'AVAILABLE BIKES' + 'AVAILABLE BIKE STANDS' as a measure of capacity instead"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "01b78b4e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['OCCUPANCY_PCT'] =  df['AVAILABLE BIKES'] / (df['AVAILABLE BIKES'] + df['AVAILABLE BIKE STANDS'])\n",
    "df['FULL'] = np.where(df['OCCUPANCY_PCT'] == 0, 1, 0 )\n",
    "df['EMPTY'] = np.where(df['OCCUPANCY_PCT'] == 1, 1, 0 )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "1ad9ed9d",
   "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>OCCUPANCY_PCT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.708920e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>3.689747e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.819564e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.333333e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>3.250000e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>5.500000e-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       OCCUPANCY_PCT\n",
       "count   2.708920e+07\n",
       "mean    3.689747e-01\n",
       "std     2.819564e-01\n",
       "min     0.000000e+00\n",
       "25%     1.333333e-01\n",
       "50%     3.250000e-01\n",
       "75%     5.500000e-01\n",
       "max     1.000000e+00"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sanity check\n",
    "df[['OCCUPANCY_PCT']].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "2a817a01",
   "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>STATION ID</th>\n",
       "      <th>TIME</th>\n",
       "      <th>LAST UPDATED</th>\n",
       "      <th>BIKE STANDS</th>\n",
       "      <th>AVAILABLE BIKE STANDS</th>\n",
       "      <th>AVAILABLE BIKES</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY_NUMBER</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>MINUTE</th>\n",
       "      <th>OCCUPANCY_PCT</th>\n",
       "      <th>FULL</th>\n",
       "      <th>EMPTY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-01 00:00:06</td>\n",
       "      <td>2019-03-31 23:52:05</td>\n",
       "      <td>20</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Open</td>\n",
       "      <td>2019-04-01 00:00:06</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-01 00:05:04</td>\n",
       "      <td>2019-04-01 00:02:13</td>\n",
       "      <td>20</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Open</td>\n",
       "      <td>2019-04-01 00:05:04</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-01 00:10:04</td>\n",
       "      <td>2019-04-01 00:02:13</td>\n",
       "      <td>20</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Open</td>\n",
       "      <td>2019-04-01 00:10:04</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-01 00:15:03</td>\n",
       "      <td>2019-04-01 00:12:21</td>\n",
       "      <td>20</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Open</td>\n",
       "      <td>2019-04-01 00:15:03</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-04-01 00:20:03</td>\n",
       "      <td>2019-04-01 00:12:21</td>\n",
       "      <td>20</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>Open</td>\n",
       "      <td>2019-04-01 00:20:03</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>2019</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATION ID                 TIME         LAST UPDATED  BIKE STANDS  \\\n",
       "0           2  2019-04-01 00:00:06  2019-03-31 23:52:05           20   \n",
       "1           2  2019-04-01 00:05:04  2019-04-01 00:02:13           20   \n",
       "2           2  2019-04-01 00:10:04  2019-04-01 00:02:13           20   \n",
       "3           2  2019-04-01 00:15:03  2019-04-01 00:12:21           20   \n",
       "4           2  2019-04-01 00:20:03  2019-04-01 00:12:21           20   \n",
       "\n",
       "   AVAILABLE BIKE STANDS  AVAILABLE BIKES STATUS            DATETIME  \\\n",
       "0                     14                6   Open 2019-04-01 00:00:06   \n",
       "1                     14                6   Open 2019-04-01 00:05:04   \n",
       "2                     14                6   Open 2019-04-01 00:10:04   \n",
       "3                     14                6   Open 2019-04-01 00:15:03   \n",
       "4                     14                6   Open 2019-04-01 00:20:03   \n",
       "\n",
       "         DATE  YEAR  MONTH  DAY_NUMBER  HOUR  MINUTE  OCCUPANCY_PCT  FULL  \\\n",
       "0  2019-04-01  2019      4           0     0       0            0.3     0   \n",
       "1  2019-04-01  2019      4           0     0       5            0.3     0   \n",
       "2  2019-04-01  2019      4           0     0      10            0.3     0   \n",
       "3  2019-04-01  2019      4           0     0      15            0.3     0   \n",
       "4  2019-04-01  2019      4           0     0      20            0.3     0   \n",
       "\n",
       "   EMPTY  \n",
       "0      0  \n",
       "1      0  \n",
       "2      0  \n",
       "3      0  \n",
       "4      0  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1f9934b",
   "metadata": {},
   "source": [
    "Next get an estimate of the total movement of bikes in and out of the station from one interval to the next. \n",
    "We do this by first sorting the data by station id and datetime. We then calculate the difference in available\n",
    "bike stands between the current interval and the previous interval. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "af8c797b",
   "metadata": {},
   "outputs": [],
   "source": [
    "bike_flow = df[['STATION ID', 'DATETIME', 'AVAILABLE BIKE STANDS']].copy().sort_values(by=['STATION ID', 'DATETIME'])\n",
    "bike_flow['BIKE_ARR_DEP'] = bike_flow['AVAILABLE BIKE STANDS'].diff(-1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e3a50ff",
   "metadata": {},
   "source": [
    "Sanity check next...."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "2e7e540b",
   "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>STATION ID</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>AVAILABLE BIKE STANDS</th>\n",
       "      <th>BIKE_ARR_DEP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9528</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 04:45:02</td>\n",
       "      <td>36</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9530</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 04:55:03</td>\n",
       "      <td>35</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9538</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 05:35:04</td>\n",
       "      <td>34</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9540</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 05:45:02</td>\n",
       "      <td>33</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9541</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 05:50:03</td>\n",
       "      <td>32</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9544</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 06:05:03</td>\n",
       "      <td>31</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9550</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 06:35:03</td>\n",
       "      <td>30</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9552</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 06:45:03</td>\n",
       "      <td>28</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9556</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 07:05:03</td>\n",
       "      <td>27</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9558</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 07:15:03</td>\n",
       "      <td>25</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9562</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 07:35:03</td>\n",
       "      <td>22</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9563</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 07:40:03</td>\n",
       "      <td>18</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9564</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 07:45:03</td>\n",
       "      <td>14</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9565</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 07:50:04</td>\n",
       "      <td>12</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9566</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 07:55:03</td>\n",
       "      <td>10</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9567</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 08:00:05</td>\n",
       "      <td>5</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9568</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 08:05:03</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9569</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 08:10:03</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9570</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 08:15:03</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9572</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 08:25:04</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9577</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 08:50:02</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9610</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 11:35:03</td>\n",
       "      <td>5</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9612</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 11:45:02</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9623</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 12:40:03</td>\n",
       "      <td>14</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9628</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 13:05:03</td>\n",
       "      <td>13</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9629</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 13:10:03</td>\n",
       "      <td>10</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9630</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 13:15:03</td>\n",
       "      <td>9</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9631</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 13:20:04</td>\n",
       "      <td>8</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9648</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 14:45:02</td>\n",
       "      <td>13</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9667</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 16:20:04</td>\n",
       "      <td>39</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9673</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 16:50:04</td>\n",
       "      <td>40</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9674</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 16:55:03</td>\n",
       "      <td>39</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9678</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 17:15:03</td>\n",
       "      <td>40</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9690</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 18:15:03</td>\n",
       "      <td>40</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9714</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 20:15:03</td>\n",
       "      <td>40</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9718</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 20:35:03</td>\n",
       "      <td>39</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9734</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 21:55:03</td>\n",
       "      <td>33</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9735</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 22:00:05</td>\n",
       "      <td>32</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41992</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 04:45:02</td>\n",
       "      <td>33</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41993</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 04:50:03</td>\n",
       "      <td>32</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41994</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 04:55:03</td>\n",
       "      <td>31</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41997</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 05:10:03</td>\n",
       "      <td>30</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41999</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 05:20:03</td>\n",
       "      <td>29</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42004</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 05:45:02</td>\n",
       "      <td>40</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42008</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 06:05:03</td>\n",
       "      <td>40</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42012</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 06:25:06</td>\n",
       "      <td>39</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42014</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 06:35:03</td>\n",
       "      <td>38</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42015</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 06:40:03</td>\n",
       "      <td>36</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42016</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 06:45:02</td>\n",
       "      <td>35</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42019</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 07:00:04</td>\n",
       "      <td>34</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       STATION ID            DATETIME  AVAILABLE BIKE STANDS  BIKE_ARR_DEP\n",
       "9528           36 2019-04-01 04:45:02                     36           1.0\n",
       "9530           36 2019-04-01 04:55:03                     35           1.0\n",
       "9538           36 2019-04-01 05:35:04                     34           1.0\n",
       "9540           36 2019-04-01 05:45:02                     33           1.0\n",
       "9541           36 2019-04-01 05:50:03                     32           1.0\n",
       "9544           36 2019-04-01 06:05:03                     31           1.0\n",
       "9550           36 2019-04-01 06:35:03                     30           2.0\n",
       "9552           36 2019-04-01 06:45:03                     28           1.0\n",
       "9556           36 2019-04-01 07:05:03                     27           2.0\n",
       "9558           36 2019-04-01 07:15:03                     25           3.0\n",
       "9562           36 2019-04-01 07:35:03                     22           4.0\n",
       "9563           36 2019-04-01 07:40:03                     18           4.0\n",
       "9564           36 2019-04-01 07:45:03                     14           2.0\n",
       "9565           36 2019-04-01 07:50:04                     12           2.0\n",
       "9566           36 2019-04-01 07:55:03                     10           5.0\n",
       "9567           36 2019-04-01 08:00:05                      5           2.0\n",
       "9568           36 2019-04-01 08:05:03                      3           1.0\n",
       "9569           36 2019-04-01 08:10:03                      2           1.0\n",
       "9570           36 2019-04-01 08:15:03                      1           1.0\n",
       "9572           36 2019-04-01 08:25:04                      1           1.0\n",
       "9577           36 2019-04-01 08:50:02                      1           1.0\n",
       "9610           36 2019-04-01 11:35:03                      5           2.0\n",
       "9612           36 2019-04-01 11:45:02                      3           1.0\n",
       "9623           36 2019-04-01 12:40:03                     14           1.0\n",
       "9628           36 2019-04-01 13:05:03                     13           3.0\n",
       "9629           36 2019-04-01 13:10:03                     10           1.0\n",
       "9630           36 2019-04-01 13:15:03                      9           1.0\n",
       "9631           36 2019-04-01 13:20:04                      8           1.0\n",
       "9648           36 2019-04-01 14:45:02                     13           1.0\n",
       "9667           36 2019-04-01 16:20:04                     39           1.0\n",
       "9673           36 2019-04-01 16:50:04                     40           1.0\n",
       "9674           36 2019-04-01 16:55:03                     39           1.0\n",
       "9678           36 2019-04-01 17:15:03                     40           1.0\n",
       "9690           36 2019-04-01 18:15:03                     40           1.0\n",
       "9714           36 2019-04-01 20:15:03                     40           1.0\n",
       "9718           36 2019-04-01 20:35:03                     39           9.0\n",
       "9734           36 2019-04-01 21:55:03                     33           1.0\n",
       "9735           36 2019-04-01 22:00:05                     32           1.0\n",
       "41992          36 2019-04-02 04:45:02                     33           1.0\n",
       "41993          36 2019-04-02 04:50:03                     32           1.0\n",
       "41994          36 2019-04-02 04:55:03                     31           1.0\n",
       "41997          36 2019-04-02 05:10:03                     30           1.0\n",
       "41999          36 2019-04-02 05:20:03                     29           1.0\n",
       "42004          36 2019-04-02 05:45:02                     40           1.0\n",
       "42008          36 2019-04-02 06:05:03                     40           1.0\n",
       "42012          36 2019-04-02 06:25:06                     39           1.0\n",
       "42014          36 2019-04-02 06:35:03                     38           2.0\n",
       "42015          36 2019-04-02 06:40:03                     36           1.0\n",
       "42016          36 2019-04-02 06:45:02                     35           1.0\n",
       "42019          36 2019-04-02 07:00:04                     34           3.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bike_flow[(bike_flow.BIKE_ARR_DEP > 0) & (bike_flow['STATION ID']==36)][['STATION ID', 'DATETIME', \n",
    "                                                          'AVAILABLE BIKE STANDS', 'BIKE_ARR_DEP', \n",
    "                                                         ]].head(n=50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c84d01e0",
   "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>STATION ID</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>AVAILABLE BIKE STANDS</th>\n",
       "      <th>BIKE_ARR_DEP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9523</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 04:20:02</td>\n",
       "      <td>35</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9571</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 08:20:02</td>\n",
       "      <td>0</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9575</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 08:40:05</td>\n",
       "      <td>0</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9587</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 09:40:04</td>\n",
       "      <td>0</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9589</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 09:50:04</td>\n",
       "      <td>1</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9602</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 10:55:04</td>\n",
       "      <td>2</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9603</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 11:00:04</td>\n",
       "      <td>3</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9605</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 11:10:03</td>\n",
       "      <td>4</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9613</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 11:50:03</td>\n",
       "      <td>2</td>\n",
       "      <td>-8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9615</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 12:00:06</td>\n",
       "      <td>10</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9616</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 12:05:03</td>\n",
       "      <td>11</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9617</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 12:10:03</td>\n",
       "      <td>12</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9618</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 12:15:03</td>\n",
       "      <td>13</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9634</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 13:35:03</td>\n",
       "      <td>7</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9636</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 13:45:03</td>\n",
       "      <td>8</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9645</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 14:30:04</td>\n",
       "      <td>9</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9646</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 14:35:04</td>\n",
       "      <td>10</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9647</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 14:40:03</td>\n",
       "      <td>11</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9649</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 14:50:03</td>\n",
       "      <td>12</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9650</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 14:55:03</td>\n",
       "      <td>13</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9652</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 15:05:02</td>\n",
       "      <td>14</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9653</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 15:10:04</td>\n",
       "      <td>15</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9654</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 15:15:03</td>\n",
       "      <td>16</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9657</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 15:30:04</td>\n",
       "      <td>18</td>\n",
       "      <td>-3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9659</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 15:40:03</td>\n",
       "      <td>21</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9660</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 15:45:03</td>\n",
       "      <td>23</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9661</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 15:50:04</td>\n",
       "      <td>24</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9662</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 15:55:03</td>\n",
       "      <td>28</td>\n",
       "      <td>-4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9663</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 16:00:05</td>\n",
       "      <td>32</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9664</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 16:05:02</td>\n",
       "      <td>34</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9665</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 16:10:04</td>\n",
       "      <td>35</td>\n",
       "      <td>-3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9666</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 16:15:03</td>\n",
       "      <td>38</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9670</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 16:35:04</td>\n",
       "      <td>38</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9676</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 17:05:03</td>\n",
       "      <td>38</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9677</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 17:10:04</td>\n",
       "      <td>39</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9679</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 17:20:04</td>\n",
       "      <td>39</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9697</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 18:50:03</td>\n",
       "      <td>39</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9722</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 20:55:02</td>\n",
       "      <td>30</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9729</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 21:30:03</td>\n",
       "      <td>31</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9731</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-01 21:40:04</td>\n",
       "      <td>32</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41984</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 04:05:03</td>\n",
       "      <td>31</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41985</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 04:10:03</td>\n",
       "      <td>32</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42000</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 05:25:03</td>\n",
       "      <td>28</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42001</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 05:30:02</td>\n",
       "      <td>29</td>\n",
       "      <td>-9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42002</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 05:35:03</td>\n",
       "      <td>38</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42006</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 05:55:03</td>\n",
       "      <td>39</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42030</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 07:55:03</td>\n",
       "      <td>10</td>\n",
       "      <td>-15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42042</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 08:55:03</td>\n",
       "      <td>17</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42045</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 09:10:05</td>\n",
       "      <td>18</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42050</th>\n",
       "      <td>36</td>\n",
       "      <td>2019-04-02 09:35:03</td>\n",
       "      <td>20</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       STATION ID            DATETIME  AVAILABLE BIKE STANDS  BIKE_ARR_DEP\n",
       "9523           36 2019-04-01 04:20:02                     35          -1.0\n",
       "9571           36 2019-04-01 08:20:02                      0          -1.0\n",
       "9575           36 2019-04-01 08:40:05                      0          -1.0\n",
       "9587           36 2019-04-01 09:40:04                      0          -1.0\n",
       "9589           36 2019-04-01 09:50:04                      1          -1.0\n",
       "9602           36 2019-04-01 10:55:04                      2          -1.0\n",
       "9603           36 2019-04-01 11:00:04                      3          -1.0\n",
       "9605           36 2019-04-01 11:10:03                      4          -1.0\n",
       "9613           36 2019-04-01 11:50:03                      2          -8.0\n",
       "9615           36 2019-04-01 12:00:06                     10          -1.0\n",
       "9616           36 2019-04-01 12:05:03                     11          -1.0\n",
       "9617           36 2019-04-01 12:10:03                     12          -1.0\n",
       "9618           36 2019-04-01 12:15:03                     13          -1.0\n",
       "9634           36 2019-04-01 13:35:03                      7          -1.0\n",
       "9636           36 2019-04-01 13:45:03                      8          -1.0\n",
       "9645           36 2019-04-01 14:30:04                      9          -1.0\n",
       "9646           36 2019-04-01 14:35:04                     10          -1.0\n",
       "9647           36 2019-04-01 14:40:03                     11          -2.0\n",
       "9649           36 2019-04-01 14:50:03                     12          -1.0\n",
       "9650           36 2019-04-01 14:55:03                     13          -1.0\n",
       "9652           36 2019-04-01 15:05:02                     14          -1.0\n",
       "9653           36 2019-04-01 15:10:04                     15          -1.0\n",
       "9654           36 2019-04-01 15:15:03                     16          -2.0\n",
       "9657           36 2019-04-01 15:30:04                     18          -3.0\n",
       "9659           36 2019-04-01 15:40:03                     21          -2.0\n",
       "9660           36 2019-04-01 15:45:03                     23          -1.0\n",
       "9661           36 2019-04-01 15:50:04                     24          -4.0\n",
       "9662           36 2019-04-01 15:55:03                     28          -4.0\n",
       "9663           36 2019-04-01 16:00:05                     32          -2.0\n",
       "9664           36 2019-04-01 16:05:02                     34          -1.0\n",
       "9665           36 2019-04-01 16:10:04                     35          -3.0\n",
       "9666           36 2019-04-01 16:15:03                     38          -1.0\n",
       "9670           36 2019-04-01 16:35:04                     38          -2.0\n",
       "9676           36 2019-04-01 17:05:03                     38          -1.0\n",
       "9677           36 2019-04-01 17:10:04                     39          -1.0\n",
       "9679           36 2019-04-01 17:20:04                     39          -1.0\n",
       "9697           36 2019-04-01 18:50:03                     39          -1.0\n",
       "9722           36 2019-04-01 20:55:02                     30          -1.0\n",
       "9729           36 2019-04-01 21:30:03                     31          -1.0\n",
       "9731           36 2019-04-01 21:40:04                     32          -1.0\n",
       "41984          36 2019-04-02 04:05:03                     31          -1.0\n",
       "41985          36 2019-04-02 04:10:03                     32          -1.0\n",
       "42000          36 2019-04-02 05:25:03                     28          -1.0\n",
       "42001          36 2019-04-02 05:30:02                     29          -9.0\n",
       "42002          36 2019-04-02 05:35:03                     38          -2.0\n",
       "42006          36 2019-04-02 05:55:03                     39          -1.0\n",
       "42030          36 2019-04-02 07:55:03                     10         -15.0\n",
       "42042          36 2019-04-02 08:55:03                     17          -1.0\n",
       "42045          36 2019-04-02 09:10:05                     18          -2.0\n",
       "42050          36 2019-04-02 09:35:03                     20          -1.0"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bike_flow[(bike_flow.BIKE_ARR_DEP < 0) & (bike_flow['STATION ID']==36)][['STATION ID', 'DATETIME', \n",
    "                                                          'AVAILABLE BIKE STANDS', \n",
    "                                                          'BIKE_ARR_DEP', \n",
    "                                                         ]].head(n=50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "609e9eea",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['STATION ID', 'DATETIME', 'AVAILABLE BIKE STANDS', 'BIKE_ARR_DEP'], dtype='object')"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bike_flow.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "39fed732",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.merge(bike_flow[['STATION ID', 'DATETIME', 'BIKE_ARR_DEP']], \n",
    "              on=['DATETIME', 'STATION ID'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "7a0e772d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['STATION ID', 'TIME', 'LAST UPDATED', 'BIKE STANDS',\n",
       "       'AVAILABLE BIKE STANDS', 'AVAILABLE BIKES', 'STATUS', 'DATETIME',\n",
       "       'DATE', 'YEAR', 'MONTH', 'DAY_NUMBER', 'HOUR', 'MINUTE',\n",
       "       'OCCUPANCY_PCT', 'FULL', 'EMPTY', 'BIKE_ARR_DEP'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0e55dfc",
   "metadata": {},
   "source": [
    "### Identify situations where bike stations are rebalanced i.e. bikes are re-distributed where needed\n",
    "What is the typical distribution of bike transactions within each interval? What values do we have at the extremes of the distrbution?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ce74aa40",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['BIKE_ARR_DEP_ABS'] = abs(df['BIKE_ARR_DEP'])  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "8f7d20d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:>"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXQAAAEDCAYAAAAlRP8qAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/YYfK9AAAACXBIWXMAAAsTAAALEwEAmpwYAAARDElEQVR4nO3dfYxldX3H8fensNrGsfiwZiSArlpSY6BVdgq2GjNrYoNopE1ogiEqVrOtwaqtpoJN1JqYahO0Cq1kK9SHUsb60EKR1FJhiyb1YRcXloWqq24iSFl16+Io0a5++8c92Mk4M/dh78y9++P9Sm72PPzOuZ/9ZffD3TPnHlJVSJKOfb8w6QCSpPGw0CWpERa6JDXCQpekRljoktQIC12SGjHRQk9yVZKDSe4YYOy7k+zpXl9J8r0NiChJx4xM8j70JM8BFoEPVdVpQxz3R8Azqur31y2cJB1jJvoJvapuAQ4t3ZbkKUn+NcnuJJ9J8tQVDn0xcM2GhJSkY8Txkw6wgh3AH1bVV5OcBfwN8NwHdyZ5IvAk4KYJ5ZOkqTRVhZ5kBvgt4KNJHtz88GXDzgc+VlU/2chskjTtpqrQ6V0C+l5VPX2NMecDF21MHEk6dkzVbYtVdT/wjSS/B5CeX39wf3c9/dHAf04ooiRNrUnftngNvXL+1SR3J3kFcAHwiiS3AfuAc5cccj6wUD4iUpJ+zkRvW5Qkjc9UXXKRJI1uYj8U3bx5c23ZsmWkY3/wgx/wiEc8YryBxmBac8H0ZjPXcMw1nBZz7d69+ztV9bgVd1bVRF5bt26tUd18880jH7uepjVX1fRmM9dwzDWcFnMBu2qVXvWSiyQ1wkKXpEZY6JLUCAtdkhphoUtSIyx0SWqEhS5JjbDQJakRFrokNWLanoc+kL33HObCiz858vEH3vGCMaaRpOngJ3RJaoSFLkmNsNAlqREWuiQ1wkKXpEZY6JLUiL6FnuSUJDcnuTPJviSvXWHMfJLDSfZ0rzevT1xJ0moGuQ/9CPD6qro1ySOB3UlurKo7l437TFW9cPwRJUmD6PsJvarurapbu+XvA3cBJ613MEnScNL7X9QNODjZAtwCnFZV9y/ZPg98HLgb+Bbwhqrat8Lx24HtALOzs1sXFhZGCn3w0GHue2CkQwE4/aQTRj94DYuLi8zMzKzLuY/WtGYz13DMNZwWc23btm13Vc2ttG/gQk8yA/wH8Paq+sSyfb8M/LSqFpOcA7ynqk5d63xzc3O1a9eugd57ucuuvpZL947+1IL1+ur/zp07mZ+fX5dzH61pzWau4ZhrOC3mSrJqoQ90l0uSTfQ+gV+9vMwBqur+qlrslm8ANiXZPFJaSdJIBrnLJcCVwF1V9a5Vxjy+G0eSM7vzfnecQSVJaxvkusWzgJcAe5Ps6ba9CXgCQFVdAZwHvCrJEeAB4Pwa5uK8JOmo9S30qvoskD5jLgcuH1coSdLw/KaoJDXCQpekRljoktQIC12SGmGhS1IjLHRJaoSFLkmNsNAlqREWuiQ1wkKXpEZY6JLUCAtdkhphoUtSIyx0SWqEhS5JjbDQJakRFrokNcJCl6RGWOiS1AgLXZIaYaFLUiMsdElqhIUuSY2w0CWpERa6JDXCQpekRljoktQIC12SGmGhS1IjLHRJakTfQk9ySpKbk9yZZF+S164wJknem2R/ktuTnLE+cSVJqzl+gDFHgNdX1a1JHgnsTnJjVd25ZMzzgVO711nA+7pfJUkbpO8n9Kq6t6pu7Za/D9wFnLRs2LnAh6rnc8Cjkpw49rSSpFWlqgYfnGwBbgFOq6r7l2y/HnhHVX22W/808Maq2rXs+O3AdoDZ2dmtCwsLI4U+eOgw9z0w0qEAnH7SCaMfvIbFxUVmZmbW5dxHa1qzmWs45hpOi7m2bdu2u6rmVto3yCUXAJLMAB8HXre0zIdRVTuAHQBzc3M1Pz8/ymm47OpruXTvwNF/zoELRnvffnbu3Mmov6f1Nq3ZzDUccw3noZZroLtckmyiV+ZXV9UnVhhyD3DKkvWTu22SpA0yyF0uAa4E7qqqd60y7Drgpd3dLs8EDlfVvWPMKUnqY5DrFs8CXgLsTbKn2/Ym4AkAVXUFcANwDrAf+CHw8rEnlSStqW+hdz/oTJ8xBVw0rlCSpOH5TVFJaoSFLkmNsNAlqREWuiQ1wkKXpEZY6JLUCAtdkhphoUtSIyx0SWqEhS5JjbDQJakRFrokNcJCl6RGWOiS1AgLXZIaYaFLUiMsdElqhIUuSY2w0CWpERa6JDXCQpekRljoktQIC12SGmGhS1IjLHRJaoSFLkmNsNAlqREWuiQ1wkKXpEZY6JLUCAtdkhrRt9CTXJXkYJI7Vtk/n+Rwkj3d683jjylJ6uf4AcZ8ALgc+NAaYz5TVS8cSyJJ0kj6fkKvqluAQxuQRZJ0FFJV/QclW4Drq+q0FfbNAx8H7ga+Bbyhqvatcp7twHaA2dnZrQsLCyOFPnjoMPc9MNKhAJx+0gmjH7yGxcVFZmZm1uXcR2tas5lrOOYaTou5tm3btruq5lbaN8gll35uBZ5YVYtJzgH+GTh1pYFVtQPYATA3N1fz8/MjveFlV1/LpXtHj37ggtHet5+dO3cy6u9pvU1rNnMNx1zDeajlOuq7XKrq/qpa7JZvADYl2XzUySRJQznqQk/y+CTpls/szvndoz2vJGk4fa9bJLkGmAc2J7kbeAuwCaCqrgDOA16V5AjwAHB+DXJhXpI0Vn0Lvape3Gf/5fRua5QkTZDfFJWkRljoktQIC12SGmGhS1IjLHRJaoSFLkmNsNAlqREWuiQ1wkKXpEZY6JLUCAtdkhphoUtSIyx0SWqEhS5JjbDQJakRFrokNcJCl6RGWOiS1AgLXZIaYaFLUiMsdElqhIUuSY2w0CWpERa6JDXCQpekRljoktQIC12SGmGhS1IjLHRJaoSFLkmN6FvoSa5KcjDJHavsT5L3Jtmf5PYkZ4w/piSpn0E+oX8AOHuN/c8HTu1e24H3HX0sSdKw+hZ6Vd0CHFpjyLnAh6rnc8Cjkpw4roCSpMGkqvoPSrYA11fVaSvsux54R1V9tlv/NPDGqtq1wtjt9D7FMzs7u3VhYWGk0AcPHea+B0Y6FIDTTzph9IPXsLi4yMzMzLqc+2hNazZzDcdcw2kx17Zt23ZX1dxK+44/qlRDqqodwA6Aubm5mp+fH+k8l119LZfuHT36gQtGe99+du7cyai/p/U2rdnMNRxzDeehlmscd7ncA5yyZP3kbpskaQONo9CvA17a3e3yTOBwVd07hvNKkobQ97pFkmuAeWBzkruBtwCbAKrqCuAG4BxgP/BD4OXrFVaStLq+hV5VL+6zv4CLxpZIkjQSvykqSY2w0CWpERa6JDXCQpekRljoktQIC12SGmGhS1IjLHRJaoSFLkmNsNAlqREWuiQ1wkKXpEZY6JLUCAtdkhphoUtSIyx0SWqEhS5JjbDQJakRFrokNcJCl6RGWOiS1AgLXZIaYaFLUiMsdElqhIUuSY2w0CWpERa6JDXCQpekRljoktQIC12SGjFQoSc5O8mXk+xPcvEK+y9M8u0ke7rXK8cfVZK0luP7DUhyHPDXwPOAu4EvJrmuqu5cNvQjVfXqdcgoSRrAIJ/QzwT2V9XXq+rHwAJw7vrGkiQNK1W19oDkPODsqnplt/4S4Kyln8aTXAj8BfBt4CvAH1fVN1c413ZgO8Ds7OzWhYWFkUIfPHSY+x4Y6VAATj/phNEPXsPi4iIzMzPrcu6jNa3ZzDUccw2nxVzbtm3bXVVzK+3re8llQP8CXFNVP0ryB8AHgecuH1RVO4AdAHNzczU/Pz/Sm1129bVcunf06AcuGO19+9m5cyej/p7W27RmM9dwzDWch1quQS653AOcsmT95G7bz1TVd6vqR93q+4Gt44knSRrUIIX+ReDUJE9K8jDgfOC6pQOSnLhk9UXAXeOLKEkaRN/rFlV1JMmrgU8BxwFXVdW+JG8DdlXVdcBrkrwIOAIcAi5cx8ySpBUMdCG6qm4Abli27c1Lli8BLhlvNEnSMPymqCQ1wkKXpEZY6JLUCAtdkhphoUtSIyx0SWqEhS5JjbDQJakRFrokNcJCl6RGWOiS1AgLXZIaYaFLUiMsdElqhIUuSY2w0CWpERa6JDXCQpekRljoktQIC12SGmGhS1IjLHRJaoSFLkmNsNAlqREWuiQ1wkKXpEYcP+kAk7Dl4k+OfOyBd7xgjEkkaXz8hC5JjbDQJakRFrokNcJCl6RGDFToSc5O8uUk+5NcvML+hyf5SLf/80m2jD2pJGlNfQs9yXHAXwPPB54GvDjJ05YNewXwP1X1K8C7gXeOO6gkaW2D3LZ4JrC/qr4OkGQBOBe4c8mYc4G3dssfAy5PkqqqMWadCmvd8vj6049w4Rr7veVR0noapNBPAr65ZP1u4KzVxlTVkSSHgccC31k6KMl2YHu3upjky6OEBjYvP/c0eE2fXJnsv1umcs4w17DMNZwWcz1xtR0b+sWiqtoB7Dja8yTZVVVzY4g0VtOaC6Y3m7mGY67hPNRyDfJD0XuAU5asn9xtW3FMkuOBE4DvjiOgJGkwgxT6F4FTkzwpycOA84Hrlo25DnhZt3wecFOL188laZr1veTSXRN/NfAp4Djgqqral+RtwK6qug64Evhwkv3AIXqlv56O+rLNOpnWXDC92cw1HHMN5yGVK36QlqQ2+E1RSWqEhS5JjTjmCr3fYwgmJcmBJHuT7Emya4I5rkpyMMkdS7Y9JsmNSb7a/froKcn11iT3dHO2J8k5E8h1SpKbk9yZZF+S13bbJzpna+Sa6Jwl+cUkX0hyW5frz7vtT+oe+7G/ewzIw6Yk1weSfGPJfD19I3MtyXdcki8lub5bX5/5qqpj5kXvh7JfA54MPAy4DXjapHN12Q4Am6cgx3OAM4A7lmz7S+Dibvli4J1TkuutwBsmPF8nAmd0y48EvkLvERcTnbM1ck10zoAAM93yJuDzwDOBfwTO77ZfAbxqSnJ9ADhvkn/Gukx/AvwDcH23vi7zdax9Qv/ZYwiq6sfAg48hUKeqbqF3p9FS5wIf7JY/CPzORmaCVXNNXFXdW1W3dsvfB+6i983nic7ZGrkmqnoWu9VN3auA59J77AdMZr5WyzVxSU4GXgC8v1sP6zRfx1qhr/QYgon/Ie8U8G9JdnePOJgms1V1b7f838DsJMMs8+okt3eXZDb8UtBS3VNCn0Hv093UzNmyXDDhOesuH+wBDgI30vtX8/eq6kg3ZCJ/L5fnqqoH5+vt3Xy9O8nDNzoX8FfAnwI/7dYfyzrN17FW6NPs2VV1Br2nUl6U5DmTDrSS6v0bbyo+uQDvA54CPB24F7h0UkGSzAAfB15XVfcv3TfJOVsh18TnrKp+UlVPp/et8TOBp250hpUsz5XkNOASevl+A3gM8MaNzJTkhcDBqtq9Ee93rBX6II8hmIiquqf79SDwT/T+oE+L+5KcCND9enDCeQCoqvu6v4Q/Bf6WCc1Zkk30SvPqqvpEt3nic7ZSrmmZsy7L94Cbgd8EHtU99gMm/PdySa6zu0tXVVU/Av6OjZ+vZwEvSnKA3iXi5wLvYZ3m61gr9EEeQ7DhkjwiySMfXAZ+G7hj7aM21NJHM7wMuHaCWX7mwcLs/C4TmLPueuaVwF1V9a4luyY6Z6vlmvScJXlckkd1y78EPI/e9f2b6T32AyYzXyvl+q8l/1EOvevUGzpfVXVJVZ1cVVvo9dVNVXUB6zVfk/7p7wg/LT6H3k/8vwb82aTzdJmeTO+Om9uAfZPMBVxD75/i/0vv2twr6F2z+zTwVeDfgcdMSa4PA3uB2+kV6IkTyPVsepdTbgf2dK9zJj1na+Sa6JwBvwZ8qXv/O4A3d9ufDHwB2A98FHj4lOS6qZuvO4C/p7sTZhIvYJ7/v8tlXebLr/5LUiOOtUsukqRVWOiS1AgLXZIaYaFLUiMsdElqhIUuSY2w0CWpEf8HSmtcB+8jTKYAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.BIKE_ARR_DEP_ABS.hist(bins=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "1dd565d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    2.750901e+07\n",
       "mean     2.644165e-01\n",
       "std      7.672442e-01\n",
       "min      0.000000e+00\n",
       "25%      0.000000e+00\n",
       "50%      0.000000e+00\n",
       "75%      0.000000e+00\n",
       "max      4.000000e+01\n",
       "Name: BIKE_ARR_DEP_ABS, dtype: float64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.BIKE_ARR_DEP_ABS.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "ced30b90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.500     0.0\n",
       "0.900     1.0\n",
       "0.950     1.0\n",
       "0.970     2.0\n",
       "0.980     2.0\n",
       "0.990     3.0\n",
       "0.999     9.0\n",
       "1.000    40.0\n",
       "Name: BIKE_ARR_DEP_ABS, dtype: float64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.BIKE_ARR_DEP_ABS.quantile([0.5, 0.9, 0.95, 0.97, 0.98, 0.99, 0.999, 1.0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "6ccfad72",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0     22434635\n",
       "1.0      3876106\n",
       "2.0       803952\n",
       "3.0       207633\n",
       "4.0        73174\n",
       "5.0        32572\n",
       "6.0        22610\n",
       "7.0        12953\n",
       "8.0        12488\n",
       "9.0         9974\n",
       "10.0        6717\n",
       "11.0        3638\n",
       "12.0        3489\n",
       "13.0        1938\n",
       "14.0        1761\n",
       "15.0        1196\n",
       "16.0        1196\n",
       "17.0         988\n",
       "18.0         907\n",
       "19.0         271\n",
       "20.0         184\n",
       "21.0          86\n",
       "22.0          65\n",
       "23.0          51\n",
       "25.0          44\n",
       "26.0          44\n",
       "30.0          43\n",
       "24.0          43\n",
       "28.0          37\n",
       "29.0          31\n",
       "40.0          31\n",
       "27.0          25\n",
       "34.0          19\n",
       "38.0          19\n",
       "33.0          16\n",
       "39.0          15\n",
       "32.0          15\n",
       "36.0          15\n",
       "31.0          14\n",
       "35.0          11\n",
       "37.0           8\n",
       "Name: BIKE_ARR_DEP_ABS, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.BIKE_ARR_DEP_ABS.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "928dd3ba",
   "metadata": {},
   "source": [
    "10 seems like a reasonable number. Are there any stations that have 10 or less bike stands?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "00f0b33d",
   "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>STATION ID</th>\n",
       "      <th>BIKE STANDS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>117.000000</td>\n",
       "      <td>117.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>64.076923</td>\n",
       "      <td>31.743590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>53.228712</td>\n",
       "      <td>8.081646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>32.000000</td>\n",
       "      <td>29.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>61.000000</td>\n",
       "      <td>30.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>90.000000</td>\n",
       "      <td>40.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>507.000000</td>\n",
       "      <td>40.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       STATION ID  BIKE STANDS\n",
       "count  117.000000   117.000000\n",
       "mean    64.076923    31.743590\n",
       "std     53.228712     8.081646\n",
       "min      2.000000     1.000000\n",
       "25%     32.000000    29.000000\n",
       "50%     61.000000    30.000000\n",
       "75%     90.000000    40.000000\n",
       "max    507.000000    40.000000"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[['STATION ID', 'BIKE STANDS']].drop_duplicates().describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "2d689bd9",
   "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>STATION ID</th>\n",
       "      <th>TIME</th>\n",
       "      <th>LAST UPDATED</th>\n",
       "      <th>BIKE STANDS</th>\n",
       "      <th>AVAILABLE BIKE STANDS</th>\n",
       "      <th>AVAILABLE BIKES</th>\n",
       "      <th>STATUS</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY_NUMBER</th>\n",
       "      <th>HOUR</th>\n",
       "      <th>MINUTE</th>\n",
       "      <th>OCCUPANCY_PCT</th>\n",
       "      <th>FULL</th>\n",
       "      <th>EMPTY</th>\n",
       "      <th>BIKE_ARR_DEP</th>\n",
       "      <th>BIKE_ARR_DEP_ABS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17491942</th>\n",
       "      <td>507</td>\n",
       "      <td>2021-02-17 13:05:02</td>\n",
       "      <td>2021-02-17 12:57:55</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Open</td>\n",
       "      <td>2021-02-17 13:05:02</td>\n",
       "      <td>2021-02-17</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17491943</th>\n",
       "      <td>507</td>\n",
       "      <td>2021-02-17 13:10:02</td>\n",
       "      <td>2021-02-17 13:08:01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Open</td>\n",
       "      <td>2021-02-17 13:10:02</td>\n",
       "      <td>2021-02-17</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>10</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17491944</th>\n",
       "      <td>507</td>\n",
       "      <td>2021-02-17 13:15:02</td>\n",
       "      <td>2021-02-17 13:08:01</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Open</td>\n",
       "      <td>2021-02-17 13:15:02</td>\n",
       "      <td>2021-02-17</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>15</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17491945</th>\n",
       "      <td>507</td>\n",
       "      <td>2021-02-17 13:20:02</td>\n",
       "      <td>2021-02-17 13:18:07</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Open</td>\n",
       "      <td>2021-02-17 13:20:02</td>\n",
       "      <td>2021-02-17</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>20</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17491946</th>\n",
       "      <td>507</td>\n",
       "      <td>2021-02-17 13:25:03</td>\n",
       "      <td>2021-02-17 13:18:07</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Open</td>\n",
       "      <td>2021-02-17 13:25:03</td>\n",
       "      <td>2021-02-17</td>\n",
       "      <td>2021</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>25</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27509010</th>\n",
       "      <td>507</td>\n",
       "      <td>2022-01-01 23:35:02</td>\n",
       "      <td>2021-11-18 07:11:16</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Open</td>\n",
       "      <td>2022-01-01 23:35:02</td>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>35</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27509011</th>\n",
       "      <td>507</td>\n",
       "      <td>2022-01-01 23:40:02</td>\n",
       "      <td>2021-11-18 07:11:16</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Open</td>\n",
       "      <td>2022-01-01 23:40:02</td>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>40</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27509012</th>\n",
       "      <td>507</td>\n",
       "      <td>2022-01-01 23:45:02</td>\n",
       "      <td>2021-11-18 07:11:16</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Open</td>\n",
       "      <td>2022-01-01 23:45:02</td>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>45</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27509013</th>\n",
       "      <td>507</td>\n",
       "      <td>2022-01-01 23:50:02</td>\n",
       "      <td>2021-11-18 07:11:16</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Open</td>\n",
       "      <td>2022-01-01 23:50:02</td>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>50</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27509014</th>\n",
       "      <td>507</td>\n",
       "      <td>2022-01-01 23:55:02</td>\n",
       "      <td>2021-11-18 07:11:16</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Open</td>\n",
       "      <td>2022-01-01 23:55:02</td>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>23</td>\n",
       "      <td>55</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13007 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          STATION ID                 TIME         LAST UPDATED  BIKE STANDS  \\\n",
       "17491942         507  2021-02-17 13:05:02  2021-02-17 12:57:55            1   \n",
       "17491943         507  2021-02-17 13:10:02  2021-02-17 13:08:01            1   \n",
       "17491944         507  2021-02-17 13:15:02  2021-02-17 13:08:01            1   \n",
       "17491945         507  2021-02-17 13:20:02  2021-02-17 13:18:07            1   \n",
       "17491946         507  2021-02-17 13:25:03  2021-02-17 13:18:07            1   \n",
       "...              ...                  ...                  ...          ...   \n",
       "27509010         507  2022-01-01 23:35:02  2021-11-18 07:11:16            1   \n",
       "27509011         507  2022-01-01 23:40:02  2021-11-18 07:11:16            1   \n",
       "27509012         507  2022-01-01 23:45:02  2021-11-18 07:11:16            1   \n",
       "27509013         507  2022-01-01 23:50:02  2021-11-18 07:11:16            1   \n",
       "27509014         507  2022-01-01 23:55:02  2021-11-18 07:11:16            1   \n",
       "\n",
       "          AVAILABLE BIKE STANDS  AVAILABLE BIKES STATUS            DATETIME  \\\n",
       "17491942                      0                1   Open 2021-02-17 13:05:02   \n",
       "17491943                      0                1   Open 2021-02-17 13:10:02   \n",
       "17491944                      0                1   Open 2021-02-17 13:15:02   \n",
       "17491945                      0                1   Open 2021-02-17 13:20:02   \n",
       "17491946                      0                1   Open 2021-02-17 13:25:03   \n",
       "...                         ...              ...    ...                 ...   \n",
       "27509010                      0                1   Open 2022-01-01 23:35:02   \n",
       "27509011                      0                1   Open 2022-01-01 23:40:02   \n",
       "27509012                      0                1   Open 2022-01-01 23:45:02   \n",
       "27509013                      0                1   Open 2022-01-01 23:50:02   \n",
       "27509014                      0                1   Open 2022-01-01 23:55:02   \n",
       "\n",
       "                DATE  YEAR  MONTH  DAY_NUMBER  HOUR  MINUTE  OCCUPANCY_PCT  \\\n",
       "17491942  2021-02-17  2021      2           2    13       5            1.0   \n",
       "17491943  2021-02-17  2021      2           2    13      10            1.0   \n",
       "17491944  2021-02-17  2021      2           2    13      15            1.0   \n",
       "17491945  2021-02-17  2021      2           2    13      20            1.0   \n",
       "17491946  2021-02-17  2021      2           2    13      25            1.0   \n",
       "...              ...   ...    ...         ...   ...     ...            ...   \n",
       "27509010  2022-01-01  2022      1           5    23      35            1.0   \n",
       "27509011  2022-01-01  2022      1           5    23      40            1.0   \n",
       "27509012  2022-01-01  2022      1           5    23      45            1.0   \n",
       "27509013  2022-01-01  2022      1           5    23      50            1.0   \n",
       "27509014  2022-01-01  2022      1           5    23      55            1.0   \n",
       "\n",
       "          FULL  EMPTY  BIKE_ARR_DEP  BIKE_ARR_DEP_ABS  \n",
       "17491942     0      1           0.0               0.0  \n",
       "17491943     0      1           0.0               0.0  \n",
       "17491944     0      1           0.0               0.0  \n",
       "17491945     0      1           0.0               0.0  \n",
       "17491946     0      1           0.0               0.0  \n",
       "...        ...    ...           ...               ...  \n",
       "27509010     0      1           0.0               0.0  \n",
       "27509011     0      1           0.0               0.0  \n",
       "27509012     0      1           0.0               0.0  \n",
       "27509013     0      1           0.0               0.0  \n",
       "27509014     0      1           NaN               NaN  \n",
       "\n",
       "[13007 rows x 19 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['BIKE STANDS'] < 10]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c92ba2e",
   "metadata": {},
   "source": [
    "Station 507 is a test station with 1 bike stand so we can ignore that.\n",
    "\n",
    "Some stations only have 16 bike stands while others have close to 40. Perhaps a ratio of absolute bike transaction in a interval to the number of bike stands would be more applicable?\n",
    "\n",
    "Could also possibly refine it further with an assumption like if a station has continuous occupancy of <= 10% or >= 90% for _n_ number of intervals in a row, and then sees a significant number of bike transactions for the next interval, then consider this rebalancing?\n",
    "\n",
    "It is hard to accurately deduce actual rebalancing activities.\n",
    "\n",
    "For the moment, we will stick with a threshold of 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "a9b3f376",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Attempt to create a distinction between rental and rebalancing.\n",
    "# Based on the stats from above, a threshold of 10 seems reasonable\n",
    "df['ACTIVITY_TYPE'] = np.where(df['BIKE_ARR_DEP_ABS'] >= 10, \"REBALANCING\", \"RENTAL\")\n",
    "df['IMBALANCED'] = np.where(df['OCCUPANCY_PCT'] < .1, 1, np.where(df['OCCUPANCY_PCT'] > .9, 1,0 ))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93b6ba12",
   "metadata": {},
   "source": [
    "### Drop any date from the JCDeaux test station"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "40bde005",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13007"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df[df['STATION ID'] == 507])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "383097b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['STATION ID'] != 507]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "258fcbd2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['STATION ID', 'TIME', 'LAST UPDATED', 'BIKE STANDS',\n",
       "       'AVAILABLE BIKE STANDS', 'AVAILABLE BIKES', 'STATUS', 'DATETIME',\n",
       "       'DATE', 'YEAR', 'MONTH', 'DAY_NUMBER', 'HOUR', 'MINUTE',\n",
       "       'OCCUPANCY_PCT', 'FULL', 'EMPTY', 'BIKE_ARR_DEP', 'BIKE_ARR_DEP_ABS',\n",
       "       'ACTIVITY_TYPE', 'IMBALANCED'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "4486df1a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 27496008 entries, 0 to 27508726\n",
      "Data columns (total 21 columns):\n",
      " #   Column                 Dtype         \n",
      "---  ------                 -----         \n",
      " 0   STATION ID             int64         \n",
      " 1   TIME                   object        \n",
      " 2   LAST UPDATED           object        \n",
      " 3   BIKE STANDS            int64         \n",
      " 4   AVAILABLE BIKE STANDS  int64         \n",
      " 5   AVAILABLE BIKES        int64         \n",
      " 6   STATUS                 object        \n",
      " 7   DATETIME               datetime64[ns]\n",
      " 8   DATE                   object        \n",
      " 9   YEAR                   int64         \n",
      " 10  MONTH                  int64         \n",
      " 11  DAY_NUMBER             int64         \n",
      " 12  HOUR                   int64         \n",
      " 13  MINUTE                 int64         \n",
      " 14  OCCUPANCY_PCT          float64       \n",
      " 15  FULL                   int64         \n",
      " 16  EMPTY                  int64         \n",
      " 17  BIKE_ARR_DEP           float64       \n",
      " 18  BIKE_ARR_DEP_ABS       float64       \n",
      " 19  ACTIVITY_TYPE          object        \n",
      " 20  IMBALANCED             int64         \n",
      "dtypes: datetime64[ns](1), float64(3), int64(12), object(5)\n",
      "memory usage: 4.5+ GB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7894bb54",
   "metadata": {},
   "source": [
    "## Write out to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "76305664",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Dropping:\n",
    "#'TIME': Now captured as a datetime object in 'DATETIME'\n",
    "#'LAST UPDATED': Captures when the data was last updated, not the actual data intervals\n",
    "#'BIKE STANDS': Already captured in the bike station level data\n",
    "#'STATUS': Only open stations are considered here, so this column contains no additional information.\n",
    "#'BIKE_ARR_DEP_ABS': Information already captured by 'BIKE_ARR_DEP',and no longer needed for the calcualtion of ACTIVITY_TYPE\n",
    "\n",
    "columns_of_interest = ['STATION ID', 'AVAILABLE BIKE STANDS', 'AVAILABLE BIKES', 'DATETIME', \n",
    "                       'DATE', 'YEAR', 'MONTH', 'DAY_NUMBER', 'HOUR', 'MINUTE', 'OCCUPANCY_PCT', \n",
    "                       'FULL', 'EMPTY', 'BIKE_ARR_DEP', 'ACTIVITY_TYPE', 'IMBALANCED']\n",
    "\n",
    "final = df[columns_of_interest].copy()\n",
    "final.to_csv(\"preprocessed_bike_data.csv\", index=False)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fa46f32",
   "metadata": {},
   "source": [
    "# Create pre-processed station data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "62b1f719",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 110 entries, 0 to 109\n",
      "Data columns (total 5 columns):\n",
      " #   Column      Non-Null Count  Dtype  \n",
      "---  ------      --------------  -----  \n",
      " 0   STATION ID  110 non-null    int64  \n",
      " 1   NAME        110 non-null    object \n",
      " 2   ADDRESS     110 non-null    object \n",
      " 3   LATITUDE    110 non-null    float64\n",
      " 4   LONGITUDE   110 non-null    float64\n",
      "dtypes: float64(2), int64(1), object(2)\n",
      "memory usage: 4.4+ KB\n"
     ]
    }
   ],
   "source": [
    "raw_station_df = pd.read_csv(\"data/raw_station_data.csv\")\n",
    "raw_station_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b391b05",
   "metadata": {},
   "source": [
    "## Extract the bike stand info from the bike transaction data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "945b7422",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 116 entries, 0 to 22387754\n",
      "Data columns (total 2 columns):\n",
      " #   Column       Non-Null Count  Dtype\n",
      "---  ------       --------------  -----\n",
      " 0   STATION ID   116 non-null    int64\n",
      " 1   BIKE STANDS  116 non-null    int64\n",
      "dtypes: int64(2)\n",
      "memory usage: 2.7 KB\n"
     ]
    }
   ],
   "source": [
    "bike_stand_info = df[['STATION ID', 'BIKE STANDS']].drop_duplicates()\n",
    "bike_stand_info.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "e644cf3a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "108    2\n",
       "2      1\n",
       "75     1\n",
       "87     1\n",
       "86     1\n",
       "      ..\n",
       "37     1\n",
       "36     1\n",
       "35     1\n",
       "34     1\n",
       "116    1\n",
       "Name: STATION ID, Length: 115, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bike_stand_info['STATION ID'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2bdac87",
   "metadata": {},
   "source": [
    "108 has two numbers for bike stands"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "7d17e7e4",
   "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>STATION ID</th>\n",
       "      <th>BIKE STANDS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30135</th>\n",
       "      <td>108</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22387754</th>\n",
       "      <td>108</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          STATION ID  BIKE STANDS\n",
       "30135            108           40\n",
       "22387754         108           35"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bike_stand_info[bike_stand_info['STATION ID'] == 108]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "91c5d679",
   "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></th>\n",
       "      <th>DATETIME</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BIKE STANDS</th>\n",
       "      <th>YEAR</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">35</th>\n",
       "      <th>2021</th>\n",
       "      <td>46397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2022</th>\n",
       "      <td>288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">40</th>\n",
       "      <th>2019</th>\n",
       "      <td>70753</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>74418</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2021</th>\n",
       "      <td>59691</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  DATETIME\n",
       "BIKE STANDS YEAR          \n",
       "35          2021     46397\n",
       "            2022       288\n",
       "40          2019     70753\n",
       "            2020     74418\n",
       "            2021     59691"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['STATION ID'] == 108][['BIKE STANDS', 'DATETIME', 'YEAR']].groupby(['BIKE STANDS', 'YEAR']).count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e79489a",
   "metadata": {},
   "source": [
    "Not an ideal solution, but just keep the number of bike stands as 40 for station 108 as it covers most of the time intervals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "3420e04b",
   "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>STATION ID</th>\n",
       "      <th>BIKE STANDS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>30135</th>\n",
       "      <td>108</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       STATION ID  BIKE STANDS\n",
       "30135         108           40"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# drop the row that we don't want using its index\n",
    "bike_stand_info = bike_stand_info.drop(axis='index', index=22387754)\n",
    "# Sanity check\n",
    "bike_stand_info[bike_stand_info['STATION ID'] == 108]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "51a858a7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 115 entries, 0 to 9000186\n",
      "Data columns (total 2 columns):\n",
      " #   Column       Non-Null Count  Dtype\n",
      "---  ------       --------------  -----\n",
      " 0   STATION ID   115 non-null    int64\n",
      " 1   BIKE STANDS  115 non-null    int64\n",
      "dtypes: int64(2)\n",
      "memory usage: 2.7 KB\n"
     ]
    }
   ],
   "source": [
    "bike_stand_info.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bda0edf5",
   "metadata": {},
   "source": [
    "Now have bike stand information. Combine with bike station information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "7269ab93",
   "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>STATION ID</th>\n",
       "      <th>BIKE STANDS</th>\n",
       "      <th>NAME</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>20</td>\n",
       "      <td>BLESSINGTON STREET</td>\n",
       "      <td>Blessington Street</td>\n",
       "      <td>53.356769</td>\n",
       "      <td>-6.268140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>20</td>\n",
       "      <td>BOLTON STREET</td>\n",
       "      <td>Bolton Street</td>\n",
       "      <td>53.351182</td>\n",
       "      <td>-6.269859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>20</td>\n",
       "      <td>GREEK STREET</td>\n",
       "      <td>Greek Street</td>\n",
       "      <td>53.346874</td>\n",
       "      <td>-6.272976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>40</td>\n",
       "      <td>CHARLEMONT PLACE</td>\n",
       "      <td>Charlemont Street</td>\n",
       "      <td>53.330662</td>\n",
       "      <td>-6.260177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>20</td>\n",
       "      <td>CHRISTCHURCH PLACE</td>\n",
       "      <td>Christchurch Place</td>\n",
       "      <td>53.343368</td>\n",
       "      <td>-6.270120</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATION ID  BIKE STANDS                NAME             ADDRESS   LATITUDE  \\\n",
       "0           2           20  BLESSINGTON STREET  Blessington Street  53.356769   \n",
       "1           3           20       BOLTON STREET       Bolton Street  53.351182   \n",
       "2           4           20        GREEK STREET        Greek Street  53.346874   \n",
       "3           5           40    CHARLEMONT PLACE   Charlemont Street  53.330662   \n",
       "4           6           20  CHRISTCHURCH PLACE  Christchurch Place  53.343368   \n",
       "\n",
       "   LONGITUDE  \n",
       "0  -6.268140  \n",
       "1  -6.269859  \n",
       "2  -6.272976  \n",
       "3  -6.260177  \n",
       "4  -6.270120  "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_df = bike_stand_info.merge(raw_station_df, how='outer')\n",
    "station_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "3649e2f7",
   "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>STATION ID</th>\n",
       "      <th>BIKE STANDS</th>\n",
       "      <th>NAME</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [STATION ID, BIKE STANDS, NAME, ADDRESS, LATITUDE, LONGITUDE]\n",
       "Index: []"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_df[station_df['STATION ID'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "7151f6d5",
   "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>STATION ID</th>\n",
       "      <th>BIKE STANDS</th>\n",
       "      <th>NAME</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>LONGITUDE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>14</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>35</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>46</td>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>60</td>\n",
       "      <td>30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>70</td>\n",
       "      <td>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    STATION ID  BIKE STANDS NAME ADDRESS  LATITUDE  LONGITUDE\n",
       "12          14           30  NaN     NaN       NaN        NaN\n",
       "32          35           30  NaN     NaN       NaN        NaN\n",
       "43          46           20  NaN     NaN       NaN        NaN\n",
       "57          60           30  NaN     NaN       NaN        NaN\n",
       "67          70           28  NaN     NaN       NaN        NaN"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station_df[station_df.NAME.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "ca425994",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[35, 70, 46, 14, 60]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_stations = list(set(bike_stand_info['STATION ID']).difference(set(raw_station_df['STATION ID'])))\n",
    "missing_stations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "177d23e1",
   "metadata": {},
   "source": [
    "We have 4 station IDs that are present in the transactional bike data but are missing in the station level data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "1f70a4d7",
   "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>STATION ID</th>\n",
       "      <th>DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3444</th>\n",
       "      <td>14</td>\n",
       "      <td>2019-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3445</th>\n",
       "      <td>14</td>\n",
       "      <td>2019-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3446</th>\n",
       "      <td>14</td>\n",
       "      <td>2019-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3447</th>\n",
       "      <td>14</td>\n",
       "      <td>2019-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3448</th>\n",
       "      <td>14</td>\n",
       "      <td>2019-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27492882</th>\n",
       "      <td>60</td>\n",
       "      <td>2022-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27492883</th>\n",
       "      <td>60</td>\n",
       "      <td>2022-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27492884</th>\n",
       "      <td>60</td>\n",
       "      <td>2022-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27492885</th>\n",
       "      <td>60</td>\n",
       "      <td>2022-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27492886</th>\n",
       "      <td>60</td>\n",
       "      <td>2022-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>205471 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          STATION ID        DATE\n",
       "3444              14  2019-04-01\n",
       "3445              14  2019-04-01\n",
       "3446              14  2019-04-01\n",
       "3447              14  2019-04-01\n",
       "3448              14  2019-04-01\n",
       "...              ...         ...\n",
       "27492882          60  2022-01-01\n",
       "27492883          60  2022-01-01\n",
       "27492884          60  2022-01-01\n",
       "27492885          60  2022-01-01\n",
       "27492886          60  2022-01-01\n",
       "\n",
       "[205471 rows x 2 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['STATION ID'].isin(missing_stations)][['STATION ID', 'DATE']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "dbd611a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2019, 11, 2)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "station = 14\n",
    "tmp = df[df['STATION ID'] == station][['STATION ID', 'DATE']]\n",
    "tmp.DATE.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "425d96e6",
   "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>STATION</th>\n",
       "      <th>MIN</th>\n",
       "      <th>MAX</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>2019-04-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>2019-07-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>46</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>2019-05-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>2019-11-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>60</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>2022-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   STATION         MIN         MAX\n",
       "0       35  2019-04-01  2019-04-29\n",
       "1       70  2019-04-01  2019-07-17\n",
       "2       46  2019-04-01  2019-05-28\n",
       "3       14  2019-04-01  2019-11-02\n",
       "4       60  2019-04-01  2022-01-01"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "min_date = []\n",
    "max_date = []\n",
    "\n",
    "for station in missing_stations:\n",
    "    tmp = df[df['STATION ID'] == station][['STATION ID', 'DATE']]\n",
    "    min_date.append(tmp.DATE.min())\n",
    "    max_date.append(tmp.DATE.max())\n",
    "    \n",
    "missing_station_dates = pd.DataFrame(data={'STATION': missing_stations,\n",
    "                                          'MIN': min_date,\n",
    "                                          'MAX': max_date})\n",
    "\n",
    "missing_station_dates"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "434cf0b2",
   "metadata": {},
   "source": [
    "All of the above have max dates in the past, suggesting that these stations may no longer be in use. Station level information may only be provided for currently active stations.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c689c37",
   "metadata": {},
   "source": [
    "### Write station level information out to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "75099e00",
   "metadata": {},
   "outputs": [],
   "source": [
    "station_df.to_csv(\"preprocessed_station_level_data.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "606529ab",
   "metadata": {},
   "source": [
    "# Weather data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d0d6f25",
   "metadata": {},
   "source": [
    "Rachael Breslin considered weather conditions such as rain fall and temperature in her modelling. Surprisingly these showed very little importance in the model to predict the occupancy rate of bike stations.\n",
    "\n",
    "Wind related variables were not inclued in the original modelling. From personal experience of cycling around \n",
    "Dublin, I have found wind to be a personal influence on whether I would choose to cycle or not on a given day.\n",
    "Therefore it would be interesting to enhance the existing weather data with wind related data for this analysis.\n",
    "\n",
    "Historial weather data from various weather stations around the country is publically avaiable from \n",
    "https://www.met.ie/climate/available-data/historical-data .\n",
    "There are 3 weather stations in Dublin with data at an hourly level - Casement, Dublin airport and the Phoenix Park\n",
    "The Phoenix Park is located in the city centre near many of the bike stations, therefore it is the most relevant\n",
    "source of local weather conditions for the bike stations. However, it lacks data on wind direction and speed.\n",
    "Therefore, I took this from Dublin airport, and combined it with the Phoenix Park data in one csv merging on day and \n",
    "hourly intervals for the time period covered by the bike transaction data, to give an pre-processed weather file, enhanced with wind related data.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4776482",
   "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.10.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}