Filter condition and deduplication

[leostimpfle]

Setup

I am wondering how to correctly specify a filter condition in blocking rules when deduplicating a dataset. Specifically, suppose I observe data on individuals over different time periods where the period is indicated by two columns: the first year of observation year_first and last year year_last. I know that within each time period there are no duplicates but that the same individual may be observed at different time periods with different identifiers. (See example code in this gist.)

My approach is to define a filter condition where I restrict comparisons to individuals observed over different time periods (as I know that there are no duplicates within a single time period), i.e, l.year_last < r.year_first.

Question

With link_type = dedupe_only Splink enforces a link_type_join_condition of the form l."unique_id" < r."unique_id". This means that blocking with the filter condition l.year_last < r.year_first is dependent on the order of unique_id and that not all pairs of individuals whose last observed year is smaller than the first observed year are compared.

I believe that a potential workaround is to include two separate blocking rules, one with a filter condition l.year_last < r.year_first and one with l.year_last > r.year_first. However, this feels more like a hack and I'm wondering if there is a better way to approach the problem?

A secondary comment is that it took me a while to understand why Splink did not perform some comparisons with my filter condition. Unless I am misunderstanding/abusing filter conditions, it would be nice to have a better documentation on the fact that dedupe_only enforces an ordering which might lead to unexpected behaviour in combination with filter conditions.

[leostimpfle]

I think the answer is simply to ensure that unique_id is sorted in ascending order according to [year_first, year_last] (in which case the link_type_join_condition enforced by Splink doesn't affect the filter condition l.year_last < r.year_first).

[RobinL]

Yes. Sorry - i missed this question, but that's right. You're guaranteed that the id on the left is smaller than the id on the right, and hence the easiest way to do this is to 'rig' the unique id values so the condition is met