CODEWITHSUNDEEP
pythondataframecsvdouble-quotesquoting
Carlos de la Fuente
Carlos de la Fuente

Reputation: 13

How to write single (') and (") double quotes to file from a DataFrame?

I am trying to write a DataFrame to file with cells containing single and double quotes. I have used a combination of 'escapechar' and 'quotechar' to achieve it but I have not succeeded:

data_test = pd.DataFrame(np.array([["CD's", 'CD\'sss', 1], ['"', '"', 2], ['one', 'other', 3]]))

with open(outfile, 'w') as output_file:     
    writer = csv.writer(output_file,
        delimiter = '\t',
        lineterminator = '\r\n', 
        quotechar = '\'',
        doublequote=False,
        quoting=csv.QUOTE_NONE, 
        escapechar= '\''
        )
    for i in range(len(data_test)):
        line = data_test.iloc[i]
        writer.writerow(line)      
output_file.close()

The output I get contains single double quotes in cells containing single quotes (cell [1,1] and cell [1,2]), but it is not the correct solution. 

CD''s   CD''sss   1
"       "         2
one     other     3

I'm trying to get:

CD's    CD'sss    1
"       "         2
one     other     3

Any ideas on how to combine single and double quotes?

Upvotes: 1

Views: 1805

Answers (4)

Mark Tolonen
Mark Tolonen

Reputation: 177685

Why aren't you using pandas.to_csv?

import pandas as pd
import numpy as np
import csv

data_test = pd.DataFrame(np.array([["CD's", 'CD\'sss', 1], ['"', '"', 2], ['one', 'other', 3]]))
data_test.to_csv('out.csv',sep='\t',quoting=csv.QUOTE_NONE,index=False,header=False)

out.csv:

CD's    CD'sss  1
"       "       2
one     other   3

If line-by-line is needed:

import pandas as pd
import numpy as np
import csv

data_test = pd.DataFrame(np.array([["CD's", 'CD\'sss', 1], ['"', '"', 2], ['one', 'other', 3]]))

with open('out.csv','w',newline='') as output_file:     
    writer = csv.writer(output_file,delimiter='\t',quotechar='',quoting=csv.QUOTE_NONE)
    for line in data_test.as_matrix():
        writer.writerow(line)

Upvotes: 0

Prune
Prune

Reputation: 77847

You seem to be going to a lot of trouble to do something special with your embedded quotation marks. This does not match your use case. There is no special meaning to them: they are simply characters in the string, to be taken verbatim, just as all the others. They are data, not command-line strings that might have a special meaning.

Remove your escape handling -- that instructs your writer to add those special meanings, and results in the unwanted interpretations.

Upvotes: 0

Thomas Lazer
Thomas Lazer

Reputation: 178

Use this :

writer = csv.writer(output_file,
    delimiter = '\t',
    lineterminator = '\r\n',
    quotechar = "\\",
    doublequote=False,
    quoting=csv.QUOTE_NONE,
    escapechar="\\"
    )

OUTPUT

CD's    CD'sss  1

"   "   2

one other   3

Upvotes: 3

Stefan
Stefan

Reputation: 632

It’s a bit cryptically written, but I think this is the point:

Dialect.escapechar¶ A one-character string used by the writer to escape the delimiter if quoting is set to QUOTE_NONE and the quotechar if doublequote is False. On reading, the escapechar removes any special meaning from the following character. It defaults to None, which disables escaping.

You should set the escape char to None.

Upvotes: 1

Related Questions