How to sum values from two columns in R based on conditions from two other columns?

Question

enter image description here

       HomeTeam    AwayTeam FTHG FTAG FTR HST AST
1   Aston Villa       Wigan    0    2   A   5   7
26    Liverpool Aston Villa    1    3   A  12   4
34  Aston Villa      Fulham    2    0   H   4   3
45   Birmingham Aston Villa    0    1   A   7   5
48  Aston Villa  Portsmouth    2    0   H   5  12
58    Blackburn Aston Villa    2    1   H   7   5
76  Aston Villa    Man City    1    1   D   6   7

I have a dataframe 'mydata' part of which is shown above. It shows the results of soccer games in England.

I want to sum the goals scored by a team such as Aston Villa, possibly by looping through the dataframe. This would require counting Villa's FTHG when they are the HomeTeam, and Villa's FTAG when they are the away team.

I tried

with(mydata, sum(FTHG[HomeTeam == "Aston Villa"] | FTAG[AwayTeam == "Aston Villa"]))

and

sum(subset(mydata,(HomeTeam == "Aston Villa")$FTHG|(AwayTeam == "Aston Villa")$FTAG))

and

sum(subset(mydata,(HomeTeam == "Aston Villa")$FTHG|subset(mydata,(AwayTeam == "Aston Villa")$FTAG)))

none of which worked.

What I want is the total to be shown. Can this be done without summing the home and away data separately then adding?

Please don't add images of data/code, they are not helpful. Add data using `dput`. Read about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to give a [reproducible example](http://stackoverflow.com/questions/5963269). — Ronak Shah, Oct 12 '20 at 03:36

akrun · Answer 1 · 2020-10-11T22:14:13.473

1

Perhaps we can do

sum(unlist(Map(function(x, y) x[y],  mydata[c("FTHG", "FTAG")], 
  list(mydata$HomeTeam=="Aston Villa", mydata$AwayTeam=="Aston Villa"))))

edited Oct 11 '20 at 22:14

answered Oct 11 '20 at 20:33

akrun

874,273
37
540
662

score 1 · Answer 2 · answered Oct 11 '20 at 21:42

One base R option is via which

> sum(mydata[c("FTHG","FTAG")][which(mydata == "Aston Villa",arr.ind = TRUE)])
[1] 10

Data

> dput(mydata)
structure(list(HomeTeam = c("Aston Villa", "Liverpool", "Aston Villa", 
"Birmingham", "Aston Villa", "Blackburn", "Aston Villa"), AwayTeam = c("Wigan",
"Aston Villa", "Fulham", "Aston Villa", "Portsmouth", "Aston Villa",
"Man City"), FTHG = c(0L, 1L, 2L, 0L, 2L, 2L, 1L), FTAG = c(2L, 
3L, 0L, 1L, 0L, 1L, 1L), FTR = c("A", "A", "H", "A", "H", "H",
"D"), HST = c(5L, 12L, 4L, 7L, 5L, 7L, 6L), AST = c(7L, 4L, 3L,
5L, 12L, 5L, 7L)), class = "data.frame", row.names = c("1", "26",
"34", "45", "48", "58", "76"))

score 0 · Answer 3 · answered Oct 11 '20 at 21:23

This should do the trick:

sum(mydata[mydata$HomeTeam=="AstonVilla", "FTHG"], mydata[mydata$AwayTeam=="AstonVilla", "FTAG"])

You get the sum for all items in dataframe mydata that have column HomeTeam or AwayTeam for the given team (in this case AstonVilla).

How to sum values from two columns in R based on conditions from two other columns?

3 Answers3