Learning principle: A transformation is not correct simply because the code runs. We must define the observational unit, identify the key, verify what changed, and retain evidence that the result is reproducible.

Required reading. R. A. Irizarry, Introduction to Data Science, Chapter 11: Reshaping data. This tutorial develops original examples and activities around the chapter’s concepts; it is not a reproduction of its worked examples.

Audience. All students complete the core sections and DASC 240 lab. DAAN 703 students also complete the graduate extensions. Synthetic learning-analytics examples are invented for instruction, not drawn from real students or institutional records.

Time plan. Pre-class reading (20 minutes); Session 1 (75 minutes: concepts, long and wide formats, short exercises); Session 2 (75 minutes: compound names, data quality, AI audit); independent lab (45-60 minutes; additional 30-45 minutes for DAAN 703).

Review before submitting: This is a worked draft prepared with AI assistance. Run the entire document in RStudio, record the actual flawed-code diagnostic from Section 8, check the outputs, and revise the written responses to reflect your own understanding. The code has not been executed in this environment.

1 1. Learning outcomes and evaluation

After completing this unit you should be able to:

  1. Specify an observational unit, an identifier/key, and the expected dimensions of a table.
  2. Use tidyr::pivot_longer() and pivot_wider() with meaningful column names and correct data types.
  3. Recover multiple variables encoded in headers using separate_wider_delim() or the .value feature of pivot_longer().
  4. Diagnose duplicate keys, unintended missing values, type changes, and transformations that alter the intended unit of analysis.
  5. Clean column names, detect duplicates, and understand the data.table counterparts melt() and dcast().
  6. Produce reproducible code and independent verification evidence for a proposed transformation, including an AI-generated suggestion if one is used.

Evidence of achievement. Your submission must include executable R code, two before/after tables, at least two explicit validation checks, one visualization, a concise interpretation, and a disclosure of whether AI was used. For DAAN 703, also include a round-trip or equivalent test and a written assessment of a plausible data-quality failure.

1.1 1.1 Tools and setup

Install packages once in the R console, not inside a knitted report:

install.packages(c("rmarkdown", "knitr", "tidyverse", "janitor"))
# Optional graduate extension:
install.packages("data.table")

Load the required packages in the document. Use Knit in RStudio, or render with rmarkdown::render("AI4DS_Chapter11_Data_Wrangling_Tutorial.Rmd"). This tutorial does not require an internet connection after installing packages.

required <- c("tidyr", "dplyr", "ggplot2", "tibble", "janitor")
missing_packages <- required[!vapply(required, requireNamespace,
                                     logical(1), quietly = TRUE)]
if (length(missing_packages) > 0) {
  stop("Install the following packages first: ",
       paste(missing_packages, collapse = ", "))
}
library(tidyr)
library(dplyr)
library(ggplot2)
library(tibble)

2 2. Start with the unit of observation

Long and wide describe the arrangement of fields, not whether the data are correct. The right representation depends on the question. In tidy data, each variable occupies a column, each observation a row, and each value a cell.

Consider an invented learning-analytics table. Each learner has four weekly quiz measurements, stored in separate columns. NA indicates that a score was not recorded; it does not mean zero.

weekly_wide <- tibble(
  learner_id = sprintf("L%02d", 1:6),
  pathway = c("DASC 240", "DASC 240", "DASC 240",
              "DAAN 703", "DAAN 703", "DAAN 703"),
  wk1 = c(6, 5, 7, 8, 7, 6),
  wk2 = c(7, 6, 7, 9, 8, 7),
  wk3 = c(8, NA, 8, 8, 9, 8),
  wk4 = c(9, 7, 9, 9, 10, 9)
)
weekly_wide

Think before coding. In the original table, a row represents one learner. After a long reshape, a row should represent one learner-week. The composite key becomes (learner_id, week), so a complete four-week table should contain 6 × 4 = 24 rows, including one missing score.

Concept Wide table Intended long table
One row represents A learner A learner-week
Identifier learner_id learner_id + week
Weekly values appear in Four wk* columns One quiz_score column
Week stored as Part of a column name An explicit variable

