Replace values in one data.table from another data.table

Question

I have a data.table called big in which I want to replace all corresponding values from data.table called new_big.

library(data.table)
big <- structure(list(id = c("B", "C", "D", "E", "F", "G", "H", "I", 
"J", "K"), col = c(103L, 103L, 102L, 105L, 104L, 103L, 104L, 104L, 
104L, 103L)), row.names = c(NA, -10L), class = c("data.table", 
"data.frame"))
new_big <- structure(list(id = c("B", "E", "G"), col = c(1, 11, 111)), row.names = c(NA, 
-3L), class = c("data.table", "data.frame"))

They create:

> big
    id col
 1:  B 103
 2:  C 103
 3:  D 102
 4:  E 105
 5:  F 104
 6:  G 103
 7:  H 104
 8:  I 104
 9:  J 104
10:  K 103

> new_big
   id col
1:  B   1
2:  E  11
3:  G 111

Here is the desired output -

   id  col
 1:  B 1
 2:  C 103
 3:  D 102
 4:  E 11
 5:  F 104
 6:  G 111
 7:  H 104
 8:  I 104
 9:  J 104
10:  K 103

Is there a way to join these two tables to get the desired output?

I tried following but I could not get the desired output as shown above. Any pointers will be helpful.

big[new_big, on = .(id)]
   id col i.col
1:  B 105     1
2:  E 103    11
3:  G 101   111

`big[new_big, on = .(id), col := i.col]`; see also [Use merge() to update a data frame with values from a second data frame](https://stackoverflow.com/a/54568079) — Henrik, Jun 06 '22 at 14:20

score 4 · Accepted Answer · edited Jun 05 '22 at 18:12

4

We do the join first and fcoalesce with the 'col' object - also make sure that the types are same or else it return error

big[new_big, on = .(id), col2 := i.col][, .(id, 
     col = fcoalesce(col2, as.numeric(col)))]

edited Jun 05 '22 at 18:12

Gwang-Jin Kim

9,303
17
30

answered Jun 05 '22 at 18:00

akrun

874,273
37
540
662

langtang · Answer 2 · 2022-06-05T19:21:57.337

2

If id is unique in big, try this:

rbind(new_big, big[!new_big, on="id"])

If not, do this:

new_big[big, on="id"][is.na(col), col:=i.col][, i.col:=NULL]

Output:

    id col
 1:  B   1
 2:  E  11
 3:  G 111
 4:  C 103
 5:  D 102
 6:  F 104
 7:  H 104
 8:  I 104
 9:  J 104
10:  K 103

edited Jun 05 '22 at 19:21

answered Jun 05 '22 at 19:14

langtang

22,248
1
12
27

Gwang-Jin Kim · Answer 3 · 2022-06-05T18:35:52.270

1

Not as elegant as @akrun - I did:

vec <- new_big$col
names(vec) <- new_big$id

v <- big$col
names(v) <- big$id

res <- vec[names(v)]

res[is.na(res)] <- v[is.na(res)]
names(res)[is.na(names(res))] <- names(v)[is.na(names(res))]

res_df <- data.frame(id = names(res), col = res)

edited Jun 05 '22 at 18:35

answered Jun 05 '22 at 18:15

Gwang-Jin Kim

9,303
17
30

Replace values in one data.table from another data.table

3 Answers3