tidyr Pivot Exercises in R: 18 pivot_longer & pivot_wider Problems

Explanation: USArrests is a base R data.frame with state names stored as rownames, not a real column. rownames_to_column() lifts them into a state column so pivot_longer() can keep them as identifier columns. The selector cols = -state means "pivot everything except state", which is the idiomatic way to say "all the value columns". The result has 50 states x 4 crimes = 200 rows. A common mistake is forgetting the rownames step: tidyverse verbs silently drop rownames after the first manipulation.

Explanation: The colon selector Sepal.Length:Petal.Width matches a contiguous range of columns by position. Alternatives that produce identical results are cols = -Species and cols = where(is.numeric). After the pivot, Species is automatically preserved as the identifier since it was not in cols. This is the canonical first step before plotting all four measurements together with ggplot2 + facet_wrap(~ measurement).

Explanation: Column order in the names_to output column matches the order columns appeared in the original tibble, not alphabetical. That is exactly what you want so plots stay in calendar order without an explicit factor level call. If you needed a factor with month order locked in, you would pipe into mutate(month = factor(month, levels = c("jan", "feb", "mar"))) next. Three regions x three months equals nine rows, which is a good sanity check before any downstream join.

Explanation: This is the classic summarise-then-pivot pattern that produces a wide report table. names_from is the column whose values become the new column NAMES, and values_from is the column whose values fill the cells. The order of L, M, H is preserved because tension is an ordered factor; if it were a plain character vector you would get alphabetical H, L, M. Use .groups = "drop" to avoid getting a tibble that is silently still grouped by wool, which would confuse later joins.

Explanation: Day numbers are integers, so the new column names are stringified integers wrapped in backticks. Cells that have no matching (Month, Day) combination get filled with NA by default. You could replace those with zero by passing values_fill = 0 to pivot_wider(). The select() step is not strictly necessary, but dropping the other measurement columns (Solar.R, Wind, Temp) makes the pivot's intent obvious and avoids accidentally creating a list-column.

Explanation: names_prefix = "v" glues the literal string v to each value of visit before turning it into a column name. Without it, the columns would be 1, 2, 3, which forces every downstream reference to use backticks. The implicit missings for P002 (visits 2 and 3) and P003 (visit 3) become explicit NA cells once you pivot wider. This shape is what most BI tools and Excel exports expect from clinical data.

Explanation: Passing a length-2 character vector to names_to plus names_sep tells tidyr to split each column name on the first underscore and produce that many new columns. The order of pieces in names_to must match the order in the column name. If the separator is variable or appears multiple times, switch to names_pattern with a regex (see the next exercise). After pivoting, a downstream pivot_wider(names_from = time, values_from = score) would give the pre/post difference column you usually want for paired tests.

Explanation: names_pattern uses a Perl-compatible regex where each capture group (...) becomes one of the new columns named by names_to. The pattern (Q\d)_(\d{4}) captures the quarter token and a four-digit year. Why not names_sep = "_"? It would also work here, but names_pattern is the right tool whenever you need to drop or transform pieces of the column name. names_transform runs after capture so the year arrives as integer rather than character, which matters for downstream arithmetic like year - lag(year).

Explanation: The .value sentinel tells tidyr "this captured piece of the column name should not become a new column of its own. Use it as the destination column name." After splitting on _, the first piece (height or weight) names which output column the value lands in, and the second piece (2020 or 2021) becomes the new year column. Without .value you would get a long-form table with height and weight mixed in one value column, which would then need a follow-up pivot_wider(). This pattern is the cleanest way to handle paired columns in panel data.

Explanation: Passing a character vector to names_from makes pivot_wider() combine its values into the new column names. By default, the separator is _ and the order matches names_from. names_glue overrides that with a glue template, letting you add prefixes (rev_) or reorder the pieces freely. This is invaluable when downstream consumers expect names like Q1_FY2024 or metric.store.year. The same trick works with names_glue on pivot_longer() when you need to construct synthetic identifiers in the long output.

Explanation: Without values_fn, pivot_wider() warns when it finds duplicate row-column combinations and silently stuffs the values into a list-column. Setting values_fn = mean tells it how to collapse those duplicates into a single number. Other useful choices: sum for totals, length for counts, ~ paste(.x, collapse = ", ") for string concatenation. The lesson is to either pre-summarise with group_by() |> summarise() OR use values_fn inline, but never silently ignore the duplicate-keys warning.

Explanation: When values_from is a character vector of length greater than one, pivot_wider() produces one column family per element, prefixing each new column with the source name. So you get min_temp_Q1, min_temp_Q2, ..., and the same for max_temp. This avoids two separate pivots followed by a join. The pre-summarise step is necessary because there are three rows per quarter and you want the quarterly mean. If you only had one row per (station, quarter) you could skip the summarise.

Explanation: When you need to compute differences or ratios between rows of a long table, the pattern is almost always pivot wider, compute, then drop or pivot longer again. Subtracting rev_2023 from rev_2024 across two columns is far cleaner than lag() ordering tricks in long form. names_prefix = "rev_" keeps the new column names valid (a leading digit needs backticks). For more than two periods, mutate(across(...)) or a pivot_longer() follow-up scales better than enumerating each year by hand.

Explanation: Round tripping is the cheapest unit test for any pivot logic: if longer |> wider does not return the original, you have either an aggregation hiding duplicates, a column-name collision, or missing identifier columns. The row_id is the key that makes the wide pivot unambiguous; without it, identical flowers would collide and pivot_wider() would silently create list-columns. Use this pattern in production ETL: pivot, transform, pivot back, and assert equality on a control subset before shipping the new shape.

Explanation: This is a very common workflow: group_by() |> summarise() |> pivot_wider() produces a stakeholder ready cross tabulation. The order of group_by() keys affects the row ordering but not column ordering. Note how OJ vs VC differs sharply at low doses but converges at dose 2.0, which is the headline insight any analyst would extract from this layout. The same data in long form needs eyeballing six rows to spot; the wide form makes the pattern obvious at a glance.

Explanation: names_repair accepts "minimal" (warn only), "unique" (append ...n suffixes), "universal" (also make names syntactic), or a user function. Once the values collide, you need a way to spread them out, which is what values_fn = list plus unnest_wider() does. In real code the better fix is upstream: include a pos or sequence index in names_from so each output column is unique by construction. Treat names_repair warnings as a smell, not a solution.

Explanation: Without values_drop_na, the long table would have 8 rows including three NA answers. values_drop_na = TRUE skips those during the pivot itself, which is faster and clearer than a follow-up filter(!is.na(answer)). Use it whenever an NA in the wide form means "no observation" rather than "observed as missing". For survey data where NA carries meaning (refused to answer vs not asked) you should preserve them and disambiguate downstream instead.

Explanation: Counting NA cells across a fixed set of columns is the prototypical use of rowwise() + c_across(). The alternative is to pivot back to long form with values_drop_na = FALSE and count NA per patient with group_by() |> summarise(missed = sum(is.na(systolic))), which is faster on big data but more verbose. A modern equivalent uses rowSums(is.na(across(v1:v3))) inside mutate(), which is vectorized and avoids rowwise() entirely. Either pattern is the standard data quality probe after a pivot_wider() that introduces implicit missings.