Checkpoint A — Response. The long table will have 24 rows and 4 columns (learner_id, pathway, week, quiz_score). pivot_longer(cols = everything()) is incorrect because it also gathers learner_id and pathway, which are identifiers rather than weekly measurements. It also tries to combine character identifiers with numeric quiz scores.

3 3. pivot_longer(): wide to long

pivot_longer() gathers selected measurement columns into two variables: the former column names and their values. The identifier columns are retained.

weekly_long <- weekly_wide |>
  pivot_longer(
    cols = starts_with("wk"),
    names_to = "week",
    values_to = "quiz_score"
  ) |>
  mutate(week = as.integer(sub("^wk", "", week))) |>
  arrange(learner_id, week)

weekly_long |> slice_head(n = 10)

The expression sub("^wk", "", week) removes the prefix only at the start of the string; as.integer() converts the remaining text into a numeric week. Alternatively, type conversion can happen inside pivot_longer():

weekly_wide |>
  pivot_longer(
    cols = starts_with("wk"),
    names_to = "week",
    values_to = "quiz_score",
    names_transform = list(
      week = function(x) as.integer(sub("^wk", "", x))
    )
  )

3.1 3.1 Verify shape, keys, types, and missingness

stopifnot(nrow(weekly_long) == 24L)
stopifnot(is.integer(weekly_long$week))
stopifnot(anyDuplicated(weekly_long[c("learner_id", "week")]) == 0L)
stopifnot(sum(is.na(weekly_long$quiz_score)) == 1L)

weekly_long |>
  group_by(week) |>
  summarise(rows = n(), observed_scores = sum(!is.na(quiz_score)),
            mean_score = mean(quiz_score, na.rm = TRUE), .groups = "drop")

Why these checks? Row-count verification checks completeness. A composite-key test checks uniqueness. A type check guards against lexical ordering ("1", "10", "2"). A missingness count protects the distinction between unobserved and zero. These checks answer different questions and none replaces the others.

3.2 3.2 Reshape to make an interpretable visualization

weekly_long |>
  ggplot(aes(x = week, y = quiz_score, color = pathway)) +
  geom_point(position = position_jitter(width = 0.035, height = 0),
             alpha = 0.8, size = 2.6, na.rm = TRUE) +
  stat_summary(aes(group = pathway), fun = mean, geom = "line",
               linewidth = 1.1, na.rm = TRUE) +
  scale_x_continuous(breaks = 1:4) +
  labs(title = "Weekly quiz scores in a synthetic learning cohort",
       subtitle = "Points show observed learners; lines connect cohort means",
       x = "Week", y = "Quiz score (out of 10)", color = "Course") +
  theme_minimal(base_size = 12)

Interpretation — Response. The scores generally rise over the four weeks, but the graph does not prove that a course caused the improvement. We do not have a comparison group or information about other factors that could affect scores. Learner L02 has a missing score in week 3. The plotted group mean uses na.rm = TRUE, so that missing score is left out of the week 3 average, rather than counted as zero.

Practice 1 (DASC 240 and DAAN 703). Modify the transformation to create a character variable week_label equal to "Week 1", …, "Week 4", without replacing the numeric week variable. Then confirm there are 24 learner-week rows.

weekly_labeled <- weekly_long |>
  mutate(week_label = paste("Week", week))

weekly_labeled |> select(learner_id, week, week_label, quiz_score) |> head()
nrow(weekly_labeled)
## [1] 24
stopifnot(nrow(weekly_labeled) == 24)

4 4. pivot_wider(): long to wide

pivot_wider() constructs columns using the values of a variable (names_from) and fills them from a measurement (values_from). You must know the key before pivoting. One measurement per learner-week is required for a single numeric value in each cell.

weekly_back <- weekly_long |>
  mutate(week = paste0("wk", week)) |>
  pivot_wider(
    id_cols = c(learner_id, pathway),
    names_from = week,
    values_from = quiz_score
  ) |>
  arrange(learner_id)
weekly_back

4.1 4.1 Round-trip verification

A round trip starts with the original table, reshapes long, then reshapes wide and compares the result with the original. It catches many accidental alterations, but it does not guarantee that the original table had meaningful variables or correct source values.

original_ordered <- weekly_wide |> arrange(learner_id)
roundtrip_ok <- isTRUE(all.equal(as.data.frame(original_ordered),
                                 as.data.frame(weekly_back),
                                 check.attributes = FALSE))
