scrape_clean.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Required Packages\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import datetime\n",
    "from datetime import timedelta\n",
    "import urllib.request\n",
    "import matplotlib.pyplot as plt\n",
    "import pickle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Setup Configs\n",
    "%matplotlib inline\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', 25)\n",
    "pd.set_option('display.precision', 3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Turnstile Data Import**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Functions to retreive MTA turnstile data from http://web.mta.info/developers/turnstile.html\n",
    "\n",
    "def get_next_weekday(startdate, weekday):\n",
    "    \"\"\"\n",
    "    @startdate: given date, in format '2019-07-25'\n",
    "    @weekday: week day as a integer, between 0 (Monday) to 6 (Sunday)\n",
    "    \"\"\"\n",
    "    d = datetime.datetime.strptime(startdate, '%Y-%m-%d')\n",
    "    t = timedelta((7 + weekday - d.weekday()) % 7)\n",
    "    return (d + t).strftime('%Y-%m-%d')\n",
    "\n",
    "def get_weekly_days(startdate,enddate):\n",
    "    \"\"\"\n",
    "    @startdate: given date, in format '2019-07-25'. Must start on desired weekday\n",
    "    @enddate: given date, in format '2019-07-25'. \n",
    "    \"\"\"\n",
    "    return pd.date_range(start=startdate, end=enddate, freq=\"7D\")\n",
    "    \n",
    "\n",
    "def mta_import(startdate,enddate):\n",
    "    \"\"\"\n",
    "    @startdate: given date, in format '2019-07-25'. Must start on desired weekday\n",
    "    @enddate: given date, in format '2019-07-25'. \n",
    "    \"\"\"\n",
    "    base_url = [\"http://web.mta.info/developers/data/nyct/turnstile/turnstile_\",\".txt\"]\n",
    "    date_range = get_weekly_days(get_next_weekday(startdate,5),enddate)\n",
    "    df_list = []\n",
    "    for day in date_range:\n",
    "        try:\n",
    "            df_list.append(pd.read_csv(day.strftime(\"%y%m%d\").join(base_url)))\n",
    "            print(\"Getting turnstile data for \" + day.strftime(\"%Y-%m-%d\"))\n",
    "        except urllib.error.HTTPError as err:\n",
    "            print(\"Failed to retreive turnstile data for \" + day.strftime(\"%Y-%m-%d\"))\n",
    "    return pd.concat(df_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Getting turnstile data for 2019-04-06\n",
      "Getting turnstile data for 2019-04-13\n",
      "Getting turnstile data for 2019-04-20\n",
      "Getting turnstile data for 2019-04-27\n",
      "Getting turnstile data for 2019-05-04\n",
      "Getting turnstile data for 2019-05-11\n",
      "Getting turnstile data for 2019-05-18\n",
      "Getting turnstile data for 2019-05-25\n",
      "Getting turnstile data for 2019-06-01\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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DIVISION</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>DESC</th>\n",
       "      <th>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>BMT</td>\n",
       "      <td>03/30/2019</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6999064</td>\n",
       "      <td>2373568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>BMT</td>\n",
       "      <td>03/30/2019</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6999084</td>\n",
       "      <td>2373576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>BMT</td>\n",
       "      <td>03/30/2019</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6999107</td>\n",
       "      <td>2373622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>BMT</td>\n",
       "      <td>03/30/2019</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6999214</td>\n",
       "      <td>2373710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>BMT</td>\n",
       "      <td>03/30/2019</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6999451</td>\n",
       "      <td>2373781</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP STATION LINENAME DIVISION        DATE      TIME  \\\n",
       "0  A002  R051  02-00-00   59 ST  NQR456W      BMT  03/30/2019  00:00:00   \n",
       "1  A002  R051  02-00-00   59 ST  NQR456W      BMT  03/30/2019  04:00:00   \n",
       "2  A002  R051  02-00-00   59 ST  NQR456W      BMT  03/30/2019  08:00:00   \n",
       "3  A002  R051  02-00-00   59 ST  NQR456W      BMT  03/30/2019  12:00:00   \n",
       "4  A002  R051  02-00-00   59 ST  NQR456W      BMT  03/30/2019  16:00:00   \n",
       "\n",
       "      DESC  ENTRIES  \\\n",
       "0  REGULAR  6999064   \n",
       "1  REGULAR  6999084   \n",
       "2  REGULAR  6999107   \n",
       "3  REGULAR  6999214   \n",
       "4  REGULAR  6999451   \n",
       "\n",
       "   EXITS                                                                 \n",
       "0                                            2373568                     \n",
       "1                                            2373576                     \n",
       "2                                            2373622                     \n",
       "3                                            2373710                     \n",
       "4                                            2373781                     "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mta_data = mta_import(\"2019-04-01\",\"2019-06-01\").reset_index(drop=True)\n",
    "unique_id = ['C/A','UNIT','SCP','STATION'] #unique turnstile reference used later on for grouping\n",
    "mta_data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#generating date and datetime values\n",
    "mta_data['DATETIME'] = mta_data['DATE'] + ' ' + mta_data['TIME']\n",
    "mta_data['DATETIME'] = pd.to_datetime(mta_data['DATETIME'], format = '%m/%d/%Y %H:%M:%S')\n",
    "mta_data['DATE'] = mta_data['DATETIME'].dt.date\n",
    "mta_data['TIME'] = mta_data['DATETIME'].dt.time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Pre Transform Cleanup**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>913339</th>\n",
       "      <td>N525</td>\n",
       "      <td>R142</td>\n",
       "      <td>01-00-03</td>\n",
       "      <td>DELANCEY/ESSEX</td>\n",
       "      <td>2019-05-11 05:00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>362666</th>\n",
       "      <td>JFK03</td>\n",
       "      <td>R536</td>\n",
       "      <td>00-00-01</td>\n",
       "      <td>JFK JAMAICA CT1</td>\n",
       "      <td>2019-04-07 01:00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>913721</th>\n",
       "      <td>N525</td>\n",
       "      <td>R142</td>\n",
       "      <td>01-00-04</td>\n",
       "      <td>DELANCEY/ESSEX</td>\n",
       "      <td>2019-05-11 05:00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1504257</th>\n",
       "      <td>R283</td>\n",
       "      <td>R221</td>\n",
       "      <td>00-00-01</td>\n",
       "      <td>167 ST</td>\n",
       "      <td>2019-04-15 12:00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1504642</th>\n",
       "      <td>R283</td>\n",
       "      <td>R221</td>\n",
       "      <td>00-00-02</td>\n",
       "      <td>167 ST</td>\n",
       "      <td>2019-04-16 04:00:00</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           C/A  UNIT       SCP          STATION            DATETIME  ENTRIES\n",
       "913339    N525  R142  01-00-03   DELANCEY/ESSEX 2019-05-11 05:00:00        2\n",
       "362666   JFK03  R536  00-00-01  JFK JAMAICA CT1 2019-04-07 01:00:00        2\n",
       "913721    N525  R142  01-00-04   DELANCEY/ESSEX 2019-05-11 05:00:00        2\n",
       "1504257   R283  R221  00-00-01           167 ST 2019-04-15 12:00:00        2\n",
       "1504642   R283  R221  00-00-02           167 ST 2019-04-16 04:00:00        2"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sanity check to verify that \"C/A\", \"UNIT\", \"SCP\", \"STATION\", \"DATETIME\" is unique\n",
    "(mta_data\n",
    " .groupby(unique_id + [\"DATETIME\"])  \n",
    " .ENTRIES.count()\n",
    " .reset_index()  # or use as_index = False; otherwise makes groupby columns new index \n",
    " .sort_values(\"ENTRIES\", ascending=False)).head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "REGULAR       1832524\n",
       "RECOVR AUD       8339\n",
       "Name: DESC, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mta_data.DESC.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get rid of the duplicate entry\n",
    "mta_data.sort_values(unique_id + [\"DATETIME\"], inplace=True, \\\n",
    "                          ascending=False)\n",
    "mta_data.drop_duplicates(subset= unique_id + [\"DATETIME\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>2019-03-30 00:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1227142</th>\n",
       "      <td>R142</td>\n",
       "      <td>R293</td>\n",
       "      <td>01-00-02</td>\n",
       "      <td>34 ST-PENN STA</td>\n",
       "      <td>2019-05-17 06:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1227224</th>\n",
       "      <td>R142</td>\n",
       "      <td>R293</td>\n",
       "      <td>01-00-02</td>\n",
       "      <td>34 ST-PENN STA</td>\n",
       "      <td>2019-05-30 06:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1227223</th>\n",
       "      <td>R142</td>\n",
       "      <td>R293</td>\n",
       "      <td>01-00-02</td>\n",
       "      <td>34 ST-PENN STA</td>\n",
       "      <td>2019-05-30 02:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1227222</th>\n",
       "      <td>R142</td>\n",
       "      <td>R293</td>\n",
       "      <td>01-00-02</td>\n",
       "      <td>34 ST-PENN STA</td>\n",
       "      <td>2019-05-29 22:00:00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          C/A  UNIT       SCP         STATION            DATETIME  ENTRIES\n",
       "0        A002  R051  02-00-00           59 ST 2019-03-30 00:00:00        1\n",
       "1227142  R142  R293  01-00-02  34 ST-PENN STA 2019-05-17 06:00:00        1\n",
       "1227224  R142  R293  01-00-02  34 ST-PENN STA 2019-05-30 06:00:00        1\n",
       "1227223  R142  R293  01-00-02  34 ST-PENN STA 2019-05-30 02:00:00        1\n",
       "1227222  R142  R293  01-00-02  34 ST-PENN STA 2019-05-29 22:00:00        1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sanity check to verify that \"C/A\", \"UNIT\", \"SCP\", \"STATION\", \"DATETIME\" is unique\n",
    "(mta_data\n",
    " .groupby(unique_id + [\"DATETIME\"])  \n",
    " .ENTRIES.count()\n",
    " .reset_index()  # or use as_index = False; otherwise makes groupby columns new index \n",
    " .sort_values(\"ENTRIES\", ascending=False)).head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "mta_data.columns = [column.strip() for column in mta_data.columns]\n",
    "#Reordered columns and dropped \"DESC\" and \"DIVISION\" columns \n",
    "mta_data = mta_data[unique_id + ['LINENAME','DATETIME','DATE','TIME','ENTRIES','EXITS']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>TIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00:00:00</td>\n",
       "      <td>155512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8529</th>\n",
       "      <td>04:00:00</td>\n",
       "      <td>155487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35356</th>\n",
       "      <td>16:00:00</td>\n",
       "      <td>155475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17319</th>\n",
       "      <td>08:00:00</td>\n",
       "      <td>155463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26748</th>\n",
       "      <td>12:00:00</td>\n",
       "      <td>155420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44032</th>\n",
       "      <td>20:00:00</td>\n",
       "      <td>155352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19872</th>\n",
       "      <td>09:00:00</td>\n",
       "      <td>114250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37543</th>\n",
       "      <td>17:00:00</td>\n",
       "      <td>114184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10665</th>\n",
       "      <td>05:00:00</td>\n",
       "      <td>114156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46228</th>\n",
       "      <td>21:00:00</td>\n",
       "      <td>114145</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           TIME  ENTRIES\n",
       "0      00:00:00   155512\n",
       "8529   04:00:00   155487\n",
       "35356  16:00:00   155475\n",
       "17319  08:00:00   155463\n",
       "26748  12:00:00   155420\n",
       "44032  20:00:00   155352\n",
       "19872  09:00:00   114250\n",
       "37543  17:00:00   114184\n",
       "10665  05:00:00   114156\n",
       "46228  21:00:00   114145"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#checking the number of time \"buckets\" in the dataset - we'd prefer to work with the same set of 6 times\n",
    "(mta_data\n",
    " .groupby(['TIME'])  \n",
    " .ENTRIES.count()\n",
    " .reset_index()  # or use as_index = False; otherwise makes groupby columns new index \n",
    " .sort_values(\"ENTRIES\", ascending=False)).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Rounding all datetimes to the nearest 0:00,4:00,8:00,12:00,16:00,20:00 - breaks on YoY changes\n",
    "def datetime_round(dt):\n",
    "    rounding = 4*round((float(dt.hour) + float(dt.minute)/60 + float(dt.second)/60/60) / 4)\n",
    "    if rounding == 24: #when a time rounds up to 24 hours, we have to switch to midnight of the next day\n",
    "        day = 1\n",
    "        hour = 0\n",
    "    else:\n",
    "        day = 0\n",
    "        hour = rounding\n",
    "    try:\n",
    "        return datetime.datetime(dt.year, dt.month, dt.day + day, hour)\n",
    "    except ValueError:\n",
    "        return datetime.datetime(dt.year, dt.month + 1, 1, hour)\n",
    "    \n",
    "mta_data_retime = mta_data.copy(deep=True)\n",
    "    \n",
    "mta_data_retime['DATETIME'] = mta_data_retime['DATETIME'].apply(datetime_round)\n",
    "                                                  \n",
    "mta_data_retime['DATE'] = mta_data_retime['DATETIME'].dt.date\n",
    "mta_data_retime['TIME'] = mta_data_retime['DATETIME'].dt.time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>TIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>08:00:00</td>\n",
       "      <td>325270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16:00:00</td>\n",
       "      <td>312065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00:00:00</td>\n",
       "      <td>309851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12:00:00</td>\n",
       "      <td>300219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20:00:00</td>\n",
       "      <td>297394</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>04:00:00</td>\n",
       "      <td>296026</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       TIME  ENTRIES\n",
       "2  08:00:00   325270\n",
       "4  16:00:00   312065\n",
       "0  00:00:00   309851\n",
       "3  12:00:00   300219\n",
       "5  20:00:00   297394\n",
       "1  04:00:00   296026"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#checking the evenness of time buckets - they should hopefully be even\n",
    "(mta_data_retime\n",
    " .groupby(['TIME'])  \n",
    " .ENTRIES.count()\n",
    " .reset_index()  # or use as_index = False; otherwise makes groupby columns new index \n",
    " .sort_values(\"ENTRIES\", ascending=False)).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1327379</th>\n",
       "      <td>R210</td>\n",
       "      <td>R044</td>\n",
       "      <td>00-03-01</td>\n",
       "      <td>BROOKLYN BRIDGE</td>\n",
       "      <td>2019-05-14 20:00:00</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1327748</th>\n",
       "      <td>R210</td>\n",
       "      <td>R044</td>\n",
       "      <td>00-03-02</td>\n",
       "      <td>BROOKLYN BRIDGE</td>\n",
       "      <td>2019-05-14 20:00:00</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1326267</th>\n",
       "      <td>R210</td>\n",
       "      <td>R044</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>BROOKLYN BRIDGE</td>\n",
       "      <td>2019-05-14 20:00:00</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1328859</th>\n",
       "      <td>R210</td>\n",
       "      <td>R044</td>\n",
       "      <td>00-03-05</td>\n",
       "      <td>BROOKLYN BRIDGE</td>\n",
       "      <td>2019-05-14 20:00:00</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1327009</th>\n",
       "      <td>R210</td>\n",
       "      <td>R044</td>\n",
       "      <td>00-03-00</td>\n",
       "      <td>BROOKLYN BRIDGE</td>\n",
       "      <td>2019-05-14 20:00:00</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1329232</th>\n",
       "      <td>R210</td>\n",
       "      <td>R044</td>\n",
       "      <td>00-05-00</td>\n",
       "      <td>BROOKLYN BRIDGE</td>\n",
       "      <td>2019-05-14 20:00:00</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1329610</th>\n",
       "      <td>R210</td>\n",
       "      <td>R044</td>\n",
       "      <td>00-05-01</td>\n",
       "      <td>BROOKLYN BRIDGE</td>\n",
       "      <td>2019-05-14 20:00:00</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1330340</th>\n",
       "      <td>R210</td>\n",
       "      <td>R044</td>\n",
       "      <td>00-06-01</td>\n",
       "      <td>BROOKLYN BRIDGE</td>\n",
       "      <td>2019-05-14 20:00:00</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1329966</th>\n",
       "      <td>R210</td>\n",
       "      <td>R044</td>\n",
       "      <td>00-06-00</td>\n",
       "      <td>BROOKLYN BRIDGE</td>\n",
       "      <td>2019-05-14 20:00:00</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1328487</th>\n",
       "      <td>R210</td>\n",
       "      <td>R044</td>\n",
       "      <td>00-03-04</td>\n",
       "      <td>BROOKLYN BRIDGE</td>\n",
       "      <td>2019-05-14 20:00:00</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          C/A  UNIT       SCP          STATION            DATETIME  ENTRIES\n",
       "1327379  R210  R044  00-03-01  BROOKLYN BRIDGE 2019-05-14 20:00:00       66\n",
       "1327748  R210  R044  00-03-02  BROOKLYN BRIDGE 2019-05-14 20:00:00       66\n",
       "1326267  R210  R044  00-00-00  BROOKLYN BRIDGE 2019-05-14 20:00:00       66\n",
       "1328859  R210  R044  00-03-05  BROOKLYN BRIDGE 2019-05-14 20:00:00       66\n",
       "1327009  R210  R044  00-03-00  BROOKLYN BRIDGE 2019-05-14 20:00:00       66\n",
       "1329232  R210  R044  00-05-00  BROOKLYN BRIDGE 2019-05-14 20:00:00       66\n",
       "1329610  R210  R044  00-05-01  BROOKLYN BRIDGE 2019-05-14 20:00:00       66\n",
       "1330340  R210  R044  00-06-01  BROOKLYN BRIDGE 2019-05-14 20:00:00       66\n",
       "1329966  R210  R044  00-06-00  BROOKLYN BRIDGE 2019-05-14 20:00:00       66\n",
       "1328487  R210  R044  00-03-04  BROOKLYN BRIDGE 2019-05-14 20:00:00       66"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Checking to see if duplicate time entries were made (they were)\n",
    "(mta_data_retime\n",
    " .groupby(unique_id + [\"DATETIME\"])  \n",
    " .ENTRIES.count()\n",
    " .reset_index()  # or use as_index = False; otherwise makes groupby columns new index \n",
    " .sort_values(\"ENTRIES\", ascending=False)).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [C/A, UNIT, SCP, STATION, LINENAME, DATETIME, DATE, TIME, ENTRIES, EXITS]\n",
       "Index: []"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#checking to see the problem days/turnstiles where many rows of the same time stamp are created\n",
    "mask = ((mta_data[\"C/A\"] == \"R229\") & \n",
    "        (mta_data[\"UNIT\"] == \"R143\") & \n",
    "        (mta_data[\"SCP\"] == \"01-00-03\") & \n",
    "        (mta_data[\"STATION\"] == \"28 ST\") &\n",
    "        (mta_data[\"DATE\"] == datetime.date(2019,6,25)))\n",
    "\n",
    "mta_data[mask].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 00:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>6999064</td>\n",
       "      <td>2373568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 04:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>6999084</td>\n",
       "      <td>2373576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>6999107</td>\n",
       "      <td>2373622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 12:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>6999214</td>\n",
       "      <td>2373710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 16:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>6999451</td>\n",
       "      <td>2373781</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP STATION LINENAME            DATETIME        DATE  \\\n",
       "0  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 00:00:00  2019-03-30   \n",
       "1  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 04:00:00  2019-03-30   \n",
       "2  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 08:00:00  2019-03-30   \n",
       "3  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 12:00:00  2019-03-30   \n",
       "4  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 16:00:00  2019-03-30   \n",
       "\n",
       "       TIME  ENTRIES    EXITS  \n",
       "0  00:00:00  6999064  2373568  \n",
       "1  04:00:00  6999084  2373576  \n",
       "2  08:00:00  6999107  2373622  \n",
       "3  12:00:00  6999214  2373710  \n",
       "4  16:00:00  6999451  2373781  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#the working theory is that the majority of the repeats are from too many observations in the same 4hr period\n",
    "#rather than incorrect bucketing - will confirm by checking time differences once column diffs are taken\n",
    "\n",
    "#taking the maximum entry and exit for each turnstile/datetime combo\n",
    "\n",
    "mta_data_max = (mta_data_retime\n",
    "                .groupby(unique_id + ['LINENAME','DATETIME','DATE','TIME'])[['ENTRIES','EXITS']]  \n",
    "                .max()\n",
    "                .reset_index())\n",
    "mta_data_max.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>TIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>08:00:00</td>\n",
       "      <td>302751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16:00:00</td>\n",
       "      <td>302591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00:00:00</td>\n",
       "      <td>302426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>04:00:00</td>\n",
       "      <td>295525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12:00:00</td>\n",
       "      <td>295489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>20:00:00</td>\n",
       "      <td>295198</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       TIME  ENTRIES\n",
       "2  08:00:00   302751\n",
       "4  16:00:00   302591\n",
       "0  00:00:00   302426\n",
       "1  04:00:00   295525\n",
       "3  12:00:00   295489\n",
       "5  20:00:00   295198"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#checking the evenness of time buckets - they should hopefully be even\n",
    "(mta_data_max\n",
    " .groupby(['TIME'])  \n",
    " .ENTRIES.count()\n",
    " .reset_index()  # or use as_index = False; otherwise makes groupby columns new index \n",
    " .sort_values(\"ENTRIES\", ascending=False)).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 00:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>6999064</td>\n",
       "      <td>2373568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 04:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>6999084</td>\n",
       "      <td>2373576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>6999107</td>\n",
       "      <td>2373622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 12:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>6999214</td>\n",
       "      <td>2373710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 16:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>6999451</td>\n",
       "      <td>2373781</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP STATION LINENAME            DATETIME        DATE  \\\n",
       "0  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 00:00:00  2019-03-30   \n",
       "1  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 04:00:00  2019-03-30   \n",
       "2  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 08:00:00  2019-03-30   \n",
       "3  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 12:00:00  2019-03-30   \n",
       "4  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 16:00:00  2019-03-30   \n",
       "\n",
       "       TIME  ENTRIES    EXITS  \n",
       "0  00:00:00  6999064  2373568  \n",
       "1  04:00:00  6999084  2373576  \n",
       "2  08:00:00  6999107  2373622  \n",
       "3  12:00:00  6999214  2373710  \n",
       "4  16:00:00  6999451  2373781  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mta_data_sorted = mta_data_max.sort_values(unique_id + [\"DATETIME\"], ascending = [True for _ in unique_id] + [True])\n",
    "mta_data_sorted.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Calculating Per-Period Totals**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "      <th>NEXT_DATETIME</th>\n",
       "      <th>NEXT_ENTRIES</th>\n",
       "      <th>NEXT_EXITS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 00:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>6999064</td>\n",
       "      <td>2373568</td>\n",
       "      <td>2019-03-30 04:00:00</td>\n",
       "      <td>6.999e+06</td>\n",
       "      <td>2.374e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 04:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>6999084</td>\n",
       "      <td>2373576</td>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>6.999e+06</td>\n",
       "      <td>2.374e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>6999107</td>\n",
       "      <td>2373622</td>\n",
       "      <td>2019-03-30 12:00:00</td>\n",
       "      <td>6.999e+06</td>\n",
       "      <td>2.374e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 12:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>6999214</td>\n",
       "      <td>2373710</td>\n",
       "      <td>2019-03-30 16:00:00</td>\n",
       "      <td>6.999e+06</td>\n",
       "      <td>2.374e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 16:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>6999451</td>\n",
       "      <td>2373781</td>\n",
       "      <td>2019-03-30 20:00:00</td>\n",
       "      <td>7.000e+06</td>\n",
       "      <td>2.374e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP STATION LINENAME            DATETIME        DATE  \\\n",
       "0  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 00:00:00  2019-03-30   \n",
       "1  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 04:00:00  2019-03-30   \n",
       "2  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 08:00:00  2019-03-30   \n",
       "3  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 12:00:00  2019-03-30   \n",
       "4  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 16:00:00  2019-03-30   \n",
       "\n",
       "       TIME  ENTRIES    EXITS       NEXT_DATETIME  NEXT_ENTRIES  NEXT_EXITS  \n",
       "0  00:00:00  6999064  2373568 2019-03-30 04:00:00     6.999e+06   2.374e+06  \n",
       "1  04:00:00  6999084  2373576 2019-03-30 08:00:00     6.999e+06   2.374e+06  \n",
       "2  08:00:00  6999107  2373622 2019-03-30 12:00:00     6.999e+06   2.374e+06  \n",
       "3  12:00:00  6999214  2373710 2019-03-30 16:00:00     6.999e+06   2.374e+06  \n",
       "4  16:00:00  6999451  2373781 2019-03-30 20:00:00     7.000e+06   2.374e+06  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mta_data_sorted[[\"NEXT_DATETIME\", \"NEXT_ENTRIES\",\"NEXT_EXITS\"]] = (mta_data_sorted\n",
    "                                                       .groupby(unique_id)[\"DATETIME\", \"ENTRIES\",\"EXITS\"]\n",
    "                                                       .transform(lambda grp: grp.shift(-1)))\n",
    "# transform() takes a function as parameter\n",
    "# shift moves the index by the number of periods given (positive or negative)\n",
    "\n",
    "mta_data_sorted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1793980\n",
      "1789088\n"
     ]
    }
   ],
   "source": [
    "print(len(mta_data_sorted))\n",
    "mta_data_sorted = mta_data_sorted.dropna().reset_index(drop=True)\n",
    "print(len(mta_data_sorted))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "      <th>NEXT_DATETIME</th>\n",
       "      <th>NEXT_ENTRIES</th>\n",
       "      <th>NEXT_EXITS</th>\n",
       "      <th>TIME_DELTA</th>\n",
       "      <th>ENTRIES_DELTA</th>\n",
       "      <th>EXITS_DELTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 00:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>6999064</td>\n",
       "      <td>2373568</td>\n",
       "      <td>2019-03-30 04:00:00</td>\n",
       "      <td>6.999e+06</td>\n",
       "      <td>2.374e+06</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 04:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>6999084</td>\n",
       "      <td>2373576</td>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>6.999e+06</td>\n",
       "      <td>2.374e+06</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>23.0</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>6999107</td>\n",
       "      <td>2373622</td>\n",
       "      <td>2019-03-30 12:00:00</td>\n",
       "      <td>6.999e+06</td>\n",
       "      <td>2.374e+06</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>107.0</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 12:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>6999214</td>\n",
       "      <td>2373710</td>\n",
       "      <td>2019-03-30 16:00:00</td>\n",
       "      <td>6.999e+06</td>\n",
       "      <td>2.374e+06</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>237.0</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 16:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>6999451</td>\n",
       "      <td>2373781</td>\n",
       "      <td>2019-03-30 20:00:00</td>\n",
       "      <td>7.000e+06</td>\n",
       "      <td>2.374e+06</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>345.0</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP STATION LINENAME            DATETIME        DATE  \\\n",
       "0  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 00:00:00  2019-03-30   \n",
       "1  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 04:00:00  2019-03-30   \n",
       "2  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 08:00:00  2019-03-30   \n",
       "3  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 12:00:00  2019-03-30   \n",
       "4  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 16:00:00  2019-03-30   \n",
       "\n",
       "       TIME  ENTRIES    EXITS       NEXT_DATETIME  NEXT_ENTRIES  NEXT_EXITS  \\\n",
       "0  00:00:00  6999064  2373568 2019-03-30 04:00:00     6.999e+06   2.374e+06   \n",
       "1  04:00:00  6999084  2373576 2019-03-30 08:00:00     6.999e+06   2.374e+06   \n",
       "2  08:00:00  6999107  2373622 2019-03-30 12:00:00     6.999e+06   2.374e+06   \n",
       "3  12:00:00  6999214  2373710 2019-03-30 16:00:00     6.999e+06   2.374e+06   \n",
       "4  16:00:00  6999451  2373781 2019-03-30 20:00:00     7.000e+06   2.374e+06   \n",
       "\n",
       "  TIME_DELTA  ENTRIES_DELTA  EXITS_DELTA  \n",
       "0   04:00:00           20.0          8.0  \n",
       "1   04:00:00           23.0         46.0  \n",
       "2   04:00:00          107.0         88.0  \n",
       "3   04:00:00          237.0         71.0  \n",
       "4   04:00:00          345.0         56.0  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mta_data_sorted['TIME_DELTA'] = mta_data_sorted['NEXT_DATETIME'] - mta_data_sorted['DATETIME']\n",
    "mta_data_sorted['ENTRIES_DELTA'] = mta_data_sorted['NEXT_ENTRIES'] - mta_data_sorted['ENTRIES']\n",
    "mta_data_sorted['EXITS_DELTA'] = mta_data_sorted['NEXT_EXITS'] - mta_data_sorted['EXITS']\n",
    "mta_data_sorted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>TIME_DELTA</th>\n",
       "      <th>ENTRIES_DELTA</th>\n",
       "      <th>EXITS_DELTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 00:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 04:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>23.0</td>\n",
       "      <td>46.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>107.0</td>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 12:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>237.0</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 16:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>345.0</td>\n",
       "      <td>56.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP STATION LINENAME            DATETIME        DATE  \\\n",
       "0  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 00:00:00  2019-03-30   \n",
       "1  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 04:00:00  2019-03-30   \n",
       "2  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 08:00:00  2019-03-30   \n",
       "3  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 12:00:00  2019-03-30   \n",
       "4  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 16:00:00  2019-03-30   \n",
       "\n",
       "       TIME TIME_DELTA  ENTRIES_DELTA  EXITS_DELTA  \n",
       "0  00:00:00   04:00:00           20.0          8.0  \n",
       "1  04:00:00   04:00:00           23.0         46.0  \n",
       "2  08:00:00   04:00:00          107.0         88.0  \n",
       "3  12:00:00   04:00:00          237.0         71.0  \n",
       "4  16:00:00   04:00:00          345.0         56.0  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mta_data_delta = mta_data_sorted.drop(['ENTRIES','EXITS','NEXT_DATETIME','NEXT_ENTRIES','NEXT_EXITS'],axis=1)\n",
    "mta_data_delta.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>TIME_DELTA</th>\n",
       "      <th>ENTRIES_DELTA</th>\n",
       "      <th>EXITS_DELTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>47399</th>\n",
       "      <td>A038</td>\n",
       "      <td>R085</td>\n",
       "      <td>00-00-01</td>\n",
       "      <td>8 ST-NYU</td>\n",
       "      <td>NRW</td>\n",
       "      <td>2019-04-22 08:00:00</td>\n",
       "      <td>2019-04-22</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>0 days 08:00:00</td>\n",
       "      <td>-3.584e+06</td>\n",
       "      <td>-1.246e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48937</th>\n",
       "      <td>A038</td>\n",
       "      <td>R085</td>\n",
       "      <td>00-06-00</td>\n",
       "      <td>8 ST-NYU</td>\n",
       "      <td>NRW</td>\n",
       "      <td>2019-04-28 08:00:00</td>\n",
       "      <td>2019-04-28</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>0 days 12:00:00</td>\n",
       "      <td>1.610e+05</td>\n",
       "      <td>-2.376e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49510</th>\n",
       "      <td>A039</td>\n",
       "      <td>R085</td>\n",
       "      <td>01-00-01</td>\n",
       "      <td>8 ST-NYU</td>\n",
       "      <td>NRW</td>\n",
       "      <td>2019-03-30 16:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>0 days 08:00:00</td>\n",
       "      <td>-2.235e+06</td>\n",
       "      <td>-3.312e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54788</th>\n",
       "      <td>A042</td>\n",
       "      <td>R086</td>\n",
       "      <td>01-00-04</td>\n",
       "      <td>PRINCE ST</td>\n",
       "      <td>NRW</td>\n",
       "      <td>2019-04-01 16:00:00</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>-1.116e+05</td>\n",
       "      <td>-3.247e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60584</th>\n",
       "      <td>A046</td>\n",
       "      <td>R463</td>\n",
       "      <td>00-00-03</td>\n",
       "      <td>CANAL ST</td>\n",
       "      <td>JNQRZ6W</td>\n",
       "      <td>2019-04-28 00:00:00</td>\n",
       "      <td>2019-04-28</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>-1.274e+05</td>\n",
       "      <td>-1.029e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69513</th>\n",
       "      <td>A047</td>\n",
       "      <td>R087</td>\n",
       "      <td>00-06-02</td>\n",
       "      <td>CITY HALL</td>\n",
       "      <td>NRW</td>\n",
       "      <td>2019-04-10 12:00:00</td>\n",
       "      <td>2019-04-10</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>1 days 04:00:00</td>\n",
       "      <td>-3.138e+04</td>\n",
       "      <td>-4.044e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101391</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-03-30 00:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>1.200e+01</td>\n",
       "      <td>-1.100e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101392</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-03-30 04:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>2.200e+01</td>\n",
       "      <td>-8.000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101393</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>3.100e+01</td>\n",
       "      <td>-8.670e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101394</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-03-30 12:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>4.600e+01</td>\n",
       "      <td>-1.239e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101395</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-03-30 16:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>5.000e+01</td>\n",
       "      <td>-1.107e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101396</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-03-30 20:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>20:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>4.800e+01</td>\n",
       "      <td>-3.300e+02</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101423</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-04 08:00:00</td>\n",
       "      <td>2019-04-04</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>6.300e+01</td>\n",
       "      <td>-1.894e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101424</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-04 12:00:00</td>\n",
       "      <td>2019-04-04</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>1.950e+02</td>\n",
       "      <td>-1.231e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101425</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-04 16:00:00</td>\n",
       "      <td>2019-04-04</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>4.100e+02</td>\n",
       "      <td>-7.540e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101426</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-04 20:00:00</td>\n",
       "      <td>2019-04-04</td>\n",
       "      <td>20:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>6.400e+01</td>\n",
       "      <td>-2.540e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101427</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-05 00:00:00</td>\n",
       "      <td>2019-04-05</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>1.400e+01</td>\n",
       "      <td>-2.600e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101428</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-05 04:00:00</td>\n",
       "      <td>2019-04-05</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>3.700e+01</td>\n",
       "      <td>-3.680e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101429</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-05 08:00:00</td>\n",
       "      <td>2019-04-05</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>5.700e+01</td>\n",
       "      <td>-1.691e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101430</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-05 12:00:00</td>\n",
       "      <td>2019-04-05</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>1.770e+02</td>\n",
       "      <td>-9.790e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101431</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-05 16:00:00</td>\n",
       "      <td>2019-04-05</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>4.320e+02</td>\n",
       "      <td>-5.120e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101432</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-05 20:00:00</td>\n",
       "      <td>2019-04-05</td>\n",
       "      <td>20:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>7.200e+01</td>\n",
       "      <td>-2.810e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101433</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-06 00:00:00</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>1.700e+01</td>\n",
       "      <td>-6.600e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101434</th>\n",
       "      <td>A069</td>\n",
       "      <td>R044</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>CHAMBERS ST</td>\n",
       "      <td>JZ456</td>\n",
       "      <td>2019-04-06 04:00:00</td>\n",
       "      <td>2019-04-06</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>3.600e+01</td>\n",
       "      <td>-6.900e+01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         C/A  UNIT       SCP      STATION LINENAME            DATETIME  \\\n",
       "47399   A038  R085  00-00-01     8 ST-NYU      NRW 2019-04-22 08:00:00   \n",
       "48937   A038  R085  00-06-00     8 ST-NYU      NRW 2019-04-28 08:00:00   \n",
       "49510   A039  R085  01-00-01     8 ST-NYU      NRW 2019-03-30 16:00:00   \n",
       "54788   A042  R086  01-00-04    PRINCE ST      NRW 2019-04-01 16:00:00   \n",
       "60584   A046  R463  00-00-03     CANAL ST  JNQRZ6W 2019-04-28 00:00:00   \n",
       "69513   A047  R087  00-06-02    CITY HALL      NRW 2019-04-10 12:00:00   \n",
       "101391  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-03-30 00:00:00   \n",
       "101392  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-03-30 04:00:00   \n",
       "101393  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-03-30 08:00:00   \n",
       "101394  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-03-30 12:00:00   \n",
       "101395  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-03-30 16:00:00   \n",
       "101396  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-03-30 20:00:00   \n",
       "...      ...   ...       ...          ...      ...                 ...   \n",
       "101423  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-04 08:00:00   \n",
       "101424  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-04 12:00:00   \n",
       "101425  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-04 16:00:00   \n",
       "101426  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-04 20:00:00   \n",
       "101427  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-05 00:00:00   \n",
       "101428  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-05 04:00:00   \n",
       "101429  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-05 08:00:00   \n",
       "101430  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-05 12:00:00   \n",
       "101431  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-05 16:00:00   \n",
       "101432  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-05 20:00:00   \n",
       "101433  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-06 00:00:00   \n",
       "101434  A069  R044  01-06-01  CHAMBERS ST    JZ456 2019-04-06 04:00:00   \n",
       "\n",
       "              DATE      TIME      TIME_DELTA  ENTRIES_DELTA  EXITS_DELTA  \n",
       "47399   2019-04-22  08:00:00 0 days 08:00:00     -3.584e+06   -1.246e+06  \n",
       "48937   2019-04-28  08:00:00 0 days 12:00:00      1.610e+05   -2.376e+04  \n",
       "49510   2019-03-30  16:00:00 0 days 08:00:00     -2.235e+06   -3.312e+06  \n",
       "54788   2019-04-01  16:00:00 0 days 04:00:00     -1.116e+05   -3.247e+05  \n",
       "60584   2019-04-28  00:00:00 0 days 04:00:00     -1.274e+05   -1.029e+04  \n",
       "69513   2019-04-10  12:00:00 1 days 04:00:00     -3.138e+04   -4.044e+04  \n",
       "101391  2019-03-30  00:00:00 0 days 04:00:00      1.200e+01   -1.100e+02  \n",
       "101392  2019-03-30  04:00:00 0 days 04:00:00      2.200e+01   -8.000e+01  \n",
       "101393  2019-03-30  08:00:00 0 days 04:00:00      3.100e+01   -8.670e+02  \n",
       "101394  2019-03-30  12:00:00 0 days 04:00:00      4.600e+01   -1.239e+03  \n",
       "101395  2019-03-30  16:00:00 0 days 04:00:00      5.000e+01   -1.107e+03  \n",
       "101396  2019-03-30  20:00:00 0 days 04:00:00      4.800e+01   -3.300e+02  \n",
       "...            ...       ...             ...            ...          ...  \n",
       "101423  2019-04-04  08:00:00 0 days 04:00:00      6.300e+01   -1.894e+03  \n",
       "101424  2019-04-04  12:00:00 0 days 04:00:00      1.950e+02   -1.231e+03  \n",
       "101425  2019-04-04  16:00:00 0 days 04:00:00      4.100e+02   -7.540e+02  \n",
       "101426  2019-04-04  20:00:00 0 days 04:00:00      6.400e+01   -2.540e+02  \n",
       "101427  2019-04-05  00:00:00 0 days 04:00:00      1.400e+01   -2.600e+01  \n",
       "101428  2019-04-05  04:00:00 0 days 04:00:00      3.700e+01   -3.680e+02  \n",
       "101429  2019-04-05  08:00:00 0 days 04:00:00      5.700e+01   -1.691e+03  \n",
       "101430  2019-04-05  12:00:00 0 days 04:00:00      1.770e+02   -9.790e+02  \n",
       "101431  2019-04-05  16:00:00 0 days 04:00:00      4.320e+02   -5.120e+02  \n",
       "101432  2019-04-05  20:00:00 0 days 04:00:00      7.200e+01   -2.810e+02  \n",
       "101433  2019-04-06  00:00:00 0 days 04:00:00      1.700e+01   -6.600e+01  \n",
       "101434  2019-04-06  04:00:00 0 days 04:00:00      3.600e+01   -6.900e+01  \n",
       "\n",
       "[50 rows x 11 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let's check that the number of entries for today is higher than entries for yesterday\n",
    "mta_data_delta[mta_data_delta[\"EXITS_DELTA\"] < 0].head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1789088\n",
      "1468091\n"
     ]
    }
   ],
   "source": [
    "print(len(mta_data_delta))\n",
    "mta_data_delta = mta_data_delta.drop(mta_data_delta[mta_data_delta[\"ENTRIES_DELTA\"] <= 0].index)\n",
    "mta_data_delta = mta_data_delta.drop(mta_data_delta[mta_data_delta[\"EXITS_DELTA\"] <= 0].index)\n",
    "print(len(mta_data_delta))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>TIME_DELTA</th>\n",
       "      <th>ENTRIES_DELTA</th>\n",
       "      <th>EXITS_DELTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1772354</th>\n",
       "      <td>R729</td>\n",
       "      <td>R292</td>\n",
       "      <td>00-00-02</td>\n",
       "      <td>BAYCHESTER AV</td>\n",
       "      <td>5</td>\n",
       "      <td>2019-05-16 08:00:00</td>\n",
       "      <td>2019-05-16</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>2.056e+09</td>\n",
       "      <td>1.671e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1540741</th>\n",
       "      <td>R405</td>\n",
       "      <td>R447</td>\n",
       "      <td>01-00-00</td>\n",
       "      <td>CYPRESS AV</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-04-18 00:00:00</td>\n",
       "      <td>2019-04-18</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>2.011e+09</td>\n",
       "      <td>8.351e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1444265</th>\n",
       "      <td>R258</td>\n",
       "      <td>R132</td>\n",
       "      <td>00-00-04</td>\n",
       "      <td>125 ST</td>\n",
       "      <td>456</td>\n",
       "      <td>2019-04-16 08:00:00</td>\n",
       "      <td>2019-04-16</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>1.432e+09</td>\n",
       "      <td>8.189e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1780436</th>\n",
       "      <td>S101A</td>\n",
       "      <td>R070</td>\n",
       "      <td>01-00-03</td>\n",
       "      <td>ST. GEORGE</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-05-02 08:00:00</td>\n",
       "      <td>2019-05-02</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>1.127e+09</td>\n",
       "      <td>1.078e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1362435</th>\n",
       "      <td>R231</td>\n",
       "      <td>R176</td>\n",
       "      <td>00-00-05</td>\n",
       "      <td>33 ST</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-04-09 20:00:00</td>\n",
       "      <td>2019-04-09</td>\n",
       "      <td>20:00:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>1.053e+09</td>\n",
       "      <td>2.628e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1528498</th>\n",
       "      <td>R332</td>\n",
       "      <td>R365</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>219 ST</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-05-17 08:00:00</td>\n",
       "      <td>2019-05-17</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>9.024e+08</td>\n",
       "      <td>3.176e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1528080</th>\n",
       "      <td>R331</td>\n",
       "      <td>R364</td>\n",
       "      <td>00-05-01</td>\n",
       "      <td>GUN HILL RD</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-05-10 12:00:00</td>\n",
       "      <td>2019-05-10</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>6.833e+08</td>\n",
       "      <td>6.383e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1442526</th>\n",
       "      <td>R257</td>\n",
       "      <td>R182</td>\n",
       "      <td>01-03-01</td>\n",
       "      <td>116 ST</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-05-09 08:00:00</td>\n",
       "      <td>2019-05-09</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>6.351e+08</td>\n",
       "      <td>5.849e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1466525</th>\n",
       "      <td>R287</td>\n",
       "      <td>R244</td>\n",
       "      <td>00-05-00</td>\n",
       "      <td>BURNSIDE AV</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-04-30 04:00:00</td>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>6.065e+08</td>\n",
       "      <td>1.008e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532979</th>\n",
       "      <td>N098</td>\n",
       "      <td>R028</td>\n",
       "      <td>00-02-00</td>\n",
       "      <td>FULTON ST</td>\n",
       "      <td>2345ACJZ</td>\n",
       "      <td>2019-05-24 04:00:00</td>\n",
       "      <td>2019-05-24</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>5.645e+08</td>\n",
       "      <td>5.593e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           C/A  UNIT       SCP        STATION  LINENAME            DATETIME  \\\n",
       "1772354   R729  R292  00-00-02  BAYCHESTER AV         5 2019-05-16 08:00:00   \n",
       "1540741   R405  R447  01-00-00     CYPRESS AV         6 2019-04-18 00:00:00   \n",
       "1444265   R258  R132  00-00-04         125 ST       456 2019-04-16 08:00:00   \n",
       "1780436  S101A  R070  01-00-03     ST. GEORGE         1 2019-05-02 08:00:00   \n",
       "1362435   R231  R176  00-00-05          33 ST         6 2019-04-09 20:00:00   \n",
       "1528498   R332  R365  00-00-00         219 ST        25 2019-05-17 08:00:00   \n",
       "1528080   R331  R364  00-05-01    GUN HILL RD        25 2019-05-10 12:00:00   \n",
       "1442526   R257  R182  01-03-01         116 ST         6 2019-05-09 08:00:00   \n",
       "1466525   R287  R244  00-05-00    BURNSIDE AV         4 2019-04-30 04:00:00   \n",
       "532979    N098  R028  00-02-00      FULTON ST  2345ACJZ 2019-05-24 04:00:00   \n",
       "\n",
       "               DATE      TIME TIME_DELTA  ENTRIES_DELTA  EXITS_DELTA  \n",
       "1772354  2019-05-16  08:00:00   04:00:00      2.056e+09    1.671e+08  \n",
       "1540741  2019-04-18  00:00:00   12:00:00      2.011e+09    8.351e+08  \n",
       "1444265  2019-04-16  08:00:00   04:00:00      1.432e+09    8.189e+08  \n",
       "1780436  2019-05-02  08:00:00   08:00:00      1.127e+09    1.078e+09  \n",
       "1362435  2019-04-09  20:00:00   08:00:00      1.053e+09    2.628e+07  \n",
       "1528498  2019-05-17  08:00:00   04:00:00      9.024e+08    3.176e+08  \n",
       "1528080  2019-05-10  12:00:00   04:00:00      6.833e+08    6.383e+08  \n",
       "1442526  2019-05-09  08:00:00   04:00:00      6.351e+08    5.849e+08  \n",
       "1466525  2019-04-30  04:00:00   08:00:00      6.065e+08    1.008e+09  \n",
       "532979   2019-05-24  04:00:00   08:00:00      5.645e+08    5.593e+08  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mta_data_delta.sort_values(['ENTRIES_DELTA'],ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>TIME_DELTA</th>\n",
       "      <th>ENTRIES_DELTA</th>\n",
       "      <th>EXITS_DELTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1383039</th>\n",
       "      <td>R238</td>\n",
       "      <td>R046</td>\n",
       "      <td>00-06-00</td>\n",
       "      <td>GRD CNTRL-42 ST</td>\n",
       "      <td>4567S</td>\n",
       "      <td>2019-04-02 04:00:00</td>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>3.107e+08</td>\n",
       "      <td>1.504e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1780436</th>\n",
       "      <td>S101A</td>\n",
       "      <td>R070</td>\n",
       "      <td>01-00-03</td>\n",
       "      <td>ST. GEORGE</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-05-02 08:00:00</td>\n",
       "      <td>2019-05-02</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>1.127e+09</td>\n",
       "      <td>1.078e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1466525</th>\n",
       "      <td>R287</td>\n",
       "      <td>R244</td>\n",
       "      <td>00-05-00</td>\n",
       "      <td>BURNSIDE AV</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-04-30 04:00:00</td>\n",
       "      <td>2019-04-30</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>6.065e+08</td>\n",
       "      <td>1.008e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1540741</th>\n",
       "      <td>R405</td>\n",
       "      <td>R447</td>\n",
       "      <td>01-00-00</td>\n",
       "      <td>CYPRESS AV</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-04-18 00:00:00</td>\n",
       "      <td>2019-04-18</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>2.011e+09</td>\n",
       "      <td>8.351e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1444265</th>\n",
       "      <td>R258</td>\n",
       "      <td>R132</td>\n",
       "      <td>00-00-04</td>\n",
       "      <td>125 ST</td>\n",
       "      <td>456</td>\n",
       "      <td>2019-04-16 08:00:00</td>\n",
       "      <td>2019-04-16</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>1.432e+09</td>\n",
       "      <td>8.189e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1528080</th>\n",
       "      <td>R331</td>\n",
       "      <td>R364</td>\n",
       "      <td>00-05-01</td>\n",
       "      <td>GUN HILL RD</td>\n",
       "      <td>25</td>\n",
       "      <td>2019-05-10 12:00:00</td>\n",
       "      <td>2019-05-10</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>6.833e+08</td>\n",
       "      <td>6.383e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1442526</th>\n",
       "      <td>R257</td>\n",
       "      <td>R182</td>\n",
       "      <td>01-03-01</td>\n",
       "      <td>116 ST</td>\n",
       "      <td>6</td>\n",
       "      <td>2019-05-09 08:00:00</td>\n",
       "      <td>2019-05-09</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>6.351e+08</td>\n",
       "      <td>5.849e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532979</th>\n",
       "      <td>N098</td>\n",
       "      <td>R028</td>\n",
       "      <td>00-02-00</td>\n",
       "      <td>FULTON ST</td>\n",
       "      <td>2345ACJZ</td>\n",
       "      <td>2019-05-24 04:00:00</td>\n",
       "      <td>2019-05-24</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>5.645e+08</td>\n",
       "      <td>5.593e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>655412</th>\n",
       "      <td>N220</td>\n",
       "      <td>R155</td>\n",
       "      <td>01-00-03</td>\n",
       "      <td>KINGSBRIDGE RD</td>\n",
       "      <td>BD</td>\n",
       "      <td>2019-04-15 04:00:00</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>2.340e+08</td>\n",
       "      <td>3.677e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>437805</th>\n",
       "      <td>N056</td>\n",
       "      <td>R188</td>\n",
       "      <td>01-00-01</td>\n",
       "      <td>50 ST</td>\n",
       "      <td>CE</td>\n",
       "      <td>2019-05-07 00:00:00</td>\n",
       "      <td>2019-05-07</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>3.317e+08</td>\n",
       "      <td>3.324e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           C/A  UNIT       SCP          STATION  LINENAME            DATETIME  \\\n",
       "1383039   R238  R046  00-06-00  GRD CNTRL-42 ST     4567S 2019-04-02 04:00:00   \n",
       "1780436  S101A  R070  01-00-03       ST. GEORGE         1 2019-05-02 08:00:00   \n",
       "1466525   R287  R244  00-05-00      BURNSIDE AV         4 2019-04-30 04:00:00   \n",
       "1540741   R405  R447  01-00-00       CYPRESS AV         6 2019-04-18 00:00:00   \n",
       "1444265   R258  R132  00-00-04           125 ST       456 2019-04-16 08:00:00   \n",
       "1528080   R331  R364  00-05-01      GUN HILL RD        25 2019-05-10 12:00:00   \n",
       "1442526   R257  R182  01-03-01           116 ST         6 2019-05-09 08:00:00   \n",
       "532979    N098  R028  00-02-00        FULTON ST  2345ACJZ 2019-05-24 04:00:00   \n",
       "655412    N220  R155  01-00-03   KINGSBRIDGE RD        BD 2019-04-15 04:00:00   \n",
       "437805    N056  R188  01-00-01            50 ST        CE 2019-05-07 00:00:00   \n",
       "\n",
       "               DATE      TIME TIME_DELTA  ENTRIES_DELTA  EXITS_DELTA  \n",
       "1383039  2019-04-02  04:00:00   12:00:00      3.107e+08    1.504e+09  \n",
       "1780436  2019-05-02  08:00:00   08:00:00      1.127e+09    1.078e+09  \n",
       "1466525  2019-04-30  04:00:00   08:00:00      6.065e+08    1.008e+09  \n",
       "1540741  2019-04-18  00:00:00   12:00:00      2.011e+09    8.351e+08  \n",
       "1444265  2019-04-16  08:00:00   04:00:00      1.432e+09    8.189e+08  \n",
       "1528080  2019-05-10  12:00:00   04:00:00      6.833e+08    6.383e+08  \n",
       "1442526  2019-05-09  08:00:00   04:00:00      6.351e+08    5.849e+08  \n",
       "532979   2019-05-24  04:00:00   08:00:00      5.645e+08    5.593e+08  \n",
       "655412   2019-04-15  04:00:00   08:00:00      2.340e+08    3.677e+08  \n",
       "437805   2019-05-07  00:00:00   12:00:00      3.317e+08    3.324e+08  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mta_data_delta.sort_values(['EXITS_DELTA'],ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1468091\n",
      "1468050\n"
     ]
    }
   ],
   "source": [
    "print(len(mta_data_delta))\n",
    "mta_data_delta = mta_data_delta.drop(mta_data_delta[mta_data_delta[\"ENTRIES_DELTA\"] > 1000000].index)\n",
    "mta_data_delta = mta_data_delta.drop(mta_data_delta[mta_data_delta[\"EXITS_DELTA\"] > 1000000].index)\n",
    "print(len(mta_data_delta))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>TIME_DELTA</th>\n",
       "      <th>ENTRIES_DELTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0 days 04:00:00</td>\n",
       "      <td>1443776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0 days 08:00:00</td>\n",
       "      <td>23514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0 days 12:00:00</td>\n",
       "      <td>172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2 days 20:00:00</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0 days 16:00:00</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0 days 20:00:00</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>3 days 00:00:00</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1 days 00:00:00</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1 days 12:00:00</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2 days 12:00:00</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        TIME_DELTA  ENTRIES_DELTA\n",
       "0  0 days 04:00:00        1443776\n",
       "1  0 days 08:00:00          23514\n",
       "2  0 days 12:00:00            172\n",
       "16 2 days 20:00:00            104\n",
       "3  0 days 16:00:00             78\n",
       "4  0 days 20:00:00             75\n",
       "17 3 days 00:00:00             56\n",
       "5  1 days 00:00:00             39\n",
       "8  1 days 12:00:00             37\n",
       "14 2 days 12:00:00             28"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(mta_data_delta\n",
    " .groupby(['TIME_DELTA'])  \n",
    " .ENTRIES_DELTA.count()\n",
    " .reset_index()  # or use as_index = False; otherwise makes groupby columns new index \n",
    " .sort_values(\"ENTRIES_DELTA\", ascending=False)).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME_DELTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>119836</th>\n",
       "      <td>N414A</td>\n",
       "      <td>R316</td>\n",
       "      <td>01-00-02</td>\n",
       "      <td>FLUSHING AV</td>\n",
       "      <td>2019-04-08</td>\n",
       "      <td>24 days 08:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119765</th>\n",
       "      <td>N414A</td>\n",
       "      <td>R316</td>\n",
       "      <td>01-00-00</td>\n",
       "      <td>FLUSHING AV</td>\n",
       "      <td>2019-04-08</td>\n",
       "      <td>24 days 04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119801</th>\n",
       "      <td>N414A</td>\n",
       "      <td>R316</td>\n",
       "      <td>01-00-01</td>\n",
       "      <td>FLUSHING AV</td>\n",
       "      <td>2019-04-08</td>\n",
       "      <td>24 days 04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119905</th>\n",
       "      <td>N414A</td>\n",
       "      <td>R316</td>\n",
       "      <td>01-06-01</td>\n",
       "      <td>FLUSHING AV</td>\n",
       "      <td>2019-04-08</td>\n",
       "      <td>24 days 04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151754</th>\n",
       "      <td>PTH05</td>\n",
       "      <td>R543</td>\n",
       "      <td>00-01-07</td>\n",
       "      <td>EXCHANGE PLACE</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>15 days 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31781</th>\n",
       "      <td>C025</td>\n",
       "      <td>R215</td>\n",
       "      <td>00-03-00</td>\n",
       "      <td>86 ST</td>\n",
       "      <td>2019-04-16</td>\n",
       "      <td>14 days 12:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151705</th>\n",
       "      <td>PTH05</td>\n",
       "      <td>R543</td>\n",
       "      <td>00-01-06</td>\n",
       "      <td>EXCHANGE PLACE</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>12 days 04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127283</th>\n",
       "      <td>N508</td>\n",
       "      <td>R453</td>\n",
       "      <td>00-00-04</td>\n",
       "      <td>23 ST</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>12 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127235</th>\n",
       "      <td>N508</td>\n",
       "      <td>R453</td>\n",
       "      <td>00-00-03</td>\n",
       "      <td>23 ST</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>12 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127331</th>\n",
       "      <td>N508</td>\n",
       "      <td>R453</td>\n",
       "      <td>00-00-05</td>\n",
       "      <td>23 ST</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>12 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127141</th>\n",
       "      <td>N508</td>\n",
       "      <td>R453</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>23 ST</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>12 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127188</th>\n",
       "      <td>N508</td>\n",
       "      <td>R453</td>\n",
       "      <td>00-00-01</td>\n",
       "      <td>23 ST</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>12 days 00:00:00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58594</th>\n",
       "      <td>N020</td>\n",
       "      <td>R101</td>\n",
       "      <td>00-00-03</td>\n",
       "      <td>145 ST</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>5 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58653</th>\n",
       "      <td>N020</td>\n",
       "      <td>R101</td>\n",
       "      <td>00-00-04</td>\n",
       "      <td>145 ST</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>5 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43495</th>\n",
       "      <td>H023</td>\n",
       "      <td>R236</td>\n",
       "      <td>00-06-01</td>\n",
       "      <td>DEKALB AV</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>5 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58240</th>\n",
       "      <td>N019</td>\n",
       "      <td>R101</td>\n",
       "      <td>01-00-00</td>\n",
       "      <td>145 ST</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>5 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58417</th>\n",
       "      <td>N020</td>\n",
       "      <td>R101</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>145 ST</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>5 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261855</th>\n",
       "      <td>R730</td>\n",
       "      <td>R431</td>\n",
       "      <td>00-00-01</td>\n",
       "      <td>EASTCHSTER/DYRE</td>\n",
       "      <td>2019-04-22</td>\n",
       "      <td>5 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58358</th>\n",
       "      <td>N019</td>\n",
       "      <td>R101</td>\n",
       "      <td>01-00-02</td>\n",
       "      <td>145 ST</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>5 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261796</th>\n",
       "      <td>R730</td>\n",
       "      <td>R431</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>EASTCHSTER/DYRE</td>\n",
       "      <td>2019-04-22</td>\n",
       "      <td>5 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43377</th>\n",
       "      <td>H023</td>\n",
       "      <td>R236</td>\n",
       "      <td>00-00-01</td>\n",
       "      <td>DEKALB AV</td>\n",
       "      <td>2019-04-29</td>\n",
       "      <td>5 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58712</th>\n",
       "      <td>N020</td>\n",
       "      <td>R101</td>\n",
       "      <td>00-00-05</td>\n",
       "      <td>145 ST</td>\n",
       "      <td>2019-04-15</td>\n",
       "      <td>5 days 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163885</th>\n",
       "      <td>PTH22</td>\n",
       "      <td>R540</td>\n",
       "      <td>00-05-01</td>\n",
       "      <td>PATH NEW WTC</td>\n",
       "      <td>2019-05-13</td>\n",
       "      <td>4 days 20:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67045</th>\n",
       "      <td>N062A</td>\n",
       "      <td>R010</td>\n",
       "      <td>00-05-00</td>\n",
       "      <td>42 ST-PORT AUTH</td>\n",
       "      <td>2019-05-25</td>\n",
       "      <td>4 days 16:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          C/A  UNIT       SCP          STATION        DATE       TIME_DELTA\n",
       "119836  N414A  R316  01-00-02      FLUSHING AV  2019-04-08 24 days 08:00:00\n",
       "119765  N414A  R316  01-00-00      FLUSHING AV  2019-04-08 24 days 04:00:00\n",
       "119801  N414A  R316  01-00-01      FLUSHING AV  2019-04-08 24 days 04:00:00\n",
       "119905  N414A  R316  01-06-01      FLUSHING AV  2019-04-08 24 days 04:00:00\n",
       "151754  PTH05  R543  00-01-07   EXCHANGE PLACE  2019-04-15 15 days 12:00:00\n",
       "31781    C025  R215  00-03-00            86 ST  2019-04-16 14 days 12:00:00\n",
       "151705  PTH05  R543  00-01-06   EXCHANGE PLACE  2019-04-15 12 days 04:00:00\n",
       "127283   N508  R453  00-00-04            23 ST  2019-04-29 12 days 00:00:00\n",
       "127235   N508  R453  00-00-03            23 ST  2019-04-29 12 days 00:00:00\n",
       "127331   N508  R453  00-00-05            23 ST  2019-04-29 12 days 00:00:00\n",
       "127141   N508  R453  00-00-00            23 ST  2019-04-29 12 days 00:00:00\n",
       "127188   N508  R453  00-00-01            23 ST  2019-04-29 12 days 00:00:00\n",
       "...       ...   ...       ...              ...         ...              ...\n",
       "58594    N020  R101  00-00-03           145 ST  2019-04-15  5 days 00:00:00\n",
       "58653    N020  R101  00-00-04           145 ST  2019-04-15  5 days 00:00:00\n",
       "43495    H023  R236  00-06-01        DEKALB AV  2019-04-29  5 days 00:00:00\n",
       "58240    N019  R101  01-00-00           145 ST  2019-04-15  5 days 00:00:00\n",
       "58417    N020  R101  00-00-00           145 ST  2019-04-15  5 days 00:00:00\n",
       "261855   R730  R431  00-00-01  EASTCHSTER/DYRE  2019-04-22  5 days 00:00:00\n",
       "58358    N019  R101  01-00-02           145 ST  2019-04-15  5 days 00:00:00\n",
       "261796   R730  R431  00-00-00  EASTCHSTER/DYRE  2019-04-22  5 days 00:00:00\n",
       "43377    H023  R236  00-00-01        DEKALB AV  2019-04-29  5 days 00:00:00\n",
       "58712    N020  R101  00-00-05           145 ST  2019-04-15  5 days 00:00:00\n",
       "163885  PTH22  R540  00-05-01     PATH NEW WTC  2019-05-13  4 days 20:00:00\n",
       "67045   N062A  R010  00-05-00  42 ST-PORT AUTH  2019-05-25  4 days 16:00:00\n",
       "\n",
       "[50 rows x 6 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(mta_data_delta\n",
    " .groupby(unique_id + ['DATE'])  \n",
    " .TIME_DELTA.sum()\n",
    " .reset_index()  # or use as_index = False; otherwise makes groupby columns new index \n",
    " .sort_values(\"TIME_DELTA\", ascending=False)).head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>DATE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TIME_DELTA</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0 days 04:00:00</th>\n",
       "      <td>2692</td>\n",
       "      <td>2692</td>\n",
       "      <td>2692</td>\n",
       "      <td>2692</td>\n",
       "      <td>2692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 08:00:00</th>\n",
       "      <td>3018</td>\n",
       "      <td>3018</td>\n",
       "      <td>3018</td>\n",
       "      <td>3018</td>\n",
       "      <td>3018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 12:00:00</th>\n",
       "      <td>3361</td>\n",
       "      <td>3361</td>\n",
       "      <td>3361</td>\n",
       "      <td>3361</td>\n",
       "      <td>3361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 16:00:00</th>\n",
       "      <td>6950</td>\n",
       "      <td>6950</td>\n",
       "      <td>6950</td>\n",
       "      <td>6950</td>\n",
       "      <td>6950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0 days 20:00:00</th>\n",
       "      <td>32888</td>\n",
       "      <td>32888</td>\n",
       "      <td>32888</td>\n",
       "      <td>32888</td>\n",
       "      <td>32888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1 days 00:00:00</th>\n",
       "      <td>212252</td>\n",
       "      <td>212252</td>\n",
       "      <td>212252</td>\n",
       "      <td>212252</td>\n",
       "      <td>212252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1 days 04:00:00</th>\n",
       "      <td>867</td>\n",
       "      <td>867</td>\n",
       "      <td>867</td>\n",
       "      <td>867</td>\n",
       "      <td>867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1 days 08:00:00</th>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "      <td>53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1 days 12:00:00</th>\n",
       "      <td>33</td>\n",
       "      <td>33</td>\n",
       "      <td>33</td>\n",
       "      <td>33</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1 days 16:00:00</th>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1 days 20:00:00</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2 days 00:00:00</th>\n",
       "      <td>47</td>\n",
       "      <td>47</td>\n",
       "      <td>47</td>\n",
       "      <td>47</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6 days 16:00:00</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7 days 00:00:00</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7 days 08:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8 days 08:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9 days 08:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9 days 16:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12 days 00:00:00</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12 days 04:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14 days 12:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15 days 12:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24 days 04:00:00</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24 days 08:00:00</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>44 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     C/A    UNIT     SCP  STATION    DATE\n",
       "TIME_DELTA                                               \n",
       "0 days 04:00:00     2692    2692    2692     2692    2692\n",
       "0 days 08:00:00     3018    3018    3018     3018    3018\n",
       "0 days 12:00:00     3361    3361    3361     3361    3361\n",
       "0 days 16:00:00     6950    6950    6950     6950    6950\n",
       "0 days 20:00:00    32888   32888   32888    32888   32888\n",
       "1 days 00:00:00   212252  212252  212252   212252  212252\n",
       "1 days 04:00:00      867     867     867      867     867\n",
       "1 days 08:00:00       53      53      53       53      53\n",
       "1 days 12:00:00       33      33      33       33      33\n",
       "1 days 16:00:00        8       8       8        8       8\n",
       "1 days 20:00:00        6       6       6        6       6\n",
       "2 days 00:00:00       47      47      47       47      47\n",
       "...                  ...     ...     ...      ...     ...\n",
       "6 days 16:00:00        2       2       2        2       2\n",
       "7 days 00:00:00        9       9       9        9       9\n",
       "7 days 08:00:00        1       1       1        1       1\n",
       "8 days 08:00:00        1       1       1        1       1\n",
       "9 days 08:00:00        1       1       1        1       1\n",
       "9 days 16:00:00        1       1       1        1       1\n",
       "12 days 00:00:00       5       5       5        5       5\n",
       "12 days 04:00:00       1       1       1        1       1\n",
       "14 days 12:00:00       1       1       1        1       1\n",
       "15 days 12:00:00       1       1       1        1       1\n",
       "24 days 04:00:00       3       3       3        3       3\n",
       "24 days 08:00:00       1       1       1        1       1\n",
       "\n",
       "[44 rows x 5 columns]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(mta_data_delta\n",
    " .groupby(unique_id + ['DATE'])  \n",
    " .TIME_DELTA.sum()\n",
    " .reset_index()  # or use as_index = False; otherwise makes groupby columns new index \n",
    " .groupby(['TIME_DELTA'])\n",
    " .count())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Clean Outputs**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1468050\n",
      "1443776\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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME</th>\n",
       "      <th>TIME_DELTA</th>\n",
       "      <th>ENTRIES_DELTA</th>\n",
       "      <th>EXITS_DELTA</th>\n",
       "      <th>TOTAL_DELTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 00:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>20.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>28.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 04:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>23.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 08:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>107.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>195.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 12:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>237.0</td>\n",
       "      <td>71.0</td>\n",
       "      <td>308.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30 16:00:00</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>345.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>401.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP STATION LINENAME            DATETIME        DATE  \\\n",
       "0  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 00:00:00  2019-03-30   \n",
       "1  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 04:00:00  2019-03-30   \n",
       "2  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 08:00:00  2019-03-30   \n",
       "3  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 12:00:00  2019-03-30   \n",
       "4  A002  R051  02-00-00   59 ST  NQR456W 2019-03-30 16:00:00  2019-03-30   \n",
       "\n",
       "       TIME TIME_DELTA  ENTRIES_DELTA  EXITS_DELTA  TOTAL_DELTA  \n",
       "0  00:00:00   04:00:00           20.0          8.0         28.0  \n",
       "1  04:00:00   04:00:00           23.0         46.0         69.0  \n",
       "2  08:00:00   04:00:00          107.0         88.0        195.0  \n",
       "3  12:00:00   04:00:00          237.0         71.0        308.0  \n",
       "4  16:00:00   04:00:00          345.0         56.0        401.0  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Intraday Data\n",
    "mta_data_intra = mta_data_delta.copy(deep=True)\n",
    "print(len(mta_data_intra))\n",
    "mta_data_intra = mta_data_intra.drop(mta_data_intra[mta_data_intra[\"TIME_DELTA\"] != timedelta(hours=4)].index).reset_index(drop=True)\n",
    "print(len(mta_data_intra))\n",
    "mta_data_intra['TOTAL_DELTA'] = mta_data_intra['ENTRIES_DELTA'] + mta_data_intra['EXITS_DELTA']\n",
    "mta_data_intra.head()\n",
    "#TO DISCUSS: A few of the outputs above show that not all of the time deltas are four hours. What do we want to drop here?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "262496\n",
      "212252\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>C/A</th>\n",
       "      <th>UNIT</th>\n",
       "      <th>SCP</th>\n",
       "      <th>STATION</th>\n",
       "      <th>LINENAME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>TIME_DELTA</th>\n",
       "      <th>ENTRIES_DELTA</th>\n",
       "      <th>EXITS_DELTA</th>\n",
       "      <th>TOTAL_DELTA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-30</td>\n",
       "      <td>1 days</td>\n",
       "      <td>893.0</td>\n",
       "      <td>299.0</td>\n",
       "      <td>1192.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-03-31</td>\n",
       "      <td>1 days</td>\n",
       "      <td>571.0</td>\n",
       "      <td>228.0</td>\n",
       "      <td>799.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-04-02</td>\n",
       "      <td>1 days</td>\n",
       "      <td>1593.0</td>\n",
       "      <td>554.0</td>\n",
       "      <td>2147.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-04-03</td>\n",
       "      <td>1 days</td>\n",
       "      <td>1652.0</td>\n",
       "      <td>424.0</td>\n",
       "      <td>2076.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A002</td>\n",
       "      <td>R051</td>\n",
       "      <td>02-00-00</td>\n",
       "      <td>59 ST</td>\n",
       "      <td>NQR456W</td>\n",
       "      <td>2019-04-04</td>\n",
       "      <td>1 days</td>\n",
       "      <td>1638.0</td>\n",
       "      <td>511.0</td>\n",
       "      <td>2149.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP STATION LINENAME        DATE TIME_DELTA  \\\n",
       "0  A002  R051  02-00-00   59 ST  NQR456W  2019-03-30     1 days   \n",
       "1  A002  R051  02-00-00   59 ST  NQR456W  2019-03-31     1 days   \n",
       "2  A002  R051  02-00-00   59 ST  NQR456W  2019-04-02     1 days   \n",
       "3  A002  R051  02-00-00   59 ST  NQR456W  2019-04-03     1 days   \n",
       "4  A002  R051  02-00-00   59 ST  NQR456W  2019-04-04     1 days   \n",
       "\n",
       "   ENTRIES_DELTA  EXITS_DELTA  TOTAL_DELTA  \n",
       "0          893.0        299.0       1192.0  \n",
       "1          571.0        228.0        799.0  \n",
       "2         1593.0        554.0       2147.0  \n",
       "3         1652.0        424.0       2076.0  \n",
       "4         1638.0        511.0       2149.0  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Daily Data\n",
    "\n",
    "\n",
    "mta_data_daily = (mta_data_delta\n",
    "                  .groupby(unique_id + ['LINENAME','DATE'])['ENTRIES_DELTA','EXITS_DELTA']\n",
    "                  .sum()\n",
    "                  .reset_index()\n",
    "                 )\n",
    "\n",
    "time_deltas = (mta_data_delta\n",
    "                  .groupby(unique_id + ['LINENAME','DATE'])\n",
    "                  .TIME_DELTA.sum()  # this sum had to be done separate from the entries and exits delta sum above\n",
    "                  .reset_index()\n",
    "                 )\n",
    "\n",
    "mta_data_daily['TIME_DELTA'] = time_deltas['TIME_DELTA']\n",
    "\n",
    "mta_data_daily = mta_data_daily[unique_id + ['LINENAME','DATE','TIME_DELTA','ENTRIES_DELTA','EXITS_DELTA']]\n",
    "\n",
    "print(len(mta_data_daily))\n",
    "mta_data_daily = mta_data_daily.drop(mta_data_daily[mta_data_daily[\"TIME_DELTA\"] != timedelta(days=1)].index).reset_index(drop=True)\n",
    "print(len(mta_data_daily))\n",
    "\n",
    "mta_data_daily['TOTAL_DELTA'] = mta_data_daily['ENTRIES_DELTA'] + mta_data_daily['EXITS_DELTA']\n",
    "\n",
    "mta_data_daily.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "#pickle\n",
    "with open('pickle/mta_data_intra.pickle', 'wb') as to_write:\n",
    "    pickle.dump(mta_data_intra, to_write)\n",
    "    \n",
    "with open('pickle/mta_data_daily.pickle', 'wb') as to_write:\n",
    "    pickle.dump(mta_data_daily, to_write)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:metis]",
   "language": "python",
   "name": "conda-env-metis-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  },
  "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
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}