R条件过滤器

Question

我在 R 中有两个数据框。

发布数据框

Date     Product
2011-01-13   A  
2011-02-15   A  
2011-01-14   B  
2011-02-15   B

案例数据数据框

Date       Product Numberofcases 
2011-01-13 A       50
2011-01-12 A       20
2011-01-11 A       100
2011-01-10 A       120
2011-01-09 A       150
2011-01-08 A       180
2011-01-07 A       200
2011-01-06 A       220
2011-01-23 A       500
2011-01-31 A       450
2011-02-08 A       50
2011-02-09 A       1000
2011-02-10 A       1200
2011-02-11 A       1500
2011-02-12 A       1800
2011-02-13 A       2000
2011-02-14 A       2200
2011-02-15 A       5000
2011-01-31 A       4500
:::
:::
2011-01-15 B       1000

我的要求是对于每个产品发布日期(来自发布数据框)，我应该在发布日期前一周(在 casedata 数据框中)获得相应的总和(案例数)。即，对于产品A和发布日期2011-01-13，它应该是前一周(从2011-01-06到2011-01-13)的所有案例的总和，即，(50+20+100+ 120+150+180+200+220)

Releasedate Product Numberofcasesoneweekpriorrelease
2011-01-13  A       1040
2011-02-15  A       19250
2011-01-14  B       ...
2011-02-15  B       ...

我尝试过的:

beforerelease <- sqldf("select product,release.date_release,sum(numberofcasescreated) as numberofcasesbeforerelease from release left join casedata using (product) where date_case>=weekbeforerelease and date_case<=date_release group by product,date_release") 
finaldf <- merge(beforerelease,afterelease,by=c("monthyear","product"))

我很震惊，但它并没有给我预期的结果。有人可以帮我吗？

Answer 1

使用 current development version 中最近实现的 non-equi 连接功能data.table, v1.9.7，这可以简单地完成(假设所有 Date 列都属于 Date 类):

require(data.table)
setDT(release)[, Date2 := Date-7L]
setDT(casedata)[release, on = .(Product, Date >= Date2, Date <= Date), 
                .(count = sum(Numberofcases)), by = .EACHI]

#    Product       Date       Date count
# 1:       A 2011-01-06 2011-01-13  1040
# 2:       A 2011-02-08 2011-02-15 14750
# 3:       B 2011-01-07 2011-01-14    NA
# 4:       B 2011-02-08 2011-02-15    NA