0

I have two data tables like this:

df1 <- data.table(ID = 1:10,
                  text = c("a", NA, NA, "b", NA, "a", "a", NA, "c", NA))

df2 <- data.table(ID = c(2,3,8),
                  text = c("a", "b", "a"))

I would like to fill the text column in df1 with the values from df2 based on the ID. Notice that not each ID has a value in the text column in df1 or in df2.

I would like my result to look like this:

    ID text
 1:  1    a
 2:  2    a
 3:  3    b
 4:  4    b
 5:  5 <NA>
 6:  6    a
 7:  7    a
 8:  8    a
 9:  9    c
10: 10 <NA>

When possible, the values should be taken from df2, otherwise the column text in df1 should remain empty.

I would really appreciate your help!

Besz15
  • 157
  • 7

3 Answers3

0
df1[is.na(text), text := df2[.SD, on = 'ID', text]]

Using base R data.frame you could implement similar logic with:

i_tm <- is.na(df1$text) # text missing at
df1[i_tm, 'text'] <- df2[match(df1[i_tm, 'ID'], df2$ID), 'text']
s_baldur
  • 29,441
  • 4
  • 36
  • 69
0

I've never used data.table before but this seems to do the trick. Full join and then coalesce

df3 <- merge(df1, df2, by="ID", all=T)

df3[, text := fcoalesce(text.x, text.y)]
df3
Quixotic22
  • 2,894
  • 1
  • 6
  • 14
0

We may use

df1[df2, text := i.text, on = .(ID)]

-output

> df1
    ID text
 1:  1    a
 2:  2    a
 3:  3    b
 4:  4    b
 5:  5 <NA>
 6:  6    a
 7:  7    a
 8:  8    a
 9:  9    c
10: 10 <NA>
akrun
  • 874,273
  • 37
  • 540
  • 662