How to filter rows based on difference in dates between rows in R?

by

Here’s an attempt using rolling joins in the data.table which I believe should be efficient

library(data.table)
# Set minimum distance
mindist <- 91L 
# Make sure it is a real Date
setDT(df)[, date := as.IDate(date)] 
# Create a new column with distance + 1 to roll join too
df[, date2 := date - (mindist + 1L)] 
# Perform a rolling join per each value in df$date2 that has atleast 91 difference from df$date
unique(df[df, on = c(id = "id", date = "date2"), roll = -Inf], by = c("id", "var1"))
#    id var1       date      date2 i.var1     i.date
# 1:  1    A 2005-10-01 2005-10-01      A 2006-01-01
# 2:  1    C 2006-03-02 2006-03-02      C 2006-06-02
# 3:  1    E 2007-08-31 2007-08-31      E 2007-12-01
# 4:  2    F 2007-01-18 2007-01-18      F 2007-04-20

This will give you two additional columns but it’s not a big of a deal IMO. Logically this makes sense and I’ve tested it successfully on different scenarios but it may need some additional proof tests.

More Related Contents:

  1. Gather multiple sets of columns
  2. How to get summary statistics by group
  3. Is there a dplyr equivalent to data.table::rleid?
  4. Replace NA with previous or next value, by group, using dplyr
  5. Filter rows which contain a certain string
  6. Calculate group mean, sum, or other summary stats. and assign column to original data
  7. Rolling mean (moving average) by group/id with dplyr
  8. Programming with dplyr using string as input
  9. Count number of rows by group using dplyr
  10. How to strsplit different number of strings in certain column by do function
  11. Pass a vector of variable names to arrange() in dplyr
  12. Efficient way to filter one data frame by ranges in another
  13. R – add column that counts sequentially within groups but repeats for duplicates
  14. Categorize numeric variable with mutate
  15. Means multiple columns by multiple groups [duplicate]
  16. How to use map from purrr with dplyr::mutate to create multiple new columns based on column pairs
  17. Conditional cumsum with reset
  18. How to speed up subset by groups
  19. Use rle to group by runs when using dplyr
  20. adding default values to item x group pairs that don’t have a value (df %>% spread %>% gather seems strange)
  21. dplyr issues when using group_by(multiple variables)
  22. dplyr piping data – difference between `.` and `.x`
  23. dplyr mutate rowwise max of range of columns
  24. dplyr: lead() and lag() wrong when used with group_by()
  25. Reorder rows using custom order
  26. Difference between rbind() and bind_rows() in R
  27. Spreading a two column data frame with tidyr
  28. R dplyr rowwise mean or min and other methods?
  29. dplyr summarize with subtotals
  30. Collapse all columns by an ID column [duplicate]

Leave a Comment