Shape / columns · All operations

Merge near-duplicate values

Cluster near-duplicate values within one column and merge them.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
several clauses of SQL, or a window

The DuckDB step

The fingerprint key-collision method

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
WITH keyed AS (
  SELECT customer,
         array_to_string(list_sort(list_distinct(
           regexp_split_to_array(regexp_replace(trim(lower(strip_accents(customer))), '[^a-z0-9\s]', '', 'g'), '\s+'))), ' ') AS fingerprint
  FROM orders)
SELECT fingerprint, list_sort(list(DISTINCT customer)) AS variants, count(*) AS n
FROM keyed GROUP BY fingerprint HAVING count(DISTINCT customer) > 1;
SELECT * FROM result ORDER BY fingerprint;
SELECT CASE WHEN ((SELECT map_from_entries(list((fingerprint, variants) ORDER BY fingerprint)) = MAP {'alice ng': ['Alice Ng', 'alice ng'], 'bob okafor': ['Bob  Okafor', 'Bob Okafor'], 'carla diaz': ['Carla Diaz', 'Carla Díaz']} FROM result)) THEN 'as expected' ELSE error('the three names written two ways were not found as three clusters') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the three names written two ways were not found as three clusters”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

  • Add a matching key column

    Add a column holding a phonetic or functional key for each value, leaving the values themselves unchanged, for later matching.

  • Group values within a distance

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

Group values that share a key

Cluster values whose normalised fingerprint or phonetic key is equal, then merge each cluster to one value.

Standardise values against a reference

Map each variant to the closest valid value from a reference list or a language model, rather than to a cluster's own value.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (1)
  • Precision Matchread 2026-09-27

    Standardise spelling variants of a phrase to one value, backed by an LLM connection

OpenRefine (10)
  • Beider-Morseread 2026-09-27

    Key values by a stricter variant of the Daitch-Mokotoff phonetic encoding

  • Cluster and edit…read 2026-09-27

    Find groups of similar values in a column and, on approval, merge each group to one value

  • Cologne Phoneticread 2026-09-27

    Key values by a phonetic encoding for German pronunciation

  • Custom clustering methodsread 2026-09-27

    Define a keying or distance function as an expression and use it as a clustering method

  • Daitch-Mokotoffread 2026-09-27

    Key values by a phonetic encoding for Slavic and Yiddish words, names especially

  • Fingerprintread 2026-09-27

    Key values by lowercased, de-accented, punctuation-free, sorted words; equal keys cluster

  • Levenshteinread 2026-09-27

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

  • Metaphone3read 2026-09-27

    Key values by an English-language phonetic encoding

  • N-gram Fingerprintread 2026-09-27

    Key values by their sorted, de-duplicated character n-grams of a chosen size

  • 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

RapidMiner Studio (1)
Tableau Prep (5)
  • Common Charactersread 2026-09-27

    Group values sharing letters or numbers, by an n-gram fingerprint

  • Group Valuesread 2026-09-27

    Map several values of a field to one standard value, by hand or by fuzzy match

  • Pronunciationread 2026-09-27

    Group values that sound alike, by the Metaphone 3 algorithm

  • Pronunciation + Spellingread 2026-09-27

    Match values invalid for a data role to the closest valid value of that role

  • Spellingread 2026-09-27

    Group values within an edit distance, by Levenshtein distance

Talend (1)
  • tGenKeyread 2026-09-27

    Build a functional key per row by applying key algorithms to columns, for blocking and matching

Trifacta (1)
  • Standardize columnread 2026-09-27

    Cluster similar values of one column and map each cluster to a standard value

Background

In OpenRefine, clustering finds groups of different values that might be alternative representations of the same thing.[1] In OpenRefine, the user picks one existing value for each cluster, or types a new one, to apply to all the cells in that cluster.[2] Data cleansing may involve harmonisation, so that all rows in a column have their data expressed in a single format or following the same convention.[3]

When to use it

In OpenRefine, clustering works only at the syntactic level, the characters a cell value is made of, and not on what the value means.[1] In OpenRefine, clustering is very useful for spotting errors, typos and inconsistencies, but by no means enough for effective semantically aware reconciliation.[1] Reach for it when a column that should hold a short list of names or categories holds more distinct values than it should, and many of them look like misspellings of one another. (Meridian's view) In OpenRefine, clustering always requires the user to approve each suggested edit, and only the merges the user approves modify the data.[2]

In OpenRefine's fingerprint method, "gödel" and "godél" both end up with the fingerprint "godel", though they are likely to be different names.[1] In Tableau Prep, grouping by Pronunciation, Common Characters or Spelling replaces the values in each group with the value that occurs most frequently in the group.[4] In OpenRefine, the value first suggested for a cluster is the cluster's first value once its values are sorted by how often each occurs.[1] Decide, and write down, the rule that picks the surviving spelling, such as the most common one with ties broken alphabetically, so the same input always merges the same way. (Meridian's view)

The method

In OpenRefine, key collision methods create a key holding only the most meaningful part of each value, and bucket together the values whose keys are the same.[1] OpenRefine's fingerprint key trims the value, lowercases it, removes punctuation and control characters, turns extended western characters into ASCII, then sorts the words and removes duplicates.[1] Because OpenRefine's fingerprint sorts the words, "Cruise, Tom" and "Tom Cruise" both end up with the fingerprint "cruise tom".[1] In OpenRefine, a phonetic fingerprint encodes how words are pronounced, so that similar-sounding words share a key and end up in the same cluster.[1] In OpenRefine, key collision methods are very fast and can process millions of cells in seconds.[2]

In OpenRefine, nearest neighbour methods take a radius, a distance threshold, and bin together any pair of values closer than it.[1] In OpenRefine, nearest neighbour clustering first groups values into blocks that share a substring, six characters long by default, and compares values only within a block.[1] The Levenshtein distance is the minimum number of single-symbol insertions, deletions or substitutions required to transform one string into the other.[5] In Tableau Prep, a data role can be used to match invalid values to the most similar valid value, based on spelling and pronunciation.[4]

Compared with the alternatives

Record linkage, also known as entity resolution, is the task of finding records in a data set that refer to the same entity across different data sources.[6] Most, if not all, record linkage strategies would give more accurate linkage if values were first normalised or standardised into a consistent format.[6] Merging near-duplicate values is that standardisation for one column: run it before a join or a count on the column, so that one thing written two ways is not treated as two. (Meridian's view) OpenRefine uses external semantically aware reconciliation services, such as Wikidata, to make up for what syntax-level clustering alone cannot do.[1]

Sources

  1. [1]Clustering Methods In-depth | OpenRefinedocumentation
  2. [2]Cell editing | OpenRefinedocumentation
  3. [3]Data cleansingsecondary source
  4. [4]Clean and Shape Data in Tableau Prep - Tableaudocumentation
  5. [5]Levenshtein distancesecondary source
  6. [6]Record linkagesecondary source