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>
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