Edit csv column using python

Question

I would like to make column 2 in the csv file to be all lowercase and removing all the punctuation and save the file. How can i do that?

import re
import csv

with open('Cold.csv', 'rb') as f_input1:
    with open('outing.csv', 'wb') as f_output:

      reader = csv.reader(f_input1)
      writer = csv.writer(f_output)

for row in reader:
      row[1] = re.sub('[^a-z0-9]+', ' ', str(row[1].lower()))
      writer.writerow(row)
f_input1.close()

How do i add :

re.sub('[^A-Za-z0-9]+', ' ', str(row))
filewriter.writerow([new_row.lower()])

or .lower in this code?

Possible duplicate of Using the lowercase function with CSV rows — Laxmikant Ratnaparkhi
– Laxmikant Ratnaparkhi, Commented Nov 9, 2017 at 14:34
f_input1.close() is not required when using a with statement here. Also you would need to indent your for loop to the same level as your reader and writer lines. — Martin Evans
– Martin Evans, Commented Nov 9, 2017 at 14:58

Martin Evans · Accepted Answer · 2017-11-09 15:10:05Z

2

You could add your code to modify your cell as follows:

import re
import csv

with open('in.csv', 'rb') as f_input, open('out.csv', 'wb') as f_output:
    csv_output = csv.writer(f_output)

    for row in csv.reader(f_input):
        row[1] = re.sub('[^A-Za-z0-9]+', '', row[1].lower())
        csv_output.writerow(row)

.lower() is used to first convert the string to lowercase. Using with ensures that your files are both automatically closed at the end.

Note, your regular expression sub should replace any invalid characters with an empty string, e.g. '', you currently have it set to be a single space.

edited Nov 9, 2017 at 15:10

answered Nov 9, 2017 at 14:36

Martin Evans

46.9k17 gold badges88 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OneCricketeer · Accepted Answer · 2017-11-09 17:05:46Z

1

Just edit the row in place and write it back out

with open('Cold.csv', 'rb') as f_input1, open('outing.csv', 'wb') as f_output:

    reader = csv.reader(f_input1)
    writer = csv.writer(f_output)

    for row in reader:  
        row[1] = re.sub('[^a-z0-9]+', ' ', str(row[1].lower()))
        writer.writerow(row)

edited Nov 9, 2017 at 17:05

answered Nov 9, 2017 at 14:36

OneCricketeer

193k20 gold badges146 silver badges276 bronze badges

2 Comments

Janelle Koh Hui Juan Over a year ago

Do i have to close the file?

OneCricketeer Over a year ago

You don't need to close a file when using with open() syntax

Prakash Palnati · Accepted Answer · 2017-11-09 14:38:54Z

-1

Easiest solution would be combining these two approaches in you code.

import string
s.translate(None, string.punctuation) --> to remove punctuation

##if speed is not an issue
exclude = set(string.punctuation)
s = ''.join(ch for ch in s if ch not in exclude)


row.lower() --> to convert to lower case

answered Nov 9, 2017 at 14:38

Prakash Palnati

3,43927 silver badges36 bronze badges

Collectives™ on Stack Overflow

Edit csv column using python

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related