dplyr II: Groups and Joins

Week 7 · Thursday, Nov 5 · Lecture

Dr. Marcela Alfaro Córdoba

Today’s plan

Time	What
1:30	DS 6: your questions
1:45	Mid-quarter check-ins: the schedule is on Ed
1:50	Groups: `group_by()` and `summarize()`
2:25	Joins: `left_join()`, `inner_join()`, `anti_join()`
2:55	Tuesday: DS 7, check-ins, combining data practice II

Learning goals: answer “for each group” questions in one pipe, know when a data frame is still grouped and why that matters, and attach a lookup table with a join, counting rows (and checking keys) every time.

Still no AI. You need data/sites.csv and data/buildings.csv from last week.

DS 6: your questions

⏱ 15 min

Not a check, and not graded. It’s open time for questions about DS 6 (Lesson 12: Writing Data, Lesson 13: dplyr I), or about anything from Tuesday’s pipes.

Ask out loud, or post on the Ed thread “DS 6 questions” if you’d rather not
“I got it right but I don’t know why” is a good question
Your pair’s “DS 6 check” post from Tuesday counts: if your topic is still unclear, say so

If nobody starts: when do you need <- at the start of a pipe? Why == and not = inside filter()? When does mutate() make a new column, and when does it overwrite one? Why did filter() drop the person with an NA commute?

Mid-quarter check-ins: a correction

Important

The check-in sign up is posted on Ed Discussion. Find a time and add your name today.

Check-ins are mandatory.

20 minutes, today or during office hours, at the time shown on Ed
Bring: your laptop, your rendered mid-quarter report, and your repository open
No AI, just you, your code and your choices
Expect: “Walk me through this pipeline.” “Why this model?” “What happens to this plot if that observation is removed?”

5% of your grade. If you can’t make your time, send me a private post on Ed before your slot, not after it.

Groups

Today’s script

Create week07/groups-joins.R:

# Week 7 · dplyr II: groups and joins
source("scripts/load-gapminder.R")
source("scripts/check-gapminder.R")
source("scripts/clean-survey.R")
library(dplyr)

You’ve already answered “for each group” questions twice:

sapply(split(survey, survey$site), function(d) mean(d$commute_min, na.rm = TRUE))  # Thursday
both |> filter(wave == "1") |> pull(visits) |> mean()                              # Tuesday
both |> filter(wave == "2") |> pull(visits) |> mean()                              # ...and again

One line per group doesn’t scale. With 142 countries, you’d need 142 lines.

`group_by()` + `summarize()`

survey |>
  group_by(site) |>
  summarize(mean_commute = mean(commute_min), n = n())
#> # A tibble: 4 × 3
#>   site  mean_commute     n
#>   <chr>        <dbl> <int>
#> 1 east          21.2    11
#> 2 north         NA      15
#> 3 south         19.2    18
#> 4 <NA>          11       1

group_by(site) splits the rows into groups. Nothing looks different yet
summarize() collapses each group to one row: one column per summary you ask for
n() counts the rows in each group

Two things to notice before you trust this table. Look again.

Two things to notice

survey |>
  group_by(site) |>
  summarize(mean_commute = mean(commute_min, na.rm = TRUE), n = n())
#> 1 east          21.2    11
#> 2 north         20.8    15
#> 3 south         19.2    18
#> 4 <NA>          11       1

north was NA: one person in north has no commute. mean() still needs na.rm = TRUE, and n() still counts that person (15 rows, 14 commutes)
<NA> is a group. split() dropped the person with no site. group_by() keeps them. 11 + 15 + 18 + 1 = 45

Tip

count(site) is a shortcut for group_by(site) |> summarize(n = n()). It’s the fastest way to see your groups, NA included.

Your turn: one row per continent

⏱ 8 min

For 2007 only, one pipe that gives one row per continent with:

n_countries: how many countries
median_le: the median life expectancy
max_gdp: the largest GDP per capita

Before you run it: how many rows and columns?

gapminder |>
  filter(year == 2007) |>
  group_by(continent) |>
  summarize(n_countries = n(), median_le = median(lifeExp), max_gdp = max(gdpPercap))
#>   continent n_countries median_le max_gdp
#> 1 Africa             52      52.9  13206.
#> 2 Americas           25      72.9  42952.
#> 3 Asia               33      72.4  47307.
#> 4 Europe             30      78.6  49357.
#> 5 Oceania             2      80.7  34435.

