r - 按组查找特定日期后特定值运行的第一行

Question

我有不同熊 ('ID') 在不同位置('位置'；陆地或冰上)的时间数据。这是两个人(A 和 B)的简化版本:

ID <- rep.int(c("A", "B"), times = c(10, 10))
Dates <- c(seq(as.Date("2011-06-11"), as.Date("2011-06-20"), by = "days"),
               seq(as.Date("2011-05-27"), as.Date("2011-06-05"), by="days"))
Position <- c("Land", "Ice", "Land", "Land", "Ice", "Ice", "Land", "Land", "Land", "Land",
              "Land", "Land", "Land", "Ice", "Ice", "Land", "Land", "Land", "Ice", "Ice")

data <- data.frame(ID, Dates, Position)

   ID      Dates Position
1   A 2011-06-11     Land
2   A 2011-06-12      Ice
3   A 2011-06-13     Land
4   A 2011-06-14     Land
5   A 2011-06-15      Ice
6   A 2011-06-16      Ice
7   A 2011-06-17     Land
8   A 2011-06-18     Land
9   A 2011-06-19     Land
10  A 2011-06-20     Land
11  B 2011-05-27     Land
12  B 2011-05-28     Land
13  B 2011-05-29     Land
14  B 2011-05-30      Ice
15  B 2011-05-31      Ice
16  B 2011-06-01     Land
17  B 2011-06-02     Land
18  B 2011-06-03     Land
19  B 2011-06-04      Ice
20  B 2011-06-05      Ice

我想创建一个变量 Arrival，它指示每只熊的登陆日期。我将到达陆地的日期定义为连续三个 Position 在“Land”上运行的第一行的日期。此行应设置为“Arrival”，其他行应设置为 NA。此日期也必须在 5 月 31 日之后。

对于此数据集，到达日期如下所示:

   ID      Dates Position Arrival
1   A 2011-06-11     Land      NA
2   A 2011-06-12      Ice      NA
3   A 2011-06-13     Land      NA
4   A 2011-06-14     Land      NA
5   A 2011-06-15      Ice      NA
6   A 2011-06-16      Ice      NA
7   A 2011-06-17     Land Arrival
8   A 2011-06-18     Land      NA
9   A 2011-06-19     Land      NA
10  A 2011-06-20     Land      NA
11  B 2011-05-27     Land      NA
12  B 2011-05-28     Land      NA
13  B 2011-05-29     Land      NA
14  B 2011-05-30      Ice      NA
15  B 2011-05-31      Ice      NA
16  B 2011-06-01     Land Arrival
17  B 2011-06-02     Land      NA
18  B 2011-06-03     Land      NA
19  B 2011-06-04      Ice      NA
20  B 2011-06-05      Ice      NA

有没有办法让我在 R 中执行此操作，最好使用 dplyr？

Answer 1

为此，我们可以使用 zoo::rollapply。

dplyr

library(dplyr)
data %>%
  group_by(ID) %>%
  mutate(
    Arrival = Dates > "2011-05-31" &
         lag(Position != "Land", default = FALSE) &
         zoo::rollapply(Position == "Land", 3, align = "left", FUN = all, partial = TRUE)
  ) %>%
  ungroup()
# # A tibble: 20 × 4
#    ID    Dates      Position Arrival
#    <chr> <date>     <chr>    <lgl>  
#  1 A     2011-06-11 Land     FALSE  
#  2 A     2011-06-12 Ice      FALSE  
#  3 A     2011-06-13 Land     FALSE  
#  4 A     2011-06-14 Land     FALSE  
#  5 A     2011-06-15 Ice      FALSE  
#  6 A     2011-06-16 Ice      FALSE  
#  7 A     2011-06-17 Land     TRUE   
#  8 A     2011-06-18 Land     FALSE  
#  9 A     2011-06-19 Land     FALSE  
# 10 A     2011-06-20 Land     FALSE  
# 11 B     2011-05-27 Land     FALSE  
# 12 B     2011-05-28 Land     FALSE  
# 13 B     2011-05-29 Land     FALSE  
# 14 B     2011-05-30 Ice      FALSE  
# 15 B     2011-05-31 Ice      FALSE  
# 16 B     2011-06-01 Land     TRUE   
# 17 B     2011-06-02 Land     FALSE  
# 18 B     2011-06-03 Land     FALSE  
# 19 B     2011-06-04 Ice      FALSE  
# 20 B     2011-06-05 Ice      FALSE  

带动物园的 R 基地

data$prevnotland <- ave(
  data$Position != "Land", data$ID, 
  FUN = function(z) c(FALSE, z[-length(z)]))
data$Arrival <- data$prevnotland & ave(
  data$Dates > "2011-05-31" & data$Position == "Land", data$ID,
  FUN = function(z) zoo::rollapply(z, 3, FUN=all, align="left", partial=TRUE))
data
#    ID      Dates Position prevnotland Arrival
# 1   A 2011-06-11     Land       FALSE   FALSE
# 2   A 2011-06-12      Ice       FALSE   FALSE
# 3   A 2011-06-13     Land        TRUE   FALSE
# 4   A 2011-06-14     Land       FALSE   FALSE
# 5   A 2011-06-15      Ice       FALSE   FALSE
# 6   A 2011-06-16      Ice        TRUE   FALSE
# 7   A 2011-06-17     Land        TRUE    TRUE
# 8   A 2011-06-18     Land       FALSE   FALSE
# 9   A 2011-06-19     Land       FALSE   FALSE
# 10  A 2011-06-20     Land       FALSE   FALSE
# 11  B 2011-05-27     Land       FALSE   FALSE
# 12  B 2011-05-28     Land       FALSE   FALSE
# 13  B 2011-05-29     Land       FALSE   FALSE
# 14  B 2011-05-30      Ice       FALSE   FALSE
# 15  B 2011-05-31      Ice        TRUE   FALSE
# 16  B 2011-06-01     Land        TRUE    TRUE
# 17  B 2011-06-02     Land       FALSE   FALSE
# 18  B 2011-06-03     Land       FALSE   FALSE
# 19  B 2011-06-04      Ice       FALSE   FALSE
# 20  B 2011-06-05      Ice        TRUE   FALSE