How to create new columns in a new data frame from information in an existing data frame in R-CodePudding

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!

CodePudding user response：

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