删除匹配行之前和之后的天数

Question

我能够删除两个数据框 df1 和 df2 之间匹配的行，并使用@Eric Fail 提供的一些代码:

df1[!(apply(df1[1:2], 1, toString) %in% apply(df2[1:2], 1, toString)), ]

或者使用 @steveb 的 dplyr 解决方案

df1 %>% 过滤器( !((日期 == df2$date) & (ticker == df2$ticker)) )

但是，我意识到我不仅需要删除共享行，如下所示:

df1 <- data.frame(ticker = c("MSFT", "MSFT", "MSFT", "MSFT"), 
date = c("2016-01-01", "2016-01-02", "2016-01-03", "2016-01-04"), stringsAsFactors=F)
df1

  ticker       date
1   MSFT 2016-01-01
2   MSFT 2016-01-02
3   MSFT 2016-01-03
4   MSFT 2016-01-04

df2 <- data.frame(ticker = c("AAPL", "GOOG", "MSFT", "FB"), 
date = c("2016-01-01", "2016-01-01", "2016-01-02", "2016-01-03"), stringsAsFactors=F)
df2

  ticker       date
1   AAPL 2016-01-01
2   GOOG 2016-01-01
3   MSFT 2016-01-02
4     FB 2016-01-03

df3 

  ticker       date
1   MSFT 2016-01-01
2   MSFT 2016-01-03
3   MSFT 2016-01-04

而且还包括指定行的前一天和后一天。所以我的最终 df 是:

  ticker       date
1   MSFT 2016-01-04

注意，3 MSFT 2016-01-02 是匹配项，因此需要删除该行以及前一天和后一天，3 MSFT 2016-01-01 和 3 MSFT 2016-01-03

有两个匹配的示例:

df1 <- data.frame(ticker = c("MSFT", "MSFT", "MSFT", "MSFT"),
                  date = as.Date(c("2016-01-01", "2016-01-02", "2016-01-03", "2016-01-04")),
                  stringsAsFactors=F)
df2 <- data.frame(ticker = c("AAPL", "GOOG", "MSFT", "MSFT"),
                  date = as.Date(c("2016-01-01", "2016-01-01", "2016-01-01","2016-01-02")),
                  stringsAsFactors=F)

目标输出:

ticker       date
4   MSFT 2016-01-04

Answer 1

您可以将字符串转换为日期，以便添加和减去天数

df1 <- data.frame(ticker = c("MSFT", "MSFT", "MSFT", "MSFT"),
                  date = as.Date(c("2016-01-01", "2016-01-02", "2016-01-03", "2016-01-04")),
                  stringsAsFactors=F)
df2 <- data.frame(ticker = c("AAPL", "GOOG", "MSFT", "FB"),
                  date = as.Date(c("2016-01-01", "2016-01-01", "2016-01-02", "2016-01-03")),
                  stringsAsFactors=F)


(m <- df2[(df2$date %in% df1$date) & (df2$ticker %in% df1$ticker), ])
#   ticker       date
# 3   MSFT 2016-01-02

df1[!(df1$date %in% (m$date + c(-1,0,1))), ]

#   ticker       date
# 4   MSFT 2016-01-04

编辑 - 对于多个匹配项，只需在每个日期应用function(x)

df1 <- data.frame(ticker = c("MSFT", "MSFT", "MSFT", "MSFT"),
                  date = as.Date(c("2016-01-01", "2016-01-02", "2016-01-03", "2016-01-04")),
                  stringsAsFactors=F)
df2 <- data.frame(ticker = c("AAPL", "GOOG", "MSFT", "MSFT"),
                  date = as.Date(c("2016-01-01", "2016-01-01", "2016-01-01","2016-01-02")),
                  stringsAsFactors=F)

(m <- df2[(df2$date %in% df1$date) & (df2$ticker %in% df1$ticker), ])
#   ticker       date
# 3   MSFT 2016-01-01
# 4   MSFT 2016-01-02

df1[!(df1$date %in% (sapply(m$date, function(x) x + c(-1,0,1)))), ]
#   ticker       date
# 4   MSFT 2016-01-04