Editing bad lines in CSV file

user_14660910 0
Viewed 106

I have a CSV file as following:

fruit, country,  city
banana, japan, tokyo
apple, brazil, rio
apple, korea, south, seoul
banana, denmark, copenhagen

This is an example, in reality, I have this issue in multiple large files. The issue being I can not read the 4th row with a comma delimiter as it will have one column too much.

My idea is to

  1. open the file with csv.reader
  2. find rows with term1 in column 2 and term2 in column 3 (in my example, term1=korea and term2=south)
  3. Replace term1 in column 2 with term1 and term2 combined (korea south) and deleted the 3rd column in the row
  4. save the edited file

In the code below I have completed the first two steps, but I am stuck on step 3 and 4.

import csv

path = r'C:\Users\jlobo\Desktop\example.txt'

with open(path, newline='') as csvfile:
    csv_read = csv.reader(csvfile, delimiter=',', quotechar='"') #
    
    for row in csv_read:
        search_terms1 = ["korea"]
        search_terms2 = ["south"]

        if any([term in row[1] for term in search_terms1]) and any([term in row[2] for term in search_terms2]):
            print(row)

How can I do steps 3 and 4? Or if relevant is there an alternative solution to the problem?

csv python
1 Answers

First define a list ARRAY = [], and then while you process the rows append them to that array

search_terms1 = ["korea"]
search_terms2 = ["south"]

if any([term in row[1] for term in search_terms1]) and any([term in row[2] for term in search_terms2]):
    row[1] =  row[2] + row[1] # combie south and korea
    row.pop(2) # remove the 3rd element

ARRAY.append(row)

And then you can write the file again

with open("new_file.csv", "w") as f:
    for row in ARRAY: # Write each row
        f.write(", ".join(row))
        f.write("\n") # add a new line

I do not really know how csv.reader works, and there may be some functions that do the job better than this, But the snippet above should work just fine.

user_136161630
Related