CODEWITHSUNDEEP
python-3.xpandasdatetimeexport-to-csv
RustyShackleford
RustyShackleford

Reputation: 3667

How to stop to_csv from changing date format?

I am trying to write my dataframe as is to csv. Few of the columns of the dataframe are datetime.

I have used df.column = pd.to_date(df.column) to convert dates from dd/mm/yyyy to yyyy/mm/dd, which has worked successfully.

However when I write the dataframe to csv, the date columns revert back to the dd/mm/yyyy format.

Here is how I write to csv, df.to_csv('~/Desktop/df.csv',date_format='%Y-%m-%d')

How do I prevent the dates from reverting to the old format? 

pandas version 0.23
python 3.6 - anaconda spyder

edit:

This is how I am reading the csv:

df = pd.read_csv('~/Downloads/sample.csv.gz',parse_dates=True, delimiter='\t', compression = 'gzip')

Upvotes: 0

Views: 10543

Answers (2)

Nilesh Ingle
Nilesh Ingle

Reputation: 1883

Code below saves dataframe with date column as csv. (Jupyter Notebook 5.0.0, Python 3.6.6)

Import libraries

import pandas as pd
import numpy as np
import datetime as datetime

Create sample dataframe

x = ['01/12/2018','01/12/2018','01/12/2018','01/12/2018']
y = ['A','B','C','D']
df = pd.DataFrame({'date':x, 'var':y})
df['date'] = pd.to_datetime(df['date'])
df

Note: Here, the pd.to_datetime() format changes it to yyyy-mm-dd

enter image description here

Convert to required format

df['date'] = df['date'].dt.strftime('%Y/%m/%d')
df

enter image description here

Save dataframe as csv

df.to_csv('df.csv', sep=',',index=False)

Opening file in text editor

enter image description here

Opening file in MS Excel may need change of column format as below

enter image description here

Upvotes: 2

sophros
sophros

Reputation: 16620

Based on what you write it sounds pretty strange. Is it possible that you create a copy of a dataframe and do not apply the date formatting to its column?

Otherwise, to be more on the safe side I would try creating a string column just before writing the df with the formating of your liking.

Upvotes: 1

Related Questions