Predictive Analytics in Tableau Part 2: Linear Regression with Multiple Regressors

Today, we will talk about using Tableau 8.1's R functionality to perform predictive analysis via Multiple Regression.  In our previous post in the series, Predictive Analytics in Tableau Part 1: Simple Linear Regression, we talked about Simple Linear Regression.  Now, we're moving a step up and adding multiple variables to the mix.  However, we're going to keep it simple for now and keep all of the regressors at degree 1.  For those of you without advanced mathematics, which is probably most of you, a variable of degree 1 is linear, i.e. a straight line.  When the degrees get higher, the function get more curves.  If you want to know more, you can check out this article.  For this analysis, we will use the same data set as in the previous post, which can be found here.

To begin, let's look back at our scatterplot matrix again to see what our data looks like.

Scatterplot Matrix

Initially, we can just throw everything against the wall and see what comes out.  We don't particularly care about knowing what the relationship is, we just want some predictions.  In these simple scenarios, it is safe to assume that more data is always better.  However, it's not always the case with real data.  But, we'll deal with that in a later post.  For now, let's just put everything in the model.  The code is too long to fit in one screenshot; so I have pasted it below:

SCRIPT_REAL( "

    ## Defining Variables

    cons <- .arg1

    crud <- .arg2

    djia <- .arg3

    fore <- .arg4

    gnp <- .arg5

    inte <- .arg6

    purc <- .arg7

    ## Fitting the Model

    fit <- lm( cons ~ crud + djia + fore + gnp + inte + purc )

    fit$fitted

"

, SUM( [CONSUMER] ), SUM( [CRUDE] ), SUM( [DJIA] ), SUM( [FOREIGN] ),

SUM( [GNP] ), SUM( [INTEREST] ), SUM( [PURCHASE] ) )

Now, let's see the results.

Predicted Consumer Debt by Year (Text Table) Just like in our previous post, this text table isn't very easy to read.  However, if you look closely, you will see that the predictions are significantly closer since we included all of the variables.  Now, we're stuck with another dilemma.  How do we see the results visually?  We can't have a seven-dimensional scatterplot.  Well, there are a couple of different ways.  First, let's look at what's called a Residual vs. Predicted plot.

Mathematically, the residuals are the differences between the actual value and the predicted value.  In the business world, you'll often hear this called a "Delta."

Consumer (Residual)

Now, imagine that our model fit our data extremely well.  We wouldn't expect that the model fit perfectly.  However, we would expect the model to remove most of the "systematic" variation within the data, leaving only random noise.  This is precisely what this plot is designed to see.

Residual vs. Predicted Reading these types of charts is more art than science.  But, you can ask yourself one question, "Does the data form any significant pattern?"  We would say no.  Therefore, we believe that our model is a good fit for this data.  Now, let's visualize the data in its original context, by year.  Remember in the last post where we talked about "Prediction Intervals"?  We can make those here as well, using almost identical code.  The code for Consumer (Predicted Lower) is pasted below:

SCRIPT_REAL( "

    ## Defining Variables

    cons <- .arg1

    crud <- .arg2

    djia <- .arg3

    fore <- .arg4

    gnp <- .arg5

    inte <- .arg6

    purc <- .arg7

    ## Fitting the Model

    fit <- lm( cons ~ crud + djia + fore + gnp + inte + purc )

    ## Creating the Prediction Interval

    dat <- data.frame(cbind(cons,crud,djia,fore,gnp,inte,purc))

    predict(fit, dat, interval = 'prediction')[,2]

"

, SUM( [CONSUMER] ), SUM( [CRUDE] ), SUM( [DJIA] ), SUM( [FOREIGN] ),

SUM( [GNP] ), SUM( [INTEREST] ), SUM( [PURCHASE] ) )

To find the upper bound, you simply need to change the ,2 to a ,3.  Finally, let's plot our data.

Predicted Consumer Debt by Year (Line Chart)

As you can see, the bounds fit the actual data (blue line) very tightly and follow it as it increases over time.  Now, some of the more knowledgeable readers might say, "Multiple Regression requires uncorrelated observations, not time series data!"  You would be correct.  However, our goal here was simply to predict Consumer Debt.  We don't care about using the model to show correlation/causation between these variables.  We'll leave that to the econometricians.  Thanks for reading.  We hope you found this informative.

Brad Llewellyn
Associate Data Analytics Consultant
Mariner, LLC
llewellyn.wb@gmail.com
https://www.linkedin.com/in/bradllewellyn