fill matrix more efficiently

Question

I have a data.frame DF as follows:

u <- c(14381,  20547,  17172,  17753,  667,    17753,  914,    10802,  3346,   17753, 
667,    11113,  914,    914,    17753,  11113,  10802,  20547,  14381,  11113, 
139,    17753,  17172,  10802,  14381,  20547,  139,    14381,  17753,  10802, 
10802,  139,    11113,  10802,  11113,  3346,   11113,  11113,  11113,  10802, 
17172,  20547,  914,    17172,  3346,   139,    11113,  139,    914,    10802, 
14381,  10802,  17172,  10802,  3346,   17172,  10802,  20547,  15679,  17753, 
11113,  11113,  667,    15679,  667,    1204,   355,    1204,   400,    14351, 
16405,  12760,  16405,  12760,  11072,  1204,   14351,  265,    16405,  4993,  
400,    355,    16405,  4993,   355,    14351,  14351,  14351,  400,    11021, 
11072,  1204,   12760,  265,    12760,  265,    400,    265,    1204,   12760, 
16405,  11072,  16405,  1204,   11072,  11021,  265,    11072,  18309,  11021, 
18309,  4993,   12760,  1204,   11021,  18309,  18309,  265,    14351,  14351, 
12759,  12759,  4993,   11038,  12759,  12759,  11038,  12759,  18309,  18309, 
1,      4,      4,      3,      6,      1,      1,      2,      10,     11,    
1,      2,      1,      7,      1,      2,      1,      1,      1,      1,     
5,      1,      2,      3,      2,      2,      2,      2,      1,      1,     
5,      1,      7,      2,      1,      2,      2,      2,      2,      1,     
2,      2,      1,      4,      1,      3,      1,      1,      2,      3,     
2,      3,      1,      1,      2,      1,      1,      1,      1,      1,     
1,      2,      2,      1,      1)

DF <- as.data.frame(matrix(u, ncol = 3, nrow = 65, byrow = FALSE))

Now, I need to construct a matrix MAT as follows:

• the first column of DF contains the row names of MAT
• the second column of DF contains the column names of MAT
• the third column of DF contains the cell value of MAT so, MAT("14381", "1204") = 1 and MAT("20547", "355") = 4, et cetera
• all other cells should be 0

The question is, how do I efficiently construct the matrix from the data frame above? My current approach is as follows:

DF[, 1] <- as.character(DF[, 1])  # turn into characters
DF[, 2] <- as.character(DF[, 2])  # turn into characters
rows <- unique(DF[,1])  # get the row names
cols <- unique(DF[,2])  # get the column names
MAT <- matrix(0, nrow = length(rows), ncol = length(cols)) # prefill with 0's
dimnames(MAT) <- list(rows, cols)
for (i in 1:nrow(DF)) {
  MAT[DF[i, 1], DF[i, 2]] <- DF[i, 3]
}

This works, but does not seem quite efficient. As I need to repeat this task about 10K times, efficiency will pay off. How can I circumvent the loop (with the constant copying of MAT) and do this more efficiently? I am thinking dplyr or data.table, but don't really know how to do this with these packages. Can anyone help?

Answer 1

Using tidyr

library(tidyr)
spread(DF, V2, V3, fill = 0)