Transform Correlation Matrix into dataframe with records for each row column pair
For matrix m
, you could do:
data.frame(row=rownames(m)[row(m)], col=colnames(m)[col(m)], corr=c(m))
# row col corr
# 1 60516 60516 1.000000000
# 2 45264 60516 -0.370793010
# 3 02117 60516 -0.082897940
# 4 60516 45264 -0.370793012
# 5 45264 45264 1.000000000
# 6 02117 45264 0.005145601
# 7 60516 02117 -0.082897941
# 8 45264 02117 0.005145601
# 9 02117 02117 1.000000000
But if your matrix is symmetrical and if you are not interested in the diagonal, then you can simplify it to:
data.frame(row=rownames(m)[row(m)[upper.tri(m)]],
col=colnames(m)[col(m)[upper.tri(m)]],
corr=m[upper.tri(m)])
# row col corr
# 1 60516 45264 -0.370793012
# 2 60516 02117 -0.082897941
# 3 45264 02117 0.005145601