The best way of presenting the correlation / normality results of big data

Question

I am working with databases that have a lot of rows and columns. For example, this DATA has 21263 rows and 82 columns.

My question as follows

If I would like to make a report and to present the correlation of variables for those databases, so I can show the reader that there is a high correlation in specific data.

Using a correlation matrix or its graph in this sense would not be nicely presented as appear below.

I used R programming for this graph.

Is there any tool/R-code that provides me with only one number, so I can judge about the correlation of variables for any big dataset?

NOTE

I need to show and present in a report that if there is multicollinearity between variables or not. By Multicollinearity, I mean the correlation between variables in the dataset. I need to judge if the data have a correlated variable or Not. The problem is that this judgment must be reported for many big datasets. So the figure I attached or correlation matrix would not help. I am not sure if the determinant of the correlation matrix would be enough or not. It is a simple and straightforward number!

My plan of the report is to create a table including datasets, and one of the columns is for a measure of correlation. For example determinant of the correlation matrix, or any measure like this! So it will be easy for the reader to see which dataset has correlated variables.

UPDATE

The same for checking normality. Presenting each QQ-plot of a variable will be too much in a report for big data with 82 variables.

Answer 1

This is a somewhat subjective question, but in general presenting a reader with cross correlations between 82 different factors is not particularly helpful, no matter how it is presented. The idea of exploratory analysis is to disseminate something useful to the reader without them having to necessarily go through all of the analysis themselves, and give them an idea of your thought process in what you did next. Depending on the nature of the work, you could include something verbose in an appendix, but in general you could be served here by:

1. Asking yourself whether a correlation matrix is really the best approach to present what you want to a reader, have you considered other feature selection methods and the context of the problem to help highlight potential leads in the data to then test and attempt to model?
2. Presenting only the variables of interest given what you observe, in a condensed version of the correlation matrix, perhaps with some notion of saying that only certain significant correlations are present
3. Grouping variables into logical groups and producing summary statistics, with the option for a reader to dig deeper by providing some qualification of these groupings in the report

A single number summary of the correlations you've observed would not be particularly informative, and as far as I know nothing really exists to express this unless I am misunderstanding the question. I could add 40 columns to your data containing noise and massively change any single observed metric or number; the nature of correlation is that the attributes you are talking about matter, not the dataset as a whole.

Answer 2

I will throw my 2 cents as I had to battle with exactly the same problem for presenting results for a 77x77 variables correlation matrix.

I tried just about anything you can think of in terms of conveniently visualizing a 77x77 matrix by using R, SPSS and Excel. From my experience, there is simply no magical pill/graph that will result in an 82x82 matrix (your dimensions), which can be easily visually digested, just due to too many variables being present. Now I resorted to employing the following strategy, which I thought was reasonable.

Strategy

As you are, I was interested in showing highly-correlated pairs of variables. First, I looked through the literature to understand what is classed as a high correlation. Disclaimer: as with all rules of thumb, you should not blindly rely on such recommendations. Instead, take them as a starting point and then check papers/research in your respective field to determine/validate as to what is commonly thought to be highly correlated. For example, in the field of psychometrics, it is not uncommon to think that variables correlated as |.70| and greater are highly correlated. This is also supported by some more general published guidelines produced by Kenny.

Next, suppose you have chosen some criterion (e.g. |.70| or similar). Next, I resorted to writing an R code that will essentially give me the pairs of variables that adhere to this criterion (attached at the very bottom). Unless, everything in your correlation matrix is highly correlated, which I doubt by looking at the corrplot you attached, you end up with a neater and more manageable task. That is, now, the task can be reduced to producing only, for example, a 20x20 correlation matrix of only highly correlated variables.

At this point, let's backtrack just a little to counter a potential critique, as someone may say: "Yes, but what about showing other lower correlations amongst the remaining variables (as you cannot ignore non-high correlations)?" Again, due to the difficulties of presenting an 82x82 matrix in a visually digestible way, I would recommend to start presenting your results by describing the correlation matrix more generally, by noting a few key patterns. For example,

