I've this example of longitudinal data. I need to impute 0, 999 or -1 values according to what occurs before.
ID = c(1,1,1,1,1,2,2,2,3,3,3,4,4,4,5,5,5,5,6,6,6,6,6,6,6,6)
Oxy = c(0, 999, 1, 999, 999, 0, 0, 999, 999, 0, 0, -1, 0, 999, 1, 1, -1, 1, 999, -1, 0, -1, 1,0, 999, 0)
Y = c(2010,2011,2012,2013,2014,2011,2012,2013,2010,2011,2012,2010,2011,
2012,2010,2011,2012,2013,2014,2015,2016,2017, 2018,2019,2020, 2021)
Oxy2 = c(0, 999, 1, 1, 1, 0, 0, 999, 999, 0, 0, -1, 0, 999, 1, 1, 1, 1, 999, -1, 0, -1, 1, 1,1,1)
df = data.frame(ID, Oxy, Y, Oxy2)
Basically, I'd like to get Oxy2 from Oxy. I need to keep 999 when previous values of Oxy are 0 or -1, and replace everything else comes after the first 1 appears, considering the group ID over time.
ID Oxy Y Oxy2
1 0 2010 0
1 999 2011 999
1 1 2012 1
1 999 2013 1
1 999 2014 1
2 0 2011 0
2 0 2012 0
2 999 2013 999
3 999 2010 999
3 0 2011 0
3 0 2012 0
4 -1 2010 -1
4 0 2011 0
4 999 2012 999
5 1 2010 1
5 1 2011 1
5 -1 2012 1
5 1 2013 1
6 999 2014 999
6 -1 2015 -1
6 0 2016 0
6 -1 2017 -1
6 1 2018 1
6 0 2019 1
6 999 2020 1
6 0 2021 1
Thanks for your suggestion.
You can use cumsum(Oxy == 1) >= 1 to identify which rows come after the first 1:
df %>%
group_by(ID) %>%
mutate(OxyFilled = ifelse(cumsum(Oxy == 1) >= 1, 1, Oxy))
Output:
# A tibble: 25 x 5
# Groups: ID [6]
ID Oxy Y Oxy2 OxyFilled
<dbl> <dbl> <dbl> <dbl> <dbl>
1 1 0 2010 0 0
2 1 999 2011 999 999
3 1 1 2012 1 1
4 1 999 2013 1 1
5 1 999 2014 1 1
6 2 0 2011 0 0
7 2 0 2012 0 0
8 2 999 2013 999 999
9 3 999 2010 999 999
10 3 0 2011 0 0
# … with 15 more rows