检查一个数据框中的日期是否在另一数据框中的日期范围内，并在为true时返回行 [英] Check date in one dataframe is in date range in another dataframe and return rows when true
问题描述
if (!require("pacman")) install.packages("pacman")
pacman::p_load(tidyverse, lubridate)
# Example of sample dates - these are to be used to cross check if date exists within the range
Sample.Dates = tibble(
ID = "ID",
Round = 1:3,
Start.Date = dmy(c("03/12/2018","10/12/2018","17/12/2018")),
End.Date = dmy(c("09/12/2018","16/12/2018","23/12/2018")))
# Reference dates for a particular player - "John". Need to cross check the date against Sample.Dates and attach round, start and end date columns
Ref.Dates = tibble(
ID= "ID",
Date = seq.Date(ymd("2018-12-05"), ymd("2018-12-31") , by = "day"),
Player = "John",
Rows = row_number(Date))
# Function for checking if date exists within range and then returns the round, start and end date values
Dates.Check.YN.Func = function(x){
Date = x %>% pull(Date)
Cross.Check = Sample.Dates %>% rowwise()%>%
dplyr::mutate(Match = ifelse(between(Date, Start.Date, End.Date),1,0))%>%
filter(Match == 1)%>%
ungroup()%>%
select(-Match)
left_join(x, Cross.Check, by = "ID")
}
# Applying function to each row/date using nest()
Data.Nest = Ref.Dates %>%
nest(-Rows)%>%
mutate(out = map(data,Dates.Check.YN.Func)) %>%
unnest(out) %>%
select(-data)
现在此代码可以正常工作了.但是，这只是一个虚拟数据集，实际上我想对100,000个日期进行交叉检查.使用我的真实数据集进行此操作大约需要30分钟.搜索以查看是否有人可以使用tidyverse解决方案(首选)或其他方式来加快我的代码的速度.
Now this code works with no problems. However this is just a dummy data set and in actual fact I want to cross check over 100,000 dates. When doing this with my real data set this takes ~30mins. Searching to see if anyone can see a way of speeding up my code using a tidyverse solution (preferred) or other means.
推荐答案
从v1.9.8版本开始(2016年11月25日，CRAN)， data.table 已具有执行的能力.非装备联接.
As of version v1.9.8 (on CRAN 25 Nov 2016), data.table has gained the ability to perform non-equi joins.
在这里，使用非设备更新联接来附加 Round ， Start.Date 和 End列.日期从 Sample.Dates 到 Ref.Dates . Ref.Dates 通过引用进行更新，即无需复制整个数据对象.
Here, a non-equi update join is used to append the columns Round, Start.Date, and End.Date from Sample.Dates to Ref.Dates. Ref.Dates is updated by reference, i.e., without copying the whole data object.
library(data.table)
# coerce to data.table class
setDT(Ref.Dates)[
# perform update join
setDT(Sample.Dates), on = .(ID, Date >= Start.Date, Date <= End.Date),
`:=`(Round = Round, Start.Date = Start.Date, End.Date = End.Date)]
Ref.Dates
ID Date Player Rows Round Start.Date End.Date
1: ID 2018-12-05 John 1 1 2018-12-03 2018-12-09
2: ID 2018-12-06 John 2 1 2018-12-03 2018-12-09
3: ID 2018-12-07 John 3 1 2018-12-03 2018-12-09
4: ID 2018-12-08 John 4 1 2018-12-03 2018-12-09
5: ID 2018-12-09 John 5 1 2018-12-03 2018-12-09
6: ID 2018-12-10 John 6 2 2018-12-10 2018-12-16
7: ID 2018-12-11 John 7 2 2018-12-10 2018-12-16
8: ID 2018-12-12 John 8 2 2018-12-10 2018-12-16
9: ID 2018-12-13 John 9 2 2018-12-10 2018-12-16
10: ID 2018-12-14 John 10 2 2018-12-10 2018-12-16
11: ID 2018-12-15 John 11 2 2018-12-10 2018-12-16
12: ID 2018-12-16 John 12 2 2018-12-10 2018-12-16
13: ID 2018-12-17 John 13 3 2018-12-17 2018-12-23
14: ID 2018-12-18 John 14 3 2018-12-17 2018-12-23
15: ID 2018-12-19 John 15 3 2018-12-17 2018-12-23
16: ID 2018-12-20 John 16 3 2018-12-17 2018-12-23
17: ID 2018-12-21 John 17 3 2018-12-17 2018-12-23
18: ID 2018-12-22 John 18 3 2018-12-17 2018-12-23
19: ID 2018-12-23 John 19 3 2018-12-17 2018-12-23
20: ID 2018-12-24 John 20 NA <NA> <NA>
21: ID 2018-12-25 John 21 NA <NA> <NA>
22: ID 2018-12-26 John 22 NA <NA> <NA>
23: ID 2018-12-27 John 23 NA <NA> <NA>
24: ID 2018-12-28 John 24 NA <NA> <NA>
25: ID 2018-12-29 John 25 NA <NA> <NA>
26: ID 2018-12-30 John 26 NA <NA> <NA>
27: ID 2018-12-31 John 27 NA <NA> <NA>
ID Date Player Rows Round Start.Date End.Date
这篇关于检查一个数据框中的日期是否在另一数据框中的日期范围内，并在为true时返回行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
