Create a square matrix from a data frame

Question

I'm having trouble creating a square matrix from my data.frame. Now my data looks something like this:

var1 var2 value AB 4 CD 5 DA 2 BD 1 

I am trying to convert data.frame to a matrix that looks like this:

  ABCD A 0 4 0 2 B 4 0 0 1 C 0 0 0 5 D 2 1 5 0 

I tried many functions from another package available in R, but still can't find a solution.

Answer 1

If we create all columns of factor characters with levels "A", "B", "C", "D", we can use xtabs without dropping any columns.

Unfortunately, the resulting matrix is ​​not symmetrical.

 library('tidyverse') df <- tribble( ~var1, ~var2, ~value, 'A', 'B', 4, 'C', 'D', 5, 'D', 'A', 2, 'B', 'D', 1 ) df %>% mutate_if(is.character, factor, levels=c('A', 'B', 'C', 'D')) %>% xtabs(value ~ var1 + var2, ., drop.unused.levels = F) # var2 # var1 ABCD # A 0 4 0 0 # B 0 0 0 1 # C 0 0 0 5 # D 2 0 0 0 

To make it symmetrical, I just added its transpose to myself. However, this is a bit like hacks.

 df %>% mutate_if(is.character, factor, levels=c('A', 'B', 'C', 'D')) %>% xtabs(value ~ var1 + var2, ., drop.unused.levels = F) %>% '+'(., t(.)) # var2 # var1 ABCD # A 0 4 0 2 # B 4 0 0 1 # C 0 0 0 5 # D 2 1 5 0 

Answer 2

Here is the basic R method using matrix indexing on character vectors.

 ## set up matrix # get names for row and columns nameVals <- sort(unique(unlist(dat[1:2]))) # construct 0 matrix of correct dimensions with row and column names myMat <- matrix(0, length(nameVals), length(nameVals), dimnames = list(nameVals, nameVals)) # fill in the matrix with matrix indexing on row and column names myMat[as.matrix(dat[c("var1", "var2")])] <- dat[["value"]] 

It returns

 myMat ABCD A 0 4 0 0 B 0 0 0 1 C 0 0 0 5 D 2 0 0 0 

For more information on how this indexing works, see the "Matrices and Arrays" section of the help file ?"[" . The fourth paragraph discusses this form of indexing.

Note that I assume that the first two variables are symbolic vectors, not factors. This makes it a little easier since I don't need to use as.character to force them.

To convert the result to a data.frame file, simply wrap it in as.data.frame .

<strong> data

 dat <- structure(list(var1 = c("A", "C", "D", "B"), var2 = c("B", "D", "A", "D"), value = c(4L, 5L, 2L, 1L)), .Names = c("var1", "var2", "value"), class = "data.frame", row.names = c(NA, -4L))