How do you assign data from dataframe B to a column in dataframe A if two values from dataframes A and B match?

Question

Let me explain my problem a little more clearly. Let's say I have two dataframes, A and B, and they look something like this.

Dataframe A 
   Names    Email
1   John    a@a.com
2   Harry   b@b.com 
3   Amy     c@c.com
4   Jim     d@d.com
5   Chad    e@e.com

Dataframe B 
   Email    Category
1  q@q.com  Student
2  z@z.com  Faculty
3  h@h.com  Alumni
4  c@c.com  Student
5  a@a.com  Alumni

My goal is to create a new column in dataframe A called Category, which will have a value equal to B$Category if a value in A$Email matches any value in B$Email, and NA otherwise. Here is my ideal output

Dataframe A
   Names    Email     Category
1   John    a@a.com   Alumni
2   Harry   b@b.com   NA
3   Amy     c@c.com   Student
4   Jim     d@d.com   NA
5   Chad    e@e.com   NA

What would be the best way to go about this?

Answer 1

This is a join or merge operation. In this case, a left-join:

merge(dfA, dfB, by.x = "Email", all.x = TRUE)
#     Email Names Category
# 1 a@a.com  John   Alumni
# 2 b@b.com Harry     <NA>
# 3 c@c.com   Amy  Student
# 4 d@d.com   Jim     <NA>
# 5 e@e.com  Chad     <NA>

Two good links for learning about it and how to use it in general and in R:

• How to join (merge) data frames (inner, outer, left, right)
• What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

---

Data:

dfA <- structure(list(Names = c("John", "Harry", "Amy", "Jim", "Chad"), Email = c("a@a.com", "b@b.com", "c@c.com", "d@d.com", "e@e.com")), class = "data.frame", row.names = c("1", "2", "3", "4", "5"))
dfB <- structure(list(Email = c("q@q.com", "z@z.com", "h@h.com", "c@c.com", "a@a.com"), Category = c("Student", "Faculty", "Alumni", "Student", "Alumni")), class = "data.frame", row.names = c("1", "2", "3", "4", "5"))