How is it possible to set the column value to the last positive integer? Here is an example:
Here is a dataframe:
id <- c(1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2)
test <- c(5,6,-1,2,3,-1,-2,-3,5,-2,-1,1,1,2,-3,-3,2,3,5,4)
df1 <- data.frame(id,test)
Desired output:
id|test
1 |5
1 |6
1 |6
1 |2
1 |3
1 |3
1 |3
1 |3
1 |5
1 |5
2 |NA
2 |1
2 |1
2 |2
2 |2
2 |2
2 |2
2 |3
2 |5
2 |4
We can replace the negative values to NA and use fill to replace the NA elements with the previous non-NA value for each 'id'
library(dplyr)
library(tidyr)
df1 %>%
mutate(test = replace(test, test < 0, NA)) %>%
group_by(id) %>%
fill(test)
-output
# A tibble: 20 x 2
# Groups: id [2]
# id test
# <dbl> <dbl>
# 1 1 5
# 2 1 6
# 3 1 6
# 4 1 2
# 5 1 3
# 6 1 3
# 7 1 3
# 8 1 3
# 9 1 5
#10 1 5
#11 2 NA
#12 2 1
#13 2 1
#14 2 2
#15 2 2
#16 2 2
#17 2 2
#18 2 3
#19 2 5
#20 2 4
One option using dplyr and purrr could be:
df1 %>%
group_by(id) %>%
mutate(test = map_dbl(.x = seq_along(test),
~ test[max(which(test[1:.x] > 0))]))
id test
<dbl> <dbl>
1 1 5
2 1 6
3 1 6
4 1 2
5 1 3
6 1 3
7 1 3
8 1 3
9 1 5
10 1 5
11 2 NA
12 2 1
13 2 1
14 2 2
15 2 2
16 2 2
17 2 2
18 2 3
19 2 5
20 2 4
Here is a base R option using ave + cumsum
transform(
df1,
test = ave(
replace(test, test < 0, NA),
id,
FUN = function(x) c(x[1], na.omit(x[-1]))[cumsum(!is.na(x)) + ifelse(is.na(x[1]), 1, 0)]
)
)
which gives
id test
1 1 5
2 1 6
3 1 6
4 1 2
5 1 3
6 1 3
7 1 3
8 1 3
9 1 5
10 1 5
11 2 NA
12 2 1
13 2 1
14 2 2
15 2 2
16 2 2
17 2 2
18 2 3
19 2 5
20 2 4