Split, Unite & Fuzzy Joins

In Lesson 2 you changed a table's shape. But real data arrives dirty in a different way: a single column crams two facts together, or two tables that should line up have keys that only nearly agree.

Maya, who runs a small bakery, now pulls sales from an online-orders export and prices from a supplier sheet someone typed by hand. Below are her items on the left and the supplier's price sheet on the right. To you they read as the same four products. Click inner: an exact join finds only two matches. The other two are spelled just differently enough to be invisible to the join.

By the end of this lesson you will be able to:

• Split one crammed column into several with separate() (set into and sep)
• Combine several columns into one with unite(), the exact inverse of separate()
• Spot keys that only nearly match, surface them with anti_join(), and repair them by cleaning and by string distance (a fuzzy join)

Prerequisites: you can run R, you know a tibble and the dplyr verbs, and you met the inner, left and anti joins and the join key in Lesson 1.

separate() splits a crammed column

Maya's online store exports one row per order, but it crams the product name and its size into a single product field, like "Sourdough/Large". That breaks a rule of tidy data: one variable per column. To analyse sales by item, and separately by size, those two facts need their own columns.

Each lesson runs in a fresh R session, so we build her export right here as a tibble, then never touch the disk again:

tribble() from tibble just lets us type a small table by hand: the ~order, ~product, ~ordered_on entries are the column names, and the values follow row by row underneath.

separate(), from tidyr, takes one column and cuts each value into pieces at a separator. You tell it three things: which column to cut (product), what to name the pieces (into), and the character to cut on (sep):

The one product column became two, item and size, split at the /. Read it as a picture: the crammed column on the left opens out into the two clean columns on the right.

Naming the pieces

Maya's product values are "Sourdough/Large", "Bagel/Small", "Croissant/Large". She runs separate(product, into = c("item", "size"), sep = "/"). What decides that the split lands correctly?

unite() glues columns back into one

separate() has a mirror image. unite() takes several columns and pastes them into a single one, joined by a separator you choose. It is to separate() what pivot_wider() was to pivot_longer() in Lesson 2: do one, then the other, and you are back where you started.

First we keep the split result so later steps can reuse it, then glue item and size straight back together:

Exactly Maya's original product column, reconstructed. The first argument names the new column; the bare column names after it are the ones to glue, in order; sep is the glue. Picture it as the reverse of the last step: two columns folding back into one.

Build a join key

Maya needs a single product code, a sku, that pastes the item and size with a dash, like "Sourdough-Large". You already have tidy_orders with its item and size columns. Fill in the verb that glues columns into one, then check it.

tidy_orders %>%
  unite("sku", item, size, sep = "-")

Near-miss keys vanish from an exact join

Now the real task. Maya wants the supplier's price next to each of her items, a plain left/inner join on the item name. But the supplier sheet was typed by hand, so the names only nearly agree: sourdough is lower-case, and Croissant was mistyped as Croisant. An exact join matches strings character for character, so those two simply do not line up.

Only Bagel and Pretzel survived, the two spelled identically on both sides. Sourdough and Croissant were dropped without a word, which is the dangerous part: a silent join looks like it worked. The way to catch this is anti_join(), which you met in Lesson 1: it keeps exactly the left rows with no match, the keys you need to fix.

There is the cleanup list. The widget makes the filter concrete: the two matched rows fade out, and the two unmatched keys, your real problem, stay behind.

Normalise the keys before anything clever

Before reaching for anything fancy, fix the boring differences. A great share of near-misses are nothing but case and whitespace: Sourdough versus sourdough, or a stray trailing space. Lower-case both sides and squeeze the spaces, then join on the cleaned key.

Cleaning recovered Sourdough: once both sides are lower-case, sourdough matches sourdough. We are up to three matches from two. But Croissant is still missing, because its problem was never case, it was a real typo: the supplier dropped an s. Toggle the widget to inner and watch three keys light up green while croissant and croisant stay stubbornly apart.

str_to_lower() and str_squish() come from stringr: the first lower-cases, the second trims the ends and collapses runs of spaces to one. Reach for them on every key before you join.

Edit distance: a number for "nearly the same"

Croissant and Croisant are not equal, but they are close: one is the other with a single letter dropped. To match them on purpose we need to make "close" a number, and the standard one is edit distance.

The edit distance (or Levenshtein distance) between two strings \(a\) and \(b\), written \(d(a,b)\), is the smallest number of single-character edits, an insertion, a deletion, or a substitution, needed to turn \(a\) into \(b\). Identical strings have \(d = 0\); the further apart two spellings drift, the larger \(d\) grows. Turning Croissant into Croisant needs exactly one deletion, drop one s, so \(d(\text{Croissant}, \text{Croisant}) = 1\).

Base R computes it for you with adist(), no package needed:

One edit, just as we counted. Now point it at the whole supplier list and ask which spelling is closest to Maya's leftover croissant:

The nearest supplier key is croisant, just one edit away, while every other name is many edits off. That single number is the whole idea behind a fuzzy join: match a key to its nearest neighbour, as long as the neighbour is close enough.

Which fix, and how far to reach

After cleaning, Sourdough matched but Croissant (vs the supplier's Croisant) still did not. Maya thinks: "I will set the fuzzy threshold to max_dist = 4 so nothing ever slips through again." What is the right read?

The fuzzy join, and its sharp edge

In practice you do not hand-roll adist() for every key. The fuzzyjoin package adds string-distance versions of the dplyr joins: stringdist_inner_join(), stringdist_left_join() and friends, each with a max_dist threshold. This one needs installing and runs on your own machine rather than here, so treat it as a recipe to copy:

# install once with install.packages("fuzzyjoin")
library(fuzzyjoin)

# match keys that are within ONE edit of each other:
stringdist_inner_join(sales_clean, prices_clean,
                      by = "key", max_dist = 1)
#> all four items now match, including croissant <-> croisant

With max_dist = 1, croissant finally meets croisant and Maya has her complete price list. But that threshold is a loaded setting.

References

A few authoritative places to take this further:

• R for Data Science (2e), Strings - the free, canonical chapter on cleaning and reshaping text, the raw material of every messy key.
• tidyr: separate_wider_delim() - the current splitting verbs (the modern successors to separate()), with every argument explained.
• tidyr: unite() - gluing columns into one, the inverse move, including how it handles NA.
• van der Loo (2014), The stringdist package for approximate string matching, R Journal 6(1) - the paper behind the distance metrics fuzzy joins use, edit distance included.
• fuzzyjoin package documentation - string-distance and other inexact joins as drop-in dplyr verbs, with max_dist worked examples.

Lesson 3 complete, and the course with it

You can now clean the two messes that block a join. You split a crammed column into tidy pieces with separate(), glued columns into the key you need with unite() (its exact inverse), and learned that an exact join drops near-miss keys silently, so you surface them with anti_join(), normalise away case and whitespace first, and only then reach for a string-distance fuzzy join, with the threshold kept tight to avoid false matches.

That closes Joining and Reshaping Data in R. Across three lessons you learned to combine two tables with the mutating and filtering joins, reshape one table between long and wide with pivot_longer() and pivot_wider(), and clean the columns and keys that real data throws at you. You now have the full toolkit for getting scattered, messy data into one analysis-ready table, the step every project depends on before a single chart or model. Next, point that clean data at exploratory analysis and visualisation.