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.

Note, as explained in the backends topic guide, SQLite does not natively support string fuzzy matching functions such as damareau-levenshtein and jaro-winkler (as used in this example). Instead, these have been imported as python User Defined Functions (UDFs). One drawback of python UDFs is that they are considerably slower than native-SQL comparisons. As such, if you are hitting issues with large run times, consider switching to DuckDB (or some other backend).

# Uncomment and run this cell if you're running in Google Colab.
# !pip install splink
# !pip install rapidfuzz

import pandas as pd

from splink import splink_datasets

pd.options.display.max_rows = 1000
# reduce size of dataset to make things run faster
df = splink_datasets.historical_50k.sample(5000)

from splink.backends.sqlite import SQLiteAPI
from splink.exploratory import profile_columns

db_api = SQLiteAPI()
profile_columns(
    df, db_api, column_expressions=["first_name", "postcode_fake", "substr(dob, 1,4)"]
)

from splink import block_on
from splink.blocking_analysis import (
    cumulative_comparisons_to_be_scored_from_blocking_rules_chart,
)

blocking_rules =  [block_on("first_name", "surname"),
        block_on("surname", "dob"),
        block_on("first_name", "dob"),
        block_on("postcode_fake", "first_name")]

db_api = SQLiteAPI()

cumulative_comparisons_to_be_scored_from_blocking_rules_chart(
    table_or_tables=df,
    blocking_rules=blocking_rules,
    db_api=db_api,
    link_type="dedupe_only"
)

import splink.comparison_library as cl
from splink import Linker

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": [
        cl.NameComparison("first_name"),
        cl.NameComparison("surname"),
        cl.DamerauLevenshteinAtThresholds("dob", [1, 2]).configure(
            term_frequency_adjustments=True
        ),
        cl.DamerauLevenshteinAtThresholds("postcode_fake", [1, 2]),
        cl.ExactMatch("birth_place").configure(term_frequency_adjustments=True),
        cl.ExactMatch(
            "occupation",
        ).configure(term_frequency_adjustments=True),
    ],
    "retain_matching_columns": True,
    "retain_intermediate_calculation_columns": True,
    "max_iterations": 10,
    "em_convergence": 0.01,
}

linker = Linker(df, settings, db_api=db_api)

linker.training.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.000125.
This means that amongst all possible pairwise record comparisons, one in 7,985.62 are expected to match.  With 12,497,500 total possible comparisons, we expect a total of around 1,565.00 matching pairs

linker.training.estimate_u_using_random_sampling(max_pairs=1e6)

You are using the default value for `max_pairs`, which may be too small and thus lead to inaccurate estimates for your model's u-parameters. Consider increasing to 1e8 or 1e9, which will result in more accurate estimates, but with a longer run time.
----- Estimating u probabilities using random sampling -----
u probability not trained for first_name - Jaro-Winkler distance of first_name >= 0.88 (comparison vector value: 2). This usually means the comparison level was never observed in the training data.
u probability not trained for surname - Jaro-Winkler distance of surname >= 0.88 (comparison vector value: 2). This usually means the comparison level was never observed in the training data.

Estimated u probabilities using random sampling

