<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 How to forecast time series in BigQuery ML
This notebook accompanies the article
How to do time series forecasting in BigQuery
Install library and extensions if needed
You don't need to do this if you use AI Platform Notebooks
Step1:  Helper plot functions
Step2:  Plot the time series
The first step, as with any machine learning problem is to gather the training data and explore it. Assume that we have the data on rentals until mid-June of 2015 and we'd like to predict for the rest of the month. We can gather the past 6 weeks of data using
Step3:  Train ARIMA model
We can use this data to train an ARIMA model, telling BigQuery which column is the data column and which one the timestamp column
Step4:  We can get the forecast data using
Step5:  Forecasting a bunch of series
So far, I have been forecasting the overall rental volume for all the bicycle stations in Hyde Park. How do we predict the rental volume for each individual station? Use the time_series_id_col
Step6:  Note that instead of training the series on 45 days (May 1 to June 15), I'm now training on a longer time period.
That's because the aggregate time series will tend to be smoother and much easier to predict than the time series
for individual stations. So, we have to show the model a longer trendline.
Step7:  As you would expect, the aggregated time series over all the stations is much smoother and more predictable than the time series of just one station (the one station data will be more noisy). So, some forecasts will be better than others.
Step8:  Evaluation
As you can see from the graphs above, the predictions accuracy varies by station. Can we gauge how good the prediction for a station is going to be?
<ASSISTANT_TASK:>
Python Code:
#!pip install google-cloud-bigquery
%load_ext google.cloud.bigquery
Explanation: How to forecast time series in BigQuery ML
This notebook accompanies the article
How to do time series forecasting in BigQuery
Install library and extensions if needed
You don't need to do this if you use AI Platform Notebooks
End of explanation
import matplotlib.pyplot as plt
import pandas as pd
def plot_historical_and_forecast(input_timeseries, timestamp_col_name, data_col_name, forecast_output=None, actual=None):
  input_timeseries = input_timeseries.sort_values(timestamp_col_name)    
  plt.figure(figsize=(20,6))
  plt.plot(input_timeseries[timestamp_col_name], input_timeseries[data_col_name], label = 'Historical')
  plt.xlabel(timestamp_col_name)
  plt.ylabel(data_col_name)
  if forecast_output is not None:
    forecast_output = forecast_output.sort_values('forecast_timestamp')
    forecast_output['forecast_timestamp'] = pd.to_datetime(forecast_output['forecast_timestamp'])
    x_data = forecast_output['forecast_timestamp']
    y_data = forecast_output['forecast_value']
    confidence_level = forecast_output['confidence_level'].iloc[0] * 100
    low_CI = forecast_output['confidence_interval_lower_bound']
    upper_CI = forecast_output['confidence_interval_upper_bound']
    # Plot the data, set the linewidth, color and transparency of the
    # line, provide a label for the legend
    plt.plot(x_data, y_data, alpha = 1, label = 'Forecast', linestyle='--')
    # Shade the confidence interval
    plt.fill_between(x_data, low_CI, upper_CI, color = '#539caf', alpha = 0.4, label = str(confidence_level) + '% confidence interval')
  # actual
  if actual is not None:
    actual = actual.sort_values(timestamp_col_name)
    plt.plot(actual[timestamp_col_name], actual[data_col_name], label = 'Actual', linestyle='--')   
  # Display legend
  plt.legend(loc = 'upper center', prop={'size': 16})
Explanation: Helper plot functions
End of explanation
%%bigquery df
SELECT
   CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY date
HAVING date BETWEEN '2015-05-01' AND '2015-06-15'
ORDER BY date
plot_historical_and_forecast(df, 'date', 'numrentals');
Explanation: Plot the time series
The first step, as with any machine learning problem is to gather the training data and explore it. Assume that we have the data on rentals until mid-June of 2015 and we'd like to predict for the rest of the month. We can gather the past 6 weeks of data using
End of explanation
!bq ls ch09eu || bq mk --location EU ch09eu
%%bigquery
CREATE OR REPLACE MODEL ch09eu.numrentals_forecast
OPTIONS(model_type='ARIMA',
        time_series_data_col='numrentals',
        time_series_timestamp_col='date') AS
SELECT
   CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY date
