pivot_longer() & pivot_wider() — Convert between wide and long formats (pivot_longer/pivot_wider) (intro) - R Programming

SECTION 1

Historical Context & Motivation

Data rarely arrives in the exact shape you need for analysis. Since the earliest days of statistical computing, practitioners have wrestled with the distinction between wide format — where each variable occupies its own column — and long format — where variable names are stacked into a single key column and their values into a corresponding value column. The R ecosystem has gone through several generations of reshaping tools, each refining the conceptual vocabulary and API ergonomics that data scientists rely on every day.

2004 • reshape / reshape2 by Hadley Wickham

The melt() and cast() functions introduced a principled melt-then-cast workflow. While powerful, the naming conventions and formula interface were confusing for newcomers.

2014 • tidyr v0.1 — gather() and spread()

Hadley Wickham released tidyr as part of the tidyverse. The gather() and spread() verbs simplified reshaping, but their argument names still tripped up users.

2019 • tidyr v1.0 — pivot_longer() and pivot_wider()

The pivot_longer() and pivot_wider() functions replaced gather/spread with a more explicit, spec-based API. Argument names like names_to and values_from made intent clearer and unlocked multi-column pivots.

2023 • Tidy Data as Industry Standard

The concept of tidy data — each variable is a column, each observation is a row, each value is a cell — is now the default assumption across R packages such as ggplot2, dplyr, and the broader tidyverse ecosystem.

The core question that motivated these tools remains: given a data frame whose shape does not match the structure your analysis expects, how do you mechanically and reproducibly transform its layout without altering the underlying data? That is precisely the role of pivot_longer() and pivot_wider().

SECTION 2

Core Principles & Definitions

Before diving into function signatures, it is important to internalize a few foundational ideas. The distinction between wide and long formats is not about which is "better" but about which is appropriate for the task at hand. A wide table may be ideal for human readability or for certain matrix operations, while a long table is typically required for grouped summaries, faceted plots, and tidy model specifications.

Tidy Data Principle

Each variable forms a column, each observation forms a row, and each value occupies exactly one cell. pivot_longer() is the primary tool for achieving this layout from a wide source.

Wide Format

A data frame where repeated measurements or categories are spread across multiple columns. Example: one column per year. Compact for display, but awkward for ggplot2 aesthetics that expect a single column for color or group.

Long Format

A data frame where repeated measurements are stacked into a key-value pair of columns. More rows, fewer columns. This representation maps directly onto the grammar of graphics and grouped dplyr verbs.

Reversibility

pivot_longer() and pivot_wider() are conceptual inverses. Applying one and then the other (with matching arguments) should return you to the original shape, preserving all information.

Column Selection via tidyselect

Both functions accept tidyselect helpers like starts_with(), matches(), and range notation col_a:col_z to specify which columns to pivot.

✦

KEY TAKEAWAY

Think of a wide table like a spreadsheet designed for a human reader — information is arranged side by side for quick scanning. Think of a long table like a database log — each fact is its own row, ready for programmatic grouping and filtering. pivot_longer() converts the spreadsheet view into the log view; pivot_wider() does the reverse. Neither destroys data — they only rearrange it.

SECTION 3

Visual Explanation — Reshaping in Action

The following diagram shows how a wide-format data frame is transformed into a long-format data frame via pivot_longer(), and how the reverse operation pivot_wider() reconstructs the wide layout. The key insight is that column headers in the wide table become cell values in the long table, and vice versa.

The wide table (left) has one column per year. After pivot_longer(), column headers become cell values in the year column, and the numeric values are stacked into a value column. The id column repeats to maintain associations.

Notice that the total number of data cells is preserved: 3 rows × 2 value columns = 6 values in the wide table, and 6 rows × 1 value column = 6 values in the long table. The information content is identical; only the geometric arrangement has changed. This observation is critical because it means reshaping is a lossless, invertible operation — assuming you track the right key columns.

SECTION 4

How pivot_longer() and pivot_wider() Work

pivot_longer() — From Wide to Long

