rdataframereplacenarowwise

replace values of a row with values from another row


wave <-c(1, 2, 3, 1, 2, 3, 1, 2, 1, 2)

id   <-c(1, 1, 1, 2, 2, 2, 3, 3, 4, 4)

var1 <-c(6.341464, 13.206567, NA, 5.272296,2.746650, NA, 1.684472,1.874200, 15.861959, 3.938463)

var2 <-c(6.341464, 13.206567, NA, 5.272296,2.746650, NA, 1.684472,1.874200, 15.861959, 3.938463)

var3 <-c(6.341464, 13.206567, NA, 5.272296,2.746650, NA, 1.684472,1.874200, 15.861959, 3.938463)


df<- data.frame(wave,id,var1,var2,var3)

Output:df

enter image description here

How can I replace NA's in wave 3 with values from wave 2? it should copy all the values from second wave (NA too)

I tried for loop but without success!


Solution

  • Another option with Base R, could be using which to find the row indexes of wave 3 and use -1 index to replace the values like this:

    df[which(wave == '3'),c("var1","var2","var3")] = df[which(wave == '3')-1,c("var1","var2","var3")]
    df
    #>    wave id      var1      var2      var3
    #> 1     1  1  6.341464  6.341464  6.341464
    #> 2     2  2 13.206567 13.206567        NA
    #> 3     3  3 13.206567 13.206567        NA
    #> 4     1  4  5.272296  5.272296  5.272296
    #> 5     2  5  2.746650  2.746650  2.746650
    #> 6     3  6  2.746650  2.746650  2.746650
    #> 7     1  7  1.684472  1.684472  1.684472
    #> 8     2  8  1.874200  1.874200  1.874200
    #> 9     1  9 15.861959 15.861959 15.861959
    #> 10    2 10  3.938463  3.938463  3.938463
    

    Created on 2023-03-18 with reprex v2.0.2

    Another option from @TarJae, thanks!

    library(dplyr)
    library(tidyr)
    df %>% 
      filter(wave == 2 | wave == 3) %>%    
      fill(var1:var3, .direction = "down") %>%    
      bind_rows(df) %>%    
      distinct(id, .keep_all = TRUE) %>%    
      arrange(id)
    #>    wave id      var1      var2      var3
    #> 1     1  1  6.341464  6.341464  6.341464
    #> 2     2  2 13.206567 13.206567        NA
    #> 3     3  3 13.206567 13.206567        NA
    #> 4     1  4  5.272296  5.272296  5.272296
    #> 5     2  5  2.746650  2.746650  2.746650
    #> 6     3  6  2.746650  2.746650  2.746650
    #> 7     1  7  1.684472  1.684472  1.684472
    #> 8     2  8  1.874200  1.874200  1.874200
    #> 9     1  9 15.861959 15.861959 15.861959
    #> 10    2 10  3.938463  3.938463  3.938463
    

    Old answer, different data OP

    You could use fill with a down direction like this:

    library(dplyr)
    library(tidyr)
    df %>%
      fill(var1:var3, .direction = 'down')
    #>    wave id      var1      var2      var3
    #> 1     1  1  6.341464  6.341464  6.341464
    #> 2     2  2 13.206567 13.206567 13.206567
    #> 3     3  3 13.206567 13.206567 13.206567
    #> 4     1  4  5.272296  5.272296  5.272296
    #> 5     2  5  2.746650  2.746650  2.746650
    #> 6     3  6  2.746650  2.746650  2.746650
    #> 7     1  7  1.684472  1.684472  1.684472
    #> 8     2  8  1.874200  1.874200  1.874200
    #> 9     1  9 15.861959 15.861959 15.861959
    #> 10    2 10  3.938463  3.938463  3.938463
    

    Created on 2023-03-18 with reprex v2.0.2