stopifnot(roundtrip_ok)
roundtrip_ok
## [1] TRUE

Practice 2. Starting from weekly_long, produce a table with one row per week and two columns of mean quiz scores, one for each pathway. First aggregate by week and pathway; then use pivot_wider(). Explain why averaging is valid for this particular research question but would be inappropriate for recovering individual learner scores.

weekly_means <- weekly_long |>
  group_by(week, pathway) |>
  summarise(mean_score = mean(quiz_score, na.rm = TRUE), .groups = "drop") |>
  pivot_wider(names_from = pathway, values_from = mean_score)

weekly_means

Practice 2 — Explanation. Averaging makes sense here because the question is about the average quiz score for each course pathway in each week. It would not be appropriate for recreating individual quiz scores because the average hides the differences between learners.

5 5. Compound column names: recover hidden variables

Real spreadsheets often encode multiple variables in a header. Here, headers encode a year, a month, and a metric. In "2026_08_quiz_attempts", the metric itself contains an underscore, so a naive split into three pieces fails.

activity_wide <- tibble(
  section = c("A", "B"),
  `2026_08_visits` = c(120, 95),
  `2026_08_quiz_attempts` = c(70, 58),
  `2026_09_visits` = c(135, 105),
  `2026_09_quiz_attempts` = c(84, 61)
)
activity_wide

5.1 5.1 Explicit, stepwise approach

activity_intermediate <- activity_wide |>
  pivot_longer(-section, names_to = "encoded_name", values_to = "count") |>
  separate_wider_delim(
    encoded_name,
    delim = "_",
    names = c("year", "month", "metric"),
    too_many = "merge"
  )

activity_tidy <- activity_intermediate |>
  pivot_wider(
    id_cols = c(section, year, month),
    names_from = metric,
    values_from = count
  ) |>
  mutate(year = as.integer(year), month = as.integer(month)) |>
  arrange(section, year, month)

activity_intermediate
activity_tidy

Why too_many = "merge"? Without that argument, splitting 2026_08_quiz_attempts on every underscore creates four pieces instead of three. The option preserves the remaining tokens in the third field. This is a deliberate parsing rule, not an instruction to ignore malformed inputs.

5.2 5.2 Compact alternative: .value in pivot_longer()

The special name .value tells tidyr that part of the original header names should become separate output measurement columns. This often avoids an intermediate widening step.

activity_direct <- activity_wide |>
  pivot_longer(
    cols = -section,
    names_to = c("year", "month", ".value"),
    names_pattern = "^([0-9]{4})_([0-9]{2})_(.+)$",
    names_transform = list(year = as.integer, month = as.integer)
  ) |>
  arrange(section, year, month)

stopifnot(isTRUE(all.equal(as.data.frame(activity_tidy),
                           as.data.frame(activity_direct),
                           check.attributes = FALSE)))
activity_direct

Checkpoint B — Response. A simple underscore split produces four parts for 2026_08_quiz_attempts, even though we want three variables: year, month, and metric. I would use the stepwise approach for an audit because I can inspect the intermediate table and check whether the parsing worked before creating the final table.

5.3 5.3 unite() in context

unite() combines columns into a single string. It is helpful for constructing keys or display labels, but converting "08" to numeric 8 may prevent a literal reconstruction of original header names unless you format the month back to two digits.

activity_tidy |>
  mutate(month_code = sprintf("%02d", month)) |>
  unite("period", year, month_code, sep = "-", remove = FALSE) |>
  select(section, period, visits, quiz_attempts)

6 6. Data quality: neat-looking does not mean valid

6.1 6.1 Clean imported column names

registration_raw <- tibble(
  `Learner ID` = c("L01", "L02", "L02", "L03"),
  `Week Number` = c(1, 1, 1, 1),
  `Quiz Score` = c(6, 7, 9, 8)
)
registration <- janitor::clean_names(registration_raw)
names(registration)
## [1] "learner_id"  "week_number" "quiz_score"
registration

6.2 6.2 Repair unusual spreadsheet headers and row identifiers

Two additional Chapter 11 utilities address common spreadsheet layouts. janitor::row_to_names() promotes a selected row of data to column names. Because raw imports sometimes coerce numeric values to text when they share a column with headings, verify and explicitly repair column types afterward.

