Replace with 0 when retrieving data from a matrix

0
votes

I am filling in the values ​​of a column in a data frame from the values ​​present in a matrix. The search for the value is performed from two columns called e1 and e2. But it is possible that in any of these columns contain strings that are not present in the matrix. This of course gives error, but I would like to know if it is possible to continue filling with a 0 that non-existent value.

x<-cbind(c(0.3,0.35,0.35,0),c(0.2,0.2,0.4,0.2)
,c(0,0.6,0.1,0.3),c(0.5,0.25,0.25,0))
colnames(x) <- c("A","B","C","D")
rownames(x) <- c("A","B","C","D")

y<-as.data.frame(cbind(c(1,2,3,4,5,6)
,c("A","A","B","A","B","A"),c("D","C","C","D","D","J")))
colnames(y) <- c("id","e1","e2")

index_df = y%>% select(e1,e2)
colnames(index_df)<-c('rows','cols')

y$l<-x[as.matrix(index_df)]

Error in x[as.matrix(index_df)] : subscript out of bounds

2
rmatrix

2 Answers

1
votes

We can match row and column names of x with e1 and e2 columns of y and then use it to subset the values from x.

y$l <- x[cbind(match(y$e1, rownames(x)), match(y$e2, colnames(x)))]

y
#  id e1 e2    l
#1  1  A  D 0.50
#2  2  A  C 0.00
#3  3  B  C 0.60
#4  4  A  D 0.50
#5  5  B  D 0.25
#6  6  A  J   NA

This will return NA for non-matching values which if needed can be changed to 0 by doing

y$l[is.na(y$l)] <- 0
0
votes

We need to create an index based on the occurrence of values with %in%. Here, 'J' is not a columnname for 'x'. So using 'i1', we create a logical index and update only those rows that have column names matching with the 'e2'. Now, we can directly use the OP's syntax

i1 <- index_df$cols %in% colnames(x)
y$l[i1] <- x[as.matrix(index_df[i1,])]
y
#  id e1 e2    l
#1  1  A  D 0.50
#2  2  A  C 0.00
#3  3  B  C 0.60
#4  4  A  D 0.50
#5  5  B  D 0.25
#6  6  A  J   NA