4
$\begingroup$

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.

enter image description here

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.

$\endgroup$
2
  • 1
    $\begingroup$ Is PCA an option? It reduces the data based on correlation, so the eigenvectors show which are strongly correlated in a very clear format, in fact it shows multiple patterns of correlation for each pc $\endgroup$
    – ReneBt
    Dec 25, 2019 at 14:41
  • $\begingroup$ @ReneBt, I do not think so that would help. $\endgroup$
    – jeza
    Dec 25, 2019 at 21:01

4 Answers 4

4
$\begingroup$

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.

$\endgroup$
4
  • $\begingroup$ thanks for your detailed answer. I understand your thoughts, but I need to show and present in a report that if there is multicollinearity between variables or not. $\endgroup$
    – jeza
    Dec 23, 2019 at 11:54
  • $\begingroup$ Multicollinearity is somewhat different. Do you mean multicollinearity or pairwise correlation for all of the 3,321 pairs of your 82 variables? If you want the latter, what’s wrong with the type of plot you created? That plot tells me that you have some strong correlations. $\endgroup$
    – Dave
    Dec 23, 2019 at 12:31
  • $\begingroup$ Then you can write some code to extract the maximum absolute value of elements in the correlation matrix (excluding the diagonal) and tell you the variables with that correlation. $\endgroup$
    – Dave
    Dec 23, 2019 at 15:04
  • $\begingroup$ Let us continue this discussion in chat. $\endgroup$
    – Dave
    Dec 23, 2019 at 16:04
3
$\begingroup$

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).

enter image description here

### 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)
$\endgroup$
2
$\begingroup$

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).

$\endgroup$
1
  • $\begingroup$ but as you said a lot of plots! $\endgroup$
    – jeza
    Dec 25, 2019 at 23:15
2
+50
$\begingroup$

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)
$\endgroup$
4
  • $\begingroup$ ، thank you for your answer, but what does this number tell me, for example, when I used your code with two datasets. I got VIFmax = 1.06 and 49.5 respectively. $\endgroup$
    – jeza
    Dec 25, 2019 at 19:56
  • $\begingroup$ have edited the answer to include an explanation for the term $\endgroup$
    – jf1
    Dec 26, 2019 at 2:30
  • $\begingroup$ thank you it is more clear now. What do you think about the best way of presenting normality for big data? $\endgroup$
    – jeza
    Dec 26, 2019 at 16:46
  • 1
    $\begingroup$ I'm afraid there is no single measure for summarizing this many variables w.r.t. to deviation from normal distribution simultaneously. You could present skewness and kurtosis (at least two measures). But as this refers to many variables instead of a single one, you would have tp present at least a range (min and max) for each of them, or better yet: min, median and max of both, skewness and kurtosis, i.e. at least six measures for every table. And this is still a gross simplification and not really satisfying. Moreover it only works for unimodal distributions. $\endgroup$
    – jf1
    Dec 26, 2019 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.