Reshape Data in R: pivot_wider and pivot_longer (Wide vs Long)

Wide vs Long: The Same Data, Two Shapes

Every reshaping question comes down to one distinction, so let's see it. Here is one small dataset - quarterly sales for two cities - built in both shapes:

wide <- data.frame(
    city = c("Lima", "Oslo"),
    Q1   = c(120, 80),
    Q2   = c(135, 95)
)

long <- data.frame(
    city    = c("Lima", "Lima", "Oslo", "Oslo"),
    quarter = c("Q1", "Q2", "Q1", "Q2"),
    sales   = c(120, 135, 80, 95)
)

print(wide)
print(long)

Identical information, different geometry. Wide has one row per city and one column per quarter - the quarter lives in the column names. Long has one row per observation (city × quarter), with the quarter demoted to an ordinary column and every measurement in a single sales column.

Neither shape is "correct" - they serve different masters. Wide is what humans read and spreadsheets export: compact, comparable at a glance. Long is what tools consume, and converting between them is called pivoting (or reshaping - same thing).

Why Long Format Wins for Analysis

Look at what the wide format did to the concept "quarter": it shredded one variable into column names. Nothing can compute with a column name. In long format, quarter is data again, and everything in the grouped-analysis toolbox switches on:

• aggregate(sales ~ quarter, ...) or group_by(quarter) - impossible in wide format, where each quarter is a separate column you'd handle by hand (grouped summaries all assume long data).
• ggplot2 maps columns to aesthetics: aes(x = quarter, y = sales, color = city) needs those three columns to exist. Plotting wide data is a constant fight; plotting long data is one line.
• Adding Q3 is adding rows - no schema change - where wide format grows a new column that every downstream step must learn about.

The practical rule: store and analyze long, present wide. Which is why the two conversions below get so much use - data arrives wide from spreadsheets, gets lengthened for analysis, and gets widened again for the final table in the report.

Wide to Long: pivot_longer()

The tidyr package (dplyr's sibling in the tidyverse) owns modern reshaping. Its snippets are static here - the sandbox runs base R only. Lengthening takes three decisions, one argument each:

library(tidyr)

long <- pivot_longer(wide,
    cols      = c(Q1, Q2),      # which columns to stack
    names_to  = "quarter",      # new column that receives the old NAMES
    values_to = "sales"         # new column that receives the cell VALUES
)

Walk it through: cols names the columns being dissolved (Q1:Q2 range syntax and helpers like starts_with("Q") work too - handy when there are twenty of them). Each chosen cell becomes one row; the column it came from lands in quarter, the number itself in sales. Columns not listed in cols (here city) are treated as identifiers and repeat down the rows. The result is exactly the long frame printed above.

Long to Wide: pivot_wider()

The reverse trip takes two decisions - where the new column names come from, and what fills them:

wide <- pivot_wider(long,
    names_from  = quarter,   # distinct values here become new columns
    values_from = sales      # these values fill the cells
)

Every distinct value of quarter (Q1, Q2) becomes a column; each cell is filled with the sales value from the row matching that city and quarter. Remaining columns (city) act as row identifiers - one output row per distinct combination. A city missing a quarter gets NA in that cell (the values_fill argument substitutes something else, e.g. values_fill = 0 for count data).

You'll also meet the previous generation in older tutorials: spread() is pivot_wider() and gather() is pivot_longer() - superseded since tidyr 1.0, still working, not worth learning beyond recognizing them.

Base R Has reshape() - An Honest Warning

Base R can do this without packages, via reshape() - a function so famously confusing that its own documentation historically apologized for it. It was designed around longitudinal-study vocabulary (idvar, timevar, direction), the argument names map awkwardly onto everyday data, and everyone forgets them between uses. It does work:

long <- data.frame(
    city    = c("Lima", "Lima", "Oslo", "Oslo"),
    quarter = c("Q1", "Q2", "Q1", "Q2"),
    sales   = c(120, 135, 80, 95)
)

wide <- reshape(long, idvar = "city", timevar = "quarter", direction = "wide")
print(wide)

Note the output names: sales.Q1, sales.Q2 - the value column's name fused onto each. Fine in a pinch, in a zero-dependency environment, or when you meet it in old code. But if you reshape data more than once a year, install.packages("tidyr") is the honest recommendation - this is the one wrangling task where base R's tool genuinely costs more than the dependency it saves.

The Duplicate-Key Pitfall When Widening

Widening assumes each identifier + name combination pins down one value. If Lima has two Q1 rows, which number goes in the single Q1 cell? pivot_wider() refuses to guess: it emits a warning (values are not uniquely identified) and puts a list-column in the cell - a data frame with lists nested in it, which breaks the next thing you do with it.

When you see that warning, don't reach for the values_fn escape hatch first - ask why duplicates exist. Usually the data is at a finer grain than you thought (daily rows, not quarterly - so summarize first, then widen) or an upstream join duplicated rows. values_fn = mean (or sum) is the legitimate fix only once you can say out loud which aggregation the duplicates should collapse under. reshape() is worse here: it silently keeps the first duplicate and drops the rest, with only a warning easily scrolled past.

What You Take Away

• Wide: variables hiding in column names, made for reading. Long: one row per observation, made for analysis and ggplot2.
• Store and analyze long, present wide.
• pivot_longer(cols, names_to, values_to) stacks columns into rows; unlisted columns become repeating identifiers.
• pivot_wider(names_from, values_from) spreads rows into columns; gaps fill with NA.
• spread()/gather() are the old names; base reshape() works but is famously awkward.
• Duplicate identifier + name pairs make widening ambiguous - summarize first, don't just silence the warning.

Next up: reading real data in from files - read.csv() and its sharp edges.