Generate sequence of dates for given frequency as per days of occurence

user_16102355 0
Viewed 95

Trying to generate a date sequence in R programming (using lubridate) of dates with a given start date and frequency is not a numeric value but days on which the dates can occur.

Given is the below table where group, start date, day and occurrence flag is defined

+-------+------------+-----+-----+
| Group | start_date | Day | Y/N |
+-------+------------+-----+-----+
| foo   | 02-06-2021 | Mon |   0 |
| foo   | 02-06-2021 | Tue |   1 |
| foo   | 02-06-2021 | Wed |   0 |
| foo   | 02-06-2021 | Thu |   1 |
| foo   | 02-06-2021 | Fri |   1 |
| foo   | 02-06-2021 | Sat |   1 |
| foo   | 02-06-2021 | Sun |   0 |
| bar   | 02-06-2021 | Mon |   1 |
| bar   | 02-06-2021 | Tue |   0 |
| bar   | 02-06-2021 | Wed |   0 |
| bar   | 02-06-2021 | Thu |   1 |
| bar   | 02-06-2021 | Fri |   1 |
| bar   | 02-06-2021 | Sat |   0 |
| bar   | 02-06-2021 | Sun |   0 |
+-------+------------+-----+-----+

Required output is as below.

+-------+------------+---------------------+
| Group | given_date | next_available_date |
+-------+------------+---------------------+
| foo   | 02-06-2021 | 03-06-2021          |
| foo   | 04-06-2021 | 04-06-2021          |
| foo   | 06-06-2021 | 08-06-2021          |
| bar   | 02-06-2021 | 03-06-2021          |
| bar   | 05-06-2021 | 07-06-2021          |
+-------+------------+---------------------+

Some ideas around while loop which i thought could be tired.

for each given_date{
inputdate = given_date
while(true){
 {
 if(group =="Foo" & day(inputdate) in ('Tue','Thu','Fri','Sat')
 next_available_date=inputdate
 break
 }
 else
 {
  inputdate = inputdate+(1 day) (repeat the loop until if condition is satisfied)
 } 
}
}

If condition for different groups could be different.

Not able to figure how to leverage uneven frequency to get the next available date.

dplyr lubridate r datetime
1 Answers

Working on larger sample, as discussed earlier in comments. Strategy followed -

  • As your day column always start from Mon which is not equal to start_date so the column matching weekday is required.
  • So Created day field to ordered factor type so that it can be manipulatedit into integers.
  • Arranged the dataframe in such a way that your every group starts from that day only. Used modulo division %% for this
  • After arranging the task was rather easier. I created seven dates for each weekday end, for each group and each start_date.
  • Filtered out rows with Y/N as 0 anywhere.
  • Now you require only top row so used slice_head()
df <- data.frame(
  stringsAsFactors = FALSE,
                   Group = c("foo","foo","foo",
                             "foo","foo","foo","foo","foo","foo","foo",
                             "foo","foo","foo","foo","foo","foo","foo",
                             "foo","foo","foo","foo","bar","bar","bar",
                             "bar","bar","bar","bar","bar","bar","bar","bar",
                             "bar","bar","bar"),
              start_date = c("02-06-2021",
                             "02-06-2021","02-06-2021","02-06-2021","02-06-2021",
                             "02-06-2021","02-06-2021","04-06-2021",
                             "04-06-2021","04-06-2021","04-06-2021","04-06-2021",
                             "04-06-2021","04-06-2021","06-06-2021","06-06-2021",
                             "06-06-2021","06-06-2021","06-06-2021",
                             "06-06-2021","06-06-2021","02-06-2021","02-06-2021",
                             "02-06-2021","02-06-2021","02-06-2021","02-06-2021",
                             "02-06-2021","05-06-2021","05-06-2021",
                             "05-06-2021","05-06-2021","05-06-2021","05-06-2021",
                             "05-06-2021"),
                     Day = c("Mon","Tue","Wed",
                             "Thu","Fri","Sat","Sun","Mon","Tue","Wed",
                             "Thu","Fri","Sat","Sun","Mon","Tue","Wed",
                             "Thu","Fri","Sat","Sun","Mon","Tue","Wed",
                             "Thu","Fri","Sat","Sun","Mon","Tue","Wed","Thu",
                             "Fri","Sat","Sun"),
                     y_n = c(0L,1L,0L,1L,1L,
                             1L,0L,0L,1L,0L,1L,1L,1L,0L,0L,1L,0L,1L,
                             1L,1L,0L,1L,0L,0L,1L,1L,0L,0L,1L,0L,
                             0L,1L,1L,0L,0L)
      )

library(lubridate)
library(tidyverse)

df %>% group_by(Group, start_date) %>%
  mutate(Day = factor(Day, levels = Day, ordered = T)) %>%
  arrange(Group, (as.numeric(Day) + 7 - wday(dmy(start_date), week_start = 1)) %% 7, .by_group = T) %>%
  mutate(next_available_date = dmy(start_date) + 0:6) %>%
  filter(y_n !=0) %>%
  slice_head()
#> # A tibble: 5 x 5
#> # Groups:   Group, start_date [5]
#>   Group start_date Day     y_n next_available_date
#>   <chr> <chr>      <ord> <int> <date>             
#> 1 bar   02-06-2021 Thu       1 2021-06-03         
#> 2 bar   05-06-2021 Mon       1 2021-06-07         
#> 3 foo   02-06-2021 Thu       1 2021-06-03         
#> 4 foo   04-06-2021 Fri       1 2021-06-04         
#> 5 foo   06-06-2021 Tue       1 2021-06-08

On the data provided

df <- data.frame(
  stringsAsFactors = FALSE,
                   Group = c("foo","foo","foo",
                             "foo","foo","foo","foo","bar","bar","bar",
                             "bar","bar","bar","bar"),
              start_date = c("02-06-2021",
                             "02-06-2021","02-06-2021","02-06-2021","02-06-2021",
                             "02-06-2021","02-06-2021","02-06-2021",
                             "02-06-2021","02-06-2021","02-06-2021","02-06-2021",
                             "02-06-2021","02-06-2021"),
                     Day = c("Mon","Tue","Wed",
                             "Thu","Fri","Sat","Sun","Mon","Tue","Wed",
                             "Thu","Fri","Sat","Sun"),
                     y_n = c(0L,1L,0L,1L,1L,
                             1L,0L,1L,0L,0L,1L,1L,0L,0L)
      )

library(lubridate)
library(tidyverse)



df %>% group_by(Group, start_date) %>%
  mutate(Day = factor(Day, levels = Day, ordered = T)) %>%
  arrange(Group, (as.numeric(Day) + 7 - wday(dmy(start_date), week_start = 1)) %% 7, .by_group = T) %>%
  mutate(next_available_date = dmy(start_date) + 0:6) %>%
  filter(y_n !=0) %>%
  slice_head()

#> # A tibble: 2 x 5
#> # Groups:   Group, start_date [2]
#>   Group start_date Day     y_n next_available_date
#>   <chr> <chr>      <ord> <int> <date>             
#> 1 bar   02-06-2021 Thu       1 2021-06-03         
#> 2 foo   02-06-2021 Thu       1 2021-06-03

Created on 2021-06-02 by the reprex package (v2.0.0)

user_28848590
Related