r - 为每一行查找最接近的匹配项并根据条件求和

Question

考虑以下事件数据表:

library(data.table)
breaks <- data.table(id = 1:8,
                     Channel = c("NP1", "NP1", "NP2", "NP2", "NP3", "NP3", "AT4", "AT4"),
                     Time = c(1000, 1100, 975, 1075, 1010, 1080, 1000, 1050),
                     Day = c(1, 1, 1, 1, 1, 1, 1, 1),
                     ZA = c(15, 12, 4, 2, 1, 2, 23, 18),
                     stringsAsFactors = F)

breaks
   id Channel Time Day ZA
1:  1     NP1 1000   1 15
2:  2     NP1 1100   1 12
3:  3     NP2  975   1  4
4:  4     NP2 1075   1  2
5:  5     NP3 1010   1  1
6:  6     NP3 1080   1  2
7:  7     AT4 1000   1 23
8:  8     AT4 1050   1 18

对于休息中的每个独特事件，我想使用 Time 在所有其他 channel 中找到最近的事件。变量 where Day == Day然后将这些事件的 ZA 值相加。

这是我想要达到的结果:

   id Channel Time Day ZA Sum
1:  1     NP1 1000   1 15  28
2:  2     NP1 1100   1 12  22
3:  3     NP2  975   1  4  39
4:  4     NP2 1075   1  2  32
5:  5     NP3 1010   1  1  42
6:  6     NP3 1080   1  2  32
7:  7     AT4 1000   1 23  20
8:  8     AT4 1050   1 18  19

所以第一行的 channel 是 NP1。所有其他 channel 中的关闭事件到 Time = 1000是第 3、5 和 7 行。 4+1+23 = 28
我使用带有以下代码的 data.table 使其工作:

breaks[breaks[, c("Day", "Time", "Channel", "ZA")], on = "Day", allow.cartesian = TRUE][
  Channel != i.Channel][
    order(id)][
      , delta := abs(Time - i.Time)][
        , .SD[delta == min(delta)], by = .(Channel, Time, Day, i.Channel)][
          , unique(.SD, by = c("id", "i.Channel"))][
            , .(Sum = sum(i.ZA)), by = .(id, Channel, Time, Day, ZA)]

但是，这会在第一步中创建一个包含 64 行的数据集，我想使用超过一百万行的数据集来执行此操作。

谁能帮我找到一种更有效的方法来做到这一点？

编辑:

我在 39 个不同 channel 的 140 万行的完整数据集上尝试了 G. Grothendieck (sqldf)、eddi (data.table) 和 MarkusN (dplyr) 的解决方案。数据集在内存中。

sqldf:      54 minutes
data.table: 11 hours
dplyr:      29 hours

Answer 1

在内部选择中，将每一行在同一天和不同 channel 的中断中自动连接到那些行，然后在所有连接到特定原始行的行中只保留具有最小绝对时间差的连接行。在外部选择和来自 id 内的另一个 Channel 的 ZA 给出结果。

请注意，我们在这里假设默认的 SQLite 后端为 sqldf，并且使用了特定于该数据库的功能，即 if min在选择中使用，则该选择中指定的其他值也将从最小化行填充。

默认情况下，它将使用内存数据库，如果它适合，则最好，但如果您指定 dbname = tempfile()作为 sqldf 的参数它将使用文件作为内存不足的数据库。也可以添加一个或多个索引，这可能会或可能不会加快速度。有关更多示例，请参阅 sqldf github 主页。

library(sqldf)

sqldf("select id, Channel, Time, Day, ZA, sum(bZA) Sum
 from (
   select a.*, b.ZA bZA, min(abs(a.Time - b.Time))
   from breaks a join breaks b on a.Day = b.Day and a.Channel != b.Channel
   group by a.id, b.Channel)
 group by id")

给予:

  id Channel Time Day ZA Sum
1  1     NP1 1000   1 15  28
2  2     NP1 1100   1 12  22
3  3     NP2  975   1  4  39
4  4     NP2 1075   1  2  32
5  5     NP3 1010   1  1  42
6  6     NP3 1080   1  2  32
7  7     AT4 1000   1 23  20
8  8     AT4 1050   1 18  19

这比关于这种规模问题的问题中的 data.table 代码略快，但对于更大的问题，必须重新进行比较。

此外，由于不必实现中间结果(取决于查询优化器)和处理内存不足的可能性(如果需要)，它可能能够处理更大的大小。

library(data.table)
library(dplyr)
library(sqldf)
library(rbenchmark)

benchmark(sqldf = 
sqldf("select id, Channel, Time, Day, ZA, sum(bZA) Sum
 from (
   select a.*, b.ZA bZA, min(abs(a.Time - b.Time))
   from breaks a join breaks b on a.Day = b.Day and a.Channel != b.Channel
   group by a.id, b.Channel)
 group by id"),

data.table = breaks[breaks[, c("Day", "Time", "Channel", "ZA")], on = "Day",
     allow.cartesian = TRUE][
  Channel != i.Channel][
    order(id)][
      , delta := abs(Time - i.Time)][
        , .SD[delta == min(delta)], by = .(Channel, Time, Day, i.Channel)][
          , unique(.SD, by = c("id", "i.Channel"))][
            , .(Sum = sum(i.ZA)), by = .(id, Channel, Time, Day, ZA)],

dplyr = { breaks %>% 
  inner_join(breaks, by=c("Day"), suffix=c("",".y")) %>%
  filter(Channel != Channel.y) %>%
  group_by(id, Channel, Time, Day, ZA, Channel.y) %>%
  arrange(abs(Time - Time.y)) %>%
  filter(row_number()==1) %>%
  group_by(id, Channel, Time, Day, ZA) %>%
  summarise(Sum=sum(ZA.y)) %>%                           
  ungroup() %>% 
  select(id:Sum) },

order = "elapsed")[1:4]

给予:

        test replications elapsed relative
1      sqldf          100    3.38    1.000
2 data.table          100    4.05    1.198
3      dplyr          100    9.23    2.731