assigning values from smaller table to larger table in R

Question

I have a data.table A where one column is a descriptor code. I have another data.table B containing the descriptions that match those codes. How do I add a column to table A which matches the code's description in table B?

for example, table A currently looks like this:

Var1 Var2 Var3 code
qwer tyui  op   1a
asdf ghjk  ls   1a
lore mips  um   b4
foo  bar   dys  5c
bars foot  ls   b4

and table B looks like this:

code descriptor
1a   the a1 description
b4   the b4 description
5c   the 5c description

I would like table A to look like this:

Var1 Var2 Var3 code  descriptor
qwer tyui  op   1a   the a1 description
asdf ghjk  ls   1a   the a1 description
lore mips  um   b4   the b4 description
foo  bar   dys  5c   the 5c description
bars foot  ls   b4   the b4 description

score 0 · Answer 1 · answered Dec 02 '21 at 21:16

You should be able to simply use the merge function (though perhaps I read the question wrong):

A <- data.table(Var1 = c("qwer","asdf","lore","foo","bars"),
                      Var2 =  c("tyui","ghjk","mips","bar","foot"),
                      Var3 = c("op","ls","um","dys","ls"),
                      code = c("1a","1a","b4","5c","b4"))
B <- data.table(code = c("1a","b4","5c"),
                descriptor = c("the a1 description","the b4 description","the 5c description"))



C <- merge(A,B, by = "code")
print(C)
# code Var1 Var2 Var3         descriptor
# 1:   1a qwer tyui   op the a1 description
# 2:   1a asdf ghjk   ls the a1 description
# 3:   5c  foo  bar  dys the 5c description
# 4:   b4 lore mips   um the b4 description
# 5:   b4 bars foot   ls the b4 description

This puts code at the beginning. If you needed it for some reason in the exact format you stated, just rearrange C <- C[,c(2:4,1,5)]

score 0 · Answer 2 · answered Dec 02 '21 at 21:27

A data.table join would do this

library(data.table)

dt1 <- data.table(x=sample(letters[1:3], 10, replace=TRUE), y=1:10)
dt2 <- data.table(x=letters[1:3], z=LETTERS[1:3])

# join...
dt1[dt2, on="x"]

You could do that the more proper data.table way too and set keys first

setkey(dt1, "x")
setkey(dt2, "x")
dt1[dt2]

assigning values from smaller table to larger table in R

2 Answers2