3

I am working with DataFrame which contains multiple datetime formats in one column. For example:

2020-11-09 00:00:48
2020-11-09 00:00:48
2020-11-09 00:00:48
2020-11-09 00:00:48
2020-11-09 00:00:48
2020-08-25
2020-08-25
2017-08-25
2018-08-25
2020-08-25
25.08.2020
25.08.2020  
25.08.2017
25.08.2018  
25.08.2020

I want to convert it into "dd.mm.yyyy" format. pd.to_datetime(columnName, format = "dd.mm.yyyy") does not help.

Zhamshidbek Abdulkhamidov
  • 33
  • 2
  • 7

2 Answers2

3

You can use pd.to_datetime(data,'infer_datetime_format=True'):

Create the dataframe with your data:

data = {'dates': ['2020-11-09 00:00:48' ,'2020-11-09 00:00:48',
                  '2020-11-09 00:00:48' ,'2020-11-09 00:00:48',
                  '2020-11-09 00:00:48' ,'2020-08-25',
                  '2020-08-25' ,'2017-08-25',
                  '2018-08-25' ,'2020-08-25',
                  '25.08.2020' ,'25.08.2020',
                  '25.08.2017' ,'25.08.2018',
                  '25.08.2020']}
mini_df = pd.DataFrame(data)

Convert it to the same datetime format.

mini_df['dates'] = pd.to_datetime(mini_df['dates'], infer_datetime_format=True)

Result dataframe: result:

niv dudovitch
  • 176
  • 5
2
import pandas as pd 

date_list = ["2020-11-09 00:00:48",
"2020-11-09 00:00:48",
"2020-11-09 00:00:48",
"2020-11-09 00:00:48",
"2020-11-09 00:00:48",
"2020-08-25",
"2020-08-25",
"2017-08-25",
"2018-08-25",
"2020-08-25",
"25.08.2020",
"25.08.2020",  
"25.08.2017",
"25.08.2018",  
"25.08.2020"]

df = pd.DataFrame(date_list,columns=['date'])  
df['date'] = df['date'].apply(lambda x: pd.to_datetime(x).strftime('%d/%m/%Y'))

output will be

    date
0   09/11/2020
1   09/11/2020
2   09/11/2020
3   09/11/2020
4   09/11/2020
5   25/08/2020
6   25/08/2020
7   25/08/2017
8   25/08/2018
9   25/08/2020
10  25/08/2020
11  25/08/2020
12  25/08/2017
13  25/08/2018
14  25/08/2020
Udaya Unnikrishnan
  • 331
  • 1
  • 4
  • Thanks for the answer. However, I got an error with NaT values. NaTType does not support strftime. I forgot to consider that my dataset contains NaN values. Can you help with this? – Zhamshidbek Abdulkhamidov Aug 18 '21 at 12:19
  • 1
    You can replace NaN value in the column by df['date'] = df['date'].fillna(0) then df['date'] = df['date'].apply(lambda x: pd.to_datetime(x).strftime('%d/%m/%Y') if x != 0 else x) And you need to do handle the zero entry in the date column according to your requirement – Udaya Unnikrishnan Aug 18 '21 at 12:39