Agfdhyk

I have a table like this

  Id   A    B     

  1  11   event1  

  2  12   event2   

  3  00   event1

  4  00   event2

  5  11   event1

  6  00   event2

  7  00   event3

  8  13   event1

I want to change 00 from the previous values. The output will look like this

  Id  A   B 

  1  11   event1

  2  12   event2

  3  12   event1

  4  12   event2

  5  11   event1

  6  11   event2

  7  11   event3

  8  13   event1

Is there a way to do this in R or MySQL.
Thanks

You can use correlated subquery in MySQL :

SELECT t.id, 

       (CASE WHEN A <> '00' 

             THEN A

             ELSE (SELECT t1.A

                   FROM table t1

                   WHERE t1.id < t.id AND t1.A <> '00'

                   ORDER BY t1.id DESC

                   LIMIT 1

                  )

        END) AS A, B

FROM table t;

Here is a one-liner to do this in R. Basically, we replace 0s with NA and use zoo::na.locf() to fill with the last non-NA value, i.e.

transform(d1, A = zoo::na.locf(replace(A, A == 0, NA)))

which gives,

  Id  A      B

1  1 11 event1

2  2 12 event2

3  3 12 event1

4  4 12 event2

5  5 11 event1

6  6 11 event2

7  7 11 event3

8  8 13 event1

As @G. Grothendieck notes, your A variable seems to be a character. If that's the case then,

transform(d1, A = na.locf0(replace(A, A == "00", NA)))

In R you can do this with the tidyr function fill. First change all the 0 to NA with mutate, then the fill function will replace it with the last value.

library(dplyr)

library(tidyr)

df %>% mutate(A = ifelse(A==0,NA,A)) %>% fill(A)

#  Id  A      B

#1  1 11 event1

#2  2 12 event2

#3  3 12 event1

#4  4 12 event2

#5  5 11 event1

#6  6 11 event2

#7  7 11 event3

#8  8 13 event1