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.

Aside: You might just want to iterate over `glob.glob('*.csv')`. — kojiro, Oct 02 '13 at 20:14

C0deH4cker · Answer 1 · 2013-10-02T20:39:36.457

4

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)

edited Oct 02 '13 at 20:39

answered Oct 02 '13 at 20:12

C0deH4cker

3,959
1
24
35

1

you can use `import glob` and then `for item in glob.glob('*.csv')` – Saullo G. P. Castro Oct 02 '13 at 20:15
1

@SaulloCastro I always forget about `glob`, but it's such an awesome module. Thanks for mentioning it – C0deH4cker Oct 02 '13 at 20:16
3

Downvoter here - there's a csv module, don't try to parse the csv file yourself. Your code breaks if any data in the file needs to be escaped, e.g. if there's a newlines or `,` in any of the columns. The csv readers and writers all take care of this, so use them. – l4mpi Oct 02 '13 at 20:20
1

Almost, but the csv reader is an iterator, not a list; you should use an accumulator or just wrap the reader in a `list`. Oh, and your second `open` call is missing a `"w"` :) – l4mpi Oct 02 '13 at 20:33
Should be all fixed now – C0deH4cker Oct 02 '13 at 20:34

score 3 · Accepted Answer · answered Oct 02 '13 at 20:25

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)

Batch editing of csv files with Python

2 Answers2

Linked