Entity resolution of PERSON entities with multiple addresses

[JonnyNZCustoms]

Hello,

I am exploring using Splink for the first time for deduplicating PERSON entities in a data source. Thanks so much for the effort and thought that has gone into Splink. It is a great product and very easy to use!

For my data it is common for a PERSON to have multiple associated addresses (and phone numbers, e-mail addresses). Is there a preferred method for making use of the information in multiple addresses?

If you had a maximum of 2 addresses you could generate 4 comparisons like:
ADDR1.l : ADDR1.r
ADDR2.l : ADDR2.r
ADDR1.l : ADDR2.r
ADDR2.l : ADDR1.r
but this will get unsustainable when there are 3, 4 or more addresses.

I also thought about using a graph approach. You could create a graph using PERSON and ADDRESS entities as nodes, with edges between nodes when a PERSON is associated with an ADDRESS. You could then calculate the shortest path between PERSON entities to determine if they share an ADDRESS. However, calculation of shortest paths seems like an expensive operation to run for every comparison in my blocks. Another downside is that any de-duplications would change the graph so that you would require an iterative approach.

Thank you so much for reading my question!

[RobinL]

If you store the addresses in an array column (i.e. a single column, with each row containing a list of addresses), then you can use an array intersection comparison

import splink.duckdb.comparison_library as cl
cl.array_intersect_at_sizes("first_name", [3, 1])

If you need the comparisons to also allow for fuzzy matches, it's possible to do that using the Spark linker at the moment, see this comment:
#1994 (comment)

More broadly this issue contains some ideas, and also some sample code for how a fuzzy array comparison could be implemented in duckdb:
#1994

[JonnyNZCustoms]

Thanks so much for your reply, it is much appreciated :)

I will work through those suggestions!

[JonnyNZCustoms]

Hi Robin, thanks again from your reply. I am really enjoying applying Splink!

For 2 out of the 3 array columns I have exact matching is appropriate and those are working really well :)

For my 3rd array column fuzzy matching is more appropriate and will be more effective. I have followed the links above and looked through the various resources here, but remain unsure how to apply the code here: https://gist.github.com/RobinL/d8a84f7a31fa7cb17dafb05c94518225 (where I have also posted a reply) into my Splink workflow. Any help would be much appreciated. I.e. a link to an example of how to create a complicated (i.e. more that 1 line) custom SQL comparison or some specific guidance or code for applying fuzzy matching to an array comparison using DuckDB.

Thanks again!

[RobinL]

Sorry have been on leave. Nice one - thanks for posting the solution you found on the gist.

Let me know if you're still having problems but sounds like you're good now?

For future readers, I'm going to drop a full runnable example here using your code:

import pandas as pd

import splink.duckdb.comparison_level_library as cll
from splink.duckdb.blocking_rule_library import block_on
from splink.duckdb.comparison_library import exact_match
from splink.duckdb.linker import DuckDBLinker

data = [
    {
        "unique_id": 1,
        "first_name": "John",
        "phone_numbers": ["123456", "654321"],
    },
    {
        "unique_id": 2,
        "first_name": "John",
        "phone_numbers": ["123455"],
    },
    {
        "unique_id": 3,
        "first_name": "John",
        "phone_numbers": ["123456"],
    },
    {
        "unique_id": 4,
        "first_name": "John",
        "phone_numbers": ["9999", "8888"],
    },
]

df = pd.DataFrame(data)


comparison_fuzzy_array_phone = {
    "output_column_name": "phone_numbers",
    "comparison_description": "Fuzzy array phone numbers",
    "comparison_levels": [
        {
            "sql_condition": "phone_numbers_l IS NULL OR phone_numbers_r IS NULL OR phone_numbers_l == [] OR phone_numbers_r == []",
            "label_for_charts": "Null",
            "is_null_level": True,
        },
        cll.array_intersect_level("phone_numbers", 1),
        {
            "sql_condition": " list_reduce( list_transform(    flatten(list_transform(phone_numbers_l, x -> (list_transform(phone_numbers_r, y -> [x,y])))) , x -> levenshtein(x[1], x[2]) < 2  )    , (x,y) -> x or y ) ",
            "label_for_charts": "Levenshtein <= 2",
        },
        {"sql_condition": "ELSE", "label_for_charts": "All other comparisons"},
    ],
}

settings = {
    "probability_two_random_records_match": 0.01,
    "link_type": "dedupe_only",
    "comparisons": [
        comparison_fuzzy_array_phone,
        exact_match("first_name"),
        
    ],
    "retain_intermediate_calculation_columns": True,
}


linker = DuckDBLinker(df, settings)

linker.predict().as_pandas_dataframe()