How to compare 7 numeric columns, get the lowest number between them and insert value from character-CodePudding

This question is a little confusing, but I'll try my best to explain it. I have a dataset with 7 numeric columns (DIF1, DIF2, DIF3, DIF4, DIF5, DIF6, DIF7) and I want to compare them to see which one has the lowest number in each row.

After I do that, I need to create a column to fill with other 7 character columns (D1, D2, D3, D4, D5, D6, D7). I thought about doing it using ifelse statement as below:

 eapv$DOSE <- ifelse(eapv$DIF1 < eapv$DIF2 & eapv$DIF3 & eapv$DIF4 & eapv$DIF5 & eapv$DIF6 & eapv$DIF7, eapv$D1,
              ifelse(eapv$DIF2 < eapv$DIF1 & eapv$DIF3 & eapv$DIF4 & eapv$DIF5 & eapv$DIF6 & eapv$DIF7, eapv$D2,
              ifelse(eapv$DIF3 < eapv$DIF1 & eapv$DIF2 & eapv$DIF4 & eapv$DIF5 & eapv$DIF6 & eapv$DIF7, eapv$D3,
              ifelse(eapv$DIF4 < eapv$DIF1 & eapv$DIF2 & eapv$DIF3 & eapv$DIF5 & eapv$DIF6 & eapv$DIF7, eapv$D4,
              ifelse(eapv$DIF5 < eapv$DIF1 & eapv$DIF2 & eapv$DIF3 & eapv$DIF4 & eapv$DIF5 & eapv$DIF7, eapv$D5,
              ifelse(eapv$DIF6 < eapv$DIF1 & eapv$DIF2 & eapv$DIF3 & eapv$DIF4 & eapv$DIF5 & eapv$DIF7, eapv$D6,
              ifelse(eapv$DIF7 < eapv$DIF1 & eapv$DIF2 & eapv$DIF3 & eapv$DIF4 & eapv$DIF5 & eapv$DIF6, eapv$D7, NA)))))))

Some of values from the DIF columns are NA, but those can't be 0 because some of the values are already 0.

I can provide reproductible data:

DIF1 <- c(2, 3, 4, 5, 6, 7, 8)
DIF2 <- c(3, 2, 2, 1, NA, NA, NA)
DIF3 <- c(NA, NA, NA, 1, O, NA)
D1 <- c("First", "Second", "First", "Third", "First", "Fourth", "Second")
D2 <- c("Second", "First", "First",  "First", "Third", "First", "Second")
D3 <- c("Fourth",  "Third", "Second", "First", "First", "First", "Second")

data <- as.data.frame(cbind(DIF1, DIF2, DIF3, D1, D2, D3))

The result would be something like this:

DIF1     DIF2      DIF3        D1           D2           D3           DOSE
2          3        NA       First         Second       Fourth        First
3          2        NA       Second        First        Third         First
4          2        NA       First         First        Second        First
5          1        1        Third         First        First         First
6         NA        0        First         Third        First         First
7         NA        NA       Fourth        First        First         Fourth
8         NA        NA       Second        Second       Second        Second

How can I do that?

CodePudding user response：

You could apply which.min row-wise on columns 1:3 and add 3L to shift the index and subset the row with it. i.e. chose D*.

apply(data, 1, \(x) x[which.min(x[1:3])   3L])
# [1] "First"  "First"  "First"  "First"  "First"  "Fourth" "Second"

Note: R >= 4.1 used.

Update

We could do without hard coding:

dif_cols <- match(c("DIF1", "DIF2", "DIF3"), names(data))
d_cols <- match(c("D1", "D2", "D3"), names(data))

apply(data, 1, \(x) x[d_cols][which.min(x[dif_cols])])
# [1] "First"  "First"  "First"  "First"  "First"  "Fourth" "Second"

Data:

data <- structure(list(DIF1 = c("2", "3", "4", "5", "6", "7", "8"), DIF2 = c("3", 
"2", "2", "1", NA, NA, NA), DIF3 = c(NA, NA, NA, "1", "0", NA, 
NA), D1 = c("First", "Second", "First", "Third", "First", "Fourth", 
"Second"), D2 = c("Second", "First", "First", "First", "Third", 
"First", "Second"), D3 = c("Fourth", "Third", "Second", "First", 
"First", "First", "Second")), class = "data.frame", row.names = c(NA, 
-7L))

CodePudding user response：

You can do:

#Get column index of minimum values among numeric columns
idx <- apply(data[, sapply(data, is.numeric)], 1, which.min)
#Subset of character column
dataChar <- data[, sapply(data, is.character)]

data$DOSE <- mapply(function(x, y) dataChar[x, y], seq(nrow(dataChar)), idx)
[1] "First"  "First"  "First"  "First"  "First"  "Fourth" "Second"