0

I've had a look to other solutions like this: How to copy specific values from one data column to another while matching other columns in R?, but it's not really what I need. I know I could do all with for-loops but I have several similar large datasets and I know finding a sort of single-line solution, it'll be much more efficient.

For instance, I've got two data tables:

df1 <- data.frame(ID=c(1,1,1,1,2,2,2), CODE=c("cd1", "cd2", "cd3", "cd4", "cd2", "cd3", "cd4"), FREQ=c(2,3,1,2,1,2,3))
df2 <- data.frame(CODE=c("cd1", "cd2", "cd3", "cd4"), DESCRIPTION=c("code1", "code2", "code3", "code4"))

dt1 <- data.table(df1)
dt2 <- data.table(df2)

What I'm trying to do is to add a column DESCRIPTION to the first data table with a matching value from dt2.

I've tried with match and which, like this:

dt1[,DESCRIPTION:=dt2$DESCRIPTION[which(dt1$CODE==dt2$CODE)], by:=.(ID,CODE)]

But I get warnings and does not really work... It must be a simple thing that I can't see it... Any ideas on how to do it?

Community
  • 1
  • 1
Fabiola Fernández
  • 262
  • 3
  • 14
  • `df3<-merge(df1,df2,by='CODE')` – Jason Dec 08 '15 at 22:52
  • 1
    you used incorrect tag for `data.table` – jangorecki Dec 08 '15 at 23:12
  • 2
    Try `dt1[dt2, DESCRIPTION := i.DESCRIPTION, on = "CODE"]`. You really need to go thru the vignettes – David Arenburg Dec 09 '15 at 00:54
  • somebody could answer that question to not leave it in current state. Self-answers are allowed too. – jangorecki Dec 09 '15 at 02:48
  • Thanks, I thought it would be simple but never (shame on me!) used merge before... Also, I did try to find the correct tag, sorry about that. And I did look in the vignettes here: https://cran.r-project.org/web/packages/data.table/, but couldn't find anything for my case. @DavidArenburg, where would you advise me to look for future work on data.table? I looked tutorials but didn't find anything addressing getting values from other tables... Thanks! – Fabiola Fernández Dec 09 '15 at 08:45
  • This is a good resource https://github.com/Rdatatable/data.table/wiki/Getting-started – David Arenburg Dec 09 '15 at 09:21

1 Answers1

1

You can use Merge function to find matching vlaues.

dt1 = merge(dt1,dt2,by="CODE")
David Arenburg
  • 91,361
  • 17
  • 137
  • 196
  • Thanks! It works great. Just a quick question, merge would work with tables with same column names. If that is not the case, apart from the obvious of changing to matching column names, is there any easy way to do it? – Fabiola Fernández Dec 09 '15 at 08:51
  • You can specify which columns to match if the the names differ. refer to ?merge documentation. instead of by = "code". you can specify by.x = "name_in_df1", by.y = "name_in_df2" –  Dec 09 '15 at 09:21