Comparing columns from two CSV files

Question

This question has a second part here:

Comparing columns from two CSV files - follow-up

I'm currently trying to compare two CSV files column by column but only when their indexes match.

Here is what I've done:

import sys

def get_column(columns, name):
    count = 0
    columns = columns.split(';')
    for column in columns:
        array = list(column)
        if(array[0] == '"'):
            array[0] = ''
        if(array[len(column) - 1] == '"'):
            array[len(column) - 1] = ''
        column = ''.join(array)
        if column != name:
            count += 1
        else:
            return(count)

def set_up_file(file, variable):
    columns = next(file)
    siren_pos = get_column(columns, 'INDEX1')
    nic_pos = get_column(columns, 'INDEX2')
    variable_pos = get_column(columns, variable)
    return(siren_pos, nic_pos, variable_pos)

def test_variable(variable):
    with open('source.csv', 'r') as source:
        sir_s, nic_s, comp_s = set_up_file(source, variable)
        with open('tested.csv', 'r') as tested:
            sir_t, nic_t, comp_t = set_up_file(tested, variable)
            correct = 0
            memory = 0
            for row_s in source:
                row_s = row_s.split(';')
                tested.seek(0, 0)
                count = 0
                for row_t in tested:
                    count += 1
                    if(count >= memory):
                        row_t = row_t.split(';')
                        if(row_s[sir_s] == row_t[sir_t] and row_s[nic_s] == row_t[nic_t]):
                            if(row_s[comp_s] == row_t[comp_t]):
                                correct += 1
                                memory = count
                            break
            tested.seek(0, 0)
            print(correct / sum(1 for line in tested) * 100)

def main():
    test_variable('VARIABLE_TO_COMPARE')


if(__name__ == "__main__"):
    main()

This script is running into some performance issue and I would like to know how to make it run faster / make it more readable.

Answer 1

First I'd like to call out the good things that you've done:

• Writing functions, including a standard main().
• Having more or less sensible function and variable names.
• Proper use of with.

An improvement here is to stop parsing the file yourself, and to start parsing it with Python's native csv library. Even though your format is not technically CSV (it's separated by semicolon), you can still configure csv to use a different delimiter.

I recommend the use of the DictReader class. Given that you only pay attention to one variable, just use csv.reader. During initialization, get the index of the column you want, and then use that on every record that the reader gives back.

The critical performance issue here is that you have nested loops for row comparison. Given that you are comparing two series expected to be in the same order, but with edits (insertions and deletions), effectively you're doing a diff. Read this for a nice walkthrough.

http://www.xmailserver.org/diff2.pdf