searching a value from one csv file in another csv file - Python

One thing that you should avoid is reading the same file multiple times. There isn't any detail about how big are your files in the question, so I guess that they can fit in memory. In that case, I'd recommend to read the files once, work on the data in memory and write the result file.

Aside from that, as you read the data, there should be some way to improve the search time later. It looks like the columns in which you're interested are the ones related to the ProductCode. So maybe you could create a dictionary of lists that can be accessed using the ProductCode as key. As I said, this should speed up the search.

If there's some reason why using a dictionary isn't appropriate. You can try to use a database like sqlite3, which is part of the standard library, and store your data in memory in such a way that you can run SQL queries to get the data that you need in a faster way.

I hope this helps.

I think I have come up with something along the lines of what you want using dicts

import csv
in_file = open("in.csv", 'r')
reader = csv.DictReader(in_file, delimiter= ',')
out_file = open("out.txt", 'w')
out_file.write("StockNumber,SKU,ChannelProfileID\n")
check_file = open("check.csv", 'r')
check = csv.DictReader(check_file, delimiter=',')

prodid = set()
prod_sn = dict()
for row in reader:
    prodid.add(row["SKU"])
    prod_sn[row["SKU"]] = row["StockNumber"]
    print(row["SKU"])

stocknums = dict()
for row in check:
    stocknums[row["ProductCode"]] = row[" Stock"]
    print(row["ProductCode"])


for product in prodid:
    ref = 0
    if product in stocknums:
        if(stocknums[product] > 0):
            ref = 1


    out_file.write(str(prod_sn[product]) + ',' + str(product) + ','+ str(ref)+ "\n")

