Find correlation between columns whose names are specified as values ​​in another data frame

Question

I have two data frames: one of them is a list of pairs of faces similar to the ones below (but with about 150 pairs):

ID_1   ID_2
X14567  X26789
X12637 X34560
X67495 X59023

Another information frame consists of a column per person with numerical values ​​related to these persons below. Everything said about 300 columns and 300 rows. For instance:

X14567  X12637  X26789  X67495  X34560  X59023
0.41    0.29    0.70    0.83    0.41    0.30
0.59    0.44    0.20    0.94    0.03    0.97
0.48    0.91    0.78    0.92    0.40    0.09
0.07    0.21    0.42    0.14    0.96    0.96
0.33    0.13    0.53    0.04    0.52    0.49
0.94    0.28    0.37    0.26    0.11    0.09

I want to find the ratio of these values ​​between each pair of individuals. for something like:

ID_1       ID_2    Correlation
X14567     X26789      -0.25
X12637     X34560      -0.25
X67495     X59023      -0.11

Is there a way in which I can pull values ​​from the first data frame to indicate the name of the two columns that I need to find correlations between them so that they can be easily repeated for each row of the first data frame?

Many thanks for your help

Answer 1

x y - data.frames , apply.

apply(x, 1, function(row) cor(y[row[1]], y[row[2]]))

x data.frame:

x$cor <- apply(x, 1, function(row) cor(y[row[1]], y[row[2]]))


      V1     V2        cor
2 X14567 X26789 -0.2515737
3 X12637 X34560 -0.2563294
4 X67495 X59023 -0.1092830

Answer 2

, :

library(reshape2)

df.corr = melt(cor(df))

(.. ):

df.corr = subset(df.corr, Var1 != Var2)

mtcars:

mtcars.corr = melt(cor(mtcars))

    Var1 Var2       value
1    mpg  mpg  1.00000000
2    cyl  mpg -0.85216196
3   disp  mpg -0.84755138
...
119   am carb  0.05753435
120 gear carb  0.27407284
121 carb carb  1.00000000