Create identifier column based on repeating sequence of numbers in data frame

I have a monitoring data set which records weights for different subjects in cycles of 21 days (mostly) - however, sometimes the cycle i shortened (e.g. 20 days) or expanded (e.g. 28 days). See example:

set.seed(20)

df <- data.frame(subject1 = runif(n=1:90, min = 1, max = 100), 
                 subject2 = runif(n=1:90, min = 1, max = 100), 
                 subject3 = runif(n=1:90, min = 1, max = 100), 
                 day = c(rep(1:21, 2), 1:28, 1:20))
df

I would like to create a column with a "batch ID" which increase in number each time the cycle starts over, getting something like this:

set.seed(20)

df <- data.frame(subject1 = runif(n=1:90, min = 1, max = 100), 
                 subject2 = runif(n=1:90, min = 1, max = 100), 
                 subject3 = runif(n=1:90, min = 1, max = 100), 
                 day = c(rep(1:21, 2), 1:28, 1:20), 
                 ID = c(rep("batch 1", 21), rep("batch 2", 21), rep("batch 3", 28), rep("batch 4", 20)))
df

I don't know where to start. The data has been collected for many years and the df is extremely long, which is why I need an automated method for this.

I usually use dplyr, however solutions in all languages are welcome.

It is a kind of starting a new group. We can do it with cumsum(day==1). Eachtime day is 1, a new group is started:

library(dplyr)

df %>% 
  mutate(ID = cumsum(day==1),
         ID = paste("batch", ID))

   subject1  subject2  subject3 day      ID
1  28.112304 23.747643  2.506307   1 batch 1
2  14.707646 64.856926 64.934039   2 batch 1
3  63.048533 72.325234 68.608072   3 batch 1
4  94.326426 75.051280  6.784585   4 batch 1
5  27.176812 31.487133 56.319612   5 batch 1
6  94.341248 59.129401  7.002702   6 batch 1
7  97.219212 89.005288 51.363620   7 batch 1
8  14.826730 11.091299 72.477603   8 batch 1
9  14.250449 84.572366 51.806585   9 batch 1
10  8.731178 50.483787 63.905433  10 batch 1
11 24.595016 54.690629 29.220187  11 batch 1
12 66.109872 38.976594 57.952008  12 batch 1
13 69.709213 28.246633 96.082079  13 batch 1
14 98.662153 39.431172 38.304419  14 batch 1
15 35.180087 19.315271 94.495912  15 batch 1
16 45.465575 31.429093 86.362035  16 batch 1
17 88.569241 65.789032 15.264510  17 batch 1
18 41.317364 19.413599 36.570227  18 batch 1
19 93.470092 61.837675 28.270692  19 batch 1
20 99.405225  1.825664 46.233582  20 batch 1
21 98.778535 10.493757  3.473848  21 batch 1
22 94.813979 15.090318 10.536749   1 batch 2
23  1.580498 43.355038 47.193968   2 batch 2
24  8.171572 82.660440 99.404702   3 batch 2
25 10.791114 40.136418  7.615652   4 batch 2
26 28.782935 61.110801 15.203424   5 batch 2
27 26.170606 60.001865 21.975446   6 batch 2
28 98.073649  4.205692 97.422975   7 batch 2
29 23.640042  5.384184 18.883684   8 batch 2
30 18.924822 78.691799 15.707679   9 batch 2
31 63.542991 83.722546 81.379621  10 batch 2
32 65.861969 51.918785 14.969885  11 batch 2
33 59.136869 45.680453 77.921039  12 batch 2
34 43.916728 96.927358 71.636865  13 batch 2
35 84.404238 25.997312 27.519646  14 batch 2
36  1.343036 38.276619 74.979714  15 batch 2
37 54.025242 68.931825 67.697015  16 batch 2
38  9.654027 45.233349 87.036974  17 batch 2
39 90.297132 78.244482 43.231548  18 batch 2
40 53.228791 49.865308 86.774027  19 batch 2
41 72.950380 74.636688 45.980257  20 batch 2
42 15.766594 32.927134 96.377100  21 batch 2
43 65.280840 33.047331 53.369068   1 batch 3
44 41.419024 84.291542  1.055891   2 batch 3
45 64.189356 97.864732  8.435106   3 batch 3
46 64.782105 82.842300 80.991830   4 batch 3
47 61.184364 20.249522 13.622524   5 batch 3
48 20.608730 74.588932 91.931483   6 batch 3
49 80.901141 28.992681 72.589333   7 batch 3
50  4.084888 30.209502 63.598710   8 batch 3
51 95.588275 74.761589 62.743750   9 batch 3
52 62.271646  9.675652 76.652212  10 batch 3
53 43.174593 94.037260 42.026702  11 batch 3
54 53.207426 93.448598 61.360762  12 batch 3
55 19.355807 35.463982 53.029776  13 batch 3
56 13.580139 97.384648 80.816749  14 batch 3
57 32.219313 26.256102 45.672542  15 batch 3
58 33.894195 73.801500  8.051782  16 batch 3
59 45.671515 51.298810  7.600933  17 batch 3
60 24.499980 62.102165 86.877326  18 batch 3
61 60.893774 30.891038 16.634176  19 batch 3
62 89.360993 78.245477 29.801789  20 batch 3
63 57.501857 19.254282 20.347036  21 batch 3
64 80.004186 17.583524 30.764541  22 batch 3
65 71.140736 76.311887 30.298813  23 batch 3
66 72.739952 13.229765 56.278070  24 batch 3
67 62.314198 79.444292  8.105422  25 batch 3
68 32.037481 10.490401 92.195441  26 batch 3
69 66.342709 48.151444 26.442403  27 batch 3
70 65.165316  9.942519  6.828309  28 batch 3
71 45.501737 15.926725 93.197676   1 batch 4
72 48.523332  1.570688 55.853054   2 batch 4
73 26.508309 52.256063 93.128411   3 batch 4
74 97.476880 35.398011 89.341347   4 batch 4
75 17.123732 84.779030 43.547328   5 batch 4
76 18.453889 59.836859 38.898992   6 batch 4
77  1.419466  1.403754 75.434308   7 batch 4
78 21.803886 91.401409 84.694884   8 batch 4
79 61.964926 93.287858 36.304794   9 batch 4
80 81.242887 82.993459  3.940457  10 batch 4
81 57.470248  2.156846 16.255565  11 batch 4
82 79.895440 46.352555  7.666849  12 batch 4
83 38.304018 46.883902 28.208153  13 batch 4
84 92.238729 35.422918 99.706443  14 batch 4
85 60.493556 71.277974 52.243233  15 batch 4
86 99.128086 36.706295 60.799051  16 batch 4
87 29.826266 61.463732 86.887740  17 batch 4
88 46.486269 86.044449 73.024360  18 batch 4
89 96.568415  6.379009 58.893206  19 batch 4
90 92.866674 53.126197 59.812420  20 batch 4

