Summarizing in R using dplyr: values in 2 different columns

Question

I have the following dataframe:

 points_team1 points_team2               team1                team2
------------------------------------------------------------------------
1           42           32       Doppler/Horst        Doherty/Allen
2           40           46   Abbiati/Andreatta          Mesa/Garcia
3           50           49      Bergmann/Harms        Basta/Kolaric
4           46           48     Mol H./Berntsen          Regza/Smits
5           29           42       Doppler/Horst        Hyden/Brunner
6           31           42       Hyden/Brunner   Liamin/Krasilnikov

Now I would like to build the sum of points each team has scored and lost. Note, that a team might be team 1 OR team 2 (e.g. Hyden/Brunner is once on each side).

I tried to use gather, but then got stuck how to use something like SUMIF.

k <- structure(list(points_team1 = c(42, 40, 50, 46, 29, 31), points_team2 = c(32, 
46, 49, 48, 42, 42), team1 = c("Doppler/Horst", "Abbiati/Andreatta", 
"Bergmann/Harms", "Mol H. / Berntsen", "Doppler/Horst", "Hyden/Brunner"
), team2 = c("Doherty/Allen", "Mesa/Garcia", "Basta/Kolaric", 
"Regza/Smits", "Hyden/Brunner", "Liamin/Krasilnikov")), row.names = c(NA, 
-6L), class = "data.frame")

v <- k %>% tidyr::gather('team1','team2', key="team_id", value="teamname") %>% 
                  dplyr::group_by(teamname) %>% 
                  dplyr::summarize(matches_played=n(), points_won=sum(points_team1[team_id == "team1"]))

The expected outcome for the given dataset would be:

teamname                points_won     points_lost
-----------------------------------------------------
1 Doppler/Horst         71             74
2 Abbiati/Andreatta     40             46
3 Mesa/Garcia           46             40
4 Hyden/Brunner         73             71
...

The results I got from researching google and stackoverflow only gave me answers to sum all the rows containing a certain element (e.g. here: Summarize with conditions in dplyr) but in my question the column to sum up may depend on 2 different columns, and I could not figure out how to do that.

Please help!

Nate Nate · Accepted Answer · 2018-12-27T19:28:44

You can build two dataframes, one for each team, with identical names, then stack them together and summarize like normal.

team1 <- k %>% select(points_won = points_team1,
                      points_lost = points_team2,
                      team = team1)

team2 <- k %>% select(points_won = points_team2,
                      points_lost = points_team1,
                      team = team2)

bind_rows(team1, team2) %>%
    group_by(team) %>%
    summarise_all(sum)

Summarizing in R using dplyr: values in 2 different columns

1 Answers