r语言 - 如何计算与给定数据表与开始和结束坐标匹配的序列分数

Question

给定两个数据表，其中包含整数序列的开始和结束坐标：

df1 <- data.table(CAT = c(rep("A", 3), rep("B", 3), rep("C", 3)),
              START = c(1, 11, 21, 1, 21, 41, 1, 11, 21),
              END = c(10, 20, 30, 20, 40, 60, 10, 20, 30)
)
df2 <- data.table(CAT = c(rep("A", 3), rep("B", 3), rep("C", 3)),
              START = c(1, 11, 21, 31, 41, 51, 1, 11, 21),
              END = c(5, 17, 23, 38, 48, 54, 9, 17, 26)
)

如何计算每个序列中df1中任何序列的开始和结束坐标内的整数数，这些整数在df2具有匹配CAT？我目前正在使用 for 循环：

seq2 <- Vectorize(seq.default, vectorize.args = c("from", "to"))
df1$MATCH <- NA
for (i in 1:nrow(df1)){
  df2_sub <- subset(df2, df2$CAT == df1$CAT[i])
  df2_int <- unlist(seq2(from = df2_sub$START, to = df2_sub$END))
  df1_int <- seq(df1$START[i], df1$END[i])
  df1$MATCH[i] <- length(na.omit(match(df1_int, df2_int)))
}

返回

df1

   CAT START END MATCH
1:   A     1  10     5
2:   A    11  20     7
3:   A    21  30     3
4:   B     1  20     0
5:   B    21  40     8
6:   B    41  60    12
7:   C     1  10     9
8:   C    11  20     7
9:   C    21  30     6

但是，我将其应用于的数据表和向量非常大？有没有人能够提出提高性能的方法？也许使用data.table？

Answer 1

无需创建之后折叠的序列。

正如Cole所提到的，foverlaps()函数可用于识别重叠序列。通过一些简单的算术，可以计算出重叠的长度，这些长度被聚合为每行df1，最后：

library(data.table)
foverlaps(df1[, rn := .I], setkey(df2, CAT, START, END))[
  , ovl := (pmin(END, i.END) - pmax(START, i.START) + 1)][
    , .(MATCH = sum(ovl)), by = .(rn)][
      is.na(MATCH), MATCH := 0][]

   rn MATCH
1:  1     5
2:  2     7
3:  3     3
4:  4     0
5:  5     8
6:  6    12
7:  7     9
8:  8     7
9:  9     6

data.table的开发版本1.12.3带有一个新的nafill()功能：

library(data.table) # version 1.12.3
foverlaps(df1[, rn := .I], setkey(df2, CAT, START, END))[
  , ovl := (pmin(END, i.END) - pmax(START, i.START) + 1)][
    , .(MATCH = sum(ovl)), by = .(rn)][
      , MATCH := nafill(MATCH, fill = 0)][]

Answer 2

您需要知道的第一件事是，您将 df1 中的所有行都用于您的函数 seq2，该函数用于性能非常糟糕。

这是我提出的解决方案，我还没有用非常大的数据集来测试它的性能。

seq2 <- Vectorize(seq.default, vectorize.args = c("from", "to"))
df2_sub = df2[, sequence = unlist( seq2(from = START, to = END) ), by = CAT]
f = function(cat, start, end){
  df2_sub[CAT == cat, length( intersect(seq(start, end), sequence) )]
}
df1[, MATCH := f(CAT, START, END), by = 1:nrow(df1)]

如果两个表 df1 和 df2 是 data.table，则它有效。