Subset by consecutive years in a data.frame

Question

I have a data.frame / data.table in R as follows:

df <- data.frame(
  ID = c(rep("A", 20)),
  year = c(1968, 1971, 1972, 1973, 1974, 1976, 1978, 1980, 1982, 1984, 1985, 
           1986, 1987, 1988, 1990, 1991, 1992, 1993, 1994, 1995)
)

I'd like to subset the df in order to keep only those entries which have at least five consecutive years. In this example this is the case in two periods (1984:1988 and 1990:1995). How can I do this in R?

Answer 1

A compact solution using diff and cumsum:

setDT(df)[, grp := cumsum(c(0, diff(year)) > 1), by = ID
          ][, if (.N > 4) .SD, by = .(ID, grp)][, grp := NULL][]

which gives the desired result:

    ID year
 1:  A 1984
 2:  A 1985
 3:  A 1986
 4:  A 1987
 5:  A 1988
 6:  A 1990
 7:  A 1991
 8:  A 1992
 9:  A 1993
10:  A 1994
11:  A 1995

Explanation:

• With grp := cumsum(c(0, diff(year)) > 1), by = ID you create a (temporary) grouping variable for consecutive years for each ID.
• With if (.N > 4) .SD, by = .(ID, grp) you select only groups with 5 or more consecutive years for each ID.
• With grp := NULL you remove the (temporary) grouping variable.

---

A compareble approach in base R:

i <- with(df, ave(year, ID, FUN = function(x) { 
  r <- rle(cumsum(c(0, diff(year)) > 1));
  rep(r$lengths, r$lengths)
  } ))

df[i > 4,] # or df[which(i > 4),]

which will get you the same result.

Answer 2

Here is another way:

df2 <- NULL 
   sapply(seq(nrow(df)), function(x)
             {
              ifelse((sum(diff(df[x:(x+4), "year"], 1)) == 4 &
                      sum(diff(df[x:(x+4), "year"], 1) == 1) == 4),
                      df2 <<- rbind(df2, df[x:(x+4),]),"")
             })
df2 <- unique(df2)

Answer 3

We can try

i1 <- with(df, as.logical(ave(year, ID,  FUN = function(x) {
                        i1 <- (x[-1] - x[-length(x)]) ==1
                        i2 <- c(FALSE, i1)
                        i3 <- c(i1, FALSE)
                        rl <- rle(i2|i3)
                        rl$values[rl$values][rl$lengths[rl$values] <5] <- FALSE
                        rep(rl$values, rl$lengths)
                      })))

df[i1,]
#   ID year
#10  A 1984
#11  A 1985
#12  A 1986
#13  A 1987
#14  A 1988
#15  A 1990
#16  A 1991
#17  A 1992
#18  A 1993
#19  A 1994
#20  A 1995

---

Or use data.table

library(data.table)
i1 <- setDT(df)[, ind := (year - shift(year, fill= year[1L]))==1L , ID][, 
         {i1 <- .I[.N * ind > 3]
         .(v1 = head(i1,1)-1, v2 = tail(i1, 1))}, 
         .(ID, rl = rleid(ind))][, seq(v1, v2) , rl]$V1
df[, ind := NULL][i1]
#     ID year
# 1:  A 1984
# 2:  A 1985
# 3:  A 1986
# 4:  A 1987
# 5:  A 1988
# 6:  A 1990
# 7:  A 1991
# 8:  A 1992
# 9:  A 1993
#10:  A 1994
#11:  A 1995

Or a slightly compact option

i1 <- setDT(df)[, (shift(year, type="lead", fill = year[.N])-year)==1 |
       (year - shift(year, fill = year[1L]))==1, ID][, .I[.N>4 & V1] , .(rleid(V1), ID)]$V1
df[i1]

data

df <- data.frame(
   ID=c(rep("A", 20)),
   year=c(1968, 1971, 1972, 1973, 1974, 1976, 1978, 1980, 1982, 1984, 1985, 
   1986, 1987, 1988, 1990, 1991, 1992, 1993, 1994, 1995))

Answer 4

Another option using rowid:

DT[, c("rl", "rw") := {
    iscons <- cumsum(c(0L, diff(year)!=1L))
    .(iscons, rowid(ID, iscons))
}]

DT[rl %in% DT[rw>=5L]$rl]

data:

#adding one more group
DT <- rbindlist(list(setDT(df), copy(df)[, ID := "B"]))