How do I group periods of dates with the same value together in R?

Karmic Dreamwork 0
Viewed 32

Builds on this question: How do I find three or more consecutive dates with the same value in R?

I am looking to group periods of 3 days with the same values together if they occur within 7 days of each other

As an example, if a group of three days with the value of 2 (1/1/2000 to 1/3/2000), and another group with a value of 2 (1/5/2000 to 1/7/2000) occurs within a week of each other, assign a group value of x in another column (in this case, 1, since it would be the first group)

I hope that makes sense! Any help would be greatly appreciated :)

Reprex:

library("dplyr")
#Goal: Group together periods of 3 days with the same values together if they occur within 7 days of eachother
#example, if a group of three days with the value of 2 (1/1/2000 to 1/3/2000), and another group with a value of 2 (1/5/2000 to 1/7/2000) occurs within a week of eachother, assign a group value of x in another column
data <- data.frame(Date = c("2000-01-01", "2000-01-02", "2000-01-03", #2, 2, 2, ***
                            "2000-01-04",  #5
                            "2000-01-05", "2000-01-06", "2000-01-07",  #2, 2, 2, ***
                            "2000-01-08", "2000-01-09", "2000-01-10",  #0,1,8,
                            "2000-01-11", "2000-01-12", "2000-01-13",  #7,7,7, ***
                            "2000-01-14", "2000-01-15", "2000-01-16", "2000-01-17", "2000-01-18", "2000-01-19", "2000-01-20", "2000-01-21", #5,2,3,4,5,7,2,5,
                            "2000-01-22", "2000-01-23", "2000-01-24", "2000-01-25", #6,6,6,6, ***
                            "2000-01-26", "2000-01-27", #0,3
                            "2000-01-28", "2000-01-29", "2000-01-30"), #6,6,6 ***
                   Value = c(2,2,2,
                             5,
                             2,2,2,
                             0,1,8,
                             7,7,7,
                             5,2,3,4,5,7,2,5,
                             6,6,6,6,
                             0,3,
                             6,6,6))

head(data)                   
#Code that separates data into groups of 3 of the same value (https://stackoverflow.com/questions/73694683/how-do-i-find-three-or-more-consecutive-dates-with-the-same-value-in-r/73694920#73694920) 
groupeddata <- data %>% 
  filter(rep(rle(Value)$length>=3,rle(Value)$length))

groupeddata$Group <- NA

#instead of NA, the group values would go here:
#Goal: values should include 3 groups:
#1: "2000-01-01", "2000-01-02", "2000-01-03" and "2000-01-05", "2000-01-06", "2000-01-07" - group of the same value (2) within a close time period
#2: "2000-01-11", "2000-01-12", "2000-01-13" - group of the same value (7)
#3: "2000-01-22", "2000-01-23", "2000-01-24", "2000-01-25" and "2000-01-28", "2000-01-29", "2000-01-30" -group of the same value (6) within a close time period
group date dplyr r
1 Answers

I hope I understood the question correctly. One option is using case_when function:

library("dplyr")

data %>% 
   filter(rep(rle(Value)$length>=3,rle(Value)$length)) %>% 
   mutate(
     status = case_when(
               Value == 2 ~ "Group 1",
               Value == 7 ~ "Group 2",
               Value == 6 ~ "Group 3",
               TRUE ~ "NULL"      
               )
             )

         Date Value  status
1  2000-01-01     2 Group 1
2  2000-01-02     2 Group 1
3  2000-01-03     2 Group 1
4  2000-01-05     2 Group 1
5  2000-01-06     2 Group 1
6  2000-01-07     2 Group 1
7  2000-01-11     7 Group 2
8  2000-01-12     7 Group 2
9  2000-01-13     7 Group 2
10 2000-01-22     6 Group 3
11 2000-01-23     6 Group 3
12 2000-01-24     6 Group 3
13 2000-01-25     6 Group 3
14 2000-01-28     6 Group 3
15 2000-01-29     6 Group 3
16 2000-01-30     6 Group 3
uservAl810
Related