Creating a matrix indexed by names of vectors

Question

Basically, I have several frequency tables d1 and d2. Suppose I have:

UPDATE2: The actual structure of d1 is table. So d1 is obtained by d1 <- table(datavector), similarly for d2.

d1
  Value     0    1    2    3    4    9                         
  Freq     25   30  100   10   10   10

d2
  Value     0    1    3    5    7   11    13
   Freq    25   30  100   10   10   10    12

Problem: I want to produce a matrix with rows corresponding to d1 and d2 and the columns corresponding to all the distinct "Values" seen in d1 and d2. So I want to produce a matrix with rows and columns that looks like this:

     [,"0"] [,"1"] [,"2"] [,"3"] [,"4"] [,"5"] [,"7"] [,"9"] [,"11"] [,"13"]
[1,]    25     30    100    10     10    0       0      10     0         0
[2,]    25     30     0     100     0    10      10     0      10        12

Notice that, there is no column number 6 , 8, and 10 because they do not appear in the frequency table. Eventually, I am trying to put this matrix into a function image.plot().

UPDATE 1: I think I can allow column number 6,8 and 10 appear in the matrix, but eventually I will have to write a for loop to eliminate columns which consist of zeros entries only.

UPDATE 3: Please note that I am in fact working with 250 data vectors and hence 250 tables (each with different length / dimension). So, I am looking for an efficient solution

UPDATE 4: Please treat the above as an abstract of what I want to achieve. The real dataset is as follow:

> dput(head(get.dist(fnn[1])))
structure(c(0.999214894571557, 0.000134589502018843, 4.48631673396142e-05, 
2.24315836698071e-05, 6.72947510094213e-05, 8.97263346792284e-05, 
2.24315836698071e-05, 4.48631673396142e-05, 4.48631673396142e-05, 
2.24315836698071e-05, 2.24315836698071e-05, 6.72947510094213e-05, 
2.24315836698071e-05, 2.24315836698071e-05, 4.48631673396142e-05, 
2.24315836698071e-05, 6.72947510094213e-05, 2.24315836698071e-05
), class = "table", .Dim = 18L, .Dimnames = structure(list(d = c("0", 
"1", "2", "3", "4", "5", "8", "9", "11", "12", "15", "16", "17", 
"18", "20", "22", "24", "31")), .Names = "d"))

> dput(head(get.dist(fnn[2])))
structure(c(0.71161956034096, 0.199147599820547, 0.0644010767160162, 
0.0147599820547331, 0.00327501121579183, 0.000807537012113055, 
6.72947510094213e-05, 0.000785105428443248, 0.000179452669358457, 
0.000134589502018843, 0.000112157918349035, 4.48631673396142e-05, 
6.72947510094213e-05, 0.00307312696276357, 0.00107671601615074, 
0.000336473755047106, 6.72947510094213e-05, 2.24315836698071e-05, 
2.24315836698071e-05), class = "table", .Dim = 19L, .Dimnames = structure(list(
    d = c("0", "1", "2", "3", "4", "5", "6", "9", "10", "11", 
    "35", "36", "37", "38", "39", "40", "41", "42", "43")), .Names = "d"))

> dput(head(get.dist(fnn[3])))
structure(c(0.747353073126963, 0.13138178555406, 0.0295423956931359, 
0.0139075818752804, 0.0119560340960072, 0.0151861821444594, 0.0243382682817407, 
0.00697622252131, 0.00255720053835801, 0.00161507402422611, 0.00293853746074473, 
0.00116644235082997, 0.004419021982952, 0.0018842530282638, 0.000628084342754598, 
0.00053835800807537, 0.000448631673396142, 0.000493494840735756, 
0.000650515926424406, 0.000403768506056528, 0.000269179004037685, 
0.000179452669358457, 0.000269179004037685, 0.000179452669358457, 
8.97263346792284e-05, 0.000246747420367878, 4.48631673396142e-05, 
4.48631673396142e-05, 4.48631673396142e-05, 2.24315836698071e-05, 
2.24315836698071e-05, 4.48631673396142e-05, 2.24315836698071e-05, 
2.24315836698071e-05, 2.24315836698071e-05, 2.24315836698071e-05, 
2.24315836698071e-05, 2.24315836698071e-05, 2.24315836698071e-05
), class = "table", .Dim = 39L, .Dimnames = structure(list(d = c("0", 
"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", 
"13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", 
"24", "25", "26", "27", "28", "30", "32", "33", "34", "36", "37", 
"38", "43", "54", "67")), .Names = "d"))

