Need to reverse values from previous column based on group_by

tidyverse
#1

Hi,
I've scratched my head all day.... looking for some help to produce df_final from df
Thanks

# data frame I get after cleaning my data
df_ <- tibble(
  game_id = as.numeric(c("24786", "24786", "24786", "24786", "24786", "24786",
                         "24794", "24794", "24794", "24794", "24794", "24794")),
  team_id = as.numeric(c("6", "19", "19", "6", "19", "19",
                         "12", "12", "12", "12", "6", "6"))
) 

# data frame I want
df_final <- tibble(
  game_id = as.numeric(c("24786", "24786",
                         "24794", "24794")),
                       team_id = as.numeric(c("6", "19",
                                              "6", "12")),
                       GF = as.numeric(c("2", "4", "2", "4")),
                       GA = as.numeric(c("4", "2", "4", "2"))
  )
#2

First of all, what is thebenefit of as.numeric on a character vector instead of using a numeric vector directly?

Second, it may be obvious to you, but not to others (certainly not to me) what is GF or GA. Please provide that information, and what have you already tried to get those information.

Also, please confirm that GF or GA information can be obtained just from df_, without any other data source (may be other R objects in your environment or from domain information).

#3

Is this what you want to do? I suspect you have oversimplified your example

library(tidyverse)

df_ <- tibble(
    game_id = as.numeric(c("24786", "24786", "24786", "24786", "24786", "24786",
                           "24794", "24794", "24794", "24794", "24794", "24794")),
    team_id = as.numeric(c("6", "19", "19", "6", "19", "19",
                           "12", "12", "12", "12", "6", "6"))
)

df_ %>% 
    count(game_id, team_id, name = "GF") %>% 
    group_by(game_id) %>% 
    mutate(GA = sum(GF) - GF)
#> # A tibble: 4 × 4
#> # Groups:   game_id [2]
#>   game_id team_id    GF    GA
#>     <dbl>   <dbl> <int> <int>
#> 1   24786       6     2     4
#> 2   24786      19     4     2
#> 3   24794       6     2     4
#> 4   24794      12     4     2

Created on 2022-01-04 by the reprex package (v2.0.1)

#5

Thank you. I wasn't aware of the count function giving me the ability to create a column before a group_by. Much appreciated!

#6

If your problem is solved, please mark the thread as closed.

Just for my curiosity, can one of you (@turkjr19 or @andresrcs) please explain what was GF and GA? I thought it meant goal in favour and against, and that makes no sense to calculate from game and team ID's, so I was, and still am, very confused. Andre's solution, which apparently satisfied OP's requirements, didn't help me in guessing correct full forms of GF and GA, so it'd be nice to know. Thanks in advance.