How to fill column by finding best match between list and other column in pandas?

user_14256973 0
Viewed 86

So I'm trying to process a bank account spreadsheet with Pandas/Python that has posting dates, transaction description, and amount. I want to create a new column called 'VENDOR Name' that reads the transaction description and fills the new column with the best match for 'VENDOR NAME' from a list of vendors stored in vendors. I'll provide an example of what I tried (with a function I found on stack overflow). The description information has been changed to remove sensitive information, but the formatting is still the same. I have a spreadsheet of vendors called vendor_type.csv that contains a list of vendors that is much larger than what I show here. I'd still convert it into a list using vendors = vendors_df['vendor_name'].tolist() and it would be the same format as below.

import pandas as pd
import numpy as np
import re

In [1]: import pandas as pd
   ...: import numpy as np
   ...: import re

In [2]: df = pd.DataFrame({'Posting Date': ['2020-02-20', '2020-02-20', '2020-02-20', '2020-02-21', '2020-02-21'],
   ...:                   'Description': ['CHECK 12345', 'CHECK 1234', 'FPL DIRECT DEBIT ELEC PYMT', 'CHECK 9874', 'ADP PAYROLL FEES ADP - FEES'],
   ...:                   'Amount': [-500, -700, -400, -600, -90]})

In [3]: print(df)
  Posting Date                  Description  Amount
0   2020-02-20                  CHECK 12345    -500
1   2020-02-20                   CHECK 1234    -700
2   2020-02-20   FPL DIRECT DEBIT ELEC PYMT    -400
3   2020-02-21                   CHECK 9874    -600
4   2020-02-21  ADP PAYROLL FEES ADP - FEES     -90

In [4]: vendors = ['PAYROLL CHECK', 'FPL', 'ADP Payroll fees']
   ...: pattern = '|'.join(vendors)

In [5]: def pattern_searcher(search_str:str, search_list:str):
   ...:     search_obj = re.search(search_list, search_str)
   ...:     if search_obj:
   ...:         return_str = search_str[search_obj.start(): search_obj.end()]
   ...:     else:
   ...:         return_str = 'NA'
   ...:     return return_str
   ...:     

In [6]: df['VENDOR Name'] = df['Description'].apply(lambda x: pattern_searcher(search_str=x, search_list=pattern))

In [7]: print(df)
  Posting Date                  Description  Amount VENDOR Name
0   2020-02-20                  CHECK 12345    -500          NA
1   2020-02-20                   CHECK 1234    -700          NA
2   2020-02-20   FPL DIRECT DEBIT ELEC PYMT    -400         FPL
3   2020-02-21                   CHECK 9874    -600          NA
4   2020-02-21  ADP PAYROLL FEES ADP - FEES     -90          NA

This is what the end result should look like:

  Posting Date                  Description  Amount       VENDOR Name
0   2020-02-20      CHECK 12345 VENDOR_NAME    -500      CHECK-VENDOR
1   2020-02-20                   CHECK 1234    -700     PAYROLL CHECK
2   2020-02-20   FPL DIRECT DEBIT ELEC PYMT    -400               FPL
3   2020-02-21                   CHECK 9874    -600     PAYROLL CHECK
4   2020-02-21  ADP PAYROLL FEES ADP - FEES     -90  ADP Payroll fees

I'd still like to use the function above that I used to classify that one transaction (because it sort of worked), but it's not necessary. I'd also like to use maybe RegEx rules that can be expanded in case the list of vendors does expand. I am a bit stuck here, and would greatly appreciate any insight into how I can do this.

Thank you.

pandas dataframe python-3.x python regex
1 Answers

You're not looking to match a pattern (regex). You want to look for similarity between the vendor name and the description. This can be done in several ways, but I really like fuzzywuzzy:

import pandas

from typing import Optional
from fuzzywuzzy import fuzz, process


# Your input data
df = pandas.DataFrame(
    {
        "Posting Date": [
            "2020-02-20",
            "2020-02-20",
            "2020-02-20",
            "2020-02-21",
            "2020-02-21",
        ],
        "Description": [
            "CHECK 12345",
            "CHECK 1234",
            "FPL DIRECT DEBIT ELEC PYMT",
            "CHECK 9874",
            "ADP PAYROLL FEES ADP - FEES",
        ],
        "Amount": [-500, -700, -400, -600, -90],
    }
)

# List of vendors (can be loaded from file...)
vendors = ["PAYROLL CHECK", "FPL", "ADP Payroll fees"]


def matcher(description: str) -> Optional[str]:
    """Function that matches a description of a payment to a
    vendor in a list of vendors (fuzzy match).

    Args:
        description (str): The description to read

    Returns:
        str|None: The matching vendor (if we're certain enough about the match)
    """
    match, certainty = process.extractOne(
        description, vendors, scorer=fuzz.partial_ratio
    )
    if certainty >= 50:
        return match
    else:
        return None


df["VENDOR Name"] = df["Description"].apply(matcher)
df

Output:

  Posting Date                  Description  Amount       VENDOR Name
0   2020-02-20                  CHECK 12345    -500     PAYROLL CHECK
1   2020-02-20                   CHECK 1234    -700     PAYROLL CHECK
2   2020-02-20   FPL DIRECT DEBIT ELEC PYMT    -400               FPL
3   2020-02-21                   CHECK 9874    -600     PAYROLL CHECK
4   2020-02-21  ADP PAYROLL FEES ADP - FEES     -90  ADP Payroll fees

NOTE: The part with the certainty is how well of a match is found. This is optional, as you could just return the first/best match.

user_29521850
Related