2

I need to build a matrix from data that is stored in several other matrices that all have a pointer in their first column. This is how the original matrices might look, with a-e being the pointers connecting the the data from all the matrices and the v-z being the data that is linked together. The arrow points to what I want my final matrix to look like. 

 a  x   x
 b  y   y
 c  z   z
 d  w   w           
 e  v   v           

 e  v   v          
 d  w   w          
 c  z   z
 b  y   y
 a  x   x

----->

x x x x
y y y y 
z z z z
w w w w
v v v v

I cant seem to write the right algorithm to do this, I am either getting subscript out of bounds errors or replacement has length zero errors. Here is what I have now but it is not working.

for(i in 1:length(matlist)){
tempmatrix = matlist[[i]]                  # list of matrices to be combined 

genMatrix[1,i] = tempmatrix[1,2]
for(j in 2:length(tempmatrix[,1])){
  index = which(indexv == tempmatrix[j,1]) #the row index for the data that needs to be match 
                                            # with an ECID
  for(k in 1:length(tempmatrix[1,])){


    genMatrix[index,k+i] = tempmatrix[j,k]
  }
                # places the data in same row as the ecid

}

}
 print(genMatrix)

EDIT: I just want to clarify that my example only shows two matrices but in the list matlist there can be any number of matrices. I need to find a way of merging them without having to know how many matrices are in matlist at the time.

M--
  • 25,431
  • 8
  • 61
  • 93
CU_dev
  • 243
  • 1
  • 14
  • 1
    If you have a long list to merge, you can use `Reduce` or `purrr::reduce` to iteratively join your matrices together. Joining matrices is similar to joining data.frames, you can use `merge` or `dplyr::****_join` – asachet Jun 06 '19 at 12:31
  • Another alternative would be 1. row binding all the matrices and 2. adding a column with a name of each matrix in the list to that and 3. then going from long format to wide. – M-- Jun 06 '19 at 15:07

1 Answers1

2

We can merge all the matrices in the list using Reduce and merge from base package.

as.matrix(read.table(text="a  x   x
                           b  y   y
                           c  z   z
                           d  w   w
                           e  v   v")) -> mat1

as.matrix(read.table(text="e  v   v
                           d  w   w
                           c  z   z
                           b  y   y
                           a  x   x")) -> mat2

as.matrix(read.table(text="e  x   z
                           d  z   w
                           c  w   v
                           b  y   x
                           a  v   y")) -> mat3


matlist <- list(mat1=mat1, mat2=mat2, mat3=mat3)


Reduce(function(m1, m2) merge(m1, m2, by = "V1", all.x = TRUE),
       matlist)[,-1]

#>   V2.x V3.x V2.y V3.y V2 V3
#> 1    x    x    x    x  v  y
#> 2    y    y    y    y  y  x
#> 3    z    z    z    z  w  v
#> 4    w    w    w    w  z  w
#> 5    v    v    v    v  x  z

Created on 2019-06-05 by the reprex package (v0.3.0)

Or we can append all the matrices together and then use tidyr to go from long to wide and get the desired output.

library(tidyr)
library(dplyr)

bind_rows(lapply(matlist, as.data.frame), .id = "mat") %>%   
  gather(matkey, val, c("V2","V3")) %>% 
  unite(matkeyt, mat, matkey, sep = ".") %>% 
  spread(matkeyt, val) %>% 
  select(-V1)

#>   mat1.V2 mat1.V3 mat2.V2 mat2.V3 mat3.V2 mat3.V3
#> 1       x       x       x       x       v       y
#> 2       y       y       y       y       y       x
#> 3       z       z       z       z       w       v
#> 4       w       w       w       w       z       w
#> 5       v       v       v       v       x       z

Created on 2019-06-06 by the reprex package (v0.3.0)

M--
  • 25,431
  • 8
  • 61
  • 93
  • 1
    what does the `[,-1]` at the end do? – CU_dev Jun 05 '19 at 19:57
  • @CU_dev `merge` keeps the `V1` which is the first column of both matrices that we are merging by. `[,-1]` drops that column so we get only desired columns as outputs. Try it without `[,-1]` and you'll see. – M-- Jun 05 '19 at 19:59
  • I have my matrices stored in a list, how could I merge from a list of matrices instead of having the names of the matrices to put into the merge function? – CU_dev Jun 05 '19 at 20:01
  • @CU_dev ```merge(matlist[[1]], matlist[[2]], by = "V1", all = TRUE)[,-1]``` where `matlist` is ```matlist <- list(mat1=mat1, mat2=mat2)```. – M-- Jun 05 '19 at 20:04
  • Matlist has different lengths though depending on User interaction is there a way to do it 1 list at a time in a loop and iterate through matlist? – CU_dev Jun 05 '19 at 20:06
  • the number of matrices that are in `matlist` could be between 2 -20 depending on previous code. I need a way of merging all the elements in that list without knowing how many elements are in `matlist` – CU_dev Jun 06 '19 at 12:13
  • 1
    @CU_dev I updated my answer, but there should be a dupe for this as well. – M-- Jun 06 '19 at 14:50
  • When I have multiple instances of the same value in V1 I get duplicates in the resulting matrix. for example if i have 2 rows with `a` (from my example) with different values in each row, I get duplicates of each of the `a` rows giving me 4 rows with data that should only be shown in 2. Do you know what might solve this? – CU_dev Jul 01 '19 at 19:06
  • @CU_dev Question is closed. Please post another one and you are welcome to ping me here and share a link to that question. Cheers. – M-- Jul 01 '19 at 19:36