Linking a dataset of real historical persons¶
In this example, we deduplicate a more realistic dataset. The data is based on historical persons scraped from wikidata. Duplicate records are introduced with a variety of errors introduced.
Note, as explained in the backends topic guide, SQLite does not natively support string fuzzy matching functions such as damareau-levenshtein and jaro-winkler (as used in this example). Instead, these have been imported as python User Defined Functions (UDFs). One drawback of python UDFs is that they are considerably slower than native-SQL comparisons. As such, if you are hitting issues with large run times, consider switching to DuckDB (or some other backend).
```python
Uncomment and run this cell if you're running in Google Colab.¶
!pip install splink¶
!pip install rapidfuzz¶
```
```python import pandas as pd
from splink import splink_datasets
pd.options.display.max_rows = 1000
reduce size of dataset to make things run faster¶
df = splink_datasets.historical_50k.sample(5000) ```
```python from splink.backends.sqlite import SQLiteAPI from splink.exploratory import profile_columns
db_api = SQLiteAPI() profile_columns( df, db_api, column_expressions=["first_name", "postcode_fake", "substr(dob, 1,4)"] ) ```
```python from splink import block_on from splink.blocking_analysis import ( cumulative_comparisons_to_be_scored_from_blocking_rules_chart, )
blocking_rules = [block_on("first_name", "surname"), block_on("surname", "dob"), block_on("first_name", "dob"), block_on("postcode_fake", "first_name")]
db_api = SQLiteAPI()
cumulative_comparisons_to_be_scored_from_blocking_rules_chart( table_or_tables=df, blocking_rules=blocking_rules, db_api=db_api, link_type="dedupe_only" ) ```
```python import splink.comparison_library as cl from splink import Linker
settings = { "link_type": "dedupe_only", "blocking_rules_to_generate_predictions": [ block_on("first_name", "surname"), block_on("surname", "dob"), block_on("first_name", "dob"), block_on("postcode_fake", "first_name"),
],
"comparisons": [
cl.NameComparison("first_name"),
cl.NameComparison("surname"),
cl.DamerauLevenshteinAtThresholds("dob", [1, 2]).configure(
term_frequency_adjustments=True
),
cl.DamerauLevenshteinAtThresholds("postcode_fake", [1, 2]),
cl.ExactMatch("birth_place").configure(term_frequency_adjustments=True),
cl.ExactMatch(
"occupation",
).configure(term_frequency_adjustments=True),
],
"retain_matching_columns": True,
"retain_intermediate_calculation_columns": True,
"max_iterations": 10,
"em_convergence": 0.01,
}
linker = Linker(df, settings, db_api=db_api) ```
python
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.000125.
This means that amongst all possible pairwise record comparisons, one in 7,985.62 are expected to match. With 12,497,500 total possible comparisons, we expect a total of around 1,565.00 matching pairs
python
linker.training.estimate_u_using_random_sampling(max_pairs=1e6)
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 -----
u probability not trained for first_name - Jaro-Winkler distance of first_name >= 0.88 (comparison vector value: 2). This usually means the comparison level was never observed in the training data.
u probability not trained for surname - Jaro-Winkler distance of surname >= 0.88 (comparison vector value: 2). This usually means the comparison level was never observed in the training data.
Estimated u probabilities using random sampling
Your model is not yet fully trained. Missing estimates for:
- first_name (some u values are not trained, no m values are trained).
- 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).
python
training_blocking_rule = "l.first_name = r.first_name and l.surname = r.surname"
training_session_names = linker.training.estimate_parameters_using_expectation_maximisation(
training_blocking_rule, estimate_without_term_frequencies=True
)
----- Starting EM training session -----
Estimating the m probabilities of the model by blocking on:
l.first_name = r.first_name and l.surname = r.surname
Parameter estimates will be made for the following comparison(s):
- dob
- postcode_fake
- birth_place
- occupation
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- first_name
- surname
Iteration 1: Largest change in params was -0.438 in probability_two_random_records_match
Iteration 2: Largest change in params was -0.0347 in probability_two_random_records_match
Iteration 3: Largest change in params was -0.0126 in the m_probability of birth_place, level `All other comparisons`
Iteration 4: Largest change in params was 0.00644 in the m_probability of birth_place, level `Exact match on birth_place`
EM converged after 4 iterations
Your model is not yet fully trained. Missing estimates for:
- first_name (some u values are not trained, no m values are trained).
- surname (some u values are not trained, no m values are trained).
python
training_blocking_rule = "l.dob = r.dob"
training_session_dob = linker.training.estimate_parameters_using_expectation_maximisation(
training_blocking_rule, estimate_without_term_frequencies=True
)
----- Starting EM training session -----
Estimating the m probabilities of the model by blocking on:
l.dob = r.dob
Parameter estimates will be made for the following comparison(s):
- first_name
- surname
- postcode_fake
- birth_place
- occupation
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- dob
WARNING:
Level Jaro-Winkler distance of first_name >= 0.88 on comparison first_name not observed in dataset, unable to train m value
WARNING:
Level Jaro-Winkler distance of surname >= 0.88 on comparison surname not observed in dataset, unable to train m value
Iteration 1: Largest change in params was 0.327 in the m_probability of first_name, level `All other comparisons`
Iteration 2: Largest change in params was -0.0566 in the m_probability of surname, level `Exact match on surname`
Iteration 3: Largest change in params was -0.0184 in the m_probability of surname, level `Exact match on surname`
Iteration 4: Largest change in params was -0.006 in the m_probability of surname, level `Exact match on surname`
EM converged after 4 iterations
m probability not trained for first_name - Jaro-Winkler distance of first_name >= 0.88 (comparison vector value: 2). This usually means the comparison level was never observed in the training data.
m probability not trained for surname - Jaro-Winkler distance of surname >= 0.88 (comparison vector value: 2). This usually means the comparison level was never observed in the training data.
Your model is not yet fully trained. Missing estimates for:
- first_name (some u values are not trained, some m values are not trained).
- surname (some u values are not trained, some m values are not trained).
The final match weights can be viewed in the match weights chart:
python
linker.visualisations.match_weights_chart()
python
linker.evaluation.unlinkables_chart()
python
df_predict = linker.inference.predict()
df_e = df_predict.as_pandas_dataframe(limit=5)
df_e
-- 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 trained 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
| match_weight | match_probability | unique_id_l | unique_id_r | first_name_l | first_name_r | gamma_first_name | tf_first_name_l | tf_first_name_r | bf_first_name | ... | bf_birth_place | bf_tf_adj_birth_place | occupation_l | occupation_r | gamma_occupation | tf_occupation_l | tf_occupation_r | bf_occupation | bf_tf_adj_occupation | match_key | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 26.932083 | 1.000000 | Q446382-1 | Q446382-3 | marianne | marianne | 4 | 0.000801 | 0.000801 | 51.871289 | ... | 0.162366 | 1.000000 | None | None | -1 | NaN | NaN | 1.000000 | 1.000000 | 0 |
| 1 | 30.788800 | 1.000000 | Q2835078-1 | Q2835078-2 | alfred | alfred | 4 | 0.013622 | 0.013622 | 51.871289 | ... | 197.452526 | 0.607559 | None | None | -1 | NaN | NaN | 1.000000 | 1.000000 | 0 |
| 2 | 23.882340 | 1.000000 | Q2835078-1 | Q2835078-5 | alfred | alfred | 4 | 0.013622 | 0.013622 | 51.871289 | ... | 1.000000 | 1.000000 | None | None | -1 | NaN | NaN | 1.000000 | 1.000000 | 0 |
| 3 | 39.932187 | 1.000000 | Q80158702-1 | Q80158702-4 | john | john | 4 | 0.053085 | 0.053085 | 51.871289 | ... | 197.452526 | 2.025198 | sculptor | sculptor | 1 | 0.002769 | 0.002769 | 23.836781 | 13.868019 | 0 |
| 4 | 17.042339 | 0.999993 | Q18810722-3 | Q18810722-6 | frederick | frederick | 4 | 0.012220 | 0.012220 | 51.871289 | ... | 197.452526 | 0.607559 | printer | printer | 1 | 0.000791 | 0.000791 | 23.836781 | 48.538067 | 0 |
5 rows × 44 columns
You can also view rows in this dataset as a waterfall chart as follows:
```python
records_to_plot = df_e.to_dict(orient="records") linker.visualisations.waterfall_chart(records_to_plot, filter_nulls=False) ```
python
clusters = linker.clustering.cluster_pairwise_predictions_at_threshold(
df_predict, threshold_match_probability=0.95
)
Completed iteration 1, root rows count 5
Completed iteration 2, root rows count 0
```python linker.visualisations.cluster_studio_dashboard( df_predict, clusters, "dashboards/50k_cluster.html", sampling_method="by_cluster_size", overwrite=True, )
from IPython.display import IFrame
IFrame(src="./dashboards/50k_cluster.html", width="100%", height=1200) ```
python
linker.evaluation.accuracy_analysis_from_labels_column(
"cluster", output_type="roc", match_weight_round_to_nearest=0.02
)
-- 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 trained 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
python
records = linker.evaluation.prediction_errors_from_labels_column(
"cluster",
threshold_match_probability=0.999,
include_false_negatives=False,
include_false_positives=True,
).as_record_dict()
linker.visualisations.waterfall_chart(records)
-- 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 trained 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
```python
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_dict(limit=50)
linker.visualisations.waterfall_chart(records) ```
-- 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 trained 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