The pivot_longer() function takes a set of columns that represent values encoded in column names and collapses them into two new columns: one holding the former column names (controlled by names_to) and one holding the cell values (controlled by values_to). Every column not selected for pivoting acts as an identifier and is duplicated as needed.

PIVOT_LONGER SIGNATURE

pivot_longer(data, cols, names_to = "name", values_to = "value", ...)

data — the input tibble or data frame. cols — tidyselect expression for columns to lengthen. names_to — string name for the new key column. values_to — string name for the new values column.

pivot_wider() — From Long to Wide

The pivot_wider() function performs the inverse: it spreads the values of a key column into new column headers and populates each cell with the corresponding value column. The names_from argument specifies which column supplies the new header names, and values_from specifies which column supplies the cell values. When the combination of identifier columns is not unique, pivot_wider() will produce list-columns or require an aggregation function via values_fn.

PIVOT_WIDER SIGNATURE

pivot_wider(data, names_from, values_from, values_fill = NA, ...)

names_from — column whose unique values become new column headers. values_from — column whose values fill the new cells. values_fill — value to use when a combination has no observation (default NA).

Row Count Relationship

ROW MULTIPLICATION (PIVOT_LONGER)

n_rows_long = n_rows_wide × k

where k is the number of columns being pivoted. In our example, k = 2 ( $yr_2021$ , $yr_2022$ ), so 3 × 2 = 6 rows.

💡

Handling NAs

When pivoting wider, missing combinations will produce NA values by default. Use values_fill = list(value = 0) to substitute a specific default. When pivoting longer, you can drop rows that would be NA by setting values_drop_na = TRUE.

SECTION 5

Detailed Argument Breakdown & Column Selection

The power of the pivot functions lies in their rich set of optional arguments that handle complex real-world scenarios — column names that encode multiple variables, type coercion, and aggregation. The diagram below illustrates how the cols argument in pivot_longer() selects which columns to reshape and how the remaining columns are preserved as identifiers.

Three common column selection strategies are shown at top. Below, names_sep and names_pattern demonstrate how to extract multiple variables encoded in a single column name.

Argument	Used In	Purpose
`cols`	`pivot_longer()`	Selects columns to pivot into rows using tidyselect
`names_to`	`pivot_longer()`	Name(s) for the new column(s) created from former column names
`values_to`	`pivot_longer()`	Name for the new column holding cell values
`names_from`	`pivot_wider()`	Column whose unique values become new column headers
`values_from`	`pivot_wider()`	Column whose values populate the new cells
`values_fill`	`pivot_wider()`	Default value for missing combinations (avoids NAs)
`names_sep`	Both	Character to split/paste column names when multiple variables are encoded
`names_pattern`	`pivot_longer()`	Regex with capture groups to extract structured data from column names

Key arguments for pivot_longer() and pivot_wider()

SECTION 6

Worked Example — Student Exam Scores

Suppose you have a data frame scores_wide containing student exam scores across three subjects. Each subject occupies its own column. Your goal is to create a faceted ggplot2 visualization comparing score distributions, which requires a long format with a single subject column and a single score column.

Reshaping Student Scores from Wide to Long

1
Step 1 — Define the Wide Data Frame

Start with a tibble in wide format. Each row is a student; columns math, physics, and cs hold exam scores. scores_wide <- tibble( student = c("Alice", "Bob", "Carol"), math = c(88, 72, 95), physics = c(76, 81, 89), cs = c(92, 68, 97) )

A 3 × 4 tibble (student + 3 subject columns)
2
Step 2 — Apply pivot_longer()

Identify which columns to pivot: all columns except student. Name the new key column "subject" and the new value column "score". scores_long <- scores_wide |> pivot_longer( cols = math:cs, names_to = "subject", values_to = "score" )

A 9 × 3 tibble: 3 students × 3 subjects = 9 rows
3
Step 3 — Inspect the Result

