Скользящие суммы по диапазону дат в таблице данных по группам в R

У меня есть таблица данных с событиями и под-событиями с течением времени, и я заинтересован в создании двух столбцов: (1) совокупная скользящая сумма того, произошло ли событие в течение 5 лет с даты события и (2) подсчет количества дополнительных событий (включая события), которые произошли в течение 5 лет с даты события. Ниже приведен пример кода:

dt = data.table(id=c(rep(52749, 14), rep(46760, 15)),
                date=c("2007-01-30","2007-03-15","2007-11-27",
                       "2007-11-29","2008-10-09","2009-04-02",
                       "2011-01-06","2011-07-26","2012-01-25",
                       "2015-01-12","2016-09-13","2017-03-21",
                       "2017-08-29","2017-10-10","2008-01-01",
                       "2010-07-19","2011-01-14","2011-08-02",
                       "2011-08-02","2012-02-01","2012-02-01",
                       "2015-04-28","2015-10-19","2016-05-16",
                       "2016-12-22","2016-12-23","2017-05-16",
                       "2017-11-15","2018-02-22"),
                idx=c(seq_len(14), seq_len(15)),
                count=c(rep(14,14),rep(15,15)),
                event=c(1, 0, 1, 0, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 1, 
                        1, 0, 1, 0, 1, 0, 1, 0, 1, 1, 0, 0, 1, 0))

В результате получается следующее:

id    date         idx  count    event  
52749 2007-01-30   1    14       1      
52749 2007-03-15   2    14       0      
52749 2007-11-27   3    14       1      
52749 2007-11-29   4    14       0      
52749 2008-10-09   5    14       1      
52749 2009-04-02   6    14       0      
52749 2011-01-06   7    14       1      
52749 2011-07-26   8    14       1      
52749 2012-01-25   9    14       0      
52749 2015-01-12  10    14       1      
52749 2016-09-13  11    14       1      
52749 2017-03-21  12    14       1      
52749 2017-08-29  13    14       0      
52749 2017-10-10  14    14       0  
46760 2008-01-01   1    15       1
46760 2010-07-19   2    15       1      
46760 2011-01-14   3    15       0      
46760 2011-08-02   4    15       1      
46760 2011-08-02   5    15       0      
46760 2012-02-01   6    15       1      
46760 2012-02-01   7    15       0      
46760 2015-04-28   8    15       1      
46760 2015-10-19   9    15       0      
46760 2016-05-16  10    15       1      
46760 2016-12-22  11    15       1      
46760 2016-12-23  12    15       0      
46760 2017-05-16  13    15       0      
46760 2017-11-15  14    15       1      
46760 2018-02-22  15    15       0

По сути, мне нужно следующее:

id    date         idx  count    event  num_event_5yr_fu    num_subevents
52749 2007-01-30   1    14       1      4                   8
52749 2007-03-15   2    14       0      NA                  NA
52749 2007-11-27   3    14       1      3                   6
52749 2007-11-29   4    14       0      NA                  NA
52749 2008-10-09   5    14       1      2                   4
52749 2009-04-02   6    14       0      NA                  NA
52749 2011-01-06   7    14       1      2                   3
52749 2011-07-26   8    14       1      1                   2
52749 2012-01-25   9    14       0      NA                  NA
52749 2015-01-12  10    14       1      2                   4
52749 2016-09-13  11    14       1      1                   3
52749 2017-03-21  12    14       1      0                   2
52749 2017-08-29  13    14       0      NA                  NA
52749 2017-10-10  14    14       0      NA                  NA
46760 2008-01-01   1    15       1      3                   6
46760 2010-07-19   2    15       1      3                   6
46760 2011-01-14   3    15       0      NA                  NA
46760 2011-08-02   4    15       1      3                   6
46760 2011-08-02   5    15       0      NA                  NA
46760 2012-02-01   6    15       1      3                   6
46760 2012-02-01   7    15       0      NA                  NA
46760 2015-04-28   8    15       1      3                   7
46760 2015-10-19   9    15       0      NA                  NA
46760 2016-05-16  10    15       1      2                   5
46760 2016-12-22  11    15       1      1                   4
46760 2016-12-23  12    15       0      NA                  NA
46760 2017-05-16  13    15       0      NA                  NA
46760 2017-11-15  14    15       1      0                   1
46760 2018-02-22  15    15       0      NA                  NA

