Reshaping data in R and reshaping in R with tidyr, including missing values and duplicate keys.

Reshape Data in R: A Round Trip With tidyr

Turning an R data frame from wide to long changes where the table stores information: in column names or in rows. A long table often makes comparisons across periods easier, while a wide table puts each period in its own column.

For reshaping in R, identify the columns that distinguish observations before moving anything. This example follows two sites and two weeks, including one missing measurement, through both directions.

Reshape in R from wide to long

Install tidyr once with install.packages("tidyr"), then run this example in a fresh session. The input has one row per site. Each week column contains a count; NA means the count is unknown.

library(tidyr)

wide <- data.frame(
  site = c("A", "B"),
  week1 = c(10, 7),
  week2 = c(12, NA_real_)
)

long <- pivot_longer(
  wide,
  cols = c(week1, week2),
  names_to = "week",
  values_to = "visits"
)
long

The result has four rows, shown here in their default order:

site week visits
A week1 10
A week2 12
B week1 7
B week2 NA

The site column remains an identifier. names_to puts the selected column names into week, and values_to puts their cell values into visits. The pair site and week identifies a measurement in this example. The default row order keeps the two weeks from each original row together. If downstream code needs a particular order, sort explicitly rather than relying on incidental input order.

You can reshape data in R this way when column names represent values of the same variable. The selected measurement columns must also be compatible. Select only comparable columns: combining a count with a label, for example, can cause a type error or produce an unsuitable value column. Check units and types before choosing the columns.

Return to wide form

To reshape in R for a report with one column per week, use the long table as input:

back <- pivot_wider(
  long,
  id_cols = site,
  names_from = week,
  values_from = visits
)
back

The result has columns site, week1, and week2, in that order. Its rows are A with 10 and 12, then B with 7 and NA. The default column order follows the first appearance of the week names. id_cols defines one output row per site; names_from supplies column names, and values_from supplies their cells. With other inputs, compare names, types, row order, and values before treating a round trip as equivalent to the original. After the pivot, preserve the ordering explicitly if another process depends on it.

When reshaping data in R, distinguish an explicit NA from a row that never existed. The first pivot retains the explicit missing value by default. Setting values_drop_na = TRUE removes that row, so the long table no longer records whether it was originally present. The wider pivot normally creates NA for an absent combination; use values_fill = 0 only if absence means zero in the data. It does not replace an explicit NA already present in a value cell.

Across multiple records per site, include every identifier needed to distinguish the intended output rows. A date or experiment ID may be essential. Otherwise distinct observations can compete for the same cell. Check the intended key against the source before deciding whether repeated records are errors or legitimate measurements.

The most likely failure is a duplicate site-week pair. pivot_wider() warns that values are not uniquely identified and creates list-columns. Find the offending pairs with long[duplicated(long[c("site", "week")]) | duplicated(long[c("site", "week")], fromLast = TRUE), ]. Correct duplicate records, add a missing identifier, or choose a justified aggregation with values_fn, such as mean for repeated measurements that should be averaged. Inspect the result after the decision; averaging is not a safe default for counts.