• Note dimensions of the correlation matrix.
• State proportion of positive/negative correlations.
• State proportion of variables that essentially showed no correlation; weak correlation; moderate correlation; and high correlation. Give examples in each case (it is useful).

Now, at this point, notice how you ended your general summary of the correlation patterns with noting high correlations. From this point on, you can continue the flow and concentrate on the above, i.e. defining what you mean by high correlations and what made you consider a specific threshold to identify high correlations, then provide a more manageable and visually digestible corrplot of only highly-correlated pairs of variables. In addition to that, you may, optionally, rank-order highly correlated variables and present them in a simple table. From my presentation experience, it also looked very digestible and easy to follow. I will include a snapshot (note my table included extra metrics so I cut it and only attached a relevant part of the table; also my apologies for an over-sized picture, I could not figure out how to make it smaller).

### Identify highly-correlated pairs of variables 
__________________________________________________


rm(list = ls()) # clean environment 

library(foreign) # load packages
library(dplyr)
library(psych)

CORRECTED_cor <- cor(mydata) # cor matrix
CORRECTED_cor <- tbl_df(CORRECTED_cor) # tabulate cor matrix   
print(CORRECTED_cor)

CORRECTED_cor[lower.tri(CORRECTED_cor, diag = TRUE)] <- NA # remove lower triangle and diagonal elements
print(CORRECTED_cor)


CORRECTED_cor[abs(CORRECTED_cor) < 0.70] <- NA # correlations below |.70| NA
print(CORRECTED_cor)

sum(!is.na(CORRECTED_cor)) # only show cases with cor > |.70|


S <- which(abs(CORRECTED_cor) >= .70, arr.ind = T) # output rows and colums of correlations > |.70|   
S

cbind(S, abs(CORRECTED_cor[S]))   

final <- cbind(S, abs(CORRECTED_cor[S]))

final <- tbl_df(final)
print(final)


# Sanity check to make sure everything is correct

CORRECTED_cor[20, 27]
CORRECTED_cor[13, 73]
CORRECTED_cor[55, 71]
CORRECTED_cor[30, 36]


print(final)

# Sort by increasing order
arrange(final, V1)

# Final dataframe sorted by increasing order

final <- arrange(final, V1)
print(final)

Answer 3

Here is what I did once to report correlations results: prepare n-1 separate plots. On the x-axis of each plot, there were feature indexes from i+1 to n. On the y-axis of each plot, there were correlation results. I represented the plots in a grid. Sure, there were a lot of plots but they we much more readable. (you can also combine some of them to make it look nice).

Answer 4

Not sure whether the following approach really will provide insight into the structure of the datasets looked at, but you could try to calculate a variance inflation factor for every column, based on the rest of the table as predictors in a linear regression model, and report the maximum VIF per data set (assuming rows are observations and columns are variables).

VIF is a direct measure of multi-collinearity in a data set (see e.g. https://en.wikipedia.org/wiki/Variance_inflation_factor). VIF around 1 e.g. means that there is no multi-collinearity in the data set, while VIF=100 would mean that only 1% of a variable's variance is unique, while the other 99% can be explained by the predictors in the linear regression model. A common threshold for declaring substantial collinearity is VIF>=10.

This approach of course breaks down at some point either due to computational constraints (ncol or nrow too big), accumulation of missing values, which hampers modeling, or as soon as you have a complete separation of data points, i.e. for random data if ncol>=nrow.

But within some reasonable limits and for completely numeric data it should be possible as long as ncol << nrow, e.g.:

# create random matrix with dimensions as in OP above
m <- 21263
n <- 82
mydummytab <- matrix(rnorm(m*n), m, n)

# calculate variance inflation factors
library(preputils)
VIFs <- vifx(mydummytab)
VIFmax <- max(VIFs)