My dataframe looks like this

df <- read.table(text="
                 id          nr      date
    1            124         1       2017-01-01
    2            122         1       2017-01-03
    3            124         2       2017-01-04
    4            121         1       2017-02-05
    5            124         3       2017-02-06
    6            124         3       2017-04-06
", header=TRUE)

I have to remove rows from my dataframe where difference in days is less than 30 days. I usually do it with lead function, then I calculate difference in days and if it's below the limit then I remove it.

But now I want to remove each row that is below the limit. And not just that; if its date difference between date more than 30 days, then I have to filter all next rows that are below the limit.

In other words, there is first row, row numbers 2 and 3 should be removed, since date difference is only a few days. Difference between row 4 and one is more than 30 days, so it shouldn't be removed, difference between row 4 and 5 is less than 30 days so remove it, 6 should be present in result since difference is more than 30 days and so on.

2 Answers

3
Rui Barradas On Best Solutions

Maybe there are simpler algorithms but this one does it.

remove_dates <- function(DF, col = "date", lim = 30){
  n <- nrow(DF)
  log_inx <- !logical(n)
  i <- 1
  j <- 2
  while(i < n & j <= n){
    d <- difftime(DF[j, col], DF[i, col], unit = "days")
    if(d < lim){
      log_inx[j] <- FALSE
      j <- j + 1
    }else{
      i <- j
      j <- j + 1
    }
  }
  DF[log_inx, ]
}

remove_dates(df)
#   id nr       date
#1 124  1 2017-01-01
#4 121  1 2017-02-05
#6 124  3 2017-04-06

Note that the function above can be used in a package magrittr pipe, %>%.

library(dplyr)

df %>% remove_dates()
#   id nr       date
#1 124  1 2017-01-01
#4 121  1 2017-02-05
#6 124  3 2017-04-06
0
duckmayr On

Using a dplyr approach:

df <- read.table(text="
                 id          nr      date
    1            124         1       2017-01-01
    2            122         1       2017-01-03
    3            124         2       2017-01-04
    4            121         1       2017-02-05
    5            124         3       2017-02-06
    6            124         3       2017-04-06
", header=TRUE)

df$date <- as.Date(df$date)

library(dplyr)

df %>%
    mutate(tmp = lag(date)) %>%
    filter(date - tmp > 30 | date == first(date)) %>%
    select(-tmp)

#    id nr       date
# 1 124  1 2017-01-01
# 2 121  1 2017-02-05
# 3 124  3 2017-04-06