Conditionally update the value in column using lag value

July 18, 2022

I am trying to update the value in a RAIN column in following dataframe using ifelse condition. If value of RAIN is less than value of RAIN from previous row then replace that value with value from previous row. The sample dataframe is as below:

TRGCODE    gr      DATE      TIME     RAIN
   12      24   2017-03-15   1645     00
   12      24   2017-03-15   1700     00
   12      24   2017-03-15   1715     0.5
   12      24   2017-03-15   1730     00
   12      24   2017-03-15   1745     0.5
   12      24   2017-03-15   1800     0.5
   12      24   2017-03-15   1815     00
   12      24   2017-03-15   1830     00
   12      24   2017-03-15   1845     0.5
   12      24   2017-03-15   1900     00
   12      24   2017-03-15   1915     00
   12      24   2017-03-15   1930     0.5
   12      24   2017-03-15   1945     0.5

I tried with

df <- df %>% group_by(TRGCODE, gr) %>% dplyr::mutate(RAIN = ifelse(RAIN < lag(RAIN, default = first(RAIN)), lag(RAIN), RAIN))

But it’s not the working the way I want (It will change value only for time 1730, 1815, 1900 and not for 1830 & 1915). The desired output is like:

TRGCODE    gr      DATE      TIME     RAIN
   12      24   2017-03-15   1645     00
   12      24   2017-03-15   1700     00
   12      24   2017-03-15   1715     0.5
   12      24   2017-03-15   1730     0.5
   12      24   2017-03-15   1745     0.5
   12      24   2017-03-15   1800     0.5
   12      24   2017-03-15   1815     0.5
   12      24   2017-03-15   1830     0.5
   12      24   2017-03-15   1845     0.5
   12      24   2017-03-15   1900     0.5
   12      24   2017-03-15   1915     0.5
   12      24   2017-03-15   1930     0.5
   12      24   2017-03-15   1945     0.5

>Solution :

I’m not sure I’ve understood your question properly, but does this solve your problem?

library(tidyverse)

df <- read.table(text = "TRGCODE    gr      DATE      TIME     RAIN
   12      24   2017-03-15   1645     00
   12      24   2017-03-15   1700     00
   12      24   2017-03-15   1715     0.5
   12      24   2017-03-15   1730     00
   12      24   2017-03-15   1745     0.5
   12      24   2017-03-15   1800     0.5
   12      24   2017-03-15   1815     00
   12      24   2017-03-15   1830     00
   12      24   2017-03-15   1845     0.5
   12      24   2017-03-15   1900     00
   12      24   2017-03-15   1915     00
   12      24   2017-03-15   1930     0.5
   12      24   2017-03-15   1945     0.5",
   header = TRUE)

df %>%
  group_by(TRGCODE, gr) %>%
  mutate(RAIN = cummax(RAIN))
#> # A tibble: 13 × 5
#> # Groups:   TRGCODE, gr [1]
#>    TRGCODE    gr DATE        TIME  RAIN
#>      <int> <int> <chr>      <int> <dbl>
#>  1      12    24 2017-03-15  1645   0  
#>  2      12    24 2017-03-15  1700   0  
#>  3      12    24 2017-03-15  1715   0.5
#>  4      12    24 2017-03-15  1730   0.5
#>  5      12    24 2017-03-15  1745   0.5
#>  6      12    24 2017-03-15  1800   0.5
#>  7      12    24 2017-03-15  1815   0.5
#>  8      12    24 2017-03-15  1830   0.5
#>  9      12    24 2017-03-15  1845   0.5
#> 10      12    24 2017-03-15  1900   0.5
#> 11      12    24 2017-03-15  1915   0.5
#> 12      12    24 2017-03-15  1930   0.5
#> 13      12    24 2017-03-15  1945   0.5

^{Created on 2022-07-18 by the reprex package (v2.0.1)}