header_in_first_row <- tibble(
  V1 = c("team", "North", "South"),
  V2 = c("visits", "40", "28")
)
fixed_header <- janitor::row_to_names(header_in_first_row, row_number = 1) |>
  mutate(visits = as.integer(visits))
fixed_header

For algorithms requiring numeric matrices with row identifiers, tibble::column_to_rownames() can transfer an identifying column into the row names before matrix conversion. Preserve a tabular copy with an explicit ID column for routine data analysis; row names are not a substitute for an explicit observational key.

model_input <- tibble(
  metric = c("visits", "minutes"),
  north = c(40, 110),
  south = c(28, 75)
)
numeric_matrix <- model_input |>
  tibble::column_to_rownames(var = "metric") |>
  as.matrix()
numeric_matrix
##         north south
## visits     40    28
## minutes   110    75

Other tidyr parsing functions include separate_wider_position() for fixed-width fields and separate_wider_regex() for more complex structured strings. Choose a parser by the source format, then check the output for unparsed or malformed records.

6.3 6.3 Locate duplicate observational keys

duplicate_keys <- janitor::get_dupes(registration, learner_id, week_number)
duplicate_keys

The two L02, week 1 scores conflict. Do not automatically keep the first value or compute the mean. Appropriate resolution depends on data provenance: are these repeated attempts, corrected entries, or an import error? A separate attempt identifier or authoritative timestamp may be needed to distinguish them.

# Intentionally review this potential problem instead of using it blindly:
registration |>
  pivot_wider(names_from = week_number, values_from = quiz_score)
# What shape and warning do you expect for learner L02?

pivot_wider() can produce a list-column when multiple values belong to a single intended cell. The resulting table can look compact while violating the planned analytical structure. A warning or list-column is evidence to investigate, not an invitation to silence it with an arbitrary aggregation.

6.4 6.4 Explicit versus dropped missing observations

retained <- weekly_long
removed <- weekly_long |> filter(!is.na(quiz_score))

tibble(
  version = c("Retain NA learner-weeks", "Drop NA learner-weeks"),
  rows = c(nrow(retained), nrow(removed)),
  observed_scores = c(sum(!is.na(retained$quiz_score)),
                      sum(!is.na(removed$quiz_score)))
)

pivot_longer(values_drop_na = TRUE) can remove missing measurements during reshaping. That can be useful for some tasks, but it changes the number of retained rows. Document whether an absent measurement represents missing data, a structurally impossible combination, or an intentionally omitted record. In longitudinal settings this choice can affect denominators and interpretation.

Practice 3 — Response. Learner L02 has two different scores for week 1, so the learner-week combination is not unique and we cannot tell which score is correct. Before resolving this, I would request an attempt timestamp to determine whether these are separate attempts or a corrected entry.

7 7. Alternative engine: data.table (overview; DAAN 703 extension)

Chapter 11 also introduces data.table. It uses melt() for wide-to-long and dcast() for long-to-wide. You need not switch frameworks for this tutorial; the aim is to recognize the equivalent operations and compare results. This chunk runs only if the optional package is installed.

if (requireNamespace("data.table", quietly = TRUE)) {
  dt_weekly <- data.table::as.data.table(weekly_wide)
  dt_long <- data.table::melt(
    dt_weekly,
    id.vars = c("learner_id", "pathway"),
    measure.vars = paste0("wk", 1:4),
    variable.name = "week", value.name = "quiz_score",
    variable.factor = FALSE
  )
  dt_long[, week := as.integer(sub("^wk", "", week))]
  dt_wide <- data.table::dcast(
    dt_long, learner_id + pathway ~ week, value.var = "quiz_score"
  )
  print(head(dt_long))
  print(head(dt_wide))
} else {
  cat("Optional: install.packages('data.table') to run the comparison.")
}
##    learner_id  pathway  week quiz_score
##        <char>   <char> <int>      <num>
## 1:        L01 DASC 240     1          6
## 2:        L02 DASC 240     1          5
## 3:        L03 DASC 240     1          7
## 4:        L04 DAAN 703     1          8
## 5:        L05 DAAN 703     1          7
## 6:        L06 DAAN 703     1          6
## Key: <learner_id, pathway>
##    learner_id  pathway     1     2     3     4
##        <char>   <char> <num> <num> <num> <num>
## 1:        L01 DASC 240     6     7     8     9
## 2:        L02 DASC 240     5     6    NA     7
## 3:        L03 DASC 240     7     7     8     9
## 4:        L04 DAAN 703     8     9     8     9
## 5:        L05 DAAN 703     7     8     9    10
## 6:        L06 DAAN 703     6     7     8     9

