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 import splink_datasets
from splink import DuckDBAPI
db_api = DuckDBAPI()
df = splink_datasets.historical_50k
df_sdf = db_api.register(df)
df_sdf.as_duckdbpyrelation().limit(5).show(max_width=10000)
from splink.exploratory import profile_columns
profile_columns(df_sdf, column_expressions=["first_name", "substr(surname,1,2)"])
from splink import DuckDBAPI, block_on
from splink.blocking_analysis import (
chart_comparisons_from_blocking_rules,
)
blocking_rules = [
block_on("substr(first_name,1,3)", "substr(surname,1,4)"),
block_on("surname", "dob"),
block_on("first_name", "dob"),
block_on("postcode_fake", "first_name"),
block_on("postcode_fake", "surname"),
block_on("dob", "birth_place"),
block_on("substr(postcode_fake,1,3)", "dob"),
block_on("substr(postcode_fake,1,3)", "first_name"),
block_on("substr(postcode_fake,1,3)", "surname"),
block_on("substr(first_name,1,2)", "substr(surname,1,2)", "substr(dob,1,4)"),
]
chart_comparisons_from_blocking_rules(
df_sdf,
blocking_rules=blocking_rules,
link_type="dedupe_only",
record_sample_proportion=0.5,
)
import splink.comparison_library as cl
from splink import Linker, SettingsCreator
settings = SettingsCreator(
link_type="dedupe_only",
blocking_rules_to_generate_predictions=blocking_rules,
comparisons=[
cl.ForenameSurnameComparison(
"first_name",
"surname",
forename_surname_concat_col_name="first_name_surname_concat",
),
cl.DateOfBirthComparison(
"dob", input_is_string=True
),
cl.PostcodeComparison("postcode_fake"),
cl.ExactMatch("birth_place").configure(term_frequency_adjustments=True),
cl.ExactMatch("occupation").configure(term_frequency_adjustments=True),
],
retain_intermediate_calculation_columns=True,
)
# Needed to apply term frequencies to first+surname comparison
df = (
db_api.duckdb_con.from_arrow(df)
.project("*, first_name || ' ' || surname as first_name_surname_concat")
)
df_sdf = db_api.register(df)
linker = Linker(df_sdf, settings)
linker.training.estimate_probability_two_random_records_match(
[
block_on("first_name", "surname", "dob"),
block_on("substr(first_name,1,2)", "surname", "substr(postcode_fake,1,2)"),
block_on("dob", "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.training.estimate_u_using_random_sampling(max_pairs=1e7)
----- Estimating u probabilities using random sampling -----
Estimating u with: max_pairs = 10,000,000, min_count_per_level = 100, num_chunks = 10
Estimating u for: first_name_surname (Comparison 1 of 5)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 0 for comparison level Match on reversed cols: first_name and surname (both directions) (cvv=5)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 0 for level Match on reversed cols: first_name and surname (both directions) (cvv=5). Chunk took 0.4 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 0 for level Match on reversed cols: first_name and surname (both directions) (cvv=5). Chunk took 0.6 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 0 for level Match on reversed cols: first_name and surname (both directions) (cvv=5). Chunk took 0.4 seconds.
Min u_count not hit, continuing.
Running chunk 4/10
Count of 0 for level Match on reversed cols: first_name and surname (both directions) (cvv=5). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 5/10
Count of 0 for level Match on reversed cols: first_name and surname (both directions) (cvv=5). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 6/10
Count of 0 for level Match on reversed cols: first_name and surname (both directions) (cvv=5). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 7/10
Count of 0 for level Match on reversed cols: first_name and surname (both directions) (cvv=5). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 8/10
Count of 0 for level Match on reversed cols: first_name and surname (both directions) (cvv=5). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 9/10
Count of 0 for level Match on reversed cols: first_name and surname (both directions) (cvv=5). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 10/10
Count of 0 for level Match on reversed cols: first_name and surname (both directions) (cvv=5). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Estimating u for: dob (Comparison 2 of 5)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 54 for comparison level Exact match on date of birth (cvv=5)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 1,154 for level Exact match on date of birth (cvv=5). Chunk took 0.6 seconds.
Exiting early since min count of 1,154 exceeds min_count_per_level = 100
Estimating u for: postcode_fake (Comparison 3 of 5)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 5 for comparison level Exact match on full postcode (cvv=4)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 101 for level Exact match on full postcode (cvv=4). Chunk took 0.6 seconds.
Exiting early since min count of 101 exceeds min_count_per_level = 100
Estimating u for: birth_place (Comparison 4 of 5)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 494 for comparison level Exact match on birth_place (cvv=1)
Exiting early since min count of 494 exceeds min_count_per_level = 100
Estimating u for: occupation (Comparison 5 of 5)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 1,233 for comparison level Exact match on occupation (cvv=1)
Exiting early since min count of 1,233 exceeds min_count_per_level = 100
Estimated u probabilities using random sampling
Your model is not yet fully trained. Missing estimates for:
- first_name_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 = block_on("first_name", "surname")
training_session_names = (
linker.training.estimate_parameters_using_expectation_maximisation(
training_blocking_rule, estimate_without_term_frequencies=True
)
)
----- Starting EM training session -----
[EM sampling] max_pairs is None — no sampling will be applied
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.246 in probability_two_random_records_match
Iteration 2: Largest change in params was 0.0926 in probability_two_random_records_match
Iteration 3: Largest change in params was -0.023 in the m_probability of birth_place, level `Exact match on birth_place`
Iteration 4: Largest change in params was 0.00916 in the m_probability of birth_place, level `All other comparisons`
Iteration 5: Largest change in params was -0.00422 in the m_probability of birth_place, level `Exact match on birth_place`
Iteration 6: Largest change in params was -0.00229 in the m_probability of birth_place, level `Exact match on birth_place`
Iteration 7: Largest change in params was 0.00151 in the m_probability of dob, level `Abs date difference <= 10 year`
Iteration 8: Largest change in params was 0.000992 in the m_probability of dob, level `Abs date difference <= 10 year`
Iteration 9: Largest change in params was 0.000643 in the m_probability of dob, level `Abs date difference <= 10 year`
Iteration 10: Largest change in params was 0.000413 in the m_probability of dob, level `Abs date difference <= 10 year`
Iteration 11: Largest change in params was 0.000265 in the m_probability of dob, level `Abs date difference <= 10 year`
Iteration 12: Largest change in params was 0.000169 in the m_probability of dob, level `Abs date difference <= 10 year`
Iteration 13: Largest change in params was 0.000108 in the m_probability of dob, level `Abs date difference <= 10 year`
Iteration 14: Largest change in params was 6.9e-05 in the m_probability of dob, level `Abs date difference <= 10 year`
EM converged after 14 iterations
Your model is not yet fully trained. Missing estimates for:
- first_name_surname (some u values are not trained, no m values are trained).
training_blocking_rule = block_on("dob")
training_session_dob = (
linker.training.estimate_parameters_using_expectation_maximisation(
training_blocking_rule, estimate_without_term_frequencies=True
)
)
----- Starting EM training session -----
[EM sampling] max_pairs is None — no sampling will be applied
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.471 in the m_probability of first_name_surname, level `Exact match on first_name_surname_concat`
Iteration 2: Largest change in params was 0.0505 in the m_probability of first_name_surname, level `All other comparisons`
Iteration 3: Largest change in params was 0.0173 in the m_probability of first_name_surname, level `All other comparisons`
Iteration 4: Largest change in params was 0.00538 in the m_probability of first_name_surname, level `All other comparisons`
Iteration 5: Largest change in params was 0.00168 in the m_probability of first_name_surname, level `All other comparisons`
Iteration 6: Largest change in params was 0.000528 in the m_probability of first_name_surname, level `All other comparisons`
Iteration 7: Largest change in params was 0.000168 in the m_probability of first_name_surname, level `All other comparisons`
Iteration 8: Largest change in params was 5.35e-05 in the m_probability of first_name_surname, level `All other comparisons`
EM converged after 8 iterations
Your model is not yet fully trained. Missing estimates for:
- first_name_surname (some u 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_predict.as_duckdbpyrelation().limit(5).show(max_width=10000)
Blocking time: 0.24 seconds
Predict time (post-blocking): 0.94 seconds
-- 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 parameters will use default values.
Comparison: 'first_name_surname':
u values not fully trained
┌────────────────────┬────────────────────┬─────────────┬─────────────┬──────────────┬──────────────┬───────────┬───────────┬─────────────────────────────┬─────────────────────────────┬──────────────────────────┬────────────────────────────────┬────────────────────────────────┬────────────────────────┬────────────────────────┬──────────────────────┬───────────────────────┬───────────────────────┬──────────────────────────────┬────────────┬────────────┬───────────┬───────────────────┬─────────────────┬─────────────────┬─────────────────────┬───────────────────┬──────────────────┬──────────────────┬───────────────────┬───────────────────────┬───────────────────────┬────────────────────┬───────────────────────┬──────────────┬──────────────┬──────────────────┬─────────────────────┬─────────────────────┬───────────────────┬──────────────────────┬───────────┐
│ match_weight │ match_probability │ unique_id_l │ unique_id_r │ first_name_l │ first_name_r │ surname_l │ surname_r │ first_name_surname_concat_l │ first_name_surname_concat_r │ gamma_first_name_surname │ tf_first_name_surname_concat_l │ tf_first_name_surname_concat_r │ tf_surname_l │ tf_surname_r │ tf_first_name_l │ tf_first_name_r │ mw_first_name_surname │ mw_tf_adj_first_name_surname │ dob_l │ dob_r │ gamma_dob │ mw_dob │ postcode_fake_l │ postcode_fake_r │ gamma_postcode_fake │ mw_postcode_fake │ birth_place_l │ birth_place_r │ gamma_birth_place │ tf_birth_place_l │ tf_birth_place_r │ mw_birth_place │ mw_tf_adj_birth_place │ occupation_l │ occupation_r │ gamma_occupation │ tf_occupation_l │ tf_occupation_r │ mw_occupation │ mw_tf_adj_occupation │ match_key │
│ double │ double │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ int32 │ double │ double │ double │ double │ double │ double │ double │ double │ varchar │ varchar │ int32 │ double │ varchar │ varchar │ int32 │ double │ varchar │ varchar │ int32 │ double │ double │ double │ double │ varchar │ varchar │ int32 │ double │ double │ double │ double │ varchar │
├────────────────────┼────────────────────┼─────────────┼─────────────┼──────────────┼──────────────┼───────────┼───────────┼─────────────────────────────┼─────────────────────────────┼──────────────────────────┼────────────────────────────────┼────────────────────────────────┼────────────────────────┼────────────────────────┼──────────────────────┼───────────────────────┼───────────────────────┼──────────────────────────────┼────────────┼────────────┼───────────┼───────────────────┼─────────────────┼─────────────────┼─────────────────────┼───────────────────┼──────────────────┼──────────────────┼───────────────────┼───────────────────────┼───────────────────────┼────────────────────┼───────────────────────┼──────────────┼──────────────┼──────────────────┼─────────────────────┼─────────────────────┼───────────────────┼──────────────────────┼───────────┤
│ 8.996790940242771 │ 0.9980463499493436 │ Q16066528-5 │ Q16066528-9 │ henry │ harry │ gallop │ gallop │ henry gallop │ harry gallop │ 2 │ 8.683759199357402e-05 │ 6.512819399518052e-05 │ 0.00017367518398714803 │ 0.00017367518398714803 │ 0.025855754192156164 │ 0.009859238581695077 │ 8.943695972235918 │ 1.0286768553492696 │ 1857-07-21 │ NULL │ -1 │ 0.0 │ bs6 5tl │ bs6 5tl │ 4 │ 11.87016482011893 │ NULL │ bristol │ -1 │ NULL │ 0.0038716180614418914 │ 0.0 │ 0.0 │ cricketer │ NULL │ -1 │ 0.121001225829412 │ NULL │ 0.0 │ 0.0 │ 4 │
│ 23.33577961897056 │ 0.9999999055438322 │ Q6281574-10 │ Q6281574-12 │ joe │ joseph │ bloore │ bloore │ joe bloore │ joseph bloore │ 2 │ 4.341879599678701e-05 │ 4.341879599678701e-05 │ 8.683759199357402e-05 │ 8.683759199357402e-05 │ 0.003464591871077587 │ 0.010116608263546554 │ 8.943695972235918 │ 2.0286768553492696 │ 1789-01-01 │ 1789-01-11 │ 4 │ 3.639670553205556 │ st18 0le │ st18 0le │ 4 │ 11.87016482011893 │ staffordshire │ staffordshire │ 1 │ 0.0010079952349316167 │ 0.0010079952349316167 │ 6.903974685538014 │ 2.7953434399842187 │ NULL │ NULL │ -1 │ NULL │ NULL │ 0.0 │ 0.0 │ 4 │
│ 11.882480918727428 │ 0.999735209860485 │ Q8005845-1 │ Q8005845-17 │ william │ 2illiam │ bradshaw │ bradshaw │ william bradshaw │ 2illiam bradshaw │ 3 │ 0.00019538458198554154 │ 2.1709397998393504e-05 │ 0.0006729913379501986 │ 0.0006729913379501986 │ 0.055037516580546814 │ 5.939300350418721e-05 │ 11.399252033448624 │ 0.0 │ 1700-01-01 │ NULL │ -1 │ 0.0 │ tq13 8pn │ tq13 8pn │ 4 │ 11.87016482011893 │ teignbridge │ moretonhampstead │ 0 │ 0.0022679892785961377 │ 6.872694783624659e-05 │ -2.615806229990718 │ 0.0 │ writer │ writer │ 1 │ 0.05326426509549607 │ 0.05326426509549607 │ 4.390904568006546 │ -0.3162875653946058 │ 4 │
│ 28.07207439215652 │ 0.999999996456246 │ Q8005845-10 │ Q8005845-17 │ william │ 2illiam │ bradshaw │ bradshaw │ william bradshaw │ 2illiam bradshaw │ 3 │ 0.00019538458198554154 │ 2.1709397998393504e-05 │ 0.0006729913379501986 │ 0.0006729913379501986 │ 0.055037516580546814 │ 5.939300350418721e-05 │ 11.399252033448624 │ 0.0 │ 1701-01-01 │ NULL │ -1 │ 0.0 │ tq13 8pn │ tq13 8pn │ 4 │ 11.87016482011893 │ moretonhampstead │ moretonhampstead │ 1 │ 6.872694783624659e-05 │ 6.872694783624659e-05 │ 6.903974685538014 │ 6.669812557900359 │ writer │ writer │ 1 │ 0.05326426509549607 │ 0.05326426509549607 │ 4.390904568006546 │ -0.3162875653946058 │ 4 │
│ 20.616381873294266 │ 0.9999993779140628 │ Q8005845-11 │ Q8005845-17 │ willie │ 2illiam │ bradshaw │ bradshaw │ willie bradshaw │ 2illiam bradshaw │ 2 │ 4.341879599678701e-05 │ 2.1709397998393504e-05 │ 0.0006729913379501986 │ 0.0006729913379501986 │ 0.010077012927877096 │ 5.939300350418721e-05 │ 8.943695972235918 │ -0.9255194550376071 │ 1700-01-01 │ NULL │ -1 │ 0.0 │ tq13 8pn │ tq13 8pn │ 4 │ 11.87016482011893 │ moretonhampstead │ moretonhampstead │ 1 │ 6.872694783624659e-05 │ 6.872694783624659e-05 │ 6.903974685538014 │ 6.669812557900359 │ NULL │ writer │ -1 │ NULL │ 0.05326426509549607 │ 0.0 │ 0.0 │ 4 │
└────────────────────┴────────────────────┴─────────────┴─────────────┴──────────────┴──────────────┴───────────┴───────────┴─────────────────────────────┴─────────────────────────────┴──────────────────────────┴────────────────────────────────┴────────────────────────────────┴────────────────────────┴────────────────────────┴──────────────────────┴───────────────────────┴───────────────────────┴──────────────────────────────┴────────────┴────────────┴───────────┴───────────────────┴─────────────────┴─────────────────┴─────────────────────┴───────────────────┴──────────────────┴──────────────────┴───────────────────┴───────────────────────┴───────────────────────┴────────────────────┴───────────────────────┴──────────────┴──────────────┴──────────────────┴─────────────────────┴─────────────────────┴───────────────────┴──────────────────────┴───────────┘
You can also view rows in this dataset as a waterfall chart as follows:
records_to_plot = df_predict.as_record_list(limit=5)
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, num edges remaining to process: 17544
Completed iteration 2, num edges remaining to process: 2270
Completed iteration 3, num edges remaining to process: 418
Completed iteration 4, num edges remaining to process: 188
Completed iteration 5, num edges remaining to process: 0
from IPython.display import IFrame
linker.visualisations.cluster_studio_dashboard(
df_predict,
clusters,
"dashboards/50k_cluster.html",
sampling_method="by_cluster_size",
overwrite=True,
)
IFrame(src="./dashboards/50k_cluster.html", width="100%", height=1200)
linker.evaluation.accuracy_analysis_from_labels_column(
"cluster", output_type="accuracy", match_weight_round_to_nearest=0.02
)
Blocking time: 0.47 seconds
Predict time (post-blocking): 1.07 seconds
-- 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 parameters will use default values.
Comparison: 'first_name_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_list()
linker.visualisations.waterfall_chart(records)
Blocking time: 0.63 seconds
Predict time (post-blocking): 0.93 seconds
-- 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 parameters will use default values.
Comparison: 'first_name_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_list(limit=50)
linker.visualisations.waterfall_chart(records)
Blocking time: 0.60 seconds
Predict time (post-blocking): 1.57 seconds
-- 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 parameters will use default values.
Comparison: 'first_name_surname':
u values not fully trained