Move data.table column values forward when conditions are met
I have a data table with two columns.
dt = data.table(a = c(0,0,-1,rep(0,3),-1,1), b = c(1,2,3,2,4,2,4,5))
> dt
a b
1: 0 1
2: 0 2
3: -1 3
4: 0 2
5: 0 4
6: 0 2
7: -1 4
8: 1 5
I need that in any case column a == -1 I need the value in column b wrapped in place before the next line where column a == -1. If there are no more, then the value in column b must continue to the end of the data.table
As a result, I hope for
a b
1: 0 1
2: 0 2
3: -1 3
4: 0 3
5: 0 3
6: 0 3
7: -1 4
8: 1 4
+3
source to share
2 answers
Ok, it wasn't as hard as I originally thought. I can delete this question if needed, but I haven't found anything similar on stackoverflow, so I'll just post the solution for now.
There was a problem with the first solution. This is actually what I expect, but I'm sure there is a much faster way to calculate this.
library(data.table)
dt = data.table(a = c(0,0,-1,rep(0,3),-1,1), b = c(1,2,3,2,4,2,4,5))
indices = which(dt$a == -1)
values = dt$b[indices]
dt[ , "tmp" := findInterval(1:nrow(dt), indices)]
dt$b = mapply(function(tmp, b){
if(tmp == 0){
return(b)
}else{
return(values[tmp])
}
}, dt$tmp, dt$b)
dt[ , "tmp" := NULL]
> dt
a b
1: 0 1
2: 0 2
3: -1 3
4: 0 3
5: 0 3
6: 0 3
7: -1 4
8: 1 4
Better solution thanks to @Frank
dt[, tmp := cumsum(a==-1)][tmp > 0L, b := first(b), by=tmp][, tmp := NULL ]
+3
source to share