Phonetic transformations Recommended Usage

[vfrank66]

In the documentation on Phonetic transformations you show several ways to use .name_comparison(...phonetic_col_name=..), in addition within the splink code it performs an exact match on phonetic_col_name. But when attempting to use phonetics.dmetaphone() a tuple is produces the main and alternative metaphone. Currently dmetaphone produces an error when running splink because it is attempting to cast VARCHAR[] > VARCHAR I read on another discussion that matches on arrays is problematic.

Do you have a recommendation for the compability with phonetic_col_name and phonetics.dmetaphone()?

Below is how I am adding dmetaphone to the pd.DataFrame() I could cast to a string since it is an exact match anyway but was looking for a recommendation.

    def apply_phonetics_to_str_column(df, column_name):
        """Get the Double Metaphone for the string column. """
        # phonetics.dmetaphone(x) cannot get the string tuple which coverts to VARCHAR[] from dmetaphone to work with splink
        df[f"{column_name}_dm"] = df[column_name].apply(
            lambda x: phonetics.metaphone(x) if pd.isnull(x) == False else x
        )
        return df

[medwar99]

Also interested in if there's a recommended approach to this, as combining term-frequencies with a double-metaphone column creates significant slowdown (which is to be expected really). Dropping a VARCHAR[] column gives me 6x speedup when creating my __splink__df_concat and even better gains on creating __splink__df_concat_with_tf. It also seems to let me use 100% of the CPU.

I may try expanding the VARCHAR[] columns into two separate columns, have you tried this?

Edit: to clarify, dmetaphone works for me, it's just very slow due to handling arrays

[RobinL]

Hmm - yes, I don't have a great solution to this at the moment.

Out of interest are you using DuckDB or Spark? The current implementation of array intersect using DuckDB within Splink is very inefficient because it was created prior to DuckDB having 'proper' support for array intersections.
array_intersect

There's now an explicit list_intersect function that's probably a lot faster:
https://duckdb.org/docs/sql/functions/nested.html

Duckdb also just added support for faster fixed length arrays. not sure if relevant here (because presumably the dmetaphone arrays are sometimes length 1, sometimes length 2?)

[medwar99]

Using DuckDB, and that new support for arrays looks good. Your suggestion below of the custom comparison method makes sense, I will look into that, thank you.

Phonetic's dmetaphone returns a length-2 tuple, but the second element can sometimes be empty/NULL, so this is probably okay for the fixed-length aspect or could be handled appropriately in the custom comparison too.

[RobinL]

In reply to the main question, I think you probably want to avoid the .name_comparison high level comparison if you're using dmetaphone with arrays. This looks like a bit of an error on our part where we probably forgot that it returns an array.

If you want to use array/dmetaphones, I'd probaby do an array intersect level. if you're in duckdb, for speed, I'd probably write a comparison using custom sql with the new list_intersect function in duckdb

[vfrank66]

All the comments are helpful I was getting an error with duckdb and switched to phonetics.metaphone() with success. I have now switched to spark. I have a large dataset to compare so I will just use metaphone, purely based the comments about performance.