如何按最近时间合并温度DataFrame与航班信息DataFrame？

Got it, let's tackle this problem of merging your temperature and flight data by the nearest timestamp. I'll walk you through a few solid approaches depending on your data size and preferred workflow. First, let's start with a complete example by adding a sample flight DataFrame to match your temperature data:

# Sample flight data (matching your temperature data structure)
FlightTime <- c("2000-01-01 02:00:00", "2000-01-01 07:30:00", "2000-01-01 09:15:00")
FlightTime <- as.POSIXct(FlightTime)
FlightNum <- c("AA123", "UA456", "DL789")
Flights <- data.frame(FlightTime, FlightNum)

1. Fastest Option: data.table (Recommended for Large Datasets) #

If you're working with big data (like full-year hourly temps + thousands of flights), data.table is the way to go—it's optimized for speed and memory efficiency, with built-in support for nearest-time joins.

library(data.table)

# Convert data frames to data.tables
setDT(Temperature)
setDT(Flights)

# Set time columns as keys for fast matching
setkey(Temperature, Time)
setkey(Flights, FlightTime)

# Perform nearest-time join with `roll = "nearest"`
merged_data <- Temperature[Flights, roll = "nearest"]

# View the result
merged_data

Key Notes: #

• This directly maps each flight timestamp to the closest temperature timestamp without generating all possible combinations (huge win for performance).
• If a flight timestamp falls outside the range of your temperature data, it will automatically match the nearest boundary timestamp. To disable this, add rollends = FALSE.

2. Tidyverse Approach: dplyr + lubridate (Great for Small Data) #

If you prefer the tidyverse syntax and are working with a smaller dataset, this method is readable and intuitive—though it's not ideal for large data (since it creates all possible timestamp pairs first).

library(dplyr)
library(lubridate)

merged_data <- Flights %>%
  # Create all combinations of flights and temperature records
  cross_join(Temperature) %>%
  # Calculate absolute time difference between flight and temp timestamps
  mutate(time_diff = abs(FlightTime - Time)) %>%
  # Group by each flight, then keep only the row with the smallest time difference
  group_by(FlightNum, FlightTime) %>%
  filter(time_diff == min(time_diff)) %>%
  ungroup() %>%
  # Remove the time difference column (optional)
  select(-time_diff)

# View the result
merged_data

Key Notes: #

• If multiple temperature timestamps are equally close to a flight time, this will keep all matches. Add slice_head(n = 1) after filter() to pick just one.
• Avoid this for large datasets—cross_join can explode your memory usage.

3. Base R Option (No External Packages) #

If you don't want to install any packages, this base R method works for small to medium datasets. It uses sapply to find the nearest timestamp for each flight.

# For each flight timestamp, find the index of the closest temperature timestamp
match_indices <- sapply(Flights$FlightTime, function(flight_time) {
  which.min(abs(flight_time - Temperature$Time))
})

# Combine flight data with the matching temperature records
merged_data <- cbind(Flights, Temperature[match_indices, ])

# View the result
merged_data

Key Notes: #

• Simple and self-contained, but slower than data.table for large datasets since it loops through each flight individually.

General Tips: #

• Always ensure both time columns are of type POSIXct (you already did this, which is perfect!).
• If you have duplicate timestamps in either dataset, decide whether you want to average values, pick the first match, or handle duplicates before joining.

内容的提问来源于stack exchange，提问作者Ross Headington