bb9128fac91692ad4f46a785d772dd39?s=200 avatar

bb9128fac91692ad4f46a785d772dd39?s=200

Data Scientist and Engineer, I love tinkering with data!

Head of Data

Pablo Seibelt

Learn about the R package we use internally at Auth0 for ETL / Data analysis.



Today I'll be presenting [R for Auth0](https://github.com/auth0/rauth0), our internal R package to smoothen our workflow when working with the R language. At Auth0, we use R not only for statistical analysis but also for most of our ETL processing jobs.

## Auth0's Data Technology Stack

First I'll present some insight into our technology stack, we use:

* [R](https://www.r-project.org/) for ETL, Analysis and Machine Learning
* [RStudio](https://www.rstudio.com/) as an interface for working with R
* [Python](https://www.python.org/) as our importer/exporter for external APIs
* [Amazon Redshift](https://aws.amazon.com/redshift/) as our data warehouse
* [Apache Airflow](https://airflow.apache.org/) as our job scheduling system

Except for Amazon Redshift, our stats backend is all open source software.

## Enter `rauth0`

Working with ETL processes every day we noticed some recurring patterns, table loading, upserting, slowly changing dimensions, `ggplot` theming and others, that we could simplify by centralizing in one place. So we have this package which centralizes functionality that we reuse in many processes.

We will walk through some of the most interesting pieces of rauth0 in hopes it'll be useful to build out your own internal R package for your organization, or just personal use. The package is MIT licensed so you can use to adapt and use the code without significant restrictions.

## Plotting Beautifully

We teamed up with Auth0's design team member [Julian Leiss](https://twitter.com/julianleiss) to work out some better defaults for `ggplot2`, in hopes of making our R generated charts better for presentation, and more according to the company's style. Working on this, we defined these palettes for discrete variables:


```r
library(ggplot2)
library(rauth0)

base_plot =
ggplot(diamonds, aes(clarity, fill = cut)) +
geom_bar() +
theme_auth0()

base_plot + scale_fill_auth0_discrete(palette='default') +
  ggtitle('Default palette')
```

![Example graph of Auth0 default palette](https://images.ctfassets.net/23aumh6u8s0i/6j1Qi33oNsiCKj90gb5Al2/1f1041eb6bcd05935747f469bc9dfde6/default-palette)

```r
base_plot + scale_fill_auth0_discrete(palette='sequential') +
  ggtitle('Sequential palette')
```

![Example graph of Auth0 sequential palette](https://images.ctfassets.net/23aumh6u8s0i/1ykVJ3GIXWP6r3qzMlGCjQ/f9aa1301dde98c679df176f404b224f7/sequential-palette)

```r
base_plot + scale_fill_auth0_discrete(palette='colorful') +
  ggtitle('Colorful palette')
```

![Example graph of Auth0 colorful palette](https://images.ctfassets.net/23aumh6u8s0i/6wtPB7Qp3Sr2FskojiWhYh/10dd5b15aa955fbd4d1f3f1c958d1eef/colorful-palette)
And this one for continous variables:


```r
dsamp <- diamonds[sample(nrow(diamonds), 1000), ]
ggplot(dsamp, aes(carat, price)) +
  geom_point(aes(colour = price)) +
  theme_auth0() +
  scale_color_auth0_gradient(midpoint = 10000) +
  labs(title="Continous scale", subtitle='Carat vs price')
```

![Example chart of continous scale](https://images.ctfassets.net/23aumh6u8s0i/6FTD3h3ZGtRNDT6CjThg4m/806963dac484e473f56e65ed67799b5d/continous-scale)

## Accessing the Data Warehouse

As mentioned earlier, we will use Amazon Redshift for accessing the data warehouse (DWH), as such, we use the open source R package `redshiftTools` to upload data into the DWH. Although that is just a piece of the picture, we also need to read data, and there are some repetitive manipulations we do.

For example, many times we may want to connect with `dbplyr` and figure out which images are the most common as a Twitter thumbnail:

```r
library(tidyverse)
library(dbplyr)

con = dwh_connect()

page_metadata = tbl(con, in_schema('prod', 'dim_page_metadata'))
twitter_image_count = filter(page_metadata, page_meta_twitter_image != '<Blank>', scd_end_date == '9999-12-31') %>%
  group_by(page_meta_twitter_image) %>%
  count() %>%
  arrange(desc(n))

knitr::kable(collect(twitter_image_count, n=10), format='markdown') # Top 10
```

|page_meta_twitter_image                                                        |     n|
|-------------------------------------------------------------------------------|------|
|https://cdn.auth0.com/website/authenticate/meta-twitter.png                    | 10186|
|https://cdn.auth0.com/blog/blog/home-twitter-card.png                          |  2465|
|/modules/twittercard/1x-new.png                                                |   687|
|https://cdn.auth0.com/blog/illustrations/auth0.png                             |   339|
|https://cdn.auth0.com/website/assets/modules/twittercard/1x-new-383f8d95d1.png |    67|
|https://cdn.auth0.com/website/assets/modules/opengraph/1x-new-69e09d5054.png   |    61|
|https://auth0.com/learn/wp-content/uploads/2016/01/1x-new.png                  |    59|
|https://cdn.auth0.com/blog/series-c/auth0-logo.png                             |    36|
|https://cdn.auth0.com/blog/angular/logo3.png                                   |    23|
|https://cdn.auth0.com/blog/jwtalgos/logo.png                                   |    12|

The beauty of this is that with `dbplyr` we can also inspect which query is the client running to make this work:

```r
show_query(twitter_image_count)
```

```
## <SQL>
## SELECT "page_meta_twitter_image", COUNT(*) AS "n"
## FROM prod.dim_page_metadata
## WHERE (("page_meta_twitter_image" != '<Blank>') AND ("scd_end_date" = '9999-12-31'))
## GROUP BY "page_meta_twitter_image"
## ORDER BY "n" DESC
```

In the same spirit of data manipulation, we have Redshift compatible functions to run arbitrary queries and statements, create views, replace tables with data or with an SQL query, etc. I can't stress enough how painlessly it is to work with R and Amazon Redshift with these tools available, especially "dbplyr".

### Kimball methods

We also have in this library, functions to update SCD type 1 and type 2 dimensions, I invite you to look at functions `dwh_update_scd_type1`, `dwh_update_scd_type1_in_database` and `dwh_update_scd_type2`, they are quite complex but worth the effort because of the problems they solve.

They might have some edge cases not considered in our current use cases and will evolve over time. This can be considered an alternative to buying a full-fledged ETL tool without having to use a graphical interface for designing the data flows, with its advantages and disadvantages.

### A real clickstream data example

For example, this is the real production code for building the sessions dimension for clickstream data, there is another process that generates the staging tables (`stg_session_events` with a row every pageview/event and `stg_sessions` has one only row per session).

```
library(rauth0)
library(tidyverse)
library(dbplyr)

con = dwh_connect()
all_events = tbl(con, 'events_metrics') %>% filter(received > '2012-01-01')

# Only use pageviews, tracked events and identify calls
filteredEvents = filter(all_events, type %in% c('track', 'page', 'identify'), sql("url_protocol(url)") %in% c('http', 'https'))


dim_ref = tbl(con, 'stg_sessions')
map_ref = tbl(con, 'stg_session_events')
dim_page = tbl(con, in_schema('prod', 'dim_page'))
dim_sessions = tbl(con, in_schema('prod', 'dim_session'))

dwh_set_execution_slots(con, 3)

onlyNew = select(map_ref, id=event_id, session_key) %>%
  anti_join(dim_sessions, by='session_key') %>%
  compute()

basePageviews = onlyNew %>% # Only sessions not in dimension already, recalculating is costly
  inner_join(filteredEvents, by=c('id')) %>%
  inner_join(dim_page, by=c('url'='page_full_url')) %>%
  # Remove consecutive visits/tracks to the same url
  mutate(
    prev_page=sql('lag(page_clean_url) over (partition by session_key order by received, id)')
  ) %>%
  filter(prev_page!=page_clean_url)

actionNum = basePageviews %>%
  group_by(session_key) %>%
  tally()

actionSequence = basePageviews %>%
  # Get sequence numbers
  mutate(
    url=sql('left(coalesce(page_clean_url, url), 500)::varchar(500)'),
    seq=sql('row_number() over(partition by session_key order by received, id)'),
    revseq=sql('row_number() over(partition by session_key order by received desc, id)')
  ) %>%
  # Build string for first five and last 5 pageviews
  filter(seq<=5 | revseq <=5) %>%
  mutate(
    path_firstfive = ifelse(seq<=5, url, NA),
    path_lastfive = ifelse(revseq<=5 & seq > 5, url, NA)
  ) %>%
  group_by(session_key) %>%
  summarize(
    firstfive=sql("listagg(path_firstfive, ' -> ') within group (order by received)"),
    lastfive=sql("listagg(path_lastfive, ' -> ') within group (order by received)")
  ) %>%
  ungroup() %>%
  mutate(
    firstfive=sql('firstfive::varchar(2520)'),
    lastfive=sql('lastfive::varchar(2520)')
  ) %>%
  inner_join(actionNum, by='session_key') %>%
  mutate(
    action_sequence=case_when(
      n > 10 ~ concat(concat(firstfive, ' -> [...] -> '), lastfive),
      !is.na(lastfive) ~ concat(concat(firstfive, ' -> '), lastfive),
      TRUE ~ firstfive
    )
  ) %>%
  inner_join(dim_ref, by='session_key') %>%
  select(session_key, action_sequence, session_id) %>%
  compute()

dwh_upsert_table_from_temp_table(con, "prod.dim_session", remote_name(actionSequence), keys=c('session_key'))
```

## Final Notes

We use R and the other technologies mentioned for our ETL processing and it has been very fruitful and smooth for us, we hope some of the ideas on this article can be useful for your Data Warehousing efforts.

You can access and download our internal library here: [https://github.com/auth0/rauth0](https://github.com/auth0/rauth0)


RAuth0 - Our internal R package

A guide through the available drivers and tools to make your life easier when using Amazon Redshift from R and/or RStudio




[Amazon Redshift](https://aws.amazon.com/redshift/) is one of the hottest databases for Data Warehousing right now, it's one of the most cost-effective solutions available, and allows for integration with many popular BI tools. Unfortunately, the status of the drivers compatibility is a little more shaky, but there is a way to make it work very nicely with [R](https://www.r-project.org/)!

<include src="TweetQuote" quoteText="The status of Redshift drivers compatibility with R is a little shaky, but there is a way to make it work fine."/>

First of all, let's go through the 3 options we have for connecting to Amazon Redshift. For all of the connections, we'll define these variables for connecting:


```r
dbname="dbname"
host='my-redshift-url.amazon.com'
port='5439'
user='myuser'
password='mypassword'
```

## RJDBC

This is the ["official" way to use Amazon Redshift with R](https://aws.amazon.com/blogs/big-data/connecting-r-with-amazon-redshift/), using the JDBC driver on [SQL Workbench/J](http://www.sql-workbench.net/) is the official way to connect to it according to the documentation, and this driver can be loaded like this:



```r
# Save the driver into a directory
dir.create('~/.redshiftTools')
download.file('http://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.9.1009.jar','~/.redshiftTools/redshift-driver.jar')
install.packages('RJDBC')
```


```r
suppressPackageStartupMessages(library(RJDBC))

# Use Redshift driver
driver <- JDBC("com.amazon.redshift.jdbc41.Driver", "~/.redshiftTools/redshift-driver.jar", identifier.quote="`")

# Create connection    
url <- sprintf("jdbc:redshift://%s:%s/%s?tcpKeepAlive=true&ssl=true&sslfactory=com.amazon.redshift.ssl.NonValidatingFactory", host, port, dbname)
jconn <- dbConnect(driver, url, user, password)
```

Of course you can change the url with the options you need for your particular setup. The first 3 lines you only need to run them once, they download Redshift's official JDBC driver and install the RJDBC package.

This package was the only one I found which supported transactions on Redshift (`BEGIN`, `COMMIT`, `ROLLBACK`) until recently, otherwise on other packages DDL operations will autocommit.

Until a few days ago since the writing of this blogpost, this was the recommended package for uploading data and inserting/deleting data, since you usually want be able to unite your operations which modify data into a single transaction.

This package has some big problems though, which I'll explain later on this post.

## RPostgreSQL

Amazon Redshift is mostly PostgreSQL compatible, so most PostgreSQL drivers work well. [RPostgreSQL](https://code.google.com/archive/p/rpostgresql/) is configured like this:



```r
install.packages('RPostgreSQL')
```

```r
library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
pconn_rsql <- dbConnect(drv,
                 host = host,
                 port = port,
                 user = user,
                 password = password,
                 dbname = dbname)
```

There are two major problems with this driver though: It [lacks SSL support](https://stackoverflow.com/questions/38942118/can-not-connect-postgresqlover-ssl-with-rpostgresql-on-windows), and you don't have transactions like you do with the official driver. In my humble opinion this is not a good option, even if you don't use SSL now, if your company requires it in the future you may need to change all your code to switch drivers. The good thing is this driver works fine with [dbplyr](http://dbplyr.tidyverse.org/).

<include src="TweetQuote" quoteText="The RPostgreSQL driver doesn't support SSL-secured Redshift clusters."/>

## RPostgres

This is another PostgreSQL library, that has much better support using the [libpq library](https://www.postgresql.org/docs/9.6/static/libpq.html), you'll probably need to install the postgresql development header libraries locally for this one to be installed succesfully. [Check out the official docs for installing instructions if you run into issues.](https://github.com/r-dbi/RPostgres)



```r
install.packages('RPostgres')
```

```r
library(RPostgres)
pconn_r <- dbConnect(RPostgres::Postgres(),
               host = host,
               port = port,
               user = user,
               password = password,
               dbname = dbname,
               sslmode='require')
```

RPostgres supports transactions, SSL and works fine with dbplyr.

## Gotchas With The Libraries

So, what are some gotchas with these libraries?. We'll run this in SQL Workbench/J to have some data to test:


```sql
create table sicatest (
  a varchar(100),
  b bigint,
  c date,
  d timestamp,
  e boolean
);
insert into sicatest values
('Hello', 1, '2017-10-01', '2017-10-01 20:00:00', TRUE);
insert into sicatest values
('Hello ñandu', 9223372036854775807, '2017-12-30', '2017-12-11 23:59:00', FALSE);
insert into sicatest values
('こんにちは', 9223372036854775807, '2017-12-30', '2017-12-11 23:59:00', NULL);
commit;
```

We deliberately added the Ñ letter from spanish, and こんにちは (Kon'nichiwa) which is hello in japanese to have some non-ascii stuff, and see if it's handled correctly.

So, let's start with RJDBC:


```r
suppressPackageStartupMessages(library(dplyr))
sicatest = dbGetQuery(jconn, 'select * from sicatest')
dplyr::glimpse(sicatest)
```

```
## Observations: 3
## Variables: 5
## $ a <chr> "こんにちは", "Hello ñandu", "Hello"
## $ b <dbl> 9223372036854775808, 9223372036854775808, 1
## $ c <chr> "2017-12-30", "2017-12-30", "2017-10-01"
## $ d <chr> "2017-12-11 23:59:00.000000", "2017-12-11 23:59:00.000000", ...
## $ e <chr> NA, "false", "true"
```

Well, that didn't go as expected, right? If you look closely, the table has the number `9223372036854775807`, but the query has returned `9223372036854775808` 😱. This happens because the numeric integer is automatically converted to a floating point numeric, which loses precision with big numbers.

Also unfortunately, it has returned dates and booleans as strings, which is incorrect, but we can work around that. Let's compare with `RPostgres`.



```r
sicatest2 = dbGetQuery(pconn_r, 'select * from sicatest')
glimpse(sicatest2)
```

```
## Observations: 3
## Variables: 5
## $ a <chr> "Hello ñandu", "Hello", "こんにちは"
## $ b <S3: integer64> 9223372036854775807, 1, 9223372036854775807
## $ c <date> 2017-12-30, 2017-10-01, 2017-12-30
## $ d <dttm> 2017-12-11 23:59:00, 2017-10-01 20:00:00, 2017-12-11 23:59:00
## $ e <lgl> FALSE, TRUE, NA
```
Well well, that's much better isn't it? the numbers aren't modified, they are of the correct type (int64), and it correctly guessed types `date`, `datetime` and `logical/boolean`! If integer64 is problematic in your case, you can also choose to convert bigint fields into other types, by using the `bigint` parameter when creating the connection.

Great stuff! I think this is the kind of library we want to work in a day-to-day basis. Also, you can use this same connection to explore data with dbplyr:


```r
sica_ref = tbl(pconn_r, 'sicatest')

r = select(sica_ref, d, a) %>%
  mutate(rank=min_rank(desc(d))) %>%
  filter(rank==1) %>%
  collect()
knitr::kable(r, format='markdown')
```



|d                   |a           | rank|
|:-------------------|:-----------|----:|
|2017-12-11 23:59:00 |Hello ñandu |    1|
|2017-12-11 23:59:00 |こんにちは  |    1|

Before, this library failed with transactions, so something like this failed, but now it works fine!


```r
dbExecute(pconn_r, "BEGIN")
dbExecute(pconn_r, "COMMENT on table sicatest is 'best table ever'")
dbExecute(pconn_r, "ROLLBACK")
```

That's why, for maximum efficiency with R + Redshift, I recommend to use `RPostgres`, as it's the best library available today.

<include src="TweetQuote" quoteText="For maximum efficiency with R + Redshift, we can use RPostgres."/>

Having said that, there's an additional tool which I'd like to introduce to you, which is the `redshiftTools` R Package, it'll add nicely to your toolkit and supports either RJDBC or RPostgres connections. This package is MIT licensed and it's source is available at [https://github.com/sicarul/redshiftTools](https://github.com/sicarul/redshiftTools).


```r
  install.packages(c('devtools', 'httr', 'aws.s3'))
  devtools::install_github("RcppCore/Rcpp")
  devtools::install_github("r-dbi/DBI")
  devtools::install_github("sicarul/redshiftTools")
```

After installing, you'll have 4 helpful functions to use, which are explained in full detail in [the package's README](https://github.com/sicarul/redshiftTools).

`rs_create_statement`: Generates the SQL statement to create a table based on the structure of a data.frame. It allows you to specify sort key, dist key and if you want to allow compression to be added or not.

`rs_replace_table`: Deletes all records in a table, then uploads the provided data frame into it. It runs as a transaction so the table is never empty to the other users.

`rs_upsert_table`: Deletes all records matching the provided keys from the uploaded dataset, and then inserts the rows from the dataset. If no keys are provided, it acts as a regular insert.

`rs_create_table`: This just runs *rs_create_statement* and then *rs_replace_table*, creating your table and uploading it.

This package is helpful because uploading data with inserts in Redshift is super slow, this is the recommended way of doing replaces and upserts [per the Redshift documentation](http://docs.aws.amazon.com/redshift/latest/dg/t_Loading-data-from-S3.html), which consists of generating various CSV files, uploading them to an S3 bucket and then calling a copy command on the Redshift server, all of that is handled by the package.

I hope this guide let's you unlock the full potential of R + Amazon Redshift, two great tools that work very well together when well configured. We use this methodology inside Auth0 and we think it's very useful for other organizations with similar infrastructure.

If you are interested in reading other posts about our work with Data @ Auth0, you may enjoy [Machine Learning for everyone](https://auth0.com/blog/machine-learning-for-everyone/) and [Adding Authentication to Shiny Server in 4 Simple Steps](https://auth0.com/blog/adding-authentication-to-shiny-server/).

<include src="asides/MarketBasketLinksAbout" />


A comprehensive guide to connect to Amazon Redshift from R

pablo-casas

I am responsible for the creation of AI and machine learning models in different areas of Auth0, like Support, Marketing, and Anomaly Detection for Product. Learning and teaching about data is my passion! Recently, I published the open-source “Data Science Live Book”, which you can read at http://livebook.datascienceheroes.com. You can also find me teaching Data Science at Universidad Tecnológica Nacional de Argentina.

Sr. Data Scientist / AI

Pablo Casas

Case study in R reviewing common concepts regarding how to validate, run and visualize a predictive model on production ranking the most suspicious cases.



### Overview

We're going to analyze data that contain cases flagged as abnormal. So we'll build a predictive model in order to spot cases that are not currently flagged as abnormal, but behaving like ones that are.

Topics are:

* Creating a predictive model (random forest)
* Introduction to ROC value (model performance)
* Understanding of label and score prediction
* Inspection of suspicious cases (audit)
* Prediction and dimension reduction (t-SNE)

### Let's start!

This post contains R code and some machine learning explanations, which can be extrapolated to other languages such as Python. The idea is to create a case study giving the reader the opportunity to recreate results. You will need the following:

* Download <a href="https://cloud.r-project.org/" target="blank">R engine</a>
* Download <a href="https://www.rstudio.com/products/rstudio/download/">RStudio IDE</a>


> Note: There are some points oversimplified in the analysis, but hopefully you'll become curious to learn more about this topic, in case you've never done a project like this.

First, install and load the packages (libraries) containing the functions we'll use in this project, and load the data.

```r
# delete these installation lines after 1st run
install.packages("caret")
install.packages("funModeling")
install.packages("Rtsne")
```

```r
library(caret)
library(funModeling)
library(Rtsne)

## download data from github, if you have any problem go directly to github: https://github.com/auth0/machine-learning-post-2-abnormal-behavior
url_git_data = "https://raw.github.com/auth0/machine-learning-post-2-abnormal-behavior/master/data_abnormal.txt"
download.file(url_git_data,"data_abnormal.txt")

## reading source data
data=read.delim("data_abnormal.txt", header = T, stringsAsFactors = F, sep = "\t")
```

The data contains the following columns:

```r
colnames(data)
```

```
##  [1] "id"       "abnormal" "var_1"    "var_2"    "var_3"    "var_4"   
##  [7] "var_5"    "var_6"    "var_7"    "var_8"
```

We are going to predict column `abnormal` based on `var_1`, `var_2` ... `var_8` variables.

Inspecting target variable:

```r
freq(data, "abnormal")
```

![Abnormal variable distribution](https://images.ctfassets.net/23aumh6u8s0i/15gffsr1ajQPqS4jUvdBtK/d13a8d23a33ff1c3336eb7be1ce4d497/target_distribution)

Almost 3 percent of cases are flagged as abnormal.

Next, we create the predictive model using **Random Forest**, doing the model parameter tuning with `caret` library using 4-fold cross-validation optimized for the ROC metric. We'll come back to this later.

_You can find the basics of random forest in the <a href="https://auth0.com/blog/machine-learning-for-everyone/" target="blank">first post</a> of this series._

```r
################################################  
## MODEL CREATION:
################################################
set.seed(999)
## Setting the validation metric: Cross-Validation 4-fold.
fitControl = trainControl(method = "cv",
                           number = 4,
                           classProbs = TRUE,
                           summaryFunction = twoClassSummary)

## Creating the model, given the cross-validation method.
fit_model = train(abnormal ~ var_1 + var_2 + var_3 + var_4 + var_5 + var_6 + var_7 + var_8,
                 data = data,
                 method = "rf",
                 trControl = fitControl,
                 verbose = FALSE,
                 metric = "ROC")
```

There are some important things to note about the last layout:

The `mtry` column indicates a parameter that is optimized by the library `caret`.

The selection is based on the ROC metric. This metric goes from 0.5 to 1 and indicates how well the model distiguishes between `True Positve` and `False Positive` rates. The higher the better.

### Choosing the "best" model

Predictive models have some parameters that can be tuned in order to improve predictions based on the input data. In the last example, `caret` chooses the **best model configuration** based on a desired accuracy metric---ROC, in this case.

Random forest does not have many parameters to tune compared with other similar gradient boosting machines. The parameter to tune was `mtry`.

`caret` tested 3 different values of `mtry`, and the value which maximizes the ROC value is `6`.

_Cross-validating results is really important; you can get more information in ref. [1]_

### What is the ROC value?

This is a long -long- topic, but here we try to introduce you to some aspects to start becoming familiar with it.

#### Some history

During World War II, radar detected if an airplane was coming or not, so they sent out the following alerts:

* `Hey, a plane was spotted!` (positive prediction)
* `Be easy, no plane is coming....` (negative prediction)


These outcomes can be `True` or `False`, so we have four possibilites:

1- If the detection of `Hey, a plane was spotted!` is `True`, it is called a `True Positive`.

2- On the other hand, if `Hey, a plane was spotted!` is `False`, the result is a `False Positive`, which implies that the radar (predictive model) _said_ a plane was coming but there isn't actually a plane out there.

3- The radar _says_ `Be easy, no plane is coming....` and the real result is **Positive**: It's a `True Negative`.

4- The radar _says_ `Be easy, no plane is coming....` and the real result is **Negative**: It's a `False Negative`.

#### Abnormal example data

In the data we used to build the model, we have:

* `Positive` is when abnormal=`yes`.
* `Negative` is when abnormal=`no`.

Normally we associate the positive value with the less representative value, which is the one we are trying to explain and the least common.

#### Analysis of possibilites

Points 1 and 3 imply the radar (or the predictive model) asserts the prediction.

With points 2 and 4, the model failed---it predicted one thing and it was actually the opposite.

The ROC value measures the trade-off between the `True Positive` and `True Negative` rates. This is because we need to be sure about what the model is saying when it detects a `Positive` outcome:

**Is this `Positive` prediction reliable or not?**

#### Usage in medicine

This accuracy metric is so powerful that it is used in other fields, such as **medicine**, to measure the accuracy of certain diagnoses:

_"The flu test for this patient was positive"_: if this result is confirmed after a while or a second test, we've got a true positve.

It is used in many tests in which the result is either `true` or `false`; it's very important to know if we can trust this result.

#### Usage in Auth0

We did some proof of concept to automatically spot the most suspicious login cases in order to boost current **anomaly detection** feature, and ROC curve was a good option to test the predictive model sensitivity.

_More info about current anomaly detection feature at ref. [2]_

#### Understanding the extremes

Knowing extreme ROC values is a good approach to better understanding it:

* An ROC value of `1` indicates all the `Positive` values returned by the model are correct. Perfect model.
* An ROC value of `0.5` indicates all the `Positive` values returned by the model have results similar to random guessing. This is the worst model.

_See more about ROC in ref. [3]_

### Going back to the predictive model

We talked about the predictive model output, which is something like: `positive / negative` or `yes / no`. But it's better to work with probabilities (also known as **score**) so the output of any predictive model (in binary or multi-label class prediction) should be the score.

_For a longer explanation, see ref. [4]_.

Once we get the score value, we can select all cases above a threshold and label them as `abnormal`.

We assign the probability of being `abnormal=yes` to each case:

```r
data$score=predict(fit_model$finalModel, type="prob")[,2]
```

We can kept with those cases with are actually `abnormal=no`:


```r
# filtering cases...
data_no_abnormal=subset(data, abnormal=="no")
```

We obtain the top 2% of cases with the highest score for being abnormal:


```r
# obtaining the score to filter top 2%
cutoff=quantile(data_no_abnormal$score, probs = c(0.98))

# filtering most suspicious cases
data_to_inspect=subset(data_no_abnormal, score>cutoff)
```

And here we've got in `data_to_inspect` ~ 60 cases which are actually no abnormal, but **they are showing a behavior of being suspicious**.

Displaying only the first 6 suspicious IDs to further inspect...


```r
head(data_to_inspect$id)
```

```
## [1]  59  94 105 107 224 259
```

**Is there a way to visualize how the predictive model "sees" data and assigns the probabilities?**

### Let's talk about projections

We've built a machine learning model in order to know the abnormal cases, using random forest.

Predictive models handle several variables **at the same time**, which is different from the classical reporting approach, in which you see two or three variables in one plot.

In the following example, you'll see how **eight variables are mapped in only two dimensions**. In this case there are eight, but it could be thousands. This is quite fancy since it is like a compression of the information.

You've probably already seen this "complex" technique: in geographical maps. They map three dimensions into two:

![Variable reduction](https://images.ctfassets.net/23aumh6u8s0i/5jPpERmUd8aipSneVX0pw9/4f1d9fcb38a6f77c22364191b6135555/map_projections)

There are several ways to do this with data; the most popular is probably Principal Component Analysis -aka PCA- (however, this doesn't lead to good visualizations). The one we used in this post is **t-Distributed Stochastic Neighbor Embedding**, which is also implemented in languages other than **R**. _More info at ref. [5]._

_Google did a live demo based on text data, see ref. [6]._


#### Hands on R!

First some data preparation--excluding id and score variables to create the t-SNE model. Also, converting all string variables into numeric ones since t-SNE doesn't support this type of variable (one-hot encoding or placeholder variables).

Actually, we're going to map 17 variables (not eight) because of this transformation.


```r
# excluding id column and score variable
data_2=data[,!(names(data) %in% c("id", "score"))]
d_dummy = dummyVars(" ~ .", data = data_2)
data_tsne = data.frame(predict(d_dummy, newdata = data))
```


Now we create the t-SNE model, adding it to the `score` variable we created before.


```r
set.seed(999)
tsne_model = Rtsne(as.matrix(data_tsne), check_duplicates=FALSE, pca=TRUE, perplexity=30, theta=0.5, dims=2)
d_tsne = as.data.frame(tsne_model$Y)
d_tsne$abnormal = as.factor(data$abnormal)
d_tsne$score=data$score
d_tsne = d_tsne[order(d_tsne$abnormal),]
```

**Now the magic!**  Plotting the resulting t-SNE model which maps 17 variables in two dimensions :)


```r
ggplot(d_tsne, aes(x=V1, y=V2, color=abnormal)) +
  geom_point(size=0.25) +
  guides(colour=guide_legend(override.aes=list(size=6))) +
  xlab("") + ylab("") +
  ggtitle("t-SNE on Abnormal Data") +
  theme_light(base_size=20) +
  theme(axis.text.x=element_blank(),
        axis.text.y=element_blank()) +
  geom_point(data=d_tsne[d_tsne$abnormal=="yes",], color="black", alpha=1,shape=21) +
  geom_point(data=d_tsne[d_tsne$abnormal=="no" & d_tsne$score>=cutoff,], color="blue", alpha=0.8,shape=5, aes(color=Class)) +
  scale_colour_brewer(palette = "Set2")
```

![tsne on abnormal data"](https://images.ctfassets.net/23aumh6u8s0i/6E69rDzF64Tr1UqGgJoO4v/c618fdcce942e2eaafaf5853b8dd1575/tsne_abnormal_data)

### But what are we seeing there? Analysis

* **Blue rhomboids** represent the cases which are actually not flagged as abnormal, but are flagged as "highly abnormal" by the predictive model (this is what was analyzed before).

* **Pink points** represent the cases which are actually flagged as abnormal.

* **Green points** are the cases which are not actually abnormal, having a low likelihood of being one of them.

**Similar cases tend to be closer in the plot.**

Pink cases are close to the blue ones. These are the cases predicted as **highly suspicious** and that share similar behavior with the ones actually flagged as abnormal.

That's why we left the `score` variable out of the t-SNE model creation. This model put closely together some cases that were actually `abnormal` and `not abrnormal`, and this makes sense since these cases were flagged by the random forest as suspicious ones.

Look at the biggest islands made of green points---there are no abnormal points, flagged or predicted, near them.

#### Conclusions

The cases flagged as abnormal, plus the top 2 percent of suspicious ones detected by the random forest, are mapped closer together away from the normal cases, because they behave differently. This is one way of uncovering the information in the data.

Time to play with your own data :)


### References

* [1] Why validate predictive models? <a href="http://livebook.datascienceheroes.com/model_performance/knowing_the_error.html" target="blank">Knowing the error</a>
* [2] <a href="https://auth0.com/docs/anomaly-detection" target="blank">Anomaly detection</a> feature at Auth0.
* [3] <a href="https://en.wikipedia.org/wiki/Receiver_operating_characteristic" target="blank">Wikipedia ROC Curve</a>
* [4] <a href="http://livebook.datascienceheroes.com/scoring/scoring.html" target="blank">Data Science Live Book - Data Scoring</a>
* [5] Original implementation <a href='https://lvdmaaten.github.io/tsne/'>t-Distributed Stochastic Neighbor Embedding</a>
* [6] <a href="https://research.googleblog.com/2016/12/open-sourcing-embedding-projector-tool.html">
Open sourcing the Embedding Projector: a tool for visualizing high dimensional data</a>

<a href="https://github.com/auth0/machine-learning-post-2-abnormal-behavior" target="blank">Link</a> to clone from Github (only R code).

> Are you building a product that takes advantage of machine learning? We at Auth0, can help you focus on what matters the most to you, the special features of your product. [Auth0](https://auth0.com/) can help you make your product secure with state-of-the-art features like [passwordless](https://auth0.com/passwordless), [breached password surveillance](https://auth0.com/breached-passwords), and [multifactor authentication](https://auth0.com/multifactor-authentication). [We offer a generous **free tier**](https://auth0.com/pricing) to get started with modern authentication.


Machine Learning for Everyone - Part 2: Spotting anomalous data

Learn the basics of predictive modeling behind one of the most-used machine learning models



We all know that machine learning is about handling data, but it also can be seen as:

> The art of finding order in data by browsing its inner information.


### Some background on predictive models

There are several types of predictive models. These models usually have several input columns and one target or outcome column, which is the variable to be predicted.

![Predictive Model Table](https://images.ctfassets.net/23aumh6u8s0i/3lyx3cgPwFuhHkXXSbOXVX/4c7e01f051a02517a99c53ccd02d202c/predictive_model_table)

So basically, a **model performs mapping between inputs and an output**, finding-mysteriously, sometimes-the relationships between the input variables in order to predict any other variable.

As you may notice, it has some commonalities with a human being who `reads the environment => processes the information => and performs a certain action`.

## So what is this post about?

It's about becoming familiar with one of the most-used predictive models: **Random Forest** (<a href="https://www.stat.berkeley.edu/~breiman/RandomForests/cc_home.htm" target="blank">official algorithm site</a>), <a href="https://cran.r-project.org/web/packages/randomForest/randomForest.pdf" target="blank">implemented in R</a>, one of the most-used models due to its simplicity in tuning and robustness across many different types of data.

If you've never done a predictive model before and you want to, this may be a good starting point ;)
 
### Don't get lost in the forest!

![Random Forest Bear](https://images.ctfassets.net/23aumh6u8s0i/43M0l6bRqJkL0fLyDWUDVw/11a96f92dad15082295a8fbc98fb4eba/random_forest_bear)

The basic idea behind it is to build hundreds or even thousands of **simple and less-robust models** (aka <a href="https://en.wikipedia.org/wiki/Decision_tree_learning">decision trees</a>) in order to have a less-biased model.

**But how?**

Every 'tiny' branch of these decision tree models will see just part of the whole data to produce their humble predictions. So the final decision produced by the random forest model is the result of **voting by all the decision trees**. Just like democracy.

**And what is a decision tree?**

You're already familiar with **decision tree** outputs: they produce `IF-THEN` rules, such as, `If the user has more than five visits, he or she will probably use the app`.

![Decision Tree](https://images.ctfassets.net/23aumh6u8s0i/2RbnwqKvUNvFufTaXfD5FU/63528ea81a6206c3413384a4f7d5f057/decision-tree)

### Putting all together...

If a random forest has `three trees` (but normally 500-plus) and a **new customer arrives**, then the prediction whether said customer will buy a certain product will be 'yes' if 'two trees' predict 'yes'.

![Random Forest](https://images.ctfassets.net/23aumh6u8s0i/5jYXr1dfTjtAb8AYXVrype/a1f33cfd96abfaf9674899348a5c8509/random_forest)

> Having hundreds of opinions -_decision trees_- tends to produce a more accurate result on average -_random forest_-.

_But don't panic, all of the above is encapsulated in the data scientist._

With this model, you will not be able to easily know _how_ the model comes to assign a high or low probability to each input case. It acts more like a black box, similar to what is used for **deep learning** with neural networks, where every neuron contributes to the whole.

## Next post...

Will contain an example -based on real data- of how random forest **orders** the customers according to their likelihood of matching certain business condition. Also, it will **map around 20 variables into only two**, therefore, it can be seen by the analyst ;)

![Next Post](https://images.ctfassets.net/23aumh6u8s0i/54BiL6ZshSQ9smdj6MKCeh/326f1d049165fa861b3e2ea13717adac/next_post)


## What language is convenient for learning machine learning?

Auth0 mainly uses <a href="https://www.r-project.org/" target='blank'>R software</a> to create **predictive models** as well as other data processes; for example:

* Finding relationships between app features: which impacts the **engineering area**.
* Finding **anomalies** or abnormal behavior: which leads to the development of <a href='https://auth0.com/docs/anomaly-detection' target='blank'> anomaly detection </a> features.
* Improving web browsing <a href='https://auth0.com/docs' target='blank'>docs</a>: based on <a href='https://en.wikipedia.org/wiki/Markov_chain'>markov chains</a> (likelihood of visiting `Page B being on Page C`)
* Reducing times for answering **support tickets** using deep learning (not with R but with <a href="https://keras.io/" target='blank'>Keras</a>)

If you want to develop your own data science projects, you could start with **R**. It has a **enormous community** from which you can learn (and teach). It's not always just a matter of complex algorithms, but also about having support when things don't go as expected. And this occurs **often** when you're doing new things.

![R Logo](https://images.ctfassets.net/23aumh6u8s0i/1wFMe9GqIZXjaY1XKxC7ca/3c13cff3bf17654163e92cfdc5352898/R_logo)

### Finally, some numbers about community support

Despite the fact that R (and `Python` with `pandas` and `numpy`) has lots of packages, libraries, free books, and free courses, check these metrics: There are more than **160,000** questions in <a href="http://stackoverflow.com/questions/tagged/r">stackoverflow.com</a>, and another **~15,000** in <a href="http://stats.stackexchange.com/questions/tagged/r">stats.stackexchange.com</a> are tagged with **R**.


"rstats"