library(dplyr)
library(tidyr)
library(readr)
library(lubridate)10 Tidying Data with tidyr
11 Tidying Data with tidyr
11.1 Introduction
Tidy data has one row per observation and one column per variable. Real data rarely arrives that way: metrics sprawl across columns, several values share one column, and combinations go missing. In this chapter we reshape data with tidyr:
pivot_longer()/pivot_wider()— move between long and wide layoutsseparate_wider_delim()/unite()— split one column into several and backdrop_na()/complete()/fill()— handle missing values and combinations
We will use the following R packages:
11.2 Data
We reuse the ecommerce data from @dplyr-basics, so no new data to learn. The URL and date examples borrow the mock_strings and transact data sets already used in @strings-in-r and @date-and-time-in-r.
ecom <-
read_csv('https://raw.githubusercontent.com/rsquaredacademy/datasets/master/web.csv',
col_types = cols_only(device = col_factor(levels = c("laptop", "tablet", "mobile")),
referrer = col_factor(levels = c("bing", "direct", "social", "yahoo", "google")),
purchase = col_logical(), n_pages = col_double(), n_visit = col_double(),
duration = col_double(), order_value = col_double(), order_items = col_double()
)
)
ecom# A tibble: 1,000 × 8
referrer device n_visit n_pages duration purchase order_items order_value
<fct> <fct> <dbl> <dbl> <dbl> <lgl> <dbl> <dbl>
1 google laptop 10 1 693 FALSE 0 0
2 yahoo tablet 9 1 459 FALSE 0 0
3 direct laptop 0 1 996 FALSE 0 0
4 bing tablet 3 18 468 TRUE 6 434
5 yahoo mobile 9 1 955 FALSE 0 0
6 yahoo laptop 5 5 135 FALSE 0 0
7 yahoo mobile 10 1 75 FALSE 0 0
8 direct mobile 10 1 908 FALSE 0 0
9 bing mobile 3 19 209 FALSE 0 0
10 google mobile 6 1 208 FALSE 0 0
# ℹ 990 more rows11.3 Long format with pivot_longer()
Suppose we average three metrics by device. The result is wide: one column per metric, which is awkward to plot or rank.
wide <- ecom |>
summarise(across(c(n_pages, duration, order_value), \(x) mean(x, na.rm = TRUE)), .by = device)
wide# A tibble: 3 × 4
device n_pages duration order_value
<fct> <dbl> <dbl> <dbl>
1 laptop 5.36 376. 441.
2 tablet 6.23 354. 352.
3 mobile 6.17 337. 370.pivot_longer() stacks the metric columns into metric/mean_value pairs — one row per device-metric observation:
long <- wide |>
pivot_longer(-device, names_to = "metric", values_to = "mean_value")
long# A tibble: 9 × 3
device metric mean_value
<fct> <chr> <dbl>
1 laptop n_pages 5.36
2 laptop duration 376.
3 laptop order_value 441.
4 tablet n_pages 6.23
5 tablet duration 354.
6 tablet order_value 352.
7 mobile n_pages 6.17
8 mobile duration 337.
9 mobile order_value 370. 11.4 Wide format with pivot_wider()
The reverse trip is just as common. Counts by device and purchase come out long (one row per combination):
counts <- ecom |>
count(device, purchase)
counts# A tibble: 6 × 3
device purchase n
<fct> <lgl> <int>
1 laptop FALSE 294
2 laptop TRUE 31
3 tablet FALSE 295
4 tablet TRUE 36
5 mobile FALSE 308
6 mobile TRUE 36pivot_wider() spreads purchase into columns so each device occupies a single row — the layout managers usually want in a report:
counts |>
pivot_wider(names_from = purchase, values_from = n, names_prefix = "purchase_")# A tibble: 3 × 3
device purchase_FALSE purchase_TRUE
<fct> <int> <int>
1 laptop 294 31
2 tablet 295 36
3 mobile 308 3611.5 Split columns with separate_wider_delim()
The url column of mock_strings packs the protocol and the address into one string, separated by ://. separate_wider_delim() splits it into two columns:
mockstring <-
read_csv('https://raw.githubusercontent.com/rsquaredacademy/datasets/master/mock_strings.csv')
tibble(url = head(mockstring$url, 3)) |>
separate_wider_delim(url, delim = "://", names = c("protocol", "rest")) |>
select(protocol)# A tibble: 3 × 1
protocol
<chr>
1 https
2 http
3 https The same verb splits calendar dates. The Due column of transact is YYYY-MM-DD; splitting on "-" recovers the components:
transact <-
read_csv('https://raw.githubusercontent.com/rsquaredacademy/datasets/master/transact.csv')
tibble(due_iso = as.character(head(transact$Due, 5))) |>
separate_wider_delim(due_iso, delim = "-", names = c("due_year", "due_month", "due_day"))# A tibble: 5 × 3
due_year due_month due_day
<chr> <chr> <chr>
1 2013 02 01
2 2013 02 25
3 2013 08 02
4 2013 03 12
5 2012 11 24 11.6 Combine columns with unite()
unite() is the inverse: it pastes several columns back into one. Splitting the ISO date and reuniting it returns the input exactly — a useful check that a separate/unite round-trip loses nothing:
tibble(due_iso = as.character(head(transact$Due, 5))) |>
separate_wider_delim(due_iso, delim = "-", names = c("due_year", "due_month", "due_day")) |>
unite("due_rebuilt", due_year, due_month, due_day, sep = "-")# A tibble: 5 × 1
due_rebuilt
<chr>
1 2013-02-01
2 2013-02-25
3 2013-08-02
4 2013-03-12
5 2012-11-24 11.7 Drop missing values with drop_na()
ecom itself has no missing values, but joins create them: the full join of customer and order from @joining-tables-in-r-dplyr carries NAs for every unmatched row. drop_na() keeps only complete rows:
order <- read_delim('https://raw.githubusercontent.com/rsquaredacademy/datasets/master/order.csv', delim = ';')
customer <- read_delim('https://raw.githubusercontent.com/rsquaredacademy/datasets/master/customer.csv', delim = ';')
joined <- full_join(customer, order, by = join_by(id))
nrow(joined)[1] 349nrow(drop_na(joined))[1] 55Only the 55 fully matched rows survive — the same rows inner_join() returns. Use drop_na() when a downstream step needs complete cases, and prefer it over filtering each column by hand.
11.8 Complete combinations with complete()
Counts over a filtered subset can silently omit combinations. Purchasers only span purchase == TRUE, so the FALSE rows are missing rather than zero:
purchasers <- ecom |>
filter(purchase) |>
count(device, purchase)
purchasers# A tibble: 3 × 3
device purchase n
<fct> <lgl> <int>
1 laptop TRUE 31
2 tablet TRUE 36
3 mobile TRUE 36complete() adds the missing combinations and fills the count with 0, so plots and tables show every device × purchase cell:
purchasers |>
complete(device, purchase = c(TRUE, FALSE), fill = list(n = 0L))# A tibble: 6 × 3
device purchase n
<fct> <lgl> <int>
1 laptop FALSE 0
2 laptop TRUE 31
3 tablet FALSE 0
4 tablet TRUE 36
5 mobile FALSE 0
6 mobile TRUE 3611.9 Fill down labels with fill()
Spreadsheet exports often write a group label once and leave the rest blank. fill() carries the last non-missing value down (or up with .direction = "up"):
report <- tibble(device = c("laptop", NA, "tablet", NA),
metric = c("aov", "orders", "aov", "orders"))
report |>
fill(device)# A tibble: 4 × 2
device metric
<chr> <chr>
1 laptop aov
2 laptop orders
3 tablet aov
4 tablet orders11.10 Your Turn
- reshape the mean of
n_visitandorder_itemsbyreferrerfrom wide to long - split the
emailcolumn ofmockstringat"@"and reunite it — does the round-trip hold? - run the
complete()example withoutfill: what does the missing count become, and why?
Try it live
TipTry it in your browser
Edit the code below and press Run. It executes entirely in your browser via WebR — no R installation needed. The runtime downloads once, on this page only.