Cleaning Data: wrangling data for a SageMaker pipeline

Wrangling data into a usable form is a big part of any real world machine learning problem. When tackling these types of problems, several things are generally true:

First, the information to understand and/or solve the proposed business problem is usually stored in multiple locations or files. Whether it's multiple excel files, different tables within the same database, or completely different systems, having to pull information from multiple sources and then combine them is common.

Second, the data is usually in its raw form. This means the data will need to be featurized. Free text needs to be turned into some kind of numeric representation, categories either need to be binned or encoded, numeric features may need to be standardized, lists of values turned into single number representations, or other custom transformations may need to be applied.

I chose the Insurance Company Benchmark (COIL 2000) dataset because it let's me demonstrate some of these real world problems:

1. It's stored in multiple files
2. It has both numeric and categorical features
3. It's a binary classification problem

Objectives

Like a production level problem, this dataset needs to be combined and put into a usable form. Here are the data wrangling steps I'll complete in this post:

• Combine train, test, and label datasets into a single full dataset
  • Production datasets don't usually come pre-split
  • Figuring out when, where, and how to split the data is an important production consideration
• Update column names to something human readable
  • This makes my life easier when completing my exploratory data analysis to understand what the data is
  • Blind data processing is generally a bad idea
• Return categorical features to their textual representation
  • Allows me to demonstrate both numeric and categorical handling as I move through creating a SageMaker Pipeline

Prerequisites

All prerequisites from the previous post, 1_read_from_s3.ipynb, still apply (SageMaker environment and applicable IAM roles are set up). The data that was loaded into S3 in the previous post will also be needed. Below is the code to pull the data from the UCI page and load it to S3 incase you deleted the files from the last post.

# Only run this if you deleted the output from the previous post or didn't run it at all
# If you run it accidentally, oh well, it'll just overwrite the previous files
import pandas as pd
import sagemaker

session = sagemaker.session.Session()
bucket = session.default_bucket()
prefix = '1_ins_dataset/raw'

train_uri = 'https://archive.ics.uci.edu/ml/machine-learning-databases/tic-mld/ticdata2000.txt'
test_uri = 'https://archive.ics.uci.edu/ml/machine-learning-databases/tic-mld/ticeval2000.txt'
gt_uri = 'https://archive.ics.uci.edu/ml/machine-learning-databases/tic-mld/tictgts2000.txt'
cols_uri = 'https://archive.ics.uci.edu/ml/machine-learning-databases/tic-mld/dictionary.txt'

train = pd.read_table(train_uri, header=None)
test = pd.read_table(test_uri, header=None)
ground_truth = pd.read_table(gt_uri, header=None)
columns = pd.read_table(cols_uri, encoding='latin-1')

train.to_csv(f's3://{bucket}/{prefix}/train.csv', index=False)
test.to_csv(f's3://{bucket}/{prefix}/test.csv', index=False)
ground_truth.to_csv(f