Learning Objectives
In this notebook, we will explore the natality dataset before we begin model development and training to predict the weight of a baby before it is born. We will use BigQuery to explore the data and use Cloud AI Platform Notebooks to plot data explorations.
Each learning objective will correspond to a #TODO in this student lab notebook -- try to complete this notebook first and then review the solution notebook.
Check that the Google BigQuery library is installed and if not, install it.
In [ ]:
%%bash
sudo pip freeze | grep google-cloud-bigquery==1.6.1 || \
sudo pip install google-cloud-bigquery==1.6.1
In [ ]:
from google.cloud import bigquery
Our dataset is hosted in BigQuery. The CDC's Natality data has details on US births from 1969 to 2008 and is a publically available dataset, meaning anyone with a GCP account has access. Click here to access the dataset.
The natality dataset is relatively large at almost 138 million rows and 31 columns, but simple to understand. weight_pounds is the target, the continuous value we’ll train a model to predict.
The data is natality data (record of births in the US). The goal is to predict the baby's weight given a number of factors about the pregnancy and the baby's mother. Later, we will want to split the data into training and eval datasets. The hash of the year-month will be used for that -- this way, twins born on the same day won't end up in different cuts of the data. We'll first create a SQL query using the natality data after the year 2000.
In [ ]:
query = """
SELECT
weight_pounds,
is_male,
mother_age,
plurality,
gestation_weeks,
FARM_FINGERPRINT(
CONCAT(
CAST(YEAR AS STRING),
CAST(month AS STRING)
)
) AS hashmonth
FROM
publicdata.samples.natality
WHERE
year > 2000
"""
Let's create a BigQuery client that we can use throughout the notebook.
In [ ]:
bq = bigquery.Client()
Let's now examine the result of a BiqQuery call in a Pandas DataFrame using our newly created client.
In [ ]:
# Call BigQuery and examine in dataframe
df = bigquery.Client().query(query + " LIMIT 100").to_dataframe()
df.head()
First, let's get the set of all valid column names in the natality dataset. We can do this by accessing the INFORMATION_SCHEMA for the table from the dataset.
In [ ]:
# Query to get all column names within table schema
sql = """
SELECT
column_name
FROM
publicdata.samples.INFORMATION_SCHEMA.COLUMNS
WHERE
table_name = "natality"
"""
# Send query through BigQuery client and store output to a dataframe
valid_columns_df = bq.query(sql).to_dataframe()
# Convert column names in dataframe to a set
valid_columns_set = valid_columns_df["column_name"].tolist()
We can print our valid columns set to see all of the possible columns we have available in the dataset. Of course, you could also find this information by going to the Schema tab when selecting the table in the BigQuery UI.
In [ ]:
print(valid_columns_set)
Using the above code as an example, write a query to find the unique values for each of the columns and the count of those values for babies born after the year 2000. For example, we want to get these values:
is_male num_babies avg_wt False 16245054 7.104715 True 17026860 7.349797This is important to ensure that we have enough examples of each data value, and to verify our hunch that the parameter has predictive value.
Hint (highlight to see):
Use COUNT(), AVG() and GROUP BY. For example:
SELECT is_male, COUNT(1) AS num_babies, AVG(weight_pounds) AS avg_wt FROM publicdata.samples.natality WHERE year > 2000 GROUP BY is_male</p>
In [ ]:
# TODO: Create function that gets distinct value statistics from BigQuery
def get_distinct_values(valid_columns_set, column_name):
"""Gets distinct value statistics of BigQuery data column.
Args:
valid_columns_set: set, the set of all possible valid column names in
table.
column_name: str, name of column in BigQuery.
Returns:
Dataframe of unique values, their counts, and averages.
"""
assert column_name in valid_columns_set, (
"{column_name} is not a valid column_name".format(
column_name=column_name))
sql = """
"""
pass
Which factors seem to play a part in the baby's weight?
Bonus: Draw graphs to illustrate your conclusions
Hint (highlight to see):
# TODO: Reusing the get_distinct_values function you just implemented, create function that plots distinct value statistics from BigQuery
Hint (highlight to see):
The simplest way to plot is to use Pandas' built-in plotting capability
df = get_distinct_values(valid_columns_set, column_name) df = df.sort_values(column_name) df.plot(x=column_name, y="num_babies", kind="bar", figsize=(12, 5)) df.plot(x=column_name, y="avg_wt", kind="bar", figsize=(12, 5))
In [ ]:
# TODO: Create function that plots distinct value statistics from BigQuery
def plot_distinct_values(valid_columns_set, column_name, logy=False):
"""Plots distinct value statistics of BigQuery data column.
Args:
valid_columns_set: set, the set of all possible valid column names in
table.
column_name: str, name of column in BigQuery.
logy: bool, if plotting counts in log scale or not.
"""
pass
Make a bar plot to see is_male with avg_wt linearly scaled and num_babies logarithmically scaled.
In [ ]:
# TODO: Plot is_male
Make a bar plot to see mother_age with avg_wt linearly scaled and num_babies linearly scaled.
In [ ]:
# TODO: Plot mother_age
Make a bar plot to see plurality with avg_wt linearly scaled and num_babies logarithmically scaled.
In [ ]:
# TODO: Plot plurality
Make a bar plot to see gestation_weeks with avg_wt linearly scaled and num_babies logarithmically scaled.
In [ ]:
# TODO: Plot gestation_weeks
All these factors seem to play a part in the baby's weight. Male babies are heavier on average than female babies. Teenaged and older moms tend to have lower-weight babies. Twins, triplets, etc. are lower weight than single births. Preemies weigh in lower as do babies born to single moms. In addition, it is important to check whether you have enough data (number of babies) for each input value. Otherwise, the model prediction against input values that doesn't have enough data may not be reliable.
In the next notebooks, we will develop a machine learning model to combine all of these factors to come up with a prediction of a baby's weight.
Copyright 2019 Google Inc. 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