Create new var by matching two columns-CodePudding

I want to take the year at type= yes but if there are more than one Yes with in a group id, I want to take the year when last Yes observed and creat new var called year2.

data<-data.frame(id=c(1,1,1, 1, 1,2,2,2,2,2,3,3,3, 3,4,4,4),
                 year=c(2003,2004,2005,2005,2006,2004,2005,2006,2006,2000,
                        2004,2008,2002,2008,2009,2003,2004),
                   y=c(NA, NA,"yes","No","No",NA, NA, "No","yes","yes",NA,
                       NA,"yes", "No", NA, NA, "yes"))

The desired output is loke like:

   id   year  type year2
   1    2003  NA   2005
   1    2004  NA   2005
   1    2005  yes  2005
   1    2005  No   2005
   1    2006  No   2005
   2    2004  NA   2000
   2    2005  NA   2000
   2    2006  No   2000
   2    2006  yes  2000
   2    2000  yes  2000
   3    2004  NA   2004
   3    2008  NA   2002
   3    2002  yes  2002
   3    2008  No   2002
   4    2009  NA   2004
   4    2003  NA   2004
   4    2004  yes  2004

I try it using

library(dplyr)
data <- data %>%
  group_by(id) %>% mutate(year2 = dplyr::last(type=="Yes"& year))

CodePudding user response：

data %>%
  group_by(id )%>%
  mutate(yesr2 = last(year[y == 'yes']))
# A tibble: 17 x 4
# Groups:   id [4]
      id  year y     yesr2
   <dbl> <dbl> <chr> <dbl>
 1     1  2003 NA     2005
 2     1  2004 NA     2005
 3     1  2005 yes    2005
 4     1  2005 No     2005
 5     1  2006 No     2005
 6     2  2004 NA     2000
 7     2  2005 NA     2000
 8     2  2006 No     2000
 9     2  2006 yes    2000
10     2  2000 yes    2000
11     3  2004 NA     2002
12     3  2008 NA     2002
13     3  2002 yes    2002
14     3  2008 No     2002
15     4  2009 NA     2004
16     4  2003 NA     2004
17     4  2004 yes    2004