Convert object field to date and the also the display format of the date-CodePudding

I Have a Dataframe with 2 columns Object like that:

id                  object
date                object

 ------ -----------------   
|  ID  |      DATE       |
 ------ ----------------- 
|   1      01/10/2000    |
|   2      09/03/2005    |
|   3    1 January 2020  |
|   4     "21/08/1995"   |
|   5     27 April 2020  |
 ------------------------

I would like to convert the dates in the same format dd/mm/yyyy

I tried to use

df["DATE"] = pd.to_datetime(df["DATE"])

and this

df['DATE'].astype("datetime")

But i got this errpr : TypeError: data type 'datetime' not understood

Can you help me please ?

CodePudding user response：

The quotes are throwing off the datetime conversion for "21/08/1995" You need to get rid of those before converting.

import pandas as pd
df = pd.DataFrame({'ID':[1,2,3,4,5],
                  'DATE':['01/10/2000','09/03/2005','1 January 2020','"21/08/1995"', '27 April 2020']})


df['DATE'] = pd.to_datetime(df['DATE'].str.strip('"')).dt.strftime('%d/%m/%Y')

print(df)

Output

   ID        DATE
0   1  10/01/2000
1   2  03/09/2005
2   3  01/01/2020
3   4  21/08/1995
4   5  27/04/2020