CODEWITHSUNDEEP
rjoinmergeconditional-statements
Belinda-Jane Netan
Belinda-Jane Netan

Reputation: 526

R: join/merge of a table with two look-up tables based on a condition

I have a base dataset of people:

everyoneexample <- data.frame(
gender=c("Female", "Male", "Male", "Female"), age=c(18, 18, 20, 21))

> everyoneexample
  gender age
1 Female  18
2   Male  18
3   Male  20
4 Female  21

and two look-up tables:

scorefemale <- data.frame(age=c(18, 19, 20, 21, 22, 23), 
  score=c(1.1, 3.3, 5.5, 7.7, 9.9, 11.1))

> scorefemale
  age score
1  18   1.1
2  19   3.3
3  20   5.5
4  21   7.7
5  22   9.9
6  23  11.1

scoremale <- data.frame(age=c(18, 19, 20, 21, 22, 23), 
   score=c(2.2, 4.4, `6.6, 8.8, 10.1, 12.1))`

> scoremale
  age score
1  18   2.2
2  19   4.4
3  20   6.6
4  21   8.8
5  22  10.1
6  23  12.1

And I'm basically trying to get this:

    gender  age score
1   Female  18  1.1
2   Male    18  2.2
3   Male    20  6.6
4   Female  21  7.7

Everything I look up on conditional joins/merges assumes one main table and one reference table, but my problem calls for two reference tables.

Hopefully this example is clear, but please do fire away with any questions if you want me to clarify anything.

UPDATE: Thanks to Gregor, the most elegant answer seems to be just to make a temporary table out of an rbind of both reference tables, and then to do a left join using two "by" variables:

everyoneexample <- merge(scores_FandM, everyoneexample, by=c("age", "gender"))

Upvotes: 1

Views: 341

Answers (2)

Belinda-Jane Netan
Belinda-Jane Netan

Reputation: 526

Thanks to @Gregor, he suggests adding a gender column to each look-up table:

> scorefemale$gender <- "Female"
> scoremale$gender <- "Male"

Then combining the tables to form one large look-up table:

> scores_FandM <- rbind(scorefemale, scoremale)

And then finally left-joining the main table with the look-up table using two "by" variables -- age and gender -- effectively forming a composite key into the new, combined look-up table:

> everyoneexample <- 
      merge(everyoneexample, scores_FandM, by=c('age', 'gender'), all.x = TRUE)

Simple and elegant... thank you!

Upvotes: 0

Nathan Werth
Nathan Werth

Reputation: 5283

female_rows <- which(everyoneexample$gender == 'Female')
female_matches <- merge(everyoneexample[female_rows, ], scorefemale, by = 'age')

male_rows <- which(everyoneexample$gender == 'Male')
male_matches <- merge(everyoneexample[male_rows, ], scoremale, by = 'age')

everyoneexample$score <- NA
everyoneexample[female_rows, 'score'] <- female_matches$score
everyoneexample[male_rows, 'score'] <- male_matches$score

Upvotes: 1

Related Questions