# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 2
#     language: python
#     name: python2
# ---

# + [markdown] id="PDDqFd8hon3v" colab_type="text"
# <h1> Create and benchmark train, validation, and test datasets </h1>
#
# In this notebook, you will prepare the training, validation (sometimes called evaluation), and test datasets using the NYC taxi fare data. After that, you will benchmark against these datasets, in other words use your benchmark model to calculate the metric values for the training, validation, and test examples. In parallel, you will learn how to include various bash shell commands (e.g. ls, head, and others) in your Colab notebooks.
#
#
# ---
# Before you start, **make sure that you are logged in with your student account**. Otherwise you may incur Google Cloud charges for using this notebook. 
#
# ---
#

# + id="Wq75B91eon3y" colab_type="code" cellView="form" colab={}
import numpy as np
import pandas as pd
import seaborn as sns

import shutil
from google.cloud import bigquery

#@markdown Copy-paste your GCP Project ID in the following field:


PROJECT = "" #@param {type: "string"}

#@markdown When running this cell you will need to **uncheck "Reset all runtimes before running"** as shown on the following screenshot:

#@markdown ![](https://i.imgur.com/9dgw0h0.png)

#@markdown Next, use Shift-Enter to run this cell and to complete authentication.

try:  
  from google.colab import auth
  auth.authenticate_user()  
  print("AUTHENTICATED")
except:
  print("FAILED to authenticate")

# + [markdown] id="quQPY9r8MzQ6" colab_type="text"
# Next, query for 1 out of 100,000 rows of the entire taxi fare dataset and apply the clean up pre-processing rules you have developed in the earlier lab. Based on the summary from the Pandas describe table, you can confirm that there are roughly 10,500 rows of cleaned-up data in the `tripsqc` variable.

# + id="YdiXbfDuI-LI" colab_type="code" colab={}
EVERY_N = 100000
bq = bigquery.Client(project=PROJECT)

trips = bq.query('''
  SELECT
    pickup_datetime,
    pickup_longitude, pickup_latitude, 
    dropoff_longitude, dropoff_latitude,
    passenger_count,
    trip_distance,
    tolls_amount,
    fare_amount,
    total_amount
  FROM
    `nyc-tlc.yellow.trips`
  WHERE
    MOD(ABS(FARM_FINGERPRINT(STRING(pickup_datetime))), %d) = 1
    
    #note that that trips with zero distance or 
    #costing less than $2.50 are excluded    
    AND trip_distance > 0 AND fare_amount >= 2.5    
    
''' % (EVERY_N)).to_dataframe()

def preprocess(trips_in):
  trips = trips_in.copy(deep=True)
  trips.fare_amount = trips.fare_amount + trips.tolls_amount
  del trips['tolls_amount']
  del trips['total_amount']
  del trips['trip_distance']
  del trips['pickup_datetime']
  qc = np.all([\
             trips['pickup_longitude'] > -78, \
             trips['pickup_longitude'] < -70, \
             trips['dropoff_longitude'] > -78, \
             trips['dropoff_longitude'] < -70, \
             trips['pickup_latitude'] > 37, \
             trips['pickup_latitude'] < 45, \
             trips['dropoff_latitude'] > 37, \
             trips['dropoff_latitude'] < 45, \
             trips['passenger_count'] > 0,
            ], axis=0)
  return trips[qc]

tripsqc = preprocess(trips)
tripsqc.describe()

# + [markdown] id="bbsRzcqLon46" colab_type="text"
# <h3> Create ML datasets </h3>
#
# The next cell splits the cleaned up dataset randomly into training, validation and test sets. Since you are working with an in-memory dataset (for now), you will use a 70%-15%-15% split. Later you will learn about the benefits of allocating a larger percentage of the entire dataset for training.

# + id="9WZgUiO0on47" colab_type="code" colab={}
shuffled = tripsqc.sample(frac=1)
trainsize = int(len(shuffled['fare_amount']) * 0.70)
validsize = int(len(shuffled['fare_amount']) * 0.15)

