How to code for the duration of consecutive monthly "stability" in R

Question

I wanted to ask for your advice and guidance for coding in R. Specifically, my goals are:

1. Make the column Previous-month-value stability (see example below): The question is 'for how long was the previous month value stable consecutively in months?'

2. Make the column "Stability" (see example below): I'm trying to show 'after how many consecutive months was there a change in the IV'. For instance, in Month 10 for Group 1, the change (from 0.2 to 0.4) occurred after 2 consecutive months (Month 8, 9) of stable IV.

3. Make the column "Change in IV" (see example below): I want to show the amount of change only for consecutive months. For instance, Month 6 to 8 for Group 1 would be 'n/a' because there is no Month 7 for this group.

Currently, I have the first three columns ("Group", "Month", "IV"):

structure(list(Group = c(1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2), Month = c(3, 
4, 5, 6, 8, 9, 10, 5, 6, 7, 9), IV = c(0.1, 0.1, 0.5, 0.2, 0.2, 
0.2, 0.4, 0.3, 0.4, 0.4, 0.4)), class = "data.frame", row.names = c(NA, 
-11L), codepage = 65001L)

The end results would look like:

structure(list(Group = c(1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2), Month = c(3, 
4, 5, 6, 8, 9, 10, 5, 6, 7, 9), IV = c(0.1, 0.1, 0.5, 0.2, 0.2, 
0.2, 0.4, 0.3, 0.4, 0.4, 0.4), Previous_month_stability = c(NA, 
1, 2, 1, NA, 1, 2, NA, 1, 1, NA), Change_IV = c(NA, 0, 0.4, -0.3, 
NA, 0, 0.2, NA, 0.1, 0, NA), Stability2 = c(NA, 0, 2, 1, NA, 
0, 2, NA, 1, 0, NA)), class = "data.frame", row.names = c(NA, 
-11L), codepage = 65001L)

In a table, it would look like:

╔═══════╦═══════╦══════╦══════════════════════════╦══════════════╦════════════╗
║ Group ║ Month ║ IV   ║ Previous_month_stability ║ Change in IV ║ Stability2 ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 1     ║ 3     ║ 0.10 ║ n/a                      ║ n/a          ║ n/a        ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 1     ║ 4     ║ 0.10 ║ 1                        ║ 0            ║ 0          ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 1     ║ 5     ║ 0.50 ║ 2                        ║ 0.40         ║ 2          ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 1     ║ 6     ║ 0.20 ║ 1                        ║ -0.30        ║ 1          ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 1     ║ 8     ║ 0.20 ║ n/a                      ║ n/a          ║ n/a        ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 1     ║ 9     ║ 0.20 ║ 1                        ║ 0            ║ 0          ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 1     ║ 10    ║ 0.40 ║ 2                        ║ 0.2          ║ 2          ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 2     ║ 5     ║ 0.30 ║ n/a                      ║ n/a          ║ n/a        ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 2     ║ 6     ║ 0.40 ║ 1                        ║ 0.10         ║ 1          ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 2     ║ 7     ║ 0.40 ║ 1                        ║ 0            ║ 0          ║
╠═══════╬═══════╬══════╬══════════════════════════╬══════════════╬════════════╣
║ 2     ║ 9     ║ 0.40 ║ n/a                      ║ n/a          ║ n/a        ║
╚═══════╩═══════╩══════╩══════════════════════════╩══════════════╩════════════╝

Answer 1

To create both columns, "Change" and "Stability in IV", try the following, using dplyr:

library(dplyr)
library(zoo)

df2 <- df %>%
  mutate(
    Group = na.locf(Group)
  ) %>%
  arrange(Group, Month) %>%
  group_by(Group) %>%
  mutate(
    tmp_continuous_block = cumsum(coalesce(Month - lag(Month) > 1, F))
  ) %>%
  group_by(Group, tmp_continuous_block) %>%
  mutate(
    Change = IV - lag(IV),
    `Previous Month Stability` = case_when(
      is.na(Change) ~ NA_integer_,
      T ~ lag(sequence(rle(IV)$lengths))
    ),
    `Stability in IV` = ifelse(Change == 0, 0L, `Previous Month Stability`)
  ) %>%
  ungroup() %>%
  select(-starts_with("tmp_"))