Combining Data Practice I

Week 6 · Tuesday, Nov 3 · Lecture

Dr. Marcela Alfaro Córdoba

Today’s plan

Time What
1:30 DS 6 check: swap laptops
1:45 A second wave of the survey: stack it
2:05 Questions about both waves, with pipes
2:25 “It runs. Is it right?”: a merge that multiplies rows
2:45 Write it out, and your project’s combine

Learning goals: stack two versions of a dataset that don’t quite match, answer questions about them with dplyr pipes, and catch a combine that silently adds or drops rows.

You need survey_wave2.csv, buildings.csv and Thursday’s sites.csv in data/.

DS 6 check: swap laptops

⏱ 15 min

Swap laptops with your neighbor and open their week06/ds06_notes.R.

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

On the Ed thread “DS 6 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.

A second wave

Today’s script

Create week06/combining-practice.R:

# Week 6 · Combining data practice I
source("scripts/clean-survey.R")
library(dplyr)

wave2 <- read.csv("data/survey_wave2.csv")

The story: a month after the first survey, a second team surveyed 20 more people. Same questions, typed in by a different person. You want one data frame with everyone.

Look before you stack

str(wave2)
#> 'data.frame':    20 obs. of  5 variables:
#>  $ id     : int  101 102 103 104 105 106 107 108 109 110 ...
#>  $ site   : chr  "east" "south" "east" "south" ...
#>  $ age    : int  28 30 18 33 41 22 21 27 31 35 ...
#>  $ visits : int  4 5 0 3 5 1 5 2 5 3 ...
#>  $ commute: int  8 14 35 6 21 35 8 9 36 36 ...

names(survey)   #> [1] "id"          "site"        "age"         "commute_min" "visits"
names(wave2)    #> [1] "id"      "site"    "age"     "visits"  "commute"

⏱ 2 min

Two differences between the columns. Which one matters for stacking?

The order doesn’t matter: rbind() and bind_rows() match columns by name. The name does: commute is not commute_min.

Stack it, and count

rbind(survey, wave2)
#> Error in match.names(clabs, names(xi)) : names do not match previous names
both <- bind_rows(survey, wave2)
dim(both)                 #> [1] 65  6
colSums(is.na(both))
#>          id        site         age commute_min      visits     commute
#>           0           1           3          21           0          45

65 rows is right. 6 columns is not.

bind_rows() kept both names: wave 1 has NA in commute, wave 2 has NA in commute_min. No message. colSums(is.na()) is how you’d notice: 21 NAs where Thursday you had 1.

Fix the name, then stack

wave2 <- wave2 |> rename(commute_min = commute)    # new name = old name

both <- bind_rows(survey, wave2, .id = "wave")
dim(both)                 #> [1] 65  6
colSums(is.na(both))
#>        wave          id        site         age commute_min      visits
#>           0           0           1           3           1           0
table(both$wave)
#>  1  2
#> 45 20
  • 45 + 20 = 65 rows, the 5 columns plus wave, and the same NAs as Thursday
  • .id = "wave" numbers the inputs in order: "1", "2". It’s text, not a number

Tip

One more check before trusting it: is every id still unique? any(duplicated(both$id)) is FALSE. Wave 2 starts at 101 for a reason.

Your turn: put the stack in your script

⏱ 6 min

  1. Add the rename() and the bind_rows() to your script
  2. After it, add checks that stop if anything is off: 65 rows, 6 columns, no duplicated ids
  3. How many people from each site, in each wave? Use table() with two columns
if (nrow(both) != 65) stop("expected 65 rows, found ", nrow(both))
if (ncol(both) != 6) stop("expected 6 columns: did a name not match?")
if (any(duplicated(both$id))) stop("duplicated ids across waves")

table(both$site, both$wave, useNA = "ifany")
#>          1  2
#>   east  11  7
#>   north 15  7
#>   south 18  6
#>   <NA>   1  0

Questions, with pipes

From this morning’s DS

both |>
  filter(commute_min > 30) |>
  select(id, wave, site, commute_min) |>
  arrange(desc(commute_min)) |>
  slice_head(n = 5)
#>    id wave  site commute_min
#> 1  23    1  east          42
#> 2  17    1 north          40
#> 3  18    1  east          40
#> 4  19    1 south          39
#> 5 114    2 north          37

Note

12 people commute more than 30 minutes. filter() silently drops rows where the condition is NA (id 35). Base [ ] would have given you a row of NAs instead. Different tools, different defaults: count.

Your turn: four questions

⏱ 12 min

One pipe each. Before you run it, write down roughly what you expect.

  1. How many people in north visit the library 5 or more times a week?
  2. Wave 2 only: the 3 people who visit most, with id, site and visits
  3. Add a column commute_h (hours), and keep only the people with a commute of half an hour or more. How many?
  4. Average visits in wave 1 and in wave 2 (use filter() and pull(), then mean()). Did the second team find the same thing?
