<jupyter_start><jupyter_text>Exploring the Natality Dataset **Learning Objectives**- Explore a BigQuery dataset inside Jupyter notebook- Read data from BigQuery into Pandas dataframe- Examine the distribution of average baby weight across various features  IntroductionIn this notebook we'll read data from BigQuery into our notebook to begin some preliminary data exploration of the natality dataset. To beign we'll set environment variables related to our GCP project.<jupyter_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
TFVERSION = '1.12'                # TF version for CMLE to use
import os
os.environ['BUCKET'] = BUCKET
os.environ['PROJECT'] = PROJECT
os.environ['REGION'] = REGION
os.environ['TFVERSION'] = TFVERSION
%%bash
if ! gsutil ls | grep -q gs://${BUCKET}/; then
    gsutil mb -l ${REGION} gs://${BUCKET}
fi<jupyter_output><empty_output><jupyter_text>Explore data The data is natality data (record of births in the US). My 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.<jupyter_code># Create SQL query using natality data after the year 2000
query_string = """
SELECT
  weight_pounds,
  is_male,
  mother_age,
  plurality,
  gestation_weeks,
  ABS(FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING)))) AS hashmonth
FROM
  publicdata.samples.natality
WHERE year > 2000
"""
# Call BigQuery and examine in dataframe
from google.cloud import bigquery
bq = bigquery.Client(project=PROJECT)

df = bq.query(query_string + "LIMIT 100").to_dataframe()
df.head()<jupyter_output><empty_output><jupyter_text>Let's write a query to find the unique values for a given column and see how the number of babies and their average weight is distributed across a those values. This is important to ensure that we have enough examples of each data value, and to verify our hunch that the parameter has predictive value.<jupyter_code>def get_distinct_values(column_name):
  sql_query = """
    SELECT
      {0},
      COUNT(1) AS num_babies,
      AVG(weight_pounds) AS avg_wt
    FROM
      publicdata.samples.natality
    WHERE
      year > 2000
    GROUP BY
      {0}
  """.format(column_name)
  return bq.query(sql_query).to_dataframe()<jupyter_output><empty_output><jupyter_text>We'll use the `get_distinct_values` function above to explore how the variables `num_babies` and `avg_wt` are distributed across the features `is_male`, `mother_age`, `plurality` and `gestation_weeks`<jupyter_code># Bar plot to see is_male with avg_wt linear and num_babies logarithmic
df = get_distinct_values('is_male')
df.plot(x='is_male', y='num_babies', kind='bar');
df.plot(x='is_male', y='avg_wt', kind='bar');
# Line plots to see mother_age with avg_wt linear and num_babies logarithmic
df = get_distinct_values('mother_age')
df = df.sort_values('mother_age')
df.plot(x='mother_age', y='num_babies');
df.plot(x='mother_age', y='avg_wt');
# Bar plot to see plurality(singleton, twins, etc.) with avg_wt linear and num_babies logarithmic
df = get_distinct_values('plurality')
df = df.sort_values('plurality')
df.plot(x='plurality', y='num_babies', logy=True, kind='bar');
df.plot(x='plurality', y='avg_wt', kind='bar');
# Bar plot to see gestation_weeks with avg_wt linear and num_babies logarithmic
df = get_distinct_values('gestation_weeks')
df = df.sort_values('gestation_weeks')
df.plot(x='gestation_weeks', y='num_babies', logy=True, kind='bar');
df.plot(x='gestation_weeks', y='avg_wt', kind='bar');<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10129533678756476, 'fraction_numerical': 0.005699481865284974, 'mean_word_length': 5.298531810766721, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '173544', 'license': 'Apache-2.0', 'n_tokens_mistral': 1287, 'n_tokens_neox': 1221, 'n_words': 415, 'path': 'courses/machine_learning/deepdive/05_review/1_explore_dataset.ipynb', 'repo_name': 'anandprabhakar0507/training-data-analyst', 'size': '3860'}