R data.table if然后使用join进行sumif查找 [英] R data.table if then sumif lookup using join
问题描述
我希望在events_table中查找个人ID
,并计算 total_duration
作为持续时间的总和日期
之前的所有事件。
I am looking to look up the individual id
in events_table and calculate the total_duration
as the sum of the duration of all events prior to date
.
持续时间是 date_start
和 date
(表1),除非事件结束(例如,具有 date_end
),如果 date_end<日期
,持续时间= date_end-date_start
。
The duration is the time between the date_start
and date
(table1), unless the event ended (i.e. has a date_end
), in which case if 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行)。
I am using data.tables as I have large tables (>1m rows).
我的数据看起来像这样:
My data looks a bit like this:
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 ="|"
)
我最开始的猜测该查询来自:
My best guess at starting the query comes from:
table1[, total_duration:= events_table[table1,
on = .(`individual id`, date>date_start),
sum(date-date_start),
by = .EACHI][["V1"]]]
但是我不知道包含if条件的语法。
But I dont know the syntax for including the if condition.
感谢您的帮助。
推荐答案
# 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 + ...]
。
You don't have to define three distinct columns, though it is easier for debugging. Instead, you could initialize table1[, v := 0]
and for each step do table1[, v := v + ...]
.
这篇关于R data.table if然后使用join进行sumif查找的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!