d= read.csv("C:\\Users\\ADMIN\\Desktop\\d.csv")
d
## id ab Date
## 1 1 A 10/12/2022
## 2 1 A 11/12/2022
## 3 1 A 12/12/2022
## 4 1 A 13/12/2022
## 5 2 B 11/12/2022
## 6 2 A 12/12/2022
## 7 2 A 13/12/2022
## 8 2 B 14/12/2022
## 9 2 B 15/12/2022
## 10 2 B 16/12/2022
## 11 3 C 10/12/2022
## 12 3 A 11/12/2022
## 13 3 B 12/12/2022
d$Date = as.Date(d$Date, format = '%d/%m/%Y')
d$boolean [d$ab != "NA"] = 1
library(dplyr)
library (magrittr)
d %>% group_by (id, ab) %>%
summarize("Start Date"= min(as.character(Date)),
"End Date"= max(as.character(Date)),
"Length of Run"= n())
## # A tibble: 6 × 5
## # Groups: id [3]
## id ab `Start Date` `End Date` `Length of Run`
## <int> <chr> <chr> <chr> <int>
## 1 1 A 2022-12-10 2022-12-13 4
## 2 2 A 2022-12-12 2022-12-13 2
## 3 2 B 2022-12-11 2022-12-16 4
## 4 3 A 2022-12-11 2022-12-11 1
## 5 3 B 2022-12-12 2022-12-12 1
## 6 3 C 2022-12-10 2022-12-10 1
d
## id ab Date boolean
## 1 1 A 2022-12-10 1
## 2 1 A 2022-12-11 1
## 3 1 A 2022-12-12 1
## 4 1 A 2022-12-13 1
## 5 2 B 2022-12-11 1
## 6 2 A 2022-12-12 1
## 7 2 A 2022-12-13 1
## 8 2 B 2022-12-14 1
## 9 2 B 2022-12-15 1
## 10 2 B 2022-12-16 1
## 11 3 C 2022-12-10 1
## 12 3 A 2022-12-11 1
## 13 3 B 2022-12-12 1
Source: https://stackoverflow.com/questions/35162258/identify-start-date-end-date-length-of-run-of-consecutive-number-and-transpos