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

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

CodePudding user response：

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")