data_preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import warnings\n",
    "\n",
    "pd.set_option('display.float_format', '{:.4f}'.format)\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "fhv_df = pd.read_csv(\"data/FHVHV_Trip_Data.csv\", index_col=[0])\n",
    "yellow_df = pd.read_csv(\"data/Yellow_Trip_Data.csv\", index_col=[0])\n",
    "green_df = pd.read_csv(\"data/Green_Trip_Data.csv\", index_col=[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_zones_df = pd.read_csv(\"data/Taxi_Zones_Data.csv\")\n",
    "taxi_zones_df.columns = taxi_zones_df.columns.str.lower()\n",
    "taxi_zones_df.set_index(\"locationid\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>borough</th>\n",
       "      <th>zone</th>\n",
       "      <th>service_zone</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>locationid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EWR</td>\n",
       "      <td>Newark Airport</td>\n",
       "      <td>EWR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Queens</td>\n",
       "      <td>Jamaica Bay</td>\n",
       "      <td>Boro Zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bronx</td>\n",
       "      <td>Allerton/Pelham Gardens</td>\n",
       "      <td>Boro Zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Alphabet City</td>\n",
       "      <td>Yellow Zone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Staten Island</td>\n",
       "      <td>Arden Heights</td>\n",
       "      <td>Boro Zone</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  borough                     zone service_zone\n",
       "locationid                                                     \n",
       "1                     EWR           Newark Airport          EWR\n",
       "2                  Queens              Jamaica Bay    Boro Zone\n",
       "3                   Bronx  Allerton/Pelham Gardens    Boro Zone\n",
       "4               Manhattan            Alphabet City  Yellow Zone\n",
       "5           Staten Island            Arden Heights    Boro Zone"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxi_zones_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# For-Hire Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.int64(0)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fhv_df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "hvfhs_license_num\n",
       "HV0003    10521027\n",
       "HV0005     3908330\n",
       "HV0004       70643\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fhv_df[\"hvfhs_license_num\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "hvfhs_license_num\n",
       "Uber    10521027\n",
       "Lyft     3908330\n",
       "Via        70643\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace the business code with the name\n",
    "fhv_df[\"hvfhs_license_num\"] = fhv_df[\"hvfhs_license_num\"].replace(to_replace=[\"HV0002\", \"HV0003\", \"HV0004\", \"HV0005\"], value=[\"Juno\", \"Uber\", \"Via\", \"Lyft\"])\n",
    "fhv_df[\"hvfhs_license_num\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter to only Uber and Lyft\n",
    "fhv_df = fhv_df[~(fhv_df[\"hvfhs_license_num\"]==\"Via\")]\n",
    "fhv_df.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Columns not needed\n",
    "bad_cols = [\"dispatching_base_num\", \"originating_base_num\"]\n",
    "fhv_df.drop(columns=bad_cols, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "hvfhs_license_num       False\n",
       "request_datetime        False\n",
       "on_scene_datetime        True\n",
       "pickup_datetime         False\n",
       "dropoff_datetime        False\n",
       "pulocationid            False\n",
       "dolocationid            False\n",
       "trip_miles              False\n",
       "trip_time               False\n",
       "base_passenger_fare     False\n",
       "tolls                   False\n",
       "bcf                     False\n",
       "sales_tax               False\n",
       "congestion_surcharge    False\n",
       "airport_fee              True\n",
       "tips                    False\n",
       "driver_pay              False\n",
       "shared_request_flag     False\n",
       "shared_match_flag       False\n",
       "access_a_ride_flag      False\n",
       "wav_request_flag        False\n",
       "wav_match_flag          False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Columns with Null values\n",
    "fhv_df.isna().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "fhv_df[\"airport_fee\"] = fhv_df[\"airport_fee\"].replace(np.nan, 0.00)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pick up and drop off locations\n",
    "pick_up_borough = fhv_df[\"pulocationid\"].map(taxi_zones_df[\"borough\"])\n",
    "pick_up_zone = fhv_df[\"pulocationid\"].map(taxi_zones_df[\"zone\"])\n",
    "drop_off_borough = fhv_df[\"dolocationid\"].map(taxi_zones_df[\"borough\"])\n",
    "drop_off_zone = fhv_df[\"dolocationid\"].map(taxi_zones_df[\"zone\"])\n",
    "\n",
    "# Put into the dataframe\n",
    "fhv_df.insert(loc=7, column=\"pick_up_borough\", value=pick_up_borough)\n",
    "fhv_df.insert(loc=8, column=\"pick_up_zone\", value=pick_up_zone)\n",
    "fhv_df.insert(loc=9, column=\"drop_off_borough\", value=drop_off_borough)\n",
    "fhv_df.insert(loc=10, column=\"drop_off_zone\", value=drop_off_zone)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "fhv_df.drop(columns=[\"pulocationid\", \"dolocationid\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fix a couple of column names\n",
    "fhv_df.rename(columns={\"hvfhs_license_num\": \"business\", \"bcf\": \"black_car_fund\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>business</th>\n",
       "      <th>request_datetime</th>\n",
       "      <th>on_scene_datetime</th>\n",
       "      <th>pickup_datetime</th>\n",
       "      <th>dropoff_datetime</th>\n",
       "      <th>pick_up_borough</th>\n",
       "      <th>pick_up_zone</th>\n",
       "      <th>drop_off_borough</th>\n",
       "      <th>drop_off_zone</th>\n",
       "      <th>trip_miles</th>\n",
       "      <th>...</th>\n",
       "      <th>sales_tax</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "      <th>tips</th>\n",
       "      <th>driver_pay</th>\n",
       "      <th>shared_request_flag</th>\n",
       "      <th>shared_match_flag</th>\n",
       "      <th>access_a_ride_flag</th>\n",
       "      <th>wav_request_flag</th>\n",
       "      <th>wav_match_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Uber</td>\n",
       "      <td>2020-01-21 18:46:40</td>\n",
       "      <td>2020-01-21 18:47:21</td>\n",
       "      <td>2020-01-21 18:47:57</td>\n",
       "      <td>2020-01-21 18:58:42</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Two Bridges/Seward Park</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>Williamsburg (North Side)</td>\n",
       "      <td>2.5400</td>\n",
       "      <td>...</td>\n",
       "      <td>1.1800</td>\n",
       "      <td>2.7500</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>3.0000</td>\n",
       "      <td>8.1100</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Uber</td>\n",
       "      <td>2020-01-17 19:27:42</td>\n",
       "      <td>2020-01-17 19:28:38</td>\n",
       "      <td>2020-01-17 19:29:55</td>\n",
       "      <td>2020-01-17 19:45:27</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>East Village</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Union Sq</td>\n",
       "      <td>1.6600</td>\n",
       "      <td>...</td>\n",
       "      <td>1.1500</td>\n",
       "      <td>2.7500</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>9.5000</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td></td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lyft</td>\n",
       "      <td>2020-01-18 11:57:57</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-01-18 12:00:50</td>\n",
       "      <td>2020-01-18 12:08:26</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Midtown East</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lenox Hill West</td>\n",
       "      <td>1.3940</td>\n",
       "      <td>...</td>\n",
       "      <td>0.6400</td>\n",
       "      <td>2.7500</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>5.3900</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Lyft</td>\n",
       "      <td>2020-01-13 19:27:35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-01-13 19:31:23</td>\n",
       "      <td>2020-01-13 19:42:23</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Central Harlem North</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>West Concourse</td>\n",
       "      <td>1.6470</td>\n",
       "      <td>...</td>\n",
       "      <td>0.6100</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>7.2400</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Lyft</td>\n",
       "      <td>2020-01-24 03:56:08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-01-24 03:59:28</td>\n",
       "      <td>2020-01-24 04:11:37</td>\n",
       "      <td>Queens</td>\n",
       "      <td>LaGuardia Airport</td>\n",
       "      <td>Queens</td>\n",
       "      <td>Elmhurst</td>\n",
       "      <td>3.8590</td>\n",
       "      <td>...</td>\n",
       "      <td>0.9100</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>10.2400</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  business     request_datetime    on_scene_datetime      pickup_datetime  \\\n",
       "0     Uber  2020-01-21 18:46:40  2020-01-21 18:47:21  2020-01-21 18:47:57   \n",
       "1     Uber  2020-01-17 19:27:42  2020-01-17 19:28:38  2020-01-17 19:29:55   \n",
       "2     Lyft  2020-01-18 11:57:57                  NaN  2020-01-18 12:00:50   \n",
       "3     Lyft  2020-01-13 19:27:35                  NaN  2020-01-13 19:31:23   \n",
       "4     Lyft  2020-01-24 03:56:08                  NaN  2020-01-24 03:59:28   \n",
       "\n",
       "      dropoff_datetime pick_up_borough             pick_up_zone  \\\n",
       "0  2020-01-21 18:58:42       Manhattan  Two Bridges/Seward Park   \n",
       "1  2020-01-17 19:45:27       Manhattan             East Village   \n",
       "2  2020-01-18 12:08:26       Manhattan             Midtown East   \n",
       "3  2020-01-13 19:42:23       Manhattan     Central Harlem North   \n",
       "4  2020-01-24 04:11:37          Queens        LaGuardia Airport   \n",
       "\n",
       "  drop_off_borough              drop_off_zone  trip_miles  ...  sales_tax  \\\n",
       "0         Brooklyn  Williamsburg (North Side)      2.5400  ...     1.1800   \n",
       "1        Manhattan                   Union Sq      1.6600  ...     1.1500   \n",
       "2        Manhattan            Lenox Hill West      1.3940  ...     0.6400   \n",
       "3            Bronx             West Concourse      1.6470  ...     0.6100   \n",
       "4           Queens                   Elmhurst      3.8590  ...     0.9100   \n",
       "\n",
       "   congestion_surcharge  airport_fee   tips  driver_pay  shared_request_flag  \\\n",
       "0                2.7500       0.0000 3.0000      8.1100                    N   \n",
       "1                2.7500       0.0000 1.0000      9.5000                    N   \n",
       "2                2.7500       0.0000 0.0000      5.3900                    N   \n",
       "3                0.0000       0.0000 1.0000      7.2400                    N   \n",
       "4                0.0000       0.0000 1.0000     10.2400                    N   \n",
       "\n",
       "   shared_match_flag  access_a_ride_flag  wav_request_flag wav_match_flag  \n",
       "0                  N                                     N              N  \n",
       "1                  N                                     N              N  \n",
       "2                  N                   N                 N              N  \n",
       "3                  N                   N                 N              N  \n",
       "4                  N                   N                 N              N  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fhv_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 14429357 entries, 0 to 14429356\n",
      "Data columns (total 24 columns):\n",
      " #   Column                Dtype  \n",
      "---  ------                -----  \n",
      " 0   business              object \n",
      " 1   request_datetime      object \n",
      " 2   on_scene_datetime     object \n",
      " 3   pickup_datetime       object \n",
      " 4   dropoff_datetime      object \n",
      " 5   pick_up_borough       object \n",
      " 6   pick_up_zone          object \n",
      " 7   drop_off_borough      object \n",
      " 8   drop_off_zone         object \n",
      " 9   trip_miles            float64\n",
      " 10  trip_time             int64  \n",
      " 11  base_passenger_fare   float64\n",
      " 12  tolls                 float64\n",
      " 13  black_car_fund        float64\n",
      " 14  sales_tax             float64\n",
      " 15  congestion_surcharge  float64\n",
      " 16  airport_fee           float64\n",
      " 17  tips                  float64\n",
      " 18  driver_pay            float64\n",
      " 19  shared_request_flag   object \n",
      " 20  shared_match_flag     object \n",
      " 21  access_a_ride_flag    object \n",
      " 22  wav_request_flag      object \n",
      " 23  wav_match_flag        object \n",
      "dtypes: float64(9), int64(1), object(14)\n",
      "memory usage: 2.6+ GB\n"
     ]
    }
   ],
   "source": [
    "fhv_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "fhv_df.to_csv(\"data/FHVHV_Trip_Data_Clean.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Yellow Taxi Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace column names\n",
    "cols_replace = {\n",
    "    \"tpep_pickup_datetime\": \"pickup_datetime\", \"tpep_dropoff_datetime\": \"dropoff_datetime\",\n",
    "    \"trip_distance\": \"trip_miles\", \"tolls_amount\":  \"tolls\", \"tip_amount\": \"tips\", \"ratecodeid\": \"trip_category\"\n",
    "    }\n",
    "yellow_df.rename(columns=cols_replace, inplace=True)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "vendorid                 False\n",
       "pickup_datetime          False\n",
       "dropoff_datetime         False\n",
       "passenger_count           True\n",
       "trip_miles               False\n",
       "trip_category             True\n",
       "store_and_fwd_flag        True\n",
       "pulocationid             False\n",
       "dolocationid             False\n",
       "payment_type             False\n",
       "fare_amount              False\n",
       "extra                    False\n",
       "mta_tax                  False\n",
       "tips                     False\n",
       "tolls                    False\n",
       "improvement_surcharge    False\n",
       "total_amount             False\n",
       "congestion_surcharge      True\n",
       "airport_fee               True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Columns with Null values\n",
    "yellow_df.isna().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "yellow_df[\"congestion_surcharge\"] = yellow_df[\"congestion_surcharge\"].replace(np.nan, 0.00)\n",
    "yellow_df[\"airport_fee\"] = yellow_df[\"airport_fee\"].replace(np.nan, 0.00)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "trip_category\n",
       "1.0000     12494638\n",
       "2.0000       391082\n",
       "5.0000        81291\n",
       "99.0000       53138\n",
       "3.0000        32362\n",
       "4.0000        20871\n",
       "6.0000           73\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yellow_df[\"trip_category\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "store_and_fwd_flag\n",
       "N    12944407\n",
       "Y      129048\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yellow_df[\"store_and_fwd_flag\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "passenger_count\n",
       "1.0000    9802523\n",
       "2.0000    1897912\n",
       "3.0000     481916\n",
       "0.0000     249524\n",
       "5.0000     244913\n",
       "4.0000     231891\n",
       "6.0000     164651\n",
       "7.0000         53\n",
       "8.0000         53\n",
       "9.0000         19\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yellow_df[\"passenger_count\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace the values with category\n",
    "trip_category = yellow_df[\"trip_category\"].replace(to_replace=[1, 2, 3, 4, 5, 6, 99], value=[\"Standard Rate\", \"JFK\", \"Newark\", \"Nassau or Westchester\", \"Negotiated Fare\", \"Group Ride\", \"Unknown\"])\n",
    "yellow_df[\"trip_category\"] = trip_category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pick up and drop off locations\n",
    "pick_up_borough = yellow_df[\"pulocationid\"].map(taxi_zones_df[\"borough\"])\n",
    "pick_up_zone = yellow_df[\"pulocationid\"].map(taxi_zones_df[\"zone\"])\n",
    "drop_off_borough = yellow_df[\"dolocationid\"].map(taxi_zones_df[\"borough\"])\n",
    "drop_off_zone = yellow_df[\"dolocationid\"].map(taxi_zones_df[\"zone\"])\n",
    "\n",
    "# Put into the dataframe\n",
    "yellow_df.insert(loc=7, column=\"pick_up_borough\", value=pick_up_borough)\n",
    "yellow_df.insert(loc=8, column=\"pick_up_zone\", value=pick_up_zone)\n",
    "yellow_df.insert(loc=9, column=\"drop_off_borough\", value=drop_off_borough)\n",
    "yellow_df.insert(loc=10, column=\"drop_off_zone\", value=drop_off_zone)\n",
    "\n",
    "# Drop the columns\n",
    "yellow_df.drop(columns=[\"pulocationid\", \"dolocationid\", \"vendorid\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "yellow_df[\"payment_type\"] = yellow_df[\"payment_type\"].replace(to_replace=[1, 2, 3, 4, 5, 6], value=[\"Credit Card\", \"Cash\", \"No Charge\", \"Dispute\", \"Unknown\", \"Voided Trip\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 13836755 entries, 0 to 13836754\n",
      "Data columns (total 20 columns):\n",
      " #   Column                 Dtype  \n",
      "---  ------                 -----  \n",
      " 0   pickup_datetime        object \n",
      " 1   dropoff_datetime       object \n",
      " 2   passenger_count        float64\n",
      " 3   trip_miles             float64\n",
      " 4   trip_category          object \n",
      " 5   store_and_fwd_flag     object \n",
      " 6   pick_up_borough        object \n",
      " 7   pick_up_zone           object \n",
      " 8   drop_off_borough       object \n",
      " 9   drop_off_zone          object \n",
      " 10  payment_type           object \n",
      " 11  fare_amount            float64\n",
      " 12  extra                  float64\n",
      " 13  mta_tax                float64\n",
      " 14  tips                   float64\n",
      " 15  tolls                  float64\n",
      " 16  improvement_surcharge  float64\n",
      " 17  total_amount           float64\n",
      " 18  congestion_surcharge   float64\n",
      " 19  airport_fee            float64\n",
      "dtypes: float64(11), object(9)\n",
      "memory usage: 2.2+ GB\n"
     ]
    }
   ],
   "source": [
    "yellow_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.int64(0)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yellow_df.duplicated().sum()"
   ]
  },
  {
   "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>pickup_datetime</th>\n",
       "      <th>dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_miles</th>\n",
       "      <th>trip_category</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>pick_up_borough</th>\n",
       "      <th>pick_up_zone</th>\n",
       "      <th>drop_off_borough</th>\n",
       "      <th>drop_off_zone</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tips</th>\n",
       "      <th>tolls</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-12 03:00:14</td>\n",
       "      <td>2020-01-12 03:10:18</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>3.4000</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>TriBeCa/Civic Center</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>East Village</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>12.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>3.1600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>18.9600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-15 18:39:38</td>\n",
       "      <td>2020-01-15 18:50:17</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>2.0100</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Meatpacking/West Village West</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Clinton East</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>9.5000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>2.7600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>16.5600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-30 07:34:31</td>\n",
       "      <td>2020-01-30 07:48:24</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>1.8800</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lenox Hill West</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lincoln Square East</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>10.5000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>2.7600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>16.5600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-20 17:16:38</td>\n",
       "      <td>2020-01-20 17:23:34</td>\n",
       "      <td>2.0000</td>\n",
       "      <td>1.5400</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Clinton East</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lincoln Square East</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>7.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>2.2600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>13.5600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-14 11:48:39</td>\n",
       "      <td>2020-01-14 12:03:40</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>2.4000</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Midtown Center</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Yorkville West</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>11.5000</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>2.9600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>17.7600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       pickup_datetime     dropoff_datetime  passenger_count  trip_miles  \\\n",
       "0  2020-01-12 03:00:14  2020-01-12 03:10:18           1.0000      3.4000   \n",
       "1  2020-01-15 18:39:38  2020-01-15 18:50:17           1.0000      2.0100   \n",
       "2  2020-01-30 07:34:31  2020-01-30 07:48:24           1.0000      1.8800   \n",
       "3  2020-01-20 17:16:38  2020-01-20 17:23:34           2.0000      1.5400   \n",
       "4  2020-01-14 11:48:39  2020-01-14 12:03:40           1.0000      2.4000   \n",
       "\n",
       "   trip_category store_and_fwd_flag pick_up_borough  \\\n",
       "0  Standard Rate                  N       Manhattan   \n",
       "1  Standard Rate                  N       Manhattan   \n",
       "2  Standard Rate                  N       Manhattan   \n",
       "3  Standard Rate                  N       Manhattan   \n",
       "4  Standard Rate                  N       Manhattan   \n",
       "\n",
       "                    pick_up_zone drop_off_borough        drop_off_zone  \\\n",
       "0           TriBeCa/Civic Center        Manhattan         East Village   \n",
       "1  Meatpacking/West Village West        Manhattan         Clinton East   \n",
       "2                Lenox Hill West        Manhattan  Lincoln Square East   \n",
       "3                   Clinton East        Manhattan  Lincoln Square East   \n",
       "4                 Midtown Center        Manhattan       Yorkville West   \n",
       "\n",
       "  payment_type  fare_amount  extra  mta_tax   tips  tolls  \\\n",
       "0  Credit Card      12.0000 0.5000   0.5000 3.1600 0.0000   \n",
       "1  Credit Card       9.5000 1.0000   0.5000 2.7600 0.0000   \n",
       "2  Credit Card      10.5000 0.0000   0.5000 2.7600 0.0000   \n",
       "3  Credit Card       7.0000 1.0000   0.5000 2.2600 0.0000   \n",
       "4  Credit Card      11.5000 2.5000   0.5000 2.9600 0.0000   \n",
       "\n",
       "   improvement_surcharge  total_amount  congestion_surcharge  airport_fee  \n",
       "0                 0.3000       18.9600                2.5000       0.0000  \n",
       "1                 0.3000       16.5600                2.5000       0.0000  \n",
       "2                 0.3000       16.5600                2.5000       0.0000  \n",
       "3                 0.3000       13.5600                2.5000       0.0000  \n",
       "4                 0.3000       17.7600                2.5000       0.0000  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yellow_df.head()"
   ]
  },
  {
   "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>passenger_count</th>\n",
       "      <th>trip_miles</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tips</th>\n",
       "      <th>tolls</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>13073455.0000</td>\n",
       "      <td>13836755.0000</td>\n",
       "      <td>13836755.0000</td>\n",
       "      <td>13836755.0000</td>\n",
       "      <td>13836755.0000</td>\n",
       "      <td>13836755.0000</td>\n",
       "      <td>13836755.0000</td>\n",
       "      <td>13836755.0000</td>\n",
       "      <td>13836755.0000</td>\n",
       "      <td>13836755.0000</td>\n",
       "      <td>13836755.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.3910</td>\n",
       "      <td>5.2884</td>\n",
       "      <td>6.1314</td>\n",
       "      <td>1.1944</td>\n",
       "      <td>0.4882</td>\n",
       "      <td>12.3619</td>\n",
       "      <td>0.4654</td>\n",
       "      <td>0.5566</td>\n",
       "      <td>22.8663</td>\n",
       "      <td>2.1306</td>\n",
       "      <td>0.0810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.9662</td>\n",
       "      <td>534.2129</td>\n",
       "      <td>35860.2324</td>\n",
       "      <td>1.5148</td>\n",
       "      <td>0.0993</td>\n",
       "      <td>35860.0243</td>\n",
       "      <td>1.9561</td>\n",
       "      <td>0.3584</td>\n",
       "      <td>117.2293</td>\n",
       "      <td>0.9295</td>\n",
       "      <td>0.3495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.0000</td>\n",
       "      <td>-29.0600</td>\n",
       "      <td>-133391414.0000</td>\n",
       "      <td>-39.1700</td>\n",
       "      <td>-0.5000</td>\n",
       "      <td>-333.3200</td>\n",
       "      <td>-114.2000</td>\n",
       "      <td>-1.0000</td>\n",
       "      <td>-1000.0000</td>\n",
       "      <td>-2.5000</td>\n",
       "      <td>-1.7500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1.0000</td>\n",
       "      <td>1.0500</td>\n",
       "      <td>7.2000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>12.7400</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.0000</td>\n",
       "      <td>1.8000</td>\n",
       "      <td>11.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>2.1500</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>17.1600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.0000</td>\n",
       "      <td>3.3500</td>\n",
       "      <td>17.7000</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>3.5000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>25.0000</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9.0000</td>\n",
       "      <td>351613.3600</td>\n",
       "      <td>429496.7200</td>\n",
       "      <td>90.0600</td>\n",
       "      <td>42.1700</td>\n",
       "      <td>133391363.5300</td>\n",
       "      <td>700.1700</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>429562.2500</td>\n",
       "      <td>3.0000</td>\n",
       "      <td>1.7500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       passenger_count    trip_miles     fare_amount         extra  \\\n",
       "count    13073455.0000 13836755.0000   13836755.0000 13836755.0000   \n",
       "mean            1.3910        5.2884          6.1314        1.1944   \n",
       "std             0.9662      534.2129      35860.2324        1.5148   \n",
       "min             0.0000      -29.0600 -133391414.0000      -39.1700   \n",
       "25%             1.0000        1.0500          7.2000        0.0000   \n",
       "50%             1.0000        1.8000         11.0000        0.5000   \n",
       "75%             1.0000        3.3500         17.7000        2.5000   \n",
       "max             9.0000   351613.3600     429496.7200       90.0600   \n",
       "\n",
       "            mta_tax           tips         tolls  improvement_surcharge  \\\n",
       "count 13836755.0000  13836755.0000 13836755.0000          13836755.0000   \n",
       "mean         0.4882        12.3619        0.4654                 0.5566   \n",
       "std          0.0993     35860.0243        1.9561                 0.3584   \n",
       "min         -0.5000      -333.3200     -114.2000                -1.0000   \n",
       "25%          0.5000         0.0000        0.0000                 0.3000   \n",
       "50%          0.5000         2.1500        0.0000                 0.3000   \n",
       "75%          0.5000         3.5000        0.0000                 1.0000   \n",
       "max         42.1700 133391363.5300      700.1700                 1.0000   \n",
       "\n",
       "       total_amount  congestion_surcharge   airport_fee  \n",
       "count 13836755.0000         13836755.0000 13836755.0000  \n",
       "mean        22.8663                2.1306        0.0810  \n",
       "std        117.2293                0.9295        0.3495  \n",
       "min      -1000.0000               -2.5000       -1.7500  \n",
       "25%         12.7400                2.5000        0.0000  \n",
       "50%         17.1600                2.5000        0.0000  \n",
       "75%         25.0000                2.5000        0.0000  \n",
       "max     429562.2500                3.0000        1.7500  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "yellow_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "yellow_df.to_csv(\"data/Yellow_Trip_Data_Clean.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>pickup_datetime</th>\n",
       "      <th>dropoff_datetime</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>trip_miles</th>\n",
       "      <th>trip_category</th>\n",
       "      <th>store_and_fwd_flag</th>\n",
       "      <th>pick_up_borough</th>\n",
       "      <th>pick_up_zone</th>\n",
       "      <th>drop_off_borough</th>\n",
       "      <th>drop_off_zone</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>extra</th>\n",
       "      <th>mta_tax</th>\n",
       "      <th>tips</th>\n",
       "      <th>tolls</th>\n",
       "      <th>improvement_surcharge</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>congestion_surcharge</th>\n",
       "      <th>airport_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-12 03:00:14</td>\n",
       "      <td>2020-01-12 03:10:18</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>3.4000</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>TriBeCa/Civic Center</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>East Village</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>12.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>3.1600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>18.9600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-15 18:39:38</td>\n",
       "      <td>2020-01-15 18:50:17</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>2.0100</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Meatpacking/West Village West</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Clinton East</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>9.5000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>2.7600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>16.5600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-30 07:34:31</td>\n",
       "      <td>2020-01-30 07:48:24</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>1.8800</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lenox Hill West</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lincoln Square East</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>10.5000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>2.7600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>16.5600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-20 17:16:38</td>\n",
       "      <td>2020-01-20 17:23:34</td>\n",
       "      <td>2.0000</td>\n",
       "      <td>1.5400</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Clinton East</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lincoln Square East</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>7.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>2.2600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>13.5600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-14 11:48:39</td>\n",
       "      <td>2020-01-14 12:03:40</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>2.4000</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Midtown Center</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Yorkville West</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>11.5000</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>2.9600</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.3000</td>\n",
       "      <td>17.7600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13836750</th>\n",
       "      <td>2024-10-28 13:30:42</td>\n",
       "      <td>2024-10-28 13:52:28</td>\n",
       "      <td>2.0000</td>\n",
       "      <td>5.3800</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>West Village</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Manhattan Valley</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>26.8000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>7.7000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>38.5000</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13836751</th>\n",
       "      <td>2024-10-30 22:20:28</td>\n",
       "      <td>2024-10-30 22:37:53</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>3.5800</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>West Village</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lenox Hill West</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>19.1000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>6.0300</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>30.1300</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13836752</th>\n",
       "      <td>2024-10-05 20:49:33</td>\n",
       "      <td>2024-10-05 21:09:48</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>10.4900</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lincoln Square East</td>\n",
       "      <td>Bronx</td>\n",
       "      <td>Riverdale/North Riverdale/Fieldston</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>42.2000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>10.0800</td>\n",
       "      <td>3.1800</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>60.4600</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13836753</th>\n",
       "      <td>2024-10-30 09:27:50</td>\n",
       "      <td>2024-10-30 09:38:25</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>1.1000</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Yorkville West</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Lenox Hill East</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>10.0000</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>2.8000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>16.8000</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13836754</th>\n",
       "      <td>2024-10-17 21:24:33</td>\n",
       "      <td>2024-10-17 21:43:55</td>\n",
       "      <td>2.0000</td>\n",
       "      <td>8.8500</td>\n",
       "      <td>Standard Rate</td>\n",
       "      <td>N</td>\n",
       "      <td>Queens</td>\n",
       "      <td>LaGuardia Airport</td>\n",
       "      <td>Manhattan</td>\n",
       "      <td>Midtown Center</td>\n",
       "      <td>Credit Card</td>\n",
       "      <td>35.9000</td>\n",
       "      <td>6.0000</td>\n",
       "      <td>0.5000</td>\n",
       "      <td>13.2100</td>\n",
       "      <td>6.9400</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>67.8000</td>\n",
       "      <td>2.5000</td>\n",
       "      <td>1.7500</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13836755 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              pickup_datetime     dropoff_datetime  passenger_count  \\\n",
       "0         2020-01-12 03:00:14  2020-01-12 03:10:18           1.0000   \n",
       "1         2020-01-15 18:39:38  2020-01-15 18:50:17           1.0000   \n",
       "2         2020-01-30 07:34:31  2020-01-30 07:48:24           1.0000   \n",
       "3         2020-01-20 17:16:38  2020-01-20 17:23:34           2.0000   \n",
       "4         2020-01-14 11:48:39  2020-01-14 12:03:40           1.0000   \n",
       "...                       ...                  ...              ...   \n",
       "13836750  2024-10-28 13:30:42  2024-10-28 13:52:28           2.0000   \n",
       "13836751  2024-10-30 22:20:28  2024-10-30 22:37:53           1.0000   \n",
       "13836752  2024-10-05 20:49:33  2024-10-05 21:09:48           1.0000   \n",
       "13836753  2024-10-30 09:27:50  2024-10-30 09:38:25           1.0000   \n",
       "13836754  2024-10-17 21:24:33  2024-10-17 21:43:55           2.0000   \n",
       "\n",
       "          trip_miles  trip_category store_and_fwd_flag pick_up_borough  \\\n",
       "0             3.4000  Standard Rate                  N       Manhattan   \n",
       "1             2.0100  Standard Rate                  N       Manhattan   \n",
       "2             1.8800  Standard Rate                  N       Manhattan   \n",
       "3             1.5400  Standard Rate                  N       Manhattan   \n",
       "4             2.4000  Standard Rate                  N       Manhattan   \n",
       "...              ...            ...                ...             ...   \n",
       "13836750      5.3800  Standard Rate                  N       Manhattan   \n",
       "13836751      3.5800  Standard Rate                  N       Manhattan   \n",
       "13836752     10.4900  Standard Rate                  N       Manhattan   \n",
       "13836753      1.1000  Standard Rate                  N       Manhattan   \n",
       "13836754      8.8500  Standard Rate                  N          Queens   \n",
       "\n",
       "                           pick_up_zone drop_off_borough  \\\n",
       "0                  TriBeCa/Civic Center        Manhattan   \n",
       "1         Meatpacking/West Village West        Manhattan   \n",
       "2                       Lenox Hill West        Manhattan   \n",
       "3                          Clinton East        Manhattan   \n",
       "4                        Midtown Center        Manhattan   \n",
       "...                                 ...              ...   \n",
       "13836750                   West Village        Manhattan   \n",
       "13836751                   West Village        Manhattan   \n",
       "13836752            Lincoln Square East            Bronx   \n",
       "13836753                 Yorkville West        Manhattan   \n",
       "13836754              LaGuardia Airport        Manhattan   \n",
       "\n",
       "                                drop_off_zone payment_type  fare_amount  \\\n",
       "0                                East Village  Credit Card      12.0000   \n",
       "1                                Clinton East  Credit Card       9.5000   \n",
       "2                         Lincoln Square East  Credit Card      10.5000   \n",
       "3                         Lincoln Square East  Credit Card       7.0000   \n",
       "4                              Yorkville West  Credit Card      11.5000   \n",
       "...                                       ...          ...          ...   \n",
       "13836750                     Manhattan Valley  Credit Card      26.8000   \n",
       "13836751                      Lenox Hill West  Credit Card      19.1000   \n",
       "13836752  Riverdale/North Riverdale/Fieldston  Credit Card      42.2000   \n",
       "13836753                      Lenox Hill East  Credit Card      10.0000   \n",
       "13836754                       Midtown Center  Credit Card      35.9000   \n",
       "\n",
       "          extra  mta_tax    tips  tolls  improvement_surcharge  total_amount  \\\n",
       "0        0.5000   0.5000  3.1600 0.0000                 0.3000       18.9600   \n",
       "1        1.0000   0.5000  2.7600 0.0000                 0.3000       16.5600   \n",
       "2        0.0000   0.5000  2.7600 0.0000                 0.3000       16.5600   \n",
       "3        1.0000   0.5000  2.2600 0.0000                 0.3000       13.5600   \n",
       "4        2.5000   0.5000  2.9600 0.0000                 0.3000       17.7600   \n",
       "...         ...      ...     ...    ...                    ...           ...   \n",
       "13836750 0.0000   0.5000  7.7000 0.0000                 1.0000       38.5000   \n",
       "13836751 1.0000   0.5000  6.0300 0.0000                 1.0000       30.1300   \n",
       "13836752 1.0000   0.5000 10.0800 3.1800                 1.0000       60.4600   \n",
       "13836753 2.5000   0.5000  2.8000 0.0000                 1.0000       16.8000   \n",
       "13836754 6.0000   0.5000 13.2100 6.9400                 1.0000       67.8000   \n",
       "\n",
       "          congestion_surcharge  airport_fee  \n",
       "0                       2.5000       0.0000  \n",
       "1                       2.5000       0.0000  \n",
       "2                       2.5000       0.0000  \n",
       "3                       2.5000       0.0000  \n",
       "4                       2.5000       0.0000  \n",
       "...                        ...          ...  \n",
       "13836750                2.5000       0.0000  \n",
       "13836751                2.5000       0.0000  \n",
       "13836752                2.5000       0.0000  \n",
       "13836753                2.5000       0.0000  \n",
       "13836754                2.5000       1.7500  \n",
       "\n",
       "[13836755 rows x 20 columns]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(\"data/Yellow_Trip_Data_Clean.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Green Taxi Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace column names\n",
    "cols_replace = {\n",
    "    \"lpep_pickup_datetime\": \"pickup_datetime\", \"lpep_dropoff_datetime\": \"dropoff_datetime\",\n",
    "    \"trip_distance\": \"trip_miles\", \"tolls_amount\":  \"tolls\", \"tip_amount\": \"tips\", \"ratecodeid\": \"trip_category\"\n",
    "    }\n",
    "green_df.rename(columns=cols_replace, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "vendorid                 False\n",
       "pickup_datetime          False\n",
       "dropoff_datetime         False\n",
       "store_and_fwd_flag        True\n",
       "trip_category             True\n",
       "pulocationid             False\n",
       "dolocationid             False\n",
       "passenger_count           True\n",
       "trip_miles               False\n",
       "fare_amount              False\n",
       "extra                    False\n",
       "mta_tax                  False\n",
       "tips                     False\n",
       "tolls                    False\n",
       "ehail_fee                 True\n",
       "improvement_surcharge    False\n",
       "total_amount             False\n",
       "payment_type              True\n",
       "trip_type                 True\n",
       "congestion_surcharge      True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "green_df.isna().any()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "green_df[\"congestion_surcharge\"] = green_df[\"congestion_surcharge\"].replace(np.nan, 0.00)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "trip_category\n",
       "1.0000     1716374\n",
       "5.0000       75579\n",
       "2.0000        5103\n",
       "4.0000        1856\n",
       "3.0000        1095\n",
       "99.0000         96\n",
       "6.0000          19\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "green_df[\"trip_category\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "payment_type\n",
       "1.0000    1132550\n",
       "2.0000     651926\n",
       "3.0000      12129\n",
       "4.0000       3469\n",
       "5.0000         48\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "green_df[\"payment_type\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace the values with category\n",
    "trip_category = green_df[\"trip_category\"].replace(to_replace=[1, 2, 3, 4, 5, 6, 99], value=[\"Standard Rate\", \"JFK\", \"Newark\", \"Nassau or Westchester\", \"Negotiated Fare\", \"Group Ride\", \"Unknown\"])\n",
    "green_df[\"trip_category\"] = trip_category\n",
    "\n",
    "payment_type = green_df[\"payment_type\"].replace(to_replace=[1, 2, 3, 4, 5, 6], value=[\"Credit Card\", \"Cash\", \"No Charge\", \"Dispute\", \"Unknown\", \"Voided Trip\"])\n",
    "green_df[\"payment_type\"] = payment_type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pick up and drop off locations\n",
    "pick_up_borough = green_df[\"pulocationid\"].map(taxi_zones_df[\"borough\"])\n",
    "pick_up_zone = green_df[\"pulocationid\"].map(taxi_zones_df[\"zone\"])\n",
    "drop_off_borough = green_df[\"dolocationid\"].map(taxi_zones_df[\"borough\"])\n",
    "drop_off_zone = green_df[\"dolocationid\"].map(taxi_zones_df[\"zone\"])\n",
    "\n",
    "# Put into the dataframe\n",
    "green_df.insert(loc=5, column=\"pick_up_borough\", value=pick_up_borough)\n",
    "green_df.insert(loc=6, column=\"pick_up_zone\", value=pick_up_zone)\n",
    "green_df.insert(loc=7, column=\"drop_off_borough\", value=drop_off_borough)\n",
    "green_df.insert(loc=8, column=\"drop_off_zone\", value=drop_off_zone)\n",
    "\n",
    "# Drop the columns\n",
    "green_df.drop(columns=[\"pulocationid\", \"dolocationid\", \"vendorid\"], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "green_df[\"payment_type\"] = green_df[\"payment_type\"].replace(to_replace=[1, 2, 3, 4, 5, 6], value=[\"Credit Card\", \"Cash\", \"No Charge\", \"Dispute\", \"Unknown\", \"Voided Trip\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.int64(0)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "green_df.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "green_df.to_csv(\"data/Green_Trip_Data_Clean.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "nyc_tlc_trip_data-hX5l9hKQ",
   "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.13.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}