Skip to content
Tutorials Center
My learning

Topics

Lesson 20 of 39 ยท Data Frames and Data Wrangling

Tidying, Joining and Missing Values

Reshape data with tidyr pivot_longer() and pivot_wider(), combine tables with dplyr joins, stack with bind_rows(), and deal with missing values using drop_na(), replace_na() and coalesce().

Real data rarely arrives in the shape you need. Scores are spread across columns when you want them in rows. Customers live in one table and their orders in another. Some cells are empty. This lesson covers three skills that turn raw tables into analysis-ready ones: reshaping (tidyr), joining (dplyr) and handling missing values.

R
library(dplyr)
library(tidyr)

What "tidy" means

Tidy data has three rules:

  1. Each variable is a column.
  2. Each observation is a row.
  3. Each value is a cell.

Most plotting and modeling code in R expects tidy data. The trouble is that humans make tables for reading, not for computing, so you often need to reshape.

pivot_longer(): wide to long

A "wide" table has one column per measurement:

R
wide <- tibble(
  student = c("Ana", "Ben"),
  math    = c(90, 70),
  art     = c(80, 95)
)
wide
#> # A tibble: 2 ร— 3
#>   student  math   art
#>   <chr>   <dbl> <dbl>
#> 1 Ana        90    80
#> 2 Ben        70    95

The column names math and art are really values of a variable (the subject). pivot_longer() gathers them into one column:

R
long <- pivot_longer(
  wide,
  cols      = c(math, art),
  names_to  = "subject",
  values_to = "score"
)
long
#> # A tibble: 4 ร— 3
#>   student subject score
#>   <chr>   <chr>   <dbl>
#> 1 Ana     math       90
#> 2 Ana     art        80
#> 3 Ben     math       70
#> 4 Ben     art        95
  • cols says which columns to gather (use -student or where(is.numeric) for many).
  • names_to names the new column that holds the old column names.
  • values_to names the new column that holds the values.

The long form works directly with group_by(subject) from dplyr Basics and with ggplot2.

pivot_wider(): long to wide

The reverse spreads a column's values into separate columns, typically to make a compact table for people:

R
pivot_wider(long, names_from = subject, values_from = score)

This returns the original wide table. names_from provides the new column names and values_from the cell values.

flowchart LR
    W["Wide<br/>student, math, art"] -->|pivot_longer| L["Long<br/>student, subject, score"]
    L -->|pivot_wider| W

Figure: the two directions of reshaping.

Splitting and joining text columns

When one column holds two pieces of information, split it. When two columns belong together, unite them:

R
df <- tibble(id = 1:2, full = c("Ana Ruiz", "Ben Lee"))
separate_wider_delim(df, full, delim = " ", names = c("first", "last"))  # first, last columns

df2 <- separate_wider_delim(df, full, delim = " ", names = c("first", "last"))
unite(df2, "full", first, last, sep = " ")   # the reverse

(Older code uses separate(), which still works.) Text cleaning in general is the subject of String Manipulation.

Joining tables

A join combines two tables by matching a key column. Imagine customers in one table and orders in another:

R
customers <- tibble(id = 1:3, name = c("Ana", "Ben", "Cleo"))
orders    <- tibble(id = c(1L, 1L, 3L, 4L), amount = c(10, 20, 30, 40))

Ana (id 1) has two orders, Ben (id 2) has none, Cleo (id 3) has one, and id 4 has an order but no customer record.

left_join: keep every row of the left table

R
left_join(customers, orders, by = "id")
#> # A tibble: 4 ร— 3
#>      id name  amount
#>   <int> <chr>  <dbl>
#> 1     1 Ana       10
#> 2     1 Ana       20
#> 3     2 Ben       NA
#> 4     3 Cleo      30

All customers stay. Ana appears twice because she has two matching orders. Ben has no match, so amount is NA. Order 4 is dropped because its customer is not in the left table.

The join family

Function Keeps
left_join(x, y) All rows of x, plus matching data from y
right_join(x, y) All rows of y, plus matching data from x
inner_join(x, y) Only rows with a match in both
full_join(x, y) All rows from both; NA where no match
semi_join(x, y) Rows of x that have a match (no columns added)
anti_join(x, y) Rows of x that have no match
R
inner_join(customers, orders, by = "id")
#> # A tibble: 3 ร— 3
#>      id name  amount
#>   <int> <chr>  <dbl>
#> 1     1 Ana       10
#> 2     1 Ana       20
#> 3     3 Cleo      30

