transform 2 rows of a single event into 1 row of multiple columns

[Garm-Bel-Iblis]

Hello all,

I would like some help in transforming this data frame:

shots_df <- tibble(
  Venue = c("Away", "Home"),
  Opponent = c("A", "B"),
  Shots = c(6, 16),
  ShotsOnTarget = c(1, 8),
  Team = c("B", "A"),
  match_name = "A_B"
)

shots_df

Where there are stats for a single game with a separate row for each team, to something like this:

shots_transformed_df <- tibble(shots_home = 6,
       shots_away = 16,
       SOT_home = 1,
       SOT_away = 8,
       team_home = "A",
       team_away = "B",
       match_name = "A_B")

shots_transformed_df

Where a single game's stats are all in one row with columns for each stat for the home team and away team separated. I have tried several methods such as paste(collapse = ", ") to get the values in a stat column for each row into one value but then I have trouble trying to place them in the correct new column.

Any help would be appreciated!

[FJCC]

I think this is what you want but that you have the home and away labels mostly interchanged in shots_transformed_df. Maybe I am reading your table incorrectly.

library(tidyr)
shots_df <- tibble(
  Venue = c("Away", "Home"),
  Opponent = c("A", "B"),
  Shots = c(6, 16),
  ShotsOnTarget = c(1, 8),
  Team = c("B", "A"),
  match_name = "A_B"
)
shots_df
#> # A tibble: 2 x 6
#>   Venue Opponent Shots ShotsOnTarget Team  match_name
#>   <chr> <chr>    <dbl>         <dbl> <chr> <chr>     
#> 1 Away  A            6             1 B     A_B       
#> 2 Home  B           16             8 A     A_B

shots_transformed_df <- tibble(shots_home = 6,
                               shots_away = 16,
                               SOT_home = 1,
                               SOT_away = 8,
                               team_home = "A",
                               team_away = "B",
                               match_name = "A_B")

shots_transformed_df
#> # A tibble: 1 x 7
#>   shots_home shots_away SOT_home SOT_away team_home team_away match_name
#>        <dbl>      <dbl>    <dbl>    <dbl> <chr>     <chr>     <chr>     
#> 1          6         16        1        8 A         B         A_B

Trans <- pivot_wider(shots_df, id_cols = match_name, names_from = c("Venue"), 
                     values_from = Shots:Team)
Trans
#> # A tibble: 1 x 7
#>   match_name Shots_Away Shots_Home ShotsOnTarget_A~ ShotsOnTarget_H~ Team_Away
#>   <chr>           <dbl>      <dbl>            <dbl>            <dbl> <chr>    
#> 1 A_B                 6         16                1                8 B        
#> # ... with 1 more variable: Team_Home <chr>

^{Created on 2020-08-16 by the reprex package (v0.3.0)}

[Garm-Bel-Iblis]

this is great, thank you!