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

from splink.datasets import splink_datasets
from splink.sqlite.linker import SQLiteLinker
import altair as alt

import pandas as pd 
pd.options.display.max_rows = 1000
df = splink_datasets.historical_50k.sample(10000) # reduce size of dataset to reduce CI runtime

# Simple settings dictionary will be used for exploratory analysis
settings = {
    "link_type": "dedupe_only",
    "blocking_rules_to_generate_predictions": [
        "l.first_name = r.first_name and l.surname = r.surname",
        "l.surname = r.surname and l.dob = r.dob",
        "l.first_name = r.first_name and l.dob = r.dob",
        "l.postcode_fake = r.postcode_fake and l.first_name = r.first_name",
    ],
}
linker = SQLiteLinker(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.sqlite.comparison_template_library as ctl
import splink.sqlite.comparison_library as cl

settings = {
    "link_type": "dedupe_only",
    "blocking_rules_to_generate_predictions": [
        "l.first_name = r.first_name and l.surname = r.surname",
        "l.surname = r.surname and l.dob = r.dob",
        "l.first_name = r.first_name and l.dob = r.dob",
        "l.postcode_fake = r.postcode_fake and l.first_name = r.first_name",
    ],
    "comparisons": [
        ctl.name_comparison("first_name", jaro_winkler_thresholds=[0.9], term_frequency_adjustments=True),
        ctl.name_comparison("surname", jaro_winkler_thresholds=[0.9], term_frequency_adjustments=True),
        cl.damerau_levenshtein_at_thresholds("dob", [1, 2], term_frequency_adjustments=True),
        cl.damerau_levenshtein_at_thresholds("postcode_fake", [1,2]),
        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 = SQLiteLinker(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.000144.
This means that amongst all possible pairwise record comparisons, one in 6,955.02 are expected to match.  With 49,995,000 total possible comparisons, we expect a total of around 7,188.33 matching pairs

linker.estimate_u_using_random_sampling(max_pairs=5e6)

----- Estimating u probabilities using random sampling -----



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 = "l.first_name = r.first_name and l.surname = r.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.533 in probability_two_random_records_match
Iteration 2: Largest change in params was -0.047 in probability_two_random_records_match
Iteration 3: Largest change in params was 0.0181 in the m_probability of birth_place, level `Exact match`
Iteration 4: Largest change in params was 0.00608 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 = "l.dob = r.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

Iteration 1: Largest change in params was -0.353 in the m_probability of first_name, level `Exact match first_name`
Iteration 2: Largest change in params was 0.0376 in the m_probability of first_name, level `All other comparisons`
Iteration 3: Largest change in params was 0.00562 in the m_probability of postcode_fake, 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

	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	25.908286	1.000000	Q7422529-2	Q7422529-3	sarah	sarah	3	0.001603	0.001603	46.376176	...	160.817163	3.188489	None	None	-1	NaN	None	1.0	1.0
1	2.815419	0.875610	Q334210-11	Q334210-13	reginald	reginald	3	0.002304	0.002304	46.376176	...	0.170301	1.000000	None	None	-1	NaN	None	1.0	1.0
2	19.086449	0.999998	Q334210-11	Q334210-2	reginald	reginald	3	0.002304	0.002304	46.376176	...	0.170301	1.000000	None	None	-1	NaN	None	1.0	1.0
3	24.895924	1.000000	Q334210-11	Q334210-9	reginald	reginald	3	0.002304	0.002304	46.376176	...	0.170301	1.000000	None	None	-1	NaN	None	1.0	1.0
4	11.088896	0.999541	Q6233309-3	Q6233309-4	john	john	3	0.051598	0.051598	46.376176	...	0.170301	1.000000	politician	None	-1	0.09218	None	1.0	1.0

5 rows × 44 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 27
Completed iteration 2, root rows count 1
Completed iteration 3, 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)

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

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

linker.waterfall_chart(records)