在确切日期加入data.table,如果不是最近的小于日期的情况 [英] Join data.table on exact date or if not the case on the nearest less than date
问题描述
我想使用日期作为加入,加入两个 data.table
。
我没有一个精确匹配,在这种情况下,我想找到最近的减少日期。我的问题非常类似于关于SQL的这篇文章:
SQL Join on最接近的日期
我知道 data.table
语法与SQL类似, t来编码。正确的语法是什么?
一个简单的例子:
Dt1
日期x
1/26/2010 - 10
1/25/2010 - 9
1/24/2010 - 9
1/22/2010 - 7
1/19/2010 - 11
Dt2
日期
1/26/2010
1/23/2010
1/20 / 2010
输出
日期x
1/26/2010 - 10
1/23/2010 - 7
1/20/2010 - 11
$ c $ 解决方案
这里: library(data.table)
创建数据:
Dt1< - read.table =
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
键:
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
/ p>
Dt1 [Dt2,roll = TRUE]
date x
[1,] 2010 -01-20 11
[2,] 2010-01-23 7
[3,] 2010-01-26 10
I would like to join two data.table
s using the date as join.
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
I know data.table
syntax is analogous to SQL but I can't to code this. What is the correct syntax?
A simplified example :
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
output
date x
1/26/2010 - 10
1/23/2010 - 7
1/20/2010 - 11
Thank you in advance.
解决方案 Here you go:
library(data.table)
Create the data:
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)
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)
Join the tables, using 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屋!