How to convert the correlation matrix into a data frame with combination of variables and their correlations in R?

The cor function in R helps us to find the correlation matrix from a data frame or a matrix but the output of it always a matrix as intended. We might want to convert that matrix into a data frame which consists of all combination of variables with their correlation value. It can be done by reading the correlation matrix with as.table and converting that table into a data frame with as.data.frame.

Example

Consider the below data frame −

Live Demo

x1<-rnorm(20,0.5)
x2<-rnorm(20,1.5)
x3<-rnorm(20,2)
x4<-rnorm(20,2.5)
x5<-rnorm(20,3)
df1<-data.frame(x1,x2,x3,x4,x5)
df1

Output

      x1          x2       x3       x4       x5
1 1.01241819 1.6263879 1.0964170 3.589784 3.522261
2 -0.07203883 2.1113559 2.0853013 2.601339 2.838322
3 -0.40573390 0.1282928 2.6869231 1.456986 3.653687
4 -0.10169987 1.5902541 2.7803958 3.327289 2.746533
5 -0.55939989 -0.2573458 2.7141833 3.860593 1.779362
6 0.20375614 0.4087237 1.5304197 3.013231 3.345237
7 2.14060964 2.1761154 2.9756934 2.502471 2.741643
8 -0.92393329 2.5695567 0.7567924 1.423767 4.843986
9 0.19310980 1.9560434 3.5774616 3.516024 2.958461
10 0.47001355 1.4118910 2.1074962 3.665488 2.309292
11 0.43043496 -0.1977611 1.5456945 2.880366 2.745999
12 0.16766544 1.1394380 1.6436382 1.460217 4.168087
13 0.05919660 0.3138259 3.2329019 2.195498 3.102219
14 -0.27852409 1.8581345 1.0134072 1.267640 2.249398
15 1.00489665 1.3941321 0.9242278 3.652576 2.713325
16 -0.47215942 1.6161371 3.8449875 3.999393 3.470586
17 1.47659684 0.7814303 3.0221436 3.003357 2.675620
18 1.17223424 0.8835198 2.2660368 3.529340 3.374545
19 -0.30223845 3.1234578 2.5363014 2.290078 2.470584
20 0.09605103 1.9410643 -0.4640839 2.381046 1.690303

Calculating the correlation matrix and converting it into a data frame with combinations of variables and their correlation −

Example

Correlation_Matrix_as_Data_Frame_Of_Combinations<-
as.data.frame(as.table(cor(df1)))
Correlation_Matrix_as_Data_Frame_Of_Combinations

Output

 Var1 Var2     Freq
1 x1   x1    1.00000000
2 x2   x1    -0.04107413
3 x3   x1    0.05626009
4 x4   x1    0.29686054
5 x5   x1    -0.14522277
6 x1   x2    -0.04107413
7 x2   x2    1.00000000
8 x3   x2    -0.15925174
9 x4   x2    -0.17870287
10 x5  x2    0.06219340
11 x1  x3    0.05626009
12 x2  x3    -0.15925174
13 x3  x3    1.00000000
14 x4  x3    0.31640783
15 x5  x3    0.03577516
16 x1  x4    0.29686054
17 x2  x4    -0.17870287
18 x3  x4    0.31640783
19 x4  x4    1.00000000
20 x5  x4    -0.31792430
21 x1  x5    -0.14522277
22 x2  x5    0.06219340
23 x3  x5    0.03577516
24 x4  x5    -0.31792430
25 x5  x5    1.00000000

Let’s have a look at another example −

Example

Live Demo

y1<-sample(1:10,20,replace=TRUE)
y2<-sample(1:5,20,replace=TRUE)
y3<-sample(1:12,20,replace=TRUE)
y4<-sample(1:15,20,replace=TRUE)
y5<-sample(1:20,20,replace=TRUE)
df2<-data.frame(y1,y2,y3,y4,y5)
df2

Output

 y1 y2 y3 y4 y5
1 6  4  3 12 10
2 5  3  7 14 10
3 1  1  11 7 16
4 1  4  2 12 11
5 9  4  11 8 15
6 5  2  11 6 17
7 8  4  8  2 17
8 4  2  6  5 17
9 1  5  11  2 2
10 9 4  5  2 12
11 6 3  9  14 8
12 9 5  9  3 20
13 9 5  5  3 13
14 4 1  7 15 15
15 9 1  5  8  3
16 6 4  7  7 11
17 1 5  5  13 2
18 9 2  6 15 15
19 6 3  12 11 8
20 7 1  9  3  6

Example

Data_Frame_Of_Combinations_of_Correlations<-as.data.frame(as.table(cor(df2)))
Data_Frame_Of_Combinations_of_Correlations

Output

 Var1 Var2    Freq
1 y1  y1    1.00000000
2 y2  y1    0.02121834
3 y3  y1    -0.02311103
4 y4  y1    -0.24280927
5 y5  y1    0.28378665
6 y1  y2    0.02121834
7 y2  y2    1.00000000
8 y3  y2    -0.16734209
9 y4  y2    -0.23599572
10 y5 y2    -0.08963607
11 y1 y3    -0.02311103
12 y2 y3    -0.16734209
13 y3 y3    1.00000000
14 y4 y3    -0.24489247
15 y5 y3    0.12218753
16 y1 y4    -0.24280927
17 y2 y4    -0.23599572
18 y3 y4    -0.24489247
19 y4 y4    1.00000000
20 y5 y4    -0.16567707
21 y1 y5    0.28378665
22 y2 y5    -0.08963607
23 y3 y5    0.12218753
24 y4 y5    -0.16567707
25 y5 y5    1.00000000

Updated on: 08-Sep-2020

2K+ Views