This notebook will take you through the process of creating a predictive model in Python using the data manipulation and machine learning libraries distributed with Spark.

• The data we are using here is an open source dataset prepared for a machine learning challenge hosted on the Kaggle website.

Once we've worked through the process of reading, understanding and preparing our data and have built a simple model together, we'll deploy it to the Watson Machine Learning service and make it available as a real-time scoring service.

You should spend the remaining time working as a group to speculate on how you might improve these predictions. The cognihack tutors will endeavour to assist with any experimentation to help you create and evaluate refinements to the baseline model.

Learning goals

The learning goals of this exercise are:

• Loading CSV files into an Apache® Spark DataFrame.
• Exploring the data using the features within:
  a) Spark's data wrangling Python API: pyspark.sql;
  b) the pandas data wrangling library; and
  c) matplotlib for exploratory plots.
• Engineering some basic predictive features, again using pyspark.sql and Spark user defined functions (UDFs).
• Preparing the data for training and evaluation.
• Creating an Apache® Spark machine learning pipeline.
• Training and evaluating a model.
• Persisting a pipeline and model in Watson Machine Learning repository.
• Deploying the model for online scoring using Wastson Machine Learning API.
• Scoring sample scoring data using the Watson Machine Learning API.

Contents

This notebook contains the following parts:

1. Setup
2. Load and understand data
3. Prepare dataset
4. Create a basic model
5. Deploy and score
6. Taking it further

1. Setup

Before we begin working through this notebook, you must perform the following setup tasks:

• Sign up for the IBM Data Science Experience (using w3 credentials) and create a new project;
• Make sure that you are using a Spark 2.0 kernel and Python 2.x; and
• Create a Watson Machine Learning Service instance (a free plan is offered).

2. Load and explore data

2.1 Load the data

The first step in our analysis process is to bring our data into the Spark environment.
We will do this by reading directly from a Bluemix hosted dashDB instance.

pyspark.sql will help us load and manipulate our data from Python while it resides in Spark.

This last statement will make some aggregation and mutation statements look a little different to the cheat sheet as spark functions will be prefixed by F.

The spark.read function will import data directly into our spark instance, without touching the environment in which Python is running.

When this is done, we are left with a Python object which is a logical pointer to a Spark DataFrame object.
Notice as well that Spark will give us some information on how it is breaking down the heavy lifting into discrete and parallelisable tasks.

2.2 Quality and completeness

Before we can get stuck into building predictive models, we need to first understand if any immediate attention is required to issues of data quality and completeness.

Like many data manipulation libraries, Spark makes its own decisions about how to read in data.
To check it has treated our data as we expected, we need to compare the schemata of our Spark DataFrames to that provided with the datasets:

history
id - A unique id representing a customer
chain - An integer representing a store chain
offer - An id representing a certain offer
market - An id representing a geographical region
repeattrips - The number of times the customer made a repeat purchase
repeater - A boolean, equal to repeattrips > 0
offerdate - The date a customer received the offer

transactions
id - see above
chain - see above
dept - An aggregate grouping of the Category (e.g. water)
category - The product category (e.g. sparkling water)
company - An id of the company that sells the item
brand - An id of the brand to which the item belongs
date - The date of purchase
productsize - The amount of the product purchase (e.g. 16 oz of water)
productmeasure - The units of the product purchase (e.g. ounces)
purchasequantity - The number of units purchased
purchaseamount - The dollar amount of the purchase

offers
offer - see above
category - see above
quantity - The number of units one must purchase to get the discount
company - see above
offervalue - The dollar value of the offer
brand - see above

The transactions file can be joined to the history file by (id,chain). The history file can be joined to the offers file by (offer). The transactions file can be joined to the offers file by (category, brand, company). A negative value in productquantity and purchaseamount indicates a return.

While we're at it, let's also see how many observations each dataset has, take a peek at the data and look for any missing values.

2.2.1 Offer details

First up, the count of observations in the dataset.

A small, reference dataset.

Next question: how is the data typed within the Spark DataFrame?

