<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Predicting Babyweight Using BigQuery ML
Learning Objectives
- Explore the machine learning capabilities of BigQuery
- Learn how to train a linear regression model in BigQuery
- Examine the TRAINING_INFO produced by training a model
- Make predictions with a trained model in BigQuery using ML.PREDICT
Introduction
BQML is a great option when a linear model will suffice, or when you want a quick benchmark to beat. But, for more complex models such as neural networks you will need to pull the data out of BigQuery and into an ML Framework like TensorFlow. That being said, what BQML gives up in complexity, it gains in ease of use. 
Please see this notebook for more context on this problem and how the features were chosen.
We'll start as usual by setting our environment variables.
Step1:  Exploring the data
Here, we will be taking natality data and training on features to predict the birth weight.
The CDC's Natality data has details on US births from 1969 to 2008 and is available in BigQuery as a public data set. More details
Step2:  Define features
Looking over the data set, there are a few columns of interest that could be leveraged into features for a reasonable prediction of approximate birth weight.
Further, some feature engineering may be accomplished with the BigQuery CAST function -- in BQML, all strings are considered categorical features and all numeric types are considered continuous ones.
The hashmonth is added so that we can repeatably split the data without leakage -- we want all babies that share a birthday to be either in training set or in test set and not spread between them (otherwise, there would be information leakage when it comes to triplets, etc.)
Step3:  Train a model in BigQuery
With the relevant columns chosen to accomplish predictions, it is then possible to create (train) the model in BigQuery. First, a dataset will be needed store the model. (if this throws an error in Datalab, simply create the dataset from the BigQuery console).
Step4:  With the demo dataset ready, it is possible to create a linear regression model to train the model.
This will take approximately 4 minutes to run and will show Done when complete.
Exercise 1
Complete the TODOs in the cell below to train a linear regression model in BigQuery using weight_pounds as the label. Name your model babyweight_model_asis; it will reside within the demo dataset we created above. 
Have a look at the documentation for CREATE MODEL in BQML to see examples of the correct syntax.
Step5:  Explore the training statistics
During the model training (and after the training), it is possible to see the model's training evaluation statistics. For each training run, a table named &lt;model_name&gt;_eval is created. This table has basic performance statistics for each iteration.
While the new model is training, review the training statistics in the BigQuery UI to see the below model training
Step6:  Some of these columns are obvious; although, what do the non-specific ML columns mean (specific to BQML)?
training_run - Will be zero for a newly created model. If the model is re-trained using warm_start, this will increment for each re-training.
iteration - Number of the associated training_run, starting with zero for the first iteration.
duration_ms - Indicates how long the iteration took (in ms).
Note, you can also see these stats by refreshing the BigQuery UI window, finding the &lt;model_name&gt; table, selecting on it, and then the Training Stats sub-header.
Let's plot the training and evaluation loss to see if the model has an overfit.
Step7:  As you can see, the training loss and evaluation loss are essentially identical. We do not seem to be overfitting.
Make a prediction with BQML using the trained model
With a trained model, it is now possible to make a prediction on the values. The only difference from the second query above is the reference to the model. The data has been limited (LIMIT 100) to reduce amount of data returned.
When the ml.predict function is leveraged, output prediction column name for the model is predicted_&lt;label_column_name&gt;.
Exercise 3
Complete the TODO in the cell below to make predictions in BigQuery with our newly trained model demo.babyweight_model_asis on the public.samples.natality table. You'll need to preprocess the data for training by selecting only those examples which have
- year greater than 2000
- gestation_weeks greater than 0
- mother_age  greater than 0
- plurality greater than 0
- weight_pounds greater than 0
Look at the expected syntax for the ML.PREDICT Function.
Hint
Step8:  More advanced...
In the original example, we were taking into account the idea that if no ultrasound has been performed, some of the features (e.g. is_male) will not be known. Therefore, we augmented the dataset with such masked features and trained a single model to deal with both these scenarios.
In addition, during data exploration, we learned that the data size set for mothers older than 45 was quite sparse, so we will discretize the mother age.
Step9:  On the same dataset, will also suppose that it is unknown whether the child is male or female (on the same dataset) to simulate that an ultrasound was not been performed.
Step10:  Bringing these two separate data sets together, there is now a dataset for male or female children determined with ultrasound or unknown if without.
Step11:  Create a new model
With a data set which has been feature engineered, it is ready to create model with the CREATE or REPLACE MODEL statement
This will take 5-10 minutes and will show Done when complete.
Exercise 4
As in Exercise 1 above, below you are asked to complete the TODO in the cell below to train a linear regression model in BigQuery using weight_pounds as the label. This time, since we're using the supplemented dataset containing without_ultrasound data, name your model babyweight_model_fc. This model will reside within the demo dataset. 
Have a look at the documentation for CREATE MODEL in BQML to see examples of the correct syntax.
Step12:  Training Statistics
While the new model is training, review the training statistics in the BigQuery UI to see the below model training
Step13:  Make a prediction with the new model
Perhaps it is of interest to make a prediction of the baby's weight given a number of other factors
<ASSISTANT_TASK:>
Python Code:
PROJECT = 'cloud-training-demos'  # Replace with your PROJECT
BUCKET = 'cloud-training-bucket'  # Replace with your BUCKET
REGION = 'us-central1'            # Choose an available region for Cloud MLE
import os
os.environ['BUCKET'] = BUCKET
os.environ['PROJECT'] = PROJECT
os.environ['REGION'] = REGION
%%bash
gcloud config set project $PROJECT
gcloud config set compute/region $REGION
%load_ext google.cloud.bigquery
Explanation: Predicting Babyweight Using BigQuery ML
Learning Objectives
- Explore the machine learning capabilities of BigQuery
- Learn how to train a linear regression model in BigQuery
- Examine the TRAINING_INFO produced by training a model
- Make predictions with a trained model in BigQuery using ML.PREDICT
Introduction
BQML is a great option when a linear model will suffice, or when you want a quick benchmark to beat. But, for more complex models such as neural networks you will need to pull the data out of BigQuery and into an ML Framework like TensorFlow. That being said, what BQML gives up in complexity, it gains in ease of use. 
Please see this notebook for more context on this problem and how the features were chosen.
We'll start as usual by setting our environment variables.
End of explanation
%%bigquery --project $PROJECT
SELECT
    *
