Deep-dive · Wrangling ABCD-Shaped Data

Optional companion to Module 2

This page expands on the data-wrangling steps in Module 2. Nothing here is needed for the session; it is for when you want to know what else the tools can do, or why they behave the way they do.

library(dplyr)


Attaching package: 'dplyr'

The following objects are masked from 'package:stats':

    filter, lag

The following objects are masked from 'package:base':

    intersect, setdiff, setequal, union

library(tidyr)
library(readr)

data <- readRDS("data/data.RDS")
levels_dict <- read_csv("data/data_dictionary_levels.csv")

Rows: 84 Columns: 5

── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (2): name, label
dbl (2): value, order_level
lgl (1): label_es

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Choosing columns

Module 2 used select() by position, by name and by matches(). There are several other selection helpers, and they all work anywhere dplyr expects a set of columns — select(), across(), pivot_longer(cols = ...):

Helper	Keeps columns whose name…
`starts_with("nc_")`	begins with `nc_`
`ends_with("_sum")`	ends with `_sum`
`contains("cbcl")`	contains `cbcl` anywhere, taken literally
`matches("_(wkdy\\|wknd)_")`	matches a regular expression
`where(is.numeric)`	— this one tests the column’s contents, not its name

ABCD’s naming scheme makes these very effective. Every NIH Toolbox score:

data |>
  select(starts_with("nc_")) |>
  names()

 [1] "nc_y_nihtb__comp__cryst__agecorr_score"
 [2] "nc_y_nihtb__comp__fluid__agecorr_score"
 [3] "nc_y_nihtb__comp__tot__agecor_score"   
 [4] "nc_y_nihtb__crdst__agecorr_score"      
 [5] "nc_y_nihtb__flnkr__agecor_score"       
 [6] "nc_y_nihtb__lswmt__agecor_score"       
 [7] "nc_y_nihtb__picsq__agecor_score"       
 [8] "nc_y_nihtb__picvcb__agecor_score"      
 [9] "nc_y_nihtb__pttcp__agecor_score"       
[10] "nc_y_nihtb__readr__agecor_score"

Every parent-reported variable — the second chunk of the name is the reporter:

data |>
  select(matches("^[a-z]{2}_p_")) |>
  names()

[1] "mh_p_cbcl__synd__ext_sum"   "mh_p_cbcl__synd__int_sum"  
[3] "nt_p_yst__screen__wkdy_sum" "nt_p_yst__screen__wknd_sum"

^[a-z]{2}_p_ reads as “at the start, two lowercase letters, then _p_”. You do not need to become fluent in regular expressions, but the handful that match ABCD’s structure are worth keeping in a note.

Helpers combine. - removes, and & means both:

data |>
  select(starts_with("mh_") & ends_with("_sum"), -contains("bisbas")) |>
  names()

[1] "mh_p_cbcl__synd__ext_sum" "mh_p_cbcl__synd__int_sum"
[3] "mh_y_upps__nurg_sum"      "mh_y_upps__pers_sum"     
[5] "mh_y_upps__plan_sum"      "mh_y_upps__purg_sum"     
[7] "mh_y_upps__sens_sum"

Doing the same thing to many columns: `across()`

across() applies one function to several columns inside mutate() or summarise(). It takes two arguments: which columns, and what to do.

Module 2 wrote the function as ~ round(.x). That ~ form is a compact way to write a small function on the spot, with .x standing for each column in turn. When the function needs no extra arguments you can just name it:

data |>
  summarise(across(starts_with("nt_"), mean)) |>
  glimpse()

Rows: 1
Columns: 4
$ nt_p_yst__screen__wkdy_sum <dbl> NA
$ nt_p_yst__screen__wknd_sum <dbl> NA
$ nt_y_stq__screen__wkdy_sum <dbl> NA
$ nt_y_stq__screen__wknd_sum <dbl> NA

data |>
  summarise(across(starts_with("nt_"), ~ mean(.x, na.rm = TRUE))) |>
  glimpse()

Rows: 1
Columns: 4
$ nt_p_yst__screen__wkdy_sum <dbl> 4.213753
$ nt_p_yst__screen__wknd_sum <dbl> 6.148314
$ nt_y_stq__screen__wkdy_sum <dbl> 6.432967
$ nt_y_stq__screen__wknd_sum <dbl> 9.499473

The first gives NAs — mean() meets missing values and, as Module 1 showed, refuses to guess. The second passes na.rm = TRUE, and extra arguments are exactly the case the ~ .x form exists for.

Several summaries at once, with a named list:

data |>
  group_by(session_id) |>
  summarise(
    across(
      c(nt_y_stq__screen__wkdy_sum, nt_y_stq__screen__wknd_sum),
      list(mean = ~ mean(.x, na.rm = TRUE), sd = ~ sd(.x, na.rm = TRUE))
    )
  )

