超大规模R语言DataFrame中匹配最近日出日期并计算时间差的高效实现方案

Hey there! Let's tackle this large-scale matching problem head-on—dealing with 100 million rows means we need solutions that prioritize memory efficiency and speed, no messy full-table copies allowed.

First, let's break down why your original code hit that "vector memory exhausted" error: when using rDTB[sDTA, ...] without modifying in-place, you're creating a brand new data.table with 100 million rows, which doubles your memory usage instantly. Instead, we'll use in-place modifications (for data.table) or vectorized operations (for dplyr) to avoid that.

Solution 1: Optimized data.table Approach #

data.table is built for large datasets, with highly optimized C-backed operations and in-place modifications to save memory. Here's how to use it properly:

library(data.table)

# Convert data frames to data.tables (no copy if done correctly)
setDT(df1)
setDT(df2)

# Sort df2 by sunrise and set it as the key for fast lookups
setorder(df2, sunrise)
setkey(df2, sunrise)

# Match nearest sunrise directly in df1 (no new table created)
df1[, closest := df2[.SD, on = .(sunrise = date), roll = "nearest", x.sunrise]]

# Calculate time difference in hours (in-place modification)
df1[, Time_until_sunrise := difftime(closest, date, units = "hours")]

Why this works: #

• setDT() converts data frames to data.tables without copying data (unlike as.data.table() in some cases).
• setkey() and setorder() prepare df2 for binary search, which is O(log n) per lookup instead of O(n).
• The := operator modifies df1 in-place, so we never create a duplicate 100M-row table.

Solution 2: dplyr + Vectorized Operations #

Since you had success switching from aggregate() to dplyr, here's a tidyverse-friendly approach using vectorized functions (no loops, no massive copies):

library(dplyr)

# First, sort df2's sunrise values (critical for fast binary search)
df2_sorted <- df2 %>% arrange(sunrise)
sunrise_vec <- df2_sorted$sunrise

# Use findInterval to get positions, then compare nearest candidates
df1 <- df1 %>%
  mutate(
    # Find where each date fits in the sorted sunrise vector
    pos = findInterval(date, sunrise_vec),
    # Handle edge cases: date earlier than all sunrises, or later than all
    pos = case_when(
      pos == 0 ~ 1,
      pos == length(sunrise_vec) ~ length(sunrise_vec),
      TRUE ~ pos
    ),
    # Get the two closest sunrise candidates
    prev_sunrise = if_else(pos > 1, sunrise_vec[pos - 1], NA_POSIXct_),
    curr_sunrise = sunrise_vec[pos],
    # Pick the nearest one
    closest = case_when(
      is.na(prev_sunrise) ~ curr_sunrise,
      abs(difftime(date, prev_sunrise, units = "hours")) < abs(difftime(date, curr_sunrise, units = "hours")) ~ prev_sunrise,
      TRUE ~ curr_sunrise
    ),
    # Calculate time difference
    Time_until_sunrise = difftime(closest, date, units = "hours")
  ) %>%
  # Clean up helper columns
  select(-pos, -prev_sunrise, -curr_sunrise)

Why this works: #

• findInterval() is a fast, C-implemented function that does binary search under the hood.
• All operations are vectorized—we process every row at once, no slow loops.
• dplyr's mutate() processes columns sequentially, avoiding large intermediate datasets.

Key Tips for Handling 100M Rows #

• Avoid data.frame conversions: data.tables and tibbles are more memory-efficient than base data.frames for large datasets.
• Sort first: Both methods rely on sorted sunrise values for fast binary search—never skip this step.
• Monitor memory: Use pryr::object_size() to check how much memory your objects are using, and avoid creating unnecessary copies.

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