R: Combine two dataframes by the nearest time

Question

I have two dataframes; one that contains a year's worth of hourly temperatures and the other contains flight information. Bellow shows an extract from the temperature dataframe:

  Time <- c("2000-01-01 00:53:00","2000-01-01 06:53:00","2000-01-01 10:53:00")
  Time <- as.POSIXct(Time)
  Temp <- c(20,30,10)
  Temperature <- data.frame(Time,Temp)
  Temperature
                 Time Temp
1 2000-01-01 00:53:00   20
2 2000-01-01 06:53:00   30
3 2000-01-01 10:53:00   10

Bellow shows an extract from the flight information dataframe:

  DepartureTime <- c("2000-01-01 03:01:00","2000-01-01 10:00:00","2000-01-01 14:00:00")
  DepartureTime <- as.POSIXct(DepartureTime)
  FlightInformation <- data.frame(DepartureTime)
  FlightInformation
        DepartureTime
1 2000-01-01 03:01:00
2 2000-01-01 10:14:00
3 2000-01-01 14:55:00

My goal is to take each row of FlightInformation$DepartureTime and find the closest time in the whole column Temperature$Time. I then want to add the corresponding temperature to the FlightInformation dataframe. The desired output should look like this:

FlightInformation
        DepartureTime Temp
1 2000-01-01 03:01:00 20
2 2000-01-01 10:14:00 10
3 2000-01-01 14:55:00 10

My attempts so far have come up with this:

  i <- 1
  j <- 1
  while(i <= nrow(Temperature)){
    while(j <= nrow(FlightInformation)){
      if(Temperature$Time[i] == FlightInformation$Time[j]){
        FlightInformation$Temp[j] == Temperature$Temp[i]
      }
      j <- j + 1
    }
    i <- i + 1
  }

This involves first rounding all times to the nearest hour. This method is not as accurate as i would like it to be and seems VERY inefficient! Is there an easy way to find the nearest posix to give my desired output?

Answer 1

Here's a way! Time is easiest to work with for this if you convert it to a numeric value. Then you can compare the numeric values to find the closest times before/after your reference time (FlightInformation$time_num in the below example). Once you have the closest time before and after your reference value, figure out which is really the closest to your reference. Use that time value to look up (index) the correct temperature value and add it to your data frame.

#convert time to numeric (seconds since origin of time)
Temperature$time_num <- as.numeric(Temperature$Time) 
FlightInformation$time_num <- as.numeric(FlightInformation$DepartureTime)

#make sure time data is in correct order so that indexes for time are in correct order 
Temperature <- Temperature[with(Temperature, order(time_num)), ] #sort data

for (i in 1:nrow(FlightInformation)) #for each row of data in flight...
{
  #find the time in Temp that is closest + prior to Flight time
  #create a logical vector saying which Temperature$time_num are <= to FlightInformation$time_num. 
  #pull the max row index from the logical vector where value == TRUE (this is the closest time for Temp that is prior to Flight Time)
  #use that row index to look up the Temperature$time_num value that is closest + prior to Flight time
  #will return NA/warning message if no time in Temp is before time in Flight
  temptime_prior <- Temperature[max(which(Temperature$time_num <= FlightInformation$time_num[i])), "time_num"] 

  #find the time in Temp that is closest + after to Flight time
  #will return NA/warning message if no time in Temp is after time in Flight
  temptime_after <- Temperature[min(which(Temperature$time_num > FlightInformation$time_num[i])), "time_num"] 

  #compare times before and after to see which is closest to flight time. If no before/after time was found (e.g., NA was returned), always use the other time value
  temptime_closest <- ifelse(is.na(temptime_prior), temptime_after, 
                             ifelse(is.na(temptime_after), temptime_prior, 
                                    ifelse((FlightInformation$time_num[i] - temptime_prior) <= (temptime_after - FlightInformation$time_num[i]),
                                           temptime_prior, temptime_after)))

  #look up the right temp by finding the row index of right Temp$time_num value and add it to Flight info
  FlightInformation$Temp[i] <- Temperature[which(Temperature$time_num == temptime_closest), "Temp"]
}

#get rid of numeric time column, you don't need it anymore
FlightInformation <- FlightInformation[,!(names(FlightInformation) %in% c("time_num"))]

Output

        DepartureTime Temp
1 2000-01-01 03:01:00   20
2 2000-01-01 10:00:00   10
3 2000-01-01 14:00:00   10

If you have subsets of data in each data frame you need to match up to (e.g., match df1$group1 time values only to df2$group1 time values), you can use survival::neardate. It's a nice function for this that does basically what the above code does, but has some additional parameters if you need them.

Hope this helps! The codes a lot shorter without all the comments =)

Answer 2

Some assumptions:

• you have temperature data before and after all flight information; otherwise you'll see NA
• temperature data is continuous-enough, meaning with the interpolation this presents, you don't grab something from 3 months prior (not useful)
• temperature data is ordered (easy enough to fix if not)

We'll use cut, that finds the interval in which values fit within a series of breaks:

(ind <- cut(FlightInformation$DepartureTime, Temperature$Time, labels = FALSE))
# [1]  1  2 NA

These indicate rows within Temperature from which we should retrieve the $Temp. Unfortunately, it is absolute and does not allow for being closer to the next value, so we can compensate for that:

(ind <- ind + (abs(Temperature$Time[ind] - FlightInformation$DepartureTime) >
                 abs(Temperature$Time[1+ind] - FlightInformation$DepartureTime)))
# [1]  1  3 NA

Okay, now that NA: that indicates that the latest $DepartureTime is outside of the known times. This indicates a violation of my first assumption above, but it can be fixed. I use a magic-constant of "6 hours" here to determine that the data is close enough to be able to use it; there are certainly many other heuristics which will be less-wrong. For those, we can just assume the latest temperature:

(is_recoverable <- is.na(ind) & abs(FlightInformation$DepartureTime - max(Temperature$Time)) < 60*60*6)
# [1] FALSE FALSE  TRUE
ind[is_recoverable] <- nrow(Temperature)
ind
# [1] 1 3 3

The the results:

FlightInformation$Temp <- Temperature$Temp[ ind ]
FlightInformation
#         DepartureTime Temp
# 1 2000-01-01 03:01:00   20
# 2 2000-01-01 10:00:00   10
# 3 2000-01-01 14:00:00   10

Though definitely quicker than double while loops, it will be a problem if you have large gaps in your temperature data. That is, if you have a 3-year gap in your data, the most-recent temperature will be used, which might be 2.99 years ago. For a double-check, use this:

FlightInformation$TempTime <- Temperature$Time[ ind ]
FlightInformation$TimeDelta <- with(FlightInformation, abs(TempTime - DepartureTime))
FlightInformation
#         DepartureTime Temp            TempTime TimeDelta
# 1 2000-01-01 03:01:00   20 2000-01-01 00:53:00  128 mins
# 2 2000-01-01 10:00:00   10 2000-01-01 10:53:00   53 mins
# 3 2000-01-01 14:00:00   10 2000-01-01 10:53:00  187 mins

You can use different units for the time delta and check for problems with:

units(FlightInformation$TimeDelta) <- "secs"
which(FlightInformation$TimeDelta > 60*60*6)
# integer(0)

(where integer(0) says you have none that are outside of my magic window of 6 hours.)