Spark has interpreted every field as a string. 🙄

There are two fields: offervalue and quantity that definitely should not be strings - let's fix them up now.

Now we're ready to take a peak at the data.

And finally, check for any records with a missing value in critical fields.

2.2.2 Customer-Offer History

Can you repeat the same operations here?

1. Count the records;
2. Inspect the type schema;
3. Convert miss-typed fields using withColumn() and cast();
4. Inspect the data; and
5. Check the dataset for missing values.

A larger dataset that relates offers and customers.

Same deal. Let's do some conversion to numeric and datetime types.

2.2.3 Transactions

The largest of the three datasets.

2.3 Exploration

Let's begin interpreting the contents of these sets, starting with the range of dates over which the offers were presented to customers.

What is the frequency of records across some of the categorical variables?

Probably too many of these to do anything useful without grouping.

Try for yourself with the market variable.

Slightly more usable, perhaps we'll come back to this.

First insight: 27% of customers to whom an offer is made become repeat shoppers. Does this vary across market?

There's a hypothesis emerging here that our larger markets may show the strongest response to offers. Let's plot it to check.

There is a weak relationship there, but we probably won't want to employ something as nuanced as this in our first iteration of analysis. Interesting to know though!

We understand a little bit of the offer history data. Let's just check for missing values.

Now we need to repeat the process across our other datasets. Let's start with the offers.

Looks as though a small number of the offers have a different quantity value.

Interesting. Is this still applicable when we join offers to our history dataset?

No. In which case, it's not going to be significant for our analysis and modelling.
Do any of the categorical fields have few enough levels to enter into a simple classification model?

These might work in a classification tree model which automatically groups, or as a binned aggregate measure of the response rate across each.
Let's move on to the transactions.

What is the range of dates of our transactions?

Right, so up to a year before the offers were presented.
What are the ranges of purchasequantity and purchaseamount?

OK, we have some returns data in here, too. We may need to account for this in a future iteration of modelling work.

2.4 Activity 2: Data understanding learning challenges

2.4.1 Data understanding learning challenge 1

Is there any pattern to the number of records in our history dataset by offerdate? Try plotting a graph to investigate.

The graph above is a little messy and difficult to interpret.
Let's use a very quick and dirty approach to extracting the month, plot that and see if this is any easier to interpret.

We are now able to discriminate between the periods in our analysis, but we've lost the interesting pattern we saw before.

Extension tasks

1. Try producing a summary of counts by week.
   Hint: take a look at the resample() function within Pandas or the pyspark.sql.functions functions next_day() or weekofyear().
2. Is there a pattern relating to the day of the week offers are sent out? Is this an important indicator in response likelihood?
   Hint: pyspark.sql.functions.date_format() may be helpful.

2.4.2 Data understanding learning challenge 2

In the data understanding phase, we run lots of operations against our data, and we need these to be optimised in order to complete the task in a timely fashion. In our case, the key differentiator is whether the function requires creation of a Python object (e.g. using Pandas), or whether it can run on a Spark data frame. Let's try using Pandas' implementation of 'describe'

Pyspark.sql also has an implementation of describe(), which we saw earlier. Note: be careful which order you run code, as you may need to declare which module to use a function from. Let's try to use a different set of Pyspark functionality to find the range of our continuous variables.

From the exercise, it looks like there is something strange in one of the columns. Are all of the values positive and with a reasonable range? Let's take a look at some of the negative values

3. Data preparation

The output of this phase is a dataset with which we can build and test models.

3.1 Summarising data for use in modelling

Given that the aim of the task is to make customer-offer level predictions about likelihood to become a repeat purchaser, data that we use from the offers or transactions datasets will need to be joined to the history dataset.
We have also observed that the transactions dataset contains a large volume of datat, too much to enter into a model without aggregation to the customer, offer level. In aggregating this, our goal is to select an approach which generates features that:

a) retain as much information about the behaviour of these customers as possible; and
b) will be usable in our model (some algorithms can only accept numerical inputs, for example).

