CODEWITHSUNDEEP
rreplaceconditional-statementsdata.tablesubset
ego_
ego_

Reputation: 1491

Replace row values in data.table using 'by' and conditions

I am trying to replace certain row values in a column according to conditions in another column, within a grouping.

EDIT: edited to highligh the recursive nature of the problem.

E.g.

DT = data.table(y=rep(c(1,3), each = 3)
            ,v=as.numeric(c(1,2,4,4,5,8))
            ,x=as.numeric(rep(c(9:11),each=2)),key=c("y","v"))
DT
   y v  x
1: 1 1  9
2: 1 2  9
3: 1 4 10
4: 3 4 10
5: 3 5 11
6: 3 8 11

Within each 'y', I then want to replace values of 'x' where 'v' has an observation v+t (e.g. t = 3), with 2222 (or in reality the results of a function) to following result:

   y v  x
1: 1 1  9
2: 1 2  9
3: 1 4 2222
4: 3 4 10
5: 3 5 11
6: 3 8 2222

I have tried the following, but to no avail.

  DT[which((v-3) %in% v), x:= 2222, y][]

And it mysteriously (?) results in: 

   y v  x
1: 1 1  9
2: 1 2  9
3: 1 4 2222
4: 3 4 2222
5: 3 5 2222
6: 3 8 2222

Running:

DT[,print(which((v-3) %in% v)), by =y]

Indicates that it does the correct indexing within the groups, but what happens from (or the lack thereof) I don't understand.

Upvotes: 3

Views: 1553

Answers (1)

akrun
akrun

Reputation: 886938

You could try using replace (which could have some overhead because it copies whole x)

 DT[, x:=replace(x, which(v %in% (v+3)), 2222), by=y]
 #   y v    x
 #1: 1 1    9
 #2: 1 2    9
 #3: 1 4 2222
 #4: 3 4   10
 #5: 3 5   11
 #6: 3 8 2222

Alternatively, you could create a logical index column and then do the assignment in the next step

 DT[,indx:=v %in% (v+3), by=y][(indx), x:=2222, by=y][, indx:=NULL]
 DT
 #   y v    x
 #1: 1 1    9
 #2: 1 2    9
 #3: 1 4 2222
 #4: 3 4   10
 #5: 3 5   11
 #6: 3 8 2222

Or slightly modifying your own approach using .I in order to create an index

indx <- DT[, .I[which((v-3) %in% v)], by = y]$V1
DT[indx, x := 2222]

Upvotes: 6

Related Questions