FROM
  publicdata.samples.natality
WHERE
  year > 2000
  AND gestation_weeks > 0
  AND mother_age > 0
  AND plurality > 0
  AND weight_pounds > 0
LIMIT 10
Explanation: Exploring the data
Here, we will be taking natality data and training on features to predict the birth weight.
The CDC's Natality data has details on US births from 1969 to 2008 and is available in BigQuery as a public data set. More details: https://bigquery.cloud.google.com/table/publicdata:samples.natality?tab=details
Lets start by looking at the data since 2000 with useful values; i.e. those greater than zero!
End of explanation
%%bigquery --project $PROJECT
SELECT
    weight_pounds, -- this is the label; because it is continuous, we need to use regression
    CAST(is_male AS STRING) AS is_male,
    mother_age,
    CAST(plurality AS STRING) AS plurality,
    gestation_weeks,
    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth
FROM
  publicdata.samples.natality
WHERE
  year > 2000
  AND gestation_weeks > 0
  AND mother_age > 0
  AND plurality > 0
  AND weight_pounds > 0
LIMIT 10
Explanation: Define features
Looking over the data set, there are a few columns of interest that could be leveraged into features for a reasonable prediction of approximate birth weight.
Further, some feature engineering may be accomplished with the BigQuery CAST function -- in BQML, all strings are considered categorical features and all numeric types are considered continuous ones.
The hashmonth is added so that we can repeatably split the data without leakage -- we want all babies that share a birthday to be either in training set or in test set and not spread between them (otherwise, there would be information leakage when it comes to triplets, etc.)
End of explanation
%%bash
bq --location=US mk -d demo
Explanation: Train a model in BigQuery
With the relevant columns chosen to accomplish predictions, it is then possible to create (train) the model in BigQuery. First, a dataset will be needed store the model. (if this throws an error in Datalab, simply create the dataset from the BigQuery console).
End of explanation
%%bigquery --project $PROJECT
# TODO: Your code goes here
  
