this probably must be an easy question, but it was hard to find an answer. I simply would like to make a new column called "groupnumber", which is a sequence of integers but holds the same number for every 7 rows.
Below is the data.
df<-structure(list(ID = c(10001L, 10001L, 10001L, 10001L, 10001L,
10001L, 10001L, 10025L, 10025L, 10025L, 10025L, 10025L, 10025L,
10025L, 10025L, 10025L, 10025L, 10025L, 10025L, 10025L, 10025L,
10092L, 10092L, 10092L, 10092L, 10092L, 10092L, 10092L), date = structure(c(16150L,
16153L, 16154L, 16155L, 16156L, 16157L, 16160L, 14848L, 14851L,
14852L, 14853L, 14854L, 14855L, 14859L, 17035L, 17036L, 17037L,
17037L, 17038L, 17039L, 17042L, 14726L, 14727L, 14728L, 14729L,
14732L, 14733L, 14734L), class = c("IDate", "Date")), eventdate = structure(c(NA,
NA, NA, 16155L, NA, NA, NA, NA, NA, NA, 14853L, NA, NA, NA, NA,
NA, NA, 17037L, NA, NA, NA, NA, NA, NA, 14729L, NA, NA, NA), class = c("IDate",
"Date")), value = c(0.58810503, 0.447739107, 0.492825138, 0.172194457,
0.440646364, 0.111933395, 0.00571012, 0.384615385, 0.226765799,
1, 1, 1, 0.2, 1, 0.448058762, 0.01423221, 0.171215881, 0.171215881,
0.603737157, 0.117750979, 0.26185567, 0.369337979, 0.393939394,
1, 0.666666667, 0.538461538, 0.314285714, 0.18), order = c(-3L,
-2L, -1L, 0L, 1L, 2L, 3L, -3L, -2L, -1L, 0L, 1L, 2L, 3L, -3L,
-2L, -1L, 0L, 1L, 2L, 3L, -3L, -2L, -1L, 0L, 1L, 2L, 3L)), row.names = c(NA,
-28L), class = c("data.table", "data.frame"))
Below is the desired output.
output<-structure(list(ID = c(10001L, 10001L, 10001L, 10001L, 10001L,
10001L, 10001L, 10025L, 10025L, 10025L, 10025L, 10025L, 10025L,
10025L, 10025L, 10025L, 10025L, 10025L, 10025L, 10025L, 10025L,
10092L, 10092L, 10092L, 10092L, 10092L, 10092L, 10092L), date = structure(c(16150L,
16153L, 16154L, 16155L, 16156L, 16157L, 16160L, 14848L, 14851L,
14852L, 14853L, 14854L, 14855L, 14859L, 17035L, 17036L, 17037L,
17037L, 17038L, 17039L, 17042L, 14726L, 14727L, 14728L, 14729L,
14732L, 14733L, 14734L), class = c("IDate", "Date")), eventdate = structure(c(NA,
NA, NA, 16155L, NA, NA, NA, NA, NA, NA, 14853L, NA, NA, NA, NA,
NA, NA, 17037L, NA, NA, NA, NA, NA, NA, 14729L, NA, NA, NA), class = c("IDate",
"Date")), value = c(0.58810503, 0.447739107, 0.492825138, 0.172194457,
0.440646364, 0.111933395, 0.00571012, 0.384615385, 0.226765799,
1, 1, 1, 0.2, 1, 0.448058762, 0.01423221, 0.171215881, 0.171215881,
0.603737157, 0.117750979, 0.26185567, 0.369337979, 0.393939394,
1, 0.666666667, 0.538461538, 0.314285714, 0.18), order = c(-3L,
-2L, -1L, 0L, 1L, 2L, 3L, -3L, -2L, -1L, 0L, 1L, 2L, 3L, -3L,
-2L, -1L, 0L, 1L, 2L, 3L, -3L, -2L, -1L, 0L, 1L, 2L, 3L), groupnumber = c(1L,
1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 4L, 4L)), row.names = c(NA,
-28L), class = c("data.table", "data.frame"))
Just for educational purposes, I would like to know two ways of doing this.
First, I would like to make the new column "groupnumber" based on the 7-row interval, where for the first 7 rows, the group number is one, and for the next 7 rows, the group number is two.
Second, I would also like to make the column based on the event date and its corresponding 3 previous and 3 next rows. So, when I first see eventdate that is not NA, I would like to group the previous 3 and next 3 rows (and thus 7 rows, including the eventdate itself) as groupnumber one. Similarly, when I find the next eventdate, I would like to do the same thing.
Thank you so much in advance for your kind help!