CODEWITHSUNDEEP
rmatrixreshape
user4005152
user4005152

Reputation:

Generate symmetric matrix using variable pairs from data.frame

How can I efficiently generate a symmetric matrix with 1s in the diagonal from a data frame like this (original df has ~12000 combinations of variables):

mydata <- data.frame(Var1 = c("A", "A", "B"), Var2 = c("B", "C", "C"), values = c(2, 3, 6))

Var1 Var2 values
1    A    B      2
2    A    C      3
3    B    C      6

I'd like to have the following output:

mymatrix <- matrix(c(1,2,3,2,1,6,3,6,1), ncol = 3, dimnames = list(c("A", "B", "C"), c("A", "B", "C")))


  A B C
A 1 2 3
B 2 1 6
C 3 6 1

dcast in itself won't give a proper symmetric matrix and maybe there is an efficient way to do it.

Upvotes: 1

Views: 1261

Answers (1)

MrFlick
MrFlick

Reputation: 206207

The easiest way it just to copy the upper.tri to the lower.tri. For example

#create new matrix
vals<-sort(unique(c(as.character(mydata$Var1), as.character(mydata$Var2))))
nm<-matrix(NA, nrow=length(vals), ncol=length(vals), dimnames=list(vals, vals))
diag(nm)<-1

#fill
nm[as.matrix(mydata[, 1:2])] <- mydata[,3]

#symmetric
nm[lower.tri(nm)] <- nm[upper.tri(nm)]
nm

#   A B C
# A 1 2 3
# B 2 1 6
# C 3 6 1

Of course this assumes all the values you are filling in are in the upper triangle of the matrix to begin with

Alternatively, you could double fill

nm <- matrix(NA, nrow=length(vals), ncol=length(vals), dimnames=list(vals, vals))
diag(nm) <- 1    

#fill
nm[as.matrix(mydata[, 1:2])] <- mydata[,3]
#fill reversed
nm[as.matrix(mydata[, 2:1])] <- mydata[,3]
nm

#   A B C
# A 1 2 3
# B 2 1 6
# C 3 6 1

which doesn't assume anything about the order in which the matrix is filled.

Finally, you could coalesce with the transpose (if you don't mind using a non-base helper function)

nm<-matrix(NA, nrow=length(vals), ncol=length(vals), dimnames=list(vals, vals))
diag(nm)<-1

#fill
nm[as.matrix(mydata[, 1:2])] <- mydata[,3]
#coalesce
nm<-coalesce(nm, t(nm))
nm

#   A B C
# A 1 2 3
# B 2 1 6
# C 3 6 1

Upvotes: 1

Related Questions