Python script to update an existing column values in a csv from another two tables

Question

I need to update two columns: feedID and OperatID of table#1.csv with 'feed description', 'Operate description' from other CSV files. I don't have the pandas module available.

import csv table1 = r'N:\Extracts\table1.csv' table2 = r'N:\Extracts\table2.csv' with open(table1,'r+')as f1: f1reader = csv.reader(f1) f1reader.next() f1writer = csv.writer(f1) for row in f1reader: in_fdrid = row[2] def get_fdrname(in_fdrid): with open(table2,'rU')as f2: f2reader = csv.reader(f2) for f2row in f2reader: if in_fdrid == f2row[1]: fdrnm = f2row[2] return fdrnm f2.close() row[2].replace(in_fdrid,get_fdrname(in_fdrid)) 

Answer 1

Your code as it stands now will open and read file f2 every time you process a line from f1. This is way more work than you need to do.

I suggest you create a dictionary mapping ids to replacement values. You can then read all the lines from table2 one time, and populate the dictionary:

feed_desc = dict() with open(table2) as tbl2: t2csv = csv.reader(tbl2) next(t2csv) # skip headings for t2row in t2csv: feed_id = t2row[1] desc = t2row[2] feed_desc[feed_id] = desc 

Once that is done, you can simply check if an id is in feed_desc, and if so provide the replacement value:

with ... ... for t1row in t1csv: feed_id = t1row[2] if feed_id in feed_desc: t1row[2] = feed_desc # replace id with desc, you said. t1writer.writerow(t1row) 

You can do a similar thing for t3 into a different dictionary. Just make sure you read both t2 and t3 before you start processing t1.