Sum of values in two different columns based on other columns in R

Question

Hello,

I’m trying to make the sum of goals made and goals suffered from a set of dice, someone can help me?

Basically I have the data as follows:

Mandante    Gols Feitos    Gols Sofridos    Visitante
Cruzeiro         0              1            Grêmio
Vitória          2              2            Flamengo
   .             .              .               .
   .             .              .               .
Fluminense       1              0            Cruzeiro

So I want to make a table, like for example:

Time       Gols Feitos    Gols Sofridos
Cruzeiro        0              2
Vitória         2              2
Fluminense      1              0

The question boils down to, how to add up the number of goals made when sending and the number of goals "suffered" when visiting. This is relatively simple to do for a team, but I want something that works for all teams. Any suggestions?

Data sample:

structure(list(Mandante = c("Cruzeiro", "Vitória", "Santos",
"América-MG", "Internacional", "Corinthians", "Vasco", "Atlético- 
PR","São Paulo", "Botafogo", "Bahia", "Flamengo", "Fluminense", "Paraná", 
"Ceará", "Chapecoense", "Palmeiras", "Atlético-MG", "Grêmio", 
"Sport"), `Gols feitos` = c(0, 2, 2, 3, 2, 2, 2, 5, 1, 1, 1, 
2, 1, 0, 0, 1, 1, 2, 0, 1), `Gols sofridos` = c(1, 2, 0, 0, 0, 
1, 1, 1, 0, 1, 0, 0, 0, 4, 0, 1, 0, 1, 0, 1), Visitante = c("Grêmio", 
"Flamengo", "Ceará", "Sport", "Bahia", "Fluminense", "Atlético-MG", 
"Chapecoense", "Paraná", "Palmeiras", "Santos", "América-MG", 
"Cruzeiro", "Corinthians", "São Paulo", "Vasco", "Internacional", 
"Vitória", "Atlético-PR", "Botafogo")), row.names = c(NA, -20L
), class = c("tbl_df", "tbl", "data.frame"))

Answer 1

Here is my suggestion

There was a problem with the name of Atlético-PR (had an n in the middle).

> test <- structure(list(Mandante = c("Cruzeiro", "Vitória", "Santos",
+ "América-MG", "Internacional", "Corinthians", "Vasco", "Atlético-PR","São Paulo", "Botafogo", "Bahia", "Flamengo", "Fluminense", "Paraná", 
+ "Ceará", "Chapecoense", "Palmeiras", "Atlético-MG", "Grêmio", 
+ "Sport"), `Gols feitos` = c(0, 2, 2, 3, 2, 2, 2, 5, 1, 1, 1, 
+ 2, 1, 0, 0, 1, 1, 2, 0, 1), `Gols sofridos` = c(1, 2, 0, 0, 0, 
+ 1, 1, 1, 0, 1, 0, 0, 0, 4, 0, 1, 0, 1, 0, 1), Visitante = c("Grêmio", 
+ "Flamengo", "Ceará", "Sport", "Bahia", "Fluminense", "Atlético-MG", 
+ "Chapecoense", "Paraná", "Palmeiras", "Santos", "América-MG", 
+ "Cruzeiro", "Corinthians", "São Paulo", "Vasco", "Internacional", 
+ "Vitória", "Atlético-PR", "Botafogo")), row.names = c(NA, -20L
+ ), class = c("tbl_df", "tbl", "data.frame"))
> 

I made a dataframe

> test <- data.frame(test)
> head(test)
       Mandante Gols.feitos Gols.sofridos  Visitante
1      Cruzeiro           0             1     Grêmio
2       Vitória           2             2   Flamengo
3        Santos           2             0      Ceará
4    América-MG           3             0      Sport
5 Internacional           2             0      Bahia
6   Corinthians           2             1 Fluminense

I made 2 date frame with visitors and patrons, both with the same order of names:

> a <- test[,-4]
> a <- a[with(a, order(Mandante)), ]
> 
> b <- test[,-1]
> b <- b[with(b, order(Visitante)), ]

Confirm that names are in the same order:

> all.equal(b$Visitante, a$Mandante)
[1] TRUE

And in the end is to add up the goals scored and suffered:

> head(a)
      Mandante Gols.feitos Gols.sofridos
4   América-MG           3             0
18 Atlético-MG           2             1
8  Atlético-PR           5             1
11       Bahia           1             0
10    Botafogo           1             1
15       Ceará           0             0
> head(b)
   Gols.feitos Gols.sofridos   Visitante
12           2             0  América-MG
7            2             1 Atlético-MG
19           0             0 Atlético-PR
5            2             0       Bahia
20           1             1    Botafogo
3            2             0       Ceará
> a[,2] <- a[,2]+b[,2]
> head(a)
      Mandante Gols.feitos Gols.sofridos
4   América-MG           3             0
18 Atlético-MG           3             1
8  Atlético-PR           5             1
11       Bahia           1             0
10    Botafogo           2             1
15       Ceará           0             0
> a[,3]<- a[,3]+b[,1]
> head(a)
      Mandante Gols.feitos Gols.sofridos
4   América-MG           3             2
18 Atlético-MG           3             3
8  Atlético-PR           5             1
11       Bahia           1             2
10    Botafogo           2             2
15       Ceará           0             2

And now I’ve done the final table:

> final <- a
> colnames(final) <- c("Time", "Gols Feitos", "Gols Sofridos")
> head(final)
          Time Gols Feitos Gols Sofridos
4   América-MG           3             2
18 Atlético-MG           3             3
8  Atlético-PR           5             1
11       Bahia           1             2
10    Botafogo           2             2
15       Ceará           0             2

I hope it helps.