anti_join(customers, orders, by = "id")     # customers without orders
#> # A tibble: 1 ร— 2
#>      id name 
#>   <int> <chr>
#> 1     2 Ben  

anti_join() is excellent for finding gaps, such as "which customers never ordered?". If the key has different names in the two tables, write by = c("customer_id" = "id"). Join on several columns with by = c("year", "region").

Check row counts after a join. If the key is not unique in the table you join to, rows multiply (as Ana did above). Compare nrow() before and after, and look at count(orders, id) to see duplicate keys. Recent dplyr versions warn about unexpected many-to-many matches.

Stacking tables: bind_rows

Joins add columns by matching. To add rows from tables with the same columns, use bind_rows():

R
bind_rows(jan_sales, feb_sales)
bind_rows(list_of_tables, .id = "source")

Columns are matched by name, and missing ones are filled with NA. This is safer than base rbind().

Handling missing values

Missing values (NA) were introduced in Data Types and Coercion. The strategy depends on why they are missing, but the tools are the same.

First, measure the problem:

R
x <- c(4, NA, 10, NA)

is.na(x)
#> [1] FALSE  TRUE FALSE  TRUE

sum(is.na(x))
#> [1] 2

mean(is.na(x))      # share missing
#> [1] 0.5

For a data frame, colSums(is.na(df)) counts per column.

Option 1: ignore them in the calculation

R
mean(x, na.rm = TRUE)
#> [1] 7

Option 2: drop rows

R
drop_na(df)              # drop rows with an NA in any column
drop_na(df, score)       # only look at the score column

Base R equivalents are na.omit(df) and df[complete.cases(df), ]. Dropping rows is simple but discards information, and may bias results if the missing rows are different from the rest.

Option 3: fill them in

R
replace_na(x, 0)
#> [1]  4  0 10  0

coalesce(x, 0)             # first non-missing value, row by row
#> [1]  4  0 10  0

replace_na() swaps NA for a value you choose. coalesce() can take another column as the replacement: coalesce(mobile, landline). In a data frame, use it inside mutate(): mutate(df, score = replace_na(score, 0)). Table-wide fills use fill(), which carries the last value down a column and suits report-style data where a label appears once per group.

The reverse: codes that mean missing

Data often hides missing values behind placeholders such as -999 or "n/a". Convert them to real NA:

R
na_if(c(5, -999, 8), -999)
#> [1]  5 NA  8

(Better still, set na.strings when reading the file, as in Reading and Writing Data.)

Be careful with replacing by zero. 0 and "not measured" mean different things. Replacing missing incomes with 0 pulls the average down. Replace only when you know the correct value, and note what you did.

A complete wrangling example

Putting everything together, a typical pipeline:

R
orders |>
  left_join(customers, by = "id") |>
  mutate(name = coalesce(name, "unknown")) |>
  group_by(name) |>
  summarise(total = sum(amount), orders = n()) |>
  arrange(desc(total))

Here order 4, whose customer is missing, ends up under "unknown" instead of vanishing. This is the data frame chapter's destination: import, join, clean, group and summarize in one readable chain. Next you will learn to work with text and dates, the other messy kinds of data.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does pivot_longer() do?

    It gathers wide columns such as math and art into a subject column and a score column.
  2. 2. Which join keeps every row of the left table, even if there is no match?

    left_join keeps all left rows and fills unmatched right columns with NA.
  3. 3. Which finds customers who have no orders?

    anti_join returns rows of the first table without a match in the second.
  4. 4. What does coalesce(c(1, NA, 3), 0) return?

    coalesce picks the first non-missing value at each position.
  5. 5. Why check nrow() after a join?

    If a key matches several rows, each match produces an output row.

Summary

  • Tidy data means one variable per column, one observation per row, one value per cell.
  • pivot_longer() and pivot_wider() reshape between wide and long forms.
  • Joins combine tables by key: left_join(), inner_join(), full_join(), plus semi_join() and anti_join() for filtering; bind_rows() stacks tables.
  • Handle NA deliberately: measure with is.na(), then na.rm, drop_na(), replace_na() or coalesce().
  • Check row counts after every join.

Next: R Strings: Create, Combine, Format and Slice Text