Reshaping Data with tidyr

One of the most common data wrangling challenges is switching between wide and long data formats. Wide data has multiple columns representing different time points or categories, while long data has each observation in its own row. The tidyr package provides two powerful functions for this: pivot_longer() and pivot_wider().

What you’ll learn

This tutorial covers the key concepts and practical techniques for working with Reshaping Data with tidyr. By the end, you will know how to apply the core functions in real data analysis workflows.

Why reshape your data?

Different data formats serve different purposes. Wide format is great for human reading and reporting:

# Wide format - easy to read
wide_data <- data.frame(
  id = c(1, 2, 3),
  name = c("Alice", "Bob", "Carol"),
  score_2023 = c(85, 92, 78),
  score_2024 = c(88, 95, 82),
  score_2025 = c(91, 98, 86)
)
print(wide_data)

Long format is required for most statistical modeling and visualization in R:

# Long format - each row is one observation
long_data <- data.frame(
  id = c(1, 1, 1, 2, 2, 2, 3, 3, 3),
  name = c("Alice", "Alice", "Alice", "Bob", "Bob", "Bob", "Carol", "Carol", "Carol"),
  year = c(2023, 2024, 2025, 2023, 2024, 2025, 2023, 2024, 2025),
  score = c(85, 88, 91, 92, 95, 98, 78, 82, 86)
)
print(long_data)

Most ggplot2 visualizations and dplyr operations work best with long data.

Converting wide to long with pivot_longer()

The pivot_longer() function transforms wide data into long format. You specify which columns to gather and what to name the new columns.

Basic usage

library(tidyr)
library(dplyr)

# Starting with wide data
wide <- data.frame(
  id = 1:3,
  name = c("Alice", "Bob", "Carol"),
  score_2023 = c(85, 92, 78),
  score_2024 = c(88, 95, 82),
  score_2025 = c(91, 98, 86)
)

# Convert to long format
long <- wide %>%
  pivot_longer(
    cols = starts_with("score_"),
    names_to = "year",
    values_to = "score"
  ) %>%
  mutate(year = parse_number(year))

print(long)

The key arguments:

• cols: Which columns to gather (use starts_with(), contains(), or column names)
• names_to: Name for the new column that holds the old column names
• values_to: Name for the new column that holds the values

Advanced pivot_longer()

You can use regular expressions and more complex patterns:

# Example with multiple value columns
wide2 <- data.frame(
  id = 1:3,
  product = c("Widget A", "Widget B", "Widget C"),
  price_q1 = c(100, 150, 200),
  price_q2 = c(105, 155, 210),
  units_q1 = c(50, 30, 20),
  units_q2 = c(55, 35, 22)
)

long2 <- wide2 %>%
  pivot_longer(
    cols = matches("_(q1|q2)$"),
    names_to = c(".value", "quarter"),
    names_sep = "_"
  )

print(long2)

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

Converting long to wide with pivot_wider()

The pivot_wider() function does the opposite: it takes long data and spreads it into wide format.

Basic usage

# Starting with long data
long <- data.frame(
  id = c(1, 1, 1, 2, 2, 2),
  name = c("Alice", "Alice", "Alice", "Bob", "Bob", "Bob"),
  year = c(2023, 2024, 2025, 2023, 2024, 2025),
  score = c(85, 88, 91, 92, 95, 98)
)

# Convert to wide format
wide <- long %>%
  pivot_wider(
    names_from = year,
    values_from = score,
    names_prefix = "score_"
  )

print(wide)

Key arguments:

• names_from: Column containing the new column names
• values_from: Column containing the values
• names_prefix: Optional prefix for new column names

Handling missing values

When pivoting wider, you might encounter missing values. Use values_fill to specify a default:

long_with_na <- data.frame(
  id = c(1, 1, 2, 2),
  measure = c("height", "weight", "height", "weight"),
  value = c(175, 70, 180, NA)
)

wide_filled <- long_with_na %>%
  pivot_wider(
    names_from = measure,
    values_from = value,
    values_fill = 0  # Fill missing with 0
  )

print(wide_filled)

Practical example: time series analysis

Let’s walk through a real example. Imagine you have sales data in wide format and need to analyze trends:

# Wide sales data
sales_wide <- data.frame(
  product_id = 1:4,
  product_name = c("Widget", "Gadget", "Gizmo", "Thingamajig"),
  jan = c(100, 150, 200, 50),
  feb = c(110, 160, 190, 55),
  mar = c(120, 170, 180, 60),
  apr = c(130, 180, 170, 65)
)

# Convert to long for analysis
sales_long <- sales_wide %>%
  pivot_longer(
    cols = jan:apr,
    names_to = "month",
    values_to = "sales"
  )

# Analyze monthly trends
monthly_summary <- sales_long %>%
  group_by(month) %>%
  summarise(total_sales = sum(sales), .groups = "drop_last")

print(monthly_summary)

Best practices

1. Plan your structure first: Know which format you need before reshaping
2. Use clear column names: After pivoting, clean up the new column names
3. Check for duplicates: pivot_wider() will fail if you have duplicate identifier-value pairs
4. Handle NA values explicitly: Use values_fill or values_drop_na as needed

Wide vs long format

Wide format has one row per observation with multiple value columns, each column is a separate time point or measurement. Long format has one row per observation-variable combination. Long format is required by ggplot2 and most tidyverse functions. pivot_longer() converts wide to long; pivot_wider() converts long to wide.

pivot_longer details

pivot_longer(df, cols = c(q1, q2, q3, q4), names_to = "quarter", values_to = "revenue") converts four columns to two. cols uses tidy-select: cols = starts_with("q") or cols = where(is.numeric). When column names encode multiple variables (e.g., sales_2023, costs_2023), names_sep = "_" and names_to = c("type", "year") split them into two columns.

