Tidy Data, Reshaping and Joins

Getting data into the right shape

Tidy data has a simple structure: each variable is a column, each observation is a row, and each value is a cell. Data often arrives untidy, such as a spreadsheet with one column per month. tidyr reshapes it: pivot_longer() turns many columns into key-value rows (months become a month column and a sales column), and pivot_wider() does the reverse, often for presentation. separate_wider_delim() splits one column into several, unite() combines them, and fill(), drop_na() and replace_na() handle missing values. To combine tables, use dplyr joins: left_join() keeps all rows of the left table and adds matching columns from the right (the most common), inner_join() keeps only matches, full_join() keeps everything, anti_join() finds rows with no match (great for data-quality checks) and semi_join() filters to rows with a match. Specify keys explicitly with by = join_by(customer_id), and watch for duplicate keys, which multiply rows; recent dplyr versions warn about unexpected many-to-many matches.

Pivoting and joining

Wide monthly sales become tidy rows, then customer details are joined in.

library(tidyr)
library(dplyr)

wide <- tibble::tibble(
  city = c("Pune", "Delhi"),
  Jan = c(10, 12), Feb = c(8, 15), Mar = c(9, 11)
)

long <- wide |>
  pivot_longer(cols = Jan:Mar, names_to = "month", values_to = "units")
# one row per city and month (columns city, month, units): six rows in total

long |> pivot_wider(names_from = city, values_from = units)   # back to a wide table

orders <- tibble::tibble(order_id = c("o1", "o2", "o3"), customer_id = c(1, 2, 9), total = c(1200, 300, 800))
customers <- tibble::tibble(customer_id = c(1, 2, 3), name = c("Asha", "Ravi", "Meera"))

orders |> left_join(customers, by = join_by(customer_id))      # o3 gets name = NA
orders |> anti_join(customers, by = join_by(customer_id))      # orders with unknown customers: o3

Count rows before and after joins

If a join unexpectedly increases the number of rows, the key is duplicated in one table. Check with count(table, key) |> filter(n > 1) before joining.

Quick check: Which join returns rows from the left table that have no match in the right table?

• anti_join
• left_join
• inner_join
• full_join