notebooks/nyc_taxi_years_correlation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using cuSpatial to Correlate Taxi Data after a Format Change\n",
    "In 2017, the NYC Taxi data switched from giving their pickup and drop off locations in `lat/lon` to one of 262 `LocationID`s.  While these `LocationID`s made it easier to determine some regions and borough information that was lacking in the previous datasets, it made it difficult to compare datasets before and after this transition.  \n",
    "\n",
    "\n",
    "\n",
    "By using cuSpatial `Points in Polygon` (PIP), we can quickly and easily map the latitude and longitude of the pre-2017 taxi dataset to the `LocationID`s of the 2017+ dataset.  In this notebook, we will show you how to do so.  cuSpatial 0.14 PIP only works on 31 polygons per call, so we will show how to process this larger 263 polygon shapefile with minimal memory impact.  cuSpatial 0.15 will eliminate the 31 polygon limitation and provide substantial additional speedup.\n",
    "\n",
    "You may need a 16GB card or larger."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import cuspatial\n",
    "import geopandas as gpd\n",
    "import cudf\n",
    "from numba import cuda\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Download the Data\n",
    "We're going to download the January NYC Taxi datasets for 2016 and 2017.  We also need the NYC Taxi Zones "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "tzones_lonlat.json found\n",
      "taxi2016.csv found\n",
      "  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n",
      "                                 Dload  Upload   Total   Spent    Left  Speed\n",
      "100  128M  100  128M    0     0  53.5M      0  0:00:02  0:00:02 --:--:-- 53.4M\n"
     ]
    }
   ],
   "source": [
    "!if [ ! -f \"tzones_lonlat.json\" ]; then curl \"https://data.cityofnewyork.us/api/geospatial/d3c5-ddgc?method=export&format=GeoJSON\" -o tzones_lonlat.json; else echo \"tzones_lonlat.json found\"; fi\n",
    "!if [ ! -f \"taxi2016.csv\" ]; then curl https://storage.googleapis.com/anaconda-public-data/nyc-taxi/csv/2016/yellow_tripdata_2016-01.csv -o taxi2016.csv; else echo \"taxi2016.csv found\"; fi   \n",
    "!if [ ! -f \"taxi2017.csv\" ]; then curl https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2017-01.parquet -o taxi2017.parquet; else echo \"taxi2017.csv found\"; fi"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read in the Taxi Data with cuDF\n",
    "Let's read in the pickups and dropoffs for 2016 and 2017."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi2016 = cudf.read_csv(\"taxi2016.csv\")\n",
    "taxi2017 = cudf.read_parquet(\"taxi2017.parquet\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's have a look at the columns in `taxi2016` and `taxi2017` to verify the difference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'DOLocationID', 'PULocationID', 'airport_fee', 'congestion_surcharge'}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(taxi2017.columns).difference(set(taxi2016.columns))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read in the Spatial Data with cuSpatial\n",
    "\n",
    "cuSpatial loads polygons into a `cudf.Series` of _Feature offsets_, a `cudf.Series` of _ring offsets_, and a `cudf.DataFrame` of `x` and `y` coordinates (which can be used for lon/lat as in this case) with the `read_polygon_shapefile` function. We're working on more advanced I/O integrations and nested `Columns` this year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "tzones = gpd.GeoDataFrame.from_file('tzones_lonlat.json')\n",
    "taxi_zones = cuspatial.from_geopandas(tzones).geometry\n",
    "taxi_zone_rings = cuspatial.GeoSeries.from_polygons_xy(\n",
    "    taxi_zones.polygons.xy, taxi_zones.polygons.ring_offset,\n",
    "    taxi_zones.polygons.part_offset,\n",
    "    cudf.Series(range(len(taxi_zones.polygons.part_offset)))\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Converting lon/lat coordinates to LocationIDs with cuSpatial\n",
    "Looking at the taxi zones and the taxi2016 data, you can see that\n",
    "- 10.09 million pickup locations\n",
    "- 10.09 million dropoff locations\n",
    "- 263 LocationID features\n",
    "- 354 LocationID rings\n",
    "- 98,192 LocationID coordinates\n",
    "\n",
    "Now that we've collected the set of pickup locations and dropoff locations, we can use `cuspatial.point_in_polygon` to quickly determine which pickups and dropoffs occur in each borough.\n",
    "\n",
    "To do this in a memory efficient way, instead of creating two massive 10.09 million x 263 arrays, we're going to use the 31 polygon limit to our advantage and map the resulting true values in the array a new `PULocationID` and `DOLocationID`, matching the 2017 schema.  Locations outside of the `LocationID` areas are `264` and `265`.  We'll be using 264 to indicate our out-of-bounds zones."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0, 31, 62, 93, 124, 155, 186, 217, 248, 263]\n"
     ]
    }
   ],
   "source": [
    "pip_iterations = list(np.arange(0, 263, 31))\n",
    "pip_iterations.append(263)\n",
    "print(pip_iterations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def make_geoseries_from_lonlat(lon, lat):\n",
    "    lonlat = cudf.DataFrame({\"lon\": lon, \"lat\": lat}).interleave_columns()\n",
    "    return cuspatial.GeoSeries.from_points_xy(lonlat)\n",
    "\n",
    "pickup2016 = make_geoseries_from_lonlat(taxi2016['pickup_longitude'] , taxi2016['pickup_latitude'])\n",
    "dropoff2016 = make_geoseries_from_lonlat(taxi2016['dropoff_longitude'] , taxi2016['dropoff_latitude'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 9.85 s, sys: 6.31 s, total: 16.2 s\n",
      "Wall time: 16 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "taxi2016['PULocationID'] = 264\n",
    "taxi2016['DOLocationID'] = 264\n",
    "for i in range(len(pip_iterations)-1):\n",
    "    start = pip_iterations[i]\n",
    "    end = pip_iterations[i+1]\n",
    "    \n",
    "    zone = taxi_zone_rings[start:end]\n",
    "    pickups = cuspatial.point_in_polygon(pickup2016, zone)\n",
    "    dropoffs = cuspatial.point_in_polygon(dropoff2016, zone)\n",
    "\n",
    "    for j in pickups.columns:\n",
    "        taxi2016['PULocationID'].loc[pickups[j]] = j\n",
    "    for j in dropoffs.columns:\n",
    "        taxi2016['DOLocationID'].loc[dropoffs[j]] = j"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "del pickups\n",
    "del dropoffs"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I wonder how many taxi rides in 2016 started and ended in the same location."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.09205741858888485\n"
     ]
    }
   ],
   "source": [
    "print(taxi2016['DOLocationID'].corr(taxi2016['PULocationID']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Not nearly as many as I thought. How many exactly?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.19 %\n"
     ]
    }
   ],
   "source": [
    "print(format((taxi2016['DOLocationID'] == taxi2016['PULocationID']).sum()/taxi2016.shape[0], '.2f'), '%')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Something with perhaps a higher correlation: It seems likely that pickups and dropoffs by zone are not likely to change much from year to year, especially within a given month. Let's see how similar the pickup and dropoff patterns are in January 2016 and 2017:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.07718639422194801\n",
      "0.06962159908563305\n"
     ]
    }
   ],
   "source": [
    "print(taxi2016['DOLocationID'].value_counts().corr(taxi2017['DOLocationID'].value_counts()))\n",
    "print(taxi2016['PULocationID'].value_counts().corr(taxi2017['PULocationID'].value_counts()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Bringing Them All Together\n",
    "If you wanted to include this as part of a larger clean up of Taxi data, you'd then concatenate this dataframe into a `dask_cudf` dataframe and delete its `cuDF` version, or convert it into arrow memory format and process it similar to how we did in the mortgage notebook.  For now, as we are only working on a couple of GBs, we'll concatenate in cuDF."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A little Data checking and cleaning before merging..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VendorID                          int64\n",
       "tpep_pickup_datetime     datetime64[us]\n",
       "tpep_dropoff_datetime    datetime64[us]\n",
       "passenger_count                   int64\n",
       "trip_distance                   float64\n",
       "RatecodeID                        int64\n",
       "store_and_fwd_flag               object\n",
       "PULocationID                      int64\n",
       "DOLocationID                      int64\n",
       "payment_type                      int64\n",
       "fare_amount                     float64\n",
       "extra                           float64\n",
       "mta_tax                         float64\n",
       "tip_amount                      float64\n",
       "tolls_amount                    float64\n",
       "improvement_surcharge           float64\n",
       "total_amount                    float64\n",
       "congestion_surcharge             object\n",
       "airport_fee                      object\n",
       "dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxi2017.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "VendorID                   int64\n",
       "tpep_pickup_datetime      object\n",
       "tpep_dropoff_datetime     object\n",
       "passenger_count            int64\n",
       "trip_distance            float64\n",
       "pickup_longitude         float64\n",
       "pickup_latitude          float64\n",
       "RatecodeID                 int64\n",
       "store_and_fwd_flag        object\n",
       "dropoff_longitude        float64\n",
       "dropoff_latitude         float64\n",
       "payment_type               int64\n",
       "fare_amount              float64\n",
       "extra                    float64\n",
       "mta_tax                  float64\n",
       "tip_amount               float64\n",
       "tolls_amount             float64\n",
       "improvement_surcharge    float64\n",
       "total_amount             float64\n",
       "PULocationID               int64\n",
       "DOLocationID               int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "taxi2016.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Seems that we have type differences in `tpep_pickup_datetime` and `tpep_dropoff_datetime` to fix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi2016[\"tpep_pickup_datetime\"]=taxi2016[\"tpep_pickup_datetime\"].astype(\"datetime64[us]\")\n",
    "taxi2016[\"tpep_dropoff_datetime\"]=taxi2016[\"tpep_dropoff_datetime\"].astype(\"datetime64[us]\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now we can safely concat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = cudf.concat([taxi2017, taxi2016])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Final Check\n",
    "Now to test to see if both years are present as expected."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         VendorID tpep_pickup_datetime tpep_dropoff_datetime  passenger_count  \\\n",
      "4487880         1  2017-01-15 17:33:37   2017-01-15 17:35:32                1   \n",
      "\n",
      "         trip_distance  RatecodeID store_and_fwd_flag  PULocationID  \\\n",
      "4487880            0.0           5                  N           204   \n",
      "\n",
      "         DOLocationID  payment_type  ...  tip_amount  tolls_amount  \\\n",
      "4487880           204             1  ...        10.0           0.0   \n",
      "\n",
      "         improvement_surcharge  total_amount  congestion_surcharge  \\\n",
      "4487880                    0.3        103.22                  <NA>   \n",
      "\n",
      "        airport_fee pickup_longitude pickup_latitude dropoff_longitude  \\\n",
      "4487880        <NA>             <NA>            <NA>              <NA>   \n",
      "\n",
      "        dropoff_latitude  \n",
      "4487880             <NA>  \n",
      "\n",
      "[1 rows x 23 columns]\n"
     ]
    }
   ],
   "source": [
    "print(df.query('PULocationID == 204'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see, 2017 values lack longitude and latitude. It is trivial and fast using our existing `DataFrame` to compute the mean of each `PULocationID` and `DOLocationID`s. We could inject those into the missing values to easily see how well the new LocationIDs map to pickup and dropoff locations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Back To Your Workflow\n",
    "So now you've seen how to use cuSpatial to clean and correlate your spatial data using the NYC taxi data. You can now perform multi year analytics across the entire range of taxi datasets using your favorite RAPIDS libraries,"
   ]
  }
 ],
 "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.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}