The output has columns student, subject, and score. Alice now has three rows — one per subject — each with her corresponding score. This long format maps directly onto ggplot aesthetics: aes(x = subject, y = score, fill = student).

# A tibble: 9 × 3 student subject score <chr> <chr> <dbl> 1 Alice math 88 2 Alice physics 76 3 Alice cs 92 4 Bob math 72 ...
4
Step 4 — Reverse with pivot_wider()

To recover the original wide format, apply pivot_wider() with names_from = subject and values_from = score. scores_wide_again <- scores_long |> pivot_wider( names_from = subject, values_from = score )

Returns the original 3 × 4 tibble, confirming reversibility.

SECTION 7

Strengths, Limitations & Legacy Comparisons

While pivot_longer() and pivot_wider() are the recommended tidyr functions, it is useful to understand how they compare to their predecessors and to base R alternatives. The following table summarizes key differences across the four main reshaping approaches in R.

Feature	pivot_longer / pivot_wider	gather / spread	reshape2 melt / dcast
Status	Current, actively maintained	Superseded (still works)	Retired, not recommended
Multi-column pivots	Native support via names_sep, names_pattern	Not supported; requires manual workarounds	Limited; formula interface is awkward
Argument clarity	names_to, values_to, names_from, values_from — intent is self-documenting	key, value — generic and easily confused	variable, value, id.vars — workable but dated
Type coercion	names_transform and values_transform for fine-grained control	convert = TRUE for simple cases	Manual post-processing
Missing value handling	values_drop_na, values_fill	na.rm, fill	na.rm in melt; fill.value in dcast

Comparison of R reshaping approaches

🔄

WHEN TO USE WHICH FORMAT

Use long format when feeding data into ggplot2, running grouped summaries with dplyr, or fitting models that expect a response and predictor structure. Use wide format when presenting human-readable summary tables, performing correlation matrices, or doing direct element-wise operations across columns. Think of it like choosing between row-major and column-major storage in a matrix library — neither is inherently superior; the optimal choice depends on your access pattern.

SECTION 8

Connection to Advanced Reshaping & Tidy Data Theory

The pivot functions introduced here form the foundation for more sophisticated data-wrangling patterns. In production-grade R code, you will often encounter scenarios where column names encode multiple variables simultaneously — for instance, bp_sys_visit1, bp_dia_visit2. These demand the advanced names_sep and names_pattern features, or even the pivot spec API (build_longer_spec() / build_wider_spec()) for full programmatic control.

Concept	Intro Level (this lesson)	Advanced Level
Column selection	Single tidyselect expression: name ranges, helpers, negation	Programmatic selection using external vectors, across() integration
Name encoding	One variable extracted from column name	Multiple variables via names_sep, names_pattern, or pivot specs
Aggregation	Assumes unique combinations; no aggregation needed	values_fn argument handles duplicates with arbitrary summary functions
Multiple value cols	Single values_from column	Vector of values_from columns; produces compound-named columns
Type safety	Rely on defaults; occasional character coercion	names_transform / values_transform with explicit parsing functions

Intro vs. advanced usage of pivot functions

Looking forward, the tidy data philosophy connects directly to relational database normal forms — specifically third normal form (3NF). When you pivot longer, you are essentially normalizing a denormalized table. When you pivot wider, you are denormalizing for performance or readability. Understanding this parallel between R data wrangling and database theory deepens your intuition for when and why reshaping is necessary across the entire data engineering stack.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain in your own words why ggplot2's aes(color = group) mapping requires data in long format rather than wide format. What role does pivot_longer() play in enabling this?

Show answer

ggplot2 aesthetics map a single column to a visual property. If groups are spread across multiple columns (wide format), there is no single column for ggplot2 to map to color. By calling pivot_longer(), you collapse those column headers into a key column (e.g., group) and stack the values into a single value column. Now aes(color = group) can map each unique group name to a distinct color. This is a direct consequence of the tidy data principle: each aesthetic corresponds to exactly one variable (column).

Question 2: BASIC CALCULATION

