0

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'))
thelatemail
  • 91,185
  • 12
  • 128
  • 188
flightless13wings
  • 109
  • 10
  • Your `DF` and `DF2` weren't properly defined above. A bunch of your `Product` items didn't have quotes so `R` is looking for variables with those names. Have addressed this in my answer – Dan Aug 25 '16 at 23:24
  • @Dan - fixed in an edit now. – thelatemail Aug 25 '16 at 23:26

1 Answers1

2

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)
Dan
  • 2,625
  • 5
  • 27
  • 42