Skip to content

Deduplicate 50k rows historical persons

Linking a dataset of real historical persons¶

In this example, we deduplicate a more realistic dataset. The data is based on historical persons scraped from wikidata. Duplicate records are introduced with a variety of errors introduced.

from splink.datasets import splink_datasets
from splink.duckdb.linker import DuckDBLinker
import altair as alt

import pandas as pd 
pd.options.display.max_rows = 1000
df = splink_datasets.historical_50k
from splink.duckdb.blocking_rule_library import block_on

# Simple settings dictionary will be used for exploratory analysis
settings = {
    "link_type": "dedupe_only",
    "blocking_rules_to_generate_predictions": [
        block_on(["first_name", "surname"]),
        block_on(["surname", "dob"]),
        block_on(["first_name", "dob"]),
        block_on(["postcode_fake", "first_name"]),
    ],
}
linker = DuckDBLinker(df, settings)

linker.profile_columns(
    ["first_name", "postcode_fake", "substr(dob, 1,4)"], top_n=10, bottom_n=5
)
linker.cumulative_num_comparisons_from_blocking_rules_chart()
import splink.duckdb.comparison_template_library as ctl
import splink.duckdb.comparison_library as cl

settings = {
    "link_type": "dedupe_only",
    "blocking_rules_to_generate_predictions": [
        block_on(["first_name", "surname"]),
        block_on(["surname", "dob"]),
        block_on(["first_name", "dob"]),
        block_on(["postcode_fake", "first_name"]),
    ],
    "comparisons": [
        ctl.name_comparison("first_name", term_frequency_adjustments=True),
        ctl.name_comparison("surname", term_frequency_adjustments=True),
        ctl.date_comparison("dob", cast_strings_to_date=True, invalid_dates_as_null=True),
        ctl.postcode_comparison("postcode_fake"),
        cl.exact_match("birth_place", term_frequency_adjustments=True),
        cl.exact_match("occupation",  term_frequency_adjustments=True),
    ],
    "retain_matching_columns": True,
    "retain_intermediate_calculation_columns": True,
    "max_iterations": 10,
    "em_convergence": 0.01
}

linker = DuckDBLinker(df, settings)
linker.estimate_probability_two_random_records_match(
    [
        "l.first_name = r.first_name and l.surname = r.surname and l.dob = r.dob",
        "substr(l.first_name,1,2) = substr(r.first_name,1,2) and l.surname = r.surname and substr(l.postcode_fake,1,2) = substr(r.postcode_fake,1,2)",
        "l.dob = r.dob and l.postcode_fake = r.postcode_fake",
    ],
    recall=0.6,
)
Probability two random records match is estimated to be  0.000136.
This means that amongst all possible pairwise record comparisons, one in 7,362.31 are expected to match.  With 1,279,041,753 total possible comparisons, we expect a total of around 173,728.33 matching pairs
linker.estimate_u_using_random_sampling(max_pairs=5e6)
----- Estimating u probabilities using random sampling -----



FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))



Estimated u probabilities using random sampling

Your model is not yet fully trained. Missing estimates for:
    - first_name (no m values are trained).
    - surname (no m values are trained).
    - dob (no m values are trained).
    - postcode_fake (no m values are trained).
    - birth_place (no m values are trained).
    - occupation (no m values are trained).
training_blocking_rule = block_on(["first_name", "surname"])
training_session_names = linker.estimate_parameters_using_expectation_maximisation(training_blocking_rule)
----- Starting EM training session -----

Estimating the m probabilities of the model by blocking on:
(l."first_name" = r."first_name") AND (l."surname" = r."surname")

Parameter estimates will be made for the following comparison(s):
    - dob
    - postcode_fake
    - birth_place
    - occupation

Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules: 
    - first_name
    - surname

