Match values between R dataframes without using a for-loop

Question

I have the following dataframes:

db1 = data.frame(name = c('a', 'b', 'c', 'd'), age = c('10', '20', '30', '40'), tier = NA)
db2 = data.frame(name = c('a', 'a', 'c', 'b'), age = c('10', '10', '30', '20'), tier = c('1', '3', '4', '2'))

I want to enter the tier values from db2 into the same column in db1 if the name and age variables match.

I can do this with a for-loop but when we're dealing with thousands of rows this takes far too long. Is there a faster way to do this?

for (i in 1:nrow(db1)){
  for (j in 1:nrow(db2)){
    if (db1$name[i] == db2$name[j] & db1$age[i] == db2$age[j]){
      db1$tier[i] = db2$tier[j]
    }
  }
}

Drop the tier column from db1 (db1$tier <- NULL). This would be a simple merge : merge(db1, db2) Or to be specific - merge(db1, db2, by = c('name', 'age')) — Ronak Shah
– Ronak Shah, Commented Jun 9, 2021 at 13:32
This works, thank you. If you post an answer then I'll accept it — mendy
– mendy, Commented Jun 9, 2021 at 13:38
What to do if it matces two times? In you case name=a and age=10? Take the first? — GKi
– GKi, Commented Jun 9, 2021 at 13:43

GKi · Accepted Answer · 2021-06-09 13:42:40Z

2

When taking the first in case it matches multiple times is also ok (you code takes the last), you can use match and for multiple columns with interaction.

db1$tier <- db2$tier[match(interaction(db1[c("name","age")]),
                           interaction(db2[c("name","age")]))]
db1
#  name age tier
#1    a  10    1
#2    b  20    2
#3    c  30    4
#4    d  40 <NA>

Or taking the last match (like your code is doing) using in addition `rev.

db1$tier <- rev(db2$tier)[match(interaction(db1[c("name","age")]),
                    rev(interaction(db2[c("name","age")])))]
db1
#  name age tier
#1    a  10    3
#2    b  20    2
#3    c  30    4
#4    d  40 <NA>

edited Jun 9, 2021 at 13:42

answered Jun 9, 2021 at 13:32

GKi

40.1k3 gold badges36 silver badges56 bronze badges

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

1 Comment

mendy Over a year ago

Thanks! I haven't come across interaction() before

Ronak Shah · Accepted Answer · 2021-06-09 13:50:07Z

2

Drop the tier column and use merge -

db1$tier <- NULL
merge(db1, db2)

#  name age tier
#1    a  10    1
#2    a  10    3
#3    b  20    2
#4    c  30    4

If you want d in the final output use all.x = TRUE -

merge(db1, db2, all.x = TRUE)

#  name age tier
#1    a  10    1
#2    a  10    3
#3    b  20    2
#4    c  30    4
#5    d  40 <NA>

answered Jun 9, 2021 at 13:50

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Comments

ThomasIsCoding · Accepted Answer · 2021-06-09 13:54:34Z

1

We can use merge + duplicated like below

subset(
  merge(db1, db2, by = c("name", "age"), all.x = TRUE),
  !duplicated(cbind(name, age)),
  select = -tier.x
)

which gives you

  name age tier.y
1    a  10      1
3    b  20      2
4    c  30      4
5    d  40   <NA>

answered Jun 9, 2021 at 13:54

ThomasIsCoding

106k9 gold badges38 silver badges110 bronze badges

Comments

d3hero23 · Accepted Answer · 2021-06-09 15:24:27Z

0

This is a simple join.

library(dplyr)
db3<-full_join(db2,db1, by = c("name" = "name", "age" = "age"), suffix = c("", ".x"))

  name age tier tier.x
1    a  10    1     NA
2    a  10    3     NA
3    c  30    4     NA
4    b  20    2     NA
5    d  40 <NA>     NA

### i am assuming you want to have tier from db2 shown if they are not all NAs otherwise you can just drop before the join ###

db3$tier.x = NULL

answered Jun 9, 2021 at 15:24

d3hero23

3921 silver badge14 bronze badges

Collectives™ on Stack Overflow

Match values between R dataframes without using a for-loop

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related