1
$\begingroup$

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")
$\endgroup$
0
$\begingroup$

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.

$\endgroup$
1
  • $\begingroup$ Thanks for the beautiful code and the explanation! Appreciate it! $\endgroup$ May 13 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.