pivot_wider details

pivot_wider(df, names_from = quarter, values_from = revenue) expands unique values of quarter into columns. If multiple rows match the same key-name combination, provide values_fn = sum to aggregate. names_prefix = "q_" prepends text to column names, preventing names starting with digits. values_fill = 0 replaces NA for missing combinations.

Long and wide data formats

Data comes in two fundamental shapes. Wide format has one row per observation and one column per variable — common in spreadsheets and survey tools. Long format (tidy) has one row per observation-variable combination. Many R analysis and visualization functions expect long format; many reporting and data entry tools use wide format.

pivot_longer() converts wide to long. The cols argument specifies which columns to collapse; names_to names the new column that receives the old column names; values_to names the new column that receives the values. For a survey with columns q1, q2, q3, q4: pivot_longer(df, cols = starts_with("q"), names_to = "question", values_to = "response").

pivot_wider() converts long to wide. id_cols identifies the row identifier; names_from specifies which column provides the new column names; values_from specifies which column provides the values.

Advanced pivot_longer options

names_prefix strips a common prefix from column names before putting them in the names column: pivot_longer(df, cols = starts_with("score_"), names_prefix = "score_", names_to = "period") produces clean values like “2023”, “2024” rather than “score_2023”, “score_2024”.

names_transform converts the names column to a specific type: names_transform = list(period = as.integer) converts string year values to integers in one step.

Multiple key columns from column names: names_sep or names_pattern splits column names into multiple key columns. Columns named "value_2023_jan" and "value_2023_feb" can be split into year and month columns: pivot_longer(df, cols = starts_with("value"), names_to = c("year", "month"), names_sep = "_", names_transform = list(year = as.integer)).

Completing and filling implicit missing values

Tidy data should have explicit missing values rather than implicit ones (missing rows). complete() adds rows for all combinations of variables, filling missing values with NA or a specified default. complete(df, year, region) creates rows for every year-region combination that appears in either column, adding NA for values where that combination did not exist.

fill() fills NA values with the previous (or next) non-NA value within each group. This handles the common survey pattern where a category column is only filled when the category changes, leaving NAs for subsequent rows in the same category. fill(df, category, .direction = "down").

replace_na(df, list(score = 0, label = "unknown")) replaces NA in specific columns with specified values. This is more precise than a global is.na() replacement because you can specify different defaults per column.

Separating and uniting columns

separate(df, col, into = c("part1", "part2"), sep = "-") splits one column into multiple. sep can be a literal string, a regex pattern, or an integer position. extra = "drop" discards extra pieces; extra = "merge" keeps all extra pieces in the last column. fill = "right" fills missing pieces from the right with NA.

separate_wider_delim(), separate_wider_position(), and separate_wider_regex() are the newer replacements with stricter type handling and better error messages.

unite(df, new_col, col1, col2, sep = "_") combines multiple columns into one. remove = FALSE keeps the original columns in addition to the new combined column.

Nesting and unnesting

nest(df, data = c(col1, col2)) packs columns into a list-column. Each row gets a mini data frame. unnest(df, data) expands list-columns back into regular columns.

unnest_wider(df, list_col) expands a list-column where each element is a named list, creating one column per list element. unnest_longer(df, list_col) expands a list-column where each element is a vector, creating one row per vector element.

These tools are essential for working with JSON data where API responses contain nested objects. jsonlite::fromJSON() returns nested lists; unnest_wider() flattens one level at a time.

Why data shape matters

The same data can be stored in many shapes. Wide format puts multiple observations across columns: each row is one subject, and separate columns hold measurements at different time points. Long format puts one observation per row: each row is one measurement, with a column identifying which time point it comes from. Most tidyverse functions expect long format, where each variable occupies exactly one column and each observation occupies exactly one row.

The concepts of pivoting wider and longer come from the observation that shape transformations are symmetric. Data that is in long format can be pivoted wider, and data in wide format can be pivoted longer. The tools for both transformations accept the same kinds of arguments. Understanding one makes understanding the other natural.

Pivoting wider for display

Wide format is often more readable for humans: a table showing quarterly sales by region has regions as columns and is easy to scan across time. Pivoting wider converts long data to display format. The names_from argument specifies the column whose values become new column names. The values_from argument specifies the column whose values fill the new columns. The result has one row per unique combination of the remaining columns.

When multiple rows correspond to one output row — multiple values would fill the same cell — an aggregation function resolves the conflict. Setting values_fn = sum aggregates numerically. Without specifying values_fn when aggregation is needed, pivot_wider fills with a list column, which is rarely what you want and causes confusing downstream behavior.

Completing and filling sparse data

Real datasets often have implicit missing values — combinations of variables that are absent from the data because no observation occurred. complete() makes implicit missings explicit by generating all combinations of the specified columns and filling missing rows with NA. This is useful for time series with gaps, experimental data where some conditions produced no results, and panel data where not all units appear at all time points.

fill() propagates non-missing values forward or backward along a column. Survey data and hierarchical data often repeat group labels only on the first row of each group, leaving subsequent rows empty. fill() repeats the first non-missing value downward until the next non-missing value, replacing the empty cells with the appropriate group label. This is a form of data cleaning that tidyr handles in one step rather than requiring a custom loop.

Summary

Function	Use Case	Key Arguments
pivot_longer()	Wide to Long	cols, names_to, values_to
pivot_wider()	Long to Wide	names_from, values_from

These functions are essential tools in your data wrangling toolkit. Master them, and you’ll be able to transform any messy dataset into the format you need for analysis.

Next steps

Now that you understand reshaping data with tidyr, explore these related topics to deepen your knowledge and apply these techniques in more complex scenarios.