问题描述
我正在尝试将R base中的两个data.tables合并为多个setkey,并且它们具有重复的条目.例如
>DT1
ID state Month Day Year
1 IL Jan 3 2013
1 IL Jan 3 2014
1 IL Jan 3 2014
1 IL Jan 10 2014
1 IL Jan 11 2013
1 IL Jan 30 2013
1 IL Jan 30 2013
1 IL Feb 2 2013
1 IL Feb 2 2014
1 IL Feb 3 2013
1 IL Feb 3 2014
>DT2
state Month Day Year Tavg
IL Jan 1 2013 13
IL Jan 2 2013 19
IL Jan 3 2013 22
IL Jan 4 2013 23
IL Jan 5 2013 26
IL Jan 6 2013 24
IL Jan 7 2013 27
IL Jan 8 2013 32
IL Jan 9 2013 36
... ... .. ... ...
... ... .. ... ...
IL Dec 31 2013 33
我想将DT2的"Tavg"值添加到DT1中的相应日期.例如,DT1中所有2013年1月3日的条目都需要在附加列中添加Tavg 13.
我尝试了以下setkey(DT1, state, Month, Day, Year)
,与DT2相同,然后执行Join操作DT1[DT2, nomatch=0, allow.cartesian=TRUE
但这没用
只是帮助了一个朋友(他找不到一个很好的Stack Overflow答案),所以我认为这个问题需要一个更完整的玩具"答案. /p>
这里有几个带有一个不匹配键的简单数据表:
dt1 <- data.table(a = LETTERS[1:5],b=letters[1:5],c=1:5)
dt2 <- data.table(c = LETTERS[c(1:4,6)],b=letters[1:5],a=6:10)
这是几个多个键合并选项:
merge(dt1,dt2,by.x=c("a","b"),by.y=c("c","b")) #Inner Join
merge(dt1,dt2,by.x=c("a","b"),by.y=c("c","b"),all=T) #Outer Join
setkey(dt1,a,b)
setkey(dt2,c,b)
dt2[dt1] #Left Join (if dt1 is the "left" table)
dt1[dt2] #Right Join (if dt1 is the "left" table)
I am trying to join two data.tables in R base don multiple setkeys and which have repeated entries. As an example
>DT1
ID state Month Day Year
1 IL Jan 3 2013
1 IL Jan 3 2014
1 IL Jan 3 2014
1 IL Jan 10 2014
1 IL Jan 11 2013
1 IL Jan 30 2013
1 IL Jan 30 2013
1 IL Feb 2 2013
1 IL Feb 2 2014
1 IL Feb 3 2013
1 IL Feb 3 2014
>DT2
state Month Day Year Tavg
IL Jan 1 2013 13
IL Jan 2 2013 19
IL Jan 3 2013 22
IL Jan 4 2013 23
IL Jan 5 2013 26
IL Jan 6 2013 24
IL Jan 7 2013 27
IL Jan 8 2013 32
IL Jan 9 2013 36
... ... .. ... ...
... ... .. ... ...
IL Dec 31 2013 33
I would like to add the "Tavg" values of DT2 to the corresponding dates in DT1 For example, all entries in DT1 that are on Jan 3 2013 need to have Tavg 13 in an additional column.
I tried the following setkey(DT1, state, Month, Day, Year)
and same for DT2 followed by a Join operation DT1[DT2, nomatch=0, allow.cartesian=TRUE
But it didn't work
Just helped a friend with this (he couldn't find a good Stack Overflow answer) so I figured this question needed a more complete "toy" answer.
Here's a couple of simple data tables with one mismatched key:
dt1 <- data.table(a = LETTERS[1:5],b=letters[1:5],c=1:5)
dt2 <- data.table(c = LETTERS[c(1:4,6)],b=letters[1:5],a=6:10)
And here's several multiple key merge options:
merge(dt1,dt2,by.x=c("a","b"),by.y=c("c","b")) #Inner Join
merge(dt1,dt2,by.x=c("a","b"),by.y=c("c","b"),all=T) #Outer Join
setkey(dt1,a,b)
setkey(dt2,c,b)
dt2[dt1] #Left Join (if dt1 is the "left" table)
dt1[dt2] #Right Join (if dt1 is the "left" table)
这篇关于基于多个键和重复项在R中联接两个data.tables的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!