How to group by and get opposite values of a discrete column in dplyr?

user_5869121 0
Viewed 124

Hi I have a simple problem i run into a lot when working with sports data, where I have to group by a GameID variable and manually create an opponent variable. I do a lot of data pivoting so instead of having only 1 row for every game that's played, I want to have every team have their own row and include an opponent variable for each one. I traditionally do this by creating 2 data frames, changing column names, joining, filtering, and it just feels overly complex and I'm looking for a better way to do it.

You can follow along with this example in R.

library(tidyverse)
df <- data.frame(team = c('BKN', 'MIL'),
                 moneyline = c('-110', '-105'),
                 gameid = c(1, 1)) %>%
  group_by(gameid) %>%
  mutate(opponent = team) %>%  ### idk how to create this variable properly
  ungroup()

Let's say MIL is playing against BKN. They both have the same gameid variable. I basically just want to group by that variable and make an opponent variable that gives me the opposite team so that opponent != team. Every gameid can only have 2 possible teams playing.

Anyone have any easy tricks to get this done?

dplyr r
2 Answers

We can use rev

df <- data.frame(team = c('BKN', 'MIL'),
                 moneyline = c('-110', '-105'),
                 gameid = c(1, 1)) %>%
  group_by(gameid) %>%
  mutate(opponent = rev(team)) %>%  #
  ungroup()
user_37322710

Another possibility could be:

df %>% 
  group_by(gameid) %>% 
  mutate(opponent=c(last(team), first(team)))

Output:

  team  moneyline gameid opponent
  <chr> <chr>      <dbl> <chr>   
1 BKN   -110           1 MIL     
2 MIL   -105           1 BKN
user_133216470
Related