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))

I just added the script that I was using to replace. no error and table not updated. Was tried to updated first one column since i just started python and don't have any prior coding experience. — user157778
– user157778, Commented Jan 12, 2018 at 23:10

aghast · Accepted Answer · 2018-01-13 07:05:48Z

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.

Stack Exchange Network

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

1 Answer 1

You must log in to answer this question.

Hot Network Questions

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

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions