tidyr and Reshaping Data

Analogy categoryAnalogies

What Makes Data Tidy?

Tidy data, the organizing principle behind tidyr and the rest of the tidyverse, follows three rules: every variable forms a column, every observation forms a row, and every type of observational unit forms its own table. Data collected or exported from spreadsheets and surveys is often 'wide' or 'messy' instead, for example having separate columns for each year (2021, 2022, 2023) rather than a single year column, which makes it awkward to filter, group, or plot with dplyr and ggplot2 until it's reshaped into tidy form.

🏏

Cricket analogy: Messy data with separate columns for each match's score is like a scorecard that lists every over as its own sheet instead of one table with over, runs, and wickets as columns; tidy data merges those into rows.

Reshaping with pivot_longer() and pivot_wider()

pivot_longer() converts wide columns into two new columns: a names column holding what used to be the column headers and a values column holding the corresponding data, for example pivot_longer(df, cols = c(2021, 2022, 2023), names_to = 'year', values_to = 'revenue') turns three year columns into a tidy year/revenue pair of columns with one row per year per original row.

🏏

Cricket analogy: pivot_longer() is like unfolding a scorecard that lists a batter's runs across four separate innings columns into one innings column and one runs column, giving four tidy rows instead of one wide row.

pivot_wider() does the reverse, spreading a single names column's distinct values out into multiple new columns populated from a corresponding values column, for example pivot_wider(df, names_from = year, values_from = revenue) turns a long year/revenue pair back into one column per year; it is the natural verb for producing summary tables meant for human reading, such as a year-by-region revenue matrix.

🏏

Cricket analogy: pivot_wider() is like taking a long table of player/innings/runs rows and spreading it into one column per innings, producing the familiar wide scorecard format for a printed match report.

library(tidyr)
library(dplyr)

wide <- tibble(
  region = c('North', 'South', 'East'),
  `2021` = c(120, 95, 80),
  `2022` = c(135, 100, 88),
  `2023` = c(150, 110, 97)
)

long <- wide %>%
  pivot_longer(cols = -region, names_to = 'year', values_to = 'revenue')

long %>%
  pivot_wider(names_from = year, values_from = revenue)

Splitting and Combining Columns

separate() (or its modern replacement separate_wider_delim()) splits one column into several based on a delimiter, such as turning a city, country column into separate city and country columns; unite() does the opposite, pasting several columns together into one with a chosen separator, which is handy when you need a single composite key like year-month for joining or plotting.

🏏

Cricket analogy: separate_wider_delim() is like splitting a player, country scorecard column into two clean columns, while unite() is combining separate year and tournament columns into one year-tournament label for a chart axis.

separate_wider_delim() and separate_wider_position() (tidyr 1.3+) are the recommended replacements for the older separate() function, offering more explicit error handling when a row doesn't split into the expected number of pieces.

Handling Nested and Missing Values While Reshaping

pivot_wider() can introduce NA values whenever a names/values combination doesn't exist for a given row, for example a customer with no purchase in a given month, so it is common to pair it with values_fill = 0 to substitute a default instead of NA; conversely complete() explicitly fills in combinations of variables that should exist but are missing from the data, and pivot_longer() accepts values_drop_na = TRUE to discard rows created purely because the original wide format had blank cells.

🏏

Cricket analogy: pivot_wider(values_fill = 0) is like filling a batting-average table with 0 for a player who didn't play a particular match, rather than leaving a blank cell that could be misread as a missing scorecard.

pivot_wider() silently introduces NA for any names/values combination absent from the long data; always check whether those NAs represent a true zero (use values_fill), a genuinely unknown value, or a data entry gap before analyzing the resulting wide table.

Tidy data follows three rules: one variable per column, one observation per row, one observational unit per table.
pivot_longer() turns wide columns into name/value column pairs, producing more rows and fewer columns.
pivot_wider() spreads a name/value column pair back into multiple columns, producing fewer rows and more columns.
separate_wider_delim() splits one column into several by a delimiter; unite() combines several columns into one.
pivot_wider() can introduce NA for missing names/values combinations; values_fill supplies a default instead.
complete() explicitly materializes variable combinations that should exist but are absent from the data.
Reshaping to tidy form is usually the prerequisite step before dplyr verbs or ggplot2 mappings work cleanly.