使用R data.table填充缺失的行

Question

我是一名有用的助手，可以为您进行翻译。以下是需要翻译的内容：

我有一个在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>

为了对数据进行一些计算，我希望将每个日期、标识符、描述和位置的组合都转换成表格中的一行，并将value1和value2的值设为NA。我知道日期的范围以及位置可能的所有取值。

我对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

数据库中的数据是稀疏的，因为对于每个日期，一个给定的标识符/描述/位置组合可能有任意数量的条目或根本没有。我想要在给定日期范围内(例如2014年3月1日到2014年5月1日)，每个标识符/描述和位置在表中都有一行。

这似乎是一个有趣的 data.table 技巧，但我无从下手。

编辑: 对于一个标识符/描述，我曾经在较小的规模上通过合并另一个数据表来完成此操作，但我不确定如何处理多个标识符/描述和位置的复杂性。

非常感谢您的回复。

以下是可以轻松复制到 R 中的原始数据的 dput 输出:

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"))

- Jerry Smithwell

---

rbind(DT0,DT0[,.(value1=NA_integer_,value2=NA_integer_),by=.(date,identifier,description,location)]) 符合您的口头描述，但您给出的输出只更改了 bar 行。 - Frank

我已经更新了问题，并附上了dput输出，以便在R中轻松复制数据。对于我的误解，我很抱歉。 - Jerry Smithwell

@Frank 谢谢，看起来需要进行一些修改。我认为在设置“key”列之后你可能需要进行连接操作。 - akrun

@akrun 顺便说一下，CJ 设置键（key），尽管文档描述的非常奇怪。 （文档没有明确说明其已设置键，而是说该对象获得了“排序”[对应于具有键的对象属性]。） - Frank

@Frank，起初我根据帖子认为问题在于加载数据框架结构。然后，我只是想指出，数据通常应该从文件中加载，因为我觉得你建议使用“粘贴”方法。但现在我明白你的意思了。 - Kamil S Jaron

Answer 1

在@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]]

identifier一列在新行中缺失，但可以填充：

setkey(DT1,description)
DT1[unique(DT0[,c("description","identifier")]),identifier:=i.identifier]

- Frank

---

这是我想到的 DT1[DT0[,c(3,1,4,2,5,6), with=FALSE], c(paste0('value', 1:2), 'identifier') := list(i.value1, i.value2, i.identifier)][, identifier:= identifier[!is.na(identifier)][1L], description][order(identifier, date, description)] - akrun

我有点困惑 - 你是在尝试泛化 DT0[CJ(unique(description), unique(date), unique(location))]，这样你就不必写出 unique，还是有其他事情我没注意到？ - eddi

@Frank 对的。有一段时间提出了一个建议，即支持将数据表作为CJ函数的参数。 使用此问题中的函数，您可以执行以下操作： setkey(DT0，date，identifier，description，location); DT0[CJ.dt(unique(date)，data.table(unique(identifier),unique(description)),unique(location))]; 如果 / when issue 1090 得到实现，常规的 CJ也能够实现这一点。 - eddi

那个最后的观察是非常准确的 - 我从来没有见过 CJ 不和 unique 一起使用。FR added - eddi

@JerrySmithwell 很抱歉，我已经修复了它，现在应该可以正常工作了，使用 i.identifier。为什么我不得不更改它：当合并两个具有相同列名（除了被合并的那些列）的数据表 X[Y] 时，必须使用 i.colname 引用 Y 列。 - Frank

Answer 2

如果我理解问题正确-并且仅使用基本的R语言，没有任何特殊的data.table：

# 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)

- Reign of Error

---

你的 expand.grid 会产生96行。因为标识符和描述是一对一的（baz始终为2等），所以你不想取它们的交叉积。 - Frank