Flag consecutive dates by group

Question

Flag consecutive dates by group

Marvin Aliaga

2021年5月14日 09:32

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)

Topic dplyr dataframe r

Category Data Science

rcs · Accepted Answer · 2021年5月14日 09:32

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.

Flag consecutive dates by group

About