R函数定位日期间隔并返回特定值?

Question

我是R的新手，这是我的第一个堆栈溢出问题！

我有以下数据框，我想创建一个函数，该函数接受来自tbl_1的"DocDate"，确定它属于tbl_2的间隔，并从新列中分配tbl_2的"Period_Ending"值。

library(tidyverse)
library(lubridate)

tbl_1 <- data.frame(DocDate = as.Date(c("2017-01-03", "2017-02-07", "2017-03-04")),
State1 = c("RI", "CT", "GA"),
MD = c(78, 115, 127),
OD = c(150, 220, 235))
tbl_1
DocDate    State1  MD  OD
1 2017-01-03     RI  78 150
2 2017-02-07     CT 115 220
3 2017-03-04     GA 127 235

tbl_2 <- data.frame(begin = as.Date(c("2017-01-01", "2017-01-29", "2017-02-26")),
Period_Ending = as.Date(c("2017-01-29", "2017-02-26", "2017-03-26")))
tbl_2 <- tbl_2 %>% 
mutate(date_interval = as.interval(x = weeks(4), start = (tbl_2$Period_Ending)))
tbl_2
begin Period_Ending  date_interval
1 2017-01-01    2017-01-29   2017-01-29 UTC--2017-02-26 UTC
2 2017-01-29    2017-02-26   2017-02-26 UTC--2017-03-26 UTC
3 2017-02-26    2017-03-26   2017-03-26 UTC--2017-04-23 UTC

所需的结果是一个如下所示的表：

DocDate       State1  MD  OD    Period_Ending
1 2017-01-03     RI    78  150    2017-01-29
2 2017-02-07     CT   115  220    2017-02-26
3 2017-03-04     GA   127  235    2017-03-26

我有蜜蜂能够使用这样的语句手动执行此操作case_when

mutate(Period_Ending = case_when(
(DocDate > ymd("2017-01-01")) & (DocDate < ymd("2017-01-29")) ~ ymd("2017-01-29"),
(DocDate > ymd("2017-01-29")) & (DocDate < ymd("2017-02-26")) ~ ymd("2017-02-26"),
(DocDate > ymd("2017-02-26")) & (DocDate < ymd("2017-03-26")) ~ ymd("2017-03-26")

，但我希望找到一个自动化的解决方案，并非常感谢您能提供的任何帮助！

Answer 1

library(tidyverse)
# example data
tbl_1 <- data.frame(DocDate = as.Date(c("2017-01-03", "2017-02-07", "2017-03-04")),
State1 = c("RI", "CT", "GA"),
MD = c(78, 115, 127),
OD = c(150, 220, 235))
tbl_2 <- data.frame(begin = as.Date(c("2017-01-01", "2017-01-29", "2017-02-26")),
Period_Ending = as.Date(c("2017-01-29", "2017-02-26", "2017-03-26")))

tbl_2 %>%
mutate(date = map2(begin, Period_Ending , ~seq(.x, .y, "day"))) %>%  # create a sequence of dates (begin to end)
unnest(date) %>%                                                     # save it as column
inner_join(tbl_1, by=c("date" = "DocDate")) %>%                      # join the other dataset
select(DocDate = date, State1, MD, OD, Period_Ending)                # re-ararnge columns

# # A tibble: 3 x 5
#   DocDate    State1    MD    OD Period_Ending
#   <date>     <fct>  <dbl> <dbl> <date>       
# 1 2017-01-03 RI        78   150 2017-01-29   
# 2 2017-02-07 CT       115   220 2017-02-26   
# 3 2017-03-04 GA       127   235 2017-03-26