DAAN 703 — Response. I would compare memory usage and the time required for the entire workflow, including loading data and converting formats. I would also verify that the two methods produce equivalent rows, values, keys, and missing values. data.table can modify an object by reference, so I would check whether the original data changes unexpectedly. Package versions, computer specifications, and consistent input data matter for reproducibility. I would run repeated benchmarks on a realistically large dataset before making any claim that one method is faster.

8 8. AI4DS verification exercise: evaluate, do not copy

Task. An AI assistant recommends the following code to obtain long-format quiz scores:

# AI-suggested code: intentionally flawed. DO NOT treat as verified.
weekly_wide |>
  pivot_longer(cols = everything(),
               names_to = "week", values_to = "quiz_score")

Independent audit:

  1. State the intended unit of observation and list the identifier columns that must survive unchanged.
  2. Predict the type problem when character learner identifiers and numeric scores are combined into one value column. Run the code interactively and record the diagnostic message. Do not insert an intentional failing chunk into your rendered submission.
  3. Repair the code. Validate row count, composite-key uniqueness, numeric quiz_score, and number of missing scores.
  4. Explain in 80-120 words how the executed evidence supports your conclusion. Documentation and successful execution are evidence; a model’s confident explanation is not.

AI-use pathways. You may complete this exercise without AI. If you consult AI, use only the synthetic data here, preserve one short excerpt of its suggestion, and disclose what was accepted, rejected, or corrected. Never paste restricted, unpublished, or personally identifying student information into an external AI system. All students are accountable for the final analysis and claims.

Record What to report
AI use “No AI used” or tool and narrow purpose
Proposed change Exact transformation or short excerpt (if applicable)
Independent evidence Check(s), output, and relevant package documentation
Human decision Accepted, modified, or rejected, with reason

8.1 8.1 Completed AI-code audit (U6 reference)

Intended unit and identifiers. After reshaping, one row should represent one learner in one week. The learner_id and pathway columns must be preserved, and (learner_id, week) must be unique.

Expected diagnostic. The intentionally flawed pivot_longer(cols = everything()) includes both character and numeric fields. tidyr should report that it cannot combine the identifier fields, which are character, with numeric score fields. Run the flawed code interactively in your own RStudio Console and replace this prediction with the diagnostic you actually receive; it must not be run in a knitted chunk.

audit_long <- weekly_wide |>
  pivot_longer(cols = starts_with("wk"),
               names_to = "week", values_to = "quiz_score") |>
  mutate(week = as.integer(sub("^wk", "", week)))

stopifnot(nrow(audit_long) == 24)
stopifnot(anyDuplicated(audit_long[c("learner_id", "week")]) == 0)
stopifnot(is.numeric(audit_long$quiz_score))
stopifnot(sum(is.na(audit_long$quiz_score)) == 1)

cat("Rows:", nrow(audit_long), "\n")
## Rows: 24
cat("Duplicate keys:", anyDuplicated(audit_long[c("learner_id", "week")]), "\n")
## Duplicate keys: 0
cat("Missing scores:", sum(is.na(audit_long$quiz_score)), "\n")
## Missing scores: 1

AI audit explanation (80–120 words). The original suggestion did not correctly identify which columns were measurements. Including every column in the pivot would try to combine learner IDs and pathway labels with numeric scores. I corrected the transformation by selecting only the week columns and keeping the identifier columns unchanged. Then I checked the number of rows, the uniqueness of learner-week pairs, the score data type, and the number of missing scores. The expected result is 24 rows, no duplicate learner-week keys, numeric scores, and one missing score. These checks provide evidence that the corrected shape matches the intended observations. They do not independently confirm that the source scores were recorded accurately.

