R data.table transform with row explosion

Question

I would like to split one row into two (or more) rows when the cumsum of one of the column breaks the period. Is there any elegant way to perform such specific row explosion using data.table? Do not focus on cumsum (which I used in reversed order to have cumsum from most recent row to the oldest one), strictly speaking I want transform dt into rdt from code below.

# current data
dt <- data.table(
  time_id = 101:110, 
  desc = c('asd','qwe','xyz','qwe','qwe','xyz','asd','asd','qwe','asd'),
  value = c(5.5,3.5,14,0.7,6,5.5,9.3,29.8,4,7.2)
)
dt[, cum_value_from_now := rev(cumsum(rev(value)))]
period_width <- 10
dt[, value_period := ceiling(cum_value_from_now/period_width)*period_width]
dt

# expected result
rdt <- data.table(
  time_id = c(101,102,103,103,104,105,105,106,107,107,108,108,108,108,109,109,110), 
  desc = c('asd','qwe','xyz','xyz','qwe','qwe','qwe','xyz','asd','asd','asd','asd','asd','asd','qwe','qwe','asd'),
  value = c(5.5,3.5,6.5,7.5,0.7,1.8,4.2,5.5,0.3,9,1,10,10,8.8,1.2,2.8,7.2)
)[, cum_value_from_now := rev(cumsum(rev(value)))][, value_period := ceiling(cum_value_from_now/period_width)*period_width]
rdt

# validation
all.equal(
  dt[,list(time_id,desc,value)],
  rdt[,list(value = sum(value)), by=c('time_id','desc')]
  )

edit: I realized my question is not explained well the transformation I want to perform. To better understand the breaks the period meaning please take a look at my rdt the cum_value_from_now values from the last to first. Each value_period is completely filled by cumsum on value, the rest of value is produced as new row (if value is big enough then it is produced to multiple rows) to fit into next period(s). Thanks

What do you mean by "breaks the period" ? Have some sympathy for us: post a small, reproducible example using just a matrix, no data.table operations, and the desired outcome. Equally useful: tell us what the point of this function is, as there may be a much simpler way to achieve your final goal. — Carl Witthoft
"breaks the period" is best defined by value_period variable. It is kind of grouping variable, in this example grouping cumsum by 10. As for the matrix example, I didn't use matrix for quite long time already and what I remember it might be not possible to mix data types in matrix. In my real data.table I use also more complex data types so it would be even harder to convert it to matrix. The point is to split rows which would fit to the two (or more) periods into multiple rows which would aggregate to the source row - as in the validation step. — jangorecki

jlhoward jlhoward · Accepted Answer · 2014-05-12T00:18:37

First, you seem to be applying your rules inconsistently. If "breaking the period" means that a row has value_period different from the previous row, then row 2 breaks the period, but you do not treat it that way.

Second, you never explain the partitioning of value. For instance, row 3 has value=14. This is replaced in rdt with two rows with values 6.5 and 7.5. These add to 14 all right, but there is no explanation of why this should be 6.5 and 7.5, rather than, say, 7 and 7. So in the solution below I partition equally.

The code below produces a result which passes your test, but it is not quite the same as your rdt, due to the above-mentioned problems with your question.

dt[,diff:=c(-diff(value_period)/10,0)]
rdt <- dt[,list(value=as.numeric(rep(value/(diff+1),diff+1))),
          by=list(time_id,desc,cum_value_from_now, value_period)]

all.equal(
  dt[,list(time_id,desc,value)],
  rdt[,list(value = sum(value)), by=c('time_id','desc')]
)
# [1] TRUE

R data.table transform with row explosion

1 Answers