Expressing blocking rule where token in left is present in right or vice versa

[adelinor]

Hi,

I would like to express a blocking rule that verifies that a token in the left expression is found in the right expression. For example
l.firstnames="jens pieter johan" and r.firstnames="johan" both include "johan" so the corresponding rows should be considered.

Kind regards

[adelinor]

Thanks to @NickCrews 's answer on #1053 and also the codes samples from the Splink in Databricks - Expert Answer.py, I am able to answer myself:

1. Split values to an array column, say names_array
2. For the DuckDB, the blocking rule would then be (list_unique(l.names_array) + list_unique(r.names_array) - list_unique(list_concat(l.names_array, r.names_array)) > 0)

[adelinor]

Good point.

Assuming that values in the names column are unique and sorted, would it help the performance to write a more permissive blocking rule such as l.names=r.names OR length(l.names) <> length(r.names)?

[checkbook-org]

@adelinor I'm pretty interested in the direction you were originally going. It seems to be that both in blocking rules and in comparisons there are a lot of cases where multiple fields are correlated but much better tested individually. Names, following the example you describe, seem like one of them.

All that I've read about the fastLink/splink methodology indicates that it requires the assumption of independence of different fields. Coming close to this requires things like merging addresses into a single field, because at least state/province and postal code are highly correlated. But that approach rules out a number of important ways of comparing records, such as distance between two addresses, or whether the address is in the same county, etc. I'm not sure even how to express this, but I think there's some sort of hierarchical comparison that groups related fields together, that would be more faithful to the underlying data.
I don't imagine that's any easy task - it probably requires re-doing the Felligi-Sunter model's mathematics. However, if anyone knows of resources about doing this I, at least, would find them very valuable.

Thanks!

Eric

[checkbook-org]

Just want to add one more thing. Having read more about ComparisonLevels, I think the thing you were trying to do in your blocking rules also has utility within ComparisonLevels. It is often the case that names to be matched arrive in an unclear order. While one can set the order in data prep, it seems to me that could potentially destroy important information, as most people's names are likely to be in the right order.
I will likely work on something to compare names using the array approach you laid out. If you're interested in collaborating please message me.
Eric

[NickCrews]

All that I've read about the fastLink/splink methodology indicates that it requires the assumption of independence of different fields

I want to make sure you don't have a misunderstanding here. There are two different concepts at play here: Fields, and Comparisons. The methodology requires that Comparisons are uncorrelated. It's fine for Fields to be correlated. So for example, you could have zipcode, city, and state fields, and those are correlated, and that's fine. You just need to combine those into a single Comparison. The street line of an address, however, is fairly uncorrelated with zipcode/city/state (eg a lot of cities might have a 123 main street). So I might have a single comparison based off the street field, and another comparison that merges together the info for zipcode/city/state.

You're right however that things fall apart once you want to include lat/longs, which is dependent on all of street/city/state/zipcode. At that point, I think your best bet is to just make one Comparison that unifies it all together. But I see what you're saying, it feels like there should be a natural way to do this, and I think you're right that the Felligi-Sunter model just won't work.

[adelinor]

If you're interested in collaborating please message me.

Sure I would love to.

[checkbook-org]

Great! I’ll be in touch next week. Eric From: Adelino Rodrigues ***@***.***> Sent: Friday, February 24, 2023 2:40 AM To: moj-analytical-services/splink ***@***.***> Cc: Eric Ellsworth ***@***.***>; Comment ***@***.***> Subject: Re: [moj-analytical-services/splink] Expressing blocking rule where token in left is present in right or vice versa (Discussion #1032) EXTERNAL EMAIL - This email originated from outside our organization. Be cautious of links and attachments. If you're interested in collaborating please message me. Sure I would love to. — Reply to this email directly, view it on GitHub<#1032 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/APDMNRAFD4L6W6OYS5UWMZDWZBQWXANCNFSM6AAAAAAUT54TLY>. You are receiving this because you commented.Message ID: ***@***.******@***.***>>