我有一个数据框df.sample
像这样
id <- c("A","A","A","A","A","A","A","A","A","A","A")
date <- c("2018-11-12","2018-11-12","2018-11-12","2018-11-12","2018-11-12",
"2018-11-12","2018-11-12","2018-11-14","2018-11-14","2018-11-14",
"2018-11-12")
hour <- c(8,8,9,9,13,13,16,6,7,19,7)
min <- c(47,59,6,18,22,36,12,32,12,21,47)
value <- c(70,70,86,86,86,74,81,77,79,83,91)
df.sample <- data.frame(id,date,hour,min,value,stringsAsFactors = F)
df.sample$date <- as.Date(df.sample$date,format="%Y-%m-%d")
我有另一个数据框df.state
像这样
id <- c("A","A","A")
starttime <- c("2018-11-12 08:59:00","2018-11-14 06:24:17","2018-11-15 09:17:00")
endtime <- c("2018-11-12 15:57:00","2018-11-14 17:22:16","2018-11-15 12:17:32")
state <- c("Pass","Pass","Pass")
df.state <- data.frame(id,starttime,endtime,state,stringsAsFactors = F)
df.state$starttime <- as.POSIXct(df.state$starttime,format="%Y-%m-%d %H:%M:%S")
df.state$endtime <- as.POSIXct(df.state$endtime,format="%Y-%m-%d %H:%M:%S")
我正在尝试根据条件合并这两个数据框
if the hour
and min
in df.sample
是在starttime
and endtime
of df.state
,然后合并state = Pass
in the df.sample
.
例如,第 2 行df.sample
has hour = 8
, min = 59
并且由于它在starttime = 2018-11-12 08:59:00
in df.state
, 价值Pass
被添加
这是我的期望的输出
id date hour min value state
A 2018-11-12 8 47 70
A 2018-11-12 8 59 70 Pass
A 2018-11-12 9 6 86 Pass
A 2018-11-12 9 18 86 Pass
A 2018-11-12 13 22 86 Pass
A 2018-11-12 13 36 74 Pass
A 2018-11-12 16 12 81
A 2018-11-14 6 32 77 Pass
A 2018-11-14 7 12 79 Pass
A 2018-11-14 19 21 83
A 2018-11-12 7 47 91
我能够像这样合并这两个数据帧,但无法在 df.state 的开始时间和结束时间中查找 df.sample 的小时和分钟
library(tidyverse)
df.sample <- df.sample %>%
left_join(df.state)
有人能指出我正确的方向吗