data.table外加入组 [英] data.table outer join by group
问题描述
我试图使用data.table来填补一个大的不平衡的多维面板中缺少的观察。下面是有关我想要的一些注释的数据示例：
I am trying to use data.table to fill missing observations in a large unbalanced multi-dimensional panel that I have. Below is an example of the data with some comments as to what I want:
mydat <- structure(list(fund = c(1, 1, 1, 1, 2, 2, 2, 3, 3), holdingid = c(10,
10, 11, 11, 15, 15, 14, 20, 20), yearqtr = structure(c(2000,
2000.5, 2000, 2000.25, 2000, 2000.75, 2000.25, 2000.25, 2000.5
), class = "yearqtr"), shares = c(20, 25, 30, 30, 34, 34, 4,
8, 10)), .Names = c("fund", "holdingid", "yearqtr", "shares"), row.names = c(NA,
-9L), class = "data.frame")
allqtrs <- structure(c(2000, 2000.25, 2000.5, 2000.75), class = "yearqtr")
#note that there are missing yearqtrs for some fund-holding series
#if a fund-holding series is missing an observation I want to create
#that fund-holding-quarter and fill it with NA
我试图平衡面板与最终目标滞后（或差异）每个基金系列正确地（在数据的不规则性被处理的意义上）。显然，我可以使用zooreg为每个fund-holdingid组和滞后使用这个，但我的数据> 2000万行，我试图写一个更有效的解决方案。感谢您的帮助。
I am trying to balance the panel with the end goal of lagging (or differencing) each fund-holdingid series properly (in the sense that the irregularity of the data is taken care of). Obviously I could use zooreg for each fund-holdingid group and lag using this, but my data is >20 million rows and I am trying to write a more efficient solution. Thanks for the help.
编辑为了澄清一点，我想做一些类似于Oracle SQL的分区外部联接，如此处所示 http：// st-curriculum.oracle.com/obe/db/10g/r2/prod/bidw/outerjoin/outerjoin_otn.htm
EDIT To clarify a bit more I am looking to do something similar to what can be done with Oracle SQL's partition by outer joins as demonstrated here http://st-curriculum.oracle.com/obe/db/10g/r2/prod/bidw/outerjoin/outerjoin_otn.htm
EDIT-2 我在说明中使用了大量的时间序列术语。更具体地说，对于每个基金持有对，我想在allqtrs中对每年的yearqtr进行观察。因此，在这种情况下，由于有3个基金分别有3，2和1持有，在输出中应该有（2 + 2 + 1）* 4总行，因为每个基金持有4个可能的季度。另一个重要的点是坚持是非常多样化的。像expand.grid（unique（基金），unique（holdid），unique（allqtrs））会导致太多的行，因为每个基金只有一小部分可能持有。
EDIT-2 I used a lot of time series terms in the description. To be more specific, for each fund-holding pair I want to have an observation for every yearqtr in allqtrs. So in this case since there are 3 funds with 3, 2, and 1 holdings respectively, there should be (2+2+1)*4 total rows in the output since there are 4 possible quarters for each fund-holding. Another important point is that the holdingids are very diverse. Something like expand.grid(unique(fund),unique(holdingid),unique(allqtrs)) will lead to far too many rows since each fund will only have a small subset of the possible holdings.
推荐答案
发布你所期望的答案总是好的，以避免任何歧义/误传。这是您要找的吗？
It's always good to post the answer you're expecting as well to avoid any ambiguity/miscommunication. Is this what you're looking for?
require(data.table)
dt <- as.data.table(mydat)
setkey(dt, "yearqtr")
dt[, .SD[J(allqtrs)], by = list(fund, holdingid)]
# fund holdingid yearqtr shares
# 1: 1 10 2000.00 20
# 2: 1 10 2000.25 NA
# 3: 1 10 2000.50 25
# 4: 1 10 2000.75 NA
# 5: 1 11 2000.00 30
# 6: 1 11 2000.25 30
# 7: 1 11 2000.50 NA
# 8: 1 11 2000.75 NA
# 9: 2 15 2000.00 34
# 10: 2 15 2000.25 NA
# 11: 2 15 2000.50 NA
# 12: 2 15 2000.75 34
# 13: 2 14 2000.00 NA
# 14: 2 14 2000.25 4
# 15: 2 14 2000.50 NA
# 16: 2 14 2000.75 NA
# 17: 3 20 2000.00 NA
# 18: 3 20 2000.25 8
# 19: 3 20 2000.50 10
# 20: 3 20 2000.75 NA
这篇关于data.table外加入组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
