Batch editing of csv files with Python

Question

I need to edit several csv files. Actually, most of the files are fine as they are, it's just the last (41st) column that needs to be changed. For every occurrence of a particular string in that column, I need it to be replaced by a different string; specifically, every occurrence of 'S-D' needs to be replaced by 'S'. I've tried to accomplish this using Python, but I think I need to write the csv files and I'm not quite sure how to do this:

import os 
import csv


path=os.getcwd()

filenames = os.listdir(path)

for filename in filenames:

    if filename.endswith('.csv'):
        r=csv.reader(open(filename))

        for row in r:
            if row[40] == "S-D":
                row[40] = "S"

Any help? Also, if anyone has a quick , elegant way of doing this with a shell script, that would probably be very helpful to me as well.

Answer 1

Try something along these lines. Now using the glob module as mentioned by @SaulloCastro and the csv module.

import glob
import csv

for item in glob.glob(".csv"):
    r = list(csv.reader(open(item, "r")))
    for row in r:
        row[-1] = row[-1].replace("S-D", "S")
    w = csv.writer(open(item, "w"))
    w.writerows(r)

Answer 2

Be sure to read up on the Python documentation for the CSV File Reading and Writing. Lots to learn there. Here is a basic example based on your question. Only modifying the data in the last column, writing out a modified file with "_edited" in the name.

import os 
import csv


path=os.getcwd()

filenames = os.listdir(path)

for filename in filenames:

    if filename.endswith('.csv'):
        r=csv.reader(open(filename))
        new_data = []
        for row in r:
            row[-1] = row[-1].replace("S-D", "S")
            new_data.append(row)

        newfilename = "".join(filename.split(".csv")) + "_edited.csv"
        with open(newfilename, "w") as f:
            writer = csv.writer(f)
            writer.writerows(new_data)