Grouping and summarising

Session 2 practical exercises

The previous exercise ended with an open question: mutate() alone cannot split the data before computing. You had to run two separate pipelines to get the mean age for 1999 and 2002. The same is true for many other operations we will be interested in conducting in our daily work as epidemiologists.

Think about epi tables and summaries. Think about the reporting of surveillance or outbreak data, epidemiological trends and population characteristics. We are usually interested in differences across time (years, epi weeks), groups of people (sex, age groups) and places (regions, provinces, towns). If we lacked a way of quickly computing all those, R would not have become so widespread and famous.

The answer to that limitation is group_by(), and the concept of grouped operations

Part 4 · group_by(), the invisible wall

group_by() does not transform your data visibly. Run it just in the console and look at the result:

imd %>%
  group_by(year)

The dataframe looks identical to imd. Same rows, same columns, same values. But something has changed — look at the header in the console output. You will see a line that reads # Groups: year [4]. R has placed an invisible wall between the years, splitting the dataframe into four mini-dataframes that happen to be stacked on top of each other.

From this point on, any verb that follows in the pipeline will operate inside each group separately, not across the whole dataset.

You can group by as many variables as needed, as long as they make sense. You just need to write every grouping variable name a comma-separated arguments. Grouping will happen in the same order to specify the variables.

Importantgroup_by() is a false friend

The name suggests it brings things together — but it actually does the opposite. It separates. Think of it as drawing walls inside your data: everything that comes after works within those walls, not across them. Always remember to remove the grouping with ungroup() when you are done, or it will silently affect every subsequent operation.

Grouped mutate()

Now that you understand the walls, let’s put them to use. A grouped mutate() computes within each group and stamps the result onto every row — but the result is now group-specific, not global.

WarningThings start getting interesting

You will notice that instructions are getting complex step by step. Now you will start needing more than one function or operation for some of the tasks ahead. Let’s remember the Grammar Model we are using to learn. Take the first task below, and before writing or executing, think for a minute about how the sentence translates into tidy functions

Action — Calculate the mean age by year using a grouped mutate(). Assign the result to imd_grouped. Don’t forget to ungroup() the data afterwards.

Action — Open imd_grouped and look at mean_age_year. Youy will need to scroll down the dataframe in the viewer (in case you didn’t know: you can do it). How does it differ from the global mean you calculated in the previous exercise?

  1. You must have realized that it takes more time and effort checking the result of this grouped operation. The dataset has 2627 rows and only has 4 years, so it takes a while to navigate. We can do better.

  2. I’m teaching you a new function: table(). It’s a very basic R function that counts observations for you. Probably, you would have benefited from it in the previous exercise, but I wanted you to fight with the data yourself for at least once. It doesn’t work inside tidy pipes, so you need to call it apart.

  3. Write table(imd_grouped$mean_age_year) and execute it to see in the console the different values present on the variable and how many times they are repeated, but now we are not interested in the count, but quickly checking the different values resulting from the grouped operation

  4. How many different mean ages are there?

  5. Could you have guessed that number without running any code to check?

As you guessed, we got ourselves one mean age value per level of the grouping variable: if we have four years of data, then we asked R to calculate one mean age of cases per year, therefore four different results

Now try adding a second grouping variable. Instead of splitting only by year, split by year and sex simultaneously.

Action — Repeat the grouped mutate(), this time grouping by both year and sex. Assign the result to imd_grouped2 and inspect the new mean_age_year column. Repeat the steps from the previous action. but first answer the question below:

  1. Before running the code, how many different mean age values are expected?

  2. Now run the code and check the correct answer

The correct answer to the question is a number between 8 and 12. Try writing down any of those in the question box above, you’ll see they are still correct. But why? If your answer was just 8, you guessed correctly that 4 years with “male” and “female” options every year equals the 8 values. And you’d be correct if it didn’t happen to exist a third empty category, that doesn’t need to be present in all years - thus leading to the actual 9 results output.