AI-use record. AI tool: ChatGPT, used to help draft and explain the R Markdown solutions. Proposed change: restrict the pivot to starts_with("wk") and validate the resulting table. Independent evidence: the code checks above and the diagnostic obtained interactively. Human decision: reject the everything() suggestion; review and verify the correction before submission.

9 9. Independent lab: learning-resource use (synthetic data)

The following small dataset is new to this lab. It describes three fictitious outreach teams over three months. Each team-month has two metrics: visits and minutes. The missing mar_visits value for Central is intentionally unrecorded. A zero, if present, would mean a genuine count of zero visits.

resource_wide <- tibble(
  team = c("North", "Central", "South"),
  jan_visits = c(40, 35, 28),
  jan_minutes = c(110, 90, 75),
  feb_visits = c(45, 37, 30),
  feb_minutes = c(120, 95, 82),
  mar_visits = c(51, NA, 32),
  mar_minutes = c(130, 101, 88)
)
resource_wide

9.1 DASC 240: required laboratory tasks

Task U1: Plan (2 points). Describe the observational unit of the original table and the desired result. Identify its key and predict its number of rows if missing values are retained.

U1 — Response. The original table has one row per team, with six columns of monthly measurements. The desired tidy table has one row for each team-month combination, with visits and minutes as separate numeric columns. The key is (team, month). There are 3 teams and 3 months, so the result should contain 9 rows, retaining Central’s missing March visits value.

Task U2: One measure long (3 points). Select only the three *_visits columns and pivot them into month and visits. Keep team. Verify that your result has nine rows.

resource_visits <- resource_wide |>
  select(team, ends_with("visits")) |>
  pivot_longer(cols = ends_with("visits"),
               names_to = "month", values_to = "visits") |>
  mutate(month = sub("_visits", "", month))

resource_visits
stopifnot(nrow(resource_visits) == 9)

Task U3: Two measures tidy (4 points). Reshape the original data to one row per (team, month), with separate numeric visits and minutes columns. Use either a stepwise approach or .value with names_sep = "_". Explain in a sentence why this header format can be split with a simple separator whereas the compound names in Section 5 could not.

resource_tidy <- resource_wide |>
  pivot_longer(cols = -team,
               names_to = c("month", ".value"),
               names_sep = "_") |>
  arrange(team, match(month, c("jan", "feb", "mar")))

resource_tidy

U3 — Explanation. These names have exactly two parts, such as jan_visits, so splitting on the underscore works. The earlier headers include a metric like quiz_attempts that contains another underscore.

Task U4: Verify (3 points). Assert or display (i) nine rows, (ii) unique (team, month) pairs, (iii) exactly one missing visits value, and (iv) numeric visits and minutes. Then explain whether NA should be converted to zero.

stopifnot(nrow(resource_tidy) == 9)
stopifnot(anyDuplicated(resource_tidy[c("team", "month")]) == 0)
stopifnot(sum(is.na(resource_tidy$visits)) == 1)
stopifnot(is.numeric(resource_tidy$visits), is.numeric(resource_tidy$minutes))

cat("Rows:", nrow(resource_tidy), "\n")
## Rows: 9
cat("Missing visits:", sum(is.na(resource_tidy$visits)), "\n")
## Missing visits: 1
cat("Duplicate keys:", anyDuplicated(resource_tidy[c("team", "month")]), "\n")
## Duplicate keys: 0

U4 — Explanation. I would keep Central’s missing March value as NA. Changing it to zero would mean that Central had no visits, but the dataset only tells us the number was not recorded.

Task U5: Visualize and interpret (3 points). Draw a labeled plot of monthly visits by team. Order month levels as January, February, March. Write two or three sentences describing the observed pattern and the missing value without asserting unmeasured changes.

resource_plot <- resource_tidy |>
  mutate(month = factor(month, levels = c("jan", "feb", "mar"),
                        labels = c("January", "February", "March")))

ggplot(resource_plot, aes(x = month, y = visits, color = team, group = team)) +
  geom_line(na.rm = TRUE) +
  geom_point(size = 3, na.rm = TRUE) +
  labs(title = "Monthly resource visits by team",
       x = "Month", y = "Visits", color = "Team") +
  theme_minimal()

