luffe luffe - 3 months ago 11
R Question

Join two tables but fill common column

I have a data frame

left
with columns
name
and
x
, and I want to match the missing
x
entries with the corresponding
name
entries from a data frame
right
:

left = data.frame(
name = c("Bob", "Billy", "Roger", "Anna", "Kathy"),
x = c(NA, NA, NA, 4, 5)
)

# > left
# name x
# 1 Bob NA
# 2 Billy NA
# 3 Roger NA
# 4 Anna 4
# 5 Kathy 5

right = data.frame(
name = c("Bob", "Billy", "Roger"),
x = c(1, 2, 3)
)

# > right
# name x
# 1 Bob 1
# 2 Billy 2
# 3 Roger 3


A
left_join
gives me two colums

> left_join(left, right, by="name")
name x.x x.y
1 Bob NA 1
2 Billy NA 2
3 Roger NA 3
4 Anna 4 NA
5 Kathy 5 NA


But what I want is:

name x
1 Bob 1
2 Billy 2
3 Roger 3
4 Anna 4
5 Kathy 5


Can I do this with a join command?

Answer
library(data.table)

setDT(left)[right, on = 'name', x := i.x]
left
#    name x
#1:   Bob 1
#2: Billy 2
#3: Roger 3
#4:  Anna 4
#5: Kathy 5