Converting different date formats to a single format in a dataframe column-CodePudding

I want to make different date formats the same in my dataframe column.

input:

0      16.09.2014
1      2014-09-16
2      02.12.2014
3      2014-12-02
4      09.01.2018

183    2015-03-30
184    12.04.2017
185    2017-04-12
186    28.12.2018
187    2018-12-28
Name: invoiceDate, Length: 188, dtype: object

but I use:

df['invoiceDate'] = pd.to_datetime(df['invoiceDate'], errors='coerce') 
df['invoiceDate'].dt.strftime('%d.%m.%Y')

output: (it writes the first two in the same format but then it goes wrong and gets month by day and month by month.)

0      16.09.2014
1      16.09.2014
2      12.02.2014 (true)
3      02.12.2014 (false)
4      01.09.2018
   
183    30.03.2015
184    04.12.2017 (true)
185    12.04.2017 (false)
186    28.12.2018
187    28.12.2018
Name: invoiceDate, Length: 188, dtype: object

If the output should be;

output :

0      16.09.2014
1      16.09.2014
2      02.12.2014
3      02.12.2014
4      09.01.2018

183    30.03.2015
184    12.04.2017
185    12.04.2017
186    28.12.2018
187    28.12.2018
Name: invoiceDate, Length: 188, dtype: object

CodePudding user response：

In this particular case, you just need to pass dayfirst=True to parse the dates as you intend

df['invoiceDate'] = (
    pd.to_datetime(df['invoiceDate'], errors='coerce', dayfirst=True)
      .dt.strftime('%d.%m.%Y')
)

CodePudding user response：

If you set the column of the dates to = date you could do the following

df['Date'] = pd.to_datetime(df['Date'], infer_datetime_format=True)

from here you can simply tell it the format you want the date to be structured using a strftime

df['Date'] = df['Date'].apply(lambda x : x.strftime('%d.%m.%Y'))