Filling in missing values in a data.table by reference

1
votes

Suppose I have a data.table with missing values and a reference data.table:

dt <- data.table(id = 1:5, value = c(1, 2, NA, NA, 5))

   id value
1:  1     1
2:  2     2
3:  3    NA
4:  4    NA
5:  5     5

ref <- data.table(id = 1:4, value = c(1, 2, 98, 99))

   id value
1:  1     1
2:  2     2
3:  3    98
4:  4    99

How would I fill the column value of dt by using the matching id in the two data.tables, so that I get the following data.table?

   id value
1:  1     1
2:  2     2
3:  3    98
4:  4    99
5:  5     5
1
rdata.table

1 Answers

2
votes

We can use a join on the 'id' and assign (:=) the value column from 'ref' (i.value) to that in 'dt'

library(data.table)
dt[ref, value := i.value, on = .(id)]
dt
#   id value
#1:  1     1
#2:  2     2
#3:  3    98
#4:  4    99
#5:  5     5

If we don't want to replace the original non-NA elements in the 'value' column

dt[ref, value := fcoalesce(value, i.value), on = .(id)]