I have an existing data frame with these columns. The home teams played against the Away Team in the sequence mentioned. In Results column H denotes that Home Team won, P denotes AwayTeam won and D denotes that it was a draw.
HomeTeam = Liverpool, Brighton, Birmingham, Manchester, Portsmouth
Away Team = Netherland, Austria, Cambodia, Netherlands, Austria
Results = H,H,P,D,H
My new data frame consists of column 'TeamName' where it shows the total number of teams playing the series.
TeamName = Liverpool, Brighton, Birmingham, Manchester, Netherland, Austria, Cambodia, Portsmouth
I want to add a column in the new data frame named 'Record' that record as wins, losses and ties for each team.
I am new in R so any help will be great! Thanks!
library(data.table)
df1 <- data.frame(HomeTeam = c('Liverpool', 'Brighton', 'Birmingham', 'Manchester', 'Portsmouth'),
AwayTeam = c('Netherlands', 'Austria', 'Cambodia', 'Netherlands', 'Austria'),
Results = c('H','H','P','D','H'))
teams <- c(df1$HomeTeam, df1$AwayTeam) |> unique() |> sort()
df2 <- lapply(teams, function(x) {
wins <- 0
losses <- 0
draws <- 0
idx <- which(df1$HomeTeam == x)
wins <- sum(df1[ idx, 'Results' ] == 'H')
losses <- sum(df1[ idx, 'Results' ] == 'P')
draws <- sum(df1[ idx, 'Results' ] == 'D')
idx <- which(df1$AwayTeam == x)
wins <- wins + sum(df1[ idx, 'Results' ] == 'P')
losses <- losses + sum(df1[ idx, 'Results' ] == 'H')
draws <- draws + sum(df1[ idx, 'Results' ] == 'D')
data.frame(TeamName = x, Wins = wins, Losses = losses, Draws = draws)
}) |>
rbindlist() |>
as.data.frame()
df2 |> print()
Output:
TeamName Wins Losses Draws
1 Austria 0 2 0
2 Birmingham 0 1 0
3 Brighton 1 0 0
4 Cambodia 1 0 0
5 Liverpool 1 0 0
6 Manchester 0 0 1
7 Netherlands 0 1 1
8 Portsmouth 1 0 0