在R中合并表格，更新日期重叠且NewData优先级更高的行

Hey there! No worries at all about your first question—we’ve all fumbled through our first data merging ask 😊 Nested loops can get clunky and slow, especially with bigger datasets, so let’s walk through two way cleaner, more efficient approaches in R: one using tidyverse tools, and another with SQL (since you mentioned that!).

First, let’s make sure we’re aligned on the rules:

• Match rows by ID
• Check if date ranges between OldData and NewData overlap (i.e., OldData's start is before NewData's end, and OldData's end is after NewData's start)
• Only update when NewData’s Priority is higher than OldData’s
• Split the original OldData ranges if needed, and insert the higher-priority NewData row in the gap

Sample Data Setup #

First, let’s get our sample data into R so you can test these code snippets:

library(tibble)

OldData <- tibble(
  ID = c(1,1,2,2),
  DateFrom = as.Date(c("2018-11-01", "2018-12-01", "2017-06-01", "2018-03-01")),
  DateTo = as.Date(c("2018-12-01", "2019-02-01", "2018-03-01", "2018-04-05")),
  Priority = c(5,5,5,5)
)

NewData <- tibble(
  ID = c(1,2),
  DateFrom = as.Date(c("2018-11-13", "2018-03-21")),
  DateTo = as.Date(c("2018-12-01", "2018-05-01")),
  Priority = c(6,6)
)

Method 1: Tidyverse (dplyr + fuzzyjoin) #

This approach uses vectorized operations instead of loops, which is way faster for large datasets. We’ll first find overlapping rows, then split the old ranges and insert the new high-priority row:

library(dplyr)
library(fuzzyjoin)

# Step 1: Identify overlapping rows where NewData has higher priority
overlapping_matches <- fuzzy_inner_join(
  OldData,
  NewData,
  by = c("ID" = "ID", "DateFrom" = "DateTo", "DateTo" = "DateFrom"),
  match_fun = list(`==`, `<`, `>`)  # Match same ID, and date ranges overlap
) %>%
  filter(Priority.y > Priority.x) %>%
  select(
    ID = ID.x,
    OldFrom = DateFrom.x, OldTo = DateTo.x,
    NewFrom = DateFrom.y, NewTo = DateTo.y,
    NewPriority = Priority.y
  )

# Step 2: Split old rows and assemble updated data
updated_rows <- bind_rows(
  # Keep the part of the old row before the new high-priority range (if it exists)
  overlapping_matches %>%
    filter(OldFrom < NewFrom) %>%
    mutate(DateFrom = OldFrom, DateTo = NewFrom, Priority = 5) %>%
    select(ID, DateFrom, DateTo, Priority),
  
  # Insert the new high-priority row
  overlapping_matches %>%
    mutate(DateFrom = NewFrom, DateTo = NewTo, Priority = NewPriority) %>%
    select(ID, DateFrom, DateTo, Priority),
  
  # Keep the part of the old row after the new high-priority range (if it exists)
  overlapping_matches %>%
    filter(OldTo > NewTo) %>%
    mutate(DateFrom = NewTo, DateTo = OldTo, Priority = 5) %>%
    select(ID, DateFrom, DateTo, Priority)
)

# Step 3: Combine with non-overlapping original OldData rows
final_data <- bind_rows(
  OldData %>% anti_join(overlapping_matches, by = c("ID" = "ID", "DateFrom" = "OldFrom", "DateTo" = "OldTo")),
  updated_rows
) %>%
  arrange(ID, DateFrom)

# View the result
final_data

Method 2: SQL with sqldf #

If you prefer SQL-style logic, the sqldf package lets you run SQL queries directly on R data frames. This follows the same logic as the tidyverse approach but uses set-based SQL operations:

library(sqldf)

# Convert dates to character for SQL compatibility (sqldf handles this well)
OldData$DateFrom <- as.character(OldData$DateFrom)
OldData$DateTo <- as.character(OldData$DateTo)
NewData$DateFrom <- as.character(NewData$DateFrom)
NewData$DateTo <- as.character(NewData$DateTo)

# SQL query to handle merging and splitting
final_query <- "
WITH overlap_cte AS (
  SELECT 
    o.ID, 
    o.DateFrom AS OldFrom, 
    o.DateTo AS OldTo, 
    n.DateFrom AS NewFrom, 
    n.DateTo AS NewTo, 
    n.Priority AS NewPriority
  FROM OldData o
  JOIN NewData n 
    ON o.ID = n.ID 
    AND o.DateFrom < n.DateTo 
    AND o.DateTo > n.DateFrom
  WHERE n.Priority > o.Priority
)
-- Keep non-overlapping original rows
SELECT ID, DateFrom, DateTo, Priority FROM OldData
LEFT JOIN overlap_cte oc 
  ON OldData.ID = oc.ID 
  AND OldData.DateFrom = oc.OldFrom 
  AND OldData.DateTo = oc.OldTo
WHERE oc.ID IS NULL

UNION ALL

-- Add the pre-overlap segment of old rows
SELECT ID, OldFrom AS DateFrom, NewFrom AS DateTo, 5 AS Priority FROM overlap_cte
WHERE OldFrom < NewFrom

UNION ALL

-- Add the new high-priority rows
SELECT ID, NewFrom AS DateFrom, NewTo AS DateTo, NewPriority AS Priority FROM overlap_cte

UNION ALL

-- Add the post-overlap segment of old rows
SELECT ID, NewTo AS DateFrom, OldTo AS DateTo, 5 AS Priority FROM overlap_cte
WHERE OldTo > NewTo

ORDER BY ID, DateFrom
"

# Run the query and convert dates back to Date type
final_data_sql <- sqldf(final_query)
final_data_sql$DateFrom <- as.Date(final_data_sql$DateFrom)
final_data_sql$DateTo <- as.Date(final_data_sql$DateTo)

# View the result
final_data_sql

Why These Are Better Than Nested Loops #

• Speed: Both methods use vectorized/set-based operations, which are orders of magnitude faster than row-by-row loops for large datasets.
• Maintainability: The logic is broken into clear, modular steps, so it’s easier to adjust if your rules change (e.g., handling edge cases like full date range overlaps).
• Readability: Anyone familiar with tidyverse or SQL can quickly follow what’s happening, unlike nested loops which can be hard to parse.

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