Logistic Regression in Tableau using R

In my post on Tableau website earlier this year, I included an example of multiple linear regression analysis to demonstrate taking advantage of parameters to create a dashboard that can be used for What-If analysis. In that example, the model fitting was done inside the same calculation as the predictions, in order to provide a self-contained example that just works. However in real-life, once created, the models are reused for predictions many times until (if at all) they get updated. Let’s take logistic regression as an example to examine reusability.

In this sample, we will use admissions^(generated) data that contains, GPA, GRE score, rank of the institution (1 being top tier schools, 4 the lowest tier) and whether the student was admitted or not. We will use regression model to predict the probability of admission for a given GPA, GRE, Rank combination. 

If we were to do fitting and prediction inside the same calculated field, it would look like:

SCRIPT_REAL('mydata <- data.frame(admit=.arg1, gpa=.arg2, gre=.arg3, rank=.arg4); lrmodel <- glm(admit ~ gre + gpa + as.factor(rank), data = mydata, family = "binomial"); prob <- predict(lrmodel, newdata = mydata, type = "response")', AVG([admit]),AVG([gpa]),AVG([gre]),AVG([rank]))

Here “binomial” indicates that we want a logistic regression model.

There are many ways to save your model for future use. You can go through the model fitting inside R (using your favorite IDE e.g. R GUI or RStudio etc.)  and save it, then just load the model into Tableau or you can save it to a file directly from within Tableau. If you were doing this in R (assuming you have the data as a CSV), it would look like

mydata <- read.csv("C:/Users/bberan/R/admission.csv") mydata$rank <- factor(mydata$rank) lrmodel <- glm(admit ~ gre + gpa + rank, data = mydata, family = "binomial") save(lrmodel, file = "C:/Users/bberan/R/mymodel.rda")

This is probably what most people would do. And of course evaluate the goodness-of-fit for the model before using it to make predictions.

But if you don’t want to switch between R and Tableau, instead wanted to do this inside Tableau, it is just adding the last line to your calculated field so it looks like:

SCRIPT_REAL('mydata <- data.frame(admit=.arg1, gpa=.arg2, gre=.arg3, rank=.arg4); lrmodel <- glm(admit ~ gre + gpa + as.factor(rank), data = mydata, family = "binomial"); save(lrmodel, file = "C:/Users/bberan/R/mymodel.rda") prob <- predict(lrmodel, newdata = mydata, type = "response")', AVG([admit]),AVG([gpa]),AVG([gre]),AVG([rank]))

You would probably do this once to save the model and after the calculation re-executes, remove it from the calculated field. Your calculation reading a previously saved model would look like the following:

SCRIPT_REAL('mydata <- data.frame(admit=.arg1, gpa=.arg2, gre=.arg3, rank=.arg4); load("C:/Users/bberan/R/mymodel.rda") prob <- predict(lrmodel, newdata = mydata, type = "response")', AVG([admit]),AVG([gpa]),AVG([gre]),AVG([rank]))

Of course once the model is written, it can be used from R as well as Tableau. An even nicer way to do this is to have the model preloaded on Rserve, readily available for use. Doing this involves making some minor edits to Rserve’s configuration file: Rserv.cfg.

There are two relevant options you can use in Rserv.cfg to achieve this. “Source” option runs the provided R script file. Any objects created in the source file becomes available to all users with access to Rserve.  These could be new functions (assume there is a custom function you wrote, that’s rather lengthy and you don’t want to write all the script inside a Tableau calculated field, you just want to call it by name and use it), or any object that holds any kind of data (e.g. read an XDF file in R and have access to its contents from Tableau).

The other option is “eval” which executes the R code to follow. For example if you had

eval x = 5; library("mvoutlier")

it would create an object that holds the value 5 and load the mvoutlier library.

So your Rserv.cfg file may contain the following lines

source C:/Users/bberan/R/myRFunctionCode.R eval load("C:/Users/bberan/R/mymodel.rda");

In this case the second line is loading the model we created. So the person who will use the model for predictions doesn’t have to know where it is saved. If they know the name of the model, they can call it from Tableau. Note that config file read when Rserve starts so if you made changes to the file, you need to start Rserve for them to take effect. Also by default Rserve looks for the config file in R’s working directory. In R, you can find out what your working directory is using the getwd() command.

So I put my Rserv.cfg file in the documents folder. Restarted Rserve and Volia! I am able to call the predict function directly as shown below, without having to fit the model again or read it from the disk.

SCRIPT_REAL('mydata <- data.frame(admit=.arg1, gpa=.arg2, gre=.arg3, rank=.arg4); prob <- predict(lrmodel, newdata = mydata, type = "response")', AVG([admit]),AVG([gpa]),AVG([gre]),AVG([rank]))

You can download the example workbook from HERE. The parts that rely on reading models from files and relying objects in Rserve session will not work until you make the necessary changes listed above. Each calculated field also contains comments about changes needed.

Enjoy!

Advertisements