Import 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>
Apply the following dplyr verbs to your data
Filter rows
filter(prem, FTHG == 1, FTAG == 1)
## # A tibble: 42 × 22
## Date HomeTeam AwayTeam FTHG FTAG FTR HTHG HTAG HTR Referee HS
## <chr> <chr> <chr> <dbl> <dbl> <chr> <dbl> <dbl> <chr> <chr> <dbl>
## 1 25/09/20… Watford Newcast… 1 1 D 0 1 A J Gill… 15
## 2 06/11/20… Brighton Newcast… 1 1 D 1 0 H D Coote 14
## 3 15/05/20… Leeds Brighton 1 1 D 0 1 A M Dean 19
## 4 28/08/20… Aston V… Brentfo… 1 1 D 1 1 D P Bank… 7
## 5 23/10/20… Leeds Wolves 1 1 D 0 1 A R Jones 18
## 6 04/12/20… Southam… Brighton 1 1 D 1 0 H A Tayl… 14
## 7 28/12/20… Southam… Tottenh… 1 1 D 1 1 D A Tayl… 9
## 8 02/04/20… Leeds Southam… 1 1 D 1 0 H A Tayl… 12
## 9 29/12/20… Chelsea Brighton 1 1 D 1 0 H M Dean 11
## 10 28/11/20… Chelsea Man Uni… 1 1 D 0 0 D A Tayl… 24
## # ℹ 32 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>
Arrange rows
arrange(prem, HomeTeam, AwayTeam)
## # 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 22/10/20… Arsenal Aston V… 3 1 H 2 0 H C Paws… 22
## 2 19/02/20… Arsenal Brentfo… 2 1 H 0 0 D J Moss 24
## 3 09/04/20… Arsenal Brighton 1 2 A 0 1 A D Coote 20
## 4 23/01/20… Arsenal Burnley 0 0 D 0 0 D D Coote 20
## 5 22/08/20… Arsenal Chelsea 0 2 A 0 2 A P Tier… 6
## 6 18/10/20… Arsenal Crystal… 2 2 D 1 0 H M Dean 17
## 7 22/05/20… Arsenal Everton 5 1 H 2 1 H A Marr… 26
## 8 08/05/20… Arsenal Leeds 2 1 H 2 0 H C Kava… 19
## 9 13/03/20… Arsenal Leicest… 2 0 H 1 0 H A Tayl… 21
## 10 16/03/20… Arsenal Liverpo… 0 2 A 0 0 D A Marr… 9
## # ℹ 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>
Select columns
select(prem, HomeTeam: FTR)
## # A tibble: 380 × 5
## HomeTeam AwayTeam FTHG FTAG FTR
## <chr> <chr> <dbl> <dbl> <chr>
## 1 Newcastle Aston Villa 1 0 H
## 2 Watford Newcastle 1 1 D
## 3 Watford Chelsea 1 2 A
## 4 Watford Aston Villa 3 2 H
## 5 Leeds Norwich 2 1 H
## 6 Chelsea Newcastle 1 0 H
## 7 Chelsea Tottenham 2 0 H
## 8 Liverpool Crystal Palace 3 0 H
## 9 Brentford Aston Villa 2 1 H
## 10 Brighton Aston Villa 0 2 A
## # ℹ 370 more rows
select(prem, HomeTeam: FTR, HS, AS, HR, AR)
## # A tibble: 380 × 9
## HomeTeam AwayTeam FTHG FTAG FTR HS AS HR AR
## <chr> <chr> <dbl> <dbl> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 Newcastle Aston Villa 1 0 H 10 11 0 0
## 2 Watford Newcastle 1 1 D 15 20 0 0
## 3 Watford Chelsea 1 2 A 13 8 0 0
## 4 Watford Aston Villa 3 2 H 13 11 0 0
## 5 Leeds Norwich 2 1 H 13 12 0 0
## 6 Chelsea Newcastle 1 0 H 8 7 0 0
## 7 Chelsea Tottenham 2 0 H 15 6 0 0
## 8 Liverpool Crystal Palace 3 0 H 25 13 0 0
## 9 Brentford Aston Villa 2 1 H 10 16 0 0
## 10 Brighton Aston Villa 0 2 A 12 9 0 0
## # ℹ 370 more rows
select(prem, HomeTeam, FTHG, FTR, starts_with("H"))
## # A tibble: 380 × 12
## HomeTeam FTHG FTR HTHG HTAG HTR HS HST HF HC HY HR
## <chr> <dbl> <chr> <dbl> <dbl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 Newcastle 1 H 1 0 H 10 2 22 3 3 0
## 2 Watford 1 D 0 1 A 15 4 19 5 4 0
## 3 Watford 1 A 1 1 D 13 6 18 6 4 0
## 4 Watford 3 H 2 0 H 13 7 18 2 3 0
## 5 Leeds 2 H 1 0 H 13 7 18 6 3 0
## 6 Chelsea 1 H 0 0 D 8 3 17 7 4 0
## 7 Chelsea 2 H 0 0 D 15 7 17 10 2 0
## 8 Liverpool 3 H 1 0 H 25 10 17 10 3 0
## 9 Brentford 2 H 1 1 D 10 4 17 3 2 0
## 10 Brighton 0 A 0 1 A 12 1 17 7 4 0
## # ℹ 370 more rows
select(prem, contains("A"))
## # A tibble: 380 × 11
## Date HomeTeam AwayTeam FTAG HTAG AS AST AF AC AY AR
## <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 13/02/2022 Newcastle Aston V… 0 0 11 1 15 6 4 0
## 2 25/09/2021 Watford Newcast… 1 1 20 6 12 6 3 0
## 3 01/12/2021 Watford Chelsea 2 1 8 4 16 0 3 0
## 4 14/08/2021 Watford Aston V… 2 0 11 2 13 4 1 0
## 5 13/03/2022 Leeds Norwich 1 0 12 4 8 2 1 0
## 6 13/03/2022 Chelsea Newcast… 0 0 7 2 16 2 3 0
## 7 23/01/2022 Chelsea Tottenh… 0 0 6 3 14 3 1 0
## 8 18/09/2021 Liverpool Crystal… 0 0 13 2 11 5 1 0
## 9 02/01/2022 Brentford Aston V… 1 1 16 5 11 7 2 0
## 10 26/02/2022 Brighton Aston V… 2 1 9 4 11 2 5 0
## # ℹ 370 more rows
Add columns
prem <- mutate(prem,
HTGD = FTHG - FTAG) %>%
select(HomeTeam: FTR, HTGD) %>%
arrange(desc(HTGD))
Summarize by groups
prem %>%
# Group by team
group_by(HomeTeam) %>%
# Calculate Goal Differential
summarise(GD = mean(HTGD, na.rm = TRUE)) %>%
#Sort it
arrange(GD)
## # A tibble: 20 × 2
## HomeTeam GD
## <chr> <dbl>
## 1 Norwich -1.63
## 2 Watford -1.53
## 3 Leeds -1
## 4 Burnley -0.368
## 5 Wolves -0.263
## 6 Brighton -0.211
## 7 Southampton -0.0526
## 8 Newcastle -0.0526
## 9 Aston Villa 0
## 10 Brentford 0.0526
## 11 Everton 0.105
## 12 West Ham 0.368
## 13 Man United 0.526
## 14 Crystal Palace 0.526
## 15 Leicester 0.579
## 16 Chelsea 0.789
## 17 Arsenal 0.947
## 18 Tottenham 1
## 19 Liverpool 2.11
## 20 Man City 2.26