> dput(head(get.dist(fnn[4])))
structure(c(0.217743382682817, 0.49416778824585, 0.135150291610588, 
0.0331987438313145, 0.0243831314490803, 0.0431135038133692, 0.022790489008524, 
0.00912965455361149, 0.00614625392552714, 0.00937640197397936, 
0.00244504262000897, 0.000560789591745177, 0.000493494840735756, 
0.000448631673396142, 0.000336473755047106, 0.000112157918349035, 
0.000201884253028264, 4.48631673396142e-05, 4.48631673396142e-05, 
2.24315836698071e-05, 2.24315836698071e-05, 4.48631673396142e-05, 
2.24315836698071e-05), class = "table", .Dim = 23L, .Dimnames = structure(list(
    d = c("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", 
    "11", "12", "13", "14", "15", "16", "17", "18", "19", "23", 
    "25", "45")), .Names = "d"))

Answer 1

I was using dataframes, but if d1 and d2 were matrices this should still work if you removed the unlist calls:

 M <- matrix(0, nrow=2, ncol=12 )
 colnames(M) <- as.character(0:11)
 M[1 , as.character(d1[1 , 2:7]) ] <- unlist(d1[2, 2:7 ])
 M

#      0  1   2  3  4 5 6 7 8  9 10 11
#[1,] 25 30 100 10 10 0 0 0 0 10  0  0
#[2,]  0  0   0  0  0 0 0 0 0  0  0  0

 M[2 , as.character(d2[1 , 2:7]) ] <- unlist(d2[2, 2:7 ])
 M
#-------------------
      0  1   2   3  4  5 6  7 8  9 10 11
[1,] 25 30 100  10 10  0 0  0 0 10  0  0
[2,] 25 30   0 100  0 10 0 10 0  0  0 10

Converting my examples to matrices (which inherit their indexing from the matrix class):

 d1a <-data.matrix(d1[,-1])
 rownames(d1a) <- d1[,1]
 d2a <-data.matrix(d2[,-1])
 rownames(d2a) <- d2[,1]
 M[1 , as.character(d1a[1 , ]) ] <-d1a[2,  ]
 M[2 , as.character(d2a[1 , ]) ] <-d2a[2,  ]
 M
#---------
      0  1   2   3  4  5 6  7 8  9 10 11
[1,] 25 30 100  10 10  0 0  0 0 10  0  0
[2,] 25 30   0 100  0 10 0 10 0  0  0 10

If as thelatemail thinks (although I do not) these are one row tables then it's even easier:

 M[2 , colnames(d2b) ] <-d2b
 M[2 , colnames(d1b) ] <-d1b
 M

      0  1   2   3  4  5 6  7 8  9 10 11
[1,] 25 30 100  10 10  0 0  0 0 10  0  0
[2,] 25 30   0 100  0 10 0 10 0  0  0 10

And please, please, please, no for-loops to be used on these:

> M[ , !colSums(M==0)==2]
      0  1   2   3  4  5  7  9 11
[1,] 25 30 100  10 10  0  0 10  0
[2,] 25 30   0 100  0 10 10  0 10

You don't need to remove any zero columns if you don't create any:

You can probably create dist.list this way:

dist.list= lapply(fnn, get.dist)
# 3 element example built from your example

dist.list<-{}
dist.list[[1]] <-
structure(c(0.999214894571557, 0.000134589502018843, 4.48631673396142e-05, 
2.24315836698071e-05, 6.72947510094213e-05, 8.97263346792284e-05, 
2.24315836698071e-05, 4.48631673396142e-05, 4.48631673396142e-05, 
2.24315836698071e-05, 2.24315836698071e-05, 6.72947510094213e-05, 
2.24315836698071e-05, 2.24315836698071e-05, 4.48631673396142e-05, 
2.24315836698071e-05, 6.72947510094213e-05, 2.24315836698071e-05
), class = "table", .Dim = 18L, .Dimnames = structure(list(d = c("0", 
"1", "2", "3", "4", "5", "8", "9", "11", "12", "15", "16", "17", 
"18", "20", "22", "24", "31")), .Names = "d"))

 dist.list[[2]] <-
structure(c(0.71161956034096, 0.199147599820547, 0.0644010767160162, 
0.0147599820547331, 0.00327501121579183, 0.000807537012113055, 
6.72947510094213e-05, 0.000785105428443248, 0.000179452669358457, 
0.000134589502018843, 0.000112157918349035, 4.48631673396142e-05, 
6.72947510094213e-05, 0.00307312696276357, 0.00107671601615074, 
0.000336473755047106, 6.72947510094213e-05, 2.24315836698071e-05, 
2.24315836698071e-05), class = "table", .Dim = 19L, .Dimnames = structure(list(
    d = c("0", "1", "2", "3", "4", "5", "6", "9", "10", "11", 
    "35", "36", "37", "38", "39", "40", "41", "42", "43")), .Names = "d"))

 dist.list[[3]] <-
