conditional rolling difference or gradient based on a column in data.table R

user_6851715 0
Viewed 68

I need to get the gradient of a column based on the conditional value of another column in a very large data.table.

> require(data.table)
> DT = data.table( ID = c(rep('A', 8), rep('B', 6)),
                   Condition = c(0,1,0,0,1,1,0,1,0,0,1,0,0,1),
                   Value = c(4,3,2,1,4,3,2,1,4,3,2,1,4,3))

I'd like to get the rolling gradient of the 'Value' column by ID, only for rows for which Condition == 1.

> desired_output
    ID Condition Value Gradient
 1:  A         0     4       NA    # condition isn't met so no gradient
 2:  A         1     3        0    # condition is met but there is no predecessor. Gradient set to 0
 3:  A         0     2       NA    # condition isn't met so no gradient
 4:  A         0     1       NA    # condition isn't met so no gradient
 5:  A         1     4        1    # condition is met and gradient is 4-3=1
 6:  A         1     3       -1    # condition is met and gradient is 3-4=-1
 7:  A         0     2       NA    # condition isn't met so no gradient
 8:  A         1     1       -2    # condition is met and gradient is 1-3=-2
 9:  B         0     4       NA
10:  B         0     3       NA
11:  B         1     2        0
12:  B         0     1       NA
13:  B         0     4       NA
14:  B         1     3        1

If possible I'd prefer a native data.table solution.

Please note: it's possible to do this by sub-setting DT[Condition == 1] and then rejoining the results. I'd like to avoid sub-setting and rejoining if possible.

data.table gradient conditional-statements r
1 Answers
library(data.table)
library(magrittr)
dt = data.table( ID = c(rep('A', 8), rep('B', 6)),
                 Condition = c(0,1,0,0,1,1,0,1,0,0,1,0,0,1),
                 Value = c(4,3,2,1,4,3,2,1,4,3,2,1,4,3))

# 1
dt[Condition == 1, Gradient := Value - shift(Value, fill = first(Value)), by = ID][]
#>     ID Condition Value Gradient
#>  1:  A         0     4       NA
#>  2:  A         1     3        0
#>  3:  A         0     2       NA
#>  4:  A         0     1       NA
#>  5:  A         1     4        1
#>  6:  A         1     3       -1
#>  7:  A         0     2       NA
#>  8:  A         1     1       -2
#>  9:  B         0     4       NA
#> 10:  B         0     3       NA
#> 11:  B         1     2        0
#> 12:  B         0     1       NA
#> 13:  B         0     4       NA
#> 14:  B         1     3        1

#2
dt$grad <- c (NA, NA, -1, -2,1, -1, - 1, -2, NA, NA, NA, -1,2,1)

dt[Condition == 1, Value2 := Value, by = ID] %>% 
  .[, Value2 := shift(nafill(Value2, "locf"))] %>% 
  .[ Value2 != 1, Gradient2 := Value - Value2] %>% 
  .[, Value2 := NULL] %>% 
  .[]
#>     ID Condition Value Gradient grad Gradient2
#>  1:  A         0     4       NA   NA        NA
#>  2:  A         1     3        0   NA        NA
#>  3:  A         0     2       NA   -1        -1
#>  4:  A         0     1       NA   -2        -2
#>  5:  A         1     4        1    1         1
#>  6:  A         1     3       -1   -1        -1
#>  7:  A         0     2       NA   -1        -1
#>  8:  A         1     1       -2   -2        -2
#>  9:  B         0     4       NA   NA        NA
#> 10:  B         0     3       NA   NA        NA
#> 11:  B         1     2        0   NA        NA
#> 12:  B         0     1       NA   -1        -1
#> 13:  B         0     4       NA    2         2
#> 14:  B         1     3        1    1         1

Created on 2021-06-04 by the reprex package (v2.0.0)

user_120254830
Related