How to create a win/loss record from a data set in R-CodePudding

I have a set of fantasy football data that I am trying to figure out records from. The data is structured in the following way

team <- c("Mary", "John", "Matt","Paul","Mary", "John", "Matt","Paul")
week <- c(1,1,1,1,2,2,2,2)
opponent <- c("John", "Mary" , "Paul" , "Matt" , "Paul" , "Matt" , "John" , "Mary")
team.score <- c(10,15,6,7,8,12,2,3)
df <- data.frame(team,week,opponent,team.score)

head(df)

team week opponent team.score
Mary    1     John         10
John    1     Mary         15
Matt    1     Paul          6
Paul    1     Matt          7
Mary    2     Paul          8
John    2     Matt         12

What I would like to be able to do, is say Mary had a record of 1 - 1. I am not sure how to compare with the way the data is structured.

CodePudding user response：

It's pretty messy but it works.

library(dplyr)
library(tidyr)

df %>%
  rowwise %>%
  mutate(key = paste0(sort(c(team, opponent)), collapse = "-")) %>%
  group_by(week, key) %>%
  mutate(a = rank(team.score) - 1) %>%
  group_by(team,a) %>%
  summarize(n = n())%>%
  pivot_wider(id_cols = team, names_from = a, values_from = n, names_glue = "score_{a}") %>%
  replace(is.na(.), 0)

  team  score_1 score_0
  <chr>   <dbl>   <dbl>
1 John        2       0
2 Mary        1       1
3 Matt        0       2
4 Paul        1       1

CodePudding user response：

The first step I took was adding in a column for the opponent's score. It's kind of tricky with the current structure of the data but here's what I did:

#create separate opponent df
opponent <- df %>%
  select(week, opponent, team.score)

#join onto original df on team name = opponent name
new_df <- df %>%
  inner_join(opponent, by = c("team" = "opponent", "week" = "week")) %>%
  rename("team_score" = "team.score.x",
         "opponent_score" = "team.score.y")

  team week opponent team_score opponent_score
1 Mary    1     John         10             15
2 John    1     Mary         15             10
3 Matt    1     Paul          6              7
4 Paul    1     Matt          7              6
5 Mary    2     Paul          8              3
6 John    2     Matt         12              2

Next is adding in our win-loss logic:

#add in W/L logic
new_df <- new_df %>%
  mutate(win = if_else(team_score > opponent_score, 1, 0),
         loss = if_else(team_score < opponent_score, 1, 0))

  team week opponent team_score opponent_score win loss
1 Mary    1     John         10             15   0    1
2 John    1     Mary         15             10   1    0
3 Matt    1     Paul          6              7   0    1
4 Paul    1     Matt          7              6   1    0
5 Mary    2     Paul          8              3   1    0
6 John    2     Matt         12              2   1    0
7 Matt    2     John          2             12   0    1
8 Paul    2     Mary          3              8   0    1

Finally, you can use a quick group by on team name to obtain the "official" W/L records:

#group by
aggregated_df <- new_df %>%
  group_by(team) %>%
  dplyr::summarise(win = sum(win), loss = sum(loss)) %>%
  arrange(-win)

  team    win  loss
  <chr> <dbl> <dbl>
1 John      2     0
2 Mary      1     1
3 Paul      1     1
4 Matt      0     2

Hope you're doing well in your fantasy season haha!