Shape · Combine tables

Fuzzy Match

Joins two tables on keys that are similar but not identical, such as customer names with typos. A score column shows how close each match is.

Also known as: Approximate join, record linkage

How it works

Wire the tables into left and right and click Add key under Match keys for each left and right column pair. Algorithm is Jaro-Winkler or Levenshtein, the latter scaled to 0 to 1 by the longer string; with several keys the score is their average. A pair is a match when the score reaches the Threshold, 0.85 by default. Keep is Best match per left row, which keeps the top-scoring right row (ties broken by the right key, so runs are repeatable), or All matches ≥ threshold. The output has both sides' columns plus the Score column, similarity by default; the run stops if an input already has a column of that name. A left row with no match above the threshold is dropped. Every left row is compared with every right row, so cost grows with the product of the two sizes.

Inputs and outputs

Settings

{
  keys: [{ leftKey, rightKey }]
  algorithm: "jaro_winkler"|"levenshtein"
  threshold: number (0..1)
  policy: "best"|"all"
  scoreColumn: string
}

Example

A spreadsheet of customer names has typos and must be matched to the CRM. Key name to customer_name, Jaro-Winkler, threshold 0.9, Best match per left row, then review rows whose similarity is below 0.95 by hand.

More in Combine tables

• Join — Matches rows from two tables on one or more key columns: inner, left, right, full, anti or semi. Keys can also compare with <, > or between, for range joins.
• Lookup — Adds columns from a reference table to every row by matching one key, like Excel's VLOOKUP. Rows without a match stay, with blanks.
• Union — Stacks the rows of two or more tables one under another. Columns line up by name or by position.
• Zip — Puts two tables side by side, row 1 with row 1, row 2 with row 2, with no key needed. Both tables must have the same number of rows.
• Cross Join — Pairs every row of one table with every row of the other, so 10 × 5 rows gives 50. For example, every store with every month.
• As-of Join — Matches each row to the closest row in another table by a sorted key such as a time. For example, attach the price in effect at the time of each trade.