I had a similar question to this previously again, and thanks to @akrun, I could solve the problem. However, now I have run into another new problem.
Again, I have the following table. I would like to make a new column called order so that the day when eventdate is not NA, the order of it is zero. And then, I would like to fill up negative consecutively decreasing integers to the previous rows and positive consecutive increasing integers to the below rows per ID. Again, I only need the previous three rows to the event date and three rows after the event date, which makes seven rows per eventdate, since the seven rows include the eventdate itself. Please note that there could be multiple eventdates per ID.
What I would like to add to the previous question is that now the two eventdates for the ID 3 occurs too frequent. There is only a day between the two eventdates. In this case, I would like to ignore the upcoming eventdate (2012-06-12) and create three consecutive and increasing order integers as if nothing happened on 2012-06-12. Similarly, on 2012-06-12, I would like to ignore eventdate(2012-06-10) and create three consecutive and decreasing order integers prior to 2012-06-12. It would make more sense to compare the below tables.
This is the original table called df I have right now. Please not that the eventdates for ID 3 occurs frequently.
+------------+----+------------+-------+
| Date | ID | eventdate | Value |
+------------+----+------------+-------+
| 2011-03-14 | 1 | NA | 10 |
| 2011-03-15 | 1 | NA | 11 |
| 2011-03-16 | 1 | NA | 12 |
| 2011-03-17 | 1 | NA | 11 |
| 2011-03-18 | 1 | 2011-03-18 | 15 |
| 2011-03-19 | 1 | NA | 17 |
| 2011-03-20 | 1 | NA | 18 |
| 2011-03-21 | 1 | NA | 15 |
| 2011-06-17 | 1 | NA | 20 |
| 2011-06-18 | 1 | NA | 21 |
| 2011-06-19 | 1 | NA | 22 |
| 2011-06-20 | 1 | 2011-06-20 | 25 |
| 2011-06-21 | 1 | NA | 26 |
| 2011-06-22 | 1 | NA | 25 |
| 2011-06-23 | 1 | NA | 26 |
| 2011-05-25 | 2 | NA | 5 |
| 2011-05-26 | 2 | NA | 6 |
| 2011-05-27 | 2 | NA | 7 |
| 2011-05-28 | 2 | 2011-05-28 | 9 |
| 2011-05-29 | 2 | NA | 10 |
| 2011-05-30 | 2 | NA | 11 |
| 2011-05-31 | 2 | NA | 15 |
| 2011-06-01 | 2 | NA | 16 |
| 2012-06-06 | 3 | NA | 20 |
| 2012-06-07 | 3 | NA | 22 |
| 2012-06-08 | 3 | NA | 23 |
| 2012-06-09 | 3 | NA | 24 |
| 2012-06-10 | 3 | 2012-06-10 | 28 |
| 2012-06-11 | 3 | NA | 29 |
| 2012-06-12 | 3 | 2012-06-12 | 25 |
| 2012-06-13 | 3 | NA | 24 |
| 2012-06-14 | 3 | NA | 26 |
| 2012-06-15 | 3 | NA | 24 |
+------------+----+------------+-------+
Below is the desired output from the above table. Please check the change for ID 3.
+------------+----+------------+-------+-------+
| Date | ID | eventdate | Value | order |
+------------+----+------------+-------+-------+
| 2011-03-15 | 1 | NA | 11 | -3 |
| 2011-03-16 | 1 | NA | 12 | -2 |
| 2011-03-17 | 1 | NA | 11 | -1 |
| 2011-03-18 | 1 | 2011-03-18 | 15 | 0 |
| 2011-03-19 | 1 | NA | 17 | 1 |
| 2011-03-20 | 1 | NA | 18 | 2 |
| 2011-03-21 | 1 | NA | 15 | 3 |
| 2011-06-17 | 1 | NA | 20 | -3 |
| 2011-06-18 | 1 | NA | 21 | -2 |
| 2011-06-19 | 1 | NA | 22 | -1 |
| 2011-06-20 | 1 | 2011-06-20 | 25 | 0 |
| 2011-06-21 | 1 | NA | 26 | 1 |
| 2011-06-22 | 1 | NA | 25 | 2 |
| 2011-06-23 | 1 | NA | 26 | 3 |
| 2011-05-25 | 2 | NA | 5 | -3 |
| 2011-05-26 | 2 | NA | 6 | -2 |
| 2011-05-27 | 2 | NA | 7 | -1 |
| 2011-05-28 | 2 | 2011-05-28 | 9 | 0 |
| 2011-05-29 | 2 | NA | 10 | 1 |
| 2011-05-30 | 2 | NA | 11 | 2 |
| 2011-05-31 | 2 | NA | 15 | 3 |
| 2012-06-07 | 3 | NA | 22 | -3 |
| 2012-06-08 | 3 | NA | 23 | -2 |
| 2012-06-09 | 3 | NA | 24 | -1 |
| 2012-06-10 | 3 | 2012-06-10 | 28 | 0 |
| 2012-06-11 | 3 | NA | 29 | 1 |
| 2012-06-12 | 3 | NA | 25 | 2 |
| 2012-06-13 | 3 | NA | 24 | 3 |
| 2012-06-09 | 3 | NA | 24 | -3 |
| 2012-06-10 | 3 | NA | 28 | -2 |
| 2012-06-11 | 3 | NA | 29 | -1 |
| 2012-06-12 | 3 | 2012-06-12 | 25 | 0 |
| 2012-06-13 | 3 | NA | 24 | 1 |
| 2012-06-14 | 3 | NA | 26 | 2 |
| 2012-06-15 | 3 | NA | 24 | 3 |
+------------+----+------------+-------+-------+
Below is the data!
df<-structure(list(Date = structure(c(15047L, 15048L, 15049L, 15050L,
15051L, 15052L, 15053L, 15054L, 15142L, 15143L, 15144L, 15145L,
15146L, 15147L, 15148L, 15119L, 15120L, 15121L, 15122L, 15123L,
15124L, 15125L, 15126L, 15497L, 15498L, 15499L, 15500L, 15501L,
15502L, 15503L, 15504L, 15505L, 15506L), class = c("IDate", "Date"
)), ID = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L), eventdate = structure(c(NA, NA, NA, NA, 15051L,
NA, NA, NA, NA, NA, NA, 15145L, NA, NA, NA, NA, NA, NA, 15122L,
NA, NA, NA, NA, NA, NA, NA, NA, 15501L, NA, 15503L, NA, NA, NA
), class = c("IDate", "Date")), Value = c(10L, 11L, 12L, 11L,
15L, 17L, 18L, 15L, 20L, 21L, 22L, 25L, 26L, 25L, 26L, 5L, 6L,
7L, 9L, 10L, 11L, 15L, 16L, 20L, 22L, 23L, 24L, 28L, 29L, 25L,
24L, 26L, 24L)), row.names = c(NA, -33L), class = c("data.table",
"data.frame"))
output<-structure(list(Date = structure(c(15048L, 15049L, 15050L, 15051L,
15052L, 15053L, 15054L, 15142L, 15143L, 15144L, 15145L, 15146L,
15147L, 15148L, 15119L, 15120L, 15121L, 15122L, 15123L, 15124L,
15125L, 15498L, 15499L, 15500L, 15501L, 15502L, 15503L, 15504L,
15500L, 15501L, 15502L, 15503L, 15504L, 15505L, 15506L), class = c("IDate",
"Date")), ID = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L,
1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L,
3L, 3L, 3L, 3L, 3L, 3L, 3L, 3L), eventdate = structure(c(NA,
NA, NA, 15051L, NA, NA, NA, NA, NA, NA, 15145L, NA, NA, NA, NA,
NA, NA, 15122L, NA, NA, NA, NA, NA, NA, 15501L, NA, NA, NA, NA,
NA, NA, 15503L, NA, NA, NA), class = c("IDate", "Date")), Value = c(11L,
12L, 11L, 15L, 17L, 18L, 15L, 20L, 21L, 22L, 25L, 26L, 25L, 26L,
5L, 6L, 7L, 9L, 10L, 11L, 15L, 22L, 23L, 24L, 28L, 29L, 25L,
24L, 24L, 28L, 29L, 25L, 24L, 26L, 24L), 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, -3L, -2L,
-1L, 0L, 1L, 2L, 3L)), row.names = c(NA, -35L), class = c("data.table",
"data.frame"))
Thank you very much in advance!