tidyr Interview Questions: Pivot Keys, Duplicate Cells, and Nested Data

A tidyr interview question becomes interesting when a reshape produces the wrong observations without producing an error. A forgotten replicate key can merge measurements. An extra timestamp can prevent rows from combining. Expanding two nested columns separately can multiply records that were supposed to stay paired.

Define what one row and one output cell represent before choosing the pivot. Then explain the key, the value type, the treatment of missing observations, and the expected row count. This guide uses original measurement and nested-data exercises to make those decisions visible.

Evidence boundary: API behavior below comes from official tidyr documentation, checked October 5, 2026. The examples and interview recommendations are original. No candidate report establishes an employer's question list or frequency. R snippets were reviewed against the references; local Python checks verify the constructed ledgers, not execution by the tidyr engine.

How do you define pivot keys before writing code?

Start with a sentence: “One row is one site at one visit.” If two readings exist for that site and visit, decide whether the sentence needs a replicate identifier. A reshape cannot decide that scientific or business rule for you.

Our first fixture has two sites and two visits. Temperature and status are different measures, encoded in column names:

library(tidyr)
library(dplyr)

wide <- tibble(
  site = c("S1", "S2"),
  temp_V1 = c(18, 21), temp_V2 = c(22, NA_real_),
  status_V1 = c("ok", "ok"),
  status_V2 = c("ok", "sensor_fault")
)

long <- wide |>
  pivot_longer(
    cols = -site,
    names_to = c(".value", "visit"),
    names_pattern = "^(temp|status)_(V[12])$"
  )

Official behavior: In pivot_longer, the special .value component identifies the output value-column name. Here it creates temp and status; visit receives the other name component. It avoids forcing numeric temperatures and character statuses into one shared value column.

The expected result has four rows: S1/V1, S1/V2, S2/V1 and S2/V2. S2/V2 still exists because its status is sensor_fault, even though its temperature is missing. The original two rows therefore become four observations, each containing two measures. They do not become eight interchangeable measurement rows.

Our key check is explicit:

duplicates <- long |>
  count(site, visit) |>
  filter(n > 1L)
stopifnot(nrow(duplicates) == 0L, nrow(long) == 4L)

Explain the naming pattern as part of the contract. It accepts only the two measures and two visits shown. A future temp_V3 needs a deliberate schema update. In production, validate selected column names before pivoting so that an unexpected name does not silently turn into missing name components.

If the interviewer adds temp_unit, selecting every column except site becomes questionable. Should the unit stay as metadata, vary by visit, or become another measure? Clarify that before broadening the selection. This follow-up tests whether your code captures the intended grain rather than just the current spelling of the columns.

Why does pivot_wider produce list columns?

Our second fixture contains repeated measurements. Each reading has a site, visit, replicate, metric and value:

readings <- tibble(
  site = c("S1", "S1", "S1", "S2"),
  visit = c("V1", "V1", "V2", "V1"),
  replicate = c(1L, 2L, 1L, 1L),
  metric = c("temp", "temp", "temp", "temp"),
  value = c(18, 22, 20, 21)
)

collisions <- readings |>
  count(site, visit, metric) |>
  filter(n > 1L)

result <- readings |>
  pivot_wider(id_cols = c(site, visit),
              names_from = metric, values_from = value)

The proposed scalar cell S1/V1/temp has two candidates: 18 and 22. Official semantics: pivot_wider uses id_cols for observation identity, names_from for output column names and values_from for cell contents. Nonunique cell mappings need an explicit policy; the default can warn and produce list columns.

Our diagnosis starts with collisions, not with suppressing the warning. It shows one key with a count of two. These could be valid replicates, duplicated ingestion, or distinct readings whose identifier was omitted. Each explanation implies a different repair.

Interpretation of 18 and 22	Defensible response	Information preserved or lost
Two separate replicates	Include replicate in id_cols	Keeps both observations
Two values intentionally stored together	Use values_fn = list	Keeps both values in a list cell
Replicate mean is the requested measure	Use values_fn = mean after agreeing on missing-value policy	Produces 20; loses individual readings
Duplicate ingestion of one event	Deduplicate using a verified event identity	Removes repetition only when identity supports it

Taking the first value is not evidence that 18 is correct. Taking the largest is not a neutral cleanup operation. Even averaging can be wrong if the two values represent different measurement times or require different weights.

A strong answer also notices the opposite failure. If id_cols is omitted, unrelated columns such as replicate or ingestion timestamp can become part of the default identity. The result may contain many rows where you expected one. Making keys explicit helps you explain both unwanted merging and unwanted separation.

How do you preserve missingness and schema meaning?

Consider a smaller original fixture:

cells <- tibble(
  site = c("S1", "S1", "S2"),
  metric = c("temp", "count", "temp"),
  value = c(NA_real_, 0, 21)
)
filled <- cells |>
  pivot_wider(id_cols = site, names_from = metric,
              values_from = value, values_fill = 0)

