How to specify date format when using pandas.to_csv?

Question

The default output format of to_csv() is:

12/14/2012  12:00:00 AM

I cannot figure out how to output only the date part with specific format:

20121214

or date and time in two separate columns in the csv file:

20121214,  084530

The documentation is too brief to give me any clue as to how to do these. Can anyone help?

Hilliard

Answer 1

You could use strftime to save these as separate columns:

df['date'] = df['datetime'].apply(lambda x: x.strftime('%d%m%Y'))
df['time'] = df['datetime'].apply(lambda x: x.strftime('%H%M%S'))

and then be specific about which columns to export to csv:

df[['date', 'time', ... ]].to_csv('df.csv')

Stettin

Answer 2

Since version v0.13.0 (January 3, 2014) of Pandas you can use the date_format parameter of the to_csv method:

df.to_csv(filename, date_format='%Y%m%d')

Raasch

yes but is there a way to store two different Timestamp columns using different formats? I could convert them to string first, then save, but is there something more elegant? – Mccool

upvoted! if I use %s for date_format i have no idea why the timestamp goes 5 hours 30 mins behind the datetime values, my datetime values in the dataframe are already at UTC, how do I convert 2018-06-28 12:15:00+00:00 to unix epoch while writing to csv – Cleek

didn't work for me. i am trying to save this '%Y-%m-%d'. Wwhen I go open up a file, this is saved as %Y/%m%d. – Italianate

@PierreD consider styler and to_string per here – Rochellrochella

I got stuck on this, but it turns out that this will not work on dates, but on datetimes!. So, just convert them to datetime and then the date_format will work. The documentation clearly states that date_format is used to "Format string for datetime objects." – Disparate

Answer 3

To export as a timestamp, do this:

df.to_csv(filename, date_format='%s')

The %s format is not documented in python/pandas but works in this case.

I found the %s from the dates formats of ruby. Strftime doc for C here

Note that the timestamp miliseconds format %Q does not work with pandas (you'll have a litteral %Q in the field instead of the date). I caried my sets with python 3.6 and pandas 0.24.1

Tiffanietiffanle

To also export milliseconds or more precisely microseconds use %f like this df.to_csv(filename, date_format='%s.%f') – Rudy