CODEWITHSUNDEEP
r
Rani Powers
Rani Powers

Reputation: 206

Multiple columns in R to conditionally modify other columns (not create a new column)

I've seen some questions asking how to compare values between two columns in R, but I haven't been able to figure out how to use two columns to conditionally edit the rest of the row. I'm basically trying to apply a different range to each row.

For example:

data <- matrix(c(0.1, 0.3, 0.1,0.5,0.4,0.2,0.3,2,2,1,0.1,0.5,0.4,0.3,0.2), nrow=3, ncol=5)
colnames(data) <- c("Min", "Lim", "Var1", "Var2", "Var3")
data
     Min    Lim    Var1    Var2    Var3
[1,] 0.1    0.5     0.3     1.0     0.4
[2,] 0.3    0.4     2.0     0.1     0.3
[3,] 0.1    0.2     2.0     0.5     0.2

I would like to compare Var1, Var2, and Var3 to the Min and Lim column. If the Var1, Var2, or Var3 value in row 1 is below the Min value in row 1, the value should be replaced with "Below Min." If a value is above the Min but below the Lim, the value should be replaced with "Below Lim." If a value is above both the Min and the Lim, it should remain as is. My expected output would be:

data
     Min    Lim    Var1       Var2       Var3
[1,] 0.1    0.5  Below Lim     1.0     Below Lim
[2,] 0.3    0.4     2.0     Below Min  Below Lim
[3,] 0.1    0.2     2.0        0.5        0.2

I'm new to R and have tried looping over the rows with something like...

for(i in 1:nrow(data){
    data[i,3:5] <- ifelse(data[,3:5] > data[,1], data[,3:5], "Below LOD")
}

...but this is obviously not working for the multiple columns (Var1, Var2, Var3) and I know that loops should be avoided in R. Is there another way to achieve this?

I would really appreciate anyone pointing me in the right direction, thanks!

Upvotes: 3

Views: 922

Answers (2)

Carlos Cinelli
Carlos Cinelli

Reputation: 11597

Try:

data[,c("Var1", "Var2", "Var3")] <- 
  ifelse(data[,c("Var1", "Var2", "Var3")] < data[,"Min"], "Below Min",
              ifelse(data[,c("Var1", "Var2", "Var3")] < data[,"Lim"], "Below Lim", data))

This will give you:

data
    Min   Lim   Var1        Var2        Var3       
[1,] "0.1" "0.5" "Below Lim" "1"         "Below Lim"
[2,] "0.3" "0.4" "2"         "Below Min" "Below Lim"
[3,] "0.1" "0.2" "2"         "0.5"       "0.2"

Now, keep in mind that you are mixing numbers with characters, so this will coerce your matrix to character.

Upvotes: 0

Ricardo Saporta
Ricardo Saporta

Reputation: 55360

library(data.table)
DT <- data.table(data)

## The columns need to be converted to strings. You cannot mix strings with numbers
varCols <- grep("^Var", names(DT), value=TRUE, ignore.case=TRUE)
DT[, (varCols) := lapply(.SD, as.character), .SDcols = varCols]

DT[Var1 < Lim, Var1 := ifelse(Var1 < Min, "Below Min", "Below Lim")]
DT[Var2 < Lim, Var2 := ifelse(Var2 < Min, "Below Min", "Below Lim")]
DT[Var3 < Lim, Var3 := ifelse(Var3 < Min, "Below Min", "Below Lim")]

DT
#    Min Lim      Var1      Var2      Var3
# 1: 0.1 0.5 Below Lim         1 Below Lim
# 2: 0.3 0.4         2 Below Min Below Lim
# 3: 0.1 0.2         2       0.5       0.2

You can do the repetitive lines programatically: 

for (col in varCols)
  DT[get(col) < Lim, (col) := ifelse((get(col)) < Min, "Below Min", "Below Lim")]

Upvotes: 4

Related Questions