CODEWITHSUNDEEP
pythoncsv
Gabriel Trezza
Gabriel Trezza

Reputation: 121

Copy and modify a row in csv file

I need some help with a csv file. I'm working with the CIS Automotive API and am extracting data to put in a database. I turned the data into a csv file, however there are some rows with multiple ID's. Is there anyway I can copy the row but with one ID each?

The csv file is like this at the moment:

Dealership     | Address       |Zipcode| DealerID
Charleston Kia | 123 Bowman rd | 29412 | 12345,21456,32145

and I wanted to make it to look like this:

Dealership     | Address       |Zipcode| DealerID
Charleston Kia | 123 Bowman rd | 29412 | 12345
Charleston Kia | 123 Bowman rd | 29412 | 21456
Charleston Kia | 123 Bowman rd | 29412 | 32145

Upvotes: 2

Views: 604

Answers (1)

Tomerikoo
Tomerikoo

Reputation: 19414

You can use a Dictreader to read each row with the headers. Then, split() the DealerID column on commas, and replicate the row with the matching ID.

So for an input file file.csv like:

Dealership|Address|Zipcode|DealerID
Charleston Kia|123 Bowman rd|29412|12345,21456,32145

The following code:

import csv

with open("file.csv") as file, open("new.csv", 'w', newline='') as out_file:
    reader = csv.DictReader(file, delimiter='|')
    writer = csv.DictWriter(out_file, fieldnames=reader.fieldnames)
    writer.writeheader()
    for row in reader:
        for _id in row["DealerID"].split(','):
            writer.writerow({**row, "DealerID": _id})

Will create a new file, new.csv with contents:

Dealership,Address,Zipcode,DealerID
Charleston Kia,123 Bowman rd,29412,12345
Charleston Kia,123 Bowman rd,29412,21456
Charleston Kia,123 Bowman rd,29412,32145

Upvotes: 2

Related Questions