R Academy · Lesson

Wide to Long with pivot_longer()

Reshape wide format tables into tidy long format for analysis.

Lesson 1 of 413 steps

Wide to Long with pivot_longer() is a free R Academy lesson on CoddyKit. This is lesson 1 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.

Wide vs Long Data Formats

Data can be stored in wide format (one row per subject, multiple value columns) or long format (one row per observation, a column for variable name and another for value). Many R packages for visualization and modeling expect long format.

library(tidyr)
library(dplyr)

# Wide format: each quarter is a column
wide_df <- data.frame(
  student = c('Alice','Bob','Carol'),
  q1 = c(85, 90, 78),
  q2 = c(88, 85, 80),
  q3 = c(92, 88, 84)
)

print(wide_df)
cat('\nShape:', nrow(wide_df), 'rows x', ncol(wide_df), 'cols')

pivot_longer() Basics

pivot_longer(df, cols, names_to, values_to) converts wide format to long format. cols specifies which columns to pivot, names_to is the new column for the old column names, and values_to is the new column for the values.

library(tidyr)

wide_df <- data.frame(
  student = c('Alice','Bob','Carol'),
  q1 = c(85, 90, 78),
  q2 = c(88, 85, 80),
  q3 = c(92, 88, 84)
)

long_df <- pivot_longer(
  wide_df,
  cols = c(q1, q2, q3),
  names_to = 'quarter',
  values_to = 'score'
)

print(long_df)

Selecting Columns with starts_with()

Instead of listing columns explicitly, use tidyselect helpers inside cols. starts_with('prefix'), ends_with('suffix'), and contains('string') are especially useful for pivoting consistently-named columns.

library(tidyr)

df <- data.frame(
  id = 1:3,
  sales_jan = c(100, 200, 150),
  sales_feb = c(120, 180, 160),
  sales_mar = c(130, 190, 170)
)

# Select all sales_ columns automatically
pivot_longer(
  df,
  cols = starts_with('sales_'),
  names_to = 'month',
  values_to = 'sales'
)

Excluding Columns with -col

Another approach: specify which columns to exclude from pivoting using -col_name. Everything else gets pivoted. This is handy when you have just one or two ID columns and many value columns.

library(tidyr)

df <- data.frame(
  region = c('East','West'),
  jan = c(100, 200),
  feb = c(110, 210),
  mar = c(120, 220),
  apr = c(130, 230)
)

# Pivot everything except 'region'
pivot_longer(df, cols = -region, names_to = 'month', values_to = 'revenue')

names_prefix — Stripping Prefixes

When column names have a prefix like score_q1, score_q2, the names_prefix argument strips that prefix from the resulting names_to column. This keeps the identifier clean.

library(tidyr)

df <- data.frame(
  id = 1:3,
  score_q1 = c(85, 90, 78),
  score_q2 = c(88, 85, 80),
  score_q3 = c(92, 88, 84)
)

pivot_longer(
  df,
  cols = starts_with('score_'),
  names_to = 'quarter',
  names_prefix = 'score_',
  values_to = 'score'
)

Pivoting Multiple Sets of Columns

When you have multiple groups of value columns (e.g., both sales_q1 and cost_q1), use names_to with multiple values and names_pattern to split column names into parts.

library(tidyr)

df <- data.frame(
  id = 1:2,
  sales_q1 = c(100, 200),
  sales_q2 = c(110, 210),
  cost_q1 = c(60, 110),
  cost_q2 = c(65, 115)
)

pivot_longer(
  df,
  cols = -id,
  names_to = c('.value', 'quarter'),
  names_pattern = '(.+)_(q[0-9]+)'
)

names_transform — Converting Types

By default, the new names column is character. Use names_transform to convert the names column to a different type automatically (e.g., integer year from column names like y2020, y2021).

library(tidyr)

df <- data.frame(
  product = c('A','B'),
  y2021 = c(100, 200),
  y2022 = c(110, 210),
  y2023 = c(120, 220)
)

