Febrl3 Dedupe
Deduplicating the febrl3 dataset¶
See A.2 here and here for the source of this data
from splink.datasets import splink_datasets
from splink.internals.misc import show
from splink import DuckDBAPI
import duckdb
df = splink_datasets.febrl3
df = df.rename_columns([column.strip() for column in df.column_names])
con = duckdb.connect()
df = con.from_arrow(df)
df = con.sql(
"""
select
* replace (
trim(cast(date_of_birth as varchar)) as date_of_birth,
trim(cast(soc_sec_id as varchar)) as soc_sec_id
),
regexp_extract(rec_id, '^([^-]+-[^-]+)', 1) as cluster
from df
"""
)
db_api = DuckDBAPI(connection=con)
df_sdf = db_api.register(df)
It's usually a good idea to perform exploratory analysis on your data so you understand what's in each column and how often it's missing:
from splink.exploratory import completeness_chart
completeness_chart(df_sdf)
from splink.exploratory import profile_columns
profile_columns(df_sdf, column_expressions=["given_name", "surname"])
from splink import DuckDBAPI, block_on
from splink.blocking_analysis import (
chart_comparisons_from_blocking_rules,
)
blocking_rules = [
block_on("soc_sec_id"),
block_on("given_name"),
block_on("surname"),
block_on("date_of_birth"),
block_on("postcode"),
]
chart_comparisons_from_blocking_rules(
df_sdf,
blocking_rules=blocking_rules,
link_type="dedupe_only",
unique_id_column_name="rec_id",
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."soc_sec_id" = r."soc_sec_id"' is based on 216 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."date_of_birth" = r."date_of_birth"' is based on 307 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."postcode" = r."postcode"' is based on 459 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(
import splink.comparison_library as cl
from splink import Linker, SettingsCreator
settings = SettingsCreator(
unique_id_column_name="rec_id",
link_type="dedupe_only",
blocking_rules_to_generate_predictions=blocking_rules,
comparisons=[
cl.NameComparison("given_name"),
cl.NameComparison("surname"),
cl.DateOfBirthComparison(
"date_of_birth",
input_is_string=True,
datetime_format="%Y%m%d",
),
cl.DamerauLevenshteinAtThresholds("soc_sec_id", [2]),
cl.ExactMatch("street_number").configure(term_frequency_adjustments=True),
cl.ExactMatch("postcode").configure(term_frequency_adjustments=True),
],
retain_intermediate_calculation_columns=True,
)
db_api = DuckDBAPI(connection=con)
df_sdf = db_api.register(df)
linker = Linker(df_sdf, settings)
from splink import block_on
deterministic_rules = [
block_on("soc_sec_id"),
block_on("given_name", "surname", "date_of_birth"),
"l.given_name = r.surname and l.surname = r.given_name and l.date_of_birth = r.date_of_birth",
]
linker.training.estimate_probability_two_random_records_match(
deterministic_rules, recall=0.9
)
Probability two random records match is estimated to be 0.000528.
This means that amongst all possible pairwise record comparisons, one in 1,893.56 are expected to match. With 12,497,500 total possible comparisons, we expect a total of around 6,600.00 matching pairs
linker.training.estimate_u_using_random_sampling(max_pairs=1e6, seed=2)
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 -----
Estimating u with: max_pairs = 1,000,000, min_count_per_level = 100, num_chunks = 10
Estimating u for: given_name (Comparison 1 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 20 for comparison level Jaro-Winkler distance of given_name >= 0.88 (cvv=2)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 175 for level Jaro-Winkler distance of given_name >= 0.92 (cvv=3). Chunk took 0.1 seconds.
Exiting early since min count of 175 exceeds min_count_per_level = 100
Estimating u for: surname (Comparison 2 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 4 for comparison level Jaro-Winkler distance of surname >= 0.88 (cvv=2)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 40 for level Jaro-Winkler distance of surname >= 0.88 (cvv=2). Chunk took 0.2 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 91 for level Jaro-Winkler distance of surname >= 0.88 (cvv=2). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 137 for level Jaro-Winkler distance of surname >= 0.88 (cvv=2). Chunk took 0.1 seconds.
Exiting early since min count of 137 exceeds min_count_per_level = 100
Estimating u for: date_of_birth (Comparison 3 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 6 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 44 for level Exact match on date of birth (cvv=5). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 72 for level Exact match on date of birth (cvv=5). Chunk took 0.2 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 125 for level Exact match on date of birth (cvv=5). Chunk took 0.3 seconds.
Exiting early since min count of 125 exceeds min_count_per_level = 100
Estimating u for: soc_sec_id (Comparison 4 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 3 for comparison level Damerau-Levenshtein distance of soc_sec_id <= 2 (cvv=1)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 27 for level Damerau-Levenshtein distance of soc_sec_id <= 2 (cvv=1). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 57 for level Damerau-Levenshtein distance of soc_sec_id <= 2 (cvv=1). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 84 for level Damerau-Levenshtein distance of soc_sec_id <= 2 (cvv=1). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 4/10
Count of 113 for level Damerau-Levenshtein distance of soc_sec_id <= 2 (cvv=1). Chunk took 0.3 seconds.
Exiting early since min count of 113 exceeds min_count_per_level = 100
Estimating u for: street_number (Comparison 5 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 154 for comparison level Exact match on street_number (cvv=1)
Exiting early since min count of 154 exceeds min_count_per_level = 100
Estimating u for: postcode (Comparison 6 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 12 for comparison level Exact match on postcode (cvv=1)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 127 for level Exact match on postcode (cvv=1). Chunk took 0.0 seconds.
Exiting early since min count of 127 exceeds min_count_per_level = 100
Estimated u probabilities using random sampling
Your model is not yet fully trained. Missing estimates for:
- given_name (no m values are trained).
- surname (no m values are trained).
- date_of_birth (no m values are trained).
- soc_sec_id (no m values are trained).
- street_number (no m values are trained).
- postcode (no m values are trained).
em_blocking_rule_1 = block_on("date_of_birth")
session_dob = linker.training.estimate_parameters_using_expectation_maximisation(
em_blocking_rule_1
)
----- 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."date_of_birth" = r."date_of_birth"
Parameter estimates will be made for the following comparison(s):
- given_name
- surname
- soc_sec_id
- street_number
- postcode
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- date_of_birth
Iteration 1: Largest change in params was -0.377 in the m_probability of surname, level `Exact match on surname`
Iteration 2: Largest change in params was 0.0153 in the m_probability of surname, level `All other comparisons`
Iteration 3: Largest change in params was -0.000682 in the m_probability of postcode, level `Exact match on postcode`
Iteration 4: Largest change in params was 3.65e-05 in the m_probability of postcode, level `All other comparisons`
EM converged after 4 iterations
Your model is not yet fully trained. Missing estimates for:
- date_of_birth (no m values are trained).
em_blocking_rule_2 = block_on("postcode")
session_postcode = linker.training.estimate_parameters_using_expectation_maximisation(
em_blocking_rule_2
)
----- 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."postcode" = r."postcode"
Parameter estimates will be made for the following comparison(s):
- given_name
- surname
- date_of_birth
- soc_sec_id
- street_number
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- postcode
Iteration 1: Largest change in params was 0.0809 in probability_two_random_records_match
Iteration 2: Largest change in params was 0.0017 in the m_probability of date_of_birth, level `All other comparisons`
Iteration 3: Largest change in params was 5.5e-05 in the m_probability of date_of_birth, 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
linker.visualisations.match_weights_chart()
results = linker.inference.predict(threshold_match_probability=0.2)
Blocking time: 0.09 seconds
Predict time (post-blocking): 0.65 seconds
linker.evaluation.accuracy_analysis_from_labels_column(
"cluster", match_weight_round_to_nearest=0.1, output_type="accuracy"
)
Blocking time: 0.06 seconds
Predict time (post-blocking): 0.64 seconds
pred_errors_df = linker.evaluation.prediction_errors_from_labels_column(
"cluster"
)
print(len(pred_errors_df.as_record_list()))
pred_errors_df.as_duckdbpyrelation().limit(5).show(max_width=10000)
Blocking time: 0.04 seconds
Predict time (post-blocking): 0.92 seconds
95
┌──────────────────────┬─────────────────────────┬─────────────────────┬───────────────────────┬────────────────┬────────────────┬──────────────┬──────────────┬──────────────────┬─────────────────┬─────────────────┬─────────────────────┬──────────────────────┬───────────┬───────────┬───────────────┬──────────────┬──────────────┬─────────────────────┬───────────────────┬─────────────────┬─────────────────┬─────────────────────┬─────────────────────┬──────────────┬──────────────┬──────────────────┬────────────────────┬─────────────────┬─────────────────┬─────────────────────┬────────────────────┬────────────────────┬─────────────────────┬─────────────────────────┬────────────┬────────────┬────────────────┬───────────────┬───────────────┬─────────────────────┬─────────────────────┬───────────┬───────────┬───────────┐
│ clerical_match_score │ found_by_blocking_rules │ match_weight │ match_probability │ rec_id_l │ rec_id_r │ given_name_l │ given_name_r │ gamma_given_name │ tf_given_name_l │ tf_given_name_r │ mw_given_name │ mw_tf_adj_given_name │ surname_l │ surname_r │ gamma_surname │ tf_surname_l │ tf_surname_r │ mw_surname │ mw_tf_adj_surname │ date_of_birth_l │ date_of_birth_r │ gamma_date_of_birth │ mw_date_of_birth │ soc_sec_id_l │ soc_sec_id_r │ gamma_soc_sec_id │ mw_soc_sec_id │ street_number_l │ street_number_r │ gamma_street_number │ tf_street_number_l │ tf_street_number_r │ mw_street_number │ mw_tf_adj_street_number │ postcode_l │ postcode_r │ gamma_postcode │ tf_postcode_l │ tf_postcode_r │ mw_postcode │ mw_tf_adj_postcode │ cluster_l │ cluster_r │ match_key │
│ double │ boolean │ double │ double │ varchar │ varchar │ varchar │ varchar │ int32 │ double │ double │ double │ double │ varchar │ varchar │ int32 │ double │ double │ double │ double │ varchar │ varchar │ int32 │ double │ varchar │ varchar │ int32 │ double │ varchar │ varchar │ int32 │ double │ double │ double │ double │ int64 │ int64 │ int32 │ double │ double │ double │ double │ varchar │ varchar │ varchar │
├──────────────────────┼─────────────────────────┼─────────────────────┼───────────────────────┼────────────────┼────────────────┼──────────────┼──────────────┼──────────────────┼─────────────────┼─────────────────┼─────────────────────┼──────────────────────┼───────────┼───────────┼───────────────┼──────────────┼──────────────┼─────────────────────┼───────────────────┼─────────────────┼─────────────────┼─────────────────────┼─────────────────────┼──────────────┼──────────────┼──────────────────┼────────────────────┼─────────────────┼─────────────────┼─────────────────────┼────────────────────┼────────────────────┼─────────────────────┼─────────────────────────┼────────────┼────────────┼────────────────┼───────────────┼───────────────┼─────────────────────┼─────────────────────┼───────────┼───────────┼───────────┤
│ 1.0 │ true │ -2.752250838264325 │ 0.1292377879703772 │ rec-1490-dup-0 │ rec-1490-dup-1 │ sonja │ jake │ 0 │ 0.0008 │ 0.0046 │ -2.1637957458790504 │ 0.0 │ wilknis │ wilkins │ 3 │ 0.0004 │ 0.0018 │ 7.574494133862688 │ 0.0 │ 19741205 │ 19340404 │ 0 │ -3.9992918344774924 │ 2867046 │ 2867046 │ 2 │ 10.925649006466864 │ 1024 │ 1042 │ 0 │ 0.0004 │ 0.0002 │ -2.0842124700423654 │ 0.0 │ 2768 │ 2760 │ 0 │ 0.0012 │ 0.0004 │ -2.1189701427073038 │ 0.0 │ rec-1490 │ rec-1490 │ 0 │
│ 1.0 │ true │ -8.781418920289337 │ 0.0022674808500982406 │ rec-1083-dup-0 │ rec-1083-dup-1 │ bloomfield │ jaisen │ 0 │ 0.0004 │ 0.0002 │ -2.1637957458790504 │ 0.0 │ jaiden │ glosetr │ 0 │ 0.0006 │ 0.0002 │ -2.4539657826398154 │ 0.0 │ │ │ -1 │ 0.0 │ 7038120 │ 7038120 │ 2 │ 10.925649006466864 │ 14 │ 3 │ 0 │ 0.013 │ 0.0318 │ -2.0842124700423654 │ 0.0 │ 5276 │ 2576 │ 0 │ 0.0006 │ 0.0016 │ -2.1189701427073038 │ 0.0 │ rec-1083 │ rec-1083 │ 0 │
│ 1.0 │ true │ -8.781418920289337 │ 0.0022674808500982406 │ rec-785-dup-0 │ rec-785-dup-4 │ callum │ jankus │ 0 │ 0.0044 │ 0.0002 │ -2.1637957458790504 │ 0.0 │ januks │ callum │ 0 │ 0.0002 │ 0.0002 │ -2.4539657826398154 │ 0.0 │ 19930925 │ 19903925 │ -1 │ 0.0 │ 5175345 │ 5175345 │ 2 │ 10.925649006466864 │ 132 │ 67 │ 0 │ 0.001 │ 0.0028 │ -2.0842124700423654 │ 0.0 │ 3178 │ 3187 │ 0 │ 0.003 │ 0.0012 │ -2.1189701427073038 │ 0.0 │ rec-785 │ rec-785 │ 0 │
│ 1.0 │ true │ -3.59660180219847 │ 0.07635217076402025 │ rec-304-dup-1 │ rec-304-dup-2 │ sebretgs │ james │ 0 │ 0.0002 │ 0.009 │ -2.1637957458790504 │ 0.0 │ james │ sebregts │ 0 │ 0.0004 │ 0.0008 │ -2.4539657826398154 │ 0.0 │ 19040404 │ 19151102 │ 0 │ -3.9992918344774924 │ 5342259 │ 5342259 │ 2 │ 10.925649006466864 │ 30 │ 30 │ 1 │ 0.0056 │ 0.0056 │ 5.495019122974949 │ 1.6048773595510442 │ 3303 │ 3300 │ 0 │ 0.0002 │ 0.0008 │ -2.1189701427073038 │ 0.0 │ rec-304 │ rec-304 │ 0 │
│ 1.0 │ true │ -2.5582518184605423 │ 0.1451392411455069 │ rec-66-dup-1 │ rec-66-org │ morrison │ max │ 0 │ 0.001 │ 0.0018 │ -2.1637957458790504 │ 0.0 │ max │ morrison │ 0 │ 0.0004 │ 0.0088 │ -2.4539657826398154 │ 0.0 │ 19961120 │ 19810825 │ 0 │ -3.9992918344774924 │ 6404764 │ 6404764 │ 2 │ 10.925649006466864 │ │ 30 │ 0 │ 0.049 │ 0.0056 │ -2.0842124700423654 │ 0.0 │ 2024 │ 2024 │ 1 │ 0.0028 │ 0.0028 │ 9.120731758544922 │ -1.0172429649459396 │ rec-66 │ rec-66 │ 0 │
└──────────────────────┴─────────────────────────┴─────────────────────┴───────────────────────┴────────────────┴────────────────┴──────────────┴──────────────┴──────────────────┴─────────────────┴─────────────────┴─────────────────────┴──────────────────────┴───────────┴───────────┴───────────────┴──────────────┴──────────────┴─────────────────────┴───────────────────┴─────────────────┴─────────────────┴─────────────────────┴─────────────────────┴──────────────┴──────────────┴──────────────────┴────────────────────┴─────────────────┴─────────────────┴─────────────────────┴────────────────────┴────────────────────┴─────────────────────┴─────────────────────────┴────────────┴────────────┴────────────────┴───────────────┴───────────────┴─────────────────────┴─────────────────────┴───────────┴───────────┴───────────┘
The following chart seems to suggest that, where the model is making errors, it's because the data is corrupted beyond recognition and no reasonable linkage model could find these matches
records = linker.evaluation.prediction_errors_from_labels_column(
"cluster"
).as_record_list(limit=10)
linker.visualisations.waterfall_chart(records)
Blocking time: 0.05 seconds
Predict time (post-blocking): 0.90 seconds