%md

# [SDS-2.2, Scalable Data Science](https://lamastex.github.io/scalable-data-science/sds/2/2/)

%md
### Diamonds ML Pipeline Workflow - DataFrame ETL and EDA Part

This is the Spark SQL parts that are focussed on extract-transform-Load (ETL) and exploratory-data-analysis (EDA) parts of an end-to-end example of a Machine Learning (ML) workflow.

**Why are we using DataFrames?** *This is because of the **Announcement** in the Spark MLlib Main Guide for Spark 2.2* [https://spark.apache.org/docs/latest/ml-guide.html](https://spark.apache.org/docs/latest/ml-guide.html) that *"DataFrame-based API is primary API"*.

This notebook is a scala*rific* break-down of the python*ic* 'Diamonds ML Pipeline Workflow' from the Databricks Guide.

**We will see this example again in the sequel**

For this example, we analyze the Diamonds dataset from the R Datasets hosted on DBC.  

Later on, we will use the [DecisionTree algorithm](http://spark.apache.org/docs/latest/ml-classification-regression.html#decision-trees) to predict the price of a diamond from its characteristics.

Here is an outline of our pipeline:

* **Step 1. *Load data*: Load data as DataFrame**
* **Step 2. *Understand the data*: Compute statistics and create visualizations to get a better understanding of the data.**
* Step 3. *Hold out data*: Split the data randomly into training and test sets.  We will not look at the test data until *after* learning.
* Step 4. On the training dataset:
  * *Extract features*: We will index categorical (String-valued) features so that DecisionTree can handle them.
  * *Learn a model*: Run DecisionTree to learn how to predict a diamond's price from a description of the diamond.
  * *Tune the model*: Tune the tree depth (complexity) using the training data.  (This process is also called *model selection*.)
* Step 5. *Evaluate the model*: Now look at the test dataset.  Compare the initial model with the tuned model to see the benefit of tuning parameters.
* Step 6. *Understand the model*: We will examine the learned model and results to gain further insight.

In this notebook, we will only cover **Step 1** and **Step 2.** above. The other Steps will be revisited in the sequel.

%md
### Step 1. Load data as DataFrame

This section loads a dataset as a DataFrame and examines a few rows of it to understand the schema.

For more info, see the DB guide on [importing data](https://docs.databricks.com/user-guide/importing-data.html).

%md
*Note:* `(nullable = true)` simply means if the value is allowed to be `null`.

Let us count the number of rows in ``diamondsDF``.

%md
So there are 53940 records or rows in the DataFrame.

Use the `show(n)` method to see the first `n` (default is 20) rows of the DataFrame, as folows:

%md
If you notice the schema of `diamondsRawDF` you will see that the automatic schema inference of `SqlContext.read` method has cast the values in the column `price` as `integer`. 

To cleanup:

* let's recast the column `price` as `double` for downstream ML tasks later and 
* let's also get rid of the first column of row indices. 

%md
### Step 2. Understand the data

Let's examine the data to get a better understanding of what is there.  We only examine a couple of features (columns), but it gives an idea of the type of exploration you might do to understand a new dataset.

For more examples of using Databricks's visualization (even across languages) see [https://docs.databricks.com/user-guide/visualizations/index.html](https://docs.databricks.com/user-guide/visualizations/index.html) NOW.

%md 
We can see that we have a mix of 

* categorical features (`cut`, `color`, `clarity`) and 
* continuous features (`depth`, `x`, `y`, `z`).  

##### Let's first look at the categorical features.

%md
You can also select one or more individual columns using so-called DataFrame API. 

Let us `select` the column `cut` from `diamondsDF` and create a new DataFrame called `cutsDF` and then display it as follows:

%md
Let us use `distinct` to find the distinct types of `cut`'s in the dataset.