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

%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. 

%md 
Clearly, there are just 5 kinds of cuts.

%md
We can examine the distribution of a particular feature by using display(), 

**You Try!**

1. Click on the chart icon and Plot Options, and setting:
 * Value=`<id>` 
 * Series groupings='cut'
 * and Aggregation=`COUNT`.
2. You can also try this using columns "color" and "clarity"

%md
** You Try!**

Now play around with display of the entire DF and choosing what you want in the GUI as opposed to a `.select(...)` statement earlier.

For instance, the following `display(diamondsDF)` shows the counts of the colors by choosing in the `Plot Options` a `bar-chart` that is `grouped` with `Series Grouping` as `color`, `values` as `<id>` and `Aggregation` as `COUNT`. You can click on `Plot Options` to see these settings and can change them as you wish by dragging and dropping. 

%md 
Now let's examine one of the continuous features as an example.

%md 
The above histogram of the diamonds' carat ratings shows that carats have a skewed distribution: Many diamonds are small, but there are a number of diamonds in the dataset which are much larger.  

* Extremely skewed distributions can cause problems for some algorithms (e.g., Linear Regression).  
* However, Decision Trees handle skewed distributions very naturally.

Note: When you call `display` to create a histogram like that above, **it will plot using a subsample from the dataset** (for efficiency), but you can plot using the full dataset by selecting "Plot over all results".  For our dataset, the two plots can actually look very different due to the long-tailed distribution.

We will not examine the label distribution for now.  It can be helpful to examine the label distribution, but it is best to do so only on the training set, not on the test set which we will hold out for evaluation.  These will be seen in the sequel

%md
**You Try!**
Of course knock youself out visually exploring the dataset more...

%md
Try scatter plot to see pairwise scatter plots of continuous features.

%md
Note that columns of type string are not in the scatter plot!

%md
### Let us run through some basic inteactive SQL queries next
* HiveQL supports =, <, >, <=, >= and != operators. It also supports LIKE operator for fuzzy matching of Strings
* Enclose Strings in single quotes
* Multiple conditions can be combined using `and` and `or`
* Enclose conditions in `()` for precedence
* ...
* ...

**Why do I need to learn interactive SQL queries?**

Such queries in the widely known declarative SQL language can help us explore the data and thereby inform the modeling process!!!

%md
Using DataFrame API, we can apply a `filter` after `select` to transform the DataFrame `diamondsDF` to the new DataFrame `diamondsDColoredDF`.

Below, `$` is an alias for column. 

Let as select the columns named `carat`, `colour`, `price` where `color` value is equal to `D`.

%md
As you can see all the colors are now 'D'. But to really confirm this we can do the following for fun:

%md
Let's try to do the same in SQL for those who know SQL from before.

First we need to see if the table is registerd (not just the DataFrame), and if not we ened to register our DataFrame as a temporary table.

%md
Looks like diamonds is already there (if not just execute the following cell).

%md
Alternatively, one could just write the SQL statement in scala to create a new DataFrame `diamondsDColoredDF_FromTable` from the table `diamonds` and display it, as follows: