flightless13wings flightless13wings - 3 months ago 7
R Question

Replace or Merge in R?

I would like to replace the rows in one data frame with rows from another data frame by the ID and Product. I have tried using merge, but I am only left with the rows that were merged. Is there a way to merge/replace so that my data set will look like the one below?

library(data.table)
DF <- as.data.table(list(ID = c(1,2,3,4,5), Product = c('Y', 'W', 'N', 'Z', 'A'), Type = c(2, 4, 5, 7, 4)))

DF
ID Product Type
1 Y 2
2 W 4
3 N 5
4 Z 7
5 A 4


and

DF2 <- as.data.table(list(ID = c(1,2,3), Product = c('Y','W','N'), Category = c(1, 1, 5)))

DF2
ID Product Category
1 Y 1
2 W 1
3 N 5


Which i would like to look like:

ID Product Type Category
1 Y 2 1
2 W 4 1
3 N 5 5
4 Z 7 NA
5 A 4 NA


My code:

merge(DF, DF2, by=c('ID', 'Product'))

Dan Dan
Answer

Try this instead

library(data.table)
DF <- as.data.table(list(ID = c(1,2,3,4,5), Product = c('Y', 'W', 'N', 'Z', 'A'), Type = c(2, 4, 5, 7, 4)))
DF2 <- as.data.table(list(ID = c(1,2,3), Product = c('Y', 'W', 'N'), Category = c(1, 1, 5)))

merge(DF, DF2, by=c("ID","Product"), all.x=TRUE)