Flag consecutive dates by group

Question

Below is an example of my data (Room and Date). I would like to generate variables Goal1 , Goal2 and Goal3. Every time there is a gap in the Date variable means that the room was closed. My goal is to identify consecutive dates by room.

  Room    Date         Goal1     Goal2       Goal3
1 Upper A 2021-01-01   1         2021-01-01  2021-01-02
2 Upper A 2021-01-02   1         2021-01-01  2021-01-02
3 Upper A 2021-01-05   2         2021-01-05  2021-01-05
4 Upper A 2021-01-10   3         2021-01-10  2021-01-10
5 Upper B 2021-01-01   1         2021-01-01  2021-01-01
6 Upper B 2021-02-05   2         2021-02-05  2021-02-07
7 Upper B 2021-02-06   2         2021-02-05  2021-02-07
8 Upper B 2021-02-07   2         2021-02-05  2021-02-07

df <- data.frame("Area" = c("Upper A", "Upper A", "Upper A", "Upper A",
                            "Upper B", "Upper B", "Upper B", "Upper B"),
                "Date" = c("1/1/2021", "1/2/2021", "1/5/2021", "1/10/2021",
                           "1/1/2021", "2/5/2021", "2/6/2021", "2/7/2021"))
df$Date <- as.Date(df$Date, format = "%m/%d/%Y")

rcs · Accepted Answer · 2021-05-14T09:32:50.777

Using dplyr:

R> df %>%
  group_by(Area) %>%
  mutate(Goal = cumsum(c(1, diff(Date) > 1))) %>%
  group_by(Area, Goal) %>%
  mutate(Goal2 = min(Date), Goal3 = max(Date))
# # A tibble: 8 x 5
# # Groups:   Area, Goal [5]
#   Area    Date        Goal Goal2      Goal3     
#   <chr>   <date>     <dbl> <date>     <date>    
# 1 Upper A 2021-01-01     1 2021-01-01 2021-01-02
# 2 Upper A 2021-01-02     1 2021-01-01 2021-01-02
# 3 Upper A 2021-01-05     2 2021-01-05 2021-01-05
# 4 Upper A 2021-01-10     3 2021-01-10 2021-01-10
# 5 Upper B 2021-01-01     1 2021-01-01 2021-01-01
# 6 Upper B 2021-02-05     2 2021-02-05 2021-02-07
# 7 Upper B 2021-02-06     2 2021-02-05 2021-02-07
# 8 Upper B 2021-02-07     2 2021-02-05 2021-02-07

The Goal column is calculated in the following way: use diff on Date column to identify gaps (difference > 1), afterwards apply cumsum to obtain the consecutive values.

Thanks for the beautiful code and the explanation! Appreciate it! — Mar355, May 13 '21 at 12:56
I had the exact same question, but this solution only worked for me for a portion of my data. Some sequences were grouped correctly while it also grouped dates that were a year apart...any ideas? — Nate, Apr 16 '22 at 04:17

Flag consecutive dates by group

1 Answers1