intermediate_notebooks/examples/blazingsql/taxi_fare_prediction.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "l4fOFMjbRvkZ"
   },
   "source": [
    "# BlazingSQL + cuML NYC Taxi Cab Fare Prediction\n",
    "\n",
    "This demo uses pubically availible [NYC Taxi Cab Data](https://www.kaggle.com/c/new-york-city-taxi-fare-prediction) to predict the total fare of a taxi ride in New York City given the pickup and dropoff locations. \n",
    "\n",
    "In this notebook, we will cover: \n",
    "- How to read and query csv files with cuDF and BlazingSQL.\n",
    "- How to implement a linear regression model with cuML."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### BlazingSQL install check\n",
    "The next cell checks that you have BlazingSQL installed, and offers to install it if not (making sure the notebook will run as expected)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"You've got BlazingSQL set up perfectly! Let's get started with SQL in RAPIDS AI!\""
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sys \n",
    "# point import path notebooks-contrib/utils\n",
    "sys.path.append('../../../utils/')\n",
    "from sql_check import bsql_start\n",
    "# check that BlazingSQL is installed\n",
    "bsql_start()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "RnUyVHwHmKyk"
   },
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import cudf\n",
    "from cuml import LinearRegression\n",
    "from blazingsql import BlazingContext"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create BlazingContext\n",
    "You can think of the BlazingContext much like a Spark Context (i.e. where information such as FileSystems you have registered and Tables you have created will be stored). If you have issues running this cell, restart runtime and try running it again."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BlazingContext ready\n"
     ]
    }
   ],
   "source": [
    "bc = BlazingContext()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Gt0TPBqif50q"
   },
   "source": [
    "### Download Data\n",
    "For this demo we will train our model with 20,000,000 rows of data from 4 CSV files (5,000,000 rows each). \n",
    "\n",
    "The cell below will download them from AWS to the main `notebooks-contrib/data/blazingsql/` folder for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import urllib.request\n",
    "\n",
    "# relative path to data folder\n",
    "data_dir = '../../../data/blazingsql/'\n",
    "# does folder exist?\n",
    "if not os.path.exists(data_dir):\n",
    "    print('creating blazingsql directory')\n",
    "    # create folder\n",
    "    os.system('mkdir ../../data/blazingsql')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Downloading https://blazingsql-colab.s3.amazonaws.com/taxi_data/taxi_00.csv to ../../../data/blazingsql/taxi_00.csv\n",
      "Downloading https://blazingsql-colab.s3.amazonaws.com/taxi_data/taxi_01.csv to ../../../data/blazingsql/taxi_01.csv\n",
      "Downloading https://blazingsql-colab.s3.amazonaws.com/taxi_data/taxi_02.csv to ../../../data/blazingsql/taxi_02.csv\n",
      "Downloading https://blazingsql-colab.s3.amazonaws.com/taxi_data/taxi_03.csv to ../../../data/blazingsql/taxi_03.csv\n"
     ]
    }
   ],
   "source": [
    "# download taxi data\n",
    "base_url = 'https://blazingsql-colab.s3.amazonaws.com/taxi_data/'\n",
    "# thanks to Taurean Dyer\n",
    "years = list(range(0, 3))\n",
    "for i in range(0, 4):\n",
    "    fn = 'taxi_0' + str(i) + '.csv'\n",
    "    if not os.path.isfile(data_dir + fn):\n",
    "        print(f'Downloading {base_url + fn} to {data_dir + fn}')\n",
    "        urllib.request.urlretrieve(base_url + fn, data_dir + fn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "PXtydYrimQGt"
   },
   "source": [
    "## Extract, transform, load\n",
    "In order to train our Linear Regression model, we must first preform ETL so to prepare our data.\n",
    "\n",
    "### ETL: Read and Join CSVs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "Gr7CUSrsEBmW"
   },
   "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>key</th>\n",
       "      <th>fare_amount</th>\n",
       "      <th>pickup_longitude</th>\n",
       "      <th>pickup_latitude</th>\n",
       "      <th>dropoff_longitude</th>\n",
       "      <th>dropoff_latitude</th>\n",
       "      <th>passenger_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2012-02-02 22:30:19.002</td>\n",
       "      <td>8.9</td>\n",
       "      <td>-73.988708</td>\n",
       "      <td>40.758804</td>\n",
       "      <td>-73.986519</td>\n",
       "      <td>40.737202</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2014-09-20 07:19:24.001</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-73.990204</td>\n",
       "      <td>40.746708</td>\n",
       "      <td>-73.994728</td>\n",
       "      <td>40.750515</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-02-23 07:18:05.001</td>\n",
       "      <td>5.5</td>\n",
       "      <td>-74.016762</td>\n",
       "      <td>40.709438</td>\n",
       "      <td>-74.009003</td>\n",
       "      <td>40.719498</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015-04-18 23:49:27.009</td>\n",
       "      <td>13.5</td>\n",
       "      <td>-74.002708</td>\n",
       "      <td>40.733730</td>\n",
       "      <td>-73.986099</td>\n",
       "      <td>40.734776</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010-03-04 08:15:59.001</td>\n",
       "      <td>10.5</td>\n",
       "      <td>-73.988365</td>\n",
       "      <td>40.737663</td>\n",
       "      <td>-74.012459</td>\n",
       "      <td>40.713932</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      key  fare_amount  pickup_longitude  pickup_latitude  \\\n",
       "0 2012-02-02 22:30:19.002          8.9        -73.988708        40.758804   \n",
       "1 2014-09-20 07:19:24.001          4.0        -73.990204        40.746708   \n",
       "2 2013-02-23 07:18:05.001          5.5        -74.016762        40.709438   \n",
       "3 2015-04-18 23:49:27.009         13.5        -74.002708        40.733730   \n",
       "4 2010-03-04 08:15:59.001         10.5        -73.988365        40.737663   \n",
       "\n",
       "   dropoff_longitude  dropoff_latitude  passenger_count  \n",
       "0         -73.986519         40.737202              1.0  \n",
       "1         -73.994728         40.750515              1.0  \n",
       "2         -74.009003         40.719498              3.0  \n",
       "3         -73.986099         40.734776              1.0  \n",
       "4         -74.012459         40.713932              1.0  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# set column names and types\n",
    "col_names = ['key', 'fare_amount', 'pickup_longitude', 'pickup_latitude', \n",
    "                'dropoff_longitude', 'dropoff_latitude', 'passenger_count']\n",
    "col_types = ['date64', 'float32', 'float32', 'float32', \n",
    "                'float32', 'float32', 'float32']\n",
    "\n",
    "# load first csv \n",
    "gdf_00 = cudf.read_csv(data_dir+'taxi_00.csv', names=col_names, dtype=col_types)\n",
    "# load second csv\n",
    "gdf_01 = cudf.read_csv(data_dir+'taxi_01.csv', names=col_names, dtype=col_types)\n",
    "# load third csv\n",
    "gdf_02 = cudf.read_csv(data_dir+'taxi_02.csv', names=col_names, dtype=col_types)\n",
    "# load fourth csv\n",
    "gdf_03 = cudf.read_csv(data_dir+'taxi_03.csv', names=col_names, dtype=col_types)\n",
    "\n",
    "# combine all those dataframes into one master dataframe\n",
    "gdf = cudf.concat([gdf_00,gdf_01, gdf_02, gdf_03])\n",
    "\n",
    "# what's it look like?\n",
    "gdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>fare_amount</th>\n",
       "      <th>pickup_longitude</th>\n",
       "      <th>pickup_latitude</th>\n",
       "      <th>dropoff_longitude</th>\n",
       "      <th>dropoff_latitude</th>\n",
       "      <th>passenger_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2.000000e+07</td>\n",
       "      <td>2.000000e+07</td>\n",
       "      <td>2.000000e+07</td>\n",
       "      <td>1.999987e+07</td>\n",
       "      <td>1.999987e+07</td>\n",
       "      <td>2.000000e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.134995e+01</td>\n",
       "      <td>-7.250857e+01</td>\n",
       "      <td>3.992013e+01</td>\n",
       "      <td>-7.251212e+01</td>\n",
       "      <td>3.992170e+01</td>\n",
       "      <td>1.685199e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.878444e+01</td>\n",
       "      <td>1.273661e+01</td>\n",
       "      <td>9.756748e+00</td>\n",
       "      <td>1.273016e+01</td>\n",
       "      <td>9.628169e+00</td>\n",
       "      <td>1.334611e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-1.760000e+02</td>\n",
       "      <td>-3.442060e+03</td>\n",
       "      <td>-3.475482e+03</td>\n",
       "      <td>-3.440697e+03</td>\n",
       "      <td>-3.475482e+03</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>-7.399206e+01</td>\n",
       "      <td>4.073494e+01</td>\n",
       "      <td>-7.399139e+01</td>\n",
       "      <td>4.073404e+01</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>8.500000e+00</td>\n",
       "      <td>-7.398180e+01</td>\n",
       "      <td>4.075267e+01</td>\n",
       "      <td>-7.398015e+01</td>\n",
       "      <td>4.075317e+01</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.250000e+01</td>\n",
       "      <td>-7.396707e+01</td>\n",
       "      <td>4.076714e+01</td>\n",
       "      <td>-7.396367e+01</td>\n",
       "      <td>4.076811e+01</td>\n",
       "      <td>2.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9.396336e+04</td>\n",
       "      <td>3.456222e+03</td>\n",
       "      <td>3.408790e+03</td>\n",
       "      <td>3.456222e+03</td>\n",
       "      <td>3.537133e+03</td>\n",
       "      <td>2.080000e+02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        fare_amount  pickup_longitude  pickup_latitude  dropoff_longitude  \\\n",
       "count  2.000000e+07      2.000000e+07     2.000000e+07       1.999987e+07   \n",
       "mean   1.134995e+01     -7.250857e+01     3.992013e+01      -7.251212e+01   \n",
       "std    2.878444e+01      1.273661e+01     9.756748e+00       1.273016e+01   \n",
       "min   -1.760000e+02     -3.442060e+03    -3.475482e+03      -3.440697e+03   \n",
       "25%    6.000000e+00     -7.399206e+01     4.073494e+01      -7.399139e+01   \n",
       "50%    8.500000e+00     -7.398180e+01     4.075267e+01      -7.398015e+01   \n",
       "75%    1.250000e+01     -7.396707e+01     4.076714e+01      -7.396367e+01   \n",
       "max    9.396336e+04      3.456222e+03     3.408790e+03       3.456222e+03   \n",
       "\n",
       "       dropoff_latitude  passenger_count  \n",
       "count      1.999987e+07     2.000000e+07  \n",
       "mean       3.992170e+01     1.685199e+00  \n",
       "std        9.628169e+00     1.334611e+00  \n",
       "min       -3.475482e+03     0.000000e+00  \n",
       "25%        4.073404e+01     1.000000e+00  \n",
       "50%        4.075317e+01     1.000000e+00  \n",
       "75%        4.076811e+01     2.000000e+00  \n",
       "max        3.537133e+03     2.080000e+02  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gdf.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "3RU7kfesmGex"
   },
   "source": [
    "### ETL: Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "uri88uVIRcHa"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 2 µs, sys: 1 µs, total: 3 µs\n",
      "Wall time: 6.2 µs\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<pyblazing.apiv2.context.BlazingTable at 0x7f186c3385f8>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%time\n",
    "# make a table from the combined df\n",
    "bc.create_table('train_taxi', gdf, column_names=col_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "XnzjqEFnmDC5"
   },
   "source": [
    "### ETL: Query Tables for Training Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 425
    },
    "colab_type": "code",
    "id": "_MDxz73ZMhhK",
    "outputId": "f2abeafc-0cdf-46b1-ddf5-a5cde3d37792"
   },
   "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>hours</th>\n",
       "      <th>days</th>\n",
       "      <th>months</th>\n",
       "      <th>years</th>\n",
       "      <th>longitude_distance</th>\n",
       "      <th>latitude_distance</th>\n",
       "      <th>passenger_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>22.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.002190</td>\n",
       "      <td>-0.021603</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>-0.004524</td>\n",
       "      <td>0.003807</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.007759</td>\n",
       "      <td>0.010059</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.016609</td>\n",
       "      <td>0.001045</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>-0.024094</td>\n",
       "      <td>-0.023731</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hours  days  months  years  longitude_distance  latitude_distance  \\\n",
       "0   22.0   2.0     2.0   12.0            0.002190          -0.021603   \n",
       "1    7.0  20.0     9.0   14.0           -0.004524           0.003807   \n",
       "2    7.0  23.0     2.0   13.0            0.007759           0.010059   \n",
       "3   23.0  18.0     4.0   15.0            0.016609           0.001045   \n",
       "4    8.0   4.0     3.0   10.0           -0.024094          -0.023731   \n",
       "\n",
       "   passenger_count  \n",
       "0              1.0  \n",
       "1              1.0  \n",
       "2              3.0  \n",
       "3              1.0  \n",
       "4              1.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract time columns, long & lat, # riders (all floats)\n",
    "query = '''\n",
    "        select \n",
    "            cast(hour(key) as float) hours, \n",
    "            cast(dayofmonth(key) as float) days, \n",
    "            cast(month(key) as float) months, \n",
    "            cast(year(key) - 2000 as float) years,  \n",
    "            cast(dropoff_longitude - pickup_longitude as float) longitude_distance, \n",
    "            cast(dropoff_latitude - pickup_latitude as float) latitude_distance, \n",
    "            cast(passenger_count as float) passenger_count\n",
    "        from \n",
    "            train_taxi\n",
    "            '''\n",
    "\n",
    "# run query on table (returns cuDF DataFrame)\n",
    "X_train = bc.sql(query)\n",
    "\n",
    "# fill null values \n",
    "X_train['longitude_distance'] = X_train['longitude_distance'].fillna(0)\n",
    "X_train['latitude_distance'] = X_train['latitude_distance'].fillna(0)\n",
    "X_train['passenger_count'] = X_train['passenger_count'].fillna(0)\n",
    "\n",
    "# how's it look? \n",
    "X_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>fare_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fare_amount\n",
       "0          8.9\n",
       "1          4.0\n",
       "2          5.5\n",
       "3         13.5\n",
       "4         10.5"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query dependent variable y\n",
    "y_train = bc.sql('SELECT fare_amount FROM train_taxi')\n",
    "\n",
    "y_train.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "-J3ZzyD6iwQy"
   },
   "source": [
    "## Linear Regression\n",
    "### LR: Train Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 531
    },
    "colab_type": "code",
    "id": "tVUZvT9TB6Ii",
    "outputId": "d61c0249-47ee-40b8-a72f-9d62383f23dd"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Coefficients:\n",
      "0   -0.027290\n",
      "1    0.003329\n",
      "2    0.106803\n",
      "3    0.637564\n",
      "4    0.000871\n",
      "5   -0.000516\n",
      "6    0.092400\n",
      "dtype: float32\n",
      "\n",
      "Y intercept:\n",
      "3.3568549156188965\n",
      "\n",
      "CPU times: user 689 ms, sys: 590 ms, total: 1.28 s\n",
      "Wall time: 1.28 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# call & create cuML model\n",
    "lr = LinearRegression(fit_intercept=True, normalize=False, algorithm=\"eig\")\n",
    "\n",
    "# train Linear Regression model \n",
    "reg = lr.fit(X_train, y_train)\n",
    "\n",
    "# display results\n",
    "print(f\"Coefficients:\\n{reg.coef_}\\n\")\n",
    "print(f\"Y intercept:\\n{reg.intercept_}\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "pHtni9xcl-ht"
   },
   "source": [
    "### LR: Use Model to Predict Future Taxi Fares \n",
    "\n",
    "#### Download Test Data\n",
    "The cell below will check to see if you've already got the Test data, and, if you don't, will download it for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2020-01-21 17:21:52--  https://blazingsql-demos.s3-us-west-1.amazonaws.com/test.csv\n",
      "Resolving blazingsql-demos.s3-us-west-1.amazonaws.com (blazingsql-demos.s3-us-west-1.amazonaws.com)... 52.219.120.105\n",
      "Connecting to blazingsql-demos.s3-us-west-1.amazonaws.com (blazingsql-demos.s3-us-west-1.amazonaws.com)|52.219.120.105|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 982916 (960K) [text/csv]\n",
      "Saving to: ‘../../../data/blazingsql/test.csv’\n",
      "\n",
      "test.csv            100%[===================>] 959.88K  2.56MB/s    in 0.4s    \n",
      "\n",
      "2020-01-21 17:21:53 (2.56 MB/s) - ‘../../../data/blazingsql/test.csv’ saved [982916/982916]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# do we have Test taxi file?\n",
    "if not os.path.isfile('../../../data/blazingsql/test.csv'):\n",
    "    !wget -P ../../../data/blazingsql https://blazingsql-demos.s3-us-west-1.amazonaws.com/test.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are going to create this table directly from CSV, BlazingSQL requires the full path to the data for table creation. This cell uses the `pwd` bash command to identify the path to this directory, then adds the path to the notebooks-contrib `data/` directory to provide a full path to the test data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/home/jupyter-winston/notebooks-contrib/data/blazingsql/test.csv'"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# identify path to this notebook, !pwd returns SList w/ path (str) at 0th index\n",
    "path = !pwd\n",
    "# extract path notebooks-contrib then\n",
    "path = path[0].split('intermediate_notebooks')[0] \n",
    "# add path to data from there\n",
    "path = path + 'data/blazingsql/' + 'test.csv'\n",
    "# how's it look?\n",
    "path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "yRM5PosNiuGh"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyblazing.apiv2.context.BlazingTable at 0x7f186c3dc358>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# set column names and types\n",
    "col_names = ['key', 'fare_amount', 'pickup_longitude', 'pickup_latitude', \n",
    "                'dropoff_longitude', 'dropoff_latitude', 'passenger_count']\n",
    "col_types = ['date64', 'float32', 'float32', 'float32', 'float32', 'float32', 'float32']\n",
    "\n",
    "# create test table directly from CSV\n",
    "bc.create_table('test_taxi', path, names=col_names, dtype=col_types)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "g4I8AJ51dpW5"
   },
   "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>hours</th>\n",
       "      <th>days</th>\n",
       "      <th>months</th>\n",
       "      <th>years</th>\n",
       "      <th>longitude_distance</th>\n",
       "      <th>latitude_distance</th>\n",
       "      <th>passenger_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>-0.008110</td>\n",
       "      <td>-0.019970</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>-0.012024</td>\n",
       "      <td>0.019814</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.002869</td>\n",
       "      <td>-0.005119</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>-0.009277</td>\n",
       "      <td>-0.016178</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>-0.022537</td>\n",
       "      <td>-0.045345</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hours  days  months  years  longitude_distance  latitude_distance  \\\n",
       "0   13.0  27.0     1.0   15.0           -0.008110          -0.019970   \n",
       "1   13.0  27.0     1.0   15.0           -0.012024           0.019814   \n",
       "2   11.0   8.0    10.0   11.0            0.002869          -0.005119   \n",
       "3   21.0   1.0    12.0   12.0           -0.009277          -0.016178   \n",
       "4   21.0   1.0    12.0   12.0           -0.022537          -0.045345   \n",
       "\n",
       "   passenger_count  \n",
       "0              1.0  \n",
       "1              1.0  \n",
       "2              1.0  \n",
       "3              1.0  \n",
       "4              1.0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# extract time columns, long & lat, # riders (all floats)\n",
    "query = '''\n",
    "        select \n",
    "            cast(hour(key) as float) hours, \n",
    "            cast(dayofmonth(key) as float) days, \n",
    "            cast(month(key) as float) months, \n",
    "            cast(year(key) - 2000 as float) years,  \n",
    "            cast(dropoff_longitude - pickup_longitude as float) longitude_distance, \n",
    "            cast(dropoff_latitude - pickup_latitude as float) latitude_distance, \n",
    "            cast(passenger_count as float) passenger_count\n",
    "        from \n",
    "            test_taxi\n",
    "            '''\n",
    "\n",
    "# run query on table (returns cuDF DataFrame)\n",
    "X_test = bc.sql(query)\n",
    "\n",
    "# fill null values \n",
    "X_test['longitude_distance'] = X_test['longitude_distance'].fillna(0)\n",
    "X_test['latitude_distance'] = X_test['latitude_distance'].fillna(0)\n",
    "X_test['passenger_count'] = X_test['passenger_count'].fillna(0)\n",
    "\n",
    "# how's it look? \n",
    "X_test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "zCft6P5QkepN"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       12.854630\n",
       "1       12.854605\n",
       "2       11.256927\n",
       "3       11.811884\n",
       "4       11.811888\n",
       "5       11.811880\n",
       "6       11.222965\n",
       "7       11.222733\n",
       "8       11.222973\n",
       "9       12.239309\n",
       "10      12.239325\n",
       "11      12.239347\n",
       "12       9.696036\n",
       "13       9.696022\n",
       "14      11.468582\n",
       "15      11.468594\n",
       "16      11.460928\n",
       "17      11.460958\n",
       "18      11.460936\n",
       "19      11.460926\n",
       "20      13.485119\n",
       "21      12.707811\n",
       "22      12.707788\n",
       "23      12.707800\n",
       "24      12.707800\n",
       "25      12.707785\n",
       "26      12.707952\n",
       "27      12.707806\n",
       "28      12.707804\n",
       "29      12.707785\n",
       "          ...    \n",
       "9884    12.643631\n",
       "9885    12.643671\n",
       "9886    12.643652\n",
       "9887    12.643633\n",
       "9888    12.643650\n",
       "9889    12.643656\n",
       "9890    12.643648\n",
       "9891    12.643673\n",
       "9892    12.643652\n",
       "9893    12.643667\n",
       "9894    12.643648\n",
       "9895    12.643719\n",
       "9896    12.643631\n",
       "9897    13.454716\n",
       "9898    13.212105\n",
       "9899    14.138895\n",
       "9900    13.368757\n",
       "9901    13.635015\n",
       "9902    14.171509\n",
       "9903    13.832354\n",
       "9904    13.669437\n",
       "9905    13.259691\n",
       "9906    14.138172\n",
       "9907    13.452593\n",
       "9908    13.717201\n",
       "9909    13.714552\n",
       "9910    13.157532\n",
       "9911    13.419586\n",
       "9912    13.657433\n",
       "9913    13.259361\n",
       "Length: 9914, dtype: float32"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# predict fares \n",
    "predictions = lr.predict(X_test)\n",
    "\n",
    "# display predictions\n",
    "predictions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {},
    "colab_type": "code",
    "id": "GdjUjJ42l2BI"
   },
   "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>hours</th>\n",
       "      <th>days</th>\n",
       "      <th>months</th>\n",
       "      <th>years</th>\n",
       "      <th>longitude_distance</th>\n",
       "      <th>latitude_distance</th>\n",
       "      <th>passenger_count</th>\n",
       "      <th>predicted_fare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>-0.008110</td>\n",
       "      <td>-0.019970</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.854630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>13.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>-0.012024</td>\n",
       "      <td>0.019814</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.854605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.002869</td>\n",
       "      <td>-0.005119</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.256927</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>21.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>-0.009277</td>\n",
       "      <td>-0.016178</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.811884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>-0.022537</td>\n",
       "      <td>-0.045345</td>\n",
       "      <td>1.0</td>\n",
       "      <td>11.811888</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hours  days  months  years  longitude_distance  latitude_distance  \\\n",
       "0   13.0  27.0     1.0   15.0           -0.008110          -0.019970   \n",
       "1   13.0  27.0     1.0   15.0           -0.012024           0.019814   \n",
       "2   11.0   8.0    10.0   11.0            0.002869          -0.005119   \n",
       "3   21.0   1.0    12.0   12.0           -0.009277          -0.016178   \n",
       "4   21.0   1.0    12.0   12.0           -0.022537          -0.045345   \n",
       "\n",
       "   passenger_count  predicted_fare  \n",
       "0              1.0       12.854630  \n",
       "1              1.0       12.854605  \n",
       "2              1.0       11.256927  \n",
       "3              1.0       11.811884  \n",
       "4              1.0       11.811888  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# add predictions to test dataframe\n",
    "X_test['predicted_fare'] = predictions\n",
    "\n",
    "# how's that look?\n",
    "X_test.head()"
   ]
  }
 ],
 "metadata": {
  "accelerator": "GPU",
  "colab": {
   "collapsed_sections": [],
   "name": "BlazingSQL_cuML_Taxi_Fare_Prediction.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}