This I hope, meets all your needs. It allows you to hold on to your csv in a dict form, do lookups and modifications, and also write it in a perserved order. You can also change which column you want to be your lookup column (making sure that there is a unique id for every row of that column. In my example of usage, it assumes that both classes are contained withing the same file named 'CustomDictReader.py'. So in the end, what you can do with this is create two CSVRW objects, set your lookup column for each one and do your swapping/compare/lookup, then do the final write, when you are done going through what you need

-- File 'CustomDictReader.py' --

import csv, collections, copy

'''
# CSV TEST FILE 'test.csv'

TBLID,DATETIME,VAL
C1,01:01:2011:00:01:23,5
C2,01:01:2012:00:01:23,8
C3,01:01:2013:00:01:23,4
C4,01:01:2011:01:01:23,9
C5,01:01:2011:02:01:23,1
C6,01:01:2011:03:01:23,5
C7,01:01:2011:00:01:23,6
C8,01:01:2011:00:21:23,8
C9,01:01:2011:12:01:23,1


#usage

>>> import CustomDictReader
>>> import pprint
>>> test = CustomDictReader.CSVRW()
>>> success, thedict = test.createCsvDict('TBLID',',',None,'test.csv')
>>> pprint.pprint(dict(d))
{'C1': OrderedDict([('TBLID', 'C1'), ('DATETIME', '01:01:2011:00:01:23'), ('VAL', '5')]),
 'C2': OrderedDict([('TBLID', 'C2'), ('DATETIME', '01:01:2012:00:01:23'), ('VAL', '8')]),
 'C3': OrderedDict([('TBLID', 'C3'), ('DATETIME', '01:01:2013:00:01:23'), ('VAL', '4')]),
 'C4': OrderedDict([('TBLID', 'C4'), ('DATETIME', '01:01:2011:01:01:23'), ('VAL', '9')]),
 'C5': OrderedDict([('TBLID', 'C5'), ('DATETIME', '01:01:2011:02:01:23'), ('VAL', '1')]),
 'C6': OrderedDict([('TBLID', 'C6'), ('DATETIME', '01:01:2011:03:01:23'), ('VAL', '5')]),
 'C7': OrderedDict([('TBLID', 'C7'), ('DATETIME', '01:01:2011:00:01:23'), ('VAL', '6')]),
 'C8': OrderedDict([('TBLID', 'C8'), ('DATETIME', '01:01:2011:00:21:23'), ('VAL', '8')]),
 'C9': OrderedDict([('TBLID', 'C9'), ('DATETIME', '01:01:2011:12:01:23'), ('VAL', '1')])}

'''

class CustomDictReader(csv.DictReader):
    '''
        override the next() function and  use an
        ordered dict in order to preserve writing back
        into the file
    '''

    def __init__(self, f, fieldnames = None, restkey = None, restval = None, dialect ="excel", *args, **kwds):
        csv.DictReader.__init__(self, f, fieldnames = None, restkey = None, restval = None, dialect = "excel", *args, **kwds)

    def next(self):
        if self.line_num == 0:
            # Used only for its side effect.
            self.fieldnames
        row = self.reader.next()
        self.line_num = self.reader.line_num

        # unlike the basic reader, we prefer not to return blanks,
        # because we will typically wind up with a dict full of None
        # values
        while row == []:
            row = self.reader.next()
        d = collections.OrderedDict(zip(self.fieldnames, row))

        lf = len(self.fieldnames)
        lr = len(row)
        if lf < lr:
            d[self.restkey] = row[lf:]
        elif lf > lr:
            for key in self.fieldnames[lr:]:
                d[key] = self.restval
        return d

class CSVRW(object):

    def __init__(self):
        self.file_name = ""
        self.csv_delim = ""
        self.csv_dict  = collections.OrderedDict()

    def setCsvFileName(self, name):
        '''
            @brief stores csv file name
            @param name- the file name
        '''
        self.file_name = name

    def getCsvFileName():
        '''
            @brief getter
            @return returns the file name
        '''
        return self.file_name

    def getCsvDict(self):
        '''
            @brief getter
            @return returns a deep copy of the csv as a dictionary
        '''
        return copy.deepcopy(self.csv_dict)

    def clearCsvDict(self):
        '''
            @brief resets the dictionary
        '''
        self.csv_dict = collections.OrderedDict()

    def updateCsvDict(self, newCsvDict):
        '''
            creates a deep copy of the dict passed in and
            sets it to the member one
        '''
        self.csv_dict = copy.deepcopy(newCsvDict)

    def createCsvDict(self,dictKey, delim, handle = None, name = None, readMode = 'rb', **kwargs):
        '''
            @brief create a dict from a csv file where:
                the top level keys are the first line in the dict, overrideable w/ **kwargs
                each row is a dict
                each row can be accessed by the value stored in the column associated w/ dictKey

                that is to say, if you want to index into your csv file based on the contents of the
                third column, pass the name of that col in as 'dictKey'

            @param dictKey  - row key whose value will act as an index
            @param delim    - csv file deliminator
            @param handle   - file handle (leave as None if you wish to pass in a file name)
            @param name     - file name   (leave as None if you wish to pass in a file handle)
            @param readMode - 'r' || 'rb'
            @param **kwargs - additional args allowed by the csv module
            @return bool    - SUCCESS|FAIL
        '''
        retVal         = (False, None)
        self.csv_delim = delim

        try:
            reader = None
            if isinstance(handle, file):
                self.setCsvFileName(handle.name)
                reader = CustomDictReader(handle, delim, **kwargs)
            else:
                if None == name:
                    name = self.getCsvFileName()
                else:
                    self.setCsvFileName(name)
                reader = CustomDictReader(open(name, readMode), delim, **kwargs)

            for row in reader:
                self.csv_dict[row[dictKey]] = row

            retVal = (True, self.getCsvDict())

        except IOError:
            retVal = (False, 'Error opening file')

        return retVal

    def createCsv(writeMode, outFileName = None, delim = None):
        '''
            @brief create a csv from self.csv_dict
            @param writeMode   - 'w' || 'wb'
            @param outFileName - file name || file handle
            @param delim       - csv deliminator
            @return none
        '''
        if None == outFileName:
            outFileName = self.file_name
        if None == delim:
            delim = self.csv_delim

        with open(outFileName, writeMode) as fout:
            for key in self.csv_dict.values():
                fout.write(delim.join(key.keys()) + '\n')
                break

            for key in self.csv_dict.values():
                fout.write(delim.join(key.values()) + '\n')