Geek Answers Handbook

Data.table, how to reset cumsum (or add a group to a group)

I have a data table with some computed columns

dt <- data.table(x=c(1,4,-3,-2,3,4)) dt[,y:=cumsum(x)] dt[,q:=cumsum(ifelse(x>0,x,0))] xyq 1: 1 1 1 2: 4 5 5 3: -3 2 5 4: -2 0 5 5: 3 3 8 6: 4 7 12

I need to reset q after y == 0. Essentially lines 1: 4 belong to subgroup A and 5: 6 to subgroup B. The result should be:

  xyq 1: 1 1 1 2: 4 5 5 3: -3 2 5 4: -2 0 5 5: 3 3 3 6: 4 7 7

I assume that I could imagine another group of columns with values ​​A, B, ... that would change after y == 0, and then use this by exception, but I don't know how (at least not using for suggestions)

+7
r data.table
source share
3 answers

Try something like this

 dt[, group:= cumsum(y == 0)] dt[y == 0, group := group - 1] dt[, q:=cumsum(ifelse(x>0,x,0)), by = group] dt # xy group q #1: 1 1 0 1 #2: 4 5 0 5 #3: -3 2 0 5 #4: -2 0 0 5 #5: 3 3 1 3 #6: 4 7 1 7
+8
source share

With the version of data.table devel

 dt[, q:=cumsum(ifelse(x>0,x,0)),by=shift(cumsum(y==0),1, fill=0)] #library(devtools) #install_github("Rdatatable/data.table", build_vignettes = FALSE) # xyq #1: 1 1 1 #2: 4 5 5 #3: -3 2 5 #4: -2 0 5 #5: 3 3 3 #6: 4 7 7
+9
source share

You can add the condition c(0L, cumsum(y == 0L))[-length(y)] to the by statement

 dt[, q := cumsum(ifelse(x > 0, x, 0)), by = c(0L, cumsum(y == 0L))[-length(y)]][] # xyq # 1: 1 1 1 # 2: 4 5 5 # 3: -3 2 5 # 4: -2 0 5 # 5: 3 3 3 # 6: 4 7 7
+6
source share

More articles:


All Articles