data.table column assignment within a loop

Question

This is what my data.table looks like. The three rightmost columns are my desired columns.

 library(data.table)
    dt <- fread('
        Product  Sales    A-CumSales   B-CumSales  C-CumSales 
        A          10        10            0          0
        B          5         10            5          0
        A          10        20            5          0
        A          20        40            5          0
        B          10        40           15          0
        C          5         40           15          5
        C          5         40           15          10
    ')
 dt[, Product:= as.factor(Product)]

The levels in my Product column are always changing. I am trying to do a loop where I am creating a separate column for each Product that computes the cumulative Sales of the respective Product.

I have tried:

for (i in levels(dt$Product)) {
  dt[,i:= cumsum((Product == "i") * Sales)]
}

@Frank You are right. I think i meant dcast(dt[, 1:2, with = FALSE], Product + 1:nrow(dt) + rowid(Product)~Product,value.var = "Sales", fill = 0)[, c("A", "B", "C") := lapply(.SD, cumsum), .SDcols = A:C][] — akrun
– akrun, Commented Apr 11, 2017 at 16:18

mt1022 · Accepted Answer · 2017-04-11 16:49:03Z

2

Here is what I tried based on the OP's code:

dt <- dt[, .(Product, Sales)]

plevels <- levels(dt$Product)

dt[, c(paste(plevels, 'CumSales', sep = '-')) :=
        lapply(plevels, function(x) cumsum(Sales * (Product == x)))]

#    Product Sales A-CumSales B-CumSales C-CumSales
# 1:       A    10         10          0          0
# 2:       B     5         10          5          0
# 3:       A    10         20          5          0
# 4:       A    20         40          5          0
# 5:       B    10         40         15          0
# 6:       C     5         40         15          5
# 7:       C     5         40         15         10

edited Apr 11, 2017 at 16:49

answered Apr 11, 2017 at 16:23

mt1022

17.4k6 gold badges53 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

data.table column assignment within a loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related