Your model is not yet fully trained. Missing estimates for:
    - first_name (some u values are not trained, no m values are trained).
    - surname (some u values are not trained, 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 = "l.first_name = r.first_name and l.surname = r.surname"
training_session_names = linker.training.estimate_parameters_using_expectation_maximisation(
    training_blocking_rule, estimate_without_term_frequencies=True
)

----- 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.438 in probability_two_random_records_match
Iteration 2: Largest change in params was -0.0347 in probability_two_random_records_match
Iteration 3: Largest change in params was -0.0126 in the m_probability of birth_place, level `All other comparisons`
Iteration 4: Largest change in params was 0.00644 in the m_probability of birth_place, level `Exact match on birth_place`

EM converged after 4 iterations

Your model is not yet fully trained. Missing estimates for:
    - first_name (some u values are not trained, no m values are trained).
    - surname (some u values are not trained, no m values are trained).

training_blocking_rule = "l.dob = r.dob"
training_session_dob = linker.training.estimate_parameters_using_expectation_maximisation(
    training_blocking_rule, estimate_without_term_frequencies=True
)

----- 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

WARNING:
Level Jaro-Winkler distance of first_name >= 0.88 on comparison first_name not observed in dataset, unable to train m value

WARNING:
Level Jaro-Winkler distance of surname >= 0.88 on comparison surname not observed in dataset, unable to train m value

Iteration 1: Largest change in params was 0.327 in the m_probability of first_name, level `All other comparisons`
Iteration 2: Largest change in params was -0.0566 in the m_probability of surname, level `Exact match on surname`
Iteration 3: Largest change in params was -0.0184 in the m_probability of surname, level `Exact match on surname`
Iteration 4: Largest change in params was -0.006 in the m_probability of surname, level `Exact match on surname`

EM converged after 4 iterations
m probability not trained for first_name - Jaro-Winkler distance of first_name >= 0.88 (comparison vector value: 2). This usually means the comparison level was never observed in the training data.
m probability not trained for surname - Jaro-Winkler distance of surname >= 0.88 (comparison vector value: 2). This usually means the comparison level was never observed in the training data.

Your model is not yet fully trained. Missing estimates for:
    - first_name (some u values are not trained, some m values are not trained).
    - surname (some u values are not trained, some m values are not trained).

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

linker.visualisations.match_weights_chart()

linker.evaluation.unlinkables_chart()

df_predict = linker.inference.predict()
df_e = df_predict.as_pandas_dataframe(limit=5)
df_e

 -- WARNING --
You have called predict(), but there are some parameter estimates which have neither been estimated or specified in your settings dictionary.  To produce predictions the following untrained trained parameters will use default values.
Comparison: 'first_name':
    m values not fully trained
Comparison: 'first_name':
    u values not fully trained
Comparison: 'surname':
    m values not fully trained
Comparison: 'surname':
    u values not fully trained

	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
0	26.932083	1.000000	Q446382-1	Q446382-3	marianne	marianne	4	0.000801	0.000801	51.871289	...	0.162366	1.000000	None	None	-1	NaN	NaN	1.000000	1.000000
1	30.788800	1.000000	Q2835078-1	Q2835078-2	alfred	alfred	4	0.013622	0.013622	51.871289	...	197.452526	0.607559	None	None	-1	NaN	NaN	1.000000	1.000000
2	23.882340	1.000000	Q2835078-1	Q2835078-5	alfred	alfred	4	0.013622	0.013622	51.871289	...	1.000000	1.000000	None	None	-1	NaN	NaN	1.000000	1.000000
3	39.932187	1.000000	Q80158702-1	Q80158702-4	john	john	4	0.053085	0.053085	51.871289	...	197.452526	2.025198	sculptor	sculptor	1	0.002769	0.002769	23.836781	13.868019
4	17.042339	0.999993	Q18810722-3	Q18810722-6	frederick	frederick	4	0.012220	0.012220	51.871289	...	197.452526	0.607559	printer	printer	1	0.000791	0.000791	23.836781	48.538067

5 rows × 44 columns

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

records_to_plot = df_e.to_dict(orient="records")
linker.visualisations.waterfall_chart(records_to_plot, filter_nulls=False)

clusters = linker.clustering.cluster_pairwise_predictions_at_threshold(
    df_predict, threshold_match_probability=0.95
)

Completed iteration 1, root rows count 5
Completed iteration 2, root rows count 0

linker.visualisations.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.evaluation.accuracy_analysis_from_labels_column(
    "cluster", output_type="roc", match_weight_round_to_nearest=0.02
)

 -- WARNING --
You have called predict(), but there are some parameter estimates which have neither been estimated or specified in your settings dictionary.  To produce predictions the following untrained trained parameters will use default values.
Comparison: 'first_name':
    m values not fully trained
Comparison: 'first_name':
    u values not fully trained
Comparison: 'surname':
    m values not fully trained
Comparison: 'surname':
    u values not fully trained

records = linker.evaluation.prediction_errors_from_labels_column(
    "cluster",
    threshold_match_probability=0.999,
    include_false_negatives=False,
    include_false_positives=True,
).as_record_dict()
linker.visualisations.waterfall_chart(records)

 -- WARNING --
You have called predict(), but there are some parameter estimates which have neither been estimated or specified in your settings dictionary.  To produce predictions the following untrained trained parameters will use default values.
Comparison: 'first_name':
    m values not fully trained
Comparison: 'first_name':
    u values not fully trained
Comparison: 'surname':
    m values not fully trained
Comparison: 'surname':
    u values not fully trained

# Some of the false negatives will be because they weren't detected by the blocking rules
records = linker.evaluation.prediction_errors_from_labels_column(
    "cluster",
    threshold_match_probability=0.5,
    include_false_negatives=True,
    include_false_positives=False,
).as_record_dict(limit=50)

linker.visualisations.waterfall_chart(records)

 -- WARNING --
You have called predict(), but there are some parameter estimates which have neither been estimated or specified in your settings dictionary.  To produce predictions the following untrained trained parameters will use default values.
Comparison: 'first_name':
    m values not fully trained
Comparison: 'first_name':
    u values not fully trained
Comparison: 'surname':
    m values not fully trained
Comparison: 'surname':
    u values not fully trained