Fill missing values in a matrix with values from different rows in that matrix in R

user_12881209 0
Viewed 390

I have a matrix with nucleotide sequences (containing NAs) in rows as shown here:

n.mat

samp1 <- c("a","c","a",NA,"t","c")
samp2 <- c("a","c","t","t",NA,"a")
samp3 <- c("a","g","g","c","a","c")
samp4 <- c("a","g",NA,"g","g", NA)
samp5<- c(NA, "g","g","g","t","g")

n.mat <- rbind(samp1,samp2,samp3,samp4,samp5)

      [,1] [,2] [,3] [,4] [,5] [,6]
samp1 "a"  "c"  "a"   NA  "t"  "c" 
samp2 "a"  "c"  "t"  "t"   NA  "a" 
samp3 "a"  "g"  "g"  "c"  "a"  "a" 
samp4 "a"  "g"   NA  "g"  "g"   NA 
samp5  NA  "g"  "g"  "g"  "t"  "g"

I also have a data frame with two columns containing the sequence names:

df

df <- data.frame(
  X1 = c("samp1", "samp2", "samp3", "samp4", "samp5"),
  X2 = c("samp2", "samp5", "samp1", "samp3", "samp2"))

          X1       X2
1      samp1    samp2
2      samp2    samp5
3      samp3    samp1
4      samp4    samp3
5      samp5    samp2

I would like to fill the gaps of a row in the matrix with nucleotides/values from another row in the matrix indicated by the df$X2 column in the data frame.

So for example: samp1 in the matrix has an NA in its row in the fourth column. So I would like to take the string in the same column from samp2 (indicated by the data frame in column X2). For samp2 I would like to fill the NA by taking the string from samp5 (indicated by the data frame in column X2). If there is no NA in the row as it is in samp3, then do nothing. If there are two NAs in a row (as it is in samp4), then I would like to take both strings from samp3 both columns.

I have tried following code:

replace.na <- function(n.mat,val) {
  i <- is.na(n.mat)
  j <- which(i)
  k <- which(!i)
  n.mat[j[j > k[length(k)]]] <- val
  n.mat
}

n.mat[,-1] <- t(apply(matrix[,-1],1,replace.na))

But I am not quite sure how to include the df table to replace the NAs.

r matrix
2 Answers

Here's some very compact code that I will explain (and it assumes either using R v 4.x or creating the 'df' dataframe with stringsAsFactors=FALSE):

n.mat[ is.na(n.mat) ] <-  n.mat[df[['X2']],][ is.na(n.mat)]

n.mat
#------
      [,1] [,2] [,3] [,4] [,5] [,6]
samp1 "a"  "c"  "a"  "t"  "t"  "c" 
samp2 "a"  "c"  "t"  "t"  "t"  "a" 
samp3 "a"  "g"  "g"  "c"  "a"  "a" 
samp4 "a"  "g"  "g"  "g"  "g"  "a" 
samp5 "a"  "g"  "g"  "g"  "t"  "g"

The is.na(.n.mat) returns a logical matrix of the same dimension as n.mat. It is used as an index on both sides of the assignment but on the right side of the assignment is is picking from a matrix that has the rows rearranged by the ordering of the "replacement rows" that you specified in df. If the 'X1' column had not been ibn the same order as the target matrix, you would have needed to reorder that column via an order call, but it wasn't needed here.

 df <- read.table(text= 'X1       X2
 1      samp1    samp2
 2      samp2    samp5
 3      samp3    samp1
 4      samp4    samp3
 5      samp5    samp2', header=TRUE, stringsAsFactors=FALSE)

Note the stringsAsFactors=FALSE. I think my failing to use that (since I'm still on R 3.6 meant that I had factors in the X2 column.

The other way of doing this is to create a two column index of NA positions with the arr.idx parameter set to TRUE:

 pos <- which(is.na(n.mat),arr.ind=TRUE)

> pos
      row col
samp5   5   1
samp4   4   3
samp1   1   4
samp2   2   5
samp4   4   6

Then you can index with that 2 column matrix:

 n.mat[pos] <- n.mat[ df[['X2']] ,][pos]

> n.mat
      [,1] [,2] [,3] [,4] [,5] [,6]
samp1 "a"  "c"  "a"  "t"  "t"  "c" 
samp2 "a"  "c"  "t"  "t"  "t"  "a" 
samp3 "a"  "g"  "g"  "c"  "a"  "a" 
samp4 "a"  "g"  "g"  "g"  "g"  "a" 
samp5 "a"  "g"  "g"  "g"  "t"  "g"

R's matrix indexing can produce some very compact solutions to problems like this. You should consider reading the ?'[' help page for more details and examples. The time you put into that effort will be repaid many times over if you continue using R. I'm sure I've read through it 10 or 20 times by now.

Reprex:

n.mat <- matrix( scan(text = 'samp1 "a"  "c"  "a"  "NA" "t"  "c" 
 samp2 "a"  "c"  "t"  "t"  "NA" "a" 
 samp3 "a"  "g"  "g"  "c"  "a"  "a" 
 samp4 "a"  "g"  "NA" "g"  "g"  "NA" 
 samp5 "NA" "g"  "g"  "g"  "t"  "g" ', what=""), nrow=5, byrow=TRUE)

 n.mat <- matrix(n.mat[ ,-1], nrow=5, dimnames=list(n.mat[,1], NULL))

df <- read.table(text= 'X1       X2
 1      samp1    samp2
 2      samp2    samp5
 3      samp3    samp1
 4      samp4    samp3
 5      samp5    samp2', header=TRUE, stringsAsFactors=FALSE)
user_18556770

You could try something like this below. Note that the output is a bit different, but I copied/pasted your code for creating your matrix (see samp3 column 6).

t(sapply(rownames(n.mat), function(x) {
  na_cols <- is.na(n.mat[x, ])
  n.mat[x, na_cols] <- n.mat[df[df$X1 == x, "X2"], na_cols]
  n.mat[x, ]
}))

Output

      [,1] [,2] [,3] [,4] [,5] [,6]
samp1 "a"  "c"  "a"  "t"  "t"  "c" 
samp2 "a"  "c"  "t"  "t"  "t"  "a" 
samp3 "a"  "g"  "g"  "c"  "a"  "c" 
samp4 "a"  "g"  "g"  "g"  "g"  "c" 
samp5 "a"  "g"  "g"  "g"  "t"  "g"

Data

n.mat <- structure(c("a", "a", "a", "a", NA, "c", "c", "g", "g", "g", 
"a", "t", "g", NA, "g", NA, "t", "c", "g", "g", "t", NA, "a", 
"g", "t", "c", "a", "c", NA, "g"), .Dim = 5:6, .Dimnames = list(
    c("samp1", "samp2", "samp3", "samp4", "samp5"), NULL))
user_34606700
Related