R合并三个数据框而不形成笛卡尔积

Question

我有以下数据框 a,b,c

Year<-rep(c("2002","2003"),1)
Crop<-c("TTT","RRR")
a<-data.frame(Year,Crop)

Year<-rep(c("2002","2003"),2)
ProductB<-c("A","A","B","B")
b<-data.frame(Year,ProductB)

Year<-rep(c("2002","2003"),3)
Location<-c("XX","XX","YY","YY","ZZ","ZZ")
c<-data.frame(Year,Location)

并且想让他们聚在一起。当我使用 merge函数我得到了不是我想要的笛卡尔积。

d<-merge(a,b,by="Year")
e<-merge(d,c,by="Year")

我希望数据框看起来像

Year   Crop    ProductB    Location
 2002  TTT      A              XX
 2002   NA      B              YY
 2002   NA      NA             ZZ
 2003  RRR      A              XX 
 2003   NA      B              YY
 2003   NA      NA             ZZ

这可能吗？谢谢你的帮助

Answer 1

这是使用 data.table 的一种方法.

require(data.table) ## 1.9.2
# (1)
setDT(a)[, GRP := 1:.N, by=Year]
setDT(b)[, GRP := 1:.N, by=Year]
setDT(c)[, GRP := 1:.N, by=Year]
# (2)
merge(a, merge(b, c, by=c("Year", "GRP"), 
          all=TRUE), by=c("Year", "GRP"), all=TRUE)

#    Year GRP Crop ProductB Location
# 1: 2002   1  TTT        A       XX
# 2: 2002   2   NA        B       YY
# 3: 2002   3   NA       NA       ZZ
# 4: 2003   1  RRR        A       XX
# 5: 2003   2   NA        B       YY
# 6: 2003   3   NA       NA       ZZ

• (1) - setDT converts the data.frame to data.table and then we create a new column GRP by grouping by Year. With this, we've a unique combination of Year, Grp.
• (2) - we merge on the two columns Year, GRP.

.N是一个内置变量，用于保存该组的行数。