CODEWITHSUNDEEP
rdata.table
Pawan
Pawan

Reputation: 1106

Row wise logical operation on Data Table in R

I have to perform row wise logical operation in data table, let me explain this with example. Suppose I have data table (DT) as given below

V1      V2        V3        V4      V5 
 AAE     CDG       2        0        0  
 AAE     CDG       2        5        5  
 AAE     ORY       2        4        4  
 AAE     ORY       2        0        0  
 AAE     ORY       2        5        5  
 AAE     ORY       2        3        3

Now, depending upon the value of V5, I want to add additional column V6 to data table, this is logical operation. I did something like this to do it-

DT[, V6 := if(V5 == 0){1
              }else if(V5 == 1){2
              }else if(V5 == 2){3
              }else if(V5 == 3){4
              }else if(V5 == 4){5
              }else if(V5 == 5){6}
    ]

But this does not give the desired result, which should be 

V1   V2        V3      V4        V5   V6
 AAE CDG       2        0        0     1
 AAE CDG       2        5        5     6
 AAE ORY       2        4        4     5
 AAE ORY       2        0        0     1
 AAE ORY       2        5        5     6
 AAE ORY       2        3        3     4

Whereas gives the following result-

 V1   V2       V3       V4       V5    V6
 AAE CDG       2        0        0     1
 AAE CDG       2        5        5     1
 AAE ORY       2        4        4     1
 AAE ORY       2        0        0     1
 AAE ORY       2        5        5     1
 AAE ORY       2        3        3     1

This happens because first value of V5 (which is 0) is used in logical operation, instead of dynamically using one value or row value at a time. How can I change [,J] argument to get the desired results. I can use the for loop to do this, but it would be very in-efficient way of doing it.

Upvotes: 2

Views: 2177

Answers (3)

eddi
eddi

Reputation: 49448

The other answers so far are in data.frame language. In data.table language you should use DT[, V6 := ifelse...] as opposed to DT$V6 <- ifelse... and you'd use the [ instead of calling merge:

setkey(DT, V5)
DT[J(V5 = 0:5, V6 = 1:6), nomatch = 0]

But at least in the example in the OP it looks like the solution is simply:

DT[, V6 := V5 + 1]

Oh, and the reason your if/else doesn't work is because if/else doesn't operate on vectors and it simply takes the first value of your vector V5, which is indeed 0, and returns 1, which is effectively the same as writing DT[, V6 := 1].

Upvotes: 2

zx8754
zx8754

Reputation: 56004

Try this:

dat <- read.table(
text= "V1      V2        V3        V4      V5 
  AAE     CDG       2        0        0  
  AAE     CDG       2        5        5  
  AAE     ORY       2        4        4  
  AAE     ORY       2        0        0  
  AAE     ORY       2        5        5  
  AAE     ORY       2        3        3",header=TRUE)

dat$V6 <- ifelse(dat$V5 == 0,1,
                 ifelse(dat$V5 == 1,2,
                        ifelse(dat$V5 == 2,3,
                               ifelse(dat$V5 == 3,4,
                                      ifelse(dat$V5 == 4,5,
                                             ifelse(dat$V5 == 5,6,NA))))))

Upvotes: 1

Geoffrey Absalom
Geoffrey Absalom

Reputation: 1895

Why don't you just do 

dat <- read.table(text= "V1      V2        V3        V4      V5 
+  AAE     CDG       2        0        0  
+  AAE     CDG       2        5        5  
+  AAE     ORY       2        4        4  
+  AAE     ORY       2        0        0  
+  AAE     ORY       2        5        5  
+  AAE     ORY       2        3        3  ",header=TRUE)
dat$V6 <- dat$V5 + 1

As @Steph said you can create a mapping table as follows and then merge the columns.

mapping <- data.frame(V5=c(0,1,2,3,4,5),V6=c(1,2,3,4,5,6))
merge(dat,mapping,by="V5")

Upvotes: 2

Related Questions