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:
- count the number of home wins a team has had in a season
- average the home win odds for the whole season (only for the team's home games)
- display as separate records — in the actual dataset there are 20 teams for each season so therefore there will be 20 records for each season.
I appreciate in advance anyone who can help me with this.