Help arrange output

General
1

I need to arrange the output as such: arrange(desc(PPM), desc(Wins), desc(GSM), GAM)
but I am having trouble with the placement. Any ideas?
CODE BELOW
Thanks!

#Function
EPL_Standings <- function(date, season){

options(dplyr.summarise.inform = FALSE)

#Data Frames
s2021 <- read_csv("http://www.football-data.co.uk/mmz4281/2021/E0.csv")
s2021$Season <- factor("2020/21")
s1920 <- read_csv("http://www.football-data.co.uk/mmz4281/1920/E0.csv")
s1920$Season <- factor("2019/20")

#Select the columns needed for assignment
EPL_data <- bind_rows(s2021, s1920, s1819) %>% select(Date,HomeTeam,AwayTeam,FTHG,FTAG,FTR)%>%

#Use lubridate to change the date to read Month/Day/Year
mutate(Date = dmy(Date))%>%
filter(Date <= mdy(date))

#Make a column called 'Team'
Home <- EPL_data%>%
select(Date,Team = HomeTeam,FTHG,FTAG,FTR)%>%
mutate(HomeAway = 'Home')
Away <- EPL_data%>%
select(Date,Team = AwayTeam,FTHG,FTAG,FTR)%>%
mutate(HomeAway = 'Away')
#Join these sets
team_data <- bind_rows(Home, Away)%>%
arrange(Team, desc(Date))%>%
#Check to see if the team won or lost
mutate(Home_Win = ifelse (HomeAway == 'Home' & FTHG > FTAG, 1, 0),
Away_Win = ifelse (HomeAway == 'Away' & FTAG > FTHG, 1, 0),
Home_Tie = ifelse (HomeAway == 'Home' & FTHG == FTAG, 1, 0),
Away_Tie = ifelse (HomeAway == 'Away' & FTAG == FTHG, 1, 0),
Home_Loss = ifelse (HomeAway == 'Home' & FTHG < FTAG, 1, 0),
Away_Loss = ifelse (HomeAway == 'Away' & FTAG < FTHG, 1, 0),
#Goals Scored for Home and Away
Goals_Scored = ifelse (HomeAway == 'Home', FTHG, FTAG),
#Goals Allowed = Goals by the away team
Goals_Allowed = ifelse (HomeAway == 'Home', FTAG, FTHG),
#Returns 1 for win, 0 for loss
Wins1 = ifelse (Home_Win == 1 | Away_Win == 1, 1, 0),
#Returns 1 for loss, 0 for win
Losses1 = ifelse (Home_Loss == 1 | Away_Loss == 1, 1, 0),
#Returns 1 if tie (Draw), 0 if not
Tie = ifelse (Home_Tie == 1 | Away_Tie == 1, 1, 0),)%>%
#Grouping by team
group_by(Team)%>%

#Using variables created above to calculate the following:
#Wins is the sum of Wins1 created above
summarize(Wins = sum(Wins1),
#Ttl Losses is the sum of Losses1 created above
Losses = sum(Losses1),
#Ttl Draws is the sum of Tie(s) created above
Draws = sum(Tie),
#Ttl Wins by the Home team created above
HomeWins = sum(Home_Win),
#Ttl Losses by the Home Team created above
HomeLosses = sum(Home_Loss),
#Ttl Draws for the Home Team by using Home_Tie created above
HomeDraws = sum(Home_Tie),
#Ttl wins by the Away team from Away_Win created above
AwayWins = sum(Away_Win),
#Ttl Losses by the Away team from the Away_Loss created above
AwayLosses = sum(Away_Loss),
#Ttl Draws by the Away team from Away_tie created above
AwayDraws = sum(Away_Tie),
#Ttl Goals Scored for the season using Goals_Scored created above
GS = sum(Goals_Scored),
#Ttl Goals Allowed (scored by the other team by using Goals_Allowed created above)
GA = sum(Goals_Allowed),
#Calculated Points by multiplying the wins by 3, and then adding the total ties (each worth 1 point)
Points = (3 * sum(Wins1)) + sum(Tie),
#Getting the matches played by each team
MatchesPlayed = n(),
#The following helps us get the team's record over the last 10 games
Last10Wins = sum(Wins1[1:10]),
Last10Losses = sum(Losses1[1:10]),
Last10Draws = sum(Tie[1:10]),
min_win = which.min(Wins1 == 1),
min_losses = which.min(Losses1 == 1),
min_draws = which.min(Tie == 1),
length = min(c(min_win, min_losses, min_draws)[c(min_win, min_losses, min_draws) > 1]))%>%

#Using the above variables to calculate PPM, PTPCT, GSM, and GAM and creating columns for each new variable
#Points per match = Points divided by MatchesPlayed
mutate(PPM = Points / MatchesPlayed,
#Point percentage calculated by dividing Points by 3 X MatchesPlayed
PtPct = Points / (3 * MatchesPlayed),
#Goals scored per match is Goals Scored (GS) divided by MatchesPlayed
GSM = GS / MatchesPlayed,
#Goals allowed per match is Goals Allowed (GA) divided by MatchesPlayed
GAM = GA / MatchesPlayed,
#Create Record Column
Record = str_c(Wins, "-",Losses, "-", Draws),
#Create HomeRec Column for the home team's record
HomeRec = str_c(HomeWins, "-", HomeLosses, "-", HomeDraws),
#Create AwayRec Column for the away team's record
AwayRec = str_c(AwayWins, "-", AwayLosses, "-", AwayDraws),
#Last 10 Games Played for each team to find the last 10 wins, losses, draws; (W, L, D)
Last10 = str_c(Last10Wins, "-", Last10Losses, "-", Last10Draws),
#Find the latest Streak
streak_type = ifelse(min_win == 1, 'W', 'D'),
streak_type = ifelse(min_losses == 1, 'L', streak_type),
Streak = str_c(streak_type, length))

#Return only the needed columns
return(team_data %>% select(Team,Record,HomeRec,AwayRec,MatchesPlayed,Points,PPM,PtPct,GS,GSM,GA,GAM,Last10,Streak))
}

