How to specify date format when using pandas.to_csv to YYYY-MM-DD?

Question

The OP of this post highlighted the lack of documentation for using the date_format argument in pandas. I would like to format the data into YYYY-MM-DD, not MM/DD/YYYY when using the to_csv command.

I've tried: frame.to_csv(output_dir, index=False, encoding='utf-8', date_format='%Y-%m-%d') and it is not working, where frame is a DataFrame

The file I'm trying to convert can be found here

score 3 · Accepted Answer · answered May 14 '15 at 19:45

3

This is already supported in to_csv as a param date_format:

t="""date,val
12/05/1984,sadas
1/11/1980,sadas
2/4/1945,sadas
22/10/1921,sadas"""
df = pd.read_csv(io.StringIO(t), parse_dates=[0])
df.to_csv(r'c:\data\date.csv', date_format='%Y-%m-%d')

Yields:

,date,val
0,1984-12-05,sadas
1,1980-01-11,sadas
2,1945-02-04,sadas
3,1921-10-22,sadas

The format strings are of the form taken from strftime

answered May 14 '15 at 19:45

EdChum

376,765
198
813
562

1

I had tried that and unfortunately it's not working (%Y-%m-%d) – NumenorForLife May 14 '15 at 19:47
What version pandas are you using? this works fine in `0.16.0` – EdChum May 14 '15 at 19:49
1

Are your dates already datetime dtype already or not? post the output from `df.info()` – EdChum May 14 '15 at 19:50
2

Well the only explanation is that they are strings and not datetime, try converting first: `df['date'] = pd.to_datetime(df['date'])` – EdChum May 14 '15 at 19:52
When I use df.info(), I see that even the columns that are seen as datetime64[ns] are NOT converted to the desired format. – NumenorForLife May 14 '15 at 19:53
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/77838/discussion-between-jsc123-and-edchum). – NumenorForLife May 14 '15 at 19:54
Well you'll need to post raw input data (or a link to your data) and complete code to reproduce this error – EdChum May 14 '15 at 19:55
You'll see it in the chat – NumenorForLife May 14 '15 at 20:00
very helpful, but how do you know this because it surely isn't mentioned in the documentation for `to_csv`? – crobar Aug 17 '23 at 09:47

score 1 · Answer 2 · answered May 14 '15 at 19:43

1

You can use the standard datetime string formatting behavior, here that would be: df.to_csv('df.csv, date_format="%Y-%m-%d") https://docs.python.org/2/library/datetime.html#strftime-strptime-behavior

EDIT: don't iterate through the dataframe with strftime, that's highly unoptimized and theres already a built-in function for performing the operation you want

answered May 14 '15 at 19:43

Nicholas Aldershof

265
2
8

I had tried that and unfortunately it's not working (%Y-%m-%d) – NumenorForLife May 14 '15 at 19:47
That's because it's ("%Y-%m-%d") not (%Y-%m-%d) – Nicholas Aldershof May 14 '15 at 19:54
frame.to_csv(output_dir, index=False, encoding='utf-8', date_format="%Y-%m-%d") doesn't work – NumenorForLife May 14 '15 at 19:59
1

I just confirmed that that works. How are you opening the csv afterwards? If you try looking at a csv in excel or similar the dates will be displayed differently, make sure you're verifying in a text editor. – Nicholas Aldershof May 14 '15 at 20:13
Thank you, I didn't realize that! Do you have any idea why the "Paid" column isn't datetime64? Should I manually change this? – NumenorForLife May 14 '15 at 20:16
Thanks a bunch. Looking at the file in excel, excel automatically converts dates to its format. Looking in notepad the format is what you specify. – Alex F Oct 22 '16 at 19:17

score 0 · Answer 3 · answered May 14 '15 at 19:43

0

You show iterate csv' rows and get every date and format it with strptime method from datetime.

answered May 14 '15 at 19:43

Gocht

9,924
3
42
81

How to specify date format when using pandas.to_csv to YYYY-MM-DD?

3 Answers3