R calculate overlapping months between two start and end dates

user_4793408 0
Viewed 59

I have a dataframe with two start and stop dates that looks like this:

ID    G1_START    G1_END    G2_START    G2_END    LOCATION
1     1/1/2021   5/31/2021   2/1/2021   5/31/2021    A
2     12/1/2020  3/31/2021   10/1/2020  5/31/2021    B

What I would like to do is create one row per month per patient where the months overlap between the four dates. For example

ID    MONTH    ACTIVE    LOCATION
1    2/1/2021    1          A
1    3/1/2021    1          A
1    4/1/2021    1          A
1    5/1/2021    1          A
2    12/1/2020   1          B
2    1/1/2021    1          B
2    2/1/2021    1          B
2    3/1/2021    1          B

Where active means the ID was on both G1 and G2 during these months.

dplyr r
1 Answers

Here is a method in tidyverse

  1. Reshape data from wide to long format - pivot_longer
  2. Convert the date columns 'START', 'END' to Date class (mdy)
  3. Loop over the 'START', 'END' with map2, get the sequence by '1 month'
  4. Floor the date by month - floor_date
  5. grouped by ID, LOCATION, MONTH, filter the groups where 'Categ' distinct elements are 2
  6. Create 'ACTIVE' column of 1 after returning the distinct rows
library(dplyr)
library(tidyr)
library(lubridate)
library(purrr)
pivot_longer(df1, cols = contains("_"),
       names_to = c("Categ", ".value"), names_sep= "_") %>%     
    transmute(ID, LOCATION, Categ, MONTH = map2(mdy(START), mdy(END), ~ 
       floor_date(seq(.x, .y, by = '1 month'), 'month'))) %>% 
    unnest(MONTH) %>%
    group_by(ID, LOCATION, MONTH) %>%
    filter(n_distinct(Categ) == 2) %>% 
    ungroup %>% 
    distinct(ID, LOCATION, MONTH) %>%
    mutate(ACTIVE = 1) %>%
    select(ID, MONTH, ACTIVE, LOCATION)

-output

# A tibble: 8 x 4
     ID MONTH      ACTIVE LOCATION
  <int> <date>      <dbl> <chr>   
1     1 2021-02-01      1 A       
2     1 2021-03-01      1 A       
3     1 2021-04-01      1 A       
4     1 2021-05-01      1 A       
5     2 2020-12-01      1 B       
6     2 2021-01-01      1 B       
7     2 2021-02-01      1 B       
8     2 2021-03-01      1 B

data

df1 <- structure(list(ID = 1:2, G1_START = c("1/1/2021", "12/1/2020"
), G1_END = c("5/31/2021", "3/31/2021"), G2_START = c("2/1/2021", 
"10/1/2020"), G2_END = c("5/31/2021", "5/31/2021"), LOCATION = c("A", 
"B")), class = "data.frame", row.names = c(NA, -2L))
user_37322710
Related