0Pricing
R Academy · Lesson

Wide to Long with pivot_longer()

Reshape wide format tables into tidy long format for analysis.

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

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()?

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'
)

Frequently asked questions

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

Yes — the full text of “Wide to Long with pivot_longer()” 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 “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; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

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

  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