WITH natality_data AS (
  SELECT
    weight_pounds,-- this is the label; because it is continuous, we need to use regression
    CAST(is_male AS STRING) AS is_male,
    mother_age,
    CAST(plurality AS STRING) AS plurality,
    gestation_weeks,
    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth
  FROM
    publicdata.samples.natality
  WHERE
    year > 2000
    AND gestation_weeks > 0
    AND mother_age > 0
    AND plurality > 0
    AND weight_pounds > 0
)
SELECT
    weight_pounds,
    is_male,
    mother_age,
    plurality,
    gestation_weeks
FROM
    natality_data
WHERE
  ABS(MOD(hashmonth, 4)) < 3  -- select 75% of the data as training
Explanation: With the demo dataset ready, it is possible to create a linear regression model to train the model.
This will take approximately 4 minutes to run and will show Done when complete.
Exercise 1
Complete the TODOs in the cell below to train a linear regression model in BigQuery using weight_pounds as the label. Name your model babyweight_model_asis; it will reside within the demo dataset we created above. 
Have a look at the documentation for CREATE MODEL in BQML to see examples of the correct syntax.
End of explanation
%%bigquery --project $PROJECT
# TODO: Your code goes here
Explanation: Explore the training statistics
During the model training (and after the training), it is possible to see the model's training evaluation statistics. For each training run, a table named &lt;model_name&gt;_eval is created. This table has basic performance statistics for each iteration.
While the new model is training, review the training statistics in the BigQuery UI to see the below model training: https://bigquery.cloud.google.com/. Since these statistics are updated after each iteration of model training, you will see different values for each refresh while the model is training.
The training details may also be viewed after the training completes from this notebook.
Exercise 2
The cell below is missing the SQL query to examine the training statistics of our trained model. Complete the TODO below to view the results of our training job above. 
Look back at the usage of the ML.TRAINING_INFO function and its correct syntax.
End of explanation
from google.cloud import bigquery
bq = bigquery.Client(project=PROJECT)
df = bq.query("SELECT * FROM ML.TRAINING_INFO(MODEL demo.babyweight_model_asis)").to_dataframe()
# plot both lines in same graph
import matplotlib.pyplot as plt
plt.plot( 'iteration', 'loss', data=df, marker='o', color='orange', linewidth=2)
plt.plot( 'iteration', 'eval_loss', data=df, marker='', color='green', linewidth=2, linestyle='dashed')
plt.xlabel('iteration')
plt.ylabel('loss')
plt.legend();
Explanation: Some of these columns are obvious; although, what do the non-specific ML columns mean (specific to BQML)?
training_run - Will be zero for a newly created model. If the model is re-trained using warm_start, this will increment for each re-training.
iteration - Number of the associated training_run, starting with zero for the first iteration.
duration_ms - Indicates how long the iteration took (in ms).
Note, you can also see these stats by refreshing the BigQuery UI window, finding the &lt;model_name&gt; table, selecting on it, and then the Training Stats sub-header.
Let's plot the training and evaluation loss to see if the model has an overfit.
End of explanation
%%bigquery --project $PROJECT
SELECT
  *
FROM
    # TODO: Your code goes here
