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.