As a starter set, we will simply measure how much each customer had spent in the 30, 60, 90 and 180 days prior to being made an offer.
To do so, we will first need to join the offer history and transactions tables.

Calculate "history" interval dates based on offerdate.

We can employ a Spark "user defined function" to create corresponding aggregation flags to identify whether the transaction in scope of one of the history periods.

At this point we can calculate the quantity and spend per customer per offer.
As an extension, you could join onto the offers table and create equivalent measures for quantity and spend in the same brand, company and category as the offer presented to the customer.

3.2 Reshaping data

What is the average spend in these intervals?
Spark will allow us to calculate this quite easily. In order to plot this nicely, we will need help from the Python data wrangling library of choice: Pandas. Luckily Spark also offers an easy way to translate between the two types of object using the .toPandas() function.

3.3 Divertissement: interactive charting

(Maybe useful for the hack later...)

If we want to add some interactivity to our charts, one great option is the Bokeh library.

This gives us a very small level of interaction - but much more is possible!
You may have to move outside of the notebook environment to do this though (using output_file, for example).
Bokeh interactivity docs and examples

3.4 Activity 3: Data prep learning challenges

3.4.1 Data prep learning challenge 1

Let's apply some of what we've looked at together and engineer a new feature that may or may not be predictive and useful, but will at least give us some experience of working with the summarisation capability of pyspark.sql.

What we are looking to do is examine which departments customers have shopped into as a way of classifying their habits.

Let's start by identifying the five most popular departments in our transactions set. We can sample our data to achieve this quickly.

We can use these to create a series of flags at the customer level which may be useful in the classification task. The task here is to create a flag for every customer, showing whether they have shopped in each of: department 99, department 35, 37, 56 and 26.

Inspect and generate some summary statistics for these fields.

Last piece of the puzzle: let's measure the level of correlation of these predictors.
(If everybody buys from the same departments then these will not be good predictor variables to use in a model.)

Pandas has a very slick way of doing this via. the .corr() function, but what do we need to do first to allow us to use our summary data?

Qs.

1. Are these good variables to add into the model?
2. How else could we test their suitability?
3. If we built these flags for a hundred departments: how could we use this information but still only add a small number of additional predictors into our model?

3.4.2 Data prep learning challenge 2

How could we add a new column to our offer history data, expressing the rank of the chain (by number of customers) within the market?

Wrangling pattern:

1. Summarise customer counts by chain and market;
2. Apply the rank() function to customer counts by market; then
3. Join back to original dataframe.

Starting with (1) - can you use the agg() function to summarise the data appropriately?

• Name your column of counts: customer_count
• Name your new dataframe: historyCustCounts

Moving on to (2), let's calculate a new column chain_market_rank within historyCustCount showing the ranking of chains by customer count within each market.
Hint: as this is a window function (calculates a quantity within a partition, in this case market), you'll need to specify the window specification using Window which is available in the pyspark.sql.window library.

Is this a good variable to use in our model? Let's plot the distribution of values as a histogram.

Again, Pandas has a very neat function: .hist() that allows us to plot histograms quickly.

Do you think this would be informative?
What other analysis could you do to support your assertion?

For completeness, let's go ahead and join this back to our original trainHistory dataframe.

We don't need customer_count, let's go ahead and drop it.

3.4.3 Data prep learning challenge 3

This challenge is more open ended: you will work from a specification to produce a new predictor variable (i.e. without direction from the script).

This is the specification you have been provided:

Calculating "Customer first transaction interval"

We are hoping to create a variable to encapsulate information about how long a customer began shopping with the company prior to being offered an incentive.
To do this, you will need to:

1. Find the first transaction for each customer in the transactions dataset;
2. Compare that to the offerdate in the offer history dataset and calculate the number of days between the two (the datediff() function will help); and
3. Plot (or otherwise analyse) the distribution and make a decision about whether you would include this in a model. You might consider sampling your data before plotting.

4. Modelling experiments

We're now ready to have a first pass at building a model.

4.1 Holdout partitioning

