Jeppe Olsen Jeppe Olsen - 3 years ago 162
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?

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