如何在R中的两个日期之间进行汇总？ [英] How to aggregate between two dates in R?

问题描述

下面是两个表

Table1
Date                   OldPrice   NewPrice
2014-06-12 09:32:56       0          10
2014-06-27 16:13:36       10         12
2014-08-12 22:41:47       12         13

Table2
Date                   Qty
2014-06-15 18:09:23     5
2014-06-19 12:04:29     4
2014-06-22 13:21:34     3
2014-06-29 19:01:22     6
2014-07-01 18:02:33     3
2014-09-29 22:41:47     6

我要以这种方式显示结果

I want to display the result in this manner

Date                   OldPrice   NewPrice    Qty
2014-06-12 09:32:56       0          10        0
2014-06-27 16:13:36       10         12        12
2014-08-12 22:41:47       12         13        15

我使用了命令

for(i in 1:nrow(Table1)){

  startDate = Table1$Date[i]
  endDate = Table1$Date[i+1]


 code=aggregate(list(Table2$Qty),
by=list(Table1$Date, Table1$OldPrice, Table1$NewPrice, Date = Table2$Date > startDate  & Table2$Date <= endDate), FUN=sum)

}

我希望在第一张表中的给定日期之间（即第一和第二个日期之间，第二个和第三个日期之间等等）汇总数量。

I want the quantity to be aggregated between the given dates in first table, i.e between the first and second dates, second and third dates and so on.

在此先感谢！

推荐答案

我们可以使用 data.table p>

We can do a join with data.table

library(data.table)
res <- setDT(df1)[df2, roll = -Inf, on = .(Date)][, .(Qty = sum(Qty)),
           .(OldPrice, NewPrice)][df1, on = .(OldPrice, NewPrice)][is.na(Qty), Qty := 0]
setcolorder(res, c(names(df1), "Qty"))
res
#                   Date OldPrice NewPrice Qty
#1: 2014-06-12 09:32:56        0       10   0
#2: 2014-06-27 16:13:36       10       12  12
#3: 2014-08-12 22:41:47       12       13   9

这篇关于如何在R中的两个日期之间进行汇总？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！