mapping matrices R

user_10285492 0
Viewed 1517

I have a csv file contains 10000 row of this type

ref

ref     smbole      name
r1      ts          table_spoon
r2      kn          knife
r3      fr          door
...     ...         ...

and a matrix extracted from some text files with R in this format (contains between 60 and 100 row)

data

ref     smbole      name
r2      kn          NA
r3      NA          door
NA      NA          table_spoon
NA      NA          door
...     ...         ...

I want to map the data matrix with values from ref matrix according to the NA values I mean to replace each NA with the equivalent value

my expected output is

ref     smbole      name
r2      kn         knife
r3      fr           door
r1      ts          table_spoon
r3      fr          door

I tried this code but it haven't change any thing

ref <- as.matrix(read.delim("name.csv", sep = "\t"))

fun <- function(rowi,r) {
  res <- apply(as.data.frame(ref),1,function(x) {length(na.omit(match(na.omit(rowi),x)))})
  IND <- which(  max(data) == data  )[1]

  rowi[is.na(rowi)] <- unlist(genemap[IND,])[is.na(rowi)]
  return(rowi)
}

as.data.frame(t(apply(data, 1, fun, ref))
)
mapping r matrix
3 Answers

A solution with update join of data.table:

library(data.table)
ref <- as.data.frame(ref, stringsAsFactors = F); setDT(ref)
data <- as.data.frame(data, stringsAsFactors = F); setDT(data)
for(oncol in colnames(ref)){
    for(scol in setdiff(colnames(ref), oncol)){
        rcol <- paste0('i.', scol)
        data[ref, (scol) := ifelse(is.na(get(scol)), get(rcol), get(scol)), on = oncol]
    }
}

# > data
#    ref smbole        name
# 1:  r2     kn       knife
# 2:  r3     fr        door
# 3:  r1     ts table spoon
# 4:  r3     fr        door

Here is the data in use:

data <- structure(list(ref = c("r2", "r3", NA, NA), smbole = c("kn", 
    NA, NA, NA), name = c(NA, "door", "table spoon", "door")), row.names = c(NA, 
        -4L), class = c("data.table", "data.frame"))

ref <- structure(list(ref = c("r1", "r2", "r3"), smbole = c("ts", "kn", 
    "fr"), name = c("table spoon", "knife", "door")), row.names = c(NA, 
        -3L), class = c("data.table", "data.frame"))
user_39265430

A little convoluted, but it might work.

Assuming data look like the following:

ref<-structure(list(ref = c("r1", "r2", "r3"), smbole = c("ts", "kn", 
"fr"), name = c("table_spoon", "knife", "door")), class = "data.frame", row.names = c(NA, -3L))

data<-structure(list(ref = c("r2", "r3", NA, NA), smbole = c("kn", 
NA, NA, NA), name = c(NA, "door", "table_spoon", "door")), class = "data.frame", row.names = c(NA, -4L))

You can try:

Reduce(function(x,y) {x[rowSums(!is.na(x))==0,]<-y[rowSums(!is.na(x))==0,];x},
           Map(function(x,y) ref[match(y,x),],ref,data))

That gives:

#     ref smbole        name
#2     r2     kn       knife
#3     r3     fr        door
#NA    r1     ts table_spoon
#NA.1  r3     fr        door
user_39872940

You can go through each row of dat, find the matching row in ref, and then the result is all of those matching rows.

t(
  apply(dat, 1, function(x){
    ind <- which.max(!is.na(x)) #index of first non-NA
    ref[ref[,ind] == x[ind],] # row of ref which matches this value
  })
)

#      ref  smbole name         
# [1,] "r2" "kn"   "knife"      
# [2,] "r3" "fr"   "door"       
# [3,] "r1" "ts"   "table_spoon"
# [4,] "r3" "fr"   "door"

Data used:

ref <- structure(c("r1", "r2", "r3", "ts", "kn", "fr", "table_spoon", 
"knife", "door"), .Dim = c(3L, 3L), .Dimnames = list(NULL, c("ref", 
"smbole", "name")))

#      ref  smbole name         
# [1,] "r1" "ts"   "table_spoon"
# [2,] "r2" "kn"   "knife"      
# [3,] "r3" "fr"   "door" 

dat <- structure(c("r2", "r3", NA, NA, "kn", NA, NA, NA, NA, "door", 
"table_spoon", "door"), .Dim = c(4L, 3L), .Dimnames = list(NULL, 
    c("ref", "smbole", "name")))

#      ref  smbole name         
# [1,] "r2" "kn"   NA           
# [2,] "r3" NA     "door"       
# [3,] NA   NA     "table_spoon"
# [4,] NA   NA     "door"
user_67627550
Related