Tidy data for the enterprise

Day 1 · 11:00

Most of the week is not visualisation. It is getting a warehouse extract into a shape where visualisation is even legal. The tidyverse is the grammar for that job.

In an ideal world the commercial team would collect data already tidy. They do not. Category descriptions arrive as "premium tomato paste / 400g, family pack" in one cell. Branch KPIs arrive with one column per week. HR dumps arrive with merged header rows. Estimates put 80% of analysis time on preparation (Dasu & Johnson, 2003). That statistic has not aged out of the Nigerian enterprise.

The three rules

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

If a week is spread across columns w1:w52, weeks are not a variable — they are a layout. If a SKU has three flavour descriptors in one cell, you do not have three observations; you have a sentence. Tidy data is not an aesthetic. It is the contract ggplot2, models, and joins all assume.

The six verbs

Hadley Wickham’s claim is that most analysis reduces to six English verbs. Learn these and you can read other people’s pipelines.

Two more reshape verbs do the heavy lifting: pivot_longer() (wide → long; the old name was gather()) and pivot_wider() (long → wide; old name spread()). separate() / separate_longer_delim() split a packed cell.

A messy grocer extract

library(tidyverse)
sku <- read_csv("sku-descriptions.csv")
sku

Each row is a SKU. The description column packs one, two or three free-text tags, separated by commas, slashes or hyphens, in mixed case, with stray whitespace. Category managers want the top descriptors nationally — not 220 unique snowflakes.

Long, then split, then clean

top10 <- sku %>%
  separate_longer_delim(description, delim = regex("(,|;|/|-)+")) %>%
  mutate(
    description = description %>% str_trim() %>% str_to_lower()
  ) %>%
  filter(nchar(description) > 2) %>%
  count(description, sort = TRUE) %>%
  slice_head(n = 10)
 
top10

Pipes — ‘and then’

The pipeline above never created sku2, sku3, sku_clean_FINAL. The pipe %>% (or the newer |>) reads as and then. Intermediate objects are how analyses become un-reviewable. Keep the verbs; throw away the breadcrumbs — unless an intermediate is itself a deliverable (a reconciled fact table).

# Same story, spoken:
# read the extract AND THEN
# split packed descriptions AND THEN
# trim and lowercase AND THEN
# drop fragments AND THEN
# count AND THEN
# keep the top 10.