57

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?

smci
  • 26,085
  • 16
  • 96
  • 138

3 Answers3

136

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')
Fabich
  • 2,080
  • 2
  • 25
  • 32
BubbleGuppies
  • 4,542
  • 7
  • 18
  • 15
  • 7
    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? – Pierre D Nov 11 '15 at 00:59
  • 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 – PirateApp Jun 28 '18 at 12:37
  • 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. – Nguai al Jul 14 '20 at 20:27
37

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')
Andy Hayden
  • 291,328
  • 80
  • 565
  • 500
6

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

Benjamin Crouzier
  • 34,915
  • 36
  • 154
  • 219
  • To also export milliseconds or more precisely microseconds use `%f` like this `df.to_csv(filename, date_format='%s.%f')` – maechler Aug 27 '20 at 15:25