Aggregate Function in R using Multiple Criteria

Question

I've been playing around with this formula for a while as I am trying to obtain the number of goals each football team has scored in my dataset. I am able to find out how many goals they have scored at home or away. But I'm struggling to do both combined.

Here's an example of the code I've done. I can add up the goals scored in the FTHG (Full-Time Home Goals) column, but at the moment it is based only on home team goals.

goalsScored <- aggregate(FTHG ~ HomeTeam, matchData, sum)

I know I've got to add the away goals as well, but I need it so it only counts the goals that specific team scores away. Here it is adding the home and away goals for every match the home team plays, rather than the amount that corresponds to whether they played at home or away.

goalsScored <- aggregate(FTHG + FTAG ~ HomeTeam, matchData, sum)

Here is an example table (matchData):

---------------------------------
HomeTeam | AwayTeam | FTHG | FTAG
TeamA    |  TeamB   |   3  |  1
TeamC    |  TeamD   |   1  |  0
TeamB    |  TeamA   |   2  |  2
TeamD    |  TeamC   |   0  |  3

The output should be like this (goalsScored):

---------------------------------
Team  | GoalsScored
TeamA |      5
TeamB |      3
TeamC |      4
TeamD |      0

Answer 1

Using your example:

df<-data.frame(HomeTeam = c("TeamA","TeamC","TeamB","TeamD"),
               AwayTeam = c("TeamB","TeamD","TeamA","TeamC"),
               FTHG = c(3,1,2,0),
               FTAG = c(1,0,2,3))

You can do this with Base R, but dplyr makes it easier to do it in steps. Indexing each team column and then binding them by rows using rbind will allow you to sum them up. You can save it as a separate dataframe if needed.

Here's your output:

library(dplyr)

rbind(data.frame(Team = df[,1], Goals_Scored = df[,3]),
      data.frame(Team = df[,2], Goals_Scored = df[,4])) %>%
  group_by(Team) %>% 
  summarize(Goals_Scored = sum(Goals_Scored))

Team  | Goals Scored
TeamA |      5
TeamB |      3
TeamC |      4
TeamD |      0