df_train = shuffled.iloc[:trainsize, :]
df_valid = shuffled.iloc[trainsize:(trainsize+validsize), :]
df_test = shuffled.iloc[(trainsize+validsize):, :]

df_train.describe()

# + id="G60adT61on5C" colab_type="code" colab={}
df_valid.describe()

# + id="1i2mo1CAon5H" colab_type="code" colab={}
df_test.describe()


# + [markdown] id="ng1gbvDuon5K" colab_type="text"
# Let's write out the three dataframes to appropriately named csv files. The files will be useful for local training while you are developing your machine learning models. In future labs, you will scale out to a larger dataset using other serverless capabilities like Cloud Machine Learning Engine (Cloud MLE) and Dataflow.

# + id="yuQwBVVpon5L" colab_type="code" colab={}
def to_csv(df, filename):
  outdf = df.copy(deep=False)
  outdf.loc[:, 'key'] = np.arange(0, len(outdf)) # rownumber as key
  # reorder columns so that target is first column
  cols = outdf.columns.tolist()
  cols.remove('fare_amount')
  cols.insert(0, 'fare_amount')
  print (cols)  # new order of columns
  outdf = outdf[cols]
  outdf.to_csv(filename, header=False, index_label=False, index=False)

to_csv(df_train, 'taxi-train.csv')
to_csv(df_valid, 'taxi-valid.csv')
to_csv(df_test, 'taxi-test.csv')


# + [markdown] id="MOPzJGT7OtWu" colab_type="text"
# There are 2 ways to execute shell commands in the OS environment hosting this notebook:
#
# 1. You can prefix your `bash` command with an exclaimation mark as shown in the next code cell.
#
# 2. You can use the `%%bash` "magic" as the first line of a code cell. This approach is better suited for multi-line shell scripts.
#
# If you are interested in details about Jupyter "magics", you can learn more [here](https://nbviewer.jupyter.org/github/ipython/ipython/blob/1.x/examples/notebooks/Cell%20Magics.ipynb)

# + [markdown] id="RGZsBGRcon5T" colab_type="text"
# <h3> Verify that datasets exist </h3>

# + id="57XAd4O8on5U" colab_type="code" colab={}
# !ls -l *.csv

# + [markdown] id="f1AvPQIDon5Y" colab_type="text"
# There are 3 .csv files corresponding to train, valid, test datasets.  The ratio of file sizes reflect the percentages in the split of the data.

# + id="Tcqtnc0Hon5Y" colab_type="code" colab={} language="bash"
# head -10 taxi-train.csv

# + [markdown] id="St5NHo1lon5b" colab_type="text"
# Looks good! Now the datasets are prepared so you will be ready to train machine learning models, evaluate, and test them.

# + [markdown] id="DBbYTPZAon5c" colab_type="text"
# <h3> Benchmark </h3>
#
# Before committing to a complex machine learning model, it is a good idea to come up with a very simple, heuristic model and use that as a benchmark.
#
# My model is going to simply divide the mean fare_amount by the mean trip_distance to come up with an average rate per kilometer and use that to predict.  Let's compute the RMSE of such a model.

# + id="o4TuDR8Jon5c" colab_type="code" colab={}
def distance_between(lat1, lon1, lat2, lon2):
  # haversine formula to compute distance "as the crow flies".  Taxis can't fly of course.
  dist = np.degrees(np.arccos(np.minimum(1,np.sin(np.radians(lat1)) * np.sin(np.radians(lat2)) + np.cos(np.radians(lat1)) * np.cos(np.radians(lat2)) * np.cos(np.radians(lon2 - lon1))))) * 60 * 1.515 * 1.609344
  return dist

def estimate_distance(df):
  return distance_between(df['pickuplat'], df['pickuplon'], df['dropofflat'], df['dropofflon'])

def compute_rmse(actual, predicted):
  return np.sqrt(np.mean((actual-predicted)**2))

