Here is a small sample of my data
data = data.frame(
Year = c("1994", "1995", "1996", "1997", "1998", "1999", "2000", "2001", "2004", "2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2017", "2017", "2017", "2018"),
RepYear = c("NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", "2", "3", "1", "NA"),
Id = c("A013", "A013", "A013", "A013", "A013", "A013", "A013", "A013", "J633", "J633", "J633", "J633", "J633", "J633", "J633", "J633", "J633", "J633", "J633", "J633", "J633", "J633")
)
Year RepYear Id
1 1994 NA A013
2 1995 NA A013
3 1996 NA A013
4 1997 NA A013
5 1998 NA A013
6 1999 NA A013
7 2000 NA A013
8 2001 NA A013
9 2004 NA J633
10 2006 NA J633
11 2007 NA J633
12 2008 NA J633
13 2009 NA J633
14 2010 NA J633
15 2011 NA J633
16 2012 NA J633
17 2013 NA J633
18 2014 NA J633
19 2017 2 J633
20 2017 3 J633
21 2017 1 J633
22 2018 NA J633
And this is what I would like to acomplish with dplyr::lag
Year RepYear Id PreviousYear
1 1994 NA A013 <NA>
2 1995 NA A013 1994
3 1996 NA A013 1995
4 1997 NA A013 1996
5 1998 NA A013 1997
6 1999 NA A013 1998
7 2000 NA A013 1999
8 2001 NA A013 2000
9 2004 NA J633 <NA>
10 2006 NA J633 2004
11 2007 NA J633 2006
12 2008 NA J633 2007
13 2009 NA J633 2008
14 2010 NA J633 2009
15 2011 NA J633 2010
16 2012 NA J633 2011
17 2013 NA J633 2012
18 2014 NA J633 2013
19 2017 2 J633 2014
20 2017 3 J633 2014
21 2017 1 J633 2014
22 2018 NA J633 2017
The issue is when the year is repeated like in the row 20 and 21 because i want previousyear = 2014 and not previous row 2017
This is what I tried:
data %>% arrange(Id, Year) %>%
group_by(Id) %>%
mutate(PreviousYear = lag(Year, 1)) %>%
mutate(PreviousYear = if_else(Year == lag(Year), lag(PreviousYear, 1), PreviousYear)) %>% # Fix issue created by reapeted year
mutate(PreviousYear = if_else(Year == lag(Year), lag(PreviousYear, 1), PreviousYear)) # idem
but it's extremely clumsy because aparently I need to reapet two time the function mutate to fix the two rows...
Thanks in advance
I.
One way would be to keep only values of Id and Year and then take the lag. You can then join this lagged dataframe to the original one to keep the number of rows same.
library(dplyr)
data %>%
distinct(Id, Year) %>%
group_by(Id) %>%
mutate(prev_year = lag(Year)) %>%
left_join(data, by = c('Year', 'Id'))
# Year Id prev_year RepYear
#1 1994 A013 <NA> NA
#2 1995 A013 1994 NA
#3 1996 A013 1995 NA
#4 1997 A013 1996 NA
#5 1998 A013 1997 NA
#6 1999 A013 1998 NA
#7 2000 A013 1999 NA
#8 2001 A013 2000 NA
#9 2004 J633 <NA> NA
#10 2006 J633 2004 NA
#11 2007 J633 2006 NA
#12 2008 J633 2007 NA
#13 2009 J633 2008 NA
#14 2010 J633 2009 NA
#15 2011 J633 2010 NA
#16 2012 J633 2011 NA
#17 2013 J633 2012 NA
#18 2014 J633 2013 NA
#19 2017 J633 2014 2
#20 2017 J633 2014 3
#21 2017 J633 2014 1
#22 2018 J633 2017 NA