U5 — Interpretation. North’s visits increase from 40 in January to 51 in March, and South’s visits increase from 28 to 32. Central rises from 35 to 37 between January and February, but its March visits are missing, so we cannot describe its March trend. These observations do not show what caused any changes.

Task U6: AI verification and reflection (5 points). Complete the audit in Section 8 and submit the AI-use record (including the “No AI used” option). State which check was most informative and why.

U6 — Response. The completed audit and disclosure appear in Section 8.1. I found the composite-key check especially informative because a table can have the expected number of rows and still contain repeated learner-week combinations. I would also preserve the missing-value check because an unrecorded score should not be silently treated as zero.

9.2 DAAN 703: additional graduate requirements

Complete U1-U6 and the following extensions. The 20-point rubric below includes these as enhanced evidence expectations rather than additional points.

Task G1: Two implementations and equivalence. Implement the two-measure reshape both (a) through pivot_longer() + separate_wider_delim() + pivot_wider() and (b) through pivot_longer(names_to = c("month", ".value"), names_sep = "_"). Compare results after consistent sorting; provide a programmatic equality check and discuss what it does not verify.

Task G2: Ambiguous duplicates. Construct a scenario where Central, feb has two different visits entries. Demonstrate the failure of key uniqueness, describe how widening responds, and propose a provenance-based resolution rule. Do not use mean() or distinct() as an undocumented fix.

Task G3: Missingness and interpretation. Compute monthly mean visits using available observations and show the denominator used each month. Explain in 100-150 words how imputing zero for Central’s missing March value would change the March estimate and interpretation. No causal claims.

9.3 G1 — Two approaches and their equivalence

# Method A: stepwise reshaping
resource_stepwise <- resource_wide |>
  pivot_longer(cols = -team,
               names_to = "column_name", values_to = "value") |>
  separate_wider_delim(column_name, delim = "_",
                       names = c("month", "metric")) |>
  pivot_wider(id_cols = c(team, month),
              names_from = metric, values_from = value) |>
  arrange(team, match(month, c("jan", "feb", "mar")))

# Method B: the .value shortcut
resource_direct <- resource_wide |>
  pivot_longer(cols = -team,
               names_to = c("month", ".value"),
               names_sep = "_") |>
  arrange(team, match(month, c("jan", "feb", "mar")))

resource_stepwise
resource_direct
same_results <- isTRUE(all.equal(resource_stepwise, resource_direct,
                                 check.attributes = FALSE))
stopifnot(same_results)
same_results
## [1] TRUE

G1 — Explanation. Both approaches produce the same team-month observations and measured values. This equality check does not prove the original source values are correct, that the column names were meaningful, or that the same approach will handle all possible future data formats.

9.4 G2 — Ambiguous duplicate records

# Add a second, conflicting Central-February visit record.
visits_duplicate <- resource_visits |>
  add_row(team = "Central", month = "feb", visits = 41)

# Locate the duplicated observational keys.
visits_duplicate |>
  count(team, month) |>
  filter(n > 1)
stopifnot(anyDuplicated(visits_duplicate[c("team", "month")]) > 0)

# Widening without a resolution rule creates a list-column.
duplicate_wide <- visits_duplicate |>
  pivot_wider(names_from = month, values_from = visits)

duplicate_wide

G2 — Explanation. Central has two February visits values, 37 and 41, so (team, month) is no longer unique. Widening has no single number to place in the Central-February cell and can produce a list-column (with a warning). I would request an import timestamp or source record ID and determine whether the second entry is a correction or a separate reporting event. Only then would I apply a documented selection rule; I would not silently average or discard an entry.

9.5 G3 — Monthly means and missingness sensitivity

monthly_means <- resource_tidy |>
  group_by(month) |>
  summarise(mean_visits = mean(visits, na.rm = TRUE),
            denominator = sum(!is.na(visits)),
            .groups = "drop")

monthly_means
march_observed <- resource_tidy |>
  filter(month == "mar") |>
  summarise(observed_mean = mean(visits, na.rm = TRUE),
            mean_if_missing_zero = mean(replace(visits, is.na(visits), 0)))

march_observed

