<jupyter_start><jupyter_text>Creating a Sampled Dataset**Learning Objectives**- Sample the natality dataset to create train/eval/test sets- Preprocess the data in Pandas dataframe  IntroductionIn this notebook we'll read data from BigQuery into our notebook to preprocess the data within a Pandas dataframe.<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>Create ML datasets by sampling using BigQueryWe'll begin by sampling the BigQuery data to create smaller datasets.<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
"""<jupyter_output><empty_output><jupyter_text>There are only a limited number of years and months in the dataset. Let's see what the hashmonths are.We'll call BigQuery but group by the hashmonth and see the number of records for each group. This will enable us to get the correct train/eval/test percentages<jupyter_code>from google.cloud import bigquery
bq = bigquery.Client(project=PROJECT)

df = bq.query("SELECT hashmonth, COUNT(weight_pounds) AS num_babies FROM (" 
              + query_string + 
              ") GROUP BY hashmonth").to_dataframe()

print("There are {} unique hashmonths.".format(len(df)))
df.head()<jupyter_output><empty_output><jupyter_text>Here's a way to get a well-distributed portion of the data in such a way that the train/eval/test sets do not overlap.<jupyter_code># Added the RAND() so that we can now subsample from each of the hashmonths to get approximately the record counts we want
train_query = "SELECT * FROM (" + query_string + ") WHERE MOD(hashmonth, 100) < 80 AND RAND() < 0.0005"
eval_query = "SELECT * FROM (" + query_string + ") WHERE MOD(hashmonth, 100) >= 80 AND MOD(hashmonth, 100) < 90  AND RAND() < 0.0005"
test_query = "SELECT * FROM (" + query_string + ") WHERE MOD(hashmonth, 100) >= 90 AND RAND() < 0.0005"

train_df = bq.query(train_query).to_dataframe()
eval_df = bq.query(eval_query).to_dataframe()
test_df = bq.query(test_query).to_dataframe()

print("There are {} examples in the train dataset.".format(len(train_df)))
print("There are {} examples in the validation dataset.".format(len(eval_df)))
print("There are {} examples in the test dataset.".format(len(test_df)))<jupyter_output><empty_output><jupyter_text>Preprocess data using PandasWe'll perform a few preprocessing steps to the data in our dataset. Let's add extra rows to simulate the lack of ultrasound. That is we'll duplicate some rows and make the `is_male` field be `Unknown`. Also, if there is more than child we'll change the `plurality` to `Multiple(2+)`. While we're at it, We'll also change the plurality column to be a string. We'll perform these operations below. Let's start by examining the training dataset as is.<jupyter_code>train_df.head()<jupyter_output><empty_output><jupyter_text>Also, notice that there are some very important numeric fields that are missing in some rows (the count in Pandas doesn't count missing data)<jupyter_code>train_df.describe()<jupyter_output><empty_output><jupyter_text>It is always crucial to clean raw data before using in machine learning, so we have a preprocessing step. We'll define a `preprocess` function below. Note that the mother's age is an input to our model so users will have to provide the mother's age; otherwise, our service won't work. The features we use for our model were chosen because they are such good predictors and because they are easy enough to collect.<jupyter_code>import pandas as pd

def preprocess(df):
  # clean up data
  # remove what we don't want to use for training
  df = df[df.weight_pounds > 0]
  df = df[df.mother_age > 0]
  df = df[df.gestation_weeks > 0]
  df = df[df.plurality > 0]
  
  # modify plurality field to be a string
  twins_etc = dict(zip([1,2,3,4,5],
                   ['Single(1)', 'Twins(2)', 'Triplets(3)', 'Quadruplets(4)', 'Quintuplets(5)']))
  df['plurality'].replace(twins_etc, inplace=True)
  
  # now create extra rows to simulate lack of ultrasound
  no_ultrasound = df.copy(deep=True)
  no_ultrasound.loc[no_ultrasound['plurality'] != 'Single(1)', 'plurality'] = 'Multiple(2+)'
  no_ultrasound['is_male'] = 'Unknown'
  
  return pd.concat([df, no_ultrasound])<jupyter_output><empty_output><jupyter_text>Let's process the train/eval/test set and see a small sample of the training data after our preprocessing:<jupyter_code>train_df = preprocess(train_df)
eval_df = preprocess(eval_df)
test_df = preprocess(test_df)
train_df.head()
train_df.tail()<jupyter_output><empty_output><jupyter_text>Let's look again at a summary of the dataset. Note that we only see numeric columns, so `plurality` does not show up.<jupyter_code>train_df.describe()<jupyter_output><empty_output><jupyter_text>Write to .csv files In the final versions, we want to read from files, not Pandas dataframes. So, we write the Pandas dataframes out as csv files. Using csv files gives us the advantage of shuffling during read. This is important for distributed training because some workers might be slower than others, and shuffling the data helps prevent the same data from being assigned to the slow workers.<jupyter_code>train_df.to_csv('train.csv', index=False, header=False)
eval_df.to_csv('eval.csv', index=False, header=False)
test_df.to_csv('test.csv', index=False, header=False)
%%bash
wc -l *.csv
%%bash
head *.csv
%%bash
tail *.csv<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0998191086992271, 'fraction_numerical': 0.010524584772241408, 'mean_word_length': 5.1496461071789685, 'pattern_counts': {'":': 0, '<': 46, '<?xml version=': 0, '>': 48, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '496166', 'license': 'Apache-2.0', 'n_tokens_mistral': 1989, 'n_tokens_neox': 1875, 'n_words': 676, 'path': 'courses/machine_learning/deepdive/05_review/2_sample_dataset.ipynb', 'repo_name': 'anandprabhakar0507/training-data-analyst', 'size': '6081'}