For those who have worked in targeted marketing, this approach will be quite familiar. The premise is to train a model based on one part of your dataset and evaluate its performance using the other.
Spark has a convenience function to do just that: randomSplit(). The arguments supplied to this function is an array of weights specifying the proportions by which the dataset should be split.

Let's also cache these datafrmaes like we did with our original data sets.

Spark has an idiosyncratic way of taking input and we will have to employ the 'VectorAssembler' function to bundle up the features for us to input into the model.

We now have a fitted model!
What values have been chosen for the parameters?

Not bad, the competition benchmark for this dataset is 0.59.
Before we start extending this, we should perform some diagnositcs.

First, let's look at where the misclassifications are occuring by creating a contingency table (a.k.a confusion matrix).

What does this tell us? Is this a bad model?

And of course we should check if our model generalises well by scoring and evaluating the test set.

4.2 Modeling Exercises

4.2.1 Exercise 1: Scoring on the test set

Go ahead and use the pipeline we've already developed to transform the test set data. When you've done that, plot a ROC curve for the predictions and measure the AUC. Discuss and draw some conclusions about whether the model is generalising well.

Build a confusion matrix to calculate rate of misclassification

4.2.2 Exercise 2: Creating a Decision Tree

We've seen how to create a logistic regression model. However, this is a parametric model and requires making some distributional assumptions about our data. In some cases this is not appropriate and we need to use a non-parametric method. Let's go through the same approach using pyspark.ml.classification, and fit a decision tree.
Slight additional complexity here: regardless of the original data type of the target variable, this algorithm requires you to have processed it with StringIndexer (from pyspark.ml.feature) prior to sending it to the model. Hence we have an additional stage to this pipeline.

The first thing we did with our logistic regression was to look at the parameter values. There is no equivalent for decision trees. Instead, there is a featureImportances object which will give us similar useful information about the model. Extract it from the pipeline in the same way we did for the coefficients of our logistic regression.

Go ahead and measure the AUC metric as before using the trains and test sets.

Now, there is a stochastic element to the building of these models, but in preparation for Cognihack, something felt strange about the AUC we were getting. Have a chat with your teams about why this may be the case, in particular in the context of parallel computing.

Extension:

• Try recreating the decision tree model, carefully selecting your features.
• What about any more data derivations you could include
• Try selecting the parameters of the decision tree, such as depth and minimum split size
• Consider other classification algorithms, either from pyspark.ml or elsewhere

5. Deploy and score

With the advent of Watson Machine Learning, we can quite easily deploy our model to a cloud scoring service.

5.1 Persist Spark model within the ML repository

The first step here is to import the relevant client libraries.

Tip: service_path, user and password can be found on Service Credentials tab of service instance created in Bluemix.

For example, the following code:

wml_service_path = "https://ibm-watson-ml.mybluemix.net"  
wml_username = "ebecda6c-a18b-4c6f-82e4-4c7fc26361f4"  
wml_password = "4705d497-fcc0-4e1c-9f55-934b13b13fb2"

Will create the necessary credentials to connect to the Watson ML service. Just substitute in your own in the place of these example values.

Create model artifact (abstraction layer).

Tip: The MLRepositoryArtifact method expects a trained model object, training data, and a model name. (It is this model name that is displayed by the Watson Machine Learning service).

We can now save our model to the repository.

Get saved model metadata from Watson Machine Learning.
Tip: Use meta.available_props() to get the list of available props.

Tip: modelVersionHref is our model unique indentifier in the Watson Machine Learning repository.

5.2 Create an online scoring endpoint

In this section you will learn how to create online scoring and to score a new data record by using the Watson Machine Learning REST API.
For more information about REST APIs, see the Swagger Documentation.
To work with the Watson Machine Leraning REST API you must generate an access token. To do that you can use the following sample code:

You can now create an online scoring endpoint. Execute the following sample code that uses the modelVersionHref value to create the scoring endpoint to the Bluemix repository.

Let's see what happens when we send a PUT request to our new endpoint containing a new scoring record. The model should hopefully return some predictions.