Skip to content

Deterministic dedupe

Linking a dataset of real historical persons with Deterrministic Rules

While Splink is primarily a tool for probabilistic records linkage, it includes functionality to perform deterministic (i.e. rules based) linkage.

Significant work has gone into optimising the performance of rules based matching, so Splink is likely to be significantly faster than writing the basic SQL by hand.

In this example, we deduplicate a 50k row dataset based on historical persons scraped from wikidata. Duplicate records are introduced with a variety of errors introduced. The probabilistic dedupe of the same dataset can be found at Deduplicate 50k rows historical persons.

Open In Colab 

# Uncomment and run this cell if you're running in Google Colab.
# !pip install "splink[altair,igraph,pyarrow]"

from splink import splink_datasets
from splink import DuckDBAPI

df = splink_datasets.historical_50k
db_api = DuckDBAPI()
df_sdf = db_api.register(df)
df_sdf.as_duckdbpyrelation().limit(5).show(max_width=10000)

┌────────────┬──────────┬──────────────────────────────────────────────────┬───────────────────┬────────────┬───────────┬────────────┬─────────────┬───────────────┬─────────┬────────────┐
│ unique_id  │ cluster  │                    full_name                     │ first_and_surname │ first_name │  surname  │    dob     │ birth_place │ postcode_fake │ gender  │ occupation │
│  varchar   │ varchar  │                     varchar                      │      varchar      │  varchar   │  varchar  │  varchar   │   varchar   │    varchar    │ varchar │  varchar   │
├────────────┼──────────┼──────────────────────────────────────────────────┼───────────────────┼────────────┼───────────┼────────────┼─────────────┼───────────────┼─────────┼────────────┤
│ Q2296770-1 │ Q2296770 │ thomas clifford, 1st baron clifford of chudleigh │ thomas chudleigh  │ thomas     │ chudleigh │ 1630-08-01 │ devon       │ tq13 8df      │ male    │ politician │
│ Q2296770-2 │ Q2296770 │ thomas of chudleigh                              │ thomas chudleigh  │ thomas     │ chudleigh │ 1630-08-01 │ devon       │ tq13 8df      │ male    │ politician │
│ Q2296770-3 │ Q2296770 │ tom 1st baron clifford of chudleigh              │ tom chudleigh     │ tom        │ chudleigh │ 1630-08-01 │ devon       │ tq13 8df      │ male    │ politician │
│ Q2296770-4 │ Q2296770 │ thomas 1st chudleigh                             │ thomas chudleigh  │ thomas     │ chudleigh │ 1630-08-01 │ devon       │ tq13 8hu      │ NULL    │ politician │
│ Q2296770-5 │ Q2296770 │ thomas clifford, 1st baron chudleigh             │ thomas chudleigh  │ thomas     │ chudleigh │ 1630-08-01 │ devon       │ tq13 8df      │ NULL    │ politician │
└────────────┴──────────┴──────────────────────────────────────────────────┴───────────────────┴────────────┴───────────┴────────────┴─────────────┴───────────────┴─────────┴────────────┘

When defining the settings object, specity your deterministic rules in the blocking_rules_to_generate_predictions key.

For a deterministic linkage, the linkage methodology is based solely on these rules, so there is no need to define comparisons nor any other parameters required for model training in a probabilistic model.

Prior to running the linkage, it's usually a good idea to check how many record comparisons will be generated by your deterministic rules:

from splink import DuckDBAPI, block_on
from splink.blocking_analysis import (
    chart_comparisons_from_blocking_rules,
)


chart_comparisons_from_blocking_rules(
    df_sdf,
    blocking_rules=[
        block_on("first_name", "surname", "dob"),
        block_on("surname", "dob", "postcode_fake"),
        block_on("first_name", "dob", "occupation"),
    ],
    link_type="dedupe_only",
    record_sample_proportion=0.2,
)

