consider the following data table
library(data.table)
dt <- data.table(sys_id = 1:9,
A = c(11, 12, 12, 13, 14, 15, 15, 18, 18),
B = c(2, 1, 2, 1, 2, 1, 2, 1, 2),
Age = c(13, NA, 13, NA, 11, NA, 12, NA, 12))
which gives the following data table:
sys_id A B Age
1: 1 11 2 13
2: 2 12 1 NA
3: 3 12 2 13
4: 4 13 1 NA
5: 5 14 2 11
6: 6 15 1 NA
7: 7 15 2 12
8: 8 18 1 NA
9: 9 18 2 12
The goal is to replace the NA's within groups of Variable A (1 or 2 rows per group) with the other value of Age within the same group. This can only be done when a group (of A) consists of two rows with only one NA in one of the two rows of that group. So the value of Age in row 4 can remain NA. I did the following that works:
dt[, Age := unique(Age[!is.na(Age)]), by = A]
dt
this last code gave me the following data table:
sys_id A B Age
1: 1 11 2 13
2: 2 12 1 13
3: 3 12 2 13
4: 4 13 1 NA
5: 5 14 2 11
6: 6 15 1 12
7: 7 15 2 12
8: 8 18 1 12
9: 9 18 2 12
My Problem:
Although the code works, the code is not intuitive (to me that is). I would like a more understandable code that shows that for each NA in a group of variable A, a replacement must be done with the Age value from the nearest row (above or below within that group). Is there another (more intuitive) code to do this with the data.table package?
