Compare two CSV files in Python
I have two CSV files:
CSV1:
**ID Name Address Ph**
1 Mr.C dsf 142
2 Ms.N asd 251
4 Mr.V fgg 014
12 Ms.S trw 547
CSV2:
**ID Name Service Day**
1 Mr.C AAA Mon
2 Ms.N AAA Mon
2 Ms.N BBB Tue
2 Ms.N AAA Sat
As you can see very quickly CSV1 file is unique in that it only has one instance of each ID, while CSV2 is repeating.
I am trying to match two CSV files based on ID, and then wherever they match adding the CSV2 file, the Address and Ph fields from CSV1. It is then saved as a new output file, leaving the two original CSV files.
I wrote some code, but here's what happens:
- Either all records from CSV1 are appended to the last line of CSV2
- Or all records from CSV2 get the same address data that was added against them.
Here's what I've done so far.
import csv
csv1=open('C:\csv1file.csv')
csv2=open('C:\csv2file.csv')
csv1reader=csv.reader(csv1)
csv2reader=csv.reader(csv2)
outputfile=open('C:\mapped.csv', 'wb')
csvwriter=csv.writer(outputfile)
counter=0
header1=csv1reader.next()
header2=csv2reader.next()
csvwriter.writerow(header2+header1[2:4])
for row1 in csv1reader:
for row2 in csv2reader:
if row1[0]==row2[0]:
counter=counter+1
csvwriter.writerow(row2+row1[2:4])
I am running this code in Python 2.7. As you might have guessed, the two different results I get are based on the indentation of the csvwriter expression in the above code. I feel like I'm close enough to the answer and understand the logic, but somehow the loop doesn't work very well.
Can any of you help?
Thank.
source to share
The problem arises because the inner loop only runs once. the reason for this is that the csv2reader will be empty after running the loop once
the way to fix this would be to make a copy of the lines in the second file and use that copy in a loop
csvwriter.writerow(header2+header1[2:4])
csv2copy=[]
for row2 in csv2reader: csv2copy.append(row2)
for row1 in csv1reader:
for row2 in csv2copy:
print row1,row2,counter
if row1[0]==row2[0]:
counter=counter+1
csvwriter.writerow(row2+row1[2:4])
source to share