This example is useful for making you think about inconsistencies between your results and what you would expect under perfect conditions - something that will save you a lot of time and error in the future!

Notice that imd_grouped2 still has the same number of rows as imd. A grouped mutate() never collapses the data — it always returns a dataframe of the same size, with the group-specific result repeated for every row in that group.

Part 5 · summarise() — the verb of destruction

A grouped mutate() keeps every row. The original data did not change, it only created new information the length of the original data. Sometimes you do not need every row: you just need one number per group, the summary or the count or other output you are looking for. That is what summarise() does: it collapses each group into a single summary row.

If we labelled mutate() the verb of creation, we can call summarise() the verb of destruction because it is producing something completely different for you, that will not resemble at all the data you were originally working with.

Using summarise is as simple as using mutate() - they behave identically using the new_var = function() logic. See for yourself:

Action — Calculate the mean age by year using group_by() + summarise(). Assign the result to imd_summary.

Action — Compare the existing rows of both imd_grouped and imd_summary. What happened to the data?

The result has one row per year — four rows total. summarise() consumed the groups and produced a clean summary table. This is the key difference from mutate():

mutate() summarise()
Output size Same as input One row per group
Use case Add a column to the full data Produce a summary table

Action — Extend imd_summary to include, alongside the mean age like you did with the mutate(), the total number of cases per year using a new function n(), that counts the number of observations (rows)

Action — Now group by both year and sex and repeat the summary in imd_summary2. How many rows does the result have, and why?

You can keep adding variables to summarise() without a limit, to produce tables with as much information as you want. You will always have columns for the grouping variables to identify the combination summaries.

What does group_by(year) do to the dataframe?

What does group_by(year) %>% mutate(mean_age = mean(age_years, na.rm = TRUE)) return?

What is the key difference between group_by() %>% summarise() and group_by() %>% mutate()?

What does n() compute inside summarise()?

Exercise summary

group_by() is the verb that gives the others their power. Alone it does nothing visible — but paired with mutate() it brings group-specific values to every row, and paired with summarise() it collapses your data into a clean summary table. The choice between the two depends on what you need: keep the full data enriched with group information, or reduce it to one row per group.

These two combinations — group_by() %>% mutate() and group_by() %>% summarise() — are the backbone of almost every descriptive analysis you will ever write in R.

These are the functions you learned:

Function Package What it does
group_by() dplyr Splits the dataframe into groups for subsequent operations
ungroup() dplyr Removes grouping so subsequent operations work on all rows
summarise() dplyr Collapses each group into a single summary row
n() dplyr Counts the number of rows in the current group
table() base R Counts occurrences of each unique value in a variable 
# Libraries
pacman::p_load(tidyverse, rio, here)

# Data
imd <- import(here("data", "clean", "IMD_Sample_Clean.xlsx"))

# Simple grouping
imd %>%
  group_by(year)

# Mean age by year
imd_grouped <- imd %>%
  group_by(year) %>%
  mutate(mean_age_year = mean(age_years, na.rm = TRUE)) %>%
  ungroup() 

table(imd_grouped$mean_age_year)

# Mean age by year and sex
imd_grouped2 <- imd %>%
  group_by(year, sex) %>%
  mutate(mean_age_year = mean(age_years, na.rm = TRUE)) %>%
  ungroup() 

table(imd_grouped2$mean_age_year)

# Summarise operation
imd_summary <- imd %>%
  group_by(year) %>%
  summarise(mean_age = mean(age_years, na.rm = TRUE))

# Addding one extra variable to the table
imd_summary <- imd %>%
  group_by(year) %>%
  summarise(
    mean_age = mean(age_years, na.rm = TRUE),
    total_cases = n()
  )

# Grouping by two variables
imd_summary2 <- imd %>%
  group_by(year, sex) %>%
  summarise(
    mean_age = mean(age_years, na.rm = TRUE),
    total_cases = n()
  )