Add a new column to a dataframe using matching values of another dataframe

Question

I am trying to fill in table1 with matching val2 values of table2

table1$New_val2 = table2[table2$pid==table1$pid,]$val2

But I get the warning

longer object length is not a multiple of shorter object length

which is fair enough because the table lengths are not the same.

Please kindly direct me on the correct way to do this.

Answer 1

merge(table1, table2[, c("pid", "val2")], by="pid")

Add in the all.x=TRUE argument in order to keep all of the pids in table1 that don't have matches in table2...

You were on the right track. Here's a way using match...

table1$val2 <- table2$val2[match(table1$pid, table2$pid)]

Answer 2

I am not sure if you mean this but you might use:

newtable <- merge(table1,table2, by  = "pid") 

This will create a new table called newtable, with 3 columns and those values matched by the id, in this case "pid".

Answer 3

I'm way late here, but in case anybody else asks the same question:
This is exactly what dplyr's inner_merge does.

table1.df <- dplyr::inner_join(table1, table2, by=pid)

The by-command specifies which column should be used to match the rows.

EDIT: I used to have so much difficulty remembering it's a [join], and not a [merge].