pandas to_csv output quoting issue
Solution 1
You could pass quoting=csv.QUOTE_NONE
, for example:
>>> df.to_csv('foo.txt',index=False,header=False)
>>> !cat foo.txt
123,"this is ""out text"""
>>> import csv
>>> df.to_csv('foo.txt',index=False,header=False, quoting=csv.QUOTE_NONE)
>>> !cat foo.txt
123,this is "out text"
but in my experience it's better to quote more, rather than less.
Solution 2
Note: there is currently a small error in the Pandas to_string documentation. It says:
- quoting : int, Controls whether quotes should be recognized. Values are taken from csv.QUOTE_* values. Acceptable values are 0, 1, 2, and 3 for QUOTE_MINIMAL, QUOTE_ALL, QUOTE_NONE, and QUOTE_NONNUMERIC,
respectively.
But this reverses how csv defines the QUOTE_NONE and QUOTE_NONNUMERIC variables.
In [13]: import csv
In [14]: csv.QUOTE_NONE
Out[14]: 3
Solution 3
To use quoting=csv.QUOTE_NONE
, you need to set the escapechar
, e.g.
# Create a tab-separated file with quotes
$ echo abc$'\t'defg$'\t'$'"xyz"' > in.tsv
$ cat in.tsv
abc defg "xyz"
# Gotcha the quotes disappears in `"..."`
$ python3
>>> import pandas as pd
>>> import csv
>>> df = pd.read("in.tsv", sep="\t")
>>> df = pd.read_csv("in.tsv", sep="\t")
>>> df
Empty DataFrame
Columns: [abc, defg, xyz]
Index: []
# When reading in pandas, to read the `"..."` quotes,
# you have to explicitly say there's no `quotechar`
>>> df = pd.read_csv("in.tsv", sep="\t", quotechar='\0')
>>> df
Empty DataFrame
Columns: [abc, defg, "xyz"]
Index: []
# To print out without the quotes.
>> df.to_csv("out.tsv", , sep="\t", quoting=csv.QUOTE_NONE, quotechar="", escapechar="\\")
Solution 4
To use without escapechar:
Replace comma char ,
(Unicode:U+002C) in your df with an single low-9 quotation mark character ‚
(Unicode: U+201A)
After this, you can simply use:
import csv
df.to_csv('foo.txt', index=False, header=False, quoting=csv.QUOTE_NONE)
user3199761
Updated on July 05, 2022Comments
-
user3199761 almost 2 years
I'm having trouble getting the pandas
dataframe.to_csv(...)
output quoting strings right.import pandas as pd text = 'this is "out text"' df = pd.DataFrame(index=['1'],columns=['1','2']) df.loc['1','1']=123 df.loc['1','2']=text df.to_csv('foo.txt',index=False,header=False)
The output is:
123,"this is ""out text"""
But I would like:
123,this is "out text"
Does anyone know how to get this right?
-
user3199761 over 10 yearsI tried this but got "Error: need to escape, but no escapechar set". I also tried to add escapechar='\\' or escapechar=None, but does not seem to work...
-
DSM over 10 years@user3199761: that's because your real data -- unlike the example you pasted -- has something that you need to escape, such as a string with a comma. If you output
a,"b,c",d
you can parse the results, but what are the columns if you write outa,b,c,d
? You can't tell. -
user3199761 over 10 yearsI did try your code with the same example above, but got the error. I am using pandas 0.11.0 and python 2.7.6, not sure if this helps.
-
DSM over 10 years@user3199761: it might, though I'd be a little surprised. I'm using (pre-release) 0.13.0.
-
ericmjl over 10 years@DSM, I also tried your code, and it returned the exact same error.
-
user3199761 over 10 years@DSM: both pandas 0.11.0 and 0.12.0 does not work, but upgrading to pandas 0.13.0 solved the problem!
-
user5359531 almost 8 yearsso does this mean that in order to disable quoting, you need to load another library and call a function from it within the function for pandas?
-
DSM almost 8 years@user5359531: no, it means you import the built-in
csv
library just to get thecsv.QUOTE_NONE
constant (which happens to be the number 3, but you've got no guarantee that's always going to be true). -
Leukonoe almost 7 yearsGot error "Error: need to escape, but no escapechar set". I'm aware, that there might be issues with columns, but need the solution anyway.
-
DamDam almost 2 yearsThis solution works for me, even with commas into some strings. I just use a different separator :
df.to_csv(f"./export_{today}-{current_time}.tsv", sep="\t", index=False, quoting=csv.QUOTE_NONE)