0Pricing
R Academy · Lesson

Long to Wide with pivot_wider()

Spread key-value pairs back into wide format tables.

Long to Wide with pivot_wider() is a free R Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the R Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

When to Go Wide

While long format suits analysis, wide format is often needed for reports, spreadsheets, and certain algorithms (e.g., correlation matrices). pivot_wider() is the inverse of pivot_longer() — it spreads values from a column into multiple new columns.

library(tidyr)

# Long format: one row per student-quarter combination
long_df <- data.frame(
  student = c('Alice','Alice','Alice','Bob','Bob','Bob'),
  quarter = c('Q1','Q2','Q3','Q1','Q2','Q3'),
  score = c(85, 88, 92, 90, 85, 88)
)

print(long_df)
cat('\n', nrow(long_df), 'rows x', ncol(long_df), 'cols')

pivot_wider() Basics

pivot_wider(df, names_from, values_from) converts long to wide format. names_from specifies the column whose values become new column names; values_from specifies the column whose values fill those new columns.

library(tidyr)

long_df <- data.frame(
  student = c('Alice','Alice','Alice','Bob','Bob','Bob'),
  quarter = c('Q1','Q2','Q3','Q1','Q2','Q3'),
  score = c(85, 88, 92, 90, 85, 88)
)

wide_df <- pivot_wider(
  long_df,
  names_from = quarter,
  values_from = score
)

print(wide_df)

id_cols — Identifying Row Keys

id_cols specifies which columns uniquely identify each row in the wide output. By default, dplyr uses all columns not specified in names_from or values_from. Explicitly setting it prevents unexpected grouping.

library(tidyr)

df <- data.frame(
  year = c(2023,2023,2024,2024),
  region = c('East','West','East','West'),
  quarter = c('Q1','Q1','Q1','Q1'),
  revenue = c(100, 200, 120, 220)
)

pivot_wider(
  df,
  id_cols = c(year, quarter),
  names_from = region,
  values_from = revenue
)

values_fill — Handling Missing Combinations

When some combinations of id_cols and names_from don't exist, the resulting cell would be NA. Use values_fill to replace those missing values with a default (e.g., 0 for counts or sums).

library(tidyr)

df <- data.frame(
  product = c('A','A','B'),
  channel = c('online','store','online'),
  sales = c(100, 150, 200)
)

# 'B' has no 'store' data — fill with 0
pivot_wider(
  df,
  names_from = channel,
  values_from = sales,
  values_fill = 0
)

names_prefix — Adding Prefixes

Use names_prefix to prepend a string to all new column names. This avoids issues when the values in names_from are numbers or when you want to make column names more descriptive.

library(tidyr)

df <- data.frame(
  student = c('Alice','Alice','Bob','Bob'),
  quarter = c(1, 2, 1, 2),
  score = c(85, 90, 78, 82)
)

# Without prefix: columns named '1' and '2' (problematic)
# With prefix: q1, q2
pivot_wider(
  df,
  names_from = quarter,
  values_from = score,
  names_prefix = 'q'
)

Multiple values_from Columns

You can pivot multiple value columns at once by passing a vector to values_from. The resulting columns are named as value_name combining the value column name and the category.

library(tidyr)

df <- data.frame(
  region = c('East','East','West','West'),
  period = c('Q1','Q2','Q1','Q2'),
  revenue = c(100, 120, 200, 210),
  units = c(10, 12, 20, 22)
)

pivot_wider(
  df,
  names_from = period,
  values_from = c(revenue, units)
)

Handling Duplicates with values_fn

If there are multiple values for the same id + name combination, pivot_wider doesn't know which to use and returns a list-column with a warning. Use values_fn to resolve this by specifying an aggregation function.

library(tidyr)

# Duplicate rows: Alice has two Q1 scores
df <- data.frame(
  student = c('Alice','Alice','Alice','Bob','Bob'),
  quarter = c('Q1','Q1','Q2','Q1','Q2'),
  score = c(85, 87, 90, 78, 82)
)

