r - 总结 data.table - 在 R 中按日期创建多列子集

Question

我有关于 ID 和相应的 amount 多年的数据。像这样:

 ID <- c(rep("A", 5), rep("B", 7), rep("C", 3))
  amount <- c(sample(1:10000, 15))
  Date <- c("2016-01-22","2016-07-25", "2016-09-22", "2017-10-22", "2017-01-02",
              "2016-08-22", "2016-09-22", "2016-10-22", "2017-08-22", "2017-09-22", "2017-10-22", "2018-08-22", 
              "2016-10-22","2017-10-25", "2018-10-22")

现在，我想分析每个 ID 的每一年。具体来说，我对 amount 感兴趣。首先，我想知道每年的总金额。然后，我还想知道每年前11个月、每年前10个月、每年前9个月和每年前8个月的总金额。为此，我计算了每个 year 的每个 ID 的 cumSum，如下所示:

  myData <- cbind(ID, amount, Date)
  myData <- as.data.table(myData)

  # createe cumsum per ID per Year
  myData$Date <- as.Date(myData$Date, format = "%Y-%m-%d")
  myData[order(clientID, clDate)]
  myData[, CumSum := cumsum(amount), by =.(ID, year(Date))]

如何汇总 data.table 以便我得到列 amount9month、amount10month、amount11month 每年的每个 ID？

Answer 1

在 cumsum、by 和 dcast 之间，这几乎非常简单。最困难的一点是处理那些没有任何数据的月份。因此这个解决方案并不像它几乎一样简短，但它确实以“data.table 方式”做事并避免了缓慢的操作，如循环遍历行。

# Just sort the formatting out first
myData[, Date:=as.Date(Date)]
myData[, `:=`(amount = as.numeric(amount),
              year = year(Date),
              month = month(Date))]
bycols <- c('ID', 'year', 'month')

# Summarise all transactions for the same ID in the same month
summary <- myData[, .(amt = sum(amount)), by=bycols]

# Create a skeleton table with all possible combinations of ID, year and month, to fill in any gaps.
skeleton <- myData[, CJ(ID, year, month = 1:12, unique = TRUE)]

# Join the skeleton to the actual data, to recreate the data but with no gaps in
result.long <- summary[skeleton, on=bycols, allow.cartesian=TRUE]
result.long[, amt.cum:=cumsum(fcoalesce(amt, 0)), by=c('ID', 'year')]

# Cast the data into wide format to have one column per month
result.wide <- dcast(result.long, ID + year ~ paste0('amount',month,'month'), value.var='amt.cum')

注意。如果您没有 fcoalesce，请更新您的 data.table 包。