双键数据表连接请求：支持数值键精确/近似匹配（不限data.table）

Got it, let's break down how to solve this problem—joining two tables with a string key (exact match, obviously) and a numeric key that supports both exact and closest-value matches. I'll walk you through two practical approaches using popular R tools:

Approach 1: Using data.table for Fast, Flexible Matching #

data.table shines with large datasets and gives you fine-grained control over matching logic. Let's start with sample data to demonstrate:

library(data.table)

# Sample tables
dt1 <- data.table(
  str_key = c("A", "A", "B", "C"),
  num_key = c(10, 25, 15, 30),
  value1 = c("X", "Y", "Z", "W")
)

dt2 <- data.table(
  str_key = c("A", "B", "B", "C"),
  num_key = c(12, 14, 28, 31),
  value2 = c("P", "Q", "R", "S")
)

Exact Match (Both Keys) #

For exact matches on both the string and numeric keys, it's straightforward—just specify both keys in the on parameter:

# Exact join on str_key and num_key
exact_join <- dt1[dt2, on = .(str_key, num_key), nomatch = 0]
print(exact_join)

Closest-Value Match (Numeric Key) + Exact String Match #

To match the closest numeric value within the same string key group, we'll cross join within each string group, calculate the difference, then pick the row with the smallest difference:

# Join within str_key groups, calculate differences, then keep closest match
closest_join <- dt2[dt1, on = .(str_key), 
                    .(str_key, num_key_i = num_key, value1, 
                      num_key_j = i.num_key, value2, 
                      diff = abs(num_key - i.num_key)),
                    allow.cartesian = TRUE][, .SD[which.min(diff)], by = .(str_key, num_key_i)]

# Clean up the result
closest_join <- closest_join[, .(str_key, num_key = num_key_i, value1, matched_num_key = num_key_j, value2)]
print(closest_join)

Approach 2: Using dplyr + fuzzyjoin for Readable Fuzzy Matching #

If you prefer a more readable, pipe-based workflow, fuzzyjoin pairs perfectly with dplyr for this task.

library(dplyr)
library(fuzzyjoin)

# Convert to data frames (works with tibbles too)
df1 <- as.data.frame(dt1)
df2 <- as.data.frame(dt2)

Exact Match #

Same as with data.table—use inner_join with both keys:

# Exact join
exact_join_df <- inner_join(df1, df2, by = c("str_key", "num_key"))
print(exact_join_df)

Closest-Value Match #

Use difference_join to handle the numeric key's closest match, while enforcing exact matches on the string key:

# Closest-value join, grouped by str_key
closest_join_df <- difference_join(
  df1, df2,
  by = c("str_key", "num_key"),
  match_fun = list(`==`, function(x, y) abs(x - y)),  # Exact str match, numeric difference
  max_dist = Inf,  # No upper limit on difference—we'll filter for the smallest later
  mode = "inner"
) %>%
  group_by(str_key.x, num_key.x) %>%
  filter(abs(num_key.x - num_key.y) == min(abs(num_key.x - num_key.y))) %>%
  ungroup() %>%
  rename(str_key = str_key.x, num_key = num_key.x, matched_num_key = num_key.y) %>%
  select(str_key, num_key, value1, matched_num_key, value2)

print(closest_join_df)

Quick Notes #

• If multiple numeric values have the same smallest difference (e.g., num_key 15 matching 14 and 16), both will be kept. Add slice_head(n = 1) after filtering to pick just one.
• For large datasets, the data.table approach is significantly faster due to its optimized grouping and sorting.
• You can wrap these logics into a single function with a parameter (like match_type = c("exact", "closest")) to toggle between the two matching modes easily.

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