Join R data.tables where key values are not exactly equal–combine rows with closest times

by

Another option may be roll="nearest" (new in v1.8.8 on CRAN).

> setkey(DT1,x,time)
> DT1
   x time v
1: a   10 1
2: a   30 2
3: a   60 3
4: b   10 4
5: b   30 5
6: b   60 6
7: c   10 7
8: c   30 8
9: c   60 9
> DT2
   x time
1: a   17
2: b   54
3: c    3
> DT1[DT2,roll="nearest"]
   x time v
1: a   17 1
2: b   54 6
3: c    3 7

Note that 17 appears to be closer to 10 than 30, hence the result in the first row.

If you need to roll to the next observation (next observation carried backwards) :

> DT1[DT2,roll=-Inf]
   x time v
1: a   17 2
2: b   54 6
3: c    3 7

More Related Contents:

  1. How to create a lag variable within each group?
  2. data.table objects assigned with := from within function not printed
  3. What is the purpose of setting a key in data.table?
  4. Use a value from the previous row in an R data.table calculation
  5. Convert UNIX epoch to Date object
  6. How to efficiently calculate distance between pair of coordinates using data.table :=
  7. Sample random rows within each group in a data.table
  8. Faster way to read fixed-width files
  9. Melt using patterns when variable names contain string information – avoid coercion to numeric
  10. Error: package or namespace load failed for ggplot2 and for data.table
  11. How to perform join over date ranges using data.table?
  12. Display a time clock in the R command line
  13. Split text string in a data.table columns
  14. Update subset of data.table based on join
  15. Difference between as.POSIXct/as.POSIXlt and strptime for converting character vectors to POSIXct/POSIXlt
  16. No RTools compatible with R version 3.5.0 was found
  17. knitr gets tricked by data.table `:=` assignment
  18. How to convert time (mm:ss) to decimal form in R
  19. Add multiple columns to R data.table in one function call?
  20. Subsetting data.table using variables with same name as column
  21. data.table equivalent of tidyr::complete()
  22. data.table merge based on date ranges
  23. Reason behind speed of fread in data.table package in R
  24. best way to transpose data.table
  25. data.table row-wise sum, mean, min, max like dplyr?
  26. := (pass by reference) operator in the data.table package modifies another data table object simultaneously
  27. Select NA in a data.table in R
  28. assigning by reference into loaded package datasets
  29. Convert decimal day to HH:MM
  30. calculating sum of previous 3 rows in R data.table (by grid-square)

Leave a Comment