tidyr::pivot_longer() / tidyr::pivot_wider()

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

Returns tibble· Updated May 29, 2026· Tidyverse

tidyrreshapetidyversepivotdata-wrangling

pivot_longer() and pivot_wider() are tidyr functions for reshaping data between long and wide formats. They replace the older gather() and spread() functions with a more intuitive interface.

Syntax

pivot_longer(
  data,
  cols,
  names_to = "name",
  names_prefix = NULL,
  names_sep = NULL,
  names_pattern = NULL,
  values_to = "value",
  values_drop_na = FALSE,
  ...
)

pivot_wider(
  data,
  names_from,
  values_from,
  id_cols = NULL,
  names_prefix = "",
  names_sep = "_",
  values_fill = NULL,
  values_drop_na = FALSE
)

Parameters

pivot_longer()

Parameter	Type	Default	Description
`data`	tibble / data.frame	,	Input data frame
`cols`	columns	,	Columns to pivot into longer format (use tidyselect)
`names_to`	string	”name”	Name of the column to store column names
`values_to`	string	”value”	Name of the column to store values
`names_prefix`	string	NULL	Remove prefix from column names
`values_drop_na`	logical	FALSE	Drop rows where values are NA

pivot_wider()

Parameter	Type	Default	Description
`data`	tibble / data.frame	—	Input data frame
`names_from`	column	—	Column containing names for new columns
`values_from`	column	—	Column containing values for new columns
`values_fill`	value	NULL	Value to use for missing combinations
`values_drop_na`	logical	FALSE	Drop rows where values are NA

Examples

Basic pivot_longer

Convert wide data to long format:

library(tidyr)
library(dplyr)

df <- data.frame(
  id = 1:3,
  score_2022 = c(85, 92, 78),
  score_2023 = c(88, 95, 82),
  score_2024 = c(90, 98, 85)
)

df |> pivot_longer(
  cols = starts_with("score_"),
  names_to = "year",
  names_prefix = "score_",
  values_to = "score"
)

# # A tibble: 9 × 3
#      id year  score
#   <dbl> <chr> <dbl>
# 1     1 2022     85
# 2     1 2023     88
# 3     1 2024     90
# 4     2 2022     92
# 5     2 2023     95
# 6     2 2024     98
# 7     3 2022     78
# 8     3 2023     82
# 9     3 2024     85

pivot_longer() takes the column names that match the cols selection and stacks them into rows, placing the original column names in the names_to column and the corresponding values in the values_to column. The names_prefix argument strips a common prefix so that only the meaningful portion of each column name is retained.

Basic pivot_wider

Convert long data to wide format:

df_long <- data.frame(
  id = c(1, 1, 2, 2),
  year = c("2022", "2023", "2022", "2023"),
  score = c(85, 88, 92, 95)
)

df_long |> pivot_wider(
  names_from = year,
  values_from = score
)

# # A tibble: 2 × 3
#      id X2022 X2023
#   <dbl> <dbl> <dbl>
# 1     1    85    88
# 2     2    92    95

The inverse operation, pivot_wider(), takes a long-format data frame and spreads the values from one column across multiple new columns named after the distinct entries in the names_from column. This restores the wide layout that is often more convenient for presentation and certain types of statistical modeling.

Multiple value columns

Pivot multiple value columns simultaneously:

df <- data.frame(
  id = 1:2,
  treatment_a = c(10, 15),
  treatment_b = c(20, 25),
  control_a = c(8, 12),
  control_b = c(18, 22)
)

df |> pivot_longer(
  cols = -id,
  names_to = c("group", "outcome"),
  names_sep = "_"
)

# # A tibble: 8 × 4
#      id group    outcome
#   <dbl> <chr>      <dbl>
# 1     1 treatment        10
# 2     1 treatment        20
# 3     1 control           8
# 4     1 control          18
# 5     2 treatment        15
# 6     2 treatment        25
# 7     2 control          12
# 8     2 control          22

When column names encode multiple pieces of information separated by a delimiter, the names_sep argument splits them into multiple columns in the output. This is especially useful for experimental data where column names follow a pattern like group_measurement and you want to separate the two components.

Common patterns

Handling missing values with values_fill

Fill missing values when pivoting wider:

df <- data.frame(
  id = c(1, 1, 2),
  metric = c("a", "b", "a"),
  value = c(10, 20, 30)
)

df |> pivot_wider(
  names_from = metric,
  values_from = value,
  values_fill = 0
)

# # A tibble: 2 × 3
#      id     a     b
#   <dbl> <dbl> <dbl>
# 1     1    10    20
# 2     2    30     0

The values_fill argument provides a default value for any cell that would otherwise be NA because a particular combination of row and column identifiers does not exist in the original long data. Setting values_fill = 0 is a common choice for count or numeric data.

Using names_pattern for complex column names

Extract multiple pieces information from column names:

df <- data.frame(
  id = 1:2,
  result_q1_pre = c(85, 90),
  result_q1_post = c(92, 95),
  result_q2_pre = c(78, 82),
  result_q2_post = c(88, 90)
)

df |> pivot_longer(
  cols = -id,
  names_to = c("question", "time"),
  names_pattern = "result_(q\\d+)_(pre|post)"
)

tidyr::pivot_longer() and pivot_wider() in practice

pivot_longer() converts wide data to long format: multiple columns become rows. pivot_longer(df, cols = c(Q1, Q2, Q3, Q4), names_to = "quarter", values_to = "sales") takes four quarter columns and creates one row per quarter per observation, with quarter identifying the source column.

pivot_wider() is the inverse: it spreads a key-value pair across multiple columns. pivot_wider(df, names_from = quarter, values_from = sales) recreates the original wide format. If there are duplicate combinations of the non-pivoted columns and the names_from variable, values_fn controls aggregation.

names_prefix and names_sep in pivot_longer() handle structured column names. pivot_longer(df, cols = starts_with("week_"), names_to = "week", names_prefix = "week_") strips the "week_" prefix from the column names when creating the week column.

pivot_wider() with values_fill replaces NA values created by sparse combinations: pivot_wider(df, values_fill = 0) fills missing cells with 0. This is the equivalent of SQL’s CASE WHEN ... THEN value ELSE 0 END pattern in a cross-tabulation.

These two functions replace the older gather() and spread() functions from the original tidyr API. The new functions are more explicit, handle more edge cases, and have better documentation.

Names and values in pivoting

pivot_longer() and pivot_wider() mirror each other. pivot_longer() takes columns and converts them to rows, gathering the column names into a names column and the values into a values column. pivot_wider() takes two columns — one providing new column names and one providing values — and spreads them into multiple columns.

The naming conventions in pivot operations are explicit: names_to specifies what to call the column that receives the old column names; values_to specifies what to call the column that receives the values. Choosing meaningful names for these columns, rather than accepting the defaults “name” and “value”, makes the resulting data frame self-documenting. For a dataset of temperature measurements across months, naming the columns “month” and “temperature_c” is more informative than the defaults.