Error in update join when joining on factor

[Henrik-P]

I want to do an update join where I replace values in one data.table with values from another. For example, replace NA in 'd1' with values from 'd2':

d1 <- data.table(fac = factor(letters[1:4]), char = letters[1:4], val = c(1L, NA, 3L, NA))
d2 <- data.table(fac = factor(letters[1:4]), char = letters[1:4], val = 1:4)

When joining on a factor variable 'fac', it errors:

d1[is.na(val), val := d2[.SD, x.val, on = .(fac)]]
# Error in set(i, j = lc, value = newfactor) : 
# .SD is locked. Updating .SD by reference using := or set are reserved for future use. 
# Use := in j directly. Or use copy(.SD) as a (slow) last resort, until shallow() is exported.

Same operation but joining on a character 'char' works:

d1[is.na(val), val := d2[.SD, x.val, on = .(char)]]
d1
#     fac char val
# 1:    a   a    1  
# 2:    b   b    2
# 3:    c   c    3
# 4:    d   d    4 

---

Searched among the issues for the error and found .SD locked error using foverlaps after ordering by variable (exactly the same error) and .SD is locked for DT[, DT2[.SD]] joins (nearly the same error). The latter indeed looks similar, but it doesn't seem to be directly related to the factor vs character issue described here.

---

data.table_1.12.2

[symbalex]

idk how or why d1[is.na(val), val := d2[.SD, x.val, on = .(char)]] works, but I think what you want to do, in a more readable way is

d1[
  d2,
  on = .(fac),
  val := ifelse(is.na(val), i.val, val)
]

[Henrik-P]

Readability is in the eye of the beholder ;) But it's not "only" about readability; see e.g. the arguments for this kind of update join by @franknarf1 here. The idiom is also suggested by Arun here.

[Henrik-P]

unlock shallow copy of .SD when needed in joins in j seems to have fixed this issue as well.

d1[is.na(val), val := d2[.SD, x.val, on = .(fac)]]
d1
#    fac char val
# 1:   a    a   1
# 2:   b    b   2
# 3:   c    c   3
# 4:   d    d   4

data.table 1.12.3 IN DEVELOPMENT built 2019-09-01 03:43:32 UTC

Thanks a lot for your great work with this issue!

[jangorecki]

@Henrik-P thanks for confirming. Just filled #3807 so we can ensure no regression in future.