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. This is lesson 2 of 4. Any 3 lessons of this learning path are free to read in full — after that, CoddyKit PRO unlocks every lesson, plus hands-on practice with a built-in code editor and a 24/7 AI tutor. It is part of the R Academy learning path, and your progress syncs across the web and the CoddyKit app. The R Academy course includes 4 lessons in total.
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 columnsid_colsspecifies which columns identify each rowvalues_fill = 0(or any scalar) fills missing combinationsnames_prefixprepends a string to avoid numeric or ambiguous column namesvalues_fnresolves duplicate cell values with an aggregation function- Pass a vector to
values_fromto 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)
Learn R with an AI tutor — free
Write and run real code in your browser, get instant help from a 24/7 AI tutor, and pick up where you left off on the web or in the app.
- Courses
- 43
- Lessons
- 159
Frequently Asked Questions
Is the “Long to Wide with pivot_wider()” lesson free?
Yes — any 3 lessons of the R Academy learning path, including “Long to Wide with pivot_wider()”, are free to read in full here on the web. After that, CoddyKit PRO unlocks every lesson, plus interactive practice with a built-in code editor and a 24/7 AI tutor. The R Academy course includes 4 lessons in total.
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, so you can start here or from the beginning and move at your own pace. This is lesson 2 of 4.
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
- Wide to Long with pivot_longer()
- Long to Wide with pivot_wider()
- separate() and unite() for String Columns
- Nesting and Unnesting Data Frames