Import your data

# excel file
prem <- read_excel("../00_data/Prem.xlsx")
prem
## # A tibble: 380 × 22
##    Date      HomeTeam AwayTeam  FTHG  FTAG FTR    HTHG  HTAG HTR   Referee    HS
##    <chr>     <chr>    <chr>    <dbl> <dbl> <chr> <dbl> <dbl> <chr> <chr>   <dbl>
##  1 13/02/20… Newcast… Aston V…     1     0 H         1     0 H     C Paws…    10
##  2 25/09/20… Watford  Newcast…     1     1 D         0     1 A     J Gill…    15
##  3 01/12/20… Watford  Chelsea      1     2 A         1     1 D     D Coote    13
##  4 14/08/20… Watford  Aston V…     3     2 H         2     0 H     M Dean     13
##  5 13/03/20… Leeds    Norwich      2     1 H         1     0 H     S Attw…    13
##  6 13/03/20… Chelsea  Newcast…     1     0 H         0     0 D     D Coote     8
##  7 23/01/20… Chelsea  Tottenh…     2     0 H         0     0 D     P Tier…    15
##  8 18/09/20… Liverpo… Crystal…     3     0 H         1     0 H     A Madl…    25
##  9 02/01/20… Brentfo… Aston V…     2     1 H         1     1 D     C Paws…    10
## 10 26/02/20… Brighton Aston V…     0     2 A         0     1 A     J Broo…    12
## # ℹ 370 more rows
## # ℹ 11 more variables: AS <dbl>, HST <dbl>, AST <dbl>, HF <dbl>, AF <dbl>,
## #   HC <dbl>, AC <dbl>, HY <dbl>, AY <dbl>, HR <dbl>, AR <dbl>

Pivoting

long to wide form

prem_long <- prem %>%
  pivot_longer(
    cols = c(HS, AS),
    names_to = "team_side",
    values_to = "shots"
  )

wide to long form

prem_long %>%
  pivot_wider(names_from = team_side,
              values_from = shots)
## # A tibble: 380 × 22
##    Date      HomeTeam AwayTeam  FTHG  FTAG FTR    HTHG  HTAG HTR   Referee   HST
##    <chr>     <chr>    <chr>    <dbl> <dbl> <chr> <dbl> <dbl> <chr> <chr>   <dbl>
##  1 13/02/20… Newcast… Aston V…     1     0 H         1     0 H     C Paws…     2
##  2 25/09/20… Watford  Newcast…     1     1 D         0     1 A     J Gill…     4
##  3 01/12/20… Watford  Chelsea      1     2 A         1     1 D     D Coote     6
##  4 14/08/20… Watford  Aston V…     3     2 H         2     0 H     M Dean      7
##  5 13/03/20… Leeds    Norwich      2     1 H         1     0 H     S Attw…     7
##  6 13/03/20… Chelsea  Newcast…     1     0 H         0     0 D     D Coote     3
##  7 23/01/20… Chelsea  Tottenh…     2     0 H         0     0 D     P Tier…     7
##  8 18/09/20… Liverpo… Crystal…     3     0 H         1     0 H     A Madl…    10
##  9 02/01/20… Brentfo… Aston V…     2     1 H         1     1 D     C Paws…     4
## 10 26/02/20… Brighton Aston V…     0     2 A         0     1 A     J Broo…     1
## # ℹ 370 more rows
## # ℹ 11 more variables: AST <dbl>, HF <dbl>, AF <dbl>, HC <dbl>, AC <dbl>,
## #   HY <dbl>, AY <dbl>, HR <dbl>, AR <dbl>, HS <dbl>, AS <dbl>

Separating and Uniting

Separate a column

prem_sep <- prem %>%
  separate(col = Date, into = c("day", "month", "year"), sep = "/")

Unite two columns

prem_sep %>%
  unite(col = "Date", c(day, month, year), sep = "/")
## # A tibble: 380 × 22
##    Date      HomeTeam AwayTeam  FTHG  FTAG FTR    HTHG  HTAG HTR   Referee    HS
##    <chr>     <chr>    <chr>    <dbl> <dbl> <chr> <dbl> <dbl> <chr> <chr>   <dbl>
##  1 13/02/20… Newcast… Aston V…     1     0 H         1     0 H     C Paws…    10
##  2 25/09/20… Watford  Newcast…     1     1 D         0     1 A     J Gill…    15
##  3 01/12/20… Watford  Chelsea      1     2 A         1     1 D     D Coote    13
##  4 14/08/20… Watford  Aston V…     3     2 H         2     0 H     M Dean     13
##  5 13/03/20… Leeds    Norwich      2     1 H         1     0 H     S Attw…    13
##  6 13/03/20… Chelsea  Newcast…     1     0 H         0     0 D     D Coote     8
##  7 23/01/20… Chelsea  Tottenh…     2     0 H         0     0 D     P Tier…    15
##  8 18/09/20… Liverpo… Crystal…     3     0 H         1     0 H     A Madl…    25
##  9 02/01/20… Brentfo… Aston V…     2     1 H         1     1 D     C Paws…    10
## 10 26/02/20… Brighton Aston V…     0     2 A         0     1 A     J Broo…    12
## # ℹ 370 more rows
## # ℹ 11 more variables: AS <dbl>, HST <dbl>, AST <dbl>, HF <dbl>, AF <dbl>,
## #   HC <dbl>, AC <dbl>, HY <dbl>, AY <dbl>, HR <dbl>, AR <dbl>

Missing Values

prem <- tibble(
  HomeTeam = c("Man City", "Man City", "Chelsea", "Chelsea", "Liverpool"),
  FTR = c("H", "D", "H", "A", "H"),
  FTHG = c(2, 0, 3, 1, 4)
)
prem %>%
  pivot_wider(names_from = HomeTeam, values_from = FTHG)
## # A tibble: 3 × 4
##   FTR   `Man City` Chelsea Liverpool
##   <chr>      <dbl>   <dbl>     <dbl>
## 1 H              2       3         4
## 2 D              0      NA        NA
## 3 A             NA       1        NA
prem %>%
  complete(HomeTeam, FTR)
## # A tibble: 9 × 3
##   HomeTeam  FTR    FTHG
##   <chr>     <chr> <dbl>
## 1 Chelsea   A         1
## 2 Chelsea   D        NA
## 3 Chelsea   H         3
## 4 Liverpool A        NA
## 5 Liverpool D        NA
## 6 Liverpool H         4
## 7 Man City  A        NA
## 8 Man City  D         0
## 9 Man City  H         2