MTA data cleaned and structure Copy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2018/9/26 Updates\n",
    "\n",
    "Changes\n",
    "1. Columns converted to proper datatypes\n",
    "2. Entry and Exit numbers tracked for each turnstile in new columns \n",
    "3. df.head() in each block to show the progression of the cleaning. This will need to be removed\n",
    "4. Some operations were performed because of the guidance of the challenge. \n",
    "   This is not always what we need for our project goals. I have kept the unused code in markedown cells at the end,\n",
    "   incase we do need it later\n",
    "\n",
    "Qustions:\n",
    "1. Should an i.d. column be created that combines C_A, Unit, SCP, and Station? \n",
    "\n",
    "Next steps:\n",
    "2. Fix functions\n",
    "1. Calculate daily entry/exits for each day\n",
    "\n",
    "ID explained \n",
    "Station then Unit(think enterance maybe) then CA(booth) then SCP(turnstyle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/GabeKlick/anaconda3/lib/python3.6/site-packages/IPython/core/interactiveshell.py:2785: DtypeWarning: Columns (11) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n",
      "/Users/GabeKlick/anaconda3/lib/python3.6/site-packages/numpy/lib/arraysetops.py:472: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\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>04/29/2017</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157740</td>\n",
       "      <td>2085315</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>04/29/2017</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157777</td>\n",
       "      <td>2085319</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>04/29/2017</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157810</td>\n",
       "      <td>2085353</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>04/29/2017</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157963</td>\n",
       "      <td>2085453</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>04/29/2017</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6158212</td>\n",
       "      <td>2085529</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  04/29/2017  00:00:00   \n",
       "1    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  04:00:00   \n",
       "2    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  08:00:00   \n",
       "3    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  12:00:00   \n",
       "4    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  16:00:00   \n",
       "\n",
       "      desc  entries    exits  \n",
       "0  REGULAR  6157740  2085315  \n",
       "1  REGULAR  6157777  2085319  \n",
       "2  REGULAR  6157810  2085353  \n",
       "3  REGULAR  6157963  2085453  \n",
       "4  REGULAR  6158212  2085529  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#load April - July, 2017 & 2018 MTA Turntile data\n",
    "#time intensive block\n",
    "df = pd.read_csv('/Users/GabeKlick/Downloads/turnstile_recordings_summer_2017_2018_clean.csv', index_col = 'Unnamed: 0')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>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>04/29/2017</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157740</td>\n",
       "      <td>2085315</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>04/29/2017</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157777</td>\n",
       "      <td>2085319</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>04/29/2017</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157810</td>\n",
       "      <td>2085353</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>04/29/2017</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157963</td>\n",
       "      <td>2085453</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>04/29/2017</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6158212</td>\n",
       "      <td>2085529</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  04/29/2017  00:00:00   \n",
       "1    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  04:00:00   \n",
       "2    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  08:00:00   \n",
       "3    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  12:00:00   \n",
       "4    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  16:00:00   \n",
       "\n",
       "      desc  entries    exits  \n",
       "0  REGULAR  6157740  2085315  \n",
       "1  REGULAR  6157777  2085319  \n",
       "2  REGULAR  6157810  2085353  \n",
       "3  REGULAR  6157963  2085453  \n",
       "4  REGULAR  6158212  2085529  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#rename index column to be used later for dateframes merges\n",
    "df.rename(index = {'Unnamed: 0': 'Index'}, inplace = True)\n",
    "df.head()"
   ]
  },
  {
   "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>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>04/29/2017</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157740</td>\n",
       "      <td>2085315</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>04/29/2017</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157777</td>\n",
       "      <td>2085319</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>04/29/2017</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157810</td>\n",
       "      <td>2085353</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>04/29/2017</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157963</td>\n",
       "      <td>2085453</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>04/29/2017</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6158212</td>\n",
       "      <td>2085529</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  04/29/2017  00:00:00   \n",
       "1    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  04:00:00   \n",
       "2    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  08:00:00   \n",
       "3    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  12:00:00   \n",
       "4    A002  R051  02-00-00   59 ST  NQR456W      BMT  04/29/2017  16:00:00   \n",
       "\n",
       "      desc  entries    exits  \n",
       "0  REGULAR  6157740  2085315  \n",
       "1  REGULAR  6157777  2085319  \n",
       "2  REGULAR  6157810  2085353  \n",
       "3  REGULAR  6157963  2085453  \n",
       "4  REGULAR  6158212  2085529  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#exit column has a few misplaced chars preventing the column from being int values\n",
    "#this block removes the chars and converts columns to int64\n",
    "\n",
    "#apply regex statement to ensure only numbers in the column\n",
    "def only_num(col):\n",
    "    return re.findall('(\\d)', col)\n",
    "df.exits = df.exits.apply(lambda x: only_num(str(x)))\n",
    "\n",
    "#the regex statment returns a list a numbers for each value\n",
    "#join list values to single string items of numbers\n",
    "df.exits = df.exits.apply(lambda x: ''.join(x))\n",
    "\n",
    "#convert str to int64\n",
    "df.exits = df.exits.apply(lambda x: int(x))\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>division</th>\n",
       "      <th>date_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>2017-04-29 00:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157740</td>\n",
       "      <td>2085315</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>2017-04-29 04:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157777</td>\n",
       "      <td>2085319</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>2017-04-29 08:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157810</td>\n",
       "      <td>2085353</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>2017-04-29 12:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6157963</td>\n",
       "      <td>2085453</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>2017-04-29 16:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6158212</td>\n",
       "      <td>2085529</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 2017-04-29 00:00:00   \n",
       "1    A002  R051  02-00-00   59 ST  NQR456W      BMT 2017-04-29 04:00:00   \n",
       "2    A002  R051  02-00-00   59 ST  NQR456W      BMT 2017-04-29 08:00:00   \n",
       "3    A002  R051  02-00-00   59 ST  NQR456W      BMT 2017-04-29 12:00:00   \n",
       "4    A002  R051  02-00-00   59 ST  NQR456W      BMT 2017-04-29 16:00:00   \n",
       "\n",
       "      desc  entries    exits  \n",
       "0  REGULAR  6157740  2085315  \n",
       "1  REGULAR  6157777  2085319  \n",
       "2  REGULAR  6157810  2085353  \n",
       "3  REGULAR  6157963  2085453  \n",
       "4  REGULAR  6158212  2085529  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#combine and convert date,time column from str to datetime\n",
    "#time intensive block, run then take 5\n",
    "\n",
    "#df.date = pd.to_datetime(df.date_time, infer_datetime_format= True)\n",
    "df.date = pd.to_datetime(df.date + ' ' + df.time)\n",
    "\n",
    "#rename to showcase the new time feature in the column\n",
    "df.rename({'date': 'date_time'}, axis = 'columns', inplace = True)\n",
    "\n",
    "#removes the now redundant time column\n",
    "df.drop(columns = ['time'], inplace = True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "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>level_0</th>\n",
       "      <th>index</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_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>0</td>\n",
       "      <td>12</td>\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>2017-05-01 00:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6159107</td>\n",
       "      <td>2085842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\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>2017-05-01 04:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6159116</td>\n",
       "      <td>2085849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\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>2017-05-01 08:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6159166</td>\n",
       "      <td>2085976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\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>2017-05-01 12:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6159331</td>\n",
       "      <td>2086218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\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>2017-05-01 16:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6159595</td>\n",
       "      <td>2086299</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   level_0  index   c_a  unit       scp station linename division  \\\n",
       "0        0     12  A002  R051  02-00-00   59 ST  NQR456W      BMT   \n",
       "1        1     13  A002  R051  02-00-00   59 ST  NQR456W      BMT   \n",
       "2        2     14  A002  R051  02-00-00   59 ST  NQR456W      BMT   \n",
       "3        3     15  A002  R051  02-00-00   59 ST  NQR456W      BMT   \n",
       "4        4     16  A002  R051  02-00-00   59 ST  NQR456W      BMT   \n",
       "\n",
       "            date_time     desc  entries    exits  \n",
       "0 2017-05-01 00:00:00  REGULAR  6159107  2085842  \n",
       "1 2017-05-01 04:00:00  REGULAR  6159116  2085849  \n",
       "2 2017-05-01 08:00:00  REGULAR  6159166  2085976  \n",
       "3 2017-05-01 12:00:00  REGULAR  6159331  2086218  \n",
       "4 2017-05-01 16:00:00  REGULAR  6159595  2086299  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#remove dates before May and after August\n",
    "df = df[(df.date_time.dt.month != 4) & (df.date_time.dt.month != 8)].reset_index()\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/GabeKlick/anaconda3/lib/python3.6/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "#replace typo\n",
    "df[df['c_a'] == ' \"A002']['c_a'] = 'A002'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "difference_of_entries = df.groupby(['station', 'unit', 'c_a', 'scp'])['entries'].apply(lambda x: x- x.shift(1))\n",
    "difference_of_exits = df.groupby(['station', 'unit', 'c_a', 'scp'])['exits'].apply(lambda x: x- x.shift(1))\n",
    "hours_elapsed = df.groupby(['station', 'unit', 'c_a', 'scp'])['date_time'].apply(lambda x: x- x.shift(1))\n",
    "temp_df = pd.DataFrame({'Index': df.index, 'num_entries': difference_of_entries, 'num_exits': difference_of_exits, 'hours_elapsed': hours_elapsed})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": false
   },
   "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>level_0</th>\n",
       "      <th>index</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_time</th>\n",
       "      <th>desc</th>\n",
       "      <th>entries</th>\n",
       "      <th>exits</th>\n",
       "      <th>Index</th>\n",
       "      <th>num_entries</th>\n",
       "      <th>num_exits</th>\n",
       "      <th>hours_elapsed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>12</td>\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>2017-05-01 00:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6159107</td>\n",
       "      <td>2085842</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\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>2017-05-01 04:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6159116</td>\n",
       "      <td>2085849</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>14</td>\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>2017-05-01 08:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6159166</td>\n",
       "      <td>2085976</td>\n",
       "      <td>2</td>\n",
       "      <td>50.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>15</td>\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>2017-05-01 12:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6159331</td>\n",
       "      <td>2086218</td>\n",
       "      <td>3</td>\n",
       "      <td>165.0</td>\n",
       "      <td>242.0</td>\n",
       "      <td>04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\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>2017-05-01 16:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6159595</td>\n",
       "      <td>2086299</td>\n",
       "      <td>4</td>\n",
       "      <td>264.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   level_0  index   c_a  unit       scp station linename division  \\\n",
       "0        0     12  A002  R051  02-00-00   59 ST  NQR456W      BMT   \n",
       "1        1     13  A002  R051  02-00-00   59 ST  NQR456W      BMT   \n",
       "2        2     14  A002  R051  02-00-00   59 ST  NQR456W      BMT   \n",
       "3        3     15  A002  R051  02-00-00   59 ST  NQR456W      BMT   \n",
       "4        4     16  A002  R051  02-00-00   59 ST  NQR456W      BMT   \n",
       "\n",
       "            date_time     desc  entries    exits  Index  num_entries  \\\n",
       "0 2017-05-01 00:00:00  REGULAR  6159107  2085842      0          NaN   \n",
       "1 2017-05-01 04:00:00  REGULAR  6159116  2085849      1          9.0   \n",
       "2 2017-05-01 08:00:00  REGULAR  6159166  2085976      2         50.0   \n",
       "3 2017-05-01 12:00:00  REGULAR  6159331  2086218      3        165.0   \n",
       "4 2017-05-01 16:00:00  REGULAR  6159595  2086299      4        264.0   \n",
       "\n",
       "   num_exits hours_elapsed  \n",
       "0        NaN           NaT  \n",
       "1        7.0      04:00:00  \n",
       "2      127.0      04:00:00  \n",
       "3      242.0      04:00:00  \n",
       "4       81.0      04:00:00  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#time intensive, run then take 5\n",
    "#merge the main and temp df (created in function) on the index column\n",
    "df = pd.concat([df, temp_df], axis = 1, join = 'outer')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>level_0</th>\n",
       "      <th>index</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_time</th>\n",
       "      <th>desc</th>\n",
       "      <th>entries</th>\n",
       "      <th>exits</th>\n",
       "      <th>Index</th>\n",
       "      <th>num_entries</th>\n",
       "      <th>num_exits</th>\n",
       "      <th>hours_elapsed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1427698</th>\n",
       "      <td>1427698</td>\n",
       "      <td>1526516</td>\n",
       "      <td>N309A</td>\n",
       "      <td>R140</td>\n",
       "      <td>00-05-00</td>\n",
       "      <td>QUEENS PLAZA</td>\n",
       "      <td>EMR</td>\n",
       "      <td>IND</td>\n",
       "      <td>2018-07-21 01:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>5430</td>\n",
       "      <td>0</td>\n",
       "      <td>1427698</td>\n",
       "      <td>810.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>364 days 04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1427740</th>\n",
       "      <td>1427740</td>\n",
       "      <td>1526558</td>\n",
       "      <td>N309A</td>\n",
       "      <td>R140</td>\n",
       "      <td>00-05-01</td>\n",
       "      <td>QUEENS PLAZA</td>\n",
       "      <td>EMR</td>\n",
       "      <td>IND</td>\n",
       "      <td>2018-07-21 01:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>0</td>\n",
       "      <td>91</td>\n",
       "      <td>1427740</td>\n",
       "      <td>0.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>364 days 04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1427782</th>\n",
       "      <td>1427782</td>\n",
       "      <td>1526600</td>\n",
       "      <td>N309A</td>\n",
       "      <td>R140</td>\n",
       "      <td>00-05-02</td>\n",
       "      <td>QUEENS PLAZA</td>\n",
       "      <td>EMR</td>\n",
       "      <td>IND</td>\n",
       "      <td>2018-07-21 01:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>1095</td>\n",
       "      <td>0</td>\n",
       "      <td>1427782</td>\n",
       "      <td>143.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>364 days 04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1427824</th>\n",
       "      <td>1427824</td>\n",
       "      <td>1526642</td>\n",
       "      <td>N309A</td>\n",
       "      <td>R140</td>\n",
       "      <td>00-05-03</td>\n",
       "      <td>QUEENS PLAZA</td>\n",
       "      <td>EMR</td>\n",
       "      <td>IND</td>\n",
       "      <td>2018-07-21 01:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>0</td>\n",
       "      <td>497</td>\n",
       "      <td>1427824</td>\n",
       "      <td>0.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>364 days 04:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1427866</th>\n",
       "      <td>1427866</td>\n",
       "      <td>1526684</td>\n",
       "      <td>N309A</td>\n",
       "      <td>R140</td>\n",
       "      <td>00-06-00</td>\n",
       "      <td>QUEENS PLAZA</td>\n",
       "      <td>EMR</td>\n",
       "      <td>IND</td>\n",
       "      <td>2018-07-21 01:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>8021406</td>\n",
       "      <td>930044</td>\n",
       "      <td>1427866</td>\n",
       "      <td>41760.0</td>\n",
       "      <td>151341.0</td>\n",
       "      <td>364 days 04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         level_0    index    c_a  unit       scp       station linename  \\\n",
       "1427698  1427698  1526516  N309A  R140  00-05-00  QUEENS PLAZA      EMR   \n",
       "1427740  1427740  1526558  N309A  R140  00-05-01  QUEENS PLAZA      EMR   \n",
       "1427782  1427782  1526600  N309A  R140  00-05-02  QUEENS PLAZA      EMR   \n",
       "1427824  1427824  1526642  N309A  R140  00-05-03  QUEENS PLAZA      EMR   \n",
       "1427866  1427866  1526684  N309A  R140  00-06-00  QUEENS PLAZA      EMR   \n",
       "\n",
       "        division           date_time     desc  entries   exits    Index  \\\n",
       "1427698      IND 2018-07-21 01:00:00  REGULAR     5430       0  1427698   \n",
       "1427740      IND 2018-07-21 01:00:00  REGULAR        0      91  1427740   \n",
       "1427782      IND 2018-07-21 01:00:00  REGULAR     1095       0  1427782   \n",
       "1427824      IND 2018-07-21 01:00:00  REGULAR        0     497  1427824   \n",
       "1427866      IND 2018-07-21 01:00:00  REGULAR  8021406  930044  1427866   \n",
       "\n",
       "         num_entries  num_exits     hours_elapsed  \n",
       "1427698        810.0        0.0 364 days 04:00:00  \n",
       "1427740          0.0       55.0 364 days 04:00:00  \n",
       "1427782        143.0        0.0 364 days 04:00:00  \n",
       "1427824          0.0       33.0 364 days 04:00:00  \n",
       "1427866      41760.0   151341.0 364 days 04:00:00  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['hours_elapsed'] == df['hours_elapsed'].max()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['hours_elapsed'] != df['hours_elapsed'].max()]"
   ]
  },
  {
   "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>level_0</th>\n",
       "      <th>index</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_time</th>\n",
       "      <th>desc</th>\n",
       "      <th>entries</th>\n",
       "      <th>exits</th>\n",
       "      <th>Index</th>\n",
       "      <th>num_entries</th>\n",
       "      <th>num_exits</th>\n",
       "      <th>hours_elapsed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1953245</th>\n",
       "      <td>1953245</td>\n",
       "      <td>2084773</td>\n",
       "      <td>D002</td>\n",
       "      <td>R390</td>\n",
       "      <td>00-03-02</td>\n",
       "      <td>8 AV</td>\n",
       "      <td>N</td>\n",
       "      <td>BMT</td>\n",
       "      <td>2017-05-06 01:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>15777157</td>\n",
       "      <td>5811094</td>\n",
       "      <td>1953245</td>\n",
       "      <td>14571690.0</td>\n",
       "      <td>4527159.0</td>\n",
       "      <td>-452 days +04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         level_0    index   c_a  unit       scp station linename division  \\\n",
       "1953245  1953245  2084773  D002  R390  00-03-02    8 AV        N      BMT   \n",
       "\n",
       "                  date_time     desc   entries    exits    Index  num_entries  \\\n",
       "1953245 2017-05-06 01:00:00  REGULAR  15777157  5811094  1953245   14571690.0   \n",
       "\n",
       "         num_exits       hours_elapsed  \n",
       "1953245  4527159.0 -452 days +04:00:00  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['hours_elapsed'] == df['hours_elapsed'].min()]"
   ]
  },
  {
   "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>level_0</th>\n",
       "      <th>index</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_time</th>\n",
       "      <th>desc</th>\n",
       "      <th>entries</th>\n",
       "      <th>exits</th>\n",
       "      <th>Index</th>\n",
       "      <th>num_entries</th>\n",
       "      <th>num_exits</th>\n",
       "      <th>hours_elapsed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1864221</th>\n",
       "      <td>1864221</td>\n",
       "      <td>1995749</td>\n",
       "      <td>A061</td>\n",
       "      <td>R142</td>\n",
       "      <td>00-00-02</td>\n",
       "      <td>DELANCEY/ESSEX</td>\n",
       "      <td>FJMZ</td>\n",
       "      <td>BMT</td>\n",
       "      <td>2018-06-09 01:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>1646619071</td>\n",
       "      <td>1057689610</td>\n",
       "      <td>1864221</td>\n",
       "      <td>1.646614e+09</td>\n",
       "      <td>1.057688e+09</td>\n",
       "      <td>-42 days +04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         level_0    index   c_a  unit       scp         station linename  \\\n",
       "1864221  1864221  1995749  A061  R142  00-00-02  DELANCEY/ESSEX     FJMZ   \n",
       "\n",
       "        division           date_time     desc     entries       exits  \\\n",
       "1864221      BMT 2018-06-09 01:00:00  REGULAR  1646619071  1057689610   \n",
       "\n",
       "           Index   num_entries     num_exits      hours_elapsed  \n",
       "1864221  1864221  1.646614e+09  1.057688e+09 -42 days +04:00:00  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['num_entries'] == df['num_entries'].max()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>level_0</th>\n",
       "      <th>index</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_time</th>\n",
       "      <th>desc</th>\n",
       "      <th>entries</th>\n",
       "      <th>exits</th>\n",
       "      <th>Index</th>\n",
       "      <th>num_entries</th>\n",
       "      <th>num_exits</th>\n",
       "      <th>hours_elapsed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1787835</th>\n",
       "      <td>1787835</td>\n",
       "      <td>1919363</td>\n",
       "      <td>A061</td>\n",
       "      <td>R142</td>\n",
       "      <td>00-00-02</td>\n",
       "      <td>DELANCEY/ESSEX</td>\n",
       "      <td>FJMZ</td>\n",
       "      <td>BMT</td>\n",
       "      <td>2018-07-18 13:00:00</td>\n",
       "      <td>RECOVR AUD</td>\n",
       "      <td>7</td>\n",
       "      <td>0</td>\n",
       "      <td>1787835</td>\n",
       "      <td>-1.646681e+09</td>\n",
       "      <td>-1.057707e+09</td>\n",
       "      <td>2 days 04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         level_0    index   c_a  unit       scp         station linename  \\\n",
       "1787835  1787835  1919363  A061  R142  00-00-02  DELANCEY/ESSEX     FJMZ   \n",
       "\n",
       "        division           date_time        desc  entries  exits    Index  \\\n",
       "1787835      BMT 2018-07-18 13:00:00  RECOVR AUD        7      0  1787835   \n",
       "\n",
       "          num_entries     num_exits   hours_elapsed  \n",
       "1787835 -1.646681e+09 -1.057707e+09 2 days 04:00:00  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['num_entries'] == df['num_entries'].min()]"
   ]
  },
  {
   "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>level_0</th>\n",
       "      <th>index</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_time</th>\n",
       "      <th>desc</th>\n",
       "      <th>entries</th>\n",
       "      <th>exits</th>\n",
       "      <th>Index</th>\n",
       "      <th>num_entries</th>\n",
       "      <th>num_exits</th>\n",
       "      <th>hours_elapsed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1346644</th>\n",
       "      <td>1346644</td>\n",
       "      <td>1445462</td>\n",
       "      <td>N215</td>\n",
       "      <td>R237</td>\n",
       "      <td>00-00-02</td>\n",
       "      <td>182-183 STS</td>\n",
       "      <td>BD</td>\n",
       "      <td>IND</td>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>1493111155</td>\n",
       "      <td>1845552454</td>\n",
       "      <td>1346644</td>\n",
       "      <td>1.482180e+09</td>\n",
       "      <td>1.838317e+09</td>\n",
       "      <td>28 days 04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         level_0    index   c_a  unit       scp      station linename  \\\n",
       "1346644  1346644  1445462  N215  R237  00-00-02  182-183 STS       BD   \n",
       "\n",
       "        division  date_time     desc     entries       exits    Index  \\\n",
       "1346644      IND 2018-06-30  REGULAR  1493111155  1845552454  1346644   \n",
       "\n",
       "          num_entries     num_exits    hours_elapsed  \n",
       "1346644  1.482180e+09  1.838317e+09 28 days 04:00:00  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['num_exits'] == df['num_exits'].max()]"
   ]
  },
  {
   "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>level_0</th>\n",
       "      <th>index</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_time</th>\n",
       "      <th>desc</th>\n",
       "      <th>entries</th>\n",
       "      <th>exits</th>\n",
       "      <th>Index</th>\n",
       "      <th>num_entries</th>\n",
       "      <th>num_exits</th>\n",
       "      <th>hours_elapsed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1543682</th>\n",
       "      <td>1543682</td>\n",
       "      <td>1675210</td>\n",
       "      <td>N215</td>\n",
       "      <td>R237</td>\n",
       "      <td>00-00-02</td>\n",
       "      <td>182-183 STS</td>\n",
       "      <td>BD</td>\n",
       "      <td>IND</td>\n",
       "      <td>2018-05-19</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>10911801</td>\n",
       "      <td>7230222</td>\n",
       "      <td>1543682</td>\n",
       "      <td>-1.482157e+09</td>\n",
       "      <td>-1.838335e+09</td>\n",
       "      <td>-74 days +04:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         level_0    index   c_a  unit       scp      station linename  \\\n",
       "1543682  1543682  1675210  N215  R237  00-00-02  182-183 STS       BD   \n",
       "\n",
       "        division  date_time     desc   entries    exits    Index  \\\n",
       "1543682      IND 2018-05-19  REGULAR  10911801  7230222  1543682   \n",
       "\n",
       "          num_entries     num_exits      hours_elapsed  \n",
       "1543682 -1.482157e+09 -1.838335e+09 -74 days +04:00:00  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['num_exits'] == df['num_exits'].min()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unused Code Below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#Entry and Exits data recorded as a 'snapshot' every 4 hours\n",
    "#Adding columns tracking passanger entery and exit evert 4 hours\n",
    "df = df.assign(entry_count = df['entries'] - df['entries'].shift(1))\n",
    "df = df.assign(exit_count = df['exits'] - df['exits'].shift(1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This block can be used for the challenge but not neccessary for our goals for the project\n",
    "returns values in the formate requested by the challages \n",
    "\n",
    "#This blocks combines the date and time columns so they may later be converted to a datetime type\n",
    "\n",
    "#combines columns, separated by a space, into the pre-exisiting column 'date'\n",
    "df.date = df.date + ' '+ df.time\n",
    "\n",
    "#rename to showcase the new time feature in the column\n",
    "df.rename({'date': 'date_time'}, axis = 'columns', inplace = True)\n",
    "\n",
    "#removes the now redundant time column\n",
    "df.drop(columns = ['time'], inplace= True)\n",
    "\n",
    "#converting date_time from string to datetime\n",
    "df.date_time = df.date_time.apply(lambda x: dateutil.parser.parse(x))\n",
    "\n",
    "\n",
    "#time intensive. Run, then take 5\n",
    "# df.date = df.date.apply(lambda x: dateutil.parser.parse(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, may be used for the challenge, but not necessary for the project\n",
    "\n",
    "#create a new column that combines the date and time columns, separated by a single space\n",
    "df['date_time'] = df.date + ' '+ df.time\n",
    "#Warning: time intensive block, run then take 5\n",
    "#converting date_time from string to datetime\n",
    "df.date_time = df.date_time.apply(lambda x: dateutil.parser.parse(x))\n",
    "df.head()\n",
    "#currently returning datetime in same formate it is in: mm/dd/yyyy hh:mm:ss\n",
    "#different than the challenge scope, but seems "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Incase any issues arrive with exit_entry function, this is the originial code\n",
    "\n",
    "#This should be a function. See above\n",
    "#time intensive. Run, then take 5\n",
    "\n",
    "#Groups turnstiles by station, turnstyle id, and date\n",
    "#within each group find the difference between the consecutive entry points \n",
    "#this difference is stored as a series\n",
    "df.test = df.groupby(['station','scp','date']).entries.apply(lambda x: x - x.shift(1))\n",
    "\n",
    "#create a temp df that uses the same index column as the main df\n",
    "\n",
    "#merge the main and temp df on the index column\n",
    "df = pd.merge(df, df_entry, how = 'outer', on = 'Index')\n",
    "\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This function is no longer being used\n",
    "\n",
    "'''\n",
    "Creates a new series that groups turnstyles by station, id, and date, \n",
    "and find the difference in consecutive data points\n",
    "stores this difference as a series\n",
    "crease a temp df using difference and the main df's index column\n",
    "'''\n",
    "def entry_exit_calc(column, new_col_name, index_name):\n",
    "    #Groups turnstiles by station, turnstyle id, and date\n",
    "    #within each group find the difference between the consecutive entry points \n",
    "    #this difference is stored as a series\n",
    "    difference_of_entries = df.groupby(['station', 'unit', 'c_a', 'scp'])[column].apply(lambda x: x- x.shift(1))\n",
    "    \n",
    "    hours_elapsed = df.groupby(['station', 'unit', 'c_a', 'scp'])['date_time'].apply(lambda x: x- x.shift(1)) #make sure date_time is correct name\n",
    "    #create a temp df that uses the same index column as the main df\n",
    "    return pd.DataFrame({'Index': df[index_name], new_col_name: difference_of_tolls, hours_elapsed: hours_elapsed})\n",
    "    "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}