Change values of a column in CSV file using python

Question

I am new to python and just need a small help.

We have a Pipe delimited CSV file which looks like this

DATE|20160101
ID | Name | Address | City | State | Zip   | Phone | OPEID  | IPEDS |
10 | A... | 210 W.. | Mo.. | AL... | '31.. | 334.. | '01023 | 10063 |
20 | B... | 240 N.. | Ne.. | Ut... | '21.. | 335.. | '01024 | 10064 |

Every value of Zip and OPEID columns has apostrophes in the beginning

So we wish to create a new CSV file where apostrophes are removed from each value of these 2 columns.

The new file should then look like this:

DATE|20160101
ID | Name | Address | City | State | Zip  | Phone | OPEID | IPEDS |
10 | A... | 210 W.. | Mo.. | AL... | 31.. | 334.. | 01023 | 10063 |
20 | B... | 240 N.. | Ne.. | Ut... | 21.. | 335.. | 01024 | 10064 |

This code works for copying data without removing apostrophes

import os
import csv

file1 = "D:\CSV\File1.csv"
with open(file1, 'rb') as csvfile:

         reader = csv.reader(csvfile, delimiter = '|')

         path = "D:/CSV/New"
         if not os.path.exists(path):
             os.makedirs(path)

         writer = csv.writer(open(path+"File2"+".csv", 'wb'), delimiter = '|')

         for row in reader:
             writer.writerow(row)

csvfile.close()

have you produced any code that tries to do that? Are all instances of apostrophes unwanted or can there possibly be some that you'd rather keep? — Ma0
– Ma0, Commented Aug 26, 2016 at 11:46
I have tried to code, but got errors. And all apostrophes are unwanted — Aditya
– Aditya, Commented Aug 26, 2016 at 11:51
What is the question? You succeed to open the file? To write a file? To do anything? StackOverflow is a Q&A website, not a free writing code service. — Ohad Eytan
– Ohad Eytan, Commented Aug 26, 2016 at 11:59
check the code.. It simply writes all rows of File1 to FIle2. I don't have any idea how to remove apostrophes — Aditya
– Aditya, Commented Aug 26, 2016 at 12:07

John Zwinck · Accepted Answer · 2016-08-26 12:33:02Z

3

You can do it very efficiently with Pandas--this will be good if your file is very large:

import pandas as pd
import sys

with open('t.txt') as infile:
    title = next(infile)
    infile.seek(0)
    table = pd.read_csv(infile, '|', header=1, dtype=str)

table.rename(columns={'Unnamed: 9':''}, inplace=True)

table[' Zip   '] = table[' Zip   '].str.replace("'", "")
table[' OPEID  '] = table[' OPEID  '].str.replace("'", "")

sys.stdout.write(title)
table.to_csv(sys.stdout, '|', index=False)

answered Aug 26, 2016 at 12:33

John Zwinck

252k44 gold badges346 silver badges459 bronze badges

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

Comments

Jean-François Fabre · Accepted Answer · 2017-11-21 20:22:50Z

1

It worked for me... Try this.

res=[]
with open('hi.csv') as f:
    content=csv.reader(f,delimiter='|')
    for row in content:
        for str in range (len(row)):
            row[str]=row[str].replace('\'','')
        res.append(row)
    f.close()
with open('hi.csv','wb') as ff:  # python 3 => 'wb' => 'w',newline=''
    sw=csv.writer(ff,delimiter='|',quoting=csv.QUOTE_MINIMAL)
    for rows in res:
        sw.writerow(rows)
ff.close()

edited Nov 21, 2017 at 20:22

Jean-François Fabre♦

141k24 gold badges179 silver badges246 bronze badges

answered Aug 26, 2016 at 12:19

Lokesh Sanapalli

1,0324 gold badges19 silver badges39 bronze badges

Comments

Xavier C. · Accepted Answer · 2016-08-26 12:17:07Z

1

To remove apostrophes you can use the replace function, you just need to get the content of every cell one by one, and replace the apostrophes with:

new = old.replace("'", "")

More simply, open your csv file with any file editor and search and replace for "'".

answered Aug 26, 2016 at 12:17

Xavier C.

1,98117 silver badges23 bronze badges

Comments

Ma0 · Accepted Answer · 2016-08-26 12:15:18Z

The code below would be the same for all file formats. The fact that it is a *.csv doesn't change a thing. What it actually does, is that it goes in the file from which you want to remove the apostrophes, my_csv_in, and parses it line by line each time replacing them with nothing (a.k.a removing). The modified lines are written in a second file, my_csv_out.

my_csv_in = r'full_file_path_to_csv_in.csv'
my_csv_out = r'full_file_path_to_csv_out.csv'

with open(my_csv_in, 'r') as f_in:
    with open(my_csv_out, 'w') as f_out:
        for line in f_in:
            f_out.write(line.replace("'", ''))

There are probably better ways to do this that take advantage of the file being a *.csv and using the csv library. You can take a look at the quoting options in the documentation.

Collectives™ on Stack Overflow

Change values of a column in CSV file using python

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related