Advertisement
Advertisement


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': DOB 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: DOB datetime64[ns].

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

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

2020/04/20
1
111
4/20/2020 8:43:04 AM

Accepted Answer

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
2019/01/24
211
1/24/2019 10:21:55 AM

Changing the format but not changing the type:

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

The below code worked for me instead of the previous one - try it out !

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

Compared to the first answer, I will recommend to use dt.strftime() first, 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)
2018/12/05

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 datas 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 the question.

I will suppose that your column DOB already has the type datetime64 (you 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 to 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()
2020/08/16

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

df['DOB'] = pd.to_datetime(df['DOB'], dayfirst = True)
2019/08/04