Compare values from two csv files, append value to text file

Question

I have three files. I want to compare columns with fruit in them and those that match, I want to append the matching fruit to the Append.txt file and then sort ascending.

test1.csv

CustID,Name,Count,Item,Date  
23,Smith,8,apples,08/12/2010  
1,Jones,8,banana,03/26/2009  
15,Miller,2,cookie dough,03/27/2009  
6,Fisher,8,oranges,06/09/2011

test2.csv

FRUIT,Amount,Aisle  
oranges,1,1  
apples,1,1  
pears,1,1

Append.txt

Fruit,Total,Aisle
cherries,1,1  
dates,2,1  
grapes,5,1  
kiwis,2,2  
peaches,2,2  
plums,1,1  
watermelon1,2

Code:

import csv

# Iterate through both  reader1 and reader2, compare common row, and append matching column data to test.txt in its matching column
with open("C:\\Test\\Append.txt", 'a') as f:
    reader1 = csv.reader(open("C:\\Test\\test1.csv", 'rb'), delimiter=',')
    row1 = reader1.next()
    reader2 = csv.reader(open("C:\\Test\\test2.csv", 'rb'), delimiter=',')
    row2 = reader2.next()
    if (row1[3] == row2[0]):
        print "code to append data from row1[0] to test.txt row[0] goes here"

f.close()
exit

print "code to sort test.txt ascending on column[0] goes here"

My initial script will not work. After examining I can see that the code only compares row 1 with row 1, row 2 with 2, etc. and I really want it to compare all rows (row1 with row 1, row1 with row 2, row 2 with row 1, row 2 with row 2, etc>). After running main script, test files can be populated with no records or up to 5 records. Append file can be either empty or have hundreds of records. Using python 2.7.

I am also unsure as to how to sort the file in ascending order when done.

Martijn Pieters · Accepted Answer · 2013-03-28 23:36:05Z

1

Use sets. Read the two CSV files first and collect just the fruits from the rows.

Then use set intersections to find all fruit that the two files have in common, add these to the fruit from the Append.txt file, sort, and write all fruit back to the file.

import csv

# collect the fruits of both CSV files
with open('c:/Test/test1.csv', 'rb') as test1:
    reader = csv.reader(test1)
    next(reader, None)  # ignore header
    test1_fruit = set(row[3] for row in reader)
with open('c:/Test/test2.csv', 'rb') as test2:
    reader = csv.reader(test2)
    next(reader, None)  # ignore header
    test2_fruit = set(row[0] for row in reader)

# Read all the fruit from Append
with open("C:/Test/Append.txt", 'r') as append:
    fruit = set(line.strip() for line in append if line.strip())

# add all fruit that are in both test1 and test2
fruit |= test1_fruit & test2_fruit

# write out a sorted list
with open("C:/Test/Append.txt", 'w') as append:
    append.write('\n'.join(sorted(fruit)))

edited Mar 28, 2013 at 23:36

answered Mar 28, 2013 at 20:42

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

user12059 Over a year ago

Overall it worked,. I deleted the | on line 12 and changed the 'w' to 'a' on line 15 otherwise it overwrote the existing data inside the Append.txt file. Anyway to put a newline at the end of the Append file? To those newbies (like me) don't forget to import csv. Yahoo. Thanks.

Martijn Pieters Over a year ago

@user12059: Right, I interpreted your question slightly differently. If you remove the | then you did not care about the fruit already listed in Append.txt, so I removed reading that file altogether. Added the extra newline for you.

user12059 Over a year ago

Perfect. It now makes sense to me that it would be nested just inside the last bracket. Your my new BFF of the day. :-)

user12059 Over a year ago

Martijn Pieters I just noticed a snag. It only sorts the appended data, not the entire file. The appended data needs to be integrated into the entire list alphabetically.

Martijn Pieters Over a year ago

@user12059: That was what my original version did. I'll roll that part back. That is why you needed the |= inplace or as well, to merge the fruit from the CSV files into the fruit list from the text file.

|

Collectives™ on Stack Overflow

Compare values from two csv files, append value to text file

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related