CODEWITHSUNDEEP
rgroup-bydata.table
tiezhuetc
tiezhuetc

Reputation: 41

R group by then filter to add value on each row

I have a data.table contains 100000 rows, I want to create a column for each row to count the number of this horse win the race (ind_win=1) within two years (rdate<rdate&rdate>=rdate-years(2)), the output should have the same format as input except one more column horse_win_count. I know I can use apply to filter each row then subset entire data to count the value. But how can I do this in a fast way?

input

enter image description here

structure(list(index = c(2501L, 3415L, 19740L, 20566L, 22604L, 
24622L, 66025L, 67207L, 87018L), rdate = structure(c(13845, 13873, 
14531, 14559, 14622, 14685, 16200, 16236, 16974), class = "Date"), 
    horsenum = c("E268", "E268", "E268", "E268", "E268", "E268", 
    "P178", "P178", "P178"), ind_win = c(0L, 1L, 0L, 1L, 0L, 
    1L, 0L, 1L, 0L)), row.names = c(NA, -9L), class = c("data.table", 
"data.frame"), .internal.selfref = <pointer: 0x0000029b06fb1ef0>)

output

enter image description here

Upvotes: 0

Views: 45

Answers (1)

chinsoon12
chinsoon12

Reputation: 25225

You can use a non-equi join:

setDT(DT)
DT[, twoyago := as.IDate(sapply(rdate, function(d) seq(d, by="-2 years", length.out=2L)[2L]))]
DT[, horse_win_count := 
        DT[DT, on=.(horsenum, rdate<rdate, rdate>=twoyago), 
            i.ind_win + sum(x.ind_win, na.rm=TRUE), by=.EACHI]$V1
    ]

output:

   index      rdate horsenum ind_win    twoyago horse_win_count
1:  2501 2007-11-28     E268       0 2005-11-28               0
2:  3415 2007-12-26     E268       1 2005-12-26               1
3: 19740 2009-10-14     E268       0 2007-10-14               1
4: 20566 2009-11-11     E268       1 2007-11-11               2
5: 22604 2010-01-13     E268       0 2008-01-13               1
6: 24622 2010-03-17     E268       1 2008-03-17               2
7: 66025 2014-05-10     P178       0 2012-05-10               0
8: 67207 2014-06-15     P178       1 2012-06-15               1
9: 87018 2016-06-22     P178       0 2014-06-22               0

data:

library(data.table)
DT <- structure(list(index = c(2501L, 3415L, 19740L, 20566L, 22604L, 
    24622L, 66025L, 67207L, 87018L), rdate = structure(c(13845, 13873, 
        14531, 14559, 14622, 14685, 16200, 16236, 16974), class = "Date"), 
    horsenum = c("E268", "E268", "E268", "E268", "E268", "E268", 
        "P178", "P178", "P178"), ind_win = c(0L, 1L, 0L, 1L, 0L, 
            1L, 0L, 1L, 0L)), row.names = c(NA, -9L), class = c("data.table", 
                "data.frame"))

Upvotes: 1

Related Questions