CODEWITHSUNDEEP
rmatrix
s.harkins
s.harkins

Reputation: 85

Use a matrix to fill NA values in another matrix

I'm trying to replace NA values from one matrix with values from a row with the same name in a separate matrix.
This is part of a loop to process 32 matrices with differing row numbers so I need code that references the locations of the NA's, preferably by row name. The maximum number of rows is 7, all data has two columns.

#Sample Matrices:    
> ss.
                SD_d13c  SD_d15n
    Arthropod 2.0550750 1.417745
    C4Plants  3.8064638 2.606882
    Lichen           NA       NA
    MiddleC3  0.8845903 1.244990
    UpperC3   1.2798437 1.795272

 > s.sds
             SD_d13c SD_d15n
Arthropod       2.39    2.10
C4Grass         2.71    1.56
C4Plants        2.04    2.57
Carex+NFixer    0.71    1.63
Lichen          0.93    2.29
MiddleC3        1.07    1.79
UpperC3         2.07    2.40

#Leading to this:
> s.sds
             SD_d13c SD_d15n
Arthropod 2.0550750 1.417745
C4Plants  3.8064638 2.606882
Lichen         0.93     2.29
MiddleC3  0.8845903 1.244990
UpperC3   1.2798437 1.795272

In the sample I want to replace the NA values in the "Lichen" row of ss. with the "Lichen" values in s.sds and can't sort out how to automate this in a loop, given the number of rows in ss. will vary (2-7 rows) while the number of rows numbers in s.sds remains 7.

Maybe I need to go through all iterations and make them all have seven rows so the row numbers will match? Except I can't do that without skewing my data. The purpose of this code is to replace NA values with site standard deviation values. These values are dependent on vegetation category. If a category wasn't found at a site it isn't listed in the site matrix.

Obviously I can do every iteration manually, but I want to learn to automate things like this more if possible.

Upvotes: 0

Views: 879

Answers (2)

Ronak Shah
Ronak Shah

Reputation: 388907

Convert matrix to dataframe join both the tables by the rownames, replace NA value with the corresponding value and change it back to matrix.

library(dplyr)
library(tibble)

ss. %>%
  as.data.frame() %>%
  rownames_to_column(var = 'name') %>%
  left_join(s.sds %>%
  as.data.frame() %>%
  rownames_to_column(var = 'name'), by = 'name') %>%
  transmute(name, 
            SD_d13c = coalesce(SD_d13c.x, SD_d13c.y), 
            SD_d15n = coalesce(SD_d15n.x, SD_d15n.y)) %>%
  column_to_rownames('name') %>%
  as.matrix() -> result

result

#            SD_d13c  SD_d15n
#Arthropod 2.0550750 1.417745
#C4Plants  3.8064638 2.606882
#Lichen    0.9300000 2.290000
#MiddleC3  0.8845903 1.244990
#UpperC3   1.2798437 1.795272

Upvotes: 0

Marc in the box
Marc in the box

Reputation: 11995

ss. <- as.matrix(read.table(text = "
                SD_d13c  SD_d15n
    Arthropod 2.0550750 1.417745
    C4Plants  3.8064638 2.606882
    Lichen           NA       NA
    MiddleC3  0.8845903 1.244990
    UpperC3   1.2798437 1.795272"))

s.sds <- as.matrix(read.table(text = "
             SD_d13c SD_d15n
Arthropod       2.39    2.10
C4Grass         2.71    1.56
C4Plants        2.04    2.57
Carex+NFixer    0.71    1.63
Lichen          0.93    2.29
MiddleC3        1.07    1.79
UpperC3         2.07    2.40"))



ss.2 <- ss. # make a new copy of your target matrix
NAs <- which(is.na(rowSums(ss.))) # identify rows with missing values
ss.2[names(NAs),] <- s.sds[names(NAs),] # pass values from s.sds 
ss.2 # result
#             SD_d13c  SD_d15n
# Arthropod 2.0550750 1.417745
# C4Plants  3.8064638 2.606882
# Lichen    0.9300000 2.290000
# MiddleC3  0.8845903 1.244990
# UpperC3   1.2798437 1.795272

Upvotes: 1

Related Questions