CODEWITHSUNDEEP
rmergedata.table
AlexP
AlexP

Reputation: 647

Merging 2, 1 row data.tables on column names

I have 2, 1 row data.tables.

dt1 <- data.table(a = 0, b = 0, c = 0)
dt2 <- data.table(a = 1, c = 5)

My goal is to update dt1 with the values of dt2 so that I get:

desired_dt <- data.table(a = 1, b = 0, c = 5)

I tried merging, assuming it'd match on the shared column names, but no luck.

dt2[dt1]

Error in `[.data.table`(dt2, dt1) : 
When i is a data.table (or character vector), the columns to join by must be specified using 'on=' 
argument (see ?data.table), by keying x (i.e. sorted, and, marked as sorted, see ?setkey), or by 
sharing column names between x and i (i.e., a natural join). Keyed joins might have further speed 
benefits on very large data due to x being sorted in RAM.

Wouldn't this be considered a natural join? I'd appreciate any help on what I'm missing!

Upvotes: 1

Views: 176

Answers (2)

ThomasIsCoding
ThomasIsCoding

Reputation: 101054

Here is a data.table option using rbindlist + fcoalesce

setcolorder(
  rbindlist(
    list(dt2, dt1),
    fill = TRUE
  )[
    ,
    lapply(.SD, function(x) fcoalesce(as.list(x)))
  ], names(dt1)
)[]

which gives

   a b c
1: 1 0 5

Upvotes: 1

akrun
akrun

Reputation: 886938

We get the intersecting column names and do the assignment

nm1 <- intersect(names(dt1), names(dt2))
dt1[, (nm1) := dt2]

Or we can set the key

setkeyv(dt1, intersect(names(dt1), names(dt2)))
out <- dt1[dt2]
for(j in seq_along(out)) set(out, i = which(is.na(out[[j]])), j=j, value = 0)

Upvotes: 2

Related Questions