You have a tibble with 5 rows and columns: country, gdp_2020, gdp_2021, gdp_2022, gdp_2023. Write the pivot_longer() call to produce columns country, year, and gdp. How many rows will the result have?

Show answer

The call is: df |> pivot_longer(cols = starts_with("gdp_"), names_to = "year", values_to = "gdp"). You are pivoting 4 columns (

gdp_2020

through

gdp_2023

), and the original data has 5 rows, so the result will have 5 × 4 = 20 rows and 3 columns. The year column will contain the strings "gdp_2020", "gdp_2021", etc. To extract just the numeric year, you could add names_prefix = "gdp_" to strip the prefix and then use names_transform = list(year = as.integer).

Question 3: INTERMEDIATE

Given the long-format tibble below, write a pivot_wider() call that produces one row per patient and one column per measurement type, filling missing combinations with 0:

# patient | measurement | value
# A       | bp          | 120
# A       | hr          | 72
# B       | bp          | 130
# B       | temp        | 98.6

Show answer

The call is: df |> pivot_wider(names_from = measurement, values_from = value, values_fill = 0). This produces a 2 × 4 tibble (patient, bp, hr, temp). Patient A will have 0 for the temp column, and patient B will have 0 for the hr column, since those combinations were absent in the original long data. The values_fill = 0 argument replaces the NAs that would otherwise appear. In a clinical context, you would want to consider carefully whether 0 is semantically correct or whether NA better represents a missing measurement.

Question 4: APPLIED

You receive a CSV where sensor readings are stored in columns named temp_sensor1, temp_sensor2, humid_sensor1, humid_sensor2, alongside a timestamp column (10 rows). Write a pivot_longer() call that produces columns timestamp, metric (temp or humid), sensor (sensor1 or sensor2), and reading. State the resulting dimensions.

Show answer

This requires splitting column names into two variables. The call is: df |> pivot_longer(cols = -timestamp, names_to = c("metric", "sensor"), names_sep = "_", values_to = "reading"). The underscore separates "temp" or "humid" from "sensor1" or "sensor2". With 4 value columns and 10 rows, the result will have 10 × 4 = 40 rows and 4 columns (timestamp, metric, sensor, reading). This tidy structure lets you easily filter by metric, group by sensor, or facet a ggplot by both. Note that names_sep = "_" splits on the first underscore encountered, which works here because the metric names (temp, humid) contain no underscores.

Question 5: CRITICAL THINKING

Consider a long-format tibble where each (student, subject) combination has multiple rows because each student took the same exam twice (attempt 1 and attempt 2). If you call pivot_wider(names_from = subject, values_from = score) without additional arguments, what will happen? Propose a solution using values_fn and discuss the trade-offs of different aggregation choices.

Show answer

Without values_fn, pivot_wider will detect that the identifier columns (student, attempt) do not uniquely identify each row and will produce list-columns — each cell will contain a vector of values rather than a scalar. tidyr will emit a warning. To resolve this, you can supply values_fn = list(score = mean) to average across attempts, or values_fn = list(score = max) to keep the best score. Choosing mean gives a representative central tendency but obscures variability; max highlights peak performance but loses information about consistency. A more principled approach might be to first add an attempt column and include it as an id column, preserving all data without aggregation. The choice depends on the downstream analytical question.

SUMMARY

Lesson Summary

Summary

The pivot_longer() function collapses multiple columns into key-value pairs, converting a wide-format data frame into a long-format (tidy) one. Its key arguments are cols (which columns to pivot), names_to (the name of the new key column), and values_to (the name of the new value column). Conversely, pivot_wider() spreads a key column's unique values into new column headers via names_from and fills cells with values_from.

These two functions are conceptual inverses — applying one and then the other recovers the original shape. They supersede the older gather()/spread() and reshape2 melt()/dcast() APIs with a clearer, more powerful interface that supports tidyselect column selection, multi-variable name extraction via names_sep/names_pattern, and missing-value handling through values_fill and values_drop_na. Mastering these verbs is essential for any tidy data workflow in R.