[R data.table如果再SUMIF查找使用连接(R data.table if then s

我期待中查找individual id在events_table并计算total_duration之前所有事件的持续时间的总和date 。

的持续时间是之间的时间date_start和date （表1），除非该事件结束（即，具有date_end ），在这种情况下，如果date_end < date ， duration = date_end - date_start 。

在伪代码：

IF (date>date_start) Then{
   IF(date_end < date & date_end != NA) Then{
       duration = date_end-date_start
   } else if (date_start < date) {
       duration = date - date_start
   }
}
Then sum all the durations separately for each "individual_id" and "date" combo

我使用data.tables，因为我有大表（> 1M行）。

我的数据看起来有点像这样：

 table1 <- fread(
      "individual id | date       
       1             |  2019-01-02
       1             |  2019-01-03
       2             |  2019-01-02
       2             |  2019-01-03", 
      sep ="|"
    )
    events_table<- fread(
      "individual id | date_start  | date_end
       1             |  2018-01-02 |   NA     
       1             |  2018-01-04 | 2018-07-01     
       1             |  2018-01-05 |   NA       
       2             |  2018-01-01 |   NA         
       2             |  2018-01-02 |   NA           
       2             |  2018-01-05 | 2018-11-21",
      sep = "|"
    )

输出应该如下：

 table1 <- fread(
          "individual id | date         | total_duration
           1             |  2019-01-02  |    905
           1             |  2019-01-03  |    907
           2             |  2019-01-02  |    1051
           2             |  2019-01-03  |    1053", 
          sep ="|"
        )

在开始查询我最好的猜测来自于：

table1[, total_duration:= events_table[table1, 
                              on = .(`individual id`, date>date_start), 
                              sum(date-date_start),
                              by = .EACHI][["V1"]]]

但我不知道，包括如果条件的语法。

谢谢你的帮助。

Answer 1:

# formatting
table1[, date := as.IDate(date)]
events_table[, `:=`(date_start = as.IDate(date_start), date_end = as.IDate(date_end))]

# list max dur
events_table[, dur := date_end - date_start]

# add up completed events
table1[, v1 := 
  events_table[.SD, on=.(`individual id`, date_end <= date), sum(x.dur, na.rm = TRUE), by=.EACHI]$V1
]

# add on incomplete events
table1[, v2 := 
  events_table[!is.na(date_end)][.SD, on=.(`individual id`, date_start <= date, date_end > date), sum(i.date - x.date_start, na.rm = TRUE), by=.EACHI]$V1
]

# add on ill-defined events
table1[, v3 := 
  events_table[is.na(date_end)][.SD, on=.(`individual id`, date_start <= date), sum(i.date - x.date_start, na.rm = TRUE), by=.EACHI]$V1
]

table1[, v := v1 + v2 + v3]

   individual id       date total_duration  v1 v2  v3    v
1:             1 2019-01-02            905 178  0 727  905
2:             1 2019-01-03            907 178  0 729  907
3:             2 2019-01-02           1051 320  0 731 1051
4:             2 2019-01-03           1053 320  0 733 1053

你不必定义三个不同的列，但它对于调试更容易。 相反，你可以初始化table1[, v := 0]和每一步的做table1[, v := v + ...]

文章来源: R data.table if then sumif lookup using join