Lesson 14 of 17 · Week 07 · Thu Nov 5

dplyr II: groups and joins

Questions

How do I compute a summary for every group at once?
How do I combine information from two tables?

Objectives

Use group_by() with summarize(), n(), mutate() and slice_sample().
Group by more than one column.
Combine tables with left_join() and inner_join(), and find gaps with anti_join().

Split, apply, combine

Many questions are about groups: the mean life expectancy per continent, the richest country in each year. dplyr answers them in two steps: group_by() splits the rows into groups, and the next verb works within each group. Then the results are combined into one table.

library(dplyr)
gm |>
  group_by(continent) |>
  summarize(mean_lifeExp = mean(lifeExp),
            n_rows = n())

summarize() (or summarise()) collapses each group to one row. n() counts rows in the group; n_distinct(country) counts different countries. Group by several columns for one row per combination: group_by(continent, year). count(continent) is a shortcut for grouping and counting.

Adapted from Software Carpentry's “Data Frame Manipulation with dplyr” episode (CC BY 4.0, The Carpentries). Text, examples and exercises have been rewritten for this course.

Groups with the other verbs

gm |>
  group_by(year) |>
  mutate(rank_in_year = min_rank(desc(lifeExp))) |>   # ranked within each year
  filter(rank_in_year <= 3) |>
  arrange(year, rank_in_year)

gm |>
  filter(year == 2007) |>
  group_by(continent) |>
  slice_sample(n = 2)          # 2 random rows from each continent

After a grouped summarize(), dplyr drops the last grouping level. Use ungroup() when you're done with groups, to avoid surprises later in the pipe.

Joining tables

Information often lives in separate tables that share a key column. A join matches rows by that key:

capitals <- data.frame(country = c("Chile", "Kenya", "Japan", "Peru"),
                       capital = c("Santiago", "Nairobi", "Tokyo", "Lima"))
gm07 <- filter(gm, year == 2007)

left_join(gm07, capitals, by = "country")    # all gm07 rows; capital is NA where there's no match
inner_join(gm07, capitals, by = "country")   # only rows that match in both
anti_join(gm07, capitals, by = "country")    # gm07 rows with NO match - great for spotting gaps

After a join, always check the number of rows. If it grew, some keys appear more than once in the second table.

Exercises

Each exercise gives you up to 10 checked attempts. Use Run as many times as you like to experiment first, then click Check answer when you're ready. Your exercises use numbers/subsets unique to you, so answers won't match a classmate's exactly.

Exercise 1 — Longest and shortest lives

0 / 10 attempts used

Using only the years from from_year on (click Run), compute each country's average life expectancy in by_country, a data frame with columns country and mean_lifeExp. Then store the names of the countries with the longest and shortest average in longest and shortest (as text).