Iteration 1: Largest change in params was -0.52 in probability_two_random_records_match
Iteration 2: Largest change in params was -0.0446 in the m_probability of birth_place, level `All other comparisons`
Iteration 3: Largest change in params was -0.0185 in the m_probability of birth_place, level `All other comparisons`
Iteration 4: Largest change in params was 0.00717 in the m_probability of birth_place, level `Exact match`

EM converged after 4 iterations

Your model is not yet fully trained. Missing estimates for:
    - first_name (no m values are trained).
    - surname (no m values are trained).
training_blocking_rule = block_on("dob")
training_session_dob = linker.estimate_parameters_using_expectation_maximisation(training_blocking_rule)
----- Starting EM training session -----

Estimating the m probabilities of the model by blocking on:
l."dob" = r."dob"

Parameter estimates will be made for the following comparison(s):
    - first_name
    - surname
    - postcode_fake
    - birth_place
    - occupation

Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules: 
    - dob



FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))



Iteration 1: Largest change in params was -0.362 in the m_probability of first_name, level `Exact match first_name`
Iteration 2: Largest change in params was 0.0426 in the m_probability of first_name, level `All other comparisons`
Iteration 3: Largest change in params was 0.00794 in the m_probability of surname, level `All other comparisons`

EM converged after 3 iterations

Your model is fully trained. All comparisons have at least one estimate for their m and u values

The final match weights can be viewed in the match weights chart:

linker.match_weights_chart()
linker.unlinkables_chart()
df_predict = linker.predict()
df_e = df_predict.as_pandas_dataframe(limit=5)
df_e
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
match_weight match_probability unique_id_l unique_id_r first_name_l first_name_r gamma_first_name tf_first_name_l tf_first_name_r bf_first_name ... bf_birth_place bf_tf_adj_birth_place occupation_l occupation_r gamma_occupation tf_occupation_l tf_occupation_r bf_occupation bf_tf_adj_occupation match_key
0 12.731320 0.999853 Q2296770-1 Q2296770-14 thomas thomas 4 0.028667 0.028667 43.19637 ... 1.000000 1.0 politician politician 1 0.088932 0.088932 25.927656 0.388868 0
1 21.634289 1.000000 Q2296770-3 Q2296770-7 tom tom 4 0.012948 0.012948 43.19637 ... 0.159229 1.0 politician NaN -1 0.088932 NaN 1.000000 1.000000 0
2 9.131145 0.998220 Q2296770-10 Q2296770-14 thomas thomas 4 0.028667 0.028667 43.19637 ... 1.000000 1.0 politician politician 1 0.088932 0.088932 25.927656 0.388868 0
3 13.443772 0.999910 Q2296770-13 Q2296770-7 tom tom 4 0.012948 0.012948 43.19637 ... 0.159229 1.0 politician NaN -1 0.088932 NaN 1.000000 1.000000 0
4 35.771776 1.000000 Q1443188-1 Q1443188-3 frank frank 4 0.006335 0.006335 43.19637 ... 0.159229 1.0 liturgist liturgist 1 0.000237 0.000237 25.927656 145.760504 0

5 rows × 41 columns

You can also view rows in this dataset as a waterfall chart as follows:

from splink.charts import waterfall_chart
records_to_plot = df_e.to_dict(orient="records")
linker.waterfall_chart(records_to_plot, filter_nulls=False)
clusters = linker.cluster_pairwise_predictions_at_threshold(df_predict, threshold_match_probability=0.95)
Completed iteration 1, root rows count 671
Completed iteration 2, root rows count 147
Completed iteration 3, root rows count 43
Completed iteration 4, root rows count 12
Completed iteration 5, root rows count 3
Completed iteration 6, root rows count 2
Completed iteration 7, root rows count 1
Completed iteration 8, root rows count 0
linker.cluster_studio_dashboard(df_predict, clusters, "dashboards/50k_cluster.html", sampling_method='by_cluster_size', overwrite=True)

from IPython.display import IFrame

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

linker.roc_chart_from_labels_column("cluster",match_weight_round_to_nearest=0.02)
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))