result <- pivot_longer(
  df,
  cols = -product,
  names_to = 'year',
  names_prefix = 'y',
  names_transform = list(year = as.integer),
  values_to = 'sales'
)

print(result)
cat('year column type:', class(result$year))

values_drop_na — Removing Missing Values

When the wide format has missing values for some column-row combinations, they appear as NA in long format. Set values_drop_na = TRUE to automatically remove rows where the value is NA.

library(tidyr)

df <- data.frame(
  student = c('Alice','Bob','Carol'),
  q1 = c(85, NA, 78),
  q2 = c(88, 85, NA),
  q3 = c(NA, 88, 84)
)

# Without drop: 9 rows including NAs
pivot_longer(df, -student, names_to='quarter', values_to='score',
             values_drop_na = TRUE)

Long Format Enables ggplot2

The most important reason to pivot to long format: ggplot2 maps aesthetics from columns. When all values are in one column and their categories in another, you can use color = quarter or facet_wrap(~quarter) effortlessly.

library(tidyr)
# (ggplot2 not run here, but showing the data preparation)

df <- data.frame(
  month = c('Jan','Feb','Mar'),
  product_A = c(100, 120, 110),
  product_B = c(200, 190, 210)
)

# After pivoting, ready for ggplot2:
long_df <- pivot_longer(df, -month, names_to='product', values_to='sales')
print(long_df)
# ggplot(long_df, aes(x=month, y=sales, color=product)) + geom_line()

Pivoting with Numeric Column Names

Sometimes column names are pure numbers (e.g., years: 2020, 2021). In R, numeric column names are quoted in data frames. Use num_range('', 2020:2023) or backtick-quoting in the cols argument to handle them.

library(tidyr)

# Year columns as numbers (common in census-style data)
df <- data.frame(
  country = c('US','UK'),
  '2021' = c(100, 80),
  '2022' = c(110, 85),
  '2023' = c(120, 90),
  check.names = FALSE
)

pivot_longer(
  df,
  cols = c('2021','2022','2023'),
  names_to = 'year',
  values_to = 'gdp_index'
)

Comparing Pivot Results

After pivoting, validate your result: row count should be nrow(wide) * n_pivoted_cols, and the unique values in names_to column should match your original column names (minus the prefix if stripped).

library(tidyr)

wide <- data.frame(
  id = 1:4,
  jan = c(10,20,30,40),
  feb = c(11,21,31,41),
  mar = c(12,22,32,42)
)

long <- pivot_longer(wide, -id, names_to='month', values_to='value')

cat('Wide rows:', nrow(wide), '\n')
cat('Long rows:', nrow(long), '(expected', nrow(wide)*3, ')\n')
cat('Months:', paste(unique(long$month), collapse=', '), '\n')

Quick Check

What does the names_prefix argument do in pivot_longer()?

Answer it in the interactive lesson

Recap: pivot_longer()

Key takeaways for wide-to-long conversion:

pivot_longer(df, cols, names_to, values_to) is the core function
cols accepts tidyselect: starts_with(), -id_col, c('a','b')
names_prefix strips a leading string from column names
names_transform converts names column type (e.g., to integer)
values_drop_na = TRUE removes NA rows automatically
Long format is required by ggplot2, most modeling functions, and statistical tests
Validate: long rows = wide rows × number of pivoted columns

library(tidyr)

df <- data.frame(
  team = c('Alpha','Beta'),
  sales_2022 = c(100, 200),
  sales_2023 = c(120, 220),
  sales_2024 = c(140, 240)
)

pivot_longer(
  df,
  cols = starts_with('sales_'),
  names_to = 'year',
  names_prefix = 'sales_',
  names_transform = list(year = as.integer),
  values_to = 'revenue'
)

Free to start

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

Start learning free View the full course

Frequently Asked Questions

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

Yes — any 3 lessons of the R Academy learning path, including “Wide to Long with pivot_longer()”, 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 “Wide to Long with pivot_longer()”?

Reshape wide format tables into tidy long format for analysis. 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 1 of 4.

How long does the “Wide to Long with pivot_longer()” 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

← Back to R Academy