A new group starts, if its day value is smaller than its precursor. This works even for groups of unequal sizes. Then you can apply a run length encoding:

library(tidyverse)

set.seed(20)

df <- data.frame(
  subject1 = runif(n = 1:10, min = 1, max = 100),
  subject2 = runif(n = 1:10, min = 1, max = 100),
  subject3 = runif(n = 1:10, min = 1, max = 100),
  day = c(1:10, 1:12, 1:8)
)

df <-
  df |>
  as_tibble() |>
  mutate(batch_flag = (day > lag(day)) |> replace_na(TRUE))

day_rle <- df$batch_flag |> rle()
day_rle$values <-
  day_rle$values |>
  length() |>
  seq()
day_batches <- day_rle |> inverse.rle()

df <-
  df |>
  mutate(
    batch = day_batches |>
      map_dbl(~ ifelse(.x %% 2 == 0, .x + 1, .x)) |>
      map_dbl(~ ceiling(.x / 2))
  ) |>
  select(-batch_flag)

df |> print(n = 30)
#> # A tibble: 30 × 5
#>    subject1 subject2 subject3   day batch
#>       <dbl>    <dbl>    <dbl> <int> <dbl>
#>  1    87.9     71.8     49.7      1     1
#>  2    77.1     76.0      4.00     2     1
#>  3    28.6      1.19    44.6      3     1
#>  4    53.4     74.5      8.65     4     1
#>  5    96.3     20.0     27.2      5     1
#>  6    98.1     45.8      7.89     6     1
#>  7    10.0     32.9     90.8      7     1
#>  8     8.00    11.8     99.2      8     1
#>  9    33.4     29.6      7.34     9     1
#> 10    37.6     82.1     67.8     10     1
#> 11    87.9     71.8     49.7      1     2
#> 12    77.1     76.0      4.00     2     2
#> 13    28.6      1.19    44.6      3     2
#> 14    53.4     74.5      8.65     4     2
#> 15    96.3     20.0     27.2      5     2
#> 16    98.1     45.8      7.89     6     2
#> 17    10.0     32.9     90.8      7     2
#> 18     8.00    11.8     99.2      8     2
#> 19    33.4     29.6      7.34     9     2
#> 20    37.6     82.1     67.8     10     2
#> 21    87.9     71.8     49.7     11     2
#> 22    77.1     76.0      4.00    12     2
#> 23    28.6      1.19    44.6      1     3
#> 24    53.4     74.5      8.65     2     3
#> 25    96.3     20.0     27.2      3     3
#> 26    98.1     45.8      7.89     4     3
#> 27    10.0     32.9     90.8      5     3
#> 28     8.00    11.8     99.2      6     3
#> 29    33.4     29.6      7.34     7     3
#> 30    37.6     82.1     67.8      8     3

^{Created on 2023-05-13 by the reprex package (v2.0.1)}