如何实现data.table多条件至少满足其一的高效数据集匹配？

Absolutely, you can pull this off efficiently using data.table's optimized join operations—no slow loops needed, which is perfect for your large dataset (10k rows in dt1, 300k in dt2). Let's walk through how to implement this for your scenario, then extend it to your 4 actual conditions.

First, Recreate Your Sample Data #

Let's start with the data you provided to make the example concrete:

library(data.table)

dt1 <- data.table(
  c1 = c(rep('a', 2), rep('b', 2), rep('c', 2)),
  c2 = c('x','y','x','y','x','z'),
  c3.min = c(rep(0,3), rep(-1,3)),
  c3.max = c(rep(10,3), rep(11,3)),
  x = 1:6
)

dt2 <- data.table(
  c1 = c(rep('a', 3), rep('b', 3), rep('c', 4)),
  c2 = c(rep(c('x','y'), 5)),
  c3 = c(-1, 2, 0, 10, 11, -1, 3, 6, 3, 12),
  y = 1:10
)

Core Approach: Join on Each Condition Separately, Then Combine & Deduplicate #

The key idea is to perform a join for each of your matching conditions, then combine all the results and remove duplicate matches (since a single pair of rows from dt1/dt2 might satisfy multiple conditions).

For your 3 example conditions:

1. dt1$c1 == dt2$c1
2. dt1$c2 == dt2$c2
3. dt2$c3 falls between dt1$c3.min and dt1$c3.max

Here's how to implement each join:

# Match on condition 1: c1 equality
match_c1 <- dt1[dt2, on = .(c1), allow.cartesian = TRUE][, match_type := "c1_match"]

# Match on condition 2: c2 equality
match_c2 <- dt1[dt2, on = .(c2), allow.cartesian = TRUE][, match_type := "c2_match"]

# Match on condition 3: c3 range check
match_c3 <- dt1[dt2, on = .(c3.min <= c3, c3.max >= c3), allow.cartesian = TRUE][, match_type := "c3_range_match"]

Now combine all matches and remove duplicates (we'll keep only unique pairs of x (dt1's identifier) and y (dt2's identifier)):

# Combine all matching results
all_matches <- rbind(match_c1, match_c2, match_c3, fill = TRUE)

# Deduplicate to avoid duplicate pairs (same dt1 + dt2 row matched via multiple conditions)
unique_matches <- unique(all_matches, by = c("x", "y"))

Why This Works (and Is Efficient) #

• data.table's joins are optimized at the C level, so they're way faster than any loop-based approach for large datasets.
• allow.cartesian = TRUE lets us handle cases where one row in dt1 matches multiple rows in dt2 (which is expected here).
• Deduplication ensures we don't have redundant entries for pairs that matched via more than one condition.

Extending to 4 Conditions #

If you have a 4th condition, just add another join step and include it in the rbind call. For example, if your 4th condition is dt1$some_col == dt2$another_col:

match_c4 <- dt1[dt2, on = .(some_col = another_col), allow.cartesian = TRUE][, match_type := "c4_match"]
all_matches <- rbind(match_c1, match_c2, match_c3, match_c4, fill = TRUE)

Verifying the Results #

For your original problem cases:

• x=3 (dt1 row with c1='b', c2='x') will now match dt2 rows where c1='b', c2='x', or c3 is between 0-10.
• x=6 (dt1 row with c1='c', c2='z') will match dt2 rows where c1='c' or c3 is between -1-11 (since dt2 has no rows with c2='z').

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