How do I write my own complex fuzzy match with pandas?

Question

I have two dataframes: one with an account number, a purchase ID, a total cost, and a date and another with account number, money paid, and date:

To make it clear there are two accounts, 11111 and 33333, but there are some typos in the dataframes.

AccountNumber  Purchase ID       Total Cost       Date
   11111              100           10          1/1/2020
   333333             100           10          1/1/2020
   33333              200           20          2/2/2020
   11111              300           30          4/2/2020

AccountNumber    Date            Money Paid:
   11111          1/1/2020          5 
   111111         1/2/2020          2
   33333          1/2/2020          1
   33333          2/3/2020          15
   1111           4/2/2020          30

Each Purchase ID is an identifier for a single purchase, however multiple accounts may be involved within the purchase, such as account 11111 and 33333. Moreover, an account may be used for two different purchases such as account 11111 with Purchase ID 100 and 300. In the second dataframe, payments can be made in increments, so I need to use the date to make sure that the payment is associated with the correct Purchase ID. Moreover, there may be some slight errors in the account numbers so I need to use a fuzzy match. In the end, I want to get a dataframe that is grouped by Purchase ID and compares how much the accounts paid vs. the cost of the item:

Purchase ID     Date         Total Cost     Amount Paid       $Owed
   100          1/1/2020       10               8               2
   200          2/2/2020       20               15              5
   300          4/2/2020       30               30              0

As you can see, this is a fairly complicated question. I first tried just joining the two dataframes based on AccountNumber but I ran into issues due to the slight differences as well as the problem of matching the Accountnumber transaction to the correct Purchase ID with the date, because one error with merging is that you might accidentally sum up money paid for the wrong Purchase since accounts can be involved with different purchases. I'm thinking about iterating through the rows and using if statements/regex but I feel like that would take too long.

What's the simplest and efficient solution to this problem? I'm a beginner at pandas and python.

user_13461602 · Answer

The library pandas-dedupe can help you to link the two dataframe by using a combination of active learning and clustering. have a look at the repo.

Here is the sample code (and step by step explanation):

import pandas as pd
import pandas_dedupe

#load dataframes
dfa = pd.read_csv('file_a.csv')
dfb = pd.read_csv('file_b.csv')

#initiate matching
df_final = pandas_dedupe.link_dataframes(dfa, dfb, ['field_1', 'field_2', 'field_3', 'field_4'])

# At this point pandas_dedupe will ask you to label a sample of records according   
# to whether they are distinct or the same observation.   
# After that, pandas-dedupe uses its knowledge to cluster together similar records.

#send output to csv
df_final.to_csv('linkage_output.csv')

How do I write my own complex fuzzy match with pandas?

1 Answers