# Resolve duplicates by taking the mean
pivot_wider(
  df,
  names_from = quarter,
  values_from = score,
  values_fn = mean
)

values_fn with a Named List

Pass a named list to values_fn to apply different aggregation functions to different value columns simultaneously. This is powerful when pivoting multiple value columns that need different summary methods.

library(tidyr)

df <- data.frame(
  region = c('East','East','West','West'),
  period = c('Q1','Q1','Q1','Q1'),
  revenue = c(100, 120, 200, 210),
  orders = c(10, 12, 20, 22)
)

pivot_wider(
  df,
  names_from = period,
  values_from = c(revenue, orders),
  values_fn = list(revenue = sum, orders = sum)
)

Creating Cross-Tabulations

A cross-tabulation (contingency table) in data frame form is easily created with pivot_wider() after counting with dplyr::count(). This combines the power of both packages for frequency analysis.

library(tidyr)
library(dplyr)

survey <- data.frame(
  dept = c('HR','HR','Eng','Eng','HR','Eng'),
  response = c('Yes','No','Yes','Yes','Yes','No')
)

survey %>%
  count(dept, response) %>%
  pivot_wider(
    names_from = response,
    values_from = n,
    values_fill = 0
  )

Round-Trip: Long -> Wide -> Long

Understanding the inverse relationship between pivot_longer() and pivot_wider() helps catch errors. A round-trip (long->wide->long) should return the original data (modulo row order and type conversions).

library(tidyr)

original_long <- data.frame(
  id = c(1,1,2,2),
  key = c('a','b','a','b'),
  value = c(10, 20, 30, 40)
)

# Go wide
wide <- pivot_wider(original_long, names_from=key, values_from=value)
print(wide)

# Go long again
pivot_longer(wide, -id, names_to='key', values_to='value')

Practical: Score Report

A typical use case: take a long table of test scores and reshape it into a student-by-subject report table. Combine with dplyr::arrange() and rename() to produce a clean, presentation-ready output.

library(tidyr)
library(dplyr)

scores <- data.frame(
  student = c('Alice','Alice','Bob','Bob','Carol','Carol'),
  subject = c('Math','Science','Math','Science','Math','Science'),
  score = c(92, 88, 85, 91, 79, 83)
)

scores %>%
  pivot_wider(names_from=subject, values_from=score) %>%
  mutate(average = round((Math + Science) / 2, 1)) %>%
  arrange(desc(average))

Quick Check

What happens when you call pivot_wider() and there are multiple values for the same id + name combination?

Recap: pivot_wider()

Key takeaways for long-to-wide conversion:

  • pivot_wider(names_from, values_from) spreads a key column into multiple new columns
  • id_cols specifies which columns identify each row
  • values_fill = 0 (or any scalar) fills missing combinations
  • names_prefix prepends a string to avoid numeric or ambiguous column names
  • values_fn resolves duplicate cell values with an aggregation function
  • Pass a vector to values_from to pivot multiple value columns simultaneously
  • Use after count() to build cross-tabulations efficiently
library(tidyr)
library(dplyr)

data.frame(
  region = c('East','East','West','West'),
  metric = c('revenue','units','revenue','units'),
  value = c(100, 10, 200, 20)
) %>%
  pivot_wider(
    names_from = metric,
    values_from = value,
    values_fill = 0
  ) %>%
  mutate(revenue_per_unit = revenue / units)

Frequently asked questions

Is the “Long to Wide with pivot_wider()” lesson free?

Yes — the full text of “Long to Wide with pivot_wider()” is free to read here on the web, and the R Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the R Academy course, upgrade to CoddyKit PRO.

What will I learn in “Long to Wide with pivot_wider()”?

Spread key-value pairs back into wide format tables. You practise R Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start R Academy?

No prior experience is required. R Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Long to Wide with pivot_wider()” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this R Academy lesson?

Yes. Every R Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Wide to Long with pivot_longer()
  2. Long to Wide with pivot_wider()
  3. separate() and unite() for String Columns
  4. Nesting and Unnesting Data Frames
← Back to R Academy