I am very confused by the R package Matrix.utils and its implementation of merge.Matrix(). I want to merge two matrices with 0 common values, but merge common column names and fill the rest with zeros.
The results are inconsistent and sensitive to whether merge() or merge.Matrix() is specified. I expected this to be similar to the dplyr::join() function but this is not true.
Simulating the data I plan to use:
mtx.x <- sample(1:100, 100) ; mtx.x <- matrix(mtx.x, nrow = 10)
mtx.y <- sample(1:100, 100) ; mtx.y <- matrix(mtx.y, nrow = 10)
colnames(mtx.x) <- letters[1:10] ; colnames(mtx.y) <- letters[6:15]
mtx.x ; mtx.y
a b c d e f g h i j
[1,] 82 61 76 36 27 67 85 38 29 87
[2,] 83 89 43 70 81 30 35 17 39 95
[3,] 1 75 69 54 66 3 10 47 93 73
[4,] 52 98 26 88 51 64 31 72 13 92
[5,] 44 74 86 9 63 58 50 56 6 49
[6,] 24 16 77 12 55 97 18 45 14 40
[7,] 11 5 79 94 2 80 37 15 41 42
[8,] 100 84 65 59 34 62 53 60 99 28
[9,] 19 78 8 25 96 21 90 46 68 71
[10,] 32 20 7 4 57 91 22 48 33 23
f g h i j k l m n o
[1,] 24 22 8 94 89 7 50 93 40 4
[2,] 63 80 32 44 64 83 16 96 46 47
[3,] 85 30 81 95 23 91 19 92 99 52
[4,] 21 55 61 58 27 76 67 65 37 14
[5,] 9 66 12 2 41 11 56 84 87 39
[6,] 18 57 88 3 68 100 74 62 82 25
[7,] 70 90 43 54 72 86 69 20 29 51
[8,] 1 59 60 45 79 75 15 5 73 10
[9,] 38 28 26 17 53 36 97 13 77 49
[10,] 6 71 98 35 42 31 78 33 48 34
Case 1: merge() with all.x/all.y set to TRUE does what I want
merge(x = mtx.x, y = mtx.y,
all.x = T, all.y = T)
f g h i j a b c d e k l m n o
1 1 59 60 45 79 NA NA NA NA NA 75 15 5 73 10
2 3 10 47 93 73 1 75 69 54 66 NA NA NA NA NA
3 6 71 98 35 42 NA NA NA NA NA 31 78 33 48 34
4 9 66 12 2 41 NA NA NA NA NA 11 56 84 87 39
5 18 57 88 3 68 NA NA NA NA NA 100 74 62 82 25
6 21 55 61 58 27 NA NA NA NA NA 76 67 65 37 14
7 21 90 46 68 71 19 78 8 25 96 NA NA NA NA NA
8 24 22 8 94 89 NA NA NA NA NA 7 50 93 40 4
9 30 35 17 39 95 83 89 43 70 81 NA NA NA NA NA
10 38 28 26 17 53 NA NA NA NA NA 36 97 13 77 49
11 58 50 56 6 49 44 74 86 9 63 NA NA NA NA NA
12 62 53 60 99 28 100 84 65 59 34 NA NA NA NA NA
13 63 80 32 44 64 NA NA NA NA NA 83 16 96 46 47
14 64 31 72 13 92 52 98 26 88 51 NA NA NA NA NA
15 67 85 38 29 87 82 61 76 36 27 NA NA NA NA NA
16 70 90 43 54 72 NA NA NA NA NA 86 69 20 29 51
17 80 37 15 41 42 11 5 79 94 2 NA NA NA NA NA
18 85 30 81 95 23 NA NA NA NA NA 91 19 92 99 52
19 91 22 48 33 23 32 20 7 4 57 NA NA NA NA NA
20 97 18 45 14 40 24 16 77 12 55 NA NA NA NA NA
Case 2: merge.Matrix() with same arguments wants me to specify by.x/by.y
merge.Matrix(x = mtx.x, y = mtx.y,
all.x = T, all.y = T)
Error in grr::matches(by.x, by.y, all.x, all.y, nomatch = NULL) :
argument "by.x" is missing, with no default
Case 3: specifying by.x/by.y as respective column names does not merge common columns. also, no idea why its offsetting the matrices by 5 and not 10, the matrices have no common values.
merge.Matrix(x = mtx.x, y = mtx.y,
all.x = T, all.y = T,
by.x = colnames(mtx.x), by.y = colnames(mtx.y))
a b c d e f g h i j y.f y.g y.h y.i y.j k l m n o
82 61 76 36 27 67 85 38 29 87 NA NA NA NA NA NA NA NA NA NA
83 89 43 70 81 30 35 17 39 95 NA NA NA NA NA NA NA NA NA NA
1 75 69 54 66 3 10 47 93 73 NA NA NA NA NA NA NA NA NA NA
52 98 26 88 51 64 31 72 13 92 NA NA NA NA NA NA NA NA NA NA
44 74 86 9 63 58 50 56 6 49 NA NA NA NA NA NA NA NA NA NA
24 16 77 12 55 97 18 45 14 40 24 22 8 94 89 7 50 93 40 4
11 5 79 94 2 80 37 15 41 42 63 80 32 44 64 83 16 96 46 47
100 84 65 59 34 62 53 60 99 28 85 30 81 95 23 91 19 92 99 52
19 78 8 25 96 21 90 46 68 71 21 55 61 58 27 76 67 65 37 14
32 20 7 4 57 91 22 48 33 23 9 66 12 2 41 11 56 84 87 39
fill.x NA NA NA NA NA NA NA NA NA NA 18 57 88 3 68 100 74 62 82 25
fill.x NA NA NA NA NA NA NA NA NA NA 70 90 43 54 72 86 69 20 29 51
fill.x NA NA NA NA NA NA NA NA NA NA 1 59 60 45 79 75 15 5 73 10
fill.x NA NA NA NA NA NA NA NA NA NA 38 28 26 17 53 36 97 13 77 49
fill.x NA NA NA NA NA NA NA NA NA NA 6 71 98 35 42 31 78 33 48 34
Case 4: by.x/by.y specified as common column names, all.x/all.y set to TRUE and fill.x/fill.y set to 0 does not do a full join as the documentation claims
common <- intersect(colnames(mtx.x), colnames(mtx.y))
merge.Matrix(x = mtx.x, y = mtx.y,
all.x = T, all.y = T,
by.x = common, by.y = common)
a b c d e f g h i j y.f y.g y.h y.i y.j k l m n o
82 61 76 36 27 67 85 38 29 87 24 22 8 94 89 7 50 93 40 4
83 89 43 70 81 30 35 17 39 95 63 80 32 44 64 83 16 96 46 47
1 75 69 54 66 3 10 47 93 73 85 30 81 95 23 91 19 92 99 52
52 98 26 88 51 64 31 72 13 92 21 55 61 58 27 76 67 65 37 14
44 74 86 9 63 58 50 56 6 49 9 66 12 2 41 11 56 84 87 39