基于多个键和重复条目加入 R 中的两个 data.tables

问题描述

我试图在 R 基础中加入两个 data.tables 并使用多个 setkeys 并且它们有重复的条目.举个例子

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

我想将 DT2 的Tavg"值添加到 DT1 中的相应日期.例如，DT1 中所有 2013 年 1 月 3 日的条目都需要在附加列中包含 Tavg 13.

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.

我尝试了以下方法setkey(DT1, state, Month, Day, Year) 与 DT2 相同，然后是 Join 操作DT1[DT2, nomatch=0,allow.cartesian=TRUE但是没用

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=TRUEBut it didn't work

推荐答案

刚刚帮助一位朋友解决了这个问题(他没有找到一个好的 Stack Overflow 答案)，所以我认为这个问题需要一个更完整的玩具"答案.

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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！