rdatetimedplyrlubridateperiod

How to split datetime period data into monthyear based on unique values in a column in R


I have a series of receivers that were deployed for various periods. The dataset looks like this:

Receiver start_dt end_dt
1 2021-05-19 15:43:00 2022-06-19 12:43:00
2 2021-08-19 15:43:00 2022-05-10 18:43:00
3 2021-12-19 15:43:00 2022-06-19 12:43:00

I would like to extract each monthyear that each receiver was deployed within the start and end dates such that each receiver is linked to each of it's respective monthyear's it was active. Here is an example of what I'm trying to achieve:

Receiver monthyear
1 2021-05
1 2021-06
1 2021-07
1 2021-08
1 2021-09
1 2021-10
1 2021-11
1 2021-12
1 2022-01
1 2022-01
1 2022-02
1 2022-03
1 2022-04
1 2022-05
1 2022-06
2 2021-08
2 2021-09
2 2021-10
2 2021-11
2 2021-12
2 2022-01
2 2022-02
2 2022-03
2 2022-04
2 2022-05
3 2021-12
3 2022-01
3 2022-02
3 2022-03
3 2022-04
3 2022-05
3 2022-06

I am not sure if lubridate is the best option here? In my head it seemed relatively simple but I can't seem to make it work with it or using other examples on stack.

Thank you for any help.


Solution

  • We create and expand a sequence of months rowwise between start_dt and end_dt. With format(monthyear, "%Y-%m") we get the month and year.

    library(dplyr)
    library(tidyr)
    
    df %>%
      mutate(across(ends_with("dt"), ~ymd_hms(.))) %>% # if you already have datetime format you don't need this line
      rowwise() %>%
      mutate(monthyear = list(seq(ceiling_date(start_dt, "month"), 
                                  floor_date(end_dt, "month"), 
                                  by = "month"))) %>%
      unnest(monthyear) %>%
      mutate(monthyear = format(monthyear, "%Y-%m")) %>%
      select(Receiver, monthyear) %>% 
      print(n=50)
    
      Receiver monthyear
          <int> <chr>    
     1        1 2021-06  
     2        1 2021-07  
     3        1 2021-08  
     4        1 2021-09  
     5        1 2021-10  
     6        1 2021-11  
     7        1 2021-12  
     8        1 2022-01  
     9        1 2022-02  
    10        1 2022-03  
    11        1 2022-04  
    12        1 2022-05  
    13        1 2022-06  
    14        2 2021-09  
    15        2 2021-10  
    16        2 2021-11  
    17        2 2021-12  
    18        2 2022-01  
    19        2 2022-02  
    20        2 2022-03  
    21        2 2022-04  
    22        2 2022-05  
    23        3 2022-01  
    24        3 2022-02  
    25        3 2022-03  
    26        3 2022-04  
    27        3 2022-05  
    28        3 2022-06