J'ai un tableau de données avec des événements et des sous-événements au fil du temps et je suis intéressé par la création de deux colonnes: (1) une somme glissante cumulative indiquant si un événement s'est produit dans les 5 ans à compter de la date de l'événement et (2) un décompte du nombre de sous-événements (y compris les événements) qui ont eu lieu dans les 5 ans à compter de la date de l'événement. Voici un exemple avec du code:
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
Ce que cela produit est le suivant:
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
Ce dont j'ai essentiellement besoin est ce qui suit: p>
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))
Où le num_event_5yr_fu compte le nombre de fois qu'un événement s'est produit (ou une somme cumulée le long de celui-ci) dans les 5 ans à compter de la date de l'événement (sans compter la date de l'événement), et le num_subevents compte le nombre d'enregistrements dans les 5 ans à compter de la date de l'événement (sans compter la date de l'événement).
J'y suis depuis un certain temps et je suis coincé et j'apprécierais vraiment quelques commentaires sur la façon dont cela pourrait être réalisé. Merci.
3 Réponses :
Voici une approche data.table utilisant une jointure non équi:
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
Approche vraiment intéressante - merveilleuse! Je pense que l'approche de @ arg0naut est un peu plus propre mais c'est génial aussi. Merci!
Le produit cartésien peut être évité en agrégeant sur les paramètres de jointure ( by = .EACHI .
Autre option:
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
Sortie:
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)]
Exactement ce dont j'avais besoin! Merci beaucoup @ arg0naut, très utile - dans un format propre et court également.
Comment sont les 18-19èmes observations 5s pour num_subevents ?
Parce que la date de l'événement n'est pas incluse - et là, vous avez à la fois la date de l'événement et celle du sous-événement le même jour. OP semble convenir à cette approche, mais peut être modifié pour en tenir compte.
Il y a un écart entre la spécification de l'OP et le résultat attendu de l'OP.
L'OP a spécifié que num_event_5yr_fu compte le nombre de fois qu'un événement s'est produit (ou une somme cumulée le long de ce ) dans les 5 ans à compter de la date de l'événement (sans compter la date de l'événement), et num_subevents compte le nombre d'enregistrements dans les 5 ans à compter de la date de l'événement (sans compter la date de l'événement). em >
Cependant, dans le résultat attendu de l'OP, num_subevents compte le nombre de enregistrements dans les 5 ans à compter de la date de l'événement (sans compter l'événement row (= record?).
Par conséquent, deux solutions sont proposées qui couvrent les deux interprétations.
Cette approche reproduit le résultat attendu d'OP (contrairement aux réponses de arg0naut et docendo discimus quel im plement OP comme décrit).
Cette approche agrège et met à jour dans une jointure non équi . Il inclut la date de l'événement dans la jointure mais corrige les agrégats pour compter un événement de moins.
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)]
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
Notez que les lignes 18 à 20 ( id == 46760 et date entre le 02/08/2011 et le 01/02/2012) sont en ligne avec le résultat attendu de OP.
Cela peut être vérifié par
tmp
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
Ici, seuls les enregistrements sont comptés dont la date est supérieure à la date de l'événement.
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
[1] TRUE
Le résultat intermédiaire est
all.equal(result, expected, check.attributes = FALSE)
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
Il ne contient que les résultats pour event == 1 . Dans la jointure de mise à jour finale, l ' événement est inclus dans les clés auxquelles participer. Pour les lignes avec event == 1 il n'y a pas de correspondance et donc les nouvelles colonnes sont automatiquement définies sur NA .
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
Pouvez-vous clarifier quel est le résultat attendu car la description de vos besoins n'est pas conforme au résultat attendu affiché? Vous dites que les deux agrégats ne doivent pas contenir l'événement date mais le résultat attendu pour
num_subeventscommence à compter à partir de la ligne suivante après l'événement bien qu'il a la même date d'événement (id == 46760et dates 2011-08-02 et 2012-02-01).Oui, c'est correct, il doit toujours contenir l'enregistrement suivant même s'il a la même date d'événement. Il ne peut tout simplement pas compter la date de l'événement sur l'enregistrement actuel, uniquement les enregistrements qui viennent après. Est-ce que ça a du sens?