Comparing columns from two CSV files
up vote
1
down vote
favorite
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.
python csv
add a comment |
up vote
1
down vote
favorite
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.
python csv
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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.
python csv
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.
python csv
python csv
edited yesterday
Jamal♦
30.2k11115226
30.2k11115226
asked 2 days ago
Comte_Zero
257
257
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
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 Given that you only pay attention to one variable, just use DictReader
class.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
Indeed, changing the separator allowed me to use thecsv
module. However I can't usezip
because the files are not containing exactly the same lines so it won't line up correctly
– Comte_Zero
2 days ago
@Comte_Zero Are both in the same order, with some random insertions and deletions? Or are the files the same size, but out of order with respect to each other?
– Reinderien
2 days ago
Same order, random insertions and deletions
– Comte_Zero
2 days ago
And are you checking all of the columns in both files, or only some of the columns?
– Reinderien
2 days ago
This may vary, that is why the main function contains only one, potentially copiable line
– Comte_Zero
2 days ago
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
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 Given that you only pay attention to one variable, just use DictReader
class.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
Indeed, changing the separator allowed me to use thecsv
module. However I can't usezip
because the files are not containing exactly the same lines so it won't line up correctly
– Comte_Zero
2 days ago
@Comte_Zero Are both in the same order, with some random insertions and deletions? Or are the files the same size, but out of order with respect to each other?
– Reinderien
2 days ago
Same order, random insertions and deletions
– Comte_Zero
2 days ago
And are you checking all of the columns in both files, or only some of the columns?
– Reinderien
2 days ago
This may vary, that is why the main function contains only one, potentially copiable line
– Comte_Zero
2 days ago
add a comment |
up vote
2
down vote
accepted
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 Given that you only pay attention to one variable, just use DictReader
class.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
Indeed, changing the separator allowed me to use thecsv
module. However I can't usezip
because the files are not containing exactly the same lines so it won't line up correctly
– Comte_Zero
2 days ago
@Comte_Zero Are both in the same order, with some random insertions and deletions? Or are the files the same size, but out of order with respect to each other?
– Reinderien
2 days ago
Same order, random insertions and deletions
– Comte_Zero
2 days ago
And are you checking all of the columns in both files, or only some of the columns?
– Reinderien
2 days ago
This may vary, that is why the main function contains only one, potentially copiable line
– Comte_Zero
2 days ago
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
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 Given that you only pay attention to one variable, just use DictReader
class.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
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 Given that you only pay attention to one variable, just use DictReader
class.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
edited 2 days ago
answered 2 days ago
Reinderien
1,362516
1,362516
Indeed, changing the separator allowed me to use thecsv
module. However I can't usezip
because the files are not containing exactly the same lines so it won't line up correctly
– Comte_Zero
2 days ago
@Comte_Zero Are both in the same order, with some random insertions and deletions? Or are the files the same size, but out of order with respect to each other?
– Reinderien
2 days ago
Same order, random insertions and deletions
– Comte_Zero
2 days ago
And are you checking all of the columns in both files, or only some of the columns?
– Reinderien
2 days ago
This may vary, that is why the main function contains only one, potentially copiable line
– Comte_Zero
2 days ago
add a comment |
Indeed, changing the separator allowed me to use thecsv
module. However I can't usezip
because the files are not containing exactly the same lines so it won't line up correctly
– Comte_Zero
2 days ago
@Comte_Zero Are both in the same order, with some random insertions and deletions? Or are the files the same size, but out of order with respect to each other?
– Reinderien
2 days ago
Same order, random insertions and deletions
– Comte_Zero
2 days ago
And are you checking all of the columns in both files, or only some of the columns?
– Reinderien
2 days ago
This may vary, that is why the main function contains only one, potentially copiable line
– Comte_Zero
2 days ago
Indeed, changing the separator allowed me to use the
csv
module. However I can't use zip
because the files are not containing exactly the same lines so it won't line up correctly– Comte_Zero
2 days ago
Indeed, changing the separator allowed me to use the
csv
module. However I can't use zip
because the files are not containing exactly the same lines so it won't line up correctly– Comte_Zero
2 days ago
@Comte_Zero Are both in the same order, with some random insertions and deletions? Or are the files the same size, but out of order with respect to each other?
– Reinderien
2 days ago
@Comte_Zero Are both in the same order, with some random insertions and deletions? Or are the files the same size, but out of order with respect to each other?
– Reinderien
2 days ago
Same order, random insertions and deletions
– Comte_Zero
2 days ago
Same order, random insertions and deletions
– Comte_Zero
2 days ago
And are you checking all of the columns in both files, or only some of the columns?
– Reinderien
2 days ago
And are you checking all of the columns in both files, or only some of the columns?
– Reinderien
2 days ago
This may vary, that is why the main function contains only one, potentially copiable line
– Comte_Zero
2 days ago
This may vary, that is why the main function contains only one, potentially copiable line
– Comte_Zero
2 days ago
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f208144%2fcomparing-columns-from-two-csv-files%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown