r - left_join (dplyr) 下一个可用日期

Question

我在“R”中有 2 个数据集。

第一个数据库包含特定日期:

    Value       Date   
#   20          2017-10-19 
#   19          2017-10-23 
#   19          2017-11-03 
#   20          2017-11-10  

第二个包含过去 5 年的股票指数水平

     Date       Index
#    2017-11-10 13.206,35
#    2017-11-03 13.378,96
#    2017-10-25 13.404,58
#    2017-10-19 13.517,98

现在我想通过从第一个数据集“DB”中搜索日期并从第二个数据集“Hist”中添加该日期的正确索引值来合并。

我所做的是使用 left_join 函数:

DB <- left_join(DB, Hist, by = "Date")

问题是第一个数据集中的某些日期是公共(public)假期，而第二个数据集“Hist”中没有可用的数据。所以我有一些“NA”。

  Value   Date         Index
# 20      2017-10-19   13.517,98
# 19      2017-10-23   NA
# 19      2017-11-03   13.378,96
# 20      2017-11-10   13.206,35

我正在寻找的是取下一个可用日期的值而不是添加 NA。

示例:而不是添加 NA 取 2017-10-25 的索引(2 天后)

  Value   Date         Index
# 20      2017-10-19   13.517,98
# 19      2017-10-23   13.404,58
# 19      2017-11-03   13.378,96
# 20      2017-11-10   13.206,35

有没有人有想法。提前致谢!

Answer 1

原始请求

以下是一个选项。它使用 full_join ，然后是 fill函数来估算缺失值。

library(tidyverse)

DB_final <- DB %>%
  full_join(Hist, by = "Date") %>%
  arrange(Date) %>%
  fill(Index, .direction = "up") %>%
  filter(!is.na(Value))
DB_final
#   Value       Date     Index
# 1    20 2017-10-19 13.517,98
# 2    19 2017-10-23 13.404,58
# 3    19 2017-11-03 13.378,96
# 4    20 2017-11-10 13.206,35

但是，用户需要提前知道填充方向( up 或 down)。如果用户不知道，它可能没有用。

根据最近日期估算缺失值

这是另一种选择，我认为它更强大。它将使用 Index 估算缺失值从最近的日期开始。

第 1 步:查找最近的日期

# Collect all dates
Date_vec <- sort(unique(c(DB$Date, Hist$Date)))

# Create a distance matrix based on dates than convert to a data frame
dt <- Date_vec %>%
  dist() %>%
  as.matrix() %>%
  as.data.frame() %>%
  rowid_to_column(var = "ID") %>%
  gather(ID2, Value, -ID) %>%
  mutate(ID2 = as.integer(ID2)) %>%
  filter(ID != ID2) %>%
  arrange(ID, Value) %>%
  group_by(ID) %>%
  slice(1) %>%
  select(-Value)

dt$ID <- Date_vec[dt$ID]
dt$ID2 <- Date_vec[dt$ID2]  

names(dt) <- c("Date1", "Date2")

dt
# # A tibble: 5 x 2
# # Groups:   ID [5]
#       Date1      Date2
#      <date>     <date>
# 1 2017-10-19 2017-10-23
# 2 2017-10-23 2017-10-25
# 3 2017-10-25 2017-10-23
# 4 2017-11-03 2017-11-10
# 5 2017-11-10 2017-11-03

dt显示所有日期中最近的日期。

步骤 2:执行多重连接

加入 DB和 dt ，然后加入 Hist两次基于不同的日期列。

DB2 <- DB %>% left_join(dt, by = c("Date" = "Date1")) 

DB3 <- DB2 %>%
  left_join(Hist, by = "Date") %>%
  left_join(Hist, by = c("Date2" = "Date")) 
DB3
#   Value       Date      Date2   Index.x   Index.y
# 1    20 2017-10-19 2017-10-23 13.517,98      <NA>
# 2    19 2017-10-23 2017-10-25      <NA> 13.404,58
# 3    19 2017-11-03 2017-11-10 13.378,96 13.206,35
# 4    20 2017-11-10 2017-11-03 13.206,35 13.378,96

第 3 步:完成索引

如果 Index.x 中有值, 使用它，否则使用 Index.y 中的值.

DB4 <- DB3 %>% 
  mutate(Index = ifelse(is.na(Index.x), Index.y, Index.x)) %>%
  select(Value, Date, Index)
DB4
#   Value       Date     Index
# 1    20 2017-10-19 13.517,98
# 2    19 2017-10-23 13.404,58
# 3    19 2017-11-03 13.378,96
# 4    20 2017-11-10 13.206,35

DB4是最终的输出。

数据

DB <- structure(list(Value = c(20L, 19L, 19L, 20L), Date = structure(c(17458, 
                                                                       17462, 17473, 17480), class = "Date")), class = "data.frame", .Names = c("Value", 
                                                                                                                                                "Date"), row.names = c(NA, -4L))


Hist <- structure(list(Date = structure(c(17480, 17473, 17464, 17458), class = "Date"), 
                       Index = c("13.206,35", "13.378,96", "13.404,58", "13.517,98"
                       )), class = "data.frame", .Names = c("Date", "Index"), row.names = c(NA, 
                                                                                            -4L))