pivot_wider() in R: Fix 'Values Are Not Uniquely Identified', Use List-Columns Intentionally, and Know When to Stay Long

Introduction

The most common pivot_wider() problem is not syntax. It is data shape. You ask tidyr to widen a table, and it warns that the values are not uniquely identified so the output will contain list-columns. That message feels cryptic at first, but it is actually describing the problem very precisely: for at least one cell in the wide result, your data provides more than one candidate value.

In other words, the wide table you requested is not well defined yet. Tidyr cannot guess whether you wanted a summary, a list of all values, or an extra identifier that distinguishes repeated observations. Until you make that decision, there is no single correct cell value to place in the widened table.

Once you see pivot_wider() as a data-design step rather than a formatting trick, the warning becomes much easier to resolve.

If You Only Remember Four Rules

• pivot_wider() needs each combination of identifiers and names_from values to map to one value, not many.
• If duplicates are real and should be summarized, use values_fn.
• If duplicates should be preserved, keep list-columns intentionally or add a row number before widening.
• If the data is naturally repeated-measure long data, staying long is often the cleanest answer.

What the Warning Really Means

The tidyr reference explains that values_fn is typically used when the combination of id_cols and names_from does not uniquely identify an observation. That is the core issue. Two or more rows are competing for the same location in the wide result.

Community examples show the same pattern over and over: repeated measurements, duplicate keys introduced earlier in the pipeline, or an implicit identifier that was never made explicit before widening. The warning is not telling you to silence tidyr. It is telling you to decide what one cell should mean.

Option 1: Summarize Duplicates with values_fn

If multiple rows should collapse into one value per cell, aggregation is the right fix. The tidyr docs show values_fn = mean as a standard example. In real work, the summary function might be sum, max, first, or a custom function that matches the business rule.

wide <- long_df |>
  pivot_wider(
    id_cols = customer_id,
    names_from = metric,
    values_from = value,
    values_fn = mean
  )

This is the correct approach when repeated rows are multiple measurements that should become one summary value in the wide table.

Option 2: Keep All Values Intentionally with List-Columns

Sometimes duplicates are not a problem to eliminate. They are the real data. In that case, list-columns are not a failure state. They are an explicit representation that more than one value belongs in that wide cell.

You can preserve that intent with values_fn = list, then decide later whether to unnest, inspect, or summarize those values in a downstream step.

wide_lists <- long_df |>
  pivot_wider(
    id_cols = customer_id,
    names_from = metric,
    values_from = value,
    values_fn = list
  )

This is useful when each customer can genuinely have multiple readings, tags, codes, or time points for the same metric and you do not want to throw any of them away.

Option 3: Add a Sequence Before Widening

If the real intent is to spread repeated occurrences across separate columns, add an explicit within-group row number before you pivot. This is the same conceptual fix as pairing repeated keys before a join: make the repeated observation number visible in the data model.

wide_ranked <- long_df |>
  dplyr::group_by(customer_id, metric) |>
  dplyr::mutate(obs = dplyr::row_number()) |>
  dplyr::ungroup() |>
  pivot_wider(
    id_cols = customer_id,
    names_from = c(metric, obs),
    values_from = value
  )

This is often the right fix when the long data contains repeated attempts, visits, survey responses, or multiple events per key.

Be Explicit About id_cols

Another common source of confusion is relying on the default identifier logic. By default, pivot_wider() treats all remaining columns other than names_from and values_from as identifiers. That can be correct, but it can also preserve distinctions you did not intend. Being explicit about id_cols makes the target grain of the wide table much easier to reason about.

If you expected one row per customer but left a timestamp or status column in the identifiers, you may end up with more rows than expected even before dealing with duplicate cell values.

When You Should Stay Long

Some datasets are simply easier to analyze in long form. Repeated measurements, event logs, panel data, and most grouped visualizations often work better long. If widening forces you into list-columns, dozens of synthetic columns, or awkward names like metric_17, that is a strong signal that long format may already be the right structure.

Wider is not always cleaner. It is only cleaner when each wide column corresponds to a stable variable and each cell has a well-defined single value.

Common Pivot Mistakes

• Trying to widen before deciding what repeated rows mean.
• Using distinct() as a reflex without checking whether the repeated rows carry meaningful differences.
• Assuming pivot_longer() and pivot_wider() are exact inverses even after aggregation or deduplication changes the data.
• Treating list-columns as inherently wrong when they may actually be the honest representation of the data.

Final Takeaway

When pivot_wider() warns that values are not uniquely identified, it is telling you that your requested wide cell still has more than one candidate value. The fix is not “make the warning go away.” The fix is to decide which of the three honest outcomes you want: summarize duplicates, preserve them as lists, or add an identifier so each repeated observation gets its own column. Once that choice is explicit, the pivot stops being mysterious.

FAQ

Does the warning mean my data is broken?

No. It means the requested wide table is ambiguous given the current identifiers. The data may be perfectly valid long-form data with repeated observations.

When should I use values_fn = list?

Use it when multiple values per wide cell are legitimate and you want to preserve them instead of forcing a summary too early.

How do I know whether to add a row number before widening?

If repeated observations have a natural first, second, third, or nth occurrence that you want represented separately in wide form, a sequence column is usually the cleanest solution.