def print_rmse(df, rate, name):
  print ("{1} RMSE = {0}".format(compute_rmse(df['fare_amount'], rate*estimate_distance(df)), name))

FEATURES = ['pickuplon','pickuplat','dropofflon','dropofflat','passengers']
TARGET = 'fare_amount'
columns = list([TARGET])
columns.extend(FEATURES) # in CSV, target is the first column, after the features
columns.append('key')

df_train = pd.read_csv('taxi-train.csv', header=None, names=columns)
df_valid = pd.read_csv('taxi-valid.csv', header=None, names=columns)
df_test = pd.read_csv('taxi-test.csv', header=None, names=columns)

rate = df_train['fare_amount'].mean() / estimate_distance(df_train).mean()
print ("Rate = ${0}/km".format(rate))

print_rmse(df_train, rate, 'Train')
print_rmse(df_valid, rate, 'Valid') 
print_rmse(df_test, rate, 'Test') 


# + [markdown] id="Z5TGIWkQon5i" colab_type="text"
# <h2>Benchmark on same dataset</h2>
#
# The RMSE depends on the dataset and for meaningful and reproducible comparisons it is critical to measure on the same dataset each time. The following query will continue to be reused in the later labs:

# + id="O9xVQv49on5j" colab_type="code" colab={}
def create_query(phase, EVERY_N):
  """
  phase: 1=train 2=valid
  """
  base_query = """
    SELECT
      (tolls_amount + fare_amount) AS fare_amount,
      
      CONCAT( STRING(pickup_datetime), 
              CAST(pickup_longitude AS STRING), 
              CAST(pickup_latitude AS STRING),
              CAST(dropoff_latitude AS STRING), 
              CAST(dropoff_longitude AS STRING)) AS key,
              
      EXTRACT(DAYOFWEEK FROM pickup_datetime)*1.0 AS dayofweek,
      EXTRACT(HOUR FROM pickup_datetime)*1.0 AS hourofday,
      pickup_longitude AS pickuplon,
      pickup_latitude AS pickuplat,
      dropoff_longitude AS dropofflon,
      dropoff_latitude AS dropofflat,
      passenger_count*1.0 AS passengers
    FROM
      `nyc-tlc.yellow.trips`
    WHERE
      {}
      AND trip_distance > 0
      AND fare_amount >= 2.5
      AND pickup_longitude > -78
      AND pickup_longitude < -70
      AND dropoff_longitude > -78
      AND dropoff_longitude < -70
      AND pickup_latitude > 37
      AND pickup_latitude < 45
      AND dropoff_latitude > 37
      AND dropoff_latitude < 45
      AND passenger_count > 0
  """
  if EVERY_N == None:
    if phase < 2:
      # training
      selector = "MOD(ABS(FARM_FINGERPRINT(STRING(pickup_datetime))), 4) < 2"
    else:
      selector = "MOD(ABS(FARM_FINGERPRINT(STRING(pickup_datetime))), 4) = 2"
  else:
      selector = "MOD(ABS(FARM_FINGERPRINT(STRING(pickup_datetime))), %d) = %d" % (EVERY_N, phase)
    
  query = base_query.format(selector)

  return query

sql = create_query(2, 100000)
df_valid = bq.query(sql).to_dataframe()
print_rmse(df_valid, 2.56, 'Final Validation Set')

# + [markdown] id="MT9dwy_ton5m" colab_type="text"
# The simple distance-based rule gives us a RMSE of <b>$7.42</b>.  We have to beat this, of course, but you will find that simple rules of thumb like this can be surprisingly difficult to beat.
#
# Let's be ambitious, though, and make our goal to build ML models that have a RMSE of less than $6 on the test set.

# + [markdown] id="HVuX8oeMon5m" colab_type="text"
# Copyright 2019 CounterFactual.AI LLC. Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at
# http://www.apache.org/licenses/LICENSE-2.0
# Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.

StarcoderdataJupyterScriptsDedupFiltered