Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to set column value to last positive integer

Tags:

r

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
like image 249
MGJ-123 Avatar asked Jan 25 '23 17:01

MGJ-123


2 Answers

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
like image 181
akrun Avatar answered Jan 29 '23 16:01

akrun


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
like image 42
tmfmnk Avatar answered Jan 29 '23 15:01

tmfmnk