Match two Panda Series (Dataframe columns) using regex

makrbec 0
Viewed 54

I have a Panda Series and a Dataframe column, containing special characters, spaces and have extra characters at the end (one column has singular form of fruits, the other plural). You can see that in the first element, ä is transcribed as a in apple, but in Banänas it is transcribed as bananaes.

  a = pd.Series(['Äpple', 'Orange & Pear', 'Banänas', 'Octopi', 'Wine'])
  df = {
          "fruits": ['apple','orange-something-pear', 'bananaes', 'octopus', 'kiwi'],
          "amounts": [100, 200, 300, 400, 500]
       }

I need a result, which looks the following:

  df = {
          "fruits": ['apple','orange-something-pear', 'octopus', 'bananaes', 'kiwi'],
          "amounts": [100, 200, 300, 400, 500],
          "title": ['Äpples', 'Orange & Pear', 'Banänas', 'Octopi', NaN]
       }

So far, I've thought of the following:

  1. make the Series a lowercase and replace special characters with regex using a dictionary
  2. match the regex column with df['fruits']

Code that I have:

   import pandas as pd 
   
    # data
    s = pd.Series(['Äpple', 'Orange & Pear', 'Banänas', 'Octopi', 'Wine'])
    df = {
              "fruits": ['apple','orange-something-pear', 'bananaes', 'octopus', 'kiwi'],
              "amounts": [100, 200, 300, 400, 500]
           }

    #lowercase
    s = s.str.lower()

    # dictionary for replacement
    rep_dict = {"ö":"(.{1,2})", "ä":'(.{1,2})', "ü":'(.{1,2})', " & ":'-(.?)-'}

    # replace
    s2 = s.replace(rep_dict)
    
    # make into a dictionary
    s_dict = dict(zip(s, s2))

    # map/match the dict to df
    (pd.concat((df['fruits'].str.match(k) for k in s_dict), axis=1, ignore_index = True)
    .dot(pd.Series(d for k, d in s_dict.items()))
    )

The match does not work, though (followed this question: How to use map with a dictionary having regular expression keys?)

Also I'm not sure, how to take into account only the beginning of the string, due to the plural suffixes at the end.

I'm super grateful for any help, I hope the question is fine how it is, otherwise I'm happy to split it into subquestions.

pandas python-3.x regex python
1 Answers

You might consider using Python's difflib module:

import difflib
import string
import pandas as pd

# Dictionary for replacement
rep_dict = {"ö": "(.{1,2})", "ä": "(.{1,2})", "ü": "(.{1,2})", " & ": "-(.?)-"}
values = (
    pd.Series(["Äpple", "Orange & Pear", "Banänas", "Octopi", "Wine"])
    .astype(str)
    .str.lower()
    .replace(rep_dict)
)

# Implementation
df["title"] = df["fruits"].apply(
    lambda row: difflib.get_close_matches(
        row, [row, *values], n=2, cutoff=1 / (1 + np.e ** -len(row)) / 1.8
    )[-1]
)

# Notes: `difflib.get_close_matches` function uses n=2 to guarantee that it returns a
#        non-empty list. I've set the cutoff parameter using a sigmoid function to reduce
#        the cutoff threshold, for smaller words (like kiwi).
#        Bear in mind that the cutoff parameter have a direct impact to the end result.

# When title equals the fruit name, meaning no other value from the `values` list matches the fruit name
# replace it with pd.NA
df["title"] = df.apply(
    lambda row: pd.NA
    if row["title"] == row["fruits"]
    else string.capwords(row["title"]),
    axis=1,
)
df
# Prints:
#                   fruits  amounts          title
# 0                  apple      100          Äpple
# 1  orange-something-pear      200  Orange & Pear
# 2               bananaes      300        Banänas
# 3                octopus      400         Octopi
# 4                   kiwi      500           <NA>

Caution

The solution above is not 100% failproof. Changes to the cutoff parameter directly impact the end result. For example:

df["title"] = (
    df["fruits"]
    .apply(
        lambda row: difflib.get_close_matches(
            row, [row, *values], n=2, cutoff=0.4
        )[-1]
    )
)

# Notes: `difflib.get_close_matches` function uses n=2 to guarantee that it returns a
#        non-empty list. I've set the cutoff parameter using a sigmoid function to reduce
#        the cutoff threshhold, for smaller words (like kiwi).
#        Bear in mind that the cutoff parameter have a direct impact to the end result.

# When title equals the fruit name, meaning no other value from the `values` list matches the fruit name
# replace it with pd.NA
df["title"] = df.apply(lambda row: pd.NA if row['title'] == row['fruits'] else string.capwords(row['title']), axis=1)
df
# Prints:
#                   fruits  amounts          title
# 0                  apple      100          Äpple
# 1  orange-something-pear      200  Orange & Pear
# 2               bananaes      300        Banänas
# 3                octopus      400         Octopi
# 4                   kiwi      500           Wine  <-- Incorrect match

You might also consider using fuzzy match libraries, like fuzzymatcher.

Ingwersen_erik0
Related