Exploratory Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e0311dd2-df03-410c-beb5-2f685bd0abcd",
   "metadata": {},
   "source": [
    "# Explore Dataset\n",
    "\n",
    "We download and play with the NYC taxi dataset, learning a bit about the data before we train models with it.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ff78b3e-2c5d-4363-a281-ee6e1ff5f24e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import dask.dataframe as dd\n",
    "from s3fs import S3FileSystem\n",
    "import numpy as np\n",
    "import coiled\n",
    "from distributed import Client\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6289c85c-0089-42f8-bf1b-7d8ee385d935",
   "metadata": {},
   "source": [
    "## Download\n",
    "\n",
    "Download the \"Taxi Zone Lookup Table (CSV) from [here](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page), and save it to `data/taxi+_zone_lookup.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13a8172c-8508-4128-8976-74c4414fe215",
   "metadata": {},
   "outputs": [],
   "source": [
    "GET_SUBSET = False\n",
    "\n",
    "if GET_SUBSET:\n",
    "    cluster = coiled.Cluster(\n",
    "        worker_vm_types=[\"m6i.2xlarge\"],\n",
    "        scheduler_vm_types=[\"m6i.2xlarge\"],\n",
    "        package_sync=True,  # copy local packages,\n",
    "        shutdown_on_close=True,  # reuse cluster across runs\n",
    "        show_widget=False,\n",
    "        n_workers=20,\n",
    "        use_best_zone=True,\n",
    "        backend_options={\n",
    "            \"region\": \"us-east-2\",\n",
    "            \"spot\": True,\n",
    "            \"spot_on_demand_fallback\": True,\n",
    "        },\n",
    "        scheduler_options={\"idle_timeout\": \"10 minutes\"},\n",
    "    )\n",
    "\n",
    "    client = Client(cluster)\n",
    "\n",
    "    def subset_data():\n",
    "        print(\"loading data\")\n",
    "        to_exclude = [\"string\", \"category\", \"object\"]\n",
    "        ddf = dd.read_parquet(\n",
    "            \"s3://coiled-datasets/prefect-dask/nyc-uber-lyft/processed_data.parquet\"\n",
    "        )\n",
    "        ddf = ddf.assign(accessible_vehicle=1)\n",
    "        print(\"Make accessible feature\")\n",
    "        ddf.accessible_vehicle = ddf.accessible_vehicle.where(\n",
    "            ddf.on_scene_datetime.isnull(), 0\n",
    "        )  # Only applies if the vehicle is wheelchair accessible\n",
    "        ddf = ddf.assign(pickup_month=ddf.pickup_datetime.dt.month)\n",
    "        ddf = ddf.assign(pickup_dow=ddf.pickup_datetime.dt.dayofweek)\n",
    "        ddf = ddf.assign(pickup_hour=ddf.pickup_datetime.dt.hour)\n",
    "\n",
    "        ddf = ddf.drop(\n",
    "            columns=[\n",
    "                \"on_scene_datetime\",\n",
    "                \"request_datetime\",\n",
    "                \"pickup_datetime\",\n",
    "                \"dispatching_base_num\",\n",
    "                \"originating_base_num\",\n",
    "                \"shared_request_flag\",\n",
    "                \"shared_match_flag\",\n",
    "                \"dropoff_datetime\",\n",
    "            ]\n",
    "        )\n",
    "\n",
    "        ddf = ddf.dropna(how=\"any\")\n",
    "        ddf = ddf.repartition(partition_size=\"100MB\")\n",
    "        ddf = ddf.reset_index(drop=True)\n",
    "\n",
    "        categories = [\"pickup_month\", \"pickup_dow\", \"pickup_hour\", \"hvfhs_license_num\"]\n",
    "        for cat in categories:\n",
    "            ddf[cat] = ddf[cat].astype(\"category\")\n",
    "        ddf = ddf.categorize(columns=categories)\n",
    "\n",
    "        df = ddf.sample(frac=0.0025).compute()\n",
    "        df.to_parquet(\"data/rides.parquet\")\n",
    "\n",
    "    subset_data()\n",
    "    client.shutdown()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a140d9d-50cb-4a04-8d6e-22554e70c947",
   "metadata": {},
   "source": [
    "## Here we start the EDA portion of our pipeline with Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "683bf991-af34-41a4-93d7-d5b5e711a51c",
   "metadata": {},
   "source": [
    "### Let's grab some information about taxi zones / boroughs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c2687e2-5d55-4647-9234-979939f29ee2",
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_df = pd.read_csv(\"data/taxi+_zone_lookup.csv\", usecols=[\"LocationID\", \"Borough\"])\n",
    "taxi_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c6d27ea-095c-4375-8be8-748fd2a40ab2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# These are the unique Boroughs in the taxi zone lookup table\n",
    "taxi_df.Borough.unique().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9ebbde5-67a2-4702-be88-cde48c35ad47",
   "metadata": {},
   "source": [
    "The taxi_df includes two `LocationID` values that correspond to `Unknown` Boroughs.  \n",
    "We need to know if these exist in the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b3c34a9-773c-4132-9dfe-cd5e57761f29",
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_df.loc[taxi_df[\"Borough\"] == \"Unknown\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95795d0c-70e8-4666-8465-a8bc2babf753",
   "metadata": {},
   "source": [
    "## And take a look at the distribution of travel times and the presence of `Unknown` boroughs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66fc316e-e87c-4ab7-895a-44e31a8c8c27",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_parquet(\"data/rides.parquet\").reset_index(drop=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7d6ce917-a029-4125-94ce-a2a53c1894d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df.index) // 1e6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4388155d-f1ad-46cc-8d73-da146f2092c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.memory_usage(deep=True).sum() / 2**20"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5a03fc5-6762-41dc-ba4e-3c4ed7839f5c",
   "metadata": {},
   "source": [
    "We can see from below that over 9000 entries exist where travel occurs in or out of a borough that \n",
    "is an `Unknown` zone."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "577572e6-4c59-42af-bdfa-5a3faa71f372",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df.PULocationID.isin([263, 264])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9495249-94fa-4f9e-925e-db5f999a9a4a",
   "metadata": {},
   "source": [
    "## We can see from the below that, as expected, our travel times are not normally distributed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78164ffb-c24e-489d-be0a-831e052c52a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"trip_time\"].hist(bins=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e119e09-3270-4779-ab70-29e3373c51de",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"trip_time\"].min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38b4848f-5924-4a17-909f-d6dde48591b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"trip_time\"].max()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b66bd770-6c32-42b4-a518-e725c4956239",
   "metadata": {},
   "source": [
    "We need to filter outliers from our data.  For this exercise, we will use `1.5 * Interquartile Range`\n",
    "We can find the bounds using `df.quartile(method=\"median_unbiased\")`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3d6d5fa-48d3-410f-b437-5bce3c16c37a",
   "metadata": {},
   "outputs": [],
   "source": [
    "Q1 = df[\"trip_time\"].quantile(0.25, interpolation=\"median_unbiased\")\n",
    "Q3 = df[\"trip_time\"].quantile(0.75, interpolation=\"median_unbiased\")\n",
    "print(f\"Q1:  {Q1}, Q3:  {Q3}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "682d18ac-88a6-438b-93b2-58f6da9eb5be",
   "metadata": {},
   "source": [
    "As a check -- when porting code from Pandas to Dask, it was discovered that the Dask quantiles method does not\n",
    "exactly replicate the Pandas / Numpy method.  See [here](https://docs.dask.org/en/stable/generated/dask.dataframe.DataFrame.quantile.html) and \n",
    "[here](https://github.com/dask/dask/issues/6566) for details.  Let's compare the results of the Dask and Pandas implementations "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2834f69-655f-49f7-8f51-e37a524f26ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\n",
    "    f\"Using the `dask` method:  {dd.from_pandas(df['trip_time'], npartitions=8).quantile(0.75).compute()}\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3d357b58-3150-4a0a-b23b-4bade2ebe6f8",
   "metadata": {},
   "source": [
    "Let's plot the upper and lower bounds, then filter the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e559d34-7e83-4902-ae3a-3d1251e43cc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "lower_bound = Q1 - (1.5 * (Q3 - Q1))\n",
    "lower_bound = lower_bound if lower_bound > 0 else 0\n",
    "\n",
    "upper_bound = Q3 + (1.5 * (Q3 - Q1))\n",
    "print(f\"Lower bound is:  {lower_bound}\")\n",
    "print(f\"Upper bound is:  {upper_bound}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "061326d5-ff1c-4676-b8a8-da4d3ed07d3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"trip_time\"].hist(bins=100)\n",
    "plt.axvline(lower_bound, color=\"r\")\n",
    "plt.axvline(upper_bound, color=\"r\")\n",
    "plt.title(\"Histogram of Trip Durations\")\n",
    "plt.xlabel(\"Trip Duration\")\n",
    "plt.savefig(\"data/trip_histogram.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "09d4dce8-6d50-4422-9c78-bb2e9bcbd664",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\n",
    "    f\"Fraction of data lost after filtering outliers:  {len(df.loc[df.trip_time > upper_bound].index) / len(df.index)}\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85bf0bcc-ab0a-4e88-b938-92cc93a101ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "size_raw_data = len(df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "388c7f9a-a615-420d-8748-f307bf86c32b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.loc[(df.trip_time >= lower_bound) & (df.trip_time <= upper_bound)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b81c31dd-8b7f-4413-8b63-bbffc46b8e21",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\n",
    "    f\"Fraction of data remaining after removing outliers:  {len(df.index) / size_raw_data}\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b532df35-6c72-4c69-b3c0-0b2b9e56fbfc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c2043f44-6027-4120-84dd-d624c841553f",
   "metadata": {},
   "source": [
    "## What are the mean trip times by DoW like?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8224e8ad-f6a7-4fcd-ab14-8fe22908212d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.groupby(\"pickup_dow\")[\"trip_time\"].agg([\"mean\", \"median\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6ac90a06-14f9-4015-a7f4-ae148bff9411",
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted = pd.pivot_table(\n",
    "    df, values=\"trip_time\", index=\"pickup_dow\", columns=\"pickup_hour\"\n",
    ")\n",
    "pivoted"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e87f0e3-f5ad-48b8-8350-a235e01dc160",
   "metadata": {
    "tags": []
   },
   "source": [
    "#### What does the data look like?  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f19cd35-a14c-43e4-a0e5-c18a519c9071",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\n",
    "    f\"Shorted and longest average trip times based on pickup hour and day of week are:  {pivoted.min().min()} and {pivoted.max().max()} respectively\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "366d7b44-1ceb-4204-b5c7-bd63c742d316",
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.heatmap(pivoted)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b8f5577-83fc-4b47-91e6-2e1f81c533b7",
   "metadata": {},
   "source": [
    "### Let's build two tables of mean travel times, based on pickup_dow and pickup_hour to create continuous features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a95bbf8-4abc-459b-99e7-543b7ccd36d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "pickup_hour_means = (\n",
    "    df.groupby(\"pickup_hour\")[\"trip_time\"].mean().to_frame().reset_index()\n",
    ")\n",
    "pickup_hour_means = pickup_hour_means.rename(\n",
    "    columns={\"trip_time\": \"mean_trip_time_by_pickup_hour\"}\n",
    ")\n",
    "pickup_hour_means"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3558b1ac-1dbc-46b4-b218-3e0b79992c3b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pickup_dow_means = df.groupby(\"pickup_dow\")[\"trip_time\"].mean().to_frame().reset_index()\n",
    "pickup_dow_means = pickup_dow_means.rename(\n",
    "    columns={\"trip_time\": \"mean_trip_time_by_pickup_dow\"}\n",
    ")\n",
    "pickup_dow_means"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40ab80dd-9e85-4ea0-bd0c-41028c8afa69",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(len(df.index))\n",
    "print(len(df.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7fadddfe-7378-4d2a-b0da-f1955c2b5869",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(df, pickup_hour_means, left_on=\"pickup_hour\", right_on=\"pickup_hour\")\n",
    "df = pd.merge(df, pickup_dow_means, left_on=\"pickup_dow\", right_on=\"pickup_dow\")\n",
    "print(len(df.index))\n",
    "print(len(df.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3483e111-bd91-4fb5-b143-51caf9f91846",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3635068e-a1bc-44fe-9a89-2284a0768d81",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Taking inspiration from [this paper on using geospatial characteristics to predict trip durations](https://amr4i.github.io/pdfs/nyc_taxi_times.pdf),   \n",
    "\n",
    "We can assign each pickup and dropoff location to a `Superborough`, as defined by the following table.  To do this, we will  \n",
    "add the PU and DO borough to each trip, then assign a Superborough.  We do this to determine if a trip occurs between \n",
    "Superboroughs\n",
    "\n",
    "| Superborough    | Boroughs                 |\n",
    "|-----------------| -------------------------|\n",
    "| Superborough 1  | Manhattan, Bronx, & EWR  |\n",
    "| Superborough 2  | Brooklyn & Queens        |\n",
    "| Superborough 3  | Staten Island            |\n",
    "| Unknown         | Unknown"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8b885f6-ea66-4f8f-9ff4-58855dc0b3e7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(df, taxi_df, left_on=\"PULocationID\", right_on=\"LocationID\", how=\"inner\")\n",
    "df = df.rename(columns={\"Borough\": \"PUBorough\"})\n",
    "df = df.drop(columns=\"LocationID\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b024329-9c84-4910-bc48-7f4ed1efa5ee",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.merge(df, taxi_df, left_on=\"DOLocationID\", right_on=\"LocationID\", how=\"inner\")\n",
    "df = df.rename(columns={\"Borough\": \"DOBorough\"})\n",
    "df = df.drop(columns=\"LocationID\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "923a52db-cdc0-4509-bd58-7a21697dfbec",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa441ad3-d882-4416-8ed8-47e50a9da855",
   "metadata": {},
   "outputs": [],
   "source": [
    "borough_mapping = {\n",
    "    \"Manhattan\": \"Superborough 1\",\n",
    "    \"Bronx\": \"Superborough 1\",\n",
    "    \"EWR\": \"Superborough 1\",\n",
    "    \"Brooklyn\": \"Superborough 2\",\n",
    "    \"Queens\": \"Superborough 2\",\n",
    "    \"Staten Island\": \"Superborough 3\",\n",
    "    \"Unknown\": \"Unknown\",\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e0c6f86-eb36-49b6-9d06-dda17de665fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "PUSuperborough = [borough_mapping.get(x) for x in df.PUBorough.tolist()]\n",
    "DOSuperborough = [borough_mapping.get(x) for x in df.DOBorough.tolist()]\n",
    "cross_superborough = [\n",
    "    \"N\" if i == j else \"Y\" for (i, j) in zip(PUSuperborough, DOSuperborough)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d1ef70f-3de7-43b8-9953-6da0a8568283",
   "metadata": {},
   "outputs": [],
   "source": [
    "PUSuperborough_DOSuperborough_Pair = [\n",
    "    f\"{i}-{j}\" for i, j in zip(PUSuperborough, DOSuperborough)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c706225-aaa2-47f6-832b-9b314317ccc1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.assign(CrossSuperborough=cross_superborough)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30453bf6-6ff8-4767-aa88-1bd1be527fb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.assign(PUSuperborough_DOSuperborough=PUSuperborough_DOSuperborough_Pair)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fd32496e-007c-47b0-a4ce-fb5eb1fade8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45ffe9ab-e611-4e5c-8b10-312fe9da1b02",
   "metadata": {},
   "outputs": [],
   "source": [
    "c = df.columns.tolist()\n",
    "c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58fdf339-cf6b-4764-a469-d2ec3e10b94b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"congestion_surcharge\"].hist(bins=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "88d9f390-df5a-400b-bd7a-81ca1d0a55d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"airport_fee\"].hist(bins=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4cc169a2-a199-4c4f-afc5-a02c40aa16ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"tolls\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8963f314-b265-47f4-87e9-9376e32a9bc5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"congestion_surcharge\"].hist(bins=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "126810a0-c5fe-4776-ad82-502cdbefd426",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"CrossSuperborough\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "211ae252-90db-4cc7-ad39-15667e940687",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6169997-225f-4546-9916-be161e40580f",
   "metadata": {},
   "source": [
    "## Final Cleanup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65726e7f-e253-48db-8404-240ad7b86aad",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"airport_fee\"] = df[\"airport_fee\"].replace(\"None\", 0)\n",
    "df[\"airport_fee\"] = df[\"airport_fee\"].replace(\"nan\", 0)\n",
    "df[\"airport_fee\"] = df[\"airport_fee\"].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c16b7d8e-dd2b-49d3-903a-9a4ea42bc0c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"airport_fee\"] = df[\"airport_fee\"].fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2173f3e-f6bd-4271-baa8-cf33b85547a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.hvfhs_license_num.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24b266d1-03e3-4843-965b-bbd32ecf36a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.wav_request_flag.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b9118a2-5d7c-4e9b-8b45-e38a2f4a5290",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.wav_match_flag.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac35f6af-d3a3-40eb-b6f0-a1b641fecd84",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.CrossSuperborough.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d86a2d0-4a60-449f-bd83-f43104088518",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.access_a_ride_flag.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dce7c9d5-1b74-4bdc-849d-5541a9b2daac",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.PUBorough.value_counts().plot(kind=\"bar\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f111c3c6-3cd5-42a2-867d-0d28217d4e8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.DOBorough.value_counts().plot(kind=\"bar\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0451112c-b946-4c94-a5c9-15052ec08a02",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5093131a-6038-4a3a-b88f-bffe9d55aebe",
   "metadata": {},
   "outputs": [],
   "source": [
    "to_drop = [\n",
    "    \"base_passenger_fare\",\n",
    "    \"bcf\",\n",
    "    \"sales_tax\",\n",
    "    \"tips\",\n",
    "    \"driver_pay\",\n",
    "    \"access_a_ride_flag\",\n",
    "    \"wav_match_flag\",\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "92961f4e-5168-4059-a597-af0294e09332",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.drop(columns=to_drop)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f9532cd-3ea5-4ab6-8f62-5b3c35036157",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27155bda-d7cd-48e9-9531-df806d95263e",
   "metadata": {},
   "outputs": [],
   "source": [
    "categories = [\n",
    "    \"hvfhs_license_num\",\n",
    "    \"PULocationID\",\n",
    "    \"DOLocationID\",\n",
    "    \"wav_request_flag\",\n",
    "    \"accessible_vehicle\",\n",
    "    \"pickup_month\",\n",
    "    \"pickup_dow\",\n",
    "    \"pickup_hour\",\n",
    "    \"PUBorough\",\n",
    "    \"DOBorough\",\n",
    "    \"CrossSuperborough\",\n",
    "    \"PUSuperborough_DOSuperborough\",\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b879b99-6815-4fc3-9634-d0c9fabb0c46",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2[categories] = df2[categories].astype(\"category\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a5c11ed-4785-494a-a684-7cb93405b97d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2[\"PUSuperborough_DOSuperborough\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "206bc5c4-8579-412f-ab76-a70c5343173c",
   "metadata": {},
   "source": [
    "## Train a Scikit Learn Dummy Regressor and evaluate model performance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb6ec9bb-9785-44be-8b02-1bb35544a340",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.dummy import DummyRegressor\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.metrics import mean_squared_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21c5884e-6136-4131-b42f-9e3c3ec6881e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da2f65f7-6b4d-4be8-9be6-0eb515dc788e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_valid = df2.sample(frac=0.1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b313778-9864-472e-872e-a925ce6d7bb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42315d97-9f22-4eb7-9a46-585356bb6573",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.columns.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "304942d9-595d-41a1-9b2e-ce967addede5",
   "metadata": {},
   "outputs": [],
   "source": [
    "y = df2[\"trip_time\"]\n",
    "X = df2.drop(columns=\"trip_time\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4c83a3d9-aba5-45d7-9856-c7c5a0c13861",
   "metadata": {},
   "outputs": [],
   "source": [
    "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5656aaf4-279b-464d-9c06-30189b66b61d",
   "metadata": {},
   "outputs": [],
   "source": [
    "mdl = DummyRegressor(strategy=\"mean\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e9e213b-6799-434b-a231-f68cb2cb6fe2",
   "metadata": {},
   "outputs": [],
   "source": [
    "mdl.fit(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fe7cb63-834c-4c5a-a719-f00d8cb814a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "y_predict = mdl.predict(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48cf0306-a94a-40c8-ba2a-4f4824781f77",
   "metadata": {},
   "outputs": [],
   "source": [
    "mse = mean_squared_error(y_test, y_predict, squared=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55de522b-d9d4-462d-8dba-6b50bf49af09",
   "metadata": {},
   "outputs": [],
   "source": [
    "mse"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d7831cc-840f-4189-b578-e2dcc5d85e3d",
   "metadata": {},
   "source": [
    "## Linear Regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37e812bc-20bc-4aa2-b321-15062d82bc66",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.preprocessing import OneHotEncoder, StandardScaler\n",
    "from sklearn.pipeline import Pipeline\n",
    "from sklearn.compose import make_column_selector as selector\n",
    "from sklearn.compose import ColumnTransformer\n",
    "from sklearn.linear_model import LinearRegression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8fd50c0-a022-48a5-bd4e-77d38676c290",
   "metadata": {},
   "outputs": [],
   "source": [
    "categorical_transformer = OneHotEncoder(handle_unknown=\"ignore\")\n",
    "numeric_transformer = StandardScaler()\n",
    "\n",
    "preprocessor = ColumnTransformer(\n",
    "    transformers=[\n",
    "        (\"num\", numeric_transformer, selector(dtype_exclude=\"category\")),\n",
    "        (\"cat\", categorical_transformer, selector(dtype_include=\"category\")),\n",
    "    ]\n",
    ")\n",
    "mdl = Pipeline(\n",
    "    steps=[\n",
    "        (\"preprocessor\", preprocessor),\n",
    "        (\"regressor\", LinearRegression()),\n",
    "    ]\n",
    ")\n",
    "mdl.fit(X_train, y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad60285f-b21e-4a61-83d1-325f183d6517",
   "metadata": {},
   "outputs": [],
   "source": [
    "mse = mean_squared_error(y_test, mdl.predict(X_test), squared=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d10b2c8-80a7-4ce2-b36a-0f4093b12087",
   "metadata": {},
   "outputs": [],
   "source": [
    "mse"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63e31e82-27ba-4f43-8619-46740a48be9f",
   "metadata": {},
   "source": [
    "## XGBoost"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d680760-6975-4aef-9112-b2ed5aeeaf70",
   "metadata": {},
   "outputs": [],
   "source": [
    "import xgboost as xgb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98cf4e87-aaa8-4591-9399-311782812656",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "mdl2 = xgb.XGBRegressor(enable_categorical=True, tree_method=\"hist\")\n",
    "mdl2.fit(\n",
    "    X_train,\n",
    "    y_train,\n",
    "    eval_set=[(X_train, y_train), (X_test, y_test)],\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f636a90d-8420-43fd-8a79-45d5c7146d35",
   "metadata": {},
   "outputs": [],
   "source": [
    "mse = mean_squared_error(y_test, mdl2.predict(X_test), squared=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44a33cb6-40f8-4ac0-9fb3-01082a326cf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "mse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b837a62-e141-4ebc-9108-462e6e1b6375",
   "metadata": {},
   "outputs": [],
   "source": [
    "mdl2.get_xgb_params()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "779117ad-33c1-47a6-9149-cc4ab6595ec6",
   "metadata": {},
   "outputs": [],
   "source": [
    "mdl2.feature_importances_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e1d16b0-92a9-4a56-8f6f-7337dbb16aef",
   "metadata": {},
   "outputs": [],
   "source": [
    "mdl2.feature_names_in_"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "56768686-4c07-47c4-919c-901eee587d91",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.bar(x=mdl2.feature_names_in_, height=mdl2.feature_importances_)\n",
    "plt.xticks(rotation=90)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58f9e9c6-1edb-402e-956e-bdc0c7997f63",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}