# Repeatable splitting

In this notebook, we will explore the impact of different ways of creating machine learning datasets.

Repeatability is important in machine learning. If you do the same thing now and 5 minutes from now and get different answers, then it makes experimentation difficult. In other words, you will find it difficult to gauge whether a change you made has resulted in an improvement or not.

In :
from google.cloud import bigquery


### Create a simple machine learning model

The dataset that we will use is a BigQuery public dataset of airline arrival data. Click on the link, and look at the column names. Switch to the Details tab to verify that the number of records is 70 million, and then switch to the Preview tab to look at a few rows.

We want to predict the arrival delay of an airline based on the departure delay. The model that we will use is a zero-bias linear model: $$delay_{arrival} = \alpha * delay_{departure}$$

To train the model is to estimate a good value for $\alpha$.

One approach to estimate alpha is to use this formula: $$\alpha = \frac{\sum delay_{departure} delay_{arrival} }{ \sum delay_{departure}^2 }$$ Because we'd like to capture the idea that this relationship is different for flights from New York to Los Angeles vs. flights from Austin to Indianapolis (shorter flight, less busy airports), we'd compute a different $alpha$ for each airport-pair. For simplicity, we'll do this model only for flights between Denver and Los Angeles.

## Naive random split (not repeatable)

In :
compute_alpha = """
#standardSQL
SELECT
SAFE_DIVIDE(SUM(arrival_delay * departure_delay), SUM(departure_delay * departure_delay)) AS alpha
FROM
(
SELECT RAND() AS splitfield,
arrival_delay,
departure_delay
FROM
bigquery-samples.airline_ontime_data.flights
WHERE
departure_airport = 'DEN' AND arrival_airport = 'LAX'
)
WHERE
splitfield < 0.8
"""

In :
results = bigquery.Client().query(compute_alpha).to_dataframe()
alpha = results['alpha']
print(alpha)

0.9759292430838074


### What is wrong with calculating RMSE on the training and test data as follows?

In :
compute_rmse = """
#standardSQL
SELECT
dataset,
SQRT(AVG((arrival_delay - ALPHA * departure_delay)*(arrival_delay - ALPHA * departure_delay))) AS rmse,
COUNT(arrival_delay) AS num_flights
FROM (
SELECT
IF (RAND() < 0.8, 'train', 'eval') AS dataset,
arrival_delay,
departure_delay
FROM
bigquery-samples.airline_ontime_data.flights
WHERE
departure_airport = 'DEN'
AND arrival_airport = 'LAX' )
GROUP BY
dataset
"""
bigquery.Client().query(compute_rmse.replace('ALPHA', str(alpha))).to_dataframe()

Out:
dataset rmse num_flights
0 train 13.034724 63913
1 eval 13.280670 15776

Hint:

• Are you really getting the same training data in the compute_rmse query as in the compute_alpha query?
• Do you get the same answers each time you rerun the compute_alpha and compute_rmse blocks?

### How do we correctly train and evaluate?

Here's the right way to compute the RMSE using the actual training and held-out (evaluation) data. Note how much harder this feels.

Although the calculations are now correct, the experiment is still not repeatable.

Try running it several times; do you get the same answer?

In :
train_and_eval_rand = """
#standardSQL
WITH
alldata AS (
SELECT
IF (RAND() < 0.8,
'train',
'eval') AS dataset,
arrival_delay,
departure_delay
FROM
bigquery-samples.airline_ontime_data.flights
WHERE
departure_airport = 'DEN'
AND arrival_airport = 'LAX' ),
training AS (
SELECT
SAFE_DIVIDE( SUM(arrival_delay * departure_delay) , SUM(departure_delay * departure_delay)) AS alpha
FROM
alldata
WHERE
dataset = 'train' )
SELECT
MAX(alpha) AS alpha,
dataset,
SQRT(AVG((arrival_delay - alpha * departure_delay)*(arrival_delay - alpha * departure_delay))) AS rmse,
COUNT(arrival_delay) AS num_flights
FROM
alldata,
training
GROUP BY
dataset
"""

In :
bigquery.Client().query(train_and_eval_rand).to_dataframe()

Out:
alpha dataset rmse num_flights
0 0.976553 train 13.105478 63682
1 0.976553 eval 12.997229 16007

## Using HASH of date to split the data

Let's split by date and train.

In :
compute_alpha = """
#standardSQL
SELECT
SAFE_DIVIDE(SUM(arrival_delay * departure_delay), SUM(departure_delay * departure_delay)) AS alpha
FROM
bigquery-samples.airline_ontime_data.flights
WHERE
departure_airport = 'DEN' AND arrival_airport = 'LAX'
AND ABS(MOD(FARM_FINGERPRINT(date), 10)) < 8
"""
results = bigquery.Client().query(compute_alpha).to_dataframe()
alpha = results['alpha']
print(alpha)

0.9758039143620403


We can now use the alpha to compute RMSE. Because the alpha value is repeatable, we don't need to worry that the alpha in the compute_rmse will be different from the alpha computed in the compute_alpha.

In :
compute_rmse = """
#standardSQL
SELECT
IF(ABS(MOD(FARM_FINGERPRINT(date), 10)) < 8, 'train', 'eval') AS dataset,
SQRT(AVG((arrival_delay - ALPHA * departure_delay)*(arrival_delay - ALPHA * departure_delay))) AS rmse,
COUNT(arrival_delay) AS num_flights
FROM
bigquery-samples.airline_ontime_data.flights
WHERE
departure_airport = 'DEN'
AND arrival_airport = 'LAX'
GROUP BY
dataset
"""

  dataset       rmse  num_flights