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)
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!
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