Filling in missing values in a data.table by reference

Question

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

akrun · Accepted Answer · 2020-04-05 19:09:37Z

2

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)]

edited Apr 5, 2020 at 19:09

answered Apr 5, 2020 at 19:07

akrun

891k38 gold badges590 silver badges700 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

johnny Over a year ago

Ah, of course! I knew it was something simple like this. Thanks a lot, akrun!!

johnny Over a year ago

Could you tell me what "i.value" means? And "on = .(id)" simply means to use the column named "id" for matching rows, right?

akrun Over a year ago

@johnc th on = .(id) is the joining column on that 'id' (it is ssimilar to by = in merge or left_join. It will return the rows where there is a corresponding match for 'id' in both datasets. As the column names are same for 'value', the i. helps in identfying the column from second dataset

akrun Over a year ago

@johnc I meant that if you ref column names was value1 or any other name othere than 'value', you could simply use that name instead of i., but i. would also work. It is to uniquely identify that column

akrun Over a year ago

@johnC I would say the vignettes of data.table could be one place you can read about it

|

Collectives™ on Stack Overflow

Filling in missing values in a data.table by reference

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related