data.table merge()，NA在按列中 [英] data.table merge() with NA in by column

问题描述

我正在尝试联接两个表，其中联接的列具有一些NA值，以便在遇到NA时，用NA填充记录，即

I'm trying to join two tables where the column that is joined on has some NA values such that when the NA is encountered the record is padded with NA's i.e.

给出:

> x = data.table(c(1,2,3,NA,5), c("a","b","c","d","e"))
> x
   V1 V2
1:  1  a
2:  2  b
3:  3  c
4: NA  d
5:  5  e

> y = data.table(c(NA,2,3,4,5), c("A","B","C","D","E"))
> y
   V1 V2
1: NA  A
2:  2  B
3:  3  C
4:  4  D
5:  5  E

我希望我的输出是:

> z = data.table(c(NA,NA,1,2,3,4,5),c("d",NA,"a","b","c",NA,"e"),c(NA,"A",NA,"B","C","D","E"))
> z
   V1 V2 V3
1: NA  d NA
2: NA NA  A
3:  1  a NA
4:  2  b  B
5:  3  c  C
6:  4 NA  D
7:  5  e  E

我认为 merge()可以用来做到这一点.但是我无法获得期望的输出结果

I thought merge() could be used to do this. But I can't get it to produce the output I expect:

> merge(x,y, by=c("V1"), all=TRUE)
   V1 V2.x V2.y
1: NA    d    A
2:  1    a   NA
3:  2    b    B
4:  3    c    C
5:  4   NA    D
6:  5    e    E 

我真的不喜欢它基于NA值进行合并，就好像它是一个匹配项，并且当我在具有多个NA的较大表中进行此操作时，似乎遍历了V1列值的所有可能组合和V2提供了NA键.任何帮助将不胜感激.

I really don't like that it merges based on the NA value as if it was a match, and when I do this in a larger table with several NA's, it seems to iterate over all possible combinations of column values for V1 and V2 given an NA key. Any help would be appreciated.

推荐答案

merge 的数据框方法具有 incomparables 参数，其中

The dataframe method of merge has a incomparables-argument, which the data.table version of merge doesn't have.

因此，使用dataframe方法:

So, using the dataframe method:

merge.data.frame(x, y, by = "V1", all = TRUE, incomparables = NA)

达到预期的结果:

  V1 V2.x V2.y
1  1    a <NA>
2  2    b    B
3  3    c    C
4  4 <NA>    D
5  5    e    E
6 NA    d <NA>
7 NA <NA>    A

---

_注意:根据该GitHub问题，开发人员计划 data.table 的问题将来在 merge.data.table 中包含一个 incomparables 参数.

---

_{NOTE: According to this GitHub-issue, the data.table developers are planning to include an incomparables-argument in merge.data.table in the future.}

这篇关于data.table merge()，NA在按列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！