csv files comparing, parsing, and writing (Linearly?)

Question

The below code looks at csv files, file1 and file2. According to a unique key from each file, Compares them with a csv cross-reference file and if there is a match it compares the unique identifiers from file1 and file2 together, if there is a match it will pick the values from file2 and do a write, otherwise it will pick the values from file1 and do a write.

import csv, datetime, calendar
global_dic = {}
with open('D:\\hello.csv', 'r') as file0:

    reader1 = csv.reader(file0, delimiter='\t')
    header = next(reader1)

    for row in reader1:
        key = (row[0] + '|' + row[1] + '|' + row[2] + '|' + row[3])
        global_dic[key] = {header[0]: row[0], header[1]: row[1], header[2]: row[2], header[3]: row[3], header[4]: row[4], header[5]: row[5], header[6]: row[6], header[7]: row[7], header[8]: row[8], header[9]: row[9]}


with open('D:\\file1.csv', 'r') as master:
    master_indices = dict(((r[0] + r[1] + r[2] + r[3] + r[4]), i) for i, r in enumerate(csv.reader(master)) if '/' in r[0])

with open('D:\\file2.csv', 'r') as hosts:
    with open('results1.csv', 'w') as results:
        reader = csv.reader(hosts)
        writer = csv.writer(results)

        writer.writerow(next(reader, []) + ['RESULTS'])

        for row in reader:

            if '/' in row[0]:
                key = row[1] + row[2] + row[3] + row[4]
                value = row[6]
                if key in global_dic:

                    meow = datetime.datetime.strptime(row[0], '%m/%d/%Y %H:%M:%S')  # change str time to date/time obj && add .%f for ms consolidation
                    unix_timestamp = calendar.timegm(meow.timetuple())  # do the conversion to unix stamp
                    time_ms = unix_timestamp * 1000

                    index = master_indices.get(row[0] + row[1] + row[2] + row[3] + row[4])
                    if index is not None:
                        a = ''
                    else:
                        writer.writerow('value' + ',' + str(global_dic[key]['cpKey']) + ',' + str(global_dic[key]['SCADA Key']) + ',' + str(value) + ',' + str(time_ms) + ',' + str(time_ms) + ',' + '0' + ',' + '0' + ',' + '0' + '\n')

problem needed help with: when there is no match i can write the file, however when there is a match i dont know how to bring r[6] from master_indecies. If i include r[6] in the dictionary than it will create issues during the matching process

                    if index is not None:
                        # HELPPPPPPPPPP
                    else:
                        writer.writerow('value' + ',' + str(global_dic[key]['cpKey']) + ',' + str(global_dic[key]['SCADA Key']) + ',' + str(value) + ',' + str(time_ms) + ',' + str(time_ms) + ',' + '0' + ',' + '0' + ',' + '0' + '\n')

score 0 · Accepted Answer · edited May 23 '17 at 12:32

1st the provided code is much too complicated, try to make it easier. Use better names (which file contains these cross reference(s)?)

I think smaller steps are the right way to solve this issue, somethink like:

dict1 = get_dict_from_csv('file1.csv')
dict2 = get_dict_from_csv('file2.csv')
cross_refs = get_cross_refs_from_csv('cross_refs.csv')
with open('result.csv', mode='w') as result:
    for row in dict1:
        #...
        if index is not None:
            writer.writerow(dict2[index])

Will make your live much easier, so my 2nd advice is, refactor your code 1st. Anyway good luck!

[edit]Take a lookt at: Read two textfile line by line simultaneously -python to get some examples of how to read several files at the same time in python.

Cheers Lars

csv files comparing, parsing, and writing (Linearly?)

1 Answers1