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()