I have data that look like this:
dt = data.table(from = c('a','a','b','b','c','c','c','d'), to = c('c','d','a','c','a','b','d','c'), value = c(1,2,4,6,7,8,9,10))
> dt
from to value
1: a c 1
2: a d 2
3: b a 4
4: b c 6
5: c a 7
6: c c 8
7: c d 9
8: d c 10
I would like to turn this into a matrix where from becomes the row names, to is the column names and the resulting cells are filled by value.
Where there are no rows in dt for a given from & to combination, I'd like it filled by 0 in the matrix. So the ideal result would look like this:
a b c d
a 0 0 1 2
b 4 0 6 0
c 7 8 0 9
d 0 0 10 0
The closest I could come to is this, which unfortunately repeats the values.
> matrix(dt$value, nrow = length(unique(dt$from)), ncol = length(unique(dt$to)), byrow = T,
+ dimnames = list(sort(unique(dt$from)),
+ sort(unique(dt$to))))
a b c d
a 1 2 4 6
b 7 8 9 10
c 1 2 4 6
d 7 8 9 10
How can I edit this so that instead of the values being repeated, empty spaces (i.e. combinations of from and to that do not exist in dt are filled with 0)?
