R: Generate sub data frame with groupings of date ranges

Question

I have a data frame containg two columns, an identifier and a date. The code below creates a sample data frame.

x <- c(rep(c("a","b"), each=10), rep(c("c", "d"), each=5))
y <- c(seq(as.Date("2014-01-01"), as.Date("2014-01-05"), by = 1), 
    as.Date("2014-03-12"), 
    as.Date("2014-03-15"),
    seq(as.Date("2014-05-11"), as.Date("2014-05-13"), by = 1),
    seq(as.Date("2014-06-11"), as.Date("2014-06-14"), by = 1),
    seq(as.Date("2014-06-01"), as.Date("2014-06-20"), by = 2),
    seq(as.Date("2014-07-31"), as.Date("2014-08-05"), by = 1))  

df <- data.frame(x = x, y = y)  

The following is the output of df.

   x      y
1 a 2014-01-01
2 a 2014-01-02
3 a 2014-01-03
4 a 2014-01-04
5 a 2014-01-05
6 a 2014-03-12
7 a 2014-03-15
8 a 2014-05-11
.
.
.
23 c 2014-06-17
24 c 2014-06-19
25 c 2014-07-31
26 d 2014-08-01
27 d 2014-08-02
28 d 2014-08-03
29 d 2014-08-04
30 d 2014-08-05

I would like to create another data frame that summarises the date ranges; i.e. for each x an entry will be created for each contiguous set of dates. The output I would like (based on the data in df) is the following:

x start.rng      end.rng       days.rng
a 2014-01-01 2014-01-05 5
a 2014-03-12 2014-03-12 1
a 2014-03-15 2014-03-15 1
a 2014-05-11 2014-05-13 3
b 2014-06-11 2014-06-14 4
b 2014-06-01 2014-06-01 1
b 2014-06-03 2014-06-03 1
b 2014-06-05 2014-06-05 1
b 2014-06-07 2014-06-07 1
b 2014-06-09 2014-06-09 1
b 2014-06-11 2014-06-11 1
c 2014-06-13 2014-06-13 1
c 2014-06-15 2014-06-15 1
c 2014-06-17 2014-06-17 1
c 2014-06-19 2014-06-19 1
c 2014-07-31 2014-07-31 1
d 2014-08-01 2014-08-05 5

I am unable to figure out how to go about this.

Thank you

Answer 1

Try

 res <- do.call(rbind, 
     lapply(split(df, df$x), function(.df)
         do.call(rbind, lapply(split(.df, 
cumsum(c(TRUE, diff(.df$y) != 1))), function(.x)
      data.frame(x = .x[1, 1], start.rng = .x[1, 
2], end.rng = .x[nrow(.x), 2], days.rng = nrow(.x))))))

row.names(res) <- 1:nrow(res)
head(res)
#  x  start.rng    end.rng days.rng
#1 a 2014-01-01 2014-01-05        5
#2 a 2014-03-12 2014-03-12        1
#3 a 2014-03-15 2014-03-15        1
#4 a 2014-05-11 2014-05-13        3
#5 b 2014-06-11 2014-06-14        4
#6 b 2014-06-01 2014-06-01        1

Or using data.table

library(data.table)
 DT1 <- setDT(df)[,indx:= cumsum(c(TRUE, diff(y)!=1)),
          by=x][,list(start.rng=y[1], end.rng=y[.N], days.rng=.N),
          by=list(x, indx)][, indx:=NULL] 

  head(DT1)
 #   x  start.rng    end.rng days.rng
 #1: a 2014-01-01 2014-01-05        5
 #2: a 2014-03-12 2014-03-12        1
 #3: a 2014-03-15 2014-03-15        1
 #4: a 2014-05-11 2014-05-13        3
 #5: b 2014-06-11 2014-06-14        4
 #6: b 2014-06-01 2014-06-01        1

Explanation

I will try to explain by splitting the codes in data.table

• Check for the difference between consecutive row values in y for each x group

     setDT(df)[, #converts `df` from `data.frame` to `data.table`
      indx:=  #create an index 
    c(0, diff(y)), by=x] #calculates the difference between consecutive `y` elements
       #for each `x` group.  Here `diff` returns one element less than the length of each `x` group.  So, I appended `0` to it.  It can be any value other than `1` so that in the next step, I can use it to create a `grouping` index
  

• Create a grouping index of indx from the above step

   df[, indx1:=cumsum(indx!=1), by=x] # you can check the result of this step to understand the process.  
  

• Using the indx1 as a new grouping variable in addition to x, we check for the first and last value of y

      df1 <-  df[, 
         list(start.rng=y[1], #first y value 
          end.rng=y[.N], #last y value .N signifies the length of each group
          day.rng=.N),  #group length i.e. .N
           by=list(x, indx1)] #grouped by x and indx1 
  

• If you don't want the column indx1,

     df1[, indx1:=NULL]