How to change the datetime format in Pandas

Question

My dataframe has a DOB column (example format 1/1/2016) which by default gets converted to Pandas dtype 'object'.

Converting this to date format with df['DOB'] = pd.to_datetime(df['DOB']), the date gets converted to: 2016-01-26 and its dtype is: datetime64[ns].

Now I want to convert this date format to 01/26/2016 or any other general date format. How do I do it?

(Whatever the method I try, it always shows the date in 2016-01-26 format.)

Are you looking for a solution that only works under Jupyter notebook? (in which case use a per-column 'styler') or works in plain Python console and iPython? — smci, Apr 20 '20 at 08:44
Note: `datetime` as a data structure to hold information on date and time ***has no format*** - it's just a data structure. Its content could be *displayed* in a certain way / "format". Or if you have strings that represent date/time, it can be expressed therein in a certain way / "format". — FObersteiner, Oct 21 '21 at 10:00
@MrFuppes That's true, but it does have a default format with the `__str__()` method. I'm just mentioning in case any newbies are confused. — wjandrea, Nov 07 '21 at 21:24
Is there any way to achieve a format change (i.e. style) and a data type change? For example, if the original column contained data formatted as a string in format **yyyy-mm-dd**. Is there any way to convert the dates to datetime data type with a format of **dd/mm/yyyy** ? — Josh_BI_UK, Nov 08 '22 at 17:38
`01/26/2016` is NOT a general date format. It's US specific, and ambiguous if the day is <=12 (as it could also be UK dd/mm/yyyy). Stick with YYYY-MM-DD anywhere you can, anything else and you're heading for a world of pain. Only place that should not is the UI itself, when it needs to be displayed in a culture that is appropriate for the user. — dsz, Jul 31 '23 at 00:26

jezrael · Accepted Answer · 2019-01-24T10:21:55.573

404

You can use dt.strftime if you need to convert datetime to other formats (but note that then dtype of column will be object (string)):

import pandas as pd

df = pd.DataFrame({'DOB': {0: '26/1/2016', 1: '26/1/2016'}})
print (df)
         DOB
0  26/1/2016 
1  26/1/2016

df['DOB'] = pd.to_datetime(df.DOB)
print (df)
         DOB
0 2016-01-26
1 2016-01-26

df['DOB1'] = df['DOB'].dt.strftime('%m/%d/%Y')
print (df)
         DOB        DOB1
0 2016-01-26  01/26/2016
1 2016-01-26  01/26/2016

edited Jan 24 '19 at 10:21

answered Jun 28 '16 at 05:34

jezrael

822,522
95
1,334
1,252

53

'strftime' converts the datetime column to unicode for applying the operation on DOB1 we again have to convert it to datetime. Isn't there any other way of formating without losing the data_type? – M.Zaman Nov 24 '17 at 12:02
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/188826/discussion-between-jezrael-and-poete-maudit). – jezrael Feb 21 '19 at 14:45

score 46 · Answer 2 · edited Mar 29 '19 at 10:49

46

Changing the format but not changing the type:

df['date'] = pd.to_datetime(df["date"].dt.strftime('%Y-%m'))

edited Mar 29 '19 at 10:49

Basj

41,386
99
383
673

answered Mar 14 '19 at 18:14

Yanni Cao

620
5
6

4

just remember that df["date"] should be datetime64 before you do this – adhg Jul 18 '19 at 14:59
28

**No!** Suppose the original value of some item in the `date` column is “November **26**, 2019”. `strftime()` means *"string from time"*, so `df["date"].dt.strftime('%Y-%m')` will be a **string** `"2019-11"` for that item. Then, `pd.to_datetime()` will convert this string **back** to the `datetime64` format, but now as “November **1**, 2019”! So the result will be: **No format change, but the change of the date value itself!** – MarianD Feb 17 '20 at 23:38
11

@MarianD: all your comments on individual answers are useful, but can you please summarize them in one rollup of "Pitfalls/Don't do these" at the bottom of your answer? Also you need to state clearly what the problem with each of these is: if any of the input dates isn't in the expected format, these will either risk throwing exceptions, or mangle the date. Simply writing "No!" everywhere doesn't convey that. – smci Apr 20 '20 at 08:29

MarianD · Answer 3 · 2023-05-17T02:44:05.820

There is a difference between

the content of a dataframe cell (a binary value) and
its presentation (displaying it) for us, humans.

So the question is: How to reach the appropriate presentation of my data without changing the data / data types themselves?

Here is the answer:

If you use the Jupyter notebook for displaying your dataframe, or
if you want to reach a presentation in the form of an HTML file (even with many prepared superfluous id and class attributes for further CSS styling — you may or you may not use them),

