78

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
  • 32,567
  • 20
  • 113
  • 146

3 Answers3

171

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,768
  • 3
  • 30
  • 44
BubbleGuppies
  • 5,750
  • 7
  • 20
  • 15
  • 8
    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
  • 4
    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
  • @PierreD consider `styler` and `to_string` per [here](https://stackoverflow.com/a/76411568/21115) – davetapley Jun 06 '23 at 05:05
45

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
  • 359,921
  • 101
  • 625
  • 535
9

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
  • 40,265
  • 44
  • 171
  • 236
  • 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