Grouping sales transactions by person

Question

I have a csv file with sales transactions. Each transaction includes person identifiers (which are sometimes/often missing) and transaction data. Person identifiers are fname, lname, phone, email and social security number. I want to link each transaction to a unique person. As a business rule, I have set that two transactions belong to the same person if fname and lname are identical AND at least one of the 3 other person identifiers are identical. As an outcome, I need to have two dataframes (and ultimately two csv files): one with the unique persons and one copy of the initial data with an additional column for the person id.

I have written code that works very well for solving the problem for small files. Except that when the file gets really long (hundreds of thousands of lines), it gets stuck. I am almost sure that my code is not optimized and I think I can find a better way using agreggate functions like groupby() or unique(), which I think are much faster. But I can't figure out how.

import pandas as pd
workDir=r"D:\fichiers\perso\perso\python\unicity\\"


sourceFile='rawdata.csv'
inFrame=pd.read_csv(workDir+sourceFile, sep=";",encoding='ISO-8859-1')
personFrame=pd.DataFrame(columns=('id','fname','lname','email', 'phone','social security number'))
outFrame=pd.DataFrame(columns=inFrame.columns)
idPerson=0
#print(inFrame)


def samePerson(p1, p2):
  response=0
  if p1['fname']==p2['fname'] and p1['lname']==p2['lname']:
      if p1['email']==p2['email'] or p1['phone']==p2['phone'] or p1['social security number']==p2['social security number']:
        response=1
  return(response)

def completePerson(old, new):
    #complete with new line missing data in ols version of the person
    for theColumn in ('fname','lname','email', 'phone','social security number'):
        if pd.isnull(old[theColumn]) :
            old [theColumn]=new[theColumn]
    return(old)

def processLine(theLine):
  global personFrame
  global idPerson
  global outFrame
  theFlag=0
  for indexPerson, thePerson in personFrame.iterrows(): 
      if theFlag==0:
          if samePerson(theLine,thePerson):
              theLine['idPerson']=thePerson.idPerson
              personFrame.loc[indexPerson]=completePerson(thePerson, theLine)
              theFlag=1
  if theFlag==0:
      theLine['idPerson']=idPerson
      idPerson=idPerson+1
      personFrame=personFrame.append(theLine)
  outFrame=outFrame.append(theLine)


def processdf():
    inFrame.apply(processLine, axis=1)
    with open(workDir+'persons.csv','w', encoding='ISO-8859-1') as f:
        personFrame.to_csv(f, index='false')
    with open(workDir+'transactionss.csv','w', encoding='ISO-8859-1') as f:
        outFrame.to_csv(f, index='false')

processdf()

Answer 1

Here's a much faster algorithm:

Create a Person object containing fields like fname, lname, etc and a list of associated Transaction objects.

Then, create a dictionary with "fname,lname" as the key and a list of Person objects as the value. Iterate through your CSV line-by-line, and if that "fname,lname" key isn't in the dictionary, add it along with a Person object that defines the person's details.

However, if the key is in the dictionary then check the other details to make sure it's an actual match. If it is an actual match, add the transaction to that Person's Transaction array. If not, add a new person item to the end of the array.