搜索数据框中最近的日期 [英] Searching for nearest date in data frame
本文介绍了搜索数据框中最近的日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个数据框:
purchases:
time quantity
1: 2013-07-31 03:42:02 30
2: 2013-07-31 03:59:32 30
3: 2013-07-31 04:02:22 28
....
history:
time price
1: 2013-07-31 04:26:46 10
2: 2013-07-31 07:11:01 10
3: 2013-07-31 08:16:36 5
4: 2013-07-31 08:40:03 8
5: 2013-07-31 08:47:56 7
....
我想要什么对采购中的每一行执行:
,查找历史记录中具有最近日期的行(如果可能更小,则购买中的那个)
What I want to do: for each row in 'purchases', look up in 'history' the row with closest date (if possible smaller that the one in 'purchases')
我试图做这样的事情
history <- as.vector(history$time)
对于购买中的每一行:
current.price <- purchases[i,]$time
which(history-current.price)==min(history-current.price)
如果这些值是数字值,但是我不知道如何处理POSIXct类的日期。
This is useful if the values are numeric, but I don't know how to handle theses dates of class POSIXct.
编辑:添加可重复数据
#Reproducible dummy data
p <- read.table(text="
t,quantity
2013-07-31 03:42:02,30
2013-07-31 03:59:32,30
2013-07-31 04:02:22,28",header=TRUE,sep=",")
h <- read.table(text="
t,price
2013-07-31 04:26:46,10
2013-07-31 07:11:01,10
2013-07-31 08:16:36,5
2013-07-31 08:40:03,8
2013-07-31 08:47:56,7",header=TRUE,sep=",")
#Convert to POSIXct
p$t <- as.POSIXct(strptime(p$t, "%Y-%m-%d %H:%M:%S"))
h$t <- as.POSIXct(strptime(h$t, "%Y-%m-%d %H:%M:%S"))
推荐答案
这是一个使用 difftime
。我已经更新了你的例子,以便在历史记录表格的后面加上一些日期。
Here is a solution using difftime
. I have updated your example to have some lines with dates posterior to those in the history table.
#Reproducible dummy data
p <- read.table(text="
t,quantity
2013-07-31 03:42:02,30
2013-07-31 03:59:32,30
2013-07-31 04:02:22,28
2013-07-31 04:40:22,28
2013-07-31 05:50:22,28
2013-07-31 08:40:22,28",header=TRUE,sep=",")
h <- read.table(text="
t,price
2013-07-31 04:10:46,10
2013-07-31 04:35:46,10
2013-07-31 07:11:01,10
2013-07-31 08:16:36,5
2013-07-31 08:40:03,8
2013-07-31 08:47:56,7",header=TRUE,sep=",")
#Convert to POSIXct
p$t <- as.POSIXct(strptime(p$t, "%Y-%m-%d %H:%M:%S"))
h$t <- as.POSIXct(strptime(h$t, "%Y-%m-%d %H:%M:%S"))
get_closest_line_in_history <- function(x, history){
time_diffs <- difftime(x, history)
time_diffs[time_diffs<0] <- NA
res <- which.min(time_diffs)
if (length(res) != 1){
return(NA)
}else{
return(res)
}
}
sapply(p$t, get_closest_line_in_history, h$t)
这篇关于搜索数据框中最近的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文