How to write a search in R which matches columns from different tables and returns another column from the first table?

Question

Table_1

Name    Age    City           Mobile
John S  40    New York        444444
Roy M   24    London          999999
Smith   30    Venice          444555

Table_2
Name      Age   Gender
John S    40      M
Susane    28      F

What will be the code to match columns from Table_1 Name, Age and Table_2 Name,Age and return Mobile from Table_1.

You should determine data structures which are used by you at first. — Dmitriy, Dec 18 '17 at 07:28

score 0 · Answer 1 · answered Dec 18 '17 at 07:32

0

You can use merge from base R, or use left_join from library(dplyr)

library(dplyr)

Table_3 <- Table_2 %>% left_join(Table_1, by = c("Name", "Age"))

answered Dec 18 '17 at 07:32

Matt W.

3,692
2
23
46

Tim Biegeleisen · Answer 2 · 2017-12-18T07:38:10.190

Use merge and join your two data frames on the Name and Age columns.

result <- merge(Table_1, Table_2,by=c("Name", "Age"))
result
    Name Age     City Mobile Gender
1 John S  40 New York 444444      M

Note that the default values for all.x and all.y are false, which is what we want here. We want an inner join between the two data frames, i.e. in the result a row should only appear if the name and age also appeared in both data frames.

How to write a search in R which matches columns from different tables and returns another column from the first table?

2 Answers2

Demo