Count the number of value changes within the past n days

user_10006534 0
Viewed 97

I'd like to count the number of num that don't equal the previous num within the past d days, preferably with data.table.

My data looks like this: 

d <- 3
   df1 <- structure(list(LET = structure(c(1L, 1L, 1L, 1L, 1L, 2L, 
                                         2L, 2L, 2L, 2L), 
                                       .Label = c("a", "b"), class = "factor"), 
                   day = c(1L, 2L, 3L, 4L, 5L, 1L, 2L, 3L, 4L, 5L),
                   num = c(5, 6, 6, 7, 7, 1, 1, 3, 1, 1)), 
              class = c("data.table", "data.frame"), 
              row.names = c(NA, -10L))
    LET day num
 1:   a   1   5
 2:   a   2   6
 3:   a   3   6
 4:   a   4   7
 5:   a   5   7
 6:   b   1   1
 7:   b   2   1
 8:   b   3   3
 9:   b   4   1
10:   b   5   1

And I can use shift to say whether or not a num equals its lagged value, whether or not a num has changed:

df1[,hasChanged := num!=shift(num, n=1, fill=FALSE), by='LET']

But I'd like to count the number of num changes where day <= currentRowDay & day > currentRowDay - d , by LET.

Output would look like this (hasChanged column is optional):

df1[,NumberOfChangesPast3Days := c(1,2,2,2,1,1,1,2,2,2),]

    LET day num hasChanged NumberOfChangesPast3Days
 1:   a   1   5       TRUE                        1
 2:   a   2   6       TRUE                        2
 3:   a   3   6      FALSE                        2
 4:   a   4   7       TRUE                        2
 5:   a   5   7      FALSE                        1
 6:   b   1   1       TRUE                        1
 7:   b   2   1      FALSE                        1
 8:   b   3   3       TRUE                        2
 9:   b   4   1       TRUE                        2
10:   b   5   1      FALSE                        2
data.table r
3 Answers

A possible solution is using Reduce with shift:

df1[, N_Changes := Reduce(`+`, shift(hasChanged, n = 0:(d-1), fill = FALSE))
    , by = LET]

which gives:

> df1
    LET day num hasChanged N_Changes
 1:   a   1   5       TRUE         1
 2:   a   2   6       TRUE         2
 3:   a   3   6      FALSE         2
 4:   a   4   7       TRUE         2
 5:   a   5   7      FALSE         1
 6:   b   1   1       TRUE         1
 7:   b   2   1      FALSE         1
 8:   b   3   3       TRUE         2
 9:   b   4   1       TRUE         2
10:   b   5   1      FALSE         2
user_22044100

All the solution posted so far are using the shift() function which requires that the sequence of days is contiguous, i.e., has no gaps. The answers return the expected result only by chance because the sample dataset does not have any gaps.

The OP has clearly stated that he likes

to count the number of num changes where day <= currentRowDay & day > currentRowDay - d

This can be achieved even for gapped data by aggregating in a non-equi self-join which aggregates in a rollling window of 3 days (not 3 rows!):

library(data.table)
setDT(df1)[
  , c("hasChanged", "day_start") := 
    .(c(TRUE, diff(num) != 0), day - d), by = LET][
      , sprintf("NumberOfChangesPast%iDays", d) := 
        .SD[.SD, on = .(LET, day > day_start, day <= day), sum(hasChanged), 
            by = .EACHI]$V1][]

    LET day num hasChanged day_start NumberOfChangesPast3Days
 1:   a   1   5       TRUE        -2                        1
 2:   a   2   6       TRUE        -1                        2
 3:   a   3   6      FALSE         0                        2
 4:   a   4   7       TRUE         1                        2
 5:   a   5   7      FALSE         2                        1
 6:   b   1   1       TRUE        -2                        1
 7:   b   2   1      FALSE        -1                        1
 8:   b   3   3       TRUE         0                        2
 9:   b   4   1       TRUE         1                        2
10:   b   5   1      FALSE         2                        2

For gapped data like, e.g.,

df2 <- structure(list(LET = structure(c(1L, 1L, 1L, 1L, 1L, 2L, 
                                        2L, 2L, 2L, 2L), 
                                      .Label = c("a", "b"), class = "factor"), 
                      day = c(1L, 2L, 3L, 5L, 7L, 1L, 2L, 3L, 4L, 6L),
                      num = c(5, 6, 6, 7, 7, 1, 1, 3, 1, 1)), 
                 class = c("data.table", "data.frame"), 
                 row.names = c(NA, -10L))

the code

setDT(df2)[
  , c("hasChanged", "day_start") := 
    .(c(TRUE, diff(num) != 0), day - d), by = LET][
      , sprintf("NumberOfChangesPast%iDays", d) := 
        .SD[.SD, on = .(LET, day > day_start, day <= day), sum(hasChanged), 
            by = .EACHI]$V1][]

returns

    LET day num hasChanged day_start NumberOfChangesPast3Days
 1:   a   1   5       TRUE        -2                        1
 2:   a   2   6       TRUE        -1                        2
 3:   a   3   6      FALSE         0                        2
 4:   a   5   7       TRUE         2                        1
 5:   a   7   7      FALSE         4                        1
 6:   b   1   1       TRUE        -2                        1
 7:   b   2   1      FALSE        -1                        1
 8:   b   3   3       TRUE         0                        2
 9:   b   4   1       TRUE         1                        2
10:   b   6   1      FALSE         3                        1

Note that day_start is not included in the rolling window as the OP has requested a left open interval.

user_38170040

That would be one way of using data.table's shift:

library(data.table)

df1[, hasChanged := num != shift(num, n=1, fill=FALSE), by = LET][
  , NumberOfChangesPast3Days := colSums(do.call(rbind, shift(hasChanged, (d-1):0)), na.rm = TRUE), 
  by = LET
  ]

Output:

    LET day num hasChanged NumberOfChangesPast3Days
 1:   a   1   5       TRUE                        1
 2:   a   2   6       TRUE                        2
 3:   a   3   6      FALSE                        2
 4:   a   4   7       TRUE                        2
 5:   a   5   7      FALSE                        1
 6:   b   1   1       TRUE                        1
 7:   b   2   1      FALSE                        1
 8:   b   3   3       TRUE                        2
 9:   b   4   1       TRUE                        2
10:   b   5   1      FALSE                        2
user_83890030
Related