0

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!

IAwa
  • 5
  • 3

1 Answers1

0
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
br00t
  • 1,440
  • 8
  • 10
  • The output is the column name Record which i believe has three sub columns Wins, losses and Draws. How do i go about that? – IAwa Oct 23 '22 at 19:30
  • You could combine the wins, losses and draws column into a single one by doing: `df2$Record <- sprintf('%s / %s / %s', df2$Wins, df2$Losses, df2$Draws)` or something similar. It is encumbent on you to decide what you want to do with your results. – br00t Oct 23 '22 at 20:14
  • 1
    Thank you! Also can you please tell how can I calculate a column from this data of TotalMatchesPlayed by each team. I will be grateful! – IAwa Oct 24 '22 at 03:12
  • Please see: `?rowSums` – br00t Oct 24 '22 at 12:56
  • Its not really working. Can you give the whole logic? – IAwa Oct 25 '22 at 00:36
  • `df2$GamesPlayed <- rowSums(df2[ , c('Wins', 'Losses', 'Draws') ]` – br00t Oct 25 '22 at 12:56