Где num_event_5yr_fu подсчитывает количество раз, когда событие произошло (или совокупную сумму в течение этого времени) в течение 5 лет с даты события (не включая дату события), а num_subevents подсчитывает количество записей в течение 5 лет с момента события. дата (не включая дату события).

Я занимаюсь этим довольно давно, застрял и был бы очень признателен за некоторые комментарии о том, как этого можно достичь. Спасибо.

rastrast 27.02.2019 источник

comment

Уточните, пожалуйста, каков ожидаемый результат, поскольку описание ваших требований не соответствует показанному ожидаемому результату? Вы говорите, что оба агрегата не должны содержать событие date, но ожидаемый результат для num_subevents начинает отсчитываться со следующей строки после события, хотя оно имеет ту же дату события (id == 46760 и даты 2011-08-02 и 2012-02-01). - Uwe 02.03.2019

comment

Да, это правильно, он все равно должен содержать следующую запись, даже если у нее такая же дата события. Он просто не может считать дату события на текущей записи, только записи, которые поступают позже. Имеет ли это смысл? - rastrast 04.04.2019

Ответы (3)

arrow_upward
2
arrow_downward

Другой вариант:

library(data.table)
library(lubridate)

dt[, date := as.Date(date)][
  , num_event_5yr_fu := sapply(date,
                               function(x) sum(event[between(date, x + 1, x + years(5))])), by = id
  ][, num_subevents := sapply(date,
                              function(x) length(event[between(date, x + 1, x + years(5))])), by = id
  ][event == 0, `:=` (num_event_5yr_fu = NA, num_subevents = NA)]

Выход:

       id       date idx count event num_event_5yr_fu num_subevents
 1: 52749 2007-01-30   1    14     1                4             8
 2: 52749 2007-03-15   2    14     0               NA            NA
 3: 52749 2007-11-27   3    14     1                3             6
 4: 52749 2007-11-29   4    14     0               NA            NA
 5: 52749 2008-10-09   5    14     1                2             4
 6: 52749 2009-04-02   6    14     0               NA            NA
 7: 52749 2011-01-06   7    14     1                2             3
 8: 52749 2011-07-26   8    14     1                1             2
 9: 52749 2012-01-25   9    14     0               NA            NA
10: 52749 2015-01-12  10    14     1                2             4
11: 52749 2016-09-13  11    14     1                1             3
12: 52749 2017-03-21  12    14     1                0             2
13: 52749 2017-08-29  13    14     0               NA            NA
14: 52749 2017-10-10  14    14     0               NA            NA
15: 46760 2008-01-01   1    15     1                3             6
16: 46760 2010-07-19   2    15     1                3             6
17: 46760 2011-01-14   3    15     0               NA            NA
18: 46760 2011-08-02   4    15     1                3             5
19: 46760 2011-08-02   5    15     0               NA            NA
20: 46760 2012-02-01   6    15     1                3             5
21: 46760 2012-02-01   7    15     0               NA            NA
22: 46760 2015-04-28   8    15     1                3             7
23: 46760 2015-10-19   9    15     0               NA            NA
24: 46760 2016-05-16  10    15     1                2             5
25: 46760 2016-12-22  11    15     1                1             4
26: 46760 2016-12-23  12    15     0               NA            NA
27: 46760 2017-05-16  13    15     0               NA            NA
28: 46760 2017-11-15  14    15     1                0             1
29: 46760 2018-02-22  15    15     0               NA            NA

arg0naut91 27.02.2019

comment

Именно то, что мне нужно! Большое спасибо @ arg0naut, очень полезно - тоже в ясном и коротком формате. - rastrast; 27.02.2019

comment

Как 18-19-е наблюдения 5s для num_subevents? - Khashaa; 28.02.2019

comment

Потому что дата события не указана - и у вас есть дата события и суб-события в один и тот же день. OP, похоже, согласен с этим подходом, но может быть изменен с учетом этого. - arg0naut91; 28.02.2019

arrow_upward
1
arrow_downward

Вот подход data.table с использованием неэквивалентного соединения:

library(lubridate) 

dt[, date := as.Date(date)]
dt[, end_date := date]
year(dt$end_date) <- year(dt$end_date) + 5
dt[, rowid := .I]