use styling. Styling don't change data / data types of columns of your dataframe.

Now I show you how to reach it in the Jupyter notebook — for a presentation in the form of HTML file see the note near the end of this answer.

I will suppose that your column DOB already has the datetime64 type (you have shown that you know how to reach it). I prepared a simple dataframe (with only one column) to show you some basic styling:

Not styled:
```
df
```

          DOB
0  2019-07-03
1  2019-08-03
2  2019-09-03
3  2019-10-03

Styling it as mm/dd/yyyy:

df.style.format({"DOB": lambda t: t.strftime("%m/%d/%Y")})

          DOB
0  07/03/2019
1  08/03/2019
2  09/03/2019
3  10/03/2019

Styling it as dd-mm-yyyy:

df.style.format({"DOB": lambda t: t.strftime("%d-%m-%Y")})

          DOB
0  03-07-2019
1  03-08-2019
2  03-09-2019
3  03-10-2019

Be careful!
The returning object is NOT a dataframe — it is an object of the class Styler, so don't assign it back to df:

Don't do this:

df = df.style.format({"DOB": lambda t: t.strftime("%m/%d/%Y")})    # Don't do this!

(Every dataframe has its Styler object accessible by its .style property, and we changed this df.style object, not the dataframe itself.)

Questions and Answers:

Q: Why your Styler object (or an expression returning it) used as the last command in a Jupyter notebook cell displays your (styled) table, and not the Styler object itself?
A: Because every Styler object has a callback method ._repr_html_() which returns an HTML code for rendering your dataframe (as a nice HTML table).

Jupyter Notebook IDE calls this method automatically to render objects which have it.

Note:

You don't need the Jupyter notebook for styling (i.e., for nice outputting a dataframe without changing its data / data types).

A Styler object has a method render(), too, if you want to obtain a string with the HTML code (e.g., for publishing your formatted dataframe on the Web, or simply present your table in the HTML format):

df_styler = df.style.format({"DOB": lambda t: t.strftime("%m/%d/%Y")})
HTML_string = df_styler.render()

It's worth pointing out that **styler code like this is intended to be run under, and only takes effect under Jupyter notebook, and has absolutely zero effect when run in console or iPython**. The OP didn't specify "under Jupyter", so this may or may not be a viable solution depending on their setup. Lots of data science code gets copy-and-pasted, and the Jupyter-specific assumptions don't get explicitly specified, then people wonder why the styler code "doesn't work" when run in their (console) environment. — smci, Apr 20 '20 at 08:45
@smci, isn't is explicitly mentioned in the second paragraph of my answer? In the form of conditional `if`, statement so known for every programmer? — In spite of it thanks for your comment, it may be helpful for some people. — MarianD, Apr 20 '20 at 08:48
no that's very unclear, also buried. The original question supposed nothing about Jupyter, and the OP and some users may not even have Jupyter available to them. Your answer would need to say in boldface its first line **"The following approach (styling) only works under Jupyter notebook, and will have no effect whatsoever when run outside Jupyter notebook"**. (In data science blogs and sites I see on a daily basis people posting Jupyter code into non-Jupyter environments, and wondering why it doesn't work). — smci, Apr 20 '20 at 08:52
Cool. I also suggest you add all the (many) pitfalls you identified on the other "convert-to-string-with-strftime-then-back-again-with-pd.to_datetime" approaches. At least, need to mention raising and catching exceptions. Also, `pd.to_datetime()` has the arguments `errors='raise'/'coerce'/'ignore', dayfirst, yearfirst, utc, exact` to control how precise and exception-happy it is, and whether invalid outputs get coerced to `NaT` or what. What makes it more complicated in "real-world" datasets is mixed/missing/incomplete formats, times, timezones, etc; exceptions are not necessarily bad things. — smci, Apr 20 '20 at 09:14
...or else I can write that as a rollup of pitfalls in the non-Jupyter approaches. — smci, Apr 20 '20 at 09:16
@smci, it would be nice from you to write that rollup of pitfalls. Thanks. — MarianD, Apr 20 '20 at 09:29
Ok some day. As long as you don't write "No!" underneath it too :) — smci, Apr 20 '20 at 09:30

score 21 · Answer 4 · edited Jul 24 '21 at 13:21

21

Compared to the first answer, I will recommend to use dt.strftime() first, and then pd.to_datetime(). In this way, it will still result in the datetime data type.

For example,

import pandas as pd