LIMIT 100
Explanation: As you can see, the training loss and evaluation loss are essentially identical. We do not seem to be overfitting.
Make a prediction with BQML using the trained model
With a trained model, it is now possible to make a prediction on the values. The only difference from the second query above is the reference to the model. The data has been limited (LIMIT 100) to reduce amount of data returned.
When the ml.predict function is leveraged, output prediction column name for the model is predicted_&lt;label_column_name&gt;.
Exercise 3
Complete the TODO in the cell below to make predictions in BigQuery with our newly trained model demo.babyweight_model_asis on the public.samples.natality table. You'll need to preprocess the data for training by selecting only those examples which have
- year greater than 2000
- gestation_weeks greater than 0
- mother_age  greater than 0
- plurality greater than 0
- weight_pounds greater than 0
Look at the expected syntax for the ML.PREDICT Function.
Hint: You will need to cast the features is_male and plurality as STRINGs
End of explanation
%%bigquery --project $PROJECT
SELECT
    weight_pounds,
    CAST(is_male AS STRING) AS is_male,
    IF(mother_age < 18, 'LOW',
         IF(mother_age > 45, 'HIGH',
            CAST(mother_age AS STRING))) AS mother_age,
    CAST(plurality AS STRING) AS plurality,
    CAST(gestation_weeks AS STRING) AS gestation_weeks,
    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth
  FROM
    publicdata.samples.natality
  WHERE
    year > 2000
    AND gestation_weeks > 0
    AND mother_age > 0
    AND plurality > 0
    AND weight_pounds > 0
LIMIT 25
Explanation: More advanced...
In the original example, we were taking into account the idea that if no ultrasound has been performed, some of the features (e.g. is_male) will not be known. Therefore, we augmented the dataset with such masked features and trained a single model to deal with both these scenarios.
In addition, during data exploration, we learned that the data size set for mothers older than 45 was quite sparse, so we will discretize the mother age.
End of explanation
%%bigquery --project $PROJECT
SELECT
    weight_pounds,
    'Unknown' AS is_male,
    IF(mother_age < 18, 'LOW',
         IF(mother_age > 45, 'HIGH',
            CAST(mother_age AS STRING))) AS mother_age,
    IF(plurality > 1, 'Multiple', 'Single') AS plurality,
    CAST(gestation_weeks AS STRING) AS gestation_weeks,
    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth
  FROM
    publicdata.samples.natality
  WHERE
    year > 2000
    AND gestation_weeks > 0
    AND mother_age > 0
    AND plurality > 0
    AND weight_pounds > 0
LIMIT 25
Explanation: On the same dataset, will also suppose that it is unknown whether the child is male or female (on the same dataset) to simulate that an ultrasound was not been performed.
End of explanation
%%bigquery --project $PROJECT
WITH with_ultrasound AS (
  SELECT
    weight_pounds,
    CAST(is_male AS STRING) AS is_male,
    IF(mother_age < 18, 'LOW',
         IF(mother_age > 45, 'HIGH',
            CAST(mother_age AS STRING))) AS mother_age,
    CAST(plurality AS STRING) AS plurality,
    CAST(gestation_weeks AS STRING) AS gestation_weeks,
    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth
  FROM
    publicdata.samples.natality
  WHERE
    year > 2000
    AND gestation_weeks > 0
    AND mother_age > 0
    AND plurality > 0
    AND weight_pounds > 0
),
without_ultrasound AS (
  SELECT
    weight_pounds,
    'Unknown' AS is_male,
    IF(mother_age < 18, 'LOW',
         IF(mother_age > 45, 'HIGH',
            CAST(mother_age AS STRING))) AS mother_age,
    IF(plurality > 1, 'Multiple', 'Single') AS plurality,
    CAST(gestation_weeks AS STRING) AS gestation_weeks,
    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth
  FROM
    publicdata.samples.natality
  WHERE
    year > 2000
    AND gestation_weeks > 0
    AND mother_age > 0
    AND plurality > 0
    AND weight_pounds > 0
),
preprocessed AS (
  SELECT * from with_ultrasound
  UNION ALL
  SELECT * from without_ultrasound
)
SELECT
    weight_pounds,
    is_male,
    mother_age,
    plurality,
    gestation_weeks
FROM
    preprocessed
WHERE
  ABS(MOD(hashmonth, 4)) < 3
