I am comparing two columns of data (~20k rows) to find the items that do not match. Actually one of my files is an export from an Oracle database, but I have not gotten to the point of working with databases in python yet.
The code works as intended, but as I am new to this I am wondering if there is a more efficient way to do all of this.
First I read the columns into a list:
import openpyxl wb = openpyxl.load_workbook('file.XLSX') ws = wb['dirtyfile'] dirtysource =  cleansource =  for col in ws['A']: dirtysource.append(col.value) ws2 = wb['Database Export'] for col in ws2['A']: cleansource.append(col.value)
The data I have extracted from the database is already cleaned. The other data set is in an inconsistent format:
dirtysource = [u'Tom Jones (01134)', u'Bob Smith (On Leave) (598711)', u'Jane Doe (00004)', u'100627 - Juan Carlos' ]
Next I clean the data up. I am slicing out the name, and just keeping the ID. The ID can be of a variable length. When it appears at the end of the string it is enclosed in parentheses. When it is at the beginning it is delimited by a '-'.
j = 0 for i in dirtysource: if i.endswith(')'): dirtysource[j] = str(i[i.rfind('(')+1:len(i)-1]) else: dirtysource[j] = str(i[0:i.find('-')-1]) j = j +1
The only thing I am doing to the clean list is to convert it from unicode:
j = 0 for i in cleansource: cleansource[j] = str(i) j = j + 1
Finally I compare the two lists to find the non-matching values:
delta =  for i in dirtysource: if i in cleansource: continue else: delta.append(i) print delta delta1 =  for i in cleansource: if i in dirtysource: continue else: delta1.append(i) print delta1
Eventually I will be passing the two lists into some queries, but I have more to learn before I do that.