按组选择第二次最低日期并更改新列

Question

我希望选择每个ID分组的第二个最低日期(如果超过一个日期)，然后将结果突变为一个新列( new )。

   DF<-tibble::tribble(
      ~ID,        ~DATE, ~TYPE,
       1L, "2001-01-01",   "P",
       2L, "2002-02-02",   "P",
       2L, "2005-05-05",   "R",
       3L, "2001-10-10",   "R",
       4L, "2008-08-08",   "P",
       4L, "2010-10-10",   "R"
      )

# A tibble: 6 × 3
     ID DATE       TYPE 
  <int> <date>     <chr>
1     1 2001-01-01 P    
2     2 2002-02-02 P    
3     2 2005-05-05 R    
4     3 2001-10-10 R    
5     4 2008-08-08 P    
6     4 2010-10-10 R  

期望产出：

    ID DATE       TYPE  NEW       
  <int> <date>     <chr> <chr>     
1     1 2001-01-01 P     NA        
2     2 2002-02-02 P     2005-05-05
3     2 2005-05-05 R     2005-05-05
4     3 2001-10-10 R     NA        
5     4 2008-08-08 P     2010-10-10
6     4 2010-10-10 R     2010-10-10

我已经尝试了下面的代码，但是如果只有一个日期，我想不出如何用NA来修改新列。

DF%>%group_by(ID)%>%arrange(DATE)%>%slice_head(n=2)%>%slice_max(order_by=DATE,n=1)%>%pull(DATE)

向你问好，H

Answer 1

如果您的日期是按顺序排列的，则可以在一个组中选择第二个DATE作为DATE[2]。

library(tidyverse)

DF %>%
  mutate(DATE = as.Date(DATE)) %>%
  group_by(ID) %>%
  arrange(ID, DATE) %>%
  mutate(NEW = DATE[2])

输出

     ID DATE       TYPE  NEW       
  <int> <date>     <chr> <date>    
1     1 2001-01-01 P     NA        
2     2 2002-02-02 P     2005-05-05
3     2 2005-05-05 R     2005-05-05
4     3 2001-10-10 R     NA        
5     4 2008-08-08 P     2010-10-10
6     4 2010-10-10 R     2010-10-10

Answer 2

使用ave和order结合案例处理来生成NA's。

transform(DF, NEW=ave(DATE, ID, FUN=\(x) {
  if (NROW(x) == 1)  NA
  else x[order(as.Date(x)) == 2]
  }))
#   ID       DATE TYPE        NEW
# 1  1 2001-01-01    P       <NA>
# 2  2 2002-02-02    P 2005-05-05
# 3  2 2005-05-05    R 2005-05-05
# 4  3 2001-10-10    R       <NA>
# 5  4 2008-08-08    P 2010-10-10
# 6  4 2010-10-10    R 2010-10-10

数据：

DF <- structure(list(ID = c(1L, 2L, 2L, 3L, 4L, 4L), DATE = c("2001-01-01", 
"2002-02-02", "2005-05-05", "2001-10-10", "2008-08-08", "2010-10-10"
), TYPE = c("P", "P", "R", "R", "P", "R")), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -6L))