event_count = dt[dt, on = .(date < date , end_date >= date, id), 
                 allow.cartesian=TRUE][!is.na(rowid) & event == 1, 
                                       .(events = sum(i.event), num_subevents = .N), 
                                       by = .(rowid, id)]

dt[event_count, on = .(rowid, id), `:=`(num_event_5yr_fu = i.events,
                                        num_subevents = i.num_subevents)]

dt[, c("end_date", "rowid") := NULL]

dt

#        id       date idx count event num_event_5yr_fu num_subevents
#  1: 52749 2007-01-30   1    14     1                4             8
#  2: 52749 2007-03-15   2    14     0               NA            NA
#  3: 52749 2007-11-27   3    14     1                3             6
#  4: 52749 2007-11-29   4    14     0               NA            NA
#  5: 52749 2008-10-09   5    14     1                2             4
#  6: 52749 2009-04-02   6    14     0               NA            NA
#  7: 52749 2011-01-06   7    14     1                2             3
#  8: 52749 2011-07-26   8    14     1                1             2
#  9: 52749 2012-01-25   9    14     0               NA            NA
# 10: 52749 2015-01-12  10    14     1                2             4
# 11: 52749 2016-09-13  11    14     1                1             3
# 12: 52749 2017-03-21  12    14     1                0             2
# 13: 52749 2017-08-29  13    14     0               NA            NA
# 14: 52749 2017-10-10  14    14     0               NA            NA
# 15: 46760 2008-01-01   1    15     1                3             6
# 16: 46760 2010-07-19   2    15     1                3             6
# 17: 46760 2011-01-14   3    15     0               NA            NA
# 18: 46760 2011-08-02   4    15     1                3             5
# 19: 46760 2011-08-02   5    15     0               NA            NA
# 20: 46760 2012-02-01   6    15     1                3             5
# 21: 46760 2012-02-01   7    15     0               NA            NA
# 22: 46760 2015-04-28   8    15     1                3             7
# 23: 46760 2015-10-19   9    15     0               NA            NA
# 24: 46760 2016-05-16  10    15     1                2             5
# 25: 46760 2016-12-22  11    15     1                1             4
# 26: 46760 2016-12-23  12    15     0               NA            NA
# 27: 46760 2017-05-16  13    15     0               NA            NA
# 28: 46760 2017-11-15  14    15     1                0             1
# 29: 46760 2018-02-22  15    15     0               NA            NA

talat 27.02.2019

comment

Действительно интересный подход - замечательно! Я думаю, что подход @arg0naut немного чище, но это тоже здорово. Спасибо! - rastrast; 27.02.2019

comment