# A tibble: 5 × 5
  session_id nt_y_stq__screen__wkdy_sum_mean nt_y_stq__screen__wkdy_sum_sd
  <fct>                                <dbl>                         <dbl>
1 ses-00A                               4.33                          3.34
2 ses-01A                               5.53                          5.27
3 ses-02A                               6.59                          5.17
4 ses-03A                               7.66                          5.75
5 ses-04A                               8.54                          5.97
# ℹ 2 more variables: nt_y_stq__screen__wknd_sum_mean <dbl>,
#   nt_y_stq__screen__wknd_sum_sd <dbl>

The new columns are named {column}_{function} automatically.

Recoding values

`case_when()`

Module 2 recoded sex with case_when(). Each line is condition ~ result. Lines are tried top to bottom, and the first match wins — so order matters when conditions overlap:

data |>
  mutate(
    screen_band = case_when(
      nt_y_stq__screen__wkdy_sum > 24 ~ "implausible",
      nt_y_stq__screen__wkdy_sum >= 6 ~ "6+ h",
      nt_y_stq__screen__wkdy_sum >= 3 ~ "3–6 h",
      nt_y_stq__screen__wkdy_sum >= 0 ~ "under 3 h",
      .default = NA
    )
  ) |>
  count(screen_band)

  screen_band    n
1       3–6 h 1024
2        6+ h  884
3 implausible   32
4   under 3 h  332
5        <NA>   23

Put the > 24 line last and it would never fire: those rows would already have matched >= 6.

`factor(levels, labels)`

For coded variables with a dictionary, the dataset page shows a different route: hand the codes and labels to factor(). This has two advantages over case_when() — you do not have to type the labels, so there is nothing to mistype, and the result is a factor that keeps the dictionary’s order.

case_when() is the right tool when you are making categories (like the screen-time bands above). factor() is the right tool when a dictionary already defines them.

Labelling every coded variable at once

The dataset page labels one variable at a time and notes that doing all thirteen in one stroke needs more tools. Here they are.

First, write the one-variable recipe as a function — a reusable piece of code with a name:

apply_labels <- function(x, var) {
  lv <- levels_dict |>
    filter(name == var) |>
    arrange(order_level)

  factor(x, levels = lv$value, labels = lv$label)
}

function(x, var) declares two inputs: x, the column’s values, and var, the column’s name, used to look up its levels. The last line inside the braces is what the function returns.

Then apply it with across(). any_of() selects every column that appears in the dictionary, and cur_column() tells the function which column it is currently working on:

data_labelled <- data |>
  mutate(
    across(
      any_of(unique(levels_dict$name)),
      ~ apply_labels(.x, cur_column())
    )
  )

data_labelled |> count(ab_g_dyn__visit_type)

  ab_g_dyn__visit_type    n
1              On-site 1521
2               Remote  477
3               Hybrid  297

data_labelled |> count(ab_g_dyn__cohort_income__hhold__3lvl)

  ab_g_dyn__cohort_income__hhold__3lvl    n
1                                < 50k  590
2                          50k to 100k  466
3                               > 100k 1055
4                           Don't know   76
5                    Decline to answer   83
6                                 <NA>   25

Thirteen variables, one pipeline. Check the labelling did not invent missing values — a code missing from the dictionary would silently become NA:

coded <- intersect(names(data), levels_dict$name)

tibble(
  variable = coded,
  na_before = sapply(coded, \(v) sum(is.na(data[[v]]))),
  na_after  = sapply(coded, \(v) sum(is.na(data_labelled[[v]])))
)

# A tibble: 13 × 3
   variable                             na_before na_after
   <chr>                                    <int>    <int>
 1 ab_g_dyn__visit_type                         0        0
 2 ab_g_dyn__design_site                        0        0
 3 ab_g_dyn__cohort_edu__cgs                   34       34
 4 ab_g_dyn__cohort_income__hhold__3lvl        25       25
 5 ab_g_dyn__cohort_income__hhold__6lvl        25       25
 6 ab_g_stc__cohort_ethn                        0        0
 7 ab_g_stc__cohort_ethnrace__leg               0        0
 8 ab_g_stc__cohort_race__nih                   0        0
 9 ab_g_stc__cohort_sex                         0        0
10 fc_y_nsc__ns_003                           436      436
11 su_y_itu__alc_001                          666      666
12 su_y_itu__mj_001                           597      597
13 su_y_itu__nic_001                          964      964

Identical counts: every code found its label. (\(v) is shorthand for function(v).)

Joins

A join combines two tables by matching values in a key column. Module 2 used left_join() to attach site names. The family:

Join	Keeps
`left_join(x, y)`	every row of `x`; columns from `y` where they match, `NA` where they do not
`inner_join(x, y)`	only rows that match in both
`full_join(x, y)`	every row of both
`anti_join(x, y)`	rows of `x` with no match in `y` — the one for finding problems

