0

I have a DT and desire to compute some easy Stock balances like shown. This computations are easy and immediate in Excel, which allows some recursive computations.

library(data.table)
DT <- data.table(expand.grid(8:9, 0:59, 1:4))
names(DT) <- c("Hour", "Minute", "Shop")

set.seed(1234)
DT <- DT[, ':=' (Demand = rpois(1, lambda = 0.8),
                 Stock_in = rpois(1, lambda = 0.05)*250
                 ), by=names(DT)]

DT$Stock_Before <- NA
DT$Stock_After <- NA
DT$Sells <- NA
DT$Lost_Sells <- NA

DT$Stock_Before[which(DT$Hour == 8 & DT$Minute == 0)] <- c(21, 45, 31, 50) 

DT <- DT[order(DT$Hour, DT$Minute, DT$Shop),]

The desired format is done, by now using tapply but it is slow. So I tried to use data table syntax to do it better.

Since the Stock_Before of a minute is the Stock_After of the minute before, the line I designed reports an error. The command I would like to use is the following lines:

DT <- DT[, ':=' (
  Stock_After =  max(0, Stock_Before+Stock_in-Demand),
  Stock_Before  = shift(Stock_After, 1),
  Sells = min(Stock_Before + Stock_in, Demand, na.rm=T),
  Lost_Sells = max(Demand-Sells, 0)
  ), by=Shop]

And the error is the following:

Error in shift(Stock_After, 1) : object 'Stock_After' not found
In addition: Warning message:
In `[.data.table`(DT, , `:=`(Stock_After = max(0, Stock_Before +  :
  Invalid .internal.selfref detected and fixed by taking a (shallow) copy of
 the data.table so that := can add this new column by reference. At an earlier
 point, this data.table has been copied by R (or been created manually using structure() or similar). Avoid key<-, 
 names<- and attr<- which in R currently (and oddly) 
 may copy the whole data.table. Use set* syntax instead to avoid copying: ?set, 
 ?setnames and ?setattr. Also, in R<=v3.0.2, list(DT1,DT2) copied the entire DT1 
 and DT2 (R's list() used to copy named objects); please upgrade to R>v3.0.2 if that
 is biting. If this message doesn't help, please report to datatable-help so the root cause can be fixed.

The results are those I show below:

head(DT, 12)

   Hour Minute Shop Demand Stock_in Stock_Before Stock_After Sells Lost_Sells
1     8      0    1      0        0           21          21     0          0
2     8      0    2      0        0           45          45     0          0
3     8      0    3      1        0           31          30     1          0
4     8      0    4      2        0           50          48     2          0
5     8      1    1      2        0           21          19     2          0
6     8      1    2      3        0           45          42     3          0
7     8      1    3      0        0           30          30     0          0
8     8      1    4      1      250           48         297     1          0
9     8      2    1      1        0           19          18     1          0
10    8      2    2      3        0           42          39     3          0
11    8      2    3      1        0           30          29     1          0
12    8      2    4      0      250          297         547     0          0

Can anyone help me? Thanks a lot!

Manuel
  • 46
  • 1
  • 10
  • 5
    Providing a [reproducible example](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) will generate better responses, but the issue is that your syntax for data.tables is wrong. Get rid of := and just put list around the vars you're trying to create - that'll start you down the right path. – Tchotchke Jul 21 '16 at 15:41
  • @Tchotchke why is their syntax is wrong? Looks perfectly fine to me. Only the `DT <-` part is wrong, but still should work – David Arenburg Jul 21 '16 at 22:31
  • That may be a syntax I'm not familiar with then - good to learn! I would have tried it out if there had been data with it... – Tchotchke Jul 21 '16 at 23:05
  • 1
    `Stock_After` and `Stock_Before` attempt to use a variable which doesn't exists at that time, use chaining to produce interim variables. Provide the data, not its print. – jangorecki Jul 21 '16 at 23:06
  • I've just done a reproducible example. Thanks for the help! :D – Manuel Jul 22 '16 at 08:04

0 Answers0