How to use tidyverse functions to sum up variables of one column with numeric values of others

Question

A very beginner question... I have this fictitious, practice dataset of a football team's league performance over 20 seasons, set up as follows:

Brighton <- tribble(
  ~"Year", ~"League", ~"Win", ~"Draw", ~"Lose", ~"Points", ~"Position",
  # -------------------------------------------------------------------
  2021, "Premier", 10, 0, 28, 30, 19,
  2022, "Championship", 27, 0, 19, 81, 3,
  2023, "Championship", 20, 5, 21, 65, 7,
  2024, "Championship", 15, 9, 22, 54, 12,
  2025, "Championship", 20, 8, 18, 68, 6,
  2026, "Premier", 16, 5, 17, 53, 11,
  2027, "Premier", 10, 5, 23, 35, 18,
  2028, "Championship", 25, 5, 16, 80, 2,
  2029, "Premier", 18, 5, 15, 59, 7,
  2030, "Premier", 21, 3, 14, 66, 5,
  2031, "Premier", 16, 8, 14, 56, 10,
  2032, "Premier", 15, 7, 14, 52, 12,
  2033, "Premier", 15, 1, 22, 46, 15,
  2034, "Premier", 11, 7, 20, 40, 17,
  2035, "Premier", 18, 2, 18, 56, 10,
  2036, "Premier", 11, 3, 24, 36, 18,
  2037, "Championship", 23, 7, 16, 76, 3,
  2038, "Championship", 21, 8, 17, 71, 4,
  2039, "Championship", 12, 10, 24, 46, 22,
  2040, "League One", 18, 6, 22, 60, 8,
  2041, "League One", 15, 8, 23, 53, 10
)

Brighton$Year <- as.integer(Brighton$Year)

What I would like to do is sum up total games played in each "League" variable (Premier, Championship, League One), all those won, lost and drawn.

I have been able to do this in a plot as follows:

Br_long <- Brighton %>%
  pivot_longer(c("Win", "Draw", "Lose"), values_to = "Games")

ggplot(Br_long) +
         geom_col(mapping = aes(x = League, 
                                y = Games), 
                  position = "stack"
)

But I have not been able to actually calculate the total games mathematically (so as to include the output in rmd inline code).

My question: What tidyverse (tidyr, dplyr) code do I use to add up all Premier games, Championship games and League One games played in the dataset (whether won, lost or drawn), so that I end up with three totals?

I wish I could show you what I've tried to do but have tried so many different things, watched so many tutorials and read so many online docs I've lost my way.

I would appreciate any assistance in my solution or in sending me on towards where and how to learn to do this well. Thank you!

Hi and welcome to SO. Part of your difficulty is because your data is not tidy. The tidyverse, unsurprisingly, is designed to work with tidy data. In this case, tidy data might have columns for, say, Year, League, Result (Win, Draw, Lost), and N in one tibble and another tibble with Year, League and Position. If you do this, getting the summaries you want will be straightforward. An important principle is to separate data storage from result presentation. Many questions on SO are caused by forgetting this. — Limey
– Limey, Commented Feb 11, 2021 at 14:18
Thanks @Limey this is very helpful, will set about applying this. — GaryZA73
– GaryZA73, Commented Feb 11, 2021 at 15:48

Edo · Accepted Answer · 2021-02-11 14:39:54Z

2

If you need to get the sum of all games, so to end up with 3 totals, this is the answer:

Brighton %>% 
 group_by(League) %>% 
 summarise(Games = sum(Win, Draw, Lose))

#> # A tibble: 3 x 2
#>   League       Games
#>   <chr>        <dbl>
#> 1 Championship   368
#> 2 League One      92
#> 3 Premier        416

If you need separated sums for wins, loses and draws, then the answer you're looking for is @Jon's.

answered Feb 11, 2021 at 14:39

Edo

7,8982 gold badges13 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GaryZA73 Over a year ago

Thank you! Looks so simple. @Jon's answer also gives me what I'm looking for. Much appreciation to you all.

Jon Spring · Accepted Answer · 2021-02-11 14:36:39Z

2

dplyr's across, addedin dplyr 1.0.0, is useful for this:

Brighton %>%
  group_by(League) %>%
  summarize(across(Win:Lose, sum))

 A tibble: 3 x 4
  League         Win  Draw  Lose
  <chr>        <dbl> <dbl> <dbl>
1 Championship   163    52   153
2 League One      33    14    45
3 Premier        161    46   209

answered Feb 11, 2021 at 14:36

Jon Spring

70.2k4 gold badges42 silver badges70 bronze badges

1 Comment

GaryZA73 Over a year ago

Thank you! Appreciate the help.

Collectives™ on Stack Overflow

How to use tidyverse functions to sum up variables of one column with numeric values of others

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related