Import your data

soccer <- readxl::read_xlsx("../00_data/mydata.xlsx")

set.seed(1234)
soccer_small <- sample_n(soccer, 3) %>% select(Date, HomeTeam, AwayTeam, FTHG, FTAG, FTR)
soccer_small
## # A tibble: 3 × 6
##   Date       HomeTeam    AwayTeam     FTHG  FTAG FTR  
##   <chr>      <chr>       <chr>       <dbl> <dbl> <chr>
## 1 17/03/2022 Everton     Newcastle       1     0 H    
## 2 30/04/2022 Watford     Burnley         1     2 A    
## 3 44327      Southampton Aston Villa     1     0 H

Pivoting

long to wide form

soccer_long <- soccer %>%
    pivot_longer(cols = c('FTHG', 'FTAG'), 
                 names_to = "Location", 
                 values_to = "Goals")

wide to long form

soccer_wide <- soccer_long %>%
    pivot_wider(names_from = Location, 
                values_from = Goals)

knitr::kable(head(soccer_long, 10))
Date HomeTeam AwayTeam FTR HTHG HTAG HTR Referee HS AS HST AST HF AF HC AC HY AY HR AR Location Goals
13/08/2021 Brentford Arsenal H 1 0 H M Oliver 8 22 3 4 12 8 2 5 0 0 0 0 FTHG 2
13/08/2021 Brentford Arsenal H 1 0 H M Oliver 8 22 3 4 12 8 2 5 0 0 0 0 FTAG 0
14/08/2021 Man United Leeds H 1 0 H P Tierney 16 10 8 3 11 9 5 4 1 2 0 0 FTHG 5
14/08/2021 Man United Leeds H 1 0 H P Tierney 16 10 8 3 11 9 5 4 1 2 0 0 FTAG 1
14/08/2021 Burnley Brighton A 1 0 H D Coote 14 14 3 8 10 7 7 6 2 1 0 0 FTHG 1
14/08/2021 Burnley Brighton A 1 0 H D Coote 14 14 3 8 10 7 7 6 2 1 0 0 FTAG 2
14/08/2021 Chelsea Crystal Palace H 2 0 H J Moss 13 4 6 1 15 11 5 2 0 0 0 0 FTHG 3
14/08/2021 Chelsea Crystal Palace H 2 0 H J Moss 13 4 6 1 15 11 5 2 0 0 0 0 FTAG 0
14/08/2021 Everton Southampton H 0 1 A A Madley 14 6 6 3 13 15 6 8 2 0 0 0 FTHG 3
14/08/2021 Everton Southampton H 0 1 A A Madley 14 6 6 3 13 15 6 8 2 0 0 0 FTAG 1

Separating and Uniting

Unite two columns

soccer_united <- soccer %>%
  unite(
    col = "HS",
    HS,
    HST,
    sep = ", ",
    na.rm = TRUE
  )
knitr::kable(head(soccer_united, 10))
Date HomeTeam AwayTeam FTHG FTAG FTR HTHG HTAG HTR Referee HS AS AST HF AF HC AC HY AY HR AR
13/08/2021 Brentford Arsenal 2 0 H 1 0 H M Oliver 8, 3 22 4 12 8 2 5 0 0 0 0
14/08/2021 Man United Leeds 5 1 H 1 0 H P Tierney 16, 8 10 3 11 9 5 4 1 2 0 0
14/08/2021 Burnley Brighton 1 2 A 1 0 H D Coote 14, 3 14 8 10 7 7 6 2 1 0 0
14/08/2021 Chelsea Crystal Palace 3 0 H 2 0 H J Moss 13, 6 4 1 15 11 5 2 0 0 0 0
14/08/2021 Everton Southampton 3 1 H 0 1 A A Madley 14, 6 6 3 13 15 6 8 2 0 0 0
14/08/2021 Leicester Wolves 1 0 H 1 0 H C Pawson 9, 5 17 3 6 10 5 4 1 2 0 0
14/08/2021 Watford Aston Villa 3 2 H 2 0 H M Dean 13, 7 11 2 18 13 2 4 3 1 0 0
14/08/2021 Norwich Liverpool 0 3 A 0 1 A A Marriner 14, 3 19 8 4 14 3 11 1 1 0 0
15/08/2021 Newcastle West Ham 2 4 A 2 1 H M Atkinson 17, 3 8 9 4 3 7 6 1 0 0 0
15/08/2021 Tottenham Man City 1 0 H 0 0 D A Taylor 13, 3 18 4 11 8 3 11 2 1 0 0

Separate a column

soccer_separated <- soccer_united %>%
  separate(
    col = HS,
    into = c("HS", "HST"),
    sep = ", ",
    fill = "right",
    extra = "drop"
  )
knitr::kable(head(soccer_separated, 10))
Date HomeTeam AwayTeam FTHG FTAG FTR HTHG HTAG HTR Referee HS HST AS AST HF AF HC AC HY AY HR AR
13/08/2021 Brentford Arsenal 2 0 H 1 0 H M Oliver 8 3 22 4 12 8 2 5 0 0 0 0
14/08/2021 Man United Leeds 5 1 H 1 0 H P Tierney 16 8 10 3 11 9 5 4 1 2 0 0
14/08/2021 Burnley Brighton 1 2 A 1 0 H D Coote 14 3 14 8 10 7 7 6 2 1 0 0
14/08/2021 Chelsea Crystal Palace 3 0 H 2 0 H J Moss 13 6 4 1 15 11 5 2 0 0 0 0
14/08/2021 Everton Southampton 3 1 H 0 1 A A Madley 14 6 6 3 13 15 6 8 2 0 0 0
14/08/2021 Leicester Wolves 1 0 H 1 0 H C Pawson 9 5 17 3 6 10 5 4 1 2 0 0
14/08/2021 Watford Aston Villa 3 2 H 2 0 H M Dean 13 7 11 2 18 13 2 4 3 1 0 0
14/08/2021 Norwich Liverpool 0 3 A 0 1 A A Marriner 14 3 19 8 4 14 3 11 1 1 0 0
15/08/2021 Newcastle West Ham 2 4 A 2 1 H M Atkinson 17 3 8 9 4 3 7 6 1 0 0 0
15/08/2021 Tottenham Man City 1 0 H 0 0 D A Taylor 13 3 18 4 11 8 3 11 2 1 0 0

Missing Values