# excel file
data <- read_excel("../00_data/MyData.xlsx")data_min <- data %>%
filter(Goals %% 1 == 0) %>%
select(Opponent, Shots, `Shots on goal`)
data_min## # A tibble: 18 × 3
## Opponent Shots `Shots on goal`
## <chr> <dbl> <dbl>
## 1 vs New England Pilgrims 5 2
## 2 @ University of New England Nor'easters 10 4
## 3 @ Babson Beavers 6 4
## 4 vs SUNY-Plattsburgh Cardinals 3 0
## 5 @ New England Pilgrims 4 2
## 6 vs Worcester State Lancers 10 4
## 7 vs Framingham State Rams 0 0
## 8 @ Salem State Univ. 6 1
## 9 vs Trinity Bantams 8 3
## 10 @ UMass Dartmouth Corsairs 6 3
## 11 vs Westfield State Owls 9 4
## 12 @ Hamilton Continentals 4 3
## 13 @ Williams Ephs 7 4
## 14 @ Rivier University Raiders 3 0
## 15 vs Fitchburg State Falcons 8 7
## 16 @ Worcester State Lancers 5 2
## 17 vs Massachusetts College of Liberal Arts Trailblazers 6 3
## 18 @ Anna Maria Amcats 9 6data_wide <- data_min %>%
pivot_wider(names_from = Shots,
values_from = `Shots on goal`)
data_wide## # A tibble: 18 × 10
## Opponent `5` `10` `6` `3` `4` `0` `8` `9` `7`
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 vs New England Pilgrims 2 NA NA NA NA NA NA NA NA
## 2 @ University of New En… NA 4 NA NA NA NA NA NA NA
## 3 @ Babson Beavers NA NA 4 NA NA NA NA NA NA
## 4 vs SUNY-Plattsburgh Ca… NA NA NA 0 NA NA NA NA NA
## 5 @ New England Pilgrims NA NA NA NA 2 NA NA NA NA
## 6 vs Worcester State Lan… NA 4 NA NA NA NA NA NA NA
## 7 vs Framingham State Ra… NA NA NA NA NA 0 NA NA NA
## 8 @ Salem State Univ. NA NA 1 NA NA NA NA NA NA
## 9 vs Trinity Bantams NA NA NA NA NA NA 3 NA NA
## 10 @ UMass Dartmouth Cors… NA NA 3 NA NA NA NA NA NA
## 11 vs Westfield State Owls NA NA NA NA NA NA NA 4 NA
## 12 @ Hamilton Continentals NA NA NA NA 3 NA NA NA NA
## 13 @ Williams Ephs NA NA NA NA NA NA NA NA 4
## 14 @ Rivier University Ra… NA NA NA 0 NA NA NA NA NA
## 15 vs Fitchburg State Fal… NA NA NA NA NA NA 7 NA NA
## 16 @ Worcester State Lanc… 2 NA NA NA NA NA NA NA NA
## 17 vs Massachusetts Colle… NA NA 3 NA NA NA NA NA NA
## 18 @ Anna Maria Amcats NA NA NA NA NA NA NA 6 NAdata_min %>%
separate(Opponent, into = c("Venue", "Team"), sep = 2) %>%
mutate(Team = str_trim(Team, side = "left"),
Venue = str_trim(Venue, side = "right"))## # A tibble: 18 × 4
## Venue Team Shots `Shots on goal`
## <chr> <chr> <dbl> <dbl>
## 1 vs New England Pilgrims 5 2
## 2 @ University of New England Nor'easters 10 4
## 3 @ Babson Beavers 6 4
## 4 vs SUNY-Plattsburgh Cardinals 3 0
## 5 @ New England Pilgrims 4 2
## 6 vs Worcester State Lancers 10 4
## 7 vs Framingham State Rams 0 0
## 8 @ Salem State Univ. 6 1
## 9 vs Trinity Bantams 8 3
## 10 @ UMass Dartmouth Corsairs 6 3
## 11 vs Westfield State Owls 9 4
## 12 @ Hamilton Continentals 4 3
## 13 @ Williams Ephs 7 4
## 14 @ Rivier University Raiders 3 0
## 15 vs Fitchburg State Falcons 8 7
## 16 @ Worcester State Lancers 5 2
## 17 vs Massachusetts College of Liberal Arts Trailblaze… 6 3
## 18 @ Anna Maria Amcats 9 6data %>%
unite(col = "Failed Shots", c(`Blocked shots`, `Missed shots`), sep = "/") %>%
select(Opponent, `Failed Shots`, Shots, `Shots on goal`)## # A tibble: 19 × 4
## Opponent `Failed Shots` Shots `Shots on goal`
## <chr> <chr> <dbl> <dbl>
## 1 vs New England Pilgrims 3/0 5 2
## 2 @ University of New England Nor'easters 2/4 10 4
## 3 @ Babson Beavers 0/2 6 4
## 4 vs SUNY-Plattsburgh Cardinals 3/0 3 0
## 5 @ New England Pilgrims 2/0 4 2
## 6 vs Worcester State Lancers 2/4 10 4
## 7 vs Framingham State Rams 0/0 0 0
## 8 @ Salem State Univ. 3/2 6 1
## 9 vs Trinity Bantams 5/0 8 3
## 10 @ UMass Dartmouth Corsairs 3/0 6 3
## 11 vs Westfield State Owls 3/2 9 4
## 12 @ Hamilton Continentals 0/1 4 3
## 13 @ Williams Ephs 2/1 7 4
## 14 @ Rivier University Raiders 3/0 3 0
## 15 vs Fitchburg State Falcons 0/1 8 7
## 16 @ Worcester State Lancers 3/0 5 2
## 17 vs Massachusetts College of Liberal Art… 3/0 6 3
## 18 @ Anna Maria Amcats 2/1 9 6
## 19 Average per game 1.77/1.1 5.8 3data %>%
mutate(`Failed Shots` = `Blocked shots` + `Missed shots`) %>%
select(Opponent, `Failed Shots`, Shots, `Shots on goal`)## # A tibble: 19 × 4
## Opponent `Failed Shots` Shots `Shots on goal`
## <chr> <dbl> <dbl> <dbl>
## 1 vs New England Pilgrims 3 5 2
## 2 @ University of New England Nor'easters 6 10 4
## 3 @ Babson Beavers 2 6 4
## 4 vs SUNY-Plattsburgh Cardinals 3 3 0
## 5 @ New England Pilgrims 2 4 2
## 6 vs Worcester State Lancers 6 10 4
## 7 vs Framingham State Rams 0 0 0
## 8 @ Salem State Univ. 5 6 1
## 9 vs Trinity Bantams 5 8 3
## 10 @ UMass Dartmouth Corsairs 3 6 3
## 11 vs Westfield State Owls 5 9 4
## 12 @ Hamilton Continentals 1 4 3
## 13 @ Williams Ephs 3 7 4
## 14 @ Rivier University Raiders 3 3 0
## 15 vs Fitchburg State Falcons 1 8 7
## 16 @ Worcester State Lancers 3 5 2
## 17 vs Massachusetts College of Liberal Art… 3 6 3
## 18 @ Anna Maria Amcats 3 9 6
## 19 Average per game 2.87 5.8 3