How to join two dataframes by nearest time-date?

by

data.table should work for this (can you explain the error you’re coming up against?), although it does tend to convert POSIXlt to POSIXct on its own (perhaps do that conversion on your datetime column manually to keep data.table happy). Also make sure you’re setting the key column before using roll.

(I’ve created my own example tables here to make my life that little bit easier. If you want to use dput on yours, I’m happy to update this example with your data):

new <- data.table( date = as.POSIXct( c( "2016-03-02 12:20:00", "2016-03-07 12:20:00", "2016-04-02 12:20:00" ) ), data.new = c( "t","u","v" ) )
head( new, 2 )

                  date data.new
1: 2016-03-02 12:20:00        t
2: 2016-03-07 12:20:00        u

old <- data.table( date = as.POSIXct( c( "2016-03-02 12:20:00", "2016-03-07 12:20:00", "2016-04-02 12:20:00", "2015-03-02 12:20:00" ) ), data.old = c( "a","b","c","d" ) )
head( old, 2 )


                  date data.old
1: 2016-03-02 12:20:00        a
2: 2016-03-07 12:20:00        b

setkey( new, date )
setkey( old, date )

combined <- new[ old, roll = "nearest" ]
combined

                  date data.new data.old
1: 2015-03-02 12:20:00        t        d
2: 2016-03-02 12:20:00        t        a
3: 2016-03-07 12:20:00        u        b
4: 2016-04-02 12:20:00        v        c

I’ve intentionally made the two tables different row lengths, in order to show how the rolling join deals with multiple matches. You can switch the way it joins with:

combined <- old[ new, roll = "nearest" ]
combined

                  date data.old data.new
1: 2016-03-02 12:20:00        a        t
2: 2016-03-07 12:20:00        b        u
3: 2016-04-02 12:20:00        c        v

More Related Contents:

  1. How to prevent ifelse() from turning Date objects into numeric objects
  2. strptime, as.POSIXct and as.Date return unexpected NA
  3. How to flatten / merge overlapping time periods
  4. Split date-time column into Date and time variables
  5. efficiently generate a random sample of times and dates between two dates
  6. Change timezone in a POSIXct object
  7. How do you convert POSIX date to day of year?
  8. Split time series data into time intervals (say an hour) and then plot the count
  9. calculating time difference in R
  10. Date conversion from POSIXct to Date in R
  11. Convert seconds to days: hours:minutes:seconds
  12. Understanding dates and plotting a histogram with ggplot2 in R
  13. R – converting date and time fields to POSIXct with HHMMSS format
  14. How to determine if date is a weekend or not (not using lubridate)
  15. Rounding time to nearest quarter hour
  16. Change day of the month in a Date to first day (01)
  17. Handling dates when we switch to daylight savings time and back
  18. Checking if Date is Between two Dates in R
  19. extract hours and seconds from POSIXct for plotting purposes in R
  20. Convert hours:minutes:seconds to minutes
  21. R: merge two irregular time series
  22. Calculating Time Difference between two columns
  23. Convert Factor to Date/Time in R
  24. Converting numeric time to datetime POSIXct format in R
  25. R: convert date from character to datetime [duplicate]
  26. as.Date(as.POSIXct()) gives the wrong date?
  27. Convert decimal day to HH:MM
  28. R datetime series missing values
  29. Round a POSIX date (POSIXct) with base R functionality
  30. How to calculate time difference with previous row of a data.frame by group

Leave a Comment