HAVING date BETWEEN '2015-05-01' AND '2015-06-15'
Explanation: Train ARIMA model
We can use this data to train an ARIMA model, telling BigQuery which column is the data column and which one the timestamp column:
End of explanation
%%bigquery fcst
SELECT * FROM ML.FORECAST(MODEL ch09eu.numrentals_forecast, 
                  STRUCT(14 AS horizon, 0.9 AS confidence_level))
plot_historical_and_forecast(df, 'date', 'numrentals', fcst);
%%bigquery actual
SELECT
   CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY date
HAVING date BETWEEN '2015-06-16' AND '2015-07-01'
ORDER BY date
plot_historical_and_forecast(df, 'date', 'numrentals', fcst, actual);
Explanation: We can get the forecast data using:
End of explanation
%%bigquery
CREATE OR REPLACE MODEL ch09eu.numrentals_forecast
OPTIONS(model_type='ARIMA',
        time_series_data_col='numrentals',
        time_series_timestamp_col='date',
        time_series_id_col='start_station_name') AS
SELECT
   start_station_name
   , CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY start_station_name, date
HAVING date BETWEEN '2015-01-01' AND '2015-06-15'
Explanation: Forecasting a bunch of series
So far, I have been forecasting the overall rental volume for all the bicycle stations in Hyde Park. How do we predict the rental volume for each individual station? Use the time_series_id_col:
End of explanation
%%bigquery
SELECT * 
FROM ML.ARIMA_COEFFICIENTS(MODEL ch09eu.numrentals_forecast)
ORDER BY start_station_name
%%bigquery fcst
SELECT 
  * 
FROM ML.FORECAST(MODEL ch09eu.numrentals_forecast, 
                  STRUCT(14 AS horizon, 0.9 AS confidence_level))
ORDER By start_station_name, forecast_timestamp
%%bigquery df
SELECT
   start_station_name
   , CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY start_station_name, date
HAVING date BETWEEN '2015-05-01' AND '2015-06-15'  -- this is just for plotting, hence we'll keep this 45 days.
%%bigquery actual
SELECT
   start_station_name
   , CAST(EXTRACT(date from start_date) AS TIMESTAMP) AS date
   , COUNT(*) AS numrentals
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
GROUP BY start_station_name, date
HAVING date BETWEEN '2015-06-16' AND '2015-07-01'
Explanation: Note that instead of training the series on 45 days (May 1 to June 15), I'm now training on a longer time period.
That's because the aggregate time series will tend to be smoother and much easier to predict than the time series
for individual stations. So, we have to show the model a longer trendline.
End of explanation
%%bigquery stations
SELECT DISTINCT start_station_name
FROM
  `bigquery-public-data`.london_bicycles.cycle_hire
WHERE start_station_name LIKE '%Hyde%'  -- all stations in Hyde Park
ORDER by start_station_name ASC
stations
station = stations['start_station_name'].iloc[3] # Hyde Park Corner
print(station)
plot_historical_and_forecast(df[df['start_station_name']==station],
                             'date', 'numrentals',
                             fcst[fcst['start_station_name']==station], 
                             actual[actual['start_station_name']==station]);
station = stations['start_station_name'].iloc[6] # Serpentine Car Park,
print(station)
plot_historical_and_forecast(df[df['start_station_name']==station],
                             'date', 'numrentals',
                             fcst[fcst['start_station_name']==station], 
                             actual[actual['start_station_name']==station]);
station = stations['start_station_name'].iloc[4] # Knightsbridge
print(station)
plot_historical_and_forecast(df[df['start_station_name']==station],
                             'date', 'numrentals',
                             fcst[fcst['start_station_name']==station], 
                             actual[actual['start_station_name']==station]);
Explanation: As you would expect, the aggregated time series over all the stations is much smoother and more predictable than the time series of just one station (the one station data will be more noisy). So, some forecasts will be better than others.
End of explanation
%%bigquery
SELECT * FROM ML.EVALUATE(MODEL ch09eu.numrentals_forecast)
ORDER BY variance DESC
Explanation: Evaluation
As you can see from the graphs above, the predictions accuracy varies by station. Can we gauge how good the prediction for a station is going to be?
End of explanation
<END_TASK>