structure(c(0.747353073126963, 0.13138178555406, 0.0295423956931359, 
0.0139075818752804, 0.0119560340960072, 0.0151861821444594, 0.0243382682817407, 
0.00697622252131, 0.00255720053835801, 0.00161507402422611, 0.00293853746074473, 
0.00116644235082997, 0.004419021982952, 0.0018842530282638, 0.000628084342754598, 
0.00053835800807537, 0.000448631673396142, 0.000493494840735756, 
0.000650515926424406, 0.000403768506056528, 0.000269179004037685, 
0.000179452669358457, 0.000269179004037685, 0.000179452669358457, 
8.97263346792284e-05, 0.000246747420367878, 4.48631673396142e-05, 
4.48631673396142e-05, 4.48631673396142e-05, 2.24315836698071e-05, 
2.24315836698071e-05, 4.48631673396142e-05, 2.24315836698071e-05, 
2.24315836698071e-05, 2.24315836698071e-05, 2.24315836698071e-05, 
2.24315836698071e-05, 2.24315836698071e-05, 2.24315836698071e-05
), class = "table", .Dim = 39L, .Dimnames = structure(list(d = c("0", 
"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", 
"13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", 
"24", "25", "26", "27", "28", "30", "32", "33", "34", "36", "37", 
"38", "43", "54", "67")), .Names = "d"))

all.names <- lapply(dist.list, names)
uniq.names <- unique(unlist(all.names))
M <- matrix(0, nrow=length(dist.list), ncol=length(uniq.names) )
colnames(M) <- uniq.names
for (i in seq_along(dist.list) ) {
          M[i, all.names[[i]] ] <- dist.list[[i]] }
M

First 20 columns

             0            1            2            3            4
[1,] 0.9992149 0.0001345895 4.486317e-05 2.243158e-05 6.729475e-05
[2,] 0.7116196 0.1991475998 6.440108e-02 1.475998e-02 3.275011e-03
[3,] 0.7473531 0.1313817856 2.954240e-02 1.390758e-02 1.195603e-02
                5            8            9           11           12
[1,] 8.972633e-05 2.243158e-05 4.486317e-05 4.486317e-05 2.243158e-05
[2,] 8.075370e-04 0.000000e+00 7.851054e-04 1.345895e-04 0.000000e+00
[3,] 1.518618e-02 2.557201e-03 1.615074e-03 1.166442e-03 4.419022e-03
               15           16           17           18           20
[1,] 2.243158e-05 6.729475e-05 2.243158e-05 2.243158e-05 4.486317e-05
[2,] 0.000000e+00 0.000000e+00 0.000000e+00 0.000000e+00 0.000000e+00
[3,] 5.383580e-04 4.486317e-04 4.934948e-04 6.505159e-04 2.691790e-04
# remainder excluded

Answer 2

Here is an option using Reduce that seems to work given the provided data:

# make a list including your 3 dput parts
keylist <- list(d1,d2,d3)
result <- Reduce(function(...) merge(..., by="d", all=T), keylist)
result <- transform(result,row.names=d,d=NULL)
result <- t(result)
rownames(result) <- NULL

It seems to work:

> result[,c(1:2,44:45)]
             0            1           54           67
[1,] 0.9992149 0.0001345895           NA           NA
[2,] 0.7116196 0.1991475998           NA           NA
[3,] 0.7473531 0.1313817856 2.243158e-05 2.243158e-05

Answer 3

If you turn your d1 and d2 into data.tables, you can easily merge them by a common key:

library(data.table)

> d1 <- data.table(value = c(0, 1, 2, 3, 4, 9), freq = c(25, 30, 100, 10, 10, 10))
> d2 <- data.table(value = c(0, 1, 3, 5, 7, 11), freq = c(25, 30, 100, 10, 10, 10))
> setkey(d1, value)
> setkey(d2, value)
> merge(d1, d2, all = TRUE)
   value freq.x freq.y
1:     0     25     25
2:     1     30     30
3:     2    100     NA
4:     3     10    100
5:     4     10     NA
6:     5     NA     10
7:     7     NA     10
8:     9     10     NA
9:    11     NA     10

You can then convert the resulting data.table to a matrix, replace NAs with 0s, etc.