I have a data.table in R that was fetched from a database that looks like this:
我在R中有一个data.table，它是从一个如下所示的数据库中获取的：
date,identifier,description,location,value1,value2
2014-03-01,1,foo,1,100,200
2014-03-01,1,foo,2,200,300
2014-04-01,1,foo,1,100,200
2014-04-01,1,foo,2,100,200
2014-05-01,1,foo,1,100,200
2014-05-01,1,foo,2,100,200
2014-03-01,2,bar,1,100,200
2014-04-01,2,bar,1,100,200
2014-05-01,2,bar,1,100,200
2014-03-01,3,baz,1,100,200
2014-03-01,3,baz,2,200,300
2014-04-01,3,baz,1,100,200
2014-04-01,3,baz,2,100,200
2014-05-01,3,baz,1,100,200
2014-05-01,3,baz,2,100,200
2014-05-01,4,quux,2,100,200
<SNIP>
In order to do some calculations on the data, I'd like to massage it so that each combination of date, identifier, description and location has a row in the table with NA as value1 and value2. I know the range of date and all potential values for location.
为了对数据进行一些计算，我想按摩它，以便日期，标识符，描述和位置的每个组合在表中有一行，其中NA为value1和value2。我知道日期的范围和所有可能的位置值。
I'm new to both R and data.table and my mind is mush at this point. The result I'd like to come up with for the above sample table is:
我是R和data.table的新手，我的思绪在这一点上很难。我想为上面的示例表提出的结果是：
date,identifier,description,location,value1,value2
2014-03-01,1,foo,1,100,200
2014-03-01,1,foo,2,200,300
2014-04-01,1,foo,1,100,200
2014-04-01,1,foo,2,100,200
2014-05-01,1,foo,1,100,200
2014-05-01,1,foo,2,100,200
2014-03-01,2,bar,1,100,200
2014-03-01,2,bar,2,NA,NA
2014-04-01,2,bar,1,100,200
2014-04-01,2,bar,2,NA,NA
2014-05-01,2,bar,1,100,200
2014-05-01,2,bar,2,NA,NA
2014-03-01,3,baz,1,100,200
2014-03-01,3,baz,2,200,300
2014-04-01,3,baz,1,100,200
2014-04-01,3,baz,2,100,200
2014-05-01,3,baz,1,100,200
2014-05-01,3,baz,2,100,200
2014-03-01,4,quux,1,NA,NA
2014-03-01,4,quux,2,NA,NA
2014-04-01,4,quux,1,NA,NA
2014-04-01,4,quux,2,NA,NA
2014-05-01,4,quux,1,NA,NA
2014-05-01,4,quux,2,100,200
The data in the database is sparse in that a given identifier/description/location combination could have any number of entries or none at all for each date. I want to get to for a given date range (e.g., 2014-03-01 through 2014-05-01) each identifier/description and location has a row in the table.
数据库中的数据是稀疏的，因为给定的标识符/描述/位置组合可以具有任何数量的条目或者对于每个日期根本没有条目。我希望在给定的日期范围内（例如，2014-03-01至2014-05-01），每个标识符/描述和位置在表中都有一行。
This seems like something there'd be an interesting data.table trick to do, but I'm blanking.
这似乎是一个有趣的数据。可行的技巧，但我在消隐。
Edit: I did this on a smaller scale for one identifier/description by merging in another datatable but I'm not sure how to do this with the added complexity of multiple identifier/descriptions and locations.
编辑：我通过合并另一个数据表，以较小的比例为一个标识符/描述做了这个，但我不知道如何通过增加多个标识符/描述和位置的复杂性来做到这一点。
Thanks very much for your responses.
非常感谢您的回复。
Here is dput output of the original data that can be readily copied into R:
这是原始数据的输出输出，可以很容易地复制到R中：
structure(list(date = structure(c(1L, 1L, 2L, 2L, 3L, 3L, 1L, 2L, 3L, 1L, 1L, 2L, 2L, 3L, 3L, 3L),
.Label = c("2014-03-01", "2014-04-01", "2014-05-01"), class = "factor"),
identifier = c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 3L, 3L, 3L, 4L),
description = structure(c(3L, 3L, 3L, 3L, 3L, 3L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 4L),
.Label = c("bar", "baz", "foo", "quux"), class = "factor"),
location = c(1L, 2L, 1L, 2L, 1L, 2L, 1L, 1L, 1L, 1L, 2L, 1L, 2L, 1L, 2L, 2L),
value1 = c(100L, 200L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 100L, 200L, 100L, 100L, 100L, 100L, 100L),
value2 = c(200L, 300L, 200L, 200L, 200L, 200L, 200L, 200L, 200L, 200L, 300L, 200L, 200L, 200L, 200L, 200L)),
.Names = c("date", "identifier", "description", "location", "value1", "value2"),
row.names = c(NA, -16L),
class = c("data.table", "data.frame"))
2 个解决方案
#1
4
With help from @akrun and @eddi, here's the idiomatic (?) way:
在@akrun和@eddi的帮助下，这是惯用的（？）方式：
mycols = c("description","date","location")
setkeyv(DT0,mycols)
DT1 <- DT0[J(do.call(CJ,lapply(mycols,function(x)unique(get(x)))))]
# alternately: DT1 <- DT0[DT0[,do.call(CJ,lapply(.SD,unique)),.SDcols=mycols]]
The identifier column is missing for the new rows, but can be filled:
新行缺少标识符列，但可以填充：
setkey(DT1,description)
DT1[unique(DT0[,c("description","identifier"),with=FALSE]),identifier:=i.identifier]
#2
1
If I understand the question rightly - and using just base R, not any special data.table:
如果我理解正确的问题 - 并且只使用基数R，而不是任何特殊的数据。表：
# The fields for whose every permutation we require a row
unique.fields <- c("date", "identifier", "description", "location")
filler <- expand.grid(sapply(unique.fields, function(f) unique(foo[,f])) )
merge(filler, foo, by=unique.fields, all.x=TRUE)
#1
4
With help from @akrun and @eddi, here's the idiomatic (?) way:
在@akrun和@eddi的帮助下，这是惯用的（？）方式：
mycols = c("description","date","location")
setkeyv(DT0,mycols)
DT1 <- DT0[J(do.call(CJ,lapply(mycols,function(x)unique(get(x)))))]
# alternately: DT1 <- DT0[DT0[,do.call(CJ,lapply(.SD,unique)),.SDcols=mycols]]
The identifier column is missing for the new rows, but can be filled:
新行缺少标识符列，但可以填充：
setkey(DT1,description)
DT1[unique(DT0[,c("description","identifier"),with=FALSE]),identifier:=i.identifier]
#2
1
If I understand the question rightly - and using just base R, not any special data.table:
如果我理解正确的问题 - 并且只使用基数R，而不是任何特殊的数据。表：
# The fields for whose every permutation we require a row
unique.fields <- c("date", "identifier", "description", "location")
filler <- expand.grid(sapply(unique.fields, function(f) unique(foo[,f])) )
merge(filler, foo, by=unique.fields, all.x=TRUE)