Use previously calculated value in data.table

Question

The x.wanted in the MWE uses x.wanted calculated on the row above and then adds values from actual row. How can that be done in data.table? I thought shift was the way, but gives wrong result.

x.wanted is a way to separate bars in a plot that have different bar-widths.

library(data.table)
library(ggplot2)
dt <- data.table(x.group=c(rep(1L, 4L), rep(2L, 5L)),
                 x.width=c(2L, 4L, 2L, 6L, 4L, 2L, 4L, 6L, 2L),
                 x.sep=c(0L, 1L, 1L, 1L, 0L, 1L, 1L, 1L, 1L),
                 x.wanted=c(1, 5, 9, 14, 2, 6, 10, 16, 21))

dt[, x.try.1:=x.width/2]
dt[, x.try.1:=shift(x=x.try.1, fill=0, type="lag") + x.sep + x.width/2, by=x.group]

p <- ggplot(dt, aes(x=x.wanted, y=5))
p <- p + geom_bar(aes(width=x.width), stat="identity", position="stack")
p <- p + facet_grid(x.group~., scales="free_x")
p

Plot is added to visualise wanted result. x.try.1 is my failed attempt.

*"I thought `shift` was the way, but gives wrong result"* doesn't tell us much. Please reduce the data to the minimum to show why shift is wrong, and post the output of using shift. Related: dnlbrky's answer [Use a value from the previous row in an R data.table calculation](https://stackoverflow.com/a/25084650/202229) where he/she shows you how to define `rowShift(B,-1)` . Please try that, I think this is a duplicate. — smci, Dec 06 '18 at 07:55
I thought rowShift was a solution for data.table pre 1.9.6. The accepted solution in your link was the one I started from. — Chris, Dec 06 '18 at 07:59
can you show how did you get 9 in row 3? i think you might be looking for `Reduce` — chinsoon12, Dec 06 '18 at 08:12

Uwe · Accepted Answer · 2018-12-06T10:12:15.980

If I understand correctly, the OP wants to locate the midpoints of the bars. This can be achieved by using the cumsum() function (plus shift()):

dt[, x.mid := cumsum(shift(x.width, fill = 0) + x.sep) + x.width/2, by = x.group][]

   x.group x.width x.sep x.wanted x.try.1 x.mid
1:       1       2     0        1       1     1
2:       1       4     1        5       4     5
3:       1       2     1        9       4     9
4:       1       6     1       14       5    14
5:       2       4     0        2       2     2
6:       2       2     1        6       4     6
7:       2       4     1       10       4    10
8:       2       6     1       16       6    16
9:       2       2     1       21       5    21

The computed values of x.mid are in line with OP's x.wanted.

Another approach is to plot rectangles instead of bars. This will use the left and right cornerpoints on the x-axis which makes the computation more straightforward, IMHO:

dt[, x.min := cumsum(shift(x.width, fill = 0) + x.sep), by = x.group]

ggplot(dt, aes(xmin = x.min, xmax = x.min + x.width, ymin = 0, ymax = 5)) +
  geom_rect() +
  facet_grid(x.group ~ .)

This will create the same chart as shown in OP's question.

Use previously calculated value in data.table

1 Answers1