Match values between two columns of two dataframes and create a new column with values of another

Question

I have the 2 dataframes below

    name<-c("Adam","Bill","Jack")
    value<-c(3,2,1)
    n<-data.frame(name,value)
    
    name value
    1 Adam     3
    2 Bill     2
    3 Jack     1

    id<-c("Adam","Adam","Bill","Jack","Jack")
    group<-c("A","A","A","B","B")
    e<-data.frame(id,group)

    id group
    1 Adam     A
    2 Adam     A
    3 Bill     A
    4 Jack     B
    5 Jack     B

in which I want to match the values from n$name and e$id and then create a new column group in n with the respective values found in e$group like:

name value group
1 Adam     3     A
2 Bill     2     A
3 Jack     1     B

GKi · Accepted Answer · 2021-03-02 12:34:11Z

2

You can use match:

n$group <- e$group[match(n$name, e$id)]
n
#  name value group
#1 Adam     3     A
#2 Bill     2     A
#3 Jack     1     B

answered Mar 2, 2021 at 12:34

GKi

40.1k3 gold badges36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ronak Shah · Accepted Answer · 2021-03-02 12:33:05Z

2

Keep only unique rows of e and merge

merge(n, unique(e), by.x = 'name', by.y = 'id')

#  name value group
#1 Adam     3     A
#2 Bill     2     A
#3 Jack     1     B

With dplyr :

library(dplyr)
n %>% inner_join(distinct(e), by = c('name' = 'id'))

answered Mar 2, 2021 at 12:33

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Comments

akrun · Accepted Answer · 2021-03-02 16:33:00Z

2

We can use data.table

library(data.table)
setDT(n)[unique(e), on = .(name = id)]
#     name value group
#1: Adam     3     A
#2: Bill     2     A
#3: Jack     1     B

answered Mar 2, 2021 at 16:33

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

Match values between two columns of two dataframes and create a new column with values of another

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related