CODEWITHSUNDEEP
rsumgroupingaverageframe
Statto
Statto

Reputation: 468

Count Wins and Average Home Win Odds in R

I'm trying to create a data frame in R that will allow me to view the average home betting odds for each team along with the number of home wins for each season.

There are 6,840 records in the dataset representing 18 seasons' worth of Premier League football. This means there are 380 match entries for each season.

Let me show you an example. It is a drastically cut down example, but it gives you a good enough idea about what I'm trying to achieve.

Key: FTHG (Full-Time Home Goals), FTAG (Full-Time Away Goals), FTR (Full-Time Result), HWO (Home Win Odds), AHWO (Average Home Win Odds), W (Win Count)

matchData:

       Season   | HomeTeam | AwayTeam | FTHG | FTAG | FTR | HWO
-----------------------------------------------------------------
1  |  2017/2018 |  TeamA   |  TeamB   |   2  |   1  |  H  | 1.30
2  |  2017/2018 |  TeamA   |  TeamC   |   1  |   1  |  D  | 1.45
3  |  2017/2018 |  TeamA   |  TeamD   |   1  |   0  |  H  | 2.20
4  |  2017/2018 |  TeamB   |  TeamA   |   4  |   1  |  H  | 1.85
5  |  2017/2018 |  TeamC   |  TeamA   |   1  |   0  |  H  | 1.70
6  |  2017/2018 |  TeamD   |  TeamA   |   2  |   3  |  A  | 3.10
7  |  2016/2017 |  TeamA   |  TeamB   |   2  |   1  |  H  | 1.30
8  |  2016/2017 |  TeamA   |  TeamC   |   0  |   0  |  D  | 1.50
9  |  2016/2017 |  TeamA   |  TeamD   |   1  |   2  |  A  | 1.67
10 |  2016/2017 |  TeamB   |  TeamA   |   3  |   1  |  H  | 1.42
11 |  2016/2017 |  TeamB   |  TeamC   |   2  |   1  |  H  | 1.90
12 |  2016/2017 |  TeamB   |  TeamD   |   5  |   1  |  H  | 1.20
13 |  2016/2017 |  TeamC   |  TeamA   |   1  |   0  |  H  | 2.00
14 |  2016/2017 |  TeamC   |  TeamB   |   3  |   1  |  H  | 1.80

I need to summarise the matchData data frame into a new one like this:

homeWinOdds:

       Season   |   Team  | W | AHWO
-------------------------------------
1  |  2017/2018 |  TeamA  | 2 | 1.75
2  |  2017/2018 |  TeamB  | 1 | 1.85
3  |  2017/2018 |  TeamC  | 1 | 1.70
4  |  2017/2018 |  TeamD  | 0 | 3.10
5  |  2016/2017 |  TeamA  | 1 | 1.49
6  |  2016/2017 |  TeamB  | 3 | 1.51
7  |  2016/2017 |  TeamC  | 2 | 1.90
8  |  2016/2017 |  TeamD  | 0 | N/A

For instance, based on the above, TeamB won three home matches in season 2016/2017 and their average home odds (based on all of their home matches in that season) were 1.51.

In my actual dataset, each one of the 20 teams will have each played exactly 19 home matches in every season, so the home odds of these matches will be averaged.

In summary:

I appreciate in advance anyone who can help me with this.

Upvotes: 1

Views: 357

Answers (1)

Jon Spring
Jon Spring

Reputation: 66880

library(dplyr)
homeWinOdds <- matchData %>% 
  group_by(Season, HomeTeam) %>% 
  summarize(W    = sum(FTR == "H"), 
            AHWO = mean(HWO)) %>%
  ungroup()

Upvotes: 0

Related Questions