How to convert a 3 columns data to a co-existance matrix in R?

Question

I've 3 columns data:

var1 var2    Z
   1   2    1.2
   3   4    5.4
   1   4    2.3

And I Want to convert it to a co-existance matrix like this one below:

       1   2    3    4
  1    0   1.2  0   2.3
  2    0   0    0   0
  3    0   0    0   5.4
  4    0   0    0   0

Is there any way to do it with R language? Thank you in advance for your time.

Answer 1

Actually I am not familiar with R language but you can use this type of logic I have done in java..

int[] row1 = {1, 3, 1}; 
int[] row2 = {2, 4, 4};
double[] z = {1.2, 5.4, 2.3};

        for (int i = 0; i < 4; i++) {
            for (int j = 0; j < 4; j++) {
                for (int k = 0; k < row1.length; k++) {
                    if (row1[k] == i && row2[k] == j) {
                        System.out.println(z[k]);
                    }
                }
            }
            System.out.println("\n");
        }

Answer 2

I'll assume you're starting with a data.frame, perhaps something like this:

mydf <- structure(list(var1 = c(1L, 3L, 1L), var2 = c(2L, 4L, 4L), Z = c(1.2, 
    5.4, 2.3)), .Names = c("var1", "var2", "Z"), row.names = c(NA, 
    3L), class = "data.frame")

There are several approaches that you can use, including:

Matrix indexing

D <- max(unlist(mydf[1:2]))
M <- matrix(0, ncol = D, nrow = D)
M[as.matrix(mydf[1:2])] <- mydf[[3]]
M
#      [,1] [,2] [,3] [,4]
# [1,]    0  1.2    0  2.3
# [2,]    0  0.0    0  0.0
# [3,]    0  0.0    0  5.4
# [4,]    0  0.0    0  0.0

Factoring "var1" and "var2" and using xtabs, spread, or dcast

Note: All of the following answers use a dataset where "var1" and "var2" have been converted to factors.

D <- max(unlist(mydf[1:2]))
mydf[1:2] <- lapply(mydf[1:2], factor, seq_len(D))
str(mydf)
# 'data.frame': 3 obs. of  3 variables:
#  $ var1: Factor w/ 4 levels "1","2","3","4": 1 3 1
#  $ var2: Factor w/ 4 levels "1","2","3","4": 2 4 4
#  $ Z   : num  1.2 5.4 2.3

xtabs(Z ~ var1 + var2, mydf)
#     var2
# var1   1   2   3   4
#    1 0.0 1.2 0.0 2.3
#    2 0.0 0.0 0.0 0.0
#    3 0.0 0.0 0.0 5.4
#    4 0.0 0.0 0.0 0.0

library(tidyr)
mydf %>% spread(var2, Z, fill = 0, drop = FALSE)
#   var1 1   2 3   4
# 1    1 0 1.2 0 2.3
# 2    2 0 0.0 0 0.0
# 3    3 0 0.0 0 5.4
# 4    4 0 0.0 0 0.0

library(data.table)
dcast(as.data.table(mydf), var1 ~ var2, value.var = "Z", fill = 0, drop = FALSE)
#    var1 1   2 3   4
# 1:    1 0 1.2 0 2.3
# 2:    2 0 0.0 0 0.0
# 3:    3 0 0.0 0 5.4
# 4:    4 0 0.0 0 0.0