Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?.

like image 912
john doe Avatar asked Apr 10 '17 13:04

john doe


People also ask

How do I add a separator in pandas?

We use the python string format syntax '{:,. 0f}'. format to add the thousand comma separators to the numbers. Then we use python's map() function to iterate and apply the formatting to all the rows in the 'Median Sales Price' column.

What does to_csv do in pandas?

Pandas DataFrame to_csv() function converts DataFrame into CSV data. We can pass a file object to write the CSV data into a file. Otherwise, the CSV data is returned in the string format.

Does pandas to_csv overwrite?

When you write pandas DataFrame to an existing CSV file, it overwrites the file with the new contents. To append a DataFrame to an existing CSV file, you need to specify the append write mode using mode='a' .


Video Answer


4 Answers

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.

like image 136
Gustavo Lopes Avatar answered Oct 11 '22 20:10

Gustavo Lopes


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.

like image 21
Jonathan DEKHTIAR Avatar answered Oct 11 '22 18:10

Jonathan DEKHTIAR


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.

like image 2
john doe Avatar answered Oct 11 '22 20:10

john doe


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=':::')
like image 2
Kumar Abhisek Avatar answered Oct 11 '22 19:10

Kumar Abhisek