Pandas exporting to_csv() with quotation marks around column names

Question

For some reason I need to output to a csv in this format with quotations around each columns names, my desired output looks like:

"date" "ret"
2018-09-24 0.00013123989025119056

I am trying with

import csv
import pandas as pd

Y_pred.index.name = "\"date\""
Y_pred.name = "\'ret\'"
Y_pred = Y_pred.to_frame()
path = "prediction/Q1/"
try:
    os.makedirs(path)
except:
    pass

Y_pred.to_csv(path+instrument_tmp+"_ret.txt",sep=' ')   

and got outputs like:

"""date""" 'ret'
2018-09-24 0.00013123989025119056

I can't seem to find a way to use quotation to wrap at the columns. Does anyone know how to? Thanks.

My solution: using quoting=csv.QUOTE_NONE together with Y_pred.index.name = "\"date\"", Y_pred.name = "\"ret\""

Y_pred.index.name = "\"date\""
Y_pred.name = "\"ret\""
Y_pred = Y_pred.to_frame()
path = "prediction/Q1/"
try:
    os.makedirs(path)
except:
    pass

Y_pred.to_csv(path+instrument_tmp+"_ret.txt",sep=' ',quoting=csv.QUOTE_NONE)   

and then I get

"date" "ret"
2018-09-24 0.00013123989025119056

Mccaffrey

This is called quoted output. In general there is no need to manually hack quotes into the column names. Use one of the quoting=csv.QUOTE_... options – Matilda

Possible duplicate of Quote only the required columns using pandas to_csv – Matilda

Answer 1

IIUC, you can use the quoting argument with csv.QUOTE_NONE

import csv
df.to_csv('test.csv',sep=' ',quoting=csv.QUOTE_NONE)

And your resulting csv will look like:

 "date" "ret"
0 2018-09-24 0.00013123989025119056

Side Note: To facilitate the adding of quotations to your columns, you can use add_prefix and add_suffix. If your starting dataframe looks like:

>>> df
         date       ret
0  2018-09-24  0.000131

Then do:

df = df.add_suffix('"').add_prefix('"')
df.to_csv('test.csv',sep=' ',quoting=csv.QUOTE_NONE)

Capillarity

is this version dependent? I didn't get the desired output when quoting=csv.QUOTE_NONE – Mccaffrey

I wouldn't think so, but just for reference I'm using '0.21.1' – Capillarity

Answer 2

This is called quoted output. Instead of manually hacking in quotes into your column names (which will mess with other dataframe functionality), use the quoting option:

df = pd.DataFrame({"date": ["2018-09-24"], "ret": [0.00013123989025119056]})

df.to_csv("out_q_esc.txt", sep=' ', escapechar='\\', quoting=csv.QUOTE_ALL, index=None)
"date" "ret"
"2018-09-24" "0.00013123989025119056"

The 'correct' way is to use quoting=csv.QUOTE_ALL (and optionally escapechar='\\'), but note however that QUOTE_ALL will force all columns to be quoted, even obviously numeric ones like the index; if we hadn't specified index=None, we would get:

"" "date" "ret"
"0" "2018-09-24" "0.00013123989025119056"

• csv.QUOTE_MINIMAL refuses to quote these fields because they don't strictly need quotes (they're neither multiline nor do they contain internal quote or separator chars)

Matilda