Outer Join 2 Data Frames in R two columns to one

Question

I am SURE that this is answered somewhere but I just don't know what this kind of operation is called to research. I have two tables:

Score:

Name	Points
Bob	14
Janet	8
Fred	87
Alex	221

Schedule:

Left Player	Right Player
Janet	Fred
Alex	Bob

So, I have a few things I want to do with this, but the main part I'm stuck on is if I want to make a new column in the "Score" table that is "Next Opponent". And it looks through the "Schedule" table and sees if the player is the left player and if they are fills in the right player as Next Opponent, and then does the reverse if they are the Right Player. I'm happy to research this on my own, I just don't know what this kind of merge is called or if this is a set of iteration with if statements or not. Would love a bump in the right direction here.

Jon Spring · Accepted Answer · 2022-10-11 06:05:41Z

3

I'd suggest reconfiguring Schedule as a table with Name and Opponent columns. Then it's just a left join.

library(dplyr)

Matchups <- bind_rows(
  Schedule %>% transmute(Name = Left.Player, Opponent = Right.Player),
  Schedule %>% transmute(Name = Right.Player, Opponent = Left.Player)
)

left_join(Score, Matchups)

Result

Joining, by = "Name"
   Name Points Opponent
1   Bob     14     Alex
2 Janet      8     Fred
3  Fred     87    Janet
4  Alex    221      Bob

Or alternatively:

Score %>%
  left_join(Schedule, by = c("Name" = "Left.Player")) %>%
  left_join(Schedule, by = c("Name" = "Right.Player")) %>%
  mutate(Opponent = coalesce(Left.Player, Right.Player))


   Name Points Right.Player Left.Player Opponent
1   Bob     14         <NA>        Alex     Alex
2 Janet      8         Fred        <NA>     Fred
3  Fred     87         <NA>       Janet    Janet
4  Alex    221          Bob        <NA>      Bob

answered Oct 11, 2022 at 6:05

Jon Spring

70.1k4 gold badges42 silver badges70 bronze badges

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

1 Comment

user8624315 Over a year ago

Delightful, thank you for the assistance.

Collectives™ on Stack Overflow

Outer Join 2 Data Frames in R two columns to one

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related