Combining Data Practice II

Week 7 · Tuesday, Nov 10 · Lecture

Dr. Marcela Alfaro Córdoba

Today’s plan

Time	What
1:30	DS 7 check: swap laptops
1:45	Both waves, by group
2:05	Reshape: a table for people, long data for ggplot
2:25	“It runs. Is it right?”: an average of averages
2:45	Your project: groups, joins, shapes

Learning goals: rebuild last week’s combined survey with dplyr, summarize it by group, reshape the summary into a table people can read, and catch a summary that weights groups wrongly.

You need survey_wave2.csv and sites.csv in data/, from last week.

DS 7 check: swap laptops

⏱ 15 min

Swap laptops with your neighbor and open their week07/ds07_notes.R.

Explain what you did. Each of you walks the other through one exercise, on the screen, line by line
Discuss the differences. Where did your code differ? Do both versions do the same thing?
Agree on one topic: the most difficult one this week, or one that you didn’t understand

On the Ed thread “DS 7 check”, one post per pair: your topic, and one sentence on why it was hard.

The 1-point discussion check. I read every post, and they decide what we review.

Both waves, by group

Today’s script

Create week07/combining-practice-ii.R. Last Tuesday’s combine, in four lines:

# Week 7 · Combining data practice II
source("scripts/clean-survey.R")
library(dplyr)
library(tidyr)

wave2 <- read.csv("data/survey_wave2.csv") |> rename(commute_min = commute)
both  <- bind_rows(survey, wave2, .id = "wave")
sites <- read.csv("data/sites.csv")
both_sites <- left_join(both, sites, by = "site", relationship = "many-to-one")

if (nrow(both_sites) != 65) stop("expected 65 rows, found ", nrow(both_sites))

Last week this took a slide of checks. The checks stay; the code got shorter.

One row per wave and site

by_ws <- both |>
  group_by(wave, site) |>
  summarize(n = n(), mean_visits = mean(visits), .groups = "drop")
by_ws
#>   wave  site      n mean_visits
#> 1 1     east     11        2.55
#> 2 1     north    15        3.4
#> 3 1     south    18        3.94
#> 4 1     <NA>      1        5
#> 5 2     east      7        3
#> 6 2     north     7        2.71
#> 7 2     south     6        3.83

7 rows: 3 sites × 2 waves, plus the person with no site, who’s only in wave 1
.groups = "drop": we’re done with groups, so say so
The n column is not decoration. A mean of 1 person (row 4) or 6 people (row 7) deserves less trust than one of 18

Your turn: your own grouped summary

⏱ 8 min

Using both_sites:

For each building: number of people, median commute_min, and the share who visit 5 or more times a week (hint: mean(visits >= 5))
Same, but by wave and building. How many rows do you expect?
Which group would you not report a percentage for, and why?

both_sites |>
  group_by(building) |>
  summarize(n = n(),
            median_commute = median(commute_min, na.rm = TRUE),
            share_5plus = mean(visits >= 5),
            .groups = "drop")
#>   building       n median_commute share_5plus
#> 1 Cedar Hall    24           18         0.5
#> 2 Oak Hall      18           15.5       0.222
#> 3 Pine Hall     22           23         0.227
#> 4 <NA>           1           11         1

mean() of TRUE/FALSE is the proportion of TRUEs, one of R’s most useful tricks. Part 2: 7 rows, for the same reason as by_ws. Part 3: “100% visit 5+ times” is one person.

Reshape

`by_ws` is long. People read wide

To compare the waves side by side, you want one row per site and a column per wave:

by_ws |>
  select(site, wave, mean_visits) |>
  pivot_wider(names_from = wave, values_from = mean_visits, names_prefix = "wave_")
#>   site  wave_1 wave_2
#> 1 east    2.55   3
#> 2 north   3.4    2.71
#> 3 south   3.94   3.83
#> 4 <NA>    5     NA

4 rows, 3 columns. Predict it, then check. The NA is real: nobody without a site in wave 2.

Why the `select()`?

Skip it, and n comes along:

by_ws |>
  pivot_wider(names_from = wave, values_from = mean_visits, names_prefix = "wave_")
