Match values in 2 different data frames

Question

I have 2 data frames like this:

zip    c
1      2
2      5
3      3
4      4

and the other one:

x      zip     ch
1      1       2
2      2       1
3      1       4

what i want to do is to create another data frame (or add a column in the second one) in which the value has to be computed as (ch - c) when the two zip are the same. For instance, in the above example it would be like this:

x      zip     ch    new
1      1       2     0
2      2       1     -4
3      1       4     2

I am currently doing it with a for loop, cycling over each item of the second data frame and checking for the correspondent one in the first data frame, but since my input data is quite huge, I am wondering whether R could do it faster.

Answer 1

We can do a merge and then create the column by subtracting the 'c' and 'ch'

transform(merge(df1, df2, by = "zip"), new = ch-c)[-2]

---

Or if we are using data.table, this can be done with a join

library(data.table)
setDT(df2)[df1, new := ch- c, on = "zip"]
df2 
#   x zip ch new
#1: 1   1  2   0
#2: 2   2  1  -4
#3: 3   1  4   2

Answer 2

The dplyr way:

library(dplyr)
inner_join(df2, df1, by = "zip") %>% mutate(new=ch-c) %>% select(-c)

#  x zip ch new
#1 1   1  2   0
#2 2   2  1  -4
#3 3   1  4   2