EPL_Standings("11/30/2018","2018/19")

2

Added this:

  s1819 <- read_csv("http://www.football-data.co.uk/mmz4281/1819/E0.csv")
  s1819$Season <- factor("2018/19")

There also wasn't a Wins column so that had to be created.

epl <- EPL_Standings("11/30/2018","2018/19") 

epl %>% 
  mutate(Record2 = Record) %>% 
  separate(Record2, c("Wins", "Losses", "Draws")) %>% 
  mutate_at(c("Wins", "Losses", "Draws"), ~as.numeric(.)) %>% 
  arrange(desc(PPM), desc(Wins), desc(GSM), GAM)

Should get you what you want.

3

I'm sorry, I forgot to mention that I cannot add anything under the function input EPL_Standings ("11/30/2018", "2018/19) because that is just me testing the function. The prof will put that in when he looks over it. The Wins column is hard to see but it is the first statement in the summarize function. So in that case, where should this arrange command be placed? It must be inside the function. I have changed the beginning dataset command to be
url = paste("http://www.football-data.co.uk/mmz4281/",gsub("/","",substr(season, 3,7)),"/E0.csv",sep="")
EPL_data = read.csv(url) %>%

To account for more seasons.

4

This at the end of the function.

#Return only the needed columns
  return(team_data %>% 
           select(Team,Record,HomeRec,AwayRec,MatchesPlayed,Points,PPM,PtPct,GS,GSM,GA,GAM,Last10,Streak) %>% 
           mutate(Record2 = Record) %>% 
           separate(Record2, c("Wins", "Losses", "Draws")) %>% 
           mutate_at(c("Wins", "Losses", "Draws"), ~as.numeric(.)) %>% 
           arrange(desc(PPM), desc(Wins), desc(GSM), GAM))

You didn't select to be returned.

5

You are amazing and a life saver and thank you thank you thank you!!!!!!!!!! Oh, it worked btw! :slight_smile:

1 Like
6

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.