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.
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