r-如何删除包含两列之间的顺序条件的行

Question

我正试图从数据帧中删除这些因素，但只删除特定日期之后的因素。这里我做了一个玩具示例：

我有一个test数据帧和一个检查数据帧inspec。我想删除var1中出现在inspec中的字母，但只删除inspec中日期之后的行。例如，考虑

> test = data.frame(var1 = c("A", "B", "A", "B", "C","B", "A"), measure = c(6,7,8,6,10,1,0), date = as.Date(c("2021-01-02", "2021-01-03", "2021-01-04", "2021-01-05", "2021-01-06", "2021-01-07",  "2021-01-12")))
> test
var1 measure       date
1    A       6 2021-01-02
2    B       7 2021-01-03
3    A       8 2021-01-04
4    B       6 2021-01-05
5    C      10 2021-01-06
6    B       1 2021-01-07
7    A       0 2021-01-12
> 
> inspec = data.frame(var1 = c("A", "C", "D", "A"), date = as.Date(c("2021-01-03", "2021-01-06", "2021-01-10", "2021-01-12")))
> inspec
var1       date
1    A 2021-01-03
2    C 2021-01-06
3    D 2021-01-10
4    A 2021-01-12

然后，作为结果，我想获得：

> test
var1 measure       date
1    A       6 2021-01-02
2    B       7 2021-01-03
3    B       6 2021-01-05
4    B       1 2021-01-07

注意，仅排除了在inspec数据帧中指示的日期之后检查的var1中的A。如果我不想在inspec日期之前维护var1，我可以使用test= test[!(test$var1 %in% inspec$var1),]

有什么提示我该怎么做吗？

Answer 1

基R

## reduce `inspec` to the earliest date
inspec$date <- as.Date(inspec$date)
tmpinspec <- inspec[ave(as.integer(inspec$date), inspec$var1, FUN = function(z) z == min(z)) > 0,]
tmpinspec
#   var1       date
# 1    A 2021-01-03
# 2    C 2021-01-06
# 3    D 2021-01-10
tmp <- merge(test, tmpinspec, by = "var1", all.x = TRUE, suffixes = c("", ".y"))
tmp
#   var1 measure       date     date.y
# 1    A       6 2021-01-02 2021-01-03
# 2    A       8 2021-01-04 2021-01-03
# 3    A       0 2021-01-12 2021-01-03
# 4    B       7 2021-01-03       <NA>
# 5    B       6 2021-01-05       <NA>
# 6    B       1 2021-01-07       <NA>
# 7    C      10 2021-01-06 2021-01-06
tmp <- tmp[with(tmp, is.na(date.y) | date < date.y),]
# tmp$date.y <- NULL
# tmp
var1 measure       date
# 1    A       6 2021-01-02
# 4    B       7 2021-01-03
# 5    B       6 2021-01-05
# 6    B       1 2021-01-07

dplyr

library(dplyr)
group_by(inspec, var1) %>%
slice_min(date) %>%
left_join(test, ., by = "var1", suffix = c("", ".y")) %>%
filter(is.na(date.y) | date < date.y) %>%
select(-date.y)
#   var1 measure       date
# 1    A       6 2021-01-02
# 2    B       7 2021-01-03
# 3    B       6 2021-01-05
# 4    B       1 2021-01-07

Answer 2

我们可以在var1上加入，并基于dates进行过滤，而数据按var1分组，只保持第一个匹配。见下文；

library(dplyr)
test %>%
left_join(inspec, by = "var1", suffix = c("", ".y")) %>%
group_by(var1) %>% 
filter(is.na(date.y) | date < first(date.y)) %>% 
select(-date.y) %>% 
group_by_all() %>% 
slice(1)
#> # A tibble: 4 x 3
#> # Groups:   var1, measure, date [4]
#>   var1  measure date      
#>   <fct>   <dbl> <date>    
#> 1 A           6 2021-01-02
#> 2 B           1 2021-01-07
#> 3 B           6 2021-01-05
#> 4 B           7 2021-01-03

_{这是r2evans答案的变体}

Answer 3

使用data.table，我们还可以使用合并，然后过滤：

library(data.table)
test <- setDT(test); inspec <- setDT(inspec)
test <- merge(test, inspec, by = "var1", all.x = TRUE, suffixes = c("", ".y"))
test <- test[date < date.y | !is.na(date.y), .(var1, measure, date)]