Data_Preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from uszipcode import SearchEngine\n",
    "from uszipcode import Zipcode\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_data_file(csv_path, row_count=None):\n",
    "    dfcolumns = pd.read_csv(csv_path, nrows = 1)\n",
    "    ncols = len(dfcolumns.columns)\n",
    "    if row_count:\n",
    "        df = pd.read_csv(csv_path, header = None, sep= ',', \n",
    "                     skiprows = 1, usecols = list(range(ncols)),\n",
    "                     names = dfcolumns.columns, low_memory=False, nrows=row_count)\n",
    "    else:\n",
    "        df = pd.read_csv(csv_path, header = None, sep= ',', \n",
    "                         skiprows = 1, usecols = list(range(ncols)),\n",
    "                         names = dfcolumns.columns, low_memory=False)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# spark.read().csv(\"s3://\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_columns(df, col_type=\"relevant\"):\n",
    "    cols = []\n",
    "    dfcols = list(df.columns)\n",
    "    if col_type == \"relevant\":\n",
    "        subs_to_check = ['time', 'location', 'passenger','distance', \n",
    "                         'ratecode', 'fare', \"longitude\", \"latitude\"]\n",
    "        for sub in subs_to_check:\n",
    "            for col in dfcols:\n",
    "                if sub.lower() in col.lower():\n",
    "                    cols.append(col)\n",
    "    \n",
    "    elif col_type == \"geolocation\":\n",
    "        subs_to_check = [\"location\", \"longitude\", \"latitude\"]\n",
    "        for sub in subs_to_check:\n",
    "            for col in dfcols:\n",
    "                if sub.lower() in col.lower():\n",
    "                    cols.append(col)\n",
    "    return cols\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting rid of Null, NaN and Zero values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_nan_values(df):\n",
    "    df = df.replace(to_replace='None', value=np.nan).dropna()\n",
    "    print(df.shape)\n",
    "    df = df[(df != 0).all(1)]\n",
    "    print(df.shape)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Removing trips with invalid latitudes and longitudes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_invalid_lat_long(df):\n",
    "    max_lat = 40.917577\n",
    "    min_lat = 40.477399 \n",
    "    max_long = -73.700272 \n",
    "    min_long = -74.259090\n",
    "    loc_cols = get_columns(df, \"geolocation\")\n",
    "    print(loc_cols)\n",
    "    lat_cols = []\n",
    "    long_cols = []\n",
    "    for col in loc_cols:\n",
    "        if \"latitude\" in col.lower():\n",
    "            lat_cols.append(col)\n",
    "        elif \"longitude\" in col.lower():\n",
    "            long_cols.append(col)\n",
    "\n",
    "    for col in lat_cols:\n",
    "        df = df.loc[(df[col] >= min_lat) & (df[col] <= max_lat)]\n",
    "        print(df.shape)\n",
    "\n",
    "    for col in long_cols:\n",
    "        df = df.loc[(df[col] >= min_long) & (df[col] <= max_long)]\n",
    "        print(df.shape)\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Removing all trips with Fare_amount less than min NYC taxi fare, i.e., \\$2.5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_invalid_fare_trips(df):\n",
    "    df = df.loc[df1[\"Fare_amount\"] >= 2.5]\n",
    "    print(df.shape)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_column_datatypes(df, date_columns=[], numeric_columns=[]):\n",
    "    df[date_columns] = df[date_columns].apply(pd.to_datetime)\n",
    "    df[numeric_columns] = df[numeric_columns].apply(pd.to_numeric)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Feature Engineering"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting ZipCode from Latitude and Longitude"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_zip_code(lat,long):\n",
    "    search = SearchEngine(simple_zipcode=True)\n",
    "    result = search.by_coordinates(lat, long, radius=5, returns=1)\n",
    "    return result[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_zip_codes(df):\n",
    "    for index, row in df.iterrows():\n",
    "        lat = row['Pickup_latitude']\n",
    "        long = row['Pickup_longitude']\n",
    "        zipcode = get_zip_code(lat,long)\n",
    "\n",
    "        df.at[index,'zipcode'] = zipcode.zipcode\n",
    "#         df2.at[index,'city'] = zipcode.city\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Trip Frequency by time slices in a particular Zipcode radius "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_trip_frequency(df, curr_row, time_col, hrs=1):\n",
    "    print(curr_row)\n",
    "    start_time = curr_row[time_col] - timedelta(hours=hrs)\n",
    "    end_time = curr_row[time_col]\n",
    "    current_zip = curr_row['zipcode']\n",
    "    rides_between = df[df[time_col].between(start_time,end_time, inclusive=True)]\n",
    "    rides_between_same_zip = rides_between[rides_between['zipcode'] == current_zip]\n",
    "    return len(rides_between_same_zip)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_trip_frequencies(df, hrs=[]):\n",
    "    print(\"try\")\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Main"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "files = [\"green_tripdata_2018-06.csv\",\n",
    "         \"green_tripdata_2014-05.csv\", \n",
    "         \"yellow_tripdata_2018-06.csv\",\n",
    "         \"fhv_tripdata_2018-06.csv\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = read_data_file(files[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['lpep_pickup_datetime', 'Lpep_dropoff_datetime', 'Passenger_count', 'Trip_distance', 'RateCodeID', 'Fare_amount', 'Pickup_longitude', 'Dropoff_longitude', 'Pickup_latitude', 'Dropoff_latitude']\n"
     ]
    }
   ],
   "source": [
    "cols = get_columns(df)\n",
    "print(cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>lpep_pickup_datetime</th>\n",
       "      <th>Lpep_dropoff_datetime</th>\n",
       "      <th>Passenger_count</th>\n",
       "      <th>Trip_distance</th>\n",
       "      <th>RateCodeID</th>\n",
       "      <th>Fare_amount</th>\n",
       "      <th>Pickup_longitude</th>\n",
       "      <th>Dropoff_longitude</th>\n",
       "      <th>Pickup_latitude</th>\n",
       "      <th>Dropoff_latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014-05-01 00:00:00</td>\n",
       "      <td>2014-05-01 22:05:36</td>\n",
       "      <td>1</td>\n",
       "      <td>0.95</td>\n",
       "      <td>1</td>\n",
       "      <td>6.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-73.977715</td>\n",
       "      <td>0.0</td>\n",
       "      <td>40.687542</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-05-01 00:00:00</td>\n",
       "      <td>2014-05-01 07:52:17</td>\n",
       "      <td>1</td>\n",
       "      <td>1.95</td>\n",
       "      <td>1</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2014-05-01 00:00:00</td>\n",
       "      <td>2014-05-01 10:50:16</td>\n",
       "      <td>1</td>\n",
       "      <td>5.65</td>\n",
       "      <td>1</td>\n",
       "      <td>26.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-05-01 00:00:00</td>\n",
       "      <td>2014-05-01 20:50:04</td>\n",
       "      <td>1</td>\n",
       "      <td>6.88</td>\n",
       "      <td>1</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-05-01 00:00:00</td>\n",
       "      <td>2014-05-01 10:35:50</td>\n",
       "      <td>1</td>\n",
       "      <td>4.46</td>\n",
       "      <td>1</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  lpep_pickup_datetime Lpep_dropoff_datetime  Passenger_count  Trip_distance  \\\n",
       "0  2014-05-01 00:00:00   2014-05-01 22:05:36                1           0.95   \n",
       "1  2014-05-01 00:00:00   2014-05-01 07:52:17                1           1.95   \n",
       "2  2014-05-01 00:00:00   2014-05-01 10:50:16                1           5.65   \n",
       "3  2014-05-01 00:00:00   2014-05-01 20:50:04                1           6.88   \n",
       "4  2014-05-01 00:00:00   2014-05-01 10:35:50                1           4.46   \n",
       "\n",
       "   RateCodeID  Fare_amount  Pickup_longitude  Dropoff_longitude  \\\n",
       "0           1          6.5               0.0         -73.977715   \n",
       "1           1          9.0               0.0           0.000000   \n",
       "2           1         26.5               0.0           0.000000   \n",
       "3           1         24.0               0.0           0.000000   \n",
       "4           1         21.0               0.0           0.000000   \n",
       "\n",
       "   Pickup_latitude  Dropoff_latitude  \n",
       "0              0.0         40.687542  \n",
       "1              0.0          0.000000  \n",
       "2              0.0          0.000000  \n",
       "3              0.0          0.000000  \n",
       "4              0.0          0.000000  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = remove_nan_values(df1)\n",
    "df1 = remove_invalid_lat_long(df1)\n",
    "df1 = remove_invalid_fare_trips(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df1.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_columns = []\n",
    "numeric_columns = []\n",
    "df1 = fix_column_datatypes(df1, date_columns, numeric_columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = fill_zip_codes(df1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}