How can i Transpose the table to matrix

Question

I have a data frame, like this:

n1    n2   freq  
A     B     10
W     Q      9
A     E     23
A     F     31
A     W      9
B     Q      25
B     E      54
B     F      33
B     W      14
A     Q      4
F     E      1
E     W      43
Q     E      67
F     W      10
Q     F      6

How can I transpose the data to a matrix like this?

     A   B   E   F    W    Q 
A    1  10  23  31    9    4
B   10   1  54  33   14   25
E   23  54   1   1   43   67
F   31  33   1   1   10    6
W    9  14  43  10    1    9
Q    4  25  67   6    9    1

The diag(data) <- 1

Answer 1

Here's an alternative approach.

Assuming you're starting with a data.frame named "mydf", you can try:

## The "n" columns 
Cols <- c("n1", "n2")

## The factor levels
Levs <- sort(unique(unlist(mydf[Cols])))

## Applying the factor levels to all "n" columns
mydf[Cols] <- lapply(mydf[Cols], function(x) factor(x, Levs))

## xtabs is your friend
out <- xtabs(freq ~ n1 + n2, mydf)
out <- out + t(out)

## replace the diagonal
diag(out) <- 1
out
#    n2
# n1   A  B  E  F  Q  W
#   A  1 10 23 31  4  9
#   B 10  1 54 33 25 14
#   E 23 54  1  1 67 43
#   F 31 33  1  1  6 10
#   Q  4 25 67  6  1  9
#   W  9 14 43 10  9  1

Answer 2

DF
##    n1 n2 freq
## 1   A  B   10
## 2   W  Q    9
## 3   A  E   23
## 4   A  F   31
## 5   A  W    9
## 6   B  Q   25
## 7   B  E   54
## 8   B  F   33
## 9   B  W   14
## 10  A  Q    4
## 11  F  E    1
## 12  E  W   43
## 13  Q  E   67
## 14  F  W   10
## 15  Q  F    6

N <- sort(unique(unlist(DF[, 1:2])))
N
## [1] "A" "B" "E" "F" "Q" "W"

# First we create empty matrix with dims of expected result
RES <- matrix(NA, nrow = length(N), ncol = length(N), dimnames = list(N, N))


# now lets populate the matrix
RES[as.matrix(DF[, 1:2])] <- DF[, 3]
# We repeat the step with coordinates inverted. note 1:2 vs 2:1 As the matrix is supposed to be symmetric
RES[as.matrix(DF[, 2:1])] <- DF[, 3]
# Set diagonal to 1
RES[cbind(N, N)] <- 1
# expected result
RES
##    A  B  E  F  Q  W
## A  1 10 23 31  4  9
## B 10  1 54 33 25 14
## E 23 54  1  1 67 43
## F 31 33  1  1  6 10
## Q  4 25 67  6  1  9
## W  9 14 43 10  9  1