#>   site      n wave_1 wave_2
#> 1 east     11   2.55  NA
#> 2 north    15   3.4   NA
#> 3 south    18   3.94  NA
#> 4 <NA>      1   5     NA
#> 5 east      7  NA      3
#> 6 north     7  NA      2.71
#> 7 south     6  NA      3.83

7 rows, half NA. No error.

Every row of a wide result is one combination of the columns you didn’t pivot. n differs between waves, so site + n never repeats. Fix: drop n, or pivot it too with values_from = c(n, mean_visits).

Long for ggplot

Three variables, one plot? Stack them first:

long <- both |>
  pivot_longer(cols = c(age, commute_min, visits),
               names_to = "metric", values_to = "value")
dim(long)    #> [1] 195   5
head(long, 3)
#>   wave     id site  metric      value
#> 1 1         1 east  age            26
#> 2 1         1 east  commute_min    23
#> 3 1         1 east  visits          1

library(ggplot2)
ggplot(long, aes(x = value, fill = wave)) +
  geom_histogram(bins = 15, position = "identity", alpha = 0.6) +
  facet_wrap(~ metric, scales = "free")

65 people × 3 metrics = 195 rows. scales = "free": age, minutes and visits don’t share an axis. ggplot will warn about removed rows: the 4 NAs, now in one column.

Your turn: one table, three metrics

⏱ 8 min

From long:

For each metric: how many values, how many are NA, and the median
The median of each metric in each wave, as a table with one row per metric and a column per wave
Did the second team find the same people? Write one sentence

long |>
  group_by(metric) |>
  summarize(n = n(), n_missing = sum(is.na(value)), median = median(value, na.rm = TRUE))
#> 1 age            65         3   29
#> 2 commute_min    65         1   17.5
#> 3 visits         65         0    4

long |>
  group_by(wave, metric) |>
  summarize(median = median(value, na.rm = TRUE), .groups = "drop") |>
  pivot_wider(names_from = wave, values_from = median, names_prefix = "wave_")
#> 1 age           28     30.5
#> 2 commute_min   17.5   17.5
#> 3 visits         4      3

It runs. Is it right?

An AI-written summary

A student asked an AI: “What’s the average number of library visits per week in my survey, by site and overall?”

site_means <- both |>
  group_by(site) |>
  summarize(mean_visits = mean(visits))
site_means
#>   site  mean_visits
#> 1 east         2.72
#> 2 north        3.18
#> 3 south        3.92
#> 4 <NA>         5

mean(site_means$mean_visits)    #> [1] 3.705177

Result. Visits differ by site, from 2.7 (east) to 3.9 (south). Overall, respondents visit the library 3.7 times a week.

⏱ 10 min

In pairs. It runs with no errors or warnings. Find what’s wrong, how you found it, and what you’d do instead. No other AI.

What we found

mean(both$visits)                              #> [1] 3.353846     all 65 people
both |> count(site)
#>    site  n
#> 1  east 18
#> 2 north 22
#> 3 south 24
#> 4  <NA>  1

Problem	How you’d find it
“Overall” is an average of 4 averages: each site counts the same, whatever its size	Compare with `mean(both$visits)`: 3.35, not 3.7
One person with no site is a quarter of the “overall” answer	`count(site)`, or `n = n()` in the summary
No `n` column, so nothing on the screen looks off	Every grouped summary gets `n = n()`
“Respondents visit 3.7 times”: that’s not any respondent’s average	Ask: average over what? People, or sites?

The lesson of the average of averages

The mean of group means is the overall mean only if every group is the same size.

site_means <- both |> group_by(site) |> summarize(n = n(), mean_visits = mean(visits))
weighted.mean(site_means$mean_visits, site_means$n)    #> [1] 3.353846

Sometimes the unweighted one is the question (“the typical site”). Then say so in words
Decide what to do with the NA site on purpose, and report it
AI writes the summary in one line. You check what it’s averaging over

On Ed, thread “AI audit: averages”: one post per pair, the problem you found first, and how.

Your project

Groups, joins, shapes