left_join() is the everyday choice for “look up extra information”. It never loses a row of your data, so a failed match shows up as an NA, not as a participant silently disappearing.

anti_join() is the checking tool. Which site codes in the data have no entry in the dictionary?

site_levels <- levels_dict |>
  filter(name == "ab_g_dyn__design_site") |>
  transmute(value = as.character(value), site_label = label)

data |>
  mutate(site = as.character(ab_g_dyn__design_site)) |>
  distinct(site) |>
  anti_join(site_levels, join_by(site == value))

[1] site
<0 rows> (or 0-length row.names)

Zero rows: every site matched.

Why Module 2 converted both keys to character

Keys must be the same type. In the data, site is a factor; in the levels dictionary, value is a number. Try joining them as they are:

data |>
  left_join(
    levels_dict |> filter(name == "ab_g_dyn__design_site"),
    join_by(ab_g_dyn__design_site == value)
  )

Error in `left_join()`:
! Can't join `x$ab_g_dyn__design_site` with `y$value` due to
  incompatible types.
ℹ `x$ab_g_dyn__design_site` is a <factor<68132>>.
ℹ `y$value` is a <double>.

The error is helpful: it names both columns and both types. Converting each side with as.character() gives them a common type that preserves the codes exactly.

Do not use as.numeric() on a factor

It is tempting to convert the factor to a number instead. But as.numeric() on a factor returns the level’s position, not the value printed on it. Here the levels happen to be "1" to "22" in order, so it would work by luck. With levels like "777" or out-of-order codes, it quietly gives wrong numbers. Go via as.character() first, or join as text.

Reshaping: long and wide

pivot_longer() stacks columns into rows; pivot_wider() spreads them back. The two are inverses.

anthro_long <- data |>
  select(participant_id, session_id, starts_with("ph_y_anthr")) |>
  pivot_longer(
    cols = starts_with("ph_y_anthr"),
    names_to = "measure",
    values_to = "value"
  )

anthro_long

# A tibble: 6,885 × 4
   participant_id session_id measure                 value
   <chr>          <fct>      <chr>                   <dbl>
 1 sub-0001       ses-00A    ph_y_anthr__height_mean  58.1
 2 sub-0001       ses-00A    ph_y_anthr__weight_mean 107  
 3 sub-0001       ses-00A    ph_y_anthr__waist_001    30.4
 4 sub-0001       ses-01A    ph_y_anthr__height_mean  60.2
 5 sub-0001       ses-01A    ph_y_anthr__weight_mean 116. 
 6 sub-0001       ses-01A    ph_y_anthr__waist_001    NA  
 7 sub-0001       ses-02A    ph_y_anthr__height_mean  63.2
 8 sub-0001       ses-02A    ph_y_anthr__weight_mean 147. 
 9 sub-0001       ses-02A    ph_y_anthr__waist_001    22.2
10 sub-0001       ses-03A    ph_y_anthr__height_mean  NA  
# ℹ 6,875 more rows

Three times the rows, three columns fewer. Long data is what ggplot2 wants for faceting and colouring by “which measure”, and what group_by() wants for “the same summary for each measure”:

anthro_long |>
  group_by(measure) |>
  summarise(
    n_present = sum(!is.na(value)),
    mean      = mean(value, na.rm = TRUE)
  )

# A tibble: 3 × 3
  measure                 n_present  mean
  <chr>                       <int> <dbl>
1 ph_y_anthr__height_mean      1917  60.9
2 ph_y_anthr__waist_001        1804  29.8
3 ph_y_anthr__weight_mean      1917 113.

pivot_wider() goes the other way. A common longitudinal use: one row per participant, one column per visit — handy for looking at each person’s trajectory, and the shape a paired test in Module 3 will want:

data |>
  select(participant_id, session_id, nt_y_stq__screen__wkdy_sum) |>
  pivot_wider(
    names_from = session_id,
    values_from = nt_y_stq__screen__wkdy_sum
  )

# A tibble: 500 × 6
   participant_id `ses-00A` `ses-01A` `ses-02A` `ses-03A` `ses-04A`
   <chr>              <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
 1 sub-0001             3.1       5         6.5       4.4       9.1
 2 sub-0002             4.5      11.5       8.7       8.4       8  
 3 sub-0003             4.4      NA        NA        NA        NA  
 4 sub-0004             3.6       3.4       4.8       7.7      NA  
 5 sub-0005             2.3       3.7       3.5       5.4       7.6
 6 sub-0006             3.8       4.4       5         8         8.8
 7 sub-0007             3.4       3.4      NA         4.7       8.3
 8 sub-0008             1.7      NA        NA        NA        NA  
 9 sub-0009             3.9       6         6        13.1      10.8
10 sub-0010             3         3.7       4.4       5.6       5.5
# ℹ 490 more rows

The NAs on the right are participants who had left the study by that visit — the attrition described on the dataset page, now visible as gaps in a row rather than as absent rows.

Back to Module 2.