LIMIT 25
Explanation: Bringing these two separate data sets together, there is now a dataset for male or female children determined with ultrasound or unknown if without.
End of explanation
%%bigquery --project $PROJECT
# TODO: Your code goes here
  
WITH with_ultrasound AS (
  SELECT
    weight_pounds,
    CAST(is_male AS STRING) AS is_male,
    IF(mother_age < 18, 'LOW',
         IF(mother_age > 45, 'HIGH',
            CAST(mother_age AS STRING))) AS mother_age,
    CAST(plurality AS STRING) AS plurality,
    CAST(gestation_weeks AS STRING) AS gestation_weeks,
    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth
  FROM
    publicdata.samples.natality
  WHERE
    year > 2000
    AND gestation_weeks > 0
    AND mother_age > 0
    AND plurality > 0
    AND weight_pounds > 0
),
without_ultrasound AS (
  SELECT
    weight_pounds,
    'Unknown' AS is_male,
    IF(mother_age < 18, 'LOW',
         IF(mother_age > 45, 'HIGH',
            CAST(mother_age AS STRING))) AS mother_age,
    IF(plurality > 1, 'Multiple', 'Single') AS plurality,
    CAST(gestation_weeks AS STRING) AS gestation_weeks,
    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth
  FROM
    publicdata.samples.natality
  WHERE
    year > 2000
    AND gestation_weeks > 0
    AND mother_age > 0
    AND plurality > 0
    AND weight_pounds > 0
),
preprocessed AS (
  SELECT * from with_ultrasound
  UNION ALL
  SELECT * from without_ultrasound
)
SELECT
    weight_pounds,
    is_male,
    mother_age,
    plurality,
    gestation_weeks
FROM
    preprocessed
WHERE
  ABS(MOD(hashmonth, 4)) < 3
Explanation: Create a new model
With a data set which has been feature engineered, it is ready to create model with the CREATE or REPLACE MODEL statement
This will take 5-10 minutes and will show Done when complete.
Exercise 4
As in Exercise 1 above, below you are asked to complete the TODO in the cell below to train a linear regression model in BigQuery using weight_pounds as the label. This time, since we're using the supplemented dataset containing without_ultrasound data, name your model babyweight_model_fc. This model will reside within the demo dataset. 
Have a look at the documentation for CREATE MODEL in BQML to see examples of the correct syntax.
End of explanation
bq = bigquery.Client(project=PROJECT)
df = # TODO: Your code goes here
# plot both lines in same graph
import matplotlib.pyplot as plt
plt.plot( 'iteration', 'loss', data=df, marker='o', color='orange', linewidth=2)
plt.plot( 'iteration', 'eval_loss', data=df, marker='', color='green', linewidth=2, linestyle='dashed')
plt.xlabel('iteration')
plt.ylabel('loss')
plt.legend();
Explanation: Training Statistics
While the new model is training, review the training statistics in the BigQuery UI to see the below model training: https://bigquery.cloud.google.com/
The training details may also be viewed after the training completes from this notebook.
Exercise 5
Just as in Exercise 2 above, let's plot the train and eval curve using the TRAINING_INFO from the model training job for the babyweight_model_fc model we trained above. Complete the TODO to create a Pandas dataframe that has the TRAINING_INFO from the training job.
End of explanation
%%bigquery --project $PROJECT
SELECT
  *
FROM
  # TODO: Your code goes here
Explanation: Make a prediction with the new model
Perhaps it is of interest to make a prediction of the baby's weight given a number of other factors: Male, Mother is 28 years old, Mother will only have one child, and the baby was born after 38 weeks of pregnancy.
To make this prediction, these values will be passed into the SELECT statement.
Exercise 6
Use your newly trained babyweight_model_fc to predict the birth weight of a baby that have the following characteristics
- the baby is male
- the mother's age is 28
- there are not multiple babies (i.e., no twins, triplets, etc)
- the baby had 38 weeks of gestation
End of explanation
<END_TASK>