Choose column name of the first column which fits certain logical test

Question

I have following input:

id <- c("a", "b", "c", "d")
target <- seq(from = 100, to = 400, length.out = 4)
a <- c(300, 304, 100, 405)
b <- c(300, 104, 100, 405)
c <- c(85, 304, 500, 405)
df <- as.data.frame(cbind(id, target, a, b, c))

I would like to add a new column "column" which indicates per row, which of the columns "a", "b", "c" would be the first column with a value smaller than the target solution. The requested output looks like this:

Required Output:

df$column <- c("c", "b", "a", "NA")
df

I thought about a concenated if check per row and apply this to all rows with the apply function. However the abc columns are quite long (round 20, therefore a loop would be required) and the number of rows are about 4.000. Does anybody have an idea on how to solve it?

Answer 1

You can do this as follows:

1) Create a logical matrix indicating whether or not a value in the 'a', 'b' or 'c' column is smaller than the target column:

m <- df[,3:5] < df[,2]

2) Create an integer vector which is the first names of these three column that has a value smaller than the target column with max.col and make sure that a NA value is returned for rows where no values was smaller with [c(TRUE,NA)[1 + (rowSums(m) == 0)]]:

mc <- max.col(m, ties.method = 'first')[c(TRUE,NA)[1 + (rowSums(m) == 0)]]

3) Assign the names to a new column:

df$column <- names(df[,3:5])[mc]

which gives:

> df
  id target   a   b   c column
1  a    100 300 300  85      c
2  b    200 304 104 304      b
3  c    300 100 100 500      a
4  d    400 405 405 405   <NA>

---

I separated the steps to make it more clear what the code does. But you can of course integrate it more like follows:

m <- df[,3:5] < df[,2]
df$column <- names(df[,3:5])[max.col(m, ties.method = 'first')[c(TRUE,NA)[1 + (rowSums(m) == 0)]]]

Answer 2

Here's another vectorized solution using which. This is basically takes all the occurrences where target is larger and the takes the first instances using the duplicated function.

indx <- which(df[, 3:5] < df[, 2], arr.ind = TRUE)
indx2 <- indx[!duplicated(indx[, "row"]),]
df[indx2[, "row"], "column"] <- names(df)[3:5][indx2[, "col"]]
df
#   id target   a   b   c column
# 1  a    100 300 300  85      c
# 2  b    200 304 104 304      b
# 3  c    300 100 100 500      a
# 4  d    400 405 405 405   <NA>

Answer 3

You can apply a function along the rows e.g. and use the result for populating your column etc

searchFunction <- function(row) {
  result <- "NA"
  for (name in names(row)) {
    if (name == "target" || name == "id") {
      next
    }
    if (result == "NA" && as.numeric(row[name]) < as.numeric(row["target"])) {
      result = name
    }
  }
  return(result);
}

apply(df, 1, searchFunction)
# [1] "c"  "b"  "a"  "NA"