R Question
assign value to NA's based on index in data table
Suppose we have a data table like this:
DT <- data.table(x = 1:5, y = c(6, NA, 8, NA, 10), z = 11:15)
> DT
x y z
1: 1 6 11
2: 2 NA 12
3: 3 8 13
4: 4 NA 14
5: 5 10 15
I would like to assign the NA values in y to the value in the row above in column z, so the output would be like this:
x y z
1: 1 6 11
2: 2 11 12
3: 3 8 13
4: 4 13 14
5: 5 10 15
I can do it statically (all NAs will be 15) like this:
DT[is.na(y), `:=`(y = DT[5, z])]
But I am after something like this (doesn't work):
DT[is.na(y), `:=`(y = DT[row-1, z])]
What am I missing?
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
You can use replace and shift, i.e.
library(data.table)
DT[, y := replace(y, is.na(y), shift(z, type = 'lag')[is.na(y)])][]
which gives,
x y z 1: 1 6 11 2: 2 11 12 3: 3 8 13 4: 4 13 14 5: 5 10 15
We can avoid replace as per @Jaap's comment as follows,
DT[is.na(y), y := DT[, shift(z, type = 'lag')[is.na(y)]]][]
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download