/home/runner/work/splink/splink/splink/internals/blocking_analysis.py:668: UserWarning: The sampled blocking analysis estimate for blocking rule '(l."surname" = r."surname") AND (l."dob" = r."dob") AND (l."postcode_fake" = r."postcode_fake")' is based on 345 sampled pairwise comparisons. This is below the recommended minimum of 1,000, so the estimate may be unstable. Increase record_sample_proportion for a more stable estimate.
  return _cumulative_comparisons_to_be_scored_from_blocking_rules(
/home/runner/work/splink/splink/splink/internals/blocking_analysis.py:668: UserWarning: The sampled blocking analysis estimate for blocking rule '(l."first_name" = r."first_name") AND (l."dob" = r."dob") AND (l."occupation" = r."occupation")' is based on 163 sampled pairwise comparisons. This is below the recommended minimum of 1,000, so the estimate may be unstable. Increase record_sample_proportion for a more stable estimate.
  return _cumulative_comparisons_to_be_scored_from_blocking_rules(

from splink import Linker, SettingsCreator

settings = SettingsCreator(
    link_type="dedupe_only",
    blocking_rules_to_generate_predictions=[
        block_on("first_name", "surname", "dob"),
        block_on("surname", "dob", "postcode_fake"),
        block_on("first_name", "dob", "occupation"),
    ],
    retain_intermediate_calculation_columns=True,
)

linker = Linker(df_sdf, settings)

The results of the linkage can be viewed with the deterministic_link function.

df_predict = linker.inference.deterministic_link()
df_predict.as_duckdbpyrelation().limit(5).show(max_width=10000)

Blocking time: 0.10 seconds


┌─────────────┬─────────────┬─────────────────┬─────────────────┬────────────┬────────────┬──────────────┬──────────────┬──────────────┬──────────────┬────────────────┬────────────────┬───────────┐
│ unique_id_l │ unique_id_r │ postcode_fake_l │ postcode_fake_r │   dob_l    │   dob_r    │ occupation_l │ occupation_r │ first_name_l │ first_name_r │   surname_l    │   surname_r    │ match_key │
│   varchar   │   varchar   │     varchar     │     varchar     │  varchar   │  varchar   │   varchar    │   varchar    │   varchar    │   varchar    │    varchar     │    varchar     │  varchar  │
├─────────────┼─────────────┼─────────────────┼─────────────────┼────────────┼────────────┼──────────────┼──────────────┼──────────────┼──────────────┼────────────────┼────────────────┼───────────┤
│ Q7927021-1  │ Q7927021-9  │ hp5 2ju         │ hp5 2ju         │ 1862-09-23 │ 1862-09-23 │ NULL         │ NULL         │ victoria     │ victoria     │ sackville-west │ sackville-west │ 0         │
│ Q7927021-2  │ Q7927021-9  │ hp5 2ju         │ hp5 2ju         │ 1862-09-23 │ 1862-09-23 │ NULL         │ NULL         │ victoria     │ victoria     │ sackville-west │ sackville-west │ 0         │
│ Q7927021-3  │ Q7927021-9  │ hp5 2ju         │ hp5 2ju         │ 1862-09-23 │ 1862-09-23 │ NULL         │ NULL         │ victoria     │ victoria     │ sackville-west │ sackville-west │ 0         │
│ Q7927021-5  │ Q7927021-9  │ hp5 2ju         │ hp5 2ju         │ 1862-09-23 │ 1862-09-23 │ NULL         │ NULL         │ victoria     │ victoria     │ sackville-west │ sackville-west │ 0         │
│ Q7927021-7  │ Q7927021-9  │ hp5 2ju         │ hp5 2ju         │ 1862-09-23 │ 1862-09-23 │ NULL         │ NULL         │ victoria     │ victoria     │ sackville-west │ sackville-west │ 0         │
└─────────────┴─────────────┴─────────────────┴─────────────────┴────────────┴────────────┴──────────────┴──────────────┴──────────────┴──────────────┴────────────────┴────────────────┴───────────┘

Which can be used to generate clusters.

Note, for deterministic linkage, each comparison has been assigned a match probability of 1, so to generate clusters, set threshold_match_probability=1 in the cluster_pairwise_predictions_at_threshold function.

clusters = linker.clustering.cluster_pairwise_predictions_at_threshold(
    df_predict
)

Completed iteration 1, num edges remaining to process: 2260


Completed iteration 2, num edges remaining to process: 64


Completed iteration 3, num edges remaining to process: 0

clusters.as_duckdbpyrelation().limit(5).show(max_width=10000)

┌─────────────┬─────────────┬───────────┬─────────────────────────────────────┬───────────────────┬────────────┬───────────┬────────────┬─────────────┬───────────────┬─────────┬────────────┐
│ cluster_id  │  unique_id  │  cluster  │              full_name              │ first_and_surname │ first_name │  surname  │    dob     │ birth_place │ postcode_fake │ gender  │ occupation │
│   varchar   │   varchar   │  varchar  │               varchar               │      varchar      │  varchar   │  varchar  │  varchar   │   varchar   │    varchar    │ varchar │  varchar   │
├─────────────┼─────────────┼───────────┼─────────────────────────────────────┼───────────────────┼────────────┼───────────┼────────────┼─────────────┼───────────────┼─────────┼────────────┤
│ Q2296770-11 │ Q2296770-11 │ Q2296770  │ thomas clifford, clifford chydleigh │ thomas chydleigh  │ thomas     │ chydleigh │ 1630-08-11 │ devon       │ tq13 8jr      │ male    │ NULL       │
│ Q90404618-5 │ Q90404618-5 │ Q90404618 │ emlie bennet                        │ emlie bennet      │ emlie      │ bennet    │ NULL       │ NULL        │ wr11 8qp      │ NULL    │ NULL       │
│ Q18672658-4 │ Q18672658-4 │ Q18672658 │ thomas sharp                        │ thomas sharp      │ thomas     │ sharp     │ 1780-01-01 │ trafford    │ m32 0ej       │ male    │ NULL       │
│ Q7793204-10 │ Q7793204-10 │ Q7793204  │ tom pollard                         │ tom pollard       │ tom        │ pollard   │ NULL       │ NULL        │ ba11 1bb      │ male    │ actor      │
│ Q5015070-14 │ Q5015070-14 │ Q5015070  │ clarence john leumane               │ clarence leumane  │ clarence   │ leumane   │ 1815-01-01 │ NULL        │ al7 4ef       │ male    │ NULL       │
└─────────────┴─────────────┴───────────┴─────────────────────────────────────┴───────────────────┴────────────┴───────────┴────────────┴─────────────┴───────────────┴─────────┴────────────┘

These results can then be passed into the Cluster Studio Dashboard.

linker.visualisations.cluster_studio_dashboard(
    df_predict,
    clusters,
    "dashboards/50k_deterministic_cluster.html",
    sampling_method="by_cluster_size",
    overwrite=True,
)

from IPython.display import IFrame

IFrame(src="./dashboards/50k_deterministic_cluster.html", width="100%", height=1200)