There are three different facts. S1/temp has an explicit missing measurement. S1/count is an observed zero. S2/count has no input row. Official pivot behavior: values_fill supplies values for structurally absent output cells; it does not turn every existing explicit NA into that fill value. Our expected ledger keeps S1/temp missing, keeps S1/count zero and fills the absent S2/count with zero.

The visual similarity of the two count cells does not make their evidence identical. Our recommendation is to preserve a presence indicator when downstream logic needs that distinction. A reported zero count and a count synthesized because a row was absent may have different meanings.

If absence means “not measured,” filling it with zero is inappropriate. If absence means “no transactions after a complete extraction,” zero may be justified. State the data-collection assumption before choosing the fill rule. A reshape option does not prove an extraction was complete.

Official missing-value control: values_drop_na in pivot_longer can turn explicit missing values into implicit ones. In the first .value fixture, the S2/V2 observation has a nonmissing status; explain the behavior across the generated measures rather than promising that every missing temperature removes a row. Avoid dropping missing observations merely to make counts tidy.

Column expansion also needs a declared domain. Official options: expand and pivot_wider's names_expand can make absent combinations or unused factor levels explicit. Our recommendation is to keep a stable reporting schema when the domain is known, while checking the possible expansion size. A large Cartesian domain can create mostly empty cells and consume memory.

Generated column names and data keys are separate concerns. names_repair addresses invalid or colliding column labels. It does not decide whether two source readings belong to one output observation. If two business labels collapse to the same sanitized name, preserve their mapping or revise the naming scheme; a numeric suffix alone does not explain the underlying categories.

What do nest and unnest do to row counts?

Official semantics: nest creates list columns of data frames and can group by named outer columns. In our four-observation site/visit fixture, this retains two outer site rows and stores two visit rows inside each site:

nested <- long |> nest(.by = site)
restored <- nested |> unnest(data)

Before accepting the round trip, compare the reconstructed observations by keys and values, with an explicit policy for order and missing-value equality. Four rows alone are insufficient: losing a status and duplicating another row could preserve the same count.

Empty children expose a different contract. Suppose an outer table includes site S3 with an empty child data frame. Official unnest behavior: unnest drops size-zero children by default; keep_empty = TRUE retains their parent with missing child values. Our recommendation depends on the question: an observation table may exclude empty sites, while a site-coverage report should retain them.

Record the original child size before expanding if you need to distinguish “no visits” from “a visit whose fields are missing.” Both can appear as missing values in the flattened output, but they describe different source records.

Paired list columns create a useful interview trap:

paired <- tibble(
  site = "S1",
  visit = list(c("V1", "V2")),
  temp = list(c(18, 22))
)
aligned <- paired |> unnest_longer(c(visit, temp))
crossed <- paired |> unnest_longer(visit) |>
  unnest_longer(temp)

Official rectangling behavior: unnest_longer expands selected list columns together to their common size. For these equal-length vectors, the intended aligned pairs are V1/18 and V2/22: two rows. Separate expansion yields V1/18, V1/22, V2/18 and V2/22: four rows.

Our cardinality check distinguishes the two-result contract from the four-result Cartesian product. It also tests an empty parent. Those Python checks establish the arithmetic of the constructed example; the R snippets still need execution in a tidyr environment before use in a real pipeline.

If the lists have different lengths, first ask whether recycling is meaningful. Do not assume position pairs independent events correctly. When each child has its own identifier, join on that identifier inside the nested data rather than inventing alignment from list position.

How do you test a reshape under interview constraints?

Use a small fixture that makes the wrong solution visibly different. For this article, the critical examples are two values competing for one cell, an explicit missing value beside an absent cell, and two aligned child lists. Add a preserved observed zero so that careless filtering cannot pass unnoticed.

Our recommended checks cover three levels. First, validate the input schema and intended keys. Second, check output names, types and cardinality. Third, compare actual values and missingness provenance. A successful function call proves only that the library accepted the inputs.

For reversible transformations, attempt a round trip with explicit column ordering and compare sorted keyed records. For aggregating pivots, expect the round trip to lose information: test the agreed statistic against an independent calculation instead. The mean of 18 and 22 is 20, but that arithmetic does not justify choosing a mean.

Finish by explaining cost. A long representation can scale with the number of selected name combinations; a wide representation can scale with rows times generated columns. Nested data can preserve groups compactly, yet expanding independent children can multiply their sizes. Estimate the expansion before materializing it, then state what your test did and did not establish.

Sources and Further Reading

• tidyr: pivot_longer: Names, .value and Missing Values
• tidyr: pivot_wider: Keys, Cell Aggregation and Fill
• tidyr: expand: Explicit Combinations and Factor Levels
• tidyr: nest: Grouped Data Frames
• tidyr: unnest: Empty Children and Expansion
• tidyr: unnest_longer: Aligned List Columns

Research checked October 5, 2026 against tidyr 1.3.2 documentation. Fixtures are original preparation material. No candidate interviews or real data systems were observed.