5 rows, 4 columns. 52 + 25 + 33 + 30 + 2 = 142, the number of countries. Oceania’s “median” is the average of 2 countries: always look at n next to a summary.

Grouping by two columns

le_cy <- gapminder |>
  group_by(continent, year) |>
  summarize(mean_le = mean(lifeExp))
#> `summarise()` has regrouped the output.
#> ℹ Summaries were computed grouped by continent and year.
#> ℹ Output is grouped by continent.

nrow(le_cy)          #> [1] 60
group_vars(le_cy)    #> [1] "continent"

One row per combination: 5 continents × 12 years = 60
The message is not an error. It says the result is still grouped, by continent

The exact wording depends on your dplyr version. Either way it means “still grouped”.

Important

Done with groups? Say so: add ungroup() at the end of the pipe, or summarize(..., .groups = "drop").

Why “still grouped” matters

Rank the continent-years by mean life expectancy, then look at 2007:

le_cy |>
  mutate(rank = min_rank(desc(mean_le))) |>
  filter(year == 2007)
#>   continent  year mean_le  rank
#> 1 Africa     2007    54.8     1
#> 2 Americas   2007    73.6     1
#> 3 Asia       2007    70.7     1
...

Every continent is #1. The rank was computed within each continent, across years.

le_cy |>
  ungroup() |>
  mutate(rank = min_rank(desc(mean_le))) |>
  filter(year == 2007)
#> 1 Africa     2007    54.8    44
#> 2 Americas   2007    73.6    12
#> 3 Asia       2007    70.7    22
#> 4 Europe     2007    77.6     4
#> 5 Oceania    2007    80.7     1

No error, no warning, a plausible-looking column. That’s how a leftover group bites you.

Groups with the other verbs

group_by() changes what every verb after it does:

gapminder |>
  filter(year == 2007) |>
  group_by(continent) |>
  slice_max(lifeExp, n = 1)                      # the top row within each continent
#> 1 Africa    Reunion      76.4
#> 2 Americas  Canada       80.7
#> 3 Asia      Japan        82.6
#> 4 Europe    Iceland      81.8
#> 5 Oceania   Australia    81.2

gapminder |>
  filter(year == 2007) |>
  group_by(continent) |>
  mutate(le_vs_cont = lifeExp - mean(lifeExp)) |>   # mean() of the group, not of everything
  ungroup()
#>   country     continent lifeExp le_vs_cont
#> 1 Afghanistan Asia         43.8     -26.9
#> 2 Albania     Europe       76.4      -1.23

mutate() with groups keeps every row (142); summarize() collapses to one per group (5). Ask which one you want before you write it.

Your turn: share of the continent

⏱ 7 min

For 2007:

Add a column pop_share: each country’s population as a share of its continent’s population
Keep the country with the largest share in each continent
Check: within one continent, do the shares add up to 1? (Write the check before you look)

gapminder |>
  filter(year == 2007) |>
  group_by(continent) |>
  mutate(pop_share = pop / sum(pop)) |>
  slice_max(pop_share, n = 1) |>
  ungroup() |>
  select(continent, country, pop_share)
#> 1 Africa    Nigeria         0.145
#> 2 Americas  United States   0.335
#> 3 Asia      China           0.346
#> 4 Europe    Germany         0.141
#> 5 Oceania   Australia       0.832

Forget the group_by() and sum(pop) is the world: China becomes 0.21 and the shares stop meaning “of its continent”.

Joins

Tuesday’s merge, the dplyr way

sites <- read.csv("data/sites.csv")

survey_sites <- left_join(survey, sites, by = "site")
nrow(survey_sites)       #> [1] 45
head(survey_sites, 3)
#>   id  site age commute_min visits  building km_to_library
#> 1  1  east  26          23      1  Oak Hall           1.2
#> 2  2 north  38          11      6 Pine Hall           0.6
#> 3  3  east  39          17      0  Oak Hall           1.2

left_join(x, y) = merge(x, y, all.x = TRUE): every row of x, NA where y has no match
Rows stay in survey’s order (merge() re-sorted them by site), and it fits in a pipe

Three joins, three row counts

	Keeps	`survey` + `sites`
`left_join(x, y)`	every row of `x`	45
`inner_join(x, y)`	rows of `x` with a match in `y`	44
`anti_join(x, y)`	rows of `x` with no match in `y`	1

