CODEWITHSUNDEEP
rdata.tablemean
Gaurav Singhal
Gaurav Singhal

Reputation: 1086

Calculate row-average for columns based on condition met by other columns

I want to take row-average of certain columns, based on values in other columns. If we take the following dataset:

library(data.table)
test <- data.table(s1=c(0,4,29,9,1,2,10),
                   s2=c(20,17,11,15,32,15,10),
                   s3=c(1,0,2,1,4,7,0),
                   m1=c(0,4,29,NA,1,22,8),
                   m2=c(20,17,NA,15,32,15,12),
                   m3=c(1,0,1,1,1,NA,0),
                   z=c(1,5,25,5,30,20,10)
)

I want to take average of s1, s2, s3 and create a new column, based on values of m1, m2, m3, z; specifically using following condition. 

ifelse( !is.na(m) & m<z, s, NA)

That is, if m is not NA and m < z, only then s should be considered for row-average.

As of now, I have got this working, but seems too lengthy

test[,t1:=ifelse(!is.na(m1) & m1<z,s1,NA),]
test[,t2:=ifelse(!is.na(m2) & m2<z,s2,NA),]
test[,t3:=ifelse(!is.na(m3) & m3<z,s3,NA),]

test[,s_avg:=rowMeans(.SD,na.rm = TRUE),.SDcols=c('t1','t2','t3')]

An alternative data.frame solution will also be appreciated.

EDIT: t columns are not needed.

Upvotes: 1

Views: 108

Answers (3)

Sathish
Sathish

Reputation: 12703

method 1:

test[ , avg := rowMeans( test[, .(ifelse( m1 < z, s1, NA),
                                  ifelse( m2 < z, s2, NA),
                                  ifelse( m3 < z, s3, NA)) ],
                         na.rm = TRUE ) ]

method 2: using an expression

expr <- paste0("ifelse( m", 1:3, " < z, s", 1:3, ", NA )")
test[ , avg := rowMeans( test[, lapply( expr, function(x) eval(parse(text = x)))],
                         na.rm = TRUE ) ]

output:

test
#    Time Zone quadrat s1 s2 s3 m1 m2 m3  z  avg
# 1:    0    1       1  0 20  1  0 20  1  1  0.0
# 2:    0    1       2  4 17  0  4 17  0  5  2.0
# 3:    0    0       3 29 11  2 29 NA  1 25  2.0
# 4:    7    1       1  9 15  1 NA 15  1  5  1.0
# 5:    7    0       2  1 32  4  1 32  1 30  2.5
# 6:    7    0       3  2 15  7 22 15 NA 20 15.0
# 7:   12    1       1 10 10  0  8 12  0 10  5.0

Upvotes: 1

akrun
akrun

Reputation: 886938

One option would to use Map for doing the comparison on corresponding columns of 's' and 'm'

nm1 <- grep("s\\d+", names(test), value = TRUE)
nm2 <- grep("m\\d+", names(test), value = TRUE)
test[, paste0("t", 1:3) := Map(function(x, y) 
         ifelse(y < z & !is.na(y), x, NA), .SD[, ..nm1], .SD[, ..nm2]) ]

Then do, the last step as in the OP's post. It is not clear whether 't' columns are needed or not for the OP. 

test[,s_avg:=rowMeans(.SD,na.rm = TRUE),.SDcols=c('t1','t2','t3')]
test
#    s1 s2 s3 m1 m2 m3  z t1 t2 t3 s_avg
#1:  0 20  1  0 20  1  1  0 NA NA   0.0
#2:  4 17  0  4 17  0  5  4 NA  0   2.0
#3: 29 11  2 29 NA  1 25 NA NA  2   2.0
#4:  9 15  1 NA 15  1  5 NA NA  1   1.0
#5:  1 32  4  1 32  1 30  1 NA  4   2.5
#6:  2 15  7 22 15 NA 20 NA 15 NA  15.0
#7: 10 10  0  8 12  0 10 10 NA  0   5.0

If we don't need the 't' columns, then the s_avg can be created in the above step

test[,  s_avg := rowMeans(mapply(function(x, y) x *(NA^!(y < z & !is.na(y))),
                     .SD[, ..nm1], .SD[, ..nm2]), na.rm = TRUE) ]
test
#   s1 s2 s3 m1 m2 m3  z s_avg
#1:  0 20  1  0 20  1  1   0.0
#2:  4 17  0  4 17  0  5   2.0
#3: 29 11  2 29 NA  1 25   2.0
#4:  9 15  1 NA 15  1  5   1.0
#5:  1 32  4  1 32  1 30   2.5
#6:  2 15  7 22 15 NA 20  15.0
#7: 10 10  0  8 12  0 10   5.0

Even the grep step can be done in the above line of code.

Another option is to melt it to 'long' format after creating a row index, then do the join on the index to create the 's_avg'

test[, ind := seq_len(.N)]
test[melt(test, measure = patterns("^s\\d+", "^m\\d+"),
     value.name = c("s", "m"))[!is.na(m) & m < z][, 
     .(s_avg = mean(s, na.rm = TRUE)), ind], 
             on = .(ind)][order(ind)][, ind := NULL][]
#    s1 s2 s3 m1 m2 m3  z s_avg
#1:  0 20  1  0 20  1  1   0.0
#2:  4 17  0  4 17  0  5   2.0
#3: 29 11  2 29 NA  1 25   2.0
#4:  9 15  1 NA 15  1  5   1.0
#5:  1 32  4  1 32  1 30   2.5
#6:  2 15  7 22 15 NA 20  15.0
#7: 10 10  0  8 12  0 10   5.0

Upvotes: 3

Artem Klevtsov
Artem Klevtsov

Reputation: 9423

Seems you no need ifelse. Simply use i expression.

iter <- 1:3
t <- paste0("t", iter)
s <- paste0("s", iter)
m <- paste0("m", iter)
for (i in iter) test[!is.na(get(m[i])) & get(m[i]) < z, (t[i]) := get(s[i])]
test[, s_avg := rowMeans(.SD, na.rm = TRUE), .SDcols = t]
print(test)
#>    s1 s2 s3 m1 m2 m3  z t1 t2 t3 s_avg
#> 1:  0 20  1  0 20  1  1  0 NA NA   0.0
#> 2:  4 17  0  4 17  0  5  4 NA  0   2.0
#> 3: 29 11  2 29 NA  1 25 NA NA  2   2.0
#> 4:  9 15  1 NA 15  1  5 NA NA  1   1.0
#> 5:  1 32  4  1 32  1 30  1 NA  4   2.5
#> 6:  2 15  7 22 15 NA 20 NA 15 NA  15.0
#> 7: 10 10  0  8 12  0 10 10 NA  0   5.0

Upvotes: 1

Related Questions