Untangling messy records with `set_group_id()`

Clustering candidate matches across rows — graphfast does the linkage legwork in one call

library(graphfast)
library(data.table)
set.seed(1)

Note

This packages uses the graphfast package. Visit repo →

The problem

Real-world data is full of records that might be duplicates without looking like it. Three rows with three different names could be the same person signing up three times — or they could just be three people who happen to share a household phone line. A shared phone number, on its own, isn’t proof of identity; it’s a clue. No single column ties all three rows together — but a chain of shared values is enough to flag them as candidates worth linking, and ultimately worth a human or a downstream rule deciding what to do with.

This is exactly the kind of problem graph algorithms are good at, and it’s why set_group_id() exists: treat every row and every value it contains as nodes in a graph, connect a row to each value it holds, and then ask “which nodes end up in the same connected component?” Rows in the same component get the same group id — not a verdict that they’re the same entity, but a cluster of candidate links that a data linkage workflow can build on.

Why it’s quick to use

No graph code, no manual joins — just point it at the columns that might overlap:

dt <- data.table(
  id     = 1:5,
  name   = c("John Smith", "J. Smith", "Jane Doe", "Bob Jones", "Robert Jones"),
  phone  = c("555-0101", "555-0101", "555-0202", "555-0303", "555-0404"),
  email  = c("john@mail.com", "jsmith@mail.com", "jane@mail.com",
             "bob@mail.com", "555-0303")
)
dt

      id         name    phone           email
   <int>       <char>   <char>          <char>
1:     1   John Smith 555-0101   john@mail.com
2:     2     J. Smith 555-0101 jsmith@mail.com
3:     3     Jane Doe 555-0202   jane@mail.com
4:     4    Bob Jones 555-0303    bob@mail.com
5:     5 Robert Jones 555-0404        555-0303

Rows 1 and 2 share a phone number. Row 5 doesn’t share a phone or name with row 4 at all — but its email happens to be the same string as row 4’s phone number. One call surfaces the whole chain of candidates:

set_group_id(dt, cols = "phone|email", var_output_name = "gid")

      id         name    phone           email   gid
   <int>       <char>   <char>          <char> <int>
1:     1   John Smith 555-0101   john@mail.com     1
2:     2     J. Smith 555-0101 jsmith@mail.com     1
3:     3     Jane Doe 555-0202   jane@mail.com     2
4:     4    Bob Jones 555-0303    bob@mail.com     3
5:     5 Robert Jones 555-0404        555-0303     3

Rows 1 and 2 are grouped together (matching phone numbers), and rows 4 and 5 are grouped together (row 5’s stray value matches row 4’s phone), while row 3 (Jane Doe) stays on her own. Grouping isn’t the same as confirming they’re the same person — it’s set_group_id() doing the linkage step of finding which records are worth treating as a unit, so the harder identity question can be answered with that narrowed-down set rather than the whole table. That’s the whole API: a data.table, a column pattern, done.

Isolation: stopping false links across columns

Notice what just happened with row 5 — its email value ("555-0303") is textually identical to row 4’s phone number, so they got linked even though one is “obviously” a phone number and the other is “obviously” an email. That might be exactly what you want (data entry errors do happen), or it might be a false positive you need to prevent.

That’s what isolate is for: it keeps values from different column sets in separate namespaces, so identical text only links rows within the same set.

dt2 <- copy(dt)[, gid := NULL]
set_group_id(dt2, isolate = list("phone", "email"))

      id         name    phone           email   gid
   <int>       <char>   <char>          <char> <int>
1:     1   John Smith 555-0101   john@mail.com     1
2:     2     J. Smith 555-0101 jsmith@mail.com     1
3:     3     Jane Doe 555-0202   jane@mail.com     2
4:     4    Bob Jones 555-0303    bob@mail.com     3
5:     5 Robert Jones 555-0404        555-0303     4

With phone and email isolated from each other, row 5’s stray value no longer connects it to row 4 — the phone-shaped string sitting in the email column is just ignored for grouping purposes. Row 1 and 2 still merge, because that link is a genuine phone match within the same set.

This matters most when you’re matching across many heterogeneous columns (phone1, phone2, alt_phone, email, secondary_email, …) and don’t want a coincidental string collision between unrelated fields to merge two otherwise-unrelated customers.

Seeing the linkage

Because set_group_id() can hand back the record-to-record edge list (every pair of ids that share a value, plus the shared value itself), the result drops straight into visNetwork for a quick visual sanity check:

library(visNetwork)

Warning: package 'visNetwork' was built under R version 4.5.3

dt3 <- copy(dt)[, gid := NULL]
edges <- set_group_id(dt3, isolate = list("phone", "email"), return_edges = TRUE)

nodes <- data.frame(
  id    = dt3$id,
  label = dt3$name,
  group = dt3$gid,
  title = paste0("phone: ", dt3$phone, "<br>email: ", dt3$email)
)
vis_edges <- data.frame(
  from  = edges$from,
  to    = edges$to,
  title = edges$value   # hover shows the value that links the pair
)

visNetwork(nodes, vis_edges, width = "100%", height = "320px") |>
  visNodes(shape = "dot", size = 24) |>
  visOptions(highlightNearest = TRUE) |>
  visLegend()

Two clusters, one isolated node — exactly what the table above said, but now it’s obvious at a glance which shared value pulled which records together (hover an edge to see it).

Takeaways

set_group_id() doesn’t decide that two records are the same entity — it facilitates that decision by doing the graph traversal that finds every record reachable through a chain of shared values, fast enough to run on the whole table instead of pairwise comparisons.

Quick: one function call, no manual graph wrangling, works directly on a data.table by reference.
Flexible matching: a regex (cols) picks up every relevant column at once, so adding a new identifier column doesn’t require new code.
Safe by default, configurable when needed: isolate stops unrelated columns from cross-linking on a coincidental string match, while still letting genuinely shared values (within a set) merge records.
Inspectable: return_edges = TRUE gives you the why behind every group, which is what makes the visNetwork plot above possible — and what lets a reviewer (human or rule-based) judge whether a cluster really is one entity before treating it as such.

See ?set_group_id and the examples page for more patterns, including incomparables for excluding sentinel values like "Unknown" from grouping.