4

I want to match/join two matrices, a small one with values should match in a bigger one by rownames/colnames. I only find this answer. However, I cannot match the locations as the codeline frn <- as.matrix(bigMatrix[[1]]) does not work in my case. The answers of inner, outer ... join here did not help, as I want to match/join over a lot of different columns (and not e.g. a CostumerID for X and a CustomerID for y).

As the matrices I use are a 126x104 and a 193x193 matrices. I prepared example data: 1. the bigger matrix where the smaller one should be included (the letters are in the original data set country names):

a = c("A", "B", "C", "D", "E", "F")
full_matrix = matrix(nrow = length(a), ncol=length(a))
dimnames(full_matrix) <- list(levels(as.factor(a)), levels(as.factor(a)))

full_matrix
   A  B  C  D  E  F
A NA NA NA NA NA NA
B NA NA NA NA NA NA
C NA NA NA NA NA NA
D NA NA NA NA NA NA
E NA NA NA NA NA NA
F NA NA NA NA NA NA

And the smaller matrix:

matrix = matrix(c(2, 4, 3, 1, 5, 7, 3, 1, 6), nrow=3, ncol=3)
dimnames(matrix) <- list(c("B","C","E"), c("A","B","F"))

matrix
  A B F
B 2 1 3
C 4 5 1
E 3 7 6

The result should look so:

   A  B  C  D  E  F
A NA NA NA NA NA NA
B  2  1 NA NA NA  3
C  4  5 NA NA NA  1
D NA NA NA NA NA NA
E  3  7 NA NA NA  6
F NA NA NA NA NA NA
Community
  • 1
  • 1
N.Varela
  • 910
  • 1
  • 11
  • 25
  • `fix_rc(list(matrix), factors = list(LETTERS[1:6], LETTERS[1:6]))` see this [question/answer](http://stackoverflow.com/questions/30946374/retrieve-index-of-newly-added-row-for-loop-in-r/30946969#30946969). the only difference is that your matrix isn't in a list, so you can adjust that – rawr Jun 25 '15 at 12:57

2 Answers2

6

1) as.data.frame.table Convert the small matrix to the following long form data frame L having columns Var1, Var2 and Freq and then use matrix subscripting to assign the components:

L <- as.data.frame.table(matrix)
full_matrix[as.matrix(L[1:2])] <- L$Freq

giving:

> full_matrix
   A  B  C  D  E  F
A NA NA NA NA NA NA
B  2  1 NA NA NA  3
C  4  5 NA NA NA  1
D NA NA NA NA NA NA
E  3  7 NA NA NA  6
F NA NA NA NA NA NA

2) direct subscripting

full_matrix[rownames(matrix), colnames(matrix)] <- matrix

Note: The two solutions give identical results:

a = c("A", "B", "C", "D", "E", "F")
full_matrix = matrix(nrow = length(a), ncol=length(a))
dimnames(full_matrix) <- list(levels(as.factor(a)), levels(as.factor(a)))
matrix = matrix(c(2, 4, 3, 1, 5, 7, 3, 1, 6), nrow=3, ncol=3)
dimnames(matrix) <- list(c("B","C","E"), c("A","B","F"))

fm1 <- full_matrix
L <- as.data.frame.table(matrix)
fm1[as.matrix(L[1:2])] <- L$Freq

fm2 <- full_matrix
fm2[rownames(matrix), colnames(matrix)] <- matrix

identical(fm1, fm2)
## [1] TRUE
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • The first solution works fine, thanks. The second however retrieves the same (small) "matrix". – N.Varela Jun 26 '15 at 10:10
  • I don't know what you mean by "retrieves the same (small) "matrix" " but the two solutions give identical results. I have added a note with self contained code (just copy and paste into a fresh R session) showing this. – G. Grothendieck Jun 26 '15 at 11:42
3

See below using R's match function (I renamed your small matrix): 

a = c("A", "B", "C", "D", "E", "F")
full_matrix = matrix(nrow = length(a), ncol=length(a))
dimnames(full_matrix) <- list(levels(as.factor(a)), levels(as.factor(a)))

small_matrix = matrix(c(2, 4, 3, 1, 5, 7, 3, 1, 6), nrow=3, ncol=3)
dimnames(small_matrix) <- list(as.factor(c("B","C","E")), as.factor(c("A","B","F")))

You can then use:

rowmatch <- match(rownames(small_matrix), rownames(full_matrix))
colmatch <- match(colnames(small_matrix), colnames(full_matrix))
full_matrix[rowmatch, colmatch] <- small_matrix

Which gives you the desired output:

   A  B  C  D  E  F
A NA NA NA NA NA NA
B  2  1 NA NA NA  3
C  4  5 NA NA NA  1
D NA NA NA NA NA NA
E  3  7 NA NA NA  6
F NA NA NA NA NA NA

Or alternatively, you can use %in% with slighly different syntax:

full_matrix[rownames(full_matrix) %in% rownames(small_matrix), 
            colnames(full_matrix) %in% colnames(small_matrix)] <- small_matrix
Serban Tanasa
  • 3,592
  • 2
  • 23
  • 45