Assign a value to previous rows based on a condition in a group using R

Question

I have a data frame, sorted by groupID and date

> d1 <- data.frame(groupID = c(1,1,1,1,1,3,3,3,3), 
             date = c(1,2,3,4,5,6,7,8,9),
             value = c(1,1,25,1,1,25,1,25,1))

 >d1

 groupID date value
   1    1     1
   1    2     1
   1    3    25
   1    4     1
   1    5     1
   3    6    25
   3    7     1
   3    8    25
   3    9     1

I want to create a new column for each group with dates of the latest 25 value.

I intent to see the following output

   groupID date value newvalue
       1    1     1     3
       1    2     1     3
       1    3    25     3
       1    4     1     NA  
       1    5     1     NA
       3    6    25     6
       3    7     1     8
       3    8    25     8
       3    9     1     NA 

I have tried Assign value to group based on condition in column and if-else, but I have been unsuccessful with my attempts.

Answer 1

You can use dplyr/tidyr to do this

library(dplyr)
library(tidyr)

df<- data.frame(groupID = c(1,1,1,1,1,3,3,3,3),
            date = c(1:9),
            value = c(1,1,25,1,1,25,1,25,1))

df <- df %>%
      group_by(groupID) %>%
      mutate(newvalue = ifelse(value == 25,date,NA)) %>%
      fill(newvalue, .direction = "up")