How to create a dataframe using values computed from another dataframe in R?

Question

I started using R this week, so excuse me if this is a simple question. I want to create a dataframe from another dataframe that I created before. The first dataframe is:

HomeTeam   AwayTeam  Hgoals Agoals  

Parma       Juventus    0    1   
Fiorentina  Napoli      3    4   
Udinese     Milan       1    0   
Cagliari    Brescia     0    1   
Roma        Genoa       3    3   
Sampdoria   Lazio       0    3   
Spal        Atalanta    2    3 
....

Now I want to create a dataframe that shows me the average of a team's home and away goals for the whole season. Something like this:

Team        Hgoals(Avg)  Agoals(Avg) 

Parma          2.5          1.4   
Fiorentina     1.3          2.1   
Udinese        1.8          1.4

This question has been asked and answered multiple times. Please search for answers. — slava-kohut, Jul 16 '20 at 14:40
Just to be clear: You want Hgoals(Avg) for Parma to be all goals by Parma as hometeam divided by the number of matches and Agoals(Avg) for Parma the goals as away team? — Martin Gal, Jul 16 '20 at 14:43
@MartinGal, is the first part of my answer what you believe is wanted? I edited in the second interpretation with better labelling to add in the same results as the accepted answer. — Daniel O, Jul 16 '20 at 15:00
@slava-kohut I think this question is not as easy as you think. Please see Daniel's answer. I think he understand the problem correctly. — Darren Tsai, Jul 16 '20 at 15:06
@DarrenTsai maybe, but the OP should have stated their question more precisely — slava-kohut, Jul 16 '20 at 15:08

Daniel O · Accepted Answer · 2020-07-16T15:04:16.160

we could use:

merge(
  with(df,aggregate(list(Hgoals.Avg = Hgoals),list(Team = HomeTeam), mean)),
  with(df,aggregate(list(Agoals.Avg = Agoals),list(Team = AwayTeam), mean)),
  by="Team",all=T
)

         Team Hgoals.Avg Agoals.Avg
1    Cagliari          0         NA
2  Fiorentina          3         NA
3       Parma          0         NA
4        Roma          3         NA
5   Sampdoria          0         NA
6        Spal          2         NA
7     Udinese          1         NA
8    Atalanta         NA          3
9     Brescia         NA          1
10      Genoa         NA          3
11   Juventus         NA          1
12      Lazio         NA          3
13      Milan         NA          0
14     Napoli         NA          4

If this alternative interpretation is wanted.

with(df,aggregate(list(goals.for.Avg = Hgoals, goals.agaist.Avg = Agoals),list(Team = HomeTeam), mean))

        Team goals.for.Avg  goals.agaist.Avg
1   Cagliari             0                 1
2 Fiorentina             3                 4
3      Parma             0                 1
4       Roma             3                 3
5  Sampdoria             0                 3
6       Spal             2                 3
7    Udinese             1                 0

Thank you very much. I will indeed need the second interpretation at one point. — Nckh, Jul 16 '20 at 18:30

score -4 · Answer 2 · answered Jul 16 '20 at 14:39

Try this:

#Data
df <- structure(list(HomeTeam = structure(c(3L, 2L, 7L, 1L, 4L, 5L, 
6L), .Label = c("Cagliari", "Fiorentina", "Parma", "Roma", "Sampdoria", 
"Spal", "Udinese"), class = "factor"), AwayTeam = structure(c(4L, 
7L, 6L, 2L, 3L, 5L, 1L), .Label = c("Atalanta", "Brescia", "Genoa", 
"Juventus", "Lazio", "Milan", "Napoli"), class = "factor"), Hgoals = c(0L, 
3L, 1L, 0L, 3L, 0L, 2L), Agoals = c(1L, 4L, 0L, 1L, 3L, 3L, 3L
)), class = "data.frame", row.names = c(NA, -7L))

df %>% select(-AwayTeam) %>%group_by(HomeTeam) %>% summarise_all(mean,na.rm=T)

# A tibble: 7 x 3
  HomeTeam   Hgoals Agoals
  <fct>       <dbl>  <dbl>
1 Cagliari        0      1
2 Fiorentina      3      4
3 Parma           0      1
4 Roma            3      3
5 Sampdoria       0      3
6 Spal            2      3
7 Udinese         1      0

How to create a dataframe using values computed from another dataframe in R?

2 Answers2