按类别计算基于日期的累积乘积

Question

按类别计算基于日期的累积乘积

5

我想在我的data.table中添加一个新列，该列包含基于Date的Data1的累积乘积。累计乘积应针对每个类别(Cat)进行计算，并且应从最新可用的Date开始。

示例数据：

     DF = data.frame(Cat=rep(c("A","B"),each=4), Date=rep(c("01-08-2013","01-07-2013","01-04-2013","01-03-2013"),2), Data1=c(1:8))
     DF$Date = as.Date(DF$Date , "%m-%d-%Y")
     DT = data.table(DF)
     DT[ , Data1_cum:=NA_real_]
     DT

        Cat      Date Data1 Data1_cum
     1:  A 2013-01-08     1    NA
     2:  A 2013-01-07     2    NA
     3:  A 2013-01-04     3    NA
     4:  A 2013-01-03     4    NA
     5:  B 2013-01-08     5    NA
     6:  B 2013-01-07     6    NA
     7:  B 2013-01-04     7    NA
     8:  B 2013-01-03     8    NA

结果应该如下所示：

        Cat      Date Data1 Data1_cum
     1:  A 2013-01-08     1    1
     2:  A 2013-01-07     2    2
     3:  A 2013-01-04     3    6
     4:  A 2013-01-03     4    24
     5:  B 2013-01-08     5    5
     6:  B 2013-01-07     6    30
     7:  B 2013-01-04     7    210
     8:  B 2013-01-03     8    1680

我发现我可以使用cumprod()来实现类似的功能，但是我不知道如何处理这些分类。在Data1中的NA应该被忽略/视为1。

真实数据集有大约8百万行和1000个类别。

- Cake

你说有1000个类别，共有800万条目。这意味着每个类别大约有8000个条目。即使最小值为2，累积乘积也会达到2的8000次方，对吧？那么你的大多数值不是都会变成无穷大吗？ - Arun

是的，但幸运的是主要都是“NA”，而且大多数数字都小于1。 - Cake

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Ricardo Saporta · Accepted Answer

如果唯一的问题是排序...

DT[order(Date, decreasing=TRUE), Data1_cum := cumprod(Data1), by=Cat]
DT
   Cat       Date Data1 Data1_cum
1:   A 2013-01-08     1         1
2:   A 2013-01-07     2         2
3:   A 2013-01-04     3         6
4:   A 2013-01-03     4        24
5:   B 2013-01-08     5         5
6:   B 2013-01-07     6        30
7:   B 2013-01-04     7       210
8:   B 2013-01-03     8      1680

但是，如果你需要处理NA值，则需要进行一些额外的步骤：

注意：如果你随机改变行的顺序，结果可能会有所不同。使用order(.)命令时要小心。

  ## Let's add some NA values
  DT <- rbind(DT, DT)
  DT[c(2, 6, 11, 15), Data1 := NA]

  # shuffle the rows, to make sure this is right
  set.seed(1)
  DT <- DT[sample(nrow(DT))]

累积乘积的分配：

忽略NA值

## If you want to leave the NA's as NA's in the cum prod, use: 
DT[ , Data1_cum := NA_real_ ]
DT[ intersect(order(Date, decreasing=TRUE), which(!is.na(Data1))) 
      , Data1_cum := cumprod(Data1)
      , by=Cat]

# View the data, orderly
DT[order(Date, decreasing=TRUE)][order(Cat)]

     Cat       Date Data1 Data1_cum
  1:   A 2013-01-08     1         1
  2:   A 2013-01-08     1         1
  3:   A 2013-01-07     2         2
  4:   A 2013-01-07    NA        NA  <~~~~~~~  Note that the NA rows have the value of the prev row     
  5:   A 2013-01-04     3         6
  6:   A 2013-01-04    NA        NA  <~~~~~~~  Note that the NA rows have the value of the prev row
  7:   A 2013-01-03     4        24
  8:   A 2013-01-03     4        96
  9:   B 2013-01-08     5         5  
 10:   B 2013-01-08     5        25
 11:   B 2013-01-07     6       150
 12:   B 2013-01-07    NA        NA  <~~~~~~~  Note that the NA rows have the value of the prev row  
 13:   B 2013-01-04     7      1050
 14:   B 2013-01-04    NA        NA  <~~~~~~~  Note that the NA rows have the value of the prev row    
 15:   B 2013-01-03     8      8400
 16:   B 2013-01-03     8     67200

使用前一行的值替换NA

## If instead you want to treat the NA's as 1, use: 
DT[order(Date, decreasing=TRUE), Data1_cum := {Data1[is.na(Data1)] <- 1;  cumprod(Data1 [order(Date, decreasing=TRUE)] )}, by=Cat]

# View the data, orderly
DT[order(Date, decreasing=TRUE)][order(Cat)]

    Cat       Date Data1 Data1_cum
 1:   A 2013-01-08     1         1
 2:   A 2013-01-08     1         1
 3:   A 2013-01-07     2         2
 4:   A 2013-01-07    NA         2   <~~~~~~~ Rows with NA took on values of the previous Row
 5:   A 2013-01-04     3         6
 6:   A 2013-01-04    NA         6   <~~~~~~~ Rows with NA took on values of the previous Row
 7:   A 2013-01-03     4        24
 8:   A 2013-01-03     4        96
 9:   B 2013-01-08     5         5
10:   B 2013-01-08     5        25
11:   B 2013-01-07     6       150
12:   B 2013-01-07    NA       150   <~~~~~~~ Rows with NA took on values of the previous Row
13:   B 2013-01-04     7      1050
14:   B 2013-01-04    NA      1050   <~~~~~~~ Rows with NA took on values of the previous Row
15:   B 2013-01-03     8      8400
16:   B 2013-01-03     8     67200

如果您已经有了累积乘积，并且只想去掉NA，可以按以下方式进行：

# fix the NA's with the previous value
DT[order(Date, decreasing=TRUE),
      Data1_cum := {tmp <- c(0, head(Data1_cum, -1));  
      Data1_cum[is.na(Data1_cum)] <- tmp[is.na(Data1_cum)]; 
      Data1_cum }
      , by=Cat ]