benson_cleaner.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/home/rjh336/nyc17_ds13/student_submissions/projects/01-benson/benson_team_6\n",
      "['170408.csv', '170415.csv', '170422.csv', '170429.csv', '170506.csv', '170513.csv', '170520.csv', '170527.csv', '170603.csv', '170610.csv', '170617.csv', '170624.csv', '170701.csv']\n"
     ]
    }
   ],
   "source": [
    "# run from root\n",
    "!pwd\n",
    "\n",
    "from os import listdir\n",
    "from os.path import isfile, join\n",
    "# create filenames for pd.read_csv()\n",
    "files = [f for f in listdir('./csv') if isfile(join('./csv', f))]\n",
    "\n",
    "# specify year to go back to\n",
    "# example below is just one year\n",
    "# we used 3 years which takes time to preprocess below\n",
    "from_year = 2017\n",
    "# use to cut down years pulled\n",
    "files_cut = list(sorted([f for f in files if (int(f[:2]) >= from_year%2000)]))\n",
    "print(files_cut)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Loading ./csv/170408.csv\n",
      "Loading ./csv/170415.csv\n",
      "Loading ./csv/170422.csv\n",
      "Loading ./csv/170429.csv\n",
      "Loading ./csv/170506.csv\n",
      "Loading ./csv/170513.csv\n",
      "Loading ./csv/170520.csv\n",
      "Loading ./csv/170527.csv\n",
      "Loading ./csv/170603.csv\n",
      "Loading ./csv/170610.csv\n",
      "Loading ./csv/170617.csv\n",
      "Loading ./csv/170624.csv\n",
      "Loading ./csv/170701.csv\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2565187 entries, 0 to 197420\n",
      "Data columns (total 12 columns):\n",
      "Unnamed: 0                                                              int64\n",
      "C/A                                                                     object\n",
      "UNIT                                                                    object\n",
      "SCP                                                                     object\n",
      "STATION                                                                 object\n",
      "LINENAME                                                                object\n",
      "DIVISION                                                                object\n",
      "DATE                                                                    object\n",
      "TIME                                                                    object\n",
      "DESC                                                                    object\n",
      "ENTRIES                                                                 int64\n",
      "EXITS                                                                   int64\n",
      "dtypes: int64(3), object(9)\n",
      "memory usage: 166.4+ MB\n"
     ]
    }
   ],
   "source": [
    "# Create initial DataFrame\n",
    "dfs = pd.DataFrame()\n",
    "for file in files_cut:\n",
    "    print(\"Loading {}\".format(str(\"./csv/\"+file)))\n",
    "    df = pd.read_csv(str(\"./csv/\"+file))\n",
    "    dfs = dfs.append(df)\n",
    "    \n",
    "dfs.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</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>0</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>04/01/2017</td>\n",
       "      <td>00:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6117586</td>\n",
       "      <td>2072818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</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>04/01/2017</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6117617</td>\n",
       "      <td>2072823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</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>04/01/2017</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6117635</td>\n",
       "      <td>2072856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</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>04/01/2017</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6117746</td>\n",
       "      <td>2072972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</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>04/01/2017</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>REGULAR</td>\n",
       "      <td>6118002</td>\n",
       "      <td>2073040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0   C/A  UNIT       SCP STATION LINENAME DIVISION        DATE  \\\n",
       "0           0  A002  R051  02-00-00   59 ST  NQR456W      BMT  04/01/2017   \n",
       "1           1  A002  R051  02-00-00   59 ST  NQR456W      BMT  04/01/2017   \n",
       "2           2  A002  R051  02-00-00   59 ST  NQR456W      BMT  04/01/2017   \n",
       "3           3  A002  R051  02-00-00   59 ST  NQR456W      BMT  04/01/2017   \n",
       "4           4  A002  R051  02-00-00   59 ST  NQR456W      BMT  04/01/2017   \n",
       "\n",
       "       TIME     DESC  ENTRIES  \\\n",
       "0  00:00:00  REGULAR  6117586   \n",
       "1  04:00:00  REGULAR  6117617   \n",
       "2  08:00:00  REGULAR  6117635   \n",
       "3  12:00:00  REGULAR  6117746   \n",
       "4  16:00:00  REGULAR  6118002   \n",
       "\n",
       "   EXITS                                                                 \n",
       "0                                            2072818                     \n",
       "1                                            2072823                     \n",
       "2                                            2072856                     \n",
       "3                                            2072972                     \n",
       "4                                            2073040                     "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def preprocess(df):\n",
    "    \n",
    "    # STANDARDIZE COL NAMES\n",
    "    df.columns = df.columns.str.strip()\n",
    "        \n",
    "    # Create DATETIME column from 'DATE' and 'TIME' cols\n",
    "    df['DATETIME'] = pd.to_datetime(df.DATE + \" \" + df.TIME, \n",
    "                                    format=\"%m/%d/%Y %H:%M:%S\")\n",
    "    \n",
    "    # Create 'DOW' (Day of Week) col from 'DATETIME'\n",
    "    df['DOW'] = df[['DATETIME']]\\\n",
    "    .apply(lambda x: datetime.datetime.strftime(x['DATETIME'], '%A'), axis=1)\n",
    "       \n",
    "    # DROP UNNECESSARY COLUMNS\n",
    "    df = df.drop(['LINENAME', 'DIVISION', 'DATE','Unnamed: 0'], axis=1) \n",
    "\n",
    "    # Remove non-REGULAR values from 'DESC'\n",
    "    df = df.drop(df.loc[df.DESC != 'REGULAR'].index)\n",
    "    df = df.drop(['DESC', 'TIME'], axis=1) \n",
    "    \n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DOW</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>6117586</td>\n",
       "      <td>2072818</td>\n",
       "      <td>2017-04-01 00:00:00</td>\n",
       "      <td>Saturday</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>6117617</td>\n",
       "      <td>2072823</td>\n",
       "      <td>2017-04-01 04:00:00</td>\n",
       "      <td>Saturday</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>6117635</td>\n",
       "      <td>2072856</td>\n",
       "      <td>2017-04-01 08:00:00</td>\n",
       "      <td>Saturday</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>6117746</td>\n",
       "      <td>2072972</td>\n",
       "      <td>2017-04-01 12:00:00</td>\n",
       "      <td>Saturday</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>6118002</td>\n",
       "      <td>2073040</td>\n",
       "      <td>2017-04-01 16:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP STATION  ENTRIES    EXITS            DATETIME  \\\n",
       "0  A002  R051  02-00-00   59 ST  6117586  2072818 2017-04-01 00:00:00   \n",
       "1  A002  R051  02-00-00   59 ST  6117617  2072823 2017-04-01 04:00:00   \n",
       "2  A002  R051  02-00-00   59 ST  6117635  2072856 2017-04-01 08:00:00   \n",
       "3  A002  R051  02-00-00   59 ST  6117746  2072972 2017-04-01 12:00:00   \n",
       "4  A002  R051  02-00-00   59 ST  6118002  2073040 2017-04-01 16:00:00   \n",
       "\n",
       "        DOW  \n",
       "0  Saturday  \n",
       "1  Saturday  \n",
       "2  Saturday  \n",
       "3  Saturday  \n",
       "4  Saturday  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#BREAKPOINT\n",
    "\n",
    "df_2 = preprocess(dfs)\n",
    "df_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "prior shape:  (2448638, 8)\n",
      "post shape:  (2448638, 8)\n"
     ]
    }
   ],
   "source": [
    "# Drop Nulls created by previous cell\n",
    "print(\"prior shape: \", df_2.shape)\n",
    "df_2.dropna(how='any', inplace=True)\n",
    "print(\"post shape: \", df_2.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "prior shape:  (2448638, 8)\n",
      "post shape:  (2448638, 8)\n"
     ]
    }
   ],
   "source": [
    "# Check for duplicates for a given turnstile for a given datetime\n",
    "(df_2\n",
    " .groupby([\"C/A\", \"UNIT\", \"SCP\", \"STATION\", \"DATETIME\"])\n",
    " .ENTRIES.count()\n",
    " .reset_index()\n",
    " .sort_values(\"ENTRIES\", ascending=False)).head(5)\n",
    "\n",
    "# DROP DUPLICATES\n",
    "print(\"prior shape: \", df_2.shape)\n",
    "df_2.drop_duplicates(subset=[\"C/A\", \"UNIT\", \"SCP\", \"STATION\", \"DATETIME\"], inplace=True)\n",
    "print(\"post shape: \", df_2.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_2 = df_2.sort_values(['STATION', 'C/A', 'UNIT', 'SCP','DATETIME'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DOW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29335</th>\n",
       "      <td>H007</td>\n",
       "      <td>R248</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>1 AV</td>\n",
       "      <td>12184694</td>\n",
       "      <td>13417252</td>\n",
       "      <td>2017-04-01 00:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29339</th>\n",
       "      <td>H007</td>\n",
       "      <td>R248</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>1 AV</td>\n",
       "      <td>12186126</td>\n",
       "      <td>13419539</td>\n",
       "      <td>2017-04-01 16:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29340</th>\n",
       "      <td>H007</td>\n",
       "      <td>R248</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>1 AV</td>\n",
       "      <td>12186780</td>\n",
       "      <td>13420833</td>\n",
       "      <td>2017-04-01 20:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29341</th>\n",
       "      <td>H007</td>\n",
       "      <td>R248</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>1 AV</td>\n",
       "      <td>12187300</td>\n",
       "      <td>13421711</td>\n",
       "      <td>2017-04-02 00:00:00</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29342</th>\n",
       "      <td>H007</td>\n",
       "      <td>R248</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>1 AV</td>\n",
       "      <td>12187444</td>\n",
       "      <td>13422048</td>\n",
       "      <td>2017-04-02 04:00:00</td>\n",
       "      <td>Sunday</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        C/A  UNIT       SCP STATION   ENTRIES     EXITS            DATETIME  \\\n",
       "29335  H007  R248  00-00-00    1 AV  12184694  13417252 2017-04-01 00:00:00   \n",
       "29339  H007  R248  00-00-00    1 AV  12186126  13419539 2017-04-01 16:00:00   \n",
       "29340  H007  R248  00-00-00    1 AV  12186780  13420833 2017-04-01 20:00:00   \n",
       "29341  H007  R248  00-00-00    1 AV  12187300  13421711 2017-04-02 00:00:00   \n",
       "29342  H007  R248  00-00-00    1 AV  12187444  13422048 2017-04-02 04:00:00   \n",
       "\n",
       "            DOW  \n",
       "29335  Saturday  \n",
       "29339  Saturday  \n",
       "29340  Saturday  \n",
       "29341    Sunday  \n",
       "29342    Sunday  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Create diff columns to take entries/exits for given time interval\n",
    "\n",
    "df_2['entry_diff'] = df_2.groupby(['STATION', 'C/A', 'UNIT', 'SCP'], as_index=False)['ENTRIES'].diff().tolist()\n",
    "df_2['exit_diff'] = df_2.groupby(['STATION', 'C/A', 'UNIT', 'SCP'], as_index=False)['EXITS'].diff().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DOW</th>\n",
       "      <th>entry_diff</th>\n",
       "      <th>exit_diff</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29335</th>\n",
       "      <td>H007</td>\n",
       "      <td>R248</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>1 AV</td>\n",
       "      <td>12184694</td>\n",
       "      <td>13417252</td>\n",
       "      <td>2017-04-01 00:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29339</th>\n",
       "      <td>H007</td>\n",
       "      <td>R248</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>1 AV</td>\n",
       "      <td>12186126</td>\n",
       "      <td>13419539</td>\n",
       "      <td>2017-04-01 16:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>1432.0</td>\n",
       "      <td>2287.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29340</th>\n",
       "      <td>H007</td>\n",
       "      <td>R248</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>1 AV</td>\n",
       "      <td>12186780</td>\n",
       "      <td>13420833</td>\n",
       "      <td>2017-04-01 20:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>654.0</td>\n",
       "      <td>1294.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29341</th>\n",
       "      <td>H007</td>\n",
       "      <td>R248</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>1 AV</td>\n",
       "      <td>12187300</td>\n",
       "      <td>13421711</td>\n",
       "      <td>2017-04-02 00:00:00</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>520.0</td>\n",
       "      <td>878.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29342</th>\n",
       "      <td>H007</td>\n",
       "      <td>R248</td>\n",
       "      <td>00-00-00</td>\n",
       "      <td>1 AV</td>\n",
       "      <td>12187444</td>\n",
       "      <td>13422048</td>\n",
       "      <td>2017-04-02 04:00:00</td>\n",
       "      <td>Sunday</td>\n",
       "      <td>144.0</td>\n",
       "      <td>337.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        C/A  UNIT       SCP STATION   ENTRIES     EXITS            DATETIME  \\\n",
       "29335  H007  R248  00-00-00    1 AV  12184694  13417252 2017-04-01 00:00:00   \n",
       "29339  H007  R248  00-00-00    1 AV  12186126  13419539 2017-04-01 16:00:00   \n",
       "29340  H007  R248  00-00-00    1 AV  12186780  13420833 2017-04-01 20:00:00   \n",
       "29341  H007  R248  00-00-00    1 AV  12187300  13421711 2017-04-02 00:00:00   \n",
       "29342  H007  R248  00-00-00    1 AV  12187444  13422048 2017-04-02 04:00:00   \n",
       "\n",
       "            DOW  entry_diff  exit_diff  \n",
       "29335  Saturday         NaN        NaN  \n",
       "29339  Saturday      1432.0     2287.0  \n",
       "29340  Saturday       654.0     1294.0  \n",
       "29341    Sunday       520.0      878.0  \n",
       "29342    Sunday       144.0      337.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Only used for multiple years of data\n",
    "#df_2['YEAR'] = df_2['DATETIME'].transform(lambda x: x.year)\n",
    "#df_2['MONTH'] = df_2['DATETIME'].transform(lambda x: x.month)\n",
    "#df_2.groupby(['YEAR', 'MONTH']).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "prior shape:  (2448638, 10)\n",
      "post shape:  (2443903, 10)\n"
     ]
    }
   ],
   "source": [
    "# Drop Nulls created by previous groupby().diff()\n",
    "print(\"prior shape: \", df_2.shape)\n",
    "df_2.dropna(how='any', inplace=True)\n",
    "print(\"post shape: \", df_2.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "negative_entries:  0.007851784624839856\n",
      "negative_exits:  0.006221196176771336\n",
      "prior shape:  (2443903, 10)\n",
      "post shape:  (1996409, 10)\n"
     ]
    }
   ],
   "source": [
    "# ASSUMPTION: All negative actuals for entries and exits will be dropped from dataset\n",
    "\n",
    "len_entry_diffs = df_2['entry_diff'].shape[0]\n",
    "len_exit_diffs = df_2['exit_diff'].shape[0]\n",
    "\n",
    "negative_entries = df_2[df_2['entry_diff'] < 0].shape[0] / len_entry_diffs\n",
    "negative_exits = df_2[df_2['exit_diff'] < 0].shape[0] / len_exit_diffs\n",
    "\n",
    "print('negative_entries: ', negative_entries)\n",
    "print('negative_exits: ', negative_exits)\n",
    "\n",
    "## DROP NEGATIVES\n",
    "print(\"prior shape: \", df_2.shape)\n",
    "df_2 = df_2.loc[ (df_2['entry_diff'] > 0) & (df_2['exit_diff'] > 0) ]\n",
    "print(\"post shape: \", df_2.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "prior shape:  (1996409, 10)\n",
      "post shape:  (1996368, 10)\n"
     ]
    }
   ],
   "source": [
    "# Create Outlier Threshold based on total seconds in a day\n",
    "threshold_entry = 86400\n",
    "threshold_exit = 86400\n",
    "\n",
    "## DROP OUTLIERS\n",
    "print(\"prior shape: \", df_2.shape)\n",
    "df_2 = df_2[(df_2['entry_diff'] < threshold_entry) & (df_2['exit_diff'] < threshold_exit)]\n",
    "print(\"post shape: \", df_2.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "post shape:  (1996368, 11)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>ENTRIES</th>\n",
       "      <th>EXITS</th>\n",
       "      <th>DATETIME</th>\n",
       "      <th>DOW</th>\n",
       "      <th>entry_diff</th>\n",
       "      <th>exit_diff</th>\n",
       "      <th>TOTAL_TRAFFIC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>110690</th>\n",
       "      <td>PTH02</td>\n",
       "      <td>R544</td>\n",
       "      <td>00-00-05</td>\n",
       "      <td>HARRISON</td>\n",
       "      <td>92324</td>\n",
       "      <td>7364</td>\n",
       "      <td>2017-06-06 08:27:27</td>\n",
       "      <td>Tuesday</td>\n",
       "      <td>65925.0</td>\n",
       "      <td>5782.0</td>\n",
       "      <td>71707.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114536</th>\n",
       "      <td>PTH07</td>\n",
       "      <td>R550</td>\n",
       "      <td>00-02-00</td>\n",
       "      <td>CITY / BUS</td>\n",
       "      <td>4182</td>\n",
       "      <td>27701</td>\n",
       "      <td>2017-06-16 15:09:35</td>\n",
       "      <td>Friday</td>\n",
       "      <td>4180.0</td>\n",
       "      <td>27697.0</td>\n",
       "      <td>31877.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114450</th>\n",
       "      <td>PTH07</td>\n",
       "      <td>R550</td>\n",
       "      <td>00-02-00</td>\n",
       "      <td>CITY / BUS</td>\n",
       "      <td>4182</td>\n",
       "      <td>27701</td>\n",
       "      <td>2017-06-21 08:47:35</td>\n",
       "      <td>Wednesday</td>\n",
       "      <td>4178.0</td>\n",
       "      <td>27477.0</td>\n",
       "      <td>31655.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114458</th>\n",
       "      <td>PTH07</td>\n",
       "      <td>R550</td>\n",
       "      <td>00-02-00</td>\n",
       "      <td>CITY / BUS</td>\n",
       "      <td>4182</td>\n",
       "      <td>27701</td>\n",
       "      <td>2017-06-22 14:11:35</td>\n",
       "      <td>Thursday</td>\n",
       "      <td>4176.0</td>\n",
       "      <td>27399.0</td>\n",
       "      <td>31575.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114465</th>\n",
       "      <td>PTH07</td>\n",
       "      <td>R550</td>\n",
       "      <td>00-02-00</td>\n",
       "      <td>CITY / BUS</td>\n",
       "      <td>4182</td>\n",
       "      <td>27701</td>\n",
       "      <td>2017-06-23 15:23:35</td>\n",
       "      <td>Friday</td>\n",
       "      <td>4174.0</td>\n",
       "      <td>27341.0</td>\n",
       "      <td>31515.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          C/A  UNIT       SCP     STATION  ENTRIES  EXITS            DATETIME  \\\n",
       "110690  PTH02  R544  00-00-05    HARRISON    92324   7364 2017-06-06 08:27:27   \n",
       "114536  PTH07  R550  00-02-00  CITY / BUS     4182  27701 2017-06-16 15:09:35   \n",
       "114450  PTH07  R550  00-02-00  CITY / BUS     4182  27701 2017-06-21 08:47:35   \n",
       "114458  PTH07  R550  00-02-00  CITY / BUS     4182  27701 2017-06-22 14:11:35   \n",
       "114465  PTH07  R550  00-02-00  CITY / BUS     4182  27701 2017-06-23 15:23:35   \n",
       "\n",
       "              DOW  entry_diff  exit_diff  TOTAL_TRAFFIC  \n",
       "110690    Tuesday     65925.0     5782.0        71707.0  \n",
       "114536     Friday      4180.0    27697.0        31877.0  \n",
       "114450  Wednesday      4178.0    27477.0        31655.0  \n",
       "114458   Thursday      4176.0    27399.0        31575.0  \n",
       "114465     Friday      4174.0    27341.0        31515.0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create 'TOTAL_TRAFFIC\" = exits + entries. This will be our ranking metric\n",
    "\n",
    "df_2['TOTAL_TRAFFIC'] = df_2['entry_diff'] + df_2['exit_diff']\n",
    "print(\"post shape: \", df_2.shape)\n",
    "df_2.sort_values('TOTAL_TRAFFIC', ascending=False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "UNIQUE TIMES:  [datetime.time(4, 0) datetime.time(8, 0) datetime.time(12, 0)\n",
      " datetime.time(16, 0) datetime.time(20, 0) datetime.time(0, 0)]\n"
     ]
    }
   ],
   "source": [
    "# Force time intervals to 4-hour frequency\n",
    "df_2['TIME'] = df_2['DATETIME'].apply(lambda x: x.time())\n",
    "df_2 = df_2.groupby(['C/A',\n",
    "                     'UNIT', \n",
    "                     'SCP', \n",
    "                     'STATION', \n",
    "                     pd.Grouper(key='DATETIME', freq='4H'), \n",
    "                     'DOW', \n",
    "                     'TIME'])\\\n",
    "            .sum()\\\n",
    "            .reset_index()\n",
    "\n",
    "# Shift time intervals back 4 hours to make DOW accurate reading of day\n",
    "df_2['DATETIME'] = df_2['DATETIME'].apply(lambda x: x - datetime.timedelta(hours=4))\n",
    "\n",
    "print(\"UNIQUE TIMES: \", df_2['TIME'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# taken from preprocess()\n",
    "df_2['DOW'] = df_2['DATETIME'].apply(lambda x: datetime.datetime.strftime(x, '%A'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "#sanity check\n",
    "#df_2.groupby([pd.Grouper(key='DATETIME', freq='1D'), 'DOW', 'STATION'])\\\n",
    "#    .sum()\\\n",
    "#    .reset_index()\\\n",
    "#    .groupby('DOW')\\\n",
    "#    .mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>DOW</th>\n",
       "      <th>TIME</th>\n",
       "      <th>TOTAL_TRAFFIC</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>2017-04-01 00:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>04:00:00</td>\n",
       "      <td>36.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>2017-04-01 04:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>08:00:00</td>\n",
       "      <td>51.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>2017-04-01 08:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>12:00:00</td>\n",
       "      <td>227.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>2017-04-01 12:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>16:00:00</td>\n",
       "      <td>324.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>2017-04-01 16:00:00</td>\n",
       "      <td>Saturday</td>\n",
       "      <td>20:00:00</td>\n",
       "      <td>408.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    C/A  UNIT       SCP STATION            DATETIME       DOW      TIME  \\\n",
       "0  A002  R051  02-00-00   59 ST 2017-04-01 00:00:00  Saturday  04:00:00   \n",
       "1  A002  R051  02-00-00   59 ST 2017-04-01 04:00:00  Saturday  08:00:00   \n",
       "2  A002  R051  02-00-00   59 ST 2017-04-01 08:00:00  Saturday  12:00:00   \n",
       "3  A002  R051  02-00-00   59 ST 2017-04-01 12:00:00  Saturday  16:00:00   \n",
       "4  A002  R051  02-00-00   59 ST 2017-04-01 16:00:00  Saturday  20:00:00   \n",
       "\n",
       "   TOTAL_TRAFFIC  \n",
       "0           36.0  \n",
       "1           51.0  \n",
       "2          227.0  \n",
       "3          324.0  \n",
       "4          408.0  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# We only care about TOTAL_TRAFFIC\n",
    "df_2.drop(['ENTRIES', 'EXITS', 'entry_diff', 'exit_diff'], axis=1, inplace=True)\n",
    "df_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#sanity check\n",
    "\n",
    "#from copy import deepcopy\n",
    "#traffic_by_station = deepcopy(df_2)\n",
    "#traffic_by_station['DATE'] = traffic_by_station['DATETIME'].apply(lambda x: x.date())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#sanity check\n",
    "\n",
    "#tmp = traffic_by_station.groupby(['STATION','DATE'],as_index=False).sum()\n",
    "#tmp.groupby('STATION', as_index=False).mean().sort_values('TOTAL_TRAFFIC',ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#sanity check\n",
    "#times_by_traffic = df_2.groupby('TIME').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#sanity check \n",
    "#times_by_traffic.sort_values('TOTAL_TRAFFIC',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from difflib import SequenceMatcher\n",
    "from copy import deepcopy\n",
    "def similar(a, b):\n",
    "    return SequenceMatcher(None, a, b).ratio()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('86 ST-2 AVE', '96 ST-2 AVE')]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Look for stations that are the same but named slightly differently\n",
    "cleaned_df = deepcopy(df_2)\n",
    "df_work = cleaned_df[cleaned_df['STATION'].str.contains('-') & cleaned_df['STATION'].str.contains('ST')]\n",
    "stations = df_work.STATION.unique()\n",
    "similar_pairs = []\n",
    "for i,s in enumerate(stations):\n",
    "    for j in range(i,len(stations)):\n",
    "        if not i == j:\n",
    "            sim = similar(sorted(s), sorted(stations[j]))\n",
    "            if sim > 0.9:\n",
    "                similar_pairs.append((stations[i],stations[j]))\n",
    "similar_pairs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#NOTE: 'replaces' is based on output from similar_pairs\n",
    "#more years == more naming conflicts\n",
    "\n",
    "def norm_names(x):\n",
    "    replaces = {'TIMES SQ-42 ST':'42 ST-TIMES SQ',\n",
    "                'GRD CNTRL-42 ST':'42 ST-GRD CNTRL',\n",
    "                '4AV-9 ST':'4 AV-9 ST',\n",
    "                '110 ST-CATHEDRL':'CATHEDRL-110 ST',\n",
    "                '68ST-HUNTER CO':'68ST-HUNTER COL',\n",
    "                '149 ST-3 AVE':'3 AV-149 ST'\n",
    "               }\n",
    "    if x in replaces:\n",
    "        return replaces[x]\n",
    "    else:\n",
    "        return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Normalize station names which are different but referring to the same place\n",
    "\n",
    "df_2['STATION'] = df_2['STATION'].apply(lambda x: norm_names(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Load into \"merge_weather.ipynb\"\n",
    "# Load into \"benson_viz.ipynb\"\n",
    "df_2.to_csv('./prepped_data_2015-2017_presummer.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [default]",
   "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.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}