Different date formats in pandas dataframe-CodePudding

Below is my data frame -

date                                name
18-Sep-18                            bb
6-Aug-18                             vv
Kingdom on 2 January 2019            cc
Kingdom on 9 June 2021               aa
States on 10 September 2021          bb
States on 14 April 2021              mm

I want my data frame -

date                                name
2018-09-18                            bb
2018-08-06                            vv
2019-01-02                            cc
2021-06-09                            aa
2021-09-10                            bb
2021-04-14                            mm

I have done the following code -

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

But it's not working

CodePudding user response：

You can clean up the data with a regex first (here removing all the non digits before the first digit):

df['date'] = pd.to_datetime(df['date'].str.replace('^\D (?=\d)', ''))

output (with an new column):

                          date name      date2
0                    18-Sep-18   bb 2018-09-18
1                     6-Aug-18   vv 2018-08-06
2    Kingdom on 2 January 2019   cc 2019-01-02
3       Kingdom on 9 June 2021   aa 2021-06-09
4  States on 10 September 2021   bb 2021-09-10
5      States on 14 April 2021   mm 2021-04-14