I have all the pairwise correlations and would like to construct the var-covariance matrix in order to do some standard analysis on that matrix. Here's a sample data for the covariances, the first two columns are the "ids" while the third column shows the covariance between the "ids".
data<-data.frame("id1" = c("a","b","c","a","a","b"),
"id2" = c("a","b","c","b","c","c"),
"cov"=c(1,1,1,0.1,0.3,0.4))
Base-R solution:
nm <- unique(data$id1) ## row/col names
v <- matrix(NA,length(nm),length(nm),dimnames=list(nm,nm)) ## set up template
v[cbind(data$id1,data$id2)] <- data$cov ## fill in upper triangle
v[is.na(v)] <- t(v)[is.na(v)] ## symmetrize
An easy dplyr solution is to make the data.frame wider with the help of pivot_wider, i.e.
data<-data.frame("id1" = c("a","b","c","a","a","b"),
"id2" = c("a","b","c","b","c","c"),
"cov"=c(1,1,1,0.1,0.3,0.4))
tidyr::pivot_wider(data,
id_cols = c(cov, id2),
names_from = id1,
values_from = cov)
which yields the output
id2 a b c
<fct> <dbl> <dbl> <dbl>
1 a 1 NA NA
2 b 0.1 1 NA
3 c 0.3 0.4 1
Since a covariance-matrix is symmetric, it's done.
