Pseudopeople Census to ACS link
Linking the pseudopeople Census and ACS datasets¶
In this tutorial we will configure and link two realistic, simulated datasets generated by the pseudopeople Python package. These datasets reflect a fictional sample population of ~10,000 simulants living in Anytown, Washington, USA, but pseudopeople can also generate datasets about two larger fictional populations, one simulating the state of Rhode Island, and the other simulating the entire United States.
Here we will link Anytown's 2020 Decennial Census dataset to all years of its American Community Survey (ACS) dataset. Some people surveyed in the ACS, particularly in years other than 2020, will not be represented in the 2020 Census because they did not live in Anytown, or were not alive, when the 2020 Census was conducted - pseudopeople simulates people moving residence, being born and dying. So, our aim will be to link as many as possible of the ACS respondents which are reflected in the 2020 Census.
This tutorial is adapted from the Febrl4 linking example.
Configuring pseudopeople¶
pseudopeople is designed to generate realistic datasets which are challenging to link to one another in many of the same ways that actual datasets are challenging to link. This requires adding noise to the data in the form of various types of errors that occur in real data collection and entry. The frequencies of each type of noise in the dataset can be configured for each column.
Because the ACS dataset is small and therefore has less opportunities for noise to create linkage challenges, let's increase the noise for the date_of_birth and last_name columns in both datasets from their default values. For date_of_birth we increase the frequency with which respondents swap the month and day when answering that survey question, and for last_name we increase both the frequency of respondents typing their last names carelessly, and the probability of a mistake on each character when typing carelessly. See here and here for more details.
import duckdb
import pandas as pd
import pseudopeople as psp
from IPython.display import display
from splink.internals.misc import show
pd.options.future.infer_string = False
/home/runner/work/splink/splink/docs/demos/.venv/lib/python3.13/site-packages/vivarium/framework/values.py:140: SyntaxWarning: invalid escape sequence '\p'
* - :math:`\prod_x(1 - p_x)`
config_census = {
"decennial_census": { # Dataset
# "Swap month and day" and "Make typos" are in the
# column-based noise category
"column_noise": {
"date_of_birth": { # Column
"swap_month_and_day": { # Noise type
"cell_probability": 0.15, # Default = .01
},
},
"last_name": { # Column
"make_typos": { # Noise type
"cell_probability": 0.1, # Default = .01
"token_probability": 0.15, # Default = .10
},
},
},
},
}
config_acs = {
"american_community_survey": { # Dataset
# "Swap month and day" and "Make typos" are in the
# column-based noise category
"column_noise": {
"date_of_birth": { # Column
"swap_month_and_day": { # Noise type
"cell_probability": 0.15, # Default = .01
},
},
"last_name": { # Column
"make_typos": { # Noise type
"cell_probability": 0.1, # Default = .01
"token_probability": 0.15, # Default = .10
},
},
},
},
}
Exploring the data¶
Next, let's get the data ready for Splink. The Census data has about 10,000 rows, while the ACS data only has around 200. Note that each dataset has a column called simulant_id, which uniquely identifies a simulated person in our fictional population. The simulant_id is consistent across datasets, and can be used to check the accuracy of our model. Because it represents the truth, and we wouldn't have it in a real-life linkage task, we will not use it for blocking, comparisons, or any other part of our model, except to check the accuracy of our predictions at the end.
census_raw = psp.generate_decennial_census(config=config_census)
acs_raw = psp.generate_american_community_survey(
config=config_acs, year=None
) # generate all years data with year=None
census_count = len(census_raw)
census = duckdb.sql(
"""
select
*,
row_number() over () - 1 as id,
try_cast(age as integer) as age_in_2020
from census_raw
"""
).arrow().read_all()
acs = duckdb.sql(
f"""
select
* exclude (survey_date),
year(survey_date) as year,
{census_count} + row_number() over () - 1 as id,
try_cast(age as integer) - (year(survey_date) - 2020) as age_in_2020
from acs_raw
"""
).arrow().read_all()
simulant_lookup = duckdb.sql(
"""
select id, simulant_id from census
union all
select id, simulant_id from acs
"""
).arrow().read_all()
def prepare_data(data):
return duckdb.sql(
"""
select
*,
case
when street_number is null
or street_name is null
or city is null
or state is null
or zipcode is null
then null
else trim(
street_number || ' ' || street_name || ' ' || city || ' '
|| state || ' ' || zipcode || ' ' || coalesce(unit_number, '')
)
end as address
from data
"""
).arrow().read_all()
dfs = [prepare_data(dataset) for dataset in [census, acs]]
show(dfs[0]) # Census
Applying noise: 0%| | 0/15 [00:00<?, ?type/s]
Applying noise: 7%|▋ | 1/15 [00:00<00:02, 5.40type/s]
Applying noise: 20%|██ | 3/15 [00:00<00:01, 10.87type/s]
Applying noise: 33%|███▎ | 5/15 [00:00<00:01, 6.28type/s]
Applying noise: 60%|██████ | 9/15 [00:00<00:00, 12.40type/s]
Applying noise: 73%|███████▎ | 11/15 [00:01<00:00, 9.86type/s]
Applying noise: 87%|████████▋ | 13/15 [00:01<00:00, 6.14type/s]
Applying noise: 100%|██████████| 15/15 [00:02<00:00, 3.61type/s]
Applying noise: 0%| | 0/15 [00:00<?, ?type/s]
Applying noise: 27%|██▋ | 4/15 [00:00<00:00, 19.41type/s]
Applying noise: 60%|██████ | 9/15 [00:00<00:00, 30.02type/s]
Applying noise: 87%|████████▋ | 13/15 [00:00<00:00, 15.14type/s]
┌─────────────┬──────────────┬────────────┬────────────────┬───────────┬─────────┬───────────────┬───────────────┬──────────────────┬─────────────┬─────────┬─────────┬─────────┬──────────────┬──────────────────────────────────┬─────────┬──────────────────────┬───────┬───────┬─────────────┬─────────────────────────────────────────┐
│ simulant_id │ household_id │ first_name │ middle_initial │ last_name │ age │ date_of_birth │ street_number │ street_name │ unit_number │ city │ state │ zipcode │ housing_type │ relationship_to_reference_person │ sex │ race_ethnicity │ year │ id │ age_in_2020 │ address │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ int64 │ int64 │ int32 │ varchar │
├─────────────┼──────────────┼────────────┼────────────────┼───────────┼─────────┼───────────────┼───────────────┼──────────────────┼─────────────┼─────────┼─────────┼─────────┼──────────────┼──────────────────────────────────┼─────────┼──────────────────────┼───────┼───────┼─────────────┼─────────────────────────────────────────┤
│ 0_2 │ 0_7 │ Diana │ P │ Kofron │ 25 │ 05/06/1994 │ 5112 │ 145th st │ NULL │ Anytown │ WA │ 00000 │ Household │ Reference person │ Female │ White │ 2020 │ 0 │ 25 │ 5112 145th st Anytown WA 00000 │
│ 0_3 │ 0_7 │ Anna │ A │ Kofron │ 25 │ 09/29/1994 │ 5112 │ 145th st │ NULL │ Anytown │ WA │ 00000 │ Household │ Other relative │ Female │ White │ 2020 │ 1 │ 25 │ 5112 145th st Anytown WA 00000 │
│ 0_923 │ 0_8033 │ Gerald │ R │ Butler │ 76 │ 11/03/1943 │ 1130 │ mallory ln │ NULL │ Anytown │ WA │ 00000 │ Household │ Reference person │ Male │ Black │ 2020 │ 2 │ 76 │ 1130 mallory ln Anytown WA 00000 │
│ 0_2641 │ 0_1066 │ Loretta │ T │ Carley │ 61 │ 07/76/1958 │ NULL │ delacorte dr │ NULL │ Anytown │ WA │ 00000 │ Household │ Reference person │ Female │ White │ 2020 │ 3 │ 61 │ NULL │
│ 0_2801 │ 0_1138 │ Richard │ R │ Jones │ 73 │ 03/03/1947 │ 950 │ caribou lane │ NULL │ Anytown │ WA │ 00000 │ Household │ Reference person │ Male │ White │ 2020 │ 4 │ 73 │ 950 caribou lane Anytown WA 00000 │
│ 0_6176 │ 0_2514 │ Sandra │ S │ Runnalls │ 66 │ 03/18/1954 │ 4458 │ windsor pl │ NULL │ Anytown │ WA │ 00000 │ Household │ Reference person │ Female │ Multiracial or Other │ 2020 │ 5 │ 66 │ 4458 windsor pl Anytown WA 00000 │
│ 0_13972 │ 0_5627 │ Jerry │ E │ Murray │ 70 │ 01/03/1950 │ 17868 │ winding trail rd │ NULL │ Anytown │ WA │ 00000 │ Household │ Reference person │ Male │ White │ 2020 │ 6 │ 70 │ 17868 winding trail rd Anytown WA 00000 │
│ 0_13973 │ 0_5627 │ Anita │ R │ Murraj │ 70 │ 11/06/1949 │ 17868 │ winding trail rd │ NULL │ Anytown │ WA │ 00000 │ Household │ Opposite-sex spouse │ Female │ White │ 2020 │ 7 │ 70 │ 17868 winding trail rd Anytown WA 00000 │
│ 0_13974 │ 0_5627 │ Jada │ S │ Murray │ 45 │ 04/11/1974 │ 17868 │ winding trail rd │ NULL │ Anytown │ WA │ 00000 │ Household │ Biological child │ Female │ White │ 2020 │ 8 │ 45 │ 17868 winding trail rd Anytown WA 00000 │
│ 0_13975 │ 0_5627 │ Toni │ K │ Murray │ 44 │ 02/12/1976 │ 17868 │ winding trail rd │ NULL │ Anytown │ WA │ 00000 │ Household │ Biological child │ Female │ White │ 2020 │ 9 │ 44 │ 17868 winding trail rd Anytown WA 00000 │
└─────────────┴──────────────┴────────────┴────────────────┴───────────┴─────────┴───────────────┴───────────────┴──────────────────┴─────────────┴─────────┴─────────┴─────────┴──────────────┴──────────────────────────────────┴─────────┴──────────────────────┴───────┴───────┴─────────────┴─────────────────────────────────────────┘
10 rows 21 columns
show(dfs[1]) # ACS
┌─────────────┬──────────────┬────────────┬────────────────┬───────────┬─────────┬───────────────┬───────────────┬──────────────────┬─────────────┬─────────┬─────────┬─────────┬──────────────┬──────────────────────────────────┬─────────┬────────────────┬───────┬───────┬─────────────┬────────────────────────────────────────┐
│ simulant_id │ household_id │ first_name │ middle_initial │ last_name │ age │ date_of_birth │ street_number │ street_name │ unit_number │ city │ state │ zipcode │ housing_type │ relationship_to_reference_person │ sex │ race_ethnicity │ year │ id │ age_in_2020 │ address │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ int64 │ int64 │ int64 │ varchar │
├─────────────┼──────────────┼────────────┼────────────────┼───────────┼─────────┼───────────────┼───────────────┼──────────────────┼─────────────┼─────────┼─────────┼─────────┼──────────────┼──────────────────────────────────┼─────────┼────────────────┼───────┼───────┼─────────────┼────────────────────────────────────────┤
│ 0_10873 │ 0_4411 │ Betty │ P │ Todd │ 86 │ 09/03/1932 │ 2403 │ magnolia park rd │ NULL │ Anytown │ WA │ 00000 │ Household │ Opposite-sex spouse │ Female │ White │ 2019 │ 10231 │ 87 │ 2403 magnolia park rd Anytown WA 00000 │
│ 0_10344 │ 0_4207 │ Dina │ P │ Thomas │ 46 │ 09/19/1972 │ 4826 │ stone ridge ln │ NULL │ Anytown │ WA │ 00000 │ Household │ Opposite-sex spouse │ Female │ Black │ 2019 │ 10232 │ 47 │ 4826 stone ridge ln Anytown WA 00000 │
│ 0_10345 │ 0_4207 │ Fiona │ M │ Thomas │ 12 │ NULL │ 4826 │ stone ridge ln │ NULL │ Anytown │ WA │ 00000 │ Household │ Biological child │ Female │ Black │ 2019 │ 10233 │ 13 │ 4826 stone ridge ln Anytown WA 00000 │
│ 0_10346 │ 0_4207 │ Molly │ A │ Thomas │ 8 │ 15/03/2010 │ 4826 │ stone ridge ln │ NULL │ Anytown │ WA │ 00000 │ Household │ Biological child │ Female │ Black │ 2019 │ 10234 │ 9 │ 4826 stone ridge ln Anytown WA 00000 │
│ 0_10347 │ 0_4207 │ Daniel │ M │ Thomas │ 18 │ 11/29/2000 │ 4826 │ stone ridge ln │ NULL │ Anytown │ WA │ 00000 │ Household │ Stepchild │ Male │ Black │ 2019 │ 10235 │ 19 │ 4826 stone ridge ln Anytown WA 00000 │
│ 0_13280 │ 0_5337 │ Edwin │ P │ Woodcock │ 55 │ 11/24/1963 │ 925 │ sawmill rd │ NULL │ Anytown │ WA │ 00000 │ Household │ Reference person │ Male │ White │ 2019 │ 10236 │ 56 │ 925 sawmill rd Anytown WA 00000 │
│ 0_1962 │ 0_796 │ Karla │ L │ Broughman │ 46 │ 15/05/1973 │ 5101 │ garden st ext │ NULL │ Anytown │ WA │ 00000 │ Household │ Reference person │ Female │ White │ 2019 │ 10237 │ 47 │ 5101 garden st ext Anytown WA 00000 │
│ 0_1963 │ 0_796 │ Dominic │ R │ Broughman │ 46 │ 08/21/1973 │ 5101 │ garden st ext │ NULL │ Anytown │ WA │ 00000 │ Household │ Opposite-sex spouse │ Female │ White │ 2019 │ 10238 │ 47 │ 5101 garden st ext Anytown WA 00000 │
│ 0_1965 │ 0_796 │ Kylee │ E │ Broughman │ 15 │ 12/09/2003 │ 5101 │ garden st ext │ NULL │ Anytown │ WA │ 00000 │ Household │ Biological child │ Female │ White │ 2019 │ 10239 │ 16 │ 5101 garden st ext Anytown WA 00000 │
│ 0_8685 │ 0_3526 │ Francis │ J │ Turner │ 48 │ 05/12/1971 │ 1432 │ isaac place │ NULL │ Anytown │ WA │ 00000 │ Household │ Opposite-sex unmarried partner │ Male │ Latino │ 2019 │ 10240 │ 49 │ 1432 isaac place Anytown WA 00000 │
└─────────────┴──────────────┴────────────┴────────────────┴───────────┴─────────┴───────────────┴───────────────┴──────────────────┴─────────────┴─────────┴─────────┴─────────┴──────────────┴──────────────────────────────────┴─────────┴────────────────┴───────┴───────┴─────────────┴────────────────────────────────────────┘
10 rows 21 columns
Because we are using all years of ACS surveys, and only the 2020 Decennial Census, there will be respondents to ACS surveys which were not in the 2020 Census because they moved away from Anytown before the 2020 Census was conducted, moved to Anytown after the 2020 Census was conducted, or were not alive during the 2020 Census. Let's check how many of these there are and display some of them.
Note that we cannot simply remove ACS respondents whose surveys indicated they were born after 2020, because their age response could be inaccurate. Also note that we are only able to count and display these simulants for the tutorial because we know the true identities of each simulant - in a real-record linkage scenario, we would not know this information.
not_in_census = duckdb.sql(
"""
select a.*
from acs as a
anti join census as c
on a.simulant_id = c.simulant_id
"""
).arrow().read_all()
display(f"{not_in_census.num_rows} ACS simulants not in 2020 Census")
show(not_in_census, rows=5)
'54 ACS simulants not in 2020 Census'
┌─────────────┬──────────────┬────────────┬────────────────┬─────────────────┬─────────┬───────────────┬───────────────┬─────────────────────┬─────────────┬─────────┬─────────┬─────────┬──────────────┬────────────────────────────────────────────────┬─────────┬────────────────┬───────┬───────┬─────────────┐
│ simulant_id │ household_id │ first_name │ middle_initial │ last_name │ age │ date_of_birth │ street_number │ street_name │ unit_number │ city │ state │ zipcode │ housing_type │ relationship_to_reference_person │ sex │ race_ethnicity │ year │ id │ age_in_2020 │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ int64 │ int64 │ int64 │
├─────────────┼──────────────┼────────────┼────────────────┼─────────────────┼─────────┼───────────────┼───────────────┼─────────────────────┼─────────────┼─────────┼─────────┼─────────┼──────────────┼────────────────────────────────────────────────┼─────────┼────────────────┼───────┼───────┼─────────────┤
│ 0_10347 │ 0_4207 │ Daniel │ M │ Thomas │ 18 │ 11/29/2000 │ 4826 │ stone ridge ln │ NULL │ Anytown │ WA │ 00000 │ Household │ Stepchild │ Male │ Black │ 2019 │ 10235 │ 19 │
│ 0_2713 │ 0_14807 │ Kristi │ J │ Bajn │ 53 │ 08/06/1984 │ 6045 │ s patterson pl │ NULL │ Anytown │ WA │ 00000 │ Household │ Reference person │ Female │ NULL │ 2037 │ 10247 │ 36 │
│ 0_19708 │ 0_3 │ Ariana │ E │ Camarillo Leyva │ 20 │ 05/08/1999 │ 8203 │ west farwell avenue │ NULL │ Anytown │ WA │ 00000 │ Carceral │ Noninstitutionalized group quarters population │ Female │ Latino │ 2020 │ 10304 │ 20 │
│ 0_16379 │ 0_2246 │ Gregory │ J │ Mustafa │ 73 │ 05/30/1963 │ 12679 │ kingston ave │ NULL │ Anytown │ WA │ 00000 │ Household │ Other nonrelative │ Male │ Latino │ 2037 │ 10324 │ 56 │
│ 0_1251 │ 0_9630 │ NULL │ M │ Cotfmaj │ 19 │ 05/06/2004 │ 17947 │ newman dr │ NULL │ Anytown │ WA │ 00000 │ Household │ Reference person │ Male │ White │ 2023 │ 10332 │ 16 │
└─────────────┴──────────────┴────────────┴────────────────┴─────────────────┴─────────┴───────────────┴───────────────┴─────────────────────┴─────────────┴─────────┴─────────┴─────────┴──────────────┴────────────────────────────────────────────────┴─────────┴────────────────┴───────┴───────┴─────────────┘
Next, to better understand which variables will prove useful in linking, we have a look at how populated each column is, as well as the distribution of unique values within each.
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 import DuckDBAPI
from splink.exploratory import completeness_chart
db_api = DuckDBAPI()
dfs_sdf = [db_api.register(df) for df in dfs]
completeness_chart(
dfs_sdf,
table_names_for_chart=["census", "acs"],
cols=[
"age_in_2020",
"last_name",
"sex",
"middle_initial",
"date_of_birth",
"race_ethnicity",
"first_name",
"address",
"street_number",
"street_name",
"unit_number",
"city",
"state",
"zipcode",
],
)
from splink.exploratory import profile_columns
db_api = DuckDBAPI()
dfs_sdf = [db_api.register(df) for df in dfs]
profile_columns(dfs_sdf)
You will notice that six addresses have many more simulants living at them than the others. Simulants in our fictional population may live either in a residential household, or in group quarters (GQ), which models institutional and non-institutional GQ establishments: carceral, nursing homes, and other institutional, and college, military, and other non-institutional. Each of these addresses simulates one of these six types of GQ "households".
In the ACS data there are 103 unique street names (including typos), with 58 residents of the West Farwell Avenue GQ represented.
duckdb.sql(
"""
select street_name, count(*) as record_count
from acs
group by street_name
order by record_count desc
"""
).show()
┌────────────────────────────┬──────────────┐
│ street_name │ record_count │
│ varchar │ int64 │
├────────────────────────────┼──────────────┤
│ west farwell avenue │ 58 │
│ grove street │ 6 │
│ stone ridge ln │ 4 │
│ n holman st │ 4 │
│ cortez cir │ 3 │
│ hamilton avenue │ 3 │
│ glenview rd │ 3 │
│ garden st ext │ 3 │
│ sth wst thornwood dve │ 3 │
│ morris avenue │ 3 │
│ · │ · │
│ · │ · │
│ · │ · │
│ cheshire parkway north │ 1 │
│ e belmont st │ 1 │
│ wessex wy │ 1 │
│ n. northlake wy │ 1 │
│ north michigan avenue │ 1 │
│ grand river boulevard east │ 1 │
│ prospect st │ 1 │
│ grigg st │ 1 │
│ plaza dr │ 1 │
│ northwest topeka boulevard │ 1 │
└────────────────────────────┴──────────────┘
104 rows (20 shown) 2 columns
Defining the model¶
Next let's come up with some candidate blocking rules, which define the record comparisons to generate, and have a look at how many comparisons each rule will generate.
For blocking rules that we use in prediction, our aim is to have the union of all rules cover all true matches, whilst avoiding generating so many comparisons that it becomes computationally intractable - i.e. each true match should have at least one of the following conditions holding.
from splink import DuckDBAPI, block_on
from splink.blocking_analysis import (
chart_comparisons_from_blocking_rules,
)
blocking_rules = [
block_on("first_name"),
block_on("last_name"),
block_on("date_of_birth"),
block_on("street_name"),
block_on("age_in_2020", "sex"),
block_on("age_in_2020", "race_ethnicity"),
block_on("age_in_2020", "middle_initial"),
block_on("street_number"),
block_on("middle_initial", "sex", "race_ethnicity"),
]
db_api = DuckDBAPI()
dfs_sdf = [db_api.register(df) for df in dfs]
chart_comparisons_from_blocking_rules(
dfs_sdf,
blocking_rules=blocking_rules,
link_type="link_only",
unique_id_column_name="id",
source_dataset_column_name="source_dataset",
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."first_name" = r."first_name"' is based on 191 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."last_name" = r."last_name"' is based on 25 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 2 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."street_name" = r."street_name"' is based on 331 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."age_in_2020" = r."age_in_2020") AND (l."sex" = r."sex")' is based on 507 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."age_in_2020" = r."age_in_2020") AND (l."race_ethnicity" = r."race_ethnicity")' is based on 237 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."age_in_2020" = r."age_in_2020") AND (l."middle_initial" = r."middle_initial")' is based on 24 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."street_number" = r."street_number"' is based on 24 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(
For columns like age_in_2020 that would create too many comparisons, we combine them with one or more other columns which would also create too many comparisons if blocked on alone.
Now we get our model's settings by including the blocking rules, as well as deciding the actual comparisons we will be including in our model.
To estimate probability_two_random_records_match, we will make the assumption that everyone in the ACS is also in the Census - we know from our check using the "true" simulant IDs above that this is not the case, but it is a good approximation. Depending on our knowledge of the dataset, we might be able to get a more accurate value by defining a set of deterministic matching rules and a guess of the number of matches reflected in those rules.
import splink.comparison_library as cl
from splink import Linker, SettingsCreator
settings = SettingsCreator(
unique_id_column_name="id",
link_type="link_only",
blocking_rules_to_generate_predictions=blocking_rules,
comparisons=[
cl.NameComparison("first_name", jaro_winkler_thresholds=[0.9]).configure(
term_frequency_adjustments=True
),
cl.ExactMatch("middle_initial").configure(term_frequency_adjustments=True),
cl.NameComparison("last_name", jaro_winkler_thresholds=[0.9]).configure(
term_frequency_adjustments=True
),
cl.DamerauLevenshteinAtThresholds(
"date_of_birth", distance_threshold_or_thresholds=[1]
),
cl.DamerauLevenshteinAtThresholds("address").configure(
term_frequency_adjustments=True
),
cl.ExactMatch("sex"),
],
retain_intermediate_calculation_columns=True,
probability_two_random_records_match=dfs[1].num_rows / (dfs[0].num_rows * dfs[1].num_rows),
)
db_api = DuckDBAPI()
dfs_sdf_0 = db_api.register(dfs[0], dataset_display_name="census")
dfs_sdf_1 = db_api.register(dfs[1], dataset_display_name="acs")
simulant_lookup_sdf = db_api.register(
simulant_lookup,
table_name="simulant_lookup",
dataset_display_name="simulant_lookup",
)
linker = Linker([dfs_sdf_0, dfs_sdf_1], settings)
Estimating model parameters¶
Next we estimate u and m values for each comparison, so that we can move to generating predictions.
# We generally recommend setting max pairs higher (e.g. 1e7 or more)
# But this will run faster for the purpose of this demo
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 -----
Estimating u with: max_pairs = 1,000,000, min_count_per_level = 100, num_chunks = 10
Estimating u for: first_name (Comparison 1 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 8 for comparison level Jaro-Winkler distance of first_name >= 0.9 (cvv=1)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 106 for level Jaro-Winkler distance of first_name >= 0.9 (cvv=1). Chunk took 0.1 seconds.
Exiting early since min count of 106 exceeds min_count_per_level = 100
Estimating u for: middle_initial (Comparison 2 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 669 for comparison level Exact match on middle_initial (cvv=1)
Exiting early since min count of 669 exceeds min_count_per_level = 100
Estimating u for: last_name (Comparison 3 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 1 for comparison level Exact match on last_name (cvv=2)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 38 for level Jaro-Winkler distance of last_name >= 0.9 (cvv=1). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 76 for level Jaro-Winkler distance of last_name >= 0.9 (cvv=1). Chunk took 0.1 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 107 for level Jaro-Winkler distance of last_name >= 0.9 (cvv=1). Chunk took 0.1 seconds.
Exiting early since min count of 107 exceeds min_count_per_level = 100
Estimating u for: date_of_birth (Comparison 4 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 1 for comparison level Exact match on date_of_birth (cvv=2)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
Count of 9 for level Exact match on date_of_birth (cvv=2). Chunk took 0.5 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
Count of 17 for level Exact match on date_of_birth (cvv=2). Chunk took 0.7 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
Count of 24 for level Exact match on date_of_birth (cvv=2). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 4/10
Count of 30 for level Exact match on date_of_birth (cvv=2). Chunk took 0.6 seconds.
Min u_count not hit, continuing.
Running chunk 5/10
Count of 41 for level Exact match on date_of_birth (cvv=2). Chunk took 0.7 seconds.
Min u_count not hit, continuing.
Running chunk 6/10
Count of 52 for level Exact match on date_of_birth (cvv=2). Chunk took 0.3 seconds.
Min u_count not hit, continuing.
Running chunk 7/10
Count of 59 for level Exact match on date_of_birth (cvv=2). Chunk took 0.4 seconds.
Min u_count not hit, continuing.
Running chunk 8/10
Count of 64 for level Exact match on date_of_birth (cvv=2). Chunk took 0.6 seconds.
Min u_count not hit, continuing.
Running chunk 9/10
Count of 68 for level Exact match on date_of_birth (cvv=2). Chunk took 0.6 seconds.
Min u_count not hit, continuing.
Running chunk 10/10
Count of 77 for level Exact match on date_of_birth (cvv=2). Chunk took 0.4 seconds.
Min u_count not hit, continuing.
Estimating u for: address (Comparison 5 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 0 for comparison level Damerau-Levenshtein distance of address <= 1 (cvv=2)
Probe did not converge; restarting with normal chunking
Running chunk 1/10
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Count of 0 for level Damerau-Levenshtein distance of address <= 1 (cvv=2). Chunk took 5.6 seconds.
Min u_count not hit, continuing.
Running chunk 2/10
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Count of 0 for level Damerau-Levenshtein distance of address <= 1 (cvv=2). Chunk took 3.1 seconds.
Min u_count not hit, continuing.
Running chunk 3/10
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Count of 64 for level Damerau-Levenshtein distance of address <= 1 (cvv=2). Chunk took 4.1 seconds.
Min u_count not hit, continuing.
Running chunk 4/10
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Count of 100 for level Damerau-Levenshtein distance of address <= 1 (cvv=2). Chunk took 4.0 seconds.
Exiting early since min count of 100 exceeds min_count_per_level = 100
Estimating u for: sex (Comparison 6 of 6)
Running probe chunk (~1.00% of max_pairs)
Min u_count: 4,217 for comparison level Exact match on sex (cvv=1)
Exiting early since min count of 4,217 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).
- middle_initial (no m values are trained).
- last_name (no m values are trained).
- date_of_birth (no m values are trained).
- address (no m values are trained).
- sex (no m values are trained).
When training the m values using expectation maximisation, we need some more blocking rules to reduce the total number of comparisons. For each rule, we want to ensure that we have neither proportionally too many matches, or too few.
We must run this multiple times using different rules so that we can obtain estimates for all comparisons - if we block on e.g. date_of_birth, then we cannot compute the m values for the date_of_birth comparison, as we have only looked at records where these match.
session_dob = linker.training.estimate_parameters_using_expectation_maximisation(
block_on("date_of_birth"), estimate_without_term_frequencies=True
)
session_ln = linker.training.estimate_parameters_using_expectation_maximisation(
block_on("last_name"), 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."date_of_birth" = r."date_of_birth"
Parameter estimates will be made for the following comparison(s):
- first_name
- middle_initial
- last_name
- address
- sex
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.303 in the m_probability of address, level `Exact match on address`
Iteration 2: Largest change in params was 0.0025 in probability_two_random_records_match
Iteration 3: Largest change in params was 6.64e-05 in probability_two_random_records_match
EM converged after 3 iterations
Your model is not yet fully trained. Missing estimates for:
- date_of_birth (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."last_name" = r."last_name"
Parameter estimates will be made for the following comparison(s):
- first_name
- middle_initial
- date_of_birth
- address
- sex
Parameter estimates cannot be made for the following comparison(s) since they are used in the blocking rules:
- last_name
Iteration 1: Largest change in params was 0.212 in the m_probability of date_of_birth, level `All other comparisons`
Iteration 2: Largest change in params was 0.0231 in the m_probability of date_of_birth, level `All other comparisons`
Iteration 3: Largest change in params was 0.00951 in the m_probability of first_name, level `All other comparisons`
Iteration 4: Largest change in params was 0.00507 in the m_probability of first_name, level `All other comparisons`
Iteration 5: Largest change in params was 0.00294 in the m_probability of first_name, level `All other comparisons`
Iteration 6: Largest change in params was 0.0018 in the m_probability of first_name, level `All other comparisons`
Iteration 7: Largest change in params was 0.00114 in the m_probability of first_name, level `All other comparisons`
Iteration 8: Largest change in params was 0.000743 in the m_probability of first_name, level `All other comparisons`
Iteration 9: Largest change in params was 0.000492 in the m_probability of first_name, level `All other comparisons`
Iteration 10: Largest change in params was 0.000328 in the m_probability of first_name, level `All other comparisons`
Iteration 11: Largest change in params was 0.000221 in the m_probability of first_name, level `All other comparisons`
Iteration 12: Largest change in params was 0.000149 in the m_probability of first_name, level `All other comparisons`
Iteration 13: Largest change in params was 0.000101 in the m_probability of first_name, level `All other comparisons`
Iteration 14: Largest change in params was 6.81e-05 in the m_probability of first_name, level `All other comparisons`
EM converged after 14 iterations
Your model is fully trained. All comparisons have at least one estimate for their m and u values
If we wish we can have a look at how our parameter estimates changes over these training sessions.
session_dob.m_u_values_interactive_history_chart()
session_ln.m_u_values_interactive_history_chart()
For variables that aren't used in the m-training blocking rules, we have two estimates --- one from each of the training sessions (see for example address). We can have a look at how the values compare between them, to ensure that we don't have drastically different values, which may be indicative of an issue.
linker.visualisations.parameter_estimate_comparisons_chart()
We can now visualise some of the details of our model. We can look at the match weights, which tell us the relative importance for/against a match for each of our comparison levels.
linker.visualisations.match_weights_chart()
As well as the match weights, which give us an idea of the overall effect of each comparison level, we can also look at the individual u and m parameter estimates, which tells us about the prevalence of coincidences and mistakes (for further details/explanation about this see this article). We might want to revise aspects of our model based on the information we ascertain here.
Note however that some of these values are very small, which is why the match weight chart is often more useful for getting a decent picture of things.
linker.visualisations.m_u_parameters_chart()
It is also useful to have a look at unlinkable records - these are records which do not contain enough information to be linked at some match probability threshold. We can figure this out by seeing whether records are able to be matched with themselves.
We have low column missingness, so almost all of our records are linkable for almost all match thresholds.
linker.evaluation.unlinkables_chart()
Making predictions and evaluating results¶
predictions = linker.inference.predict() # include all match_probabilities
columns_to_show = [
"match_probability",
"first_name_l",
"first_name_r",
"last_name_l",
"last_name_r",
"date_of_birth_l",
"date_of_birth_r",
"address_l",
"address_r",
]
predictions.as_duckdbpyrelation().project(
", ".join(columns_to_show)
).show(max_width=10000)
Blocking time: 0.10 seconds
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Predict time (post-blocking): 2.80 seconds
┌────────────────────────┬──────────────┬──────────────┬─────────────┬─────────────┬─────────────────┬─────────────────┬───────────────────────────────────────────┬─────────────────────────────────────────────┐
│ match_probability │ first_name_l │ first_name_r │ last_name_l │ last_name_r │ date_of_birth_l │ date_of_birth_r │ address_l │ address_r │
│ double │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├────────────────────────┼──────────────┼──────────────┼─────────────┼─────────────┼─────────────────┼─────────────────┼───────────────────────────────────────────┼─────────────────────────────────────────────┤
│ 0.9999999999876885 │ Betty │ Betty │ Todd │ Todd │ 09/03/1932 │ 09/03/1932 │ 2403 magnolia park rd Anytown WA 00000 │ 2403 magnolia park rd Anytown WA 00000 │
│ 0.9999999999566469 │ Dina │ Dina │ Thomas │ Thomas │ 09/19/1972 │ 09/19/1972 │ 4826 stone ridge ln Anytown WA 00000 │ 4826 stone ridge ln Anytown WA 00000 │
│ 0.9999928107339112 │ Fiona │ Fiona │ Thomas │ Thomas │ NULL │ 05/23/2006 │ 4826 stone ridge ln Anytown WA 00000 │ 4826 stone ridge ln Anytown OK 00000 │
│ 0.9999960251328948 │ Molly │ Molly │ Thomas │ Thomas │ 15/03/2010 │ 03/15/2010 │ 4826 stone ridge ln Anytown WA 00000 │ 4826 stone ridge ln Anytown WA 00000 │
│ 1.0943900809063874e-06 │ Daniel │ Daniel │ Thomas │ Cloutier │ 11/29/2000 │ 01/02/1982 │ 4826 stone ridge ln Anytown WA 00000 │ 504 hwy 69 Anytown WA 00000 │
│ 1.176456783823678e-05 │ Edwin │ Edwin │ Woodcock │ Cahill │ 11/24/1963 │ 12/26/1988 │ 925 sawmill rd Anytown WA 00000 │ 4180 12th st Anytown WA 00000 │
│ 3.1371565780441643e-05 │ Karla │ Karla │ Broughman │ Venkatesan │ 15/05/1973 │ 09/04/2009 │ 5101 garden st ext Anytown WA 00000 │ 17832 nw 20th st Anytown WA 00000 │
│ 3.123478222458602e-07 │ Dominic │ Dominic │ Broughman │ Norris │ 08/21/1973 │ 08/30/1998 │ 5101 garden st ext Anytown WA 00000 │ 210 huntington green court Anytown WA 00000 │
│ 1.5686028937865174e-05 │ Kylee │ Kylee │ Broughman │ Ciccarelli │ 12/09/2003 │ 28/10/2013 │ 5101 garden st ext Anytown WA 00000 │ 4240 118 avenu Anytown WA 00000 │
│ 0.0022735146457675544 │ Francis │ Francis │ Turner │ Jones │ 05/12/1971 │ 07/10/1952 │ 1432 isaac place Anytown WA 00000 │ 37r s ave 64 Anytown WA 00000 │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ 1.2357894166147727e-07 │ Raymond │ Cory │ Thrower │ Hindman │ 04/24/1990 │ 05/11/1986 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 1.2357894166147727e-07 │ James │ Cory │ Starr │ Hindman │ 05/09/1984 │ 05/11/1986 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 1.2357894166147727e-07 │ Alexander │ Cory │ Ciotola │ Hindman │ 10/06/1999 │ 05/11/1986 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 2.8066417748665034e-08 │ Vicky │ Cory │ Done │ Hindman │ 12/03/1949 │ 05/11/1986 │ 8203 west farwell avenue Anytown TN 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 1.2357894166147727e-07 │ Matthew │ Cory │ Speer │ Hindman │ 02/13/2000 │ 05/11/1986 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 1.8132007667964547e-10 │ Gabrielle │ Cory │ Hetzel │ Hindman │ 03/19/1999 │ 05/11/1986 │ NULL │ 8203 west farwell avenue Anytown WA 00000 │
│ 1.2357894166147727e-07 │ Loren │ Cory │ Bobo │ Hindman │ 11/14/1972 │ 05/11/1986 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 6.9720579287583e-09 │ Brianna │ Cory │ Sheltra │ Hindman │ 06/07/2000 │ 05/11/1986 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 6.9720579287583e-09 │ Makayla │ Cory │ Butler │ Hindman │ 07/10/1999 │ 05/11/1986 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 3.2138783056466032e-09 │ Shawn │ Cory │ Tyler │ Hindman │ 10/20/1999 │ 05/11/1986 │ NULL │ 8203 west farwell avenue Anytown WA 00000 │
└────────────────────────┴──────────────┴──────────────┴─────────────┴─────────────┴─────────────────┴─────────────────┴───────────────────────────────────────────┴─────────────────────────────────────────────┘
? rows (>9999 rows, 20 shown) 9 columns
We can see how our model performs at different probability thresholds, with a couple of options depending on the space we wish to view things. The chart below shows that for a match weight around 6.4, or probability around 99%, our model has 2 false positives and 12 false negatives.
These false negatives make sense, since we added a high degree of noise to date_of_birth, a very important matching column.
linker.evaluation.accuracy_analysis_from_labels_column(
"simulant_id", output_type="accuracy"
)
Blocking time: 0.08 seconds
FloatProgress(value=0.0, layout=Layout(width='auto'), style=ProgressStyle(bar_color='black'))
Predict time (post-blocking): 2.07 seconds
And we can easily see how many individuals we identify and link by looking at clusters generated at some threshold match probability of interest - let's choose 99% again for this example.
clusters = linker.clustering.cluster_pairwise_predictions_at_threshold(
predictions, threshold_match_probability=0.99
)
cluster_size_distribution = clusters.query_sql(
"""
select cluster_size, count(*) as cluster_count
from (
select cluster_id, count(*) as cluster_size
from {this}
group by cluster_id
)
group by cluster_size
order by cluster_size
"""
)
cluster_size_distribution.as_duckdbpyrelation().show()
Completed iteration 1, num edges remaining to process: 0
┌──────────────┬───────────────┐
│ cluster_size │ cluster_count │
│ int64 │ int64 │
├──────────────┼───────────────┤
│ 1 │ 10149 │
│ 2 │ 142 │
│ 3 │ 3 │
└──────────────┴───────────────┘
There are a couple of interactive visualizations which can be useful for understanding and evaluating results.
from IPython.display import IFrame
linker.visualisations.cluster_studio_dashboard(
predictions,
clusters,
"./dashboards/pseudopeople_cluster_studio.html",
sampling_method="by_cluster_size",
overwrite=True,
)
# You can view the cluster_studio.html file in your browser,
# or inline in a notbook as follows
IFrame(src="./dashboards/pseudopeople_cluster_studio.html", width="100%", height=1000)
linker.visualisations.comparison_viewer_dashboard(
predictions, "./dashboards/pseudopeople_scv.html", overwrite=True
)
IFrame(src="./dashboards/pseudopeople_scv.html", width="100%", height=1000)
In this example we know what the true links are, so we can also manually inspect the ones with the lowest match weights to see what our model is not capturing - i.e. where we have false negatives.
Similarly, we can look at the non-links with the highest match weights, to see whether we have an issue with false positives.
Ordinarily we would not have this luxury, and so would need to dig a bit deeper for clues as to how to improve our model, such as manually inspecting records across threshold probabilities.
df_predictions = predictions.query_sql(
"""
select
p.*,
sl.simulant_id as simulant_id_l,
sr.simulant_id as simulant_id_r
from {this} as p
left join simulant_lookup as sl
on p.id_l = sl.id
left join simulant_lookup as sr
on p.id_r = sr.id
"""
)
# sort links by lowest match_probability to see if we missed any
links = df_predictions.query_sql(
"""
select *
from {this}
where simulant_id_l = simulant_id_r
order by match_weight
"""
)
# sort nonlinks by highest match_probability to see if we matched any
nonlinks = df_predictions.query_sql(
"""
select *
from {this}
where simulant_id_l != simulant_id_r
order by match_weight desc
"""
)
links.as_duckdbpyrelation().project(
", ".join(columns_to_show)
).limit(15).show(max_width=10000)
┌───────────────────────┬──────────────┬──────────────┬─────────────┬─────────────┬─────────────────┬─────────────────┬───────────────────────────────────────────────┬───────────────────────────────────────────┐
│ match_probability │ first_name_l │ first_name_r │ last_name_l │ last_name_r │ date_of_birth_l │ date_of_birth_r │ address_l │ address_r │
│ double │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├───────────────────────┼──────────────┼──────────────┼─────────────┼─────────────┼─────────────────┼─────────────────┼───────────────────────────────────────────────┼───────────────────────────────────────────┤
│ 0.0020830614117420955 │ Fxigy │ Faigy │ Wallace │ Wqolace │ 05/18/1980 │ 18/05/1980 │ 12679 kingston ave Anytown WA 00000 │ 12679 kingston ave Anytown WA 00000 │
│ 0.008152372053953562 │ Antoinette │ Antoinette │ Strawn │ Sggawg │ 23/08/1997 │ 08/23/1997 │ 1700 north kilpatrick street Anytown WA 00000 │ 510 holcomb ave Anytown WA 00000 │
│ 0.022337802235084806 │ Peter │ Peter │ Giahnoia │ Giajgnkla │ 12/31/1952 │ 31/12/1952 │ NULL │ 7171 glenhaven circle Anytown WA 00000 │
│ 0.025114312449847256 │ Bailey │ Bailey │ Lapolla │ KapklUa │ 09/12/2000 │ 12/09/2000 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 wrst farwell avenie Anytown WA 000o0 │
│ 0.08568445110059343 │ Elizabeth │ Elizabeth │ Four │ Frost │ 08/29/1996 │ 29/08/1996 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 0.181110359793545 │ Zachary │ Zachary │ Coilier │ Colliwr │ 20/01/2003 │ 01/20/2003 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 0.5261212534457775 │ Rmily │ Emily │ Yancey │ Yancey │ 12/24/1992 │ 12/24/1993 │ 8 kline st Anytown WA 00000 ap # 97 │ 610 n 54th ln Anytown WA 00000 │
│ 0.5268991310101353 │ Christy │ Boy │ Cornel │ Cognel │ 12/20/1964 │ 20/12/1964 │ 8203 west farwell avenue Anytown WA 00000 │ 8203 west farwell avenue Anytown WA 00000 │
│ 0.7423883729903563 │ Benjamin │ Benjamin │ Allen │ Allen │ 02/26/2001 │ 02/26/z001 │ 8203 west farwell avenue Anytown WA 00000 │ 2002 203rd pl se Anytown WA 00000 │
│ 0.7863113955052137 │ Susan │ Susan │ Winchell │ Wknchell │ 05/18/1967 │ 18/05/1967 │ 301 downer st Anytown WA 00000 │ 204 linden road Anytown WA 00000 │
│ 0.9258962112548851 │ Jessica │ Jessica │ Martin │ Marrin │ 05/10/1981 │ 10/05/1981 │ 152 glenview rd Anytown WA 00000 apt 226 │ 122 rue royale Anytown WA 00000 │
│ 0.9336401809263977 │ Barbara │ Barbara │ Anderson │ Anderson │ 28/12/1964 │ 12/28/1964 │ 1471 south e pine street Anytown WA 00000 │ 375 jefferson st Anytown WA 00000 │
│ 0.988024647733004 │ Brielle │ Brielle │ Gonzalez │ Gonzalez │ 27/11/2000 │ 11/27/2000 │ 8203 west farwell avenue Anytown WA 00000 │ 233 saint peters road Anytown WA 00000 │
│ 0.9931409889847075 │ Karla │ Karla │ Broughman │ Bdoughkab │ 15/05/1973 │ 05/15/1973 │ 5101 garden st ext Anytown WA 00000 │ 5101 garden st ext Anytown WA 00000 │
│ 0.993819813328762 │ Leonard │ Leonard │ Gimlin │ Nimlin │ 16/10/2007 │ 16/10/2007 │ 16 w ocotillo rd Anytown WA 00000 │ 6117 granny smith court Anytown WA 00000 │
└───────────────────────┴──────────────┴──────────────┴─────────────┴─────────────┴─────────────────┴─────────────────┴───────────────────────────────────────────────┴───────────────────────────────────────────┘
15 rows 9 columns
As you can see, nearly all of the false negatives (links with match probabilities below our chosen 99% threshold) are due to month/day swaps.
nonlinks.as_duckdbpyrelation().project(
", ".join(columns_to_show)
).limit(5).show(max_width=10000)
┌────────────────────┬──────────────┬──────────────┬────────────────┬────────────────┬─────────────────┬─────────────────┬──────────────────────────────────────────────────────────┬──────────────────────────────────────────────────────────┐
│ match_probability │ first_name_l │ first_name_r │ last_name_l │ last_name_r │ date_of_birth_l │ date_of_birth_r │ address_l │ address_r │
│ double │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├────────────────────┼──────────────┼──────────────┼────────────────┼────────────────┼─────────────────┼─────────────────┼──────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────┤
│ 0.9951674058759886 │ Tonya │ Ryan │ Maiden │ Maiden │ 04/02/1975 │ 03/01/2008 │ 7214 wild plum ct Anytown WA 00000 │ 7214 wild plum ct Anytown WA 00000 │
│ 0.9919282202099076 │ Francis │ Angela │ Turner │ Turner │ 05/12/1971 │ 05/12/1971 │ 1432 isaac place Anytown WA 00000 │ 1432 isaac place Anytown WA 00000 │
│ 0.9755339647738995 │ David │ Alex │ Middleton │ Middleton │ 09/19/1967 │ 29/03/2001 │ 6634 beachplum way Anytown WA 00000 │ 6634 beachplum way Anytown WA 00000 │
│ 0.9755339647738995 │ David │ Frederick │ Middleton │ Middleton │ 09/19/1967 │ 07/23/1999 │ 6634 beachplum way Anytown WA 00000 │ 6634 beachplum way Anytown WA 00000 │
│ 0.970412747948424 │ Charles │ Lorraine │ Estrada Canche │ Estrada Canche │ 01/06/1931 │ 11/25/1934 │ 1009 northwest topeka boulevard Anytown WA 00000 no 72 l │ 1009 northwest topeka boulevard Anytown WA 00000 no 72 l │
└────────────────────┴──────────────┴──────────────┴────────────────┴────────────────┴─────────────────┴─────────────────┴──────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────┘
Note that not all columns used for comparisons are displayed in the tables above due to space. The waterfall charts below show some of the lowest match weight true links and highest match weight true nonlinks in more detail.
records_to_view = 15
linker.visualisations.waterfall_chart(links.as_record_list(limit=records_to_view))
records_to_view = 5
linker.visualisations.waterfall_chart(nonlinks.as_record_list(limit=records_to_view))
We may also wish to evaluate the effects of using term frequencies for some columns, such as address, by looking at examples of the values tf_address for both common and uncommon address values. Common addresses such as the GQ household, displayed first in the waterfall chart below, will have smaller (negative) values for tf_address, while uncommon addresses will have larger (positive) values.
linker.visualisations.waterfall_chart(
# choose comparisons that have a term frequency adjustment for address
df_predictions.query_sql(
"""
select *
from {this}
where mw_tf_adj_address != 0
order by mw_tf_adj_address
limit 10
"""
).as_record_list()
)