CODEWITHSUNDEEP
pythonpython-3.xcsvpandas
john doe
john doe

Reputation: 2253

How to set a custom separator in pandas to_csv()?

From the docs I know that in order to save as a .csv file one can simply do:

df.to_csv(sep = ';')

However, I would like to use my custom separator, for instance: :::. How can I set ::: as a separator?. I tried to: 

df.to_csv(sep = ':::')

And got: TypeError: "delimiter" must be a 1-character string

Also I tried to: df.to_csv('../data.csv', sep='\s*\:::', index=False), and got the same result. Thus, How can I set my own separator?.

UPDATE

Since I have in my dataframe |, I can not use such character as a separator. I tried to removed it with:

df.replace('\b|\b', '-', regex = True)

However, it did not worked. Any alternative on how to remove it?.

Upvotes: 36

Views: 121690

Answers (6)

Kumar Abhisek
Kumar Abhisek

Reputation: 31

Try this

import pandas as pd
import numpy as np

my_numpy = pandas_df.to_numpy()
np.savetxt('out.csv', my_numpy,fmt='%s', delimiter=':::')

Upvotes: 3

Itachi
Itachi

Reputation: 2995

Pandas version 0.24.2. Putting comment by Jonathan Dekhtiar as an answer. Saving the csv with special characters like α works like a charm

Upvotes: 0

Gustavo Lopes
Gustavo Lopes

Reputation: 4174

This is an old post, but I always seem to land here when googling how to export Dataframe to csv.

Although you can't do it directly with Pandas, you can do it with Numpy.

Since Pandas requires Numpy, you are not increasing your package size.

To do what you want, you can simply do:

import numpy as np
np.savetxt('out.csv', my_df, delimiter=':::')

Numpy offers a greater api to save csv files. You can even specify different separators using:

import numpy as np
np.savetxt('out.csv', my_df, fmt=['%.2f:::', '%f', '%s'])

You can find all the possible options in the docs.

Upvotes: 23

k0ngcrete
k0ngcrete

Reputation: 116

Zipa helped me with my problem of using consecutive spaces as seperator here:

This could be a workaround:

  myCsv = df.astype(str).apply(lambda x: '   '.join(x), axis=1)
  myCsv.rename('   '.join(df.columns)).to_csv(file, header=True, index=False)

Maybe based on his answer ,try :

myCsv = df.astype(str).apply(lambda x: ':::'.join(x), axis=1)
myCsv.rename(':::'.join(df.columns)).to_csv(file, header=True,index=False)

It did work for me, if te column names are strings

Upvotes: 1

john doe
john doe

Reputation: 2253

After all, I did:

df['Col'] = df['Col'].str.replace('|', ':')

In order to remove it from the column. Then I fixed a different character to separate my df.

Upvotes: 1

Jonathan DEKHTIAR
Jonathan DEKHTIAR

Reputation: 3536

Obviously Pandas seems not to allow this behavior.

However, if you absolutely want ":::". Why not exporting the dataframe with an uncommon character such as "|" and then open back the file and replace "|" by ":::".

That's the only solution I imagine to perform your desired result.

Upvotes: 6

Related Questions