Shape / columns · All operations

Group values within a distance

A variant of Merge near-duplicate values

Cluster values whose edit or similarity distance falls within a threshold, then merge each cluster to one value.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Cluster values within an edit distance of 1 and merge each cluster to its most common value

SQL
CREATE TEMP TABLE raw AS SELECT * FROM (VALUES
  (1, 'Jon Smith'), (2, 'Jon Smith'), (3, 'Jon Smith'), (4, 'John Smith'), (5, 'John Smith'), (6, 'Jon Smyth'),
  (7, 'Mary Jones'), (8, 'Mary Jones'), (9, 'Mary Jonas'), (10, 'Mary Jonas'), (11, 'Zed'), (12, NULL)
) t(id, name);
-- Jon Smith is 1 from John Smith and from Jon Smyth, which are 2 apart: the cluster follows the chain, so the pairs are linked
-- to a common root before each name takes the most common spelling of its cluster. Mary Jones and Mary Jonas are equally common
-- (2 each), so the tie goes to the value that sorts first.
CREATE TEMP TABLE result AS
WITH RECURSIVE
  counts AS (SELECT name, count(*) AS n FROM raw WHERE name IS NOT NULL GROUP BY name),
  pairs AS (SELECT a.name AS a, b.name AS b FROM counts a JOIN counts b ON levenshtein(lower(a.name), lower(b.name)) <= 1),
  reach(name, root) AS (SELECT name, name FROM counts UNION SELECT p.b, r.root FROM reach r JOIN pairs p ON p.a = r.name),
  clusters AS (SELECT name, min(root) AS cluster_id FROM reach GROUP BY name),
  merged AS (SELECT cluster_id, arg_min(c.name, (-n, c.name)) AS canonical FROM clusters JOIN counts c USING (name) GROUP BY cluster_id)
SELECT r.id, r.name, m.canonical
FROM raw r LEFT JOIN clusters USING (name) LEFT JOIN merged m USING (cluster_id);
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT list(canonical ORDER BY id) FROM result) = ['Jon Smith', 'Jon Smith', 'Jon Smith', 'Jon Smith', 'Jon Smith', 'Jon Smith', 'Mary Jonas', 'Mary Jonas', 'Mary Jonas', 'Mary Jonas', 'Zed', NULL] AND (SELECT count(DISTINCT name) FROM result) = 6 AND (SELECT count(DISTINCT canonical) FROM result) = 3) THEN 'as expected' ELSE error('the six spellings were not merged to Jon Smith, Mary Jonas (the tie goes to the first by value) and Zed, following the chain through the 1-away pairs, with the missing name left missing') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the six spellings were not merged to Jon Smith, Mary Jonas (the tie goes to the first by value) and Zed, following the chain through the 1-away pairs, with the missing name left missing”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

OpenRefine (2)
  • Levenshteinread 2026-09-27

    Cluster values within a radius of edit distance, after blocking on shared substrings

  • PPMread 2026-09-27

    Cluster values within a radius of a compression-based distance, after blocking

Power Query M (1)
  • Table.AddFuzzyClusterColumnread 2026-09-27

    Add a column holding a representative value for each group of fuzzily similar values in a column

Tableau Prep (1)
  • Spellingread 2026-09-27

    Group values within an edit distance, by Levenshtein distance

Background

Grouping values within a distance bins together any pair of values closer than a distance threshold that the analyst sets.[1] The Levenshtein distance counts the fewest single-character insertions, deletions or substitutions that turn one string into another.[2] Increasing the threshold makes the matches more lax, as bigger differences are clustered.[3]

When to use it

A distance can join different things: "M. Makeba" is 5 edits from "Miriam Makeba" but only one from "B. Makeba", another person entirely.[3] Use a distance for short values such as names and codes, where one typo matters, and keep the threshold small relative to the length of the values. (Meridian's view) DuckDB's levenshtein and jaro_winkler_similarity functions treat characters of different cases, such as a and A, as different.[4] Counted character by character, "New York" and "newyork" are 3 edits apart: two capitals and a space.[3] Lowercase and trim values before measuring the distance, so that the threshold is spent on spelling rather than on case and spacing. (Meridian's view)

Comparing every pair of n strings takes n(n-1)/2 distance calculations, which for a dataset of 3000 rows means 4.5 million.[1] Blocking cuts this by comparing strings only within blocks whose members share a substring of a set size, six characters by default in OpenRefine.[1]

Single-linkage clustering, which joins clusters through their closest pair of elements, tends to produce long thin clusters whose ends may be much farther apart than elements of other clusters.[5]

The method

The Levenshtein distance is named after Soviet mathematician Vladimir Levenshtein, who introduced it in 1965 in the context of error-correcting codes.[2] Damerau-Levenshtein distance extends the Levenshtein distance by also allowing the transposition of two adjacent characters as an edit.[4]

The Jaro–Winkler similarity is a variant of Matthew A. Jaro's 1989 distance metric, proposed in 1990 by William E. Winkler.[6] The Jaro–Winkler similarity uses a prefix scale that gives more favourable ratings to strings that match from the beginning.[6] DuckDB's jaro_winkler_similarity returns a number between 0 and 1, and returns 0 for any similarity below its optional score_cutoff.[4] Power Query's Table.AddFuzzyClusterColumn groups values at a similarity threshold between 0.00 and 1.00, 0.80 by default, where 1.00 allows only exact matches.[7]

Sources

  1. [1]Clustering Methods In-depthdocumentation
  2. [2]Levenshtein distancesecondary source
  3. [3]Cell editingdocumentation
  4. [4]Text Functions – DuckDBdocumentation
  5. [5]Single-linkage clusteringsecondary source
  6. [6]Jaro–Winkler distancesecondary source
  7. [7]Table.AddFuzzyClusterColumn - PowerQuery M | Microsoft Learndocumentation