Adding a new column in R from matching multiple columns in two dataframes?

Question

I have two data frames like the following and am trying to add a new column. I want to add a new column in Data Frame 1 which comes from matching 3 different columns with different names (Name,Age,Country) in Dataframe 1 and (First,Age,BornPlace) in Dataframe2.

I have tried filtering and setting a new column but I cannot get to work for every row in df1.

Data frame 1
   Name    Age    Country   Unrelated  Unrelated 
1  Josh    15     USA       ...        ...      
2  Kyle    18     USA       ...        ...       
3  Pete    17     USA       ...        ...       
4  Devin   19     USA       ...        ...       
5  Josh    15     Canada    ...        ...      

Data frame 2
   First   AgeNum  BornPlace   Unrelated  Unrelated Weight
1  Max     25      USA          ...        ...       150
2  Morgan  28      USA          ...        ...       170
3  Josh    15      USA          ...        ...       140
3  Devin   19      USA          ...        ...       180

Expected Result(Dataframe1 with new column)

   Name    Age    Country   Unrelated  Unrelated Weight
1  Josh    15     USA       ...        ...       140
2  Kyle    18     USA       ...        ...       -   
3  Pete    17     USA       ...        ...       -     
4  Devin   19     USA       ...        ...       180     
5  Josh    15     Canada    ...        ...       -

This post might help https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right — Ronak Shah, Oct 07 '21 at 12:19

score 0 · Answer 1 · answered Oct 06 '21 at 20:22

We could use left_join:

library(dplyr)
left_join(df1, df2, by=c("Name"="First","Age" = "AgeNum","Country" = "BornPlace"))

   Name Age Country Weight
1  Josh  15     USA    140
2  Kyle  18     USA     NA
3  Pete  17     USA     NA
4 Devin  19     USA    180
5  Josh  15  Canada     NA

score 0 · Answer 2 · answered Oct 06 '21 at 20:23

0

use data.table package

merge.data.table(
  x = DT1, y = DT2, 
  by.x = c('Name','Age','Country'), 
  by.y = c('First','Age','BornPlace'), 
  all.x = T, all.y = F)

answered Oct 06 '21 at 20:23

niels kristian schmidt

137
1
7

2

Why not simply use base R's `merge` instead of `merge.data.table`? – Martin Gal Oct 06 '21 at 20:27

Adding a new column in R from matching multiple columns in two dataframes?

2 Answers2