Update join: replace with values from a column with the same name

Question

I need to merge some data from one data.table into another. I know how to add a new column from one data.table to another in a join. Below values from column b in df2 is added to df1 in a join based on the Id column:

df1 <- data.table(Id = letters[1:5], a = 1:5)
df2 <- data.table(Id = letters[1:3], a = 7:9, b = 7:9)
setkey(df1, Id)
setkey(df2, Id)
df1[df2, b := b][]
#>    Id a  b
#> 1:  a 1  7
#> 2:  b 2  8
#> 3:  c 3  9
#> 4:  d 4 NA
#> 5:  e 5 NA

However, that idiom does not work when the column already exists in df1, here column a:

df1[df2, a := a][]
#>    Id a
#> 1:  a 1
#> 2:  b 2
#> 3:  c 3
#> 4:  d 4
#> 5:  e 5

I understand that a is not updated by this assignment because the field a already exists in df1. The reference to a in the right hand side of the assignment resolves to that value, not the on in df2.

So how to update values in df1$a with those in df2$a in a join on matching id to get the following:

#>    Id a
#> 1:  a 7
#> 2:  b 8
#> 3:  c 9
#> 4:  d 4
#> 5:  e 5

Thanks! I knew there had to be a simple solution! Just to be clear for others, the j= is naming the argument to [.data.table, as in df1[df2,a:=i.a] — Jthorpe
– Jthorpe, Commented Apr 2, 2016 at 0:24

Henrik · Accepted Answer · 2023-02-11 12:21:23Z

6

From ?data.table:

When i is a data.table, the columns of i can be referred to in j by using the prefix i., e.g., X[Y, .(val, i.val)]. Here val refers to X's column and i.val Y's.

Thus, in the RHS of :=, use the i. prefix to refer to the a column in df2, i.a:

library(data.table)
df1 <- data.table(Id = letters[1:5], a = 1:5)
df2 <- data.table(Id = letters[1:3], a = 7:9, b = 7:9)
setkey(df1, Id)
setkey(df2, Id)
df1[df2, a := i.a]

# or instead of setting keys, use `on` argument:
df1[df2, on = .(Id), a := i.a]

df1
#        Id     a
#    <char> <int>
# 1:      a     7
# 2:      b     8
# 3:      c     9
# 4:      d     4
# 5:      e     5

edited Feb 11, 2023 at 12:21

Henrik

68k15 gold badges153 silver badges166 bronze badges

answered Apr 2, 2016 at 0:25

jangorecki

16.8k5 gold badges86 silver badges169 bronze badges

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

Collectives™ on Stack Overflow

Update join: replace with values from a column with the same name

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related