Sequence of daily dates from week dates with group number for each week-CodePudding

I am working with a data table which has one column of class c("POSIXct", "POSIXt"), representing a series of dates. These dates are the first of a seven-days series. The data looks like this:

x <- structure(list(date = structure(c(1645747200, 1646352000, 1646956800, 
1647561600, 1648166400, 1648771200, 1649376000), class = c("POSIXct", 
"POSIXt"), tzone = "UTC")), row.names = c(NA, -7L), class = c("data.table", 
"data.frame"))

x
# date
# 1: 2022-02-25
# 2: 2022-03-04
# 3: 2022-03-11
# 4: 2022-03-18
# 5: 2022-03-25
# 6: 2022-04-01
# 7: 2022-04-08

I would like to extract the sequence of days between each date I have, along with the number of the seven-days period in chronological order.

I am able to obtain the daily sequence with this:

x[, seq(first(date), last(date), by = "days"), ]

However, I am a bit stuck as to how I should assign the "week number" to them.

My aim is to obtain this:

# id         date
# 1:  1 2022-02-25
# 2:  1 2022-02-26
# 3:  1 2022-02-27
# 4:  1 2022-02-28
# 5:  1 2022-03-01
# 6:  1 2022-03-02
# 7:  1 2022-03-03
# 8:  2 2022-03-04
# 9:  2 2022-03-05
# 10:  2 2022-03-06
# 11:  2 2022-03-07
# 12:  2 2022-03-08
# 13:  2 2022-03-09
# 14:  2 2022-03-10
# 15:  3 2022-03-11
# 16:  3 2022-03-12
# 17:  3 2022-03-13
# 18:  3 2022-03-14
# 19:  3 2022-03-15
# 20:  3 2022-03-16
# 21:  3 2022-03-17
# 22:  4 2022-03-18
# 23:  4 2022-03-19
# 24:  4 2022-03-20
# 25:  4 2022-03-21
# 26:  4 2022-03-22
# 27:  4 2022-03-23
# 28:  4 2022-03-24
# 29:  5 2022-03-25
# 30:  5 2022-03-26
# 31:  5 2022-03-27
# 32:  5 2022-03-28
# 33:  5 2022-03-29
# 34:  5 2022-03-30
# 35:  5 2022-03-31
# 36:  6 2022-04-01
# 36:  6 2022-04-02
# 36:  6 2022-04-03
# 36:  6 2022-04-04
# 36:  6 2022-04-05
# 36:  6 2022-04-06
# 36:  6 2022-04-07

CodePudding user response：

You can use strftime rleid:

library(data.table)
x[, "week.number" := rleid(strftime(date, format = "%V"))]

output

data.table(date = x[, seq(first(date), last(date), by = "days"), ]
           )[, "week.number" := rleid(strftime(date, format = "%V"))][]
          date week.number
 1: 2022-02-25           1
 2: 2022-02-26           1
 3: 2022-02-27           1
 4: 2022-02-28           2
 5: 2022-03-01           2
 6: 2022-03-02           2
 7: 2022-03-03           2
 8: 2022-03-04           2
 9: 2022-03-05           2
10: 2022-03-06           2
11: 2022-03-07           3
12: 2022-03-08           3
13: 2022-03-09           3
14: 2022-03-10           3
15: 2022-03-11           3
16: 2022-03-12           3
17: 2022-03-13           3
18: 2022-03-14           4
19: 2022-03-15           4
20: 2022-03-16           4
21: 2022-03-17           4
22: 2022-03-18           4
23: 2022-03-19           4
24: 2022-03-20           4
25: 2022-03-21           5
26: 2022-03-22           5
27: 2022-03-23           5
28: 2022-03-24           5
29: 2022-03-25           5
30: 2022-03-26           5
31: 2022-03-27           5
32: 2022-03-28           6
33: 2022-03-29           6
34: 2022-03-30           6
35: 2022-03-31           6
36: 2022-04-01           6
37: 2022-04-02           6
38: 2022-04-03           6
39: 2022-04-04           7
40: 2022-04-05           7
41: 2022-04-06           7
42: 2022-04-07           7
43: 2022-04-08           7

Depends on what you need, but this might more appropriate to your expected output:

data.table(date = x[, seq(first(date), last(date), by = "days"), ]
           )[, "week.number" := ((rleid(date) - 1) %/% 7)   1][]

CodePudding user response：

I just wanted to post a (most probably) quick and dirty alternative to the accepted answer that I just found.

x[, group := 1:nrow(x), ]
x[, .(rep(group, times = 7), seq(first(date), last(date), by = "day")), by = group]

# group V1         V2
# 1:     1  1 2022-02-25
# 2:     1  1 2022-02-25
# 3:     1  1 2022-02-25
# 4:     1  1 2022-02-25
# 5:     1  1 2022-02-25
# 6:     1  1 2022-02-25
# 7:     1  1 2022-02-25
# 8:     2  2 2022-03-04
# 9:     2  2 2022-03-04
# 10:     2  2 2022-03-04
# 11:     2  2 2022-03-04
# 12:     2  2 2022-03-04
# 13:     2  2 2022-03-04
# 14:     2  2 2022-03-04
# 15:     3  3 2022-03-11
# 16:     3  3 2022-03-11
# 17:     3  3 2022-03-11
# 18:     3  3 2022-03-11
# 19:     3  3 2022-03-11
# 20:     3  3 2022-03-11
# 21:     3  3 2022-03-11
# 22:     4  4 2022-03-18
# 23:     4  4 2022-03-18
# 24:     4  4 2022-03-18
# 25:     4  4 2022-03-18
# 26:     4  4 2022-03-18
# 27:     4  4 2022-03-18
# 28:     4  4 2022-03-18
# 29:     5  5 2022-03-25
# 30:     5  5 2022-03-25
# 31:     5  5 2022-03-25
# 32:     5  5 2022-03-25
# 33:     5  5 2022-03-25
# 34:     5  5 2022-03-25
# 35:     5  5 2022-03-25
# 36:     6  6 2022-04-01
# 37:     6  6 2022-04-01
# 38:     6  6 2022-04-01
# 39:     6  6 2022-04-01
# 40:     6  6 2022-04-01
# 41:     6  6 2022-04-01
# 42:     6  6 2022-04-01
# 43:     7  7 2022-04-08
# 44:     7  7 2022-04-08
# 45:     7  7 2022-04-08
# 46:     7  7 2022-04-08
# 47:     7  7 2022-04-08
# 48:     7  7 2022-04-08
# 49:     7  7 2022-04-08