Data Dissection: Upper and Lower Triangular Correlation Matrix
This month, I’m going to try creating a correlation matrix visualization in Tableau. I stumbled upon this correlation matrix by Kelly Tan, a Tableau Public 2020 Featured Author, and really liked how it’s a lower triangular matrix (my old linear algebra days are calling out to me!). I wanted to see if I could figure out how to create lower or upper triangular tables myself.
Now to start out with, I’m going to need some data to be do correlations with. The Bob Ross data in Kelly’s visualization needs some manipulation so I want a more simple dataset so I can just focus on how to do the viz. Of course, Kelly’s exact viz can be recreated since you can download the Tableau workbook but that is an exercise left to the reader (man, that feels much better being on the other side of that haha).
I’m going to use the mtcars dataset that comes with R. The guide I used to get the dataset and its correlations can be found here. Really, it’s just sourcing the dataset and running a correlation on it. You should end up with this dataset:
Now, you can clearly see that I want to emulate this exact table in Tableau but then hide either the lower triangular or upper triangular part of the table because it’s the same values when reflected across the diagonal. However, this is not in a form that Tableau likes just yet. I tend to find that Tableau really likes the long, skinny table format so we need to pivot the table to have it look like this:
A lot more rows but you’ve got the data right where you want it now! Import this table into Tableau. You can save the data and import it or you can simply copy and paste the data from the clipboard into Tableau like I did.
Drag the two car_metric fields to the columns and drop the correlation values into the Text Mark card:
And voila, we’ve already got the correlation matrix!
Next step: hiding one of the triangles. You can see that the correlation between disp and drat is the same as between drat and disp. Correlations are commutative so if it’s related in one direction (disp -> drat) then the exact same relation holds in the other direction (drat -> disp). But this means we have redundant information so let’s not repeat the same information twice.
To do this, we need to create indexes along both the rows and the columns. Create a couple Calculated Fields. Use the Index function and under “Default Table Calculation”, set it to compute along Car Metric1. Then do this again but for Car Metric2.
Now, if you’ve ever had to program a loop to go through a matrix before, this next step will feel familiar. It’s the old “i > j” trick! (Now I’m pulling from my old C/C++ days. Funny how connected experience tends to be after awhile.) Create a boolean calculated field and set Index1 ≥ Index2.
Drag that filter over and set it only to the True value. You will finally end up with this:
Aha, we have our lower triangular correlation matrix! If you wanted it to be upper triangular instead, just go back into the calculation and set it to Index1 ≤ Index2. I also dropped the correlation values onto color to make my positive and negative values stand out more clearly.
Another successful dissection! It was a little unexpected to draw on my background so much to recognize what was going on under the hood of this visualization but I thought of it as a welcome little throwback to past lives. Thanks for the inspiration, Kelly!
