Home Add data to one table based on matching variable of the second smaller table

Questions

Add data to one table based on matching variable of the second smaller table

November 27, 2021

I need help in solving this problem. I have two tables. They are of different lengths. I want to add code information to the second table based on ID match with the first.

year <- c(2018,2019,2020,2021)

ID <- c("001","002","003","004")

code <- c("AA", "DD", "CR", "RT")

d1 <- data.frame(year, ID, code)

> d1
  year  ID code
1 2018 001   AA
2 2019 002   DD
3 2020 003   CR
4 2021 004   RT

d2 <- data.frame(year = c(2017, 2018,2019,2020,2021),
  ID = c("012","007","011","003" ,"004"),
  code = NA)

> d2
  year   ID code
1 2017  012 <NA>
2 2018  007 <NA>
3 2019  011 <NA>
4 2020  003 <NA>
5 2021  004 <NA>

It should go like this.

d2
  year   ID code
1 2017  012 <NA>
2 2018  007 <NA>
3 2019  011 <NA>
4 2020  003  CR
5 2021  004  RT

>Solution :

The NA created is NA_logical_ by default. It should be the same class as the as the column ‘code’ in ‘d1’ – character. So, either create the columns as NA_character_ or convert to character class with as.character and then do a join

library(data.table)
d2$code <- as.character(d2$code)
setDT(d2)[d1, code := i.code, on = .(ID)]

-output

> d2
   year  ID code
1: 2017 012 <NA>
2: 2018 007 <NA>
3: 2019 011 <NA>
4: 2020 003   CR
5: 2021 004   RT

data

d2 <- structure(list(year = 2017:2021, ID = c("012", "007", "011", 
"003", "004"), code = c(NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_)), row.names = c("1", "2", "3", 
"4", "5"), class = "data.frame")