Using R to find correlation pairs

Question

           VZ.Close CBOU.Close SBUX.Close   T.Close
VZ.Close   1.0000000  0.5804478  0.8872978 0.9480894
CBOU.Close 0.5804478  1.0000000  0.7876277 0.4988890
SBUX.Close 0.8872978  0.7876277  1.0000000 0.8143305
T.Close    0.9480894  0.4988890  0.8143305 1.0000000

So, Let's say I have these correlations between stock prices. I would like to look at the first row and find the pair with the highest correlation. That would be VZ and T. I then want to remove those 2 stocks as options. Then, among the remaining stocks find the pair with the highest correlation. And so on until all stocks are paired. In this example it would obviously be CBOU and SBUX because they are the only 2 left, but I want the code able to accommodate any number of pairs.

Why do you only look at the first row in the first step ? Don't you want to find the higher correlation among all the rows instead ? — juba
The question is a bit ambiguous and needs better explanation. Do you want to remove those two stocks as options in the first row ( i.e. the stock relating to the first row itself and the stock with the next greatest correlation), and then return out of the remaining stocks, no matter how many there may be, the two that have the greatest correlation in the first row (i.e. greatest correlation with VZ.close in this example), and do this for each row? — Simon O'Hanlon
This question is not related to stocks: it's purely a matter of evaluating matches in rows of a matrix. I'm not sure this is a good use of the term "correlation," as it appears you just want to find the 2 values in a given row which are closest to each other. In that case, @SimonO101 's answer looks pretty good. — Carl Witthoft

juba juba · Accepted Answer · 2013-02-05T09:38:28

Here is a solution if you want to look at the maximum correlation at each step. So the first step will not look only at the first row, but at the whole matrix.

Sample data :

d <- matrix(runif(36),ncol=6,nrow=6)
rownames(d) <- colnames(d) <- LETTERS[1:6]
diag(d) <- 1
d
           A          B         C          D         E          F
A 1.00000000 0.65209204 0.8520392 0.26980214 0.5844000 0.69335143
B 0.73531603 1.00000000 0.5499431 0.60511580 0.7483990 0.14788134
C 0.56433218 0.27242769 1.0000000 0.07952776 0.2147628 0.03711562
D 0.91756919 0.04853523 0.5554490 1.00000000 0.4344089 0.23381447
E 0.06897889 0.80740821 0.7974340 0.87425643 1.0000000 0.74546072
F 0.19961474 0.61665231 0.2829632 0.58110694 0.7433924 1.00000000

And the code :

results <- data.frame(v1=character(0), v2=character(0), cor=numeric(0), stringsAsFactors=FALSE)
diag(d) <- 0
while (sum(d>0)>1) {
  maxval <- max(d)
  max <- which(d==maxval, arr.ind=TRUE)[1,]
  results <- rbind(results, data.frame(v1=rownames(d)[max[1]], v2=colnames(d)[max[2]], cor=maxval))
  d[max[1],] <- 0
  d[,max[1]] <- 0
  d[max[2],] <- 0
  d[,max[2]] <- 0
}

Which gives :

  v1 v2       cor
1  D  A 0.9175692
2  E  B 0.8074082
3  F  C 0.2829632

Using R to find correlation pairs

2 Answers