I currently have a fairly complicated dataset, but I tried to break it down and hope that the problem at hand catches sufficient complexity.
My data looks as follows:
df <- data.frame(c(1,1,1,1,2,2,2,3,3,3), c(3,3,NA,5,0,0,0,7,4,7),
c(TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE,
TRUE, FALSE))
colnames(df) <- c("ID", "Number", "Status")
> df
ID Number Status
1 1 3 TRUE
2 1 3 TRUE
3 1 NA TRUE
4 1 5 FALSE
5 2 0 FALSE
6 2 0 FALSE
7 2 0 FALSE
8 3 7 FALSE
9 3 4 TRUE
10 3 7 FALSE
I am looking at each ID in turn (using dplyr group_by). Whenever the status of an observation is TRUE, I would like to replace "Number" by the subsequent number with the status FALSE. For ID = 1, this would imply the number 5 for all 4 observations.
I have found a workaround, but I am sure there must be an easier solution (using replace?). This is how I proceeded:
library(dplyr)
library(zoo)
# Setting up a new variable that replaces all "unwanted
# numbers by NA
df$newNumber <- NA
df$newNumber[df$Status == FALSE] <- df$Number[df$Status == FALSE]
# Using the zoo function na.locf to replace the Gas
df <- df %>%
group_by(ID) %>%
mutate(Number2 = ifelse(any(Status == TRUE), na.locf(newNumber,
fromLast = TRUE), Number2))
> df
# A tibble: 10 x 5
# Groups: ID [3]
ID Number Status newNumber Number2
<dbl> <dbl> <lgl> <dbl> <dbl>
1 1 3 TRUE NA 5
2 1 3 TRUE NA 5
3 1 NA TRUE NA 5
4 1 5 FALSE 5 5
5 2 0 FALSE 0 0
6 2 0 FALSE 0 0
7 2 0 FALSE 0 0
8 3 7 FALSE 7 7
9 3 4 TRUE NA 7
10 3 7 FALSE 7 7
Thank you very much in advance!