df = pd.DataFrame({'DOB': {0: '26/1/2016 ', 1: '26/1/2016 '})
print(df.dtypes)

df['DOB1'] = df['DOB'].dt.strftime('%m/%d/%Y')
print(df.dtypes)

df['DOB1'] = pd.to_datetime(df['DOB1'])
print(df.dtypes)

edited Jul 24 '21 at 13:21

Peter Mortensen

30,738
21
105
131

answered Dec 05 '18 at 22:36

user3512680

407
4
5

6

This does not work at least in my case. Specifically, the column is converted to datetime data type but also the values are converted to the original format! – Outcast Feb 21 '19 at 13:58
1

**No!** Syntax error (missing brace), in my version of Pandas (0.25.1) another syntax error (dt.strftime() — can only use .dt accessor with datetimelike values) - you rely on inherent data type, but in different versions of Pandas the inherent data types may be different), and a strange logic — **why to convert datetime to string and then back to datetime**? See my comment to rishi jain's answer. – MarianD Feb 18 '20 at 00:27

score 8 · Answer 5 · edited Jun 17 '21 at 08:23

8

The below code worked for me instead of the previous one:

df['DOB']=pd.to_datetime(df['DOB'].astype(str), format='%m/%d/%Y')

edited Jun 17 '21 at 08:23

desertnaut

57,590
26
140
166

answered Jun 14 '19 at 06:33

rishi jain

1,524
1
19
26

10

**No!** Your `format='%m/%d/%Y'` parameter is for **parsing** a string, i.e. you are supposed **to provide the string in such a format** (e. g. `"5/13/2019"`). **Nothing more, no format change.** It will be still displayed as `2019-05-13` — or it will raise an exception, if `df['DOB'].astype(str)` contains item(s) not in such a format, e. g. in a format `"2019-05-13"`. – MarianD Feb 18 '20 at 00:04
1

What is *"the previous one"*? What post does it refer to? Or do you mean *"the previous ones"* (all of them)? Please respond by [editing (changing) your answer](https://stackoverflow.com/posts/56592671/edit), not here in comments (***without*** "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Peter Mortensen Jul 24 '21 at 13:13

score 0 · Answer 6 · edited Jul 24 '21 at 13:16

0

You can try this. It'll convert the date format to DD-MM-YYYY:

df['DOB'] = pd.to_datetime(df['DOB'], dayfirst = True)

edited Jul 24 '21 at 13:16

Peter Mortensen

30,738
21
105
131

answered Aug 04 '19 at 08:24

Ashu007

745
1
9
13

3

**No!** `dayfirst=True` is only the specification of a date parse order, e.g. that ambivalent date string as a "2-1-2019" will be parsed as January 2, 2019, and not as February 1, 2019. **Nothing more, no change for output formatting**. – MarianD Feb 18 '20 at 00:46

score -2 · Answer 7 · edited Jul 24 '21 at 13:15

-2

The below code changes to the 'datetime' type and also formats in the given format string.

df['DOB'] = pd.to_datetime(df['DOB'].dt.strftime('%m/%d/%Y'))

edited Jul 24 '21 at 13:15

Peter Mortensen

30,738
21
105
131

answered Jun 29 '19 at 23:13

San

13
2

2

change it to this: `df['DOB']=pd.to_datetime(df['DOB']).dt.strftime('%m/%d/%Y')` – John Doe Aug 20 '19 at 08:59
1

**No!** - **Why to convert datetime to string and then back to datetime**? See my comments to other answers. – MarianD Feb 18 '20 at 00:32

score -2 · Answer 8 · edited Jul 24 '21 at 13:20

-2

Below is the code that worked for me. And we need to be very careful for format. The below link will be definitely useful for knowing your exiting format and changing into the desired format (follow the strftime() and strptime() format codes in strftime() and strptime() Behavior):

data['date_new_format'] = pd.to_datetime(data['date_to_be_changed'] , format='%b-%y')

edited Jul 24 '21 at 13:20

Peter Mortensen

30,738
21
105
131

answered Nov 01 '20 at 10:18

Anil Kumar

385
2
17

3

Another confused person and misguided answer. Please read comments to other answers, they may help you understand the point. – MarianD Nov 07 '20 at 04:06
The provided link will help in understanding various formats of dates and its useage in python. In no answer I find this. So I posted it for the benifit of others. I don't think there is any confusion here. Kindly be specific about your comments. So that I can plan to change my answer. – Anil Kumar Nov 08 '20 at 17:41
I have read all the answer and comments. They are definitely useful. But adding to all this the link provided gives better understanding of different kind of date formats and conversation (*Where ever possible) – Anil Kumar Nov 08 '20 at 17:54
1

Your answer is useful, too. But usefulness is not the same as a correct answer. For example *“Use deque for FIFO”* is useful, too, but has nothing with the OP question. – MarianD Nov 08 '20 at 18:38

How to change the datetime format in Pandas

8 Answers8

Linked

Related