Duration of the previous month's value under a certain condition

[fullsteam]

I have a problem that I want to ask for your advice regarding the calculation of the number of months that the previous month's value persisted under a certain condition.

I am trying to compute "the duration of the previous month's Value in consecutive months" only when there are at least 3 months' of observations in the past.

An example data looks like:

structure(list(Group = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2), Month = c(2, 
4, 5, 6, 7, 10, 11, 12, 13, 14, 5), Value = c(0.1, 0.1, 0.1, 
0.1, 0.2, 0.1, 0.1, 0.1, 0.2, 0.2, 0.3)), class = "data.frame", row.names = c(NA, 
-11L), codepage = 65001L)

To clarify, the table below ("Stability" column) is what I am seeking:

╔═══════╦═══════╦═══════╦═══════════╦════════════════════════════════════════════════════════════╗
║ Group ║ Month ║ Value ║ Stability ║ Note                                                       ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   1   ║   2   ║  0.1  ║     na    ║ Because there is no previous month's Value                 ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   1   ║   4   ║  0.1  ║     na    ║ Because there is no previous month's Value                 ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   1   ║   5   ║  0.1  ║     na    ║ We cannot know what happened for Month 3                   ║
║       ║       ║       ║           ║ (Or there is no data for 3 consecutive months in the past) ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   1   ║   6   ║  0.1  ║     na    ║ There is no 3 consecutive months' of data in the past      ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   1   ║   7   ║  0.2  ║     3     ║ 0.1 (Month 6's Value) was stable for 3 months in the past, ║
║       ║       ║       ║           ║ from Month 4 to 6                                          ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   1   ║   10  ║  0.1  ║     na    ║ Because there is no previous month's Value                 ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   1   ║   11  ║  0.1  ║     na    ║ There is no 3 consecutive months' of data in the past      ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   1   ║   12  ║  0.1  ║     na    ║ There is no 3 consecutive months' of data in the past      ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   1   ║   13  ║  0.2  ║     3     ║ 0.1 (Month 12's Value) was stable for 3 months in the past ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   1   ║   14  ║  0.2  ║     1     ║ 0.2 (Month 13's Value) was stable for 1 month (Month 13)   ║
╠═══════╬═══════╬═══════╬═══════════╬════════════════════════════════════════════════════════════╣
║   2   ║   5   ║  0.3  ║     na    ║ Because there is no previous month's Value for Group 2...  ║
╚═══════╩═══════╩═══════╩═══════════╩════════════════════════════════════════════════════════════╝

[technocrat]

To simplify, subset dat into the two Group entries; what will work for one will work for the other. That step is omitted below, because the value of Group does not change until the final row; if values alternate, the problem arises.

The condition test can be performed with dplyr::lag on Month with three tests:

Month - lag(Month,3) == 1 &
Month - lag(Month,2) == 1 &
Month - lag(Month,1) == 1

If all three conditions are met, Stability = lag(Value,1); else, NA

It will prove convenient to use a function for the test.

suppressPackageStartupMessages({library(dplyr)})
dat <- structure(list(Group = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2), Month = c(
  2,
  4, 5, 6, 7, 10, 11, 12, 13, 14, 5
), Value = c(
  0.1, 0.1, 0.1,
  0.1, 0.2, 0.1, 0.1, 0.1, 0.2, 0.2, 0.3
)), class = "data.frame", row.names = c(
  NA,
  -11L
), codepage = 65001L)

do_test <- function(x) {
  x - lag(x,3) == 3 &
  x - lag(x,2) == 2 &
  x - lag(x,1) == 1
}

dat %>% mutate(Stability = ifelse(do_test(Month) == TRUE,lag(Value,1),NA))
#>    Group Month Value Stability
#> 1      1     2   0.1        NA
#> 2      1     4   0.1        NA
#> 3      1     5   0.1        NA
#> 4      1     6   0.1        NA
#> 5      1     7   0.2       0.1
#> 6      1    10   0.1        NA
#> 7      1    11   0.1        NA
#> 8      1    12   0.1        NA
#> 9      1    13   0.2       0.1
#> 10     1    14   0.2       0.2
#> 11     2     5   0.3        NA

^{Created on 2020-09-23 by the reprex package (v0.3.0.9001)}

A similar lag approach will serve to detect 5 consecutive months.

[fullsteam]

Thank you, @technocrat, for the 'Stability' column created, is there a way to change Stability = lag(Value, 1) such that the output is an integer? For instance, for row 5 (or Month 7 for Group 1), the Stability needs to be '3' considering that Month 6's Value was 0.1 for 3 months in the past!

Could it perhaps work by using something like rle function?

[technocrat]

I misconstrued the question. For Stability to reflect an integer value of 3 or more consecutive entries different code will be needed. RLE can be used, possibly, by creating a lag(x,1) test of a difference of one, first. I'll look at this later today.