R: Conditional Sum by Row in DataTable

user_16384595 0
Viewed 80

I've got a very large dataset (millions of rows that I need to loop through thousands of times), and during the loop I have to do a conditional sum that appears to be taking a very long time. Is there a way of making this more efficient?

Datatable format as follows:

DT <- data.table('A' = c(1,1,1,2,2,3,3,3,3,4),
                 'B' = c(500,510,540,500,540,500,510,519,540,500),
                 'C' = c(10,20,10,20,10,50,20,50,20,10))
A B C
1 500 10
1 510 20
1 540 10
2 500 20
2 540 10
3 500 50
3 510 20
3 519 50
3 540 20
4 500 10

I need the sum of column C (in a new column, D) subject to A == A, and B >= B & B < B + 20 (by row). So the output table would look like the following:

A B C D
1 500 10 30
1 510 20 30
1 540 10 10
2 500 20 20
2 540 10 10
3 500 50 120
3 510 20 120
3 519 50 120
3 540 20 20
4 500 10 10

The code I'm currently using:

DT[,D:= sum(DT$C[A == DT$A & ((B >= DT$B) & (B < DT$B + 20))]), by=c('A', 'B')]

This takes a very long time to actually run, as well as giving me the wrong answer. The output I get looks like this:

A B C D
1 500 10 10
1 510 20 30
1 540 10 10
2 500 20 20
2 540 10 10
3 500 50 50
3 510 20 70
3 519 50 120
3 540 20 20
4 500 10 10

(i.e. D only appears to increase cumulatively).

I'm less concerned with the cumulative thing, more about speed. Ultimately what I'm trying to get to is the largest sum of C, by A, subject to B being within 20 of eachother. I would really appreciate any help on this! Thanks in advance.

data.table conditional-statements r sum performance
2 Answers
# logic for B
DT[, g := B >= shift(B) & B < shift(B, 1) + 20, by = A]
# creating index column
DT[, gi := !g] 
DT[is.na(gi), gi := T]
DT[, gi := cumsum(gi)]
DT[, D := sum(C), by = gi] # summing by new groups
DT
#     A   B  C     g gi   D
#  1: 1 500 10    NA  1  30
#  2: 1 510 20  TRUE  1  30
#  3: 1 540 10 FALSE  2  10
#  4: 2 500 20    NA  3  20
#  5: 2 540 10 FALSE  4  10
#  6: 3 500 50    NA  5 120
#  7: 3 510 20  TRUE  5 120
#  8: 3 519 50  TRUE  5 120
#  9: 3 540 20 FALSE  6  20
# 10: 4 500 10    NA  7  10

You might need to adjust logic for B, as all edge cases isn't clear from the question... if for one A value we have c(30, 40, 50, 60), all of those rows are in one group?

user_70633750

If I understand correctly, this can be solved by a non-equi self join:

DT[, Bp20 := B + 20][
  DT, on = .(A, B >= B, B < Bp20), mult = "last"][
    , .(B, C = i.C, D = sum(i.C)), by = .(A, Bp20)][
      , Bp20 := NULL][]

    A   B  C   D
 1: 1 500 10  30
 2: 1 510 20  30
 3: 1 540 10  10
 4: 2 500 20  20
 5: 2 540 10  10
 6: 3 500 50 120
 7: 3 510 20 120
 8: 3 519 50 120
 9: 3 540 20  20
10: 4 500 10  10
user_38170040
Related