G3 — Explanation (100–150 words). The monthly averages use only the visit counts that were actually recorded. January uses three teams and February also uses three teams. March uses just two teams because Central’s value is missing. The observed March average is 41.5 visits, based on North’s 51 and South’s 32 visits. If I replace Central’s missing value with zero, the March average becomes about 27.7 visits across all three teams. This is a large difference, but it is caused by the assumption about the missing value rather than by a change we observed in the original data. A missing value means the result was not recorded, whereas a zero means the team definitely had no visits. I would report the available-case denominator and avoid substituting zero without a documented reason.

Optional G4: Alternative engine. Reproduce the two-measure transformation in data.table and verify the output against the tidyr result. Explain what a fair timing comparison would require.

10 10. Rubric (20 points total)

Criterion Points Evidence expected
Observational unit, keys, and reshape plan 3 Explicit grain, key, expected dimensions
Correct data transformations 5 Valid long/tidy representation, appropriate types
Verification and data-quality reasoning 5 At least two checks; missingness and duplicates evaluated; DAAN: equivalence and failure investigation
Visualization and interpretation 3 Labeled, correctly ordered plot; cautious interpretation; DAAN: denominator or sensitivity reasoning
Reproducibility and AI-use disclosure 4 Rendered report, code runs from a fresh R session, transparent AI audit or no-AI record
Total 20

10.1 Submission checklist

11 11. Exit ticket

1. A variable in a header is stored in the column name (for example, jan_visits), while an explicit variable has its own column (for example, month).

2. If an intended key appears more than once, pivot_wider() has multiple values for the same cell and may create a list-column rather than a single numeric entry.

3. First I would compare the expected and actual row counts and check key uniqueness; then I would examine missing values and duplicate combinations.

4. Dropping Central’s missing March visits row would leave only two teams for March and could change the denominator for monthly comparisons.

5. I would inspect the resulting data, validate dimensions, keys, types, and missingness, and compare against an independently justified expected result before trusting the AI-suggested code.

  1. What is the difference between a variable encoded in a header and an explicit variable column?
  2. Why can pivot_wider() reveal duplicate keys through list-columns?
  3. Which verification check would you use first if a pivot unexpectedly changed the number of rows?
  4. Give one example in which dropping NA while reshaping could change the meaning of an analysis.
  5. If an AI tool provides working code, what independent evidence is still needed before using the result?

12 12. Reference and additional documentation

sessionInfo()
## R version 4.6.1 (2026-06-24)
## Platform: aarch64-apple-darwin23
## Running under: macOS Sequoia 15.6.1
## 
## Matrix products: default
## BLAS:   /Library/Frameworks/R.framework/Versions/4.6/Resources/lib/libRblas.0.dylib 
## LAPACK: /Library/Frameworks/R.framework/Versions/4.6/Resources/lib/libRlapack.dylib;  LAPACK version 3.12.1
## 
## locale:
## [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
## 
## time zone: America/New_York
## tzcode source: internal
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
## [1] tibble_3.3.1  ggplot2_4.0.3 dplyr_1.2.1   tidyr_1.3.2  
## 
## loaded via a namespace (and not attached):
##  [1] gtable_0.3.6        jsonlite_2.0.0      compiler_4.6.1     
##  [4] tidyselect_1.2.1    stringr_1.6.0       snakecase_0.11.1   
##  [7] jquerylib_0.1.4     scales_1.4.0        yaml_2.3.12        
## [10] fastmap_1.2.0       R6_2.6.1            labeling_0.4.3     
## [13] generics_0.1.4      knitr_1.52          janitor_2.2.1      
## [16] lubridate_1.9.5     bslib_0.12.0        pillar_1.11.1      
## [19] RColorBrewer_1.1-3  rlang_1.3.0         stringi_1.8.9      
## [22] cachem_1.1.0        xfun_0.60           sass_0.4.10        
## [25] S7_0.2.2            otel_0.2.0          timechange_0.4.0   
## [28] cli_3.6.6           withr_3.0.3         magrittr_2.0.5     
## [31] digest_0.6.39       grid_4.6.1          rstudioapi_0.19.0  
## [34] lifecycle_1.0.5     vctrs_0.7.3         data.table_1.18.6.1
## [37] evaluate_1.0.5      glue_1.8.1          farver_2.1.2       
## [40] rmarkdown_2.32      purrr_1.2.2         tools_4.6.1        
## [43] pkgconfig_2.0.3     htmltools_0.5.9