Febrl3 Dedupe
Deduplicating the febrl3 dataset¶
See A.2 here and here for the source of this data
import pandas as pd
import altair as alt
from splink.datasets import splink_datasets
df = splink_datasets.febrl3
df = df.rename(columns=lambda x: x.strip())
df["cluster"] = df["rec_id"].apply(lambda x: "-".join(x.split('-')[:2]))
# dob and ssn needs to be a string for fuzzy comparisons like levenshtein to be applied
df["date_of_birth"] = df["date_of_birth"].astype(str).str.strip()
df["date_of_birth"] = df["date_of_birth"].replace("", None)
df["soc_sec_id"] = df["soc_sec_id"].astype(str).str.strip()
df["soc_sec_id"] = df["soc_sec_id"].replace("", None)
df["postcode"] = df["postcode"].astype(str).str.strip()
df["postcode"] = df["postcode"].replace("", None)
df.head(2)
rec_id | given_name | surname | street_number | address_1 | address_2 | suburb | postcode | state | date_of_birth | soc_sec_id | cluster | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | rec-1496-org | mitchell | green | 7 | wallaby place | delmar | cleveland | 2119 | sa | 19560409 | 1804974 | rec-1496 |
1 | rec-552-dup-3 | harley | mccarthy | 177 | pridhamstreet | milton | marsden | 3165 | nsw | 19080419 | 6089216 | rec-552 |
from splink.duckdb.linker import DuckDBLinker
settings = {
"unique_id_column_name": "rec_id",
"link_type": "dedupe_only",
}
linker = DuckDBLinker(df, settings)
linker.missingness_chart()
linker.profile_columns(list(df.columns))
from splink.duckdb.blocking_rule_library import block_on
blocking_rules = [
block_on("soc_sec_id"),
block_on("given_name"),
block_on("surname"),
block_on("date_of_birth"),
block_on("postcode"),
]
linker.cumulative_num_comparisons_from_blocking_rules_chart(blocking_rules)
from splink.duckdb.linker import DuckDBLinker
import splink.duckdb.comparison_library as cl
import splink.duckdb.comparison_template_library as ctl
settings = {
"unique_id_column_name": "rec_id",
"link_type": "dedupe_only",
"blocking_rules_to_generate_predictions": blocking_rules,
"comparisons": [
ctl.name_comparison("given_name", term_frequency_adjustments=True),
ctl.name_comparison("surname", term_frequency_adjustments=True),
ctl.date_comparison("date_of_birth",
damerau_levenshtein_thresholds=[],
cast_strings_to_date=True,
invalid_dates_as_null=True,
date_format="%Y%m%d"),
cl.levenshtein_at_thresholds("soc_sec_id", [2]),
cl.exact_match("street_number", term_frequency_adjustments=True),
cl.exact_match("postcode", term_frequency_adjustments=True),
],
"retain_intermediate_calculation_columns": True,
}
linker = DuckDBLinker(df, settings)
deterministic_rules = [
"l.soc_sec_id = r.soc_sec_id",
"l.given_name = r.given_name and l.surname = r.surname and l.date_of_birth = r.date_of_birth",
"l.given_name = r.surname and l.surname = r.given_name and l.date_of_birth = r.date_of_birth"
]
linker.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.estimate_u_using_random_sampling(max_pairs=1e6)
----- 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:
- 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("substr(date_of_birth,1,3)")
em_blocking_rule_2 = block_on("substr(postcode,1,2)")
session_dob = linker.estimate_parameters_using_expectation_maximisation(em_blocking_rule_1)
session_postcode = linker.estimate_parameters_using_expectation_maximisation(em_blocking_rule_2)
----- Starting EM training session -----
Estimating the m probabilities of the model by blocking on:
SUBSTR(l."date_of_birth", 1, 3) = SUBSTR(r."date_of_birth", 1, 3)
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
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Iteration 1: Largest change in params was -0.508 in probability_two_random_records_match
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Iteration 2: Largest change in params was -0.0388 in the m_probability of soc_sec_id, level `All other comparisons`
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Iteration 3: Largest change in params was -0.00602 in the m_probability of soc_sec_id, level `All other comparisons`
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Iteration 4: Largest change in params was -0.000955 in the m_probability of soc_sec_id, level `All other comparisons`
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Iteration 5: Largest change in params was -0.000155 in the m_probability of soc_sec_id, level `All other comparisons`
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Iteration 6: Largest change in params was -2.55e-05 in the m_probability of soc_sec_id, level `All other comparisons`
EM converged after 6 iterations
Your model is not yet fully trained. Missing estimates for:
- date_of_birth (no m values are trained).
----- Starting EM training session -----
Estimating the m probabilities of the model by blocking on:
SUBSTR(l."postcode", 1, 2) = SUBSTR(r."postcode", 1, 2)
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
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Iteration 1: Largest change in params was -0.227 in probability_two_random_records_match
Iteration 2: Largest change in params was -0.0159 in the m_probability of soc_sec_id, level `All other comparisons`
Iteration 3: Largest change in params was -0.001 in the m_probability of soc_sec_id, level `All other comparisons`
Iteration 4: Largest change in params was -7.04e-05 in the m_probability of soc_sec_id, level `All other comparisons`
EM converged after 4 iterations
Your model is fully trained. All comparisons have at least one estimate for their m and u values
linker.match_weights_chart()
results = linker.predict(threshold_match_probability=0.2)
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
linker.roc_chart_from_labels_column("cluster")
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))