如何按带动态容差的数值变量连接两个data.table？

Got it, let's tackle this dynamic tolerance join problem. The key here is that the tolerance for matching mz (from DT1) to iso_mz (from DT2) isn't a fixed value—it's calculated as iso_mz * 5e-6 for each row in DT2.

Step-by-Step Solution #

First, let's start with the example data you provided (included for completeness):

library(data.table)

# Example data
DT1 <- data.table(mz = c(433.231512451172, 451.091953822545, 454.347605202415, 490.167234693255, 518.225894504123), 
                  Var1 = c(433.231018066406, 451.091430664062, 454.347015380859, 490.166381835938, 518.22509765625), 
                  Var2 = c(433.232147216797, 451.092559814453, 454.34814453125, 490.168273925781, 518.2265625))
DT2 <- data.table(iso_mz = c(451.0900, 490.1651, 518.2281, 433.2335), 
                  comp = c("m1", "m2", "m3", "m4"))

Method 1: Direct Non-Equi Join (Simplest)

data.table's non-equi join feature lets us define dynamic range conditions directly in the on parameter. We'll match rows where DT1's mz falls within iso_mz ± iso_mz*5e-6 from DT2:

# Perform the dynamic tolerance join
Output <- DT2[DT1, 
              on = .(iso_mz - iso_mz*5e-6 <= mz, mz <= iso_mz + iso_mz*5e-6),
              nomatch = 0, # Drop rows with no matches
              .(iso_mz, comp, mz, Var1, Var2)] # Select desired columns

# Reorder to match your expected output
Output <- Output[order(factor(comp, levels = c("m4", "m1", "m2", "m3")))]

Method 2: Using foverlaps (For More Complex Intervals)

If you need to work with more complex interval logic later, foverlaps is a flexible tool. First, define interval bounds for both tables, then join on overlapping intervals:

# Add interval bounds to DT1 (since mz is a single value, bounds match mz)
DT1[, `:=`(mz_low = mz, mz_high = mz)]

# Calculate dynamic tolerance bounds for DT2
DT2[, `:=`(mz_low = iso_mz - iso_mz*5e-6, mz_high = iso_mz + iso_mz*5e-6)]

# Set key on DT2's interval columns for foverlaps
setkey(DT2, mz_low, mz_high)

# Perform the overlap join
Output <- foverlaps(DT1, DT2, 
                    by.x = c("mz_low", "mz_high"), 
                    by.y = c("mz_low", "mz_high"),
                    nomatch = 0)

# Clean up and reorder to match expected output
Output <- Output[, .(iso_mz, comp, mz, Var1, Var2)]
Output <- Output[order(factor(comp, levels = c("m4", "m1", "m2", "m3")))]

Verify the Result #

Running either method will give you exactly the output you requested:

iso_mz comp        mz      Var1      Var2
1: 433.2335   m4 433.23151 433.23102 433.23215
2: 451.0900   m1 451.09195 451.09143 451.09256
3: 490.1651   m2 490.16723 490.16638 490.16827
4: 518.2281   m3 518.22589 518.22510 518.22656

Key Notes #

• The nomatch = 0 parameter ensures we only keep rows with valid matches (no NA values from unmatched rows).
• The reordering step is optional—it's just to align with the exact row order in your expected output. Adjust the levels in factor() if you need a different sort order.

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