Декартова произведения можно избежать путем агрегирования параметров соединения (by = .EACHI. - Uwe; 02.03.2019

arrow_upward
0
arrow_downward

Существует расхождение между спецификацией OP и ожидаемым результатом OP.

OP указал, что num_event_5yr_fu подсчитывает количество раз, когда событие произошло (или совокупную сумму вдоль этого) в течение 5 лет с даты события (не включая дату события), а num_subevents подсчитывает количество записей в течение 5 лет с даты события (не считая даты события).

Однако в ожидаемом результате OP num_subevents подсчитывает количество записей в течение 5 лет с даты события (не включая событие строка (= запись ?).

Таким образом, предлагаются два решения, которые охватывают обе интерпретации.

Воспроизведение ожидаемого результата OP

Этот подход воспроизводит ожидаемый результат OP (в отличие от ответов arg0naut и docendo discimus, которые реализуют требования OP, как описано).

Этот подход объединяет и обновляет неэквивалентное соединение. Он включает дату события в объединение, но исправляет агрегаты, чтобы подсчитать на одно событие меньше.

library(data.table)
new_cols <- c("num_event_5yr_fu", "num_subevents")
result <- dt[
  , date := as.Date(date)][
    .(id = id, start = date, end = date + lubridate::years(5)), 
    on = .(id, date >= start, date <= end), 
    new_cols := .(sum(event) - 1, .N - 1L), by = .EACHI][
      event == 0, new_cols := NA][]
result

       id       date idx count event num_event_5yr_fu num_subevents
 1: 52749 2007-01-30   1    14     1                4             8
 2: 52749 2007-03-15   2    14     0               NA            NA
 3: 52749 2007-11-27   3    14     1                3             6
 4: 52749 2007-11-29   4    14     0               NA            NA
 5: 52749 2008-10-09   5    14     1                2             4
 6: 52749 2009-04-02   6    14     0               NA            NA
 7: 52749 2011-01-06   7    14     1                2             3
 8: 52749 2011-07-26   8    14     1                1             2
 9: 52749 2012-01-25   9    14     0               NA            NA
10: 52749 2015-01-12  10    14     1                2             4
11: 52749 2016-09-13  11    14     1                1             3
12: 52749 2017-03-21  12    14     1                0             2
13: 52749 2017-08-29  13    14     0               NA            NA
14: 52749 2017-10-10  14    14     0               NA            NA
15: 46760 2008-01-01   1    15     1                3             6
16: 46760 2010-07-19   2    15     1                3             6
17: 46760 2011-01-14   3    15     0               NA            NA
18: 46760 2011-08-02   4    15     1                3             6
19: 46760 2011-08-02   5    15     0               NA            NA
20: 46760 2012-02-01   6    15     1                3             6
21: 46760 2012-02-01   7    15     0               NA            NA
22: 46760 2015-04-28   8    15     1                3             7
23: 46760 2015-10-19   9    15     0               NA            NA
24: 46760 2016-05-16  10    15     1                2             5
25: 46760 2016-12-22  11    15     1                1             4
26: 46760 2016-12-23  12    15     0               NA            NA
27: 46760 2017-05-16  13    15     0               NA            NA
28: 46760 2017-11-15  14    15     1                0             1
29: 46760 2018-02-22  15    15     0               NA            NA
       id       date idx count event num_event_5yr_fu num_subevents

Обратите внимание, что строки с 18 по 20 (id == 46760 и date между 2011-08-02 и 2012-02-01) соответствуют ожидаемому результату OP.

Это можно проверить

all.equal(result, expected, check.attributes = FALSE)

[1] TRUE

Воспроизведение других ответов

Здесь учитываются только записи, дата которых больше даты события.

library(data.table)
tmp <- dt[, date := as.Date(date)][
  dt[event == 1, .(id, start = date, end = date + lubridate::years(5))],
  on = .(id, date > start, date <= end), 
  .(event = 1, sum(event), .N), by = .EACHI]
result <- dt[tmp, on = .(id, event, date), 
              c("num_event_5yr_fu", "num_subevents") := .(V2, N)][]
result

       id       date idx count event num_event_5yr_fu num_subevents
 1: 52749 2007-01-30   1    14     1                4             8
 2: 52749 2007-03-15   2    14     0               NA            NA
 3: 52749 2007-11-27   3    14     1                3             6
 4: 52749 2007-11-29   4    14     0               NA            NA
 5: 52749 2008-10-09   5    14     1                2             4
 6: 52749 2009-04-02   6    14     0               NA            NA
 7: 52749 2011-01-06   7    14     1                2             3
 8: 52749 2011-07-26   8    14     1                1             2
 9: 52749 2012-01-25   9    14     0               NA            NA
10: 52749 2015-01-12  10    14     1                2             4
11: 52749 2016-09-13  11    14     1                1             3
12: 52749 2017-03-21  12    14     1                0             2
13: 52749 2017-08-29  13    14     0               NA            NA
14: 52749 2017-10-10  14    14     0               NA            NA
15: 46760 2008-01-01   1    15     1                3             6
16: 46760 2010-07-19   2    15     1                3             6
17: 46760 2011-01-14   3    15     0               NA            NA
18: 46760 2011-08-02   4    15     1                3             5
19: 46760 2011-08-02   5    15     0               NA            NA
20: 46760 2012-02-01   6    15     1                3             5
21: 46760 2012-02-01   7    15     0               NA            NA
22: 46760 2015-04-28   8    15     1                3             7
23: 46760 2015-10-19   9    15     0               NA            NA
24: 46760 2016-05-16  10    15     1                2             5
25: 46760 2016-12-22  11    15     1                1             4
26: 46760 2016-12-23  12    15     0               NA            NA
27: 46760 2017-05-16  13    15     0               NA            NA
28: 46760 2017-11-15  14    15     1                0             1
29: 46760 2018-02-22  15    15     0               NA            NA
       id       date idx count event num_event_5yr_fu num_subevents

Промежуточный результат:

tmp

       id       date       date event V2 N
 1: 52749 2007-01-30 2012-01-30     1  4 8
 2: 52749 2007-11-27 2012-11-27     1  3 6
 3: 52749 2008-10-09 2013-10-09     1  2 4
 4: 52749 2011-01-06 2016-01-06     1  2 3
 5: 52749 2011-07-26 2016-07-26     1  1 2
 6: 52749 2015-01-12 2020-01-12     1  2 4
 7: 52749 2016-09-13 2021-09-13     1  1 3
 8: 52749 2017-03-21 2022-03-21     1  0 2
 9: 46760 2008-01-01 2013-01-01     1  3 6
10: 46760 2010-07-19 2015-07-19     1  3 6
11: 46760 2011-08-02 2016-08-02     1  3 5
12: 46760 2012-02-01 2017-02-01     1  3 5
13: 46760 2015-04-28 2020-04-28     1  3 7
14: 46760 2016-05-16 2021-05-16     1  2 5
15: 46760 2016-12-22 2021-12-22     1  1 4
16: 46760 2017-11-15 2022-11-15     1  0 1

Он содержит результаты только для event == 1. В последнем обновлении соединения event включен в ключи, к которым нужно присоединиться. Для строк с event == 1 совпадений нет, поэтому для новых столбцов автоматически устанавливается NA.

Данные

dt = data.table(id=c(rep(52749, 14), rep(46760, 15)),
                date=c("2007-01-30","2007-03-15","2007-11-27",
                       "2007-11-29","2008-10-09","2009-04-02",
                       "2011-01-06","2011-07-26","2012-01-25",
                       "2015-01-12","2016-09-13","2017-03-21",
                       "2017-08-29","2017-10-10","2008-01-01",
                       "2010-07-19","2011-01-14","2011-08-02",
                       "2011-08-02","2012-02-01","2012-02-01",
                       "2015-04-28","2015-10-19","2016-05-16",
                       "2016-12-22","2016-12-23","2017-05-16",
                       "2017-11-15","2018-02-22"),
                idx=c(seq_len(14), seq_len(15)),
                count=c(rep(14,14),rep(15,15)),
                event=c(1, 0, 1, 0, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 1, 
                        1, 0, 1, 0, 1, 0, 1, 0, 1, 1, 0, 0, 1, 0))


expected <- 
fread("id    date         idx  count    event  num_event_5yr_fu    num_subevents
52749 2007-01-30   1    14       1      4                   8
52749 2007-03-15   2    14       0      NA                  NA
52749 2007-11-27   3    14       1      3                   6
52749 2007-11-29   4    14       0      NA                  NA
52749 2008-10-09   5    14       1      2                   4
52749 2009-04-02   6    14       0      NA                  NA
52749 2011-01-06   7    14       1      2                   3
52749 2011-07-26   8    14       1      1                   2
52749 2012-01-25   9    14       0      NA                  NA
52749 2015-01-12  10    14       1      2                   4
52749 2016-09-13  11    14       1      1                   3
52749 2017-03-21  12    14       1      0                   2
52749 2017-08-29  13    14       0      NA                  NA
52749 2017-10-10  14    14       0      NA                  NA
46760 2008-01-01   1    15       1      3                   6
46760 2010-07-19   2    15       1      3                   6
46760 2011-01-14   3    15       0      NA                  NA
46760 2011-08-02   4    15       1      3                   6
46760 2011-08-02   5    15       0      NA                  NA
46760 2012-02-01   6    15       1      3                   6
46760 2012-02-01   7    15       0      NA                  NA
46760 2015-04-28   8    15       1      3                   7
46760 2015-10-19   9    15       0      NA                  NA
46760 2016-05-16  10    15       1      2                   5
46760 2016-12-22  11    15       1      1                   4
46760 2016-12-23  12    15       0      NA                  NA
46760 2017-05-16  13    15       0      NA                  NA
46760 2017-11-15  14    15       1      0                   1
46760 2018-02-22  15    15       0      NA                  NA")[
  , date := as.Date(date)]

Uwe 02.03.2019

Скользящие суммы по диапазону дат в таблице данных по группам в R

Ответы (3)

Воспроизведение ожидаемого результата OP

Воспроизведение других ответов

Данные

Вопросы по теме