Data.table - 多个表上的左外连接 [英] Data.table - left outer join on multiple tables
问题描述
假设您有
fruit< - data.table(FruitID = c ,Fruit = c(Apple,Banana,Strawberry))
colors< - data.table(ColorID = c(1,2,3,4,5),FruitID = c ,1,1,2,3),Color = c(Red,Yellow,Green,Yellow,Red))
tastes< - data.table(TasteID = c (1,2,3),FruitID = c(1,1,3),Taste = c(Sweeet,Sour,Sweet))
setkey )
setkey(colors,ColorID)
setkey(tastes,TasteID)
fruits
FruitID水果
1:1 Apple
2:2香蕉
3:3草莓
颜色
ColorID FruitID颜色
1:1 1红色
2:2 1黄色
3:3 1绿色
4:4 2黄色
5:5 3红色
tastes
TasteID FruitID Taste
1:1 1 Sweeet
2:2 1 Sour
3:3 3 Sweet
需要对这样的数据执行左外连接。例如,给我所有的水果和他们的颜色要求我写(也许有一个更好的方法?)
setkey (颜色,FruitID)
pre>
result < - colors [fruits,allow.cartesian = TRUE]
setkey(colors,ColorID)
这样一个简单而频繁的任务的三行代码似乎过多,所以我写了一个方法
myLeftJoin
/ p>
myLeftJoin < - function(tbl1,tbl2){
#使用tbl1中的键执行左连接保持来自tbl1的所有行并且只有来自tbl2的匹配行)
oldkey < - key(tbl2)
setkeyv(tbl2,key(tbl1))
result < [tbl1,allow.cartesian = TRUE]
setkeyv(tbl2,oldkey)
return(result)
}
我可以使用像
myLeftJoin ColorID FruitID Color Fruit
1:1 1红苹果
2:2 1黄苹果
3:3 1绿苹果
4:4 2黄香蕉
5: 5 3红草莓
如何扩展此方法,以便我可以传递任何数量的表并得到所有的链接左外连接?像
myLeftJoin(tbl1,...)
例如,我想要的结果
myleftJoin(水果,颜色,口味)
等同于setkey ,FruitID)
setkey(tastes,FruitID)
result< - tastes [colors [fruits,allow.cartesian = TRUE],allow.cartesian = TRUE]
setkey tasteesTasteID)
setkey(颜色,ColorID)
结果
TasteID FruitID Taste ColorID Color Fruit
1:1 1 Sweeet 1 Red Apple
2:2 1酸1红苹果
3:1 1 Sweeet 2黄苹果
4:2 1酸2黄苹果
5:1 1 Sweeet 3绿苹果
6:2 1酸3绿苹果
7:NA 2 NA 4黄香蕉
8:3 3甜5红草莓
也许有一个优雅的解决方案使用data.table包中的方法,我错过了?感谢
(EDIT:修正我的资料中的错误)
解决方案我刚刚在
data.table,v1.9.5中提交了一项新功能
,我们可以使用它们而不用设置键(即,直接指定要加入的列,而不必使用setkey()
first):
这样就简单了:
require(data.table)#v1.9.5 +
fruits [tastes,on =FruitID] [colors,on =FruitID]#无setkey必需
#FruitID Fruit TasteID Taste ColorID Color
#1:1 Apple 1 Sweeet 1 Red
#2:1 Apple 2 Sour 1 Red
#3:1 Apple 1 Sweeet 2黄色
#4:1 Apple 2 Sour 2黄
#5:1苹果1 Sweeet 3绿
#6:1苹果2酸3绿
#7:2 NA NA NA 4黄
#8:3草莓3 Sweet 5 Red
Suppose you have data like
fruits <- data.table(FruitID=c(1,2,3), Fruit=c("Apple", "Banana", "Strawberry")) colors <- data.table(ColorID=c(1,2,3,4,5), FruitID=c(1,1,1,2,3), Color=c("Red","Yellow","Green","Yellow","Red")) tastes <- data.table(TasteID=c(1,2,3), FruitID=c(1,1,3), Taste=c("Sweeet", "Sour", "Sweet")) setkey(fruits, "FruitID") setkey(colors, "ColorID") setkey(tastes, "TasteID") fruits FruitID Fruit 1: 1 Apple 2: 2 Banana 3: 3 Strawberry colors ColorID FruitID Color 1: 1 1 Red 2: 2 1 Yellow 3: 3 1 Green 4: 4 2 Yellow 5: 5 3 Red tastes TasteID FruitID Taste 1: 1 1 Sweeet 2: 2 1 Sour 3: 3 3 Sweet
I typically need to perform left-outer joins on data like this. For instance, "give me all fruits and their colors" requires me to write (and maybe there's a better way?)
setkey(colors, "FruitID") result <- colors[fruits, allow.cartesian=TRUE] setkey(colors, "ColorID")
Three lines of code for such a simple and frequent task seemed excessive, so I wrote a method
myLeftJoin
myLeftJoin <- function(tbl1, tbl2){ # Performs a left join using the key in tbl1 (i.e. keeps all rows from tbl1 and only matching rows from tbl2) oldkey <- key(tbl2) setkeyv(tbl2, key(tbl1)) result <- tbl2[tbl1, allow.cartesian=TRUE] setkeyv(tbl2, oldkey) return(result) }
which I can use like
myLeftJoin(fruits, colors) ColorID FruitID Color Fruit 1: 1 1 Red Apple 2: 2 1 Yellow Apple 3: 3 1 Green Apple 4: 4 2 Yellow Banana 5: 5 3 Red Strawberry
How can I extend this method so that I can pass any number of tables to it and get the chained left outer join of all of them? Something like
myLeftJoin(tbl1, ...)
For instance, I'd like the result of
myleftJoin(fruits, colors, tastes)
to be equivalent tosetkey(colors, "FruitID") setkey(tastes, "FruitID") result <- tastes[colors[fruits, allow.cartesian=TRUE], allow.cartesian=TRUE] setkey(tastes, "TasteID") setkey(colors, "ColorID") result TasteID FruitID Taste ColorID Color Fruit 1: 1 1 Sweeet 1 Red Apple 2: 2 1 Sour 1 Red Apple 3: 1 1 Sweeet 2 Yellow Apple 4: 2 1 Sour 2 Yellow Apple 5: 1 1 Sweeet 3 Green Apple 6: 2 1 Sour 3 Green Apple 7: NA 2 NA 4 Yellow Banana 8: 3 3 Sweet 5 Red Strawberry
Perhaps there's an elegant solution using methods in the data.table package that I missed? Thanks
(EDIT: Fixed a mistake in my data)
解决方案I just committed a new feature in
data.table, v1.9.5
, with which we can join without setting keys (that is, specify the columns to join by directly, without having to usesetkey()
first):With that, this is simply:
require(data.table) # v1.9.5+ fruits[tastes, on="FruitID"][colors, on="FruitID"] # no setkey required # FruitID Fruit TasteID Taste ColorID Color # 1: 1 Apple 1 Sweeet 1 Red # 2: 1 Apple 2 Sour 1 Red # 3: 1 Apple 1 Sweeet 2 Yellow # 4: 1 Apple 2 Sour 2 Yellow # 5: 1 Apple 1 Sweeet 3 Green # 6: 1 Apple 2 Sour 3 Green # 7: 2 NA NA NA 4 Yellow # 8: 3 Strawberry 3 Sweet 5 Red
这篇关于Data.table - 多个表上的左外连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!