anti_join() doesn’t add anything. It’s for finding gaps, in both directions:

anti_join(survey, sites, by = "site")     # survey people with no site info
#>   id site age commute_min visits
#> 1 27 <NA>  33          11      5
anti_join(sites, survey, by = "site")     # sites nobody in the survey lives in
#>   site building km_to_library
#> 1 west Elm Hall           1.8

Order matters: anti_join(x, y) answers “what in x is missing from y?”

The duplicated key, again

Tuesday’s buildings.csv has two rows for north:

buildings <- read.csv("data/buildings.csv")
nrow(left_join(survey, buildings, by = "site"))
#> [1] 60
#> Warning message:
#> Detected an unexpected many-to-many relationship between `x` and `y`.
#> ℹ Row 2 of `x` matches multiple rows in `y`.

merge() gave you 86 rows on Tuesday and said nothing. dplyr warns. Better: tell it what you expect, and it stops:

left_join(survey, buildings, by = "site", relationship = "many-to-one")
#> Error in `left_join()`:
#> ! Each row in `x` must match at most 1 row in `y`.

Tip

For “add information from a lookup table”: relationship = "many-to-one". Many survey rows per site, one row per site in the lookup.

Your turn: predict, then join

⏱ 10 min

Read wave 2 again and fix its column name: wave2 <- read.csv("data/survey_wave2.csv") |> rename(commute_min = commute).

Write down the number you expect, then run:

left_join(wave2, sites, by = "site"): how many rows?
inner_join() of the same: how many rows? Why the same as 1, when it wasn’t for survey?
Both anti_join()s: what’s in each?
Join wave2 with buildings, using relationship = "many-to-one". What happens, and what would you ask whoever made buildings.csv?

nrow(left_join(wave2, sites, by = "site"))     #> [1] 20
nrow(inner_join(wave2, sites, by = "site"))    #> [1] 20    no one in wave 2 is missing a site
anti_join(wave2, sites, by = "site")           #> 0 rows
anti_join(sites, wave2, by = "site")           #> west, again

4 stops with an error, and that’s the right outcome. “Which building is north?” is a question about the data, not the code.

Key points

group_by() + summarize(): one row per group. n() counts rows; check it next to every summary
group_by() keeps NA as a group (split() dropped it). count() shows your groups fast
mean(), median() and friends still need na.rm = TRUE inside summarize()
After summarize() with two grouping columns, the result is still grouped: ungroup() or .groups = "drop"
Grouped mutate() keeps every row; grouped summarize() collapses. A leftover group changes results silently
left_join() keeps every row of x, inner_join() only matches, anti_join() finds the gaps
relationship = "many-to-one" turns a duplicated lookup key from a silent problem into an error

Tuesday, Nov 10

Tuesday is a full day

9:50-10:55 am · DS 7 + check-ins

Lesson 14: dplyr II and Lesson 15: tidyr, alone, no AI. Two PDFs.

1:30-3:05 pm · Combining data practice II

Starts with the DS 7 check: swap laptops. Then both survey waves, by group and reshaped, and an average that looks right and isn’t.

Important

Check-ins are mandatory. Look up your time on the Ed schedule today. If it isn’t today, it’s in office hours. The DS is not check-in time.

Before you go

week07/groups-joins.R restarts and sources cleanly
Your check-in time is in your calendar, from the schedule on Ed Discussion
Your rendered mid-quarter report and repository are ready to open in under a minute
Project: one group_by() summary of your data, with n next to it. Any group much smaller than you expected?

Before Tuesday: read the Lesson 14 and 15 pages. Lesson 15 introduces pivot_longer() and pivot_wider(), which change the shape of a data frame. The DS is your first practice with them.

Next: Tuesday, Nov 10 · DS 7 in the morning, combining data practice II in the afternoon.

dplyr II: Groups and Joins

Today’s plan

DS 6: your questions

Mid-quarter check-ins: a correction

Groups

Today’s script

group_by() + summarize()

Two things to notice

Your turn: one row per continent

Grouping by two columns

Why “still grouped” matters

Groups with the other verbs

Your turn: share of the continent

Joins

Tuesday’s merge, the dplyr way

Three joins, three row counts

The duplicated key, again

Your turn: predict, then join

Key points

Tuesday, Nov 10

Tuesday is a full day

Before you go

`group_by()` + `summarize()`