2
\$\begingroup\$

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()
\$\endgroup\$
  • \$\begingroup\$ Is the program crashing? Do you have some code to indicate progress to show the code is still running? \$\endgroup\$ – pacmaninbw May 14 at 23:03
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.