How to perform join over date ranges using data.table?

by

You can use the foverlaps() function which implements joins over intervals efficiently. In your case, we just need a dummy column for measurments.

Note 1: You should install the development version of data.table – v1.9.5 as a bug with foverlaps() has been fixed there. You can find the installation instructions here.

Note 2: I’ll call whatWasMeasured = dt1 and measurments = dt2 here for convenience.

require(data.table) ## 1.9.5+
dt2[, dummy := time]

setkey(dt1, start, end)
ans = foverlaps(dt2, dt1, by.x=c("time", "dummy"), nomatch=0L)[, dummy := NULL]

See ?foverlaps for more info and this post for a performance comparison.

More Related Contents:

  1. rolling joins data.table in R
  2. data.table vs dplyr: can one do something well the other can’t or does poorly?
  3. How to delete a row by reference in data.table?
  4. Why does X[Y] join of data.tables not allow a full outer join, or a left join?
  5. How to convert dataframe into time series?
  6. Data.table meta-programming
  7. data.table with two string columns of set elements, extract unique rows with each row unsorted
  8. Evaluating both column name and the target value within `j` expression within `data.table`
  9. Fill in missing values by group in data.table
  10. dplyr on data.table, am I really using data.table?
  11. data.table and parallel computing
  12. Elegantly assigning multiple columns in data.table with lapply()
  13. Sort a data.table fast by Ascending/Descending order
  14. Join R data.tables where key values are not exactly equal–combine rows with closest times
  15. how to show a legend on dual y-axis ggplot
  16. Multiple time series in one plot
  17. Why is as.Date slow on a character vector?
  18. How to group data.table by multiple columns?
  19. Apply a function to a subset of data.table columns, by column-indices instead of name
  20. Selecting a subset of columns in a data.table
  21. Merge overlapping ranges into unique groups, in dataframe
  22. add missing rows to a data table
  23. Time series plot gets offset by 2 hours if scale_x_datetime is used
  24. Using data.table i and j arguments in functions
  25. R data.table breaks in exported functions
  26. Applying a function to each row of a data.table
  27. Empty factors in “by” data.table
  28. How to use data.table within functions and loops?
  29. What you can do with a data.frame that you can’t with a data.table?
  30. data.table: anonymous function in j

Leave a Comment