在确切日期加入 data.table，如果不是，则在最接近的小于日期的情况下加入 [英] Join data.table on exact date or if not the case on the nearest less than date

问题描述

我想使用日期作为连接来连接两个 data.table.

I would like to join two data.tables using the date as join.

嗯，有时我没有完全匹配，在这种情况下，我想找到最近的更少日期.我的问题与这篇关于 SQL 的帖子非常相似:SQL Join on Nearest less than date

Well , sometime i didn't have a exact match and in this case i would like to find the nearest less date. My probleme is very similar to this post about SQL : SQL Join on Nearest less than date

我知道 data.table 语法类似于 SQL，但我无法编写此代码.正确的语法是什么?

I know data.table syntax is analogous to SQL but I can't to code this. What is the correct syntax?

一个简化的例子:

Dt1 
   date      x
1/26/2010 - 10  
1/25/2010 - 9  
1/24/2010 - 9   
1/22/2010 - 7    
1/19/2010 - 11

Dt2
   date
1/26/2010   
1/23/2010   
1/20/2010  

输出

   date     x
1/26/2010 - 10  
1/23/2010 - 7 
1/20/2010 - 11

提前谢谢你.

推荐答案

给你:

library(data.table)

创建数据:

Dt1 <- read.table(text="
date      x
1/26/2010,  10  
1/25/2010,  9  
1/24/2010,  9   
1/22/2010,  7    
1/19/2010,  11", header=TRUE, stringsAsFactors=FALSE)

Dt2 <- read.table(text="
date
1/26/2010   
1/23/2010   
1/20/2010", header=TRUE, stringsAsFactors=FALSE)

转换为data.table，将字符串转换为日期，并设置data.table键:

Convert to data.table, convert strings to dates, and set the data.table key:

Dt1 <- data.table(Dt1)
Dt2 <- data.table(Dt2)

Dt1[, date:=as.Date(date, format=("%m/%d/%Y"))]
Dt2[, date:=as.Date(date, format=("%m/%d/%Y"))]

setkey(Dt1, date)
setkey(Dt2, date)

加入表格，使用 roll=TRUE:

Dt1[Dt2, roll=TRUE]

           date  x
[1,] 2010-01-20 11
[2,] 2010-01-23  7
[3,] 2010-01-26 10

这篇关于在确切日期加入 data.table，如果不是，则在最接近的小于日期的情况下加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！