Loop through rows and variables with same prefix

Question

Suppose we have the following data:

d <- data.frame(
  "V" = c("A", "B"),
  "X1" = c("A", "A"),
  "X2" = c("B","B"),
  "X3" = c("C", "C"),
  "Y1" = c(1, 4),
  "Y2" = c(2, 5),
  "Y3" = c(3, 6)
)
d[] <- lapply(d, as.character)

d
  V X1 X2 X3 Y1 Y2 Y3
1 A  A  B  C  1  2  3
2 B  A  B  C  4  5  6

I want to create a variable VAL that will take the value of Y[n] if V=X[n]

I can do it with ifelse statements but I want to avoid nested ifelse because n is unknown

d$VAL_ifelse = ifelse(d$V == d$X1,d$Y1,
                      ifelse(d$V == d$X2,d$Y2,
                             ifelse(d$V == d$X3,d$Y3,NA)))  

I tried to create this loop but problem is with j I think ?

d_X_var=grep("^X", names(d), value=TRUE)

for(i in 1:nrow(d)){
  for(j in 1:length(d_X_var)){
    if((d[i,c('V')] == d[i,paste0('X',j)]) == TRUE){
      d$VAL_loop[i] <- as.character(d[i,paste0('Y',j)])
    } else if((d[i,c('V')] != d[i,paste0('X',j)]) == TRUE){
      d$VAL_loop[i] <- NA
    }
  }
}

d
  V X1 X2 X3 Y1 Y2 Y3 VAL_ifelse VAL_loop
1 A  A  B  C  1  2  3          1     <NA>
2 B  A  B  C  4  5  6          5     <NA>

Answer 1

We can use vectorized way to get VAL

d$Val <- d[5:7][which(d[2:4] == d$V, arr.ind = TRUE)]

d
#  V X1 X2 X3 Y1 Y2 Y3 Val
#1 A  A  B  C  1  2  3   1
#2 B  A  B  C  4  5  6   5

The above is true when you know the column numbers beforehand of X and Y columns. If you don't know we can use grep first to get column numbers and then subset.

X_cols <- grep("^X", names(d))
Y_cols <- grep("^Y", names(d))
d$Val <- d[Y_cols][which(d[X_cols] == d$V, arr.ind = TRUE)]