CODEWITHSUNDEEP
pythoncsv
Vimalraj Selvam
Vimalraj Selvam

Reputation: 2245

Update CSV file by column name using Python

I have the csv file as follows:

product_name, product_id, category_id
book, , 3
shoe, 3, 1
lemon, 2, 4

I would like to update product_id of each row by providing the column name using python's csv library.

So for an example if I pass:

update_data = {"product_id": [1,2,3]}

then the csv file should be:

product_name, product_id, category_id
book, 1, 3
shoe, 2, 1
lemon, 3, 4

Upvotes: 3

Views: 3587

Answers (2)

Jon Clements
Jon Clements

Reputation: 142206

You can use your existing dict and iter to take items in order, eg:

import csv

update_data = {"product_id": [1,2,3]}
# Convert the values of your dict to be directly iterable so we can `next` them
to_update = {k: iter(v) for k, v in update_data.items()}

with open('input.csv', 'rb') as fin, open('output.csv', 'wb') as fout:
    # create in/out csv readers, skip intial space so it matches the update dict
    # and write the header out
    csvin = csv.DictReader(fin, skipinitialspace=True)
    csvout = csv.DictWriter(fout, csvin.fieldnames)
    csvout.writeheader()
    for row in csvin:
        # Update rows - if we have something left and it's in the update dictionary,
        # use that value, otherwise we use the value that's already in the column.
        row.update({k: next(to_update[k], row[k]) for k in row if k in to_update})
        csvout.writerow(row)

Now - this assumes that each new column value goes to the row number and that the existing values should be used after that. You could change that logic to only use new values when the existing value is blank for instance (or whatever other criteria you wish).

Upvotes: 1

NDevox
NDevox

Reputation: 4086

(assuming you're using 3.x)

Python has a CSV module in the standard library which helps read and amend CSV files.

Using that I'd find the index for the column you are after and store it in the dictionary you've made. Once that has been found it's simply a matter of popping the list item into each row.

import csv

update_data = {"product_id": [None, [1,2,3]]}
#I've nested the original list inside another so that we can hold the column index in the first position.

line_no = 0 
#simple counter for the first step.

new_csv = [] 
#Holds the new rows for when we rewrite the file.

with open('test.csv', 'r') as csvfile:
    filereader = csv.reader(csvfile)

    for line in filereader:
        if line_no == 0:

            for key in update_data:
                update_data[key][0] = line.index(key) 
                #This finds us the columns index and stores it for us.

        else:

            for key in update_data:
                line[update_data[key][0]] = update_data[key][1].pop(0) 
                #using the column index we enter the new data into the correct place whilst removing it from the input list.

        new_csv.append(line)

        line_no +=1

with open('test.csv', 'w') as csvfile:
    filewriter = csv.writer(csvfile)

    for line in new_csv:
        filewriter.writerow(line)

Upvotes: 0

Related Questions