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.)

Fetor

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? – Paragraph

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". – Abound

@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. – Civilization

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 ? – Pinup

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. – Praedial

Answer 1

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

Adin

'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? – Ruffner

Answer 2

Changing the format but not changing the type:

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

Curricle

just remember that df["date"] should be datetime64 before you do this – Spelter

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! – Neuburger

@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. – Paragraph

Answer 3

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()

Neuburger

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. – Paragraph

@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. – Neuburger

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). – Paragraph

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. – Paragraph

...or else I can write that as a rollup of pitfalls in the non-Jupyter approaches. – Paragraph

@smci, it would be nice from you to write that rollup of pitfalls. Thanks. – Neuburger

Ok some day. As long as you don't write "No!" underneath it too :) – Paragraph

Answer 4

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)

Minuteman

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! – Henghold

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. – Neuburger

Answer 5

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

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

Neuroticism

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". – Neuburger

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, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Sergent

Answer 6

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

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

Legged

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. – Neuburger

Answer 7

df['variable'] = pd.to_datetime(df['variable'], errors='coerce')

Enscroll

Answer 8

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'))

Commutual

change it to this: df['DOB']=pd.to_datetime(df['DOB']).dt.strftime('%m/%d/%Y') – Zoroaster

No! - Why to convert datetime to string and then back to datetime? See my comments to other answers. – Neuburger

Answer 9

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')

Churchless

Another confused person and misguided answer. Please read comments to other answers, they may help you understand the point. – Neuburger

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. – Churchless

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) – Churchless

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. – Neuburger