How to pivot data from wide to long format in R

Pivoting from wide to long format is one of the most common data transformations in R. This cookbook shows you how to use tidyr::pivot_longer() for this task.

Basic Wide to Long

The simplest case: you have columns that should become rows:

library(tidyr)
library(dplyr)

# Sample wide data: one row per person, columns for each survey wave
wide <- tibble(
  id = c(1, 2, 3),
  name = c("Alice", "Bob", "Carol"),
  wave1 = c(10, 20, 15),
  wave2 = c(12, 22, 18),
  wave3 = c(14, 25, 20)
)

# Pivot to long format
long <- wide %>%
  pivot_longer(
    cols = c(wave1, wave2, wave3),
    names_to = "wave",
    values_to = "score"
  )

# Result: 9 rows (3 people x 3 waves)
long
# # A tibble: 9 × 4
#      id name   wave   score
#   <dbl> <chr>  <chr>  <dbl>
# 1     1 Alice  wave1     10
# 2     1 Alice  wave2     12
# 3     1 Alice  wave3     14
# ...

Selecting Columns

Instead of listing every column, use tidyr selection helpers:

# Select columns by name range
df %>% pivot_longer(cols = wave1:wave3, names_to = "wave", values_to = "score")

# Select by prefix
df %>% pivot_longer(cols = starts_with("wave"), names_to = "wave", values_to = "score")

# Select by suffix  
df %>% pivot_longer(cols = ends_with("score"), names_to = "metric", values_to = "value")

# Select by pattern (regex)
df %>% pivot_longer(cols = matches("^q[0-9]"), names_to = "quarter", values_to = "value")

# Select all columns except ID columns
df %>% pivot_longer(cols = -c(id, name), names_to = "var", values_to = "val")

Multiple Value Columns

When your wide data has multiple value types (e.g., revenue and units for each month):

wide <- tibble(
  id = 1:2,
  rev_jan = c(100, 200),
  rev_feb = c(110, 220),
  units_jan = c(10, 20),
  units_feb = c(12, 24)
)

# Use .value in names_to to create separate columns
long <- wide %>%
  pivot_longer(
    cols = c(rev_jan:units_feb),
    names_to = c(".value", "month"),
    names_sep = "_"
  )

long
# # A tibble: 4 × 4
#      id month   rev units
#   <int> <chr> <dbl> <dbl>
# 1     1 jan     100    10
# 2     1 feb     110    12
# 3     2 jan     200    20
# 4     2 feb     220    24

The .value special token tells pivot_longer to split the column names and use the first part as column names.

Handling Missing Values

After pivoting, you might have NA values:

# Create sample data with some missing values
wide <- tibble(
  id = 1:2,
  q1 = c(10, NA),
  q2 = c(20, 25),
  q3 = c(NA, 30)
)

# Drop rows with missing values
long <- wide %>%
  pivot_longer(cols = q1:q3, names_to = "quarter", values_to = "value") %>%
  drop_na(value)

# Or fill missing values
long <- wide %>%
  pivot_longer(cols = q1:q3, names_to = "quarter", values_to = "value") %>%
  fill(value, .direction = "downup")

Column Names as Values

Sometimes you want the column names themselves as data:

wide <- tibble(
  id = 1:2,
  treatment_a = c("yes", "no"),
  treatment_b = c("no", "yes")
)

# Use names_transform to convert character to factor
long <- wide %>%
  pivot_longer(
    cols = starts_with("treatment"),
    names_to = "treatment",
    values_to = "applied",
    names_transform = list(treatment = factor)
  )

Common Options Reference

Argument	Description	Example
cols	Columns to pivot	cols = c(q1, q2, q3)
names_to	Name for new key column	names_to = "quarter"
values_to	Name for new value column	values_to = "sales"
names_sep	Separator for splitting names	names_sep = "_"
names_prefix	Remove prefix from names	names_prefix = "q_"
values_drop_na	Drop rows with NA values	values_drop_na = TRUE

See Also

• Reshaping Data with tidyr — Full tutorial on tidyr reshaping functions
• tidyr::pivot_wider() — The opposite transformation