Multiple columns in R to conditionally change other columns (don't create a new column)
I've seen several questions about how to compare values between two columns in R, but I haven't been able to figure out how to use the two columns to conditionally edit the rest of the row. I basically try to apply different ranges for 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 with the Min and Lim column. If the value of Var1, Var2, or Var3 on line 1 is below the minimum value on line 1, the value should be changed to Below Min. If the value is above Min but below Lim, the value should be replaced with "Below Lim". If the value is higher than both Min and Lim, it should remain as it is. My expected output:
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 iterating over lines 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 that obviously doesn't work for multiple columns (Var1, Var2, Var3) and I know that loops should be avoided in R. Is there any other way to achieve this?
I would really appreciate it if someone would point me in the right direction, thanks!
source to share
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 repeated lines programmatically:
for (col in varCols)
DT[get(col) < Lim, (col) := ifelse((get(col)) < Min, "Below Min", "Below Lim")]
source to share
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 symbols, so this will cause your matrix to become a symbol.
source to share