A subset where the data.frame column has at least five consecutive years

Question

I have 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 would like to multiply df to save only those records that have at least five consecutive years . In this example, this occurs in two periods (1984: 1988 and 1990: 1995). How can I do this in R? Thanks for any support.

Answer 1

Compact solution using diff and cumsum :

 setDT(df)[, grp := cumsum(c(0, diff(year)) > 1), by = ID ][, if (.N > 4) .SD, by = 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 = grp you only select groups with 5 or more consecutive years.
• With grp := NULL you remove the (temporary) grouping variable.

---

Comparative approach in the R base:

 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 give 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 little 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

First I sorted the items:

 sorted = sort(df$year, decreasing = F) count = 0 ## count sequences keep=c() ## which to keep keep_num = c() ##counting the sequence length keep_count=1 for(i in 2:length(sorted)){ if((sorted[i]- sorted[i-1]) == 1){ ## if they are in a row count = count + 1 if(count == 4){ ## if there 4+1 years involved in a row keep=c(keep, sorted[i]- 4) } if(count >= 4){ ## if length more than 5, update keep_num[keep_count]=count } } else{ ##reset count =0 keep_count = keep_count + 1 } } keep_num = keep_num[!is.na(keep_num)] 

Reconstruction saved:

 y = c() for(i in 1:length(keep)){ y = c(y, seq(keep[i], keep[i]+keep_num[i])) } 

The subset between the ones we wanted to keep:

 selected = df[match(y, df$year, nomatch = 0),] 

This will result in the selection of rows with the desired condition.

  # ID year # A 1984 # A 1985 # A 1986 # A 1987 # A 1988 # A 1990 # A 1991 # A 1992 # A 1993 # A 1994 # A 1995 

Answer 5

step1. data in data table "d"

 d hdrY mvanoyP 1: 1981 -14.3520324 2: 1982 0.4900168 3: 1983 2.6518741 4: 1984 5.2284595 5: 1985 -6.2874634 6: 1986 -1.3287914 7: 1987 20.6385345 8: 1988 24.2090114 9: 1989 21.5302571 10: 1990 9.0267066 11: 1991 10.4148838 12: 1992 13.9189716 13: 1993 7.8816196 14: 1994 3.4650221 15: 1995 2.8722555 16: 1996 -4.1442363 17: 1997 -3.2359926 18: 1998 -5.7479137 19: 1999 2.3481127 20: 2000 0.8089402 21: 2001 -14.4741916 22: 2002 -22.9272540 23: 2003 -27.3105212 24: 2004 -13.9726022 25: 2005 -14.0055281 26: 2006 -15.8456991 27: 2007 -21.0369933 28: 2008 -13.1031347 29: 2009 4.1517341 30: 2010 20.3711446 31: 2011 27.4202037 

step2. select nvanoyP <0 and find consecutive 6 years

 d %<>% data.table() db <- d[mvanoyP < 0, ] %>% .[, tag := cumsum(c(0, diff(hdrY)) > 1)] %>% .[, if (.N > 6) .SD,.(tag)] # if (nrow(db) > 0){ db[, start := min(hdrY), by = tag] db[, end := max(hdrY), by = tag] } db 

step3. Output

 db tag hdrY mvanoyP start end 1: 3 2001 -14.47419 2001 2008 2: 3 2002 -22.92725 2001 2008 3: 3 2003 -27.31052 2001 2008 4: 3 2004 -13.97260 2001 2008 5: 3 2005 -14.00553 2001 2008 6: 3 2006 -15.84570 2001 2008 7: 3 2007 -21.03699 2001 2008 8: 3 2008 -13.10313 2001 2008