both |> filter(site == "north", visits >= 5) |> nrow()          #> [1] 5
both |> filter(wave == "2") |> arrange(desc(visits)) |>
  select(id, site, visits) |> slice_head(n = 3)                 # ids 116, 117, 102
both |> mutate(commute_h = commute_min / 60) |>
  filter(commute_h >= 0.5) |> nrow()                            #> [1] 14
both |> filter(wave == "1") |> pull(visits) |> mean()           #> [1] 3.444444
both |> filter(wave == "2") |> pull(visits) |> mean()           #> [1] 3.15

wave == "2", in quotes: it’s text. wave == 2 also works here, because R converts the 2 to text to compare. Don’t rely on that.

Add the sites, keep every row

Thursday’s lookup table, now on both waves:

sites <- read.csv("data/sites.csv")
both_sites <- merge(both, sites, by = "site", all.x = TRUE)
nrow(both_sites)                  #> [1] 65
sum(is.na(both_sites$building))   #> [1] 1

65 in, 65 out, and the one missing building is the person with no site. Every number accounted for. That’s the standard for the rest of the course.

It runs. Is it right?

An AI-written merge

A student asked an AI to “add the building and its distance to the library to my survey data and tell me how often people visit.” It used buildings.csv:

buildings <- read.csv("data/buildings.csv")
survey_b <- merge(both, buildings, by = "site")

mean(survey_b$visits)                             #> [1] 3.290698
cor(survey_b$visits, survey_b$km_to_library)      #> [1] -0.02479271

Result. Respondents visit the library 3.3 times a week on average (n = 86). There is essentially no relationship between distance to the library and visits (r = −0.02).

⏱ 10 min

In pairs. The code 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

nrow(both)                     #> [1] 65
nrow(survey_b)                 #> [1] 86     <- more rows than people
sum(duplicated(survey_b$id))   #> [1] 22
buildings
#>    site   building km_to_library
#> 1  east   Oak Hall           1.2
#> 2 north  Pine Hall           0.6
#> 3 north Birch Hall           0.4
#> 4 south Cedar Hall           0.9
Problem How you’d find it
north has two rows in the lookup, so all 22 north people appear twice nrow() before and after; sum(duplicated(buildings$site))
“n = 86” counts rows, not people. There are 65 n_distinct(survey_b$id)
The person with no site disappeared: no all.x = TRUE 65 in, but only 64 distinct ids out
The mean and correlation weight north double Redo on the right data: 3.35 and −0.08

The lesson of the duplicated key

A lookup table needs one row per key. Check it before you merge.

if (any(duplicated(buildings$site))) stop("more than one row per site in buildings")
  • The fix is not code. Which building is “north”? Ask whoever made the file
  • The numbers barely moved (3.29 vs 3.35). That’s the danger: nothing looked strange
  • AI writes the merge in one line. You count the rows

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

Write it out

Save the combined data

At the end of week06/combining-practice.R:

write.csv(both_sites, "results/survey_both_waves.csv", row.names = FALSE)

back <- read.csv("results/survey_both_waves.csv")
if (!identical(dim(back), dim(both_sites))) stop("round trip changed the size")
message("wrote ", nrow(back), " rows to results/survey_both_waves.csv")

Tip

Restart R, source the script. If it runs, someone else can rebuild results/survey_both_waves.csv from data/ with one command. Your project should work the same way.

Your project’s combine

⏱ 10 min

For your mid-quarter report (due today):

  1. Do you combine two sources (two files, two years, a lookup table)? Write down the row count you expect after the combine, and why
  2. Check it. Then check your key: one row per key in the lookup table?
  3. After any filter(), write a comment with the row count before and after, and the reason it changed
  4. Does your cleaning script write the cleaned data to results/, and does your report read it from there?

AI on the report: allowed with disclosure. If an AI wrote any combine or filter, the row-count checks are your part, and they go in the report.

Key points

  • rbind() and bind_rows() match columns by name, in any order. A different name is a different column
  • bind_rows() fills mismatches with NA silently: check ncol() and colSums(is.na()) after
  • rename(new = old) before stacking. .id records where each row came from
  • filter() drops NA rows silently; base [ ] doesn’t. Count either way
  • A lookup table with a duplicated key multiplies rows in a merge, without a warning
  • Before every combine: expected rows, unique keys. After: nrow(), n_distinct(id), colSums(is.na())

Before Thursday

Thursday, Nov 5: dplyr II: groups and joins. group_by() replaces today’s filter() per wave, and left_join() replaces merge(all.x = TRUE).