R replacing missing values with na.locf

[xushan14]

I am new to R. I was hoping to replace the missing values for X in the data. How can I replace the missing values of "X" when "Time" = 1 and 2 with the value of "X" when "Time" = 3 for the same "ID" and the same "Day"

X: only has a valid value when Time is 3, others are missing.

 ID   Day  Time   X    
 1    1     1     NA
 1    1     2     NA
 1    1     3     7.4
 1    2     1     NA
 1    2     3     6.2
 2    1     1     NA
 2    1     2     NA
 2    1     3     7.1
 2    2     3     5.9
 2    2     2     NA
 2    2     1     NA

I was able to write and run the following codes with the package zoo and data.table, but afterward when I checked the data, it didn't work--the X value still has all the missing values when time=1 and 2. Anything wrong with my code? Any suggestions? I have very limited experience in R. Thank you in advance!

setDT(data1) data1 <- data1 [order(-Time), X := na.locf(X), by = .(ID, Day)]

[siddharthprabhu]

Hi @xushan14, welcome to RStudio Community.

I'm not familiar with data.table but I can offer a tidyverse solution.

library(dplyr, warn.conflicts = FALSE)
library(tidyr, warn.conflicts = FALSE)

data1 <- tribble(~ ID, ~ Day, ~ Time, ~ X,
                 1, 1, 1, NA, 
                 1, 1, 2, NA, 
                 1, 1, 3, 7.4, 
                 1, 2, 1, NA, 
                 1, 2, 3, 6.2, 
                 2, 1, 1, NA, 
                 2, 1, 2, NA, 
                 2, 1, 3, 7.1, 
                 2, 2, 3, 5.9, 
                 2, 2, 2, NA, 
                 2, 2, 1, NA)

data1 %>% 
  arrange(ID, Day, Time) %>% 
  group_by(ID, Day) %>% 
  fill(X, .direction = "up") %>% 
  ungroup()
#> # A tibble: 11 x 4
#>       ID   Day  Time     X
#>    <dbl> <dbl> <dbl> <dbl>
#>  1     1     1     1   7.4
#>  2     1     1     2   7.4
#>  3     1     1     3   7.4
#>  4     1     2     1   6.2
#>  5     1     2     3   6.2
#>  6     2     1     1   7.1
#>  7     2     1     2   7.1
#>  8     2     1     3   7.1
#>  9     2     2     1   5.9
#> 10     2     2     2   5.9
#> 11     2     2     3   5.9

^{Created on 2020-04-27 by the reprex package (v0.3.0)}