Cookbook¶
This notebook contains a miscellaneous collection of runnable examples illustrating various Splink techniques.
Array columns¶
Comparing array columns¶
This example shows how we can use use ArrayIntersectAtSizes to assess the similarity of columns containing arrays.
import splink.comparison_library as cl
from splink import DuckDBAPI, Linker, SettingsCreator, block_on
data = [
{"unique_id": 1, "first_name": "John", "postcode": ["A", "B"]},
{"unique_id": 2, "first_name": "John", "postcode": ["B"]},
{"unique_id": 3, "first_name": "John", "postcode": ["A"]},
{"unique_id": 4, "first_name": "John", "postcode": ["A", "B"]},
{"unique_id": 5, "first_name": "John", "postcode": ["C"]},
]
df = data
settings = SettingsCreator(
link_type="dedupe_only",
blocking_rules_to_generate_predictions=[
block_on("first_name"),
],
comparisons=[
cl.ArrayIntersectAtSizes("postcode", [2, 1]),
cl.ExactMatch("first_name"),
]
)
db_api = DuckDBAPI()
df_sdf = db_api.register(df)
linker = Linker(df_sdf, settings, log_level=None)
linker.inference.predict().as_duckdbpyrelation().show(max_width=10000)
┌─────────────────────┬──────────────────────┬─────────────┬─────────────┬────────────┬────────────┬────────────────┬──────────────┬──────────────┬──────────────────┬───────────┐
│ match_weight │ match_probability │ unique_id_l │ unique_id_r │ postcode_l │ postcode_r │ gamma_postcode │ first_name_l │ first_name_r │ gamma_first_name │ match_key │
│ double │ double │ int64 │ int64 │ varchar[] │ varchar[] │ int32 │ varchar │ varchar │ int32 │ varchar │
├─────────────────────┼──────────────────────┼─────────────┼─────────────┼────────────┼────────────┼────────────────┼──────────────┼──────────────┼──────────────────┼───────────┤
│ -8.287568102831404 │ 0.003190110656963414 │ 1 │ 5 │ [A, B] │ [C] │ 0 │ John │ John │ 1 │ 0 │
│ -8.287568102831404 │ 0.003190110656963414 │ 2 │ 5 │ [B] │ [C] │ 0 │ John │ John │ 1 │ 0 │
│ -8.287568102831404 │ 0.003190110656963414 │ 3 │ 5 │ [A] │ [C] │ 0 │ John │ John │ 1 │ 0 │
│ -8.287568102831404 │ 0.003190110656963414 │ 4 │ 5 │ [A, B] │ [C] │ 0 │ John │ John │ 1 │ 0 │
│ 6.712431897168596 │ 0.9905542828802871 │ 1 │ 4 │ [A, B] │ [A, B] │ 2 │ John │ John │ 1 │ 0 │
│ -0.2875681028314041 │ 0.4503325820460668 │ 2 │ 4 │ [B] │ [A, B] │ 1 │ John │ John │ 1 │ 0 │
│ -0.2875681028314041 │ 0.4503325820460668 │ 3 │ 4 │ [A] │ [A, B] │ 1 │ John │ John │ 1 │ 0 │
│ -0.2875681028314041 │ 0.4503325820460668 │ 1 │ 3 │ [A, B] │ [A] │ 1 │ John │ John │ 1 │ 0 │
│ -8.287568102831404 │ 0.003190110656963414 │ 2 │ 3 │ [B] │ [A] │ 0 │ John │ John │ 1 │ 0 │
│ -0.2875681028314041 │ 0.4503325820460668 │ 1 │ 2 │ [A, B] │ [B] │ 1 │ John │ John │ 1 │ 0 │
└─────────────────────┴──────────────────────┴─────────────┴─────────────┴────────────┴────────────┴────────────────┴──────────────┴──────────────┴──────────────────┴───────────┘
10 rows 11 columns
Blocking on array columns¶
This example shows how we can use block_on to block on the individual elements of an array column - that is, pairwise comaprisons are created for pairs or records where any of the elements in the array columns match.
import splink.comparison_library as cl
from splink import DuckDBAPI, Linker, SettingsCreator, block_on
data = [
{"unique_id": 1, "first_name": "John", "postcode": ["A", "B"]},
{"unique_id": 2, "first_name": "John", "postcode": ["B"]},
{"unique_id": 3, "first_name": "John", "postcode": ["C"]},
]
df = data
settings = SettingsCreator(
link_type="dedupe_only",
blocking_rules_to_generate_predictions=[
block_on("postcode", arrays_to_explode=["postcode"]),
],
comparisons=[
cl.ArrayIntersectAtSizes("postcode", [2, 1]),
cl.ExactMatch("first_name"),
]
)
db_api = DuckDBAPI()
df_sdf = db_api.register(df)
linker = Linker(df_sdf, settings, log_level=None)
linker.inference.predict().as_duckdbpyrelation().show(max_width=10000)
┌─────────────────────┬────────────────────┬─────────────┬─────────────┬────────────┬────────────┬────────────────┬──────────────┬──────────────┬──────────────────┬───────────┐
│ match_weight │ match_probability │ unique_id_l │ unique_id_r │ postcode_l │ postcode_r │ gamma_postcode │ first_name_l │ first_name_r │ gamma_first_name │ match_key │
│ double │ double │ int64 │ int64 │ varchar[] │ varchar[] │ int32 │ varchar │ varchar │ int32 │ varchar │
├─────────────────────┼────────────────────┼─────────────┼─────────────┼────────────┼────────────┼────────────────┼──────────────┼──────────────┼──────────────────┼───────────┤
│ -0.2875681028314041 │ 0.4503325820460668 │ 1 │ 2 │ [A, B] │ [B] │ 1 │ John │ John │ 1 │ 0 │
└─────────────────────┴────────────────────┴─────────────┴─────────────┴────────────┴────────────┴────────────────┴──────────────┴──────────────┴──────────────────┴───────────┘
Other¶
Using DuckDB without pandas¶
In this example, we read data directly using DuckDB and obtain results in native DuckDB DuckDBPyRelation format.
import duckdb
import tempfile
import os
import pyarrow.parquet as pq
import splink.comparison_library as cl
from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets
# Create a parquet file on disk to demontrate native DuckDB parquet reading
df = splink_datasets.fake_1000
temp_file = tempfile.NamedTemporaryFile(delete=True, suffix=".parquet")
temp_file_path = temp_file.name
pq.write_table(df, temp_file_path)
# Example would start here if you already had a parquet file
duckdb_df = duckdb.read_parquet(temp_file_path)
db_api = DuckDBAPI(":default:")
df_sdf = db_api.register(duckdb_df)
settings = SettingsCreator(
link_type="dedupe_only",
comparisons=[
cl.NameComparison("first_name"),
cl.JaroAtThresholds("surname"),
],
blocking_rules_to_generate_predictions=[
block_on("first_name", "dob"),
block_on("surname"),
],
)
linker = Linker(df_sdf, settings, log_level=None)
result = linker.inference.predict().as_duckdbpyrelation()
# Since result is a DuckDBPyRelation, we can use all the usual DuckDB API
# functions on it.
# For example, we can use the `sort` function to sort the results,
# or could use result.to_parquet() to write to a parquet file.
result.sort("match_weight")
┌─────────────────────┬────────────────────────┬─────────────┬─────────────┬──────────────┬──────────────┬──────────────────┬───────────┬───────────┬───────────────┬────────────┬────────────┬───────────┐
│ match_weight │ match_probability │ unique_id_l │ unique_id_r │ first_name_l │ first_name_r │ gamma_first_name │ surname_l │ surname_r │ gamma_surname │ dob_l │ dob_r │ match_key │
│ double │ double │ int64 │ int64 │ varchar │ varchar │ int32 │ varchar │ varchar │ int32 │ date │ date │ varchar │
├─────────────────────┼────────────────────────┼─────────────┼─────────────┼──────────────┼──────────────┼──────────────────┼───────────┼───────────┼───────────────┼────────────┼────────────┼───────────┤
│ -11.83278901894715 │ 0.00027406686429545097 │ 758 │ 760 │ Henry │ Henry │ 4 │ Dy │ Day │ 0 │ 2002-09-15 │ 2002-09-15 │ 0 │
│ -10.247826518225992 │ 0.0008217501639050423 │ 670 │ 671 │ Ollie │ Ollie │ 4 │ Rowe │ Rewo │ 0 │ 2006-12-05 │ 2006-12-05 │ 0 │
│ -9.662864017504836 │ 0.0012321189988629302 │ 558 │ 559 │ Violet │ Violet │ 4 │ alCr │ Clark │ 0 │ 2020-02-11 │ 2020-02-11 │ 0 │
│ -9.47021893956244 │ 0.0014078881864458073 │ 259 │ 260 │ Oliver │ Oliver │ 4 │ Hguehes │ Hughes │ 1 │ 1983-03-07 │ 1983-03-07 │ 0 │
│ -8.47021893956244 │ 0.002811817648042496 │ 644 │ 645 │ Oliver │ Oliver │ 4 │ White │ NULL │ -1 │ 1992-02-06 │ 1992-02-06 │ 0 │
│ -8.287568102831404 │ 0.003190110656963414 │ 5 │ 150 │ Grace │ Alfie │ 0 │ Kelly │ Kelly │ 3 │ 1991-04-26 │ 2020-09-05 │ 1 │
│ -8.287568102831404 │ 0.003190110656963414 │ 19 │ 474 │ Rowe │ Scott │ 0 │ Caleb │ Caleb │ 3 │ 1992-12-20 │ 1990-12-11 │ 1 │
│ -8.287568102831404 │ 0.003190110656963414 │ 20 │ 670 │ Caleb │ Ollie │ 0 │ Rowe │ Rowe │ 3 │ 2003-01-17 │ 2006-12-05 │ 1 │
│ -8.287568102831404 │ 0.003190110656963414 │ 25 │ 356 │ Gabriel │ Jayden │ 0 │ Thomas │ Thomas │ 3 │ 1977-09-13 │ 2009-04-15 │ 1 │
│ -8.287568102831404 │ 0.003190110656963414 │ 31 │ 419 │ Lola │ Florence │ 0 │ Brown │ Brown │ 3 │ 1983-11-06 │ 1992-02-28 │ 1 │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ 5.337135982495164 │ 0.9758593366351408 │ 91 │ 93 │ Moore │ Moore │ 4 │ Oscar │ Oscar │ 3 │ 2016-01-12 │ 2016-01-12 │ 0 │
│ 5.337135982495164 │ 0.9758593366351408 │ 740 │ 742 │ Ahmed │ Ahmed │ 4 │ Oscar │ Oscar │ 3 │ 2005-09-18 │ 2006-09-14 │ 1 │
│ 5.337135982495164 │ 0.9758593366351408 │ 339 │ 340 │ Rose │ Rose │ 4 │ Wood │ Wood │ 3 │ 2004-12-21 │ 2004-12-22 │ 1 │
│ 5.337135982495164 │ 0.9758593366351408 │ 409 │ 411 │ Emily │ Emily │ 4 │ Atkinson │ Atkinson │ 3 │ 2017-05-03 │ 2008-05-05 │ 1 │
│ 5.337135982495164 │ 0.9758593366351408 │ 415 │ 418 │ Brown │ Brown │ 4 │ Florence │ Florence │ 3 │ 2002-02-25 │ 1993-03-01 │ 1 │
│ 5.337135982495164 │ 0.9758593366351408 │ 417 │ 419 │ Florence │ Florence │ 4 │ Brown │ Brown │ 3 │ 2002-02-24 │ 1992-02-28 │ 1 │
│ 5.337135982495164 │ 0.9758593366351408 │ 473 │ 474 │ Scott │ Scott │ 4 │ Caleb │ Caleb │ 3 │ 1990-12-11 │ 1990-12-11 │ 0 │
│ 5.337135982495164 │ 0.9758593366351408 │ 172 │ 174 │ Leah │ Leah │ 4 │ Russell │ Russell │ 3 │ 2012-07-06 │ 2012-07-09 │ 1 │
│ 5.337135982495164 │ 0.9758593366351408 │ 774 │ 778 │ Armstrong │ Armstrong │ 4 │ Eva │ Eva │ 3 │ 2027-04-21 │ 2017-04-23 │ 1 │
│ 5.337135982495164 │ 0.9758593366351408 │ 221 │ 225 │ Ferguson │ Ferguson │ 4 │ Logan │ Logan │ 3 │ 2013-10-15 │ 2013-10-15 │ 0 │
└─────────────────────┴────────────────────────┴─────────────┴─────────────┴──────────────┴──────────────┴──────────────────┴───────────┴───────────┴───────────────┴────────────┴────────────┴───────────┘
1800 rows (20 shown) 13 columns
Fixing m or u probabilities during training¶
import splink.comparison_level_library as cll
import splink.comparison_library as cl
from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets
db_api = DuckDBAPI()
first_name_comparison = cl.CustomComparison(
comparison_levels=[
cll.NullLevel("first_name"),
cll.ExactMatchLevel("first_name").configure(
m_probability=0.9999,
fix_m_probability=True,
u_probability=0.7,
fix_u_probability=True,
),
cll.ElseLevel(),
]
)
settings = SettingsCreator(
link_type="dedupe_only",
comparisons=[
first_name_comparison,
cl.ExactMatch("surname"),
cl.ExactMatch("dob"),
cl.ExactMatch("city"),
],
blocking_rules_to_generate_predictions=[
block_on("first_name"),
block_on("dob"),
],
additional_columns_to_retain=["cluster"],
)
df = splink_datasets.fake_1000
df_sdf = db_api.register(df)
linker = Linker(df_sdf, settings, log_level=None)
linker.training.estimate_u_using_random_sampling(max_pairs=1e6)
linker.training.estimate_parameters_using_expectation_maximisation(block_on("dob"))
linker.visualisations.m_u_parameters_chart()
Manually altering m and u probabilities post-training¶
This is not officially supported, but can be useful for ad-hoc alterations to trained models.
import splink.comparison_level_library as cll
import splink.comparison_library as cl
from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets
from splink.datasets import splink_dataset_labels
labels = splink_dataset_labels.fake_1000_labels
db_api = DuckDBAPI()
settings = SettingsCreator(
link_type="dedupe_only",
comparisons=[
cl.ExactMatch("first_name"),
cl.ExactMatch("surname"),
cl.ExactMatch("dob"),
cl.ExactMatch("city"),
],
blocking_rules_to_generate_predictions=[
block_on("first_name"),
block_on("dob"),
],
)
df = splink_datasets.fake_1000
df_sdf = db_api.register(df)
linker = Linker(df_sdf, settings, log_level=None)
linker.training.estimate_u_using_random_sampling(max_pairs=1e6)
linker.training.estimate_parameters_using_expectation_maximisation(block_on("dob"))
surname_comparison = linker._settings_obj._get_comparison_by_output_column_name(
"surname"
)
else_comparison_level = (
surname_comparison._get_comparison_level_by_comparison_vector_value(0)
)
else_comparison_level._m_probability = 0.1
linker.visualisations.m_u_parameters_chart()
Generate the (beta) labelling tool¶
import splink.comparison_library as cl
from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets
db_api = DuckDBAPI()
df = splink_datasets.fake_1000
settings = SettingsCreator(
link_type="dedupe_only",
comparisons=[
cl.ExactMatch("first_name"),
cl.ExactMatch("surname"),
cl.ExactMatch("dob"),
cl.ExactMatch("city").configure(term_frequency_adjustments=True),
cl.ExactMatch("email"),
],
blocking_rules_to_generate_predictions=[
block_on("first_name"),
block_on("surname"),
],
max_iterations=2,
)
df_sdf = db_api.register(df)
linker = Linker(df_sdf, settings, log_level=None)
linker.training.estimate_probability_two_random_records_match(
[block_on("first_name", "surname")], recall=0.7
)
linker.training.estimate_u_using_random_sampling(max_pairs=1e6)
linker.training.estimate_parameters_using_expectation_maximisation(block_on("dob"))
pairwise_predictions = linker.inference.predict(threshold_match_weight=-10)
first_unique_id = df_sdf.as_record_list(limit=1)[0]["unique_id"]
linker.evaluation.labelling_tool_for_specific_record(unique_id=first_unique_id, overwrite=True)
Modifying settings after loading from a serialised .json model¶
import splink.comparison_library as cl
from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets
# setup to create a model
db_api = DuckDBAPI()
df = splink_datasets.fake_1000
settings = SettingsCreator(
link_type="dedupe_only",
comparisons=[
cl.LevenshteinAtThresholds("first_name"),
cl.LevenshteinAtThresholds("surname"),
],
blocking_rules_to_generate_predictions=[
block_on("first_name", "dob"),
block_on("surname"),
]
)
df_sdf = db_api.register(df)
linker = Linker(df_sdf, settings)
linker.misc.save_model_to_json("mod.json", overwrite=True)
new_settings = SettingsCreator.from_path_or_dict("mod.json")
new_settings.retain_intermediate_calculation_columns = True
new_settings.blocking_rules_to_generate_predictions = ["1=1"]
new_settings.additional_columns_to_retain = ["cluster"]
db_api_new = DuckDBAPI()
df_sdf_new = db_api_new.register(df)
linker = Linker(df_sdf_new, new_settings)
linker.inference.predict().as_duckdbpyrelation().show()
Blocking time: 0.16 seconds
Predict time (post-blocking): 0.56 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':
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
The 'probability_two_random_records_match' setting has been set to the default value (0.0001).
If this is not the desired behaviour, either:
- assign a value for `probability_two_random_records_match` in your settings dictionary, or
- estimate with the `linker.training.estimate_probability_two_random_records_match` function.
┌─────────────────────┬────────────────────────┬─────────────┬─────────────┬──────────────┬──────────────┬──────────────────┬───────────────┬───────────┬───────────┬───────────────┬────────────┬───────────┬───────────┬───────────┐
│ match_weight │ match_probability │ unique_id_l │ unique_id_r │ first_name_l │ first_name_r │ gamma_first_name │ mw_first_name │ surname_l │ surname_r │ gamma_surname │ mw_surname │ cluster_l │ cluster_r │ match_key │
│ double │ double │ int64 │ int64 │ varchar │ varchar │ int32 │ double │ varchar │ varchar │ int32 │ double │ int64 │ int64 │ varchar │
├─────────────────────┼────────────────────────┼─────────────┼─────────────┼──────────────┼──────────────┼──────────────────┼───────────────┼───────────┼───────────┼───────────────┼────────────┼───────────┼───────────┼───────────┤
│ -23.287568102831404 │ 9.766600706301031e-08 │ 120 │ 496 │ Macdonald │ Harrison │ 0 │ -5.0 │ Dylan │ Norma │ 0 │ -5.0 │ 34 │ 124 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 121 │ 496 │ Dylan │ Harrison │ 0 │ -5.0 │ NULL │ Norma │ -1 │ 0.0 │ 34 │ 124 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 122 │ 496 │ Dylan │ Harrison │ 0 │ -5.0 │ NULL │ Norma │ -1 │ 0.0 │ 34 │ 124 │ 0 │
│ -23.287568102831404 │ 9.766600706301031e-08 │ 123 │ 496 │ Harley │ Harrison │ 0 │ -5.0 │ Kra │ Norma │ 0 │ -5.0 │ 35 │ 124 │ 0 │
│ -23.287568102831404 │ 9.766600706301031e-08 │ 124 │ 496 │ Kaur │ Harrison │ 0 │ -5.0 │ Harley │ Norma │ 0 │ -5.0 │ 35 │ 124 │ 0 │
│ -23.287568102831404 │ 9.766600706301031e-08 │ 125 │ 496 │ Harley │ Harrison │ 0 │ -5.0 │ Kaur │ Norma │ 0 │ -5.0 │ 35 │ 124 │ 0 │
│ -13.287568102831404 │ 0.00010000000000000002 │ 126 │ 496 │ NULL │ Harrison │ -1 │ 0.0 │ NULL │ Norma │ -1 │ 0.0 │ 35 │ 124 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 127 │ 496 │ Hayler │ Harrison │ 0 │ -5.0 │ NULL │ Norma │ -1 │ 0.0 │ 35 │ 124 │ 0 │
│ -23.287568102831404 │ 9.766600706301031e-08 │ 128 │ 496 │ Barker │ Harrison │ 0 │ -5.0 │ Matilda │ Norma │ 0 │ -5.0 │ 36 │ 124 │ 0 │
│ -23.287568102831404 │ 9.766600706301031e-08 │ 129 │ 496 │ Matilda │ Harrison │ 0 │ -5.0 │ Barker │ Norma │ 0 │ -5.0 │ 36 │ 124 │ 0 │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 0 │ 516 │ Robert │ NULL │ -1 │ 0.0 │ Alan │ Morgan │ 0 │ -5.0 │ 0 │ 128 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 1 │ 516 │ Robert │ NULL │ -1 │ 0.0 │ Allen │ Morgan │ 0 │ -5.0 │ 0 │ 128 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 2 │ 516 │ Rob │ NULL │ -1 │ 0.0 │ Allen │ Morgan │ 0 │ -5.0 │ 0 │ 128 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 3 │ 516 │ Robert │ NULL │ -1 │ 0.0 │ Alen │ Morgan │ 0 │ -5.0 │ 0 │ 128 │ 0 │
│ -13.287568102831404 │ 0.00010000000000000002 │ 4 │ 516 │ Grace │ NULL │ -1 │ 0.0 │ NULL │ Morgan │ -1 │ 0.0 │ 1 │ 128 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 5 │ 516 │ Grace │ NULL │ -1 │ 0.0 │ Kelly │ Morgan │ 0 │ -5.0 │ 1 │ 128 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 6 │ 516 │ Logan │ NULL │ -1 │ 0.0 │ pMurphy │ Morgan │ 0 │ -5.0 │ 2 │ 128 │ 0 │
│ -13.287568102831404 │ 0.00010000000000000002 │ 7 │ 516 │ NULL │ NULL │ -1 │ 0.0 │ NULL │ Morgan │ -1 │ 0.0 │ 3 │ 128 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 8 │ 516 │ NULL │ NULL │ -1 │ 0.0 │ Dean │ Morgan │ 0 │ -5.0 │ 3 │ 128 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ 9 │ 516 │ Evie │ NULL │ -1 │ 0.0 │ Dean │ Morgan │ 0 │ -5.0 │ 3 │ 128 │ 0 │
└─────────────────────┴────────────────────────┴─────────────┴─────────────┴──────────────┴──────────────┴──────────────────┴───────────────┴───────────┴───────────┴───────────────┴────────────┴───────────┴───────────┴───────────┘
? rows (>9999 rows, 20 shown) 15 columns
Using a DuckDB UDF in a comparison level¶
import difflib
import duckdb
from duckdb.sqltypes import VARCHAR, DOUBLE
import splink.comparison_level_library as cll
import splink.comparison_library as cl
from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets
def custom_partial_ratio(s1, s2):
"""Custom function to compute partial ratio similarity between two strings."""
s1, s2 = str(s1), str(s2)
matcher = difflib.SequenceMatcher(None, s1, s2)
return matcher.ratio()
df = splink_datasets.fake_1000
con = duckdb.connect()
con.create_function(
"custom_partial_ratio",
custom_partial_ratio,
[duckdb.sqltypes.VARCHAR, duckdb.sqltypes.VARCHAR],
duckdb.sqltypes.DOUBLE,
)
db_api = DuckDBAPI(connection=con)
fuzzy_email_comparison = {
"output_column_name": "email_fuzzy",
"comparison_levels": [
cll.NullLevel("email"),
cll.ExactMatchLevel("email"),
{
"sql_condition": "custom_partial_ratio(email_l, email_r) > 0.8",
"label_for_charts": "Fuzzy match (≥ 0.8)",
},
cll.ElseLevel(),
],
}
settings = SettingsCreator(
link_type="dedupe_only",
comparisons=[
cl.ExactMatch("first_name"),
cl.ExactMatch("surname"),
cl.ExactMatch("dob"),
cl.ExactMatch("city").configure(term_frequency_adjustments=True),
fuzzy_email_comparison,
],
blocking_rules_to_generate_predictions=[
block_on("first_name"),
block_on("surname"),
],
max_iterations=2,
)
df_sdf = db_api.register(df)
linker = Linker(df_sdf, settings)
linker.training.estimate_probability_two_random_records_match(
[block_on("first_name", "surname")], recall=0.7
)
linker.training.estimate_u_using_random_sampling(max_pairs=1e5)
linker.training.estimate_parameters_using_expectation_maximisation(block_on("dob"))
pairwise_predictions = linker.inference.predict(threshold_match_weight=-10)
Probability two random records match is estimated to be 0.000821.
This means that amongst all possible pairwise record comparisons, one in 1,218.29 are expected to match. With 499,500 total possible comparisons, we expect a total of around 410.00 matching pairs
----- Estimating u probabilities using random sampling -----
Estimating u with: max_pairs = 100,000, min_count_per_level = 100, num_chunks = 10
Estimating u for: first_name (Comparison 1 of 5)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 11 for comparison level Exact match on first_name (cvv=1)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 50 for level Exact match on first_name (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 124 for level Exact match on first_name (cvv=1). Chunk took 0.0 seconds.
Exiting early since min count of 124 exceeds min_count_per_level = 100
Estimating u for: surname (Comparison 2 of 5)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 4 for comparison level Exact match on surname (cvv=1)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 35 for level Exact match on surname (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 50 for level Exact match on surname (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 78 for level Exact match on surname (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 4/10
Count of 99 for level Exact match on surname (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 5/10
Count of 128 for level Exact match on surname (cvv=1). Chunk took 0.0 seconds.
Exiting early since min count of 128 exceeds min_count_per_level = 100
Estimating u for: dob (Comparison 3 of 5)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 0 for comparison level Exact match on dob (cvv=1)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 13 for level Exact match on dob (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 32 for level Exact match on dob (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 49 for level Exact match on dob (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 4/10
Count of 64 for level Exact match on dob (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 5/10
Count of 68 for level Exact match on dob (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 6/10
Count of 81 for level Exact match on dob (cvv=1). Chunk took 0.0 seconds.
Min u_count not hit, continuing.
Running chunk 7/10
Count of 106 for level Exact match on dob (cvv=1). Chunk took 0.0 seconds.
Exiting early since min count of 106 exceeds min_count_per_level = 100
Estimating u for: city (Comparison 4 of 5)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 52 for comparison level Exact match on city (cvv=1)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 379 for level Exact match on city (cvv=1). Chunk took 0.0 seconds.
Exiting early since min count of 379 exceeds min_count_per_level = 100
Estimating u for: email_fuzzy (Comparison 5 of 5)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 0 for comparison level Fuzzy match (≥ 0.8) (cvv=1)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 9 for level Fuzzy match (≥ 0.8) (cvv=1). Chunk took 0.6 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 21 for level Exact match on email (cvv=2). Chunk took 0.6 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 33 for level Fuzzy match (≥ 0.8) (cvv=1). Chunk took 0.6 seconds.
Min u_count not hit, continuing.
Running chunk 4/10
Count of 48 for level Fuzzy match (≥ 0.8) (cvv=1). Chunk took 0.4 seconds.
Min u_count not hit, continuing.
Running chunk 5/10
Count of 52 for level Fuzzy match (≥ 0.8) (cvv=1). Chunk took 0.7 seconds.
Min u_count not hit, continuing.
Running chunk 6/10
Count of 65 for level Fuzzy match (≥ 0.8) (cvv=1). Chunk took 0.7 seconds.
Min u_count not hit, continuing.
Running chunk 7/10
Count of 83 for level Fuzzy match (≥ 0.8) (cvv=1). Chunk took 0.7 seconds.
Min u_count not hit, continuing.
Running chunk 8/10
Count of 102 for level Fuzzy match (≥ 0.8) (cvv=1). Chunk took 1.0 seconds.
Exiting early since min count of 102 exceeds min_count_per_level = 100
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).
- city (no m values are trained).
- email_fuzzy (no m values are trained).
----- 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
- city
- email_fuzzy
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.35 in the m_probability of email_fuzzy, level `Fuzzy match (≥ 0.8)`
Iteration 2: Largest change in params was 0.217 in probability_two_random_records_match
EM converged after 2 iterations
Your model is not yet fully trained. Missing estimates for:
- dob (no m values are trained).
Blocking time: 0.03 seconds
Predict time (post-blocking): 0.45 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: 'dob':
m values not fully trained
Nested linkage¶
In this example, we want to deduplicate persons but only within each company.
The problem is that the companies themselves may be duplicates, so we proceed by deduplicating the companies first and then deduplicating persons nested within each company we resolved in step 1.
Note I do not include full model training code here, just a simple/illustrative model spec. The example is more about demonstrating the nested linkage process.
import duckdb
import os
import pyarrow as pa
import splink.comparison_library as cl
from splink import DuckDBAPI, Linker, SettingsCreator, block_on
from splink.clustering import cluster_pairwise_predictions_at_threshold
# Example data with companies and persons
company_person_records_list = [
{
"unique_id": 1001,
"client_id": "GGN1",
"company_name": "Green Garden Nurseries Ltd",
"postcode": "NR1 1AB",
"person_firstname": "John",
"person_surname": "Smith",
},
{
"unique_id": 1002,
"client_id": "GGN1",
"company_name": "Green Gardens Ltd",
"postcode": "NR1 1AB",
"person_firstname": "Sarah",
"person_surname": "Jones",
},
{
"unique_id": 1003,
"client_id": "GGN2",
"company_name": "Green Garden Nurseries Ltd",
"postcode": "NR1 1AB",
"person_firstname": "John",
"person_surname": "Smith",
},
{
"unique_id": 3001,
"client_id": "GW1",
"company_name": "Garden World",
"postcode": "LS2 3EF",
"person_firstname": "Emma",
"person_surname": "Wilson",
},
{
"unique_id": 3002,
"client_id": "GW1",
"company_name": "Garden World UK",
"postcode": "LS2 3EF",
"person_firstname": "Emma",
"person_surname": "Wilson",
},
{
"unique_id": 3003,
"client_id": "GW2",
"company_name": "Garden World",
"postcode": "LS2 3EF",
"person_firstname": "Emma",
"person_surname": "Wilson",
},
{
"unique_id": 3004,
"client_id": "GW2",
"company_name": "Garden World",
"postcode": "LS2 3EF",
"person_firstname": "James",
"person_surname": "Taylor",
},
]
company_person_records = pa.Table.from_pylist(company_person_records_list)
company_person_records
print("========== NESTED COMPANY-PERSON LINKAGE EXAMPLE ==========")
print("This example demonstrates a two-phase linkage process:")
print("1. First, link and cluster to find duplicate companies (client_id)")
print("2. Then, deduplicate persons ONLY within each company cluster")
# Initialize database
if os.path.exists("nested_linkage.ddb"):
os.remove("nested_linkage.ddb")
con = duckdb.connect("nested_linkage.ddb")
# Load data into DuckDB
con.execute(
"CREATE OR REPLACE TABLE company_person_records AS "
"SELECT * FROM company_person_records"
)
print("\n--- PHASE 1: COMPANY LINKAGE ---")
print("Company records to be linked:")
con.table("company_person_records").show()
# STEP 1: Find duplicate client_ids
# Configure company linkage
# We match on person name because if we have duplicate client_ids,
# it's likely that they may share the same contact
# Note though, at this stage the entity is client not a person
company_settings = SettingsCreator(
link_type="dedupe_only",
unique_id_column_name="unique_id",
probability_two_random_records_match=0.001,
comparisons=[
cl.ExactMatch("client_id"),
cl.JaroWinklerAtThresholds("person_firstname"),
cl.JaroWinklerAtThresholds("person_surname"),
cl.JaroWinklerAtThresholds("company_name"),
cl.ExactMatch("postcode"),
],
blocking_rules_to_generate_predictions=[
block_on("postcode"),
block_on("company_name"),
],
retain_matching_columns=True,
)
db_api = DuckDBAPI(connection=con)
company_records_sdf = db_api.register("company_person_records")
company_linker = Linker(company_records_sdf, company_settings)
company_predictions = company_linker.inference.predict(threshold_match_probability=0.5)
print("\nCompany pairwise matches:")
company_predictions.as_duckdbpyrelation().show()
# Cluster companies
company_nodes = con.sql("SELECT DISTINCT client_id FROM company_person_records")
company_edges = con.sql(f"""
SELECT
client_id_l as n_1,
client_id_r as n_2,
match_probability
FROM {company_predictions.physical_name}
""")
# Perform company clustering
company_clusters = cluster_pairwise_predictions_at_threshold(
company_nodes,
company_edges,
node_id_column_name="client_id",
edge_id_column_name_left="n_1",
edge_id_column_name_right="n_2",
db_api=db_api,
threshold_match_probability=0.5,
)
# Add company cluster IDs to original records
company_clusters_ddb = company_clusters.as_duckdbpyrelation()
con.register("company_clusters_ddb", company_clusters_ddb)
sql = """
CREATE TABLE records_with_company_cluster AS
SELECT cr.*,
cc.cluster_id as company_cluster_id
FROM company_person_records cr
LEFT JOIN company_clusters_ddb cc
ON cr.client_id = cc.client_id
"""
con.execute(sql)
print("Records with company cluster:")
con.table("records_with_company_cluster").show()
# Not needed, just to see what's happening
print("\nCompany clustering results:")
con.sql("""
SELECT
company_cluster_id,
array_agg(DISTINCT client_id) as client_ids,
array_agg(DISTINCT company_name) as company_names
FROM records_with_company_cluster
GROUP BY company_cluster_id
""").show()
print("\n--- PHASE 2: PERSON LINKAGE WITHIN COMPANIES ---")
print("Now linking persons, but only within their company clusters")
# STEP 2: Link persons within company clusters
db_api2 = DuckDBAPI(connection=con)
# Configure person linkage within company clusters
# Simple linking model just distinguishes between people within a client_id
# There shouldn't be many so this model can be straightforward
person_settings = SettingsCreator(
link_type="dedupe_only",
probability_two_random_records_match=0.01,
comparisons=[
cl.JaroWinklerAtThresholds("person_firstname"),
cl.JaroWinklerAtThresholds("person_surname"),
],
blocking_rules_to_generate_predictions=[
# Critical: Block on company_cluster_id to only compare within company
block_on("company_cluster_id"),
],
retain_matching_columns=True,
)
person_records_sdf = db_api2.register("records_with_company_cluster")
person_linker = Linker(person_records_sdf, person_settings)
person_predictions = person_linker.inference.predict(threshold_match_probability=0.5)
print("\nPerson pairwise matches (within company clusters):")
person_predictions.as_duckdbpyrelation().show(max_width=1000)
person_clusters = person_linker.clustering.cluster_pairwise_predictions_at_threshold(
person_predictions, threshold_match_probability=0.5
)
person_clusters.as_duckdbpyrelation().sort("cluster_id").show(max_width=1000)
========== NESTED COMPANY-PERSON LINKAGE EXAMPLE ==========
This example demonstrates a two-phase linkage process:
1. First, link and cluster to find duplicate companies (client_id)
2. Then, deduplicate persons ONLY within each company cluster
--- PHASE 1: COMPANY LINKAGE ---
Company records to be linked:
┌───────────┬───────────┬────────────────────────────┬──────────┬──────────────────┬────────────────┐
│ unique_id │ client_id │ company_name │ postcode │ person_firstname │ person_surname │
│ int64 │ varchar │ varchar │ varchar │ varchar │ varchar │
├───────────┼───────────┼────────────────────────────┼──────────┼──────────────────┼────────────────┤
│ 1001 │ GGN1 │ Green Garden Nurseries Ltd │ NR1 1AB │ John │ Smith │
│ 1002 │ GGN1 │ Green Gardens Ltd │ NR1 1AB │ Sarah │ Jones │
│ 1003 │ GGN2 │ Green Garden Nurseries Ltd │ NR1 1AB │ John │ Smith │
│ 3001 │ GW1 │ Garden World │ LS2 3EF │ Emma │ Wilson │
│ 3002 │ GW1 │ Garden World UK │ LS2 3EF │ Emma │ Wilson │
│ 3003 │ GW2 │ Garden World │ LS2 3EF │ Emma │ Wilson │
│ 3004 │ GW2 │ Garden World │ LS2 3EF │ James │ Taylor │
└───────────┴───────────┴────────────────────────────┴──────────┴──────────────────┴────────────────┘
Blocking time: 0.02 seconds
Predict time (post-blocking): 0.12 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: 'client_id':
m values not fully trained
Comparison: 'client_id':
u values not fully trained
Comparison: 'person_firstname':
m values not fully trained
Comparison: 'person_firstname':
u values not fully trained
Comparison: 'person_surname':
m values not fully trained
Comparison: 'person_surname':
u values not fully trained
Comparison: 'company_name':
m values not fully trained
Comparison: 'company_name':
u values not fully trained
Comparison: 'postcode':
m values not fully trained
Comparison: 'postcode':
u values not fully trained
Completed iteration 1, num edges remaining to process: 0
Company pairwise matches:
┌────────────────────┬────────────────────┬─────────────┬─────────────┬─────────────┬─────────────┬─────────────────┬────────────────────┬────────────────────┬────────────────────────┬──────────────────┬──────────────────┬──────────────────────┬────────────────────────────┬────────────────────────────┬────────────────────┬────────────┬────────────┬────────────────┬───────────┐
│ match_weight │ match_probability │ unique_id_l │ unique_id_r │ client_id_l │ client_id_r │ gamma_client_id │ person_firstname_l │ person_firstname_r │ gamma_person_firstname │ person_surname_l │ person_surname_r │ gamma_person_surname │ company_name_l │ company_name_r │ gamma_company_name │ postcode_l │ postcode_r │ gamma_postcode │ match_key │
│ double │ double │ int64 │ int64 │ varchar │ varchar │ int32 │ varchar │ varchar │ int32 │ varchar │ varchar │ int32 │ varchar │ varchar │ int32 │ varchar │ varchar │ int32 │ varchar │
├────────────────────┼────────────────────┼─────────────┼─────────────┼─────────────┼─────────────┼─────────────────┼────────────────────┼────────────────────┼────────────────────────┼──────────────────┼──────────────────┼──────────────────────┼────────────────────────────┼────────────────────────────┼────────────────────┼────────────┼────────────┼────────────────┼───────────┤
│ 3.0356591322075825 │ 0.8913067130888913 │ 1001 │ 1002 │ GGN1 │ GGN1 │ 1 │ John │ Sarah │ 0 │ Smith │ Jones │ 0 │ Green Garden Nurseries Ltd │ Green Gardens Ltd │ 2 │ NR1 1AB │ NR1 1AB │ 1 │ 0 │
│ 33.035659132207584 │ 0.9999999998864268 │ 3001 │ 3002 │ GW1 │ GW1 │ 1 │ Emma │ Emma │ 3 │ Wilson │ Wilson │ 3 │ Garden World │ Garden World UK │ 2 │ LS2 3EF │ LS2 3EF │ 1 │ 0 │
│ 18.035659132207584 │ 0.9999962784488419 │ 3002 │ 3003 │ GW1 │ GW2 │ 0 │ Emma │ Emma │ 3 │ Wilson │ Wilson │ 3 │ Garden World UK │ Garden World │ 2 │ LS2 3EF │ LS2 3EF │ 1 │ 0 │
│ 10.035659132207583 │ 0.9990481861705929 │ 3003 │ 3004 │ GW2 │ GW2 │ 1 │ Emma │ James │ 0 │ Wilson │ Taylor │ 0 │ Garden World │ Garden World │ 3 │ LS2 3EF │ LS2 3EF │ 1 │ 0 │
│ 25.035659132207584 │ 0.9999999709252743 │ 1001 │ 1003 │ GGN1 │ GGN2 │ 0 │ John │ John │ 3 │ Smith │ Smith │ 3 │ Green Garden Nurseries Ltd │ Green Garden Nurseries Ltd │ 3 │ NR1 1AB │ NR1 1AB │ 1 │ 0 │
│ 25.035659132207584 │ 0.9999999709252743 │ 3001 │ 3003 │ GW1 │ GW2 │ 0 │ Emma │ Emma │ 3 │ Wilson │ Wilson │ 3 │ Garden World │ Garden World │ 3 │ LS2 3EF │ LS2 3EF │ 1 │ 0 │
└────────────────────┴────────────────────┴─────────────┴─────────────┴─────────────┴─────────────┴─────────────────┴────────────────────┴────────────────────┴────────────────────────┴──────────────────┴──────────────────┴──────────────────────┴────────────────────────────┴────────────────────────────┴────────────────────┴────────────┴────────────┴────────────────┴───────────┘
Records with company cluster:
┌───────────┬───────────┬────────────────────────────┬──────────┬──────────────────┬────────────────┬────────────────────┐
│ unique_id │ client_id │ company_name │ postcode │ person_firstname │ person_surname │ company_cluster_id │
│ int64 │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├───────────┼───────────┼────────────────────────────┼──────────┼──────────────────┼────────────────┼────────────────────┤
│ 1001 │ GGN1 │ Green Garden Nurseries Ltd │ NR1 1AB │ John │ Smith │ GGN1 │
│ 1002 │ GGN1 │ Green Gardens Ltd │ NR1 1AB │ Sarah │ Jones │ GGN1 │
│ 1003 │ GGN2 │ Green Garden Nurseries Ltd │ NR1 1AB │ John │ Smith │ GGN1 │
│ 3001 │ GW1 │ Garden World │ LS2 3EF │ Emma │ Wilson │ GW1 │
│ 3002 │ GW1 │ Garden World UK │ LS2 3EF │ Emma │ Wilson │ GW1 │
│ 3003 │ GW2 │ Garden World │ LS2 3EF │ Emma │ Wilson │ GW1 │
│ 3004 │ GW2 │ Garden World │ LS2 3EF │ James │ Taylor │ GW1 │
└───────────┴───────────┴────────────────────────────┴──────────┴──────────────────┴────────────────┴────────────────────┘
Company clustering results:
┌────────────────────┬──────────────┬─────────────────────────────────────────────────┐
│ company_cluster_id │ client_ids │ company_names │
│ varchar │ varchar[] │ varchar[] │
├────────────────────┼──────────────┼─────────────────────────────────────────────────┤
│ GW1 │ [GW1, GW2] │ [Garden World, Garden World UK] │
│ GGN1 │ [GGN2, GGN1] │ [Green Gardens Ltd, Green Garden Nurseries Ltd] │
└────────────────────┴──────────────┴─────────────────────────────────────────────────┘
--- PHASE 2: PERSON LINKAGE WITHIN COMPANIES ---
Now linking persons, but only within their company clusters
Blocking time: 0.01 seconds
Predict time (post-blocking): 0.05 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: 'person_firstname':
m values not fully trained
Comparison: 'person_firstname':
u values not fully trained
Comparison: 'person_surname':
m values not fully trained
Comparison: 'person_surname':
u values not fully trained
Completed iteration 1, num edges remaining to process: 0
Person pairwise matches (within company clusters):
┌────────────────────┬────────────────────┬─────────────┬─────────────┬────────────────────┬────────────────────┬────────────────────────┬──────────────────┬──────────────────┬──────────────────────┬──────────────────────┬──────────────────────┬───────────┐
│ match_weight │ match_probability │ unique_id_l │ unique_id_r │ person_firstname_l │ person_firstname_r │ gamma_person_firstname │ person_surname_l │ person_surname_r │ gamma_person_surname │ company_cluster_id_l │ company_cluster_id_r │ match_key │
│ double │ double │ int64 │ int64 │ varchar │ varchar │ int32 │ varchar │ varchar │ int32 │ varchar │ varchar │ varchar │
├────────────────────┼────────────────────┼─────────────┼─────────────┼────────────────────┼────────────────────┼────────────────────────┼──────────────────┼──────────────────┼──────────────────────┼──────────────────────┼──────────────────────┼───────────┤
│ 13.370643379920391 │ 0.9999055951557918 │ 3001 │ 3002 │ Emma │ Emma │ 3 │ Wilson │ Wilson │ 3 │ GW1 │ GW1 │ 0 │
│ 13.370643379920391 │ 0.9999055951557918 │ 3002 │ 3003 │ Emma │ Emma │ 3 │ Wilson │ Wilson │ 3 │ GW1 │ GW1 │ 0 │
│ 13.370643379920391 │ 0.9999055951557918 │ 1001 │ 1003 │ John │ John │ 3 │ Smith │ Smith │ 3 │ GGN1 │ GGN1 │ 0 │
│ 13.370643379920391 │ 0.9999055951557918 │ 3001 │ 3003 │ Emma │ Emma │ 3 │ Wilson │ Wilson │ 3 │ GW1 │ GW1 │ 0 │
└────────────────────┴────────────────────┴─────────────┴─────────────┴────────────────────┴────────────────────┴────────────────────────┴──────────────────┴──────────────────┴──────────────────────┴──────────────────────┴──────────────────────┴───────────┘
┌────────────┬───────────┬───────────┬────────────────────────────┬──────────┬──────────────────┬────────────────┬────────────────────┐
│ cluster_id │ unique_id │ client_id │ company_name │ postcode │ person_firstname │ person_surname │ company_cluster_id │
│ int64 │ int64 │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├────────────┼───────────┼───────────┼────────────────────────────┼──────────┼──────────────────┼────────────────┼────────────────────┤
│ 1001 │ 1001 │ GGN1 │ Green Garden Nurseries Ltd │ NR1 1AB │ John │ Smith │ GGN1 │
│ 1001 │ 1003 │ GGN2 │ Green Garden Nurseries Ltd │ NR1 1AB │ John │ Smith │ GGN1 │
│ 1002 │ 1002 │ GGN1 │ Green Gardens Ltd │ NR1 1AB │ Sarah │ Jones │ GGN1 │
│ 3001 │ 3001 │ GW1 │ Garden World │ LS2 3EF │ Emma │ Wilson │ GW1 │
│ 3001 │ 3002 │ GW1 │ Garden World UK │ LS2 3EF │ Emma │ Wilson │ GW1 │
│ 3001 │ 3003 │ GW2 │ Garden World │ LS2 3EF │ Emma │ Wilson │ GW1 │
│ 3004 │ 3004 │ GW2 │ Garden World │ LS2 3EF │ James │ Taylor │ GW1 │
└────────────┴───────────┴───────────┴────────────────────────────┴──────────┴──────────────────┴────────────────┴────────────────────┘
Comparing a list of values with fuzzy matching and term frequency adjustments¶
See here for a description of this approach
import duckdb
import splink.comparison_level_library as cll
import splink.comparison_library as cl
from splink import DuckDBAPI, Linker, SettingsCreator
con = duckdb.connect(database=":memory:")
left_records = [
{
"unique_id": 1,
"primary_forename": "Alisha",
"all_forenames": ["Alisha", "Alisha Louise", "Ali"],
},
{
"unique_id": 2,
"primary_forename": "Michael",
"all_forenames": ["Michael", "Mike"],
},
]
right_records = [
{"unique_id": 1, "primary_forename": "Alisha", "all_forenames": ["Alisha", "Ali"]},
{"unique_id": 3, "primary_forename": "Alysha", "all_forenames": ["Alysha"]},
{"unique_id": 9, "primary_forename": "Michelle", "all_forenames": ["Michelle"]},
]
def make_table(name, recs):
con.execute(f"drop table if exists {name}")
con.execute(
f"""
create table {name} (
unique_id integer,
primary_forename varchar,
all_forenames varchar[]
)
"""
)
for r in recs:
arr = "[" + ", ".join(f"'{v}'" for v in r["all_forenames"]) + "]"
con.execute(
f"""
insert into {name} values
({r["unique_id"]}, '{r["primary_forename"]}', {arr})
"""
)
make_table("df_left", left_records)
make_table("df_right", right_records)
con.table("df_left").show()
con.table("df_right").show()
forename_comparison = cl.CustomComparison(
output_column_name="forename",
comparison_levels=[
cll.NullLevel("primary_forename"),
# 1. exact with term-frequency adjustment
cll.ExactMatchLevel("primary_forename", term_frequency_adjustments=True),
# 2. any overlap between arrays
cll.ArrayIntersectLevel("all_forenames", min_intersection=1),
# 3. tight fuzzy
cll.JaroWinklerLevel("primary_forename", distance_threshold=0.9),
# 4. looser fuzzy
cll.JaroWinklerLevel("primary_forename", distance_threshold=0.7),
# 5. fuzzy anywhere in arrays
cll.PairwiseStringDistanceFunctionLevel(
col_name="all_forenames",
distance_function_name="jaro_winkler",
distance_threshold=0.85,
),
cll.ElseLevel(),
],
comparison_description="Forename comparison combining exact, array overlap and fuzzy logic",
)
settings = SettingsCreator(
link_type="link_only",
unique_id_column_name="unique_id",
blocking_rules_to_generate_predictions=[
# create all comparisons for demo
"1=1"
],
comparisons=[forename_comparison],
retain_intermediate_calculation_columns=True,
retain_matching_columns=True,
)
db_api_linker = DuckDBAPI(con)
df_left_sdf = db_api_linker.register("df_left")
df_right_sdf = db_api_linker.register("df_right")
linker = Linker(
[df_left_sdf, df_right_sdf],
settings,
)
# Skip training for demo purposes, just demonstrate that predict() works
df_predict = linker.inference.predict()
df_predict.as_duckdbpyrelation()
┌───────────┬──────────────────┬──────────────────────────────┐
│ unique_id │ primary_forename │ all_forenames │
│ int32 │ varchar │ varchar[] │
├───────────┼──────────────────┼──────────────────────────────┤
│ 1 │ Alisha │ [Alisha, Alisha Louise, Ali] │
│ 2 │ Michael │ [Michael, Mike] │
└───────────┴──────────────────┴──────────────────────────────┘
┌───────────┬──────────────────┬───────────────┐
│ unique_id │ primary_forename │ all_forenames │
│ int32 │ varchar │ varchar[] │
├───────────┼──────────────────┼───────────────┤
│ 1 │ Alisha │ [Alisha, Ali] │
│ 3 │ Alysha │ [Alysha] │
│ 9 │ Michelle │ [Michelle] │
└───────────┴──────────────────┴───────────────┘
Blocking time: 0.03 seconds
Predict time (post-blocking): 0.05 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: 'forename':
m values not fully trained
Comparison: 'forename':
u values not fully trained
The 'probability_two_random_records_match' setting has been set to the default value (0.0001).
If this is not the desired behaviour, either:
- assign a value for `probability_two_random_records_match` in your settings dictionary, or
- estimate with the `linker.training.estimate_probability_two_random_records_match` function.
┌─────────────────────┬────────────────────────┬─────────────────────────┬─────────────────────────┬─────────────┬─────────────┬────────────────────┬────────────────────┬──────────────────────────────┬─────────────────┬────────────────┬───────────────────────┬───────────────────────┬─────────────┬────────────────────┬───────────┐
│ match_weight │ match_probability │ source_dataset_l │ source_dataset_r │ unique_id_l │ unique_id_r │ primary_forename_l │ primary_forename_r │ all_forenames_l │ all_forenames_r │ gamma_forename │ tf_primary_forename_l │ tf_primary_forename_r │ mw_forename │ mw_tf_adj_forename │ match_key │
│ double │ double │ varchar │ varchar │ int32 │ int32 │ varchar │ varchar │ varchar[] │ varchar[] │ int32 │ double │ double │ double │ double │ varchar │
├─────────────────────┼────────────────────────┼─────────────────────────┼─────────────────────────┼─────────────┼─────────────┼────────────────────┼────────────────────┼──────────────────────────────┼─────────────────┼────────────────┼───────────────────────┼───────────────────────┼─────────────┼────────────────────┼───────────┤
│ -18.287568102831404 │ 3.1253027637052345e-06 │ __splink__input_table_0 │ __splink__input_table_1 │ 2 │ 1 │ Michael │ Alisha │ [Michael, Mike] │ [Alisha, Ali] │ 0 │ 0.2 │ 0.4 │ -5.0 │ 0.0 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ __splink__input_table_0 │ __splink__input_table_1 │ 2 │ 3 │ Michael │ Alysha │ [Michael, Mike] │ [Alysha] │ 0 │ 0.2 │ 0.2 │ -5.0 │ 0.0 │ 0 │
│ -12.287568102831404 │ 0.00019998000199980003 │ __splink__input_table_0 │ __splink__input_table_1 │ 2 │ 9 │ Michael │ Michelle │ [Michael, Mike] │ [Michelle] │ 3 │ 0.2 │ 0.2 │ 1.0 │ 0.0 │ 0 │
│ -12.039640589387819 │ 0.00023746734823961702 │ __splink__input_table_0 │ __splink__input_table_1 │ 1 │ 1 │ Alisha │ Alisha │ [Alisha, Alisha Louise, Ali] │ [Alisha, Ali] │ 5 │ 0.4 │ 0.4 │ 10.0 │ -8.752072486556415 │ 0 │
│ -12.287568102831404 │ 0.00019998000199980003 │ __splink__input_table_0 │ __splink__input_table_1 │ 1 │ 3 │ Alisha │ Alysha │ [Alisha, Alisha Louise, Ali] │ [Alysha] │ 3 │ 0.4 │ 0.2 │ 1.0 │ 0.0 │ 0 │
│ -18.287568102831404 │ 3.1253027637052345e-06 │ __splink__input_table_0 │ __splink__input_table_1 │ 1 │ 9 │ Alisha │ Michelle │ [Alisha, Alisha Louise, Ali] │ [Michelle] │ 0 │ 0.4 │ 0.2 │ -5.0 │ 0.0 │ 0 │
└─────────────────────┴────────────────────────┴─────────────────────────┴─────────────────────────┴─────────────┴─────────────┴────────────────────┴────────────────────┴──────────────────────────────┴─────────────────┴────────────────┴───────────────────────┴───────────────────────┴─────────────┴────────────────────┴───────────┘