group by - R grouping based on time difference

Question

Here is my dataframe:

df <- data.frame(col_1 = c('11/13/2007', '11/17/2007', '11/19/2007', '11/25/2007', '11/28/2007'),
                 col_2 = c('A', 'B', 'C', 'D', 'E'))

I would like to add column, which would group elements using time difference of dates in col_1. For example first, second and third rows will be in group 1, since dates differ only by less than 5 days (between each consecutive dates) and row four and five will be in group 2. We will get two groups since two consecutive dates '11/19/2007' and '11/25/2007' differ by more than 5 days.

I can compute day difference between dates, but now sure how to create grouping. I would prefer solution with dplyr, but any piece of advice is appreciated.

Answer 1

I think you could create the groups without have to do anything particularly fancy.

First we clean col_1 then get the groups. Note I create lag_time_diff to help with readability but you can choose to put it directly in the cumsum if you want.

df$col_1 <- as.POSIXct(df$col_1, format = "%m/%d/%Y")

lag_time_diff <- difftime(df$col_1, lag(df$col_1, default = df$col_1[1]), units = "days")
df$group <- cumsum(ifelse(lag_time_diff>5,1,0))


df
#       col_1 col_2 group
#1 2007-11-13     A     0
#2 2007-11-17     B     0
#3 2007-11-19     C     0
#4 2007-11-25     D     1
#5 2007-11-28     E     1

All this does is check if the lagged difference in times is >5, if it is it indexes by 1 otherwise it keeps the same value.