0

I have two csv files - a master & an update file. I want take specific columns from the update file, & check the values against the master.

Both files will have the same columns & should look roughly like this:

Listed Company's English Name,Listed Company's Chinese Name,Stock Code,Listing Status,Director's English Name,Director's Chinese Name,Capacity,Position,Appointment Date (yyyy-mm-dd),Resignation Date (yyyy-mm-dd)  
C.P. Lotus Corporation,________,00122,Current,CHEARAVANONT Dhanin,___,Executive Director,,2009-12-31,
C.P. Lotus Corporation,________,00121,Current,CHEARAVANON Narong,___,Executive Director,,2001-02-01,  
C.P. Lotus Corporation,________,00121,Current,CHEARAVANONT Soopakij,___,Executive Director,CEO,2000-04-14,

Basically, I want to traverse the update file, taking each stock code value from the update file & checking to see if it exists in the master file.

Then, for each matching stock code, I need to check for differences in the Director name value, keeping track of those that don't match.

I've followed this example but it doesn't seem to do quite what I need (or i don't fully understand it...): Python: Comparing two CSV files and searching for similar items

f1 = file(csvHKX, 'rU')
f2 = file(csvWRHK, 'rU')
f3 = file('results.csv', 'w')

csv1 = csv.reader(f1)
csv2 = csv.reader(f2)
csv3 = csv.writer(f3)

scode = [row for row in csv2]

for hkx_row in csv1:
  for wrhk_row in scode:
    if hkx_row[2] != wrhk_row[2]:
      print 'HKX:', hkx_row
    continue

f1.close()
f2.close()
f3.close()

The update file contains the following stock codes: '00121' & '01003' (for testing).

It seems like the code is iterating through the lists comparing each line & printing out a line if the stock codes don't match line for line. So when the first column is reading '00121' it's printing out lines containing '01003' & vice versa.

But I am only interested in when it can't find hkx_row[2] ANYWHERE in wrhk_row[2]

Community
  • 1
  • 1
Nathan
  • 267
  • 1
  • 2
  • 10
  • What do you need to do if they're different? Update the master? – theheadofabroom Mar 18 '11 at 10:44
  • 1
    If you specify what exactly is missing/not to your liking in the linked example, it could be easier for us to answer. – atzz Mar 18 '11 at 11:07
  • 1
    "but it doesn't seem to do quite what I need"? Why not? What actual problem are you actually having with your actual code? Please post **your** code and **your** errors or problems. – S.Lott Mar 18 '11 at 11:12
  • Maybe if your code is sensitive you could at least knock together some pseudocode with changed variable names? – theheadofabroom Mar 18 '11 at 11:19
  • sorry, added some detail on what's not working & the code. for now, if the stock code doesn't exist in the master i'll just write the line to a new file. thanks! – Nathan Mar 19 '11 at 03:58

1 Answers1

0

Do this help you ? :

file master.csv

Listed Company's English Name,Listed Company's Chinese Name,Stock Code,Listing Status,Director's English Name,Director's Chinese Name,Capacity,Position,Appointment Date (yyyy-mm-dd),Resignation Date (yyyy-mm-dd)  
C.P. Lotus Corporation,________,00122,Current,CHEARAVANONT Dhanin,___,Executive Director,,2009-12-31,
C.P. Lotus Corporation,________,00121,Current,CHEARAVANON Narong,___,Executive Director,,2001-02-01,  
C.P. Lotus Corporation,________,00121,Current,CHEARAVANONT Soopakij,___,Executive Director,CEO,2000-04-14,  
C.P. Lotus Corporation,________,00123,Current,DEANINO James,___,Pilot,,2009-06-25,
C.P. Lotus Corporation,________,00129,Current,GINGE Ivy,___,Dental Technician,,2010-07-27,
C.P. Lotus Corporation,________,00127,Current,ERATOR Jane,___,Engineer,,2005-12-04,
C.P. Lotus Corporation,________,00119,Current,FIELD Mary,___,Pastrycook,,2009-06-25,

file update.csv

Listed Company's English Name,Listed Company's Chinese Name,Stock Code,Listing Status,Director's English Name,Director's Chinese Name,Capacity,Position,Appointment Date (yyyy-mm-dd),Resignation Date (yyyy-mm-dd)  
C.P. Lotus Corporation,________,00133,Current,THOMPSON Sarah,___,Cosmonaut,,2004-01-20,
C.P. Lotus Corporation,________,00122,Current,CHEARAVANONT Dhanin,___,Executive Director,,2009-12-31,
C.P. Lotus Corporation,________,00121,Current,CHEARAVANON Narong,___,Executive Director,,2001-02-01,  
C.P. Lotus Corporation,________,00121,Current,BEARD Sophia,___,Executive Director,CEO,2010-04-26,   
C.P. Lotus Corporation,________,00127,Current,ERATOR Jane,___,Engineer,,2005-12-04,
C.P. Lotus Corporation,________,00129,Current,MISTOUKI Hassan,___,Folk Singer,,2010-07-27,

code

import csv

mas = csv.reader(open('master.csv','rb'))
upd = csv.reader(open('update.csv','rb'))

set24 = set((row[2],row[4]) for row in mas)
print set24
print

updkept = [ row for row in upd if (row[2],row[4]) not in set24]
print '\n'.join(map(str,updkept))

result

set([('00127', 'ERATOR Jane'), ('00121', 'CHEARAVANONT Soopakij'), ('00121', 'CHEARAVANON Narong'), ('00119', 'FIELD Mary'), ('00122', 'CHEARAVANONT Dhanin'), ('Stock Code', "Director's English Name"), ('00129', 'GINGE Ivy'), ('00123', 'DEANINO James')])

['C.P. Lotus Corporation', '________', '00133', 'Current', 'THOMPSON Sarah', '___', 'Cosmonaut', '', '2004-01-20', '']
['C.P. Lotus Corporation', '________', '00121', 'Current', 'BEARD Sophia', '___', 'Executive Director', 'CEO', '2010-04-26', '   ']
['C.P. Lotus Corporation', '________', '00129', 'Current', 'MISTOUKI Hassan', '___', 'Folk Singer', '', '2010-07-27', '']
eyquem
  • 26,771
  • 7
  • 38
  • 46
  • yup. that seems to do the trick! wasn't familiar with set (total n00b here...). thanks! – Nathan Mar 21 '11 at 07:31
  • @Nathan ``li24 = [(row[2],row[4]) for row in mas]`` would be possible too, but a set is better because its element are kept as hash, that allows faster search, as far as I know (I think my wording is poor english, excuse and correct me, please) – eyquem Mar 21 '11 at 09:04